作者:盧文雙 資深資料庫內核研發 本文首發於 2024-03-06 20:52:24 https://dbkernel.com 前言 計算下推是資料庫優化器優化查詢性能的一種常見手段,早期的資料庫系統提及的計算下推一般是指謂詞下推,其理論源自關係代數理論。2000 年以後,隨著 Oracle RAC ...
作者:盧文雙 資深資料庫內核研發
本文首發於 2024-03-06 20:52:24
前言
計算下推是資料庫優化器優化查詢性能的一種常見手段,早期的資料庫系統提及的計算下推一般是指謂詞下推,其理論源自關係代數理論。2000 年以後,隨著 Oracle RAC 的盛行以及一眾開源分散式資料庫的崛起,存算分離的概念逐步流行,計算下推的涵蓋範圍由此從基本的謂詞+投影下推延伸到了資料庫所支持的一切可能計算的下推(JOIN、聚合、完整 query、部分 query 等)。
對於單機資料庫來說,尤其是 MySQL 這種採用經典火山模型的關係型資料庫,最常見的就是謂詞下推、投影下推,通常在查詢優化的 RBO 階段完成(有的下推在 CBO 階段),通過將 Filter 和 Project 運算元在抽象語法樹(AST)中向下移動,提前對行/列進行裁剪,減少後續計算的數據量。
當然,MySQL 中不僅僅是謂詞下推、投影下推,還有條件下推、ICP 等,本文就盤點一下 MySQL 生態中有哪些計算下推。
MySQL 原生方案
本小節介紹 MySQL 社區版中的計算下推方案。
1. 索引條件下推 ICP
功能介紹
ICP(Index Condition Pushdown,索引下推),是 MySQL 5.6 版本推出的功能,用於優化 MySQL 查詢。
ICP 可以減少存儲引擎查詢回表的次數以及 MySQL server 層訪問存儲引擎的次數。
ICP 的目標是減少整行記錄讀取的次數,從而減少 I/O 操作。
在沒有使用 ICP 的情況下,索引(二級索引)掃描的過程如下:
- 存儲引擎讀取二級索引記錄;
- 根據二級索引中的主鍵值,定位並讀取完整行記錄(回表);
- 存儲引擎把記錄交給 Server 層去檢測該記錄是否滿足 where 條件。
在使用 ICP 的情況下,查詢優化階段會將部分或全部 where 條件下推,其掃描過程如下:
- 存儲引擎讀取二級索引記錄(不是完整行);
- 判斷當前二級索引列記錄是否滿足下推的 where 條件:
- 如果條件不滿足,則跳過該行,繼續處理下一行索引記錄;
- 如果條件滿足,使用索引中的主鍵去定位並讀取完整的行記錄(回表);
- 存儲引擎把記錄交給 Server 層,Server 層檢測該記錄是否滿足 where 條件的其餘部分。
適用場景:
- 單列/多列二級索引上的非範圍掃描(比如 like)。示例:(c3) 是單列二級索引,
with index condition
部分就是 ICP 下推的條件
- where 條件不滿足最左匹配原則的多列二級索引掃描。示例:(c2, c3, c4) 是多列二級索引,指定多列範圍,c4>5 範圍無法下推到引擎層掃描範圍
QUICK_RANGE_SELECT::ranges
使用限制 & 適用條件:
- 當需要訪問全表記錄時,ICP 可用於 range(範圍掃描)、ref(非唯一索引的"="操作)、eq_ref(唯一索引的"="操作) 和 ref_or_null(ref + 支持空值,比如:WHERE col = ... OR col IS NULL) 訪問方法。
- ICP 可以用於 InnoDB 和 MyISAM 引擎表(包括分區表)。
- 對於 InnoDB 表,ICP 僅支持二級索引。而對於 InnoDB 聚簇索引,由於完整的記錄會被讀到 InnoDB 緩衝區,在這種情況下,使用 ICP 不會減少 I/O 操作。
- 虛擬列上創建的二級索引不支持 ICP。
- 使用子查詢的 where 條件不支持 ICP。
- 由於引擎層無法調用位於 server 層的存儲過程,因此,調用存儲過程的 SQL 不支持 ICP。
- 觸發器不支持 ICP。
開關(預設開啟):
SET optimizer_switch = 'index_condition_pushdown=off'; -- 關閉 ICP
SET optimizer_switch = 'index_condition_pushdown=on'; -- 啟用 ICP
性能影響:
示例
準備:
// 表結構(是否顯示設置id為主鍵,對性能沒什麼影響,但執行計劃不同)
CREATE TABLE `icp` (
`id` int DEFAULT NULL,
`age` int DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`memo` varchar(600) DEFAULT NULL,
KEY `age_idx` (`age`,`name`,`memo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
for((i=0;i<$rows_num;i++))
do
mysql -u$user -h$host -P$port -e"insert into $db.$tb values($i, 1, 'a$i', repeat('a$i', 100))"
done
// 將其中三行數據的age從1改為2
mysql> update icp2 set age=2 where id=10 or id=20 or id=20000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
// 表數據行數
mysql> select count(*) from icp;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.41 sec)
mysql> select count(*) from icp where age=1;
+----------+
| count(*) |
+----------+
| 99997 |
+----------+
1 row in set (6.37 sec)
啟用 ICP:
// 啟用 ICP
mysql> set optimizer_switch="index_condition_pushdown=on";
mysql> show session status like '%handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 3 |
| Handler_read_key | 105 |
| Handler_read_last | 0 |
| Handler_read_next | 139 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 7 |
+-----------------------+-------+
7 rows in set (0.01 sec)
mysql> select * from icp where age = 1 and memo like '%9999%'; // 結果集19行,耗時2.41s
mysql> show session status like '%handler_read%'; // read_key + 1,read_next + 19
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 3 |
| Handler_read_key | 106 |
| Handler_read_last | 0 |
| Handler_read_next | 158 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 7 |
+-----------------------+-------+
7 rows in set (0.01 sec)
mysql> explain analyze select * from icp where age = 1 and memo like '%9999%'\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on icp using age_idx (age=1), with index condition: (icp.memo like '%9999%') (cost=5432.07 rows=45945) (actual time=219.708..1973.586 rows=19 loops=1) // 只需要掃描19行
1 row in set (1.97 sec)
禁用 ICP:
// 禁用ICP
mysql> set optimizer_switch="index_condition_pushdown=off";
mysql> select * from icp where age = 1 and memo like '%9999%'; // 該表總數據行數1萬,結果集19行,耗時12.05s
mysql> show session status like '%handler_read%'; // read_key + 1,read_next + 99997
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 3 |
| Handler_read_key | 107 |
| Handler_read_last | 0 |
| Handler_read_next | 100155 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 7 |
+-----------------------+--------+
7 rows in set (0.00 sec)
mysql> explain analyze select * from icp where age = 1 and memo like '%9999%'\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (icp.memo like '%9999%') (cost=5432.07 rows=5104) (actual time=1415.435..12850.675 rows=19 loops=1)
-> Index lookup on icp using age_idx (age=1) (cost=5432.07 rows=45945) (actual time=0.259..11118.374 rows=99997 loops=1)
// 需要先在引擎層執行索引掃描所有age=1的記錄 99997 行並回表得到完整行,再由server層根據 memo like 條件過濾出 19 行
1 row in set (12.86 sec)
結論
由以上測試情況可以看到,在二級索引是複合索引且前面的條件過濾性較低的情況下,打開 ICP 可以有效的降低 server 層和 engine 層之間交互的次數,從而有效的降低運行時間(從 12.86s 降低到 1.97s),但是,對於多個普通單列索引構成的 where 過濾條件,無論是否啟用 ICP,優化器都會將過濾性高的索引條件下推到 engine 層執行 index range scan,因此,收益不大。
// 開啟 ICP,下推 t1.a > 3,掃描 4096 行
mysql> explain analyze select * from t1 where b>1 and a>3\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.b > 1) (cost=1843.46 rows=2048) (actual time=0.389..281.599 rows=4096 loops=1)
-> Index range scan on t1 using idx_a, with index condition: (t1.a > 3) (cost=1843.46 rows=4096) (actual time=0.385..278.402 rows=4096 loops=1)
// 關閉 ICP,優化器判定 t1.a 過濾性更強,按 idx_a 執行 Index range scan,也是掃描 4096 行
mysql> explain analyze select * from t1 where b>1 and a>3\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((t1.b > 1) and (t1.a > 3)) (cost=1843.46 rows=2048) (actual time=0.762..224.012 rows=4096 loops=1)
-> Index range scan on t1 using idx_a (cost=1843.46 rows=4096) (actual time=0.748..218.553 rows=4096 loops=1)
2. 引擎條件下推 ECP
ECP(Engine Condition Pushdown,引擎條件下推),該優化只支持 NDB 存儲引擎,用於提高非索引列和常量之間直接比較的效率,在這種情況下,條件被下推到存儲引擎做計算。
mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 2
Extra: Using where with pushed condition
對於 NDB 集群,這種優化可以消除在 集群的數據節點 和 發出查詢的 MySQL 伺服器 之間通過網路發送不匹配的行的資源浪費。
3. 派生條件下推 DCP
功能介紹
DCP(Derived Condition Pushdown,派生表條件下推),從 MySQL 8.0.22 版本開始引入。
對於 SQL 語句:
SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant
在很多情況下可以將外部的 WHERE 條件下推到派生表,相當於 SQL 改寫為了:
SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt
這減少了派⽣表返回的⾏數,從⽽加快查詢的速度。
適用場景
DCP 適用於以下情況:
1、當派生表不使用聚合函數或視窗函數時,外部 WHERE 條件可以直接下推給它,包括具有多個謂詞與 AND、OR 或與二者同時連接的 WHERE 條件。比如:查詢
SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11
被重寫為
SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt
2、當派生表具有 GROUP BY 且未使用視窗函數時,如果外部 WHERE 條件引用了一個或多個不屬於 GROUP BY 的列,那麼該 WHERE 條件可以作為 HAVING 條件下推到派生表中。比如:查詢
SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100
被重寫為
SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt
3、當派生表使用一個 GROUP BY 且外部 WHERE 條件中的列就是 GROUP BY 的列時,引用這些列的 WHERE 條件可以直接下推到派生表中。比如:查詢
SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10
被重寫為
SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt
4、如果外部 WHERE 條件中同時包含了第 2 種與第 3 種的情況,即同時具有”引用屬於 GROUP BY 的列的謂詞“ 和 ”引用不屬於 GROUP BY 的列的謂詞“,則第一種謂詞作為 WHERE 條件下推,第二種謂詞下推後作為 HAVING 條件。比如:查詢
SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 AND sum > 100
被重寫為類似如下形式的 SQL
SELECT * FROM (
SELECT i, j, SUM(k) AS sum FROM t1
WHERE i > 10 // 第一種
GROUP BY i, j
HAVING sum > 100 // 第二種
) AS dt;
使用限制
DCP 也存在如下使用限制:
- 如果派生表包含 UNION,不能使用 DCP 。該限制在 MySQL 8.0.29 基本被取消了,但以下兩種情況除外:
- 如果 UNION 的任何派生表是 recursive common table expression ,則不能將條件下推到 UNION 查詢。
- 不能將”包含不確定表達式的條件“下推到派生表中。
- 派生表不能使用 limit 子句。
- 包含子查詢的條件不能被下推。
- 如果派生表是外部 join 的 inner table,不能使用 DCP。
- 如果派生表是一個 common table expression 並且被多次引用,則不能將條件下推到該派生表。
- 如果條件的形式是
derived_column > ?
,可以下推使用參數的條件。但是,If a derived column in an outer WHERE condition is an expression having a ? in the underlying derived table, this condition cannot be pushed down.
開關(預設開啟):
set optimizer_switch="derived_condition_pushdown=on";
set optimizer_switch="derived_condition_pushdown=off";
源碼分析可參考:
- MySQL · 性能優化 · 條件下推到物化表:http://mysql.taobao.org/monthly/2016/07/08/
4. 謂詞下推
何為謂詞?
P : X→ {true, false} called a predicate on X .
A predicate is a function that returns bool (or something that can be implicitly converted to bool)
謂詞是返回 bool 型(或可隱式轉換為 bool 型)的函數。
一般來說,where 中的條件單元都是謂詞:
- =, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL
- <>, IN, OR, NOT IN, NOT LIKE
謂詞下推是指將查詢語句中的過濾表達式儘可能下推到距離數據源最近的地方做計算,以儘早完成數據的過濾,進而顯著地減少數據傳輸或計算的開銷。
- 下推前:select count(1) from t1 A join t3 B on A.a = B.a where A.b > 100 and B.b > 100;
- 下推後:select count(1) from (select * from t1 where a>100) A join (select * from t3 where b<100) B on A.a = B.a;
MySQL/PG 優化器會自動做謂詞下推的優化,比如:
mysql> explain analyze select count(1) from t1 A join t3 B on A.a = B.c where A.b > 100 and B.b > 100\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(1) (cost=366.05 rows=331) (actual time=23.188..23.189 rows=1 loops=1)
-> Nested loop inner join (cost=332.95 rows=331) (actual time=1.421..22.925 rows=302 loops=1)
-> Filter: ((b.b > 100) and (b.c is not null)) (cost=101.25 rows=662) (actual time=1.172..6.911 rows=662 loops=1)
-> Table scan on B (cost=101.25 rows=1000) (actual time=0.535..6.242 rows=1000 loops=1)
-> Filter: (a.b > 100) (cost=0.25 rows=0) (actual time=0.023..0.024 rows=0 loops=662)
-> Single-row index lookup on A using PRIMARY (a=b.c) (cost=0.25 rows=1) (actual time=0.022..0.023 rows=1 loops=662)
5. Secondary Engine - HTAP
問:該特性是什麼版本引入的?
從手冊中對
SECONDARY_LOAD
的說明以及代碼提交記錄時間點,是在8.0.13引入的 Secondary Engine。之後,release log 中直到 8.0.19 版本才有相關 bug 修複記錄。
引入 Secondary Engine,用於支持多引擎(建表語句中的 Engine 為 Primary Engine),使用示例如下:
INSTALL PLUGIN mock SONAME "ha_mock.so";
CREATE TABLE `se` (
`id` int DEFAULT NULL,
`age` int DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`memo` varchar(600) DEFAULT NULL,
KEY `age_idx` (`age`,`name`,`memo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 SECONDARY_ENGINE=MOCK;
// 系統變數
SET @use_secondary_engine= "ON";
SET @use_secondary_engine= "OFF";
SET @use_secondary_engine= "FORCED";
// 載入和卸載數據
ALTER TABLE SE SECONDARY_LOAD;
ALTER TABLE SE SECONDARY_UNLOAD;
在支持 InnoDB 的同時,還可以把數據存放在其他的存儲引擎上。 全量的數據都存儲在 Primary Engine 上,某些指定表數據在 Secondary Engine 上也存放了一份,然後在訪問這些數據的時候,會根據系統參數和 cost 選擇存儲引擎,提高查詢效率。
MySQL 官方集成了 RAPID 來為 MySQL 提供實時的數據分析服務,即HeatWave,同時支持 InnoDB 和 RAPID 執行引擎(未開源),也就是 HTAP。
不過,開源 MySQL 引入 Secondary Engine 機制,有助於集成其他存儲引擎或者資料庫,開源生態中StoneDB就是基於該特性來實現的 HTAP。
第三方方案
本小節只介紹 RDS 範疇的計算下推,對於 PolarDB、Aurora 這種存算分離架構不做講述。
1. limit/offset、sum 下推
騰訊雲 TXSQL(騰訊自研 MySQL 分支)支持了 limit/offset、sum 下推。
功能介紹
該功能將單表查詢的 LIMIT/OFFSET 或 SUM 操作下推到 InnoDB,有效降低查詢時延。
- LIMIT/OFFSET 下推到二級索引時,該功能將避免“回表”操作,有效降低掃描代價。
- SUM 操作下推到 InnoDB 時,在 InnoDB 層進行計算返回“最終”結果,節省 Server 層和 InnoDB 引擎層多次迭代“每行”記錄的代價。
適用場景
該功能主要針對單表查詢下存在 LIMIT/OFFSET 或 SUM 的場景,如 Select * from tbl Limit 10、Select * from tbl Limit 10,2
、Select sum(c1) from tbl
等語句。
無法優化的場景:
- 查詢語句存在 distinct、group by、having。
- 存在嵌套子查詢。
- 使用了 FULLTEXT 索引。
- 存在 order by 並且優化器不能利用 index 實現 order by。
- 使用多範圍的 MRR。
- 存在 SQL_CALC_FOUND_ROWS。
個人理解:
下推的前提是不能影響結果集的正確性,因此:
- 只能支持單表查詢
- where 條件:
- 若無 where 條件,也可支持單表的全表掃描(Table Scan)
- 若有 where 條件,則必須滿足只對一條索引做範圍掃描即可覆蓋全部 where 條件才可下推,反之,則不能下推
- 不支持全文索引這種特殊的索引
- 若存在無法被優化器消除的 distinct、group by、having、order by,則不能下推
- 由於 MRR 機制、SQL_CALC_FOUND_ROWS 語法的特殊性,下推的收益不大
性能數據
sysbench 導入一百萬行數據後:
- 執行
select * from sbtest1 limit 1000000,1;
的時間從 6.3 秒下降到 2.8 秒。- 對於高併發、二級索引掃描且需回表主鍵列的情況,收益會更大,可能有 8 倍以上的提升。
- 執行
select sum(k) from sbtest1;
的時間從 5.4 秒下降到 1.5 秒。
機制(個人理解)
無論是 limit/offset 下推,還是 sum 下推,都借鑒了 ICP 的機制,思路大同小異。這裡以 offset 為例,說下我的理解:
- 在 Server 層做查詢優化時,為了避免下推後導致結果集有誤,需先判斷是否滿足下推條件(單表查詢、InnoDB 引擎、非「無法優化的場景」),若滿足,則將 offset 條件下推到引擎層,同時屏蔽掉 Server 層的 offset 邏輯。
- 若下推了 offset 運算元,比如 offset 100,則需要在引擎層跳過 100 行,後續邏輯與下推前相同。
個人對 offset 下推的理解:
延伸
問:其他聚合函數是否可以下推優化?
從官方手冊( https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html)支持的聚合函數來看:
- 至少
AVG()
也是可以很容易支持的。 - 對於
COUNT()
函數,由於 MySQL 8.0 支持了並行掃描,暫時來看優化的意義不大。 - 對於
MIN()
、MAX()
函數,優化器會使用索引來優化,基本只掃描一行即可,無下推必要。 - 其他聚合函數不太常用,下推優化意義不大。
總結
正文說的很清晰了,就不強行總結了(^_^)
參考
- MySQL ICP: https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
- MySQL ECP:https://dev.mysql.com/doc/refman/8.0/en/engine-condition-pushdown-optimization.html
- MySQL DCP:https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html
- Secondary Engine:http://mysql.taobao.org/monthly/2020/11/04/
- 騰訊自研內核 TXSQL 計算下推功能:https://cloud.tencent.com/document/product/236/63445
歡迎關註我的微信公眾號【資料庫內核】:分享主流開源資料庫和存儲引擎相關技術。
標題 | 網址 |
---|---|
GitHub | https://dbkernel.github.io |
知乎 | https://www.zhihu.com/people/dbkernel/posts |
思否(SegmentFault) | https://segmentfault.com/u/dbkernel |
掘金 | https://juejin.im/user/5e9d3ed251882538083fed1f/posts |
CSDN | https://blog.csdn.net/dbkernel |
博客園(cnblogs) | https://www.cnblogs.com/dbkernel |