本文是基于 极客时间——MySQL 实战 45 讲 整理的学习笔记,仅供学习参考,请勿用于商业用途,如若侵权,请联系并删除。
课程重点:
- 了解常见的几种导致性能差异巨大的 SQL 语句
在 MySQL 中,有很多看上去逻辑相同,但性能却差异巨大的 SQL 语句。对这些语句使用不当的话,就会不经意间导致整个数据库的压力变大。
案例一:条件字段函数操作
当需要统计某张表的某个月份的合计数量时,这个时候通常会使用 month()
函数,类似的 SQL 语句如下:
1 | select count(*) from t where month(created_at)=7; |
对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
需要注意的是,优化器并不是要放弃使用这个索引。
在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引 created_at,优化器对比索引大小后发现,索引 created_at 更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 created_at。
所以,当使用 explain 命令查看这条 SQL 语句的执行计划时:
- key=”created_at” 表示的是,使用了 created_at 这个索引;在测试表数据中插入了 10 万行数据,rows=100335,说明这条语句扫描了整个索引的所有值
- Extra 字段的 Using index,表示的是使用了覆盖索引
也就是说,由于在 created_at 字段加了 month() 函数操作,导致了全索引扫描。为了能够用上索引的快速定位能力,我们就要把 SQL 语句改成基于字段本身的范围查询。按照下面这个写法,优化器就能按照我们预期的,用上 created_at 索引的快速定位能力了。
1 | select count(*) from t where created_at >= '2022-06-01' and `created_at` <= '2022-07-01'; |
案例二:隐式类型转换
1 | select * from t where order_no = 110717; |
订单编号 order_no 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需要走全表扫描。你可能也发现了,order_no 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换。
那么,现在这里就有两个问题:
- 数据类型转换的规则是什么?
- 为什么有数据类型转换,就需要走全索引扫描?
数据库里面类型这么多,这种数据类型转换规则更多,我记不住,应该怎么办呢?
这里有一个简单的方法,看 select “10” > 9 的结果:
- 如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1
- 如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0
从图中可知,select “10” > 9 返回的是 1,所以你就能确认 MySQL 里的转换规则了:在 MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。
因此上面的语句对于优化器来说,相当于:
1 | select * from t where CAST(order_no AS signed int) = 110717; |
也就是说,这条语句触发了我们上面说到的规则:对索引字段做函数操作,优化器会放弃走树搜索功能。
案例三:隐式字符编码转换
1 | select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; |
当联表查询时,这两个表的字符集不同,例如一个是 utf8,另一个是 utf8mb4,这时做表连接查询的时候用不上关联字段的索引。
字符集 utf8mb4 是 utf8 的超集,所以当这两个类型的字符串在做比较的时候,MySQL 内部的操作是,先把 utf8 字符串转成 utf8mb4 字符集,再做比较。
因此, 在执行上面这个语句的时候,需要将被驱动数据表里的字段一个个地转换成 utf8mb4,再跟 L2(id= 2) 做比较。
也就是说,实际上这个语句等同于下面这个写法:
1 | select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; |
CONVERT() 函数,在这里的意思是把输入的字符串转成 utf8mb4 字符集。
这就再次触发了我们上面说到的原则:对索引字段做函数操作,优化器会放弃走树搜索功能。
连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。
总结
- 条件字段函数操作、隐式类型转换、隐式字符编码转换,这三个例子都会导致对索引字段做函数操作
- 对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能
- 对索引字段做函数操作,会导致全表扫猫