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

爱被打了一巴掌 2022-04-15 05:55 153阅读 0赞

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

[原文链接][Link 1]

[Link 1]: http://click.aliyun.com/m/1000025591/