参考指南：MySQL&MariaDBOnlineDDL

作者：手机用户2602931635 | 来源：互联网 | 2020-12-02 19:03

MySQL教程栏目介绍指导MySQL&MariaDBOnlineDDL。

MySQL教程栏目介绍指导MySQL & MariaDB Online DDL。

二级索引

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
创建或者添加二级索引	❌	✅	❌	✅	❌
删除索引	❌	✅	❌	✅	✅
重命名索引（⚠️MySQL 5.7+，MariaDB 10.5.2+）	❌	✅	❌	✅	✅
添加 `FULLTEXT` 索引	❌	✅ ①	❌ ①	❌	❌
添加 `SPATIAL` 索引（⚠️MySQL 5.7+，MariaDB 10.2.2+）	❌	✅	❌	❌	❌
修改索引类型	✅	✅	❌	✅	✅

说明：

① 第一次添加全文索引字段时需要重建表，之后就不需要了

主键

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
添加主键	❌	✅ ②	✅ ②	✅	❌
删除主键	❌	❌	✅	❌	❌
删除一个主键同时添加一个新的	❌	✅	✅	✅	❌

说明：

重建聚簇索引总是需要拷贝表数据（InnoDB 是“索引组织表”），所以最好是在创建表的时候就定义好主键
如果创建表是没有指定主键，InnoDB 会选择第一个 NOT NULL 的 UNIQUE 索引作为主键，或者使用系统生成的 KEY
② 对聚簇索引来说，使用 INPLACE 模式比 COPY 模式要高效一些：不会产生 undo log 和 redo log，二级索引是有序的，所以可以按顺序加载，不需要使用变更缓冲区

普通列

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
列添加	✅ ③	✅	❌ ③	✅ ③	❌
列删除	❌ ④	✅	✅	✅	❌
列重命名	❌	✅	❌	✅ ⑤	✅
改变列的顺序	❌ ⑫	✅	✅	✅	❌
设置默认值	✅	✅	❌	✅	✅
修改数据类型	❌	❌	✅	❌	❌
扩展 `VARCHAR` 长度（⚠️MySQL 5.7+, MariaDB 10.2.2+）	❌ ⑬	✅	❌ ⑥	✅	✅
删除列的默认值	✅	✅	❌	✅	✅
改变自增值	❌	✅	❌	✅	❌ ⑦
设置列为 NULL	❌	✅	✅ ⑧	✅	❌
设置列为 NOT NULL	❌	✅ ⑨	✅ ⑨	✅	❌
修改 `ENUM` 和 `SET` 列的定义	✅	✅	❌ ⑩	✅	✅

说明：

③ 并发 DML：当插入一个自增列时，不支持并发的 DML 操作，添加自增列时，大量的数据会被重新组织，代价高昂
③ 重建表：添加列时，MySQL 5.7及之前版本需要重建表，MySQL 8.0 当 ALGORITHM=INPLACE 时，需要重建表，ALGORITHM=INSTANT 时不需要重建
③ INSTANT算法：添加列时，使用 INSTANT 算法有下面这些限制
- 添加列操作不能和其它不支持 INSTANT 算法的操作合并为一条 ALTER TABLE 语句
- 新增的列只能添加到表的最后，不能放到其它列的前面，在 MariaDB 10.4 之后，支持在任意位置添加
- 不能将列添加到 ROW_FORMAT=COMPRESSED 的表中
- 不能将列添加到包含 FULLTEXT 的表中
- 不能将列添加到临时表中，临时表只支持 ALGORITHM=COPY
- 不能将列添加到驻留在数据字典表空间中的表中
- 在添加列的时候不会计算行的大小限制，该限制在执行 DML 操作插入或者更新表时才会被检查
④ 删除列时，大量的数据需要被重新组织，代价高昂，在 MariaDB 10.4 之后，删除列支持 INSTANT 算法
⑤ 重命名列时，确保只改变列名，不改变数据类型，这样才能支持并发的 DML 操作
⑥ 扩展 VARCHAR 长度时，INPLACE 是有条件的，必须保证用于标识字符串长度的长度字节不变（这里说的都是字节，不是 VARCHAR 的字符长度，字节占用与采用的字符集有关，utf8 字符集下，一个字符占 3 个字节， utf8mb4 则 4 个字节）
- 当 VARCHAR 列长度在 0-255 个字节时，长度标识占用一个字节
- 当 VARCHAR 列长度大于 255 个字节时，长度标识占用两个字节
因此，INPLACE 只支持 0-255 个字节之间或者 256 个字节到更大的长度之间的变更。VARCHAR 列长度减小是不支持 INPLACE 的。
⑦ 自增列值变更是修改的内存中的值，不是数据文件
⑧ ⑨ 设置列为 [NOT] NULL 时，大量的数据被重新组织，代价高昂
⑩ 修改 ENUM 和 SET 类型的列定义时，是否需要表拷贝取决于已有元素的个数和插入成员的位置
⑫ 在 MariaDB 10.4 之后，列排序支持 INSTANT 算法
⑬ 在 MariaDB 10.4.3 之后，InnoDB 支持使用 INSTANT 算法增加列的长度，但是也有一些限制，具体参考 Changing the Data Type of a Column

生成列

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
添加 `STORED` 列	❌	❌	✅	❌	❌
修改 `STORED` 列的排序	❌	❌	✅	❌	❌
删除 `STORED` 列	❌	✅	✅	✅	❌
添加 `VIRTUAL` 列	✅	✅	❌	✅	✅
修改 `VIRTUAL` 列的排序	✅	❌	✅	❌	❌
删除 `VIRTUAL` 列	✅	✅	❌	✅	✅

外键

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
添加外键约束	❌	✅ ⑭	❌	✅	✅
删除外键约束	❌	✅	❌	✅	✅

说明：

⑭ 添加外键时，只有当 foreign_key_checks 选项被禁用的时候才支持 INPLACE 算法

表

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
修改 `ROW_FORMAT`	❌	✅	✅	✅	❌
修改 `KEY_BLOCK_SIZE`	❌	✅	✅	✅	❌
设置持久表统计信息	❌	✅	❌	✅	✅
指定字符集	❌	✅	✅ ⑮	❌	❌
转换字符集	❌	❌	✅ ⑯	❌	❌
优化表	❌	✅ ⑰	✅	✅	❌
使用 `FORCE` 选项重建表	❌	✅ ⑱	✅	✅	❌
执行空的重建	❌	✅ ⑲	✅	✅	❌
重命名表	✅	✅	❌	✅	✅

说明：

⑮⑯ 当字符集不同时，需要重建表
⑰⑱⑲ 如果表中包含 FULLTEXT 的字段，则不支持 INPLACE

表空间

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
重命名常规表空间	❌	✅	❌	✅	✅
启用或者禁用常规表空间加密	❌	✅	❌	✅	❌
启用或者禁用 `file-per-table` 表空间加密	❌	❌	✅	❌	❌

限制

在临时表 TEMPORARY TABLE 上创建索引时会发生表拷贝
如果表上有 ON...CASCADE 或者 ON...SET NULL 约束，则 ALERT TABLE 不支持字句 LOCK=NONE
在 Onlne DDL 操作完成之前，它必须等待相关表已经持有元数据锁的事务提交或者回滚，在这个过程中，相关表的新事务会被阻塞，无法执行
当在大表上执行涉及到表重建的 DDL 时，会存在以下限制
- 没有任何机制可以暂停 Online DDL操作或限制 Online DDL 操作的 I/O 或CPU使用率
- 如果操作失败，则回滚 Online DDL操作的代价非常高昂
- 长时间运行的 Online DDL 可能会导致复制延迟。 Online DDL 操作必须在 Master 上执行完成后才能在 Slave 上执行，在这个过程中，并发处理的 DML 在 Slave 上面必须等待 DDL 操作完成后才会执行。

写在最后

本文将会持续修正和更新，更多精彩内容请 follow me。

更多相关免费学习推荐：mysql教程(视频)

以上就是参考指南：MySQL & MariaDB Online DDL的详细内容，更多请关注第一PHP社区其它相关文章！

推荐阅读

bit
阿里云主机实战应用之centos7上的防火墙设置

最近公司又上了一台服务器，以前都是用centos6系统，这次选择使用了centos7系统的安装镜像，因为现在程序版本在centos7上一般php默认就是5.4以上的，mysql也 ... [详细]

蜡笔小新 2023-10-14 17:14:15
bit
数据库进入全新时代，腾讯云发布五大数据库提前布局

8月28日，腾讯云数据库在京正式启动战略升级，宣布未来将聚焦云原生、自治、超融合三大战略方向，以用户为中心，联接未来。并在现场面向全球用户同步发布五大战略级新品，包括数据库智能管家 ... [详细]

蜡笔小新 2023-10-14 16:03:56
go
MySQL/MariaDB/PerconaDB提权条件漏洞

背景　　2016年11月01日，国外安全研究员DawidGolunski在MyS ... [详细]

蜡笔小新 2023-10-13 13:41:35
go
如何部署Zabbix监控实现监测和报警机制

这篇文章的知识点包括：Zabbix的安装部署、Zabbix监控的实现以及Zabbix报警机制的实现，阅读完整文相信大家对Zabbix监控的使用有了一定的认识。Zabb ... [详细]

蜡笔小新 2023-10-12 20:17:02
go
mysql数据库生成表插件_screw 数据库文档生成工具

简洁好用的数据库表结构文档工具在企业级开发中、我们经常会有编写数据库表结构文档的时间付出，从业以来，待过几家企业，关于数据库表结构文档状态：要么没有、要么有、但都是手写 ... [详细]

蜡笔小新 2023-10-12 19:37:43
join
【mysql基础】01、mysql的历史

1、MySQL标志说明MySQL的海豚标志的名字叫“sakila”，它是由MySQLAB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由 ... [详细]

蜡笔小新 2023-10-12 18:18:24
join
MySQL for OPS 08：MHA 高可用

MySQL for OPS 08：MHA 高可用 ... [详细]

蜡笔小新 2023-10-12 18:01:14
join
在线问卷工具LimeSurvey

什么是LimeSurvey？LimeSurvey是一款开源的在线问卷管理系统，具有问卷的设计、修改、发布、回收和统计等多项功能。它集成了调查程序开发、调 ... [详细]

蜡笔小新 2023-10-12 11:13:07
go
云原生边缘计算之KubeEdge简介及功能特点

本文介绍了云原生边缘计算中的KubeEdge系统，该系统是一个开源系统，用于将容器化应用程序编排功能扩展到Edge的主机。它基于Kubernetes构建，并为网络应用程序提供基础架构支持。同时，KubeEdge具有离线模式、基于Kubernetes的节点、群集、应用程序和设备管理、资源优化等特点。此外，KubeEdge还支持跨平台工作，在私有、公共和混合云中都可以运行。同时，KubeEdge还提供数据管理和数据分析管道引擎的支持。最后，本文还介绍了KubeEdge系统生成证书的方法。 ... [详细]

蜡笔小新 2023-12-14 16:49:01
go
2020年AI产业报告：100个岗位抢1个人，计算机视觉成最大缺口

“你永远都不知道明天和‘公司的意外’哪个先来。”疫情期间，这是我们最战战兢兢的心情。但是显然，有些人体会不了。这份行业数据，让笔者“柠檬” ... [详细]

蜡笔小新 2023-12-14 12:23:22
go
MYsql_linux mysql

篇首语：本文由编程笔记#小编为大家整理，主要介绍了linuxmysql相关的知识，希望对你有一定的参考价值。一数据库安装查看：[[email pr ... [详细]

蜡笔小新 2023-10-12 18:44:33
join
mysql – select vs left join中的子查询

通常,我必须在查询中返回某些行的计数,以获取其他不相关的行.例如A表用户表格查看和表格图片User:idnicknameReview:idto_user_idfrom_user_i ... [详细]

蜡笔小新 2023-10-12 17:44:50
join
MySQL数据库 user表保存的密码问题

MySQL数据库 user表保存的密码问题 ... [详细]

蜡笔小新 2023-10-12 15:58:26
join
4.1shell中常用的基础命令

1.diff用法：diff[options]files|directorys输出信息：[num1,num2][a|c|d][num3,num4]nu ... [详细]

蜡笔小新 2023-10-12 13:21:39
char
优化mysql 聚合_mysql性能优化总结(三)

mysql体系结构插件式存储引擎,将数据的查询和存储相分离.每一款存储引擎都有各自的优缺点.可以灵活选用架构:客户端-mysql服务层-存储引擎层存储引擎是针对表,不是针对库, ... [详细]

蜡笔小新 2023-10-12 12:10:06

手机用户2602931635

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章