MySQL5.6在线表结构变更(online ddl)总结

  MySQL从5.6.17以后,支持在线修改表结构操作(online ddl),即在变更表结构的过程中,不阻塞dml和dql操作.

  根据操作过程中是否需要表拷贝,online ddl可分为下面两大类:

1.需要表拷贝的 ddl 操作:
增加、删除、重排列。
增加、删除主键。
改变表的 ROW_FORMAT 或 KEY_BLOCK_SIZE属性。
改变的字段的null状态。
执行OPTIMIZE TABLE,优化表。
使用 FORCE 选项重建表。
使用ALTER TABLE ... ENGINE=INNODB 语句。
首次创建全文索引。


2.不需要表拷贝的 ddl 操作:
创建、增加、删除普通索引。
创建第二个及后续的全文索引。
为字段设置默认值。
改变auto-increment值。
删除外键约束。
添加外键约束( 只有在foreign_key_checks=off时)
仅仅改变列的名称
设置表的持续统计选项(STATS_PERSISTENT, STATS_AUTO_RECALC STATS_SAMPLE_PAGES)

特别说明:全文索引需要特别注意,创建了全文索引的表基本上不支持在线ddl操作。

ySQL 5.6 Online DDL把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等,实际效果要看所使用的选项和操作类别来定。

1.1 Online DDL选项

MySQL 在线DDL分为 INPLACE 和 COPY 两种方式,通过在ALTER语句的ALGORITHM参数指定。

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

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

上面只是 Online DDL 内部的实现方式,此外还有 LOCK 选项控制是否锁表,根据不同的DDL操作类型有不同的表现:默认mysql尽可能不去锁表,但是像修改主键这样的昂贵操作不得不选择锁表。

  • LOCK=NONE,即DDL期间允许并发读写涉及的表,比如为了保证 ALTER TABLE 时不影响用户注册或支付,可以明确指定,好处是如果不幸该 alter语句不支持对该表的继续写入,则会提示失败,而不会直接发到库上执行。ALGORITHM=COPY默认LOCK级别

  • LOCK=SHARED,即DDL期间表上的写操作会被阻塞,但不影响读取。

  • LOCK=DEFAULT,让mysql自己去判断lock的模式,原则是mysql尽可能不去锁表

  • LOCK=EXCLUSIVE,即DDL期间该表不可用,堵塞任何读写请求。如果你想alter操作在最短的时间内完成,或者表短时间内不可用能接受,可以手动指定。

但是有一点需要说明,无论任何模式下,online ddl开始之前都需要一个短时间排它锁(exclusive)来准备环境,所以alter命令发出后,会首先等待该表上的其它操作完成,在alter命令之后的请求会出现等待waiting meta data lock。同样在ddl结束之前,也要等待alter期间所有的事务完成,也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行,否则一样出现连环锁表。

1.2 考虑不同的DDL操作类别

从上面的介绍可以看出,不是5.6支持在线ddl就可以随心所欲的alter table,锁不锁表要看情况:

提示:下表根据官方 Summary of Online Status for DDL Operations 整理挑选的常用操作。

  • In-Place为Yes是优选项,说明该操作支持INPLACE

  • Copies Table为No是优选项,因为为Yes需要重建表。大部分情况与In-Place是相反的

  • Allows Concurrent DML?为Yes是优选项,说明ddl期间表依然可读写,可以指定 LOCK=NONE(如果操作允许的话mysql自动就是NONE)

  • Allows Concurrent Query?默认所有DDL操作期间都允许查询请求,放在这只是便于参考

  • Notes会对前面几列Yes/No带*号的限制说明

Operation In-Place? Copies Table? Allows Concurrent DML? Allows Concurrent Query? Notes
添加索引 Yes* No* Yes Yes 对全文索引的一些限制
删除索引 Yes No Yes Yes 仅修改表的元数据
OPTIMIZE TABLE Yes Yes Yes Yes 从 5.6.17开始使用ALGORITHM=INPLACE,当然如果指定了old_alter_table=1或mysqld启动带--skip-new则将还是COPY模式。如果表上有全文索引只支持COPY
对一列设置默认值 Yes No Yes Yes 仅修改表的元数据
对一列修改auto-increment 的值 Yes No Yes Yes 仅修改表的元数据
添加 foreign key constraint Yes* No* Yes Yes 为了避免拷贝表,在约束创建时会禁用foreign_key_checks
删除 foreign key constraint Yes No Yes Yes foreign_key_checks 不影响
改变列名 Yes* No* Yes* Yes 为了允许DML并发, 如果保持相同数据类型,仅改变列名
添加列 Yes* Yes* Yes* Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作。当添加列是auto-increment,不允许DML并发
删除列 Yes Yes* Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
修改列数据类型 No Yes* No Yes 修改类型或添加长度,都会拷贝表,而且不允许更新操作
更改列顺序 Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
修改ROW_FORMAT
和KEY_BLOCK_SIZE
Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
设置列属性NULL
或NOT NULL
Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
添加主键 Yes* Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作。
如果列定义必须转化NOT NULL,则不允许INPLACE
删除并添加主键 Yes Yes Yes Yes 在同一个 ALTER TABLE 语句删除就主键、添加新主键时,才允许inplace;数据大幅重组,所以它仍然是一项昂贵的操作。
删除主键 No Yes No Yes 不允许并发DML,要拷贝表,而且如果没有在同一 ATLER TABLE 语句里同时添加主键则会收到限制
变更表字符集 No Yes No Yes 如果新的字符集编码不同,重建表

从表看出,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系统表信息和数据字典,标记该所以不存在,标记所占用的表空间可以被新索引或数据行重新利用。


MySQL5.6几种建索引方式比较。
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.17 |
+-----------+
mysql> CREATE TABLE test AS SELECT * FROM information_schema.columns;
mysql> INSERT INTO test SELECT * FROM test;
mysql> INSERT INTO test SELECT * FROM test;
mysql> SELECT COUNT(1) FROM test;
+----------+
| COUNT(1) |
+----------+
| 312928 |
+----------+
1 row in set (0.17 sec)

ALGORITHM=inplace,就地进行,如果允许的话则修改操作可以直接在该表上执行。

mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=inplace;
Query OK, 0 rows affected (1.54 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test DROP INDEX ind_t_column_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0


mysql> CREATE INDEX ind_t_column_name ON test(column_name);
Query OK, 0 rows affected (1.70 sec)
Records: 0 Duplicates: 0 Warnings: 0

ALGORITHM=copy,用于标识改操作是否需要整个表。
mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=copy;
Query OK, 312928 rows affected (5.93 sec)

Records: 312928 Duplicates: 0 Warnings: 0

注:

SET old_alter_table=0; --ALGORITHM=inplace

SET old_alter_table=1; --ALGORITHM=copy




     本文转自yzy121403725 51CTO博客,原文链接:http://blog.51cto.com/lookingdream/1961451,如需转载请自行联系原作者


上一篇:面试题总结——JAVA高级工程师


下一篇:超级详细的MySql 在centos下的安装过程