表的行格式决定了其行的物理存储方式,进而会影响查询和DML操作的性能。随着更多的行适合单个磁盘页面,查询和索引查找可以更快地工作,缓冲池中需要更少的缓存,并且写出更新后的值也需要更少的I / O。
每个表中的数据分为几页。构成每个表的页面以称为B树索引的树数据结构排列。表数据和二级索引都使用这种类型的结构。代表整个表的B树索引称为聚簇索引,它是根据主键列进行组织的。聚集索引数据结构的节点包含该行中所有列的值。二级索引结构的节点包含索引列和主键列的值。
可变长度列是将列值存储在B树索引节点中的规则的例外。太长而无法容纳在B树页面上的变长列存储在单独分配的磁盘页面上,这些磁盘页面称为溢出页面。这些列称为页外列。页面外列的值存储在溢出页面的单链接列表中,每个这样的列都有其自己的一个或多个溢出页面的列表。根据列的长度,所有或可变长度列值的前缀都存储在B树中,以避免浪费存储空间并不得不读取单独的页面。
InnoDB存储引擎支持四种的格式:REDUNDANT,COMPACT, DYNAMIC,和COMPRESSED。
定义表格的行格式
InnoDB表 的默认行格式由innodb_default_row_format 变量定义 ,其默认值为DYNAMIC。ROW_FORMAT未明确定义表选项或ROW_FORMAT=DEFAULT指定表选项时,将使用默认行格式 。
可以使用CREATE TABLE或 ALTER TABLE语句中的ROW_FORMAT table选项 显式定义表的行格式 。例如:
CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;
显式定义的ROW_FORMAT设置将覆盖默认行格式。指定 ROW_FORMAT=DEFAULT等同于使用隐式默认值。
innodb_default_row_format 变量可以动态设置:
mysql>SET GLOBAL innodb_default_row_format=DYNAMIC;
有效的innodb_default_row_format 选项包括DYNAMIC, COMPACT,和REDUNDANT。COMPRESSED无法在系统表空间中使用的 行格式不能定义为默认格式。它只能在CREATE TABLEor ALTER TABLE语句中明确指定 。尝试将innodb_default_row_format 变量设置 为COMPRESSED返回错误:
mysql> SET GLOBAL innodb_default_row_format=COMPRESSED;
ERROR 1231 (42000): Variable 'innodb_default_row_format'
can't be set to the value of 'COMPRESSED'
innodb_default_row_format 当ROW_FORMAT未明确指定选项或ROW_FORMAT=DEFAULT使用选项 时, 新创建的表将使用变量定义的行格式 。例如,以下CREATE TABLE语句使用innodb_default_row_format 变量定义的行格式 。
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;
如果ROW_FORMAT未明确指定选项或ROW_FORMAT=DEFAULT使用了选项,则重建表的操作会以静默方式将表的行格式更改为innodb_default_row_format 变量定义的格式 。
表重建操作包括ALTER TABLE使用 表ALGORITHM=COPY或 ALGORITHM=INPLACE需要表重建的操作。有关更多信息,请参见第15.12.1节“在线DDL操作”。OPTIMIZE TABLE也是表重建操作。
下面的示例演示一个表重建操作,该操作以静默方式更改没有显式定义的行格式创建的表的行格式。
mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic |
+-----------------------------+
mysql> CREATE TABLE t1 (c1 INT);
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 54
NAME: test/t1
FLAG: 33
N_COLS: 4
SPACE: 35
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
mysql> SET GLOBAL innodb_default_row_format=COMPACT;
mysql> ALTER TABLE t1 ADD COLUMN (c2 INT);
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 55
NAME: test/t1
FLAG: 1
N_COLS: 5
SPACE: 36
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
从改变现有表的行格式之前,请考虑以下潜在的问题REDUNDANT或 COMPACT对DYNAMIC。
的REDUNDANT和 COMPACT行格式支持的767个字节的最大索引关键字前缀长度而 DYNAMIC与COMPRESSED 行格式支持的3072个字节的索引关键字前缀长度。在复制环境中,如果将innodb_default_row_format 变量设置为主服务器DYNAMIC上的变量,并且将其设置为COMPACT从服务器上的 变量,则以下未明确定义行格式的DDL语句在主服务器上成功但在从机上失败:
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070)));
如果innodb_default_row_format 源服务器上的设置与目标服务器上的设置不同,则导入未明确定义行格式的表会导致架构不匹配错误 。有关更多信息,请参见 第15.6.1.3节“导入InnoDB表”。
确定表的行格式
要确定表的行格式,请使用 SHOW TABLE STATUS:
mysql> SHOW TABLE STATUS IN test1\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2016-09-14 16:29:38
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
或者,查询 INFORMATION_SCHEMA.INNODB_TABLES 表:
mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic |
+----------+------------+