PolarDB支持IN谓词转JOIN功能。对于满足前提条件的复杂查询，通过该功能优化器可以将某些大的IN谓词转换为JOIN，从而提升复杂查询的执行性能。
前提条件
集群版本需为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谓词转JOIN功能。具体操作请参见设置集群参数和节点参数。
参数名称 | 级别 | 描述 |
loose_in_predicate_conversion_threshold | Global | IN谓词转JOIN功能控制开关。 当SQL语句的IN列表中的元素个数大于或等于该参数值时，SQL语句进行转换，将IN谓词转换为JOIN。取值范围：0~18446744073709551615。默认值为5000。 说明 当该参数设置为0时，表示关闭该功能。 |
示例
原查询：
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