本文转自 博客
有一个业务是查询最新审核的5条数据
SELECT`id`,`title`
FROM`th_content`
WHERE`audit_time`<1541984478
AND`status`='ONLINE'
ORDER BY`audit_time`DESC,`id`DESC
LIMIT5;
查看当时的监控情况 cpu 使用率是超过了100%,show processlist看到很多类似的查询都是处于create sort index的状态。
查看该表的结构
CREATE TABLE`th_content`(
`id`bigint(20)unsignedNOT 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)unsignedNOT 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_ft_pt_let`(`audit_time`,`last_edit_time`)
)ENGINE=InnoDBAUTO_INCREMENT=19956DEFAULT CHARSET=utf8mb4;
索引有一个 audit_time 在左边的联合索引。
分析上面的sql执行的逻辑:
从联合索引里找到所有小于该审核时间的主键id(因为该sql查询的是最新审核的,假如之前已经审核了100万条数据,则会在联合索引里取出对应的100条数据的主键id)
回表,查出100万行记录,然后逐个扫描,筛选出status='ONLINE'的行记录
最后对查询的结果进行排序(假如有50万行都是ONLINE,则继续对这50万行进行排序)
最后因为数据量很大,虽然只取5行,但是按照我们刚刚举的极端例子,实际查询了100行数据,而且最后还在进行了50行的内存排序。
所以是非常低效的。
画了一个示意图,说明第一步的查询过程,粉红色部分标识最后需要回表查询的数据行。
改进思路1
范围查找向来不太好使用好索引的,如果我们增加一个 audit_time , status 的联合索引,会有哪些改进呢?
ALTER TABLE`th_content`ADD INDEX`idx_audit_status`(`audit_time`,`status`);mysql>explainselect`id`,`title`from`th_content`where`audit_time`<1541984478and`status`='ONLINE'orderby`audit_time`desc,`id`desc limit5;
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
|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|Usingwhere|
+----+-------------+------------+-------+------------------------------------------+------------------+---------+------+--------+-------------+
因为audit_time是一个范围查找,所以第二列的索引用不上了,只能用到audit_time,所以key_len是4。
该索引的弊端
如果idx_audit_status里扫描5行都是status是ONLINE,那么只需扫描5行;
如果idx_audit_status里扫描前100万行中,只有4行status是ONLINE,则需要扫描100万零1行,才能得到需要的5行记录。
索引需要扫描的行数不确定。
疑惑
我猜到的两个处理方式
根据status,audit_time,id把索引数据进行排序,一次性排序,然后找出前5行;
遍历一遍通过status过滤出符合要求的行,然后再排序,找出前5行。
不管怎样,这里在回表的时候只有5行数据的查询了,在iops上会大大减少。
改进思路 2ALTER TABLE`th_content`DROP INDEX`idx_audit_status`;
ALTER TABLE`th_content`ADD INDEX`idx_status_audit`(`status`,`audit_time`);
实际统计
selectcount(*)from`th_content`where`audit_time`>1541984478and`status`='ONLINE';
只有7行。
因为业务属性是取最新的5条,往往都是头部数据。所以我们在使用idx_status_audit索引的时候,只需要扫描12行就能取到了所有的数据。
这样不管是排序还是回表都毫无压力。