本文分享自華為雲社群《【MySQL技術專欄】GaussDB(for MySQL) Big IN查詢最佳化》，作者：GaussDB 資料庫。
背景介紹
在生產環境中，經常會遇到客戶業務的SQL語句進行過濾查詢，然後進行聚合處理，並且IN謂詞列表中包含幾千甚至上萬個常量值。如下所示，此類語句的執行時間非常長。
MySQL最佳化
開源MySQL在處理列IN (const1, const2, .... )時，如果列上面有索引，最佳化器會選擇Range scan進行掃描，否則會使用全表掃描方式。range_optimizer_max_mem_size系統變數控制範圍最佳化過程分析中可使用的最大記憶體。如果IN謂詞的列表元素非常多，IN中每個的內容都會被視為OR每個，OR大約佔用230位元組，如果元素個數很多，則使用更多的記憶體。如果使用記憶體會超過定義的最大記憶體，會使範圍最佳化失效，最佳化器將改變策略，如轉換為全表掃描，從而引發查詢的效能下降。
對於這個最佳化問題，可以透過調整range_optimizer_max_mem_size來處理。range_optimizer_max_mem_size定義的記憶體是會話級別的，每個會話執行該型別的語句，都會佔用相同的記憶體，在大併發場景下，會導致例項記憶體佔用過高，例項OOM風險。
對於範圍查詢，MySQL定義了eq_range_index_dive_limit系統變數，來控制在處理等值範圍查詢時，最佳化器是否進行索引潛水（index div）。索引潛水是利用索引完成元組數的說明，可以得到更準確的資訊，從而做出更好的查詢策略最佳化，但是執行時間也長。在IN組合數超過一定數量的時候就不適用索引跳水，系統採用靜態索引統計資訊值來選擇索引，這種方法得到的結果一定準確。這可能導致MySQL無法很好的利用索引，導致效能回退。
GaussDB(for MySQL)的Big IN最佳化
column IN (const1, const2, ....)
column IN (SELECT ... FROM temporary_table)
串聯可以有兩個順序：
-
Materialization-scan：表示從物化表到外觀，對物化表進行全表掃描。
-
Materialization-lookup ：表示從外觀到物化表，在物化表中查詢資料的時候可以使用主建進行查詢。
物化掃描
-
執行子查詢，走索引auto_distinct_key，同時對結果進行去重；
-
將上一步的結果儲存在臨時表模板1裡；
-
從臨時表中取一行資料，到外觀中找到滿足補充條件的行；
-
步驟重複3，直到遍歷臨時表結束。
物化查詢
-
先執行子查詢；
-
將上一步得到的結果儲存到臨時表中；
-
從外觀中取出一行資料，到物化臨時表中去查詢滿足補充條件的行，走物化表的主鍵，每次掃描1行；
-
重複3，直至瀏覽整個外觀。
最佳化器會根據內部外觀的大小來選擇不同的串聯順序。真實場景中，一般查詢的表的資料量很大，上千萬甚至上億；IN列表中的元素個數遠小於表數量，最佳化器會選擇Materialization-scan方式進行掃描，外觀查詢時如果走主鍵索引，則最佳化後的總的掃描行數為N，當M遠大於N時，效能提升會非常明顯。
使用方法
rds_in_predicate_conversion_threshold引數是修改IN謂詞底部該查詢功能開關，當SQL語句的IN謂詞列表中的元素個數超過引數的取值時，將啟動該最佳化策略。透過該變數的值來使用該功能。下面一個簡單的例子說明最佳化的使用：
表結構
create table t1(id int, a int, key idx1(a));
select * from t1 where a in (1,2,3,4,5);
設定set rds_in_predicate_conversion_threshold = 0 和 set range_optimizer_max_mem_size=1關閉大IN謂詞最佳化功能和範圍掃描最佳化策略，檢視上述查詢語句的執行計劃，結果如下：
> set rds_in_predicate_conversion_threshold = 0; > set range_optimizer_max_mem_size=1; > explain select * from t1 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 | t3 | NULL | ALL | key1 | NULL | NULL | NULL | 3 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) show warnings; +---------+------+---------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------+ | Warning | 3170 | Memory capacity of 1 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query. | | Note | 1003 | /* select#1 */ select `test`.`t3`.`id` AS `id`,`test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` in (3,4,5)) | +---------+------+---------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
發現上述語句執行的時候報了警告，警告的資訊顯示因為範圍最佳化過程中使用的記憶體超過了range_optimizer_max_mem_size導致對於該語句沒有使用範圍限制最佳化。從而導致掃描的型別變成了ALL，變為全表掃描。
設定set rds_in_predicate_conversion_threshold = 3開啟大IN謂詞最佳化選項，表示當IN謂詞列表元素超過3個的時候，啟動大IN佇列查詢最佳化策略。執行EXPLAIN FORMAT=TREE語句可以檢視最佳化是否生效。
> set rds_in_predicate_conversion_threshold = 3; > explain format=tree select * from t1 where a in (1,2,3,4,5); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=0.70 rows=1) -> Filter: (t1.a is not null) (cost=0.35 rows=1) -> Table scan on t1 (cost=0.35 rows=1) -> Single-row index lookup on <in_predicate_2> using <auto_distinct_key> (a=t1.a) (cost=0.35 rows=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
執行計劃中的<in_predicate_*>（*為數字）表為Big INTool中構造的臨時表，儲存了IN謂詞列表中的所有資料。
使用限制
Big IN最佳化支援的查詢語句包括以下語句列表：
-
選擇
-
插入...選擇
-
替換...選擇
-
支援觀點
-
準備好的STMT
約束與限制
Big IN 轉子查詢，藉助mysql提供的子查詢最佳化方案來實現效能，因此在使用上有如下限制，否則反而會降低效能。
-
不支援無法使用索引的場景
-
只支援常量IN LIST（包括NOW(), ?等不涉及表查詢的語句）
-
不支援儲存過程/函式/觸發器
-
不支援不在
典型場景測試對比
表測試結構如下：
CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB; 表的資料量為1000w。 > select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+
查詢語句如下，其中條件欄位是有索引，IN列表裡包含1萬個常量數字。
select count(*) from sbtest1 where k in (2708275,5580784,7626186,8747250,228703,4589267,5938459,6982345,2665948,4830545,4929382,8723757,354179,1903875,5111120,5471341,7098051,3113388,2584956,6550102,2842606,2744112,7077924,4580644,5515358,1787655,6391388,6044316,2658197,5628504,413887,6058866,3321587,1430333,445303,7373496,9133196,6760595,4735642,4756387,9845147,9362192,7271805,4351748,6625915,3813276,4236692,8308973,4407131,9481423,3301846,432577,810938,3830320,6120078,6765157,6456566,6649509,1123840,2906490,9965014,3725748, ... ); 效能對比如下圖所示：
可以看出in-list最佳化後比原有的方式效能提高了36倍。
點選關注，第一時間瞭解華為雲新鮮技術~