MySql进阶使用之视图、索引（详解）

前言

今天给大家带来分享有关MySql进阶使用的知识分享，希望能给大家带来帮助，请认真阅读。接下来跟着我们的思维导图一起来看看吧。

1.视图

1.1 什么是视图

视图（View）是在数据库中创建的虚拟表，它基于查询的结果集。它是通过使用存储在数据库中的数据来动态生成的，但在物理存储上并不保留数据。视图可以被视为一个存储了特定数据的虚拟表，类似于物理表，但实际上并不存储任何数据。

视图可以由一个或多个表的列组成，并且可以根据需求进行筛选、过滤、排序和聚合等操作。它提供了一种简化和抽象的方式来访问和处理数据库中的数据。

使用视图可以为用户隐藏复杂的数据模型和查询，只暴露所需的数据和字段，从而简化数据访问和操作。视图还可以提供数据安全性，限制用户对敏感数据的访问权限。

通过创建视图，可以避免重复编写复杂的查询语句，提高查询的效率和可重用性。视图还可以用作其他视图的基础，实现更复杂的数据操作和分析。

1.2 视图与数据库表的区别

1. 数据存储：数据库表是物理存储的实体，它在数据库中占据一定的存储空间，直接存储数据记录。而视图只是一个虚拟的、基于查询结果集的表，不直接存储数据，它只存储了查询定义和规则。
2. 数据内容：数据库表存储了完整的数据记录，包括每个字段的值和行之间的关系。视图是基于表或其他视图的查询结果集，它只包含符合查询条件的部分数据或经过筛选、聚合、计算等操作后得到的结果。
3. 数据维护：表是可以直接修改、插入和删除数据的实体，对表的操作会直接影响存储在其中的数据。视图是只读或有限可写的，对视图进行的操作会间接地对底层的表或数据进行修改。
4. 数据结构：表有自己的列和行，定义了数据的结构和关系。视图可以由一个或多个表的列、行和数据关系组成，它是基于表结构和查询条件来动态生成的。
5. 数据访问：表可以直接被用户查询、更新和操作，用户可以直接访问表中的数据。而视图通常用于提供简化、安全和抽象的数据访问层，用户可以通过视图来查询和操作数据，而对于底层的表结构和数据的具体细节可以隐藏起来。

1.3 视图的优点

1. 简化数据访问：视图提供了对数据的简化和抽象访问方式。通过创建视图，可以隐藏复杂的查询语句和数据模型，用户可以直接从视图中查询、过滤和操作数据，无需了解底层表的结构和关系。
2. 数据安全性：视图可以用于实现数据安全性和权限控制。通过视图，可以限制用户只能访问特定的列、行或满足特定条件的数据，从而保护敏感数据的安全性，并确保用户只能获取其所需的数据。
3. 数据简化与聚合：视图可以对底层表执行复杂的查询、筛选、排序和聚合操作，从而简化了用户对数据的操作。用户可以通过视图获取已经聚合或计算好的结果，而不需要自己编写复杂的查询语句。
4. 逻辑数据独立性：通过使用视图，可以实现逻辑数据独立性。当数据库的表结构发生变化时，只需要修改视图的定义，而不需要修改使用这些视图的应用程序或查询。
5. 查询重用：视图提供了查询的重用性。可以创建基础视图，然后在其他视图或查询中使用它们，从而避免重复编写复杂的查询语句，并提高查询的效率和可维护性。
6. 数据逻辑封装：视图可以将复杂的数据逻辑封装在一个虚拟的表中。这样，应用程序和用户只需要与视图交互，而无需了解底层数据模型和具体的计算逻辑，提高了数据的抽象和封装程度。

1.4 视图的使用

语法

CREATE VIEW <视图名> AS <SELECT语句>

语法说明

• <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
• <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

• 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
• SELECT 语句不能引用系统或用户变量。
• SELECT 语句不能包含 FROM 子句中的子查询。
• SELECT 语句不能引用预处理语句参数。

案例

--创建视图
CREATE view v_student_score as
select
  s.*,sc.cid,sc.score,c.cname,t.*
from
t_mysql_student s,
t_mysql_score sc,
t_mysql_course c,
t_mysql_teacher t
where 
s.sid = sc.sid
and sc.cid = c.cid
and c.tid = t.tid;
--查看视图
SELECT * from v_student_score

结果显示

创建视图结果

查看视图结果

注意事项：

1. 数据完整性：创建视图时，要确保视图的查询逻辑和条件是正确的，并且能够返回所需的数据。对于涉及多个表的视图，要确保表之间的连接和关系是正确的，以避免查询结果不准确或丢失数据。
2. 性能考虑：创建视图时，要考虑查询的性能。如果视图查询复杂或涉及大量的数据，可能会影响查询的性能。可以使用索引、优化查询语句和限制返回的数据量等方法来提高性能。
3. 数据安全性：在创建视图时，要注意设置适当的权限和访问控制，以确保只有具有权限的用户能够访问和修改视图。可以使用数据库的权限管理功能来控制对视图的访问权限。
4. 视图的命名：选择一个有意义和描述性的视图名称，以便于理解和维护。命名应该与视图所表示的数据逻辑和用途相关联，并遵循命名约定或规范。
5. 视图的更新性：需要根据业务需求和实际情况考虑视图的更新性。视图通常是只读的，不允许直接修改底层的表数据。然而，有些情况下可能需要对视图进行可更新操作，这需要谨慎处理并确保数据的一致性和完整性。
6. 视图的维护：需要定期检查和维护视图，确保它们与底层表的结构和数据保持同步。如果底层表发生了变化，可能需要相应地修改视图的定义或重新生成视图。

例如：

--下面是这是一个错误示范，创建视图是要避免字段重复的问题，否则会创建失败
CREATE view v_student_score as
select
  *
from
t_mysql_student s,
t_mysql_score sc,
t_mysql_course c,
t_mysql_teacher t
where 
s.sid = sc.sid
and sc.cid = c.cid
and c.tid = t.tid

显示结果

2. 索引

2.1 什么是索引

数据库索引是一种数据结构，用于提高数据库中数据表的查询性能。它是根据一个或多个列的值创建的，以便快速定位和访问数据行。

索引的作用类似于书籍的目录，它可以帮助数据库快速找到存储在表中特定列或组合列上的数据。通过使用索引，数据库可以避免全表扫描，而是根据索引的值快速定位到目标数据行，从而提高查询效率。

索引可以根据列的值排序，从而支持排序和范围查询。它还可以提供数据的唯一性检查，防止重复数据的插入。索引通常是在创建表的时候定义的，但也可以在表创建完成后添加和修改。

然而，索引也会增加数据库的存储空间和写操作的开销。当数据经常需要更新或插入时，索引的维护成本也会增加。因此，在创建索引时需要权衡查询性能和数据修改的频率，并根据实际情况进行优化和调整。

常见的索引类型包括聚集索引、非聚集索引、唯一索引、组合索引等。每种索引类型都有其适用的场景和性能特点，根据具体的查询需求和数据访问模式选择合适的索引策略非常重要。

2.2 为什么要使用索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。

索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1) 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2) 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

2.3 索引的优缺点

优点

1. 提高查询性能：索引可以减少数据查询的时间复杂度，通过快速定位到具有特定值的数据行，而不需要扫描整个数据表。索引使得数据库可以更快地响应查询请求，提高了查询的效率。
2. 加快排序和范围查询：索引可以根据列的值进行排序，从而加速排序操作。它还可以通过定位到某个范围内的数据行，使得范围查询更加高效。
3. 降低系统资源消耗：通过使用索引，数据库可以减少执行查询所需的资源消耗，例如CPU计算时间和磁盘I/O操作次数。这样可以让数据库系统更有效地利用资源，提高整体性能和吞吐量。
4. 提供唯一性约束：索引可以用于实施唯一性约束，防止重复数据的插入。通过在唯一索引上添加约束，可以保证数据的一致性和完整性。
5. 改善并发性能：索引可以提供更细粒度的数据访问控制，减少并发操作之间的竞争，从而提高数据库的并发性能。例如，在查询条件中使用索引，可以减少锁冲突和阻塞情况的发生。

缺点

1. 提高查询性能：索引可以减少数据查询的时间复杂度，通过快速定位到具有特定值的数据行，而不需要扫描整个数据表。索引使得数据库可以更快地响应查询请求，提高了查询的效率。
2. 加快排序和范围查询：索引可以根据列的值进行排序，从而加速排序操作。它还可以通过定位到某个范围内的数据行，使得范围查询更加高效。
3. 降低系统资源消耗：通过使用索引，数据库可以减少执行查询所需的资源消耗，例如CPU计算时间和磁盘I/O操作次数。这样可以让数据库系统更有效地利用资源，提高整体性能和吞吐量。
4. 提供唯一性约束：索引可以用于实施唯一性约束，防止重复数据的插入。通过在唯一索引上添加约束，可以保证数据的一致性和完整性。
5. 改善并发性能：索引可以提供更细粒度的数据访问控制，减少并发操作之间的竞争，从而提高数据库的并发性能。例如，在查询条件中使用索引，可以减少锁冲突和阻塞情况的发生。

2.4 什么时候使用索引

1. 提高查询性能：索引可以减少数据查询的时间复杂度，通过快速定位到具有特定值的数据行，而不需要扫描整个数据表。索引使得数据库可以更快地响应查询请求，提高了查询的效率。
2. 加快排序和范围查询：索引可以根据列的值进行排序，从而加速排序操作。它还可以通过定位到某个范围内的数据行，使得范围查询更加高效。
3. 降低系统资源消耗：通过使用索引，数据库可以减少执行查询所需的资源消耗，例如CPU计算时间和磁盘I/O操作次数。这样可以让数据库系统更有效地利用资源，提高整体性能和吞吐量。
4. 提供唯一性约束：索引可以用于实施唯一性约束，防止重复数据的插入。通过在唯一索引上添加约束，可以保证数据的一致性和完整性。
5. 改善并发性能：索引可以提供更细粒度的数据访问控制，减少并发操作之间的竞争，从而提高数据库的并发性能。例如，在查询条件中使用索引，可以减少锁冲突和阻塞情况的发生。

2.5 索引什么情况下会失效

• like以通配符%开头索引失效
  
• 当全表扫描比走索引查询的快的时候，会使用全表扫描，而不走索引
  
• 字符串不加单引号索引会失效
  
• where中索引列使用了函数（例如substring字符串截取函数）
  
• where中索引列有运算（用了< or > 右边的索引会失效，用<= or >= 索引不会失效）
  
• is null可以走索引，is not null无法使用索引（取决于某一列的具体情况）
  
• 复合索引没有用到左列字段（最左前缀法则，如果没用用到最左列索引，或中间跳过了某列有索引的列，索引会部分失效）
  
• 条件中有or，前面的列有索引，后面的列没有，索引会失效。想让索引生效，只能将or条件中的每个列都加上索引
  

2.6 索引的分类

MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），但是只要列中有null值就不会被包含在索引中。

2.6.1 普通索引

创建语法

create index index_name on table(column)；

修改表结构方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

删除索引

DROP INDEX index_name ON table

案例

我事先创建了一个t_log表，并插入了18000多条数据

未使用索引前查询

SELECT * from t_log where moduleid='200001'

结果

创建索引

CREATE INDEX idx_moduleid on t_log(moduleid)

创建索引后的查询

SELECT * from t_log where moduleid='200001'

总结：创建索引前后的一个查询速度明显有差距，明显创建索引后的速度更快，但是我们不能忽略创建索引的时间

2.6.2.唯一索引

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX indexName ON table(column(length))

2.6.3.主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。

CREATE TABLE mytable( 
  ID INT NOT NULL, 
  username VARCHAR(16) NOT NULL, 
  PRIMARY KEY(ID) 
);

2.6.4.组合索引

组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

2.6.5.全文索引

• 概念

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

• 版本支持

Mysql 5.6之前版本，只有MyISAM支持全文索引，5.6之后，Innodb和MyISAM均支持全文索引。另外，只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

3. 数据备份与还原

3.1 什么是数据备份与还原

数据备份与还原是指将重要数据从一个存储设备复制到另一个存储设备，并在需要时将备份的数据恢复到原始设备的过程。

数据备份是为了防止意外数据丢失或数据损坏而采取的预防措施。通过备份，可以创建数据的副本，以便在发生以下情况时进行恢复：

1. 硬件故障：当计算机硬件（如硬盘、SSD）发生故障时，备份文件可以用来恢复数据。
2. 数据丢失或意外删除：当用户不小心删除了重要文件或数据丢失时，可以从备份中恢复数据。
3. 恶意软件攻击：恶意软件（如病毒、勒索软件）可能会损坏或删除数据，备份可以保护数据免受这些攻击。

数据还原是指将备份的数据恢复到原始设备的过程。当原始数据丢失或损坏时，可以使用备份数据还原到相同的设备，以便恢复丢失的文件和数据。

3.2 为什么要数据备份与还原

1. 数据恢复：数据备份提供了一种紧急恢复数据的手段。当数据丢失、受损、被删除或遭受其他意外事件时，可以通过还原备份数据来恢复原始数据，避免因此造成的数据丢失和业务中断。
2. 硬件故障应对：备份数据可以在硬件故障时提供保护，例如当硬盘崩溃、SSD损坏或服务器发生故障时，可以使用备份数据来恢复丢失的文件和数据。
3. 恶意攻击应对：备份数据可以作为应对恶意软件（如病毒、勒索软件）攻击的重要手段。如果数据被感染、加密或破坏，可以通过还原备份数据来恢复原始数据并恢复受损的系统。
4. 人为错误恢复：当用户不小心删除重要文件或者数据受到意外修改时，备份数据可以提供一个补救措施，让用户可以轻松恢复被删除或修改的数据。
5. 数据迁移：备份数据可以用于数据迁移或转移。当需要更换或升级计算机设备、迁移到新的服务器、更换存储介质等时，备份数据可以方便地将数据迁移到新设备或存储介质上。

3.3 数据备份与还原的操作

3.3.1 导出

• 选择导出的数据表或数据库右击，选择转储SQL文件，在选择结构和数据

• 使用DOM命令实现数据导出

找到安装mysql的文件夹下的bin文件夹，在该文件目录下输入cmd以管理员的身份运行终端，输入指定代码实现数据导出。

这种导出的速度1s不到，速度更快。

• 使用sql语句

语句

select * from <表名> into outfile '<文件路径>'

案例

select * from t_log into outfile 'C：123456.sql'

这种方式和上面那种速度一样

执行前

执行后

3.3.2 导入

• 先创建对应好的数据库和数据表再导入数据

这种导入方式如果数据量很大的话会影响效率

• 使用DOM命令子在终端实现数据导入

这种方式比上面的那种方式更快捷

• 使用sql语句

load data infile ''<文件路径>' into table <表名>(id,ip,userid,moduleid,content,createdate,url); --字段名

结束语

希望对大家有帮助，感谢老铁三连。