簡述SQL更新語句的執(zhí)行流程1

這篇文章會(huì)通過一條SQL更新語句的執(zhí)行流程讓大家清楚地明白：

• 什么是InnoDB頁？緩存頁又是什么？為什么這么設(shè)計(jì)？
• 什么是表空間？不同存儲引擎的表在文件系統(tǒng)的底層表示上有什么區(qū)別？
• Buffer Pool是什么？為什么需要？有哪些我們需要掌握的細(xì)節(jié)？
• MySQL的三種日志文件redo日志、undo日志、binlog分別是什么？為什么需要這么多種類型的日志？

正文開始！

之前我們講過了一條SQL查詢語句是如何執(zhí)行的，那么插入（INSERT）、更新（UPDATE）和刪除（DELETE）操作的流程又是什么樣子呢？

其實(shí)對于MySQL而言，只有兩種通常意義的操作，一種是Query（查詢），另一種是Update（更新），后者包含了我們平常使用的INSERT、UPDATE和DELETE操作。

那么MySQL的更新流程和查詢流程有什么區(qū)別呢？

其實(shí)基本的流程是一致的，也要經(jīng)過 處理連接 、 解析優(yōu)化 、存儲引擎幾個(gè)步驟。主要區(qū)別在更新操作涉及到了MySQL更多的細(xì)節(jié)。

注：我們接下來的所有描述，針對的都是InnoDB存儲引擎，如果涉及到其他存儲引擎，將會(huì)特殊說明

1. 一些需要知道的概念

對于MySQL任何存儲引擎來說，數(shù)據(jù)都是存儲在磁盤中的，存儲引擎要操作數(shù)據(jù)，必須先把磁盤中的數(shù)據(jù)加載到內(nèi)存中才可以。

那么問題來了，一次性從磁盤中加載多少數(shù)據(jù)到內(nèi)存中合適呢？當(dāng)獲取記錄時(shí)，InnoDB存儲引擎需要一條條地把記錄從磁盤中讀取出來嗎？

當(dāng)然不行！我們知道磁盤的讀寫速度和內(nèi)存讀寫速度差了幾個(gè)數(shù)量級，如果我們需要讀取的數(shù)據(jù)恰好運(yùn)行在磁盤的不同位置，那就意味著會(huì)產(chǎn)生多次I/O操作。

因此，無論是操作系統(tǒng)也好，MySQL存儲引擎也罷，都有一個(gè)預(yù)讀取的概念。概念的依據(jù)便是統(tǒng)治計(jì)算機(jī)界的局部性原理。

空間局部性：如果當(dāng)前數(shù)據(jù)是正在被使用的，那么與該數(shù)據(jù)空間地址臨近的其他數(shù)據(jù)在未來有更大的可能性被使用到，因此可以優(yōu)先加載到寄存器或主存中提高效率

就是當(dāng)磁盤上的一塊數(shù)據(jù)被讀取的時(shí)候，我們干脆多讀一點(diǎn)，而不是用多少讀多少。

1.1 InnoDB頁

InnoDB存儲引擎將數(shù)據(jù)劃分為若干個(gè)頁，以頁作為磁盤和內(nèi)存之間交互的最小單位。InnoDB中頁的大小默認(rèn)為16KB。也就是默認(rèn)情況下，一次最少從磁盤中讀取16KB的數(shù)據(jù)到內(nèi)存中，一次最少把內(nèi)存中16KB的內(nèi)容刷新到磁盤上。

對于InnoDB存儲引擎而言，所有的數(shù)據(jù)（存儲用戶數(shù)據(jù)的索引、各種元數(shù)據(jù)、系統(tǒng)數(shù)據(jù)）都是以頁的形式進(jìn)行存儲的。

1.2 表空間

為了更好地管理頁，MySQL又設(shè)計(jì)了「表空間」的概念。表空間又有很多類型，具體類型我們不需要知道，我們只需要知道，一個(gè)表空間可以劃分成很多個(gè)InnoDB頁，InnoDB表數(shù)據(jù)都存儲在某個(gè)表空間的頁中。

為了方便我們定位，MySQL貼心地為表空間設(shè)計(jì)了一個(gè)唯一標(biāo)識——表空間ID（space ID）。同理，InnoDB頁也有自己的唯一編號——頁號（page number）。

因此，我們可以這么認(rèn)為。給定表空間ID和頁號以及頁的偏移量，我們就可以定位到InnoDB頁的某條記錄，也就是數(shù)據(jù)庫表的某條記錄。

1.2.1 數(shù)據(jù)表在文件系統(tǒng)中的表示

為了更好地讓大家理解這個(gè)抽象的概念，我創(chuàng)建了名為test的數(shù)據(jù)庫，在其下分別創(chuàng)建了3張表t_user_innodb，t_user_myisam，t_user_memory，對應(yīng)的存儲引擎分別為InnoDB、MyISAM、MEMORY。

進(jìn)入MySQL的數(shù)據(jù)目錄，找到test目錄，看一下test數(shù)據(jù)庫下所有表對應(yīng)的本地文件目錄

drwxr-x--- 2 mysql mysql  4096 Jan 26 09:28 .
drwxrwxrwt 6 mysql mysql  4096 Jan 26 09:24 ..
-rw-r----- 1 mysql mysql    67 Jan 26 09:24 db.opt
-rw-r----- 1 mysql mysql  8556 Jan 26 09:28 t_user_innodb.frm
-rw-r----- 1 mysql mysql 98304 Jan 26 09:28 t_user_innodb.ibd
-rw-r----- 1 mysql mysql  8556 Jan 26 09:27 t_user_memory.frm
-rw-r----- 1 mysql mysql     0 Jan 26 09:28 t_user_myisam.MYD
-rw-r----- 1 mysql mysql  1024 Jan 26 09:28 t_user_myisam.MYI
-rw-r----- 1 mysql mysql  8556 Jan 26 09:28 t_user_myisam.frm

1.2.2 InnoDB是如何存儲表數(shù)據(jù)的

「表空間」是InnoDB存儲引擎獨(dú)有的概念。

我們看到t_user_innodb表在數(shù)據(jù)庫對應(yīng)的test目錄下會(huì)生成以下兩個(gè)文件

• t_user_innodb.frm
• t_user_innodb.ibd

其中，t_user_innodb.ibd就是t_user_innodb表對應(yīng)的表空間在文件系統(tǒng)上的表示；t_user_innodb.frm用來描述表的結(jié)構(gòu)，如表有哪些列，列的類型是什么等。

1.2.3 MyISAM是如何存儲表數(shù)據(jù)的

和InnoDB不同，MyISAM沒有表空間的概念，表的數(shù)據(jù)和索引全都直接存放在對應(yīng)的數(shù)據(jù)庫子目錄下，可以看到t_user_myisam對應(yīng)了三個(gè)文件

• t_user_myisam.MYD
• t_user_myisam.MYI
• t_user_myisam.frm

其中，t_user_myisam.MYD表示表的數(shù)據(jù)文件，也就是我們實(shí)際看到的數(shù)據(jù)表的內(nèi)容；t_user_myisam.MYI表示表的索引文件，為該表創(chuàng)建的索引都會(huì)存放在這個(gè)文件中；t_user_myisam.frm用來描述表的結(jié)構(gòu)。

1.2.4 MEMORY是如何存儲表數(shù)據(jù)的

MEMORY存儲引擎對應(yīng)的數(shù)據(jù)表只有一個(gè)描述表結(jié)構(gòu)的文件t_user_memory.frm。

2. 緩沖池Buffer Pool

為了更好的利用局部性原理帶給我們的優(yōu)勢，InnoDB在處理客戶端請求時(shí)，如果需要訪問某個(gè)頁的數(shù)據(jù)，會(huì)把該數(shù)據(jù)所在的頁的全部數(shù)據(jù)加載到內(nèi)存中。哪怕是只需要訪問一個(gè)頁中的一條數(shù)據(jù)，也需要加載整個(gè)頁。

從磁盤中加載數(shù)據(jù)到內(nèi)存中的操作太昂貴了！有什么辦法可以提高數(shù)據(jù)操作的效率呢？緩存！

為了緩存磁盤的頁，InnoDB在MySQL服務(wù)器啟動(dòng)時(shí)會(huì)向操作系統(tǒng)申請一片連續(xù)的內(nèi)存區(qū)域，這片內(nèi)存區(qū)域就是 Buffer Pool 。

很容易理解，為了更好地緩存頁數(shù)據(jù)，Buffer Pool對應(yīng)的一片連續(xù)內(nèi)存空間也被劃分為若干個(gè)頁，而且默認(rèn)情況下，Buffer Pool頁的大小和InnoDB頁大小一樣，都是16KB。為了區(qū)分兩種不同的頁，我們將Buffer Pool中的頁面稱為緩沖頁。

讀取數(shù)據(jù)的時(shí)候，InnoDB先判斷數(shù)據(jù)是否在Buffer Pool中，如果是，則直接讀取數(shù)據(jù)進(jìn)行操作，不用再次從磁盤加載；如果不是，則從磁盤加載到Buffer Pool中，然后讀取數(shù)據(jù)進(jìn)行操作。

修改數(shù)據(jù)的時(shí)候，也是將數(shù)據(jù)先寫到Buffer Pool緩沖頁中，而不是每次更新操作都直接寫入磁盤。當(dāng)緩沖頁中的數(shù)據(jù)和磁盤文件不一致的時(shí)候，緩沖頁被稱為臟頁。

那么臟頁是什么時(shí)候被同步到磁盤呢？

InnoDB中有專門的后臺線程每隔一段時(shí)間會(huì)把臟頁的多個(gè)修改刷新到磁盤上，這個(gè)動(dòng)作叫做「刷臟」。

3. redo日志

3.1 為什么需要redo日志

不定時(shí)刷臟又帶來一個(gè)問題。如果臟頁的數(shù)據(jù)還沒有刷新到磁盤上，此時(shí)數(shù)據(jù)庫突然宕機(jī)或重啟，這些數(shù)據(jù)就會(huì)丟失。

首先想到的最簡單粗暴的解決方案就是在事務(wù)提交之前，把該事務(wù)修改的所有頁面都刷新到磁盤。但是上文說過，頁是內(nèi)存和磁盤交互的最小單位，如果只修改了1個(gè)字節(jié)，卻要刷新16KB的數(shù)據(jù)到磁盤上，不得不說太浪費(fèi)了，此路不通！

所以，必須要有一個(gè)持久化的措施。

為了解決這個(gè)問題，InnoDB把對所有頁的更新操作（再強(qiáng)調(diào)一遍，包含INSERT、UPDATE、DELETE）專門寫入一個(gè)日志文件。

當(dāng)有未同步到磁盤中的數(shù)據(jù)時(shí)，數(shù)據(jù)庫在啟動(dòng)的時(shí)候，會(huì)根據(jù)這個(gè)日志文件進(jìn)行數(shù)據(jù)恢復(fù)。我們常說的關(guān)系型數(shù)據(jù)庫的ACID特性中的D（持久性），就是通過這個(gè)日志來實(shí)現(xiàn)的。

這個(gè)日志文件就是大名鼎鼎的 redo日志 。

「re」在英文中的詞根含義是“重新”，redo就是「重新做」的意思，顧名思義就是MySQL根據(jù)這個(gè)日志文件重新進(jìn)行操作

這就出現(xiàn)了一個(gè)有意思的問題，刷新磁盤和寫redo日志都是進(jìn)行磁盤操作，為什么不直接把數(shù)據(jù)刷新到磁盤中呢？

3.2 磁道尋址

我們需要稍微了解一下磁道尋址的過程。磁盤的構(gòu)造如下圖所示。

每個(gè)硬盤都有若干個(gè)盤片，上圖的硬盤有4個(gè)盤片。

每個(gè)盤片的盤面上有一圈圈的同心圓，叫做「磁道」。

從圓心向外畫直線，可以將磁道劃分為若干個(gè)弧段，每個(gè)磁道上一個(gè)弧段被稱之為一個(gè)「扇區(qū)」（右上圖白色部分）。數(shù)據(jù)是保存在扇區(qū)當(dāng)中的，扇區(qū)是硬盤讀寫的最小單元，如果要讀寫數(shù)據(jù)，必須找到對應(yīng)的扇區(qū)，這個(gè)過程叫做「尋址」。

3.2.1 隨機(jī)I/O

如果我們需要的數(shù)據(jù)是隨機(jī)分散在磁盤上不同盤片的不同扇區(qū)中，那么找到相應(yīng)的數(shù)據(jù)需要等到磁臂旋轉(zhuǎn)到指定的盤片然后繼續(xù)尋找對應(yīng)的扇區(qū)，才能找到我們所需要的一塊數(shù)據(jù)，持續(xù)進(jìn)行此過程直到找完所有數(shù)據(jù)，這個(gè)就是隨機(jī)I/O，讀取數(shù)據(jù)速度非常慢。

3.2.2 順序I/O

假設(shè)我們已經(jīng)找到了第一塊數(shù)據(jù)，并且其他所需的數(shù)據(jù)就在這一塊數(shù)據(jù)之后，那么就不需要重新尋址，可以依次拿到我們所需的數(shù)據(jù)，這個(gè)就叫順序 I/O。

現(xiàn)在回答之前的問題。因?yàn)樗⑴K是隨機(jī)I/O，而記錄日志是順序I/O（連續(xù)寫的），順序I/O效率更高，本質(zhì)上是數(shù)據(jù)集中存儲和分散存儲的區(qū)別。因此先把修改寫入日志文件，在保證了內(nèi)存數(shù)據(jù)的安全性的情況下，可以延遲刷盤時(shí)機(jī)，進(jìn)而提升系統(tǒng)吞吐。

3.3 redo日志的系統(tǒng)變量

redo日志位于MySQL數(shù)據(jù)目錄下，默認(rèn)有ib_logfile0和ib_logfile1兩個(gè)文件，如下圖所示。

可以發(fā)現(xiàn)，兩個(gè)redo日志文件的大小都是50331648，默認(rèn)48MB。為什么這個(gè)大小是固定的呢？因?yàn)槿绻覀円褂庙樞騃/O，就必須在申請磁盤空間的時(shí)候一次性決定申請的空間大小，這樣才能保證申請的磁盤空間在地址上的連續(xù)性。

這也就決定了redo日志的舊數(shù)據(jù)會(huì)被覆蓋，一旦文件被寫滿，就會(huì)觸發(fā)Buffer Pool臟頁到磁盤的同步，以騰出額外空間記錄后面的修改。

可以通過以下指令查看redo日志的系統(tǒng)屬性。

mysql> show variables like 'innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | ./       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+

介紹到這里，讀者朋友可以發(fā)現(xiàn)，我們剛才探索的是如何讓已經(jīng)提交的事務(wù)保持持久化，但是如果某些事務(wù)偏偏在執(zhí)行到一半的時(shí)候出現(xiàn)問題怎么辦？

事務(wù)的原子性要求事務(wù)中的所有操作要么都成功，要么都失敗，不允許存在中間狀態(tài)。就好比我在寫這篇文章的時(shí)候，會(huì)時(shí)不時(shí)地敲一下ctrl+Z返回到上一步或者過去好幾步之前的狀態(tài)，MySQL也需要“留一手”，把事務(wù)回滾時(shí)需要的東西都記錄下來。

比如，插入數(shù)據(jù)的時(shí)候，至少應(yīng)該把新增的這條記錄的主鍵的值記錄下來，這樣回滾的時(shí)候只要把這個(gè)主鍵值對應(yīng)的記錄刪除就可以了。

MySQL又一個(gè)鼎鼎大名的日志—— undo日志 ，正式登場！