執行計劃緩存，Prepared Statement性能躍升的秘密

摘要：一起看一下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操作。