一.MySQL的一條查詢語句是怎么運(yùn)行的

一條查詢語句的執(zhí)行過程一般是經(jīng)過連接器,、分析器,、優(yōu)化器、執(zhí)行器等功能模塊,，最后到達(dá)存儲(chǔ)引擎,。

假如在MySQL中有一個(gè)查詢會(huì)話請(qǐng)求，那么大概流程如下：

（1）MySQL客戶端對(duì)MySQL Server的監(jiān)聽端口發(fā)起請(qǐng)求,。

（2）在連接者組件層創(chuàng)建連接,、分配線程，并驗(yàn)證用戶名,、密碼和庫(kù)表權(quán)限,。

（3）如果打開了query_cache，則檢查之,，有數(shù)據(jù)直接返回，沒有繼續(xù)往下執(zhí)行,。

（4）SQL接口組件接收SQL語句,，將SQL語句分解成數(shù)據(jù)結(jié)構(gòu)，并將這個(gè)結(jié)構(gòu)傳遞到后續(xù)步驟中（將SQL語句解析成MySQL認(rèn)識(shí)的語法）,。

（5）查詢優(yōu)化器組件生成查詢路徑樹，并選舉一條最優(yōu)的查詢路徑。

（6）調(diào)用存儲(chǔ)引擎接口，打開表,，執(zhí)行查詢,，檢查存儲(chǔ)引擎緩存中是否有對(duì)應(yīng)的緩存記錄,，如果沒有就繼續(xù)往下執(zhí)行,。

（7）到磁盤物理文件中尋找數(shù)據(jù)。

（8）當(dāng)查詢到所需要的數(shù)據(jù)之后,，先寫入存儲(chǔ)引擎緩存中,，如果打開了query_cache,，也會(huì)同時(shí)寫進(jìn)去,。

（9）返回?cái)?shù)據(jù)給客戶端,。

（10）關(guān)閉表。

（11）關(guān)閉線程,。

（12）關(guān)閉連接,。

作用：

（1）提供連接協(xié)議：TCP/IP 、SOCKET方式等連接驗(yàn)證,。

（2）提供驗(yàn)證：用戶,、密碼驗(yàn)證。

（3）提供專用連接線程：接收用戶SQL,，返回結(jié)果,。

（1）接收上層傳送的SQL語句。

（2）語法驗(yàn)證模塊：驗(yàn)證語句語法,是否滿足SQL_MODE,。

（3）語義檢查：判斷SQL語句的類型：

DDL ：數(shù)據(jù)定義語言

DCL ：數(shù)據(jù)控制語言

DML ：數(shù)據(jù)操作語言

DQL：數(shù)據(jù)查詢語言

...

（4）權(quán)限檢查：用戶對(duì)庫(kù)表有沒有權(quán)限,。

（5）解析器：對(duì)語句執(zhí)行前,進(jìn)行預(yù)處理，生成解析樹(執(zhí)行計(jì)劃),說白了就是生成多種執(zhí)行方案,。

（6）優(yōu)化器：根據(jù)解析器得出的多種執(zhí)行計(jì)劃,，進(jìn)行判斷，選擇最優(yōu)的執(zhí)行計(jì)劃,。

        代價(jià)模型：資源（CPU IO MEM）的耗損評(píng)估性能好壞,。

（7）執(zhí)行器：根據(jù)最優(yōu)執(zhí)行計(jì)劃，執(zhí)行SQL語句,，產(chǎn)生執(zhí)行結(jié)果,。

（8）提供查詢緩存（默認(rèn)是沒開啟的）,，會(huì)使用redis tair替代查詢緩存功能。

（9）提供日志記錄（日志管理章節(jié)）：binlog,，默認(rèn)是沒開啟的,。

二.MySQL的一條更新語句是怎么運(yùn)行的

0、數(shù)據(jù)更新時(shí)執(zhí)行器先找buffer pool緩存池中,，如果在緩沖池中,，同時(shí)返回給執(zhí)行器。

1,、如果未命中緩存,，需要先從磁盤讀入內(nèi)存，然后再返回給執(zhí)行器,。

2,、不管是否命中緩存，都需要將更新前的舊數(shù)據(jù)寫入到undo中,。

3,、更新內(nèi)存，此時(shí)變成臟數(shù)據(jù),，后續(xù)會(huì)調(diào)用接口將數(shù)據(jù)落盤,。

4.5、同時(shí)將這個(gè)更新操作記錄到redo log里面,，此時(shí)redo log處于 prepare 狀態(tài),。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù),。

6.7,、執(zhí)行器生成這個(gè)操作的binlog，并把binlog寫入磁盤,。

8,、執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的redo log改成提交（commit）狀態(tài),，更新完成,。

9.10.11、數(shù)據(jù)落盤,。

三.MySQL的數(shù)據(jù)是如何保證不丟的

從上面的流程圖可以看出,，MySQL采用了wal機(jī)制。

只要redo log和binlog保證持久化到磁盤,，就能確保MySQL異常重啟后,，數(shù)據(jù)可以恢復(fù)。

1.redo和binlog的落盤策略

redo和binlog的落盤還涉及一個(gè)操作系統(tǒng)緩存,。

innodb_flush_log_at_trx_commit = 0/1/2

1: 表示每次事務(wù)提交時(shí)都將redo log直接持久化到磁盤,。

0：表示每次事務(wù)提交時(shí)都只是把redo log留在redo log buffer中 ，然后每秒刷新redo buffer到OS cache,，再fsync到磁盤,，異常宕機(jī)時(shí)，會(huì)有可能導(dǎo)致丟失一秒內(nèi)事務(wù),。

2：表示每次事務(wù)提交時(shí)都只是把redo log寫到OS cache,，再每秒fsync（）磁盤。異常宕機(jī)時(shí),，會(huì)有可能丟失1秒內(nèi)的事務(wù),。數(shù)據(jù)庫(kù)宕機(jī)不丟失。

sync_binlog= 0/1/n

0：表示每次提交事務(wù)都只write,，不fsync,，每過一秒fsync到磁盤，每一秒刷一次磁盤,。

1：表示每次事務(wù)提交都刷一次磁盤，也就是每次提交事務(wù)都會(huì)執(zhí)行fsync,。

n：（100 200 500）表示每次提交事務(wù)都write到OS cache,，但累積 N 個(gè)事務(wù)后才 fsync 到磁盤。

innodb_flush_log_at_trx_commit=1sync_binlog=1

雙1配置,，數(shù)據(jù)庫(kù)的安全性是最高的，不會(huì)丟事務(wù),。

其中redo和臟數(shù)據(jù)的落盤策略涉及如下參數(shù)：

innodb_flush_method

• fsync的特性：
  

buffer pool的數(shù)據(jù)寫磁盤的時(shí)候，需要先經(jīng)歷OS cache然后在寫磁盤,。

redo buffer的數(shù)據(jù)寫磁盤的時(shí)候,，需要先經(jīng)歷OS cache然后在寫磁盤。

• O_DSYNC:

buffer pool的數(shù)據(jù)寫磁盤的時(shí)候,，需要先經(jīng)歷OS cache然后在寫磁盤。

redo buffer的數(shù)據(jù)寫磁盤的時(shí)候,，穿過OS cache直接寫到磁盤,。

• O_DIRECT：

buffer pool的數(shù)據(jù)寫磁盤的時(shí)候，跨過OS cache然后在寫磁盤,。

redo buffer的數(shù)據(jù)寫磁盤的時(shí)候,，需要先經(jīng)歷OS cache然后在寫磁盤,。

2.二階段提交

步驟：

redo log和binlog都可以用于表示事務(wù)的提交狀態(tài),，而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致,。

在兩階段提交的不同時(shí)刻，MySQL異常重啟會(huì)出現(xiàn)什么現(xiàn)象,。

時(shí)刻 A ,，也就是寫入redo log處于prepare階段之后、寫binlog之前,，發(fā)生了崩潰（crash）,，由于此時(shí)binlog還沒寫，redo log也還沒提交,，所以崩潰恢復(fù)的時(shí)候,，這個(gè)事務(wù)會(huì)回滾。這時(shí)候,，binlog還沒寫,，所以也不會(huì)傳到備庫(kù)。

時(shí)刻 B,，也就是binlog寫完,，redo log還沒commit前發(fā)生crash，崩潰恢復(fù)的時(shí)候根據(jù)reod和binlog有一個(gè)共同的數(shù)據(jù)字段,，叫XID,。崩潰恢復(fù)的時(shí)候，會(huì)按順序掃描redo log：如果碰到既有prepare,、又有commit的redo log,，就直接提交；如果碰到只有parepare,、而沒有commit 的redo log,，就拿著XID去binlog 找對(duì)應(yīng)的事務(wù)，如果找到有,，則提交,，沒有則回滾。

3.組提交

日志寫到redo log buffer是很快的，wirte到page cache也差不多,，但是持久化到磁盤的速度就慢多了,。讓更多的事務(wù)，同時(shí)能夠進(jìn)行fsync就是redo的組提交,。

在并發(fā)更新場(chǎng)景下,，第一個(gè)事務(wù)寫完 redo log buffer 以后，接下來這個(gè)fsync越晚調(diào)用,，組員可能越多,，節(jié)約IOPS的效果就越好。

在執(zhí)行圖中第 4 步把binlog fsync到磁盤時(shí),，如果有多個(gè)事務(wù)的binlog已經(jīng)寫完了,，也是一起持久化的，這樣也可以減少 IOPS 的消耗,。不過通常情況下第 3 步執(zhí)行得會(huì)很快,，所以 binlog 的 write 和 fsync 間的間隔時(shí)間短，導(dǎo)致能集合到一起持久化的binlog比較少,，因此binlog的組提交的效果通常不如redo log的效果那么好,。

如果你想提升binlog組提交的效果，可以通過設(shè)置如下兩個(gè)參數(shù)來實(shí)現(xiàn)：

• binlog_group_commit_sync_delay 參數(shù),，表示延遲多少微秒后才調(diào)用 fsync

• binlog_group_commit_sync_no_delay_count 參數(shù),，表示累積多少次以后才調(diào)用 fsync。

這兩個(gè)條件是或的關(guān)系,，也就是說只要有一個(gè)滿足條件就會(huì)調(diào)用 fsync,。所以，當(dāng) binlog_group_commit_sync_delay 設(shè)置為 0 的時(shí)候,，binlog_group_commit_sync_no_delay_count 也無效了。

這兩個(gè)參數(shù)目的是減少binlog的寫盤次數(shù),。這個(gè)方法是基于“額外的故意等待”來實(shí)現(xiàn)的,，因此可能會(huì)增加語句的響應(yīng)時(shí)間,，但沒有丟失數(shù)據(jù)的風(fēng)險(xiǎn)。

從日志先行和組提交得出結(jié)論,，WAL機(jī)制主要得益于兩個(gè)方面：

• redo log和binlog都是順序?qū)?，磁盤的順序?qū)懕入S機(jī)寫速度要快；

• 組提交機(jī)制,，可以大幅度降低磁盤的IOPS消耗,。

4.臟頁落盤的時(shí)機(jī)

數(shù)據(jù)在內(nèi)存被更新后，由于wal機(jī)制，redo和binlog會(huì)先落盤,，而數(shù)據(jù)臟頁也會(huì)在后續(xù)選擇一定的時(shí)機(jī)落盤,。

redo log大小是固定的，寫完后會(huì)循環(huán)覆蓋寫入,。當(dāng)有新的內(nèi)容要寫入時(shí),，系統(tǒng)必須停止所有的更新操作，將checkpoint向前推進(jìn)到新的位置,，但是在推進(jìn)之前必須將覆蓋部分的所有臟頁都flush到磁盤上,。

此時(shí)整個(gè)系統(tǒng)不能再更新了，TPS會(huì)降為0,，所以這種情況要盡量避免,。

當(dāng)系統(tǒng)內(nèi)存不足，又有新的數(shù)據(jù)頁要更新,，就需要淘汰一些數(shù)據(jù)頁，如果淘汰的是臟頁,，就需要flush到磁盤（如果是干凈頁就直接釋放出來復(fù)用）。

innodb_max_dirty_pages_pct默認(rèn)75%,，LRU內(nèi)的臟塊如果超過75%,，強(qiáng)制性的刷臟。

其中系統(tǒng)后臺(tái)會(huì)有如下操作：

在loop主循環(huán)中又包含兩種操作,，分別是1S和10S的操作

• 每1秒：

（1）日志緩沖刷新到磁盤,，即使這個(gè)事務(wù)還沒有提交。

（2）刷新臟頁到磁盤,。

（3）執(zhí)行合并插入緩沖的操作,。

（4）產(chǎn)生checkpoint。

（5）清除無用的table cache,。

（6）如果當(dāng)前沒有用戶活動(dòng),，就可能切換到background loop。

• 每10秒：

（1）日志緩沖刷新到磁盤,，即使這個(gè)事務(wù)還沒有提交,。

（2）刷新臟頁到磁盤。

（3）執(zhí)行合并插入緩沖的操作,。

（4）刪除無用的undo頁,。

（5）產(chǎn)生checkpoint,。

5.doublewrite的實(shí)現(xiàn)機(jī)制

另外從更新流程圖里面也可以看出數(shù)據(jù)不是直接落盤的,。

double write分為兩部分：一部分是內(nèi)存中的double write buffer ,，大小為2MB（16k一個(gè)頁，一共128個(gè)頁）,，第二部分是磁盤共享表空間的128個(gè)數(shù)據(jù)頁,，在對(duì)臟頁進(jìn)行落盤的時(shí)候，并不是直接進(jìn)行落盤,，而是先復(fù)制到double write buffer,，然后再分別寫入到共享表空間，然后再寫入表空間,。

為什么要有雙寫機(jī)制,？

部分寫的問題：

頁面的刷新會(huì)遇到部分寫的問題，也就是說對(duì)于只寫了其中一個(gè)頁面,，只寫了一部分的內(nèi)容,，在數(shù)據(jù)庫(kù)崩潰后，傳統(tǒng)的數(shù)據(jù)庫(kù)會(huì)使用redo log進(jìn)行恢復(fù),，恢復(fù)的原理是通過redo對(duì)數(shù)據(jù)也進(jìn)行重新進(jìn)行物理操作,，但是如果這個(gè)數(shù)據(jù)頁本身發(fā)生了損壞，那innodb的頁面大小是16KB,，但是寫入過程中只寫了4KB（操作系統(tǒng)僅僅保證512字節(jié)寫入的完整性）,，這個(gè)是時(shí)候因?yàn)轫撁娌皇峭暾模虼瞬荒芡ㄟ^redo來進(jìn)行恢復(fù),。redo恢復(fù)的前提條件是頁是完整的,。那么redo對(duì)其進(jìn)行重做也是沒有作用的，innodb的二次寫,，在寫入的時(shí)候,，創(chuàng)造了一個(gè)關(guān)于頁的副本，這樣即使在發(fā)生寫失效后,，也可以通過副本頁,，對(duì)還原重做。