第15章InnoDB存储引擎

被最重要的查询引用。

永远不会留空。

永远不会有重复的值。

很少，如果一旦插入就改变价值。

如果您正在使用 mysql 会话进行交互式实验，请 COMMIT 在完成后 始终 （完成更改）或 ROLLBACK （撤消更改）。 关闭交互式会话，而不是让它们长时间保持打开状态，以避免事务长时间保持交易。

确保应用程序中的任何错误处理程序也未 ROLLBACK 完成更改或 COMMIT 已完成更改。

ROLLBACK 是一个相对昂贵的操作，因为 INSERT ， UPDATE 和 DELETE 操作被写入 InnoDB 表之前 COMMIT ，期望大多数更改成功提交并且回滚很少。 在试验大量数据时，请避免对大量行进行更改，然后回滚这些更改。

使用一系列 INSERT 语句 加载大量数据时 ，请定期 COMMIT 检查结果，以避免持续数小时的事务。 在数据仓库的典型加载操作中，如果出现问题，则截断表（使用 TRUNCATE TABLE ）并从头开始而不是执行 ROLLBACK 。

对于 InnoDB 表 上的大多数操作 ，您应该使用该设置 autocommit=0 。 从效率的角度看，这样就避免了在发出大量连续的不必要的I / O INSERT ， UPDATE 或 DELETE 语句。 从安全角度来看， ROLLBACK 如果您在 mysql 命令行或应用程序中的异常处理程序中 出错 ，这允许您发出 声明以恢复丢失或乱码的数据 。

autocommit=1 适用于 InnoDB 表 的时间 是在运行一系列查询以生成报告或分析统计信息时。 在这种情况下，没有与 COMMIT 或 相关的I / O代价 ROLLBACK ，并且 InnoDB 可以 自动优化只读工作负载 。

如果进行了一系列相关更改，请在最后一次完成所有更改 COMMIT 。 例如，如果将相关的信息片段插入到多个表中，请 COMMIT 在进行所有更改后 执行单个操作 。 或者，如果您运行多个连续 INSERT 语句，请 COMMIT 在加载所有数据后 执行单个语句 ; 如果您正在进行数百万条 INSERT 语句，可能会通过发出 COMMIT 每万或数十万条记录来 分割 大额交易，因此交易不会变得太大。

请记住，即使 SELECT 语句 也会 打开一个事务，因此在交互式 mysql 会话中 运行某些报表或调试查询后 ，可以发出 COMMIT 或关闭 mysql 会话。

PRIMARY KEY 为每个表 声明一个 。 通常，它是 WHERE 查找单行时 在 子句中 引用的最重要的列 。

PRIMARY KEY 在原始 CREATE TABLE 语句中 声明该 子句 ，而不是稍后通过 ALTER TABLE 语句 添加它 。

仔细选择列及其数据类型。 首选数字列而不是字符或字符串。

如果没有要使用的另一个稳定，唯一，非空的数字列，请考虑使用自动增量列。

如果对主键列的值是否可能发生变化有任何疑问，自动增量列也是一个不错的选择。 更改主键列的值是一项昂贵的操作，可能涉及重新排列表中和每个二级索引中的数据。

您不能删除 代表 系统表空间 的 ibdata文件 。 InnoDB

第15.6.1.2节“移动或复制InnoDB表” InnoDB 中介绍了 将 表 移动或复制 到不同服务器的方法 。

“ INSERT 似的 ” 陈述

在表中生成新的行中的所有语句，包括 INSERT ， INSERT ... SELECT ， REPLACE ， REPLACE ... SELECT ，和 LOAD DATA 。 包括 “ 简单插入 ” ， “ 批量插入 ” 和 “ 混合模式 ” 插入。

“ 简单插入 ”

可以预先确定要插入的行数的语句（最初处理语句时）。 这包括单行和多行 INSERT 以及 REPLACE 没有嵌套子查询的语句，但不包括 INSERT ... ON DUPLICATE KEY UPDATE 。

“ 批量插入 ”

预先不知道要插入的行数（以及所需的自动增量值的数量）的语句。 这包括 INSERT ... SELECT ， REPLACE ... SELECT 和 LOAD DATA 声明，但不是简单的 INSERT 。 在处理每一行时，一次为列 InnoDB 分配新值 AUTO_INCREMENT 。

“ 混合模式插入 ”

这些是 “ 简单插入 ” 语句，它指定一些（但不是全部）新行的自动增量值。 下面 c1 是一个 示例，其中 是一 AUTO_INCREMENT 列表 t1 ：

INSERT INTO t1（c1，c2）VALUES（1，'a'），（NULL，'b'），（5，'c'），（NULL，'d'）;

另一种类型的 “ 混合模式插入 ” 是 INSERT ... ON DUPLICATE KEY UPDATE ，在最坏的情况下实际上是 INSERT 后跟a UPDATE ，其中 AUTO_INCREMENT 在更新阶段期间可以使用或不使用列 的分配值 。

innodb_autoinc_lock_mode = 0 （ “ 传统 ” 锁定模式）

传统的锁定模式提供了与 innodb_autoinc_lock_mode MySQL 5.1中引入配置参数 之前相同的行为 。 传统的锁定模式选项用于向后兼容，性能测试以及解决“混合模式插入”问题，因为语义可能存在差异。

在此锁定模式下，所有 “ INSERT-like ” 语句都会获得一个特殊的表级 AUTO-INC 锁，以便插入带有 AUTO_INCREMENT 列的表中。 此锁通常保持在语句的末尾（而不是事务的结尾），以确保为给定的 INSERT 语句 序列以可预测和可重复的顺序分配自动增量值 ，并确保自动增量值由任何给定的声明分配是连续的。

对于基于语句的复制，这意味着在从属服务器上复制SQL语句时，自动增量列使用的值与主服务器上的值相同。 执行多个 INSERT 语句 的结果 是确定性的，并且从站再现与主站上相同的数据。 如果多个 INSERT 语句 生成的自动递增值 是交错的，则两个并发 INSERT 语句 的结果 将是不确定的，并且无法使用基于语句的复制可靠地传播到从属服务器。

为清楚起见，请考虑使用此表的示例：

CREATE TABLE t1（
  c1 INT（11）NOT NULL AUTO_INCREMENT，
  c2 VARCHAR（10）DEFAULT NULL，
  主要关键（c1）
）ENGINE = InnoDB;

假设有两个事务正在运行，每个事务都将行插入带有 AUTO_INCREMENT 列 的表中 。 一个事务使用 INSERT ... SELECT 插入1000行 的 语句， 另一个事务使用 插入一行的简单 INSERT 语句：

Tx1：INSERT INTO t1（c2）从另一个表中选择1000行...
Tx2：INSERT INTO t1（c2）VALUES（'xxx'）;

InnoDB 事先无法判断从 Tx1 SELECT 中的 INSERT 语句中 检索了多少行 ，并且 随着 语句的进行，它会一次分配一个自动增量值。 使用保持在语句末尾的表级锁定，一次只能 执行 一个 INSERT 引用表的语句 t1 ，并且不会交错生成不同语句的自动增量数。 由Tx1 INSERT ... SELECT 语句 生成的自动递增值 是连续的，并且由（单个）自动递增值使用 INSERT Tx2中的语句要小于或大于用于Tx1的语句，具体取决于首先执行的语句。

只要SQL语句在从二进制日志重放时（在使用基于语句的复制时或在恢复方案中）以相同的顺序执行，结果与首次运行Tx1和Tx2时的结果相同。 因此，表级锁定一直持续到语句结束时， INSERT 使用自动增量安全 的语句可以 安全地用于基于语句的复制。 但是，当多个事务同时执行insert语句时，这些表级锁限制了并发性和可伸缩性。

在前面的示例中，如果没有表级锁定，则用于 INSERT Tx2 中的自动增量列的值 取决于语句执行的时间。 如果 INSERT Tx2在 INSERT Tx1运行时执行（而不是在启动之前或完成之后），则由两个 INSERT 语句 分配的特定自动增量值 是不确定的，并且可能因运行而异。

在 连续 锁定模式下， InnoDB 可以避免将表级 AUTO-INC 锁定用于 预先知道行数的 “ 简单插入 ” 语句，并且仍然保留基于语句的复制的确定性执行和安全性。

如果您不使用二进制日志作为恢复或复制的一部分重放SQL语句，则 交错 锁定模式可用于消除表级 AUTO-INC 锁的 所有使用，从而 实现更高的并发性和性能，但代价是允许自动存在空白 - 由语句分配的增量编号，可能具有由并发执行的语句交错分配的编号。

innodb_autoinc_lock_mode = 1 （ “ 连续 ” 锁定模式）

在此模式下， “ 批量插入 ” 使用特殊的 AUTO-INC 表级锁定并保持它直到语句结束。 这适用于所有 INSERT ... SELECT ， REPLACE ... SELECT 和 LOAD DATA 语句。 只有一个持有 AUTO-INC 锁的 语句 可以一次执行。 如果批量插入操作的源表与目标表不同，则 AUTO-INC 在从源表中选择的第一行上执行共享锁之后，将对目标表执行锁定。 如果批量插入操作的源和目标是同一个表，则 AUTO-INC 在对所有选定的行执行共享锁定后执行锁定。

“ 简单插入 ” （预先知道要插入的行数） AUTO-INC 通过在互斥锁（轻量级锁定）的控制下获得所需数量的自动增量值来 避免表级 锁定在分配过程的持续时间内保持， 不 直到语句完成。 AUTO-INC 除非 AUTO-INC 另一个事务持有 锁，否则 不使用表级 锁 。 如果另一个事务持有 AUTO-INC 锁，则 “ 简单插入 ” 等待 AUTO-INC 锁定，就像它是 “ 批量插入 “ 。

此锁定模式确保在存在 INSERT 未提前知道行数 的 语句（以及在语句进行时指定自动增量编号）的情况下，由任何 “ INSERT -like ” 语句 指定的所有自动增量值 都是连续，并且操作对于基于语句的复制是安全的。

简而言之，这种锁定模式显着提高了可伸缩性，同时可以安全地使用基于语句的复制。 此外，与 “ 传统 ” 锁定模式一样，由任何给定语句分配的自动递增数字是 连续的 。 有 没有变化 在语义比较 “ 传统 ” 对于使用自动递增，有一个重要的例外，任何声明模式。

“ 混合模式插入 ” 的例外情况是， 用户为 AUTO_INCREMENT 多行 “ 简单插入 ”中的 某些行（但不是所有行） 提供 列的 显式值 。 对于此类插入，请 InnoDB 分配比要插入的行数更多的自动增量值。 但是，自动分配的所有值都是连续生成（因此高于）最近执行的先前语句生成的自动增量值。 “ 超额 ” 号码丢失了。

innodb_autoinc_lock_mode = 2 （ “ 交错 ” 锁定模式）

在此锁定模式下，没有 “ INSERT -like ” 语句使用表级 AUTO-INC 锁，并且多个语句可以同时执行。 这是最快且最具扩展性的锁定模式，但 在从二进制日志重放SQL语句时使用基于语句的复制或恢复方案时 ，这是 不安全的 。

在这种锁定模式下，自动增量值保证是唯一的，并且在所有同时执行的 “ INSERT 类似 ” 语句中 单调递增 。 但是，因为多个语句可以同时生成数字（即，数字的分配 在语句之间 交错 ），所以为任何给定语句插入的行生成的值可能不是连续的。

如果执行的唯一语句是 “ 简单插入 ” ，其中要插入的行数是提前知道的，则除了 “ 混合模式插入 ” 之外，单个语句生成的数字没有间隙 。 但是，当 执行 “ 批量插入 ” 时 ，任何给定语句分配的自动增量值可能存在间隙。

使用复制自动增量

如果使用基于语句的复制，请设置 innodb_autoinc_lock_mode 为0或1，并在主服务器及其从服务器上使用相同的值。 如果使用 innodb_autoinc_lock_mode = 2（ “ 交错 ” ）或主站和从站不使用相同锁定模式的配置， 则不确保从站上的自动增量值 与主站上的相同。

如果使用基于行或混合格式的复制，则所有自动增量锁定模式都是安全的，因为基于行的复制对SQL语句的执行顺序不敏感（并且混合格式使用基于行的方式）复制任何对基于语句的复制不安全的语句。

“ 丢失 ” 自动递增值和序列间隙

在所有锁定模式（0,1和2）中，如果生成自动增量值的事务回滚，则这些自动增量值将 “ 丢失 ” 。 一旦为自动增量列生成了值，就无法回滚它，无论 “ INSERT -like ” 语句是否完成，以及是否回滚包含的事务。 这些丢失的值不会被重用。 因此，存储在 AUTO_INCREMENT 表 的 列 中的值可能存在间隙 。

为 AUTO_INCREMENT 列 指定NULL或0

在所有的锁模式（0，1，2），如果用户指定NULL或0用于 AUTO_INCREMENT 在列 INSERT ， InnoDB 将行仿佛没有指定值，并且生成用于它的新值。

为 AUTO_INCREMENT 列 指定负值

在所有锁定模式（0,1和2）中，如果为 AUTO_INCREMENT 列 指定负值，则不会定义自动增量机制的行为 。

如果该 AUTO_INCREMENT 值大于指定整数类型的最大整数

在所有锁定模式（0,1和2）中，如果值大于可以存储在指定整数类型中的最大整数，则不会定义自动增量机制的行为。

“ 批量插入 ”的 自动增量值的差距

与 innodb_autoinc_lock_mode 设定为0（ “ 传统 ” ）或1（ “ 连续 ” ），通过任何给定语句生成的自动递增的值是连续的，没有间隙，这是因为表级 AUTO-INC 锁一直保持到该语句的末尾，只有一个这样的语句可以一次执行。

与 innodb_autoinc_lock_mode 设置为2（ “ 交织 ” ），有可能是在所产生的自动递增值间隙 “ 批量插入 ， ” 但只有当有同时执行的 “ INSERT 样 ” 的语句。

对于锁定模式1或2，在连续语句之间可能出现间隙，因为对于批量插入，可能不知道每个语句所需的确切数量的自动增量值，并且可能过高估计。

由 “ 混合模式插入 ” 指定的自动递增值

考虑 “ 混合模式插入 ” ，其中 “ 简单插入 ” 指定某些（但不是全部）结果行的自动增量值。 这种语句在锁定模式0,1和2中表现不同。例如，假设 c1 是一 AUTO_INCREMENT 列表 t1 ，并且最近自动生成的序列号是100。

mysql> CREATE TABLE t1 (
    - > c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    - > c2 CHAR(1)
    - >) ENGINE = INNODB;

现在，考虑以下 “ 混合模式插入 ” 语句：

MySQL的> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

随着 innodb_autoinc_lock_mode 设置为0（ “ 传统 ” ），这四个新的行是：

MySQL的> SELECT c1, c2 FROM t1 ORDER BY c2;
+ ----- + ------ +
| c1 | c2 |
+ ----- + ------ +
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+ ----- + ------ +

下一个可用的自动增量值为103，因为自动增量值一次分配一个，而不是在语句执行开始时一次分配。 无论是否同时执行 “ INSERT -like ” 语句（任何类型）， 此结果都是正确的 。

随着 innodb_autoinc_lock_mode 设置为1（ “ 连续 ” ），这四个新行也：

MySQL的> SELECT c1, c2 FROM t1 ORDER BY c2;
+ ----- + ------ +
| c1 | c2 |
+ ----- + ------ +
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+ ----- + ------ +

但是，在这种情况下，下一个可用的自动增量值是105，而不是103，因为在处理语句时分配了四个自动增量值，但只使用了两个。 无论是否同时执行 “ INSERT -like ” 语句（任何类型）， 此结果都是正确的 。

与 innodb_autoinc_lock_mode 设定为模式2（ “ 交织 ” ），四个新的行是：

MySQL的> SELECT c1, c2 FROM t1 ORDER BY c2;
+ ----- + ------ +
| c1 | c2 |
+ ----- + ------ +
| 1 | a |
|   x| b |
| 5 | c |
|   y| d |
+ ----- + ------ +

的值 x 和 y 是独一无二的，比任何先前产生的行大。 然而，具体的数值 x ，并 y 依赖于并行执行语句生成自动递增值的数量。

最后，考虑以下语句，在最近生成的序列号为100时发出：

MySQL的> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');

对于任何 innodb_autoinc_lock_mode 设置，此语句将生成重复键错误23000（ Can't write; duplicate key in table ），因为为行分配了101并且行的 (NULL, 'b') 插入 (101, 'c') 失败。

AUTO_INCREMENT 在一系列 INSERT 语句 的中间 修改 列值

在MySQL 5.7及更早版本中，修改 语句 AUTO_INCREMENT 序列中间 的 列值 INSERT 可能会导致 “ 重复条目 ” 错误。 例如，如果执行的 UPDATE 操作将 AUTO_INCREMENT 列值 更改为 大于当前最大自动增量值的值，则 INSERT 未指定未使用的自动增量值的 后续 操作可能会遇到 “ 重复条目 ” 错误。 在MySQL 8.0及更高版本中，如果您修改了 AUTO_INCREMENT 如果列值大于当前最大自动增量值，则新值将保持不变，后续 INSERT 操作将从新的较大值开始分配自动增量值。 以下示例演示了此行为。

mysql> CREATE TABLE t1 (
    - > c1 INT NOT NULL AUTO_INCREMENT,
    - > PRIMARY KEY (c1)
    - >  ) ENGINE = InnoDB;

MySQL的> INSERT INTO t1 VALUES(0), (0), (3);

MySQL的> SELECT c1 FROM t1;
+ ---- +
| c1 |
+ ---- +
| 1 |
| 2 |
| 3 |
+ ---- +

MySQL的> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

MySQL的> SELECT c1 FROM t1;
+ ---- +
| c1 |
+ ---- +
| 2 |
| 3 |
| 4 |
+ ---- +

MySQL的> INSERT INTO t1 VALUES(0);

MySQL的> SELECT c1 FROM t1;
+ ---- +
| c1 |
+ ---- +
| 2 |
| 3 |
| 4 |
| 5 |
+ ---- + 

InnoDB 允许外键引用任何索引列或列组。 但是，在引用的表中，必须有一个索引，其中引用的列是 同一顺序 中的 第一 列。 InnoDB 还会考虑添加到索引的 隐藏列 （请参见 第15.6.2.1节“聚簇和二级索引” ）。

InnoDB 当前不支持具有用户定义分区的表的外键。 这意味着没有用户分区的 InnoDB 表可能包含外键引用的外键引用或列。

InnoDB 允许外键约束引用非唯一键。 这是 InnoDB 标准SQL 的 扩展。

虽然 SET DEFAULT MySQL服务器允许，但它被拒绝为无效 InnoDB 。 CREATE TABLE 并 ALTER TABLE 利用这一条款语句不得用于InnoDB表。

如果父表中有多个行具有相同的引用键值，则 InnoDB 在外键检查中执行操作，就好像其他具有相同键值的父行不存在一样。 例如，如果已定义 RESTRICT 类型约束，并且存在具有多个父行的子行， InnoDB 则不允许删除任何这些父行。

InnoDB 基于对应于外键约束的索引中的记录，通过深度优先算法执行级联操作。

如果 ON UPDATE CASCADE 或者 ON UPDATE SET NULL recurses更新 它在级联期间先前已 更新的 同一个表 ，它的作用就像 RESTRICT 。 这意味着您不能使用自引用 ON UPDATE CASCADE 或 ON UPDATE SET NULL 操作。 这是为了防止级联更新导致的无限循环。 自引用的 ON DELETE SET NULL ，在另一方面，是可能的，因为是自引用 ON DELETE CASCADE 。 级联操作可能不会嵌套超过15级。

与MySQL一样，在一个SQL语句中， 逐行 插入，删除或更新许多行， InnoDB 检查 UNIQUE 和 FOREIGN KEY 约束。 执行外键检查时， InnoDB 在必须查看的子记录或父记录上设置共享行级锁。 InnoDB 立即检查外键约束; 检查不会延迟到事务提交。 根据SQL标准，默认行为应该是延迟检查。 也就是说，只有在处理 完整个SQL语句 后才会检查约束 。 直到 InnoDB 实现延迟约束检查，有些事情是不可能的，例如删除使用外键引用自身的记录。