offset新探索:雙管齊下,加速大資料量查詢

IT科技蘇辭 發表於 2022-11-24

offset 新探索:雙管齊下,加速大資料量查詢

offset新探索:雙管齊下,加速大資料量查詢

 

眾所周知,在各類業務中時常會用到LIMIT y offset x來做跳過x條資料讀取Y條資料的操作。例如: SELECT * FROM ... LIMIT 1000 OFFSET 1000000; 表示從第1000001條資料開始查,讀取1000條資料。隨著offset的增加,查詢的時長也會越來越長。當offset達到百萬級別的時候查詢時長有可能秒級,這是業務所不能容忍的。

 

那麼如何來提升 offset 在大資料量查詢時的效能、縮短執行時間呢?我們的答案是:

l   offset Pushdown( offset 下推,下文簡稱 OP)

l   Redundant Condition Removal ( 冗餘條件刪除,下文簡稱 RCR)

這是華為雲 GaussDB for MySQL 推出的兩個新特性,透過 OP RCR 的結合,將大資料量查詢的效能提升一到兩個數量級。下面我們分別介紹這兩個特性的基本原理、如何啟用、執行驗證、以及透過嚴密測試來驗證其帶來的效能提升。

 

Offset Pushdown -- OP

OP 賦予 MySQL 儲存引擎 InnoDB 處理 offset 的能力。當 OP 啟用時,在 SQL 層評估 offset 是否可以下推並將下推資訊傳遞給儲存引擎。 SQL 層不再對儲存引擎返回的行進行 offset 處理,取而代之的是儲存引擎層直接跳過 offset 範圍內的行,僅返回後續行,即查詢所需要的行。

 

透過啟用 OP offset 範圍內的行不會再傳輸到 SQL 層,從而節省了儲存引擎和 SQL 層之間多次來回互動時間;其次,對非覆蓋索引掃描( non-covering index ,即查詢訪問二級索引之後,還必須訪問基表),直接跳過 offset 範圍內的行可以節省對這些行回表訪問的開銷。這種對 offset 的提前處理可以節省資料處理時間,特別是當 offset 非常大時。 OP 的適用性取決於 WHERE 子句是否可以由儲存引擎整體處理。

下方圖 1 2 分別說明了在沒有 OP 和啟用 OP LIMIT offset 的處理邏輯。

 

1 OP 的極限偏移邏輯

  offset新探索:雙管齊下,加速大資料量查詢

2 啟用 OP LIMIT offset 邏輯

 

  offset新探索:雙管齊下,加速大資料量查詢

Redundant Condition Removal – RCR

RCR 的思路也比較簡單:當進行索引範圍掃描時, SQL 層對儲存引擎返回的行執行冗餘檢查,因為它不知道儲存引擎已經執行了這些檢查,而 RCR 就是讓 SQL 層瞭解這點。為了使 OP 成為可能,除了要求 WHERE 條件能夠被儲存引擎獨立且完整的評估, SQL 層還必須瞭解這點從而避免冗餘檢查。

OP 功能的實現方式與索引條件下推 ( Index Condition Pushdown ICP) 類似。對於某些查詢, ICP 透過將整個 WHERE 子句下推到儲存引擎來啟用 OP 。而 RCR ICP 執行之前會評估條件是否冗餘,並且移除冗餘條件,確保了 ICP 不會處理冗餘的條件檢查。 RCR 很好地補充了 OP 特性的適用範圍,允許更多查詢使用 OP

 

請注意: OP 的啟用需要滿足三個主要條件:

1.          SQL 語句包含 offset

2.          WHERE 子句完全由 InnoDB 處理

3.          SQL 語句只涉及一張表

 

另外,

l   查詢中使用的表必須是 InnoDB

l   不使用 HAVING, aggregations, GROUP BY, SELECT DISTINCT, ROLLUP, Window functions 以及檔案排序

l   不支援涉及多個分割槽的分割槽表查詢,只涉及單個分割槽的可以

 

RCR 適用於索引範圍掃描,如果 WHERE 子句中出現了一個或者多個條件,而這些條件涉及到的欄位在對應使用的索引上是被連續定義的,這些條件的冗餘檢查就都會被移除。

 

如何啟用 OP

方法一 使用特定的 optimizer switch: offset _PUSHDOWN

set optimizer_switch='OFFSET_PUSHDOWN=[on]/[off]';

預設為開啟。

 

方法二 使用 特定的最佳化器 hint [NO]_OFFSET_PUSHDOWN()

SELECT /*+ [NO]_OFFSET_PUSHDOWN() */ FROM TABLE LIMIT n  OFFSET p;

 

請注意 hint 優先順序高於 optimizer switch 的設定。


我們基於下方建立的 t1 來舉例說明如何使用 OP

CREATE TABLE t1 (a int, b int, INDEX  (b));

示例一:表掃描

 

explain format=tree select * from t1 limit 100 offset 1;

+----------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                              |

+----------------------------------------------------------------------------------------------------------------------+

| -> Limit/Offset: 100/1 row(s), with offset pushdown  (cost=0.65 rows=4)

    -> Table scan on t1  (cost=0.65 rows=4)

 |

+----------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

示例二:二級索引上的索引範圍掃描

 

explain  format=tree select a,b from t1 where b>2 limit 100 offset 1;

+------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                            |

+------------------------------------------------------------------------------------------------------------------------------------+

| -> Limit/Offset: 100/1 row(s), with offset pushdown  (cost=1.61 rows=3)

    -> Index range scan on t1 using b  (cost=1.61 rows=3)

 |

+------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

如何啟用 RCR ?

透過系統變數 rds_empty_redundant_check_in_range_scan 設定,如下:

set rds_empty_redundant_check_in_range_scan=[true]/[false];

預設為 true

 

我們透過一個示例來說明:

建立 t0 表:

CREATE TABLE t0 (a int, b int, INDEX  (a,b));

 

不啟用 RCR

explain format=tree select * from t0 where a<100 and a>20  LIMIT 1 OFFSET 100;

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                            |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| -> Limit/Offset: 1/100 row(s)  (cost=0.46 rows=1)

    -> Filter: ((t0.a < 100) and (t0.a > 20))  (cost=0.46 rows=1)

        -> Index range scan on t0 using a  (cost=0.46 rows=1)

 |

+---------------------------

 

可以看出:列 a 上的範圍條件會被 InnoDB 預設檢查,但 SQL 層將再次檢查 InnoDB 返回的行是否匹配列 a 的範圍條件。在這種情況下,無法使用 OP ,因為 SQL 層不知道儲存引擎實際上處理了整個 WHERE 子句。

 

啟用 RCR 設定 rds_empty_redundant_check_in_range_scan = true;

 

explain format=tree select * from t0 where a<100 and a>20  LIMIT 1 OFFSET 100;

+------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                            |

+------------------------------------------------------------------------------------------------------------------------------------+

| -> Limit/Offset: 1/100 row(s), with offset pushdown  (cost=0.46 rows=1)

    -> Index range scan on t0 using a  (cost=0.46 rows=1)

 |

+------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

可以看出:啟用 RCR ,刪除 SQL 層對列 A 的範圍條件的冗餘檢查後,啟用 OP

 

 

簡化 ICP

建立表 t1

create table t1(a int, b int, INDEX(b));

 

不啟用 RCR

 

explain format=tree select a,b from t1 where b>2 limit 100 offset 1;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                              |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| -> Limit/Offset: 100/1 row(s), with offset pushdown  (cost=1.61 rows=3)

    -> Index range scan on t1 using b, with index condition: (t1.b > 2)  (cost=1.61 rows=3)

 |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

可以看出:使用了 ICP OP 也被啟用了

 

啟用 RCR

 

explain format=tree select a,b from t1 where b>2 limit 100 offset 1;

+------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                            |

+------------------------------------------------------------------------------------------------------------------------------------+

| -> Limit/Offset: 100/1 row(s), with offset pushdown  (cost=1.61 rows=3)

    -> Index range scan on t1 using b  (cost=1.61 rows=3)

 |

+------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

以上示例說明: ICP 不是必要的。透過評估是否應使用 ICP 之前移除冗餘條件,就可以避免使用 ICP

 

 

效能驗證:

下面我們透過實際測試來驗證OP所帶來的效能提升。在測試中 ,我們重點關注:

l   覆蓋 / 非覆蓋索引

考慮一個非覆蓋索引,不使用 OP InnoDB 必須從基表讀取行,然後才能將它們返回到 SQL 層。使用 OP 後,就可以跳過行,而不必從基表讀取。因此, OP 在非覆蓋索引上可以提供更好的效能。

l   / 冷緩衝池

我們希望透過熱緩衝池全面提高效能,但我們也希望 OP 在熱緩衝池上相對更高效,原因如下:

基於一個冷緩衝池並且查詢使用覆蓋索引掃描的場景,設定 為不使用 OP 的計算時間( )和使用 OP 的計算時間 ( ) 的比值:

 

 

比值 預計將大於 1 ,因為使用 OP 將獲得效能提升。基於一個熱緩衝池並且查詢使用覆蓋索引掃描的場景,設定 是不使用 OP 的計算時間 ( 和使用 OP 的計算時間( 的比值:

其中 表示從磁碟讀取索引所需的時間,可以合理地假設,在使用 OP 和不使用 OP 的情況下, 都是相同的。因為不論是否使用 OP ,都必須從左到右遍歷索引,無法在使用 OP 的情況下,利用 B-tree 結構索引的優勢直接跳轉到 offset 範圍的結束點。

那麼,這兩個比值的差值可以表述為:

因此,我們預計 OP 在熱緩衝池將更有效。

l   緩衝池大小

對於覆蓋索引查詢,可以假定索引資料都在緩衝池中,因此,緩衝池的大小對效能不會產生太大影響。然而,對於非覆蓋索引的查詢,情況會大不相同。在不使用 OP 時,緩衝池能快取表資料的比例確實會對查詢的效能產生有利的影響。

 

基於以上三個關注點以及預判,我們在一個包含 200 萬行資料的測試表中,分別測試覆蓋 / 非覆蓋索引、冷 / 熱緩衝池、不同緩衝池大小下條件下,透過 OP 帶來的效能表現。

測試語句:

覆蓋索引查詢:

CREATE TABLE data (id int, value int, INDEX  (id,value));

SELECT * FROM data   LIMIT 1 OFFSET p;

 

非覆蓋索引查詢:

CREATE TABLE data_non_covering(id INT, value INT, INDEX  (value));

INSERT INTO data_non_covering SELECT * FROM data;

SELECT * FROM data_non_covering WHERE value>2 LIMIT 1 OFFSET p;

為了過濾干擾,計算時間是取 9 次執行結果的中位數。

  offset新探索:雙管齊下,加速大資料量查詢

                                      

透過以上測試結果可以看出:

熱緩衝池,並將其大小設為 128MB

l   使用覆蓋索引, OP 可以將查詢效能提升 3 – 12 倍;

l   使用非覆蓋索引, OP 可以將查詢效能提升 48 – 128

 

冷緩衝池,並將其大小設定為 128MB

l   使用覆蓋索引, OP 可以將效能提升 40% - 8 倍;

l   使用非覆蓋索引, OP 可以將效能提升 2 - 148

 

綜上,在所有測試中,使用 OP 能提升查詢效能。不論是冷緩衝池還是熱緩衝池,啟用 OP 後,非覆蓋索引掃描可以比覆蓋索引掃描獲得 10 倍以上的效能提升。此外,正如我們所預計,在熱緩衝池上啟用 OP 獲得了更大的效能提升。

對於大的 OFFSET ,使用 OP 可將效能提高一兩個數量級,而 RCR 可擴大了 OP 的適用範圍。正如上述測試所證明,使用 OP 所帶來的效能提升主要受下面兩個因素的影響:

l   OP 可以在儲存引擎層跳過 offset 行,而不必將它們返回到 SQL 層,這將導致計算時間的顯著降低。

l   OP 可以跳過 offset 行,而不必從基表讀取它們,從而獲得效能提升。

 

OP RCR 的聯合使用 進一步擴大了 OP 的使用範圍 可以為更多的 Limit/offset 查詢帶來效能提升,尤其是對大的 offset 操作。

 

在後續的研究中 我們將會評估 OP NDP Near Data Processing, 近資料處理 的相容性以及潛在的效能改進。

 

 

本文作者

 

呂漫漪

現任華為瑞典研究所資料庫Lab 首席科學家,雲資料庫歐洲研發團隊的負責人。在資料庫領域有20 多年經驗,曾經參與開發電信行業分散式高可用資料庫,在國際知名軟體公司深耕了十年MySQL 技術。2020 年加入華為,立志於打造世界頂端的企業級雲資料庫。

 

Maxime Conjard

華為雲資料庫工程師,就職於華為雲資料庫歐洲研發團隊。Max 畢業於挪威科技大學(NTNU ),獲得統計學碩士和博士學位;在此之前,他在法國馬賽中央學院獲得工程碩士學位。

 

 


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70022886/viewspace-2924955/,如需轉載,請註明出處,否則將追究法律責任。