最近客户现场有人问过mysql数据库的一些参数配置的问题, 这边数据库需要将strict 严格模式关掉, 目的是为了保证数据库在插入字段时不会出现8126的长度限制错误问题. 但是一直很困惑, mysql的默认安装的page 是 16kb 大小, 理论上一个页不可能超过 16000 个bytes 长度, 很多文档资料里面都有 说法是 text和blob的类型都会出现行迁移, 但是对varchar 都没有明确的说法, 今天趁着周六下了一个电子书, 将姜承尧老师的 翻了 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
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 也会同样的数据情况下行迁移 但是没有尝试中文. 改天再试试.