MySQL面試常問(wèn):一條語(yǔ)句提交后,數(shù)據(jù)庫(kù)都做了什么?
今天大部分程序需要處理的數(shù)據(jù),都來(lái)自數(shù)據(jù)庫(kù),尤其是關(guān)系型數(shù)據(jù)庫(kù),那么一條 SQL 提交到數(shù)據(jù)庫(kù)之后,數(shù)據(jù)庫(kù)都做了些什么?如果不懂這些問(wèn)題,就無(wú)法更好的使用數(shù)據(jù)庫(kù),更無(wú)法回答好面試官的問(wèn)題?,F(xiàn)在流行的開(kāi)源數(shù)據(jù)庫(kù),非 MySQL 莫屬,面試中 MySQL 也是必問(wèn),于是我就學(xué)習(xí)了專欄《MySQL實(shí)戰(zhàn)45講》,今天的文章試著回答以下兩個(gè)問(wèn)題:
1、一條 SQL 語(yǔ)句提交到數(shù)據(jù)庫(kù)之后,數(shù)據(jù)庫(kù)都會(huì)執(zhí)行哪些動(dòng)作?
2、MySQL 是如何恢復(fù)到某一天的某一秒的狀態(tài)?
先來(lái)看看一條讀操作 SQL 的查詢過(guò)程:
連接器
客戶端在提交 SQL 語(yǔ)句之前,你需要先連接上數(shù)據(jù)庫(kù),也就是說(shuō)要提供用戶名密碼登陸,這便是連接器發(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)斷開(kāi),此時(shí)再查詢就會(huì)報(bào)錯(cuò)。
一個(gè)比較好的方案是使用數(shù)據(jù)庫(kù)連接池。Python 編程可以使用第三方庫(kù) DBUtils 來(lái)管理數(shù)據(jù)庫(kù)連接池。
查詢緩存
緩存可以快速返回命中的查詢,在使用上的感受就是同一個(gè) SQL,第二次查詢時(shí)結(jié)果是立刻顯示的。查詢緩存中以 SQL 語(yǔ)句作為 KEY,查詢結(jié)果作為 VALUE。
如果你的查詢能夠直接在這個(gè)緩存中找到 key,并且具有對(duì)該表的相應(yīng)的權(quán)限,那么這個(gè) value 就會(huì)被直接返回給客戶端。
如果沒(méi)有找到,會(huì)走接下來(lái)流程,一旦查到結(jié)果,結(jié)果還是會(huì)保存在查詢緩存中。
分析器
如果沒(méi)有命中查詢緩存,SQL 語(yǔ)句就會(huì)傳給分析器進(jìn)行詞法分析,分析是否有語(yǔ)法錯(cuò)誤,解析中表名,字段名等等,其實(shí)不僅僅數(shù)據(jù)庫(kù)有分析器,很多開(kāi)源的工具也有分析 SQL 的功能,比如 Python 可以使用 python-sqlparse,JAVA 可以使用 druid(阿里巴巴開(kāi)源)。
解析出表名之后,檢查一下用戶對(duì)表的權(quán)限,如果權(quán)限符合就進(jìn)行下一步優(yōu)化器。
優(yōu)化器
經(jīng)過(guò)了分析器,MySQL 就知道你要做什么了。
在開(kāi)始執(zhí)行之前,還要先經(jīng)過(guò)優(yōu)化器的處理。優(yōu)化器是在表里面有多個(gè)索引的時(shí)候,決定使用哪個(gè)索引;或者在一個(gè)語(yǔ)句有多表關(guān)聯(lián)(join)的時(shí)候,決定各個(gè)表的連接順序。
執(zhí)行器
MySQL 通過(guò)分析器知道了你要做什么,通過(guò)優(yōu)化器知道了該怎么做,于是就進(jìn)入了執(zhí)行器階段,開(kāi)始執(zhí)行語(yǔ)句。開(kāi)始執(zhí)行的時(shí)候,要先判斷一下你對(duì)這個(gè)表 T 有沒(méi)有執(zhí)行查詢的權(quán)限,如果沒(méi)有,就會(huì)返回沒(méi)有權(quán)限的錯(cuò)誤。
也許你會(huì)問(wèn),權(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í)行是做不到的。
比如說(shuō):
- select * from T where ID=10;
如果 ID 字段沒(méi)有索引,那么執(zhí)行器的執(zhí)行流程是這樣的:調(diào)用 InnoDB 引擎接口取這個(gè)表的第一行,判斷 ID 值是不是 10,如果不是則跳過(guò),如果是則將這行存在結(jié)果集中;調(diào)用引擎接口取“下一行”,重復(fù)相同的判斷邏輯,直到取到這個(gè)表的最后一行。執(zhí)行器將上述遍歷過(guò)程中所有滿足條件的行組成的記錄集作為結(jié)果集返回給客戶端。至此,這個(gè)語(yǔ)句就執(zhí)行完成了。
對(duì)于有索引的表,執(zhí)行的邏輯也差不多。第一次調(diào)用的是“取滿足條件的第一行”這個(gè)接口,之后循環(huán)取“滿足條件的下一行”這個(gè)接口,這些接口都是引擎中已經(jīng)定義好的。
說(shuō)到存儲(chǔ)引擎,MySQl 支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎?,F(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB,它從 MySQL 5.5.5 版本開(kāi)始成為了默認(rèn)存儲(chǔ)引擎。也就是說(shuō),你執(zhí)行 create table 建表的時(shí)候,如果不指定引擎類型,默認(rèn)使用的就是 InnoDB。不過(guò),你也可以通過(guò)指定存儲(chǔ)引擎的類型來(lái)選擇別的引擎,比如在 create table 語(yǔ)句中使用 engine=memory, 來(lái)指定使用內(nèi)存引擎創(chuàng)建表。不同存儲(chǔ)引擎的表數(shù)據(jù)存取方式不同,支持的功能也不同。
接下來(lái),看一看寫操作的執(zhí)行過(guò)程,其中 redo log 和 binlog 又起到了什么作用?
寫操作
首先,可以確定的說(shuō),查詢語(yǔ)句的那一套流程,更新語(yǔ)句也是同樣會(huì)走一遍。
與查詢流程不一樣的是,更新流程還涉及兩個(gè)重要的日志模塊,它們正是redo log(重做日志)和 binlog(歸檔日志)。如果接觸 MySQL,那這兩個(gè)詞肯定是繞不過(guò)的,redo log 和 binlog 在設(shè)計(jì)上有很多有意思的地方,這些設(shè)計(jì)思路也可以用到你自己的程序里。
以更新操作為例,假如 SQL 語(yǔ)句為:
- update table_a set count = count + 1 where id = 2
- 執(zhí)行器先找引擎取 id=2 這一行。id 是主鍵,引擎直接用樹(shù)搜索找到這一行。如果 id=2 這一行所在的數(shù)據(jù)頁(yè)本來(lái)就在內(nèi)存中,就直接返回給執(zhí)行器;否則,需要先從磁盤讀入內(nèi)存,然后再返回。
- 執(zhí)行器拿到引擎給的行數(shù)據(jù),把這個(gè)值加上 1,比如原來(lái)是 N,現(xiàn)在就是 N+1,得到新的一行數(shù)據(jù),再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
- 引擎將這行新數(shù)據(jù)更新到內(nèi)存中,同時(shí)將這個(gè)更新操作記錄到 redo log 里面,此時(shí) redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了,隨時(shí)可以提交事務(wù)。
- 執(zhí)行器生成這個(gè)操作的 binlog,并把 binlog 寫入磁盤。
- 執(zhí)行器調(diào)用引擎的提交事務(wù)接口,引擎把剛剛寫入的 redo log 改成提交(commit)狀態(tài),更新完成。
這里得說(shuō)明一下,redo log 和 binlog 都是日志文件,為了防止異常重啟、掉電、恢復(fù)數(shù)據(jù)等場(chǎng)景,這些日志文件都會(huì)持久化到磁盤上。為了防止頻繁的訪問(wèn)磁盤,寫 redo log 前會(huì)先寫到內(nèi)存中的 redo log buffer,再定期一起寫到磁盤。
但是這兩個(gè) log 文件又有所區(qū)別:
- redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 層實(shí)現(xiàn)的,所有引擎都可以使用。
- redo log 是物理日志,記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”;binlog 是邏輯日志,記錄的是這個(gè)語(yǔ)句的原始邏輯,比如“給 id=2 這一行的 c 字段加 1 ”。
- redo log 是循環(huán)寫的,空間固定會(huì)用完;binlog 是可以追加寫入的。“追加寫”是指 binlog 文件寫到一定大小后會(huì)切換到下一個(gè),并不會(huì)覆蓋以前的日志。
- redo log 用于回滾,binlog 用于恢復(fù)。
如果將 MySQL 恢復(fù)到某一天的某一秒
要做到這一點(diǎn)有個(gè)前提,就是要對(duì) MySQL 數(shù)據(jù)庫(kù)定期做整庫(kù)備份。這里的定期取決于系統(tǒng)的重要性,可以是一天一備,也可以是一周一備。
當(dāng)需要恢復(fù)到指定的某一秒時(shí),比如某天下午兩點(diǎn)發(fā)現(xiàn)中午十二點(diǎn)有一次誤刪表,需要找回?cái)?shù)據(jù),那你可以這么做:
- 首先,找到最近的一次全量備份,如果你運(yùn)氣好,可能就是昨天晚上的一個(gè)備份,從這個(gè)備份恢復(fù)到臨時(shí)庫(kù);
- 然后,從備份的時(shí)間點(diǎn)開(kāi)始,將備份的 binlog 依次取出來(lái),重放到中午誤刪表之前的那個(gè)時(shí)刻。這樣你的臨時(shí)庫(kù)就跟誤刪之前的線上庫(kù)一樣了。
- 最后,你可以把表數(shù)據(jù)從臨時(shí)庫(kù)取出來(lái),按需要恢復(fù)到線上庫(kù)去。
為什么要兩階段提交
前面寫操作中的提到,寫磁盤前先寫 redo log,此時(shí) redo log 狀態(tài)為 prepare,然后再寫 binlog,寫完 binlog 后,再提交,redo log 才處于 commit 狀態(tài)。
為什么要等 binlog 寫完才能提交呢?這是因?yàn)榧偃?binlog 沒(méi)寫完就提交,此時(shí)如果異常重啟,那么 binlog 就沒(méi)有這條記錄,在后續(xù)的主從復(fù)制時(shí),將該 binlog 重放之后,從庫(kù)的數(shù)據(jù)與主庫(kù)的數(shù)據(jù)就產(chǎn)生了不一致。
如果先寫 binlog,再寫 redo log,假如寫完 binlog 系統(tǒng)異常重啟,那么重啟恢復(fù)后由于 redo log 還沒(méi)有寫,因此事務(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 -> 磁盤 這一過(guò)程,每秒都在進(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),表示本來(lái)已經(jīng)寫完了,重啟也沒(méi)關(guān)系。
如果是 prepare 之前崩潰了,也無(wú)所謂,本來(lái)就沒(méi)有開(kāi)始寫數(shù)據(jù),重啟也沒(méi)有任何損失。
現(xiàn)在有了 redo log,只能保證數(shù)據(jù)不丟,但還無(wú)法保證數(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ì)問(wèn),處于 prepare 階段的 redo log 加上完整 binlog,重啟就能恢復(fù),MySQL 為什么要這么設(shè)計(jì)?
回答:binlog 寫完以后 MySQL 發(fā)生崩潰,這時(shí)候 binlog 已經(jīng)寫入了,之后就會(huì)被從庫(kù)(或者用這個(gè) binlog 恢復(fù)出來(lái)的庫(kù))使用。所以,在主庫(kù)上也要提交這個(gè)事務(wù)。采用這個(gè)策略,主庫(kù)和備庫(kù)的數(shù)據(jù)就保證了一致性。
還有一個(gè)問(wèn)題,就是為什么不讓 redo log 也承擔(dān) binlog 的功能?
這是因?yàn)椋瑀edo log 是循環(huán)寫的,寫完后會(huì)從開(kāi)頭繼續(xù)寫,這樣 redo log 就無(wú)法記錄一段時(shí)間內(nèi)的完整操作,這樣歷史日志沒(méi)法保留,redo log 也就起不到歸檔的作用。
另一個(gè)原因就是就是 MySQL 系統(tǒng)依賴于 binlog。binlog 作為 MySQL 一開(kāi)始就有的功能,被用在了很多地方。其中,MySQL 系統(tǒng)高可用的基礎(chǔ),就是 binlog 復(fù)制。還有很多公司有異構(gòu)系統(tǒng)(比如一些數(shù)據(jù)分析系統(tǒng)),這些系統(tǒng)就靠消費(fèi) MySQL 的 binlog 來(lái)更新自己的數(shù)據(jù)。關(guān)掉 binlog 的話,這些下游系統(tǒng)就沒(méi)法輸入了。
最后的話
MySQL 的奧妙就在于 redo log 和 binlog 的完美配合,這樣的模式保證了系統(tǒng)可以應(yīng)對(duì)異常重啟,也保證了數(shù)據(jù)可以恢復(fù)到某一天的任意一秒的狀態(tài),當(dāng)然這是在有完整備份的前提下,其實(shí)這樣的設(shè)計(jì)可以遷移到平時(shí)軟件設(shè)計(jì)上,比如說(shuō)涉及用戶輸入的系統(tǒng),在發(fā)生異常重啟、掉電的情況下,如何讓用戶的輸入不丟失,系統(tǒng)的配置文件比較復(fù)雜被改亂了,如何快速恢復(fù)到某一天之前的配置狀態(tài)等。
此外 MySQL 是面試必問(wèn),找工作的同學(xué)建議多準(zhǔn)備下,《MySQL實(shí)戰(zhàn)45講》我已經(jīng)全部學(xué)完,對(duì) MySQL 的原理了解得更加深入,課程質(zhì)量高,學(xué)起來(lái)不累,推薦給你,可以掃下方二維碼直接購(gòu)買,加個(gè)好友,和我一起學(xué)習(xí) MySQL 吧。
本文轉(zhuǎn)載自微信公眾號(hào)「Python七號(hào)」,可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系Python七號(hào)公眾號(hào)。