寫在前面的話

你有沒有想過這樣一個問題：我們的數據在MySQL中是如何存放的？它是以什么樣的組織方式存放在我們磁盤中的？

我們知道，數據是存放在表里面的，在表里面是一行一行存在的。那么這一行一行的數據怎么樣在磁盤中存放的呢？表又是如何在磁盤上存放的？讀完下面的文章，你就會對這個問題整體的認識。

InnoDB的存儲結構

數據是放在表空間tablesapce中的，而表空間是段segment組成的，段又是由區extent組成的，區又是由頁page組成的。page里面放的就是一行一行的數據。這樣就組成了MySQL中innodb的存儲結構。如下圖所示：

Tablespace

tablespace就是我們平時所說的表空間。它是一個物理概念，對應到磁盤上，就是一個個數據文件。例如在我的MySQL的安裝目錄下面有一個名稱為feng的數據庫，該數據庫下的表空間如下所示：

root@test:/var/lib/mysql/feng# pwd
/var/lib/mysql/feng	--------------------->這是我的數據庫目錄，數據名稱為：feng
root@test:/var/lib/mysql/feng# ls -lstr
total 1464
  4 -rw-r----- 1 mysql mysql     67 Dec  6 14:24 db.opt
 12 -rw-r----- 1 mysql mysql   8674 Dec 24 10:51 t_innodb.frm # 表結構定義文件
 96 -rw-r----- 1 mysql mysql  98304 Dec 24 10:54 t_innodb.ibd # 表空間文件，里面存放數據和索引
 12 -rw-r----- 1 mysql mysql   8674 Dec 24 10:51 t_myisam.frm # 表結構定義文件
  4 -rw-r----- 1 mysql mysql   2048 Dec 24 10:54 t_myisam.MYI # 表索引文件
  4 -rw-r----- 1 mysql mysql    168 Dec 24 10:54 t_myisam.MYD # 表空間文件，里面存數據
root@test:/var/lib/mysql/feng#

從上面我們可以看出innodb存儲引擎的表空間和myisam存儲引擎的表空間，有一點不一樣：innodb存儲引擎的表空間對應的數據文件和索引是放在一個文件中的，而myisam存儲引擎的表對應的數據文件和索引文件是兩個分開的數據文件，這也是innodb表又稱為IOT，索引組織表的一個原因，它的數據和索引是存放在一個數據文件中的。

這里對應的一個個數據文件.ibd和.MYD結尾的文件就是一個個表空間。我們可以看出這里面是一個表對應一個表空間。不同的表他們的表空間是分開的。并不像Oracle那樣多個表共享一個表空間數據文件。其實在MySQL中也有和Oracle類似的存儲方式，多個表共享一個表空間文件。這個是通參數innodb_file_per_table來控制的。

如下是查看MySQL中當前表空間文件是否獨立的方式，這個參數是從MySQL5.6之后的版本才支持的，在5.6之前的版本中，是不支持獨立表空間設置的，和Oracle一樣多個表共享一個表空間數據文件。

mysql> show variables like 'innodb_file_per_table'; /* 當該參數為ON時，表示每一個表單獨一個表空間文件；如果為OFF，表示多個表共享一個表空間文件。 */
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.07 sec)

mysql>

常見的表空間

我們經常遇到的表空間可以參考MySQLInnodb存儲引擎的存儲架構圖：

innodb-architecture.png

從圖中我們可以看到我們經常遇到的表空間有如下幾類：

System Tablespace：系統表空間，對應到磁盤上面的數據文件就是/var/lib/mysql/ibdata1，如下：

root@test:/var/lib/mysql# ls -lstr ibdata*
77824 -rw-r----- 1 mysql mysql 79691776 Dec 28 14:32 ibdata1
root@test:/var/lib/mysql#

Undo Tablespace：回滾表空間，默認這個空間是和系統表空間共用一個表空間的，它不會單獨存在，和ibdata1系統表空間文件存在一起。但是在MySQL5.6版本以后，支持單獨配置回滾表空間了。可以為其單獨配置，使用參數innodb_undo_tablespaces來配置使用幾個回滾表空間。如果安裝MySQL的時候沒有配置回滾表空間，那么查詢的結果如下：

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.02 sec)
從提升MySQL性能的角度上來看，為了減少磁盤I/O的競爭，所以建議把回滾表空間和系統表空間分開存放，不讓回滾表空間和系統表空間共用同一個數據表空間文件：ibdata1，可以使用參數innodb_undo_tablespaces參數配置回滾表空間的數據文件的數目。配置參數在/etc/mysql/my.cnf配置文件中如下：[mysqld] # 回滾表空間的配置 innodb_max_undo_log_size = 100M innodb_undo_log_truncate = ON innodb_undo_logs = 128 innodb_undo_tablespaces = 4 配置后的結果在MySQL的命令行中查看如下：mysql> show variables like '%undo%'; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_max_undo_log_size | 104857600 | | innodb_undo_directory | ./ | | innodb_undo_log_truncate | ON | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 4 | +--------------------------+-----------+ 5 rows in set (0.01 sec) 配置后可以查看到對應的回滾表空間的數據文件已經存在/var/lib/mysql/undo*，如下所示：root@test:/var/lib/mysql# ls -lstr undo* 10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo002 10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo001 10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo004 10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo003 root@test:/var/lib/mysql# 更多關于回滾表空間的問題參考：https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html

- 從提升MySQL性能的角度上來看，為了減少磁盤I/O的競爭，所以建議把回滾表空間和系統表空間分開存放，不讓回滾表空間和系統表空間共用同一個數據表空間文件：ibdata1，可以使用參數innodb_undo_tablespaces參數配置回滾表空間的數據文件的數目。配置參數在/etc/mysql/my.cnf配置文件中如下：

[mysqld]
# 回滾表空間的配置
innodb_max_undo_log_size = 100M
innodb_undo_log_truncate = ON
innodb_undo_logs = 128
innodb_undo_tablespaces = 4

- 配置后的結果在MySQL的命令行中查看如下：

mysql> show variables like '%undo%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_max_undo_log_size | 104857600 |
| innodb_undo_directory    | ./        |
| innodb_undo_log_truncate | ON        |
| innodb_undo_logs         | 128       |
| innodb_undo_tablespaces  | 4         |
+--------------------------+-----------+
5 rows in set (0.01 sec)

- 配置后可以查看到對應的回滾表空間的數據文件已經存在/var/lib/mysql/undo*，如下所示：

root@test:/var/lib/mysql# ls -lstr undo*
10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo002
10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo001
10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo004
10240 -rw-r----- 1 mysql mysql 10485760 Dec 28 15:45 undo003
root@test:/var/lib/mysql#

- 更多關于回滾表空間的問題參考：https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html

Redo Log Tablespace：日志表空間，對應到磁盤上面的數據文件就是/var/lib/mysql/ib_logfile*，如下：

root@test:/var/lib/mysql# ls -lstr ib_logfile*
49152 -rw-r----- 1 mysql mysql 50331648 Dec  6 14:15 ib_logfile1
49152 -rw-r----- 1 mysql mysql 50331648 Dec 28 14:32 ib_logfile0
root@test:/var/lib/mysql#

Temporary Tablespace：臨時表空間，對應到磁盤上面的數據文件就是/var/lib/mysql/ibtmp1，如下：

root@test:/var/lib/mysql# ls -lstr ibtmp*
12288 -rw-r----- 1 mysql mysql 12582912 Dec 28 14:32 ibtmp1
root@test:/var/lib/mysql#

General Tablespace：一般表空間，就是平時我們用于存儲自己業務表中的數據用的表空間文件。這里需要注意的是目前很少使用這種以便的表空間了，因為它是多張表共用一個數據表空間文件，如果數據量比較大的情況下經導致這個表空間數據文件會很大，導致備份、遷移、恢復等動作都很困難。尤其是當其中某一個表的數據損壞而引起所有的表數據都不可訪問的情況。所以，推薦使用下面的獨立表空間文件。
File-Pre-Table Tablespace：它和上的General Tablespace的功能一樣，就是用來存儲我們的業務數據的表空間。但是它和上面的General Tablespace有一點不同，顧名思義，它是每一個表對應一個數據表空間文件，這樣可以提高數據文件并發時的磁盤I/O，同時可以避免因為數據表被損壞導致的所有數據表都不可用的情況。在恢復的時候，備份的時候，都很方便。該功能開啟的參數為：innodb_file_per_table=on。這也是目前MySQL5.7版本中默認的參數值。

Segment

段（Segment）由一個或多個區組成，區在文件系統是一個連續分配的空間（在 InnoDB 中是連續的 64 個頁），不過在段中不要求區與區之間是相鄰的。段是數據庫中的分配單位，不同類型的數據庫對象以不同的段形式存在。

Table表和Segment段之間的關系如下：

表是邏輯概念，段是物理存儲概念。
一張普通的表，對應一個段。
一張表也可以有多個段，比如分區表，一個分區一個段。
多張表也可以共享一個段，比如簇表，多個簇表共享一個段。
通常情況下，創建一個表會創建一個段，但是：表的創建，并不意味著一定會創建一個段，比如臨時表的創建就不會創建段。
建立其他的數據庫對象也會創建段，比如：視圖、索引對應著視圖段、索引段。

Extent

在 InnoDB 存儲引擎中，一個區塊分配 64 個連續的頁。因為 InnoDB 中的頁大小默認是 16KB，所以一個區的大小是 64*16KB=1MB。在任何情況下每個區大小都為1MB，為了保證頁的連續性，InnoDB存儲引擎每次從磁盤一次申請4-5個區。默認情況下，InnoDB存儲引擎的頁大小為16KB，即一個區中有64個連續的頁。

Page

Page頁是InnoDB存儲引擎磁盤管理的最小單位，每個頁默認16KB：16384Byte = 16KB，可以使用如下命令在MySQL中進行查看。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.02 sec)

在MySQL5.6之前的版本，這個參數是不支持動態修改的，如果想要修改，只能自己修改源碼編輯才可以。

而在5.6版本之后，參數innodb_page_size已經支持動態的配置，支持4KB、8KB、16KB(默認值)、32KB、64KB。但是這個配置也僅僅是在數據庫安裝好之后初始化之前自行配置，當有數據已經存在之后，這個參數是不能修改的。除非把數據通過mysqldump導出來，重新初始化一個新的數據庫環境，然后修改參數之后，把導出來的數據再次再導入進去。

page頁再細粒度的劃分，可以分為如下幾種結構：

mysql innodb page structure.jpg

下面分別介紹一下page頁中各個組成部分的含義。

File Header：文件頭信息，比較重要的信息有FIL_PAGE_PREV記錄上一個page頁和FIL_PAGE_NEXT下一個page頁的位置信息，通過這兩個信息，可以讓所有的page頁面組成一個雙向鏈表：

page雙向鏈表.png

關于文件頭File Header更為詳細的內容參考如下圖：

Page Header：記錄本頁存儲記錄的狀態信息，比如本頁記錄數量，槽數量，詳細的信息參考下圖：

Infimun + Supermum Records：最小行與最大行記錄，是虛擬記錄，標記該page頁中，存儲的id最大的行和id最小的行記錄。具體可以參考如下圖的結構：

page infimum and supremum.jpg

User Records：用戶真正的數據存儲區域，這里真正存放用戶的行數據，它占據了整個page頁的大部分空間。以單鏈表的形式存儲一條條行記錄。如下圖所示，他們在物理上不一定是有序的，可能剛開始是有序的，但是隨著增刪改的操作可能就無序了，但是在邏輯上是有序的：

page內數據行存儲的方式.png

- 一個page頁中的多行記錄，再結合多個page頁，就形成如下的存儲結構：頁與頁直接是雙向鏈表，頁內的行記錄直接是單向鏈表。如下所示：page頁中的每一個箭頭可以理解為一行數據。

page頁和頁之間的關系.png

- 基于上面的圖，當我們要查詢某一行記錄的時候，是通過下面的過程來查找的。
  - 通過根節點開始遍歷一個索引的B+樹，通過各層非葉子節點達到底層的葉子節點的數據頁（Page），這個Page內部存放的都是葉子節點
  - 在Page內部從“Infimum”節點開始遍歷單鏈表（遍歷一般會被優化），如果找到鍵則返回。
  - 如果遍歷到了“Supremum”，說明當前Page里沒有合適的鍵，這時借助Page頁內部的next page指針，跳轉到下一個page繼續從“Infmum”開始逐個查找。
Free Space：存數據空間中尚未使用的區域，該頁中剩余的空間，用于存放后續插入的數據。
Page Directory：頁目錄，頁中某些記錄的相對位置，用于提升查詢效率。我們要在一個頁中查找指定的一條記錄。除了從頭遍歷還有更高效率的方法么？Page Directory提供了解決方案。
- InnoDB會將一個頁中的所有記錄劃分成若干個組，每組4-8個記錄。將每個組最后一個記錄相對于第一個記錄的地址偏移量（可以定位到真實數據記錄）提取出來存放在頁中一個叫做Page Directory的數組中，數組中的元素就是這些地址偏移量，也稱為槽(slot)。所以Page Directory就是由槽組成的。
- 所以在一個頁中根據主鍵查找記錄是很快的，步驟為：二分法確定該記錄所在的槽，并找到該槽所在分組中主鍵值最小的那條記錄。通過next_record屬性遍歷單鏈表找到記錄
- 注意：二分法，適用于數組。鏈表是順序存取，不是隨機存取，用二分查找并不能提高查找效率，因為你每次還得從第一個結點出發，找到指針LOW,HIGH,MIDDLE所指的元素，所以一般不在鏈表內使用二分查找。
File Trailer：文件尾，刷盤時校驗頁是否完整。詳細內參考下圖：

什么是off-page

MySQL的表中存儲數據的時候，數據是一行一行的存儲的。這個行要落在innodb的最小存儲單位：page頁中。好比我們的書本中的一行一行的文字是在頁中，一個頁里面有很多行。MySQL中的page頁，就是用來存儲多個行的基本單位。

但是如果一個行特別的大，大于了16KB的大小，那么此時一個page頁，就容納不下這個行了，此時就要在用2個甚至更多的page頁來存儲這個行的數據，這種現象就是off-page，即行溢出，off-page是指一個表的單行的大小超過了MySQL默認的一個page頁的大小。一個行，要占用多個頁來存儲對于這種現象，在不同的行存儲格式下面會有不同的處理方式，下面會有詳細的介紹。默認的方式是將多余的數據需要在overflow-page溢出頁中存儲。

InnoDB的文件存儲格式

InnoDB存儲引擎有兩種文件存儲格式：Antelope和Barracuda，而這兩種文件存儲格式下，有分別支持兩種行存儲格式。

Antelope(羚羊)：Compact(緊湊的)與Redundant(冗余的)兩種行記錄格式
1. compact：在存儲大的數據字段的時候，比如blob、text類型的字段，涉及到行溢出的問題。它在存儲text大字段的時候，會在一個page頁中存儲前768個字節，后面的字節會存儲在溢出頁``overflow page`中。
2. redundant：是最早的一種存儲格式，相比compact要占用更多的存儲空間。現在級別已經廢棄。
Barracuda(梭魚)：Dynamic(動態的)和Compress(壓縮的)還支持compact、redundant兩種。
1. dynamic：這種行存儲方式是目前MySQL5.7版本后默認的行存儲格式。它在存儲大字段的時候，只會在page頁中存儲一個指向溢出頁的一個20個字節的物理指針，而不會真正的去存放大字段的內容。真正的字段內容存儲在溢出頁overflow page中。這種方式，針對溢出列所在的新頁利用率更高，查詢的效率會減少磁盤的I/O交互次數，提高查效率。
2. compress：相比dynamic，除了基本功能和dynamic一樣之外，它是把字段內容以壓縮的方式存儲在page頁中，但是這種壓縮只是在物理存儲上的壓縮。在需要查詢對應的字段內容的時候，需要從物理的page頁面中，讀取到內存中的數據需要進行相應的解壓縮的操作，這樣就需要大量的CPU的支持，降低的數據庫的TPS，影響數據庫的響應時間，這是一種以時間來換取空間的思想。而在當前磁盤存儲空間不是瓶頸的前提下，這種方式一般不被大家所認可了。因為磁盤價格也不貴，花費時間在CPU解壓數據上而換取節省磁盤空間的成本。這是一種得不償失的做法。

注意：在Barracuda文件存儲格式下，也是支持compact和redundant這兩種行存儲格式的，這個是為了將文件存儲格式從Antelope向Barracuda慢慢過度才支持的。

查看MySQL數據庫innodb存儲引擎使用的文件格式和行存儲格式的命令如下：

mysql> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.01 sec)

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