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

1、一條 SQL 語句提交到數據庫之后，數據庫都會執行哪些動作?

2、MySQL 是如何恢復到某一天的某一秒的狀態?

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

連接器

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

連接上去后，MySQL 就創建了一個連接對象放在了內存中，連接對象里有用戶的相關權限信息，此時如果管理員修改了用戶權限，只要用戶不退出重新連接，就不會被影響。

內存資源是比較昂貴的，不用的話就要被清理。如果不做任何操作，在一定的時間之后(默認是 8 小時)，連接器會自動斷開，此時再查詢就會報錯。

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

查詢緩存

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

如果你的查詢能夠直接在這個緩存中找到 key，并且具有對該表的相應的權限，那么這個 value 就會被直接返回給客戶端。

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

分析器

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

解析出表名之后，檢查一下用戶對表的權限，如果權限符合就進行下一步優化器。

優化器

經過了分析器，MySQL 就知道你要做什么了。

在開始執行之前，還要先經過優化器的處理。優化器是在表里面有多個索引的時候，決定使用哪個索引;或者在一個語句有多表關聯(join)的時候，決定各個表的連接順序。

執行器

MySQL 通過分析器知道了你要做什么，通過優化器知道了該怎么做，于是就進入了執行器階段，開始執行語句。開始執行的時候，要先判斷一下你對這個表 T 有沒有執行查詢的權限，如果沒有，就會返回沒有權限的錯誤。

也許你會問，權限驗證前面不是已經做了嗎?為什么這里還要進行權限驗證，因為除了sql 還可能有存儲引擎，觸發器等，在這些對象中，也可能需要調用其它表去獲取數據，也需要權限驗證，前面的階段對于觸發器，存儲引擎這種對象的執行是做不到的。

比如說：

select * from T where ID=10; 

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

對于有索引的表，執行的邏輯也差不多。第一次調用的是“取滿足條件的第一行”這個接口，之后循環取“滿足條件的下一行”這個接口，這些接口都是引擎中已經定義好的。

說到存儲引擎，MySQl 支持 InnoDB、MyISAM、Memory 等多個存儲引擎。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認存儲引擎。也就是說，你執行 create table 建表的時候，如果不指定引擎類型，默認使用的就是 InnoDB。不過，你也可以通過指定存儲引擎的類型來選擇別的引擎，比如在 create table 語句中使用 engine=memory, 來指定使用內存引擎創建表。不同存儲引擎的表數據存取方式不同，支持的功能也不同。

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

寫操作

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

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

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

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

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

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

但是這兩個 log 文件又有所區別：

1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 層實現的，所有引擎都可以使用。
2. redo log 是物理日志，記錄的是“在某個數據頁上做了什么修改”;binlog 是邏輯日志，記錄的是這個語句的原始邏輯，比如“給 id=2 這一行的 c 字段加 1 ”。
3. redo log 是循環寫的，空間固定會用完;binlog 是可以追加寫入的。“追加寫”是指 binlog 文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志。
4. redo log 用于回滾，binlog 用于恢復。

如果將 MySQL 恢復到某一天的某一秒

要做到這一點有個前提，就是要對 MySQL 數據庫定期做整庫備份。這里的定期取決于系統的重要性，可以是一天一備，也可以是一周一備。

當需要恢復到指定的某一秒時，比如某天下午兩點發現中午十二點有一次誤刪表，需要找回數據，那你可以這么做：

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

為什么要兩階段提交

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

為什么要等 binlog 寫完才能提交呢?這是因為假如 binlog 沒寫完就提交，此時如果異常重啟，那么 binlog 就沒有這條記錄，在后續的主從復制時，將該 binlog 重放之后，從庫的數據與主庫的數據就產生了不一致。

如果先寫 binlog，再寫 redo log，假如寫完 binlog 系統異常重啟，那么重啟恢復后由于 redo log 還沒有寫，因此事務回滾，但是由于 binlog 已經成功寫入，在后續的主從復制后仍然導致主從不一致。

MySQL 如何回滾與恢復數據的?

前面提到 InnoDB 有個日志文件叫 redo log，就可以持久化存在磁盤上的，但是在內存中也有一份對應的緩沖區，叫 redo log buffer，為了應對異常重啟，InnoDB 有一個后臺線程，每隔 1 秒，就會把 redo log buffer 中的日志，調用 write 寫到文件系統的 page cache，然后調用 fsync 持久化到磁盤。

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

事務提交之前，先寫入 redo log，狀態是 prepare，表示已經準備好了，隨時可以提交。

事務提交之后，redo log 對應的狀態是 commit，表示已經提交。

如果是 prepare 時發生異常重啟，mysql 在恢復后對狀態為 prepare 狀態的事務進行回滾。

如果是 commit 狀態，表示本來已經寫完了，重啟也沒關系。

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

現在有了 redo log，只能保證數據不丟，但還無法保證數據可以恢復到之前的某一時刻的狀態。

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

假如在寫 binlog 前異常重啟，mysql 在恢復后對狀態為 prepare 狀態的事務進行回滾。

假如在寫 binlog 后異常重啟，則判斷對應的事務 binlog 是否存在并完整：

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

你可能會問，處于 prepare 階段的 redo log 加上完整 binlog，重啟就能恢復，MySQL 為什么要這么設計?

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

還有一個問題，就是為什么不讓 redo log 也承擔 binlog 的功能?

這是因為，redo log 是循環寫的，寫完后會從開頭繼續寫，這樣 redo log 就無法記錄一段時間內的完整操作，這樣歷史日志沒法保留，redo log 也就起不到歸檔的作用。

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

最后的話

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

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

本文轉載自微信公眾號「Python七號」，可以通過以下二維碼關注。轉載本文請聯系Python七號公眾號。