mysql index 查找方法_通过一个 create sort index 案例彻底弄懂 mysql inndb 索引的优化方法...

本文转自 博客

有一个业务是查询最新审核的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行就能取到了所有的数据。

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