MySQL数据库页存储结构学习与了解
背景
MySQL总是出现奇奇怪怪的问题.
想着自己能够学习与提高一下.
最近看了很多文档.关于MySQL数据库相关的.
想着总结和提炼一下, 希望能够给未来的工作提供一下指导.MySQL的存储引擎
MySQL有多种存储引擎, 主要有:
InnoDB：是MySQL的默认存储引擎。支持事务、行级锁、外键约束等功能。适用于需要大量update、delete操作的应用。
MyISAM：不支持事务和行级锁，但是对于需要经常进行读写操作的应用，MyISAM比InnoDB更快。MyISAM的表级锁可以更好的支持大量的并发读操作。
MEMORY：数据被存储在内存中，无法持久化，当服务器重启后，数据会被清空。适用于需要高速读写并且数据不需要长期存储的小型表。
BLACKHOLE：将所有写入操作“吞噬”，但是可以进行正常查询操作。适用于数据备份、测试等场景。
Archive：只支持Insert和Select，不支持Update和Delete，采用文件压缩方式进行数据存储。适用于短期存储历史数据的情况。
NDB：分布式存储引擎，支持事务和高可用性。适合处理海量数据和高并发场景。MySQL的Innodb存储引擎相关限制
Mysql的Innodb的存储引擎时的限制其实很多.
1. MySQL的索引最大值不能超过3072
2. MySQL在严格模式下,表的列长度之和不能大于8126, 不然会出现错误:
ERROR 1118 (42000): 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.
3. 在非严格模式下, Mysql的大表的一个行列长度之和不能大于 65535, 但是注意, 这个长度是包含了字符集的设置在面.
latin或者是ascii字符集可以保存65535个, gbk 就是65535/2, utf8 是 65535/3 utf8mb4 是 65535/4
4. InnoDB限制每个表的最大列数为1017列.
5. Mysql在8.0之后单表大小不能超过64T.关于Mysql单页存储的说明
在严格模式下, 数据也会有 38个字节的文件头. 56个字节的页头
以及最后8个字节的校验和.
Mysql每一页里面都至少要存储两行记录, 还会有两个虚拟的最大列和最小列.
每个占用13个字节, 合计 26个字节.
Mysql还有一个Page Directory的空间. 一般使用4个字节(不是很确认)
所以一个16KB的页里面, 要有:
38+56+26+8+4=132字节的存储开销.
所以一个页面里面可以存储两条记录. 那么计算方式为
(16384-132)/2 = 8126 长度
这个就是第一个数据库严格模式下限制长度的根本原因.Mysql的行迁移
我在2020年时研究过一次行迁移. 当时的结论主要如下:
varchar 在 8099 的长度时会出现行溢出迁移的现象 将字符存储到第六个数据页
varchar 在 8098 时还是在当前数据也存储的, 而且应该是在第五个数据页存储.
姜承尧老师 <MySQL技术内幕InnoDB存储引擎第2版>
mysql5.6 初始化时 是 6个数据页,大小是96KB,
但是mysql 8.0.21 的版本初始一个ibd文件时 是初始化7个数据页. 大小是 112KB
可以使用hexdump的方式将ibd文件导成 文本文档
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 -Cv /var/lib/mysql/zhaobsh/test014.ibd > test014.txt
hexdump -Cv /var/lib/mysql/zhaobsh/test015.ibd > test015.txt
然后可以查看每个ibd文件的存储.一些简单总结
MySQL其实不是很适用于ERP这种经常多表关联的业务需求场景
互联网经常使用的CRUD模式下性能还是比较良好的.
最近发现MySQL的多表关联视图也存在很多问题.
感觉这个数据库的确不适应很复杂的业务逻辑场景.
如果应用能够在内存中进行计算而不是使用SQL进行处理. 那么还是比较合适的.