首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

参考指南：MySQL&MariaDBOnlineDDL

作者：手机用户2602931635 | 来源：互联网 | 2020-12-02 19:03
MySQL教程栏目介绍指导MySQL&MariaDBOnlineDDL。

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

二级索引

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

说明：

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

主键

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

说明：

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

普通列

操作INSTANTINPLACE重建表并发 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

生成列

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

外键

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

说明：

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

表

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

说明：

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

表空间

操作INSTANTINPLACE重建表并发 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社区 其它相关文章！


推荐阅读
author-avatar
手机用户2602931635
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有