共计 2833 个字符，预计需要花费 8 分钟才能阅读完成。
有一个业务是查询最新审核的 5 条数据
SELECT `id`, `title`
FROM `th_content`
WHERE `audit_time` < 1541984478
AND `status` = ‘ONLINE’
ORDER BY `audit_time` DESC, `id` DESC
LIMIT 5;
查看当时的监控情况 cpu 使用率是超过了 100%，show processlist 看到很多类似的查询都是处于 create sort index 的状态。
查看该表的结构
CREATE TABLE `th_content` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT ” COMMENT ‘ 内容标题 ’,
`content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT ‘ 正文内容 ’,
`audit_time` int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘ 审核时间 ’,
`last_edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘ 最近编辑时间 ’,
`status` enum(‘CREATED’,’CHECKING’,’IGNORED’,’ONLINE’,’OFFLINE’) CHARACTER SET utf8 NOT NULL DEFAULT ‘CREATED’ COMMENT ‘ 资讯状态 ’,
PRIMARY KEY (`id`),
KEY `idx_at_let` (`audit_time`,`last_edit_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
索引有一个 audit_time 在左边的联合索引，没有关于 status 的索引。
分析上面的 sql 执行的逻辑：
从联合索引里找到所有小于该审核时间的主键 id（假如在该时间戳之前已经审核了 100 万条数据，则会在联合索引里取出对应的 100 万条数据的主键 id）
对这 100 万个 id 进行排序（为的是在下面一步回表操作中优化 I/O 操作，因为很多挨得近的主键可能一次磁盘 I/O 就都取到了）
回表，查出 100 万行记录，然后逐个扫描，筛选出 status=’ONLINE’ 的行记录
最后对查询的结果进行排序（假如有 50 万行都是 ONLINE，则继续对这 50 万行进行排序）
最后因为数据量很大，虽然只取 5 行，但是按照我们刚刚举的极端例子，实际查询了 100 万行数据，而且最后还在内存中进行了 50 万行数据库的内存排序。
所以是非常低效的。
画了一个示意图，说明第一步的查询过程，粉红色部分表示最后需要回表查询的数据行。图中我按照索引存储规律来 YY 伪造填充了一些数据，如有不对请留言指出。希望通过这张图大家能够看到联合索引存储的方式和索引查询的方式
改进思路 1
范围查找向来不太好使用好索引的，如果我们增加一个 audit_time, status 的联合索引，会有哪些改进呢？
ALTER TABLE `th_content` ADD INDEX `idx_audit_status` (`audit_time`, `status`);
mysql> explain select `id`, `title` from `th_content` where `audit_time` < 1541984478 and `status` = ‘ONLINE’ order by `audit_time` desc, `id` desc limit 5;
+—-+————-+————+——-+——————————————+——————+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——-+——————————————+——————+———+——+——–+————-+
| 1 | SIMPLE | th_content | range | idx_at_ft_pt_let,idx_audit_status | idx_audit_status | 4 | NULL | 209754 | Using where |
+—-+————-+————+——-+——————————————+——————+———+——+——–+————-+
细节：因为 audit_time 是一个范围查找，所以第二列的索引用不上了，只能用到 audit_time，所以 key_len 是 4。而下面思路 2 中，还是这两个字段 key_len 则是 5。
还是分析下在添加了该索引之后的执行过程：
从联合索引里找到小于该审核时间的 audit_time 最大的一行的联合索引
然后依次往下找，因为 < audit_time 是一个范围查找，而第二列索引的值是分散的。所以需要依次往前查找，匹配出满足条件（status=’ONLINE’）的索引行，直到取到第 5 行为止。
回表查询需要的具体数据
在上面的示意图中，粉红色标识满足第一列索引要求的行，依次向前查询，本个叶子节点上筛选到了 3 条记录，然后需要继续向左，到前一个叶子节点继续查询。直到找到 5 条满足记录的行，最后回表。
改进之处
因为在索引里面有 status 的值，所以在筛选满足 status=’ONLINE’ 行的时候，就不用回表查询了。在回表的时候只有 5 行数据的查询了，在 iops 上会大大减少。
该索引的弊端
如果 idx_audit_status 里扫描 5 行都是 status 是 ONLINE，那么只需扫描 5 行；如果 idx_audit_status 里扫描前 100 万行中，只有 4 行 status 是 ONLINE，则需要扫描 100 万零 1 行，才能得到需要的 5 行记录。索引需要扫描的行数不确定。
改进思路 2
ALTER TABLE `th_content` DROP INDEX `idx_audit_status`;
ALTER TABLE `th_content` ADD INDEX `idx_status_audit` (`status`, `audit_time`);
这样不管是排序还是回表都毫无压力啦。
本文作者：周梦康阅读原文
本文为云栖社区原创内容，未经允许不得转载。
