基于InnoDB存储引擎的mysql数据库表结构详解
来源:互联网 发布:php 代码压缩 编辑:程序博客网 时间:2024/06/06 12:55
前言
前一篇文章基于InnoDB存储引擎的mysql数据库文件详解中的数据文件就是本篇文章的表文件。基于InnoDB存储引擎的mysql数据库所有数据都存在这个表文件中,可以叫做表空间。InnoDB存储引擎的表空间由段(segment),区(extent),页(page)组成。
理解一个概念:如果一台运行mysql数据库的服务器内存大小为1G,那么如果你查询大于1G的数据,必挂无疑。像select * from table的数据小于1G那么也会很慢,但是可以查询。mysql数据库把所有的数据传送完之后会存储一部分数据到内存。提供给下次的查询。
InnoDB存储引擎表空间结构图
【这里是图片但是显示不了】
虽然表空间结构是如上图所示,但是也并不是严格按照上面的结构划分的,比如一个表空间可能由几个段和很多个零散的页组成。
对于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;
【这里是图片但是显示不了】
查看新表的大小。
#ls –lh test.ibd;
【这里是图片但是显示不了】
可以看到使用innodb存储引擎的新建的空表的大小为96KB。也就是96KB/16KB=6个页。也就是说innodb为每个空表分配了6个页大小的空间用。
给name列添加索引。
mysql>create index name on test(name);
【这里是图片但是显示不了】
在查看表的大小。
#ls –lh test.ibd;
【这里是图片但是显示不了】
大小变为112KB,增加了112KB-96KB=16KB。也就是说为一个列添加一条索引innodb就会给再申请16KB大小的磁盘空间,也就是1个页大小的空间使用。
删除这个name索引,再看看表空间大小。
【这里是图片但是显示不了】
表空间的大小又变回96KB。虽然磁盘空间的大小成为96KB,但是这里面还有很多的空间可以用于新数据的存储。下面看看这个96KB大小的空间能够存储多少数据才会增加。
往表里面添加3行数据。大小为3*8000B,也就是3*8000B/1024 = 3*7.8kb = 23.4KB。在查看表空间大小。
mysql>insert test select null,repeat(‘a’,8000);
【这里是图片但是显示不了】
可以看到还是96KB大小。
再插入一条数据查看大小。
mysql>insert test select null,repeat(‘a’,8000);
【这里是图片但是显示不了】
表空间大小已经变为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。在查看表空间大小。
【这里是图片但是显示不了】
可以看到表空间大小变为576KB大小。也就是576KB/16KB=36个页。
这个时候再插入一条数据。查看大小。
【这里是图片但是显示不了】
奇迹出现,表空间大小变为2M。也就是2*1024KB/16KB = 128个页。为什么呢?
因为innodb在新建一个表的时候会用零散的36个页存储数据,如果数据量大于36页就会开始申请更大的空间存储数据。申请64个连续的页大小的空间。以后随着数据的增加继续申请64个页的空间。
页的结构
File Header文件头38个字节Page Header页头56个字节Infimun+Supermun Records主键最大值,最小值分配剩余的16KB*1024-38B-56B-8B=16282BUser Records记录数据分配16282BFree Space空闲空间分配16282BPage Directory页目录分配16282BFile 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修改当前页的最大失误ID8字节PAGE_LEVEL当前页在索引树种的位置,0×00代表叶节点2字节PAGE_INDEX_ID当前页属于那个索引8字节PAGE_BTR_SEG_LEAFB+树的叶节点中,文件段的首页指针位置10字节PAGE_BTR_SEG_TOPB+树的非叶节点中,文件段的首指针位置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数据关于行的信息记录具体的列具体的列具体的列具体的列具体的列
- 基于InnoDB存储引擎的mysql数据库表结构详解
- 基于InnoDB存储引擎的mysql数据库表结构详解
- MySQL数据库存储引擎MyISAM和InnoDB的详解对比
- MySQL数据库存储引擎MyISAM和InnoDB的对比详解
- MySQL数据库存储引擎MyISAM和InnoDB的对比详解
- MySQL的MyISAM和InnoDB存储引擎表结构
- mysql修改数据库的存储引擎(InnoDB)
- mysql修改数据库的存储引擎(InnoDB)
- MySQL InnoDB存储引擎的参数详解
- MySQL(基于InnoDB存储引擎)
- MySQL InnoDB存储引擎详解
- MySQL数据库InnoDB存储引擎 Buffer Pool Flush List详解
- InnoDB存储引擎 --mysql的存储引擎
- MySQL InnoDB引擎的索引和存储结构
- MySQL的存储引擎INNODB
- InnoDB存储引擎表的逻辑存储结构
- MySQL数据库MyISAM和InnoDB存储引擎的比较
- (转)MySQL数据库InnoDB存储引擎的磁盘空间利用率分析
- UserAgent:通过浏览器获取用户浏览器和电脑信息
- google protocol buffer 与 redis 结合使用
- jquery 实现 提示框的 滑入 滑出效果
- jQueryValidationEngine_JS验证框架(上)
- UML 第二章
- 基于InnoDB存储引擎的mysql数据库表结构详解
- 有爱好者把我的CMS管理系统改成了JAVA版,有兴趣的可以看看
- CHttpConnection
- Tomcat性能调优方案
- 开发自已的javascript,收藏夹工具栏
- comboBox执行回车键变Tab键
- jQueryValidationEngine_JS验证框架(下)
- Impala/Hive现状分析与前景展望【转载】
- PowerDesigner从数据库导出pdm以及导出html格式数据字典