Mysql5.6-8.0在线DDL操作是否锁表介绍

目录

前言

名词介绍

INPLACE       

在线 DDL要求

一、mysql5.6

5.6在线 索引DDL

5.6在线 主键DDL 

5.6在线 列DDL

二、mysql5.7

5.7在线 索引DDL

5.7在线 主键DDL 

5.7在线 列DDL

三、mysql8.0

8.0在线 索引DDL

8.0在线 主键DDL 

8.0在线 列DDL

---

前言

        常见的在线DDL语句有列操作、主键操作、索引操作、表空间的操作，我们在使用mysql过程中经常会遇到ddl操作，本文针对各种类型的ddl进行分析，列出不会影响查询写入还能在线操作DDL语句

          在mysql5.6 、5.7、8.0中同一种操作语句在不同版本中对表的影响也不一样，简单的例子如在mysql5.6中增加字段长度会锁表，但是在5.7中却不会锁表，本文针对不同mysql版本，罗列了DDL操作对表的影响

名词介绍

INSTANT和INPLACE是MySQL在线DDL操作两种模式，在8.0.12之前，默认是INPLACE，MySQL 8.0.12后默认是INSTANT

INPLACE       

        该模式需要重建表，然后将数据从旧表复制到新表中。这意味着在执行结构变更操作期间，对表的访问可能会被中断。尽管如此，INPLACE模式仍然是一个非常有用的特性，因为它可以在不破坏数据的情况下快速修改表结构。

一般来说：仅修改元数据不会造成锁表和允许并发DML不会造成锁表，但是如果仅允许并发DML的DDL操作，在大批量数据下会有性能损耗

在线 DDL要求

• 在线 DDL 操作完成之前，它必须等待在表上持有元数据锁的事务提交或回滚。在线 DDL 操作在其执行阶段可能会短暂地需要表上的独占元数据锁，并且在更新表定义时，在操作的最后阶段始终需要一个独占元数据锁。因此，持有表元数据锁的事务可能会导致联机 DDL 操作阻塞。

• 不支持 MySQL 5.6 之前创建的 包含临时列（DATE, DATETIME or TIMESTAMP ）且尚未使用重建的表。在这种情况下， 操作会返回以下错误：ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

注意：操作大表且需要重建表的DDL语句建议使用三方工具处理，例如pt-online

        

一、mysql5.6

5.6在线 索引DDL

操作	In Place	重建表	允许并发 DML	仅修改元数据
创建或添加二级索引	是	否	是的	否
删除索引	是	否	是的	是的
添加FULLTEXT索引	是*	否*	否	否
更改索引类型	是	否	是的	是的

5.6在线 主键DDL 

操作	In Place	重建表	允许并发 DML	仅修改元数据
添加主键	是的*	是	是的	否
删除主键	否	是	否	否
删除一个主键并添加另一个	是的	是	是的	否

5.6在线 列DDL

注意：在5.6中扩展VARCHAR列大小是会锁表的

操作	Instant	重建表	允许并发 DML	仅修改元数据
添加列	是的*	是	是	否
删除一列	是的*	是的	是的	否
重命名列	是的*	不	是的*	是的
对列重新排序	不	是的	是的	不
设置列默认值	是的	不	是的	是的
更改列数据类型	不	是的	不	不
删除列默认值	是的	不	是的	是的
更改自动增量值	不	不	是的	不*
列设置NULL	不	是的*	是的	不
列设置NOT NULL	不	是的*	是的	不
修改或设置列的注释	是的	不	是的	是的

二、mysql5.7

5.7在线 索引DDL

操作	Instant	In Place	允许并发 DML	仅修改元数据
创建或添加二级索引	是的	不	是的	不
删除索引	是的	不	是的	是的
重命名索引	是的	不	是的	是的
添加FULLTEXT索引	是的*	不*	不	不
添加SPATIAL索引	是的	不	不	不
更改索引类型	是的	不	是的	是的

5.7在线 主键DDL 

操作	Instant	In Place	允许并发 DML	仅修改元数据
添加主键	是的*	是的*	是的	不
删除主键	不	是的	不	不
删除一个主键并添加另一个	是的	是的	是的	不

5.7在线 列DDL

操作	Instant	In Place	允许并发 DML	仅修改元数据
添加列	是的	是的	是的*	不
删除一列	是的	是的	是的	不
重命名列	是的	不	是的*	是的
对列重新排序	是的	是的	是的	不
设置列默认值	是的	不	是的	是的
更改列数据类型	不	是的	不	不
扩展VARCHAR列大小	是的	不	是的	是的
删除列默认值	是的	不	是的	是的
更改自动增量值	是的	不	是的	不*
列设置NULL	是的	是的*	是的	不
列设置NOT NULL	是的*	是的*	是的	不
修改或设置列的注释	是的	不	是的	是的

三、mysql8.0

8.0在线 索引DDL

操作	Instant	In Place	重建表	允许并发 DML	仅修改元数据
创建或添加二级索引	不	是的	不	是的	不
删除索引	不	是的	不	是的	是的
重命名索引	不	是的	不	是的	是的
添加FULLTEXT索引	不	是的*	不*	不	不
添加SPATIAL索引	不	是的	不	不	不
更改索引类型	是的	是的	不	是的	是的

8.0在线 主键DDL 

操作	Instant	In Place	重建表	允许并发 DML	仅修改元数据
添加主键	不	是的*	是的*	是的	不
删除主键	不	不	是的	不	不
删除一个主键并添加另一个	不	是的	是的	是的	不

8.0在线 列DDL

操作	Instant	In Place	重建表	允许并发 DML	仅修改元数据
添加列	是的*	是的	不*	是的*	是的
删除一列	是的*	是的	是的	是的	是的
重命名列	是的*	是的	不	是的*	是的
对列重新排序	不	是的	是的	是的	不
设置列默认值	是的	是的	不	是的	是的
更改列数据类型	不	不	是的	不	不
扩展VARCHAR列大小	不	是的	不	是的	是的
删除列默认值	是的	是的	不	是的	是的
更改自动增量值	不	是的	不	是的	不*
列设置NULL	不	是的	是的*	是的	不
列设置NOT NULL	不	是的*	是的*	是的	不
修改或设置列的注释	是的	是的	不	是的	是的