MySQL 对 IN 子查询的优化

1.物化表

如果子查询的结果集太多，那就意味着 IN 子句中的参数特别多，这就导致：无法有效的使用索引，只能对外层查询进行全表扫描。

在对外层查询执行全表扫描时，由于 IN 子句中的参数太多，这会导致检测一条记录是否符合和 IN 子句中的参数匹配花费的时间太长。
比如说 IN 子句中的参数只有两个：
SELECT FROM tbl_name WHERE column IN (a, b);
这样相当于需要对tbl_name 表中的每条记录判断一下它的column 列是否符合 column = a OR column = b。在 IN 子句中的参数比较少时这并不是什么问题， 如果 IN 子句中的参数比较多时，比如这样：
SELECT FROM tbl_name WHERE column IN (a, b, c …, …);
那么这样每条记录需要判断一下它的 column 列是否符合 column = a OR column = b OR column = c OR …，这样性能耗费可就多了。
MySQL 的改进是不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。
写入临时表的过程是这样的：
1、该临时表的列就是子查询结果集中的列。
2、写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引。

一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory 存储引擎的临时表，而且会为该表建立哈希索引。
如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+树索引。 :::tips MySQL 把这个将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有 B+树索引），通过索引执行 IN 语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。 :::

2.物化表转内连接

s1 表中的记录数量 × 通过 m_val = xxx 对 materialized_table 表进行单表访问的成本（我们前边说过物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的)。

2、如果使用 materialized_table 表作为驱动表的话，总查询成本由下边几个部分组成：

  • 物化子查询时需要的成本
  • 扫描物化表时的成本
  • 物化表中的记录数量 × 通过 order_note= xxx 对 s1 表进行单表访问的成本（如果 order_note 列上建立了索引，这个步骤还是非常快的）。

MySQL 查询优化器会通过运算来选择上述成本更低的方案来执行查询

3.将子查询转换为半连接(semi-join)

虽然将子查询进行物化之后再执行查询都会有建立临时表的成本，但是不管怎么说，我们见识到了将子查询转换为连接的强大作用，MySQL 继续开脑洞：能不能不进行物化操作直接把子查询转换为连接呢？让我们重新审视一下上边的查询语句：
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = ‘a’);
我们可以把这个查询理解成：对于 s1 表中的某条记录，如果我们能在 s2 表
（准确的说是执行完 WHERE s2.order_no= ‘a’之后的结果集）中找到一条或多条记录，这些记录的 order_note 的值等于 s1 表记录的 order_note 列的值，那么该条s1 表的记录就会被加入到最终的结果集。这个过程其实和把 s1 和 s2 两个表连接起来的效果很像：

  1. SELECT s1.* FROM s1 INNER JOIN s2
  2. ON s1.order_note = s2.order_note WHERE s2.order_no= 'a';

只不过我们不能保证对于 s1 表的某条记录来说，在 s2 表（准确的说是执行完 WHERE s2.order_no= ‘a’之后的结果集）中有多少条记录满足 s1.order_no = s2.order_no 这个条件，不过我们可以分三种情况讨论：
情况一：对于 s1 表的某条记录来说，s2 表中没有任何记录满足 s1.order_note= s2.order_note 这个条件，那么该记录自然也不会加入到最后的结果集。

情况二：对于 s1 表的某条记录来说，s2 表中有且只有 1 条记录满足
s1.order_note = s2.order_note 这个条件，那么该记录会被加入最终的结果集。

情况三：对于 s1 表的某条记录来说，s2 表中至少有 2 条记录满足
s1.order_note = s2.order_note 这个条件，那么该记录会被多次加入最终的结果集。对于 s1 表的某条记录来说，由于我们只关心 s2 表中是否存在记录满足s1.order_no = s2.order_note 这个条件，而不关心具体有多少条记录与之匹配，又因为有情况三的存在，我们上边所说的 IN 子查询和两表连接之间并不完全等价。但是将子查询转换为连接又真的可以充分发挥优化器的作用，所以 MySQL 在这里提出了一个新概念 —- 半连接（英文名：semi-join）。

将 s1 表和 s2 表进行半连接的意思就是：对于 **s1 表的某条记录来说，我们只关心在 s2 表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留 s1 **表的记录。为了让大家有更直观的感受，我们假设 MySQL 内部是这么改写上边的子查询的：
SELECT s1. FROM s1 *SEMI JOIN s2
ON s1.order_note = s2.order_note WHERE order_no= ‘a’;
注意： semi-join 只是在 MySQL 内部采用的一种执行子查询的方式，MySQL 并没有提供面向用户的 semi-join 语法。