有一个业务是查询最新审核的5条数据
选择“id”,“标题” 从“th_content” “audit_time”& lt;1541984478 和“状态”=霸谙摺? ORDER BY audit_time DESC, DESC“id” 限制5;
查看当时的监控情况cpu使用率是超过了100%,<代码>显示processlist> 代码看到很多类似的查询都是处于<代码> 代码>创建排序索引的状态。
查看该表的结构
创建表“th_content”( “id”bigint(20)无符号NOT NULL AUTO_INCREMENT, “标题”varchar(500)字符集utf8 NOT NULL默认”评论的内容标题”, “内容”简单字符集utf8 NOT NULL评论”正文内容的, audit_time int(11)无符号NOT NULL默认“0”评论“审核时间”, “last_edit_time”时间戳NOT NULL违约CURRENT_TIMESTAMP alt="一个案例彻底弄懂如何正确使用mysql inndb联合索引"><强>改进思路1 强>
范围查找向来不太好使用好索引的,如果我们增加一个<代码> audit_time 代码>,<代码> 代码>状态的联合索引,会有哪些改进呢?
ALTER TABLE的th_content添加索引“idx_audit_status”(“audit_time”,“状态”);mysql>解释选择“id”,“标题”从“th_content”“audit_time”& lt;1541984478和“状态”=霸谙摺泵頰udit_time desc,“id”desc限制5; + - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + | | id select_type表| | |型possible_keys关键| | key_len | ref额外| | |行 + - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - + | 1 |简单| th_content | |范围idx_at_ft_pt_let, idx_audit_status | idx_audit_status零| | 4 | 209754 | |使用 + - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - +细节:因为<代码> audit_time> 代码是一个范围查找,所以第二列的索引用不上了,只能用到<代码> audit_time> 代码,所以<代码> key_len> 代码是4。而下面思路2中,还是这两个字段<代码> key_len> 代码则是5 .
还是分析下在添加了该索引之后的执行过程:
-
<李>从联合索引里找到小于该审核时间的<代码> audit_time> 代码最大的一行的联合索引李>
<李>然后依次往下找,因为<代码> & lt;audit_time> 代码是一个范围查找,而第二列索引的值是分散的,所以需要依次往前查找,匹配出满足条件(<代码> 代码>=霸谙摺弊刺?的索引行,直到取到第5行为止。李>
<李>回表查询需要的具体数据李>
在上面的示意图中,粉红色标识满足第一列索引要求的行,依次向前查询,本个叶子节点上筛选到了3条记录,然后需要继续向左,到前一个叶子节点继续查询。直到找到5条满足记录的行,最后回表。
<强>改进之处强>
因为在索引里面有<代码> 代码>状态的值,所以在筛选满足<代码> 代码>=霸谙摺弊刺械氖焙?就不用回表查询了。在回表的时候只有5行数据的查询了,在<代码> iops> 代码上会大大减少。
<强>该索引的弊端强>
如果<代码> idx_audit_status> 代码里扫描五行都是<代码> 代码>状态是<代码>网络> 代码,那么只需扫描5行;
如果<代码> idx_audit_status> 代码里扫描前100年万行中,只有4行<代码> 代码>状态是<代码>网络> 代码,则需要扫描万100年零1行,才能得到需要的5行记录。索引需要扫描的行数不确定。
<强>改进思路2 强>
ALTER TABLE的th_content下降指数“idx_audit_status”; ALTER TABLE的th_content添加索引“idx_status_audit”(“状态”,“audit_time”);