今天大部分程序需要處理的數(shù)據(jù)，都來自數(shù)據(jù)庫，尤其是關(guān)系型數(shù)據(jù)庫，那么一條 SQL 提交到數(shù)據(jù)庫之后，數(shù)據(jù)庫都做了些什么?如果不懂這些問題，就無法更好的使用數(shù)據(jù)庫，更無法回答好面試官的問題?，F(xiàn)在流行的開源數(shù)據(jù)庫，非 MySQL 莫屬，面試中 MySQL 也是必問，于是我就學(xué)習(xí)了專欄《MySQL實(shí)戰(zhàn)45講》，今天的文章試著回答以下兩個(gè)問題：

1、一條 SQL 語句提交到數(shù)據(jù)庫之后，數(shù)據(jù)庫都會(huì)執(zhí)行哪些動(dòng)作?

2、MySQL 是如何恢復(fù)到某一天的某一秒的狀態(tài)?

先來看看一條讀操作 SQL 的查詢過程：

連接器

客戶端在提交 SQL 語句之前，你需要先連接上數(shù)據(jù)庫，也就是說要提供用戶名密碼登陸，這便是連接器發(fā)揮作用的時(shí)候。

連接上去后，MySQL 就創(chuàng)建了一個(gè)連接對(duì)象放在了內(nèi)存中，連接對(duì)象里有用戶的相關(guān)權(quán)限信息，此時(shí)如果管理員修改了用戶權(quán)限，只要用戶不退出重新連接，就不會(huì)被影響。

內(nèi)存資源是比較昂貴的，不用的話就要被清理。如果不做任何操作，在一定的時(shí)間之后(默認(rèn)是 8 小時(shí))，連接器會(huì)自動(dòng)斷開，此時(shí)再查詢就會(huì)報(bào)錯(cuò)。

一個(gè)比較好的方案是使用數(shù)據(jù)庫連接池。Python 編程可以使用第三方庫 DBUtils 來管理數(shù)據(jù)庫連接池。

查詢緩存

緩存可以快速返回命中的查詢，在使用上的感受就是同一個(gè) SQL，第二次查詢時(shí)結(jié)果是立刻顯示的。查詢緩存中以 SQL 語句作為 KEY，查詢結(jié)果作為 VALUE。

如果你的查詢能夠直接在這個(gè)緩存中找到 key，并且具有對(duì)該表的相應(yīng)的權(quán)限，那么這個(gè) value 就會(huì)被直接返回給客戶端。

如果沒有找到，會(huì)走接下來流程，一旦查到結(jié)果，結(jié)果還是會(huì)保存在查詢緩存中。

分析器

如果沒有命中查詢緩存，SQL 語句就會(huì)傳給分析器進(jìn)行詞法分析，分析是否有語法錯(cuò)誤，解析中表名，字段名等等，其實(shí)不僅僅數(shù)據(jù)庫有分析器，很多開源的工具也有分析 SQL 的功能，比如 Python 可以使用 python-sqlparse，JAVA 可以使用 druid(阿里巴巴開源)。

解析出表名之后，檢查一下用戶對(duì)表的權(quán)限，如果權(quán)限符合就進(jìn)行下一步優(yōu)化器。

優(yōu)化器

經(jīng)過了分析器，MySQL 就知道你要做什么了。

在開始執(zhí)行之前，還要先經(jīng)過優(yōu)化器的處理。優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引;或者在一個(gè)語句有多表關(guān)聯(lián)(join)的時(shí)候，決定各個(gè)表的連接順序。

執(zhí)行器

MySQL 通過分析器知道了你要做什么，通過優(yōu)化器知道了該怎么做，于是就進(jìn)入了執(zhí)行器階段，開始執(zhí)行語句。開始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表 T 有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會(huì)返回沒有權(quán)限的錯(cuò)誤。

也許你會(huì)問，權(quán)限驗(yàn)證前面不是已經(jīng)做了嗎?為什么這里還要進(jìn)行權(quán)限驗(yàn)證，因?yàn)槌藄ql 還可能有存儲(chǔ)引擎，觸發(fā)器等，在這些對(duì)象中，也可能需要調(diào)用其它表去獲取數(shù)據(jù)，也需要權(quán)限驗(yàn)證，前面的階段對(duì)于觸發(fā)器，存儲(chǔ)引擎這種對(duì)象的執(zhí)行是做不到的。

比如說：

select * from T where ID=10; 

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

對(duì)于有索引的表，執(zhí)行的邏輯也差不多。第一次調(diào)用的是“取滿足條件的第一行”這個(gè)接口，之后循環(huán)取“滿足條件的下一行”這個(gè)接口，這些接口都是引擎中已經(jīng)定義好的。

說到存儲(chǔ)引擎，MySQl 支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認(rèn)存儲(chǔ)引擎。也就是說，你執(zhí)行 create table 建表的時(shí)候，如果不指定引擎類型，默認(rèn)使用的就是 InnoDB。不過，你也可以通過指定存儲(chǔ)引擎的類型來選擇別的引擎，比如在 create table 語句中使用 engine=memory, 來指定使用內(nèi)存引擎創(chuàng)建表。不同存儲(chǔ)引擎的表數(shù)據(jù)存取方式不同，支持的功能也不同。

接下來，看一看寫操作的執(zhí)行過程，其中 redo log 和 binlog 又起到了什么作用?

寫操作

首先，可以確定的說，查詢語句的那一套流程，更新語句也是同樣會(huì)走一遍。

與查詢流程不一樣的是，更新流程還涉及兩個(gè)重要的日志模塊，它們正是redo log(重做日志)和 binlog(歸檔日志)。如果接觸 MySQL，那這兩個(gè)詞肯定是繞不過的，redo log 和 binlog 在設(shè)計(jì)上有很多有意思的地方，這些設(shè)計(jì)思路也可以用到你自己的程序里。

以更新操作為例，假如 SQL 語句為：

update table_a set count = count + 1 where id = 2 

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)，更新完成。

這里得說明一下，redo log 和 binlog 都是日志文件，為了防止異常重啟、掉電、恢復(fù)數(shù)據(jù)等場景，這些日志文件都會(huì)持久化到磁盤上。為了防止頻繁的訪問磁盤，寫 redo log 前會(huì)先寫到內(nèi)存中的 redo log buffer，再定期一起寫到磁盤。

但是這兩個(gè) log 文件又有所區(qū)別：

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ì)覆蓋以前的日志。
4. redo log 用于回滾，binlog 用于恢復(fù)。

如果將 MySQL 恢復(fù)到某一天的某一秒

要做到這一點(diǎn)有個(gè)前提，就是要對(duì) MySQL 數(shù)據(jù)庫定期做整庫備份。這里的定期取決于系統(tǒng)的重要性，可以是一天一備，也可以是一周一備。

當(dāng)需要恢復(fù)到指定的某一秒時(shí)，比如某天下午兩點(diǎn)發(fā)現(xiàn)中午十二點(diǎn)有一次誤刪表，需要找回?cái)?shù)據(jù)，那你可以這么做：

1. 首先，找到最近的一次全量備份，如果你運(yùn)氣好，可能就是昨天晚上的一個(gè)備份，從這個(gè)備份恢復(fù)到臨時(shí)庫;
2. 然后，從備份的時(shí)間點(diǎn)開始，將備份的 binlog 依次取出來，重放到中午誤刪表之前的那個(gè)時(shí)刻。這樣你的臨時(shí)庫就跟誤刪之前的線上庫一樣了。
3. 最后，你可以把表數(shù)據(jù)從臨時(shí)庫取出來，按需要恢復(fù)到線上庫去。

為什么要兩階段提交

前面寫操作中的提到，寫磁盤前先寫 redo log，此時(shí) redo log 狀態(tài)為 prepare，然后再寫 binlog，寫完 binlog 后，再提交，redo log 才處于 commit 狀態(tài)。

為什么要等 binlog 寫完才能提交呢?這是因?yàn)榧偃?binlog 沒寫完就提交，此時(shí)如果異常重啟，那么 binlog 就沒有這條記錄，在后續(xù)的主從復(fù)制時(shí)，將該 binlog 重放之后，從庫的數(shù)據(jù)與主庫的數(shù)據(jù)就產(chǎn)生了不一致。

如果先寫 binlog，再寫 redo log，假如寫完 binlog 系統(tǒng)異常重啟，那么重啟恢復(fù)后由于 redo log 還沒有寫，因此事務(wù)回滾，但是由于 binlog 已經(jīng)成功寫入，在后續(xù)的主從復(fù)制后仍然導(dǎo)致主從不一致。

MySQL 如何回滾與恢復(fù)數(shù)據(jù)的?

前面提到 InnoDB 有個(gè)日志文件叫 redo log，就可以持久化存在磁盤上的，但是在內(nèi)存中也有一份對(duì)應(yīng)的緩沖區(qū)，叫 redo log buffer，為了應(yīng)對(duì)異常重啟，InnoDB 有一個(gè)后臺(tái)線程，每隔 1 秒，就會(huì)把 redo log buffer 中的日志，調(diào)用 write 寫到文件系統(tǒng)的 page cache，然后調(diào)用 fsync 持久化到磁盤。

也就是 redo log buffer -> page cache -> 磁盤 這一過程，每秒都在進(jìn)行，一旦發(fā)生異常重啟，從 redo log 中恢復(fù)就可以了。那具體是怎么恢復(fù)的呢?

事務(wù)提交之前，先寫入 redo log，狀態(tài)是 prepare，表示已經(jīng)準(zhǔn)備好了，隨時(shí)可以提交。

事務(wù)提交之后，redo log 對(duì)應(yīng)的狀態(tài)是 commit，表示已經(jīng)提交。

如果是 prepare 時(shí)發(fā)生異常重啟，mysql 在恢復(fù)后對(duì)狀態(tài)為 prepare 狀態(tài)的事務(wù)進(jìn)行回滾。

如果是 commit 狀態(tài)，表示本來已經(jīng)寫完了，重啟也沒關(guān)系。

如果是 prepare 之前崩潰了，也無所謂，本來就沒有開始寫數(shù)據(jù)，重啟也沒有任何損失。

現(xiàn)在有了 redo log，只能保證數(shù)據(jù)不丟，但還無法保證數(shù)據(jù)可以恢復(fù)到之前的某一時(shí)刻的狀態(tài)。

這就需要 binlog，binlog 是 mysql 自帶的歸檔日志。

假如在寫 binlog 前異常重啟，mysql 在恢復(fù)后對(duì)狀態(tài)為 prepare 狀態(tài)的事務(wù)進(jìn)行回滾。

假如在寫 binlog 后異常重啟，則判斷對(duì)應(yīng)的事務(wù) binlog 是否存在并完整：

a. 如果是，則提交事務(wù);b. 否則，回滾事務(wù)。

你可能會(huì)問，處于 prepare 階段的 redo log 加上完整 binlog，重啟就能恢復(fù)，MySQL 為什么要這么設(shè)計(jì)?

回答：binlog 寫完以后 MySQL 發(fā)生崩潰，這時(shí)候 binlog 已經(jīng)寫入了，之后就會(huì)被從庫(或者用這個(gè) binlog 恢復(fù)出來的庫)使用。所以，在主庫上也要提交這個(gè)事務(wù)。采用這個(gè)策略，主庫和備庫的數(shù)據(jù)就保證了一致性。

還有一個(gè)問題，就是為什么不讓 redo log 也承擔(dān) binlog 的功能?

這是因?yàn)?，redo log 是循環(huán)寫的，寫完后會(huì)從開頭繼續(xù)寫，這樣 redo log 就無法記錄一段時(shí)間內(nèi)的完整操作，這樣歷史日志沒法保留，redo log 也就起不到歸檔的作用。

另一個(gè)原因就是就是 MySQL 系統(tǒng)依賴于 binlog。binlog 作為 MySQL 一開始就有的功能，被用在了很多地方。其中，MySQL 系統(tǒng)高可用的基礎(chǔ)，就是 binlog 復(fù)制。還有很多公司有異構(gòu)系統(tǒng)(比如一些數(shù)據(jù)分析系統(tǒng))，這些系統(tǒng)就靠消費(fèi) MySQL 的 binlog 來更新自己的數(shù)據(jù)。關(guān)掉 binlog 的話，這些下游系統(tǒng)就沒法輸入了。

最后的話

MySQL 的奧妙就在于 redo log 和 binlog 的完美配合，這樣的模式保證了系統(tǒng)可以應(yīng)對(duì)異常重啟，也保證了數(shù)據(jù)可以恢復(fù)到某一天的任意一秒的狀態(tài)，當(dāng)然這是在有完整備份的前提下，其實(shí)這樣的設(shè)計(jì)可以遷移到平時(shí)軟件設(shè)計(jì)上，比如說涉及用戶輸入的系統(tǒng)，在發(fā)生異常重啟、掉電的情況下，如何讓用戶的輸入不丟失，系統(tǒng)的配置文件比較復(fù)雜被改亂了，如何快速恢復(fù)到某一天之前的配置狀態(tài)等。

此外 MySQL 是面試必問，找工作的同學(xué)建議多準(zhǔn)備下，《MySQL實(shí)戰(zhàn)45講》我已經(jīng)全部學(xué)完，對(duì) MySQL 的原理了解得更加深入，課程質(zhì)量高，學(xué)起來不累，推薦給你，可以掃下方二維碼直接購買，加個(gè)好友，和我一起學(xué)習(xí) MySQL 吧。

本文轉(zhuǎn)載自微信公眾號(hào)「Python七號(hào)」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系Python七號(hào)公眾號(hào)。