本文正在参加「技术专题19期 漫谈数据库技术」活动
一、问题背景
task_apply表有23个字段，现有数据量高达30,257,522条。
在task_apply表中，存在一个索引 KEY enterprise_id_and_provider_id (enterprise_id, provider_id) USING BTREE，线上偶发执行一个SQL慢的问题，SQL如下所示：
SELECT * FROM task_apply WHERE enterprise_id = 1619430880026857 ORDER BY apply_id DESC LIMIT 0,10;
执行这个sql用时长长长长长长长长长长长长长长长长达20多秒~30多秒 。
按照常理来说这个SQL应该很快才对，因为有enterprise_id开头的聚合索引，并且排序也是根据主键，实在想不到慢的理由，究竟是为何这个SQL这么慢？咱们一起分析。
二、问题分析
1、既然慢了，那么我们先来看一眼执行计划：
从执行计划上可以初步得出结论，极有可能是优化器产生了错误的决策，使用了回表扫描（PRIMARY + Using where 的决策）的策略导致查询变得很慢。
2、强制走索引（enterprise_id_and_provider_id），让执行计划符合我们的预期，SQL如下：
select * from task_apply force index(enterprise_id_and_provider_id) where enterprise_id = 1619430880026857 order by apply_id desc limit 0,10；
打开执行计划，如下图所示：
看执行计划确实也符合我们预期了，但是Extra列的提示信息（Using index condition，Using filesort），这是出乎我的意料的，我们接下来分析这个，先执行一下看看耗时是否达到了我们的预期，如下所示：
看执行耗时，确实得到了很明显的速度提升，不过仍有疑问，为什么执行器不选择这个这么优的执行计划呢，Extra列的提示信息（Using index condition，Using filesort）又是怎么一回事。
疑问分析如下文。
3、把这个SQL中的 * 换成 apply_id ，再次查看执行计划，如下所示：
看Extra的信息，恍然大悟，因为这个索引是enterprise_id和provider_id的联合索引，所以如果只是根据enterprise_id作为条件查的话，还要order by apply_id，不能保证顺序扫描就出来结果，所以需要根据where条件中的enterprise_id对 enterprise_id_and_provider_id 的索引进行扫描筛选，找到对应的apply_ids的集合，然后再进行file_sort。
根据这个结论，分析问题SQL（SELECT * FROM task_apply WHERE enterprise_id = 1619430880026857 ORDER BY apply_id DESC LIMIT 0,10;）的Extra列信息（Using index condition，Using filesort），可以发现 Using index condition 使用到了索引下推（感兴趣的百度关键字），相当于上面的Using where + Using index，和使用了Using filesort，继而根据filesort的结果进行回表查询，拿到数据行的所有信息。
4、结论
优化器会认为这么一顿操作猛如虎的代价，数据量这么小，还不如直接扫表，又存在order by apply_id，所以选择了主键索引，可以保证顺序，那么所有情况下都是这样子吗，比如我要选一个早一些的商户，或者limit 1000,10这种，如果真的是这样，那么肯定会有很低的性能。
选一个早一些的商户：
SELECT * FROM task_apply WHERE enterprise_id = 1587435526530116 ORDER BY apply_id DESC LIMIT 0,10;
执行计划如下：
可以发现优化器还是很聪明的，直接使用了索引 + 回表的策略。
加大查询的范围limit 1000,10（经过实验，limit 40，10的时候已经选择了索引加回表）
select * from task_apply where enterprise_id = 1619430880026857 order by apply_id desc limit 1000,10
执行计划如下：
依然没有令我们失望，走了索引 + 回表查询。
由此可见，对于新增的商户，在order by apply_id desc + limit 小值 的前提，优化器会认为直接扫描主键索引性能更高；其余情况还是会选择先索引筛选排序，然后回表拿数据。
三、不走索引情况下，SQL慢的原因分析
当再次执行开头提及的慢SQL时，查看耗时：
发现执行的时间变成了1.5s，这是为什么呢？
是因为第一次读取的时候并没有执行过查询操作，所以数据都存在磁盘中，需要将数据从磁盘中取出来，放到内存中（buffer pool），这里会涉及到磁盘页的概念，猜想是因为主键是由idworker生成的，导致了最后的页面出现了页分裂等问题，影响了磁盘的读取速度，感兴趣可以百度关键词搜索；
验证猜想
查看表的状态 show table status like 'task_apply';
上一次表重建的时间是2022-09-19，所以找一个2022-09-19这个时间点之前的数据进行验证我们的猜想；
1. 执行 SELECT * FROM task_apply WHERE enterprise_id = 1619430880026857 and apply_id <= 1565426857989435393 ORDER BY apply_id DESC LIMIT 0,10;
解释器：
执行耗时↓
第一次执行：
第二次执行：
从上面的结果来看，第一次并没有很慢，说明优化器选择这个策略没有问题，第二次执行比一次执行要快了很多，说明磁盘页已经被加载了MySQL的内存中，所以只是快速筛选就可以了。
2. 再次执行 SELECT * FROM task_apply WHERE enterprise_id = 1619430880026857 and apply_id <= 1565426857989435393 ORDER BY apply_id DESC LIMIT 10,10;
执行计划：同上；
执行耗时：
为什么第二次比第一次执行快了这么多，这涉及到磁盘预读的知识，感兴趣的可以百度。
四、结论
- 如果从性能影响上来看，优化器的选择没有问题，毕竟回表和filesort对服务器的性能是有影响的（虽然本身快了，但是服务器总体的性能下降了）。
- 我们的程序中产生了IDworker没有立刻入库，导致库里面的数据主键插入不是增量插入，存在乱序的情况，所以影响了磁盘读入内存的速度，和内存中缓存页放入CPU的速度。
- 大表需要定期重建，增加数据的规整度，加速数据的读取速度。-----重建表语句
alter table table_name engine =innodb； - 表的主键一般设置自增属性（特殊情况除外），这样可以省去好多麻烦，保证数据的规整度。
本文正在参加「技术专题19期 漫谈数据库技术」活动
