IN谓词转子查询

PolarDB支持IN谓词转子查询功能。对于满足前提条件的复杂查询，通过该功能优化器可以将某些大的IN谓词转换为IN子查询，从而提升复杂查询的执行性能。

前提条件

• 集群版本需为PolarDB MySQL版8.0版本且修订版本需为8.0.2.2.10或以上。
• IN列表中的元素个数超过loose_in_predicate_conversion_threshold参数设置的个数。
• [NOT]IN条件位于WHERE或ON子句的顶层。

使用方法

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

示例

原查询：

mysql> explain  select * from t where a in (1,2,3,5,5);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  160 |    50.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,5,5);
+------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                              |
+------------------------------------------------------------------------------------------------------+
| -> Filter: (t.a in (1,2,3,5,5))  (cost=16.25 rows=80)
    -> Table scan on t  (cost=16.25 rows=160)
 |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

转换后的查询：

mysql> set in_predicate_conversion_threshold=5;
mysql> explain  select * from t where a in (1,2,3,5,5);
+----+-------------+------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+--------------------------+
| id | select_type | table      | partitions | type   | possible_keys       | key                 | key_len | ref      | rows | filtered | Extra                    |
+----+-------------+------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+--------------------------+
|  1 | PRIMARY     | t          | NULL       | ALL    | NULL                | NULL                | NULL    | NULL     |  160 |   100.00 | Using where              |
|  1 | PRIMARY     | <derived3> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 8       | test.t.a |    1 |   100.00 | Using where; Using index |
|  3 | DERIVED     | NULL       | NULL       | NULL   | NULL                | NULL                | NULL    | NULL     | NULL |     NULL | IN-list Converted        |
+----+-------------+------------+------------+--------+---------------------+---------------------+---------+----------+------+----------+--------------------------+
mysql> explain format=tree select * from t1 where a in (1,2,3,5,5);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop semijoin
    -> Filter: (t1.a is not null)  (cost=0.55 rows=3)
        -> Table scan on t1  (cost=0.55 rows=3)
    -> Filter: (t1.a = tvc_0._col_1)
        -> Index lookup on tvc_0 using <auto_key0> (_col_1=t1.a)
            -> Materialize
                -> scan on in-list: 5 rows