到現(xiàn)在為止胀茵，MySQL對(duì)于我們來(lái)說(shuō)還是一個(gè)黑盒，我們只負(fù)責(zé)使用客戶(hù)端發(fā)送請(qǐng)求并等待服務(wù)器返回結(jié)果胁后，表中的數(shù)據(jù)到底存到了哪里?以什么格式存放的?MySQL是以什么方式來(lái)訪問(wèn)的這些數(shù)據(jù)?這些 問(wèn)題我們統(tǒng)統(tǒng)不知道店读。

我們前邊嘮叨請(qǐng)求處理過(guò)程的時(shí)候提到過(guò)，MySQL服務(wù)器上負(fù)責(zé)對(duì)表中數(shù)據(jù)的讀取和寫(xiě)入工作的部分是存儲(chǔ)引擎攀芯，而服務(wù)器又支持不同類(lèi)型的存儲(chǔ)引擎屯断，比如InnoDB、MyISAM侣诺、Memory啥的殖演，不同的存儲(chǔ)引 擎一般是由不同的人為實(shí)現(xiàn)不同的特性而開(kāi)發(fā)的，真實(shí)數(shù)據(jù)在不同存儲(chǔ)引擎中存放的格式一般是不同的年鸳，甚至有的存儲(chǔ)引擎比如Memory都不用磁盤(pán)來(lái)存儲(chǔ)數(shù)據(jù)趴久，也就是說(shuō)關(guān)閉服務(wù)器后表中的數(shù)據(jù)就消失 了。由于InnoDB是MySQL默認(rèn)的存儲(chǔ)引擎搔确，也是我們最常用到的存儲(chǔ)引擎彼棍，我們也沒(méi)有那么多時(shí)間去把各個(gè)存儲(chǔ)引擎的內(nèi)部實(shí)現(xiàn)都看一遍，所以本集要嘮叨的是使用InnoDB作為存儲(chǔ)引擎的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)膳算， 了解了一個(gè)存儲(chǔ)引擎的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)之后座硕，其他的存儲(chǔ)引擎都是依葫蘆畫(huà)瓢，等我們用到了再說(shuō)哈~

InnoDB頁(yè)簡(jiǎn)介

InnoDB是一個(gè)將表中的數(shù)據(jù)存儲(chǔ)到磁盤(pán)上的存儲(chǔ)引擎涕蜂，所以即使關(guān)機(jī)后重啟我們的數(shù)據(jù)還是存在的华匾。而真正處理數(shù)據(jù)的過(guò)程是發(fā)生在內(nèi)存中的，所以需要把磁盤(pán)中的數(shù)據(jù)加載到內(nèi)存中机隙，如果是處理寫(xiě)入 或修改請(qǐng)求的話(huà)蜘拉，還需要把內(nèi)存中的內(nèi)容刷新到磁盤(pán)上。而我們知道讀寫(xiě)磁盤(pán)的速度非常慢有鹿，和內(nèi)存讀寫(xiě)差了幾個(gè)數(shù)量級(jí)旭旭，所以當(dāng)我們想從表中獲取某些記錄時(shí)，InnoDB存儲(chǔ)引擎需要一條一條的把記錄 從磁盤(pán)上讀出來(lái)么?不印颤，那樣會(huì)慢死您机，InnoDB采取的方式是:將數(shù)據(jù)劃分為若干個(gè)頁(yè)，以頁(yè)作為磁盤(pán)和內(nèi)存之間交互的基本單位，InnoDB中頁(yè)的大小一般為 16 KB际看。也就是在一般情況下咸产，一次最少?gòu)拇?盤(pán)中讀取16KB的內(nèi)容到內(nèi)存中，一次最少把內(nèi)存中的16KB內(nèi)容刷新到磁盤(pán)中仲闽。

InnoDB行格式

我們平時(shí)是以記錄為單位來(lái)向表中插入數(shù)據(jù)的脑溢，這些記錄在磁盤(pán)上的存放方式也被稱(chēng)為行格式或者記錄格式。設(shè)計(jì)InnoDB存儲(chǔ)引擎的大叔們到現(xiàn)在為止設(shè)計(jì)了4種不同類(lèi)型的行格式赖欣，分別
是Compact屑彻、Redundant、Dynamic和Compressed行格式顶吮，隨著時(shí)間的推移社牲，他們可能會(huì)設(shè)計(jì)出更多的行格式，但是不管怎么變悴了，在原理上大體都是相同的搏恤。 指定行格式的語(yǔ)法
指定行格式的語(yǔ)法
我們可以在創(chuàng)建或修改表的語(yǔ)句中指定行格式:

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名稱(chēng)
ALTER TABLE 表名 ROW_FORMAT=行格式名稱(chēng) 

比如我們?cè)趚iaohaizi數(shù)據(jù)庫(kù)里創(chuàng)建一個(gè)演示用的表record_format_demo，可以這樣指定它的行格式:

mysql> USE xiaohaizi;
Database changed

mysql> CREATE TABLE record_format_demo (
    ->     c1 VARCHAR(10),
    ->     c2 VARCHAR(10) NOT NULL,
    ->     c3 CHAR(10),
    ->     c4 VARCHAR(10)
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.03 sec)

可以看到我們剛剛創(chuàng)建的這個(gè)表的行格式就是Compact湃交，另外熟空，我們還顯式指定了這個(gè)表的字符集為ascii，因?yàn)閍scii字符集只包括空格搞莺、標(biāo)點(diǎn)符號(hào)息罗、數(shù)字、大小寫(xiě)字母和一些不可見(jiàn)字符才沧，所以我們的漢 字是不能存到這個(gè)表里的迈喉。我們現(xiàn)在向這個(gè)表中插入兩條記錄:

mysql> INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd'), ('eeee', 'fff', NULL, NULL);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

現(xiàn)在表中的記錄就是這個(gè)樣子的:

mysql> SELECT * FROM record_format_demo;
+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.00 sec)

mysql>

演示表的內(nèi)容也填充好了，現(xiàn)在我們就來(lái)看看各個(gè)行格式下的存儲(chǔ)方式到底有啥不同吧～

COMPACT行格式

image.png

大家從圖中可以看出來(lái)糜工，一條完整的記錄其實(shí)可以被分為記錄的額外信息和記錄的真實(shí)數(shù)據(jù)兩大部分弊添，下邊我們?cè)敿?xì)看一下這兩部分的組成。

記錄的額外信息
這部分信息是服務(wù)器為了描述這條記錄而不得不額外添加的一些信息捌木，這些額外信息分為3類(lèi)，分別是變長(zhǎng)字段長(zhǎng)度列表嫉戚、NULL值列表和記錄頭信息刨裆，我們分別看一下。

變長(zhǎng)字段長(zhǎng)度列表
我們知道MySQL支持一些變長(zhǎng)的數(shù)據(jù)類(lèi)型彬檀，比如VARCHAR(M)帆啃、VARBINARY(M)、各種TEXT類(lèi)型窍帝，各種BLOB類(lèi)型努潘，我們也可以把擁有這些數(shù)據(jù)類(lèi)型的列稱(chēng)為變長(zhǎng)字段，變長(zhǎng)字段中存儲(chǔ)多少字節(jié)的數(shù)據(jù)是不固定 的，所以我們?cè)诖鎯?chǔ)真實(shí)數(shù)據(jù)的時(shí)候需要順便把這些數(shù)據(jù)占用的字節(jié)數(shù)也存起來(lái)疯坤，這樣才不至于把MySQL服務(wù)器搞懵报慕，所以這些變長(zhǎng)字段占用的存儲(chǔ)空間分為兩部分:

1. 真正的數(shù)據(jù)內(nèi)容
2. 占用的字節(jié)數(shù)

在Compact行格式中，把所有變長(zhǎng)字段的真實(shí)數(shù)據(jù)占用的字節(jié)長(zhǎng)度都存放在記錄的開(kāi)頭部位压怠，從而形成一個(gè)變長(zhǎng)字段長(zhǎng)度列表眠冈，各變長(zhǎng)字段數(shù)據(jù)占用的字節(jié)數(shù)按照列的順序逆序存放菌瘫，我們?cè)俅螐?qiáng)調(diào)一遍， 是逆序存放!

我們拿record_format_demo表中的第一條記錄來(lái)舉個(gè)例子雇盖。因?yàn)閞ecord_format_demo表的c1栖忠、c2、c4列都是VARCHAR(10)類(lèi)型的虚汛，也就是變長(zhǎng)的數(shù)據(jù)類(lèi)型，所以這三個(gè)列的值的長(zhǎng)度都需要保存在記錄開(kāi)頭 處皇帮，因?yàn)閞ecord_format_demo表中的各個(gè)列都使用的是ascii字符集卷哩，所以每個(gè)字符只需要1個(gè)字節(jié)來(lái)進(jìn)行編碼，來(lái)看一下第一條記錄各變長(zhǎng)字段內(nèi)容的長(zhǎng)度:

又因?yàn)檫@些長(zhǎng)度值需要按照列的逆序存放属拾，所以最后變長(zhǎng)字段長(zhǎng)度列表的字節(jié)串用十六進(jìn)制表示的效果就是(各個(gè)字節(jié)之間實(shí)際上沒(méi)有空格将谊，用空格隔開(kāi)只是方便理解):
01 03 04

把這個(gè)字節(jié)串組成的變長(zhǎng)字段長(zhǎng)度列表填入上邊的示意圖中的效果就是:

image.png

由于第一行記錄中c1、c2渐白、c4列中的字符串都比較短尊浓，也就是說(shuō)內(nèi)容占用的字節(jié)數(shù)比較小，用1個(gè)字節(jié)就可以表示纯衍，但是如果變長(zhǎng)列的內(nèi)容占用的字節(jié)數(shù)比較多栋齿，可能就需要用2個(gè)字節(jié)來(lái)表示。具體用1個(gè) 還是2個(gè)字節(jié)來(lái)表示真實(shí)數(shù)據(jù)占用的字節(jié)數(shù)襟诸，InnoDB有它的一套規(guī)則瓦堵，我們首先聲明一下W、M和L的意思:

1. 假設(shè)某個(gè)字符集中表示一個(gè)字符最多需要使用的字節(jié)數(shù)為W歌亲，也就是使用SHOW CHARSET語(yǔ)句的結(jié)果中的Maxlen列菇用，比方說(shuō)utf8字符集中的W就是3，gbk字符集中的W就是2陷揪，ascii字符集中的W就是1惋鸥。 2. 對(duì)于變長(zhǎng)類(lèi)型VARCHAR(M)來(lái)說(shuō)杂穷，這種類(lèi)型表示能存儲(chǔ)最多M個(gè)字符(注意是字符不是字節(jié))，所以這個(gè)類(lèi)型能表示的字符串最多占用的字節(jié)數(shù)就是M×W卦绣。
2. 假設(shè)它實(shí)際存儲(chǔ)的字符串占用的字節(jié)數(shù)是L。

所以確定使用1個(gè)字節(jié)還是2個(gè)字節(jié)表示真正字符串占用的字節(jié)數(shù)的規(guī)則就是這樣:

• 如果M×W <= 255拴鸵，那么使用1個(gè)字節(jié)來(lái)表示真正字符串占用的字節(jié)數(shù)。
  也就是說(shuō)InnoDB在讀記錄的變長(zhǎng)字段長(zhǎng)度列表時(shí)先查看表結(jié)構(gòu)聘芜，如果某個(gè)變長(zhǎng)字段允許存儲(chǔ)的最大字節(jié)數(shù)不大于255時(shí)，可以認(rèn)為只使用1個(gè)字節(jié)來(lái)表示真正字符串占用的字節(jié)數(shù)瞎饲。

• 如果M×W > 255，則分為兩種情況:

1. 如果L <= 127驮捍，則用1個(gè)字節(jié)來(lái)表示真正字符串占用的字節(jié)數(shù)。
2. 如果L > 127珊泳，則用2個(gè)字節(jié)來(lái)表示真正字符串占用的字節(jié)數(shù)。

總結(jié)一下就是說(shuō):如果該可變字段允許存儲(chǔ)的最大字節(jié)數(shù)(M×W)超過(guò)255字節(jié)并且真實(shí)存儲(chǔ)的字節(jié)數(shù)(L)超過(guò)127字節(jié)，則使用2個(gè)字節(jié)勤庐，否則使用1個(gè)字節(jié)米罚。

另外需要注意的一點(diǎn)是录择，變長(zhǎng)字段長(zhǎng)度列表中只存儲(chǔ)值為 非NULL 的列內(nèi)容占用的長(zhǎng)度，值為 NULL 的列的長(zhǎng)度是不儲(chǔ)存的 动看。也就是說(shuō)對(duì)于第二條記錄來(lái)說(shuō)菱皆，因?yàn)閏4列的值為NULL，所以第二條記錄的變 長(zhǎng)字段長(zhǎng)度列表只需要存儲(chǔ)c1和c2列的長(zhǎng)度即可篷店。其中c1列存儲(chǔ)的值為'eeee'，占用的字節(jié)數(shù)為4鸭轮，c2列存儲(chǔ)的值為'fff'窃爷，占用的字節(jié)數(shù)為3。數(shù)字4可以用1個(gè)字節(jié)表示逮京，3也可以用1個(gè)字節(jié)表示草描，所以整 個(gè)變長(zhǎng)字段長(zhǎng)度列表共需2個(gè)字節(jié)。填充完變長(zhǎng)字段長(zhǎng)度列表的兩條記錄的對(duì)比圖如下:

image.png

小貼士: 并不是所有記錄都有這個(gè) 變長(zhǎng)字段長(zhǎng)度列表 部分逛绵，比方說(shuō)表中所有的列都不是變長(zhǎng)的數(shù)據(jù)類(lèi)型的話(huà)，這一部分就不需要有添吗。

NULL值列表
我們知道表中的某些列可能存儲(chǔ)NULL值，如果把這些NULL值都放到記錄的真實(shí)數(shù)據(jù)中存儲(chǔ)會(huì)很占地方鲤孵，所以Compact行格式把這些值為NULL的列統(tǒng)一管理起來(lái)，存儲(chǔ)到NULL值列表中凯正，它的處理過(guò)程是這樣 的:

1. 首先統(tǒng)計(jì)表中允許存儲(chǔ)NULL的列有哪些。
   我們前邊說(shuō)過(guò)允睹，主鍵列、被NOT NULL修飾的列都是不可以存儲(chǔ)NULL值的该互，所以在統(tǒng)計(jì)的時(shí)候不會(huì)把這些列算進(jìn)去塘雳。比方說(shuō)表record_format_demo的3個(gè)列c1、c3妻顶、c4都是允許存儲(chǔ)NULL值的讳嘱，而c2列是被NOT NULL修飾嬉挡，不允許存儲(chǔ)NULL值拔恰。

2. 如果表中沒(méi)有允許存儲(chǔ) NULL 的列，則 NULL值列表 也不存在了河爹，否則將每個(gè)允許存儲(chǔ)NULL的列對(duì)應(yīng)一個(gè)二進(jìn)制位，二進(jìn)制位按照列的順序逆序排列，二進(jìn)制位表示的意義如下:

• 二進(jìn)制位的值為1時(shí)侨艾，代表該列的值為NULL。

• 二進(jìn)制位的值為0時(shí)当叭，代表該列的值不為NULL磺芭。
  因?yàn)楸韗ecord_format_demo有3個(gè)值允許為NULL的列，所以這3個(gè)列和二進(jìn)制位的對(duì)應(yīng)關(guān)系就是這樣:

  
  
  image.png
  
  

  再一次強(qiáng)調(diào)放棒，二進(jìn)制位按照列的順序逆序排列损肛，所以第一個(gè)列c1和最后一個(gè)二進(jìn)制位對(duì)應(yīng)荧关。

3. MySQL規(guī)定NULL值列表必須用整數(shù)個(gè)字節(jié)的位表示溉奕，如果使用的二進(jìn)制位個(gè)數(shù)不是整數(shù)個(gè)字節(jié)，則在字節(jié)的高位補(bǔ)0忍啤。

表record_format_demo只有3個(gè)值允許為NULL的列加勤，對(duì)應(yīng)3個(gè)二進(jìn)制位，不足一個(gè)字節(jié)同波，所以在字節(jié)的高位補(bǔ)0鳄梅，效果就是這樣:

image.png

以此類(lèi)推冤狡，如果一個(gè)表中有9個(gè)允許為NULL坦胶，那這個(gè)記錄的NULL值列表部分就需要2個(gè)字節(jié)來(lái)表示了。 知道了規(guī)則之后，我們?cè)俜祷仡^看表record_format_demo中的兩條記錄中的NULL值列表應(yīng)該怎么儲(chǔ)存缓待。因?yàn)橹挥衏1答姥、c3祈噪、c4這3個(gè)列允許存儲(chǔ)NULL值澈蝙，所以所有記錄的NULL值列表只需要一個(gè)字節(jié)厉斟。

• 對(duì)于第一條記錄來(lái)說(shuō)，c1霉祸、c3、c4這3個(gè)列的值都不為NULL，所以它們對(duì)應(yīng)的二進(jìn)制位都是0，畫(huà)個(gè)圖就是這樣:

  
  
  image.png

所以第一條記錄的NULL值列表用十六進(jìn)制表示就是:0x00旅赢。

• 對(duì)于第二條記錄來(lái)說(shuō)梗脾，c1偶翅、c3媳搪、c4這3個(gè)列中c3和c4的值都為NULL，所以這3個(gè)列對(duì)應(yīng)的二進(jìn)制位的情況就是:

  
  
  image.png
  
  

  所以第二條記錄的NULL值列表用十六進(jìn)制表示就是:0x06癣缅。

所以這兩條記錄在填充了NULL值列表后的示意圖就是這樣:

image.png

記錄頭信息
除了變長(zhǎng)字段長(zhǎng)度列表拖叙、NULL值列表之外氓润，還有一個(gè)用于描述記錄的記錄頭信息，它是由固定的5個(gè)字節(jié)組成薯鳍。5個(gè)字節(jié)也就是40個(gè)二進(jìn)制位咖气，不同的位代表不同的意思，如圖:

image.png

這些二進(jìn)制位代表的詳細(xì)信息如下表:

image.png

大家不要被這么多的屬性和陌生的概念給嚇著挖滤，我這里只是為了內(nèi)容的完整性把這些位代表的意思都寫(xiě)了出來(lái)崩溪，現(xiàn)在沒(méi)必要把它們的意思都記住，記住也沒(méi)啥用斩松，現(xiàn)在只需要看一遍混個(gè)臉熟伶唯，等之后用 到這些屬性的時(shí)候我們?cè)倩剡^(guò)頭來(lái)看。

記錄的真實(shí)數(shù)據(jù)
對(duì)于record_format_demo表來(lái)說(shuō)惧盹，記錄的真實(shí)數(shù)據(jù)除了c1乳幸、c2、c3钧椰、c4這幾個(gè)我們自己定義的列的數(shù)據(jù)以外粹断，MySQL會(huì)為每個(gè)記錄默認(rèn)的添加一些列(也稱(chēng)為隱藏列)，具體的列如下:

小貼士: 實(shí)際上這幾個(gè)列的真正名稱(chēng)其實(shí)是:DB_ROW_ID姿染、DB_TRX_ID、DB_ROLL_PTR秒际，我們?yōu)榱嗣烙^才寫(xiě)成了row_id、transaction_id和roll_pointer狡汉。

這里需要提一下InnoDB表對(duì)主鍵的生成策略:優(yōu)先使用用戶(hù)自定義主鍵作為主鍵娄徊，如果用戶(hù)沒(méi)有定義主鍵，則選取一個(gè)Unique鍵作為主鍵盾戴，如果表中連Unique鍵都沒(méi)有定義的話(huà)寄锐，則InnoDB會(huì)為表默認(rèn)添 加一個(gè)名為row_id的隱藏列作為主鍵。所以我們從上表中可以看出:InnoDB存儲(chǔ)引擎會(huì)為每條記錄都添加 transaction_id 和 roll_pointer 這兩個(gè)列尖啡，但是 row_id 是可選的(在沒(méi)有自定義主鍵以及Unique鍵 的情況下才會(huì)添加該列)橄仆。這些隱藏列的值不用我們操心，InnoDB存儲(chǔ)引擎會(huì)自己幫我們生成的衅斩。

因?yàn)楸韗ecord_format_demo并沒(méi)有定義主鍵盆顾，所以MySQL服務(wù)器會(huì)為每條記錄增加上述的3個(gè)列。現(xiàn)在看一下加上記錄的真實(shí)數(shù)據(jù)的兩個(gè)記錄長(zhǎng)什么樣吧:

image.png

看這個(gè)圖的時(shí)候我們需要注意幾點(diǎn):

1. 表record_format_demo使用的是ascii字符集畏梆，所以0x61616161就表示字符串'aaaa'您宪，0x626262就表示字符串'bbb'奈懒，以此類(lèi)推。
2. 注意第1條記錄中c3列的值宪巨，它是CHAR(10)類(lèi)型的磷杏，它實(shí)際存儲(chǔ)的字符串是:'cc'，而ascii字符集中的字節(jié)表示是'0x6363'捏卓，雖然表示這個(gè)字符串只占用了2個(gè)字節(jié)极祸，但整個(gè)c3列仍然占用了10個(gè)字 節(jié)的空間，除真實(shí)數(shù)據(jù)以外的8個(gè)字節(jié)的統(tǒng)統(tǒng)都用空格字符填充怠晴，空格字符在ascii字符集的表示就是0x20遥金。
3. 注意第2條記錄中c3和c4列的值都為NULL，它們被存儲(chǔ)在了前邊的NULL值列表處龄寞，在記錄的真實(shí)數(shù)據(jù)處就不再冗余存儲(chǔ)汰规，從而節(jié)省存儲(chǔ)空間。

CHAR(M)列的存儲(chǔ)格式

record_format_demo表的c1物邑、c2溜哮、c4列的類(lèi)型是VARCHAR(10)，而c3列的類(lèi)型是CHAR(10)色解，我們說(shuō)在Compact行格式下只會(huì)把變長(zhǎng)類(lèi)型的列的長(zhǎng)度逆序存到變長(zhǎng)字段長(zhǎng)度列表中茂嗓，就像這樣:

image.png

但是這只是因?yàn)槲覀兊膔ecord_format_demo表采用的是ascii字符集，這個(gè)字符集是一個(gè)定長(zhǎng)字符集科阎，也就是說(shuō)表示一個(gè)字符采用固定的一個(gè)字節(jié)述吸，如果采用變長(zhǎng)的字符集(也就是表示一個(gè)字符需要的字 節(jié)數(shù)不確定，比如gbk表示一個(gè)字符要1_{2個(gè)字節(jié)锣笨、utf8表示一個(gè)字符要1}3個(gè)字節(jié)等)的話(huà)蝌矛，c3列的長(zhǎng)度也會(huì)被存儲(chǔ)到變長(zhǎng)字段長(zhǎng)度列表中，比如我們修改一下record_format_demo表的字符集:

mysql> ALTER TABLE record_format_demo MODIFY COLUMN c3 CHAR(10) CHARACTER SET utf8;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

修改該列字符集后記錄的變長(zhǎng)字段長(zhǎng)度列表也發(fā)生了變化错英，如圖:

image.png

這就意味著:對(duì)于 CHAR(M) 類(lèi)型的列來(lái)說(shuō)入撒，當(dāng)列采用的是定長(zhǎng)字符集時(shí)，該列占用的字節(jié)數(shù)不會(huì)被加到變長(zhǎng)字段長(zhǎng)度列表椭岩，而如果采用變長(zhǎng)字符集時(shí)茅逮，該列占用的字節(jié)數(shù)也會(huì)被加到變長(zhǎng)字段長(zhǎng)度列表。

另外有一點(diǎn)還需要注意判哥，變長(zhǎng)字符集的CHAR(M)類(lèi)型的列要求至少占用M個(gè)字節(jié)献雅，而VARCHAR(M)卻沒(méi)有這個(gè)要求。比方說(shuō)對(duì)于使用utf8字符集的CHAR(10)的列來(lái)說(shuō)塌计，該列存儲(chǔ)的數(shù)據(jù)字節(jié)長(zhǎng)度的范圍是10~ 30個(gè)字節(jié)挺身。即使我們向該列中存儲(chǔ)一個(gè)空字符串也會(huì)占用10個(gè)字節(jié)，這是怕將來(lái)更新該列的值的字節(jié)長(zhǎng)度大于原有值的字節(jié)長(zhǎng)度而小于10個(gè)字節(jié)時(shí)锌仅，可以在該記錄處直接更新瞒渠，而不是在存儲(chǔ)空間中重新 分配一個(gè)新的記錄空間良蒸，導(dǎo)致原有的記錄空間成為所謂的碎片。(這里你感受到設(shè)計(jì)Compact行格式的大叔既想節(jié)省存儲(chǔ)空間伍玖，又不想更新CHAR(M)類(lèi)型的列產(chǎn)生碎片時(shí)的糾結(jié)心情了吧嫩痰。)

行溢出數(shù)據(jù)

VARCHAR(M)最多能存儲(chǔ)的數(shù)據(jù)

我們知道對(duì)于VARCHAR(M)類(lèi)型的列最多可以占用65535個(gè)字節(jié)(長(zhǎng)度用兩個(gè)字節(jié)表示)。其中的M代表該類(lèi)型最多存儲(chǔ)的字符數(shù)量窍箍，如果我們使用ascii字符集的話(huà)串纺，一個(gè)字符就代表一個(gè)字節(jié)，我們看看VARCHAR(65535)是否可用：

mysql> CREATE TABLE varchar_size_demo(
    ->     c VARCHAR(65535)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql>

從報(bào)錯(cuò)信息里可以看出椰棘，MySQL對(duì)一條記錄占用的最大存儲(chǔ)空間是有限制的纺棺，除了BLOB或者TEXT類(lèi)型的列之外，其他所有的列（不包括隱藏列和記錄頭信息）占用的字節(jié)長(zhǎng)度加起來(lái)不能超過(guò)65535個(gè)字節(jié)邪狞。所以MySQL服務(wù)器建議我們把存儲(chǔ)類(lèi)型改為TEXT或者BLOB的類(lèi)型祷蝌。這個(gè)65535個(gè)字節(jié)除了列本身的數(shù)據(jù)之外，還包括一些其他的數(shù)據(jù)（storage overhead）帆卓，比如說(shuō)我們?yōu)榱舜鎯?chǔ)一個(gè)VARCHAR(M)類(lèi)型的列巨朦，其實(shí)需要占用3部分存儲(chǔ)空間：

• 真實(shí)數(shù)據(jù)
• 真實(shí)數(shù)據(jù)占用字節(jié)的長(zhǎng)度
• NULL值標(biāo)識(shí)，如果該列有NOT NULL屬性則可以沒(méi)有這部分存儲(chǔ)空間

如果該VARCHAR類(lèi)型的列沒(méi)有NOT NULL屬性剑令，那最多只能存儲(chǔ)65532個(gè)字節(jié)的數(shù)據(jù)糊啡，因?yàn)檎鎸?shí)數(shù)據(jù)的長(zhǎng)度可能占用2個(gè)字節(jié)，NULL值標(biāo)識(shí)需要占用1個(gè)字節(jié)：

mysql> CREATE TABLE varchar_size_demo(
    ->      c VARCHAR(65532)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.02 sec)

如果VARCHAR類(lèi)型的列有NOT NULL屬性吁津，那最多只能存儲(chǔ)65533個(gè)字節(jié)的數(shù)據(jù)棚蓄，因?yàn)檎鎸?shí)數(shù)據(jù)的長(zhǎng)度可能占用2個(gè)字節(jié)，不需要NULL值標(biāo)識(shí)：

mysql> DROP TABLE varchar_size_demo;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE varchar_size_demo(
    ->      c VARCHAR(65533) NOT NULL
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.02 sec)

如果VARCHAR(M)類(lèi)型的列使用的不是ascii字符集碍脏，那會(huì)怎么樣呢梭依？來(lái)看一下：

mysql> DROP TABLE varchar_size_demo;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE varchar_size_demo(
    ->       c VARCHAR(65532)
    -> ) CHARSET=gbk ROW_FORMAT=Compact;
ERROR 1074 (42000): Column length too big for column 'c' (max = 32767); use BLOB or TEXT instead

mysql> CREATE TABLE varchar_size_demo(
    ->       c VARCHAR(65532)
    -> ) CHARSET=utf8 ROW_FORMAT=Compact;
ERROR 1074 (42000): Column length too big for column 'c' (max = 21845); use BLOB or TEXT instead

從執(zhí)行結(jié)果中可以看出，如果VARCHAR(M)類(lèi)型的列使用的不是ascii字符集典尾，那M的最大取值取決于該字符集表示一個(gè)字符最多需要的字節(jié)數(shù)睛挚。在列的值允許為NULL的情況下，gbk字符集表示一個(gè)字符最多需要2個(gè)字節(jié)急黎，那在該字符集下，M的最大取值就是32766（也就是：65532/2）侧到，也就是說(shuō)最多能存儲(chǔ)32766個(gè)字符勃教；utf8字符集表示一個(gè)字符最多需要3個(gè)字節(jié)，那在該字符集下匠抗，M的最大取值就是21844故源，就是說(shuō)最多能存儲(chǔ)21844（也就是：65532/3）個(gè)字符。

上述所言在列的值允許為NULL的情況下汞贸，gbk字符集下M的最大取值就是32766绳军，utf8字符集下M的最大取值就是21844印机，這都是在表中只有一個(gè)字段的情況下說(shuō)的，一定要記住一個(gè)行中的所有列（不包括隱藏列和記錄頭信息）占用的字節(jié)長(zhǎng)度加起來(lái)不能超過(guò)65535個(gè)字節(jié)门驾！

記錄中的數(shù)據(jù)太多產(chǎn)生的溢出

我們以ascii字符集下的varchar_size_demo表為例射赛，插入一條記錄：

mysql> CREATE TABLE varchar_size_demo(
    ->       c VARCHAR(65532)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO varchar_size_demo(c) VALUES(REPEAT('a', 65532));
Query OK, 1 row affected (0.00 sec)

其中的REPEAT('a', 65532)是一個(gè)函數(shù)調(diào)用，它表示生成一個(gè)把字符'a'重復(fù)65532次的字符串奶是。前邊說(shuō)過(guò)楣责，MySQL中磁盤(pán)和內(nèi)存交互的基本單位是頁(yè)，也就是說(shuō)MySQL是以頁(yè)為基本單位來(lái)管理存儲(chǔ)空間的聂沙，我們的記錄都會(huì)被分配到某個(gè)頁(yè)中存儲(chǔ)秆麸。而一個(gè)頁(yè)的大小一般是16KB，也就是16384字節(jié)及汉，而一個(gè)VARCHAR(M)類(lèi)型的列就最多可以存儲(chǔ)65532個(gè)字節(jié)沮趣，這樣就可能造成一個(gè)頁(yè)存放不了一條記錄的尷尬情況。

在Compact和Reduntant行格式中坷随，對(duì)于占用存儲(chǔ)空間非常大的列房铭，在記錄的真實(shí)數(shù)據(jù)處只會(huì)存儲(chǔ)該列的一部分?jǐn)?shù)據(jù)，把剩余的數(shù)據(jù)分散存儲(chǔ)在幾個(gè)其他的頁(yè)中甸箱，然后記錄的真實(shí)數(shù)據(jù)處用20個(gè)字節(jié)存儲(chǔ)指向這些頁(yè)的地址（當(dāng)然這20個(gè)字節(jié)中還包括這些分散在其他頁(yè)面中的數(shù)據(jù)的占用的字節(jié)數(shù)）育叁，從而可以找到剩余數(shù)據(jù)所在的頁(yè)，如圖所示：

image.png

從圖中可以看出來(lái)芍殖，對(duì)于Compact和Reduntant行格式來(lái)說(shuō)豪嗽，如果某一列中的數(shù)據(jù)非常多的話(huà)，在本記錄的真實(shí)數(shù)據(jù)處只會(huì)存儲(chǔ)該列的前768個(gè)字節(jié)的數(shù)據(jù)和一個(gè)指向其他頁(yè)的地址豌骏，然后把剩下的數(shù)據(jù)存放 到其他頁(yè)中龟梦，這個(gè)過(guò)程也叫做行溢出，存儲(chǔ)超出768字節(jié)的那些頁(yè)面也被稱(chēng)為溢出頁(yè)窃躲。畫(huà)一個(gè)簡(jiǎn)圖就是這樣:

image.png

最后需要注意的是计贰，不只是 VARCHAR(M) 類(lèi)型的列，其他的 TEXT蒂窒、BLOB 類(lèi)型的列在存儲(chǔ)數(shù)據(jù)非常多的時(shí)候也會(huì)發(fā)生行溢出躁倒。

行溢出的臨界點(diǎn)

那發(fā)生行溢出的臨界點(diǎn)是什么呢?也就是說(shuō)在列存儲(chǔ)多少字節(jié)的數(shù)據(jù)時(shí)就會(huì)發(fā)生行溢出?
MySQL中規(guī)定一個(gè)頁(yè)中至少存放兩行記錄，至于為什么這么規(guī)定我們之后再說(shuō)洒琢，現(xiàn)在看一下這個(gè)規(guī)定造成的影響秧秉。以上邊的varchar_size_demo表為例，它只有一個(gè)列c衰抑，我們往這個(gè)表中插入兩條記錄象迎，每 條記錄最少插入多少字節(jié)的數(shù)據(jù)才會(huì)行溢出的現(xiàn)象呢?這得分析一下頁(yè)中的空間都是如何利用的。

• 每個(gè)頁(yè)除了存放我們的記錄以外呛踊，也需要存儲(chǔ)一些額外的信息砾淌，亂七八糟的額外信息加起來(lái)需要136個(gè)字節(jié)的空間(現(xiàn)在只要知道這個(gè)數(shù)字就好了)啦撮，其他的空間都可以被用來(lái)存儲(chǔ)記錄。
• 每個(gè)記錄需要的額外信息是27字節(jié)汪厨。

這27個(gè)字節(jié)包括下邊這些部分:
- 2個(gè)字節(jié)用于存儲(chǔ)真實(shí)數(shù)據(jù)的長(zhǎng)度（總長(zhǎng)度不大于65535原因）
- 1個(gè)字節(jié)用于存儲(chǔ)列是否是NULL值
- 5個(gè)字節(jié)大小的頭信息
- 6個(gè)字節(jié)的row_id列
- 6個(gè)字節(jié)的transaction_id列
- 7個(gè)字節(jié)的roll_pointer列

假設(shè)一個(gè)列中存儲(chǔ)的數(shù)據(jù)字節(jié)數(shù)為n赃春，那么發(fā)生行溢出現(xiàn)象時(shí)需要滿(mǎn)足這個(gè)式子:

136 + 2×(27 + n) > 16384

求解這個(gè)式子得出的解是:n > 8098。也就是說(shuō)如果一個(gè)列中存儲(chǔ)的數(shù)據(jù)不大于8098個(gè)字節(jié)骄崩，那就不會(huì)發(fā)生行溢出聘鳞，否則就會(huì)發(fā)生行溢出。不過(guò)這個(gè)8098個(gè)字節(jié)的結(jié)論只是針對(duì)只有一個(gè)列 的varchar_size_demo表來(lái)說(shuō)的要拂，如果表中有多個(gè)列抠璃，那上邊的式子和結(jié)論都需要改一改了，所以重點(diǎn)就是:你不用關(guān)注這個(gè)臨界點(diǎn)是什么脱惰，只要知道如果我們想一個(gè)行中存儲(chǔ)了很大的數(shù)據(jù)時(shí)搏嗡，可能發(fā) 生行溢出的現(xiàn)象。

Dynamic和 Compressed行格式

下邊要介紹另外兩個(gè)行格式拉一，Dynamic和Compressed行格式采盒，我現(xiàn)在使用的MySQL版本是5.7，它的默認(rèn)行格式就是Dynamic蔚润，這倆行格式和Compact行格式挺像磅氨，只不過(guò)在處理行溢出數(shù)據(jù)時(shí)有點(diǎn)兒分歧，它們不會(huì)在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)字段真實(shí)數(shù)據(jù)的前768個(gè)字節(jié)嫡纠，而是把所有的字節(jié)都存儲(chǔ)到其他頁(yè)面中烦租，只在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)其他頁(yè)面的地址，就像這樣:

image.png

Compressed行格式和Dynamic不同的一點(diǎn)是除盏，Compressed行格式會(huì)采用壓縮算法對(duì)頁(yè)面進(jìn)行壓縮叉橱，以節(jié)省空間。

小結(jié)

1. 頁(yè)是MySQL中磁盤(pán)和內(nèi)存交互的基本單位者蠕，也是MySQL是管理存儲(chǔ)空間的基本單位窃祝。
2. 指定和修改行格式的語(yǔ)法如下:

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名稱(chēng)
ALTER TABLE 表名 ROW_FORMAT=行格式名稱(chēng)

3. InnoDB目前定義了4種行格式

• COMPACT行格式

  
  
  image.png

• Dynamic和Compressed行格式
  這兩種行格式類(lèi)似于COMPACT行格式，只不過(guò)在處理行溢出數(shù)據(jù)時(shí)有點(diǎn)兒分歧踱侣，它們不會(huì)在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)字符串的前768個(gè)字節(jié)粪小，而是把所有的字節(jié)都存儲(chǔ)到其他頁(yè)面中，只在記錄的真 實(shí)數(shù)據(jù)處存儲(chǔ)其他頁(yè)面的地址抡句。

• 一個(gè)頁(yè)一般是16KB探膊，當(dāng)記錄中的數(shù)據(jù)太多，當(dāng)前頁(yè)放不下的時(shí)候玉转，會(huì)把多余的數(shù)據(jù)存儲(chǔ)到其他頁(yè)中，這種現(xiàn)象稱(chēng)為行溢出殴蹄。