查詢緩存的失效非常頻繁，只要有對(duì)一個(gè)表的更新，這個(gè)表上所有的查詢緩存都會(huì)被清空。因此很可能你費(fèi)勁地把結(jié)果存起來，還沒使用呢，就被一個(gè)更新全清空了。對(duì)于更新壓力大的數(shù)據(jù)庫來說，查詢緩存的命中率會(huì)非常低。除非你的業(yè)務(wù)就是有一張靜態(tài)表，很長(zhǎng)時(shí)間才會(huì)更新一次。比如，一個(gè)系統(tǒng)配置表，那這張表上的查詢才適合使用查詢緩存。

好在 MySQL 也提供了這種“按需使用”的方式。你可以將參數(shù) query_cache_type 設(shè)置成 DEMAND，這樣對(duì)于默認(rèn)的 SQL 語句都不使用查詢緩存。而對(duì)于你確定要使用查詢緩存的語句，可以用 SQL_CACHE 顯式指定，示例如下：
mysql> select SQL_CACHE * from T where ID = 1;
需要注意的是，MySQL 8.0版本直接將查詢緩存的整塊功能刪掉了。

分析器

如果沒有命中查詢緩存，就要開始真正執(zhí)行語句了。首先，MySQL 需要知道你要做什么，因此需要對(duì) SQL 語句做解析。

分析器先會(huì)做“詞法分析”。你輸入的是由多個(gè)字符串和空格組成的一條 SQL 語句，MySQL 需要識(shí)別出里面的字符串分別是什么，代表什么。

MySQL 從你輸入的"select"這個(gè)關(guān)鍵字識(shí)別出來，這是一個(gè)查詢語句。它也要把字符串“T”識(shí)別成“表名 T”，把字符串“ID”識(shí)別成“列 ID”。

做完了這些識(shí)別以后，就要做“語法分析”。根據(jù)詞法分析的結(jié)果，語法分析器會(huì)根據(jù)語法規(guī)則，判斷你輸入的這個(gè) SQL 語句是否滿足 MySQL 語法。

如果你的語句不對(duì)，就會(huì)收到“You have an error in your SQL syntax”的錯(cuò)誤提醒。一般語法錯(cuò)誤會(huì)提示第一個(gè)出現(xiàn)錯(cuò)誤的未知，所以要關(guān)注的是緊接“user near” 的內(nèi)容。

[SQL] SELECT * FROM `sequences` wher id = 1;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id = 1' at line 1

優(yōu)化器

經(jīng)過了分析器，MySQL 就知道你要做什么了。在開始執(zhí)行之前，還要先經(jīng)過優(yōu)化器的處理。

優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引；或者在一個(gè)語句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序。比如你執(zhí)行下面這樣的語句，這個(gè)語句是執(zhí)行兩個(gè)表的 join：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 既可以先從表 t1 里面取出 c=10 的記錄的 ID 值，再根據(jù) ID 值關(guān)聯(lián)到表 t2，再判斷 t2 里面 d 的值是否等于 20。
• 也可以先從表 t2 里面取出 d=20 的記錄的 ID 值，再根據(jù) ID 值關(guān)聯(lián)到 t1，再判斷 t1 里面 c 的值是否等于 10。

這兩種執(zhí)行方法的邏輯結(jié)果是一樣的，但是執(zhí)行的效率會(huì)有不同，而優(yōu)化器的作用就是決定選擇使用哪一個(gè)方案。

執(zhí)行器

MySQL 通過分析器知道了你要做什么，通過優(yōu)化器知道了該怎么做，于是就進(jìn)入了執(zhí)行器階段，開始執(zhí)行語句.

開始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表 T 有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會(huì)返回沒有權(quán)限的錯(cuò)誤，如下所示 (在工程實(shí)現(xiàn)上，如果命中查詢緩存，會(huì)在查詢緩存返回結(jié)果的時(shí)候，做權(quán)限驗(yàn)證。查詢也會(huì)在優(yōu)化器之前調(diào)用 precheck 驗(yàn)證權(quán)限)。

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有權(quán)限，就打開表繼續(xù)執(zhí)行。打開表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口。

比如我們這個(gè)例子中的表 T 中，ID 字段沒有索引，那么執(zhí)行器的執(zhí)行流程是這樣的：

1. 調(diào)用 InnoDB 引擎接口取這個(gè)表的第一行，判斷 ID 值是不是 10，如果不是則跳過，如果是則將這行存在結(jié)果集中；
2. 調(diào)用引擎接口取“下一行”，重復(fù)相同的判斷邏輯，直到取到這個(gè)表的最后一行。
3. 執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端。至此，這個(gè)語句就執(zhí)行完成了。

你會(huì)在數(shù)據(jù)庫的慢查詢?nèi)罩局锌吹揭粋€(gè) rows_examined 的字段，表示這個(gè)語句執(zhí)行過程中掃描了多少行。這個(gè)值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)行的時(shí)候累加的。

在有些場(chǎng)景下，執(zhí)行器調(diào)用一次，在引擎內(nèi)部則掃描了多行，因此引擎掃描行數(shù)跟 rows_examined 并不是完全相同的。

小問題： 如果表 T 中沒有字段 k，而你執(zhí)行了這個(gè)語句 select * from T where k=1, 那肯定是會(huì)報(bào)“不存在這個(gè)列”的錯(cuò)誤： “Unknown column ‘k’ in ‘where clause’”。你覺得這個(gè)錯(cuò)誤是在我們上面提到的哪個(gè)階段報(bào)出來的呢？

更新語句

執(zhí)行語句之前需要先通過連接器連接數(shù)據(jù)庫。

當(dāng)一個(gè)表上有更新時(shí)，跟這個(gè)表有關(guān)的查詢緩存會(huì)失效，所以這條語句會(huì)把表中的所有緩存結(jié)果都清空，這也是一般情況下不建議使用查詢緩存的原因。

接下來進(jìn)入分析器，分析器會(huì)通過詞法和語法解析指導(dǎo)這是一條更新語句。優(yōu)化器決定要使用ID主鍵索引。然后執(zhí)行器負(fù)責(zé)具體執(zhí)行，找到這一行，然后更新。

與查詢語句不同的是，更新的流程涉及到兩個(gè)日志模塊：redo log（重做日志） 和 binlog（歸檔日志）。

redo log

如果MySQL的每次更新操作都要寫進(jìn)磁盤，然后磁盤也要找到對(duì)應(yīng)的那條記錄再更新，會(huì)產(chǎn)生巨大的IO成本。為了解決這個(gè)問題，MySQL的設(shè)計(jì)者使用了 WAL 技術(shù)，全程是 write-Ahead Logging，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤。

具體來說，在一條記錄需要更新時(shí)，InnoDB 引擎會(huì)先把記錄寫到redo log 里，并更新內(nèi)存，這時(shí)就算更新完成。同時(shí) ，InnoDB 引擎會(huì)在適當(dāng)?shù)臅r(shí)候，將這個(gè)操作記錄到磁盤中，這個(gè)操作往往是在系統(tǒng)比較空閑的時(shí)候做。

InnoDB 的redo log 是固定大小的，是一個(gè)環(huán)狀的結(jié)構(gòu)，從頭開始寫，寫到末尾就又回到開頭循環(huán)寫。

write pos是當(dāng)前記錄的位置，一邊寫一邊往后移。而checkpoint 是當(dāng)前要擦除的位置，也就是往后推移并且循環(huán)的，擦除記錄之前需要把記錄更新到數(shù)據(jù)文件。

有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為 crash-sale。

binlog

redo log 是 InnoDB 引擎在引擎層特有的日志，而在 Server 層也有自己的日志，稱為 binlog（歸檔日志）。

redo log 和 binlog 有三點(diǎn)不同

1. redo log 是InnoDB引擎特有；binlog是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用。
2. redo log 是物理日志，記錄的是 “在某個(gè)數(shù)據(jù)頁上做了什么修改”；binlog是邏輯日志，記錄的是這個(gè)語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1”。
3. redo log 是循環(huán)寫的，空間固定會(huì)用完；binlog 是可以追加寫入的。“追加寫” 是指 binlog 文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。

再來看下執(zhí)行器和 InnoDB 引擎在執(zhí)行這個(gè)簡(jiǎn)單的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)，更新完成。

淺色框代表在 InnoDB 內(nèi)部執(zhí)行，深色框代表在執(zhí)行器中執(zhí)行。

最后三步中，將 redo log 的寫入拆成了兩個(gè)步驟：prepare 和 commit，這就是 “兩階段提交”。

兩階段提交

為了讓兩份日志之間的邏輯一致，需要使用兩階段提交。
在數(shù)據(jù)庫擴(kuò)容的時(shí)候，也就是需要再多搭建一些備庫來增加系統(tǒng)的讀能力的時(shí)候，常見的做法就是用全量備份加上應(yīng)用 binlog 來實(shí)現(xiàn)。

兩階段提交是跨系統(tǒng)維持?jǐn)?shù)據(jù)邏輯一致性時(shí)常用的一個(gè)方案。

小結(jié)

sync_binlog 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候，表示每次事務(wù)的 binlog 都持久化到磁盤。這個(gè)參數(shù)我也建議你設(shè)置成 1，這樣可以保證 MySQL 異常重啟之后 binlog 不丟失。

binlog 有兩種模式，statement 格式是記錄 sql，row 格式是記錄更新前后的兩條內(nèi)容。

在 InnoDB 引擎中，執(zhí)行一條更新語句時(shí)，會(huì)使用到 MySQL 的兩個(gè)日志模塊，分別是 InnoDB 引擎層的 redo log 和 MySQL Server 層的 binlog。
物理日志 redo log 相當(dāng)于一個(gè)緩存，暫時(shí)記錄了對(duì)數(shù)據(jù)庫的操作，在MySQL 空閑時(shí)再寫入磁盤文件，降低 IO 壓力。
邏輯日志 binlog 主要存儲(chǔ)語句的原始邏輯，在磁盤中存儲(chǔ)，在恢復(fù)數(shù)據(jù)、維護(hù)數(shù)據(jù)一致性時(shí)使用廣泛。