MySQL資料庫存儲varchar時多大長度會出現行遷移?

最近客戶現場有人問過mysql資料庫的一些參數配置的問題, 這邊資料庫需要將strict 嚴格模式關掉, 目的是為了保證資料庫在插入欄位時不會出現8126的長度限制錯誤問題. 但是一直很困惑, mysql的默認安裝的page 是 16kb 大小, 理論上一個頁不可能超過 16000 個bytes 長度, 很多文檔資料裡面都有 說法是 text和blob的類型都會出現行遷移, 但是對varchar 都沒有明確的說法, 今天趁著周六下了一個電子書, 將姜承堯老師的<MySQL技術內幕InnoDB存儲引擎第2版> 翻了 100多頁 也沒翻到具體的資料, 沒辦法只能夠進行嘗試.

先說寫一下結論. 

varchar 在 8099 的長度時會出現行溢出遷移的現象 將字符存儲到第六個數據頁

varchar 8098 時 還是在當前數據也存儲的, 而且應該是在第五個數據頁存儲.

姜老師的書裡面 mysql5.6 初始化時 是 6個數據頁,大小是96KB, 但是mysql 8.0.21 的版本初始一個ibd文件時 是初始化7個數據頁. 大小是 112KB

1. 本來想使用 姜老師寫的python腳本. 但是發現可能他的腳本僅支持mysql5.6 的版本. mysql8的版本是不支持. 無法直接使用. 

會報錯. 

git clone https://github.com/happieme/py_innodb_page_info
cd py_innodb_page_info/
chmod 777 * -R
./py_innodb_page_info.py /var/lib/mysql/zhaobsh/test001.ibd

報錯信息為:

[root@CentOS76 py_innodb_page_info]# ./py_innodb_page_info.py /var/lib/mysql/zhaobsh/test001.ibd
Total number of page: 7:
Insert Buffer Bitmap: 1
File Segment inode: 1
B-tree Node: 1
Traceback (most recent call last):
File "./py_innodb_page_info.py", line 12, in <module>
mylib.get_innodb_page_type(myargv)
File "/root/mysql/py_innodb_page_info/mylib.py", line 72, in get_innodb_page_type
print "%s: %s"%(innodb_page_type[type],ret[type])
KeyError: '45bd'
[root@CentOS76 py_innodb_page_info]#

沒辦法僅能夠退而求其次. 

先通過一個網頁找到了一些關於mysql 數據頁page含義的內容

來源:

http://www.blog.chinaunix.net/uid-29170515-id-5747112.html

#數據頁具體內容分析

#一行代表16位元組
[root@master ~]# head -n 3094 ilvtu_block.txt|tail -n 22
#u前38位元組為文件頭
#前4個節a1 a3 c2 67為checksum
#第5-8個字節00 00 00 03，指定頁的偏移量，表明為第4個頁
#第9-12個字節ff ff ff ff，當前頁的上一個頁，只有為數據頁時，才有具體意義值
#第13-16個節ff ff ff ff，當前頁的下一個頁，同上
#第17-24個字節代表該頁最近被修改的日誌序列位置LSN,00 00 00 00 85 69 9e a4
#第25-26位元組，表示頁的類型，45 bf表示為數據頁
#第27-34位元組，共8位元組，00 00 00 00 00 00 00 00，只有屬於系統表空間時才有意義
#第35-38位元組，共4位元組，00 00 00 69,代表屬於哪個表空間，SPACE_ID



#第39-40位元組，共2位元組，代表存儲頁目錄中的槽位(slot)數,00 02，代表2個slot
#第41-42位元組，00 e9，代表空閒空間偏移量0000c000+00c9=0000c0e9
#第43-44位元組，00 05，表示行數，因為表的格式為COMPAT，初始後為00 02，因些相減為3條記錄
#第45-46位元組，00 00，共2位元組，存放可重用空間的首指針
#第47-48位元組，00 00，共2位元組，
#第49-50位元組，00 cb, 0000c000+00cb=0000c0cb，代表最後一條記錄插入的位置偏移量
#第51-52字共，共2位元組，代表最後插入的方向00 02，代表向右增長
#第53-54位元組，共2位元組，代表連續插入的記錄數，00 02，代表連續插入2條
#第55-56位元組，共2位元組，代表頁中記錄數，00 03代表有3條記錄
#第57-64位元組，共8位元組，00 00 00 00 00 00 00 00
#第65-66位元組，共2位元組，00 00 代表葉子節點
#第67-74位元組，共8位元組，索引ID，00 00 00 00 00 00 00 dd
#第65-84位元組，共10位元組， 00 00 00 69 00 00 00 02 00 f2 代表B+樹數據頁非葉子節點所在段的段頭
#第84-95位元組，共10位元組，代表B+樹數據頁所在段的段頭，00 00 00 69 00 00 00 02 00 32

#第96-100位元組，共5位元組，01 00 02 00 1c，infimun記錄頭
#第101-108位元組，共8位元組，69 6e 66 69 6d 75 6d 00，infimun字節

第108-112位元組，共5位元組，04 00 0b 00 00，suprenum記錄頭
第113-120位元組，共8位元組，73 75 70 72 65 6d 75 6d，suprenum記錄



0000c000 a1 a3 c2 67 00 00 00 03 ff ff ff ff ff ff ff ff |...g............|
0000c010 00 00 00 00 85 69 9e a4 45 bf 00 00 00 00 00 00 |.....i..E.......|
0000c020 00 00 00 00 00 69 00 02 00 e9 80 05 00 00 00 00 |.....i..........|
0000c030 00 cb 00 02 00 02 00 03 00 00 00 00 00 00 00 00 |................|
0000c040 00 00 00 00 00 00 00 00 00 dd 00 00 00 69 00 00 |.............i..|
0000c050 00 02 00 f2 00 00 00 69 00 00 00 02 00 32 01 00 |.......i.....2..|
0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d

##往後行記錄內容
#從121位元組開始

#第一條記錄
#第121位元組，08變長欄位長度為8，NAME欄位值『YANGYANG』為變長欄位，8位元組
#第122位元組，00，NULL標記
#第123-127位元組，共5位元組，00 00 10 00 26，記錄頭
#第128-133位元組，共6位元組，ROWID，00 00 00 04 c2 00
#第134-139字共，共6位元組，事務ID，00 00 00 00 75 0a
#第140-146位元組，共7位元組，8a 00 00 01 b8 01 10，回滾段ID，指向UNDO回滾段地址
#第147-150位元組，共4節，ID欄位所佔固定長度，80 00 00 01
#第151-158位元組，共8位元組，變長欄位NAME佔用，『59 41 4e 47 59 41 4e 47』，轉換成字符串，為'YANGYANG'

0000c070 08 00 00 00 10 00 26 00 |supremum......&.|
0000c080 00 00 04 c2 00 00 00 00 00 75 0a 8a 00 00 01 b8 |.........u......|
0000c090 01 10 80 00 00 01 59 41 4e 47 59 41 4e 47

#第二條記錄
0000c090 08 00 |......YANGYANG..|
0000c0a0 00 00 18 00 26 00 00 00 04 c2 01 00 00 00 00 75 |....&..........u|
0000c0b0 0b 8b 00 00 01 99 01 10 80 00 00 01 44 4f 4e 47 |............DONG|
0000c0c0 58 55 41 4e 07 00 00 00 20 ff a5 00 00 00 04 c2 |XUAN.... .......| //20位元組所在位置為
0000c0d0 02 00 00 00 00 75 10 8e 00 00 01 4e 01 10 80 00 |.....u.....N....|
0000c0e0 00 01 46 45 4e 47 48 55 41 00 00 00 00 00 00 00 |..FENGHUA.......| //46 45 4e 47 48 55 41 對應『FENGHUA

文章很長, 但是我用到了裡面的一個方法. 

第一步是初始化表,並且插入數據. 根據表名可以看出來我進行過了很多次嘗試.

create table test014 (a varchar(16000));

insert into test014 values ((select repeat('a',8098)));

create table test015 (a varchar(16000));

insert into test015 values ((select repeat('a',8099)));

然後使用hexdump的方式將ibd文件導成 文本文檔 

hexdump -Cv /var/lib/mysql/zhaobsh/test014.ibd > test014.txt
hexdump -Cv /var/lib/mysql/zhaobsh/test015.ibd > test015.txt

然後查看兩個文件 

vim test014.txt

比較簡單的方式

在命令模式下輸入 4096 輸入大寫的G 調準到 第五個數據頁

可以看到 底4098 行的 45bf 指代當前page 為 數據頁 然後可以看到 當插入為 8098行數據時 當前數據頁有很多 實際數據在裡面. 

 

 然後看一下 8099 的 test015.txt 就會發現不一樣的現象

 

 

 當前頁內 沒有任何 a 的跡象. 查看第六個數據頁

會發現遷移到了下一個數據頁裡面進行存儲. 

 

 本來還想驗證 多個數據列的遷移情況. 但是有點困了 

mysql的東西還是很深奧的 , 需要多學習掌握才可以. 暫時先這樣吧 mark一下. 

驗證了下 ascii 也會同樣的數據情況下行遷移 但是沒有嘗試中文. 改天再試試.