一個SQL的執(zhí)行過程詳解

一個sql的執(zhí)行過程

一、 組成部分

PDO_MYSQL is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MySQL databases.

• 客戶端，如：php 的 pdo_mysql 擴展。

• MySQL 服務(wù)：

• server 層，主要包含：連接器、查詢緩存、解析器、預(yù)處理器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時間、數(shù)學(xué)和加密函數(shù)等）。所有跨存儲引擎的功能都在這一層實現(xiàn)，比如存儲過程、觸發(fā)器、視圖等
• 存儲引擎層，主要負(fù)責(zé)：數(shù)據(jù)的存儲和提取。

二、 大概流程

• MySQL server 層的 連接器 對來自客戶端的連接進行驗證，包含：

• `用戶名密碼` 或 `SSL 證書` 驗證

• `庫-database`、`表-table`、`讀寫權(quán)限` 驗證

• MySQL server 層的 查詢緩存 對客戶源原始SQL進行 緩存命中檢測：命中則直接返回，未命中則進一步執(zhí)行查詢。

• MySQL server 層的 解析器 對查詢語句進行解析，得到查詢語句的 解析樹。

• MySQL server 層的 預(yù)處理器 對 解析樹 進一步驗證。

• MySQL server 層的 優(yōu)化器 將 解析樹 轉(zhuǎn)化為 執(zhí)行計劃。

• MySQL server 層的 執(zhí)行器 通過 API 與底層的 存儲引擎 進行交互，執(zhí)行 執(zhí)行計劃。

• MySQL 存儲引擎 層得到執(zhí)行結(jié)果，返回給 MySQL server 層。MySQL server 層將結(jié)果交由 查詢緩存 進行緩存，并返回給客戶端

三、 查詢緩存

• 關(guān)鍵邏輯：

• 包含 `now()` `current_date()` 等日期函數(shù)

• 包含 `用戶自定義函數(shù)`、`存儲函數(shù)` `用戶變量` `臨時表` 等

• 涉及 mysql 數(shù)據(jù)庫的表或者字段

• `select * from user where id=1;`

• `select * from user where id="1";`

• `select username from user where id=1;`

• `SELECT username FROM user WHERE id=1;`

• `查詢緩存` 緩存了 `執(zhí)行計劃` 的完整結(jié)果，當(dāng)緩存命中時，直接返回緩存中的結(jié)果，從而跳過了 `解析-優(yōu)化-執(zhí)行` 的過程。

• `查詢緩存` 基于不變的表結(jié)構(gòu)和表數(shù)據(jù)，`當(dāng)表結(jié)構(gòu)或表數(shù)據(jù)發(fā)生變化時，其表上的所有緩存都將失效`。

• 搜索公眾號互聯(lián)網(wǎng)架構(gòu)師回復(fù)“2T”，送你一份驚喜禮包。
  

• `查詢緩存` 可以理解將 `執(zhí)行計劃` 的結(jié)果緩存在 hashtable 中，key 是 `客戶端發(fā)來的原始查詢sql` 的 hash 值，因此：

• 的 hash 值并不相同。即：`即使同一條SQL，如果大小寫、空格、單引號、雙引號、注釋等不同，都會使用不同的緩存 key`

• [結(jié)果不 set 緩存的情況] 當(dāng)查詢語句中包含以下情況是，查詢結(jié)果不會被緩存：

• 因為在 `查詢緩存` 階段，還沒有進行 `解析器` 解析的工作，因此：`所有查詢都會嘗試去 get 緩存，但總是不命中`。

• 相關(guān)配置：
• 如果查詢結(jié)果比較大，超過了query_cache_min_res_unit的值，MySQL將一邊檢索結(jié)果，一邊進行保存結(jié)果。
• 根據(jù)自身情況設(shè)置合適的大小：太大會造成大量的 `內(nèi)存碎片`，太小又需要 `頻繁的申請內(nèi)存`。
• `have_query_cache`，當(dāng)前的MYSQL版本是否支持“查詢緩存”功能。
• `query_cache_limit`，能夠緩存的最大查詢結(jié)果，查詢結(jié)果大于該值時不會被緩存，默認(rèn)值是 1MB
• `query_cache_min_res_unit`，查詢緩存分配的最小塊（字節(jié)）。默認(rèn)值是4096（4KB）。
• `query_cache_size`，為緩存查詢結(jié)果分配的總內(nèi)存。
• `query_cache_type`，默認(rèn)為on，可以緩存除了以 `select sql_no_cache` 開頭的所有查詢結(jié)果。
• `query_cache_wlock_invalidate`，如果該表被鎖住，是否返回緩存中的數(shù)據(jù)，默認(rèn)是關(guān)閉的。
• 優(yōu)缺點：
• 對于頻繁變動（`修改表結(jié)構(gòu)、新增、刪除、修改數(shù)據(jù)`）的表，由于一旦 `變動` 就會清除該表的所有緩存，導(dǎo)致：命中率極低，每次SQL還增加了 `查詢緩存` 的額外工作。
• 搜索公眾號互聯(lián)網(wǎng)架構(gòu)師回復(fù)“2T”，送你一份驚喜禮包。
• 參與 hash 計算的是客戶端發(fā)來的原始SQL，還未經(jīng)過 `解析器` 解析，`完全一樣` 的sql才能命中緩存。
• `查詢緩存` 實質(zhì)上是緩存 `SQL的hash值` 和 `該SQL的查詢結(jié)果`，省去了大量重復(fù)SQL查詢的 `解析-優(yōu)化-執(zhí)行` 過程。
• 優(yōu)點：

• 缺點：

四、 `解析器` 和 `預(yù)處理器`

• 對 原始SQL 進行語法解析，驗證語法規(guī)則，如：

• 關(guān)鍵字是否正確

• 關(guān)鍵字順序是否正確

• 語句是否有語法錯誤，如：缺少逗號等

• 得到 `語法解析樹`
• 進一步驗證 語法解析樹，如：

• 庫、表是否存在

• 字段、類型是否正確

• 是否使用了禁止的關(guān)鍵字等

• 調(diào)用函數(shù)、識別別名等

五、 優(yōu)化器

• 優(yōu)化器 是基于 Cost-Based Optimizer 模型，預(yù)估 每條執(zhí)行方式的 成本，選擇 成本最小 的執(zhí)行方式，轉(zhuǎn)化為 執(zhí)行計劃。

• 選擇最優(yōu)的執(zhí)行方式 比較好使，優(yōu)化器 維護了一個 執(zhí)行計劃緩存，當(dāng)緩存命中時，直接使用上次的 執(zhí)行計劃。

• 每種執(zhí)行方式的成本 cost 預(yù)估包含幾個方面：
• `io_cost`，對IO操作的成本預(yù)估
• `cpu_cost`，對CPU操作的成本預(yù)估
• `import_cost`，對遠(yuǎn)程操作的成本預(yù)估
• `mem_cost`，對內(nèi)存消耗的成本預(yù)估

• Cost-Based Optimizer 對復(fù)雜語句的 成本預(yù)估 會產(chǎn)生偏差，這時候就需要用到 我們 了，哈哈。

六、 存儲引擎

具體的 執(zhí)行計劃 如何執(zhí)行，依賴于各種不同的 存儲引擎 的索引算法，如：

• B-Tree 從根節(jié)點開始，沿著向下的指針，找到存儲了行數(shù)據(jù)位置的葉子節(jié)點，再判斷是否滿足 覆蓋查詢，訪問行數(shù)據(jù)。
• Hash 則根據(jù)直接計算 hash 值，如果沖突，再遍歷鏈表。

在此不再贅述，可轉(zhuǎn)閱：MySQL之 B-Tree / B+Tree 索引

七、 結(jié)果返回客戶端