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已經正式上線,歡迎大家使用。

 

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