【MySQL · Innodb架构简析】二、Innodb Tables

本文内容主要是人工翻译自MySQL5.7官网手册——Innodb Table部分，读者可以结合官文手册阅读。如有错误请指出，感谢阅读，欢迎讨论！

1. 普通建表

CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

这是一条最基础的建表SQL，这里需要注意的是尾部的ENGINE=InnoDB 一般来说可以省略，因为大部分mysql实例的默认engine都是innodb，但在较大且复杂的mysql集群环境中工作时，建议加上，因为同样的sql在不同实例上执行结果可能不同（由于settings不完全一样）。

如何查看默认engine：mysql> SELECT @@default_storage_engine

1.1 innodb_file_per_table

默认mysql每创建一个表都会占用一个单独物理文件，但可以通过设置变量innodb_file_per_table来修改。另外，默认表文件存在于系统表空间（system tablespace），但我们可以创建到通用表空间（general tablespace），使用SQL：CREATE TABLE ... TABLESPACE，后者允许我们灵活管理特定表数据文件的存放位置，具体自行了解。

1.2 .frm文件

这个文件存储了相应表的数据字典信息，位置在db目录。当删除一个表或库时，mysql会同时删除一个或多个frm文件。注意：我们不能直接通过移动frm文件来移动/复制一个表，关于表迁移的更多信息参考这里。

1.3 行格式（Row Formats）

行格式决定数据存在磁盘上的具体特征。MySQL支持4种行格式： REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED，默认是DYNAMIC。更多信息
变量innodb_default_row_format定义了默认行格式，一般不需要更改。我们可以在建表/改表SQL中修改：

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

1.4 主键（primary key）

官方推荐的是我们创建的每张表都应该拥有一个主键（可由多字段组成），且应该具有以下特征：

• 频繁查询的列
• 不会为空的列
• 不会重复的列
• 极少更改的列

MySQL支持对主键设置自增属性，使用方式如下：

CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));

主键不是必须的，但应该设置。因为主键关乎对这张表的各种操作方面的性能，一张大表若没有主键，则可能导致极慢的操作速度。更多信息

1.5 查看表信息

第一种方式：SHOW TABLE STATUS

mysql> SHOW TABLE STATUS FROM test LIKE 't%' \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: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-02-18 12:18:28
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment:

第二种方式：查系统表

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 45
         NAME: test/t1
         FLAG: 1
       N_COLS: 5
        SPACE: 35
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

2.创建外部表

外部表指的是表物理文件存储在data目录以外；因为空间管理、I/O优化等原因，我们有时可能需要创建外部表。方法一般有2种（官网介绍了3种，但第2和第3算一种）

2.1 使用DATA DIRECTORY

CREATE TABLE test_db.t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = 'd:/ex_dir';

执行后，d:/目录结构

d:\ex_dir
└─test_db
        t1.ibd   // 表数据文件

mysql的data目录结构

mysql\data\test_db
    db.opt
    t1.frm
    t1.isl  

这样做需要注意几点，以防止出现严重错误

• 服务启动时不要卸载磁盘，或磁盘卸载时不要启动mysql
• 不要直接迁移表数据文件，如果已经迁移/消失了，需手动删除 .isl 文件，然后重启mysql，再drop table以删除 .frm文件
• 如果要使用NFS文件格式的volume，请先查看注意事项
• 如果想通过lvm快照、文件拷贝或其他基于文件的机制进行备份，请先执行FLUSH TABLES ... FOR EXPORT sql确保buffer pool中的脏页刷盘。关于 FLUSH语句 参考这里
• 使用DATA DIRECTORY建表其实是基于符号链接建表的替换方案，因为后者innodb不支持。
• 若在mysql使用了主从复制的环境中使用这种方式，且主从节点在同一主机上时，数据不会复制。

2.2 使用CREATE TABLE ... TABLESPACE

这种方式可以和DATA DIRECTORY结合，如下

CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table \
	DATA DIRECTORY = '/external/directory';

注意，如果使用了DATA DIRECTORY，那么tablespace就只支持innodb_file_per_table，因为前者其实已经指定了数据的存储位置，不可能又用TABLESPACE指向另一个位置，innodb_file_per_table指的是数据存放格式为每张表占1个文件。

当然，这里的重点并不是DATA DIRECTORY了；我们同样可以通过设置TABLESPACE选项将表存放到另一个位置，那就是前面说过的通用表空间，因为不是常见做法，所以这里只写操作步骤，具体特点以及原理，请阅读官网手册。

1. 创建一个表空间（可使用绝对/相对路径）
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
2. 创建表到这个空间
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
3. 或者迁移表到这个空间
ALTER TABLE t2 TABLESPACE ts1;

注意事项：存在通用表空间的表支持innodb4种行格式(REDUNDANT, COMPACT, DYNAMIC, COMPRESSED)，但不能同时存在压缩和未压缩的表（由于物理页大小不同）。
另外，对于包含压缩表的通用表空间，对db和table的参数FILE_BLOCK_SIZE和KEY_BLOCK_SIZE都有要求，具体参考这里

我们可以用ALTER TABLE来迁移表从一个file-per-table或系统表空间到另一个通用表空间，sql如下

ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;

也可以迁移到系统表空间：

ALTER TABLE tbl_name TABLESPACE [=] innodb_system;

也可以迁移到file-per-table表空间：

ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

迁移表是一个较重的操作，需要注意：

• 会导致整个表的重建
• 不支持从临时表迁移到持久化表
• 加密的表不支持

最后，DROP TABLESPACE ts1 之前要求先把该空间内所有的表先删除。

3. 导入innodb表

这一节介绍如何使用transportable tablespaces特性来导表。那些情况下我们需要导表：

• 在非生产mysql节点上运行统计查询，以避免造成额外的压力
• 复制数据到从节点
• 从备份的表空间文件恢复数据
• 超快的迁移数据方式，相对于导入一个dump文件。后者需要重新插入数据和重建索引
• 迁移数据（如迁移到一个容量更大，性能更好的磁盘）

3.1 前提

• 要求innodb_file_per_table参数必须是开启状态（默认开启）
• 表空间的页大小比如目标表空间的页大小一直；innodb_page_size控制了页大小，默认16KB，mysql启动后不能修改
• 如果表有外键关系，在执行DISCARD TABLESPACE之前必须设置foreign_key_checks变量为关闭状态；另外你应该在同一逻辑时间点导出所有与外键相关的表，因为ALTER TABLE ... IMPORT TABLESPACE在导表时不会强制外键约束。最后，还需要停止对所有相关表的更新，事务提交，并且给所有表加上共享锁，才能执行导出操作。
• 如果从另一个mysql实例上导表，则要求mysql版本一致，否则需要提前手动建表。
• 如果一张表是通过指定DATA DIRECTORY建表的，则在目标mysql节点上也得使用同样的数据目录。
• 如果表的行格式是使用默认，则要求与目标mysql节点的innodb_default_row_format变量的值相同。

3.2 演示

下面演示如何导入一个常规的非分区表（表空间=file-per-table）

1. 在目标节点建表
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
2. 丢弃新表的表空间
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
3. 在源节点执行FLUSH语句，确保内存脏页全部刷盘，且禁止数据的更改，只允许读
mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;

当FLUSH...sql执行时，innodb在data/db/生成.cfg文件，在目标节点导表时用来验证的。

现在复制.ibd和.cfg文件到目标节点：

shell> scp /path/to/datadir/test/t1.{ibd,cfg} destination-server:/path/to/datadir/test

注：如果是加密的表空间，innodb还会生成一个.cfp文件，也需要复制到目标节点。

解锁源表：

mysql> USE test;
mysql> UNLOCK TABLES; // 刚flush上了S锁

在目标节点导入：

mysql> USE test;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;

3.3 演示如何导入分区表（省略）

3.4 演示如何导入表分区（省略）

3.5 导表产生的内部日志（省略）

省略部分是关于具体操作步骤的，仅在需要时了解即可。

4. 迁移或复制innodb表

当遇到以下情况时可能需要对mysql数据做库/表级迁移：

• 扩容，使用高性能磁盘
• 拷贝完整数据库副本
• 拷贝部分表到其他节点，用于测试
• 拷贝到数仓用于生产报告

这部分的技术点包含以下几个：

• 导表（上面已经讲过）
• MySQL企业备份
• 复制数据文件（冷备）
• 从逻辑备份恢复

都是纯工具性的内容，细节不讲。

5. 从MyISAM转到Innodb

表引擎的转换比较复杂，需要注意下面几点：

• 调整MyISAM和Innodb的内存使用
• 处理长事务和短事务
• 处理死锁
• 存储布局
• 转换一个已存在的表
• 克隆表结构
• 传输数据
• 存储需求
• 定义主键
• 性能考虑
• 理解innodb表分配的文件

5.1 调整MyISAM和Innodb的内存使用

当你从MyISAM表过渡的时候，设置较小的key_buffer_size可以释放内存。增加innodb_buffer_pool_size的大小可以提高转换后的innodb表的查询性能。另外，请关闭查询缓存，因为这个功能在一台频繁写的节点上会降低节点性能，8.0已废弃【查询缓存】。

5.2 处理长事务和短事务

MyISAM不支持事务，所以你应该更关注autocommit配置项，它对innodb表的读写性能影响重大。
长事务的undolog和redolog会占用较多内存，短事务会造成很多磁盘I/O，因为redo log几乎每次提交事务时都会刷盘。

5.3 处理死锁

只有Innodb会产生死锁。当两个（以上）事务以不同的顺序多次修改相同的表时，就容易产生死锁。Innodb通过参数innodb_deadlock_detect默认开启死锁检测，检测到死锁时会直接回滚事务；如果关闭，则在innodb_lock_wait_timeout超时后回滚并报错。
无论哪种方式，你都需要分析并解决这个死锁，因为sql是你写的，你写的有问题才会导致死锁。（oh，这听起来有些(⊙︿⊙)）。
一般来说，我们通过SHOW ENGINE INNODB STATUS来查看上一次产生的死锁日志。但我们还可以通过打开innodb_print_all_deadlocks来打印跟踪所有死锁。

5.4 存储布局

为了尽可能提高的Innodb表性能，你还可以调整关于存储布局的几个参数。它们是innodb_file_per_table, innodb_file_format, and innodb_page_size，还有建表语句中的ROW_FORMAT and KEY_BLOCK_SIZE。
如果你刚接触Innodb，那对你来说最重要的参数是innodb_file_per_table，这在5.6.6是默认开启，它允许OS在表被truncate或drop时回收空间，它还有其他好处，参考这里。

你还可以将Innodb表存在通用表空间，这也会带来一些好处，上文讲过不再赘述。

5.5 转换一张已存在的表

ALTER TABLE table_name ENGINE=InnoDB;

🐖：不要这样去修改自带的mysql库内的系统表，这是不支持的，既然说了你就不要去尝试了~

5.6 克隆表结构

你可能想要创建一张MyISAM表的克隆表，而不是立即修改其引擎。
操作步骤是：

• 创建一个一样表结构的innodb表，包含索引（可以导入数据后创建，这会稍快一点）
• 用insert into innodb_t2 select * from myisam_t1 order by pk_col导入数据
  如果有唯一索引，可以暂时关闭唯一性检查：

SET unique_checks=0;
... import operation ...
SET unique_checks=1;

对于大表，这可以节省磁盘I/O，因为innodb使用change buffer来缓存二级索引的更改。关闭唯一性检查可以充分利用change buffer的优势。
在大表转换时，我们可以通过增加Innodb buffer pool的大小来减少磁盘I/O。推荐的buffer pool大小是主机内存的50%~75%。另外，我们也可以增加Innodb log buffer的大小，这也可以减少磁盘I/O，增加事务性能。

5.7 存储需求

如果你是临时克隆MyISAM表到Innodb表，那你最好在file-per-table的tablespace下创建表，以便drop的时候回收空间。
无论你是直接改表还是克隆，都要确保你的磁盘空间够用，innodb表会比myisam表占用更多空间，空间不足会导致rollback，大表的rollback会非常慢，对于DML操作有buffer和change buffer加速，但rollback是没有任何加速机制的。

官方建议，如果你的数据无价值可以直接kill进程，对于完整的操作步骤，参考这里。

5.8 定义主键

定义主键是对mysql性能和表以及索引空间占用影响的一个重要因素，主键唯一标识了每一行记录，每个表都应该有一个主键。定义主键有如下注意事项：

• 通过CREAT TABLE语句添加主键，而不是ALTER TABLE
• 尽量选择整型字段作为主键，可以让数据更紧凑，大表能节省更多空间；同时因为主键也存在于二级索引，如果表包含二级索引也能节省二级索引的空间占用；另外，buffer pool能缓存的数据也更多，更好的提高整体性能
• 考虑对主键使用一个自增属性，如果其他稳定、唯一、非空的整型字段可用
• 尽量不要修改主键的值，这会导致聚簇索引、二级索引的重排

如果表已经有一个非整型的主键，也建议添加一个整型自增列并将主键切换为它。原因和上面一致，节省二级索引空间。

最好，如果你还是没有给表设置主键，MySQL会自动给一个隐藏的6字节的主键ROWID，你是看不到的。6字节可能比你需要的更长，而且你也无法将ROWID作为查询条件，所以记得给个合理的主键。

5.9 应用性能考虑

官网手册中，此节讲的是整型主键在JOIN场景性能比字符型主键更好，不再详述。

5.10 理解Innodb表的物理文件布局

它比MyISAM表更复杂，物理结构也是。首先，我们不能手动删除ibdata文件，这个文件代表的是system表空间；如果要迁移或复制表，参考上面的第四小节。

6. Innodb中的自增

Innodb提供一种锁机制来显著提高INSERT 语句(含自增列的表)的扩展性和性能，但使用这个机制有个前提，那就是必须将自增列定义为某些索引的第一列或唯一列。背后的原因是方便mysql对表索引执行select max(auto_incr_col)来得到目前的最大自增列值。
这些索引最好是主键或唯一索引，但非必须，而且最好不要有重复值。

6.1 Innodb自增锁模式

自增锁的作用是生成自增列的新值，同时还会影响mysql复制功能，相关设置变量是innodb_autoinc_lock_mode，在讲这个变量用法前先讲一下相关术语：

• insert语句
  包含insert，insert...select，replace，replace_select， 以及load data；另外还包含了下面的三种insert类型
• 简单insert
  指的是一个条插入sql欲插入的行数能够提前算出，比如单行插入和多行插入，不含嵌套子查询的replace，但除了insert ... on duplicate key update
• 批量插入
  指的是一个条插入sql欲插入的行数不能够提前算出，比如insert...select，replace_select，load data。这种情况下，Innodb就只能在处理每一行数据时分配新的自增列值。
• 混合模式插入
  指的是一条多行插入sql，部分行指定了自增值。比如：INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');，其中c1是自增列。另一种是insert ... on duplicate key update，这是性能最差的情况，因为要根据是否update的情况来决定是否分配新的自增列值。

好了，术语讲完了，现在讲讲变量有哪些选项：

• 0，traditional（传统）
• 1，consecutive（连续）
• 2，interleaved（交错）

第一个，0，传统模式，这里不纠结它的名字，直接说表现。这个模式下，所有的insert语句都将获得一个表级的AUTO-INC锁，在语句执行后释放（但早于事务提交），这样来确保自增值的正确、按插入顺序分配。
在基于statement的复制方式的场景中，副节点也可以通过sql重放产生相同的数据。tradition模式的优点是在基于statement的复制方式的场景中也能够保证数据的一致性，缺点是并发写就变成了顺序写，写性能低。

第二个，1，连续模式，默认配置 。此模式下只有上面说的批量插入才会持有表级 AUTO-INC 锁。如果源表和目标表不一致，则会在从源表中选择的第一行上取得共享锁之后，在目标表上取得 AUTO-INC 锁；如果源表和目标表是同一张表，则在源表选择的所有行上取得共享锁之后，再取得该表的AUTO-INC 锁。

对于简单insert的情况，仅在有另一个事务取得AUTO-INC 锁时，它才会去获取AUTO-INC 锁。
博主理解：连续模式下，批量插入不光和批量插入的事务不能并发，也不能和简单insert并发执行。只有简单insert和简单insert才能并发。

此模式能够保证的是，对于插入行数不确定的Insert sql，所分配的自增列值也是连续的，对基于statement复制的环境是安全的。
简单的说，相比传统模式，这个模式显著提高了性能和扩展性，同时能够在基于statement复制的环境下安全运行。
还有一种情况是混合模式插入的第一种情况，也就是那种批量插入，部分行指定了自增值的sql，比如
insert into t1 (auto_col) values (1), (null)，插入时自增计数器会预分配数量等于待插入行数的多个自增值，但因为部分行指定了自增值，这就造成了一些浪费，这可能是一个小问题。

第三个，2，交错模式。该模式下，任何insert语句都不会获取AUTO-INC锁，所以并发插入的性能最好。但是在基于statement复制的环境 或者 通过binlog 进行恢复数据的情况下是不安全的。
这个模式也能够保证自增值的唯一和单调递增，但还要想并发生成自增值，就得采用交错式的生成方式，这样做的结果是多条语句的自增值可能不是连续的，比如1,3,5，但也只是个小问题。
做个小结，该模式下针对简单insert的sql生成的自增值是连续的；对于批量插入和混合模式插入sql则可能是有间隙的。

6.1 自增锁使用影响

6.1.1 对基于statement方案的复制环境

innodb_autoinc_lock_mode 需要设置为0或1才是安全的，如果设为2会导致复制后的数据可能不一致。
如果是基于Row或Mix的方案，则2也是ok的。

6.1.2 “丢失”的自增值

已经生成的自增值无法收回。
意思是，如果你的insert语句rollback了，已经生成的自增值也无法收回，自增计数器仍然会累计。

6.1.3 插入行时将自增列设置null或0

这种情况下，Innodb会当做没设置，会生成一个自增值给它。

6.1.4 插入行时将自增列设置负数

可以插入负数

6.1.5 自增计数器即将溢出字段类型

create table t1 (
    id tinyint auto_increment unique
);
insert into t1 values (127),(null); -- Duplicate entry '127' for key 'id'

要记住一点，自增列的新值计算方式是：select max(auto_incr_col)，但不会溢出字段类型。

6.1.6 批量插入时的自增间隙

当innodb_autoinc_lock_mode =0时，自增值不会有间隙，因为会获取表级的AUTO-INC锁，一个时刻只执行一条insert语句。
当innodb_autoinc_lock_mode =1时，简单insertsql自增值也不会有间隙，但是对于批量插入，则可能会产生间隙，因为无法提前准确计算出待插入的行数。
当innodb_autoinc_lock_mode =2时，则在批量插入时很可能会产生间隙，因为要允许并发写入。并且也有批量插入的问题。

6.1.7 在连续的insert语句执行过程中修改自增列字段的值

create table t1 (
    id tinyint auto_increment unique
);

insert into t1 values (1);
update t1 set id = 2 where id=1;
insert into t1 values (0); -- Duplicate entry '2' for key 'id'

这个Duplicate错误报了之后，自增计数器会+1，再试一次就可以成功执行。但是如果你修改了多行的自增列，并且是比计数器更大的值，比如插入的是1,2,3（计数器现在为4），你update为4,5,6，那么你接下来的连续3次insert into t1 (0) 都会报错！自增计数器每次只会+1，除非你手动指定了一个大于等于当前自增列最大值的值，这个时候，计数器才会更新为你指定的值，因为你指定的值是表中最大的。

这个问题的原因是update语句不会更新自增计数器，并且触发Duplicate错误并不会立即更新计数器为当前表中最大值+1，还得要在插入时手动指定一个大于等于当前自增列最大值的自增值才会修复此问题。

这个场景提醒我们：尽量不要去更新自增列！！！

6.2 Innodb自增计数器初始化

Innodb的数据字典中会为每个具有自增列的表维护一个自增计数器，用于生成新的自增值。

注意：这个计数器存在内存中，不是磁盘，在实例重启后，会自动执行类似下面sql的操作来重设计数器：
SELECT MAX(auto_incr_col)+1 FROM table_name FOR UPDATE;
这也是常说的自增计数器回溯问题！

auto_increment_increment和auto_increment_offset 两个参数用于设置自增的步长和起始值，默认都是1。但需注意，官网手册注明，当offset>incr时，offset不生效，原因没讲。另外，这两个参数可设置gloabl/session级别，可以实时生效。
同时在建表时也可以设置自增起始值，这会优先于前面2个参数。

create table t1 (
    id tinyint auto_increment unique
) auto_increment=100;

计数器的更新策略：如果插入行时你指定了一个大于当前计数器的值，插入后计数器将更新为你指定的值。
最后，mysql重启后还会取消CREATE TABLE或ALTER TABLE语句中AUTO_INCRMENT=N的效果，这句话意思是重启后自增计数器=select max(id)+1，而AUTO_INCRMENT=N则完全失效。

这个问题在8.0已经修复，计数器会存入到磁盘中