一、起因
今天在看服务质量看板时,发现我的一个服务质量明显偏低,可用性不到三个9,根据监控面板找到一个分页查询数据的接口频繁失败,故此找一下原因
二、排查过程
通过查询错误日志,发现该接口select count(*) from table where ...频繁超时,怀疑是没走索引,拿出当时的sql做explain解释,发现确实没走,伪sql如下:
-- 注:bus_id和create_time都单独建了索引
select count(*)
from table_a
where bus_id > 0 -- 一个业务id,bigint类型(数据库有极少部分是空值)
and create_time >= '2021-05-01 00:00:00'
and create_time < '2021-05-31 23:59:59'
这里为了说明为什么没走索引,先描述一下我对sql进行尝试的几种现象,从现象分析问题:
- 原sql进行了全表扫描;
- 删除bus_id>0,explain解释走了索引,type为range;
- 删除create_time条件,发现没走索引;
- 两个条件都保留,create_time 时间范围缩小到一天以内,explain发现走了索引;
三、分析
针对以上:2-4种情况,进行逐步分析
情况2:
此种情况属于正常的求总数sql,不做分析;
情况3:
删除了create_time后,发现bus_id>0没有走索引
首先明确了一个这样的知识点:
- 如果使用了 not in , not exists , (<> 不等于 !=) 这些不走索引;
- < 、 > 、 <= 、>= 这个根据实际查询数据来判断,如果全盘扫描速度比索引速度要快则不走索引 ;
因为手头有innoDB存储引擎一书,在5.6.4章节找到了对于第二条的解释:
以大于号为例,bus_id>0扫描到的数据太多了,导致优化器自动选择了不走索引。其他<、<=、>=结果一样。
情况4:
在知道了情况3不走索引后,试着将create_time时间范围缩小到一天内,结果惊奇的发现竟然走了索引,针对这种情况,询问了公司的DBA
这里其实还是优化器导致的,时间范围过长,优化器认为范围查找和bus_id>0差别不大,结果都不走索引了。
假如时间范围比较短,则会走时间范围索引;
当然优化器内部具体的实现逻辑肯定很复杂,太具体的还需要继续研究;
可以使用force index强制走create_time 索引;
-- 注:bus_id和create_time都单独建了索引
select count(*)
from table_a
FORCE INDEX(idx_create_time)
where bus_id > 0 -- 一个业务id,bigint类型(数据库有极少部分是空值)
and create_time >= '2021-05-01 00:00:00'
and create_time < '2021-05-31 23:59:59'