一个案例彻底弄懂如何正确使用 mysql inndb 联合索引

有一个业务是查询最新审核的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万行数据库的内存排序。

原文链接