本文介绍云数据库 MySQL 版提供的 IN 谓词性能优化功能的相关信息。

支持版本

MySQL 5.7 版本，且内核小版本为 MySQL 5.7.44_20250815 及以上。

MySQL 8.0 版本，且内核小版本为 MySQL 8.0.32_20250815 及以上。

MySQL 8.4 版本，且内核小版本为 MySQL 8.4.7_20260415 及以上。

背景信息

等值判断出现在查询条件（WHERE 条件或 JOIN 条件）中时，可以通过索引进行快速定位，避免扫描全表，从而降低开销，尤其对于行数很多的表，索引扫描带来的性能优化十分明显。

IN 谓词出现在查询条件（WHERE 条件或 JOIN 条件）中时，一样可以通过索引进行快速定位，避免全表扫描，从而降低开销，这是因为 IN 谓词本质上可以拆分成若干个等值条件的逻辑或。

在 MySQL 社区版的实现中，IN 谓词列表的长度越长，优化器生成索引扫描计划就会记录越多的信息，进而占用更多的内存。MySQL 为了控制内存使用，设定了一个阈值，由参数 range_optimizer_max_mem_size 控制，若优化器生成索引扫描计划时，使用内存超过该阈值，就会停止生成索引扫描计划，改用全表扫描计划。当 IN 谓词列表的长度较长，优化器使用的内存就有可能超过阈值，导致最终选择了性能更差的全表扫描。

特性说明

IN 谓词性能优化特性在优化阶段可以对包含 IN 谓词的查询进行改写，消除 IN 谓词，改为和一张临时表进行 JOIN。例如下方包含 IN 谓词的查询：

可以改写为：

其中 tmp_table 包含 3 行 1 列数据 (0), (1), (2)，对应了改写前 IN 谓词列表中的数据。

上述改写的好处在于：

如果改写前的 IN 谓词能够使用索引在 sbtest1 表上快速定位到所需数据，改写后的 SEMI JOIN 也能使用同样的索引在 sbtest1 表上快速定位到所需数据。

改写前如果 IN 谓词列表长度过长，优化器可能为了避免使用过多内存，对 sbtest1 选择性能更差的全表扫描。改写后的 SEMI JOIN 则不用担心内存使用过多的情况，因为 IN 谓词列表的所有数据被放在了临时表中，临时表一开始会存放在内存中，当使用内存过多时，会自动改为存储在硬盘上，所以无论改写前 IN 谓词列表有多长，改写后的 SEMI JOIN 总能使用 sbtest1 表的索引快速定位到所需数据。

使用说明

参数介绍

本特性新增了两个参数 loose_enable_in_list_optimization、loose_in_list_optimization_threshold，用于控制改写的时机。这两个参数的介绍如下：

名称	默认值	是否需要重启以生效	取值范围	级别	参数描述
loose_enable_in_list_optimization	OFF	否	OFF \| ON \| AUTO	Global、Session	用于控制 IN 谓词优化功能的行为：设置为 OFF 表示任何时候都不进行 IN 谓词改写。设置为 ON 表示当 IN 谓词列表长度达到阈值时进行改写，阈值由参数 loose_in_list_optimization_threshold 控制。设置为 AUTO 表示由 MySQL 自行决定改写的时机，MySQL 会根据改写前后的性能综合判断，决定是否进行改写。
loose_in_list_optimization_threshold	32768	否	[1, 4294967295]	Global、Session	当 loose_enable_in_list_optimization 设置为 ON 时，用于控制改写阈值，IN 谓词列表的长度达到该参数值时会进行改写。

若要开启本特性，推荐将 loose_enable_in_list_optimization 设置为 AUTO，这样遇到包含 IN 谓词的查询时，本特性会根据改写前后的性能进行综合判断，自行决定是否改写。更多关于修改参数的详细信息，请参见修改参数。

使用示例

设置参数开启本特性。

将 loose_enable_in_list_optimization 设置为 AUTO，由 MySQL 自行决定改写的时机：

mysql> SET loose_enable_in_list_optimization = AUTO;
Query OK, 0 rows affected (0.00 sec)

或者将 loose_enable_in_list_optimization 设置为 ON，同时设置 loose_in_list_optimization_threshold，这样将会改写所有长度达到 loose_in_list_optimization_threshold 的 IN 谓词：

-- 改写所有长度不小于 10 的 IN 谓词
mysql> SET loose_enable_in_list_optimization = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET loose_in_list_optimization_threshold = 10;
Query OK, 0 rows affected (0.00 sec)

使用 EXPLAIN 语句检查查询是否改写：

-- 改写所有长度不小于 10 的 IN 谓词
mysql> SET loose_enable_in_list_optimization = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET loose_in_list_optimization_threshold = 10;
Query OK, 0 rows affected (0.00 sec)

-- IN 谓词列表长度为 9，不改写
mysql> EXPLAIN SELECT * FROM sbtest1 WHERE k IN (0, 1, 2, 3, 4, 5, 6, 7, 8);
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | k_1 | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- IN 谓词列表长度为 10，改写
mysql> EXPLAIN SELECT * FROM sbtest1 WHERE k IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+------+----------+---------------------------------------------------------------------+
| 1 | PRIMARY | sbtest1 | NULL | ALL | k_1 | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | mysql.sbtest1.k | 2 | 100.00 | Using where; IN-list optimization; Using index; FirstMatch(sbtest1) |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+------+----------+---------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

可以看到，当 IN 谓词改写时，EXPLAIN 的输出结果中，Extra 列会显示 IN-list optimization。

注意事项

本特性的改写需要满足如下条件：

查询的 WHERE 条件或 JOIN 条件中包含 IN 谓词。

IN 谓词列表中只包含常量，且所有常量的类型一致。

IN 谓词需要能够使用索引。

若 loose_enable_in_list_optimization 为 ON，IN 谓词列表的长度需要不小于 loose_in_list_optimization_threshold 的值。若 loose_enable_in_list_optimization 为 AUTO，MySQL 会根据改写前后的性能综合判断，决定是否进行改写。

性能数据

创建一张 20,000,000 行的 sysbench 表 sbtest1，执行如下查询进行性能验证：

在 MySQL 5.7 版本下，使用不同长度的 IN 谓词，改写前后的执行时间如下：

在 MySQL 8.0 版本下，使用不同长度的 IN 谓词，改写前后的执行时间如下：

根据以上性能数据，可得出以下结论：

IN 谓词列表长度在 10000 及以下时，改写前带 IN 谓词的查询使用索引扫描，性能略优于改写后的 SEMI JOIN 查询。这是由于改写本身会有一定开销，并且改写后额外引入一张临时表，执行的计划会略微复杂一点。

IN 谓词列表长度在 50000 及以上时，改写前带 IN 谓词的查询由于内存限制，使用了全表扫描，这种情况下其性能远不如改写后的 SEMI JOIN 查询，因为改写后的 SEMI JOIN 查询可以使用索引快速定位 sbtest1 中所需的数据。

因此，使用本特性时，推荐将 loose_enable_in_list_optimization 设置为 AUTO，这样遇到包含 IN 谓词的查询时，本特性会根据改写前后的性能进行综合判断，自行决定是否改写。