文档首页/ 云数据库 TaurusDB/ 内核介绍/ 查询优化/ IN谓词转子查询
更新时间：2026-01-13 GMT+08:00
查看PDF
分享

IN谓词转子查询

操作场景

社区MySQL在处理column IN (const1, const2, .... )时，如果column上有索引，那么通常优化器会选择Range scan进行扫描。在进行Range scan分析时，如果IN后面的列表非常大，使用的内存会超过range_optimizer_max_mem_size定义的最大内存，导致Range scan失效，从而引发查询的性能下降。

为了解决该问题，通常有以下几种方案，但每种方案都存在一定的弊端：

  • 可以增加允许使用的最大内存，但是该内存是Session级别的，即每个Session进行该查询都会占用同样的内存，可能导致用户实例发生OOM。
  • 如果允许执行 range optimizer，但是IN值过多超过eq_range_index_dive_limit限制，会导致无法进行index dive，而只能走索引统计信息。由于大量Value所对应的统计信息比较简单，很有可能出现估算不准确的情况，导致性能回退。

因此，TaurusDB支持IN谓词转子查询功能。对于满足前提条件的复杂查询，通过该功能优化器可以将某些大的IN谓词转换为IN子查询，随后优化器会进一步考虑是否将其转换为semijoin，以提升复杂查询的执行性能。

转换过程

原查询：

select ... from lineitem where l_partkey in (...)

转换后的查询：

select ... from lineitem where l_partkey in 
 (select tb._col_1 from (values (9628136),(19958441),...) tb)

前提条件

支持的查询语句

  • SELECT
  • INSERT ... SELECT
  • REPLACE ... SELECT
  • 支持视图，PREPARED STMT

约束限制

  • 只支持常量IN LIST（包括NOW()等不涉及表查询的语句）。
  • 不支持STORED PROCEDURE/FUNCTION/TRIGGER。
  • 不支持NOT IN，以及无法使用索引的场景。

使用方法

您可以通过“rds_in_predicate_conversion_threshold”参数设置IN谓词转子查询功能。

表1 参数说明

参数名称

级别

描述

rds_in_predicate_conversion_threshold

Global

IN谓词转子查询功能控制开关。当SQL语句的IN列表中的元素个数超过该参数的取值时，则SQL语句进行转换，将IN谓词转换为子查询。

默认为0，表示关闭该功能。

示例

原查询： 
explain select * from t where a in (1,2,3,4,5);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | idx1          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
explain format=tree select * from t where a in (1,2,3,4,5);
+-------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                         |
+-------------------------------------------------------------------------------------------------+
| -> Filter: (t.a in (1,2,3,4,5))  (cost=0.75 rows=5)
    -> Table scan on t  (cost=0.75 rows=5)
 |
+-------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
转化后： 
set rds_in_predicate_conversion_threshold=3;
Query OK, 0 rows affected (0.00 sec)
explain select * from t where a in (1,2,3,4,5);
+----+-------------+------------------+------------+--------+---------------------+---------------------+---------+----------+------+----
| id | select_type | table            | partitions | type   | possible_keys       | key                 | key_len | ref      | rows | filtered | Extra             |
+----+-------------+------------------+------------+--------+---------------------+---------------------+---------+----------+------+----|  1 | SIMPLE      | t                | NULL       | ALL    | idx1                | NULL                | NULL    | NULL     |    5 |   100.00 | Using where       |
|  1 | SIMPLE      | <in_predicate_2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5       | test.t.a |    1 |   100.00 | IN-list converted |
+----+-------------+------------------+------------+--------+---------------------+---------------------+---------+----------+------+----
2 rows in set, 1 warning (0.00 sec)
explain format=tree select * from t where a in (1,2,3,4,5);
+----------------------------------------------------------------------------------------------------------------------------------------
| EXPLAIN                                                                                                                                                                                                                                                     +----------------------------------------------------------------------------------------------------------------------------------------| -> Nested loop inner join  (cost=2.50 rows=5)
    -> Filter: (t.a is not null)  (cost=0.75 rows=5)
        -> Table scan on t  (cost=0.75 rows=5)
    -> Single-row index lookup on <in_predicate_2> using <auto_distinct_key> (a=t.a)  (cost=0.27 rows=1)
 |

使用explain，在执行计划中的table列存在<in_predicate_*>（*为数字），该表即为构造的临时表，其中存储了IN查询中的所有数据。

也可以通过查看optimize trace，trace中存在in_to_subquery_conversion相关信息。

| explain format=tree select * from t where a in (1,2,3,4,5) | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "in_to_subquery_conversion": {
              "item": "(`t`.`a` in (1,2,3,4,5))",
              "steps": [
                {
                  "creating_tmp_table": {
                    "tmp_table_info": {
                      "table": "intermediate_tmp_table",
                      "columns": 1,
                      "row_length": 5,
                      "key_length": 5,
                      "unique_constraint": false,
                      "makes_grouped_rows": false,
                      "cannot_insert_duplicates": true,
                      "location": "TempTable"
                    }
                  }
                },

性能测试

使用sysbench模型测试。

  1. 准备1000w数据。 
    sysbench /usr/share/sysbench/oltp_read_only.lua --tables=1 --report-interval=10 --table-size=10000000  --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   prepare
  2. 查询带1万个IN。 
    select count(*) from sbtest1 where id/k in (... ...);

性能对比如下表所示：

表2 性能数据

测试方法

开启转换

关闭转换（不适用range_opt）

性能对比

带索引

0.09秒

2.48秒

提升26.56倍
图1 耗时对比
父主题： 查询优化

相关文档