天天看點
傳回

一條SQL更新語句是如何執行的

文章首發于公衆号「蟬沐風」，認真寫好每一篇文章，歡迎大家關注交流

這是圖解MySQL的第2篇文章，這篇文章會通過一條SQL更新語句的執行流程讓大家清楚地明白：

  • 什麼是InnoDB頁？緩存頁又是什麼？為什麼這麼設計？
  • 什麼是表空間？不同存儲引擎的表在檔案系統的底層表示上有什麼差別？
  • Buffer Pool是什麼？為什麼需要？有哪些我們需要掌握的細節？
  • MySQL的三種日志檔案redo日志、undo日志、binlog分别是什麼？為什麼需要這麼多種類型的日志？

正文開始！

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

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

那麼MySQL的更新流程和查詢流程有什麼差別呢？

其實基本的流程是一緻的，也要經過處理連接配接、解析優化、存儲引擎幾個步驟。主要差別在更新操作涉及到了MySQL更多的細節。

一條SQL更新語句是如何執行的
注：我們接下來的所有描述，針對的都是InnoDB存儲引擎，如果涉及到其他存儲引擎，将會特殊說明

1. 一些需要知道的概念

對于MySQL任何存儲引擎來說，資料都是存儲在磁盤中的，存儲引擎要操作資料，必須先把磁盤中的資料加載到記憶體中才可以。

那麼問題來了，一次性從磁盤中加載多少資料到記憶體中合适呢？當擷取記錄時，InnoDB存儲引擎需要一條條地把記錄從磁盤中讀取出來嗎？

當然不行！我們知道磁盤的讀寫速度和記憶體讀寫速度差了幾個數量級，如果我們需要讀取的資料恰好運作在磁盤的不同位置，那就意味着會産生多次I/O操作。

是以，無論是作業系統也好，MySQL存儲引擎也罷，都有一個預讀取的概念。概念的依據便是統治計算機界的局部性原理。

空間局部性：如果目前資料是正在被使用的，那麼與該資料空間位址臨近的其他資料在未來有更大的可能性被使用到，是以可以優先加載到寄存器或主存中提高效率

就是當磁盤上的一塊資料被讀取的時候，我們幹脆多讀一點，而不是用多少讀多少。

1.1 InnoDB頁

InnoDB存儲引擎将資料劃分為若幹個頁，以頁作為磁盤和記憶體之間互動的最小機關。InnoDB中頁的大小預設為16KB。也就是預設情況下，一次最少從磁盤中讀取16KB的資料到記憶體中，一次最少把記憶體中16KB的内容重新整理到磁盤上。

一條SQL更新語句是如何執行的

對于InnoDB存儲引擎而言，所有的資料（存儲使用者資料的索引、各種中繼資料、系統資料）都是以頁的形式進行存儲的。

1.2 表空間

為了更好地管理頁，MySQL又設計了「表空間」的概念。表空間又有很多類型，具體類型我們不需要知道，我們隻需要知道，一個表空間可以劃分成很多個InnoDB頁，InnoDB表資料都存儲在某個表空間的頁中。

為了友善我們定位，MySQL貼心的為表空間設計了一個唯一辨別——表空間ID（space ID）。同理，InnoDB頁也有自己的唯一編号——頁号（page number）。

是以，我們可以這麼認為。給定表空間ID和頁号以及頁的偏移量，我們就可以定位到InnoDB頁的某條記錄，也就是資料庫表的某條記錄。

1.2.1 資料表在檔案系統中的表示

為了更好地讓大家了解這個抽象的概念，我建立了名為

test

的資料庫，在其下分别建立了3張表

t_user_innodb

，

t_user_myisam

t_user_memory

，對應的存儲引擎分别為

InnoDB

、

MyISAM

MEMORY

。

進入MySQL的資料目錄，找到

test

目錄，看一下

test

資料庫下所有表對應的本地檔案目錄

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是如何存儲表資料的

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

我們看到

t_user_innodb

表在資料庫對應的

test

目錄下會生成以下兩個檔案

  • t_user_innodb.frm
  • t_user_innodb.ibd

其中，t_user_innodb.ibd就是

t_user_innodb

表對應的表空間在檔案系統上的表示；t_user_innodb.frm用來描述表的結構，如表有哪些列，列的類型是什麼等。

1.2.3 MyISAM是如何存儲表資料的

和InnoDB不同，MyISAM沒有表空間的概念，表的資料和索引全都直接存放在對應的資料庫子目錄下，可以看到

t_user_myisam

對應了三個檔案

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

其中，t_user_myisam.MYD表示表的資料檔案，也就是我們實際看到的資料表的内容；t_user_myisam.MYI表示表的索引檔案，為該表建立的索引都會存放在這個檔案中；t_user_myisam.frm用來描述表的結構。

1.2.4 MEMORY是如何存儲表資料的

MEMORY存儲引擎對應的資料表隻有一個描述表結構的檔案t_user_memory.frm。

2. 緩沖池Buffer Pool

為了更好的利用局部性原理帶給我們的優勢，InnoDB在處理用戶端請求時，如果需要通路某個頁的資料，會把該資料所在的頁的全部資料加載到記憶體中。哪怕是隻需要通路一個頁中的一條資料，也需要加載整個頁。

從磁盤中加載資料到記憶體中的操作太昂貴了！有什麼辦法可以提高資料操作的效率呢？緩存！

為了緩存磁盤的頁，InnoDB在MySQL伺服器啟動時會向作業系統申請一片連續的記憶體區域，這片記憶體區域就是Buffer Pool。

很容易了解，為了更好地緩存頁資料，Buffer Pool對應的一片連續記憶體空間也被劃分為若幹個頁，而且預設情況下，Buffer Pool頁的大小和InnoDB頁大小一樣，都是16KB。為了區分兩種不同的頁，我們将Buffer Pool中的頁面稱為緩沖頁。

一條SQL更新語句是如何執行的

讀取資料的時候，InnoDB先判斷資料是否在Buffer Pool中，如果是，則直接讀取資料進行操作，不用再次從磁盤加載；如果不是，則從磁盤加載到Buffer Pool中，然後讀取資料進行操作。

修改資料的時候，也是将資料先寫到Buffer Pool緩沖頁中，而不是每次更新操作都直接寫入磁盤。當緩沖頁中的資料和磁盤檔案不一緻的時候，緩沖頁被稱為髒頁。

那麼髒頁是什麼時候被同步到磁盤呢？

InnoDB中有專門的背景線程每隔一段時間會把髒頁的多個修改重新整理到磁盤上，這個動作叫做「刷髒」。

3. redo日志

3.1 為什麼需要redo日志

不定時刷髒又帶來一個問題。如果髒頁的資料還沒有重新整理到磁盤上，此時資料庫突然當機或重新開機，這些資料就會丢失。

首先想到的最簡單粗暴的解決方案就是在事務送出之前，把該事務修改的所有頁面都重新整理到磁盤。但是上文說過，頁是記憶體和磁盤互動的最小機關，如果隻修改了1個位元組，卻要重新整理16KB的資料到磁盤上，不得不說太浪費了，此路不通！

是以，必須要有一個持久化的措施。

為了解決這個問題，InnoDB把對所有頁的更新操作（再強調一遍，包含INSERT、UPDATE、DELETE）專門寫入一個日志檔案。

當有未同步到磁盤中的資料時，資料庫在啟動的時候，會根據這個日志檔案進行資料恢複。我們常說的關系型資料庫的

ACID

特性中的

D

（持久性），就是通過這個日志來實作的。

這個日志檔案就是大名鼎鼎的redo日志。

「re」在英文中的詞根含義是“重新”，redo就是「重新做」的意思，顧名思義就是MySQL根據這個日志檔案重新進行操作
一條SQL更新語句是如何執行的

這就出現了一個有意思的問題，重新整理磁盤和寫redo日志都是進行磁盤操作，為什麼不直接把資料重新整理到磁盤中呢？

3.2 磁道尋址

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

一條SQL更新語句是如何執行的

每個硬碟都有若幹個盤片，上圖的硬碟有4個盤片。

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

從圓心向外畫直線，可以将磁道劃分為若幹個弧段，每個磁道上一個弧段被稱之為一個「扇區」（右上圖白色部分）。資料是儲存在扇區當中的，扇區是硬碟讀寫的最小單元，如果要讀寫資料，必須找到對應的扇區，這個過程叫做「尋址」。

3.2.1 随機I/O

如果我們需要的資料是随機分散在磁盤上不同盤片的不同扇區中，那麼找到相應的資料需要等到磁臂旋轉到指定的盤片然後繼續尋找對應的扇區，才能找到我們所需要的一塊資料，持續進行此過程直到找完所有資料，這個就是随機I/O，讀取資料速度非常慢。

3.2.2 順序I/O

假設我們已經找到了第一塊資料，并且其他所需的資料就在這一塊資料之後，那麼就不需要重新尋址，可以依次拿到我們所需的資料，這個就叫順序 I/O。

現在回答之前的問題。因為刷髒是随機I/O，而記錄日志是順序I/O（連續寫的），順序I/O效率更高，本質上是資料集中存儲和分散存儲的差別。是以先把修改寫入日志檔案，在保證了記憶體資料的安全性的情況下，可以延遲刷盤時機，進而提升系統吞吐。

3.3 redo日志的系統變量

redo日志位于MySQL資料目錄下，預設有

ib_logfile0

和

ib_logfile1

兩個檔案，如下圖所示。

一條SQL更新語句是如何執行的

可以發現，兩個redo日志檔案的大小都是50331648，預設48MB。為什麼這個大小是固定的呢？因為如果我們要使用順序I/O，就必須在申請磁盤空間的時候一次性決定申請的空間大小，這樣才能保證申請的磁盤空間在位址上的連續性。

這也就決定了redo日志的舊資料會被覆寫，一旦檔案被寫滿，就會觸發Buffer Pool髒頁到磁盤的同步，以騰出額外空間記錄後面的修改。

可以通過以下指令檢視redo日志的系統屬性。

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     |
+-----------------------------+----------+
參數名稱 含義
innodb_log_file_size 指定每個redo日志檔案的大小，預設48MB
innodb_log_files_in_group 指定redo日志檔案的數量，預設2
innodb_log_group_home_dir 指定redo檔案的路徑，如果不指定，則預設為datadir目錄

介紹到這裡，讀者朋友可以發現，我們剛才探索的是如何讓已經送出的事務保持持久化，但是如果某些事務偏偏在執行到一半的時候出現問題怎麼辦？

事務的原子性要求事務中的所有操作要麼都成功，要麼都失敗，不允許存在中間狀态。就好比我在寫這篇文章的時候，會時不時地敲一下

ctrl+Z

傳回到上一步或者過去好幾步之前的狀态，MySQL也需要“留一手”，把事務復原時需要的東西都記錄下來。

比如，插入資料的時候，至少應該把新增的這條記錄的主鍵的值記錄下來，這樣復原的時候隻要把這個主鍵值對應的記錄删除就可以了。

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

4. undo日志

undo log（撤銷日志或復原日志）記錄了事務發生之前的資料狀态，分為insert undo log和update undo log。

如果修改資料時出現異常，可以用 undo log來實作復原操作（保持原子性）。可以了解為undo日志記錄的是反向的操作，比如INSERT操作會記錄DELETE，UPDATE會記錄UPDATE之前的值，和redo日志記錄在哪個實體頁面做了什麼操作不同，是以這是一種邏輯格式的日志。

undo日志和redo日志與事務密切相關，被統稱為「事務日志」。

一條SQL更新語句是如何執行的
關于undo日志，我們目前隻需要了解這麼多即可

5. SQL更新語句的執行總結——初版

有了事務日志之後，我們來簡單總結一下更新操作的流程，這是一個簡化的過程。

name 原值是

chanmufeng

 
update t_user_innodb set name ='chanmufeng1994' where id = 1;
  1. 事務開始，從記憶體（Buffer Pool）或磁盤取到包含這條資料的資料頁，傳回給 Server 的執行器；
  2. Server 的執行器修改資料頁的這一行資料的值為 chanmufeng1994；
  3. 記錄 name=chanmufeng 到undo log；
  4. 記錄 name=chanmufeng1994到redo log；
  5. 調用存儲引擎接口，記錄資料頁到Buffer Pool（修改 name=penyuyan）；
  6. 事務送出。

6. binlog日志

之前我們講過，從MySQL整體架構來看，其實可以分成兩部分

  • Server 層，它主要做的是 MySQL功能層面的事情，比如處理連接配接、解析優化等；
  • 存儲引擎層，負責存儲相關的具體事宜。

redo日志是InnoDB存儲引擎特有的日志，而Server層也有自己的日志，稱為 binlog（歸檔日志），它可以被所有存儲引擎使用。

6.1 為什麼有了redo日志還需要 binlog？

我想你可能會問出這個問題，實際上，更準确的問法是為什麼有了binlog還需要有redo日志？主要有以下幾個原因。

  1. 因為最開始MySQL裡并沒有InnoDB存儲引擎。MySQL自帶的引擎是MyISAM，但是 MyISAM沒有崩潰恢複的能力，InnoDB後來以插件的形式被引入，順便帶來了redo日志；

  2. binlog日志是用來歸檔的，binlog以事件的形式記錄了所有的 DDL和 DML 語句（因為它記錄的是操作而不是

    資料值，屬于邏輯日志），但是不具備當機恢複的功能，因為可能沒有來得及重新整理髒頁，造成髒頁資料的丢失，而這些操作也沒有儲存到binlog中進而造成資料丢失；

  3. binlog記錄的是關于一個事務的具體操作内容，即該日志是邏輯日志。而redo日志記錄的是關于每個頁的更改的實體情況。功能壓根不是一回事兒。

6.2 binlog日志的作用

6.2.1 主從複制

binlog是實作MySQL主從複制功能的核心元件。

master節點會将所有的寫操作記錄到binlog中，slave節點會有專門的I/O線程讀取master節點的binlog，将寫操作同步到目前所在的slave節點。

一條SQL更新語句是如何執行的

6.2.2 資料恢複

假如你在閱讀這篇文章的時候覺得我寫得實在太好，拍案叫絕的時候一不小心把公司的資料庫給删了，你該怎麼做才能恢複到你删庫之前的那個時刻的狀态？

這個時候就要用到binlog了，前提是binlog沒有被删除，否則，神仙也救不了你了。

通常情況下，公司會定期對資料庫進行全量備份，可能隔一個月，一周，甚至可能每天都備份一次。運氣好的話你可以使用前一天的全量備份，恢複到前一天的某時刻狀态（或者一周、一月之前），然後從全量備份的時刻開始，從binlog中提取該時刻之後（前提是你的binlog裡面存放了這段時間的日志）的所有寫操作（當然，你得過濾掉你的删庫操作），然後進行操作回放就可以了。

是不是很簡單？

問題又來了。再看一眼我們的更新語句。

update t_user_innodb set name ='chanmufeng1994' where id = 1;

假如這條更新語句已經被寫入到了redo日志，還沒來得及寫binlog的時候，MySQL當機重新開機了，我們看一下會發生什麼。

因為redo日志可以在重新開機的時候用于恢複資料，是以寫入磁盤的是chanmufeng1994。但是binlog裡面沒有記錄這個邏輯日志，是以這時候用binlog去恢複資料或者同步到從庫，就會出現資料不一緻的情況。

是以在寫兩個日志的情況下，就類似于「分布式事務」的情況，如果你不清楚分布式事務是個什麼東西也沒關系，我在之後的文章會介紹到。能夠明确的就是redo日志和binlog日志如果單純依次進行送出是無法保證兩種日志都寫成功或者都寫失敗的。

我們需要「兩階段送出」。

6.3 兩階段送出

兩階段送出不是MySQL的專利，兩階段送出是一種跨系統維持資料邏輯一緻性的常見方案，尤其在分布式事務上，是以請讀者重點體會思想

我們把redo日志的送出分成兩步，兩步中redo日志的狀态分别是

prepare

commit

。步驟如下

  1. InnoDB存儲引擎将更改更新到記憶體中後，同時将這個更新操作記錄到redo日志裡面，此時redo日志處于

    prepare

     狀态；
  2. 執行器生成這個操作的binlog，并将binlog刷盤；
  3. 執行器調用InnoDB的送出事務接口，InnoDB把剛剛寫入的redo日志改成

    commit

     狀态。至此，所有操作完成。
一條SQL更新語句是如何執行的

加上兩階段送出之後我們再來看一下SQL更新語句的執行流程。

7. SQL更新語句的執行總結——終版

一條SQL更新語句是如何執行的
  1. 用戶端發送更新指令到MySQL伺服器，經過處理連接配接、解析優化等步驟；
  2. Server層向InnoDB存儲引擎要id=1的這條記錄；
  3. 存儲引擎先從緩存中查找這條記錄，有的話直接傳回，沒有則從磁盤加載到緩存中然後傳回；
  4. Server層執行器修改這條記錄的name字段值；
  5. 存儲引擎更新修改到記憶體中；
  6. 存儲引擎記錄redo日志，并将狀态設定為

    prepare
  7. 存儲引擎通知執行器，修改完畢，可以進行事務送出；
  8. Server先寫了個binlog；
  9. Server送出事務；
  10. 存儲引擎将redo日志中和目前事務相關的記錄狀态設定為

    commit

     狀态。

完！

推薦閱讀

  • 一條SQL查詢語句是如何執行的

參考資料

  1. MySQL實戰45講
  2. MySQL是怎樣運作的
mysql
上一篇: IPV6改造 華為雲如此簡單
下一篇: 最新Sql語句來啦

繼續閱讀