一个慢查询引发的联合索引的思考

上周开始我司完善了 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 条件中仅仅包含oidprogress字段,因此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 中索引的一些特点, 比如:

  1. 主键索引和普通索引的区别是什么?索引中叶子节点的数据内容有什么区别?
  2. 为什么查询尽量走主键索引?
  3. 为什么曾经说MyISAM查询快,Innodb写入快?现在呢?
  4. 联合索引的非叶子节点保存的内容是什么?一个(a,b,c)的联合索引会被什么样的查询条件使用到?

根据下面这个图,其实这些都有答案。

Comments

comments powered by Disqus