《MySQL实战45讲》——学习笔记18 “索引失效、函数/隐式转换“【建议收藏】

本文介绍几种MYSQL中索引失效的典型SQL用法，包括对索引字段使用函数、索引字段隐式类型转换（如字符串转数值类型，实质上也是使用CAST函数）、索引字段隐式字符编码转换（如utf8mb4字符集与utf8字符集的等值判断，实质上也是使用CONVERT函数）；

并且，有的时候你可能发现explain执行计划中使用了索引，但是SQL的性能依然很慢，这时就要观察下rows判断是否走了全表扫描；并不是说查询走了索引就一定快；

案例一：查询条件中对索引字段显示的使用函数

假设有一个交易系统，其中交易记录表tradelog包含交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段，其中交易时间和流水号字段都加了索引；这个表的建表语句如下：

mysql> 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语句可能会这么写：

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

由于t_modified字段上有索引，于是你就很放心地在生产库中执行了这条语句，但却发现执行了特别久，才返回了结果；DBA同事的解释可能是：如果对字段做了函数计算，就用不上索引了，这是MySQL的规定；

现在分析下原因，下面是这个t_modified索引的示意图，方框上面的数字就是month()函数对应的值；

如果你的SQL语句条件用的是where t_modified='2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到t_modified='2018-7-1’需要的结果；实际上，B+树提供的这个快速定位能力，来源于同一层兄弟节点的有序性；

但是，如果计算month()函数的话，你会发现这颗B+树的"有序性"被破坏了；例如当传入month(t_modified)=7的时候，在树的第一层就不知道该怎么办了——他不知道接下来往哪个方向找；也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，转而走全索引扫描（遍历整个索引树）；

需要注意的是，"放弃走树搜索功能"并不意味着优化器放弃使用这个索引；

在这个例子里，尽管优化器放弃了树搜索功能，但它遍历索引树获取t_modified字段，既可以选择遍历主键id索引，也可以选择遍历索引t_modified；优化器对比索引大小后发现，索引t_modified更小（覆盖索引），遍历这个索引比遍历主键索引来得更快；因此最终还是会选择索引t_modified；

使用explain命令查看一下这条SQL语句的执行结果：

key="t_modified"表示的是，使用了t_modified这个索引；因为测试表数据中插入了10万行数据，这里统计rows=100335，说明这条语句扫描了整个索引的所有值；Extra字段的Using index，表示的是使用了覆盖索引，因为仅需要全表扫描t_modified字段；

结论就是——由于在t_modified这个索引字段加了month()函数操作，导致了全索引扫描；

根据上述结论，可以在语句中不对索引字段做函数运算，而是使用范围查询，如下；

mysql> 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 tradelog where id+1=10000 这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行；所以，需要你在写SQL语句的时候，手动改写成 where id=10000-1 才可以；

案例二：对索引字段隐式的使用类型转换函数

先看下下面的语句，交易编号 tradeid 这个字段上本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描；

mysql> select * from tradelog where tradeid=110717;

实际上，tradeid 的字段类型是 varchar(32)，而输入的参数110717却是整型，所以这里一定需要做类型转换；

那么，现在这里就有两个问题：

（1）数据类型转换的规则是什么？
（2）为什么有数据类型转换，就需要走全索引扫描？

先来看第一个问题，尝试执行 select '10'>9的结果：结果是1，也就是说这里"将字符串转成了数值"，也就是执行到"tradeid=110717"时MySQL将索引字段tradeid转成了数值型做比较；（字符串比较大小是逐位从高位到低位逐个比较（按ascii码），"10"的第一位字符"1"的ascii比"9"小，所以如果是数值转字符串那么结果应该为0）

所以，上述的语句对于优化器来说，相当于：

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能；举个例子，反过来，假如主键id的类型是int，如果执行下面这个语句，是否会导致全表扫描呢？

select * from tradelog where id="83126";

答案是——会走索引； 当字符串和数字比较时会把字符串转化为数字，所以这条语句里的隐式转换不会应用到索引字段上，而是作用于where等号后面的变量"83126"上，所以可以走索引；

案例三：对索引字段隐式的使用字符编码转换

假设系统里还有另外一个表 trade_detail，用于记录交易的操作细节；这个表与上面的交易日志表 tradelog 通过交易流水号关联；

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这时候，如果要查询 tradelog 表中id=2的交易记录的所有操作步骤信息，SQL语句可以这么写：

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

看下这条语句的执行计划：

分析这个explain结果：

（1）第一行显示优化器会先在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引，rows=1表示只扫描一行；
（2）第二行key=NULL，表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描；

在这个执行计划里，是从tradelog表中取tradeid字段，再去trade_detail表里查询匹配字段；因此，我们把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段；接下来，我们看下这个explain结果表示的执行流程：

图中第3步不符合我们的预期；因为表trade_detail里tradeid字段上是有索引的，我们本来是希望通过使用tradeid索引能够快速定位到等值的行；但这里却走了全表扫描；

如果你去问DBA同学，他们可能会告诉你：因为这两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引；

utf8是utf8mb4的子集，utf8mb4在utf8支持的字符上做了扩展，所以当这两个类型的字符串在做比较的时候会做隐式的字符类型转换——MySQL内部的操作是先把utf8字符串转成utf8mb4字符集（如果utf8mb4转成utf8会损失精度），再做比较；

因此，在执行上面这个语句的时候，需要将被驱动数据表trade_detail里的索引字段traideid一个个地转换成utf8mb4编码，再跟驱动表的关联字段的值做比较；也就是说，实际上这个语句等同于下面这个写法：

 /*$L.tradeid.value 指tradelog表的tradeid字段值*/
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L.tradeid.value; 

CONVERT() 函数可以把输入的字符串转成utf8mb4字符集；这就再次触发了我们上面说到的原则：对被驱动表的索引字段做了函数操作，优化器放弃走树搜索功能；

作为对比验证，现在执行另外一个需求，“查找trade_detail表里id=4的操作，对应的操作者是谁”，再来看下这个语句和它的执行计划；

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

这个语句里trade_detail表成了驱动表，explain结果的第二行显示，这次的查询操作用上了被驱动表tradelog里的索引(tradeid)，扫描行数是1；跟我们上面的结论对上了，这里被驱动表的tradeid字段是utf8mb4，无需做字符类型转换，而是where=后面的值做了隐式字符转换，即：

/*$D.tradeid.value 指trade_detail 表的tradeid字段值*/
select operator from tradelog where traideid =CONVERT($D.tradeid.value USING utf8mb4);

这里的CONVERT函数是加在输入参数上的，这样就可以用上被驱动表的traideid索引；

理解了原理以后，就可以用来优化这条语句了：

（1）修改表结构，让字符集一致，都换成utf8mb4；

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

（2）但如果数据量比较大，或者业务上暂时不能做这个DDL的话，那就只能采用修改SQL语句的方法了；在这个场景下，tradeid字段从utf8mb4强转uft8不会异常，但是其他场景可能在转换时发生异常，需要注意；

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

补充案例：查询条件中的值的长度大于索引字段定义的长度

场景条件（表结构）如下：

mysql> CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

假设现在表里面，有100万行数据，其中有10万行数据的b的值是'1234567890'，假设现在查询语句是这么写的：

mysql> select * from table_a where b='1234567890abcd';

注意这里的查询条件值，'1234567890'是长度14位的字符串，而字段b定义的是varchar(10)，最理想的情况是，MySQL看到条件目标值的长度与索引字段b定义长度不相等，直接返回空行；要不就是，直接遍历索引树b，拿'1234567890abcd'与索引字段b匹配，快速判断出上并没有这个值，也很快就能返回空结果；

实际上，MySQL没有采用上面的方式；结果是——这条SQL语句执行的很慢，最终还是返回了空行；流程是这样的：

1. 在传给引擎执行的时候，做了字符截断；因为引擎里面这个行只定义了长度是10，所以只截了前10个字节，就是’1234567890’进去与索引字段b做匹配；

2. 索引树b中查到满足b='1234567890'条件的数据有10万行；

3. 依然需要做10万次回表；（仅查字段b尽管可以覆盖索引，但是毕竟这里对条件值"擅自"做了截断，因此最终server还是要"亲自"准确的判断一次）

4. 查出来的数据，到server层重新判断，b的值都不是'1234567890abcd'；

5. 返回结果是空；

通过explain可以看到，当where的值在索引字段长度内，执行计划的Extra则是'using index'，即覆盖索引，无需回表；当where的值大于索引字段长度，执行计划的Extra则是'Using where; Using index'，即使用了覆盖索引，但存储引擎返回结果后mysql server还会再次筛选；

小结

1. 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能；函数操作可以是显示的，也有隐式的，如索引字段的类型/字符集转换；

2. MySQL 的优化器确实有“偷懒”的嫌疑，即使简单地把 where id+1=1000 改写成 where id=1000-1 就能够用上索引快速查找，它也不会主动做这个语句重写；

3. explain显示用上了索引不一定就执行快，因为他可能还是需要全表扫描，只不过扫描的字段刚好被索引覆盖，因此选了索引而非走主键索引树；除了要关注key，还要关注扫描行数rows；

下篇文章：《MySQL实战45讲》——学习笔记19 “SQL查一行执行慢的排查、锁等待/一致性读“【建议收藏】

本章参考：18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？-极客时间