赵明中的数据库分享

起因

开发让帮忙优化一条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过滤的大头。所以指定索引来解决该问题。

彩蛋

尝试过建立直方图+索引的方式，但是由于过滤字段更新频繁，加入直方图后效果并不好。