一周前，客户反馈做题页面经常卡顿，加载慢；我们监控比较少，所以根据直觉去 MySQL 慢查询日志一看，果然是一条慢 SQL。废话不多，开整！！！
业务背景
一个在线做题的代码评测系统（类似牛客那种），每一次测试/考试（业务里叫 题目集），可以查询本次测试/考试的最近提交列表，如下图
用户的所有提交，都写入一张提交表，提交列表也是该表查询，表结构核心大致如下：
CREATE TABLE`submit_topic` (
`id`bigint(20) NOTNULL AUTO_INCREMENT COMMENT'提交ID',
`topicset_id`int(11) NOTNULLCOMMENT'题目集',
`user_id`int(11) NOTNULLCOMMENT'用户编号',
/xxx
//还包括 判题情况，分数等等
PRIMARY KEY (`id`),
KEY`topicsetIndex` (`topicset_id`,`user_id`) USING BTREE COMMENT'题目集用户索引'
) ENGINE=InnoDB AUTO_INCREMENT=57157DEFAULTCHARSET=utf8mb4;
SQL 的样子
注意了，以下是重点！！！！！
因为可以查询题目集 指定用户的提交列表（默认是查所有用户），所以我还加了一个联合索引，
KEY `topicsetIndex` (`topicset_id`,`user_id`) USING BTREE COMMENT '题目集用户索引'
由于提交列表涉及到游标分页，所以默认查询最近提交列表 SQL 大致是
SELECT * FROM submit_topic as st
//指定题目集ID
where topicset_id = 34344
//根据主键ID游标分页查询
and st.id > 12
//因为ID递增，直接用ID排序
ORDERBY st.id desc
limit20;
自从上线SQL就这样，查询效率也可观。随便一个题目集一查 ，使用 explain 如下：
正常情况 100 毫秒内解决，这里要提一嘴，Using filesort 是表示用到了排序，是文件还是内存排序，要看数据量
说好的慢 SQL 呢？
继续定位，发现只有考试那几场题目集响应慢。于是将 topicset_id 换一个数作为查询条件，SQL 还是之前的
explain SELECT * FROM submit_topic as st
-- //换了一个题目集ID
where topicset_id = 42
-- //根据主键ID游标分页查询
and st.id > 123443
-- //因为ID递增，直接用ID排序
ORDERBY st.idr desc
limit20;
explain 结果一看吓死人：
扫描行数达到 7 万多，另外几个 直接扫了几十万 😫😫😫😫，跑了 5 秒才出结果。
原因分析
Extra 使用了
Using where，索引走的是主键ID。
所以 SQL 会先把 id > 123443 的都扫出来，然后利用索引自动排序，然后返回 Server 过滤出 topicset_id=42 的记录， 好家伙这相当于全表扫描了啊！！！
正常情况是会走 topicset_id 这个索引的呀（即使用到了排序）
KEY `topicsetIndex` (`topicset_id`,`user_id`) USING BTREE COMMENT '题目集用户索引'
其实这个索引有个坑，就是 topicsset_id 后面多了 user_id，所以这个联合索引结合主键索引，类似于一个联合索引的效果，如下：
但是我们的 SQL 查询条件，没有用到 user_id，这就导致主键 ID 索引不可用，所以 后面的排序 主键是用不到的。
如果一个 topicset_id 的提交数据太多，oder by id 势必造成大量文件排序，这时 MySQL 查询优化器认为排序代价太大，我干脆使用主键索引就避免排序，但而 ID 只有大于，使得扫描行数巨多，更要命的是要在如此多的数据里，Server 层过滤出 topicset_id = 42 的记录。其次，由于 Limit 很小，优化器认为即使全表过滤也很快，然而事与愿违这几乎等于全表扫描！！！
问题解决
直接单独对 topicset_id 设一个索引，根据 MySQL 索引下堆的原理，先通过 topicset_id 查询的主键是有序的，oder by 不需要排序了。
最终 SQL 如下：
explain SELECT * FROM submit_topic as st FORCEindex(topicsetId)
-- //指定题目集ID
where topicset_id = 42
-- //根据主键ID游标分页查询
and st.id > 3922
-- //因为ID递增，直接用ID排序
ORDERBY st.id desc
limit20;
Using index Condition 表示索引下堆，因为 topicset_id 选出来的 id 是有序的，我们直接在存储引擎层过滤掉了数据。
效果还是杠杆的。
来源：https://juejin.cn/post/7332752948417052687
作者：终南山人
