起因
开发让帮忙优化一条SQL
原始SQL如下(为方便阅读,这里简化并脱敏)
SELECT
id,
participate_count,
goods_list
FROM
`test`
WHERE
max_pub_time > now() and
status = 1 and
max_end_time > now()
ORDER BY
participate_count DESC,
id DESC
LIMIT 100
数据库、表的情况:
- MySQL8.0.18版本
- 表引擎ENGINE=InnoDB
- 表数据80w
- 数据库实例正常运行无死锁等异常
- max_end_time上有普通索引
- status 上有普通索引
- max_pub_time 上有普通索引
- participate_count 上有普通索引
- id自增主键
目前该语句执行约8s
处理问题
1、因为数据库本身无异常,那么问题多半出在该SQL上
2、看执行计划(三个重要点)
type:index
key:idx_participate_count
Extra:Using where; Backward index scan
3、好像也没有大问题,用了索引,也用了索引排序,但依旧这么慢
4、尝试分段分析该SQL
5、感觉问题出现在order by + limit 上,于是先去掉order by部分
SELECT
id,
participate_count,
goods_list
FROM
`test`
WHERE
max_pub_time > now() and
status = 1 and
max_end_time > now()
LIMIT 100
6、查看执行计划
type:range
key:idx_max_pub_time
Extra:Using index condition; Using where
7、查询速度直接起飞9ms
8、问题确实出在order by 上?
9、单独查询满足max_pub_time 条件的数据大概700多条。
10、确定大概是优化器选择有误了。
11、改造SQL,指定使用索引(idx_max_pub_time)
SELECT
id,
participate_count,
goods_list
FROM
`test`
use index(idx_max_pub_time)
WHERE
max_pub_time > now() and
status = 1 and
max_end_time > now()
ORDER BY
participate_count DESC,
id DESC
LIMIT 100
12、执行速度可观85ms
13、查看执行计划
type:range
key:idx_max_pub_time
Extra:Using index condition; Using where; Using filesort
14、分析一下:在where的时候已经从80w数据中过滤出了700条数据,然后再过滤其他条件,最后排序。因为第一波已经过滤了绝大部分数据,所以后续的处理不管是在engine层还是在内存都没有问题。
总结
原始SQL优化器自动选择使用participate_count DESC条件上的索引,但其实max_end_time > now() 才是这条SQL过滤的大头。所以指定索引来解决该问题。
彩蛋
尝试过建立直方图+索引的方式,但是由于过滤字段更新频繁,加入直方图后效果并不好。