GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速

華為雲開發者聯盟發表於2022-07-01
MySql快取
摘要：華為雲資料庫高階核心技術專家詳解GaussDB(for MySQL)Partial Result Cache特性，如何通過快取中間結果對運算元進行加速？

本文分享自華為雲社群《GaussDB創新特性解讀：Partial Result Cache，通過快取中間結果對運算元進行加速》，作者：GaussDB 資料庫 。

GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速

為了加速查詢效能，傳統的關係型資料庫，比如Oracle、DB2，都有結果集快取的特性，用來快取一條查詢語句的結果集。如果後續同樣的語句被查詢，資料庫將直接從結果集快取中獲取結果，而不用再重新執行該查詢。MySQL 在4.0版本中也引入了結果集快取Query cache，但是在設計上有侷限性，具體如下：

  1. Query cache針對單個查詢，任何一個表做了修改，如果影響到結果集就需要重新整理或者失效。
  2. Query cache對隔離級別有依賴，不同的隔離級別產生的結果集不一樣。
  3. Query cache需要對所有資料進行快取，如果表結果比較大的話，快取需要佔據較大的記憶體或者寫入磁碟。

這也導致了該特性在MySQL 8.0版本被移除。

鑑於結果集快取對查詢效能的增益，我們在GaussDB(for MySQL)引入Partial result cache這一新特性，簡稱PTRC。顧名思義，這也是一個結果集快取特性。不同於傳統的結果集快取，PTRC是用來輔助單個查詢的內部運算元的執行。也就是說PTRC粒度更小，是對查詢內部的某個運算元的中間結果進行快取，從而起到運算元加速的作用。

這裡的Partial 有兩層概念：

GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速

從這兩點可以看出，PTRC是與單個查詢相關的，生命週期從查詢開始到查詢結束，自動終止。由於它是對運算元進行加速，所以一個查詢內部可以有多個PTRC。只要優化器根據代價計算，認為該運算元適合PTRC，那麼優化器就會為該運算元引入PTRC。

PTRC如何確定對運算元並加速？

這裡我們引入一個新概念：引數化的重複掃描，指的是掃描運算元根據引數的不同進行運算元掃描。比如Nested Loop Join，對於外表掃描的每一條資料，內表會根據JOIN條件進行掃描，那麼對於內表來說就是一次“引數化的重複掃描”。再比如correlated subquery，對於父查詢的每一次掃描都會根據父查詢的結果集呼叫子查詢執行，然後返回子查詢的結果集。

PTRC是如何工作的？

如前所述，PTRC是快取運算元的中間結果集，那麼也和其他cache一樣，將資料以key ，value的方式快取到cache中，通過key來命中，得到value。那麼PTRC的相關key和value是如何獲取的？

下面我們以Correlated subquery為例做簡單分析，查詢語句如下：

SELECT *
FROM   t1
WHERE  t1.a IN (SELECT a
                FROM   t2,
                       t3
                WHERE  t2.b = t1.b
                       AND t2. c > t3.d);
GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速

上圖是子查詢使用EXISTS策略執行的流程圖。可以看出：對於資料表t1中的每一條資料，都會驅動子查詢執行，直到資料表t1中的所有記錄都迴圈結束。對於資料表t1中的每一條記錄對應的t1.a，都需要根據該列值重新掃描子查詢，進而判斷子查詢的返回值。

我們通過EXPLAIN來對比引入PTRC前後執行計劃的差異：

EXPLAIN format=tree

SELECT *
FROM   t1
WHERE  t1.a IN (SELECT a
                FROM   t2,
                       t3
                WHERE  t2.b = t1.b
                       AND t2. c > t3.d);

 

-> Filter: <in_optimizer>(t1.a,<exists>(select #2))  (cost=0.35 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Select #2 (subquery in condition; dependent)
        -> Result cache : cache keys(t1.a, t1.b)
            -> Limit: 1 row(s)  (cost=0.80 rows=1)
                -> Filter: (t2.c > t3.d)  (cost=0.80 rows=1)
                    -> Inner hash join (no condition)  (cost=0.80 rows=1)
                        -> Table scan on t3  (cost=0.35 rows=2)
                        -> Hash
                            -> Filter: ((t2.b = t1.b) and (<cache>(t1.a) = t2.a))  (cost=0.35 rows=1)
                                -> Table scan on t2  (cost=0.35 rows=1)

可以看出引入PTRC後，多了一個運算元Result cache（標紅部分），表明該運算元當前的子查詢引入了PTRC，引入後的執行流程變更為：

GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速

引入PTRC後，對於資料表t1中的每一條資料對應的t1.a列值，優先檢視PTRC，如果命中，直接從PTRC中獲取結果集，而不需要執行子查詢。如果未命中，需要按原來的方式繼續執行子查詢，子查詢執行的結果會儲存到PTRC中。如果下一次同樣的列值來驅動執行子查詢，可以直接從PTRC獲取。

優化器如何選擇PTRC？

優化器在為運算元選擇PTRC的時候會依賴代價估算，主要是看命中率（命中率 = 不同鍵值的行數/鍵值的總行數）， 如果命中率大於rds_partial_result_cache_cost_threshold（具體含義參考下文的系統變數介紹）變數定義的最小代價，PTRC將會被選擇，反之則不會被選擇。是否選擇了PTRC，可以通過Explain format=tree或者Explain analyze來觀察實際的命中情況。我們通過一個例子來說明：

EXPLAIN analyze

SELECT *
FROM   t1
WHERE  t1.a IN (SELECT a
                FROM   t2,
                       t3
                WHERE  t2.b = t1.b
                       AND t2. c > t3.d);

 

-> Filter: <in_optimizer>(t1.a,<exists>(select #2))  (cost=0.35 rows=1) (actual time=3800.595..3800.595 rows=0 loops=1)
    -> Table scan on t1  (cost=0.35 rows=1) (actual time=0.064..0.093 rows=1 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Result cache : cache keys(t1.a, t1.b) (Cache Hits: 0, Cache Misses：1, Cache Evictions: 0, Cache Overflows: 0, Memory Usage: 40960 )  (actual time=0.115..0.115 rows=0 loops=1)
            -> Limit: 1 row(s)  (cost=0.80 rows=1) (actual time=0.094..0.094 rows=0 loops=1)
                -> Filter: (t2.c > t3.d)  (cost=0.80 rows=1) (actual time=0.093..0.093 rows=0 loops=1)
                    -> Inner hash join (no condition)  (cost=0.80 rows=1) (actual time=0.092..0.092 rows=0 loops=1)
                        -> Table scan on t3  (cost=0.35 rows=2) (never executed)
                        -> Hash
                            -> Filter: ((t2.b = t1.b) and (<cache>(t1.a) = t1.a))  (cost=0.35 rows=1) (actual time=0.039..0.039 rows=0 loops=1)
                                -> Table scan on t2  (cost=0.35 rows=1) (actual time=0.038..0.038 rows=0 loops=1)

從Result cache這個運算元後面可以看到：

  • Cache Hits: 0，表示命中的次數為0
  • Cache Misses：1，表示沒有命中的次數為1
  • Cache Evictions: 0，表示使用LRU淘汰的記錄數
  • Cache Overflows: 0，表示記憶體overflow的次數
  • Memory Usage: 40960，表示當前查詢使用的記憶體量

由於優化器使用代價估算來計算是否使用PTRC，如果估算錯誤的話，PTRC還是有額外的代價，比如建立自身的一些資料結構，以及記錄的拷貝。為了儘可能的保證查詢的效能，PTRC採取了動態反饋的方式來檢視PTRC在實際執行的過程中是否繼續使用。PTRC會自動判斷命中率是否適合保留PTRC，優化器根據沒有命中的次數，每隔rds_partial_result_cache_hit_ratio_frequency會檢查命中率是否低於rds_partial_result_cache_min_hit_ratio。如果低於該值，優化器會自動禁止繼續使用PTRC。

優化器如何限制PTRC的記憶體使用

由於單個查詢可以有多個PTRC運算元，每個運算元都會使用記憶體來儲存快取資料，那麼控制PTRC記憶體使用就非常有必要，以防止記憶體OOM。

通過系統變數rds_partial_result_cache_max_mem_size來定義每個查詢所使用的所有PTRC運算元使用的最大記憶體。如果PTRC使用的記憶體總數超過該值，優化器會根據LRU演算法來進行淘汰。如果通過LRU演算法可以找到適合當前儲存記錄的大小的記錄進行淘汰，當前記錄可以進行快取，否則當前記錄將不被快取。

PTRC如何配置？

首先，PTRC預設是開啟的，可以通過Optimizer_switch中的partial_result_cache選項更改設定：設定為ON，啟用PTRC，否則就關閉。通過下表中的4個系統變數，對PTRC進行具體設定。

GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速

PTRC效能測試

下面是我們使用TPCH的Q17來測試不同資料量下啟用PTRC前後的效能變化。

GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速

由於PTRC是一個cache，所以命中率越高效能提升就會越高。當然如果PTRC相關的運算元執行代價越高的話，那麼PTRC獲取的效能提升也是越高的。

MariaDB的subquery cache是對重複掃描運算元correlated subquery進行加速引入的一個特性，我們參照MariaDB的subquery cache測試樣例，同樣基於dbt-3 scale 1 資料集，測試PTRC對於correlated subquery的加速效果。

由於MySQL和MariaDB索引建立的不同，執行時間與MariaDB不同，這裡只需要關注相對時間即可。測試結果如下表所示：

GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速

可以看出：上表中最後一行命中率為0的情況下，PTRC預設值檢查如果miss了200條之後，會觸發檢查命中率，發現命中率太低了，所以PTRC自動失效了，所以可以看到miss列裡只有200條。

MariaDB的測試樣例和結果請參考：https://mariadb.com/kb/en/subquery-cache/#performance-impact。

PTRC對於引數化的重複掃描都可以進行適配，只要命中率足夠，就可以加速執行。對於查詢中的多種運算元包括Correlated Subquery, Nested Loop Join, Semijoin, Antijoin都有加速作用。PTRC已經正式上線，歡迎大家使用。

 

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

相關文章