使用方法

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

说明：

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

示例：

• 原查询：

  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"
                      }
                    }
                  },