「MySQL高级篇」SQL优化

2,327 阅读10分钟

开启掘金成长之旅!这是我参与「掘金日新计划 · 12 月更文挑战」的第四天,点击查看活动详情

大家好,我是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进行插入,例如:

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

  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中,如下图所示: 而索引构成的B+Tree在叶子结点顺序存放,也就是说:表数据是根据主键顺序组织存放的

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

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

📜 页分裂

🟤 顺序插入

对于顺序插入,依次去写数据即可,如果一页的空间不足就去申请下一页的空间,并用双向指针连接,这就是主键顺序插入 主键顺序插入基本上就是如果当前的最后一页有空间则填入,如果空间不足就放在下一页的空间使用双向链表连接保证它的有序


🟣 乱序插入

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

然后重新排列三页的位置:1#page <==> 3#page <==> 2#page 所以,其实我们可以发现,相较于顺序插入,乱序插入的过程更加复杂、繁琐,这种现象我们称之为页分裂,因此在插入的时候最好是按照主键顺序插入


📑 页合并

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

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

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

对于这种情况:

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

其中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

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

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

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

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

第五个,在创建索引的时候,默认按照升序往后走,也就是说先按照age进行升序排列,再按照phone进行升序排列,而此处查找的时候,按照age升序排序,按照phone降序排序,因此就会不完全使用索引。 这张图其实能帮助我们很好的理解为什么不能一个升序一个降序,索引无法按照这个去排列


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

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

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


💬 总结

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

4️⃣ Group by 优化

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

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

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


🍁 友链


✒写在最后

都看到这里啦~,给个点赞再走呗~,也欢迎各位大佬指正,在评论区一起交流,共同进步!也欢迎加微信一起交流:Goldfish7710。咱们明天见~

求赞.jpeg