MySQL最怕的IN大列表，被百度智能云GaiaDB治好了！查询速度提升60倍！
更新时间：2025-10-31
大家好，今天给大家分享一个百度智能云GaiaDB在MySQL内核优化上的黑科技——如何让包含数十万个值的IN查询，从原来的20秒降到0.3秒！
在生产环境中，通常很多业务场景会使用包含成千上万个取值的 IN 谓词进行数据过滤。然而当列表过大时，MySQL 的 range optimizer 容易因内存限制（由 range_optimizer_max_mem_size控制）而失效，导致查询退化为全表扫描，严重影响性能。
百度智能云GaiaDB 从 3.2.3.1 版本开始，支持将大 IN 列表自动转换为 IN 子查询，从根本上解决了这一瓶颈。
传统 MySQL 的优化瓶颈
在处理形如：
Plain Text
1column IN (item1, item2, ... )这样的 IN 列表时，若 column 有索引，MySQL 会尝试使用range optimizer将其转换为多个 OR 条件，并进一步尝试使用索引范围扫描。然而该过程存在两个明显短板：
- 内存消耗大：优化过程需占用大量内存，一旦超出
range_optimizer_max_mem_size即退化为全表扫描； - 索引选择不准确：当列表长度超过
eq_range_index_dive_limit时，优化器无法通过index dive获取精确的数据分布信息，只能依赖简单的索引统计量，容易导致执行计划劣化。
百度智能云GaiaDB 的解决方案：IN 列表转 IN 子查询
百度智能云GaiaDB 将 IN 列表改写为如下形式：
Plain Text
1column IN (SELECT * FROM (VALUES ROW(item1), ROW(item2), ...) AS tvc)该改写将 IN 列表转换为一个由表值构造器组成的非关联子查询，从而绕过range optimizer的内存限制，转而采用semi-join物化策略执行（由于临时表的数据量一般远小于外部表，因此会走materialization-scan模式），其流程如下：
- 构造并物化临时表：将 IN 列表中的值构建为临时表；
- 索引关联查询：从临时表中取一行数据，使用外表索引进行匹配；
- 高效匹配输出：遍历临时表完成全部匹配。
该方法不仅规避了range optimizer的内存瓶颈，还充分利用了索引，实现了与range scan同等级别甚至更优的查询效率。
适用条件与参数设置
百度智能云GaiaDB 在以下条件下自动启用 IN 谓词转 IN 子查询：
- 版本要求：GaiaDB 3.2.3.1及以上；
- 列表长度：IN 列表中元素数量 ≥
gaia_in_predicate_conversion_threshold； - 语法位置：IN 谓词位于 WHERE 或 ON 子句顶层，且仅通过 AND 连接；
- 不支持场景：NOT IN 或无索引字段。
使用方法
通过gaia_in_predicate_conversion_threshold参数控制该功能开启。
| 参数名称 | 级别 | 描述 |
|---|---|---|
| gaia_in_predicate_conversion_threshold | Global, Session | 当IN列表中的值的数量达到该阈值时，将IN谓词转换为IN子查询，从而绕过range_optimizer_max_mem_size 对超大IN列表的优化分析限制。 设置为 0 表示关闭该功能。 取值范围：0~18446744073709551615。默认值为5000。 |
示例
表结构：
Plain Text
1CREATE TABLE `lineitem` (
2 `L_ORDERKEY` int NOT NULL,
3 `L_PARTKEY` int NOT NULL,
4 `L_SUPPKEY` int NOT NULL,
5 `L_LINENUMBER` int NOT NULL,
6 `L_QUANTITY` decimal(15,2) NOT NULL,
7 `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
8 `L_DISCOUNT` decimal(15,2) NOT NULL,
9 `L_TAX` decimal(15,2) NOT NULL,
10 `L_RETURNFLAG` char(1) NOT NULL,
11 `L_LINESTATUS` char(1) NOT NULL,
12 `L_SHIPDATE` date NOT NULL,
13 `L_COMMITDATE` date NOT NULL,
14 `L_RECEIPTDATE` date NOT NULL,
15 `L_SHIPINSTRUCT` char(25) NOT NULL,
16 `L_SHIPMODE` char(10) NOT NULL,
17 `L_COMMENT` varchar(44) NOT NULL,
18 PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`),
19 KEY `LINEITEM_FK2` (`L_PARTKEY`,`L_SUPPKEY`),
20 CONSTRAINT `lineitem_ibfk_1` FOREIGN KEY (`L_ORDERKEY`) REFERENCES `orders` (`O_ORDERKEY`),
21 CONSTRAINT `lineitem_ibfk_2` FOREIGN KEY (`L_PARTKEY`, `L_SUPPKEY`) REFERENCES `partsupp` (`PS_PARTKEY`, `PS_SUPPKEY`)
22) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;SQL 语句：
Plain Text
1EXPLAIN SELECT sum(l_extendedprice) / 7.0 AS avg_yearly FROM lineitem WHERE l_partkey IN (207066596,1483256090,...); # 10w参数关闭当前特性的执行计划：
Plain Text
1+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
2| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
3+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
4| 1 | SIMPLE | lineitem | NULL | ALL | LINEITEM_FK2 | NULL | NULL | NULL | 56764746 | 50.00 | Using where |
5+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+打开当前特性的执行计划：
Plain Text
1+----+--------------+-------------+------------+------+---------------+--------------+---------+----------------------+--------+----------+-----------------------+
2| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
3+----+--------------+-------------+------------+------+---------------+--------------+---------+----------------------+--------+----------+-----------------------+
4| 1 | PRIMARY | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
5| 1 | PRIMARY | lineitem | NULL | ref | LINEITEM_FK2 | LINEITEM_FK2 | 4 | <subquery2>.column_0 | 27 | 100.00 | Using index condition |
6| 2 | MATERIALIZED | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 100000 | 100.00 | NULL |
7| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
8+----+--------------+-------------+------------+------+---------------+--------------+---------+----------------------+--------+----------+-----------------------+性能测试
测试一：使用sysbench模型测试
1.准备5000w数据：
Plain Text
1sysbench /usr/share/sysbench/oltp_read_only.lua --tables=1 --report-interval=10 --table-size=50000000 --mysql-user=root --mysql-password=123456 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sbtest --time=300 --max-requests=0 --threads=200 prepare2.查询带10w个常量值的语句，k字段上有索引：
Plain Text
1SELECT COUNT(*) FROM sbtest1 WHERE k IN (207066596,1483256090,...);3.结果对比：
| 开启转换耗时 | 关闭转换（不使用range optimizer）耗时 | 性能对比 |
|---|---|---|
| 0.42s | 12.61s | 提升约30倍 |
测试二：使用TPC-H数据集测试
1.准备TPC-H 10GB标准测试数据集；
2.询带10w个常量值的语句，l_partkey 字段上有索引：
Plain Text
1SELECT sum(l_extendedprice) / 7.0 AS avg_yearly FROM lineitem WHERE l_partkey IN (207066596,1483256090,...);3.结果对比：
| 开启转换耗时 | 关闭转换（不使用range optimizer）耗时 | 性能对比 |
|---|---|---|
| 0.29s | 20.01s | 提升约60倍 |
结论
百度智能云GaiaDB 通过将大 IN 列表智能转换为 IN 子查询，结合semi-join物化策略，有效克服了传统 MySQL 在处理大列表时的内存与优化限制。在实际测试中，查询性能提升显著，尤其适用于高并发、大数据量的在线业务场景。该功能无需业务改造，仅通过参数即可控制，是 百度智能云GaiaDB 在查询优化方面的重要增强。