在 MySQL 中,有很多看上去逻辑相同,但性能却差异巨大的 SQL 语句。对这些语句使用不当的话,就会不经意间导致整个数据库的压力变大。
案例一:条件字段函数操作
假设我们维护一个交易系统表
CREATE TABLE `tradelog` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL,
`t_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tradeid` (`tradeid`),
KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;```
现在记录了 从 2016 到 2018年的数据,现在查询所有年份中7月份的数据
那么sql 应该是
```sql
select count(*) from tradelog where month(t_modified)=7;
我们认为t_modified 字段是有索引的,但却发现执行了特别久,才返回了结果。
如果问同事 ,他大概会告诉你:如果对字段做了函数计算,就用不上索引了,这是 MySQL 的规定
。
为什么条件是 where t_modified='2018-7-1’的时候可以用上索引,而改成 where month(t_modified)=7 的时候就不行了?
如果你的 SQL 语句条件用的是 where t_modified='2018-7-1’的话,引擎就会按照上面绿色箭头的路线,快速定位到 t_modified='2018-7-1’需要的结果。
实际上,B+ 树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。
但是,如果计算 month() 函数的话,你会看到传入 7 的时候,在树的第一层就不知道该怎么办了。
也就是说,
对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
需要注意的是,优化器并不是要放弃使用这个索引。
在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引 t_modified,优化器对比索引大小后发现,索引 t_modified 更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。
按照下面这个写法,优化器就能按照我们预期的,用上 t_modified 索引的快速定位能力了。
select count(*) from tradelog where
-> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
-> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
-> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');
到这里我给你说明了,由于加了 month() 函数操作,MySQL 无法再使用索引快速定位功能,而只能使用全索引扫描。
不过优化器在个问题上确实有“偷懒”行为,即使是对于不改变有序性的函数,也不会考虑使用索引。比如,对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句,这个加 1 操作并不会改变有序性,但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。
所以,需要你在写 SQL 语句的时候,手动改写成 where id = 10000 -1 才可以。
2 隐式类型转换
我们一起看一下这条 SQL 语句:
select * from tradelog where tradeid=110717;
交易编号 tradeid 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换。
那么,现在这里就有两个问题:
- 数据类型转换的规则是什么?
- 为什么有数据类型转换,就需要走全索引扫描?
这里有一个简单的方法,看 select “10” > 9 的结果:
- 如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1;
- 如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0。
验证结果如图 所示。
所以你就能确认 MySQL 里的转换规则了:在 MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。
select * from tradelog where tradeid=110717 相当于
select * from tradelog where CAST(tradid AS signed int) = 110717;
也就是说,这条语句触发了我们上面说到的规则:对索引字段做函数操作,
优化器会放弃走树搜索功能。
案例三:隐式字符编码转换
有两个表 table A。table B
table A 字符集 是 utf8
table B 字符集 是 utf8mb4
sql 如下
where A.id = B.id
等价于
where CONVERT(id USING utf8mb4)=B.id
CONVERT() 函数,在这里的意思是把输入的字符串转成 utf8mb4 字符集。
这就再次触发了我们上面说到的原则:对索引字段做函数操作,优化器会放弃走树搜索功能。
总结
三个例子,其实是在说同一件事儿,即:对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
第二个例子是隐式类型转换,第三个例子是隐式字符编码转换,它们都跟第一个例子一样,因为要求在索引字段上做函数操作而导致了全索引扫描。
MySQL 的优化器确实有“偷懒”的嫌疑,即使简单地把 where id+1=1000 改写成 where id=1000-1 就能够用上索引快速查找,也不会主动做这个语句重写。
因此,每次你的业务代码升级时,把可能出现的、新的 SQL 语句 explain 一下,是一个很好的习惯。