14.11 InnoDB and Online DDL

来源：互联网发布：淘宝四大化妆刷店编辑：程序博客网时间：2024/05/02 06:11

14.11 InnoDB and Online DDL

WORD(格式更清晰一点)：

http://note.youdao.com/noteshare?id=daf14083097067814afb76218984e932

14.11.1 Overview of Online DDL

14.11.2 Performance and Concurrency Considerations for Online DDL

14.11.3 SQL Syntax for Online DDL

14.11.4 Combining or Separating DDL Statements

14.11.5 Examples of Online DDL

14.11.6 Implementation Details of Online DDL

14.11.7 How Crash Recovery Works with Online DDL

14.11.8 Online DDL for Partitioned InnoDB Tables

14.11.9 Limitations of Online DDL

The online DDL feature builds on the InnoDB Fast Index Creation feature that is available in MySQL 5.1 and MySQL 5.5. The InnoDB Fast Index Creation feature optimized CREATE INDEX and DROP INDEX to avoid table-copying behavior. The online DDL feature, introduced in MySQL 5.6, enhances many other types of ALTER TABLE operations to avoid table copying, blocking DML operations while DDL is in progress, or both.

online DDL特性是建立在MySQL5.1和5.5的InnoDB Fast Index Creation特性上的。InnoDB Fast Index Creation特性优化了CREATE INDEX和DROP INDEX操作来避免了表拷贝的情况。online DDL是从MySQL5.6开始引进的，它增强了许多ALTER TABLE的操作，能够在处理DDL的时候避免表拷贝，以及避免对DML操作的阻塞。

The online DDL feature has the following benefits:

online DDL的特性有一下一些优点：

l It improves responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours whenever you modify its indexes or column definitions is not practical.

l 它提升了高负载生产环境的响应性和可用性，因为这些环境下为了修改索引或者列的定义而使得表有几小时甚至几分钟不可用是不切实际的。

l It lets you adjust the balance between performance and concurrency during the DDL operation, by choosing whether to block access to the table entirely (LOCK=EXCLUSIVE clause), allow queries but not DML (LOCK=SHARED clause), or allow full query and DML access to the table (LOCK=NONE clause). When you omit the LOCK clause or specify LOCK=DEFAULT, MySQL allows as much concurrency as possible depending on the type of operation.

l 它能够让你在DDL操作的时候自由调整性能和并发性之间的平衡，例如你可以阻塞住整个表(LOCK=EXCLUSIVE clause)，只允许查询不允许DML(LOCK=SHARED clause)，或者同时允许查询和DML(LOCK=NONE clause)。当你忽略LOCK子句或者指定LOCK=DEFAULT，MySQL会根据操作的类型尽可能允许更多的并发。

l Performing changes in-place where possible, rather than creating a new copy of the table, avoids temporary increases in disk space usage and I/O overhead associated with copying the table and reconstructing secondary indexes.

l 尽可能进行in-place的修改操作，而不是创建一个新的表拷贝，这样能够减少磁盘空间的使用以及I/O的损耗，并避免了重新构造secondary index。

The MySQL Cluster NDB storage engine also supports online table schema changes, but uses its own syntax that is not compatible with the syntax used for InnoDB online operations. For more information, see Section 13.1.7.2,“ALTER TABLE Online Operations in MySQL Cluster”.

MySQL Cluster NDB存储引擎也支持online的修改，但是它的操作语法和InnoDB的online操作语法并不兼容。更多相关信息可以查看Section 13.1.7.2, “ALTER TABLE Online Operations in MySQL Cluster”。

14.11.1 Overview of Online DDL

Historically, many DDL operations on InnoDB tables were expensive. Many ALTER TABLE operations worked by creating a new, empty table defined with the requested table options and indexes, then copying the existing rows to the new table one-by-one, updating the indexes as the rows were inserted. After all rows from the original table were copied, the old table was dropped and the copy was renamed with the name of the original table.

以前，InnoDB表上很多的DDL操作都是非常昂贵的。很多ALTER TABLE操作都会创建一个符合新定义要求的新空表，然后再一行一行地把现有的行记录复制进去，并在插入的同时更新索引的值。当原始表的所有行记录都复制好了以后，旧的表会被删除，新的表会被重命名到原来的表名。

MySQL 5.5, and MySQL 5.1 with the InnoDB Plugin, optimized CREATE INDEX and DROP INDEX to avoid the table-copying behavior. That feature was known as Fast Index Creation. MySQL 5.6 enhances many other types of ALTER TABLE operations to avoid copying the table. Another enhancement allows SELECT queries and INSERT, UPDATE, and DELETE (DML) statements to proceed while the table is being altered. This combination of features is now known as online DDL.

MySQL5.5以及使用了InnoDB Plugin的MySQL5.1，优化了CREATE INDEX and DROP INDEX操作避免了表拷贝的情况。这种特性称之为Fast Index Creation。MySQL5.6在次基础上增强了许多ALTER TABLE的操作来避免表复制，并实现了在表被修改（altered）的时候同时允许查询以及DML操作。这种特性就是这里所说的online DDL。

This mechanism also means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without any secondary indexes, then adding the secondary indexes after the data is loaded.

这同时也意味着你可以你创建表以及加载数据的时候先不添加secondary index，而是等到数据加载完毕后再添加secondary index，这就加快了整个操作的速度。

Although no syntax changes are required in the CREATE INDEX or DROP INDEX commands, some factors affect the performance, space usage, and semantics of this operation (see Section 14.11.9,“Limitations of Online DDL”).

虽然CREATE INDEX和DROP INDEX在语法上并没有什么改变，但是还是有一些因素为影响性能，空间利用，以及操作的语义（详见Section 14.11.9, “Limitations of Online DDL”）。

The online DDL enhancements in MySQL 5.6 improve many DDL operations that formerly required a table copy, blocked DML operations on the table, or both. Table 14.6,“Summary of Online Status for DDL Operations”shows the variations of the ALTER TABLE statement and shows how the online DDL feature applies to each.

MySQL5.6里面的online DDL该散了很多的DDL操作，使得它们不再需要进行表复制，以及阻塞DML操作。Table 14.6, “Summary of Online Status for DDL Operations”显示了ALTER TABLE语句的变化以及相对应的online DDL是如何应用的。

With the exception of ALTER TABLE partitioning clauses, online DDL operations for partitioned InnoDB tables follow the same rules that apply to regular InnoDB tables. For more information, see Section 14.11.8,“Online DDL for Partitioned InnoDB Tables”.

online DDL对于分区表的操作和普通表一样，都遵循相同的原则。更多信息查看Section 14.11.8,“Online DDL for Partitioned InnoDB Tables”。

l The “In-Place?”column shows which operations allow the ALGORITHM=INPLACE clause; the preferred value is“Yes”.

l In-Place?”列显示了是否支持ALGORITHM=INPLACE，首选值是“Yes”。

l The “Copies Table?”column shows which operations are able to avoid the expensive table-copying operation; the preferred value is“No”. This column is mostly the reverse of the“In-Place?”column, except that a few operations allow ALGORITHM=INPLACE but still involve some amount of table copying.

l “Copies Table?”列显示了是否能够避免昂贵的表复制操作；首选值是“No”。这列大多数和“In-Place?”列是相反的，除了一些既允许ALGORITHM=INPLACE但仍然会设计到一些表复制的情况。

l The “Allows Concurrent DML?”column shows which operations can be performed fully online; the preferred value is“Yes”. You can specify LOCK=NONE to assert that full concurrency is allowed during the DDL, but MySQL automatically allows this level of concurrency when possible. When concurrent DML is allowed, concurrent queries are also always allowed.

l Allows Concurrent DML?”表示是否支持完全的online操作；首选值是“Yes”。你可以指定LOCK=NONE来宣称在DDL的过程中允许并发，但是MySQL还是会根据实际情况来做适当调整。当并发DML被允许的时候，并发查询通常也是被允许的。

l The “Allows Concurrent Queries?”column shows which DDL operations allow queries on the table while the operation is in progress; the preferred value is“Yes”. Concurrent query is allowed during all online DDL operations. It is shown with“Yes”listed for all cells, for reference purposes. You can specify LOCK=SHARED to assert that concurrent queries are allowed during the DDL, but MySQL automatically allows this level of concurrency when possible.

l “Allows Concurrent Queries?”表示在DDL过程中是否允许并发查询；首选值是“Yes”。基本上这里的值都是“Yes”。你可以指定LOCK=SHARED来宣称在DDL过程中允许并发查询，但是MySQL还是会根据实际情况来适当调整并发的级别。

l The “Notes”column explains any exceptions to the“Yes/No”values of the other columns, such as when the answer depends on the setting of a configuration option or some other clause in the DDL statement. The values“Yes*”and“No*”indicate that an answer depends on these additional notes.

l “Notes”对“*”值进行了特别说明。

Table 14.6 Summary of Online Status for DDL Operations

Operation

In-Place?

Copies Table?

Allows Concurrent DML?

Allows Concurrent Query?

Notes

CREATE INDEX, ADD INDEX

Yes*

No*

Yes

Some restrictions for FULLTEXT index; see next row.
对于FULLTEXT index会部分限制；详见下一行

ADD FULLTEXT INDEX

Yes

No*

Yes

Creating the first FULLTEXT index for a table involves a table copy, unless there is a user-supplied FTS_DOC_ID column. Subsequent FULLTEXT indexes on the same table can be created in-place.
创建第一个FULLTEXT index的时候会涉及到表复制，除非手动指定了FTS_DOC_ID列。同一表上之后的FULLTEXT index则会以in-place方式创建。

DROP INDEX

Yes

Only modifies table metadata.
只会修改表的元数据

OPTIMIZE TABLE

Yes

Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=COPY is used if old_alter_table=1 or mysqld --skip-new option is enabled. OPTIMIZE TABLE using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes.
从MySQL5.6.17开始使用ALGORITHM=INPLACE。只有old_alter_table=1或者mysqld --skip-new开启的时候才会使用ALGORITHM=COPY。但是OPTIMIZE TABLE的online DDL操作(ALGORITHM=INPLACE)不支持有FULLTEXT index的表。

Set default value for a column

Yes

Only modifies table metadata.
只会修改表的元数据

Change auto-increment value for a column

Yes

Only modifies table metadata.
只会修改表的元数据

Add a foreign key constraint

Yes*

No*

Yes

To avoid copying the table, disable foreign_key_checks during constraint creation.
在创建约束的时候可以通过关闭foreign_key_checks来避免表复制

Drop a foreign key constraint

Yes

The foreign_key_checks option can be enabled or disabled.
foreign_key_checks开启或者关闭都可以

Rename a column

Yes*

No*

Yes*

Yes

To allow concurrent DML, keep the same data type and only change the column name.
只有保持原有数据类型的情况下才会允许并发DML

Add a column

Yes*

Yes

Concurrent DML is not allowed when adding an auto-increment column. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
当要添加的是自增长列的时候并发DML是不支持的。虽然ALGORITHM=INPLACE是被允许的，但是随后数据还是会要重新组织，所以这一操作仍然是非常昂贵的。

Drop a column

Yes

Yes*

Yes

Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
当要添加的是自增长列的时候并发DML是不支持的。虽然ALGORITHM=INPLACE是被允许的，但是随后数据还是会要重新组织，所以这一操作仍然是非常昂贵的。

Reorder columns

Yes

Change ROW_FORMAT property

Yes

Change KEY_BLOCK_SIZE property

Yes

Make column NULL

Yes

Make column NOT NULL

Yes*

Yes

STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. For more information, see Section 13.1.7,“ALTER TABLE Syntax”. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
操作成功需要把SQL_MODE设置成STRICT_ALL_TABLES or STRICT_TRANS_TABLES。如果列里面包含NULL值的操作失败。从MySQL5.6.7开始，数据库会阻止修改那些可能会引发丢失相关引用数据的外键列。更多相关信息可以查看see Section 13.1.7, “ALTER TABLE Syntax”。虽然ALGORITHM=INPLACE是被允许的，但是随后数据还是会要重新组织，所以这一操作仍然是非常昂贵的。

Change data type of column

Yes

Add primary key

Yes*

Yes

Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. ALGORITHM=INPLACE is not allowed under certain conditions if columns have to be converted to NOT NULL. See Example 14.9, “Creating and Dropping the Primary Key”.
虽然ALGORITHM=INPLACE是被允许的，但是随后数据还是会要重新组织，所以这一操作仍然是非常昂贵的。如果某些列因主键的原因需要转换为NOT NULL的时候ALGORITHM=INPLACE是不允许的。详见Example 14.9, “Creating and Dropping the Primary Key”。

Drop primary key and add another

Yes

ALGORITHM=INPLACE is only allowed when you add a new primary key in the same ALTER TABLE; the data is reorganized substantially, so it is still an expensive operation.
虽然ALGORITHM=INPLACE是被允许的，但是随后数据还是会要重新组织，所以这一操作仍然是非常昂贵的。如果某些列因主键的原因需要转换为NOT NULL的时候ALGORITHM=INPLACE是不允许的。详见Example 14.9, “Creating and Dropping the Primary Key”。

Drop primary key

Yes

Restrictions apply when you drop a primary key primary key without adding a new one in the same ALTER TABLE statement.

Convert character set

Yes

Rebuilds the table if the new character encoding is different.
如果新字符集和之前不同的话会要重建表。

Specify character set

Yes

Rebuilds the table if the new character encoding is different.
如果新字符集和之前不同的话会要重建表。

Rebuild with FORCE option

Yes

Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=COPY is used if old_alter_table=1 or mysqld --skip-new option is enabled. Table rebuild using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes.
从MySQL5.6.17开始使用ALGORITHM=INPLACE。只有old_alter_table=1或者mysqld --skip-new开启的时候才会使用ALGORITHM=COPY。但是OPTIMIZE TABLE的online DDL操作(ALGORITHM=INPLACE)不支持有FULLTEXT index的表。

Rebuild with “null”ALTER TABLE ... ENGINE=INNODB

Yes

Set table-level persistent statistics options (STATS_PERSISTENT, STATS_AUTO_RECALC STATS_SAMPLE_PAGES)

Yes

Only modifies table metadata.
只会修改表的元数据

The following sections shows the basic syntax, and usage notes related to online DDL, for each of the major operations that can be performed with concurrent DML, in-place, or both:

下面的章节讲述了online DDL的基础语法以及用法说明：

Secondary Indexes

l Create secondary indexes: CREATE INDEX name ON table (col_list) or ALTER TABLE table ADD INDEX name (col_list). (Creating a FULLTEXT index still requires locking the table.)

l Drop secondary indexes: DROP INDEX name ON table; or ALTER TABLE table DROP INDEX name

Creating and dropping secondary indexes on InnoDB tables skips the table-copying behavior, the same as in MySQL 5.5 and MySQL 5.1 with the InnoDB Plugin.

创建删除InnoDB表上的secondary index的方式和以前一样。

In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Previously, modifying the table while an index is being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.

在MySQL5.6及更高的版本里，当创建或者删除索引的时候表仍然可以进行读写操作。CREATE INDEX or DROP INDEX语句会在表上的事务都完成之后才会执行，所以索引的初始状态能够映射到表上绝大部分内容。以前，在创建或者删除索引的时候修改表通常会导致死锁并回滚掉相应的DML语句。

Column Properties

l Set a default value for a column: ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal or ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT

l 设置默认值：ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal or ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT

The default values for columns are stored in the .frm file for the table, not the InnoDB data dictionary.

默认值是存储在表的.frm文件里的，不是在InnoDB的数据目录里。

l Changing the auto-increment value for a column: ALTER TABLE table AUTO_INCREMENT=next_value;

l 修改列的自增长值：ALTER TABLE table AUTO_INCREMENT=next_value;

Especially in a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto-increment column. You might also use this technique in a data warehousing environment where you periodically empty all the tables and reload them, and you can restart the auto-increment sequence from 1.

特别是在主从或者共享的分布式系统中，你有的时候会需要重设表的自增长值。这样下一行插入的值就会使用你新执行的自增长值。这种情况还有可能会用在数据仓库的环境里：你需要周期性地清空表然后重新加载数据，这个时候你就可能会需要把自增长值重置回1。

l Renaming a column: ALTER TABLE tbl CHANGE old_col_name new_col_name datatype

l 重命名列：ALTER TABLE tbl CHANGE old_col_name new_col_name datatype

When you keep the same data type and [NOT] NULL attribute, only changing the column name, this operation can always be performed online.

当你要保持原来的数据类型以及[NOT] NULL属性的时候，只会修改列的名字，这样的操作都是以online的方式执行的。

You can also rename a column that is part of a foreign key constraint. The foreign key definition is automatically updated to use the new column name. Renaming a column participating in a foreign key only works with the in-place mode of ALTER TABLE. If you use the ALGORITHM=COPY clause, or some other condition causes the command to use ALGORITHM=COPY behind the scenes, the ALTER TABLE statement will fail.

你还可以对一个外键列进行重命名。外键定义会自动更新使用新的列名的。重命名外键列追能以in-place的方式工作。如果你使用了ALGORITHM=COPY，或者其他会引起ALGORITHM=COPY行为的场景，那么ALTER TABLE则会报错。

Foreign Keys

l Adding or dropping a foreign key constraint:

l 添加删除外键：

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

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

Dropping a foreign key can be performed online with the foreign_key_checks option enabled or disabled. Creating a foreign key online requires foreign_key_checks to be disabled.

无论foreign_key_checks开启还是关闭都可以以online方式删除一个外键。要以online的方式创建一个主键的话则就需要关闭foreign_key_checks了。

If you do not know the names of the foreign key constraints on a particular table, issue the following statement and find the constraint name in the CONSTRAINT clause for each foreign key:

如果你不知道一个表的外键的名字，可以执行下面的语句在CONSTRAINT可以找到外键约束的名字：

show create table table\G

Or, query the information_schema.table_constraints table and use the constraint_name and constraint_type columns to identify the foreign key names.

或者查询information_schema.table_constraints用constraint_name和constraint_type的值来确认外键的名字。

You can also drop a foreign key and its associated index in a single statement:

你还可以在一个语句里同时删除外键及其对应的索引：

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

If foreign keys are already present in the table being altered (that is, it is a child table containing any FOREIGN KEY ... REFERENCE clauses), additional restrictions apply to online DDL operations, even those not directly involving the foreign key columns:

如果表里外键正在被修改（也就是说，子表里包含了FOREIGN KEY ... REFERENCE），那对于online DDL操作就会有额外的限制，即使这些修改并没有直接涉及到外键列：

l Concurrent DML is disallowed during online DDL operations on such child tables. (This restriction is being evaluated as a bug and might be lifted.)

l 子表上的online DDL操作是不允许并发DML的。（这个限制可以看成是个bug，以后或许会被修复。）

l An ALTER TABLE on the child table could also wait for another transaction to commit, if a change to the parent table caused associated changes in the child table through an ON UPDATE or ON DELETE clause using the CASCADE or SET NULL parameters.

l 子表上的ALTER TABLE语句需要等到其他事务结束，如果在父表上执行含有CASCADE or SET NULL参数的ON UPDATE or ON DELETE子句，那么子表也会做相应的变动。

In the same way, if a table is the parent table in a foreign key relationship, even though it does not contain any FOREIGN KEY clauses, it could wait for the ALTER TABLE to complete if an INSERT, UPDATE, or DELETE statement caused an ON UPDATE or ON DELETE action in the child table.

同样地，如果一个表有父表的外键关系，即使它没有包含FOREIGN KEY子句，那它子表上的DML操作引起的ON UPDATE or ON DELETE动作也要等到ALTER TABLE完成后才会执行。

Notes on ALGORITHM=COPY

Any ALTER TABLE operation run with the ALGORITHM=COPY clause prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of LOCK=SHARED (allow queries but not DML). You can further restrict concurrency for such operations by specifying LOCK=EXCLUSIVE (prevent DML and queries).

任何使用ALGORITHM=COPY的ALTER TABLE操作都会阻止并行的DML操作，并行的查询不受影响。也就是说，表复制操作总是会包括一个LOCK=SHARED的并发限制（影响DML不影响查询）。当然你也可以通过指定LOCK=EXCLUSIVE来更进一步加强并发的限制（同时影响DML和查询。）

Concurrent DML but Table Copy Still Required

Some other ALTER TABLE operations allow concurrent DML but still require a table copy. However, the table copy for these operations is faster than it was in MySQL 5.5 and prior.

有一些其他的ALTER TABLE操作会允许并行的DML但仍然会进行表复制。但是，这个操作的表复制要比MySQL5.5以及之前的版本速度更快。

l Adding, dropping, or reordering columns.

l Adding or dropping a primary key.

l Changing the ROW_FORMAT or KEY_BLOCK_SIZE properties for a table.

l Changing the nullable status for a column.

l OPTIMIZE TABLE

l Rebuilding a table with the FORCE option

l Rebuilding a table using a “null”ALTER TABLE ... ENGINE=INNODB statement

Maintaining CREATE TABLE Statements

As your database schema evolves with new columns, data types, constraints, indexes, and so on, keep your CREATE TABLE statements up to date with the latest table definitions. Even with the performance improvements of online DDL, it is more efficient to create stable database structures at the beginning, rather than creating part of the schema and then issuing ALTER TABLE statements afterward.

尽量保证CREATE TABLE语句有着关于列，数据类型，约束，索引等等的最新定义。因为即使online DDL已经很大地改善了性能，但是当对于先创建后再修改的方式，在初期就创建一个完整的稳定的表结构的方法更为有效。

The main exception to this guideline is for secondary indexes on tables with large numbers of rows. It is typically most efficient to create the table with all details specified except the secondary indexes, load the data, then create the secondary indexes. You can use the same technique with foreign keys (load the data first, then set up the foreign keys) if you know the initial data is clean and do not need consistency checks during the loading process.

当然对于大表上的secondary index来说是一种例外，这种情况应该先建表，再加载数据，最后在创建secondary index。这种方法同样也适用于不需要做一致性检查的外键（先加载数据，在设定外键）。

Whatever sequence of CREATE TABLE, CREATE INDEX, ALTER TABLE, and similar statements went into putting a table together, you can capture the SQL needed to reconstruct the current form of the table by issuing the statement SHOW CREATE TABLE table\G (uppercase \G required for tidy formatting). This output shows clauses such as numeric precision, NOT NULL, and CHARACTER SET that are sometimes added behind the scenes, and you might otherwise leave out when cloning the table on a new system or setting up foreign key columns with identical type.

无论原来CREATE TABLE, CREATE INDEX, ALTER TABLE的执行顺序是什么，都可以通过SHOW CREATE TABLE table\G来获得当前最新的建表语句。这个里面包括了数值的精度，NOT NULL, and CHARACTER SET以及一些其他的信息，你可以对这些信息做一些修改来克隆一个新表。

14.11.2 Performance and Concurrency Considerations for Online DDL

Online DDL improves several aspects of MySQL operation, such as performance, concurrency, availability, and scalability:

Online DDL改善了MySQL操作的好几个方面，例如性能，并发，可用性，以及可扩展性：

l Because queries and DML operations on the table can proceed while the DDL is in progress, applications that access the table are more responsive. Reduced locking and waiting for other resources all throughout the MySQL server leads to greater scalability, even for operations not involving the table being altered.

l 因为在DDL的处理过程中表上的查询和DML操作都能继续，因此大大增强了表对应用程序的响应能力。减少锁及其他资源的等待使得MySQL获得了更高的可扩展行。

l For in-place operations, by avoiding the disk I/O and CPU cycles to rebuild the table, you minimize the overall load on the database and maintain good performance and high throughput during the DDL operation.

l in-place操作在重建表的时候能够减少磁盘I/O和CPU的资源，这样你在操作DDL的过程中能够大幅降低数据库的负载，并或者更好的性能和吞吐量。

l For in-place operations, because less data is read into the buffer pool than if all the data was copied, you avoid purging frequently accessed data from memory, which formerly could cause a temporary performance dip after a DDL operation.

l 对于in-place操作，因为只需要从buffer pool里读取少量的数据，所以能够避免频繁地从内存里进行purge操作，这也就避免了DDL操作之后的临时性能下降。

If an online operation requires temporary files, InnoDB creates them in the temporary file directory, not the directory containing the original table. If this directory is not large enough to hold such files, you may need to set the tmpdir system variable to a different directory. (See Section B.5.3.5, “Where MySQL Stores Temporary Files”.)

如果一个online操作要求临时文件，InnoDB会在临时文件目录里创建它们，而不是原始表的目录。如果临时文件目录的空间不够大，那么你就需要把tmpdir系统变量设定到另一个目录里。（详见Section B.5.3.5, “Where MySQL Stores Temporary Files”）

Locking Options for Online DDL

While an InnoDB table is being changed by a DDL operation, the table may or may not be locked, depending on the internal workings of that operation and the LOCK clause of the ALTER TABLE statement. By default, MySQL uses as little locking as possible during a DDL operation; you specify the clause either to make the locking more restrictive than it normally would be (thus limiting concurrent DML, or DML and queries), or to ensure that some expected degree of locking is allowed for an operation. If the LOCK clause specifies a level of locking that is not available for that specific kind of DDL operation, such as LOCK=SHARED or LOCK=NONE while creating or dropping a primary key, the clause works like an assertion, causing the statement to fail with an error. The following list shows the different possibilities for the LOCK clause, from the most permissive to the most restrictive:

当DDL修改InnoDB表的时候，表是否会被锁住要取决于这个操作的内部工作模式以及ALTER TABLE的LOCK子句。默认情况下，MySQL会尽可能少地锁表；当让你也可以指定LOCK子句使其更为严格（也就意味着对并行DML,查询的限制更多）。如果指定的LOCK子句是对当前的操作是不可用的，例如在创建删除的主键的时候使用OCK=SHARED or LOCK=NONE，那么会使得语句执行失败。下面的列表显示了LOCK子句不同的可能性，从最宽松的到最严格的：

l For DDL operations with LOCK=NONE, both queries and concurrent DML are allowed. This clause makes the ALTER TABLE fail if the kind of DDL operation cannot be performed with the requested type of locking, so specify LOCK=NONE if keeping the table fully available is vital and it is OK to cancel the DDL if that is not possible. For example, you might use this clause in DDLs for tables involving customer signups or purchases, to avoid making those tables unavailable by mistakenly issuing an expensive ALTER TABLE statement.

l 对于使用LOCK=NONE的DDL操作，查询和并行的DML都是允许的。当然这也会使得必须要使用锁的DDL句执行失败，所以你可以在表的可用性是非常重要的情况下指定LOCK=NONE，要么无锁修改要么更新失败。例如，在涉及到客户注册或者订单的表上使用这个子句的DDL，就可以避免错误地使表变得不可用。

l For DDL operations with LOCK=SHARED, any writes to the table (that is, DML operations) are blocked, but the data in the table can be read. This clause makes the ALTER TABLE fail if the kind of DDL operation cannot be performed with the requested type of locking, so specify LOCK=SHARED if keeping the table available for queries is vital and it is OK to cancel the DDL if that is not possible. For example, you might use this clause in DDLs for tables in a data warehouse, where it is OK to delay data load operations until the DDL is finished, but queries cannot be delayed for long periods.

l LOCK=SHARED，DML操作都会被阻塞，但是不影响查询，但这也会使得需要这种锁类型下DDL语句执行失败。指定LOCK=SHARED适用于那种查询功能非常重要的情况下。例如，你可以在数据仓库的环境下使用这个子句的DDL，数据加载的操作可以在DDL完成之后再执行，但是期间不会影响查询。

l For DDL operations with LOCK=DEFAULT, or with the LOCK clause omitted, MySQL uses the lowest level of locking that is available for that kind of operation, allowing concurrent queries, DML, or both wherever possible. This is the setting to use when making pre-planned, pre-tested changes that you know will not cause any availability problems based on the workload for that table.

l LOCK=DEFAULT或者省略LOCK子句，MySQL会使用可能的最低级别的锁，并尽量同时允许并行的查询或DML。这种设定适用于那些做过预先计划测试的情况，知道当前的操作不会引起任何可用性问题。

l For DDL operations with LOCK=EXCLUSIVE, both queries and DML operations are blocked. This clause makes the ALTER TABLE fail if the kind of DDL operation cannot be performed with the requested type of locking, so specify LOCK=EXCLUSIVE if the primary concern is finishing the DDL in the shortest time possible, and it is OK to make applications wait when they try to access the table. You might also use LOCK=EXCLUSIVE if the server is supposed to be idle, to avoid unexpected accesses to the table.

l LOCK=EXCLUSIVE，同时会阻塞DML操作和查询。这个子句会使得所有需要这种锁的DLL语句执行失败，但是这会使得语句执行得更为快速。你可以在数据库闲置的情况下使用LOCK=EXCLUSIVE，这样可以避免意外的表访问。

An online DDL statement for an InnoDB table always waits for currently executing transactions that are accessing the table to commit or roll back, because it requires exclusive access to the table for a brief period while the DDL statement is being prepared. Likewise, it requires exclusive access to the table for a brief time before finishing. Thus, an online DDL statement waits for any transactions that are started while the DDL is in progress, and query or modify the table, to commit or roll back before the DDL completes.

online DDL总是会等到当前的事务结束才会执行，因为在准备期间它会对表加一个短暂的排他锁。同样地，在DDL执行结束的时候也一样。因此，online DDL在处理的过程中会等待已经开始的事务，直到它们commit或者rollback之后才会继续。

Because there is some processing work involved with recording the changes made by concurrent DML operations, then applying those changes at the end, an online DDL operation could take longer overall than the old-style mechanism that blocks table access from other sessions. The reduction in raw performance is balanced against better responsiveness for applications that use the table. When evaluating the ideal techniques for changing table structure, consider end-user perception of performance, based on factors such as load times for web pages.

因为有些处理工作会涉及到并行的DML操作，而且在结束的时候还是把这些DML修改操作给应用上，因此online DDL操作会比老的锁表的方式花费更多的时间。虽然原始性有所下降，但这更好地平衡了应用程序的相应能力。当要以修改表结构为代价评估一个新解决方案时，我们必须必须要考虑到终端用户基于诸如网页加载时间等因素的感知性能。

A newly created InnoDB secondary index contains only the committed data in the table at the time the CREATE INDEX or ALTER TABLE statement finishes executing. It does not contain any uncommitted values, old versions of values, or values marked for deletion but not yet removed from the old index.

CREATE INDEX or ALTER TABLE语句执行结束的时候secondary index里面就已经包含了当前时间点之前所有已经commit的数据了。这里面不会有未提交的数据，旧版本(快照)的数据，以及在老的索引里已经标注delete但还为实际移除的数据。

Performance of In-Place versus Table-Copying DDL Operations

The raw performance of an online DDL operation is largely determined by whether the operation is performed in-place, or requires copying and rebuilding the entire table. See Table 14.6,“Summary of Online Status for DDL Operations”to see what kinds of operations can be performed in-place, and any requirements for avoiding table-copy operations.

一个online DDL操作的原始性能是由其工作的方式决定的：是以in-place的方式执行的，还是要复制重建整张表。Table 14.6, “Summary of Online Status for DDL Operations”里面可以看到哪些操作可以以in-place方式执行，以及有哪些需求来避免表复制的操作。

The performance speedup from in-place DDL applies to operations on secondary indexes, not to the primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the primary key, forming what some database systems call an“index-organized table”. Because the table structure is so closely tied to the primary key, redefining the primary key still requires copying the data.

in-place DDL代理的性能加速适用于secondary index，不适用于主键。InnoDB表的行记录是以基于主键的clustered index方式存储的，这种方式称之为“index-organized table（索引组织表）”。因为表的结构是和主键紧密联系在一起的，所以重定义主键就要复制数据重建表。

When an operation on the primary key uses ALGORITHM=INPLACE, even though the data is still copied, it is more efficient than using ALGORITHM=COPY because:

当在主键上使用ALGORITHM=INPLACE的操作，即使数据仍然还会进行复制，但这也要比ALGORITHM=COPY更为高效：

l No undo logging or associated redo logging is required for ALGORITHM=INPLACE. These operations add overhead to DDL statements that use ALGORITHM=COPY.

l ALGORITHM=INPLACE不需要undo以及相关的redo，而这些在ALGORITHM=COPY是有损耗的。

l The secondary index entries are pre-sorted, and so can be loaded in order.

l secondary index条目是预先排序好的，所以它们可以依序加载。

l The change buffer is not used, because there are no random-access inserts into the secondary indexes.

l 因为secondary index不存在随机的插入，素以change buffer也就不必要了。

To judge the relative performance of online DDL operations, you can run such operations on a big InnoDB table using current and earlier versions of MySQL. You can also run all the performance tests under the latest MySQL version, simulating the previous DDL behavior for the “before”results, by setting the old_alter_table system variable. Issue the statement set old_alter_table=1 in the session, and measure DDL performance to record the“before”figures. Then set old_alter_table=0 to re-enable the newer, faster behavior, and run the DDL operations again to record the“after”figures.

要评判online DDL操作的相关性能，当然你可以分别在当前的以及以前老的MySQL版本上分别处理一个大的InnoDB表来对比性能。但是你也可以通过old_alter_table系统变量来模拟“之前版本”MySQL的DDL行为。在session中设置old_alter_table=1来测量以前版本的DDL性能，然后再设置old_alter_table=0，来体验新版本的快速。

For a basic idea of whether a DDL operation does its changes in-place or performs a table copy, look at the“rows affected”value displayed after the command finishes. For example, here are lines you might see after doing different types of DDL operations:

要确定DDL操作使用的是in-place还是执行的表复制，可以通过命名结束时显示的“rows affected”来确认。例如：

l Changing the default value of a column (super-fast, does not affect the table data at all):

l 修改一列的默认值（非常快速，不需要影响表里的所有的数据）：

Query OK, 0 rows affected (0.07 sec)

l Adding an index (takes time, but 0 rows affected shows that the table is not copied):

l 添加一个索引（需要花费一定的时间，但是0 rows affected显示了表没有进行复制）：

Query OK, 0 rows affected (21.42 sec)

l Changing the data type of a column (takes substantial time and does require rebuilding all the rows of the table):

l 修改一个列的数据类型（需要花费大量的时间重建表里的所有行记录）：

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

For example, before running a DDL operation on a big table, you might check whether the operation will be fast or slow as follows:

在一个大表上运行DDL操作之前，你可以依照下面的方法来检查这个操作是快还是慢：

1.Clone the table structure.

1.克隆表结构

2.Populate the cloned table with a tiny amount of data.

2.在克隆表里填充一批小批量的数据。

3.Run the DDL operation on the cloned table.

3.在克隆表上运行DDL操作。

4.Check whether the “rows affected”value is zero or not. A non-zero value means the operation will require rebuilding the entire table, which might require special planning. For example, you might do the DDL operation during a period of scheduled downtime, or on each replication slave server one at a time.

4.检查“rows affected”的值是不是0。非零表示操作需要重建整张表，这就需要进行特别的计划了。例如在一个特定的时间停机进行DDL操作，或者先在slave端操作再进行切换。

For a deeper understanding of the reduction in MySQL processing, examine the performance_schema and INFORMATION_SCHEMA tables related to InnoDB before and after DDL operations, to see the number of physical reads, writes, memory allocations, and so on.

要更深入了解MySQL处理需要的时间，最好在执行的前后查看performance_schema和INFORMATION_SCHEMA里相关的InnoDB表，来对比物理读，写，内存分配等相关的指标。

14.11.3 SQL Syntax for Online DDL

Typically, you do not need to do anything special to enable online DDL when using the ALTER TABLE statement for InnoDB tables. See Table 14.6,“Summary of Online Status for DDL Operations”for the kinds of DDL operations that can be performed in-place, allowing concurrent DML, or both. Some variations require particular combinations of configuration settings or ALTER TABLE clauses.

通常情况下，ALTER TABLE语句不需要指定任何东西来开启online DDL。在Table 14.6, “Summary of Online Status for DDL Operations”里面可以看到哪种DDL操作可以以in-place方式执行，允许并发DML等，还有一些特定配置的不同组合变化。

You can control the various aspects of a particular online DDL operation by using the LOCK and ALGORITHM clauses of the ALTER TABLE statement. These clauses come at the end of the statement, separated from the table and column specifications by commas. The LOCK clause is useful for fine-tuning the degree of concurrent access to the table. The ALGORITHM clause is primarily intended for performance comparisons and as a fallback to the older table-copying behavior in case you encounter any issues with existing DDL code. For example:

你可以通过ALTER TABLE的LOCK和ALGORITHM子句拉控制online DDL操作的特定的各个方面。这两个子句放在整个语句的尾部，针对表和指定的列可以使用逗号分割。LOCK对于微调表的访问并发度是非常有效的。ALGORITHM主要用于性能的比较，以及当当前执行的DDL语句遇到问题时可以回退到老的表复制方法。例如：

l To avoid accidentally making the table unavailable for reads, writes, or both, specify a clause on the ALTER TABLE statement such as LOCK=NONE (allow both reads and writes) or LOCK=SHARED (allow reads). The operation halts immediately if the requested level of concurrency is not available.

l 为了避免表意外发生不可读，不可写，或者同时不可读写的情况，可以在ALTER TABLE语句里指定LOCK=NONE（同时允许读和写）或者LOCK=SHARED（允许读）。这样如果达不到请求的并发度时操作会立刻停止。

l To compare performance, run one statement with ALGORITHM=INPLACE and another with ALGORITHM=COPY, as an alternative to setting the old_alter_table configuration option.

l 为了对比性能，可以分别运行ALGORITHM=INPLACE和ALGORITHM=COPY，以及在old_alter_table配置参数上进行取舍。

l To avoid tying up the server with an ALTER TABLE operation that copies the table, include ALGORITHM=INPLACE. The statement halts immediately if it cannot use the in-place mechanism. See Table 14.6,“Summary of Online Status for DDL Operations”for a list of the DDL operations that can or cannot be performed in-place.

l 为了避免ALTER TABLE操作捆绑表复制，可以使用ALGORITHM=INPLACE。这样如果无法使用in-place机制的话语句会立刻停止。Table 14.6, “Summary of Online Status for DDL Operations”可以查看哪些DDL允许以in-place方式执行。

See Section 14.11.2, “Performance and Concurrency Considerations for Online DDL”for more details about the LOCK clause. For full examples of using online DDL, see Section 14.11.5,“Examples of Online DDL”.

关于LOCK的更多细节可以查看Section 14.11.2,“Performance and Concurrency Considerations for Online DDL”。关于online DDL的样例可以查看Section 14.11.5,“Examples of Online DDL”。

14.11.4 Combining or Separating DDL Statements

Before the introduction of online DDL, it was common practice to combine many DDL operations into a single ALTER TABLE statement. Because each ALTER TABLE statement involved copying and rebuilding the table, it was more efficient to make several changes to the same table at once, since those changes could all be done with a single rebuild operation for the table. The downside was that SQL code involving DDL operations was harder to maintain and to reuse in different scripts. If the specific changes were different each time, you might have to construct a new complex ALTER TABLE for each slightly different scenario.

在引入online DDL之前，通常的做法是把多个DDL操作合并到一个ALTER TABLE里面去执行。因为每个ALTER TABLE语句都会涉及到表的复制和重建，这样把同一个表上的多个修改合并到一起执行的话就更有效率。但是缺点是这样的DDL语句的SQL脚本难以维护，而且不利于重复利用。如果每次的修改都不一样，那么针对每次稍有不同的场景就需要准备完全不同的ALTER TABLE语句。

For DDL operations that can be done in-place, as shown in Table 14.6, “Summary of Online Status for DDL Operations”, now you can separate them into individual ALTER TABLE statements for easier scripting and maintenance, without sacrificing efficiency. For example, you might take a complicated statement such as:

如果DDL操作能够像Table 14.6,“Summary of Online Status for DDL Operations”里显示的那样以in-place的方式执行，那么你就可以把它们分散到单独的ALTER TABLE语句里，这样的脚本也更简单，更易维护，而且还不会牺牲效率。例如，这样的一个语句：

ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),

CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;

and break it down into simpler parts that can be tested and performed independently, such as:

那可以把它们分解开来分别进行测试和执行：

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;

You might still use multi-part ALTER TABLE statements for:

这样ALTER TABLE语句的大部分还可以利用：

l Operations that must be performed in a specific sequence, such as creating an index followed by a foreign key constraint that uses that index.

l 执行一个指定的序列，例如创建一个索引随后再创建一个使用这个索引的外键。

l Operations all using the same specific LOCK clause, that you want to either succeed or fail as a group.

l 操作使用的都是相同的LOCK字句，这样做为整体可以做到要么一起成功要么一起失败。

l Operations that cannot be performed in-place, that is, that still copy and rebuild the table.

l 没办是以in-place的方式执行的话，最多也是和以前一样对表进行复制和重建。

l Operations for which you specify ALGORITHM=COPY or old_alter_table=1, to force the table-copying behavior if needed for precise backward-compatibility in specialized scenarios.

l 当指定了ALGORITHM=COPY或者old_alter_table=1的时候，可以在一些特殊场景下强制进行表复制来达到精确的向后兼容的效果。

14.11.5 Examples of Online DDL

Here are code examples showing some operations whose performance, concurrency, and scalability are improved by the latest online DDL enhancements.

这里的例子显示online DDL带来的性能，并发，以及可扩展性的提升。

l Example 14.1,“Schema Setup Code for Online DDL Experiments” sets up tables named BIG_TABLE and SMALL_TABLE used in the subsequent examples.

l Example 14.1, “Schema Setup Code for Online DDL Experiments”建立了随后例子里使用的BIG_TABLE和SMALL_TABLE表。

l Example 14.2,“Speed and Efficiency of CREATE INDEX and DROP INDEX” illustrates the performance aspects of creating and dropping indexes.

l Example 14.2, “Speed and Efficiency of CREATE INDEX and DROP INDEX”说明了创建和删除索引时的性能提升。

l Example 14.3,“Concurrent DML During CREATE INDEX and DROP INDEX” shows queries and DML statements running during a DROP INDEX operation.

l Example 14.3, “Concurrent DML During CREATE INDEX and DROP INDEX”展示了在DROP INDEX过程中查询和DML语句仍然可以运行。

l Example 14.4,“Renaming a Column” demonstrates the speed improvement for renaming a column, and shows the care needed to keep the data type precisely the same when doing the rename operation.

l Example 14.4, “Renaming a Column”显示了重命名列操作的速度提升，同时也显示了再重命名操作过程中保持原有数据类型的重要性。

l Example 14.5,“Dropping Foreign Keys” demonstrates how foreign keys work with online DDL. Because two tables are involved in foreign key operations, there are extra locking considerations. Thus, tables with foreign keys sometimes have restrictions for online DDL operations.

l Example 14.5, “Dropping Foreign Keys”显示online DDL如何处理外检。因为外检操作涉及到两张表，需要考虑到会存在额外的锁。因此，使用外键的表有时会限制online DDL的操作。

l Example 14.6,“Changing Auto-Increment Value” demonstrates how auto-increment columns work with online DDL. Tables with auto-increment columns sometimes have restrictions for online DDL operations.

l Example 14.6, “Changing Auto-Increment Value”显示了online DDL操作如何处理自增长列。表的自增长列有时也会限制online DDL的操作。

l Example 14.7,“Controlling Concurrency with the LOCK Clause” demonstrates the options to permit or restrict concurrent queries and DML operations while an online DDL operation is in progress. It shows the situations when the DDL statement might wait, or the concurrent transaction might wait, or the concurrent transaction might cancel a DML statement due to a deadlock error.

l Example 14.7, “Controlling Concurrency with the LOCK Clause”显示了当online DDL操作在处理的过程中是可以允许或者限制并发的查询和DML操作的。这里的列子显示什么情况DDL语句会等待，或者是并发的事务需要等待，又或者是并发的事务由于死锁的原因会取消一个DML操作。

l Example 14.8,“Schema Setup Code for Online DDL Experiments” demonstrates creating and dropping multiple indexes in a single statement, which can be more efficient than using a separate statement for each index operation.

l Example 14.8, “Schema Setup Code for Online DDL Experiments”显示了在一条与距离创建或删除多个索引，这样比分开执行更有效率。

l Example 14.9,“Creating and Dropping the Primary Key” demonstrates how it is more efficient to define a primary key when creating the table, and relatively expensive to add one later.

l Example 14.9, “Creating and Dropping the Primary Key”显示了为什么在建表的时候定义索引更有效率，而之后添加的话则代价更大。

Example 14.1 Schema Setup Code for Online DDL Experiments

Here is the code that sets up the initial tables used in these demonstrations:

这里建立了范例需要的初始化表：

Setup code for the online DDL demonstration:

- Set up some config variables.

- Create 2 tables that are clones of one of the INFORMATION_SCHEMA tables

that always has some data. The "small" table has a couple of thousand rows.

For the "big" table, keep doubling the data until it reaches over a million rows.

- Set up a primary key for the sample tables, since we are demonstrating InnoDB aspects.

set autocommit = 0;

set foreign_key_checks = 1;

set global innodb_file_per_table = 1;

set old_alter_table=0;

prompt mysql:

use test;

\! echo "Setting up 'small' table:"

drop table if exists small_table;

create table small_table as select * from information_schema.columns;

alter table small_table add id int unsigned not null primary key auto_increment;

select count(id) from small_table;

\! echo "Setting up 'big' table:"

drop table if exists big_table;

create table big_table as select * from information_schema.columns;

show create table big_table\G

insert into big_table select * from big_table;

commit;

alter table big_table add id int unsigned not null primary key auto_increment;

select count(id) from big_table;

Running this code gives this output, condensed for brevity and with the most important points bolded:

这段代码运行后的输出，注意重要的粗体（红色）部分的内容：

Setting up 'small' table:

Query OK, 0 rows affected (0.01 sec)

Query OK, 1678 rows affected (0.13 sec)

Records: 1678 Duplicates: 0 Warnings: 0

Query OK, 1678 rows affected (0.07 sec)

Records: 1678 Duplicates: 0 Warnings: 0

+-----------+

| count(id) |

+-----------+

| 1678|

+-----------+

1 row in set (0.00 sec)

Setting up 'big' table:

Query OK, 0 rows affected (0.16 sec)

Query OK, 1678 rows affected (0.17 sec)

Records: 1678 Duplicates: 0 Warnings: 0

*************************** 1. row ***************************

Table: big_table

Create Table: CREATE TABLE `big_table` (

`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',

`COLUMN_DEFAULT` longtext CHARACTER SET utf8,

`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',

`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,

`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,

`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',

`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',

`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Query OK, 1678 rows affected (0.09 sec)

Records: 1678 Duplicates: 0 Warnings: 0

Query OK, 3356 rows affected (0.07 sec)

Records: 3356 Duplicates: 0 Warnings: 0

Query OK, 6712 rows affected (0.17 sec)

Records: 6712 Duplicates: 0 Warnings: 0

Query OK, 13424 rows affected (0.44 sec)

Records: 13424 Duplicates: 0 Warnings: 0

Query OK, 26848 rows affected (0.63 sec)

Records: 26848 Duplicates: 0 Warnings: 0

Query OK, 53696 rows affected (1.72 sec)

Records: 53696 Duplicates: 0 Warnings: 0

Query OK, 107392 rows affected (3.02 sec)

Records: 107392 Duplicates: 0 Warnings: 0

Query OK, 214784 rows affected (6.28 sec)

Records: 214784 Duplicates: 0 Warnings: 0

Query OK, 429568 rows affected (13.25 sec)

Records: 429568 Duplicates: 0 Warnings: 0

Query OK, 859136 rows affected (28.16 sec)

Records: 859136 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.03 sec)

Query OK, 1718272 rows affected (1 min 9.22 sec)

Records: 1718272 Duplicates: 0 Warnings: 0

+-----------+

| count(id) |

+-----------+

| 1718272|

+-----------+

1 row in set (1.75 sec)

Example 14.2 Speed and Efficiency of CREATE INDEX and DROP INDEX

Here is a sequence of statements demonstrating the relative speed of CREATE INDEX and DROP INDEX statements. For a small table, the elapsed time is less than a second whether we use the fast or slow technique, so we look at the“rows affected”output to verify which operations can avoid the table rebuild. For a large table, the difference in efficiency is obvious because skipping the table rebuild saves substantial time.

这里一段连续的语句显示了CREATE INDEX和DROP INDEX相关的执行速度。对于一个小表，无论使用的什么方法使用的时间都小于一秒，但是在“rows affected”输出里可以看到操作是否避免的表重建。对于一个大表，两种方法的效率是有显著地不同的，而且跳过表重建能够节省下大量的时间。

\! clear

\! echo "=== Create and drop index (small table, new/fast technique) ==="

\! echo

\! echo "Data size (kilobytes) before index created: "

\! du -k data/test/small_table.ibd

create index i_dtyp_small on small_table (data_type), algorithm=inplace;

\! echo "Data size after index created: "

\! du -k data/test/small_table.ibd

drop index i_dtyp_small on small_table, algorithm=inplace;

-- Compare against the older slower DDL.

\! echo "=== Create and drop index (small table, old/slow technique) ==="

\! echo

\! echo "Data size (kilobytes) before index created: "

\! du -k data/test/small_table.ibd

create index i_dtyp_small on small_table (data_type), algorithm=copy;

\! echo "Data size after index created: "

\! du -k data/test/small_table.ibd

drop index i_dtyp_small on small_table, algorithm=copy;

-- In the above example, we examined the "rows affected" number,

-- ideally looking for a zero figure. Let's try again with a larger

-- sample size, where we'll see that the actual time taken can

-- vary significantly.

\! echo "=== Create and drop index (big table, new/fast technique) ==="

\! echo

\! echo "Data size (kilobytes) before index created: "

\! du -k data/test/big_table.ibd

create index i_dtyp_big on big_table (data_type), algorithm=inplace;

\! echo "Data size after index created: "

\! du -k data/test/big_table.ibd

drop index i_dtyp_big on big_table, algorithm=inplace;

\! echo "=== Create and drop index (big table, old/slow technique) ==="

\! echo

\! echo "Data size (kilobytes) before index created: "

\! du -k data/test/big_table.ibd

create index i_dtyp_big on big_table (data_type),algorithm=copy;

\! echo "Data size after index created: "

\! du -k data/test/big_table.ibd

drop index i_dtyp_big on big_table, algorithm=copy;

Running this code gives this output, condensed for brevity and with the most important points bolded:

上面脚本的输出如下，注意加粗（红色）的重要部分：

Query OK, 0 rows affected (0.00 sec)

=== Create and drop index (small table, new/fast technique) ===

Data size (kilobytes) before index created:

384 data/test/small_table.ibd

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

Data size after index created:

432 data/test/small_table.ibd

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

=== Create and drop index (small table, old/slow technique) ===

Data size (kilobytes) before index created:

432 data/test/small_table.ibd

Query OK, 1678 rows affected (0.12 sec)

Records: 1678 Duplicates: 0 Warnings: 0

Data size after index created:

448 data/test/small_table.ibd

Query OK, 1678 rows affected (0.10 sec)

Records: 1678 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

=== Create and drop index (big table, new/fast technique) ===

Data size (kilobytes) before index created:

315392 data/test/big_table.ibd

Query OK, 0 rows affected (33.32 sec)

Records: 0 Duplicates: 0 Warnings: 0

Data size after index created:

335872 data/test/big_table.ibd

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

=== Create and drop index (big table, old/slow technique) ===

Data size (kilobytes) before index created:

335872 data/test/big_table.ibd

Query OK, 1718272 rows affected (1 min 5.01 sec)

Records: 1718272 Duplicates: 0 Warnings: 0

Data size after index created:

348160 data/test/big_table.ibd

Query OK, 1718272 rows affected (46.59 sec)

Records: 1718272 Duplicates: 0 Warnings: 0

Example 14.3 Concurrent DML During CREATE INDEX and DROP INDEX

Here are some snippets of code that are run in separate mysql sessions connected to the same database, to illustrate DML statements (insert, update, or delete) running at the same time as CREATE INDEX and DROP INDEX.

这里的代码段表示的是不同的session连接同一个数据库，来演示CREATE INDEX和DROP INDEX执行的同时并发DML的情况。

CREATE INDEX statement to run against a table while

insert/update/delete statements are modifying the

column being indexed.

-- Run this script in one session, while simultaneously creating and dropping

-- an index on test/big_table.table_name in another session.

use test;

create index i_concurrent on big_table(table_name);

DROP INDEX statement to run against a table while

insert/update/delete statements are modifying the

column being indexed.

-- Run this script in one session, while simultaneously creating and dropping

-- an index on test/big_table.table_name in another session.

use test;

drop index i_concurrent on big_table;

Some queries and insert/update/delete statements to run against a table

while an index is being created or dropped. Previously, these operations

would have stalled during the index create/drop period and possibly

timed out or deadlocked.

-- Run this script in one session, while simultaneously creating and dropping

-- an index on test/big_table.table_name in another session.

-- In the test instance, that column has about 1.7M rows, with 136 different values.

-- Sample values: COLUMNS (20480), ENGINES (6144), EVENTS (24576), FILES (38912),

-- TABLES (21504), VIEWS (10240).

set autocommit = 0;

use test;

select distinct character_set_name from big_table where table_name = 'FILES';

delete from big_table where table_name = 'FILES';

select distinct character_set_name from big_table where table_name = 'FILES';

-- I'll issue the final rollback interactively, not via script,

-- the better to control the timing.

-- rollback;

Running this code gives this output, condensed for brevity and with the most important points bolded:

输出如下，注意加粗的重要部分（红色斜体）：

mysql: source concurrent_ddl_create.sql

Database changed

Query OK, 0 rows affected(1 min 25.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql: source concurrent_ddl_drop.sql

Database changed

Query OK, 0 rows affected (24.98 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql: source concurrent_dml.sql

Query OK, 0 rows affected (0.00 sec)

Database changed

+--------------------+

| character_set_name |

+--------------------+

| NULL |

| utf8 |

+--------------------+

2 rows in set (0.32 sec)

Query OK, 38912 rows affected (1.84 sec)

Empty set (0.01 sec)

mysql: rollback;

Query OK, 0 rows affected (1.05 sec)

Example 14.4 Renaming a Column

Here is a demonstration of using ALTER TABLE to rename a column. We use the new, fast DDL mechanism to change the name, then the old, slow DDL mechanism (with old_alter_table=1) to restore the original column name.

这里演示了使用ALTER TABLE重命名一个列。首先，我们会使用新的，快速的DDL机制来修改名字，然后再用老的，慢的DDL机制（old_alter_table=1）同恢复到原来的名字。

Notes:

l Because the syntax for renaming a column also involves re-specifying the data type, be careful to specify exactly the same data type to avoid a costly table rebuild. In this case, we checked the output of show create table table\G and copied any clauses such as CHARACTER SET and NOT NULL from the original column definition.

l 因为重命名列的语法会涉及到对数据类型的重新指定，所以要特别小心，要指定和原来完全相同的数据类型，这样才能避免代价极高的表重建。在这种情况下，我们可以从show create table table\G的输出里检查结果，这里面包含了所有表定义项。

l Again, renaming a column for a small table is fast enough that we need to examine the“rows affected”number to verify that the new DDL mechanism is more efficient than the old one. With a big table, the difference in elapsed time makes the improvement obvious.

l 因为对一个小表进行重命名列是非常快速的，所以我们只能通过“rows affected”的结果来验证DDL语句到底使用了哪种机制。当然对于大表，两种不同机制在执行时间上还是有明显不同的。

Run through a sequence of 'rename column' statements.

Because this operation involves only metadata, not table data,

it is fast for big and small tables, with new or old DDL mechanisms.

\! clear

\! echo "Rename column (fast technique, small table):"

alter table small_table change `IS_NULLABLE` `NULLABLE` varchar(3) character

set utf8 not null, algorithm=inplace;

\! echo "Rename back to original name (slow technique):"

alter table small_table change `NULLABLE` `IS_NULLABLE` varchar(3) character

set utf8 not null, algorithm=copy;

\! echo "Rename column (fast technique, big table):"

alter table big_table change `IS_NULLABLE` `NULLABLE` varchar(3) character

set utf8 not null, algorithm=inplace;

\! echo "Rename back to original name (slow technique):"

alter table big_table change `NULLABLE` `IS_NULLABLE` varchar(3) character

set utf8 not null, algorithm=copy;

Running this code gives this output, condensed for brevity and with the most important points bolded:

运行结果如下，注意加粗（红色斜体）部分：

Rename column (fast technique, small table):

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

Rename back to original name (slow technique):

Query OK, 0 rows affected (0.00 sec)

Query OK, 1678 rows affected (0.35 sec)

Records: 1678 Duplicates: 0 Warnings: 0

Rename column (fast technique, big table):

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

Rename back to original name (slow technique):

Query OK, 0 rows affected (0.00 sec)

Query OK, 1718272 rows affected (1 min 0.00 sec)

Records: 1718272 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Example 14.5 Dropping Foreign Keys

Here is a demonstration of foreign keys, including improvement to the speed of dropping a foreign key constraint.

这里演示了外键，以及删除外键时的速度改善。

Demonstrate aspects of foreign keys that are or aren't affected by the DDL improvements.

- Create a new table with only a few values to serve as the parent table.

- Set up the 'small' and 'big' tables as child tables using a foreign key.

- Verify that the ON DELETE CASCADE clause makes changes ripple from parent to child tables.

- Drop the foreign key constraints, and optionally associated indexes. (This is the operation that is sped up.)

\! clear

-- Make sure foreign keys are being enforced, and allow

-- rollback after doing some DELETEs that affect both

-- parent and child tables.

set foreign_key_checks = 1;

set autocommit = 0;

-- Create a parent table, containing values that we know are already present

-- in the child tables.

drop table if exists schema_names;

create table schema_names (id int unsigned not null primary key auto_increment, schema_name

varchar(64) character set utf8 not null, index i_schema (schema_name)) as select distinct

table_schema schema_name from small_table;

show create table schema_names\G

show create table small_table\G

show create table big_table\G

-- Creating the foreign key constraint still involves a table rebuild when foreign_key_checks=1,

-- as illustrated by the "rows affected" figure.

alter table small_table add constraint small_fk foreign key i_table_schema (table_schema)

references schema_names(schema_name) on delete cascade;

alter table big_table add constraint big_fk foreign key i_table_schema (table_schema)

references schema_names(schema_name) on delete cascade;

show create table small_table\G

show create table big_table\G

select schema_name from schema_names order by schema_name;

select count(table_schema) howmany, table_schema from small_table group by table_schema;

select count(table_schema) howmany, table_schema from big_table group by table_schema;

-- big_table is the parent table.

-- schema_names is the parent table.

-- big_table is the child table.

-- (One row in the parent table can have many "children" in the child table.)

-- Changes to the parent table can ripple through to the child table.

-- For example, removing the value 'test' from schema_names.schema_name will

-- result in the removal of 20K or so rows from big_table.

delete from schema_names where schema_name = 'test';

select schema_name from schema_names order by schema_name;

select count(table_schema) howmany, table_schema from small_table group by table_schema;

select count(table_schema) howmany, table_schema from big_table group by table_schema;

-- Because we've turned off autocommit, we can still get back those deleted rows

-- if the DELETE was issued by mistake.

rollback;

select schema_name from schema_names order by schema_name;

select count(table_schema) howmany, table_schema from small_table group by table_schema;

select count(table_schema) howmany, table_schema from big_table group by table_schema;

-- All of the cross-checking between parent and child tables would be

-- deadly slow if there wasn't the requirement for the corresponding

-- columns to be indexed!

-- But we can get rid of the foreign key using a fast operation

-- that doesn't rebuild the table.

-- If we didn't specify a constraint name when setting up the foreign key, we would

-- have to find the auto-generated name such as 'big_table_ibfk_1' in the

-- output from 'show create table'.

-- For the small table, drop the foreign key and the associated index.

-- Having an index on a small table is less critical.

\! echo "DROP FOREIGN KEY and INDEX from small_table:"

alter table small_table drop foreign key small_fk, drop index small_fk;

-- For the big table, drop the foreign key and leave the associated index.

-- If we are still doing queries that reference the indexed column, the index is

-- very important to avoid a full table scan of the big table.

\! echo "DROP FOREIGN KEY from big_table:"

alter table big_table drop foreign key big_fk;

show create table small_table\G

show create table big_table\G

Running this code gives this output, condensed for brevity and with the most important points bolded:

输出结果如下，注意加粗（红色斜体）部分：

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

*************************** 1. row ***************************

Table: schema_names

Create Table: CREATE TABLE `schema_names` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`schema_name` varchar(64) CHARACTER SET utf8 NOT NULL,

PRIMARY KEY (`id`),

KEY `i_schema` (`schema_name`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

*************************** 1. row ***************************

Table: small_table

Create Table: CREATE TABLE `small_table` (

`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',

`COLUMN_DEFAULT` longtext CHARACTER SET utf8,

`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL,

`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,

`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,

`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',

`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',

`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1679 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

*************************** 1. row ***************************

Table: big_table

Create Table: CREATE TABLE `big_table` (

`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',

`COLUMN_DEFAULT` longtext CHARACTER SET utf8,

`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL,

`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,

`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,

`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',

`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',

`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`),

KEY `big_fk` (`TABLE_SCHEMA`)

) ENGINE=InnoDB AUTO_INCREMENT=1718273 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Query OK, 1678 rows affected (0.10 sec)

Records: 1678 Duplicates: 0 Warnings: 0

Query OK, 1718272 rows affected (1 min 14.54 sec)

Records: 1718272 Duplicates: 0 Warnings: 0

*************************** 1. row ***************************

Table: small_table

Create Table: CREATE TABLE `small_table` (

`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',

`COLUMN_DEFAULT` longtext CHARACTER SET utf8,

`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL,

`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,

`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,

`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',

`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',

`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`),

KEY `small_fk` (`TABLE_SCHEMA`),

CONSTRAINT `small_fk` FOREIGN KEY (`TABLE_SCHEMA`)

REFERENCES `schema_names` (`schema_name`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1679 DEFAULT CHARSET=latin1

1 row in set (0.12 sec)

*************************** 1. row ***************************

Table: big_table

Create Table: CREATE TABLE `big_table` (

`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',

`COLUMN_DEFAULT` longtext CHARACTER SET utf8,

`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL,

`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,

`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,

`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',

`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',

`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`),

KEY `big_fk` (`TABLE_SCHEMA`),

CONSTRAINT `big_fk` FOREIGN KEY (`TABLE_SCHEMA`)

REFERENCES `schema_names` (`schema_name`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1718273 DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

+--------------------+

| schema_name |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

+---------+--------------------+

| howmany | table_schema |

+---------+--------------------+

| 563 | information_schema |

| 286 | mysql |

| 786 | performance_schema |

| 43 | test |

+---------+--------------------+

4 rows in set (0.01 sec)

+---------+--------------------+

| howmany | table_schema |

+---------+--------------------+

| 576512 | information_schema |

| 292864 | mysql |

| 804864 | performance_schema |

| 44032 | test |

+---------+--------------------+

4 rows in set (2.10 sec)

Query OK, 1 row affected (1.52 sec)

+--------------------+

| schema_name |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

+---------+--------------------+

| howmany | table_schema |

+---------+--------------------+

| 563 | information_schema |

| 286 | mysql |

| 786 | performance_schema |

+---------+--------------------+

3 rows in set (0.00 sec)

+---------+--------------------+

| howmany | table_schema |

+---------+--------------------+

| 576512 | information_schema |

| 292864 | mysql |

| 804864 | performance_schema |

+---------+--------------------+

3 rows in set (1.74 sec)

Query OK, 0 rows affected (0.60 sec)

+--------------------+

| schema_name |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

+---------+--------------------+

| howmany | table_schema |

+---------+--------------------+

| 563 | information_schema |

| 286 | mysql |

| 786 | performance_schema |

| 43 | test |

+---------+--------------------+

4 rows in set (0.01 sec)

+---------+--------------------+

| howmany | table_schema |

+---------+--------------------+

| 576512 | information_schema |

| 292864 | mysql |

| 804864 | performance_schema |

| 44032 | test |

+---------+--------------------+

4 rows in set (1.59 sec)

DROP FOREIGN KEY and INDEX from small_table:

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

DROP FOREIGN KEY from big_table:

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

*************************** 1. row ***************************

Table: small_table

Create Table: CREATE TABLE `small_table` (

`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',

`COLUMN_DEFAULT` longtext CHARACTER SET utf8,

`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL,

`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,

`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,

`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',

`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',

`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1679 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

*************************** 1. row ***************************

Table: big_table

Create Table: CREATE TABLE `big_table` (

`TABLE_CATALOG` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_SCHEMA` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_NAME` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',

`COLUMN_DEFAULT` longtext CHARACTER SET utf8,

`IS_NULLABLE` varchar(3) CHARACTER SET utf8 NOT NULL,

`DATA_TYPE` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,

`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,

`DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,

`CHARACTER_SET_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLLATION_NAME` varchar(32) CHARACTER SET utf8 DEFAULT NULL,

`COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,

`COLUMN_KEY` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT '',

`EXTRA` varchar(30) CHARACTER SET utf8 NOT NULL DEFAULT '',

`PRIVILEGES` varchar(80) CHARACTER SET utf8 NOT NULL DEFAULT '',

`COLUMN_COMMENT` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '',

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`),

KEY `big_fk` (`TABLE_SCHEMA`)

) ENGINE=InnoDB AUTO_INCREMENT=1718273 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Example 14.6 Changing Auto-Increment Value

Here is an illustration of increasing the auto-increment lower limit for a table column, demonstrating how this operation now avoids a table rebuild, plus other facts about InnoDB auto-increment columns.

这里说明了自增长列的下限，以及演示了这个操作如何避免重建表，还有一些其他对InnoDB自增长列的有影响的因素。

If this script is run after foreign_key.sql, the schema_names table is

already set up. But to allow this script to run multiple times without

running into duplicate ID errors, we set up the schema_names table

all over again.

\! clear

\! echo "=== Adjusting the Auto-Increment Limit for a Table ==="

\! echo

drop table if exists schema_names;

create table schema_names (id int unsigned not null primary key auto_increment,

schema_name varchar(64) character set utf8 not null, index i_schema (schema_name))

as select distinct table_schema schema_name from small_table;

\! echo "Initial state of schema_names table."

\! echo "AUTO_INCREMENT is included in SHOW CREATE TABLE output."

\! echo "Note how MySQL reserved a block of IDs."

\! echo "Only 4 IDs are needed in this transaction. The next inserted values get IDs 8 and 9."

show create table schema_names\G

select * from schema_names order by id;

\! echo "Inserting even a tiny amount of data can produce gaps in the ID sequence."

insert into schema_names (schema_name) values ('eight'), ('nine');

\! echo "Bumping auto-increment lower limit to 20 (fast mechanism):"

alter table schema_names auto_increment=20, algorithm=inplace;

\! echo "Inserting 2 rows that should get IDs 20 and 21:"

insert into schema_names (schema_name) values ('foo'), ('bar');

commit;

\! echo "Bumping auto-increment lower limit to 30 (slow mechanism):"

alter table schema_names auto_increment=30, algorithm=copy;

\! echo "Inserting 2 rows that should get IDs 30 and 31:"

insert into schema_names (schema_name) values ('bletch'),('baz');

commit;

select * from schema_names order by id;

\! echo "Final state of schema_names table."

\! echo "AUTO_INCREMENT value shows the next inserted row would get ID=32."

show create table schema_names\G

Running this code gives this output, condensed for brevity and with the most important points bolded:

输出结果，注意加粗（红色斜体）部分：

=== Adjusting the Auto-Increment Limit for a Table ===

Query OK, 0 rows affected (0.01 sec)

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

Initial state of schema_names table.

AUTO_INCREMENT is included in SHOW CREATE TABLE output.

Note how MySQL reserved a block of IDs.

Only 4 IDs are needed in this transaction. The next inserted values get IDs 8 and 9.

*************************** 1. row ***************************

Table: schema_names

Create Table: CREATE TABLE `schema_names` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`schema_name` varchar(64) CHARACTER SET utf8 NOT NULL,

PRIMARY KEY (`id`),

KEY `i_schema` (`schema_name`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

+----+--------------------+

| id | schema_name |

+----+--------------------+

| 1 | information_schema |

| 2 | mysql |

| 3 | performance_schema |

| 4 | test |

+----+--------------------+

4 rows in set (0.00 sec)

Inserting even a tiny amount of data can produce gaps in the ID sequence.

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Bumping auto-increment lower limit to 20 (fast mechanism):

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Inserting 2 rows that should get IDs 20 and 21:

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Bumping auto-increment lower limit to 30 (slow mechanism):

Query OK, 8 rows affected (0.02 sec)

Records: 8 Duplicates: 0 Warnings: 0

Inserting 2 rows that should get IDs 30 and 31:

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.01 sec)

+----+--------------------+

| id | schema_name |

+----+--------------------+

| 1 | information_schema |

| 2 | mysql |

| 3 | performance_schema |

| 4 | test |

| 8 | eight |

| 9 | nine |

| 20 | foo |

| 21 | bar |

| 30 | bletch |

| 31 | baz |

+----+--------------------+

10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Final state of schema_names table.

AUTO_INCREMENT value shows the next inserted row would get ID=32.

*************************** 1. row ***************************

Table: schema_names

Create Table: CREATE TABLE `schema_names` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`schema_name` varchar(64) CHARACTER SET utf8 NOT NULL,

PRIMARY KEY (`id`),

KEY `i_schema` (`schema_name`)

) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Example 14.7 Controlling Concurrency with the LOCK Clause

This example shows how to use the LOCK clause of the ALTER TABLE statement to allow or deny concurrent access to the table while an online DDL operation is in progress. The clause has settings that allow queries and DML statements (LOCK=NONE), just queries (LOCK=SHARED), or no concurrent access at all (LOCK=EXCLUSIVE).

这个例子显示了如何使用ALTER TABLE里的LOCK子句来控制online DDL执行过程的其他session对表的并发访问。这个设定可以是同时允许查询和DML(LOCK=NONE)，以及只允许查询(LOCK=SHARED)，或者是完全不允许并行(LOCK=EXCLUSIVE)。

In one session, we run a succession of ALTER TABLE statements to create and drop an index, using different values for the LOCK clause to see what happens with waiting or deadlocking in either session. We are using the same BIG_TABLE table as in previous examples, starting with approximately 1.7 million rows. For illustration purposes, we will index and query the IS_NULLABLE column. (Although in real life it would be silly to make an index for a tiny column with only 2 distinct values.)

一个session里，我们运行一个ALTER TABLE语句来创建删除索引，并使用不同LOCK子句值来查看是否会有锁等待或者死锁。我们使用的还是之前例子里的BIG_TABLE表，大概有170万行记录。为了说明目的，我们会在IS_NULLABLE列上加索引并进行查询。（在实际情况里为一个只有两个distinct值的列建索引是非常愚蠢的（也要看情况的）。）

mysql: desc big_table;

+--------------------------+---------------------+------+-----+---------+----------------+

+--------------------------+---------------------+------+-----+---------+----------------+

| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |

...

+--------------------------+---------------------+------+-----+---------+----------------+

21 rows in set (0.14 sec)

mysql: alter table big_table add index i1(is_nullable);

Query OK, 0 rows affected (20.71 sec)

mysql: alter table big_table drop index i1;

Query OK, 0 rows affected (0.02 sec)

mysql: alter table big_table add index i1(is_nullable),lock=exclusive;

Query OK, 0 rows affected (19.44 sec)

mysql: alter table big_table drop index i1;

Query OK, 0 rows affected (0.03 sec)

mysql: alter table big_table add index i1(is_nullable), lock=shared;

Query OK, 0 rows affected (16.71 sec)

mysql: alter table big_table drop index i1;

Query OK, 0 rows affected (0.05 sec)

mysql: alter table big_table add index i1(is_nullable),lock=none;

Query OK, 0 rows affected (12.26 sec)

mysql: alter table big_table drop index i1;

Query OK, 0 rows affected (0.01 sec)

... repeat statements like the above while running queries ...

... and DML statements at the same time in another session ...

Nothing dramatic happens in the session running the DDL statements. Sometimes, an ALTER TABLE takes unusually long because it is waiting for another transaction to finish, when that transaction modified the table during the DDL or queried the table before the DDL:

在DDL语句执行的时候没有什么戏剧性的事情发送。有时，ALTER TABLE语句要花费很长的时间是要等待其他的事务结束：

mysql: alter table big_table add index i1(is_nullable), lock=none;

Query OK, 0 rows affected (59.27 sec)

mysql: -- The previous ALTER took so long because it was waiting for all the concurrent

mysql: -- transactions to commit or roll back.

mysql: alter table big_table drop index i1;

Query OK, 0 rows affected (41.05 sec)

mysql: -- Even doing a SELECT on the table in the other session first causes

mysql: -- the ALTER TABLE above to stall until the transaction

mysql: -- surrounding the SELECT is committed or rolled back.

Here is the log from another session running concurrently, where we issue queries and DML statements against the table before, during, and after the DDL operations shown in the previous listings. This first listing shows queries only. We expect the queries to be allowed during DDL operations using LOCK=NONE or LOCK=SHARED, and for the query to wait until the DDL is finished if the ALTER TABLE statement includes LOCK=EXCLUSIVE.

这是另一个正在并行运行的session的日志输出，我们会在上文的DDL操作之前，处理过程中，以及之后执行查询和DML语句。首先只是查询。我们期望在DDL执行期间允许查询（LOCK=NONE or LOCK=SHARED），以及查询需要等待到DDL语句完成之后才能继续（LOCK=EXCLUSIVE）。

mysql: show variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.01 sec)

mysql: -- A trial query before any ADD INDEX in the other session:

mysql: -- Note: because autocommit is enabled, each

mysql: -- transaction finishes immediately after the query.

mysql: select distinct is_nullable from big_table;

+-------------+

| is_nullable |

+-------------+

| NO |

| YES |

+-------------+

2 rows in set (4.49 sec)

mysql: -- Index is being created with LOCK=EXCLUSIVE on the ALTER statement.

mysql: -- The query waits until the DDL is finished before proceeding.

mysql: select distinct is_nullable from big_table;

+-------------+

| is_nullable |

+-------------+

| NO |

| YES |

+-------------+

2 rows in set (17.26 sec)

mysql: -- Index is being created with LOCK=SHARED on the ALTER statement.

mysql: -- The query returns its results while the DDL is in progress.

mysql: -- The same thing happens with LOCK=NONE on the ALTER statement.

mysql: select distinct is_nullable from big_table;

+-------------+

| is_nullable |

+-------------+

| NO |

| YES |

+-------------+

2 rows in set (3.11 sec)

mysql: -- Once the index is created, and with no DDL in progress,

mysql: -- queries referencing the indexed column are very fast:

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 411648 |

+----------+

1 row in set (0.20 sec)

mysql: select distinct is_nullable from big_table;

+-------------+

| is_nullable |

+-------------+

| NO |

| YES |

+-------------+

2 rows in set (0.00 sec)

Now in this concurrent session, we run some transactions including DML statements, or a combination of DML statements and queries. We use DELETE statements to illustrate predictable, verifiable changes to the table. Because the transactions in this part can span multiple statements, we run these tests with autocommit turned off.

在这个并发的session里，事务里会包括一些DML语句，或者是DML和查询的混合。我们使用DELETE语句来说明可预见的，能证实的修改。因为这个事务里会有多个语句，所以会把autocommit关闭。

mysql: set global autocommit = off;

Query OK, 0 rows affected (0.00 sec)

mysql: -- Count the rows that will be involved in our DELETE statements:

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 411648 |

+----------+

1 row in set (0.95 sec)

mysql: -- After this point, any DDL statements back in the other session

mysql: -- stall until we commit or roll back.

mysql: delete from big_table where is_nullable = 'YES' limit 11648;

Query OK, 11648 rows affected (0.14 sec)

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 400000 |

+----------+

1 row in set (1.04 sec)

mysql: rollback;

Query OK, 0 rows affected (0.09 sec)

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 411648 |

+----------+

1 row in set (0.93 sec)

mysql: -- OK, now we're going to try that during index creation with LOCK=NONE.

mysql: delete from big_table where is_nullable = 'YES' limit 11648;

Query OK, 11648 rows affected (0.21 sec)

mysql: -- We expect that now there will be 400000 'YES' rows left:

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 400000 |

+----------+

1 row in set (1.25 sec)

mysql: -- In the other session, the ALTER TABLE is waiting before finishing,

mysql: -- because _this_ transaction hasn't committed or rolled back yet.

mysql: rollback;

Query OK, 0 rows affected (0.11 sec)

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 411648 |

+----------+

1 row in set (0.19 sec)

mysql: -- The ROLLBACK left the table in the same state we originally found it.

mysql: -- Now let's make a permanent change while the index is being created,

mysql: -- again with ALTER TABLE ... , LOCK=NONE.

mysql: -- First, commit so the DROP INDEX in the other shell can finish;

mysql: -- the previous SELECT started a transaction that accessed the table.

mysql: commit;

Query OK, 0 rows affected (0.00 sec)

mysql: -- Now we add the index back in the other shell, then issue DML in this one

mysql: -- while the DDL is running.

mysql: delete from big_table where is_nullable = 'YES' limit 11648;

Query OK, 11648 rows affected (0.23 sec)

mysql: commit;

Query OK, 0 rows affected (0.01 sec)

mysql: -- In the other shell, the ADD INDEX has finished.

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 400000 |

+----------+

1 row in set (0.19 sec)

mysql: -- At the point the new index is finished being created, it contains entries

mysql: -- only for the 400000 'YES' rows left when all concurrent transactions are finished.

mysql:

mysql: -- Now we will run a similar test, while ALTER TABLE ... , LOCK=SHARED is running.

mysql: -- We expect a query to complete during the ALTER TABLE, but for the DELETE

mysql: -- to run into some kind of issue.

mysql: commit;

Query OK, 0 rows affected (0.00 sec)

mysql: -- As expected, the query returns results while the LOCK=SHARED DDL is running:

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 400000 |

+----------+

1 row in set (2.07 sec)

mysql: -- The DDL in the other session is not going to finish until this transaction

mysql: -- is committed or rolled back. If we tried a DELETE now and it waited because

mysql: -- of LOCK=SHARED on the DDL, both transactions would wait forever (deadlock).

mysql: -- MySQL detects this condition and cancels the attempted DML statement.

mysql: delete from big_table where is_nullable = 'YES' limit 100000;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql: -- The transaction here is still going, so in the other shell, the ADD INDEX operation

mysql: -- is waiting for this transaction to commit or roll back.

mysql: rollback;

Query OK, 0 rows affected (0.00 sec)

mysql: -- Now let's try issuing a query and some DML, on one line, while running

mysql: -- ALTER TABLE ... , LOCK=EXCLUSIVE in the other shell.

mysql: -- Notice how even the query is held up until the DDL is finished.

mysql: -- By the time the DELETE is issued, there is no conflicting access

mysql: -- to the table and we avoid the deadlock error.

mysql: select count(*) from big_table where is_nullable = 'YES'; delete from big_table

where is_nullable = 'YES' limit 100000;

+----------+

| count(*) |

+----------+

| 400000 |

+----------+

1 row in set (15.98 sec)

Query OK, 100000 rows affected (2.81 sec)

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 300000 |

+----------+

1 row in set (0.17 sec)

mysql: rollback;

Query OK, 0 rows affected (1.36 sec)

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 400000 |

+----------+

1 row in set (0.19 sec)

mysql: commit;

Query OK, 0 rows affected (0.00 sec)

mysql: -- Next, we try ALTER TABLE ... , LOCK=EXCLUSIVE in the other session

mysql: -- and only issue DML, not any query, in the concurrent transaction here.

mysql: delete from big_table where is_nullable = 'YES' limit 100000;

Query OK, 100000 rows affected (16.37 sec)

mysql: -- That was OK because the ALTER TABLE did not have to wait for the transaction

mysql: -- here to complete. The DELETE in this session waited until the index was ready.

mysql: select count(*) from big_table where is_nullable = 'YES';

+----------+

| count(*) |

+----------+

| 300000 |

+----------+

1 row in set (0.16 sec)

mysql: commit;

Query OK, 0 rows affected (0.00 sec)

In the preceding example listings, we learned that:

根据前面的例子，我们可以得到以下结论：

l The LOCK clause for ALTER TABLE is set off from the rest of the statement by a comma.

l LOCK子句是以逗号为语法连在ALTER TABLE语句后面的。

l Online DDL operations might wait before starting, until any prior transactions that access the table are committed or rolled back.

l Online DDL操作开始开始之前还是要等待表上的事务结束之后才能继续。

l Online DDL operations might wait before completing, until any concurrent transactions that access the table are committed or rolled back.

l Online DDL完成之前也需要等待其他事务结束才能继续。

l While an online DDL operation is running, concurrent queries are relatively straightforward, as long as the ALTER TABLE statement uses LOCK=NONE or LOCK=SHARED.

l 当一个online DDL操作正在运行的时候，并行查询相对是比较简单的，只要在ALTER TABLE里使用LOCK=NONE或者LOCK=SHARED。

l Pay attention to whether autocommit is turned on or off. If it is turned off, be careful to end transactions in other sessions (even just queries) before performing DDL operations on the table.

l 要注意autocommit是否打开。如果是关闭的话，那要小心其他session的事务（包括查询）要在执行DDL操作之前结束掉。

l With LOCK=SHARED, concurrent transactions that mix queries and DML could encounter deadlock errors and have to be restarted after the DDL is finished.

l 使用LOCK=SHARED的时候，混合了DML和查询的并发事务可能会有死锁，需要在DDL完成之后重新开启。

l With LOCK=NONE, concurrent transactions can freely mix queries and DML. The DDL operation waits until the concurrent transactions are committed or rolled back.

l 使用LOCK=NONE的时候，并行的事务可以随意混合查询和DML。DDL操作会要等到并行的事务完成之后才会继续。

l With LOCK=EXCLUSIVE, concurrent transactions can freely mix queries and DML, but those transactions wait until the DDL operation is finished before they can access the table.

l 使用LOCK=EXCLUSIVE，并行的事务可以随意混合查询和DML，但是这些事务需要等到DDL操作结束之后才能访问表。

Example 14.8 Schema Setup Code for Online DDL Experiments

You can create multiple indexes on a table with one ALTER TABLE statement. This is relatively efficient, because the clustered index of the table needs to be scanned only once (although the data is sorted separately for each new index). For example:

你可以在一个ALTER TABLE语句里创建多个索引。这样相对更为高效，因为表的clustered index只要扫描一次就可以了。例如：

CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;

INSERT INTO T1 VALUES (1,2,'a'), (2,3,'b'), (3,2,'c'), (4,3,'d'), (5,2,'e');

COMMIT;

ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);

The above statements create table T1 with the primary key on column A, insert several rows, then build two new indexes on columns B and C. If there were many rows inserted into T1 before the ALTER TABLE statement, this approach is much more efficient than creating all the secondary indexes before loading the data.

上面的语句创建了表T1，并在A列作为主键，并插入了一些行记录，然后再在B和C列上分别添加了新的索引。这样插入的步奏在ALTER TABLE创建索引之前，会比先创建secondary index再加载数据更为高效。

Because dropping InnoDB secondary indexes also does not require any copying of table data, it is equally efficient to drop multiple indexes with a single ALTER TABLE statement or multiple DROP INDEX statements:

因为删除InnoDB的secondary index不需要表复制，所以一次删除多个索引或者多个索引分开删除的效率是差不多的：

ALTER TABLE T1 DROP INDEX B, DROP INDEX C;

or:

DROP INDEX B ON T1;

DROP INDEX C ON T1;

Example 14.9 Creating and Dropping the Primary Key

Restructuring the clustered index for an InnoDB table always requires copying the table data. Thus, it is best to define the primary key when you create a table, rather than issuing ALTER TABLE ... ADD PRIMARY KEY later, to avoid rebuilding the table.

重组InnoDB的clustered index肯定是要进行表复制的。因此，最好在建表的时候就定义好主键，这样比之后再执行ALTER TABLE ... ADD PRIMARY KEY更好，可以避免重建表。

Defining a PRIMARY KEY later causes the data to be copied, as in the following example:

之后定义主键会引起数据的复制，如下面的例子：

CREATE TABLE T2 (A INT, B INT);

INSERT INTO T2 VALUES (NULL, 1);

ALTER TABLE T2 ADD PRIMARY KEY (B);

When you create a UNIQUE or PRIMARY KEY index, MySQL must do some extra work. For UNIQUE indexes, MySQL checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, MySQL also checks that none of the PRIMARY KEY columns contains a NULL.

当你创建了一个UNIQUE或者PRIMARY KEY索引，MySQL必须要做一些额外的工作。对于UNIQUE索引，MySQL会检查表里是否会存在重复键。对于PRIMARY KEY索引，MySQL必须要检查PRIMARY KEY列没有NULL值。

When you add a primary key using the ALGORITHM=COPY clause, MySQL actually converts NULL values in the associated columns to default values: 0 for numbers, the empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 for DATETIME. This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key using ALGORITHM=INPLACE is only allowed when the SQL_MODE setting includes the strict_trans_tables or strict_all_tables flags; when the SQL_MODE setting is strict, ADD PRIMARY KEY ... , ALGORITHM=INPLACE is allowed, but the statement can still fail if the requested primary key columns contain any NULL values. The ALGORITHM=INPLACE behavior is more standard-compliant.

当你使用ALGORITHM=COPY添加了主键，MySQL实际上会覆盖NULL值：数值型用0代替，基于字符的以及BLOB列用空字符串代替，DATETIME用0000-00-00 00:00:00代替。这不是标准行为，Oracle也不建议你这么做。只有SQL_MODE的设定包括了strict_trans_tables或者strict_all_tables的时候才允许使用ALGORITHM=INPLACE添加主键；当SQL_MODE设定是strict的时候，ADD PRIMARY KEY ... , ALGORITHM=INPLACE是允许的，但是如果要添加主键的列包括NULL值的话语句还是会执行失败的。ALGORITHM=INPLACE是更为标准的方式。

The following examples show the different possibilities for the ADD PRIMARY KEY clause. With the ALGORITHM=COPY clause, the operation succeeds despite the presence of NULL values in the primary key columns; the data is silently changed, which could cause problems.

下面的例子显示了ADD PRIMARY KEY子句的不同的可能性。当使用了ALGORITHM=COPY，尽管主键列可能会有NULL值，操作也还是会成功的；但是数据有可能会有改变并引发问题。

mysql> CREATE TABLE add_pk_via_copy (c1 INT, c2 VARCHAR(10), c3 DATETIME);

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO add_pk_via_copy VALUES (1,'a','2014-11-03 11:01:37'),(NULL,NULL,NULL);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SET sql_mode = '';

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE add_pk_via_copy ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=COPY;

Query OK, 2 rows affected, 3 warnings (0.07 sec)

Records: 2 Duplicates: 0 Warnings: 3

mysql> SHOW WARNINGS;

+---------+------+-----------------------------------------+

| Level | Code | Message |

+---------+------+-----------------------------------------+

| Warning | 1265 | Data truncated for column 'c1' at row 2 |

| Warning | 1265 | Data truncated for column 'c2' at row 2 |

| Warning | 1265 | Data truncated for column 'c3' at row 2 |

+---------+------+-----------------------------------------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM add_pk_via_copy;

+----+----+---------------------+

| c1 | c2 | c3 |

+----+----+---------------------+

| 0 | | 0000-00-00 00:00:00 |

| 1 | a | 2014-11-03 11:01:37 |

+----+----+---------------------+

2 rows in set (0.00 sec)

With the ALGORITHM=INPLACE clause, the operation could fail for different reasons, because this setting considers data integrity a high priority: the statement gives an error if the SQL_MODE setting is not“strict”enough, or if the primary key columns contain any NULL values. Once we address both of those requirements, the ALTER TABLE operation succeeds.

当使用ALGORITHM=INPLACE，操作可能会由于各种原因执行失败，因为这个设定必须要考虑数据的完整性：如果SQL_MODE的设定不够“严格”或者主键列里包含了NULL，那么这个操作就会失败。只有同时满足了这些条件，ALTER TABLE操作才会成功。

mysql> CREATE TABLE add_pk_via_inplace (c1 INT, c2 VARCHAR(10), c3 DATETIME);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO add_pk_via_inplace VALUES (1,'a','2014-11-03 11:01:37'),(NULL,NULL,NULL);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM add_pk_via_inplace;

+------+------+---------------------+

| c1 | c2 | c3 |

+------+------+---------------------+

| 1 | a | 2014-11-03 11:01:37 |

| NULL | NULL | NULL |

+------+------+---------------------+

2 rows in set (0.00 sec)

mysql> SET sql_mode = '';

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL

values, as required in this SQL_MODE. Try ALGORITHM=COPY.

mysql> SET sql_mode ='strict_trans_tables';

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;

ERROR 1138 (22004): Invalid use of NULL value

mysql> DELETE FROM add_pk_via_inplace WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM add_pk_via_inplace;

+------+------+---------------------+

| c1 | c2 | c3 |

+------+------+---------------------+

| 1 | a | 2014-11-03 11:01:37 |

+------+------+---------------------+

1 row in set (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

If you create a table without a primary key, InnoDB chooses one for you, which can be the first UNIQUE key defined on NOT NULL columns, or a system-generated key. To avoid any uncertainty and the potential space requirement for an extra hidden column, specify the PRIMARY KEY clause as part of the CREATE TABLE statement.

如果你创建的表没有定义主键，那么InnoDB把第一个NOT NULL可以定义UNIQUE的列作为主键，或者由系统自动生成一个。为了系统自动生成的额外隐藏列需要的不确定的潜在空间要求，最好在CREATE TABLE的时候就定义好主键。

14.11.6 Implementation Details of Online DDL

Each ALTER TABLE operation for an InnoDB table is governed by several aspects:

每个InnoDB表的ALTER TABLE操作都要考虑以下几个方面：

l Whether there is any change to the physical representation of the table, or whether it purely a change to metadata that can be done without touching the table itself.

l 修改操作是否是表物理上的修改，还是只是元数据的修改而不会涉及到表本身。

l Whether the volume of data in the table stays the same, increases, or decreases.

l 表里的数据量是否会有变动。

l Whether a change in table data involves the clustered index, secondary indexes, or both.

l 表上数据的修改是否会涉及到clustered index，还是secondary index，还是都会涉及。

l Whether there are any foreign key relationships between the table being altered and some other table. The mechanics differ depending on whether the foreign_key_checks configuration option is enabled or disabled.

l 修改的表是否和其他表有外键关系。技术上的不同取决于foreign_key_checks是开启还是关闭。

l Whether the table is partitioned. Partitioning clauses of ALTER TABLE are turned into low-level operations involving one or more tables, and those operations follow the regular rules for online DDL.

l 表是否有分区。带Partitioning的ALTER TABLE语句会变成涉及多个表的低级别操作，这些操作都会遵循online DDL的规则。

l Whether the table data must be copied, whether the table can be reorganized “in-place”, or a combination of both.

l 表是否一定要进行复制，还是也可以以“in-place”的方式进行重组。

l Whether the table contains any auto-increment columns.

l 表是否含有自增长列。

l What degree of locking is required, either by the nature of the underlying database operations, or a LOCK clause that you specify in the ALTER TABLE statement.

l 对锁有哪种级别的需求，是由底层的数据操作来决定，还是在ALTER TABLE语句里手动指定。

This section explains how these factors affect the different kinds of ALTER TABLE operations on InnoDB tables.

这部分解释了InnoDB表上影响ALTER TABLE操作有哪些因素。

Error Conditions for Online DDL

Here are the primary reasons why an online DDL operation could fail:

这里列出了online DDL执行失败的一些主要原因：

l If a LOCK clause specifies a low degree of locking (SHARED or NONE) that is not compatible with the particular type of DDL operation.

l LOCK子句指定了一个低级别的锁(SHARED or NONE)，但是又无法满足DDL操作对锁的要求。

l If a timeout occurs while waiting to get an exclusive lock on the table, which is needed briefly during the initial and final phases of the DDL operation.

l 当DDL操作在初始及结尾阶段短暂需要在表上持有排他锁时发生了锁等待超时。

l If the tmpdir file system runs out of disk space, while MySQL writes temporary sort files on disk during index creation.

l MySQL在创建索引时需要在磁盘上写临时文件用于排序，那么文件系统上的tmpdir如果空间不足就会报错。

l If the ALTER TABLE takes so long, and concurrent DML modifies the table so much, that the size of the temporary online log exceeds the value of the innodb_online_alter_log_max_size configuration option. This condition causes aDB_ONLINE_LOG_TOO_BIG error.

l 如果ALTER TABLE花费了很长的时间，而并行的DML操作又非常多，那么临时的online日志就可能会超过innodb_online_alter_log_max_size设定的值，这会引起DB_ONLINE_LOG_TOO_BIG错误。

l If concurrent DML makes changes to the table that are allowed with the original table definition, but not with the new one. The operation only fails at the very end, when MySQL tries to apply all the changes from concurrent DML statements. For example, you might insert duplicate values into a column while a unique index is being created, or you might insert NULL values into a column while creating a primary key index on that column. The changes made by the concurrent DML take precedence, and the ALTER TABLE operation is effectively rolled back.

l If concurrent DML makes changes to the table that are allowed with the original table definition, but not with the new one.当MySQL试图应用所有并发的DML修改时，在最后阶段有可能会报错的。例如，向有唯一索引的列里面插入重复的值，或者是像主键里面插入NULL。另外当DML和DDL并发时，DML的优先级更高，ALTER TABLE则会被回滚。

Although the configuration option innodb_file_per_table has a dramatic effect on the representation for an InnoDB table, all online DDL operations work equally well whether that option is enabled or disabled, and whether the table is physically located in its own .ibd file or inside the system tablespace.

虽然innodb_file_per_table是一个效果很不错的配置参数，但是online DDL和这个参数的配置是没有任何关系的。无论这个参数是关闭还是开启，表是放在其自己的.ibd文件里还是系统表空间里，online DDL都能很好地工作。

InnoDB has two types of indexes: the clustered index representing all the data in the table, and optional secondary indexes to speed up queries. Since the clustered index contains the data values in its B-tree nodes, adding or dropping a clustered index does involve copying the data, and creating a new copy of the table. A secondary index, however, contains only the index key and the value of the primary key. This type of index can be created or dropped without copying the data in the clustered index. Because each secondary index contains copies of the primary key values (used to access the clustered index when needed), when you change the definition of the primary key, all secondary indexes are recreated as well.

InnoDB有两种类型的索引：clustered index代表里表里所有的数据，而可选的secondary index则可以加快查询的速度。因为clustered index包含了B-tree节点的数据，所以添加删除clustered index都会涉及到数据和表的复制。而secondary index只是包含了它自己的key以及clustered index的key，所以它在创建删除的时候可以不需要进行表复制。因为每个secondary index都包含了primary key的值（用于访问clustered index），所以当修改了primary key的时候，所有的secondary index同样都会被重建。

Dropping a secondary index is simple. Only the internal InnoDB system tables and the MySQL data dictionary tables are updated to reflect the fact that the index no longer exists. InnoDB returns the storage used for the index to the tablespace that contained it, so that new indexes or additional table rows can use the space.

删除一个secondary index很简单，只是修改内部的InnoDB系统表和MySQL的数据字典表表示这个索引不再存在了。然后InnoDB就可以空出表空间里这个索引使用的空间让其他的索引和表记录使用。

To add a secondary index to an existing table, InnoDB scans the table, and sorts the rows using memory buffers and temporary files in order by the values of the secondary index key columns. The B-tree is then built in key-value order, which is more efficient than inserting rows into an index in random order. Because the B-tree nodes are split when they fill, building the index in this way results in a higher fill-factor for the index, making it more efficient for subsequent access.

向现有的一个表里加secondary index，InnoDB会进行表扫描，并用memory buffer和temporary file来对当前的secondary index列进行排序，然后再已排序好的key-value（因为相对于随机插入这样顺序的效率更高）构建B-tree。当索引填满后B-tree的节点会进行分裂，所以索引是以这种高填充因子的方式构建的，这样能使随后的访问更为快速。

Primary Key and Secondary Key Indexes

Historically, the MySQL server and InnoDB have each kept their own metadata about table and index structures. The MySQL server stores this information in .frm files that are not protected by a transactional mechanism, while InnoDB has its own data dictionary as part of the system tablespace. If a DDL operation was interrupted by a crash or other unexpected event partway through, the metadata could be left inconsistent between these two locations, causing problems such as startup errors or inability to access the table that was being altered. Now that InnoDB is the default storage engine, addressing such issues is a high priority. These enhancements to DDL operations reduce the window of opportunity for such issues to occur.

历史上，MySQL和InnoDB各自都会保存表和索引的元数据。MySQL是把它们存储在不受事务保护的.frm文件里的，而InnoDB是把阿门放在系统表空间里的。如果DDL操作被迫中断，那么两处的元数据则可能会不一致，从而导致启动的时候引发故障或者表在修改后不能被访问。现在InnoDB是默认的存储引擎了，解决这类问题就变成了最高的优先级。增强的DDL操作则减少了这种情况可能发生的机率。

14.11.7 How Crash Recovery Works with Online DDL

Although no data is lost if the server crashes while an ALTER TABLE statement is executing, thecrash recovery process is different for clustered indexes and secondary indexes.

在ALTER TABLE语句的执行过程中数据库服务崩溃并不会丢失数据，但是崩溃恢复在处理clustered index和secondary index上是有所不同的。

If the server crashes while creating an InnoDB secondary index, upon recovery, MySQL drops any partially created indexes. You must re-run the ALTER TABLE or CREATE INDEX statement.

如果在创建InnoDB secondary index的是时候发生服务崩溃，在进行恢复之前，MySQL首先会把之前未完成创建的索引先删除掉。之后就可以重新允许ALTER TABLE或者CREATE INDEX语句了。

When a crash occurs during the creation of an InnoDB clustered index, recovery is more complicated, because the data in the table must be copied to an entirely new clustered index. Remember that all InnoDB tables are stored as clustered indexes.

当在创建clustered index的时候发生崩溃，那恢复则更为复杂，因为需要把表里的数据完全复制到一个新的clustered index上。一定要记住所有的InnoDB表都是存储在clustered index上的。

MySQL creates the new clustered index by copying the existing data from the original InnoDB table to a temporary table that has the desired index structure. Once the data is completely copied to this temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is renamed with the name of the original table, and the original table is dropped from the database.

MySQL是通过从原始的InnoDB表上把数据复制到一个有相对应的索引结构的临时表上来创建新的clustered index的。一旦数据完全复制到了临时表上，那么原始表就会重命名成为另一个临时表。在之后临时表上的新clustered index会被重命名成原是表的名字，完成之后原始表就会从数据库上删除掉。

If a system crash occurs while creating a new clustered index, no data is lost, but you must complete the recovery process using the temporary tables that exist during the process. Since it is rare to re-create a clustered index or re-define primary keys on large tables, or to encounter a system crash during this operation, this manual does not provide information on recovering from this scenario.

如果在创建clustered index的时候发生服务崩溃，数据并不会丢失，但是必须要使用之前的临时表表完成恢复操作。因为在大表上重建clustered index或者是重定义主键是很少有的操作，在期间几千遭遇故障更是少见，所以这样的场景下手册无法提供更多的相关信息。

14.11.8 Online DDL for Partitioned InnoDB Tables

With the exception of ALTER TABLE partitioning clauses, online DDL operations for partitioned InnoDB tables follow the same rules that apply to regular InnoDB tables. Online DDL rules are outlined in Table 14.6,“Summary of Online Status for DDL Operations”.

除了ALTER TABLE partitioning子句部分，online DDL操作在分区表上和普通表一样都遵循相同的规则。Online DDL的规则在Table 14.6, “Summary of Online Status for DDL Operations”中有明确表述。

ALTER TABLE partitioning clauses do not go through the same internal online DDL API as regular non-partitioned InnoDB tables, and are only allowed in conjunction with ALGORITHM=DEFAULT and LOCK=DEFAULT.

ALTER TABLE partitioning子句并没有使用普通表的内部online DDL接口，所以只能以ALGORITHM=DEFAULT，LOCK=DEFAULT的方式运行。

If you use an ALTER TABLE partitioning clause in an ALTER TABLE statement, the partitioned table will be“re-partitioned”using the ALTER TABLE COPY algorithm. In other words, a new partitioned table is created with the new partitioning scheme. The newly created table will include any changes applied by the ALTER TABLE statement and the table data will be copied into the new table structure.

如果你使用了ALTER TABLE partitioning，那么以分区的表会使用ALTER TABLE COPY的算法来重新分区。话句话说，新的分区表是建立在新的分区方案上的。新创建的表会应用所有ALTER TABLE的修改，并且表的数据也会被复制到新的表结构上。

If you do not change the table's partitioning using ALTER TABLE partitioning clauses or perform any other partition management in your ALTER TABLE statement, ALTER TABLE will use the INPLACE algorithm on each table partition. Be aware, however, that when INPLACE ALTER TABLE operations are performed on each partition, there will be increased demand on system resources due to operations being performed on multiple partitions.

如果ALTER TABLE partitioning并没有修改表的分区或者只是执行了其他的分区管理语句，那么ALTER TABLE会在每个表分区上使用INPLACE的算法。但要注意的是，即使是每个每个分区上执行INPLACE ALTER TABLE的操作，这也会要占用很多的系统资源的。

Even though partitioning clauses of the ALTER TABLE statement do not go through the same internal online DDL API as regular non-partitioned InnoDB tables, MySQL still attempts to minimize data copying and locking where possible:

虽然ALTER TABLE partitioning并没有使用和普通表相同的内部online DDL接口，但MySQL还是会尽可能最减少数据的复制以及锁的使用：

ADD PARTITION and DROP PARTITION for tables partitioned by RANGE or LIST do not copy any existing data.

在RANGE或者LIST分区上使用ADD PARTITION和DROP PARTITION不会复制现有的数据。

TRUNCATE PARTITION does not copy any existing data, for all types of partitioned tables.

对于所有类型的分区表，TRUNCATE PARTITION都不会复制任何现有的数据。

Concurrent queries are allowed during ADD PARTITION and COALESCE PARTITION for tables partitioned by HASH or LIST. MySQL copies the data while holding a shared lock.

在HASH和LIST分区上进行ADD PARTITION和COALESCE PARTITION的时候是可以并发查询的。MySQL在复制数据的过程中仅会持有一个共享锁。

For REORGANIZE PARTITION, REBUILD PARTITION, or ADD PARTITION or COALESCE PARTITION for a table partitioned by LINEAR HASH or LIST, concurrent queries are allowed. Data from the affected partitions is copied while holding a shared metadata (read) lock at the table level.

LINEAR HASH和LIST分区进行REORGANIZE PARTITION，REBUILD PARTITION，ADD PARTITION或者OALESCE PARTITION的时候是可以并发查询的。这些受影响的分区上的数据在进行复制的时候MySQL仅会持有一个表级的共享的元数据锁(a shared metadata (read) lock)。

Note

Full-text search (FTS) and foreign keys are not supported by InnoDB partitioned tables. For more information, see Section 12.9.5,“Full-Text Restrictions”and Section 19.6.2, “Partitioning Limitations Relating to Storage Engines”.

InnoDB的分区表是不支持全文搜索（Full-text search (FTS)）和外键的。更多的相关信息可以查看Section 12.9.5, “Full-Text Restrictions”和Section 19.6.2, “Partitioning Limitations Relating to Storage Engines”。

14.11.9 Limitations of Online DDL

Take the following limitations into account when running online DDL operations:

运行online DDL的时候要顾及到以下的一些限制：

l During an online DDL operation that copies the table, files are written to the temporary directory ($TMPDIR on Unix, %TEMP% on Windows, or the directory specified by the --tmpdir configuration variable). Each temporary file is large enough to hold one column in the new table or index, and each one is removed as soon as it is merged into the final table or index.

l 在online DDL进行表复制的时候，会在临时目录(在Unix上是$TMPDIR，Windows上是%TEMP%，或者由tmpdir configuration变量定义)写临时文件。每个临时文件都要能够完全放得下新表的一个列或者是索引，一旦这些列或者索引被合并掉后这些临时文件就会被删除。

l The table is copied, rather than using Fast Index Creation when you create an index on a TEMPORARY TABLE. This has been reported as MySQL Bug #39833.

l 在临时表上创建索引的使用表复制，而不是使用Fast Index Creation。这个在MySQL Bug #39833上有报告。

l InnoDB handles error cases when users attempt to drop indexes needed for foreign keys. See Section B.3,“Server Error Codes and Messages”for information related to error 1553.

l 当用户是同删除外键所需要的索引的时候InnoDB会报错。详见Section B.3,“Server Error Codes and Messages”中关于error 1553的信息。

l The ALTER TABLE clause LOCK=NONE is not allowed if there are ON...CASCADE or ON...SET NULL constraints on the table.

l 当表上有ON...CASCADE或者ON...SET NULL约束的时候 ALTER TABLE是不允许使用LOCK=NONE的。

l During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there are brief periods at the beginning and end requiring an exclusive lock on the table (the same kind of lock specified by the LOCK=EXCLUSIVE clause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, updates, deletes, or SELECT ... FOR UPDATE on that table; and an online DDL operation might wait before finishing if a similar long-running transaction was started while the ALTER TABLE was in progress.

l 每个online DDL语句，不管使用了哪种的LOCK子句，它们都会在开始和结束阶段短暂地在表上加一个排他锁（即使指定了LOCK=EXCLUSIVE也一样）。因此，online DDL操作可能会要等待其他的事务结束后才能进行；同样地也要等待其他事务结束后online DDL才能完成。

l When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an“online log”of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the“online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

l 正在运行online ALTER TABLE操作的线程稍后会应用其他线程并发的时候的DML操作的online日志，那就有可能会碰到重复唯一键的错误(ERROR 1062 (23000): Duplicate entry)，即使这个重复的键有可能只是临时之后会被online日志回收的。这种情况和对外键的检查比较相似，在事务期间必须要对此进行约束。

l OPTIMIZE TABLE for an InnoDB table is mapped to an ALTER TABLE operation to rebuild the table and update index statistics and free unused space in the clustered index.Prior to 5.6.17, there is no online DDL support for this operation. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. As of 5.6.17, OPTIMIZE TABLE is supported with the addition of online DDL support for rebuilding regular and partitioned InnoDB tables. For additional information, see Section 14.11.1,“Overview of Online DDL”.

l InnoDB表的OPTIMIZE TABLE操作等同于用ALTER TABLE来重建表，并更新索引的统计信息，释放clustered index未使用的空间。在MySQL5.6.17之前，这种操作是不支持online DDL的，Secondary index的创建也并不高效，因为要插入的key是已主键的顺序排列的。从MySQL5.6.17开始，在普通的以及分区的InnoDB表上OPTIMIZE TABLE也支持online操作了。更多的信息查看 Section 14.11.1,“Overview of Online DDL”。

l InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns the following error:

l 在MySQL5.6之前，有时间列(DATE, DATETIME or TIMESTAMP)的表是不支持ALTER TABLE ... ALGORITHM=INPLACE操作的，也不能够通过ALTER TABLE ... ALGORITHM=COPY来重建。这种情况细心啊，ALTER TABLE ... ALGORITHM=INPLACE操作会返回下面的错误：

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.

Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

l These limitations are generally applicable to online DDL operations on large tables where table copying is involved:

l 在大表上要进行表复制的online DDL通常会有下面的限制：

n There is no mechanism to pause an online DDL operation or to throttle I/O or CPU usage for an online DDL operation.

n 目前没有任何机制能够暂停online DDL操作，或者是在online DDL操作过程中限制I/O或者CPU的使用。

n Progress monitoring capability for online DDL operations is limited until MySQL 5.7.6, which introduces Performance Schema stage events for monitoring ALTER TABLE progress. See Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema.

n 在MySQL5.7.6之前online DDL操作的进度监控能力是有限制的（引进了Performance Schema stage events用于对ALTER TABLE的监控）。详见Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema。

n Rollback of an online DDL operation can be expensive should the operation fail.

n 如果操作失败那么online DDL操作的回滚成本将会是十分昂贵的。

n Long running online DDL operations can cause replication lag. An online DDL operation must finish running on the master before it is run on the slave. Also, DML that was processed concurrently on the master is only processed on the slave after the DDL operation on the slave is completed (Bug #73196).

n 长时间的online DDL操作会使得主从复制的进度落后。Online DDL操作只有在主库上完成之后才会在备库上允许。同样，主库上并发的DML操作也要等到备库的DDL操作完成之后才能处理(Bug #73196)。

n An online ALTER TABLE operation can cause a server exit if the operation uses all of the available disk space on the file system where the data directory (datadir) resides (Bug #77497). To avoid this problem, ensure that there is enough disk space to accommodate operations that copy the table. During these operations, MySQL writes temporary sort files to the temporary directory (--tmpdir).

n 如果文件系统上的数据目录(datadir)的磁盘空间不够，那么online的ALTER TABLE操作则有可能会引发数据库服务退出(Bug #77497)。要避免这种情况，就要确保在进行复制表的时候留有足够的磁盘空间。还有，在次操作的过程中，MySQL也会在临时目录(--tmpdir)上写临时的排序文件。

For additional information related to running DDL operations on large tables, see Section 14.11.2,“Performance and Concurrency Considerations for Online DDL”.

更多关于在大表上进行DDL操作的信息可以查看14.11.2,“Performance and Concurrency Considerations for Online DDL”。

0 0