執行計劃快取，Prepared Statement性能躍升的秘密-有解無憂

摘要：一起看一下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倍左右，未來我們會支持越來越多的查詢場景，性能加速值得期待，

點擊關注，第一時間了解華為云新鮮技術~

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/554195.html

標籤：MySQL

上一篇：存盤引擎的概念及作用，玩轉MySQL

下一篇：返回列表