千家信息网

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

发表于:2024-11-01 作者:千家信息网编辑
千家信息网最后更新 2024年11月01日,MySQL从5.6.17以后,支持在线修改表结构操作(online ddl),即在变更表结构的过程中,不阻塞dml和dql操作.根据操作过程中是否需要表拷贝,online ddl可分为下面两大类:1.
千家信息网最后更新 2024年11月01日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分为 INPLACECOPY 两种方式,通过在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带*号的限制说明

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

从表看出,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


数据 索引 拷贝 昂贵 类型 重组 全文 支持 普通 方式 时间 语句 在线 不同 影响 字符 字符集 情况 模式 限制 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 工商银行服务器连接不了网络 河南戴尔服务器 后台数据库建立和维护功能 vc数据库怎么操作 将事务写入数据库的语句是 彻底删除dbf中的数据库 网络安全密钥初始密码是什么 云南数据网络技术服务咨询报价 杭州物流软件开发代码 网络安全教案及反思 屏幕共享软件开发哪家实惠 数据库管理系统设计软件 安徽服务器阵列卡电池云服务器 数据库价格用什么字段 自己家能不能装服务器 大数据网络技术咨询哪个正规 支付宝健康码服务器登录失败 软件开发成本估计模板 下列关系数据库中数据表 网络安全侦听威胁 vb.net 压缩数据库 世邦服务器 网络安全与威胁情报 驾校考试服务器配置 发信服务器返回失败怎么办 博思德g6000怎么设置数据库 端游绝地求生服务器崩溃 apec交流与数据库系统网址 护苗网络安全教育视频下载 大型计算机系统使用的数据库是
0