`

基于InnoDB存储引擎的mysql数据库表结构详解

阅读更多

前言

前一篇文章基于InnoDB存储引擎的mysql数据库文件详解中的数据文件就是本篇文章的表文件。基于InnoDB存储引擎的mysql数据库所有数据都存在这个表文件中,可以叫做表空间。InnoDB存储引擎的表空间由段(segment),区(extent),页(page)组成。

理解一个概念:如果一台运行mysql数据库的服务器内存大小为1G,那么如果你查询大于1G的数据,必挂无疑。像select * from table的数据小于1G那么也会很慢,但是可以查询。mysql数据库把所有的数据传送完之后会存储一部分数据到内存。提供给下次的查询。

InnoDB存储引擎表空间结构图

 

biaojiegou

 

虽然表空间结构是如上图所示,但是也并不是严格按照上面的结构划分的,比如一个表空间可能由几个段和很多个零散的页组成。

对于innodb文件结构不理解可以查看基于InnoDB存储引擎的mysql数据库文件详解

一个表空间常见的段有数据段,索引段,回滚段。一个段有多个区组成。这里不做详细解释,意义不大。

一个区由连续的64个页组成。大小为64*16KB = 1MB。这里不做详细解释,意义不大。

一个页由多个行组成,因为是索引组织表,所以一个页最少要有2个行。

页是innoDB存储引擎最小的磁盘空间单位。一个页的大小为固定的16KB。

为什么说页是innodb存储引擎的最小磁盘空间单位?

比如新建一个test空表。如果往里面插入一行只有1KB的数据。那么innodb存储引擎就会从硬盘申请16KB的磁盘空间。虽然里面只有1KB的数据,但是还是会占据16KB的磁盘空间。剩余的15KB是空闲的空间,用于下次插入数据的空间使用。

打开innodb_file_per_table参数。进行下面的测试。

新建一个空表test。

mysql>create table test(id int(10) unsigned auto_increment,name varchar(8000),primary key(id))engine = innodb;

image

查看新表的大小。

#ls –lh test.ibd;

image

可以看到使用innodb存储引擎的新建的空表的大小为96KB。也就是96KB/16KB=6个页。也就是说innodb为每个空表分配了6个页大小的空间用。

给name列添加索引。

mysql>create index name on test(name);

MLYIM7K2I)WK8Y1QKJA7DRJ

在查看表的大小。

#ls –lh test.ibd;

image

大小变为112KB,增加了112KB-96KB=16KB。也就是说为一个列添加一条索引innodb就会给再申请16KB大小的磁盘空间,也就是1个页大小的空间使用。

删除这个name索引,再看看表空间大小。

image 

表空间的大小又变回96KB。虽然磁盘空间的大小成为96KB,但是这里面还有很多的空间可以用于新数据的存储。下面看看这个96KB大小的空间能够存储多少数据才会增加。

往表里面添加3行数据。大小为3*8000B,也就是3*8000B/1024 = 3*7.8kb = 23.4KB。在查看表空间大小。

mysql>insert test select null,repeat(‘a’,8000);

image

可以看到还是96KB大小。

再插入一条数据查看大小。

mysql>insert test select null,repeat(‘a’,8000);

image

表空间大小已经变为128KB了。增加了128KB-96KB=32KB。也就是增加了32KB/16KB=2个页的大小。

就是说空表的96KB大小只能存储 23.4KB大小的数据。再插入数据的表空间大小就会增加了。其实这个大小不是23.4KB,确切的应该是32KB。就是两个页的大小。如果两个页全部用完innodb就会申请更多的空闲页。这里申请了2个空闲页。

基础插入59条数据(为什么插入59条呢?为了证明一个概念,仔细看下面的测试会明白。)。那么数据的总大小为前面的4条+新的59条=63条数据。大小为63*7.8KB = 491.4KB。在查看表空间大小。

image

可以看到表空间大小变为576KB大小。也就是576KB/16KB=36个页。

这个时候再插入一条数据。查看大小。

image

奇迹出现,表空间大小变为2M。也就是2*1024KB/16KB = 128个页。为什么呢?

因为innodb在新建一个表的时候会用零散的36个页存储数据,如果数据量大于36页就会开始申请更大的空间存储数据。申请64个连续的页大小的空间。以后随着数据的增加继续申请64个页的空间。

页的结构

File Header 文件头 38个字节
Page Header 页头 56个字节
Infimun+Supermun Records 主键最大值,最小值 分配剩余的16KB*1024-38B-56B-8B=16282B
User Records 记录数据 分配16282B
Free Space 空闲空间 分配16282B
Page Directory 页目录 分配16282B
File Trailer 文件结尾信息 8个字节

其中文件头,页头,文件结尾信息的大小是固定。其余的会根据数据大小分配剩下空间。

文件头File header组成

名称 解释 大小
FIL_PAGE_SPACE_OR_CHKSUM 该页数据那个表空间,ibdata文件中插入缓冲页中这个值记录这test.ibd中的test表空间 4字节
FIL_PAGE_OFFSET 表空间中页的偏移量 4字节
FIL_PAGE_PREV 上一个页的偏移量 4字节
FIL_PAGE_NEXT 下一个页的偏移量 4字节
FIL_PAGE_LSN 该页最后被修改的日志位置LSN(log sequence number) 8字节
FIL_PAGE_TYPE 该页存放的数据类型。数据页,索引页等 2字节
FIL_PAGE_FILE_FLUSH_LSN 文件至少被更新到了LSN值 8字节
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 页属性属于那个表空间 4字节

页头Page Header的组成

名称 解释 大小
PAGE_N_DIR_SLOTS 在页目录(Page Directory)中slot(槽)数 2字节
PAGE_HEAP_TOP 堆中第一个记录的指针 2字节
PAGE_N_HEAP 堆中的记录数 2字节
PAGE_FREE 指向空闲列表的首指针 2字节
PAGE_GARBAGE 已删除记录的字节数,就是行记录结构中delete falg为1的记录 2字节
PAGE_LAST_INSERT 最后插入记录的位置 2字节
PAGE_DIRECTION 最后插入的方向,可能值PAGE_LEFT(0×01),PAGE_RIGHT(0×02) 2字节
PAGE_N_DERECTION 一个方向连续插入记录的数量 2字节
PAGE_N_RECS 该页中记录数 2字节
PAGE_MAX_TRX_ID 修改当前页的最大失误ID 8字节
PAGE_LEVEL 当前页在索引树种的位置,0×00代表叶节点 2字节
PAGE_INDEX_ID 当前页属于那个索引 8字节
PAGE_BTR_SEG_LEAF B+树的叶节点中,文件段的首页指针位置 10字节
PAGE_BTR_SEG_TOP B+树的非叶节点中,文件段的首指针位置 10字节

Infimun+Supermun Records的组成

这两个是该页的两个边界,infimun记录比该页中任何主键的值都要小。supermun记录比该页中任何可能最大的主键值都要大。

User Records 的组成

存储着所有的行记录内容。

FreeSpace 的组成

就是空闲的空间。

Page Directory 页目录的组成

存放了记录的相对位置。

File Trailer 文件尾的组成

名称 解释 大小
FIL_PAGE_END_LSN 与FILE_HEADER的FIL_PAGE_LSN值比对,用来检查页的完整性 8字节

InnoDB存储引擎的数据是按照一行一行存储的。每个页里面最多存放16KB大小的行。最小2行,因为是索引组织表。

InnoDB有很多行的记录格式。默认是Compact行记录格式。

Compact行记录格式

变长字段长度列表 NULL标记位 记录头信息 列1数据 列2数据 列3数据 ………… 列n数据
存储vachar等变长类型的长度 记录列中是否有NULL数据 关于行的信息记录 具体的列 具体的列 具体的列 具体的列 具体的列

 

总结

分享到:
评论

相关推荐

    详解MySQL中InnoDB的存储文件

    在MYSQL中建立任何一张数据表,在其数据目录对应的数据库目录下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息,包括表结构的定义等,.frm文件跟数据库存储引擎无关,也就是任何存储引擎的...

    Mysql InnoDB引擎的索引与存储结构详解

    而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎。 MySQL主要存储引擎的区别 MySQL默认的存储引擎是MyISAM,其他常用的就是...

    2017最新老男孩MySQL高级专业DBA实战课程全套【清晰不加密】,看完教程月入40万没毛病

    12-为什么选择MySQL数据库即MySQL优势介绍.avi 13-MySQL数据库分类与版本升级知识讲解.avi 14-MySQL数据库商业版与社区版区别.avi 15-MySQL数据库的发布版本知识讲解.avi 16-MySQL数据库发展的三条产品线介绍.avi 17...

    Mysql存储引擎详解

    关系型数据库表是用来存储和组织信息的数据结构,可以将表理解为由行和列组成的表格。 由于表的类型不同,我们在实际开发过程中,就有可能需要各种各样的表,不同的表就意味着存储不同类型的数据,数据的处理上也会...

    新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar

    │ 5_InnoDB存储引擎配置.mp4 │ 6_InnoDB统计资料和其他配置.mp4 │ 7_InnoDB锁原理和锁等待问题定位.mp4 │ ├─新版MySQL DBA综合实战班 第07天 │ 1_课后作业讲解.mp4 │ 2_MySQL锁机制原理讲解.mp4 │ 3_MySQL...

    深入MySQL存储引擎比较的详解

    InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。MEMORY存储引擎使用存在内存中的内容来创建表。每个...

    MySQL创建数据表并建立主外键关系详解

    需要建立主外键关系的两个表的存储引擎必须是InnoDB。 外键列和参照列必须具有相似的数据类型,即可以隐式转换的数据类型。 外键列和参照列必须创建索引,如果外键列不存在索引,mysql将自动创建索引。 一、SQL...

    MySQL管理之道 性能调优、高可用与监控.part2.rar

    本书适合所有希望构建和管理高性能、高可用性的mysql数据库系统的开发者和dba阅读。 目录 · · · · · · 前言 第一部分 mysql5.5 新特性篇 第1章 mysql5.5介绍 2 1.1 性能上的显著改变 2 1.1.1 mysql5.5默认...

    MySQL详解视频.zip

    oMySql内部组件设计结构 MySql内部组件详解 连接器详解 分析器详解 优化器详解 执行器详解 BinLog详解 数据删除恢复 o深入理解MySql索引底层数据结构与算法 数据结构角度 B+树索引 o索引查找...

    深入mysql创建自定义函数与存储过程的详解

    一 创建自定义函数在使用mysql的过程中,mysql自带的函数可能不能完成我们的业务需求,这时就需要自定义函数,例如笔者在开发过程中遇到下面这个问题:mysql表结构如下 代码如下:DROP TABLE IF EXISTS `test`;...

    Mysql使用索引的正确方法及索引原理详解

    索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能 非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。 索引优化应该是对查询性能优化最有效的...

Global site tag (gtag.js) - Google Analytics