前两天有人向我抛出了一个SQL问题，他的MySQL是8.x版本：
大致意思sql如下：
select * from A where id in (
select max(id) as id from A
where task_id in(1,2,3)
group by task_id
);
这个A表中有这个索引。
乍一看，这个查询没有任何问题。 这个索引会在子查询中使用，然后id的主键索引会被添加到外查询中。
事实是：
乍一看，确实进行了全表扫描：
可以看到，子查询使用了这个索引，而外部查询使用了全表扫描，扫描了大约890W的数据，所以查询耗时1分30秒。
那么问题来了：为什么在有主键索引没有使用的情况下mysql会选择全表呢？
我也不知道。
MySQL有一个优化器，它会决定选择什么形式以及使用哪个索引生成最终的执行计划。
对于要执行的SQL，优化器会有一个成本模型，它会根据当前表结合当前SQL语句的一些估计值来打分，比如如果需要多少I/O成本和CPU成本使用索引 A，如果使用索引 A，索引 B 会花费多少 I/O 和 CPU 成本。
总而言之，它有自己的一套规则，根据估算来预算成本，最后根据成本生成执行计划。
篇幅有限，具体介绍不多。 如果您有兴趣，请查看官方网站：
既然是预算，可能会不准确，所以有时候会出现应该使用索引但是全表扫描的情况（因为计算的成本比全表扫描低）。
于是针对上面的情况，我让他添加了操作()来强制使用主键，但是并没有生效，还是全表都被取了。
可以看到扫描了大约996w行数据（表数据已经添加了，所以从800w多变成了900w多）。
我自己的估计：大致是因为子查询的结果是不确定的，MySQL不知道in的值有多大，所以保守选择全表（我猜测，具体逻辑估计要看源码，但事实确实是最终表演的一部分）。
问题是in的值是不确定的。
通过子查询，我们实际上并不能保证in的值。
所以最终的解决方案是放弃in方法，使用inner join方法来改造sql实现。
修改SQL如下：
select * from A a inner join (
select max(id) as id from A
where task_id in(1,2,3) group by task_id
) b
on a.id = b.id;
经过这样的改造，进行了一波，结果如下：
查询结果以秒为单位显示。 你可以看看第二行。 使用主键索引，扫描总行数超过600行。
所以我们改变了一种方式，让MySQL能够正常使用索引，并且与想要的结果一致。
这里也建议，如果sql是包含子查询的in查询，最好改用join方式，因为我们不能保证in查询一定会使用索引。 全表扫描的情况下，表的数据量比较大，在某些场景下，很容易出现阻塞。 如果此类阻塞较多，数据库连接将不可用，服务可能会挂起。
在测试环境中看起来不错，在生产环境中则很糟糕。
所以如果能用in+子查询就不要用，可以用join代替。
程序员专属T恤
商品直购链接
推荐阅读：
