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:44 PM
上周开始我司完善了 mysql 慢查询的报警机制,然后很快陆陆续续的一些业务的慢查询报警开始出现了.其中有一个慢查询是对联合索引使用不当引起的,解决这个问题的过程中也让我重新认识了联合索引的特点.
具体有问题的 sql 如下(有精简):
SELECT id,type FROM my_table WHERE oid=? AND progress>=? AND progress<=? ORDER BY progress ASC, id DESC
最初的 explain 输出如下:
notion image
可以看到,此次查询虽然使用了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 一下试试:
notion image
很遗憾我的设想落空了,范围查询并不能完整的利用到联合索引,因为在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 结果如下:
notion image
扫描的行数一下减少到 1% 左右!很明显这样做就能充分利用到这个联合索引了.
既然问题解决了,那么这里可以梳理一下 mysql 中索引的一些特点, 比如:
  1. 主键索引和普通索引的区别是什么?索引中叶子节点的数据内容有什么区别?
  1. 为什么查询尽量走主键索引?
  1. 为什么曾经说MyISAM查询快,Innodb写入快?现在呢?
  1. 联合索引的非叶子节点保存的内容是什么?一个(a,b,c)的联合索引会被什么样的查询条件使用到?
根据下面这个图,其实这些都有答案。
notion image
使用 singleflight 代替传统的并发锁go 语言中哪种字符串拼接的方式最高效?

杂鱼
杂鱼
菜鸟程序员