Stories

Detail Return Return

MySQL最怕的IN大列表，被百度智能雲GaiaDB治好了！查詢速度提升60倍！ - Stories Detail

大家好，今天給大家分享一個百度智能雲GaiaDB在MySQL內核優化上的黑科技——如何讓包含數十萬個值的IN查詢，從原來的20秒降到0.3秒！

在生產環境中，通常很多業務場景會使用包含成千上萬個取值的 IN 謂詞進行數據過濾。然而當列表過大時，MySQL 的 range optimizer 容易因內存限制（由 range_optimizer_max_mem_size 控制）而失效，導致查詢退化為全表掃描，嚴重影響性能。

百度智能雲GaiaDB 從 3.2.3.1 版本開始，支持將大 IN 列表自動轉換為 IN 子查詢，從根本上解決了這一瓶頸。

傳統 MySQL 的優化瓶頸

在處理形如：

column IN (item1, item2, ... )

這樣的 IN 列表時，若 column 有索引，MySQL 會嘗試使用 range optimizer 將其轉換為多個 OR 條件，並進一步嘗試使用索引範圍掃描。然而該過程存在兩個明顯短板：

  • 內存消耗大：優化過程需佔用大量內存，一旦超出 range_optimizer_max_mem_size 即退化為全表掃描；
  • 索引選擇不準確：當列表長度超過 eq_range_index_dive_limit 時，優化器無法通過 index dive 獲取精確的數據分佈信息，只能依賴簡單的索引統計量，容易導致執行計劃劣化。

百度智能雲GaiaDB 的解決方案：IN 列表轉 IN 子查詢

百度智能雲GaiaDB 將 IN 列表改寫為如下形式：

column IN (SELECT * FROM (VALUES ROW(item1), ROW(item2), ...) AS tvc)

該改寫將 IN 列表轉換為一個由表值構造器組成的非關聯子查詢，從而繞過 range optimizer 的內存限制，轉而採用 semi-join 物化策略執行（由於臨時表的數據量一般遠小於外部表，因此會走 materialization-scan 模式），其流程如下：

  1. 構造並物化臨時表：將 IN 列表中的值構建為臨時表；
  2. 索引關聯查詢：從臨時表中取一行數據，使用外表索引進行匹配；
  3. 高效匹配輸出：遍歷臨時表完成全部匹配。

該方法不僅規避了 range optimizer 的內存瓶頸，還充分利用了索引，實現了與 range scan 同等級別甚至更優的查詢效率。

適用條件與參數設置

百度智能雲GaiaDB 在以下條件下自動啓用 IN 謂詞轉 IN 子查詢：

  • 版本要求：GaiaDB 3.2.3.1 及以上；
  • 列表長度：IN 列表中元素數量 ≥ gaia_in_predicate_conversion_threshold；
  • 語法位置：IN 謂詞位於 WHERE 或 ON 子句頂層，且僅通過 AND 連接；
  • 不支持場景：NOT IN 或無索引字段。

使用方法

通過 gaia_in_predicate_conversion_threshold 參數控制該功能開啓。

參數名稱
gaia_in_predicate_conversion_threshold

級別
Global, Session

描述
當IN列表中的值的數量達到該閾值時，將IN謂詞轉換為IN子查詢，從而繞過range_optimizer_max_mem_size 對超大IN列表的優化分析限制。設置為 0 表示關閉該功能。取值範圍：0~18446744073709551615。默認值為5000。

示例

CREATE TABLE `lineitem` (
  `L_ORDERKEY` int NOT NULL,
  `L_PARTKEY` int NOT NULL,
  `L_SUPPKEY` int NOT NULL,
  `L_LINENUMBER` int NOT NULL,
  `L_QUANTITY` decimal(15,2) NOT NULL,
  `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
  `L_DISCOUNT` decimal(15,2) NOT NULL,
  `L_TAX` decimal(15,2) NOT NULL,
  `L_RETURNFLAG` char(1) NOT NULL,
  `L_LINESTATUS` char(1) NOT NULL,
  `L_SHIPDATE` date NOT NULL,
  `L_COMMITDATE` date NOT NULL,
  `L_RECEIPTDATE` date NOT NULL,
  `L_SHIPINSTRUCT` char(25) NOT NULL,
  `L_SHIPMODE` char(10) NOT NULL,
  `L_COMMENT` varchar(44) NOT NULL,
  PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`),
  KEY `LINEITEM_FK2` (`L_PARTKEY`,`L_SUPPKEY`),
  CONSTRAINT `lineitem_ibfk_1` FOREIGN KEY (`L_ORDERKEY`) REFERENCES `orders` (`O_ORDERKEY`),
  CONSTRAINT `lineitem_ibfk_2` FOREIGN KEY (`L_PARTKEY`, `L_SUPPKEY`) REFERENCES `partsupp` (`PS_PARTKEY`, `PS_SUPPKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SQL語句：

EXPLAIN SELECT sum(l_extendedprice) / 7.0 AS avg_yearly FROM lineitem WHERE l_partkey IN (207066596,1483256090,...); # 10w參數

關閉當前特性的執行計劃：

+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | lineitem | NULL       | ALL  | LINEITEM_FK2  | NULL | NULL    | NULL | 56764746 |    50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+-------------+

打開當前特性的執行計劃：

+----+--------------+-------------+------------+------+---------------+--------------+---------+----------------------+--------+----------+-----------------------+
| id | select_type  | table       | partitions | type | possible_keys | key          | key_len | ref                  | rows   | filtered | Extra                 |
+----+--------------+-------------+------------+------+---------------+--------------+---------+----------------------+--------+----------+-----------------------+
|  1 | PRIMARY      | <subquery2> | NULL       | ALL  | NULL          | NULL         | NULL    | NULL                 |   NULL |   100.00 | NULL                  |
|  1 | PRIMARY      | lineitem    | NULL       | ref  | LINEITEM_FK2  | LINEITEM_FK2 | 4       | <subquery2>.column_0 |     27 |   100.00 | Using index condition |
|  2 | MATERIALIZED | <derived3>  | NULL       | ALL  | NULL          | NULL         | NULL    | NULL                 | 100000 |   100.00 | NULL                  |
|  3 | DERIVED      | NULL        | NULL       | NULL | NULL          | NULL         | NULL    | NULL                 |   NULL |     NULL | No tables used        |
+----+--------------+-------------+------------+------+---------------+--------------+---------+----------------------+--------+----------+-----------------------+

性能測試

測試一：使用sysbench模型測試
1.準備5000w數據：

sysbench /usr/share/sysbench/oltp_read_only.lua --tables=1 --report-interval=10 --table-size=50000000  --mysql-user=root --mysql-password=123456 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sbtest --time=300 --max-requests=0  --threads=200   prepare

2.查詢帶10w個常量值的語句，k字段上有索引：

SELECT COUNT(*) FROM sbtest1 WHERE k IN (207066596,1483256090,...);

3.結果對比：

開啓轉換耗時
0.42s

關閉轉換（不使用range optimizer）耗時
12.61s

性能對比
提升約30倍

測試二：使用TPC-H數據集測試
1.準備TPC-H 10GB標準測試數據集；
2.詢帶10w個常量值的語句，l_partkey 字段上有索引：

SELECT sum(l_extendedprice) / 7.0 AS avg_yearly FROM lineitem WHERE l_partkey IN (207066596,1483256090,...);

3.結果對比：
開啓轉換耗時
0.29s

關閉轉換（不使用range optimizer）耗時
20.01s

性能對比
提升約60倍

結論

百度智能雲GaiaDB 通過將大 IN 列表智能轉換為 IN 子查詢，結合 semi-join 物化策略，有效克服了傳統 MySQL 在處理大列表時的內存與優化限制。在實際測試中，查詢性能提升顯著，尤其適用於高併發、大數據量的在線業務場景。該功能無需業務改造，僅通過參數即可控制，是 百度智能雲GaiaDB 在查詢優化方面的重要增強。

算法
Favorites

Add a new Comments

Some HTML is okay.