MySQL Online DDL知识点有哪些

发布时间：2021-11-12 14:35:01 作者：iii
来源：亿速云阅读：223

这篇文章主要介绍“MySQL Online DDL知识点有哪些”，在日常操作中，相信很多人在MySQL Online DDL知识点有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL Online DDL知识点有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

一：最初alter的复杂过程。

MySQL 5.5之前除了MySQL 5.1的 innodb plugin之外，对于索引的添加或删除这类DDL操作，MySQL数据库的操作过程为如下：

（1）首先创建新的临时表，表结构通过命令ALTAR TABLE新定义的结构

（2）然后把原表中数据导入到临时表（不能读和写）

（3）删除原表

（4）最后把临时表重命名为原来的表名

上述过程我们不难发现，若我们对一张大表进行索引的添加或者删除，需要很长的时间，致命的是若有大量的访问请求，意味着无法提供服务，5.5已经过时了，大家还是多关注5.6和5.7吧

二：快速索引创建：

官方文档中说明

In MySQL 5.5 and higher, or in MySQL 5.1 with the InnoDB Plugin, creating and dropping secondary indexes does not copy the contents of the entire table, making this operation much more efficient than with prior releases.

翻译：在mysql 5.5或者更高版本，或者是mysql 5.1的InnoDB Plugin中，创建和删除二级索引不需要复制整个表的数据来创建临时表了，和之前的版本相比这类操作变得更加高效了；

innodb存储引擎从1.0.x版本开始支持Fast index Creation（快速索引创建）。简称FIC。对于辅助索引的创建，会对创建索引的表加一个S锁。在创建的过程中，不需要重建表，因此速度有明显提升。对于删除辅助索引innodb存储引擎只需要更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL 数据库内部视图上对该表的索引定义即可。由于在创建辅助索引时加的是S锁，所以在这过程中只能对该表进行读操作，若有事务需要对该表进行写操作，那么数据库服务同样不可用。需要注意的是，FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表；快速索引创建语句和正常alter语句没有什么不同；

三：online ddl （注意是针对innodb引擎而言的）

mysql 5.6以及以后的版本中，对于大多数我们日常常用的DDL而言，是可以做到在线DDL的。

通常情况下，可以使用默认的语法来进行在线DDL，但你也可以通过选项来改变DDL的行为，有两个选项

LOCK=

ALGORITHM=[INPLACE|COPY]

关于这两个参数的介绍：

ALGORITHM=INPLACE，可以避免重建表带来的IO和CPU消耗，保证ddl期间依然有良好的性能和并发。

ALGORITHM=COPY，需要拷贝原始表，所以不允许并发DML写操作，可读。这种copy方式的效率不如 inplace ，因为前者需要记录undo和redo log，而且因为临时占用buffer pool引起短时间内性能受影响。

LOCK 选项控制是否锁表，根据不同的DDL操作类型有不同的表现：默认mysql尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表，以下是具体的值的意义：

（1）NONE，执行索引创建或者删除操作时，对目标表不添加任何锁，即事务仍然可以进行读写操作，不会收到阻塞，该模式可以获得最大的并发。

（2）SHARE，和Fast index Creation类似，执行索引创建或删除操作时，对目标表加一个S锁。对于并发读事务，依然可以执行。但是遇到写事务，将会发生等待操作，如果存储引擎不支持SHARE模式，将返回一个错误信息。

（3）EXCLUSIVE，执行索引创建或删除时，对目标表加上一个X锁。读写事务均不能进行。会阻塞所有的线程。这和COPY方式类似，但是不需要像COPY方式那样创建一张临时表。

（4）DEFAULT，该模式首先会判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式。

online ddl的语句：

alter table | ALGORITHM [=] {DEFAULT|INPLACE|COPY}， | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

那么如何开启online ddl？

由参数old_alter_table控制，

old_alter_table=0，不启用旧的copy the table 的模式来进行ddl操作；

mysql 5.6默认 old_alter_table=0 ，就开启了online ddl，可以使用默认的语法来进行在线DDL，

(题外话：关于set old_alter_table=0; 和 set global old_alter_table=0;的区别。前者只影响当前session，后者作为全局的修改方式，只会影响修改之后打开的session；注意后者不能改变当前session；）

实验一：

1.1

session 1

mysql> set old_alter_table=1;

Query OK, 0 rows affected (0.45 sec)

mysql> show variables like 'old_alter_table';

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

| Variable_name | Value |

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

| old_alter_table | ON |

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

1 row in set (0.00 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

session 2 执行dml操作，被阻塞。

mysql> update v_member_info set phone='1771002222' where id=1;

查看进程，发现果然是用旧的copy the table 的模式来进行ddl操作，然后update操作不能执行，等待一个metadata lock ；

mysql> show processlist;

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

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

1.2

session 1

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'old_alter_table';

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

| Variable_name | Value |

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

| old_alter_table | OFF |

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

1 row in set (0.15 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

Query OK, 0 rows affected, 2 warnings (13.42 sec)

Records: 0 Duplicates: 0 Warnings: 2

session 2 执行dml操作，并没有被阻塞，

mysql> update v_member_info set phone='1771002222' where id=1;

Query OK, 0 rows affected (0.02 sec)

Rows matched: 1 Changed: 0 Warnings: 0

mysql> show processlist;

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

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

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

3 rows in set (0.07 sec)

实验一表明：当old_alter_table=0的时候，就表示不用旧的那种copy the table 的模式来进行ddl操作，也就是开启了online ddl。并且开启online ddl之后，正常的alter命令添加索引，不会阻塞dml操作。由于不需要创建临时表，online ddl效率很高；

实验二：当old_alter_table=1的时候，ALGORITHM=INPLACE还有效吗？

mysql> set old_alter_table=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'old_alter_table';

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

| Variable_name | Value |

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

| old_alter_table | ON |

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

1 row in set (0.00 sec)

session 1 ：注意添加ALGORITHM =INPLACE参数后面有个逗号。

mysql> alter table v_member_info ALGORITHM =INPLACE,add index inde_register (register_ip) ;

session 2 并没有阻塞dml操作；

mysql> update v_member_info set phone='1771002222' where id=1;

Query OK, 0 rows affected (0.40 sec)

Rows matched: 1 Changed: 0 Warnings: 0

查看进程，发现没有使用copy temp table的方式执行ddl

mysql> show processlist;

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

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

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

3 rows in set (0.00 sec)

实验二：结果表明，在mysql5.6中，当当old_alter_table=1的时候，可以使用ALGORITHM=INPLACE来影响ddl的执行方式，也就是说ALGORITHM=INPLACE的参数的优先级高，依旧按着online ddl的方式创建索引，不建立临时表（尽管old_alter_table=1）。lock参数也肯定一样，实验2本身就没什么意义，因为没用人会把old_alter_table设置成1，而不用online ddl新特性。

实验三：

session 1 开启 online ddl，然后执行用copy的方式执行添加索引的操作；

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'old_alter_table';

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

| Variable_name | Value |

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

| old_alter_table | OFF |

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

1 row in set (0.03 sec)

mysql> alter table v_member_info ALGORITHM =copy ,add index inde_register (register_date) ;

session 2阻塞dml操作；

mysql> update v_member_info set phone='1771002222' where id=1;

mysql> show processlist; 查看进程发现确实在等待Waiting for table metadata lock

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

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

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

3 rows in set (0.06 sec)

实验三证明开启online ddl之后，也可以使用copy to tmp table的方式创建索引，依旧会阻塞其他的dml操作。但是应该没有这么无聊的dba吧；

实验四：验证myisam引擎是否可以 online ddl

session 1

mysql> alter table v_member_info engine=myisam;

Query OK, 1804082 rows affected (1 min 50.33 sec)

Records: 1804082 Duplicates: 0 Warnings: 0

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.03 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

Query OK, 1804082 rows affected (1 min 57.77 sec)

Records: 1804082 Duplicates: 0 Warnings: 0

session 2 被阻塞

mysql> update v_member_info set phone='1771002222' where id=1;

session 3 查看进程状态，会看到添加索引的过程，刚开始copy to tmp table（创建临时表）

mysql> show processlist;

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

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

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

3 rows in set (0.00 sec)

实验四证明：mysql 5.6中myisam引擎是不支持online ddl的，添加索引依旧会创建临时表，阻塞其他session的dml操作；

关于online ddl的原理层面：

innodb存储引擎实现Online DDL的原理是在执行创建或者删除操作同时，将INSERT,UPDATE，DELETE这类DML操作日志写入到一个缓存中，待完成索引创建后再将重做应用到表上，以此达到数据的一致性。这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认大小为128MB。

需要注意的是：如果待更新的表比较大，并且创建过程中有大量的写事务，如果遇到innodb_online_alter_log_max_size的空间不能存放日志时，会抛出相应的错误，如果遇到这个错误，我们可以调大该参数，以此获得更大的日志缓存空间，或者我们可以设置ALTER TABLE的lock模式为SHARE，这样在执行过程中不会有写操作事务发生。因此不需要进行DML日志的记录。

但是有一点需要说明，无论任何模式下，online ddl开始之前都需要一个短时间排它锁(exclusive)来准备环境，所以alter命令发出后，会首先等待该表上的其它操作完成，在alter命令之后的请求会出现等待waiting meta data lock。同样在ddl结束之前，也要等待alter期间所有的事务完成，也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行，否则一样出现连环锁表。你可以通过观察执行完DDL后的输出： XX rows affected，来判断是IN-PLACE 还是COPY数据，为0的话就是inplace。

copy和inplace方式的具体过程（添加index为例）

copy方式

(1).新建带索引的临时表

(2).锁原表，禁止DML，允许查询

(3).将原表数据拷贝到临时表(无排序，一行一行拷贝)

(4).进行rename，升级字典锁，禁止读写

(5).完成创建索引操作

inplace方式（整个过程相对较快）

(1).新建索引的数据字典

(2).锁表，禁止DML，允许查询（这个过程非常短暂）

(3).读取聚集索引，构造新的索引项，排序并插入新索引

(4).等待打开当前表的所有只读事务提交

(5).创建索引结束

online ddl实现

online方式实质也包含了copy和inplace方式，对于不支持online的ddl操作采用copy方式，比如修改列类型，删除主键，修改字符集等，这些操作都会导致记录格式发生变化，无法通过简单的全量+增量的方式实现online；对于inplace方式，mysql内部以“是否修改记录格式”为基准也分为两类，一类需要重建表(重新组织记录)，比如optimize table、添加索引、添加/删除列、修改列NULL/NOT NULL属性等；另外一类是只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。Mysql将这两类方式分别称为rebuild方式和no-rebuild方式

2. 实现过程（注意这里的rebuild是指从新组织记录，是相对于只修改表的元数据而言的）

online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild(重新组织记录)方式比no-rebuild（只需要修改表的元数据）方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。
Prepare阶段:
1）创建新的临时frm文件(与InnoDB无关)
2）持有EXCLUSIVE-MDL锁，禁止读写
3）根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)

假如是Add Index，则选择online-norebuild即INPLACE方式
1）更新数据字典的内存对象
2）分配row_log对象记录增量(仅rebuild类型需要)
3）生成新的临时ibd文件(仅rebuild类型需要)
ddl执行阶段:
1）降级EXCLUSIVE-MDL锁，允许读写
2）扫描old_table的聚集索引每一条记录rec
3）遍历新表的聚集索引和二级索引，逐一处理
4）根据rec构造对应的索引项
5）将构造索引项插入sort_buffer块排序
6）将sort_buffer块更新到新的索引上
7）记录ddl执行过程中产生的增量(仅rebuild类型需要)
8）重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)
9）重放row_log间产生dml操作append到row_log最后一个Block
commit阶段:
1）当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁
2）重做row_log中最后一部分增量
3）更新innodb的数据字典表
4）提交事务(刷事务的redo日志)
5）修改统计信息
6）rename临时idb文件，frm文件
7）变更完成

从官方提供的这个表格来看，还是有很多操作不支持完全的在线DDL，包括增加一个全文索引，修改列的数据类型，删除一个主键，修改表的字符集等。

Operation	In-Place?	是否重建表	允许并发DML	只修改元数据?	Notes
CREATE INDEX, ADD INDEX	Yes*	No*	Yes	No	Restrictions apply for FULLTEXT indexes; see next row.
ADD FULLTEXT INDEX	Yes*	No*	No	No	Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent FULLTEXT indexes may be added on the same table without rebuilding the table.
DROP INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
OPTIMIZE TABLE	Yes*	Yes	Yes	No	Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with FULLTEXTindexes.
Set column default value	Yes	No	Yes	Yes	Only modifies table metadata.
Change auto-increment value	Yes	No	Yes	No*	Modifies a value stored in memory, not the data file.
Add foreign key constraint	Yes*	No	Yes	Yes	The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.
Drop foreign key constraint	Yes	No	Yes	Yes	foreign_key_checks can be enabled or disabled.
Rename column	Yes	No	Yes*	Yes	To permit concurrent DML, keep the same data type and only change the column name.
Add column	Yes	Yes	Yes*	No	Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.
Drop column	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Reorder columns	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change ROW_FORMATproperty	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change KEY_BLOCK_SIZEproperty	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Make column NULL	Yes	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Make column NOT NULL	Yes*	Yes*	Yes	No	Rebuilds the table in place. 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. See Section 13.1.7, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.
Change column data type	No	Yes	No	No	Only supports ALGORITHM=COPY
Add primary key	Yes*	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL.
Drop primary key and add another	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Drop primary key	No	Yes	No	No	Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.
Convert character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Specify character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Rebuild with FORCE option	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
“null” rebuild using ALTER TABLE ... ENGINE=INNODB	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
Set STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statistics options	Yes	No	Yes	Yes	Only modifies table metadata.

从表看出，In-Place为No，DML一定是No，说明ALGORITHM=COPY一定会发生拷贝表，只读。

ALGORITHM=INPLACEE也要可能发生拷贝表，但可以并发DML:

添加、删除列，改变列顺序
添加或删除主键
改变行格式ROW_FORMAT和压缩块大小KEY_BLOCK_SIZE
改变列NULL或NOT NULL
优化表OPTIMIZE TABLE
强制 rebuild 该表

不允许并发DML的情况有：修改列数据类型、删除主键、变更表字符集，即这些类型操作ddl是不能online的。

另外，更改主键索引与普通索引处理方式是不一样的，主键即聚集索引，体现了表数据在物理磁盘上的排列，包含了数据行本身，需要拷贝表；而普通索引通过包含主键列来定位数据，所以普通索引的创建只需要一次扫描主键即可，而且是在已有数据的表上建立二级索引，更紧凑，将来查询效率更高。

修改主键也就意味着要重建所有的普通索引。删除二级索引更简单，修改InnoDB系统表信息和数据字典，标记该索引不存在，标记所占用的表空间可以被新索引或数据行重新利用。

到此，关于“MySQL Online DDL知识点有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！

MySQL Online DDL知识点有哪些

相关阅读