有一个业务是查询最新审核的 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 在左边的联合索引.
分析上面的 sql 执行的逻辑:
从联合索引里找到所有小于该审核时间的主键 id(因为该 sql 查询的是最新审核的, 假如之前已经审核了 100 万条数据, 则会在联合索引里取出对应的 100 条数据的主键 id)
回表, 查出 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.
该索引的弊端
如果 idx_audit_status 里扫描 5 行都是 status 是 ONLINE, 那么只需扫描 5 行;
如果 idx_audit_status 里扫描前 100 万行中, 只有 4 行 status 是 ONLINE, 则需要扫描 100 万零 1 行, 才能得到需要的 5 行记录.
索引需要扫描的行数不确定.
该索引的优势
因为在索引里面有 status 的值, 所以在筛选满足 status='ONLINE'行的时候, 就不用回表查询了.
疑惑
我猜的两个处理方式 (谁知道的, 帮解答下)
是根据 status,audit_time,id 把索引数据进行排序, 一次性排序, 然后找出前 5 行?
还是遍历一遍通过 status 过滤出符合要求的行, 然后再排序, 找出前 5 行?
不管怎样, 这里在回表的时候只有 5 行数据的查询了, 在 iops 上会大大减少.
改进思路 2
- ALTER TABLE `th_content` DROP INDEX `idx_audit_status`;
- ALTER TABLE `th_content` ADD INDEX `idx_status_audit` (`status`, `audit_time`);
实际统计
select count(*) from `th_content` where `audit_time`> 1541984478 and `status` = 'ONLINE';
只有 7 行.
因为业务属性是取最新的 5 条, 往往都是头部数据. 所以我们在使用 idx_status_audit 索引的时候, 最多只需要扫描 12 行就能取到了所有的数据.(为什么是最多是 12 行呢? 可以从上面的索引查询图中找到思路)
这样不管是排序还是回表都毫无压力.
来源: https://juejin.im/entry/5bf374756fb9a049bc4c43b7