更新时间:2024-09-06 GMT+08:00

使用方法

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

该参数值默认为0, 表示关闭该功能。如果您需要使用,则需要联系客服人员开启。

表1 参数说明

参数名称

级别

描述

rds_in_predicate_conversion_threshold

Global

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

示例

  • 原查询:
    mysql> 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)
    
    mysql> 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)

  • 转化后:
    mysql> set rds_in_predicate_conversion_threshold=3;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> 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)
    
    mysql> 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"
                        }
                      }
                    },