MySQL中一條更新的SQL如何執(zhí)行

MySQL 之 -- 一條更新的 SQL 如何執(zhí)行，一條更新的 SQL 語句如何執(zhí)行執(zhí)行流程一條 SQL 的執(zhí)行流程如圖所示：(圖片來源于網(wǎng)絡) 如圖所示：MySQL 數(shù)據(jù)庫主要分為兩個層級：服務層和存儲引擎層服務層：server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器，包括大多數(shù) MySQL 中的核心功能所有跨存儲引擎的功能也在這一層實現(xiàn)，包括存儲過程、觸發(fā)器、視圖等。獲取更多視頻資源，微信搜索公眾號：【碼農(nóng)編程進階筆記】

執(zhí)行流程

一條 SQL 的執(zhí)行流程如圖所示：（圖片來源于網(wǎng)絡）
如圖所示：

MySQL 數(shù)據(jù)庫主要分為兩個層級：服務層和存儲引擎層

• 服務層：server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器，包括大多數(shù) MySQL 中的核心功能所有跨存儲引擎的功能也在這一層實現(xiàn)，包括 存儲過程、觸發(fā)器、視圖等。

• 存儲引擎層：存儲引擎層包括 MySQL 常見的存儲引擎，包括 MyISAM、InnoDB 和 Memory 等，最常用的是 InnoDB，也是現(xiàn)在 MySQL 的默認存儲引擎。存儲引擎也可以在創(chuàng)建表的時候手動指定，比如：

SQL 語句的執(zhí)行過程

• 連接器：需要 MySQL 客戶端登錄，需要一個 連接器 來連接用戶和 MySQL 數(shù)據(jù)庫，“mysql -u 用戶名 -p 密碼” 進行 MySQL 登錄，在完成 TCP 握手 后，連接器會根據(jù)輸入的用戶名和密碼驗證登錄身份。若錯誤 會提示 Access denied for user。若成功，MySQL 會根據(jù)權(quán)限表中的記錄來判定權(quán)限。
• 查詢緩存：MySQL 在得到一個執(zhí)行請求后，會首先去 查詢緩存 中查找，是否執(zhí)行過這條 SQL 語句，之前執(zhí)行過的語句以及結(jié)果會以 key-value 對的形式，被直接放在內(nèi)存中。key 是查詢語句，value 是查詢的結(jié)果。如果通過 key 能夠查找到這條 SQL 語句，直接返回 SQL 的執(zhí)行結(jié)果。若存在緩存中，就會繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結(jié)果就會被放入查詢緩存中。優(yōu)點是效率高。但是查詢緩存不建議使用， 因為在 MySQL 中對某張表進行了更新操作，那么所有的查詢緩存就會失效，對于更新頻繁的數(shù)據(jù)庫來說，查詢緩存的命中率很低。需要注意：在 MySQL8.0 版本，查詢緩存功能就刪除了，不存在查詢緩存的功能了
• 分析器: 分為詞法分析和語法分析
  1).?詞法分析：首先，MySQL 會根據(jù) SQL 語句進行解析，分析器會先做 詞法分析，你寫的 SQL 就是由多個字符串和空格組成的一條 SQL 語句，MySQL 需要識別出里面的字符串是什么，代表什么。
  2).?語法分析：然后進行 語法分析， 根據(jù)詞法分析的結(jié)果，語法分析器會根據(jù)語法規(guī)則，判斷輸入的這個 SQL 語句是否滿足 MySQL 語法。如果 SQL 語句不正確，就提示：You have an error in your SQL suntax
• 優(yōu)化器：經(jīng)過分析器分析后，SQL 就合法了，但在執(zhí)行之前，還需要進行優(yōu)化器的處理，優(yōu)化器會判斷使用了哪種索引，使用哪種連接，優(yōu)化器的作用 就是確定效率最高的執(zhí)行方案。
• 執(zhí)行器：在執(zhí)行階段，MySQL 首先會判斷有沒有執(zhí)行語句的權(quán)限，若無權(quán)限，返回沒有權(quán)限的錯誤；若有權(quán)限，就打開表繼續(xù)執(zhí)行。打開表時，執(zhí)行器會根據(jù)標的引擎定義，去使用該引擎提供的接口，對于有索引額表，執(zhí)行的邏輯類似。
  存儲引擎提供數(shù)據(jù)讀取和記錄的接口。

更新 SQL 語句的日志記錄

日志記錄用到 WAL 技術(shù)，全稱為 Write-Ahead-logging

關鍵點為：先寫日志，再寫磁盤

redo log

1. redo log 是 InnoDB 引擎 中的日志模塊，只有 InnoDB 中有。
2. redo log 是物理日志，記錄 “這個數(shù)據(jù)頁做了什么改動”。
3. 是固定大小的日志模塊。比如可以配置為一組 4 個文件，每個文件大小是 1GB，那么這塊日志就可以記錄 4GB 的內(nèi)容，可以理解為一個環(huán)形結(jié)構(gòu)，有一個 write pos 標識當前記錄的位置，一邊寫入一邊后移，有一個 check point 記錄當前要擦除的位置（當然擦除之前要寫入數(shù)據(jù)文件中），也是往后推移，并且循環(huán)的。當 write pos 追上 check point 的時候表示日志已經(jīng)滿了， 當前需要停下來先擦除一些記錄，存到數(shù)據(jù)文件中，為需要寫入的日志騰出空間。獲取更多視頻資源，微信搜索公眾號：碼農(nóng)編程進階筆記
4. 有了 redo log，InnoDB 就保證數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會丟失，這個能力稱為 Crash-safe

binlog

1. binlog 是 server 層的日志，稱之為歸檔日志。因為 binlog 是 server 層的那就代表所有的存儲引擎都可以使用。
2. binglog 是邏輯日志，記錄的是這個語句的原始邏輯，比如 “給 ID=2 這行的 C 字段加 1”
3. binlog 有兩種模式：statement 格式是記錄執(zhí)行的 sql 語句，而 row 格式是記錄行的內(nèi)容，會記錄兩行數(shù)據(jù)，分別是：更新前的這行數(shù)據(jù)和更新后的這行數(shù)據(jù)。
4. binlog 是可以追加寫的日志，在日志文件寫到一定大小，會切換到下一個文件記錄，并不會覆蓋以前的日志。

redo log 與 binlog 執(zhí)行順序，和重要的兩步提交

更新流程如圖所示：紅色為在執(zhí)行器中執(zhí)行，藍色在 InnoDB 內(nèi)部執(zhí)行

update 語句執(zhí)行時的內(nèi)部流程。

1. 執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。

2. 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。

3. 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到 redo log 里面，此時 redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務。

4. 執(zhí)行器生成這個操作的 binlog，并把 binlog 寫入磁盤。

5. 執(zhí)行器調(diào)用引擎的提交事務接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。

其中 prepare 和 commit 兩個階段就是?兩步提交

若在 prepare 后寫入 binlog 階段出問題，現(xiàn)在這條數(shù)據(jù)是 prepare 狀態(tài)，然后我們恢復數(shù)據(jù)庫的時候這條數(shù)據(jù)的更新操作就會回滾，不產(chǎn)生變更，若在 commit 出了問題，也會進行回滾，這樣可以保證數(shù)據(jù)的一致性。