记一次order by 索引导致sql慢问题

 

1.故事背景

使用mysqldumpslow监控到一个列表慢sql,EXPLAIN sql 显示Type为index，key为排序字段索引，看解释结果应该不会慢。

2.数据交代

• 表结构及全表数据（总数据32w）

CREATE TABLE `terminal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `term_sn` varchar(36) NOT NULL,
  `modifydate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `term_sn` (`term_sn`),
  KEY `modifydate_id` (`modifydate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.慢sql分析

• sql执行时间（31s）

• sql解释 -因为term_sn使用了 like %（%在左边）  ，所以用不上term_sn索引，Mysql就会自动选择order by字段的索引（modifydate_id索引）

而当我们强制不使用modifydate_id索引时，查询速度为1.4s，两者查询速度比为31.4/1.4=22.4（倍）

解析不使用索引的sql

现象：使用filesort比不使用filesort 效率更高

4.原理分析

• 使用order by 索引的sql分析

select * from terminal where term_sn like '%0820197670%' order by modifydate desc limit 15;

索引用于ORDER BY子句时，会直接遍历该索引的叶子节点链表（B+树索引详情见文章底部）。执行流程如下：

1.从modifydate索引的第一个叶子节点出发，按顺序扫描所有叶子节点获取真实的行数据（具体操作见第二步）

2.根据每个叶子节点记录的主键id去主键索引(聚簇索引))找到真实的行数据，判断行数据是否满足WHERE子句的term_sn条件，若满足，则取出并返回

第一步依次顺序获取索引树的叶子节点，这一步很快，但是第二步，由于rowid是根据modifydate进行排序的，第二次去查找真实的行数据，会按照rowid乱序去读取行记录，这些行数据在磁盘的存储是分散的，每读一行都会产生寻址时延（磁臂移动到指定磁道）+旋转时延（磁盘旋转到指定扇区），因此使用了order by 索引的情况下会很慢，这个本质就是随机I/O。

注：并且此时查询效率跟满足WHERE子句的term_sn条件的数据多少/满足条件数据的修改时间有关。

如果模糊搜索条件为term_sn like '%0820%'（满足该条件的数据特别多），上述sql查询也会很快， 因为limit和order by 子句或者group by子句联合使用，mysql都对limit操作的查询实行了懒惰策略，指要查询的结果达到了length，就不再据需往下操作了，而我们上述的sql能查的数据小于15条，则会遍历整个表。

如果我们需要搜索的term_sn like '%0820197670%' 满足的这条记录修改时间靠近当前时间，上述sql查询也会变快，因为我们使用modifydate倒序，能够更快查到数据

• 禁止order by 索引的sql

select * from terminal ignore index(modifydate_id) where term_sn like '%0820197670%'
order by modifydate desc limit 15;

禁止使用order by 索引的sql时，先扫表筛选出符合条件的数据，再将筛选结果根据modifydate排序 。执行流程如下

1.扫描全表筛选出满足WHERE子句的term_sn条件的所有数据行,生成一张临时表放入排序缓冲区

2.对临时表缓冲区里的数据进行排序

第一步虽然进行了全表扫描，但是这一步遍历使用的是顺序IO ,相对与上面的随机IO会快很多。而且因为过滤后的临时表数据很少，使用filesort排序也会很快。

备注：

B+树索引：InnoDB存储引擎以B+树作为索引的底层实现，B+树的叶子节点存储着所有数据页而内部节点不存放数据信息，并且所有叶子节点形成一个(双向)链表。
举个例子，假设userinfo表的userid字段上有主键索引，且userid目前的范围在1001~1006之间，则userid的索引B+树如下：(这里只是为了举例，下图忽略了InnoDB数据页默认大小16KB、双向链表，并且假设B+树度数为3、userid顺序插入)

 

5.解决方案

1.去除modifydate索引

2.根据是否存在搜索条件判断是否禁止使用modifydate索引

 

参考资料

https://segmentfault.com/a/1190000015987895