type
Post
status
Published
date
May 24, 2017
slug
slow-query-with-clustered-index
summary
上周开始我司完善了 mysql 慢查询的报警机制,然后很快陆陆续续的一些业务的慢查询报警开始出现了.其中有一个慢查询是对联合索引使用不当引起的,解决这个问题的过程中也让我重新认识了联合索引的特点.
tags
MySQL
category
技术分享
icon
password
Property
Jan 1, 2023 02:42 PM
上周开始我司完善了 mysql 慢查询的报警机制,然后很快陆陆续续的一些业务的慢查询报警开始出现了.其中有一个慢查询是对联合索引使用不当引起的,解决这个问题的过程中也让我重新认识了联合索引的特点.
具体有问题的 sql 如下(有精简):
SELECT id,type FROM my_table WHERE oid=? AND progress>=? AND progress<=? ORDER BY progress ASC, id DESC
最初的 explain 输出如下:
可以看到,此次查询虽然使用了
oid_state_progress索引, 但是依然扫描了90W+行的数据.而且,由于 where 条件中仅仅包含oid和progress字段,因此oid_state_progress这个索引仅仅用到了oid字段,效率不高.找到问题了就好说,决绝办法无非是一加索引(几千万数据不可能),二改 where 条件.为了更好的利用
oid_state_progress索引,考虑到state字段代表了这条记录的状态值(都是正数),我把 sql 语句改成这样:SELECT id,type FROM my_table WHERE oid=? AND state>=0 AND progress>=? AND progress<=? ORDER BY progress ASC, id DESC"
explain 一下试试:
很遗憾我的设想落空了,范围查询并不能完整的利用到联合索引,因为在
B+树中,联合索引只有第一个字段是有序的,后面的字段的排列顺序完全是根据前一个字段排列的(可以理解成 sql 中的 order by oid,state,progress),其实就是无序的.因此如果我state字段想利用到联合索引,只能使用等值判断(=,IN).万幸的是state的值是有限的,所以第二次修改,sql 语句变成这样:SELECT id,type FROM my_table WHERE oid=? AND state IN (1,2,3,4,5,6,7,8,9,10,11,12) AND progress>=? AND progress<=? ORDER BY progress ASC, id DESC
explain 结果如下:
扫描的行数一下减少到 1% 左右!很明显这样做就能充分利用到这个联合索引了.
既然问题解决了，那么这里可以梳理一下 mysql 中索引的一些特点， 比如：
- 主键索引和普通索引的区别是什么？索引中叶子节点的数据内容有什么区别？
- 为什么查询尽量走主键索引？
- 为什么曾经说
MyISAM查询快，Innodb写入快？现在呢？
- 联合索引的非叶子节点保存的内容是什么？一个（a,b,c）的联合索引会被什么样的查询条件使用到？
根据下面这个图，其实这些都有答案。
