当前位置：首页 > article >正文

MySQL 篇-深入了解视图、SQL 优化（主键优化、order by 优化、group by 优化、update 优化等）

article 2024/10/8 22:58:01

🔥博客主页：【小扳_-CSDN博客】
❤感谢大家点赞👍收藏⭐评论✍

文章目录

1.0 SQL 优化

1.1 SQL 优化 - 插入数据

1.2 SQL 优化 - 主键优化

1.2.1 页分裂

1.2.2 页合并

1.2.3 主键设计原则

1.3 SQL 优化 - order by 优化

1.3.1 单字段排序

1.3.2 多字段排序

1.4 SQL 优化 - group by 优化

1.5 SQL 优化 - limit 优化

1.6 SQL 优化 - count 优化

1.7 SQL 优化 - update 优化

2.0 视图概述

2.1 检查选项 - cascaded、local

2.2 视图 - 更新

2.3 视图 - 作用

1.0 SQL 优化

SQL 优化包括多种技术和策略，旨在提高数据库插入、查询的性能。接着来介绍常用的 SQL 优化策略，比如主键优化、order by 优化、group by 优化等等，一般来说是通过索引的角度来思考如何提升查询性能。

1.1 SQL 优化 - 插入数据

当插入的数据量小于 1000 条时，可以使用 insert 优化：

1）批量操作：

insert into table_name(column) values (),(),(),()...
这种批量操作比一条一条插入数据的操作效率高很多，减少频繁的连接。

2）手动提交事务：

/*开启事务*/
start transaction;
/*插入数据*/
insert into table_name(column) values (),()...;
insert into table_name(column) values (),()...;
insert into table_name(column) values (),()...;
insert into table_name(column) values (),()...;
insert into table_name(column) values (),()...;
/*提交数据*/
commit ;

减少频繁的开启事务和提交事务、关闭事务，从而提高插入效率。

3）主键顺序插入：

主键按照顺序插入，简单来说：可以有效的防止页分裂，从而提高插入效率，因为一旦出现页分裂，就会即浪费空间，也会浪费时间。

减少页分裂的概率：

当主键按照顺序插入时，新的数据通常会被添加到现有页的末尾。这种情况下，页面的填充程度不会迅速达到上限，从而减少了页分裂的发生。反之，如果随机插入数据，可能会导致某些页面迅速填满，进而触发页分裂。

提高插入效率：

顺序插入减少了页面的随机 I/O 操作，因为数据是按顺序写入的，数据库可以更高效地管理数据页。页分裂不仅会消耗时间（因为需要创建新页并移动数据），还会导致额外的 I/O 操作，从而影响整体性能。

节省空间：

页分裂会导致页中出现空隙和碎片，影响存储的效率。顺序插入有助于保持数据的紧凑性，最大限度地利用页面空间。

当需要一次性插入大批量数据，使用 insert 语句插入性能就较低了，此时可以使用 MySQL 数据库提供的 load 指令进行插入，具体操作如下：

1）客户端连接服务器端的时候，加上参数 '--local-infile'：

mysql --local-infile -u root -p

2）设置全局参数 local_infile 为 1，开启从本地加载文件导入数据的开关：

查看当前 local_infile 是否开启的语句：

select @@local_infile;
运行结果：

默认是关闭状态。

开启 local_infile 的语句：

set global local_infile = 1;
执行结果：

3）执行 load 指令将准备好的数据，加载到表结构中：

load data local infile '需要添加的数据文件' into table '需要添加到具体的数据库' fields terminated by '字段之间的分割符' lines terminated by '行数据之间的分割符';

这就可以将本地的大量数据一次性批量的导入到数据库中。

1.2 SQL 优化 - 主键优化

1.2.1 页分裂

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT）。

在索引中，非叶子节点存放的是索引，而叶子节点存储的是行数据，这些行数据默认是按照主键大小进行升序排序。不管是叶子节点还是非叶子节点，都是存储在页单位中，页的最大存储空间为 16 kb ，一旦超过了 16 kb，就需要创建新的空间进行存储，叶子节点之间，也就是页之间会有双向链表关联着。

接着根据以上的条件，来了解数据的插入方式：

1）按照主键顺序插入数据：

按照主键顺序插入数据，一旦当前的页空间不足时，就会开辟新的空间，存储新插入的数据，且页之间会用链表进行关联。这样空间可以得到充足的利用，效率较高。

2）暗战主键乱序插入数据：

当插入主键序号为 50 的数据时，当需要在页之间插入发现空间已经不足了。

先创建新的页空间，再从第一个页中获取页的后 50% 的数据放到新开辟的空间，接着将主键序号 50 的数据也放到新的空间中。

最后，根据主键的大小利用双向链表进行重新关联起来。

以上的过程就是页分裂，所以说，如果按照主键乱序插入数据，很可能会发生页分裂，从而导致空间的浪费，效率低下。

1.2.2 页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

页合并之前：

页合并之后：

1.2.3 主键设计原则

1）满足业务需要的情况下，尽量降低主键的长度。

2）插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键。

3）尽量不要使用 UUID 做主键或者时其他自然主键，如身份证号。因为长度太长了，而且不是按照顺序插入。

4）业务操作时，避免对主键的修改。

1.3 SQL 优化 - order by 优化

对于 order by 的优化，简单来说，就是在排序的时候，尽量通过索引来排序而且不进行回表查询的方式来获取到数据。

1）Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序，操作效率较低。

2）Using index：通过有序索引顺序扫面直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

1.3.1 单字段排序

举个例子：

explain select book_id,book_price from book order by book_price;

加索引之前的结果：

没有加索引的效率比较低，通过全表扫描，将符合条件的数据放到排序缓冲区再来完成排序操作。

接着给 book_price 字段加上索引：

/*给字段添加索引*/
create index idx_book_price on book(book_price);
/*查看索引*/
show index from book;

执行结果：

再对其进行排序：

explain select book_id,book_price from book order by book_price;

执行结果：

可以看到查询计划用到了 using index，效率较高，用到了索引来获取排序结果，而且没有进行回表查询。

如果使用 ' select * ' 语句：

explain select * from book order by book_price;

执行结果：

即使 book_price 字段已经有索引，但是需要返回的结果为 ' * ' ，所以 MySQL 对其进行优化了，直接按照全表查询，再将符合条件的数据放到排序缓冲区中完成排序操作，最后再返回排序后的结果。即使该查询计划用到了 book_price 字段的索引，仍需要进行回表查询，不能直接获取到已经排序好的结果，所以是 using filesort 方式，效率比较低。

因此避免使用 ' * ' 来查询，可以创建联合索引，选择需要的字段。

1.3.2 多字段排序

对于排序规则有两个字段以上的情况，如果是都是按照升序排序，在构建联合索引的时候，默认就是按照升序排序的；如果需求是获取降序的结果，那么即使在构建索引过程中没有指定降序排序，在查询过程中仍然会走索引，仍然是用到了 using index 的方式。

但是，对于多个字段的排序的方式不同，就需要在创建索引的时候，分别指定的排序方式，否则在查询的时候，没有对应的索引排序方式，就会走全表查询，使用 using filesore 的方式，效率较低。

简单来说，进行排序查询的时候，需要注意查看是否有相对应的索引排序方式，如果有，那么就会使用 using index 方式；如果没有相对应的索引排序方式，那么就会使用 using filesore 方式，效率比较低。

1.4 SQL 优化 - group by 优化

同理，在进行分组查询的时候，尽量使用索引来查询，并且使用覆盖查询，创建联合索引。

举个例子：

在添加索引之前进行分组查询：

explain select book_category, count(*) from book group by book_category;

执行结果：

没有用到索引，效率不高。

对 book_name、book_author、book_category 字段添加联合索引：

/*添加索引*/
create index idx_book_name_author_category on book(book_name,book_author,book_category);
/*查看索引*/
show index from book;

执行结果：

接着查询分组：

explain select book_category, count(*) from book group by book_category;

执行结果：

即使用到了索引进行分组查询，由于没有满足最左前缀法则，效率还是较低。

explain select book_name ,count(*) from book group by book_name;

执行结果：

1.5 SQL 优化 - limit 优化

一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化。

思路：先查询得到对应的 id，获取到的 id 当作一个表，再进行多表查询，条件就是 id 是否符合表中的 id 即可。这样在查询获取 id 的时候，可以根据主键索引来获取 id，在多表查询的时候，也是根据索引来进行查询的。

如果查询直接是 ' * ' 的时候，会进行全表查询：

explain select * from book limit 1,2;

执行结果：

而对于只查询返回 id 的时候，会走索引查询：

explain select book_id from book limit 1,2;

执行结果：

接着根据 id 来进行多表查询，查询返回 ' * '：

explain select * from book b1,(select book_id from book limit 2) b2 where b1.book_id = b2.book_id;

执行结果：

1.6 SQL 优化 - count 优化

1）count（主键）

InnoDB 引擎会遍历整张表，把每一行的主键 id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为 null）。

2）count（字段）

没有 not null 约束：InnoDB 引擎会遍历整张表把每一行字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null，计数累加。

有 not null 约束：InnoDB 引擎会遍历整张表把每一行的字段都取出来，返回给服务层，直接按行进行累加。不需要进行判断是否为 null 。

3）count（1）

InnoDB 引擎遍历整张表，但不取值。服务层对于返回每一行，放一个数字 '1' 进去，直接按行进行累加。

4）count（*）

InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话：count(字段) < count(主键) < count(1) <= count(*)，所以尽量使用 count(*) 。

1.7 SQL 优化 - update 优化

在对数据进行修改的时候，条件中的字段是存在索引且没有失效的时候，则会对索引进行加锁，也就是说其他线程再来对该行数据进行修改的时候会阻塞等待，直到锁被释放。如果条件中的字段不存在索引或者索引失效了，那么在对该表中的其他行数据进行修改的时候，会阻塞，因为从行锁升级为表锁了。

简答来说，如果需要对数据进行修改的时候，最好给该字段加上索引，当给该字段加上索引之后，此时锁的是行数据，只对某一行正在操作的行数据进行加锁。操作不同的行数据不会影响，可以并发执行，只是针对已经执行操作的行数据再次去修改当前行数据的时候会出现阻塞。如果该字段没有索引或者索引失效了，那么就会进行锁升级，升级为表锁，会对整个表加锁，只要对表中的某一行数据进行操作，其他行数据也会阻塞，并发量大大降低，效率也随着降低。

举个例子：

book_price 字段还没创建索引的时候：

对 book_price 为 80 的行数据进行修改：

由于没有创建索引，此时还是表锁，当修改数据还没提交之前，表锁还没释放，因此，其他并发操作的数据修改会受到影响，所以还在运行等待中。

直到修改数据操作提交：

这才成功获取到锁，再对数据进行修改。

接着给 book_price 字段加上索引：

create index idx_book_price on book(book_price);

show index from book;

执行结果：

继续之前的操作：

对 book_price 为 80 的行数据进行修改：

可以发现，此时修改其他行数据还没等到当前行数据修改提交的时候，操作修改其他行数据没有出现阻塞情况。是因为此时锁的是行数据，操作相同行数据的时候，会阻塞等待，而操作不同行数据的时候，不会出现阻塞等待。这就是索引带来的好处，当该字段有索引的时候，锁的就是行数据，没有索引的时候，锁的就是表。

这就大大增加了并发量，提高了效率。

2.0 视图概述

视图是一种虚拟存在的表。视图中的数据并不是在数据库中实际存在，行和列数据自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

简单来说，视图只保存了查询的 SQL 逻辑，不保存查询结果。所以在创建视图的时候，只要的工作就落在创建这条 SQL 查询语句上。

视图相关的 SQL 语句：

1）创建语句：

create [or replace] view view_name as select 语句 [with [cascaded | local] check option];

举个例子：

/*根据 book 基表创建视图*/
create or replace view view_book as select book_name name,book_author author from book;

执行结果：

2）查询语句：

查看创建视图语句:
show create view view_name;

查看视图数据:
select * from view_name;

举个例子：

-- 查看创建视图的语句
show create view view_book;

执行结果：

-- 查看视图中的数据
select * from view_book;

执行结果：

3）修改语句：

方式一：

create [or replace] view view_name as select 语句 [with [cascaded | local] check option];

可以发现方式一的修改语句与创建视图的 SQL 语句是一样的。

方式二：

alter view view_name as select 语句 [with [cascaded | local] check option];

举个例子：

-- 修改视图
alter view view_book as select book_id id,book_name name,book_author author from book;

执行结果：

4）删除语句：

drop view if exists view_name;

举个例子：

-- 删除视图
drop view if exists view_book;

2.1 检查选项 - cascaded、local

对于视图中的数据的增删改查的操作，和表的操作语句几乎是一样的。只不过，在视图中进行增删改查的操作之前，可以对条件进行检查，满足条件就可以在表中操作数据，需要注意的是，实际是对表中的数据进行操作，视图只是映射基表中的数据。

当使用 with check option 子句创建视图时， MySQL 会通过视图检查正在更改的每个行。例如插入、更新、删除。以使其符合视图的定义。MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySQL 提供了两个选项：cascaded、local，默认值为 cascaded 。

cascaded：