MySQL深入-索引失效案例分析

相关文章：

MySQL高级-索引使用 (opens new window)

有时候一条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;

对于这条SQL语句：

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

由于我们在t_modified字段上有索引，我们简单认为该条SQL语句已经能很快执行，但是却发现执行了很长时间才返回结果。

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

那为什么条件是where t_modified='2018-7-1'的时候就可以用上索引，而month(t_modified)=7就用不上索引？

• 如果是where t_modified='2018-7-1'：由于B+树的快速定位能力，这个索引会按照上图绿色箭头的方法，快速定位到2018-7-1，这是因为同一层兄弟节点是有序的。

• 如果是month(t_modified)=7：相当于第一层我传入的是一个7，而这个7月MySQL并不知道是那一年的7月。

对索引字段做函数操作，可能会破环索引值的有序性，因此优化器决定放弃走树搜索功能。

当然在这个例子中，MySQL也不是放弃使用这个索引。优化器放弃了树搜索功能，但是可以选择主键索引遍历或者t_modified索引遍历，比较两者索引大小后，索引t_modified更小，因此最终还是会选择遍历t_modified。

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

隐式类型转换

select * from tradelog where tradeid=110717;

交易编号tradeid上是存在索引的，但是通过explain命令分析出来这条SQL语句是没有走索引的。由于tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。

数据类型转换规则

select "10" > 9

• 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1
• 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0

经过验证，结果为1，说明MySQL在数字和字符串比较的时候，是把字符串转为数字。

对于上面的SQL语句由于传入的数字，而本身字段定义为字符串，就需要把字符串转换为数字。

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

也就是说，这条语句就和上面说到的相同：对索引字段做函数操作，优化器会放弃走树搜索功能。

如果id类型是int，执行如下语句：

select * from tradelog where id="83126";

这个语句就需要把条件的字符串转换为数字，不会触发索引字段函数操作，所以会走索引。

隐私字符编码转换

现在我们来进行一个连表操作，但是两个表的字符编码不相同。

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;

这时候我们来查询id=2的交易所有操作步骤信息：

SELECT
d.* 
FROM
	tradelog l,
	trade_detail d 
WHERE
	d.tradeid = l.tradeid 
	AND l.id = 2;

下面来分析这条SQL语句：

EXPLAIN SELECT
d.* 
FROM
	tradelog l,
	trade_detail d 
WHERE
	d.tradeid = l.tradeid 
	AND l.id = 2;

• 第一行显示优化器会先在交易记录表 tradelog 上查到 id=2 的行，这个步骤用上了主键索引，rows=1 表示只扫描一行；

• 第二行 key=NULL，表示没有用上交易详情表 trade_detail 上的 tradeid 索引，进行了全表扫描。

执行过程如下：

1. 根据id在tradelog表找到L2这一行。
2. 从L2这一行中取出tradeid字段的数值。
3. 根据取出tradeid的数值到trade-detail表中找到匹配的行，由于没有使用的索引，所以是遍历主键索引的方式，一行一行进行判断。

但是我们本来希望在trade-detail中使用 tradeid 索引能够快速定位到等值的行。

字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。由于我们在trade-detail中输入的是utf-8的字符串，需要进行转换成utf8mb4字符集，这就触发了原则：对索引字段做函数操作，优化器会放弃走树搜索功能。

也就是字符串不同只是外在，内在还是在于我们对索引字段进行了函数操作。

下面我们把两表操作反过来一下：

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

由于现在驱动表变成了trade_detail，被驱动表是tradelog，由于第二次传给tradelog是uft8mb4的字符集，所以是将=之后的进行转换，就不涉及到对索引字段进行函数操作。

小结

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。 无论是直接进行操作，还是隐式类型转换，或者隐式字符编码转换，都是在索引字段做函数操作导致了全索引扫描。

参考

MySQL 实战 45 讲-极客时间 (opens new window)