从一个实例分析order by 优化

环境：

MySQL5.7.30

简介：

生产环境一张读压力很大的表，表数据量在3000万左右，该表主要是一条很简单的sql在查，因时入慢日志时间为1s，初期该sql并未进入到慢日志，一次引流导致出现了大量sql未走索引，活跃会话数激增，CPU瞬间打满的情况，由此引发了对优化器在索引选择上的一些思考。

建表语句如下：

因为生产环境，所以这里匿去表名，只分析原因，部分地方用test1代替：

CREATE TABLE `test1` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`relation_code` varchar(64) DEFAULT NULL,

`left_user_id` bigint(20) DEFAULT NULL,

`right_user_id` bigint(20) DEFAULT NULL,

`top_level` tinyint(4) NOT NULL ,

`relation_type` tinyint(4) NOT NULL DEFAULT '1' ,

`update_time` timestamp NULL DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uni_relation_code` (`relation_code`),

KEY `idx_left_user_id` (`left_user_id`),

KEY `idx_right_user_id` (`right_user_id`),

KEY `idx_toplevel_leftuserid_rightuserid` (`top_level`,`left_user_id`,`right_user_id`)

) ENGINE=InnoDB ;

查询接口对应的sql:

SELECT `top_level`

FROM `test1`

WHERE left_user_id = ?

OR right_user_id = ?

ORDER BY `top_level` DESC

LIMIT ? 

从表结构可以看出，该sql就是一条很简单的常量 + order by ，对应可用的索引可以用：

1. index_merge(idx_left_user_id,idx_right_user_id)

但使用该方式在合并了索引过滤出来的数据后，会产生一次file sort，进行一次回表操作

2. idx_toplevel_leftuserid_rightuserid

该索引为一条覆盖索引，从order by 的字段上开始过滤符合条件的数据，达到limit的条数后直接返回结果，从分析上看，所有操作在索引中就可以完成，不需要回表操作，似乎比较符合。

从explain的结果来看：

 实际上优化器选择的是index_merge。

先看一下使用两条索引时对应的查询耗时：

1. 使用 index_merge 时：

 2. 使用 idx_toplevel_leftuserid_rightuserid 时：

可以看到，使用覆盖索引比使用index_merge快10倍以上，至于优化器为什么选择的是index_merge，而不是覆盖索引，下一篇文章咱们再分析，这里先分析为什么引流的时候会导致部分查询走全表扫描，导致数据库异常。

分析问题：

order by/ group by {字段} + limit {条数} 这类的查询优化器会走 {字段} 为前缀的索引，然后扫描where条件的行，凑足limit的行数后直接返回，不再继续往下扫描，所以这里优化器理论上应该选择覆盖索引（idx_toplevel_leftuserid_rightuserid），实际上从执行耗时来看，也确实是这样，但实际上该查询方式会导致查询效率不稳定，可能导致有的sql执行很快，有的sql执行很慢，甚至是全表扫描的情况发生。

解决方式：

使用hint，加上强制索引 idx_toplevel_leftuserid_rightuserid 可以避免全表扫描的情况发生。