Mysql架構以及SQL陳述句的執行流程

在平時的開發程序中，我們使用的資料庫大多數看到的只是資料庫的一個整體，一般都是輸入一條陳述句，回傳一個結果集，但是如果我們不知道其內部執行的細節，當我們在碰到一些例外情況的時候，可能不知道如何下手，更別談對sql陳述句的優化了，在這篇文章中，我來簡單的介紹下mysql設計的架構以及當我們輸入一條SQL陳述句的時候在mysql內部是如何執行的，

我們來舉個例子，比如有如下一個簡單的查詢陳述句

select * from T where id = 1;

我們如果直接執行他，只會看到回傳的結果集，可是其內部究竟經歷了什么呢？

下圖是mysql基本的架構的示意圖，mysql在執行一條sql陳述句的時候經過的大概也是下圖所示的流程，

從上圖我們可以看出，mysql的架構可以簡單的分為server層和存盤引擎兩部分，在server層中，我們可以看到連接器，查詢快取，分析器，優化器，執行器等等結構，在這些結構中，提供了mysql的大多數的核心的功能，包括所有的內置函式的實作，以及所有跨存盤引擎層的功能的實作，

而存盤引擎層提供的服務是對資料的存取，其架構模式是插件式的，我們熟知的存盤引擎比如innoDB，MyISAM，Memory等等，而我們最常用的存盤引擎肯定就是InnoDB了，其對事物的支持以及對行鎖的支持是對現在所有高并發高一致性業務重要保障，在MySQL 5.5之后，InnoDB也成為了MySQL的默認的存盤引擎，也就是說，當我們使用create table建表的時候，無需指定engine = InnoDB，其默認使用就是InnoDB，如果我們想要使用其他存盤引擎則需要顯式指定，

在圖中我們可以看到，所有的存盤引擎共用一個server層，接下來我來說一下一條陳述句整個的執行流程，同時講解下每個組件的作用，

連接器

對資料庫進行操作的第一步就是與資料庫建立一個TCP連接，這個作業就是資料庫連接器所提供的，連接器負責跟客戶端建立連接、獲取權限、維持和管理連接，我們對資料庫連接時的操作是這樣的：

mysql -h$host -P$port -u$user -p

接下來mysql會提示我們輸入密碼，我們也可以同時通過-p引數一起輸入密碼，但是并不建議這樣做，可能會導致密碼的泄露，

當我們輸入密碼過后，會收到mysql server的提示

• 如果我們密碼輸入錯誤，我們會收到Access denied for user的提示，

• 如果密碼輸入正確，連接器會到權限表中查看你擁有的權限，并且之后該鏈接里面所有的權限判斷都依賴此時讀到的權限，

  所以我們如果修改一個賬號的權限后，需要重新登錄才會生效

  當一個客戶端在一段時間沒有操作后，此連接會自動斷開，可以通過wait_timeout引數來控制，

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

我們可以通過 show processlist命令來查看所有連接的用戶，

長連接與短連接

在資料庫中，所謂長連接就是建立連接后，如果客戶端有持續的請求，一直使用同一個連接，而短連接則是指每次執行完很少的請求后就會斷開連接從新建立請求，

而建立連接通常花費的時間是普通查詢陳述句的若干倍，所以最好不要在生產環境中頻繁的斷開和建立連接，

但是在mysql的設計中，臨時使用的記憶體是與連接物件關聯的，只有當連接釋放后才會將這些記憶體釋放，所以有可能一個長連接導致OOM，

在MySQL5.7及之后提供了mysql_reset_connection 來重新初始化連接資源，這個程序不需要重連和重新做權限檢測就可以將mysql連接恢復到初始的狀態，

查詢快取

當我們建立連接成功的時候，我們這個查詢陳述句就會在查詢快取中查看是否執行過這條陳述句，如果執行過這條陳述句并且這張表在上一條同樣的查詢之后沒有被更新過，就可以直接從查詢快取中拿到結果，如果后續有過更新或者第一次執行這條陳述句，查詢快取中都拿不到結果，則會執行下面的邏輯，所以如果我們的表有頻繁的更新查詢快取存在的意義并不大，在MySQL8.0后查詢快取的機制也就被廢棄了，

分析器

當我們輸入一串sql陳述句的時候，對mysql server來講，只不過是一串字串罷了，mysql需要用自己的分析器來分析sql陳述句中每個單詞所代表的含義，這個步驟就是詞法決議，

接下來，mysql還要做語法決議，判斷我們輸入的sql陳述句是否符合語法，

優化器

在陳述句真正開始執行之前，還需要經過優化器的處理，

優化器會決定索引的選擇和join的連接順序，比如有一個查詢有兩個索引，索引的選擇就是優化器所做的，再比如如下的陳述句

select * from t1 join t2 on t1.id=t2.id where t1.c=10 and t2.d=20;

這時候就涉及到join的順序了，

• 方案一：先從t1表中查出列c為10的值再用id關聯出t2表中的記錄查看t2表中的列d值是否為20，
• 方案二：先從t2表中查出列d為20的值再用id關聯出t1表中的記錄查看t1表中的列c值是否為10，

執行器

執行器所做的當然就是陳述句的執行，不過執行之前，還要驗證下此用戶有沒有對該表操作的權限，

如果命中快取，在查詢快取回傳結果的時候也要做權限驗證，

通過權限驗證后，執行器呼叫存盤引擎的介面接受存盤引擎回傳的資料，然后回傳給客戶端，

在執行器執行的時候，例如我們最上面那條陳述句，如果ID列沒有索引的話，執行流程如下，

1. 通過存盤引擎介面拿到該表的第一行，判斷ID的值，如果ID是10，就加入結果集，否則跳過該條記錄，
2. 回圈呼叫下一行，重復上述邏輯，直到這個表最后一行，
3. 將結果集回傳給客戶端，

如果該表ID欄位有索引，執行流程也差不多，只不過不是查找第一行和回圈查找下一行了，而是查找符合條件的第一行和符合條件的下一行，這些都是存盤引擎層面提供的介面，我們在慢查詢日志中的row_examined也是在執行器每次呼叫存盤引擎時進行累加的，

更新陳述句執行流程

介紹完上述組件后，接下來說一下更新陳述句的執行流程

首先我們準備這樣一個表

create table T(id int primary key,c int);

例如有如下這樣一條更新陳述句

update T set c = c+1 where id = 2;

首先還是一樣，在我們操作資料庫的時候，會需要連接到資料庫，這是連接器的作業，

接下來上面剛剛也說到，在更新一個表的資料時，會使查詢快取失效，所以在執行這條陳述句之前，如果開啟了查詢快取，會將查詢快取清空，

然后到了分析器和優化器與執行器，分析器會識別出這是一條更新的陳述句，優化器會決定使用ID這個主鍵索引，執行器呼叫InnoDB引擎實作的介面進行資料的更新，

但是不一樣的是，在這個更新的程序中，涉及到了兩個日志，大家一定都聽過，就是redo log（重做日志）和binlog（歸檔日志），

redo log（重做日志）

我們首先要達成一個共識，在計算機中，對磁盤的操作是最昂貴的，時間的消耗也是毫秒級別的，尤其是對磁盤的隨機讀寫；而對記憶體的操作相比于對磁盤來說，相差了好幾個數量級，

對與msyql來說，我們對資料的更改需要持久化到磁盤中，而對于每次的資料的修改操作，如果都需要寫進磁盤，然后磁盤也要找到那條記錄然后進行更新，這個程序中的IO成本，查找成本都比較高，而為了解決這個問題，mysql的設計者，應該說是InnoDB的設計者就提供了redo log，

其實我們要說的就是我們常聽到了WAL技術,WAL就是Write-Ahead Logging的縮寫，意思就是在寫磁盤之前，先寫日志，等不忙的時候再去寫磁盤，因為寫日志是順序IO，寫磁盤是隨機IO，使用順序IO來代替隨機IO大大提升了資料庫的并發效率，具體的說，當有一條記錄需要更新的時候（包括插入），InnoDB引擎首先會將記錄寫入到redo log中，然后并更新該資料頁在記憶體中的值，這時候更新就算完成了，等到系統相對來說空閑的時候會將這個操作記錄更新到磁盤中去，并將redo log中的記錄擦除，為新的資料騰出空間，

我們接下來看一下redo log的結構，在InnoDB中，redo log是固定大小的，我們可以配置其大小與個數，例如4個1GB的檔案，則mysql的redo log大小就為4GB，而redo log在寫入的時候是類似一個環形的結構，如圖所示：

write pos就是當前記錄的位置，在寫入日志的程序中這個點會逐漸后移，而check point是當前要擦除的位置，也是后移并且回圈的，當write pos追上check point時此時必須在執行新的更新了，必須停下來將一些記錄刷盤然后清除掉一些redo log，

同時，當我們引進了redo log之后，InnoDB獲得了crash-safe的能力，何為crash-safe呢？crash-safe就是當資料庫在任何時候發生例外重啟時，只要是之前提交成功的記錄，這個記錄一定不會丟失，因為當資料庫重啟后，可以通過redo log對比binlog發現未持久化的資料然后將此資料寫入到磁盤中，具體程序之后的文章會說明，

binlog（歸檔日志）

我們之前說過，mysql架構可分為server層和存盤引擎層兩個部分，而binlog就屬于server層，

和redo log相比，binlog會有如下的不同：

1. redo log 是InnoDB特有的，而binlog是MySQL在server層實作的，所有的存盤引擎都可以使用

2. redo log記錄的是物理日志，比如說在某個資料頁上進行了怎樣的資料修改，而binlog是邏輯日志，記錄的是原始陳述句的邏輯，

   binlog有三種模式

   1. statement
   2. row
   3. mix

3. redo log是回圈寫的，空間固定會用完，而binlog是追加寫的，不會覆寫以前的日志，

這時候你可能會問，為什么已經有了redo log而還需喲binlog呢？

是這樣的，binlog的產生優先于redo log，在mysql的默認存盤引擎時MyISAM的時候就已經有binlog了，但是就像大家都知道的一樣，MyISAM并不支持crash-safe，之后另一個公司以插件的形式提供了InnoDB引擎，因為binlog是沒有crash-safe能力的，所以就增加了redo log來提供了crash-safe能力，

更新程序

介紹完這兩個日志，終于可以說下上述更新陳述句的執行流程了，

1. 執行器在更新資料時，會呼叫存盤引擎找到ID=2的這一行，引擎通過樹形結構搜索到這一行的資料，如果ID=2的這一行資料本來就在記憶體中，就可以直接回傳給執行器，否則會先將這個資料頁從磁盤讀入到記憶體，然后回傳給執行器，
2. 執行器拿到行資料后，會將c值加上1，然后呼叫存盤引擎去修改該行資料，
3. InnoDB將這行資料更新到記憶體中的時候，同時將這次更新操作寫入到redo log中，這是后redo log中這次更新處于prepare狀態，然后告知執行器執行完成，
4. 執行器操作binlog，將binlog寫入磁盤，
5. 執行器呼叫事務的提交介面，InnoDB將剛剛寫入的redo log改為commit狀態，至此，更新操作完成，

二階段提交

你可能對上述的描述會有些疑問，為什么需要先將redo log置為prepare狀態然后再操作binlog最后再將redo log改為commit狀態呢？

這其實就是我們在分布式場景中常見的二階段提交，

在分布式場景中，我們為了達成一致性，所以有了這樣的一個原子性的提交協議，而二階段提交運用到這里，就是為了實作兩份日志的邏輯的一致性，

為什么要實作一致性呢？那我們說下兩份日志不一致會造成什么問題，大家都知道，mysql主從是通過發送binlog實作的，如果我們不使用二階段提交，假設該庫是主庫：

1. 當先寫redo log 后寫binlog：當寫入了redo log后資料庫crash了，此時重啟資料庫后會根據redo log來刷資料頁，而此時binlog里就會沒有這條記錄，這就會導致從庫看不到這次更新，導致主從不一致，
2. 當先寫binlog 后寫redo log：當寫入binlog后資料庫crash了，因為沒有寫redo log重啟后該庫看不到這條資料，而binlog中含有這個資料，導致這條資料會同步給從庫，所以最終也會導致主從不一致，

綜上，無論是先寫redo log還是先寫binlog都會有問題，這也就是二階段提交如此必要的原因，同時，不僅僅是在主從復制這種情況下會出現問題，當我們使用binlog恢復資料時同樣會產生問題，