摘要：一起看一下GaussDB(for MySQL)是如何對執行計划進行緩存並加速Prepared Statement性能的。 本文分享自華為雲社區《執行計劃緩存，Prepared Statement性能躍升的秘密》，作者： GaussDB 資料庫。 引言 在資料庫系統中，SQL（Structure ...
摘要：一起看一下GaussDB(for MySQL)是如何對執行計划進行緩存並加速Prepared Statement性能的。
本文分享自華為雲社區《執行計劃緩存，Prepared Statement性能躍升的秘密》，作者： GaussDB 資料庫。
引言
在資料庫系統中，SQL（Structured Query Language）語句輸入到系統後，一般要經歷：詞法語法解析(parse)、重寫（resolve）、優化（optimize）、執行(execute)的過程。詞法語法分析，重寫和優化，這三個階段會生成SQL語句的執行計劃 (plan)。當SQL語句存在多種執行計劃的時候，優化器會從這許多的執行計劃中挑選出一個它認為最優的（通常是占用系統資源最少的，包括CPU以及IO等）作為最終的執行計劃供執行器執行。生成執行計劃的過程會消耗較多的時間，特別是存在許多可選的執行計劃時。
圖1：SQL語句執行
Prepared Statement是將SQL語句中的值用占位符替代，可以視為將SQL語句模板化或者說參數化。當執行PREPARE語句時，傳統MySQL將對指定的語句進行詞法語法解析和重寫，如上圖①②。該階段稱為預編譯階段。Prepared Statement的優勢在於一次編譯、多次運行，省去了預編譯階段需要的時間。隨後發出EXECUTE命令時，MySQL將對編譯階段生成的結構執行優化，即上圖的③，生成對應的執行計劃並執行，把輸出結果返回到客戶端。例如：
PREPARE stmt FROM ‘SELECT * FROM t WHERE t.a = ?’; SET @var = 2; EXECUTE stmt USING @var;
傳統MySQL的Prepared Statement只會節省SQL語句的解析及重寫過程需要的時間，但是對於一條SQL語句，如文章開頭所述，優化SQL語句並生成執行計劃需要耗費大量的資源以及時間。如果能將該Prepared Statement語句對應的最終執行計划進行緩存，當執行EXECUTE語句的時候，就可以直接使用已緩存的執行計劃，從而就可以跳過SQL語句生成執行計劃的整個過程，進而可以提高語句的執行性能。為此，GaussDB(for MySQL) 提供了Prepared Statement執行計劃緩存特性。
接下來一起看一下GaussDB(for MySQL)是如何對執行計划進行緩存並加速Prepared Statement性能的。
執行計劃緩存工作原理
GaussDB(for MySQL)對Prepared Statement執行計划進行緩存的基本原理和流程如下圖所示：
- 響應EXECUTE，執行查詢。
- 通過is_plan_cached過程來查看當前Query的執行計劃是否已經被緩存。
- 如果已經被緩存，優化器將對當前的Query緩存的執行計划進行初始化，根據執行計劃的上下文還原執行計劃，然後利用還原的執行計劃繼續執行。
- 如果沒有被緩存，在執行完Query優化生成執行計劃之後，通過is_query_cachable過程驗證當前執行計劃是否可以被緩存。
- 如果滿足緩存條件，執行計劃將會被緩存（調用cache_JOIN_plan），以便以後的EXECUTE語句可以利用該緩存的計划進行執行。
- 如果不能緩存，通過傳統的MySQL執行流程（優化，生成執行計劃然後執行）執行EXECUTE語句。
執行計劃緩存管理
- 執行計劃緩存功能開關
GaussDB(for MySQL)引入了一個新的系統參數rds_plan_cache來開關Prepared Statement執行計劃緩存功能。
rds_plan_cache：該參數可以設置為ON/OFF。分別代表開啟和關閉執行計劃緩存。該參數是Session/Global級別的參數。
- 查看執行計劃緩存情況
GaussDB(for MySQL)提供了兩個狀態變數供用戶查看或者驗證Prepared Statement執行計劃是否被緩存，以及在執行時是否命中了緩存的執行計劃。
- cached_plan_count：顯示有多少個Prepared Statement緩存了執行計劃。這是一個Global級別的狀態變數。
- cached_plan_hits：顯示EXECUTE執行過程中命中了緩存的執行計劃的次數。這是一個Session/Global狀態。
下麵舉例來看一下Prepared Statement是如何利用了執行計劃緩存特性的：
SET @a = 'two'; SET @b = 3; PREPARE stmt FROM "SELECT * FROM t1 WHERE b = ? AND c = ?"; EXECUTE stmt USING @a,@b;
執行結果如下：
a b c 6 two 3
再次執行Prepared Statement：
EXECUTE stmt USING @a,@b; a b c 6 two 3
第三次執行Prepared Statement：
execute stmt using @a,@b; a b c 6 two 3
通過cached_plan_count和cached_plan_hits查看stmt執行計劃是否被緩存，以及在執行時是否命中了緩存的執行計劃。
SHOW SESSION STATUS LIKE "cached_plan%";
顯示結果如下：
Variable_name Value Cached_plan_count 1 Cached_plan_hits 2
從顯示結果可以看出，第一次執行EXECUTE語句的時候，Prepared Statement對執行計划進行了緩存，即可以看到Cached_plan_count為1； 之後執行兩次EXECUTE語句，都命中了執行計劃緩存，所以可以看到Cached_plan_hits變成了2。
緩存的執行計劃如何失效
為了保持當前緩存的執行計劃是儘可能最優的，GaussDB(for MySQL)定義瞭如下規則來對當前緩存的計划進行失效，並重新生成執行計劃：
- 執行計劃相關表的記錄數更改超過總記錄數的20%。
這意味著當前表的記錄數如果插入/刪除超過20%的記錄，當前緩存計劃將失效併在優化後重新緩存。註：記錄數是根據統計數據估計的。所以最好先對錶進行Analyze。 - 表定義進行了更改。
例如，執行計劃相關表上進行的DDL將導致緩存計劃無效，併在優化後重新緩存。 - 如果系統變數Optimizer_switch中影響執行計劃生成的選項值進行了更改，則緩存的計劃將失效，併在優化後重新緩存。
- 系統字元集發生變化，與緩存的計劃不同時，將導致緩存計劃失效，併在優化後重新緩存。
執行計劃緩存功能當前的一些限制
GaussDB(for MySQL)的Prepared Statement的目的是節約查詢的優化時間。對於通過並行查詢優化的大查詢，也就是數據量相對龐大的查詢，這些查詢大部分的執行時間是集中在執行計劃的執行階段。對於該類型的查詢，優化時間相比執行時間而言可以忽略不計，所以GaussDB(for MySQL)沒有對並行查詢計划進行緩存。另外，GaussDB(for MySQL)對於Prepared statement 緩存執行計劃的能力還在逐步增強中，比如當前只支持單表的SELECT查詢語句，暫時還不支持UNION操作。
執行計劃緩存性能測試結果
對於使用執行計劃緩存和不使用執行計劃緩存的場景，基於Sysbench測試集進行了性能測試對比，從測試結果可以看出，在啟用執行計劃緩存後，各類業務性能均有提升。註意：這些測試只代表相對數字，並不代表實際性能。
測試環境配置如下：
數據集 : 8 個表，每個表1000萬行
測試伺服器：Intel(R) Xeon(R) CPU E5-2690 v4 @ 2.60GHz 2 physical cores 56 processors 460G memory
總結
GaussDB(for MySQL)通過緩存執行計劃，可以提升Prepared Statement的性能。特別是針對Range Scan的測試集，性能提升可達2倍左右。未來我們會支持越來越多的查詢場景，性能加速值得期待。
