局域网站建设模版,南宁vi设计公司,宁夏 网站开发westte,精神文明建设专题网站背景
本文将深入探讨InnoDB的底层存储机制#xff0c;包括行格式、页结构、页目录以及表空间等核心概念。通过全面了解这些基础概念#xff0c;有助于把握MySQL的存储架构#xff0c;也为后续深入讨论MySQL的索引原理和查询优化策略奠定了基础。
1.行格式
mysql中数据以行…背景
本文将深入探讨InnoDB的底层存储机制包括行格式、页结构、页目录以及表空间等核心概念。通过全面了解这些基础概念有助于把握MySQL的存储架构也为后续深入讨论MySQL的索引原理和查询优化策略奠定了基础。
1.行格式
mysql中数据以行为单位进行存储将行数据、行的描述数据(可变字段长度、空值等)、隐藏字段等放在一起组成一个单位进行存储。MySQL支持四种行格式它们根据存储格式、字段溢出处理策略、是否启用压缩等因素进行区分。
1.1 行格式介绍
[1] Redundant
占用的空间最多且易导致内存严重碎片化是效率最低的行格式。已不再推荐使用Redundantmysql为了与旧版本保持兼容性而保留。
[2] Compact
Compact的每行记录由三个部分组成如下图所示: (1) 额外信息: 存放可变长字段实际长度列表、空值列表、记录头信息。 当数据库表中存在VARCHAR、VARBINARY、TEXT、BLOB等可变类型时需要记录这些字段的实际长度当数据库表中的字段允许为空时需要记录对应字段是否为空可变长字段列表、可为空字段列表中约定以字段顺序逆序排列。 其中记录头由固定的5字节组成, 包括以下字段: a) delete_flag: 标记该记录是否被删除; b) min_rec_flag: B树的每层非叶子节点中最小的目录项记录都会添加该标记; c) n_owned: 一个数据页中的记录分为若个个组每个组的最后一个记录通过n_owned记录当前组有多少条记录其他记录n_owned为0; d) heap_no: 当前记录在当前数据页的相对位置; e) record_type: 记录类型0-普通记录1-B树飞叶子节点的目录项记录2-Infimum记录3-Supremum记录; f) next_record: 下一条记录的相对位置。
(2) 隐藏字段 mysql为每条记录生成三个隐藏字段db_row_id和db_trx_id和db_roll_ptr; db_row_id: Innodb为每条记录生成的一个隐藏的自增主键可用于唯一标识记录行; 当数据库表中没有主键索引和非空的唯一索引时db_row_id数据将被用作建立聚簇索引。 db_trx_id记录插入或者最近一次修改当前记录的事务ID db_roll_ptr: 回滚指针指向上一个版本的快照数据多个版本之间通过db_roll_ptr形成版本链用于事务回滚
(3) 实际数据: 实际每列存储的数据信息。 实际存放用户数据的地方额外信息与隐藏字段是为了保证mysql的基本功能而引入的。
[3] Dynamic
InnoDB默认的行格式与Compact结构一致区别在于处理字段长度溢出时的策略不同。 数据溢出指text、blob、长varchar字段存放较大长度的数据。当数据溢出发生时Compact将前768个字节存储在当前页其他数据存储在溢出页并通过指针指向溢出页Dynamic将所有字节保存在溢出页仅保存指向该地址的20字节长度的指针。
[4] Compressed
Compressed行格式在Dynamic行格式的基础上增加了压缩功能能够进一步减少存储空间的需求适用于存储大量数据的场景。虽然可以减少存储空间但是在大量更新或查询操作下压缩和解压缩过程可能会消耗服务器的CPU资源从而影响mysql整体性能。 因此除非场景特殊一般不设置为Compressedmysql也禁止将Compressed设置为默认的行格式。
1.2 变量查询与设置
[1] 查询数据库默认行格式 通过innodb_default_row_format变量查询mysql默认支持的行格式:
mysql SHOW VARIABLES LIKE innodb_default_row_format;
------------------------------------
| Variable_name | Value |
------------------------------------
| innodb_default_row_format | dynamic |
------------------------------------
1 row in set (0.00 sec)mysql8汇总默认支持的行格式为DYNAMIC.
[2] 修改数据库默认行格式 通过设置innodb_default_row_format变量值:
SET GLOBAL innodb_default_row_format DYNAMIC;通过配置文件修改:
#/etc/my.cnf
innodb_default_row_formatRedundant修改后重启mysql生效。
注意COMPRESSED不能被设置为默认值。
[3] 建表时指定行格式 建表时通过ROW_FORMAT指定行格式案例如下所示: CREATE TABLE t_test (id INT AUTO_INCREMENT PRIMARY KEY) ENGINEInnoDB ROW_FORMATDYNAMIC;此时t_test被指定为DYNAMIC格式若不指定将使用mysql的默认行格式。
[4] 修改表的行格式
ALTER TABLE test ROW_FORMATCOMPACT;[5] 查看表的行格式 通过show table status from test like t_test;可以查看表的行格式也可通过查询information_schema.tables表的Row_format字段
mysql SELECT Row_format FROM information_schema.tables WHERE table_schema test AND table_name t_test;
------------
| ROW_FORMAT |
------------
| Dynamic |
------------
1 row in set (0.01 sec)2.页和页目录
在第一章中已经介绍了数据存储的基本单位(数据行)的存储格式本章将介绍存储数据行的容器即页。为减少IO次数以提高查询效率mysql要求内存与磁盘交换的单位是一个页大小默认为16K。页如下图所示由8个部分组成本章节将依次介绍这写组成部分:
2.1 数据部分User Records和Free Space
User Records区域用于存放行记录行之间通过next_record形成链表。Free Space表示空闲区域初始分配数据页时没有数据记录Free Space占据数据页绝大部分空间随着数据页中记录的添加User Records会逐渐占据Free Space区域。
2.2 上确界和下确界: Supremum和Infimum
在每个数据页中引入两个隐藏记录下确界和上确界分别作为链表的首部和尾部。当有数据插入时如果当前数据页可以存放新数据数据行插入在下确界和上确界之间否则在下一页存储。
2.3 页分组: Page Directory
当从数据页中检索记录行时可以从Infimum出发遍历记录行链表。一个数据页16K且绝大部分空间用于存储数据行因此数据页中的数据行数量较为巨大遍历搜索效率较低。 mysql通过引入了页目录概念解决上述问题。多个连续的数据行组成一个页目录mysql规定: (1) Infimum记录所在分组只有有一条记录是Infimum本身; (2) supremum所在分组记录数在1~8; (3) 其他中间分组在4~8条; (4)每个分组的最后一条记录的n_owned列记录所在分组的数量其他记录n_owned为0; 随着数据的插入分组的变化过程如下图所示:
数据页的初始状态: 没有用户记录行只有两个分组分别包含Infimum记录和supremum记录此时Page Directory中保存两个地址slot1和slot2分别指向Infimum记录和supremum记录二者都是分组的最后一条记录且所在分组记录数都是1所以n_owned1.
插入4条记录时:
数据根据主键判断插入在Infimum记录和supremum记录之间此时形成两个组。slot1和slot2分别指向两个组(页目录)。slot2对应的组中存在5条记录因此supremum的n_owned5.
再次插入4条记录时:
由于页目录的记录数在4~8所以需要裂解为两个组此时形成三个组分别对应page Directory中的slot1和slot2和slot3三个指针。Infimum所在分组始终只有一个记录n_owned1; slot2执行Record4的地址Record4记录的n_owned标记为4supremum所在组有5个记录行n_owned标记为5.
当理解了页目录的动态变化后可以考虑一下mysql为什么这么设计: 由于数据行按序排列(主键递增)因此页目录的最后一个记录为组内最大值; 每个页目录的最大行记录(最后一条记录)的地址固定为两个字节保存在Page Directory中。此时查询数据无需从Infimum节点开始遍历。可根据Page Directory中各个组的最大记录值快速定位出行记录属于哪个组然后在组内查找(每个组作为8条记录可以直接遍历)。
2.4 Page Header
Page Header是数据页专有的一些信息它占用固定的56个字节, 包含以下字段 (1) PAGE_N_DIR_SLOTS: Page Directory中的Slot数量; (2) PAGE_HEAP_TOP: 堆中第一个记录的地址; (3) PAGE_N_HEAP: 堆中的记录数, 包括Infimum和Supermum记录和已标记为删除的记录; (4) PAGE_NRECS: 用户实际的记录数不包括Infimum和Supermum也不包括Infimum和Supermum记录; (5) PAGE_FREE: 可用空间(free space)的地址; (6) PAGE_GARBAGE: 已删除记录的字节数; (7) PAGE_LAST_INSERT: 最后插入记录的位置; (8) PAGE_DIRECTION和PAGE_N_DIRECTION: 分别表示最后插入的方向和一个方向连续插入记录的数量。
2.5 File Header
File Header是页的通用信息部分对于所有类型的页都是相同的。File Header用于描述当前页的元信息它占用固定的38字节包含以下字段: (1) FIL_PAGE_OFFSET: 页号页的唯一ID; (2) FIL_PAGE_PREV和FIL_PAGE_NEXT分别表示上一个和下一个数据页的页号使得数据页之间形成双向链表; (3) FIL_PAGE_TYPE: 数据页的类型包括数据页索引页undolog页等; (4) FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID: 页所属的表空间; (5) FIL_PAGE_FILE_FLUSH_LSN: 仅在系统表空间的一个页中定义代表文件至少被刷新到了对应的LSN值(独立表空间默认为0); (6) FIL_PAGE_LSN页面被最后修改时对应的日志序列位置; (7) FIL_PAGE_SPACE_OR_CHKSUM页的校验和(checksum值), FIL_PAGE_LSN和FIL_PAGE_SPACE_OR_CHKSUM被用于与File Tailer进行比较以确认当前页是否完整。
2.6 File Tailer
mysql在内存中修改数据页之后刷入到磁盘中。刷盘过程遇到故障(如断电)可能导致数据页未完全写入磁盘。为确保页的完整性mysql分别在页的首部和尾部添加了File Header和File Tailer, 通过比较二者可以判断当前数据页是否完整。 File Trailer包含一个FIL_PAGE_END_LSN字段占8字节: 前4字节表示该页的checksum值后4字节表示FIL_PAGE_LSN; 两者需要与File Header中的FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN值进行比较匹配则满足一致性。
3.表空间
mysql中数据以页为单位进行存储不同类型的页存放不同类型的数据如FILE_PAGE_INDEX存放数据页FILE_PAGE_UNDO_LOG存放undo日志页。页需要以文件为载体存储在磁盘上表空间是对这个文件的抽象即表空间可以理解为页的载体类似书是页的载体。mysql中共存在两种类型的表空间用户表空间和系统表空间。 系统表空间只能有一个存放系统相关的信息以及作为公用的表空间。独立表空间可以有多个存放用户数据。 本章将依此介绍表空间结构和两种表空间。
3.1 表空间结构
一方面由于表空间的页较多合理高效的管理方式是对其划分为更小单位另一方面连续读的效率要高于随机读。因此在表空间中引入了区和组的概念。 一个页的大小是16K物理上连续的64个页作为一个区即1个区1M。当数据量较大时内存分配可以区为单位或者分配连续的多个区相对以页为单位大大提高效率。进一步每256个区划分为一个组即一个组256M。下图较为形象地展示表空间存储页的结构。 说明为了进一步优化管理mysql和节省存储空间mysql引入了段(segment)和碎片区(fragment)的概念。一般而言开发人员只需从整体上理解mysql的存储方式这部分细节可以不进行过度深入有兴趣可自行研究。
3.2 独立表空间
默认情况下(innodb_file_per_table1), 用户建表时mysql创建一个表空间与之对应innodb_file_per_table设置为0时建表时使用系统表空间。 以下结合案例进行说明
mysql show variables like innodb_file_per_table;
------------------------------
| Variable_name | Value |
------------------------------
| innodb_file_per_table | ON |
------------------------------
1 row in set (0.01 sec)innodb_file_per_table为ON(1)表示开启状态。
-- 创建数据库test和t_test表
create database test;
use test;
create table t_test(id int);查询表空间
mysql select ts.space, ts.name, ts.space_type, tf.path from information_schema.INNODB_SYS_TABLESPACES ts Left join information_schema.INNODB_SYS_DATAFILES tf on ts.space tf.space where name like %t_test%;
---------------------------------------------------
| space | name | space_type | path |
---------------------------------------------------
| 30 | test/t_test | Single | ./test/t_test.ibd |
---------------------------------------------------
1 row in set (0.00 sec)得到t_test表位于30号表空间对应磁盘文件为/test/t_test.ibd。 进入/var/lib/mysql查看表空间文件
root124:/var/lib/mysql# cd test/
root124:/var/lib/mysql/test# ls -al | grep t_test.ibd
-rw-r----- 1 mysql mysql 98304 Dec 1 03:57 t_test.ibd此时test/t_test表空间大小为98304即96k, 包含6个页。
3.3 系统表空间
表空间和表空间对应文件的路径、表与表空间的从属关系、表的列类型数据和索引等等这些用于管理数据而引入的额外信息属于系统信息, 保存在系统表空间中。以4张内部基本系统表为例 (1) sys_tables: 表信息包括表名列数表类型所属表空间 (2) sys_columns: 列信息包括所属表ID表的第几列列名类型 (3) sys_indexes: 索引信息包括索引名称索引类型列个数 (4) sys_fields: 索引列信息包括所属索引ID索引中的第几列列名称 上述四张表属于系统表用户无法直接访问。当mysql启动时读取这些sys_表信息填充到innodb_sys_表中这些表位于information_schema这个schema中。
mysql show tables like INNODB_SYS_%;
---------------------------------------------
| Tables_in_information_schema (INNODB_SYS_%) |
---------------------------------------------
| INNODB_SYS_DATAFILES |
| INNODB_SYS_VIRTUAL |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_SYS_TABLESPACES |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
---------------------------------------------
10 rows in set (0.00 sec)可通过INNODB_SYS_TABLES查看表的信息通过INNODB_SYS_TABLESPACES查看表空间信息案例如下所示 再看一下innodb_data_file_path变量保存了系统表空间的存储路径
mysql show variables like %innodb_data_file_path%;
-----------------------------------------------
| Variable_name | Value |
-----------------------------------------------
| innodb_data_file_path | ibdata1:12M:autoextend |
-----------------------------------------------
1 row in set (0.00 sec)系统表空间对应文件ibdata1初始大小为12M支持自动扩容。进入mysql路径下查看ibdata1文件
root124:/# cd /var/lib/mysql
root124:/var/lib/mysql# ls -al | grep ibdata1
-rw-r----- 1 mysql mysql 79691776 Dec 1 03:57 ibdata1此时大小为79691776即76M。
