MySQL InnoDB表空间详解
2022-06-14 23:13:06 0 举报AI智能生成
MySQL InnoDB表空间详解
作者其他创作
大纲/内容
数据目录
独立表空间在MySQL数据目标中路径
*/data/数据名/表名.frm
表结构
*/data/数据名/表名.ibd
表数据（普通索引+聚簇索引）
配置
[server]
innodb_file_per_table=0
innodb_file_per_table=0
对新表起作用
ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;
修改已经存在的表为独立的表空间
ALTER TABLE 表名 TABLESPACE [=] innodb_system;
修改已经存在的表为系统表空间
系统表空间在MySQL数据目标中路径
ibdata1
[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend
innodb_data_file_path=data1:512M;data2:512M:autoextend
数据库参数配置
*/data/数据名/db.opt
数据库的参数配置
MyISAM是如何存储表数据的
test.frm
test.MYD
test.MYI
test.MYD
test.MYI
视图在文件系统中的表示
我们知道MySQL中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个视图名.frm的文件
其他的文件
服务器进程文件。
服务器日志文件。
默认/自动生成的SSL和RSA证书和密钥文件。
文件系统对数据库的影响
数据库名称和表名称不得超过文件系统所允许的最大长度。
特殊字符的问题
文件长度受文件系统最大长度限制
MySQL系统数据库简介
mysql
它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
information_schema
这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引吧啦吧啦。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。
performance_schema
sys
这个数据库主要是通过视图的形式把information_schema和performance_schema结合起来，
独立表空间结构解析
独立表空间结构示意图
第0组
蓝色部分
固定由256个分区组成
记录独立表空间整体信息
第0组结构示意图
整体结构
单个分区结构
FSP_HDR页
FSP_HDR页结构示意图
File Space Header
File Space Header结构示意图
Space ID 4字节 表空间的ID
Not Used 4字节 这4个字节未被使用，可以忽略
Size 4字节 当前表空间占有的页面数
FREE Limit 4字节 尚未被初始化的最小页号，大于或等于这个页号的区对应的XDES Entry结构都没有被加入FREE链表
Space Flag
子主题
FRAG_N_USED 4字节 FREE_FRAG链表中已使用的页面数量
List Base Node for FREE List 16字节 FREE链表的基节点
ist Base Node for FREE_FRAG List 16字节 FREE_FRAG链表的基节点
List Base Node for FULL_FRAG List 16字节 FULL_FRAG链表的基节点
Next Unused Segment ID 8字节 当前表空间中下一个未使用的 Segment ID
List Base Node for SEG_INODES_FULL List 16字节 SEG_INODES_FULL链表的基节点
List Base Node for SEG_INODES_FREE List 16字节 SEG_INODES_FREE链表的基节点
XDES Entry
XDES Entry结构示意图
File Space Page Header
整个表空间只有一个
用来登记整个表空间的一些整体属性以及本组所有的区
IBUF_BITMAP页
IBUF_BITMAP页结构示意图
Insert Buffer Bitmap
存储本组所有的区的所有页面关于INSERT BUFFER的信息
INODE页
INODE页结构示意图
、
List Node for INODE Page List
List Node for INODE Page List结构示意图
INODE Entry
INODE Entry结构示意图
子主题
在第0个区，用来记录表空间的segment信息
用来记录表空间中所用的segment信息
第0个组之后的所有组
其他颜色部分
分区的数量可动态扩展
仅记录所在组私有信息
第0个组之后的所有组示意图
XDES 页
XDES 页结构示意图
XDES Entry
XDES Entry 页结构示意图
extent descriptor
用来记录本组256个分区的属性
IBUF_BITMAP页
区结构解析
区结构示意图
FSP_HDR页
FSP_HDR页结构示意图
File Space Header
File Space Header结构示意图
Space Flag
子主题
XDES Entry
File Space Page Header
整个表空间只有一个
用来登记整个表空间的一些整体属性以及本组所有的区
XDES 页
XDES 页结构示意图
XDES Entry
一个区就是在物理位置上连续的64个页
在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足填充满整个区），但是从性能角度看，可以消除很多的随机I/O，功大于过嘛
分区来存储叶子与非叶子节点
也就是说叶子节点有自己独有的区
存放叶子节点的区的集合就算是一个段（segment）
非叶子节点也有自己独有的区
存放非叶子节点的区的集合也算是一个段
也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。
一个区默认占用1M存储空间
区的分类
按照区内存使用维度分
空闲的区：现在还没有用到这个区中的任何页面
有剩余空间的碎片区：表示碎片区中还有可用的页面。
没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。
按照区所属端维度分
从属于索引段的区
从属于特殊端的区
区的4中状态
也代表了区的分类
也代表了区的分类
FREE 空闲的区
FREE_FRAG 有剩余空间的碎片区
FULL_FRAG 没有剩余空间的碎片区
FSEG 附属于某个段的区
XDES Entry链表
从属于表而空间
作用：在表空间的区非常多的情况下，可以用它快速找到和维护表空间中，不同状态（Free，FREE_FRAG，FULL_FRAG）的区
XDES Entry链表有如下几类
FREE链表
把状态为FREE的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为FREE链表。
FULL_FRAG链表
把状态为FREE_FRAG的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为FREE_FRAG链表。
FREE_FRAG链表
把状态为FULL_FRAG的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为FULL_FRAG链表。
XDES Entry链表
FREE链表
同一个段中，所有页面都是空闲的区对应的XDES Entry结构会被加入到这个链表。
注意和直属于表空间的FREE链表区别开了，此处的FREE链表是附属于某个段的
注意和直属于表空间的FREE链表区别开了，此处的FREE链表是附属于某个段的
NOT_FULL链表
同一个段中，仍有空闲空间的区对应的XDES Entry结构会被加入到这个链表。
FULL链表
同一个段中，已经没有空闲空间的区对应的XDES Entry结构会被加入到这个链表。
INODE Entry链表
从属于表而空间
存储位置
表空间->第0区->第0页(FSP_HDR页)中
INODE Entry链表
SEG_INODES_FULL链表
该链表中的INODE类型的页面中已经没有空闲空间来存储额外的INODE Entry结构了。
SEG_INODES_FREE链表
该链表中的INODE类型的页面中还有空闲空间来存储额外的INODE Entry结构了。
段 segment空间结构解析
段 segment结构示意图
Segment ID
就是指这个INODE Entry结构对应的段的编号（ID）。
NOT_FULL_N_USED
这个字段指的是在NOT_FULL链表中已经使用了多少个页面。
3个List Base Node
分别为段的FREE链表、NOT_FULL链表、FULL链表定义了List Base Node，这样我们想查找某个段的某个链表的头节点和尾节点的时候，就可以直接到这个部分找到对应链表的List Base Node。so easy!
Magic Number
这个值是用来标记这个INODE Entry是否已经被初始化了（初始化的意思就是把各个字段的值都填进去了）。如果这个数字是值的97937874，表明该INODE Entry已经初始化，否则没有被初始化。（不用纠结这个值有啥特殊含义，人家规定的）。
Fragment Array Entry
我们前边强调过无数次段是一些零散页面和一些完整的区的集合，每个Fragment Array Entry结构都对应着一个零散的页面，这个结构一共4个字节，表示一个零散页面的页号。
段 segment分类
数据量
当表的数据量比较少时：段是从某个碎片区以单个页面为单位来分配存储空间
当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间
其他用途的段
Undo Log Segment 回滚段
由多个Undo页面组成
用来管理 undo log的内存空间
段 segment的特点
为区分B+树的叶子节点 和非叶子节点引入段的概念
段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成
段是为了将B+树的叶子节点 和非叶子节点在逻辑上进行隔离，在范围查找时使用顺序I/O而非随机I/O
一个B+树索引，不管是聚簇索引还是非聚簇索引，都会用两个不同的段来存储叶子节点与非叶子节点
通常叶子节点包含数据，索引储存叶子节点是的段叫数据段，存储非数据段
回滚段空间结构解析
回滚段的特点
回滚段segment实质上只是一个 Rollback Segment Header
每个Rollback Segment Header对应一个段
并没有事先为回滚段申请内存空间
回滚段中关联的Undo页面是在用到的时候分配的
InnoDB中最多可以定义128个回滚段
对于系统中所有回滚段的管理信息，是记录在系统表空间的第5号页面
Rollback Segment Header结构示意图
回滚段在InnoDB中全局位置
回滚段的分类
子主题
页空间解析
FIL_PAGE_INDEX 数据页
页结构示意图
File Header
文件头部 38字节 页的一些通用信息
所有页都具有的
Page Header
页面头部 56字节 数据页专有的一些信息
专门针对数据页而存在
Infimum + Supremum
最小记录和最大记录 26字节 两个虚拟的行记录
User Records
用户记录 不确定 实际存储的行记录内容
User Records示意图
row 信息
Row信息示意图
子主题
Free Space
空闲空间 不确定 页中尚未使用的空间
Page Directory
页面目录 不确定 页中的某些记录的相对位置
用来对页中的数据进行分组的，相当于在页中建立起来的目录，便于查找
File Trailer
文件尾部 8字节 校验页是否完整
FIL_PAGE_UNDO_LOG undo日志页
页结构示意图
Undo Page Header
结构示意图
TRX_UNDO_PAGE_TYPE：本页面准备存储什么种类的undo日志
TRX_UNDO_INSERT
TRX_UNDO_UPDATE
TRX_UNDO_PAGE_START：表示在当前页面中是从什么位置开始存储undo日志的，或者说表示第一条undo日志在本页面中的起始偏移量。
TRX_UNDO_PAGE_NODE：代表一个List Node结构（链表的普通节点）。
Undo Log Segment Header
Rollback Segment Header页
页结构示意图
系统表空间结构解析
系统表空间结构示意图
第0组
蓝色部分
固定由256个分区组成
记录系统表空间整体信息
第0组结构示意图
整体结构
单个分区结构
FSP_HDR页
FSP_HDR页结构示意图
File Space Header
File Space Header结构示意图
Space ID 4字节 表空间的ID
Not Used 4字节 这4个字节未被使用，可以忽略
Size 4字节 当前表空间占有的页面数
FREE Limit 4字节 尚未被初始化的最小页号，大于或等于这个页号的区对应的XDES Entry结构都没有被加入FREE链表
Space Flag
子主题
FRAG_N_USED 4字节 FREE_FRAG链表中已使用的页面数量
List Base Node for FREE List 16字节 FREE链表的基节点
ist Base Node for FREE_FRAG List 16字节 FREE_FRAG链表的基节点
List Base Node for FULL_FRAG List 16字节 FULL_FRAG链表的基节点
Next Unused Segment ID 8字节 当前表空间中下一个未使用的 Segment ID
List Base Node for SEG_INODES_FULL List 16字节 SEG_INODES_FULL链表的基节点
List Base Node for SEG_INODES_FREE List 16字节 SEG_INODES_FREE链表的基节点
XDES Entry
XDES Entry结构示意图
File Space Page Header
整个表空间只有一个
用来登记整个表空间的一些整体属性以及本组所有的区
IBUF_BITMAP页
IBUF_BITMAP页结构示意图
Insert Buffer Bitmap
存储本组所有的区的所有页面关于INSERT BUFFER的信息
INODE页
INODE页结构示意图
、
List Node for INODE Page List
List Node for INODE Page List结构示意图
INODE Entry
INODE Entry结构示意图
子主题
在第0个区，用来记录表空间的segment信息
用来记录表空间中所用的segment信息
SYS Insert Buffer Header 存储Insert Buffer的头部信息
INDEX Insert Buffer Root 存储Insert Buffer的根页面
TRX_SYS Transction System 事务系统的相关信息
SYS First Rollback Segment 第一个回滚段的页面
SYS Data Dictionary Header 数据字典头部信息
Data Dictionary Header页示意图
extent 1
extent 2
B+树索引
数据查找的几种形式
没有索引的查找
在一个页中的查找
以主键为搜索条件
在页目录中以二分法的形式查找
以其他列作为搜索条件
从页中最小到最大记录开始遍历查找
在很多页中查找
查找步骤
1、定位到记录所在的页。
2、从所在的页内中查找相应的记录
图解
页结构
B+树索引添加记录
第一步
第二步
第三步
第四步
建立页的目录项
Record_type =1
每个记录中存放下级的页中最小的主键ID
页中同样也有记录的目录项
二级索引
图解
联合索引
图解
排序规则
先按照name列的值进行排序。
如果name列的值相同，则按照birthday列的值进行排序。
如果birthday列的值也相同，则按照phone_number的值进行排序。
如果name列的值相同，则按照birthday列的值进行排序。
如果birthday列的值也相同，则按照phone_number的值进行排序。
二级索引时当最左的想等才会对后面的所有进行排序
InnoDB的B+树索引的注意事项
根页面万年不动窝
内节点中目录项记录的唯一性
二级索引中记录的是
索引列的值
主键值
页号
一个页面最少存储2条记录
MyISAM中的索引方案简单介绍
MyISAM中建立的索引相当于全部都是二级索引
索引的代价
空间上的代价
时间上的代价
要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。
索引查找的几个场景
全值匹配
匹配左边的列
匹配列前缀
匹配范围值
只有最前面的才看可以范围查找
精确匹配某一列并范围匹配另外一列
用于排序
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
三个字段都可以使用上
联合索引排序中不可混用 ASC、DESC
ORDER BY name, birthday LIMIT 10
ORDER BY name DESC, birthday DESC LIMIT 10，
重点是这样不能高效使用索引，而要采取更复杂的算法去从索引中取数据，设计MySQL的大叔觉得这样还不如直接文件排序来的快，所以就规定使用联合索引的各个排序列的排序顺序必须是一致的。
排序列包含非同一个索引的列
排序列使用了复杂的表达式
用于分组
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number
回表的代价
访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。
，什么时候使用采用二级索引 + 回表的方式去执行查询呢
查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式
覆盖索引
如何挑选索引
只为用于搜索、排序或分组的列创建索引
考虑列的基数
列在表中的值重复度不高
索引列的类型尽量小
数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
索引字符串值的前缀
索引列前缀对排序的影响
例子
SELECT * FROM person_info ORDER BY name LIMIT 10;
无法使用索引
让索引列在比较表达式中单独出现
主键插入顺序
避免冗余和重复索引
XDES Entry 字段组成示意图
Segment ID（8字节）
每一个段都有一个唯一的编号，用ID表示，此处的Segment ID字段表示就是该区所在的段。
当然前提是该区已经被分配给某个段了，不然的话该字段的值没啥意义。
当然前提是该区已经被分配给某个段了，不然的话该字段的值没啥意义。
List Node
List Node示意图
State（4字节）
这个字段表明区的状态。可选的值就是我们前边说过的那4个，分别是：FREE、FREE_FRAG、FULL_FRAG和FSEG。
Page State Bitmap（16字节）
这个部分共占用16个字节，也就是128个比特位。我们说一个区默认有64个页，这128个比特位被划分为64个部分，每个部分2个比特位，对应区中的一个页。比如Page State Bitmap部分的第1和第2个比特位对应着区中的第1个页面，第3和第4个比特位对应着区中的第2个页面，依此类推，Page State Bitmap部分的第127和128个比特位对应着区中的第64个页面。这两个比特位的第一个位表示对应的页是否是空闲的，第二个比特位还没有用。
参考资料
https://www.pianshen.com/article/4066756791/
0 条评论
下一页
