第十八讲笔记——为什么 SQL语句逻辑一样，但是性能差异巨大

MySQL 规定：如果对字段做了函数计算，就用不上索引。

案例一：条件字段函数操作

在一个表中有一个时间字段（t_modified），也在时间字段上创建了索引，现在使用如下的 SQL 语句：

select count(*) from tradelog where month(t_modified)=7;

很遗憾，这条语句使用不了时间字段上的索引，因为对字段使用了函数计算。

下面是索引示意图，方框上的数据就是 month() 函数对应的值。

可见，时间的顺序排列的，但是计算之后的值确实被打乱了。

实际上，B+ 树提供快速定位能力，来源于同一层兄弟节点的有序性。如果对索引字段做函数操作，有可能会破坏索引值的有序性，因此优化器放弃走树的搜索功能

但不是放弃使用这个索引。

在这个例子中，放弃了树的搜索功能，优化器还可以选择遍历主键索引，或者遍历时间字段索引。优化器在对比后，还是选择了小点的时间字段索引，这样会更快。

key=‘t_modified’ 表示使用了 t_modified 这个索引；在测试表中插入了 10 万行数据，rows=100335，表示扫描了整个索引的值；Extra 字段的 Using index，表示使用了覆盖索引。

如果想使用索引的快速定位能力，就改成基于字段本身的范围查询：

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');

不过优化器也有“偷懒”行为，即使是不改变有序性的函数，也不会考虑使用索引。比如：

select * from t where id + 1 = 10000

这个语句，加一不改变有序性，但是 MySQL 优化器还是不能用 id 快速定位到这一行。所以在写 SQL 时，要改成 where id = 10000 - 1 才行。

༼ つ ◕_◕ ༽つ那么定义的字段是整数，传入的参数类型是字符串的时候，可以用上索引吗？

答案：是可以的。

案例二：隐式类型转换

如果字段定义时是 varchar 类型，存放的是 varchar类型的编号，如果你输入参数是整型，那你要做类型转换，这时就不能使用 varchar 类型字段上的索引。

select * from t where number = 110707;

༼ つ ◕_◕ ༽つ那么类型转换的规则是什么？

这里有一个简单的方法，看 select '10' > 9 的结果：

MySQL 转换规则：在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

这时上面的 SQL 相当于：

select * from t where CAST(number AS signed int) = 110717

也就说还是上面的规则：对索引字段做函数操作，会放弃走树的搜索功能。

案例三：隐式字符编码转换

两个表做连表查询时，如果双方的关联字段是同一种类型（比如：varchar），但两个表不是同一种编码（一个是 utf8，一个是 utf8mb4），那么这时就要做字符编码的转换。

因为 utf8mb4 是 utf8 的超集（utf8mb4 包含 utf8），所以 MySQL 内部操作是将 utf8 转成 utf8mb8字符集。

注意，字符集不同只是条件之一，连接过程中要求被驱动表的索引字段上加上函数操作，是直接导致对被驱动表做全局扫描的原因。

༼ つ ◕_◕ ༽つ驱动表、被驱动表和关联字段是啥？

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; 

在这个 SQL 语句中，从 tradelog 表中取 tradeid 字段，在去 trade_detail 表里匹配查询字段。于是 tradelog 称为驱动表，trade_detail 称为被驱动表，tradeid 称为关联字段。

那我们怎么改，才能都用上索引呢？

• 把两张表的编码格式改为一致，这样就没有字符转换问题了。

• 那就修改 SQL 语句，将输入参数改为与被驱动表一致的编码格式

  select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
  

小结

这三个案例其实在说同一件事：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。（注：不是放弃使用这个索引）