InnoDB表空间管理(File Space Management)

MySQL数据库使用表空间管理数据库文件,InnoDB使用多种类型的表空间优化数据存储。

系统表空间(system tablespace)

MySQL 5.6.7之前默认将所有InnoDB表和索引保留在系统表空间内,由于系统表空间不会缩小,如果加载并删除大量临时数据,则可能出现存储问题;MySQL 8.0默认会为每个表创建一个.ibd文件(独立表空间文件),用于存储表和相关索引。

常规表空间(General tablespaces)

为了避免在系统表空间中存储所有表和索引,InnoDB默认启用innodb_file_per_table选项,为每个表创建一个独立的表空间,在具有数千个表的系统中,处理数千个.ibd文件也可能出现瓶颈,InnoDB引入常规表空间,能够保存多个表。常规表空间是一种共享表空间,使用以下语句创建:

CREATE TABLESPACE tablespace_name

所以,所谓的独立表空间(separate tablespace/table-per-table)与共享表空间(shared tablespaces)自然也就清楚了。InnoDB通过innodb_data_file_path选项配置表空间。

页面/Pages

页面作为InnoDB中的一个数据单位,默认为16kb,用来表示InnoDB在磁盘和内存( 缓存)间传输的数据量,即,操作数据时以page为单位。pags可选的大小范围16KB、8KB、4KB,通过innodb_page_size调整。

范围/Extents

以1M为限对页面进行分组(64个连续的16KB页面,或128个8KB页面,或256个4KB页面)。

段/Segments

表空间中的“文件/files”在InnoDB中称为段(Segments)。

页面与行的关系

对于4KB、8KB、16KB和32KB页面,行的最大长度比page的一半还小一点,这源于InnoDB中的表为索引组织表,叶子节点是一个双向链表,所以,一个page中至少应该有两行记录,加上InnoDB自身数据结构占用的空间。

对于默认16KB的页面,最大行长度略小于8KB 。对于64KB页面,最大行长度略小于16KB。如果一行数据没有超过最大行长度,所有数据都会存储在一个页面内,如果超过最大行长度,则会把变长列挑出来放在off-page上。

注:VARCHAR、VARBINARY、BLOB、TEXT,以及长度大于或等于768字节的固定长度字段都被识为变化列,变长列的前768字节会被存放在B-tree上。LONGBLOB和 LONGTEXT必须小于4GB,且行总长度(包括BLOB和TEXT列)不能大于4GB。

off-page

变长数据因太长而无法放在B-tree page上,数据存储在溢出页面(单独分配的磁盘页面)中。

注:数据能否存储在B-tree page上,取决于数据自身的大小,而非数据类型。

InnoDB行格式

InnoDB行格式主要基于以下事实,出现的几种数据组织策略。

表的行格式确定行的物理存储方式,反过来又会影响查询和DML操作的性能。单个页面上的数据行越多,查询和索引就越快,缓冲池中的内存占用就越少,所需的I/O操作也更少。

表中的数据被分成多个页。这些页面被安排在B-tree树型结构中,表数据和二级索引都使用这种结构。代表整个表的B-tree索引称为聚簇索引,它根据主键进行组织。聚簇索引的节点上包含行中所有列的值,二级索引的节点上包含索引列和主键的值。

变长列因数据太长,无法放在B-tree页面上,这些列存储在单独分配的磁盘页面上,称为溢出页面(overflow pages)。这样的列称为页外列(off-page columns)。页外列的值存储在溢出页的单向链表中,每个变长列都有自己的一个或多个溢出页列表。所有变长列的值,前缀都存储在B-tree中,以避免浪费存储空间和读取整个页面。

InnoDB存储引擎支持四种行格式:冗余、紧凑、动态和压缩。

查询表的行格式使用以下语句:

SHOW TABLE STATUS IN [schema_name];
SHOW TABLE STATUS IN test;

或者

SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/student';

冗余行格式

将表中变长列的值,前768个字节存储在B-tree节点的索引记录中,其余部分存储在溢出页面上。如果列的值为768字节或更少,则不使用溢出页,以减少I/O操作,这适用于相对较短的BLOB,因为太长的BLOB列会导致B-tree点变得太满,而包含的数据行太少,使得整个索引的效率,低于较短行或将值存储在页外的情况。

大于或等于768字节的定长列也有可能成为变长列,例如,CHAR(255),如果使用最大字节超过3的utf8mb4字符集,则列的值有可能超过768字节。

紧凑行格式

与冗余行格式相比少了大约20%的行存储空间,代价是某些操作增加了CPU的使用。如果工作负载受缓存命中率和磁盘速度限制,则紧凑格式可能会更快。如果工作负载受CPU速度限制,则紧凑格式可能会变慢。

动态行格式

提供与紧凑行格式相同的存储特性,但增强对变长列的存储功能,并支持大型索引键前缀。

压缩行格式

提供与动态行格式相同的存储特性和功能,增加了对表和索引数据压缩的支持。

InnoDB检查点(InnoDB Checkpoints)

当对缓存中数据页进行修改时,更改会在稍后的某个时间刷回磁盘,检查点则记录已成功刷回磁盘的修改 。

检查点处理如何工作

InnoDB以小批量方式将缓存中已修改的页面刷回磁盘。

在崩溃恢复期间, InnoDB查找写入日志文件的检查点标记,并从检查点向前扫描日志文件,将修改的记录应用到数据库。

对表进行碎片整理

碎片化的表占用的空间比实际需要的空间要多。碎片化的表扫描需要花费更长的时间。

要加速索引扫描,可以定期执行空的ALTER TABLE 操作,促使MySQL重建表:

ALTER TABLE tbl_name ENGINE=INNODB

或者

ALTER TABLE tbl_name FORCE

执行碎片整理操作的另一种方法是使用mysqldump将表导出,删除表,然后再导入。

如果索引中的插入始终是升序,并且仅从末尾删除记录,则InnoDB文件空间管理算法可以确保碎片不会出现在索引中。

使用TRUNCATE TABLE回收磁盘空间

在建表时,如果每个表使用独立的表空间(.ibd),并且表与表之间不存在外键约束,当使用truncated操作时,会重建新的.ibd文件中,磁盘空间会被释放。而对系统表空间和通用表空间使用truncated操作,则不会释放磁盘空间。

注:MySQL 8.0默认innodb_file_per_table=ON,即为每个表创建独立的表空间。