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