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

周梦康 · 掘金 · · 2018-11-20 02:41

有一个业务是查询最新审核的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行呢？可以从上面的索引查询图中找到思路）

这样不管是排序还是回表都毫无压力。

原文地址：访问原文地址
快照地址：访问文章快照

分享到微博