「MySQL高级篇」SQL优化

时间：2022-12-04 17:02:00

大家好，我是Zhan，一名个人练习时长一年半的大二后台练习生，最近在学MySQL高级篇，欢迎各路大佬一起交流讨论

????本篇速览

在前面对索引的的学习中，我们学习到了从MySQL“底层”优化了SQL执行查询的算法，提高了SQL执行的效率，除了那种方法能提高我们的效率，我们也能从SQL语句方面去优化，对于业务需求，更好的SQL语句能得到更高的效率，而从基础篇到现在，似乎我们对于SQL语句的运用停留在能跑就行的阶段，而本篇将从以下七个方面，让你的SQL语句更加优雅高效：

  • 1️⃣插入数据时的SQL优化
  • 2️⃣主键优化，同时会讲到主键的设计原则
  • 3️⃣order by 语句的优化
  • 4️⃣group by 语句的优化

1️⃣ Insert 优化

提到插入数据，我们自然是通过Insert语句插入数据，而我们在往数据库中插入数据的时候，通常就是一条一条Insert语句去执行，而插入多条数据呢，我们可以有哪些实现方式呢？

⛏批量插入

因为每一次Insert都需要与数据库建立连接和网络传输，因此对于多条数据，批量插入的效率优于普通的一条插入。

同时，批量插入的数据也不要太多，最好维持在500-1000条，如果有超过这么多条的，最好是分多次批量插入。

⚒ 手动提交事务

在MySQL中，事务提交方式默认为自动提交，也就是说一条Insert语句后，就会自动提交事务，再次执行一条Insert就会再次开启事务，然后又提交，这样就会有事务频繁的开启与提交，因此最好是在插入之前开启事务，然后所有插入结束后提交事务。

	start transaction; 开始事务
	insert ……; 插入数据1
	insert ……; 插入数据2
	insert ……; 插入数据3
	commit; 提交事务
复制代码

???? 主键顺序插入

主键插入，要么乱序插入，要么顺序插入，实际上顺序插入的性能更高，这取决于MySQL数据组织结构的，我们在后面的主键优化中会详细讲解。

顺序插入是指ID以升序的方式进行插入，比如1,2,3,4,5,6……

如果现在我们要导入百万级的数据，此时我们使用Insert语句的性能就不高了，我们应该怎么做呢？

⚔ 大批量插入数据

如果需要一次性插入大批量数据，使用Insert语句插入的性能很低，此时我们可以使用MySQL数据库提供给我们的指令load进行插入，例如：

「MySQL高级篇」SQL优化

而我们如何使用这个功能呢，它有三部曲：

  1. 客户端连接服务端的时候，加上参数--local-infile 
mysql --local-infile -u root -proot
复制代码
  1. 设置全局参数local_infile = 1，开启从本机加载文件导入数据的开关
set global local_infile = 1
复制代码
  1. 执行load指令，把准备好的数据加载到表结构中
load data local infile `/root/sql1.log` into table `tb_user` fields terminated by ',' lines terminated by '\n';
复制代码

2️⃣ 主键优化

在讲解插入数据时的优化的时候，我们有提到主键插入的时候顺序插入的性能高于乱序插入的性能，至于为什么呢，我们将在这一小节详细讲解它的底层原理：

在此之前，我们首先需要了解InnoDB存储引擎的数据组织方式：在InnoDB存储引擎中，表数据是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

在前文讲索引的时候我们有讲解过聚集索引，它的选取规则是在有主键的情况下，默认选择主键为聚集索引，也就是说，行数据存储在主键构成的索引的B+Tree中，如下图所示：

「MySQL高级篇」SQL优化

而索引构成的B+Tree在叶子结点顺序存放，也就是说：表数据是根据主键顺序组织存放的

此处，每一个结点，不管是分支结点还是叶子结点都是存储在一个页中，也就是我们之前提到的逻辑存储结构：

「MySQL高级篇」SQL优化

那么插入一条数据，在逻辑存储结构中是什么样的流程呢：

???? 页分裂

???? 顺序插入

对于顺序插入，依次去写数据即可，如果一页的空间不足就去申请下一页的空间，并用双向指针连接，这就是主键顺序插入

「MySQL高级篇」SQL优化

主键顺序插入基本上就是如果当前的最后一页有空间则填入，如果空间不足就放在下一页的空间使用双向链表连接保证它的有序

???? 乱序插入

现在有两页写满了的数据：

「MySQL高级篇」SQL优化

如果我们不是顺序插入，此时插入了一条主键为50的数据，此时就会出现页分裂：找到第一页的后半部分23,47申请新的一页放进去，然后把50放在新的一页的最后：

然后重新排列三页的位置：1#page <==> 3#page <==> 2#page

「MySQL高级篇」SQL优化

所以，其实我们可以发现，相较于顺序插入，乱序插入的过程更加复杂、繁琐，这种现象我们称之为页分裂，因此在插入的时候最好是按照主键顺序插入

???? 页合并

有页分裂，自然也有页合并，否则，对于固定大小的一页来说，如果一页的空间利用率不高，将会造成冗余的内存占用，那么什么时候会出现业合并呢？

首先谈谈删除记录时，页中数据的变化，其实它有点类似于逻辑删除，在删除一条记录的时候，在实际上没有被物理删除，只是记录了被标记为删除，并且它的空间被允许其他记录声明使用，但是什么时候会被真正物理删除了，那就是业合并的时候：

当页中的记录达到MERGE_THRESHOLD（默认为50%） 的时候，InnoDB存储引擎会开始寻找两边的页，是否能够达到合并的条件，如果能就会产生业合并，以达到优化空间的目的：

对于这种情况：

「MySQL高级篇」SQL优化

第二页的数据删除超过了一半，而在比对的时候，与前一页无法合并，但是能和第二页合并，于是就合并了两页的数据：

「MySQL高级篇」SQL优化

其中MERGE_THRESHOLD（默认为50%） 是可以自行设置的

???? 主键设计原则

有了上面的前置知识，我们能总结出四点主键设计的原则，帮助我们在设计主键时避坑

???? 尽量降低主键的长度

对于一张表来说，主键索引只有一个，但是会有很多个二级索引，如果说索引的长度比较长，二级索引比较多，那么在二级索引的叶子结点处会占用大量的磁盘空间，而且在搜索的时候会耗费更多的磁盘IO

???? 尽量顺序插入

插入数据的时候尽量选择顺序插入，并选用AUTO_INCREMENT这种自增主键，这个我们在页分裂中有详细讲解

???? 尽量不要使用自然主键

尽量不要使用UUID做主键或者其他自然主键，比如身份证号这种，我们每次生成的UUID都是无序的，那么就会导致在插入的时候产生乱序的插入，同样，UUID也比较长

???? 尽量不要修改主键

业务操作的时候，尽量少修改主键，因为会伴随着索引结构的修改，不过在一般的业务里面，也没人改主键吧????

3️⃣ Order by 优化

对于MySQL中的排序有两种方式，那可能有朋友就疑惑了，那排序不就Order by嘛，哪来的两种方式，实际上这里的两种方式指的是底层实现的两种方式：

Using filesort

通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

Using index

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

我们不妨来猜一猜一下几组情况的索引使用情况，已知存在联合索引age,phone，问下面哪些SQL查询使用到了索引，哪些没有使用到：

  1. EXPLAIN SELECT id,age,phone from tb_user order by age
  2. EXPLAIN SELECT id,age,phone from tb_user order by age,phone
  3. EXPLAIN SELECT id,age,phone from tb_user order by age desc ,phone desc
  4. EXPLAIN SELECT id,age,phone from tb_user order by phone,age
  5. EXPLAIN SELECT id,age,phone from tb_user order by age asc ,phone desc

「MySQL高级篇」SQL优化

第一个，按照最左前缀法则，联合索引的最左边是age，因此使用了索引排序

「MySQL高级篇」SQL优化

第二个，使用联合索引进行排序，毋庸置疑

「MySQL高级篇」SQL优化

第三个，如果二者都查倒序排序，此时尽管还是使用了索引，但是多了一个Backward index scan，也就是说从索引的最后面开始扫描 我们只需要从最后那个叶子结点往回扫描即可

「MySQL高级篇」SQL优化

第四个，如果说联合索引的顺序反了，我们在讲最左前缀法则的时候，尽管顺序反了，索引也是能够使用了，是因为那个是 where 条件，没有先后顺序，只有书写顺序，但是对于order，书写顺序就代表了先后顺序，先对哪个进行排序。

「MySQL高级篇」SQL优化

第五个，在创建索引的时候，默认按照升序往后走，也就是说先按照age进行升序排列，再按照phone进行升序排列，而此处查找的时候，按照age升序排序，按照phone降序排序，因此就会不完全使用索引。

「MySQL高级篇」SQL优化

这张图其实能帮助我们很好的理解为什么不能一个升序一个降序，索引无法按照这个去排列

这里也说了，在创建索引的时候我们默认按照升序排序，那我们按照降序排序创建索引应该怎么创建呢？ 其实只需要在字段的后面加上asc / desc即可：

CREATE INDEX idx_user_age_phone_ad on tb_user(age asc, phone desc)

「MySQL高级篇」SQL优化

在创建结束后，我们再次去查询，得到的就是使用索引排序了：

「MySQL高级篇」SQL优化

???? 总结

  1. 根据排序字段建立合适的索引，多字段排序的时候，也遵循最左前缀法则。
  2. 尽量使用覆盖索引，减少回表查询
  3. 多字段排序的时候，注意联合索引在创建时的规则
  4. 如果不可避免出现FileSort，我们可以适当增加排序缓冲区的大小sort_buffer_size，因为如果缓冲区满了，它就会去磁盘空间进行排序，效率很低

4️⃣ Group by 优化

与Order by相比，其实二者很像，因为二者都是需要排序，但是Group by多了一个分组

它使用索引的条件是，也就是按照索引进行分组的时候，但是有一个情况是和联合索引有一些出入的：

「MySQL高级篇」SQL优化

对于这种情况，我们通过Explain分析可以发现，这个居然使用到了索引，原因是这里有使用到profession作为查询条件，然后根据年龄进行分组

标签：

相关文章