學(xué)習(xí)筆記是學(xué)習(xí)了 極客時(shí)間 - 《MySQL實(shí)戰(zhàn)45講》整理的筆記辙售。
MYSQL簡(jiǎn)單架構(gòu)
MySQL 可以分為 Server 層和存儲(chǔ)引擎層兩部分。
- Server 層： Server 層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等顿膨，涵蓋 MySQL 的大多數(shù)核
心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期叽赊、時(shí)間恋沃、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)必指，比如存儲(chǔ)過(guò)程囊咏、觸發(fā)器、視圖等塔橡。梅割，不同的存儲(chǔ)引擎共用一個(gè)Server 層，也就是從連接器到執(zhí)行器的部分葛家。- 連接器：連接器負(fù)責(zé)跟客戶端建立連接户辞、獲取權(quán)限、維持和管理連接癞谒。
- 查詢緩存（MySQL 8.0版本以后直接取消了查詢緩存的整塊功能）：MySQL 拿到一個(gè)查詢請(qǐng)求后底燎，會(huì)先到查詢緩存看看，之前是不是執(zhí)行過(guò)這條語(yǔ)句弹砚。之前執(zhí)行過(guò)的語(yǔ)句及其結(jié)果可能會(huì)以 key-value 對(duì)的形式双仍，被直接緩存在內(nèi)存中。key 是查詢的語(yǔ)句迅栅，value 是查詢的結(jié)果殊校。如果你的查詢能夠直接在這個(gè)緩存中找到 key晴玖，那么這個(gè)value 就會(huì)被直接返回給客端读存。
- 對(duì)于更新頻繁的數(shù)據(jù)庫(kù)表,不建議查詢緩存。查詢緩存的失效非常頻繁呕屎，只要有對(duì)一個(gè)表的更新让簿，這個(gè)表上所有的查詢緩存都會(huì)被清空。因此很可能你費(fèi)勁地把結(jié)果存起來(lái)秀睛，還沒(méi)使用呢尔当，就被一個(gè)更新全清空了。對(duì)于更新壓力大的數(shù)據(jù)庫(kù)來(lái)說(shuō)，查詢緩存的命中率會(huì)非常低椭迎。
- 按需配置：
- 關(guān)閉緩存：將參數(shù) query_cache_type 設(shè)置成 DEMAND锐帜，這樣對(duì)于默認(rèn)的 SQL 語(yǔ)句都不使用查詢緩存。
- 設(shè)置緩存：用 SQL_CACHE 顯式指定
select SQL_CACHE * from T where ID=10畜号；
- 分析器：
- 詞法分析
- 語(yǔ)法分析
- 優(yōu)化器：在表里面有多個(gè)索引的時(shí)候缴阎，決定使用哪個(gè)索引
- 執(zhí)行器：
- 判斷權(quán)限
- 調(diào)用引擎接口
- 存儲(chǔ)引擎層：存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。
MYSQL中 SQL語(yǔ)句的執(zhí)行流程
查詢語(yǔ)句執(zhí)行流程如圖：
更新語(yǔ)句執(zhí)行流程（InnoDB引擎）
InnoDB架構(gòu)
在更新語(yǔ)句執(zhí)行流程中简软，大部分會(huì)使用到內(nèi)存池蛮拔。
執(zhí)行SQL語(yǔ)句
update T set c=c+1 where ID=2;
可以看到 更新語(yǔ)句執(zhí)行的時(shí)候，涉及到了兩個(gè)重要的日志模塊痹升。
WAL 技術(shù)
為什么會(huì)涉及這兩個(gè)模塊建炫，首先我們需要了解一下 MYSQL WAL 技術(shù)，WAL 的全稱是 Write-Ahead Logging疼蛾，它的關(guān)鍵點(diǎn)就是先寫(xiě)日志肛跌，再寫(xiě)磁盤(pán)。具體來(lái)說(shuō)据过，當(dāng)有一條記錄需要更新的時(shí)候惋砂，InnoDB 引擎就會(huì)先把記錄寫(xiě)到redo log里面，并更新內(nèi)存绳锅，這個(gè)時(shí)候更新就算完成了西饵。同時(shí)，InnoDB 引擎會(huì)在適當(dāng)?shù)臅r(shí)候鳞芙，將這個(gè)操作記錄更新到磁盤(pán)里面眷柔，而這個(gè)更新往往是在系統(tǒng)比較空閑的時(shí)候做。
- redo log（重做日志）: InnoDB 引擎特有的日志并且是固定大小,循環(huán)寫(xiě)入機(jī)制原朝。比如4個(gè)日志文件如下圖所示驯嘱。
write pos 是當(dāng)前記錄的位置，一邊寫(xiě)一邊后移喳坠，寫(xiě)到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開(kāi)頭鞠评。checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的壕鹉，擦除記錄前要把記錄更新到數(shù)據(jù)文件剃幌。write pos checkpoint 之間的是用來(lái)記錄新的操作。如果 write pos 追上 checkpoint晾浴，如要將數(shù)據(jù)寫(xiě)入磁盤(pán)后在進(jìn)行下一步操作负乡。
優(yōu)點(diǎn)：保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失脊凰，這個(gè)能力稱為crash-safe抖棘。
redo log 是物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”；如下圖所示：
參數(shù)配置：innodb_flush_log_at_trx_commit 這個(gè)參數(shù)設(shè)置成1 的時(shí)候切省，表示每次事務(wù)的 redo log 都直接持久化到磁盤(pán)最岗。
-
binlog（歸檔日志）：是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用朝捆。只能用于歸檔(備份)仑性。其主要記錄的是SQL語(yǔ)句原始的邏輯。如下圖所示：
- 參數(shù)配置：sync_binlog 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候右蹦，表示每次事務(wù)的 binlog 都持久化到磁盤(pán)诊杆。
- Binlog有兩種模式，statement 格式的話是記sql語(yǔ)句何陆， row格式會(huì)記錄行的內(nèi)容晨汹，記兩條，更
新前和更新后都有贷盲。
對(duì)比
| redo log | binlog | |
|---|---|---|
| 實(shí)現(xiàn) | InnoDB 引擎特有 | Server 層所有引擎都可以使用淘这。 |
| 類別 | 物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改” | binlog 是邏輯日志巩剖，記錄的是這個(gè)語(yǔ)句的原始邏輯铝穷。比如“給 ID=2 這一行的 c 字段加 1 ”。 |
| 寫(xiě)入方式 | 循環(huán) | 追加佳魔，文件寫(xiě)到一定大小后會(huì)切換到下一個(gè)曙聂，并不會(huì)覆蓋以前的日志。 |
兩階段提交（ prepare 和commit ）
在之前的邏輯圖中我們可以看到鞠鲜，進(jìn)行了兩階段提交操作宁脊，為了讓兩份日志之間的邏輯一致。通過(guò)一種特殊的xid贤姆，可以在兩階段提交的不同時(shí)刻發(fā)生異常重啟時(shí)榆苞，將redo和binlog聯(lián)系起來(lái)。如下：
[Note] Found 1 prepared transaction(s) in InnoDB
[Note] commit xid 'MySQLXid\1\0\0\0\0\0\0\0\6\0\0\0\0\0\0\0'
非兩階段提交出現(xiàn)宕機(jī)問(wèn)題 以update T set c=c+1 where ID=2;為例
- 先寫(xiě) redo log 后寫(xiě) binlog霞捡。假設(shè)在 redo log 寫(xiě)完坐漏，binlog 還沒(méi)有寫(xiě)完的時(shí)候，MySQL 進(jìn)程異常重啟碧信。由于我們前面說(shuō)過(guò)的赊琳，redo log 寫(xiě)完之后，系統(tǒng)即使崩潰音婶，仍然能夠把數(shù)據(jù)恢復(fù)回來(lái)慨畸，所以恢復(fù)后這一行 c 的值是 1莱坎。但是由于 binlog 沒(méi)寫(xiě)完就 crash 了衣式，這時(shí)候 binlog 里面就沒(méi)有記錄這個(gè)語(yǔ)句。因此，之后備份日志的時(shí)候碴卧，存起來(lái)的 binlog 里面就沒(méi)有這條語(yǔ)句弱卡。然后你會(huì)發(fā)現(xiàn)，如果需要用這個(gè) binlog 來(lái)恢復(fù)臨時(shí)庫(kù)的話住册，由于這個(gè)語(yǔ)句的 binlog 丟失婶博，這個(gè)臨時(shí)庫(kù)就會(huì)少了這一次更新，恢復(fù)出來(lái)的這一行 c 的值就是 0荧飞，與原庫(kù)的值不同凡人。
- 先寫(xiě) binlog 后寫(xiě) redo log。如果在 binlog 寫(xiě)完之后 crash叹阔，由于 redo log 還沒(méi)寫(xiě)挠轴，崩潰恢復(fù)以后這個(gè)事務(wù)無(wú)效，所以這一行 c 的值是 0耳幢。但是 binlog 里面已經(jīng)記錄了“把 c 從 0 改成 1”這個(gè)日志岸晦。所以，在之后用 binlog 來(lái)恢復(fù)的時(shí)候就多了一個(gè)事務(wù)出來(lái)睛藻，恢復(fù)出來(lái)的這一行 c 的值就是 1启上，與原庫(kù)的值不同。
關(guān)于 binlog 和 redo log更為深入的了解 推薦閱讀：一文帶你看懂binlog和redo log
