Mysql是我們平時用的比較多的數(shù)據(jù)存儲工具，那么當執(zhí)行insert向Mysql中插入一條數(shù)據(jù)的時候，Mysql是如何存儲這條的數(shù)據(jù)的呢？下面我們來聊聊這個話題。

1、Mysql的數(shù)據(jù)存放在位置

    Mysql的數(shù)據(jù)是保存在磁盤上，數(shù)據(jù)具體保存在磁盤的哪個文件上是由存儲引擎決定的，Mysql支持多種存儲引擎（如InnoDB、MyISAM等），不同的存儲引擎保存的文件也存在一定的差異。以Mysql默認的存儲引擎InnoDB為例，InnoDB存儲引擎將文件存放在的位置如下圖所示：

圖片

   在InnoDB存儲引擎中，每當創(chuàng)建一個database的時候都會在/var/lib/mysql/目錄里面創(chuàng)建一個以database為名的目錄，然后表結(jié)構(gòu)和表數(shù)據(jù)的文件都會存放在這個目錄下，如下圖所示的表gonggao數(shù)據(jù)表文件：

圖片

在database為名的目錄存在三個文件，這些文件的含義如下所示：

（1）db.opt

    用來存儲當前數(shù)據(jù)庫的默認字符集和字符校驗規(guī)則。

（2）gonggao.frm

    gonggao的表結(jié)構(gòu)會保存在這個文件。在Mysql中建立一張表都會生成一個.frm 文件，該文件是用來保存每個表的元數(shù)據(jù)信息的，主要包含表結(jié)構(gòu)定義。

（3）gonggao.ibd

    gonggao的表數(shù)據(jù)會保存在這個文件。當然表數(shù)據(jù)既可以存在共享表空間文件(文件名：ibdata1)里，也可以存放在獨占表空間文件(文件名：表名字.ibd)。

    通過參數(shù)innodb_file_per_table控制可以設(shè)置表數(shù)據(jù)存儲位置，若設(shè)置參數(shù)innodb_file_per_table=1，則會將存儲的數(shù)據(jù)、索引等信息單獨存儲在一個獨占表空間（MySQL5.6.6版本開始，該參數(shù)的默認值為1，既就是表的數(shù)據(jù)都存放在一個獨立的.ibd文件）。Mysql8后，frm文件和idb文件合并成一個文件了，沒有frm文件了。

2、idb文件的結(jié)構(gòu)

    表空間由段(segment)、區(qū)(extent)、頁 (page，InnoDB與磁盤交互的單位)、行(row，具體存儲數(shù)據(jù)) 組成，InnoDB存儲引擎的邏輯存儲結(jié)構(gòu)如下圖：

圖片

（1）行

    數(shù)據(jù)庫表中的記錄都是按行(row)進行存放的，每行記錄根據(jù)不同的行格式，有不同的存儲結(jié)構(gòu)。

（2）頁

    記錄是按照行來存儲的，但是數(shù)據(jù)庫的讀取并不以行為單位，否則一次讀取只能處理一行數(shù)據(jù)，效率會非常低。InnoDB 的數(shù)據(jù)是按頁為單位來讀寫的，當讀一條記錄的時候，并不是將這個行記錄從磁盤讀出來，而是以頁為單位，將其整體讀入內(nèi)存。默認每頁的大小為16KB，也就是最多能保證16KB的連續(xù)存儲空間，當然這個16KB也是可以通過參數(shù)調(diào)整。

    頁是InnoDB存儲引擎磁盤管理的最小單元，每次數(shù)據(jù)庫讀寫都是以16KB為單位的，一次最少從磁盤中讀取16KB的內(nèi)容到內(nèi)存中。

（3）區(qū)

    InnoDB存儲引擎是用B+樹來組織數(shù)據(jù)的。B+樹中每一層都是通過雙向鏈表連接起來的，如果是以頁為單位來分配存儲空間，那么鏈表中相鄰的兩個頁之間的物理位置并不是連續(xù)的，可能離得非常遠，那么磁盤查詢時就會有大量的隨機I/O，隨機I/O會非常慢。

    區(qū)可以保證多個頁連續(xù)存儲，這樣在一個區(qū)里面的數(shù)據(jù)進行讀取的時候可以使用順序I/O。如果表中數(shù)據(jù)量大的時候，為某個索引分配空間的時候就不再按照頁為單位分配了，而是按照區(qū)為單位分配。每個區(qū)的大小為 1MB，對于16KB的頁來說，連續(xù)的64個頁（1024/16 = 64）會被劃為一個區(qū)，這樣就使得鏈表中相鄰的頁的物理位置也相鄰，就能使用順序I/O讀取。

（4）段 

    表空間是由各個段(segment)組成的，段是由多個區(qū)(extent)組成的。段一般分為數(shù)據(jù)段（存放B+樹的葉子節(jié)點的區(qū)的集合）、索引段（存放B+樹的非葉子節(jié)點的區(qū)的集合）和回滾段（存放的是回滾數(shù)據(jù)的區(qū)的集合）等。段是以區(qū)為單位保證多個區(qū)形成一個段。

3、InnoDB的行格式

    行格式是一條記錄的存儲結(jié)構(gòu)，InnoDB 提供了4種行格式，分別是Redundant、Compact、Dynamic和Compressed行格式。

    Redundant是很古老的行格式了，MySQL5.0版本之前用的行格式，現(xiàn)在基本沒人用了。

    Compact是一種緊湊的行格式，設(shè)計的初衷就是為了讓一個數(shù)據(jù)頁中可以存放更多的行記錄，從MySQL5.1版本之后，行格式默認設(shè)置成Compact。Compact的格式如下所示：

圖片

    Dynamic和Compressed兩個都是緊湊的行格式，它們的行格式都和Compact差不多，因為都是基于Compact進行改進的。從MySQL5.7版本之后，默認使用Dynamic行格式。

4、Compact的格式詳解

圖片

    記錄的額外信息有3個部分組成，分別是變長字段長度列表、null值列表、記錄頭信息。

4.1、變長字段長度列表

    Mysql支持一些變長的數(shù)據(jù)類型，比如 VARCHAR(m)、VARBINARY(n)、TEXT 類型、BLOB類型，這些數(shù)據(jù)類型修飾列稱為變長字段，變長字段中存儲多少字節(jié)的數(shù)據(jù)不是固定的，所以在存儲真實數(shù)據(jù)的時候需要順便把這些數(shù)據(jù)占用的字節(jié)數(shù)也存起來。

    在Compact 行格式中，把所有變長字段的真實數(shù)據(jù)占用的字節(jié)長度都存放在記錄的開頭部位，從而形成一個變長字段長度列表。創(chuàng)建如下的用戶表：

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;

    用戶表的字符集設(shè)置成ASCII，行格式設(shè)置為Compact格式，我們來看看行格式中的“變長字段長度列表”是怎樣存儲的，分成如下情況：

（a）變長字段不為null

    向用戶表中添加一條數(shù)據(jù)，如下圖所示：

圖片

由于表的字符集設(shè)置成ASCII，所以針對每個列的數(shù)據(jù)：

    name 列的值為a，真實數(shù)據(jù)占用的字節(jié)數(shù)是1字節(jié)，十六進制0x01；mobile列的值為789，真實數(shù)據(jù)占用的字節(jié)數(shù)是3字節(jié)，十六進制0x03；id列和age列不是變長字段，所以不用考慮。

    這些變長字段的真實數(shù)據(jù)占用的字節(jié)數(shù)會按照列的順序逆序存放，所以變長字段長度列表里的內(nèi)容是0301，變長字段長度列表存放下圖所示：

圖片

（b）存在一個變長字段為null

圖片

    name列的值為a，真實數(shù)據(jù)占用的字節(jié)數(shù)是1字節(jié)，十六進制0x01；mobile列的值為null，null是不會存放在行格式中記錄的真實數(shù)據(jù)部分里的，所以變長字段長度列表里不需要保存值為null的變長字段的長度；id列和age列不是變長字段，所以依舊不用考慮。變長字段長度列表存放下圖所示：

    變長字段字節(jié)數(shù)列表不是必須的，當數(shù)據(jù)表沒有變長字段的時候，這時候表里的行格式就不會有變長字段長度列表了，目的是節(jié)省空間。所以變長字段長度列表只出現(xiàn)在數(shù)據(jù)表有變長字段的時候。

4.2 null值列表

    表中的某些列可能會存儲null值，如果把這些null值都放到記錄的真實數(shù)據(jù)中會比較浪費空間，所以Compact行格式把這些值為null的列存儲到null值列表中。

    如果存在允許null值的列，則每個列對應(yīng)一個二進制位(bit)，二進制位按照列的順序逆序排列，二進制值的含義如下表所示：

    另外，值列表必須用整數(shù)個字節(jié)的位表示，如果使用的二進制位個數(shù)不足整數(shù)個字節(jié)，則在字節(jié)的高位補0。

（1）行數(shù)據(jù)無字段值為null

圖片

    InnoDB是用整數(shù)字節(jié)的二進制位來表示null值列表的，現(xiàn)在不足8位，所以要在高位補0，如下圖所示：

圖片

使用十六進制可以表示為0x00。

（2）行數(shù)據(jù)存在null值字段

圖片

此時的mobile字段值為null，使用二進制表示如下圖所示：

圖片

使用十六進制表示就是0x02

    null值列表不是必須的，當數(shù)據(jù)表的字段都定義成NOT NULL的時候，這時候表里的行格式就不會有null值列表了。所以在設(shè)計數(shù)據(jù)庫表的時候，通常都是建議將字段設(shè)置為NOT NULL，目的是為了節(jié)省至少1字節(jié)的空間(null值列表至少占用1字節(jié)空間)。

    null值列表的空間不是固定1字節(jié)的，當一條記錄有12個字段值是允許為null，那么就會創(chuàng)建2字節(jié)空間的null值列表。

4.3、記錄頭信息

記錄頭的信息如下圖所示：

圖片

（1）delete_mask：標識此條數(shù)據(jù)是否被刪除。從這里可以知道，我們執(zhí)行detele刪除記錄的時候，并不會真正的刪除記錄，只是將這個記錄的delete mask標記為1。

（2）next_record：下一條記錄的位置，記錄與記錄之間是通過鏈表組織的。

（3）record_type:表示當前記錄的類型，0表示普通記錄，1表示B+樹非葉子節(jié)點記錄，2表示最小記錄，3表示最大記錄。

（4）預(yù)留位1 (1 bit)：該位暫時未被使用。

（5）預(yù)留位2(1 bit)：該位暫時未被使用。

（6）min_rec_mask (1 bit)：B+樹的每層非葉子節(jié)點中的最小記錄都會添加該標記。如果是最小記錄，則該位為1；否則為0。

（7）n_owned(4bits)：表示當前記錄擁有的記錄數(shù)。使用4個bits來表示，可以表示的最大值為15。

（8）heap_no (13 bits)：表示當前記錄在記錄堆中的位置信息。使用13個bits來表示，可以表示的最大值為8191。

4.4 記錄的真實數(shù)據(jù)

    記錄真實數(shù)據(jù)部分除了我們定義的字段，還有三個隱藏字段，分別為:row_id、trx_id、roll_pointer，如下圖所示：

圖片

（1）row_id

    如果我們建表的時候指定了主鍵或者唯一約束列，那么就沒有row id隱藏字段了。如果既沒有指定主鍵，又沒有唯一約束，那么InnoDB就會為記錄添加row_id 隱藏字段。row_id不是必需的，占用6個字節(jié)。

（2）trx_id

    事務(wù)id，表示這個數(shù)據(jù)是由哪個事務(wù)生成的。trx_id是必需的，占用6個字節(jié)。

（3）roll_pointer

    這條記錄上一個版本的指針。roll_pointer是必需的，占用7個字節(jié)。

5、Mysql的數(shù)據(jù)存儲

    Mysql規(guī)定除了TEXT、BLOB這種大對象類型之外，其他所有的列(不包括隱藏列和記錄頭信息)占用的字節(jié)長度加起來不能超過65535個字節(jié)。換句話講，一行記錄除了TEXT、BLOB類型的列，限制最大為65535字節(jié)。

    varchar(n)字段類型的n代表的是最多存儲的字符數(shù)量，并不是字節(jié)大小。如果要計算varchar(n)最大能允許存儲的字節(jié)數(shù)，與數(shù)據(jù)庫表的字符集有關(guān)，因為字符集代表著1個字符要占用多少字節(jié)，如ASCII字符集中1個字符占用1字節(jié)，那么varchar(100)意味著最大能允許存儲100字節(jié)的數(shù)據(jù)。

    在ASCII字符集中下，varchar(n)中的n可以取65535嗎？答案是不行的，為什么呢？新建一張表，如下所示：

CREATE TABLE `test` (
`name` VARCHAR(65535) NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

    一行數(shù)據(jù)的最大字節(jié)數(shù)65535，其實是包含變長字段長度列表和null值列表所占用的字節(jié)數(shù)的。

圖片

    所以在計算varchar(n) 中n最大值時，需要減去變長字段長度列表和null值列表占用的字節(jié)數(shù)。

    65535 = 真實數(shù)據(jù)長度 + 變長字段長度列表長度 + null值列表長度

那么我們創(chuàng)建的test表中varchar的n最大可以取多少呢？分析如下所示：

（1）name字段是允許為null的，所以會用1字節(jié)（8個bit位）來表示null值列表。

（2）如果變長字段允許存儲的最大字節(jié)數(shù)小于等于255字節(jié)，就會用1字節(jié)表示變長字段長度；如果變長字段允許存儲的最大字節(jié)數(shù)大于255字節(jié)，就會用2字節(jié)表示變長字段長度。

    所以varchar(n)中n最大值= 65535 -2（name字段的最大字節(jié)數(shù)大于255字節(jié)，所以需要2個字節(jié)）-1（null值列表的字節(jié)數(shù)）=65532。

    當前是在字符集為ACSII下的計算方式，如果字符集是UTF-8時，一個字符最多需要三個字節(jié)，所以varchar(n)的n最大取值就是65532 / 3=21844。

6、行溢出

    Mysql中磁盤和內(nèi)存交互的基本單位是頁，一個頁的大小一般是16KB，也就是16384字節(jié)，而一個varchar(n)類型的列最多可以存儲65532字節(jié)，一些大對象如TEXT、BLOB可能存儲更多的數(shù)據(jù)，此時一個頁可能就存不了一條記錄。這個時候就會發(fā)生行溢出，多的數(shù)據(jù)就會存到另外的溢出頁中。

    InnoDB存儲引擎會自動將溢出的數(shù)據(jù)存放到溢出頁中，Compact行格式在發(fā)生行溢出后的處理方式是在記錄的真實數(shù)據(jù)處只會保存該列的一部分數(shù)據(jù)，而把剩余的數(shù)據(jù)放在溢出頁中，然后真實數(shù)據(jù)處用20字節(jié)存儲指向溢出頁的地址，從而可以找到剩余數(shù)據(jù)所在的頁，如下圖所示：

圖片

    Compressed和Dynamic這兩個行格式與Compact相比，他們的主要區(qū)別在于處理行溢出數(shù)據(jù)時存在一定的差異。這兩種格式采用完全行溢出方式，如下圖所示：

圖片

    記錄的真實數(shù)據(jù)處不會存儲該列的一部分數(shù)據(jù)，只存儲20個字節(jié)的指針來指向溢出頁，實際的數(shù)據(jù)都存儲在溢出頁中。