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