mysql 5.7 online ddl_MySQL DDL--MySQL 5.7版本Online DDL操作

最新推荐文章于 2022-07-25 17:46:02 发布
最新推荐文章于 2022-07-25 17:46:02 发布
阅读量261 收藏
点赞数
文章标签： mysql 5.7 online ddl
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42485957/article/details/113239330
版权
本文详细介绍了MySQL 5.7版本中在线DDL（Data Definition Language）操作，包括如何在线添加、删除和修改主键索引，以及二级索引的维护。在主键索引维护部分，讨论了不同情况下使用ALGORITHM=INPLACE和ALGORITHM=COPY的影响。同时，也涵盖了列属性和自增属性的修改，强调了在某些操作中需要使用LOCK=SHARED以确保数据一致性。
摘要由CSDN通过智能技术生成

主键索引维护

1、新增主键索引

## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。

ALTER TABLE tb001

ADD PRIMARY KEY (ID),

ALGORITHM = INPLACE;

Query OK, 0 rows affected (22.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

2、官网称如果创建主键索引时需要隐式转换列为NOT NULL则不能使用INPLACE方式(ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL)。

但在MySQL 5.7.19版本中测试发现在允许为NULL的列上创建主键，采用REBUILD INPLACE方式的Online DDL,操作期间允许表并发读写。

2、删除主键索引

## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。

ALTER TABLE tb001

DROP PRIMARY KEY,

ALGORITHM = INPLACE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY.

ALTER TABLE tb001

DROP PRIMARY KEY,

ALGORITHM=COPY;

Query OK, 800001 rows affected (20.01 sec)

Records: 800001 Duplicates: 0 Warnings: 0

3、删除并新增主键索引

## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。

ALTER TABLE TB001

DROP PRIMARY KEY,

ADD PRIMARY KEY(C1),

ALGORITHM=INPLACE,

LOCK=NONE;

Query OK, 0 rows affected (22.57 sec)

Records: 0 Duplicates: 0 Warnings: 0

4、修改主键列类型

## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。

ALTER TABLE TB001

CHANGE C1 C1 BIGINT NOT NULL,

ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ALTER TABLE TB001

CHANGE C1 C1 BIGINT NOT NULL,

ALGORITHM=COPY, LOCK=NONE;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

ALTER TABLE TB001

CHANGE C1 C1 BIGINT NOT NULL,

ALGORITHM=COPY, LOCK=SHARED;

Query OK, 799998 rows affected (20.49 sec)

Records: 799998 Duplicates: 0 Warnings: 0

二级索引维护

1、新增二级索引

## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。

ALTER TABLE TB001 ADD INDEX IDX_C1(C1);

Query OK, 0 rows affected (10.53 sec)

Records: 0 Duplicates: 0 Warnings: 0

2、删除二级索引

## 仅需元数据修改操作，操作时间极短。

ALTER TABLE TB001 DROP INDEX IDX_C1;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

3、修改二级索引名

## 仅需元数据修改操作，操作时间极短。

ALTER TABLE TB001

RENAME INDEX IDX_C1 TO IDX_C1_NEW,

ALGORITHM=INPLACE, LOCK=NONE;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

4、修改二级索引列类型

## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。

ALTER TABLE TB001 CHANGE C1 C1 INT;

Query OK, 800000 rows affected (25.03 sec)

Records: 800000 Duplicates: 0 Warnings: 0

ALTER TABLE TB001

CHANGE C1 C1 BIGINT,

ALGORITHM = INPLACE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

自增属性修改

1、修改普通主键列为自增主键列

## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。

ALTER TABLE TB001 MODIFY ID BIGINT AUTO_INCREMENT;

Query OK, 800000 rows affected (26.15 sec)

Records: 800000 Duplicates: 0 Warnings: 0

2、修改自增主键列为普通主键列

## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。

ALTER TABLE TB001 MODIFY ID BIGINT NOT NULL;

Query OK, 800000 rows affected (28.27 sec)

Records: 800000 Duplicates: 0 Warnings: 0

3、修改表自增开始值

## 仅需要修改元数据信息，操作时间极短。

ALTER TABLE TB001 AUTO_INCREMENT=800020;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

列属性修改

1、新增列

## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。

## 无论是新增可为NULL的列还是NOT NULL的列

ALTER TABLE TB001

ADD C2 INT NOT NULL DEFAULT 99999,

ALGORITHM=INPLACE, LOCK=NONE;

Query OK, 0 rows affected (33.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

2、删除列

## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。

ALTER TABLE TB001

DROP COLUMN C5,

ALGORITHM=INPLACE, LOCK=NONE;

Query OK, 0 rows affected (33.57 sec)

Records: 0 Duplicates: 0 Warnings: 0

3、修改列数据类型(INT至BIGINT)

## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。

ALTER TABLE TB001

CHANGE C5 C5 BIGINT NULL,

ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ALTER TABLE TB001

CHANGE C5 C5 BIGINT NULL,

ALGORITHM=COPY, LOCK=SHARED;

Query OK, 0 rows affected (29.63 sec)

Records: 0 Duplicates: 0 Warnings: 0

4、修改列类型显示长度(INT(10)至INT(8)或INT(8)至INT(10))

## 无论使用CHANGE命令还是MODIFY命令，都是修改元数据，操作时间极短。

ALTER TABLE tb001

MODIFY C2 INT(8),

ALGORITHM=INPLACE, LOCK=NONE;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

ALTER TABLE tb001

CHANGE C2 C2 INT(8) DEFAULT NULL,

ALGORITHM=INPLACE, LOCK=NONE;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

5、修改列类型扩展存储长度(VARCHAR(300)至VARCHAR(500))

## 无论使用CHANGE命令还是MODIFY命令，都是修改元数据，操作时间极短。

ALTER TABLE tb001

CHANGE C3 C3 varchar(500) DEFAULT NULL,

ALGORITHM=INPLACE, LOCK=NONE;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(500),

ALGORITHM=INPLACE, LOCK=NONE;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

6、修改列类型扩展存储长度(VARCHAR(10)至VARCHAR(100))

## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(100),

ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(100),

ALGORITHM=COPY, LOCK=SHARED;

Query OK, 0 rows affected (33.57 sec)

Records: 0 Duplicates: 0 Warnings: 0

7、修改列类型收缩存储长度(VARCHAR(500)至VARCHAR(300))

## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。

ALTER TABLE tb001

CHANGE C3 C3 VARCHAR(300) DEFAULT NULL,

ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(300),

ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(300),

ALGORITHM=COPY, LOCK=SHARED;

Query OK, 800009 rows affected (27.28 sec)

Records: 800009 Duplicates: 0 Warnings: 0

8、修改修改列注释

## 仅需修改元数据，操作时间极短

ALTER TABLE tb001

MODIFY COLUMN C3` varchar(300) DEFAULT NULL COMMENT '测试1';

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

ALTER TABLE tb001

CHANGE C3 C3 varchar(300) DEFAULT NULL COMMENT '测试2';

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

MySQL 5.7版本下DDL操作

b0ccfdfc2f73d99f5e2417d9951ca0d0.png

翻越时空你也难以忘记
关注
mysql 5.7 online ddl_MySQL的Online DDL语句
weixin_36371924的博客
01-27 644
概念什么是OnlineDDL？在了解OnlineDDL之前，我们先来了解一下什么是DDL。说DDL，就不得不提一下它的另外两个兄弟：DML和DCL。他们三者的区别如下：DDL：data definition language，数据定义语言，用来定义数据的语句。例如我们平时的create table，create index，create function等。DML：data manipulatio...
mysql 5.7 online ddl_MySQL 5.7 特性：Online DDL
weixin_31764625的博客
01-27 312
MySQL DDL 的方法MySQL 的 DDL 有很多种方法。MySQL 本身自带三种方法，分别是：copy、inplace、instant。copy 算法为最古老的算法，在 MySQL 5.5 及以下为默认算法。从 MySQL 5.6 开始，引入了 inplace 算法并且默认使用。inplace 算法还包含两种类型：rebuild-table 和 not-rebuild-table。MySQ...
MySQL5.7—在线DDL总结
weixin_33691817的博客
08-16 953
---切记：DDL操作要在业务低峰期进行1、MySQL各版本，对于DDL的处理方式是不同的，主要有三种：①：Copy Table方式： 这是InnoDB最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临 时表，然后Rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会...
5分钟了解MySQL5.7的Online DDL雷区
weixin_33912453的博客
09-23 766
Part1:写在最前Online DDL，当新手听到这个名字的时候，非常高兴，以为无论什么情况下，修改表结构都不会锁表，理想很丰满，现实很骨感！读完本文，教你如何避开这些雷区，安全的修改表结构。话不多说，我们分别来看下MySQL5.6和MySQL5.7在修改表结构上的相同和异同。Part2:5.6.25的表现①首先我们构造数据并进行测试mysql> create database helei...
mysql5.7 onlineddl原理_5分钟了解MySQL5.7的Online DDL雷区
weixin_32481609的博客
01-21 130
Part1:写在最前Online DDL，当新手听到这个名字的时候，非常高兴，以为无论什么情况下，修改表结构都不会锁表，理想很丰满，现实很骨感！读完本文，教你如何避开这些雷区，安全的修改表结构。话不多说，我们分别来看下MySQL5.6和MySQL5.7在修改表结构上的相同和异同。Part2:5.6.25的表现①首先我们构造数据并进行测试Tips:心得可以看出5.6.25这里并不支持ALGORITH...
mysql5.7 online DDL
sinat_36355775的博客
10-19 474
基础材料： centos7.5 mysql 5.7.24 online DDL是在mysql5.6版本后加入的特性，用于支持DDL执行期间DML语句的并行操作，提高数据库的吞吐量。 online DDL结构简图如下： 由上图可知online DDL大体可以分为3部分： 1、copy（ALGORITHM=COPY）这部分是offline的，在DDL执行期间其他DML不能并行，也是5.6版本前的DDL执行方法。其间生成临时表（server层的操作支持所有引擎），用于写入原表修改过的数据，同时在原.
mysql5.7安装方法和基本操作
m0_52091913的博客
07-25 493
mysql5.7安装方法和基本操作方法
Mysql5.7 索引
u010648194的博客
03-07 1158
怎么理解索引： 一个表中有很多列，对其中一个列创建了索引，那么mysql就会对这个列的数据进行排序，好处就是查询会非常的快。 B+树的缺点： 当对表的字段进行修改时，维护B+树索引的代价会比较大。 ...
mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
07-29
同时，监控MySQL的性能，定期备份，以及使用工具如pt-online-schema-change进行在线DDL修改，都是日常运维中不可或缺的部分。 总之，MySQL 5.7.31在Linux上的部署不仅提供了强大的数据库服务，而且通过优化的特性...
mysql 大表ddl_大型表的DDL操作（转）
weixin_30555169的博客
01-19 939
add by zhj: 关系型数据库有Schema，修改Schema的时间比较长，一般都要秒级和分钟级。主备延迟有相同的数量级，对于不支持Inplace方式的DDL，只能用Copy方式，Copy方式在执行DDL时，整个表不可读写，影响非常大。不过，对于MySQL5.6(尤其是MySQL5.7)，绝大部分常用的DDL都支持Inplace，DDL加MDL写锁的时间很短，所以主要评估主备延迟的影响就可以...
mysql 在线ddl_MySQL5.7—在线DDL总结
weixin_33109551的博客
01-19 287
---切记：DDL操作要在业务低峰期进行1、MySQL各版本，对于DDL的处理方式是不同的，主要有三种：①：Copy Table方式： 这是InnoDB最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临 时表，然后Rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。②...
mysql5.7 onlineddl原理,MYSQL5.7 ONLINE DDL
weixin_33236595的博客
03-17 469
MYSQL5.7 ONLINE DDLmysql版本信息mysql> select @@version;+-----------+| @@version |+-----------+| 5.7.22 |+-----------+创建测试表mysql> create table trsenzhang(id int(10) unsigned not null auto_increment,...
MySQL 5.7: 新增的online DDL操作
weixin_34297300的博客
05-10 382
(这是个汇总文章，后续有新的Online DDL操作 会不断更新…) 1. 支持ALTER TABLE t1 RENAME INDEX idx1 to idx2; (Server层)语法支持的worklog: http://dev.mysql.com/worklog/task/?id=6555 (Innodb层）Online DDL的worklog: h...
MySQL5.7 Online DDL
aa5181的专栏
08-01 123
1. ALter table (5.7) 一般情况下，alter table 都会对原有的表做一个临时的副本拷贝，然后将所做的该表应用到副本，之后再将原表删除，rename 副本。在这个过程中，原...
MySQL 5.7 InnoDB Online DDL
一条大河
01-16 221
MySQL 5.7 Innodb 允许in-place就地操作，操作时可以同步执行DML，尽可能减少对业务的影响；这极大提高了繁忙生产环境的可用性；而在版本5.6的时候，许多操作基本都需要COPY表 或者 阻塞表； DDL的时候可以用LOCK选项来协调性能和并发性之间的关系； a, LOCK=EXCLUSIVE DDL同时拒绝其他所有操作 b, LOCK=SHARE DDL同时允许查询，但不允许写入 c, LOCK=NONE DDL同时，允许增删改查 d, LOCK=DEFAULT 或者 省略
mysql 5.7 online ddl_MySQL 5.7 online ddl的一些改进
weixin_42403771的博客
01-19 246
MySQL DBA应该都知道，数据库操作里面，DDL操作(比如CREATE，DROP，ALTER等)代价是非常高的，特别是在单表上千万的情况下，加个索引或改个列类型，就有可能堵塞整个表的读写。从MySQL 5.6开始，大家期待的Online DDL出现了，可以实现修改表结构的同时，依然允许DML操作(select,insert,update,delete)。但改变字段类型，比如从CHAR变为VAR...
mysql 5.7 online ddl_MySQL Online DDL 原理和踩坑
weixin_28864139的博客
01-27 957
MySQL Online DDL 原理和踩坑MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。在 MySQL 5.6 之前，MySQL 的 DDL 操作会按照原来的表复制一份，并做相应的修改，例如，对表 A 进行 DDL 的具体过程如下：1、 按照表 A 的定义新建一个表 B2、 对表 A 加写锁3、 在表 B 上执行 DDL 指定的操...
MySQL 5.7中定位DDL阻塞：使用metadata_locks表
本文主要探讨了如何在MySQL 5.7中解决DDL（Data Definition Language）操作被阻塞的问题，特别是在MDL（Metadata Locks）环境下。MDL是一种用于保护元数据的锁定机制，防止并发操作导致的数据不一致。文章提到了在...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值