一次线上慢SQL调优分享

一个sql查询设置了联合索引，但是在查询的时候没有走联合索引，所以导致查询速度非常慢。因为MySQL查询优化器会自己判断这个查询所需要的代价，如果代价太大则不走联合索引，导致联合索引失效

用户的所有提交，都写入一张提交表，提交列表也是该表查询，表结构核心大致如下：

 CREATE TABLE `submit_topic` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '提交ID',
   `topicset_id` int(11) NOT NULL COMMENT '题目集',
   `user_id` int(11) NOT NULL COMMENT '用户编号',
     /xxx
     //还包括 判题情况，分数等等
  
   PRIMARY KEY (`id`),
   KEY `topicsetIndex` (`topicset_id`,`user_id`) USING BTREE COMMENT '题目集用户索引'
 ) ENGINE=InnoDB AUTO_INCREMENT=57157 DEFAULT CHARSET=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排序
 ORDER BY st.id desc
 limit 20;

自从上线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排序
 ORDER BY st.id desc
 limit 20;

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很小，优化器认为即使全表过滤也很快，然而事与愿违这几乎等于全表扫描！！！