建站
咨詢
售后
建站咨詢
新聞中心
InnoDB 索引頁的大小默認為 16K，然而，varchar、text、blob 類型的單個字段內(nèi)容長度就有可能超過 16K，這種情況下，整個索引頁都存不下一個字段的內(nèi)容了。
十多年的饒河網(wǎng)站建設(shè)經(jīng)驗，針對設(shè)計、前端、開發(fā)、售后、文案、推廣等六對一服務(wù)，響應(yīng)快，48小時及時工作處理。成都全網(wǎng)營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動調(diào)整饒河建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計，從而大程度地提升瀏覽體驗。創(chuàng)新互聯(lián)從事“饒河網(wǎng)站設(shè)計”,“饒河網(wǎng)站推廣”以來，每個客戶項目都認真落實執(zhí)行。
解決這個問題的辦法，是找到那些內(nèi)容比較長的字段作為??溢出字段??，把它們的內(nèi)容存放到溢出頁中，減少留在索引頁記錄中的內(nèi)容。
接下來，我們來聊聊 InnoDB 選擇溢出字段的邏輯。
本文內(nèi)容基于 MySQL 8.0.29 源碼。
正文
進入正題之前，大家可以思考一個問題：一個表中每條記錄的溢出字段都是一樣的嗎？
1、建表時的限制
單從字段數(shù)量看，MySQL 的 server 層限制一個表最多只能創(chuàng)建 1024 個字段。
InnoDB 則限制最多只能創(chuàng)建 1023 個字段，但是，如果我們創(chuàng)建表時，真要創(chuàng)建 1023 個字段，會很榮幸的收到這個錯誤：1117 - Too many columns。
因為 InnoDB 會往表中增加 2 ~ 3 個隱藏字段：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。
只有表中沒有主鍵，并且在建表時也沒有創(chuàng)建所有字段都不允許為 NULL? 的唯一索引時，才會增加 DB_ROW_ID 字段。
創(chuàng)建表時，能定義 1023 - 3 = 1020 個字段嗎？
依然不能，因為崩潰恢復(fù)過程中，解析 Redo 日志時，REDUNDANT? 記錄還會往表的內(nèi)存對象（dict_table_t）中加入 3 個字段。
從以上介紹可知，InnoDB 需要保留 6 個字段自用，所以，我們建表時，最多能創(chuàng)建的字段數(shù)量是：1023 - 3 * 2 = 1017。
表中實際能創(chuàng)建多少個字段，除了受限于 server 層和存儲引擎的字段數(shù)量限制，還會受到字段長度的限制。
創(chuàng)建表時，InnoDB 會問自己一個問題：
如果我放過這條 DDL 語句，讓它建表成功，以后對這個表進行插入、更新操作時，有沒有可能因為記錄超長導(dǎo)致操作失?。?/p>
要回答這個問題，總不能憑空想象，隨心而動吧？
所以，得有一個規(guī)則，要按規(guī)則辦事，規(guī)則是這樣的：
假定有資格被選擇成為溢出字段的那些字段，都已經(jīng)被當(dāng)作溢出字段了，它們的字段內(nèi)容都部分或全部存放到溢出頁中了。
溢出字段內(nèi)容是部分還是全部存放到溢出頁，這取決于記錄的格式。
REDUNDANT、COMPACT 記錄只會把溢出字段的部分內(nèi)容存放到溢出頁。
DYNAMIC、COMPRESSED 記錄會把溢出字段的全部內(nèi)容存放到溢出頁。
在這個規(guī)則之下，再來計算留在索引頁中的記錄內(nèi)容長度，看看是不是還會超長？
如果還會超長，InnoDB 是不會放過這條 DDL 語句的，這時，建表就會失敗，并且報如下錯誤：
1118 - Row size too large (> 8126).
Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.
為啥判斷超長的條件是大于 8126 字節(jié)呢？先別急，后面會有介紹。
如果不會超長，自然就會建表成功了。
2、索引頁長什么樣？
InnoDB 的索引頁，不管是 B+ 樹的非葉子結(jié)點，還是葉子結(jié)點，初始化完成之后，未插入記錄之前，都包含以下幾個部分：
- 38 字節(jié)的 File Header
- 56 字節(jié)的索引頁頭信息
- 13 字節(jié)的 Infimum 記錄
- 13 字節(jié)的 Supremum 記錄
- 2 字節(jié)的 Supremum Slot
- 2 字節(jié)的 Infimum Slot
- 8 字節(jié)的 File Trailer
總共占用 132 字節(jié)，如下圖所示：
3、怎么判斷行超長了？
通過上一小節(jié)，我們知道一個索引頁初始化完成之后，會占用 132 字節(jié)的空間。
索引頁默認大小為 16K，初始化之后，索引頁中剩余空間為：16 * 1024 - 132 = 16252 字節(jié)。
InnoDB 規(guī)定?：一個索引頁中最少要存放 2? 條記錄。所以，索引頁中一條記錄的最大長度就是：16252 / 2 = 8126 字節(jié)。
插入或者更新記錄時，如果插入記錄的長度，或者更新之后記錄的長度大于 8126 字節(jié)，就會選擇記錄中的部分字段作為溢出字段。
一條記錄的長度為下幾個部分的長度之和：
- 字段 NULL 標(biāo)記區(qū)域?，標(biāo)記每個字段內(nèi)容是否為 NULL，如果表中所有字段都定義為NOT NULL?，記錄中沒有此區(qū)域。
- 字段內(nèi)容長度區(qū)域?，存儲每個變長?字段的內(nèi)容長度，如果表中所有字段都不是?變長字段，記錄中沒有此區(qū)域。
- 記錄的頭信息，REDUNDANT 格式：6 字節(jié)；COMPACT、DYNAMIC、COMPRESSED 格式：5 字節(jié)。
- 用戶字段內(nèi)容。
- DB_ROW_ID?，6 字節(jié)，創(chuàng)建表時，表中既沒有主鍵，也沒有創(chuàng)建所有字段都定義為 NOT NULL 的唯一索引時，InnoDB 才會添加這個列，作為表的主鍵。
- DB_TRX_ID，8 字節(jié)，最后修改記錄的事務(wù) ID。
- DB_ROLL_PTR，7 字節(jié)，指向上一個事務(wù)產(chǎn)生的 undo 日志。
4、選擇溢出字段的邏輯
選擇溢出字段環(huán)節(jié)可能會進行一輪或多輪循環(huán)，每輪循環(huán)從表中選擇一個?字段作為溢出字段，直到留在索引頁中的記錄長度小于等于 8126 字節(jié)，選擇溢出字段環(huán)節(jié)也就結(jié)束了。
選擇溢出字段時，有一些字段是會被排除在外的，命中?以下規(guī)則的字段都不會被選為溢出字段：
- 主鍵字段。
- 固定長度字段（char、binary 字段除外）。
- 內(nèi)容為 NULL 的字段。
- REDUNDANT、COMPACT 記錄，字段內(nèi)容長度<= 788 字節(jié)。
- DYNAMIC、COMPRESSED 記錄，字段內(nèi)容長度<= 40 字節(jié)?，且字段類型是 BLOB、GEOMETRY、VAR_POINT。
- DYNAMIC、COMPRESSED 記錄，字段內(nèi)容長度<= 255 字節(jié)?，且字段類型不是 BLOB、GEOMETRY、VAR_POINT。
沒有命中以上規(guī)則的字段，都有資格被選為溢出字段。
每輪循環(huán)都會遍歷表中的所有字段，并根據(jù)以上規(guī)則，從有資格被選為溢出字段的那些字段中，找到??內(nèi)容最長??的字段，就是溢出字段了。
5、頁地址
字段被選為溢出字段之后，該字段的部分或全部內(nèi)容會存放到溢出頁，然后，索引頁記錄中，該字段的末尾?，會有一個 20 字節(jié)?的區(qū)域，保存著溢出頁地址。
20 字節(jié)的溢出頁地址由以下 4 個部分構(gòu)成：
- 表空間 ID，4 字節(jié)，溢出頁所在表空間 ID。
- 頁號，4 字節(jié)，第 1 個溢出頁的頁號。一個溢出頁存不下字段的溢出內(nèi)容時，會有多個溢出頁，組成溢出頁鏈表。
- 字段內(nèi)容 Offset?，4 字節(jié)，第 1 個溢出頁中，字段內(nèi)容在頁中的 Offset。根據(jù)是否啟用了壓縮頁，字段內(nèi)容在溢出頁中的 Offset 會不一樣，所以需要記下來。
- 溢出頁內(nèi)容長度?，當(dāng)前字段存放到溢出頁中的內(nèi)容長度，8 字節(jié)，實際只使用了最后4 字節(jié)來存儲溢出頁的內(nèi)容長度之和，如下圖所示：
溢出字段留在索引頁記錄中的內(nèi)容根據(jù)記錄格式的不同而不同：
REDUNDANT、COMPACT 記錄?，溢出字段在索引頁記錄中的長度為 788 字節(jié)，由以下兩部分組成：
- 768 字節(jié)的字段內(nèi)容。
- 20 字節(jié)的溢出頁地址。
溢出字段中 768 字節(jié)之后的內(nèi)容，會存放到溢出頁中。
DYNAMIC、COMPRESSED 記錄，溢出字段的全部內(nèi)容都會存放到溢出頁中，索引頁記錄中只保存 20 字節(jié)的溢出頁地址。
6、回答文章開頭的問題
經(jīng)過前面的介紹，相信大家對于本文開頭的那個問題已經(jīng)有了答案，回到問題：
問：一個表中每條記錄的溢出字段都是一樣的嗎？
答?：每條記錄的溢出字段，可能一樣，也可能不一樣，記錄中哪些字段會成為溢出字段，取決于每條記錄中，所有有資格被選為溢出字段的內(nèi)容長度。
7、總結(jié)
一條記錄中，所有字段內(nèi)容長度之和超過 8126 字節(jié)時，就會有部分字段被選擇成為溢出字段。
選擇溢出字段可能會進行多輪循環(huán)?，每輪循環(huán)都會從有資格被選為溢出字段的那些字段中，選擇內(nèi)容最長?的字段作為溢出字段，直到留在索引頁中的記錄長度小于等于 8126 字節(jié)。
REDUNDANT、COMPACT 記錄，溢出字段內(nèi)容的前 768 字節(jié)存放在索引頁記錄中，剩余內(nèi)容存放到溢出頁。
DYNAMIC、COMPRESSED 記錄，溢出字段的全部內(nèi)容都存放到溢出頁。
本文轉(zhuǎn)載自微信公眾號「一樹一溪」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系一樹一溪公眾號。
當(dāng)前標(biāo)題：InnoDB 行超長時怎么選擇溢出字段？
本文來源：http://m.fisionsoft.com.cn/article/dhepgji.html
