技術文章 » offset新探索：雙管齊下，加速巨量資料量查詢

offset新探索：雙管齊下，加速巨量資料量查詢

2022-10-27 12:01:21
摘要：隨著offset的增加，查詢的時長也會越來越長。當offset達到百萬級別的時候查詢時長通常是業務所不能容忍的。

本文分享自華為雲社群《offset新探索：雙管齊下，加速巨量資料量查詢》，作者： GaussDB 資料庫 。

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

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

  • offset Pushdown( offset下推，下文簡稱OP)
  • 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的極限偏移邏輯

圖2啟用OP的LIMIT 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語句只涉及一張表

另外：

  • 查詢中使用的表必須是InnoDB表
  • 不使用HAVING, aggregations, GROUP BY, SELECT DISTINCT, ROLLUP, Window functions以及檔案排序
  • 不支援涉及多個分割區的分割區表查詢

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

如何啟用OP？

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

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

預設為on。

方法二：使用特定的優化器hint：[NO]_OFFSET_PUSHDOWN[TC1] ()

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 * fromt1 limit 100 offset 1;+----------------------------------------------------------------------------------------------------------------------+| EXPLAIN|+----------------------------------------------------------------------------------------------------------------------+| -> Limit/Offset: 100/1row(s), with offset pushdown (cost=0.65 rows=4) -> Tablescan on t1  (cost=0.65 rows=4)|+----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
範例二：二級索引上的索引範圍掃描
explain format=tree select a,b from t1 where b>2limit 100 offset 1;+------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN|+------------------------------------------------------------------------------------------------------------------------------------+| -> Limit/Offset: 100/1row(s), with offset pushdown (cost=1.61 rows=3) -> Indexrange scan on t1 using b  (cost=1.61rows=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：

explainformat=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 usinga  (cost=0.46 rows=1)|+---------------------------
可以看出：列a上的範圍條件會被InnoDB預設檢查，但SQL層將再次檢查InnoDB返回的行是否匹配列a的範圍條件。在這種情況下，無法使用OP，因為SQL層不知道儲存引擎實際上處理了整個WHERE子句。

啟用RCR：設定rds_empty_redundant_check_in_range_scan = true;

explainformat=tree select * from t0 where a<100 and a>20 LIMIT 1 OFFSET 100;+------------------------------------------------------------------------------------------------------------------------------------+|EXPLAIN|+------------------------------------------------------------------------------------------------------------------------------------+|-> Limit/Offset: 1/100 row(s), with offsetpushdown (cost=0.46 rows=1) -> Index range scan on t0 using a  (cost=0.46 rows=1)|+------------------------------------------------------------------------------------------------------------------------------------+1row in set (0.00 sec)
可以看出：啟用RCR，刪除SQL層對列A的範圍條件的冗餘檢查後，啟用OP。

簡化ICP

建立表t1：

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

不啟用RCR：

explainformat=tree select a,b from t1 where b>2 limit 100 offset 1;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+|EXPLAIN |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+|-> Limit/Offset: 100/1 row(s), with offsetpushdown (cost=1.61 rows=3) -> Index range scan on t1 using b, with index condition: (t1.b >2)  (cost=1.61 rows=3)|+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+1row in set (0.00 sec)
可以看出：使用了ICP後，OP也被啟用了

啟用RCR：

explainformat=tree select a,b from t1 where b>2 limit 100 offset 1;+------------------------------------------------------------------------------------------------------------------------------------+|EXPLAIN |+------------------------------------------------------------------------------------------------------------------------------------+|-> Limit/Offset: 100/1 row(s), with offsetpushdown (cost=1.61 rows=3) -> Index range scan on t1 using b  (cost=1.61 rows=3)|+------------------------------------------------------------------------------------------------------------------------------------+1row in set (0.00 sec)
以上範例說明：ICP不是必要的。通過評估是否應使用ICP之前移除冗餘條件，就可以避免使用ICP。

效能驗證

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

  • 覆蓋/非覆蓋索引

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

  • 熱/冷緩衝池

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

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

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

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

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

  • 緩衝池大小

對於覆蓋索引查詢，可以假定索引資料都在緩衝池中，因此，緩衝池的大小對效能不會產生太大影響。然而，對於非覆蓋索引的查詢，情況會大不相同。在不使用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次執行結果的中位數。

通過以上測試結果可以看出，在緩衝池大小為128MB的場景下，冷熱緩衝池對OP帶來的效能提升有不同影響：

熱緩衝池

  • 使用覆蓋索引，OP可以將查詢效能提升3 – 12倍；
  • 使用非覆蓋索引，OP可以將查詢效能提升 48 – 128倍

冷緩衝池

  • 使用覆蓋索引，OP可以將效能提升 40% - 8倍；
  • 使用非覆蓋索引，OP可以將效能提升2 - 148 倍

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

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

  • OP可以在儲存引擎層跳過offset 行，而不必將它們返回到SQL層，這將導致計算時間的顯著降低。
  • OP可以跳過offset 行，而不必從基表讀取它們，從而獲得效能提升。

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

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

本文作者

呂漫漪

華為斯德哥爾摩研究所資料庫Lab首席科學家，雲資料庫歐洲研發團隊負責人。2020年加入華為，致力於打造世界級的企業級雲資料庫。呂漫漪在資料庫領域有20多年從業經驗，曾參與開發電信行業分散式高可用資料庫，曾任MySQL原廠研發團隊產品總監，長期深耕資料庫技術。

Maxime Conjard

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

 

點選關注，第一時間瞭解華為雲新鮮技術~