MySQL5.6版本InnoDB存储引擎在线DDL变更的官方信息中文翻译版

5.5 InnoDB存储引擎表在线DDL

5.5.1．在线DDL概述

以前，InnoDB存储引擎表的许多DDL操作代价是非常昂贵的。许多ALTER TABLE操作的原理是通过创建新的空表，定义被要求的表选项和索引，然后逐行拷贝已存在记录到新表，在插入行时更新索引。在旧表所有行被拷贝完，旧表被删除和那新表被重命名为旧的表名。    MySQL5.5,和MySQL5.1 有了InnoDB Plugin,优化了CREATE INDEX和DROP INDEX 避免表的拷贝行为。这个特性被称为Fast index Creation。MySQL 5.6 加强ALTER TABLE操作许多方式来避免拷贝表。另外加强的允许SELECT查询和INSERT, UPDATE,和DELETE(DML)语句被处理当该表正被修改时。这些功能的组合现在被称为online DDL。

这种新的机制还意味着你可以普通加快创建和加载表和在创建表时,没有任何二级索引,然后在数据被加载后再添加二级索引的整个过程的速度。

虽然在CREATE INDEX 或 DROP INDEX命令没有语法的变化要求,但一些因素影响性能,空间的使用,和操作的语义(见5.5.9节,“在线DDL极限性”)。

MySQL 5.6在线DDL的增强改善许多DDL操作,以前需要复制表,阻塞在表上DML操作或两者兼而有之。表5.9,“DDL操作的在线状态总结”显示了ALTER Table语句的变化和展示了在线DDL特性如何适用于每个操作。

• “In-Place?”列显示了哪些操作允许ALGORITHM=INPLACE条款;优选值是“Yes”。
• “Copies Table?”列显示了哪些操作可以避免昂贵的表复制操作;优选值是“No”。这列大多数和“In-Place?”列相反的,除了几个操作允许ALGORITHM=INPLACE但仍然涉及一些数量的表复制。
• “Allows Concurrent DML?”列显示执行哪些操作可以完全在线;优选值是“Yes”。你可以指定LOCK=NONE声明在DDL完全允许并发,但MySQL自动允许这种级别的并发当它是可能时。当并发的DML被允许,并发的查询也总是允许。
• “Allows Concurrent Queries?”列显示了哪些DDL操作允许查询表操作同时进行, 优选值是“Yes”。并发查询允许在所有在线DDL操作。它显示为“Yes”。你可以指定LOCK=SHARED声明并发查询在DDL期间是允许的,但MySQL自动允许这种级别的并发，如果可能。
• “Notes”列解释其他列值为“Yes/No”的任何例外,例如当答案取决于一个配置选项的设置或在DDL语句的其他一些条款。值“Yes*”和“No*”表明答案取决于这些额外的注释事项。

表5.9。对DDL在线操作的状态总结

Operation

In-Place?

Copies Table?

Allows Concurrent DML?

Allows Concurrent Query?

NotesCREATE INDEX, ADD INDEX

Yes*

No*

Yes

Yes

对全文索引的一些限制； 参照下一行。目前，该操作不是in-place(它需要复制表)如果索引将被创建和在ALTER TABLE语句之前删除语句相同ADD FULLTEXT INDEX

Yes

No*

No

Yes

对表创建第一个全文索引包含表复制，除非用户提供了FTS_DOC_ID列。随后该表的全文索引可以被就地（in-place）创建DROP INDEX

Yes

No

Yes

Yes

 对一列设置默认值

Yes

No

Yes

Yes

仅对该表.frm修改不是数据文件。对一列修改auto-increment 的值

Yes

No

Yes

Yes

修改一个值存储在内存而不是数据文件。添加 foreign key constraint

Yes*

No*

Yes

Yes

避免拷贝表，在约束创建时禁用

foreign_key_checks

删除 foreign key constraint

Yes

No

Yes

Yes

foreign_key_checks 选项可用，可不用Rename a column

Yes*

No*

Yes*

Yes

为允许DML并发, 保持相同数据类型，仅改变列名Add a column

Yes

Yes

Yes*

Yes

当添加列是

auto-increment ，不允许DML并发。尽管ALGORITHM=INPLACE 被允许，但数据大幅重组,所以它仍然是一项昂贵的操作。

Drop a column

Yes

Yes

Yes

Yes

尽管ALGORITHM=

INPLACE 被允许, 但数据大幅重组,所以它仍然是一项昂贵的操作。

Reorder columns

Yes

Yes

Yes

Yes

尽管ALGORITHM

=INPLACE 被允许，但数据大幅重组,所以它仍然是一项昂贵的操作。

Change ROW_FORMATproperty

Yes

Yes

Yes

Yes

尽管ALGORITHM

=INPLACE被允许, 但数据大幅重组,所以它仍然是一项昂贵的操作。

ChangeKEY_BLOCK_SIZEproperty

Yes

Yes

Yes

Yes

尽管ALGORITHM

=INPLACE被允许，但数据大幅重组,所以它仍然是一项昂贵的操作。

Make column NULL

Yes

Yes

Yes

Yes

尽管ALGORITHM

=INPLACE被允许，但数据大幅重组,所以它仍然是一项昂贵的操作。

Make column NOT NULL

Yes*

Yes

Yes

Yes

当 SQL_MODE 包含strict_all_tables或strict_all_tables,如果该列包含任何NULL，该操作失败。尽管ALGORITHM

=INPLACE被允许，但数据大幅重组,所以它仍然是一项昂贵的操作。

Change data type of column

No

Yes

No

Yes

 Add primary key

Yes*

Yes

Yes

Yes

尽管 ALGORITHM

=INPLACE被允许，但数据大幅重组,所以它仍然是一项昂贵的操作。ALGORITHM=INPLACE在下列条件是不允许的，如果有列必须转化NOT NULL。参照Example 5.9, “Creating and Dropping the Primary Key”.

Drop primary key and add another

Yes

Yes

Yes

Yes

ALGORITHM=INPLACE 仅当你在同一个ALTER TABLE语句添加新主键是被允许；数据大幅重组,所以它仍然是一项昂贵的操作。Drop primary key

No

Yes

No

Yes

限制的应用，当删除一个主键在同一个ALTER TABLE 语句没有添加新主键Convert character set

No

Yes

No

Yes

如果新的字符集编码不用，重建表。Specify character set

No

Yes

No

Yes

如果新的字符集编码不用，重建表。Rebuild with FORCEoption

No

Yes

No

Yes

等效于ALGORITHM

=COPY 或设置

old_alter_table=1.

以下部分显示了基本的语法和用法说明相关的在线DDL,对每个主要的操作,可以进行DML并发、就地（in-place）、或两者:

二级索引

• 创建 secondary indexes: CREATE INDEX name ON table (col_list) or ALTER TABLE table ADD INDEX name (col_list)。(创建一个全文索引依然需要锁表)
• 删除 secondary indexes: DROP INDEX name ON table; or ALTER TABLE table DROP INDEX name

在InnoDB存储引擎表创建和删除二级索引跳过表复制行为和在MySQL 5.5和MySQL 5.1的InnoDB Plugin 一样。

在MySQL 5.6和更高版本, 当索引在创建或删除时表仍然可以读和写操作。CREATE INDEX 或 DROP INDEX语句只在所有访问表的事务完成才完成,因此, 索引的初始状态反映表最近的内容。此前版本,当索引是在创建或删除，更新表通常导致死锁,撤销该表的INSERT, UPDATE, DELETE语句。

列属性

• 给列设置默认值: ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal or ALTER TABLE tbl ALTER COLUMN colDROP DEFAULT

列的默认值存储在表的 .frm 文件, 不是InnoDB data 目录。

• 对列修改auto-increment 值: ALTER TABLE table AUTO_INCREMENT=next_value;

特别是在一个分布式系统使用复制和分片,你有时会为一个表重置自增计数器为一个特定的值。插入表下一行为其自动递增列使用指定的值。你可能在数据仓库环境中也使用这种技术,你定期清空所有表和重新加载它们,您可以重新启动自动递增顺序从1开始。

• 列重命名: ALTER TABLE tbl CHANGE old_col_name new_col_name datatype

当你保持相同的数据类型和[NOT]NULL属性,只改变列名,这个操作总可以在线执行。

作为增强的一部分,现在您可以重命名一个列，该列为外键约束部分的,以前这是不允许的。外键的定义自动更新使用新的列名称。列重命名参与到外键只适用ALTER TABLE就地（in-place）的模式。如果你使用ALGORITHM=COPY条款,或其他条件使命令在幕后使用ALGORITHM=COPY,ALTER TABLE语句将失败。

外键

• 添加删除一个 外键约束:

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1) REFERENCES tbl2(col2) referential_actions;

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

删除外键可以在在线执行，不管foreign_key_checks是否启用，但在线创建外键需要foreign_key_checks 禁用。

如果你不知道在一个特定的表的外键约束名字,发出以下语句和为每个外键约束条款找到约束名称:

show create table table\G

或者,查询information_schema.table_constraints 表和使用constraint_name和 constraint_type 列来查出外键名字.

由于这种增强,你现在还可以在一个语句中删除一个外键及其相关索引,此前要求严格独立语句顺序:

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

如果 foreign keys 已存在将被修改 (也就是说, child table 包含任何FOREIGN KEY … REFERENCE 子句), 额外的限制适用于在线DDL操作, 甚至那些不直接涉及外键列:

• 子表中在线DDL操作不允许 DML并发(这个限制被评为一个bug和可能被取消。)
• 一个在子表的 ALTER TABLE 也要等到其他事务被提交，如果改变父表引起关联子表的变化的通过一个ON UPDATE或ON DELETE条款使用级联或设置为NULL参数。

同样的方式, 如果表在外键关系中是 父表, 尽管它不包含任何外键子句, 它会等待 ALTER TABLE 完成， 如果 INSERT, UPDATE, 或 DELETE 语句引起在子表ON UPDATE 或 ON DELETE 操作.

注意ALGORITHM=COPY

任何 ALTER TABLE 操作在ALGORITHM=COPY 子句下运行防止 DML并发操作。并发查询还是运行的,即表拷贝操作至少在LOCK=SHARED的并发限制条件总被包含。你可以进一步限制并发等操作 通过指定LOCK=EXCLUSIVE (阻止 DML和 查询).

DML并发但表拷贝也是必须的

一些其它的 ALTER TABLE 操作允许 DML并发, 和比 MySQL 5.5 和更早版本更快:表复制操作优化了,尽管表复制仍然是必需的:

• 对列添加、删除或重新排序。
• 添加或删除 主键.
• 修改表的ROW_FORMAT 或KEY_BLOCK_SIZE 属性。
• 修改列的可空的状态。

注意

由于你的数据库模式有新列、数据类型、约束、索引等演变,保持你的CREATE TABLE语句以获得最新的表定义。即使在线DDL性能改进了,它在开始创建稳定的数据库结构更有效率,而不是创建模式的一部分,然后发出ALTER TABLE语句。

这个指导主要的例外是有大量的数据表二级索引。它通常最有效的是创建指定所有细节除二级索引外的表,加载数据,然后创建二级索引。对外键你可以使用相同的技术 (加载数据首先,然后设置外键)如果你知道初始数据是干净的和在加载过程中不需要数据一致性的检查。

无论CREATE TABLE, CREATE INDEX, ALTER TABLE的什么顺序和类似的语句将一个表放在一起,您可以捕获SQL需要重构当前表的形式通过发出SHOW CREATE TABLE table\G语句显示创建表表 (大写\ G需要整洁的格式)。该输出显示条款如数值精度, NOT NULL,和CHARACTER SET,有时在幕后被添加，而且你可能遗漏，当在一个新系统克隆表或设置外键列具有相同类型。

5.5.2.Online DDL性能和并发注意事项

Online DDL提高了MySQL的几个方面的操作,如性能、并发性、可用性和可伸缩性：

• 因为可以查询和DML操作表当表进行DDL过程中,应用程序访问表的反应速度更快。减少锁和等待MySQL服务其他资源会导致更大的可伸缩性,即使对不涉及被修改表的操作。
• 对就地(in-place)的操作, 在DDL操作期间通过避免磁盘I / O和CPU周期来重建表,最小化了整个数据库上的负载和保持良好的性能和高吞吐量。
• 对就地操作,因为比复制所有的数据有更少的数据读入buffer pool,避免经常访问的数据从内存中清除,以前在DDL操作后可能导致临时性能下降。

对Online DDL锁选项

当InnoDB表被DDL操作修改,表可能会或可能不会被锁定,这取决于操作的内部原理,和ALTER table语句的锁子句。默认情况下,MySQL使用尽可能少的锁在DDL操作期间;您指定子句要么使锁比通常有更多的限制 (因此限制DML并发、或DML和查询),要么确保锁的一些预期程度允许对该操作。如果锁子句指定了锁的一个级别,不能用于特定类型的DDL操作,如在创建或删除主键时指定LOCK=SHARED 或LOCK=NONE,该条款的工作就像一个声明,导致语句因错误而失败。下面的列表显示了锁子句不同的可能性,从最宽容到最严格:

• 对于有LOCK= NONE的DDL操作,无论是查询和DML并发是允许的。如果这种DDL操作在要求锁的类型下不能完成，这子句使得ALTER TABLE失败,所以指定LOCK= NONE是否保持表完全可用是至关重要的, 如果这DDL不能指定这种类型，它可以取消DDL的。例如,您可能在对包含客户注册或购买的表使用此条款，以避免错误地发放一个昂贵的ALTER TABLE语句使这些表不可用。
• 对于有LOCK=DEFAULT 的DDL操作，任何对该表的写操作（也就是DML操作）被阻塞，但可以对该表读取。如果这种DDL操作在要求锁的类型下不能完成，这子句使得ALTER TABLE失败，所以指定LOCK= SHARED是否保持表可用于查询是至关重要的, 如果这DDL不能指定这种类型，它可以取消DDL的。例如,您可能使用此条款对数据仓库中表的DDL,在那里它可以延迟数据加载操作直到DDL结束,但查询是不能长时间延迟。
• 对于有LOCK=DEFAULT的DDL操作, 或省略锁子句,MySQL使用这种操作的最低级别的锁 ,允许并发查询,DML、或两者都有可能。这种设置用于当你做了预先计划,预先测试变化,你将知道根据表的负载不会引起任何的可用性问题,
• 对于有 LOCK=EXCLUSIVE的DDL操作,查询和DML操作都被阻塞。如果这种DDL操作在要求锁的类型下不能完成，这子句使得ALTER TABLE失败，所以指定LOCK=EXCLUSIVE是否主要关注的是在最短的时间内完成DDL成为可能,它可以使应用程序等待当它们试图访问表。你可能也使用LOCK=EXCLUSIVE如果服务器是空闲的,为了避免访问的表出现意外。

对InnoDB表ONLINE DDL语句总是等待当前访问表执行的事务提交或回滚,因为当DDL语句正在准备，它在短暂时间内需要独占访问表。同样, 在DDL完成之前，它需要在短暂的时间内独占访问表。因此,一个ONLINE DDL语句在这过程中等待任何已发起,查询或修改表的事务提交或回滚。

因为有一些处理工作涉及到由当前DML对记录所做的修改,然后在最后应用这些改变,一个ONLINE DDL操作可能需要更长的时间比旧式阻止来自其他对表的访问的机制。减少原料（raw）性能来平衡更好的响应应用程序使用的表。当对改变表结构来评估理想的技术,考虑终端用户感知的性能,基于对web页面加载时间等因素。

一个新创建的InnoDB二级索引仅包含在CREATE INDEX 或 ALTER TABLE完成时表已提交的数据。它不包含任何未提交的值,旧版本的值,或标记为删除但没有移除从旧的索引的值。

就地（in-place）与表复制的DDL操作性能对比

ONLINE DDL操作的原始性能很大程度上取决于是否就地进行操作还是需要复制和重建整个表。见Table 5.9, “Summary of Online Status for DDL Operations”看到什么样的操作可以就地进行的,和什么要求避免表复制操作

就地DDL操作性能加速适用于二级索引操作,并不适用于主键。InnoDB表的行存储在一个基于主键的聚集索引组织,形成一些数据库系统称为一个“索引组织表”。因为表结构是如此紧密与主键联系在一起的,重新定义主键仍然需要复制数据。

当一个操作主键使用ALGORITHM=INPLACE,即使数据仍然是复制,它比使用ALGORITHM=COPY 更有效，是因为:

• 对ALGORITHM=INPLACE没有撤消或相关重做日志记录。这些操作开销添加到使用ALGORITHM=COPY的 DDL语句。
• 二级索引条目是预排序的,因此可以按序加载。
• 不用修改缓冲,因为没有随机存取的插入到二级索引。

判断ONLINE DDL操作的相对性能,您可以在一张大的Inno  DB表使用MySQL当前和以前版本运行这些操作。您还可以在最新的MySQL版本运行所有的性能测试,模拟之前DDL行为的“之前”结果,通过设置old-alter-table系统变量。在会话中发起语句 set old_alter_ table = 1,测量性能记录DDL“之前”的数据。然后set old_alter_ table = 0重新用新的、更快的行为,并运行DDL操作再次记录“之后”的数据。

对于DDL操作一个基本的想法：它的变化是就地执行还是执行表拷贝, 在命令结束之后看看显示“rows affected “的值。例如,这里您可能会看到在做不同类型的DDL操作:

• 修改列默认值(超级快，不影响表的所有数据):

Query OK, 0 rows affected (0.07 sec)

• 添加索引 (需要时间, 但0 rows affected 表明表没有被复制):

Query OK, 0 rows affected (21.42 sec)

• 改变列的数据类型（需要大量的时间和需要重建表中的所有行):

Query OK, 1671168 rows affected (1 min 35.54 sec)

例如, 在一个大表运行一个DDL操作之前,你可能会检查操作是将快还是慢,如下所示:

1. 克隆表结构。
2. 用少量数据填充克隆的表。
3. 在克隆的表运行DDL操作。
4. 检查 “行受影响”的值是否为零或不是。一个非零值意味着操作需要重建整个表,这可能需要特殊的规划。例如,你可能在计划停机期间做DDL操作，或在复制每个从服务器。

进一步了解减少MySQL处理,在InnoDB相关的表DDL操作之前和之后检查performance_schema和INFORMATION_SCHEMA表,查看物理读、写、内存分配的量,等等。

5.5.3.ONLINE DDL 的SQL语法

通常当对InnoDB表使用ALTER TABLE语句，你没有必要对online DDL做任何特殊的事，见Table 5.9, “Summary of Online Status for DDL Operations”对各种DDL操作可以就地（in-place）执行,允许并发DML，或两者都可以。一些变化需要配置设置的额外结合或是alter table语句。

你可以通过alter table语句中LOCK 和ALGORITHM子句来控制特定online DDL的各个方面。这些子句位于语句的结束的位置，它们之间用逗号隔开。LOCK子句对表的并发访问有不同粒度控制。ALGORITHM子句主要对性能比较和作为以前表拷贝一个备用，以防你遇到存在DDL代码中的问题。例如：

• 为了避免使表不能读，写，或两者的意外，你可以在alter table 语句指定一个子句如LOCK=none(允许可读可写)或LOCK=SHARED(可读)。如果并发请求级别是不可用的，该操作立即停止。
• 为了比较性能，你可以运行有ALGORITHM=INPLACE的语句和另一个有ALOGRITHM=COPY的语句，或者设置配置选项old_alter_table。
• 为了避免服务运行一个ALTER TABLE语句一定要复制表的机会。你可以使用ALGORITHM=INPLACE，如果这alter table不能使用这种机制，该语句立刻终止。对于DDL操作可不可就地执行，请参照Table 5.9, “Summary of Online Status for DDL Operations”。

LOCK子句更详细信息请参照Section 5.5.2, “Performance and Concurrency Considerations for Online DDL”。Online DDL 的完整的事例，见Section 5.5.5, “Examples of Online DDL”。

5.5.4.结合或分开的DDL语句

在介绍Online DDL之前，最常见操作就是把多个DDL操作结合成一条ALTER TABLE语句。因为每次alter table语句涉及到复制和重建表，一次使表做几次修改，它更有效率。既然做一次重建操作就完成表这些改变。缺点是,SQL代码涉及DDL操作是难以维护和重用在不同的脚本。如果特定的变化是不同的每一次,你可能为每一个稍微不同的场景必须构造一个新的复杂的ALTER TABLE。

对DDL可以就地操作，见Table 5.9, “Summary of Online Status for DDL Operations”，现在你可以把它们分成单独ALTER TABLE语句，使脚本更容易和维护，又不牺牲效率。例如：你可能采用一个复杂语句如：

alter table t1 add index i1(c1), add unique index i2(c2), change c4_old_name c4_new_name integer unsigned;

并将其分解为更简单的部分,可以单独测试并执行,例如:

alter table t1 add index i1(c1);

alter table t1 add unique index i2(c2);

alter table t1 change c4_old_name c4_new_name integer unsigned not null;

你也许对下列情况还会使用多个部分的alter_table 语句：

• 操作执行必须以一个特定的顺序，如创建一个索引，并使用该索引为外键约束。
• 所有操作使用相同类型的LOCK子句，这样你想同时失败或同时成功。
• 那些操作不能就地执行的，换句话说，还是需复制和重建表。
• 那些指定了ALGORITHM=COPY 或 old_alter_table=1的操作, 这样迫使表的复制行为。如果在某些场合需要精确的向后兼容性。

5.5.6.Online DDL 实现细节

每个对InnoDB表的ALTER TABLE 操作由几个方面组成：

• 是否对表的物理结构修改，还是纯粹修改元数据而不触及到表的本身。
• 数据的体积在表中是保持不变，增大还是减少。
• 表数据修改是否涉及到聚集索引，还是二级索引，或两者。
• 将被修改的表和其他表是否有外键约束关系。这机制不同取决于foreign_key_checks 配置选项是否可用。
• 表是否分区了，ALTER TABLE的分区子句被变成低级操作包含一张或多张表，DDL这些操作按照通常规则。
• 表数据是否被拷贝，表是否能识别“就地”或两者结合。
• 表是否包含任何auto-increment 列.
• 锁被要求等级，要么通过底层数据库操作的默认的,要么您在ALTER TABLE语句指定一个锁子句。

本节解释这些因素将如何影响不同类型的ALTER TABLE对InnoDB表操作。

Online DDL的错误条件

下面是Online DDL操作出错主要原因：

• 如果LOCK 子句指定一个低程度（SHARED或NONE），它与特定类型的DDL操作不兼容。
• 当等待获取exclusive lock发生超时，在DDL操作的初始化和最后阶段，它是短暂的。.
• 如果tmpdir 文件系统用完硬盘空间，然而在索引创建期间MySQL 写临时排序文件到硬盘。
• 如果 ALTER TABLE 需要很长时间, 并且当前DML更改的表很多, 临时在线日志的大小超过配置选项InnoDB_online_alter_log_max_size的值，该情况引起This c DB_ONLINE_LOG_TOO_BIG 错误。
• 如果当前DML允许对最初表的定义改变，但不允许对新表改变，该操作在快要结束时失败，当MySQL尝试应用当前DML语句对表的改变。例如，你也许插入重复的值到将要创建唯一索引的列，或你可能插入NULL值到正创建主键的列。这改变使得当前DML优先执行，和ALTER TABLE 操作最终被回滚。

虽然配置选项InnoDB_file_per_table有一个戏剧性的影响表现为InnoDB表, 是否启用或禁用这个选项，表的物理位置是否在它自己的.ibd文件还是系统内的表空间，所有在线DDL操作同样可以工作。

InnoDB有两种类型的索引:聚集索引代表表中的所有数据,和可选的二级索引来加快查询。既然聚集索引包含数据值在b -树节点,添加或删除一个聚集索引确实涉及到复制数据,并创建一个表的新副本。二级索引,然而,只包含索引键和主键的值。这种类型的索引可以创建或删除而不复制聚集索引的数据。因为每个二级索引包含主键值的副本(用于需要时访问聚集索引),当你改变主键的定义,所有二级索引重新进行。

删除一个二级索引很简单的。只有InnoDB内部系统表和MySQL数据字典表更新以反映该索引已不存在的事实。InnoDB返回用于存储索引的表空间,以便新索引或额外的表行可以使用该空间。

添加一个二级索引到现有表, InnoDB对表进行扫描,使用二次索引键列在内存缓冲区和临时文件进行行排序。B-treean按键-值顺序进行构件,这比用随机顺序插入行到一个索引更有效。当他们填充时B-tree节点会发生分裂,以这种方式构建索引对索引来说有一个较高的填充因子,使后续访问更有效。

主键和二次键索引

从历史上看, 对表和索引结构MySQL服务器和InnoDB各自保持自己的元数据。MySQL服务器存储这些信息.frm文件,不受事务机制的保护,而InnoDB有其自己的数据词典作为系统表空间的一部分。如果一个DDL操作被一个崩溃或其他意外事件中断了,元数据可能在这两个位置之间产生不一致,导致其他问题,如启动错误或无法访问正被改变的表。现在,InnoDB是默认的存储引擎,解决这些问题是当务之急。这些增强DDL操作减少发生这样的问题的机会。

5.5.7．崩溃恢复如何适用于在线DDL

当ALTER TABLE语句执行时，如果服务器崩溃了，虽然没有数据丢失 , 聚集索引和二级索引崩溃恢复过程是不同的。

如果当创建一个InnoDB辅助索引，服务器崩溃,在恢复时,MySQL删除任何部分创建的索引。你必须重新运行ALTER TABLE或CREATE INDEX语句。

当在创建InnoDB聚集索引期间发生系统崩溃,恢复更复杂,因为表中的数据必须被复制到一个全新的聚集索引。记住,所有InnoDB表存储为聚集索引。在接下来的讨论中,我们使用word表和聚集索引可以互换。

MySQL创建新集群索引通过从原始InnoDB表复制现有的数据到所需的索引结构的一个临时表。一旦数据是完全复制到这个临时表,原始表重命名为一个不同于临时表的名字。包括新的聚集索引临时表重命名为原始表的名称,和原始表被从数据库删除。

如果系统崩溃发生在创建一个新的聚集索引,没有数据丢失,但你必须使用过程中存在临时表来完成恢复。虽然它在大表重建一个聚集索引或重新定义主键,或在此操作遇到系统崩溃是罕见的,本书册没有提供这个场景中恢复的信息。

5.5.8.对分区表的DDL

这个机制用于分区表的DDL操作有点不同online DDL中的讨论。ALTER TABLE语句的分区条款只允许结合ALGORITHM=DEFAULT和LOCK=DEFAULT。尽管这些的操作不经过用于非分区表内部的online DDL API,MySQL仍然试图在可能的情况下最小化数据复制和锁:

• 对使用RANGE或LIST分区表添加分区或删除分区不需要复制任何已存在的数据。
• 对所有类型的分区表进行TRUNCATE PARTITION不需要复制任何已存在的表。
• 对hash或list的分区表，在添加或合并分区期间，允许并发查询。
• 对LINEAR hash 或LIST 的分区表，进行 REORGANIZE PARTITION, REBUILD PARTITION, 或 ADD PARTITION 或 COALESCE PARTITION 操作, 允许并发查询。受影响的分区数据被复制，并且加上一个共享锁。

5.5.9.Online DDL的局限性

在创建或删除InnoDB索引考虑下列事项:

• 在online DDL 操作期间，复制表，把文件写到临时目录（unix下的$TMPDIR,Windows下的%TMP%，目录通过配置选项—tmpdir 指定）。每个临时文件足够大容下新表的列或索引，并且一旦被合并到最终的表或索引，临时文件被删除。
• 当你在临时表创建一个索引，表被复制，而不能用快速创建索引方法。这已经报给MySQL bug #39833.
• InnoDB处理错误案列，当用户试图删除外键需要的索引。详细参见 Section 14.2.5.9, “Better Error Handling when Dropping Indexes”
• ALTER TABLE的子句 LOCK=NONE 是不允许的，如果表有ON…CASCADE或ON…SET NULL约束。
• 在每个ALTER TABLE语句区间, 忽略 LOCK 子句, 在开始和对表需要一个排斥锁（和通过lock=EXCLUSIVE指定的锁一样的）是一个短暂时间。这样，如果在该表有一个长运行事务如执行插入，更新，删除或SELECT … FOR UPDATE的话， online DDL操作也许会等待一会；或者有一个类似长运行的事务在alter table 过程中开启，那么DDL操作在完成前会等待一会。

【译者注】

后续会对本片文章的内容，进行功能性为主的测试验证，并且告诉读者DDL什么情况下会对数据服务有影响，什么情况下对数据服务不会影响，以及DDL变更的技巧。

 

译者：吴飞荣        新浪微博：@飞哥最爱白菜

  文章地址MySQL5.6版本InnoDB存储引擎在线DDL变更的官方信息中文翻译版