Mysql中InnoDB的行格式詳細介紹

這篇文章主要講解了“Mysql中InnoDB的行格式詳細介紹”，文中的講解內(nèi)容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“Mysql中InnoDB的行格式詳細介紹”吧！

一、InnoDb中的頁

我們知道，要處理數(shù)據(jù)，必須先把數(shù)據(jù)放到內(nèi)存中來，那么Mysql讀寫記錄時，是怎么讀寫的勒？Mysql是將數(shù)據(jù)劃分為若干個頁，以頁作為磁盤和內(nèi)存之間交互的基本單位，InnoDB中頁的大小一般為 16 KB。也就是在一般情況下，一次最少從磁盤中讀取16KB的內(nèi)容到內(nèi)存中，一次最少把內(nèi)存中的16KB內(nèi)容刷新到磁盤中。

二、InnoDB有哪些行格式

所謂行格式就是表的一條記錄在磁盤里存儲的二進制格式。迄今為止，InnoDB有四種行格式，分別是Compact、Redundant、Dynamic和Compressed行格式。下面分別介紹下這幾種行格式。

（一）Compact

示意圖：

一條記錄存儲分為記錄的額外信息和記錄的真實數(shù)據(jù)兩部分：

1.記錄的額外信息

記錄的額外信息又包括變長字段長度列表、NULL值列表、記錄頭信息：

（1）變長字段長度列表

所謂變長字段，指的是如VARCHAR(M)、VARBINARY(M)、各種TEXT類型，各種BLOB類型的字段，變長字段列表主要是存儲的這些字段的真實數(shù)據(jù)占用的字節(jié)長度，該列表的順序是按表字段逆序。在長度列表中每個字段用1-2個字節(jié)來其字節(jié)長度，具體是1還是2個字節(jié)是通過該記錄該字段占用的最大字節(jié)長度和真實數(shù)據(jù)長度來計算得到的。具體規(guī)則是：先看字段最大字節(jié)長度，小于255直接用1個字節(jié)表示，那如果大于255的勒？比如utf8編碼格式下的varchar(100)，最大字節(jié)長度是3*100=300，超過了一個字節(jié)能表示的最大的數(shù)。這時應該看真實數(shù)據(jù)字符占用字節(jié)數(shù)，如果真實數(shù)據(jù)字符占用數(shù)據(jù)字節(jié)數(shù)小于127，用1個字節(jié)，大于用2個字節(jié)。為什么是用127做劃分勒，因為一個字節(jié)有8位，首位被用來標識需不需要一起讀取下個字節(jié)作為字段的字節(jié)長度（即這個字段用的是1個字節(jié)表示長度還是2個字節(jié)）。0需要，1表示不需要。如果碰到該記錄數(shù)據(jù)字節(jié)太長，產(chǎn)生行溢出時（后面會細講），這種情況的話，變長字段長度列表中表示該字段的長度還是2個字節(jié)，只表示該記錄在本頁的數(shù)據(jù)長度。因為2個字節(jié)所能表示的字節(jié)長度有2的15次方，遠遠大于InnoDb讀寫一頁（16KB）的長度了，所以就算該記錄只有一個字段，本頁數(shù)據(jù)全存該字段的數(shù)據(jù)，那2個字節(jié)來表示本頁所占長度也是完全放得下的。

注：對于 CHAR(M) 類型的列來說，當列采用的是定長字符集時，該列占用的字節(jié)數(shù)不會被加到變長字段長度列表，而如果采用變長字符集時，該列占用的字節(jié)數(shù)也會被加到變長字段長度列表。另外定長字符集下的CHAR類型字段，如果涉及更新或刪除的話，不會產(chǎn)生硬盤碎片，效率比varchar高。

（2）NULL值列表

null值列表只有在該記錄所在表的元數(shù)據(jù)規(guī)定有字段可以存在null值才會有null值列表，null值列表是基于位向量來維護字段是否為null的，即用二進制位的0和1表示字段是否為null，該列表也是逆序的。另外InnoDB還規(guī)定NULL值列表必須用整數(shù)個字節(jié)的位表示，如果使用的二進制位個數(shù)不是整數(shù)個字節(jié)，則在字節(jié)的高位補0。

（3）記錄頭信息

記錄頭信息由固定5個字節(jié)組成包括：

上圖中，有些概念可能不清楚，后面如果再開文章的話再學習。

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

記錄的真實數(shù)據(jù)除了用戶自己定義的列的數(shù)據(jù)以外，InnoDB還會為每個記錄默認的添加一些列（也稱為隱藏列），具體的列如下：

其中row_id不一定是必須的，只有在表中不存在主鍵的時候InnoDB才會自動添加這列。

（二）Redundant

示意圖：

這個行格式名稱是也就是Redundant，表示它是已經(jīng)是過時了的了，現(xiàn)在一般不用，但這里還是介紹一下，對比與Compact的區(qū)別。

1.字段長度偏移列表

與變長字段長度列表有兩處不同：

• 沒有了變長兩個字，意味著Redundant行格式會把該條記錄中所有列（包括隱藏列）的長度信息都按照逆序存儲到字段長度偏移列表。

• 多了個偏移兩個字，這意味著計算列值長度的方式不像Compact行格式那么直觀，它是采用兩個相鄰數(shù)值的差值來計算各個列值的長度。比如每個字段長度按逆序列表的10進制表示是6、12、9，那么偏移之后就是6、18（18-6=12）、27（27-18=9）。

從上面看出，字段長度偏移列表實質(zhì)上是存儲每個列中的值占用的空間在記錄的真實數(shù)據(jù)處結(jié)束的位置，這種表示方法相對來說更簡單直觀。

注：對于到底用1個字節(jié)或2個字節(jié)，規(guī)則類似Compact，但判斷的是該記錄所有字段真實數(shù)據(jù)長度，如果真實數(shù)據(jù)小于127字節(jié)，則每個列對應的偏移量占用1個字節(jié)，大于127，用兩個字節(jié)來劃分，當然真實數(shù)據(jù)可能超過了2個字節(jié)所能表示的最大字節(jié)數(shù)32767，這時依舊是兩個字節(jié)，原因同Compact，2個字節(jié)足夠表示該頁的最大偏移（因為1頁就16K，也就是16384個字節(jié)），剩下的為溢出列數(shù)據(jù)，交由其他頁存放，本頁只存其他頁的指向地址。

2.記錄頭信息

與Compact的記錄頭信息相比:

• Redundant行格式多了n_field和1byte_offs_flag這兩個屬性。

Redundant跟Compact不一樣的是，把是一個或兩個字節(jié)表示長度放在了頭信息里面，具體規(guī)則類似Compact，但判斷的是該記錄所有字段真實數(shù)據(jù)長度，如果真實數(shù)據(jù)小于127字節(jié)，則每個列對應的偏移量占用1個字節(jié)，大于127（這里可能會疑問為什么是127而不是255（1個字節(jié)表示的最大的數(shù)），因為與Compact格式不同，Redundant對null值信息沒有集中存儲，而是將字段長度偏移列表首個字節(jié)利用起來，標識了該字段為不為null），用兩個字節(jié)來劃分，當然真實數(shù)據(jù)可能超過了2個字節(jié)所能表示的最大字節(jié)數(shù)32767，這時依舊是兩個字節(jié)，原因同Compact，2個字節(jié)足夠表示該頁的最大偏移（因為1頁就16K，也就是16384個字節(jié)），剩下的為溢出列數(shù)據(jù)，交由其他頁存放，本頁只存其他頁的指向地址。

• Redundant行格式?jīng)]有record_type這個屬性。

（三）Dynamic

與Compact的區(qū)別是：

不會在記錄的真實數(shù)據(jù)處存儲字段真實數(shù)據(jù)的前768個字節(jié)，而是把所有的字節(jié)都存儲到其他頁面中，只在記錄的真實數(shù)據(jù)處存儲其他頁面的地址。

（四）Compressed

與Dynamic不同的一點是：

Compressed行格式會采用壓縮算法對頁面進行壓縮，以節(jié)省空間。

三、如何查看行格式

在我使用的版本Mysql5.7.26版本中，行格式默認為Dynamic。如何進行查看某個表的行格式命令是：

show table STATUS like '表名'

三、指定行格式的語法

創(chuàng)建或修改表的語句:

• CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名稱

• ALTER TABLE 表名 ROW_FORMAT=行格式名稱

四、針對行溢出數(shù)據(jù)的處理

（一）什么是行溢出

• DDL定義時報錯字段溢出

MySQL對一條記錄占用的最大存儲空間是有限制的，除了BLOB或者TEXT類型的列之外，其他所有的列（不包括隱藏列和記錄頭信息）占用的字節(jié)長度加起來不能超過65535個字節(jié)。如果超過會報ERROR，比如創(chuàng)建一個只有一個字段編碼格式為ascii（1個字節(jié)為1個字符）的表：

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

注：這里的65535包含除了列本身的數(shù)據(jù)之外，還包括一些其他的數(shù)據(jù)（storage overhead）如NULL值標識（可以為null的字段需要這個標識）、真實數(shù)據(jù)占用字節(jié)的長度。這樣可以計算一下，如果只有一個字段的表，可以為null，那么該字段真實數(shù)據(jù)可用字節(jié)就是65535-2(真實數(shù)據(jù)占用字節(jié)的長度)-1(null值標識)=65532，以上語句可以改為：

CREATE TABLE varchar_size_demo(
     c VARCHAR(65532)
) CHARSET=ascii ROW_FORMAT=Redundant
> OK
> 時間: 0.124s

這樣就恰恰夠裝。

• 運行時的行溢出（不報錯）

在Compact和Reduntant行格式中，對于占用存儲空間非常大的列，在記錄的真實數(shù)據(jù)處只會存儲該列的一部分數(shù)據(jù)，把剩余的數(shù)據(jù)分散存儲在幾個其他的頁中，然后記錄的真實數(shù)據(jù)處用20個字節(jié)存儲指向這些頁的地址（當然這20個字節(jié)中還包括這些分散在其他頁面中的數(shù)據(jù)的占用的字節(jié)數(shù)），從而可以找到剩余數(shù)據(jù)所在的頁。如圖：

那么這里怎么計算，產(chǎn)生行溢出的數(shù)據(jù)長度的臨界點勒？這里與幾個限制有關(guān)：

• MySQL中規(guī)定一個頁中至少存放兩行記錄

• 一頁只有16K

即只要保證2條數(shù)據(jù)，加起來數(shù)據(jù)大小不超過16K減去頁中其他不用于存儲記錄的大?。ü潭?32個字節(jié)），就不會產(chǎn)生行溢出，但是一條數(shù)據(jù)不止有存儲真實數(shù)據(jù)還有其他。以Compact為例，假設只有1個字段，且可以為Null,則每個記錄需要的額外信息是27字節(jié)，包括：

• 2個字節(jié)用于存儲真實數(shù)據(jù)的長度

• 1個字節(jié)用于存儲列是否是NULL值(不超過255個字段可以為null,所以直接用1個字節(jié))

• 5個字節(jié)大小的頭信息

• 6個字節(jié)的row_id列

• 6個字節(jié)的transaction_id列

• 7個字節(jié)的roll_pointer列

假設一個列中存儲的數(shù)據(jù)字節(jié)數(shù)為n，只要滿足：

132 + 2×(27 + n) < 16384

則該記錄不會造成行溢出。當然如果表中有多個字段，上面公式中的27可能會增加（因為“真實數(shù)據(jù)的長度”和“列是否是NULL值”占用字節(jié)可能會增加）。

感謝各位的閱讀，以上就是“Mysql中InnoDB的行格式詳細介紹”的內(nèi)容了，經(jīng)過本文的學習后，相信大家對Mysql中InnoDB的行格式詳細介紹這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！