久久亚洲国产高清,亚洲美女在线播放,亚洲高清影院

1.引言

大家好，我是小?，一個(gè)漂泊江湖多年的 985 非科班程序員，曾混跡于國(guó)企、互聯(lián)網(wǎng)大廠和創(chuàng)業(yè)公司的后臺(tái)開(kāi)發(fā)攻城獅。

最近發(fā)現(xiàn)，不管是初入職場(chǎng)，還是已經(jīng)工作很多年的開(kāi)發(fā)同學(xué)。雖然接觸數(shù)據(jù)庫(kù)（尤其是 MySQL）很長(zhǎng)時(shí)間了，但對(duì)于 SQL 語(yǔ)句的執(zhí)行過(guò)程都知之甚少，或者一知半解。

而 MySQL 的執(zhí)行流程也確實(shí)是一個(gè)復(fù)雜的過(guò)程，它涉及多個(gè)組件的協(xié)同工作，故而在面試或者工作的過(guò)程中很容易陷入迷惑和誤區(qū)。

SQL 執(zhí)行過(guò)程

所以，這篇文章我將以 MySQL 常見(jiàn)的 InnoDB 存儲(chǔ)引擎為例，為大家詳細(xì)介紹 SQL 語(yǔ)句的執(zhí)行流程。從連接器開(kāi)始，一直到事務(wù)的提交和數(shù)據(jù)持久化。

我們先來(lái)看一張圖：

首先，客戶端會(huì)與 MySQL Server 連接，并發(fā)送增刪改查語(yǔ)句，Server 收到語(yǔ)句后會(huì)創(chuàng)建一個(gè)解析樹(shù)，進(jìn)行優(yōu)化。

優(yōu)化器優(yōu)化語(yǔ)句時(shí)，會(huì)評(píng)估各種索引的代價(jià)，選擇合適的索引，然后通過(guò)執(zhí)行器調(diào)用 InnoDB 引擎的接口來(lái)執(zhí)行語(yǔ)句。

2. 具體執(zhí)行流程

1. 連接器（Connection Manager）

MySQL 的執(zhí)行流程始于連接器。當(dāng)客戶端請(qǐng)求與 MySQL 建立連接時(shí)，連接器負(fù)責(zé)處理這些連接請(qǐng)求。

它驗(yàn)證客戶端的身份和權(quán)限，然后分配一個(gè)線程來(lái)處理該連接。MySQL 每個(gè)連接線程會(huì)創(chuàng)建一個(gè)會(huì)話（session），在這個(gè)會(huì)話中，客戶端可以發(fā)送 SQL 語(yǔ)句進(jìn)行增刪改查等操作。

2. 分析器（Parser）

一旦連接建立，客戶端可以發(fā)送待執(zhí)行的 SQL 語(yǔ)句。

這些 SQL 語(yǔ)句首先被送到分析器，分析器的任務(wù)是解析 SQL 語(yǔ)句，確定其語(yǔ)法是否正確，并將其轉(zhuǎn)化為一個(gè)內(nèi)部數(shù)據(jù)結(jié)構(gòu)，以供 MySQL 后續(xù)使用。

如果 SQL 語(yǔ)句有語(yǔ)法錯(cuò)誤，分析器將返回錯(cuò)誤信息給客戶端。

3. 優(yōu)化器（Optimizer）

一旦 SQL 語(yǔ)句被成功解析，接下來(lái)進(jìn)入優(yōu)化器的領(lǐng)域。

優(yōu)化器的任務(wù)是評(píng)估該 SQL 語(yǔ)句不同的執(zhí)行計(jì)劃，并選擇最優(yōu)的執(zhí)行計(jì)劃。它會(huì)考慮哪些索引可用，哪種連接方法效率最高，以及如何最小化查詢的成本。

4. 執(zhí)行器（Executor）

執(zhí)行器接收到優(yōu)化器生成的執(zhí)行計(jì)劃后，它開(kāi)始執(zhí)行實(shí)際的查詢操作。

執(zhí)行器會(huì)按照?qǐng)?zhí)行計(jì)劃中的步驟，調(diào)用 InnoDB 引擎層的邏輯并從數(shù)據(jù)表中獲取數(shù)據(jù)，然后進(jìn)行排序、聚合、過(guò)濾等操作。

最終，執(zhí)行器將結(jié)果返回給客戶端。

5. 寫(xiě) undo log

當(dāng)執(zhí)行器執(zhí)行修改數(shù)據(jù)的操作時(shí)，MySQL 的 InnoDB 引擎首先會(huì)開(kāi)啟事務(wù)，為這些修改生成 undo log（也叫回滾日志）。

回滾日志用于記錄修改前的數(shù)據(jù)，以便在事務(wù)回滾時(shí)恢復(fù)原始數(shù)據(jù)。如果事務(wù)執(zhí)行失敗，MySQL 可以使用undo log 來(lái)撤銷(xiāo)已經(jīng)進(jìn)行的修改。

6. 記錄緩存（Record Cache），查找索引

MySQL 使用記錄緩存來(lái)存儲(chǔ)從數(shù)據(jù)表中讀取的數(shù)據(jù)行，這個(gè)緩存可以加速對(duì)頻繁讀取的數(shù)據(jù)的訪問(wèn)，避免了每次都要從磁盤(pán)讀取的開(kāi)銷(xiāo)。

當(dāng)數(shù)據(jù)存在于內(nèi)存中時(shí)，只需要更新內(nèi)存即可；反之則可能需要從磁盤(pán)中讀取數(shù)據(jù)，再更新磁盤(pán)數(shù)據(jù)。

這取決于 MySQL 的索引類型，可分為兩種：

唯一索引：索引列的值唯一，非主鍵的唯一索引允許有空值，主鍵索引不允許空值；
普通索引：沒(méi)有特殊限制，允許重復(fù)值和空值；

當(dāng) SQL 操作數(shù)據(jù)到達(dá)這一步時(shí)，InnoDB 首先會(huì)判斷數(shù)據(jù)頁(yè)是否在內(nèi)存中：

在內(nèi)存中，判斷更新的索引是否是唯一索引。如果是唯一索引，則判斷更新后是否破壞數(shù)據(jù)的一致性，不會(huì)的話就直接更新內(nèi)存中的數(shù)據(jù)頁(yè)；如果是非唯一索引，直接更新內(nèi)存中的數(shù)據(jù)頁(yè)。
不在內(nèi)存中：判斷更新的索引是否是唯一索引。如果是唯一索引，由于需要保證更新后的唯一性，所以需要立即把數(shù)據(jù)頁(yè)從磁盤(pán)加載到內(nèi)存，然后更新數(shù)據(jù)頁(yè)；如果是非唯一索引，則將數(shù)據(jù)更新的操作記錄到 change buffer，它將在在空閑時(shí)異步更新到磁盤(pán)。

change buffer

change buffer 是 InnoDB 引擎的特性之一，在 MySQL 5.5 之前，change buffer 的主要作用是提高數(shù)據(jù)插入的性能，又被稱作 insert buffer。

我們知道，當(dāng)非聚集索引插入時(shí)，數(shù)據(jù)會(huì)按主鍵的順序存放，所以葉子節(jié)點(diǎn)可能需要離散地訪問(wèn)數(shù)據(jù)索引頁(yè)，每次索引頁(yè)更新時(shí)，都需要刷新磁盤(pán)。而每次讀寫(xiě)磁盤(pán)的時(shí)間都會(huì)很久，故而導(dǎo)致插入性能較低。

而 insert buffer 開(kāi)啟后，會(huì)先判斷聚集索引頁(yè)是否存在于緩沖池中，如果有，直接插入；如果不在，先放入一個(gè)插入緩沖區(qū)進(jìn)行排序，再以一定的頻率合并（merge）更新索引頁(yè)。

如圖所示，insert buffer 將多次操作合并起來(lái)，以減少隨機(jī) I/O，減少和磁盤(pán)交互的操作，從而提升整體的性能。

MySQL5.5 之后，逐漸加入了數(shù)據(jù)刪除和修改的緩沖類型，統(tǒng)一叫 change buffer。

一言概之，change buffer 主要作用是將二級(jí)索引的增刪改（IDU）操作緩存下來(lái)，以減少隨機(jī) I/O，達(dá)到操作合并的效果。

由于唯一索引需要立即 IO 到磁盤(pán)，以保證數(shù)據(jù)不沖突，因此唯一索引沒(méi)有 change buffer 機(jī)制。

8. 寫(xiě) redo log

在 SQL 執(zhí)行的過(guò)程中，InnoDB 還會(huì)記錄所有的數(shù)據(jù)修改操作到 redo log（重做日志）中。

重做日志是一個(gè)循環(huán)寫(xiě)入的日志文件，它記錄了事務(wù)的每個(gè)步驟，以確保數(shù)據(jù)的持久性。如果系統(tǒng)崩潰， InnoDB 可以根據(jù) redo log 來(lái)恢復(fù)未提交的事務(wù)，以保持?jǐn)?shù)據(jù)的一致性。

注意，redo log 分為 prepare 和 commit 兩個(gè)狀態(tài)。在事務(wù)執(zhí)行的過(guò)程中，InnoDB 把數(shù)據(jù)頁(yè)的更改寫(xiě)入到 redo log 時(shí)，其狀態(tài)為 prepare 狀態(tài)。

9. 寫(xiě) binlog，提交事務(wù)

除了 redo log，MySQL 還會(huì)記錄 binlog（二進(jìn)制日志）。

二進(jìn)制日志記錄了所有執(zhí)行的 SQL 語(yǔ)句，而不僅僅是數(shù)據(jù)修改，這對(duì)于數(shù)據(jù)復(fù)制和恢復(fù)非常重要，因?yàn)樗梢源_保不僅數(shù)據(jù)的狀態(tài)被恢復(fù)，連同執(zhí)行的 SQL 操作也能被還原。

當(dāng) InnoDB 引擎層寫(xiě)好 redo log 后，會(huì)通知 MySQL Server 層已將更新操作已經(jīng)執(zhí)行完成。這時(shí)，MySQL Server 將執(zhí)行的 SQL 寫(xiě)入到 binlog，然后通知 InnoDB 將 redo log 置為 commit 狀態(tài)，事務(wù)提交成功。

注意，一個(gè)事務(wù)提交成功的判斷依據(jù)在于是否寫(xiě)入到 binlog 日志中。若已寫(xiě)入，即便 MySQL Server 崩潰，之后也可以根據(jù) redo log 和 binlog 進(jìn)行恢復(fù)。

3. redo log 和 binlog

上面說(shuō)到了，當(dāng)事務(wù)提交時(shí)，分為兩個(gè)階段，我們總結(jié)一下：

數(shù)據(jù)更新時(shí)，先更新內(nèi)存中的數(shù)據(jù)頁(yè)，將更新操作寫(xiě)入到 redo log 中，此時(shí) redo log 進(jìn)入 prepare 狀態(tài)。并通知 MySQL Server 更新執(zhí)行完了，隨時(shí)可以提交；
MySQL Server 根據(jù)持久化的模式是 STATEMENT 還是 ROW，決定將更新的 SQL 還是數(shù)據(jù)行寫(xiě)入到 binlog，然后調(diào)用 InnoDB 的接口將 redo log 設(shè)置為 commit 狀態(tài)，更新完成。

細(xì)心的同學(xué)可能會(huì)問(wèn)了，為什么 binlog 只需要提交一次，而 redo 要提交兩次？而已經(jīng)有 redo log了，還需要 binlog 干啥？

要解答這個(gè)問(wèn)題，得從兩種日志的本質(zhì)區(qū)別說(shuō)起。

redo log

用于記錄 InnoDB 引擎下事務(wù)的日志，支持崩潰數(shù)據(jù)自修復(fù)。

如果只寫(xiě) binlog，而不寫(xiě) redo log，當(dāng) MySQL 發(fā)生故障宕機(jī)時(shí)，就可能會(huì)丟失最近執(zhí)行的事務(wù)數(shù)據(jù)。

binlog

binlog 記錄了 MySQL Server 層對(duì)數(shù)據(jù)庫(kù)執(zhí)行的所有更改操作，用于數(shù)據(jù)歸檔、數(shù)據(jù)備份及主從復(fù)制等。

如果寫(xiě)了 redo log 直接提交，不經(jīng)過(guò) prepare 階段，那么這個(gè)過(guò)程在發(fā)生故障時(shí)，如果 MySQL 部署了主從節(jié)點(diǎn)，主節(jié)點(diǎn)可以根據(jù) redo log 恢復(fù)數(shù)據(jù)，但從節(jié)點(diǎn)就無(wú)法同步這部分?jǐn)?shù)據(jù)。

從上圖可以看出，MySQL 主從復(fù)制時(shí)，主要依賴 Master 節(jié)點(diǎn)的 binlog，Slave 節(jié)點(diǎn)的 relay-log 和 3 個(gè)重要線程。

log dump線程

當(dāng)從節(jié)點(diǎn)連接主節(jié)點(diǎn)時(shí)，主節(jié)點(diǎn)會(huì)為其創(chuàng)建一個(gè) log dump 線程，用于讀取和發(fā)送 binlog 內(nèi)容。在讀取 binlog 中時(shí)，log dump 線程會(huì)對(duì)主節(jié)點(diǎn)上的 bin-log 加鎖，直到讀取完成，鎖釋放。

主節(jié)點(diǎn)會(huì)為自己的每一個(gè)從節(jié)點(diǎn)創(chuàng)建一個(gè) log dump 線程。