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

一條SQL的執(zhí)行流程如圖所示

如圖所示：MySQL數(shù)據(jù)庫主要分為兩個(gè)層級(jí)：服務(wù)層和存儲(chǔ)引擎層

?服務(wù)層：server層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器，包括大多數(shù)MySQL中的核心功能所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，包括 存儲(chǔ)過程、觸發(fā)器、視圖等。?存儲(chǔ)引擎層：存儲(chǔ)引擎層包括MySQL常見的存儲(chǔ)引擎，包括MyISAM、InnoDB和Memory等，最常用的是InnoDB，也是現(xiàn)在MySQL的默認(rèn)存儲(chǔ)引擎。存儲(chǔ)引擎也可以在創(chuàng)建表的時(shí)候手動(dòng)指定，比如：

CREATE TABLE t (I iNT) ENGINE = <Storage Engine>;

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

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

存儲(chǔ)引擎提供數(shù)據(jù)讀取和記錄的接口。

更新SQL語句的日志記錄

日志記錄用到WAL技術(shù)，全稱為Write-Ahead-logging 關(guān)鍵點(diǎn)為：先寫日志，再寫磁盤

redo log

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

binlog

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

redo log與binlog執(zhí)行順序

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

update語句時(shí)的內(nèi)部流程

1.執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。2.執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。3.引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到 redo log 里面，此時(shí) redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)。4.執(zhí)行器生成這個(gè)操作的 binlog，并把 binlog 寫入磁盤。5.執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。

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

來自

https://blog.51cto.com/14612701/2505993?

作者：wx5dcb7577ac572