三分鐘掌握MySQL是如何存儲一行記錄
Mysql是我們平時用的比較多的數據存儲工具，那么當執行insert向Mysql中插入一條數據的時候，Mysql是如何存儲這條的數據的呢？下面我們來聊聊這個話題。
1、Mysql的數據存放在位置
Mysql的數據是保存在磁盤上，數據具體保存在磁盤的哪個文件上是由存儲引擎決定的，Mysql支持多種存儲引擎（如InnoDB、MyISAM等），不同的存儲引擎保存的文件也存在一定的差異。以Mysql默認的存儲引擎InnoDB為例，InnoDB存儲引擎將文件存放在的位置如下圖所示：
圖片
在InnoDB存儲引擎中，每當創建一個database的時候都會在/var/lib/mysql/目錄里面創建一個以database為名的目錄，然后表結構和表數據的文件都會存放在這個目錄下，如下圖所示的表gonggao數據表文件：
圖片
在database為名的目錄存在三個文件，這些文件的含義如下所示：
（1）db.opt
用來存儲當前數據庫的默認字符集和字符校驗規則。
（2）gonggao.frm
gonggao的表結構會保存在這個文件。在Mysql中建立一張表都會生成一個.frm 文件，該文件是用來保存每個表的元數據信息的，主要包含表結構定義。
（3）gonggao.ibd
gonggao的表數據會保存在這個文件。當然表數據既可以存在共享表空間文件(文件名：ibdata1)里，也可以存放在獨占表空間文件(文件名：表名字.ibd)。
通過參數innodb_file_per_table控制可以設置表數據存儲位置，若設置參數innodb_file_per_table=1，則會將存儲的數據、索引等信息單獨存儲在一個獨占表空間（MySQL5.6.6版本開始，該參數的默認值為1，既就是表的數據都存放在一個獨立的.ibd文件）。Mysql8后，frm文件和idb文件合并成一個文件了，沒有frm文件了。
2、idb文件的結構
表空間由段(segment)、區(extent)、頁 (page，InnoDB與磁盤交互的單位)、行(row，具體存儲數據) 組成，InnoDB存儲引擎的邏輯存儲結構如下圖：
圖片
（1）行
數據庫表中的記錄都是按行(row)進行存放的，每行記錄根據不同的行格式，有不同的存儲結構。
（2）頁
記錄是按照行來存儲的，但是數據庫的讀取并不以行為單位，否則一次讀取只能處理一行數據，效率會非常低。InnoDB 的數據是按頁為單位來讀寫的，當讀一條記錄的時候，并不是將這個行記錄從磁盤讀出來，而是以頁為單位，將其整體讀入內存。默認每頁的大小為16KB，也就是最多能保證16KB的連續存儲空間，當然這個16KB也是可以通過參數調整。
頁是InnoDB存儲引擎磁盤管理的最小單元，每次數據庫讀寫都是以16KB為單位的，一次最少從磁盤中讀取16KB的內容到內存中。
（3）區
InnoDB存儲引擎是用B+樹來組織數據的。B+樹中每一層都是通過雙向鏈表連接起來的，如果是以頁為單位來分配存儲空間，那么鏈表中相鄰的兩個頁之間的物理位置并不是連續的，可能離得非常遠，那么磁盤查詢時就會有大量的隨機I/O，隨機I/O會非常慢。
區可以保證多個頁連續存儲，這樣在一個區里面的數據進行讀取的時候可以使用順序I/O。如果表中數據量大的時候，為某個索引分配空間的時候就不再按照頁為單位分配了，而是按照區為單位分配。每個區的大小為 1MB，對于16KB的頁來說，連續的64個頁（1024/16 = 64）會被劃為一個區，這樣就使得鏈表中相鄰的頁的物理位置也相鄰，就能使用順序I/O讀取。
（4）段
表空間是由各個段(segment)組成的，段是由多個區(extent)組成的。段一般分為數據段（存放B+樹的葉子節點的區的集合）、索引段（存放B+樹的非葉子節點的區的集合）和回滾段（存放的是回滾數據的區的集合）等。段是以區為單位保證多個區形成一個段。
3、InnoDB的行格式
行格式是一條記錄的存儲結構，InnoDB 提供了4種行格式，分別是Redundant、Compact、Dynamic和Compressed行格式。
Redundant是很古老的行格式了，MySQL5.0版本之前用的行格式，現在基本沒人用了。
Compact是一種緊湊的行格式，設計的初衷就是為了讓一個數據頁中可以存放更多的行記錄，從MySQL5.1版本之后，行格式默認設置成Compact。Compact的格式如下所示：
圖片
Dynamic和Compressed兩個都是緊湊的行格式，它們的行格式都和Compact差不多，因為都是基于Compact進行改進的。從MySQL5.7版本之后，默認使用Dynamic行格式。
4、Compact的格式詳解
圖片
記錄的額外信息有3個部分組成，分別是變長字段長度列表、null值列表、記錄頭信息。
4.1、變長字段長度列表
Mysql支持一些變長的數據類型，比如 VARCHAR(m)、VARBINARY(n)、TEXT 類型、BLOB類型，這些數據類型修飾列稱為變長字段，變長字段中存儲多少字節的數據不是固定的，所以在存儲真實數據的時候需要順便把這些數據占用的字節數也存起來。
在Compact 行格式中，把所有變長字段的真實數據占用的字節長度都存放在記錄的開頭部位，從而形成一個變長字段長度列表。創建如下的用戶表：
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` VARCHAR(20)DEFAULT NULL,
`mobile` VARCHAR(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT =COMPACT;用戶表的字符集設置成ASCII，行格式設置為Compact格式，我們來看看行格式中的“變長字段長度列表”是怎樣存儲的，分成如下情況：
（a）變長字段不為null
向用戶表中添加一條數據，如下圖所示：
圖片
由于表的字符集設置成ASCII，所以針對每個列的數據：
name 列的值為a，真實數據占用的字節數是1字節，十六進制0x01；mobile列的值為789，真實數據占用的字節數是3字節，十六進制0x03；id列和age列不是變長字段，所以不用考慮。
這些變長字段的真實數據占用的字節數會按照列的順序逆序存放，所以變長字段長度列表里的內容是0301，變長字段長度列表存放下圖所示：
圖片
（b）存在一個變長字段為null
圖片
name列的值為a，真實數據占用的字節數是1字節，十六進制0x01；mobile列的值為null，null是不會存放在行格式中記錄的真實數據部分里的，所以變長字段長度列表里不需要保存值為null的變長字段的長度；id列和age列不是變長字段，所以依舊不用考慮。變長字段長度列表存放下圖所示：
變長字段字節數列表不是必須的，當數據表沒有變長字段的時候，這時候表里的行格式就不會有變長字段長度列表了，目的是節省空間。所以變長字段長度列表只出現在數據表有變長字段的時候。
4.2 null值列表
表中的某些列可能會存儲null值，如果把這些null值都放到記錄的真實數據中會比較浪費空間，所以Compact行格式把這些值為null的列存儲到null值列表中。
如果存在允許null值的列，則每個列對應一個二進制位(bit)，二進制位按照列的順序逆序排列，二進制值的含義如下表所示：
二進制值 | 含義 |
1 | 該列的值為null |
0 | 該列的值不為null |
另外，值列表必須用整數個字節的位表示，如果使用的二進制位個數不足整數個字節，則在字節的高位補0。
（1）行數據無字段值為null
圖片
InnoDB是用整數字節的二進制位來表示null值列表的，現在不足8位，所以要在高位補0，如下圖所示：
圖片
使用十六進制可以表示為0x00。
（2）行數據存在null值字段
圖片
此時的mobile字段值為null，使用二進制表示如下圖所示：
圖片
使用十六進制表示就是0x02
null值列表不是必須的，當數據表的字段都定義成NOT NULL的時候，這時候表里的行格式就不會有null值列表了。所以在設計數據庫表的時候，通常都是建議將字段設置為NOT NULL，目的是為了節省至少1字節的空間(null值列表至少占用1字節空間)。
null值列表的空間不是固定1字節的，當一條記錄有12個字段值是允許為null，那么就會創建2字節空間的null值列表。
4.3、記錄頭信息
記錄頭的信息如下圖所示：
圖片
（1）delete_mask：標識此條數據是否被刪除。從這里可以知道，我們執行detele刪除記錄的時候，并不會真正的刪除記錄，只是將這個記錄的delete mask標記為1。
（2）next_record：下一條記錄的位置，記錄與記錄之間是通過鏈表組織的。
（3）record_type:表示當前記錄的類型，0表示普通記錄，1表示B+樹非葉子節點記錄，2表示最小記錄，3表示最大記錄。
（4）預留位1 (1 bit)：該位暫時未被使用。
（5）預留位2(1 bit)：該位暫時未被使用。
（6）min_rec_mask (1 bit)：B+樹的每層非葉子節點中的最小記錄都會添加該標記。如果是最小記錄，則該位為1；否則為0。
（7）n_owned(4bits)：表示當前記錄擁有的記錄數。使用4個bits來表示，可以表示的最大值為15。
（8）heap_no (13 bits)：表示當前記錄在記錄堆中的位置信息。使用13個bits來表示，可以表示的最大值為8191。
4.4 記錄的真實數據
記錄真實數據部分除了我們定義的字段，還有三個隱藏字段，分別為:row_id、trx_id、roll_pointer，如下圖所示：
圖片
（1）row_id
如果我們建表的時候指定了主鍵或者唯一約束列，那么就沒有row id隱藏字段了。如果既沒有指定主鍵，又沒有唯一約束，那么InnoDB就會為記錄添加row_id 隱藏字段。row_id不是必需的，占用6個字節。
（2）trx_id
事務id，表示這個數據是由哪個事務生成的。trx_id是必需的，占用6個字節。
（3）roll_pointer
這條記錄上一個版本的指針。roll_pointer是必需的，占用7個字節。
5、Mysql的數據存儲
Mysql規定除了TEXT、BLOB這種大對象類型之外，其他所有的列(不包括隱藏列和記錄頭信息)占用的字節長度加起來不能超過65535個字節。換句話講，一行記錄除了TEXT、BLOB類型的列，限制最大為65535字節。
varchar(n)字段類型的n代表的是最多存儲的字符數量，并不是字節大小。如果要計算varchar(n)最大能允許存儲的字節數，與數據庫表的字符集有關，因為字符集代表著1個字符要占用多少字節，如ASCII字符集中1個字符占用1字節，那么varchar(100)意味著最大能允許存儲100字節的數據。
在ASCII字符集中下，varchar(n)中的n可以取65535嗎？答案是不行的，為什么呢？新建一張表，如下所示：
CREATE TABLE `test` (
`name` VARCHAR(65535) NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;一行數據的最大字節數65535，其實是包含變長字段長度列表和null值列表所占用的字節數的。
圖片
所以在計算varchar(n) 中n最大值時，需要減去變長字段長度列表和null值列表占用的字節數。
65535 = 真實數據長度 + 變長字段長度列表長度 + null值列表長度
那么我們創建的test表中varchar的n最大可以取多少呢？分析如下所示：
（1）name字段是允許為null的，所以會用1字節（8個bit位）來表示null值列表。
（2）如果變長字段允許存儲的最大字節數小于等于255字節，就會用1字節表示變長字段長度；如果變長字段允許存儲的最大字節數大于255字節，就會用2字節表示變長字段長度。
所以varchar(n)中n最大值= 65535 -2（name字段的最大字節數大于255字節，所以需要2個字節）-1（null值列表的字節數）=65532。
當前是在字符集為ACSII下的計算方式，如果字符集是UTF-8時，一個字符最多需要三個字節，所以varchar(n)的n最大取值就是65532 / 3=21844。
6、行溢出
Mysql中磁盤和內存交互的基本單位是頁，一個頁的大小一般是16KB，也就是16384字節，而一個varchar(n)類型的列最多可以存儲65532字節，一些大對象如TEXT、BLOB可能存儲更多的數據，此時一個頁可能就存不了一條記錄。這個時候就會發生行溢出，多的數據就會存到另外的溢出頁中。
InnoDB存儲引擎會自動將溢出的數據存放到溢出頁中，Compact行格式在發生行溢出后的處理方式是在記錄的真實數據處只會保存該列的一部分數據，而把剩余的數據放在溢出頁中，然后真實數據處用20字節存儲指向溢出頁的地址，從而可以找到剩余數據所在的頁，如下圖所示：
圖片
Compressed和Dynamic這兩個行格式與Compact相比，他們的主要區別在于處理行溢出數據時存在一定的差異。這兩種格式采用完全行溢出方式，如下圖所示：
圖片
記錄的真實數據處不會存儲該列的一部分數據，只存儲20個字節的指針來指向溢出頁，實際的數據都存儲在溢出頁中。
