不要用 in + 子查询

你好，我是yes。

前两天我的 VIP 用户向我抛出了一个 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 表中是有 task_id 这个索引的。

一眼看去这个查询没啥问题，子查询里会利用 task_id 这个索引，然后外面的这个查询会上 id 这个主键索引。

而事实是：

一看 explain ，果然走了全表扫描：

可以看到子查询用上了 taskId 这个索引，而外部的查询走的是全表扫描，大概扫描了 890W 的数据，所以查询花了 1 分 30 秒。

那么问题来了：明明有主键索引不用，mysql 为什么要选择全表？

我也不知道。

MySQL 有个优化器，它会决定最终以怎么样的形式、选择那个索引来生成最终的执行计划。

对将要执行的 SQL 而言，优化器会有成本模型，它会根据当前表的一些估算值结合当前的 SQL 语句进行打分，比如如果用了索引 A 需要多少 I/O 成本、CPU 成本，如果用索引 B 要多少I/O、CPU成本。

总而言之，它有自己的一套规则，会根据估计值预算成本，根据成本最终生成执行计划。

篇幅有限具体不多介绍，有兴趣的话看下官网：https://dev.mysql.com/doc/refman/8.0/en/cost-model.html

既然是预算，那就有可能不准，所以有时候就会产生该走索引却全表扫描的情况（因为算出的成本走全表扫描反而更低）。

所以针对上面这种情况，我让他加了强制用主键的操作 force index(PRIMARY)，但是并没有生效，还是走了全表。

可以看到扫描了大约 996w 行数据（表数据有新增，所以从800w多变成了900多w）。

我自己估计：大致就是因为子查询的结果不确定，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;

这样改造以后，进行了一波 explain，结果如下：

查询结果秒出，可以看下第二行，用上了主键索引，全部扫描的行数加起来也就 600 多。

所以我们换了一种方式来使得 MySQL 正常的用上索引且所要的结果是一致的。

这里也建议，如果 sql 是包含子查询的 in 查询，那么最好换成 join 的方式，因为我们不能保证 in 的查询一定会用上索引，万一来个全表扫描，在表数据量比较大的场景，很容易产生阻塞，多来几个这样的阻塞，数据库连接不可用，服务可能就挂了。

在测试环境看起来没问题，一到生产就 GG。

所以能不用 in + 子查询，就不要用，可以用 join 来替代实现。

最近一个月还有很多小伙伴来私聊我问我关于 VIP 一对一问答服务的，这里再宣传一下：我的VIP问答服务，关于原理解答、面试等。

我是yes，从一点点到亿点点我们下篇见~