mysql怎么用ddl 怎样使用MySQL

mysql 存储过程 DDL 参数

MySQL8.0 开始支持原⼦ DDL(atomic DDL)，数据字典的更新，存储引擎操作，写⼆进制日志结合成了一个事务。在没有原⼦DDL之前，DROP TABLE test1,test2；如遇到server crash，可能会有test1被drop了，test2没有被drop掉。下面来看下在MySQL8.0之前和MySQL8.0 数据字典的区别

弓长岭网站制作公司哪家好，找成都创新互联！从网页设计、网站建设、微信开发、APP开发、成都响应式网站建设等网站项目制作，到程序开发，运营维护。成都创新互联公司2013年成立到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选成都创新互联。

在MySQL8.0 之前，Data Dictionary除了存在与.FRM, .TRG, .OPT ⽂件外，还存在于系统表中（MyISAM ⾮事务引擎表中），在MySQL8.0 ，Data Dictionary 全部存在于Data Dictionary Storage Engine（即 InnoDB表中），这使crash recovery 维持原⼦性成为了可能

存储引擎⽀持

目前，只有InnoDB存储引擎⽀持原子DDL，为了实现原子DDL，Innodb要写DDL logs 到 mysql.innodb_ddl_log 表，这是⼀个隐藏在mysql.ibd 数据字典表空间⾥的数据字典表。要看mysql.innodb_ddl_log 中的内容，需要

SET GLOBAL LOG_ERROR_VERBOSITY=3;（MySQL 8.0 默认为2，error log 记录Errors and

warnings，不不记录notes）

SET GLOBAL innodb_print_ddl_logs=1;

CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;

查看error log

[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=30,

thread_id=25, space_id=9, old_file_path=./test/t1.ibd]

[Note] [MY-011066] InnoDB: DDL log delete : by id 30

[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=31,

thread_id=25, table_id=1066, new_file_path=test/t1]

[Note] [MY-011066] InnoDB: DDL log delete : by id 31

[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: FREE, id=32, thread_

id=25, space_id=9, index_id=143, page_no=4]

[Note] [MY-011066] InnoDB: DDL log delete : by id 32

[Note] [MY-011066] InnoDB: DDL log post ddl : begin for thread id : 25

[Note] [MY-011066] InnoDB: DDL log post ddl : end for thread id : 25

原子DDL 操作步骤

准备：创建所需的对象并将DDL⽇志写入 mysql.innodb_ddl_log表中。DDL日志定义了如何前滚和回滚DDL操作。

执行：执⾏DDL操作。例如，为CREATE TABLE操作执⾏创建。

提交：更新数据字典并提交数据字典事务。

Post-DDL：重播并从mysql.innodb_ddl_log表格中删除DDL⽇志。为确保回滚可以安全执⾏⽽不引⼊不⼀致性，在此最后阶段执⾏⽂件操作（如重命名或删除数据文件）。这一阶段还从 mysql.innodb_dynamic_metadata的数据字典表删除的动态元数据为了DROP TABLE，TRUNCATE和其它重建表的DDL操作。

⽆论事务是提交还是回滚，DDL日志都会mysql.innodb_ddl_log在Post-DDL阶段重播并从表中删除 。mysql.innodb_ddl_log如果服务器在DDL操作期间暂停，DDL⽇志应该只保留在表中。在这种情况下，DDL⽇志会在恢复后重播并删除。

在恢复情况下，当服务器重新启动时，可能会提交或回退DDL事务。如果在重做⽇志和⼆进制日志中存在DDL操作的提交阶段期间执⾏的数据字典事务，则该操作被认为是成功的并且被前滚。否则，在InnoDB重放数据字典重做日志时回滚不完整的数据字典事务 ，并且回滚DDL事务。

原⼦DDL ⽀持类型

• DROP TABLES , all tables dropped or none

• DROP SCHEMA, all entities in the schema are dropped, or none

• Note that atomic DDL statements will be rolled back or committed even in case of crash, e.g. RENAME TABLES

• CREATE TABLE would be successfully committed or rolled back (no orphan ibd left)

• TRUNCATE TABLE (including InnoDB tables with FTS AUX tables) would be successfully committed or rolled back

• RENAME TABLES, all or none

• ALTER TABLE successful or not done

示例

请点击输入图片描述

请点击输入图片描述

结论

在MySQL8.0之前，alter table 操作在server crash的情况下，会遗留.frm,.ibd文件。MySQL8.0 能实现原⼦DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情况下，不会遗留.frm，.ibd临时文件。让我们⼀起期待MySQL8.0 GA的到来吧！

Mysql Online DDL 和 pt-ost 、gh-ost 简要

在 Mysql 5.6 之前版本中 ， 如果要修改一个表的ddl信息 ，需要锁表 。

具体步骤如下:

下面是Mysql官方文档对于DDL操作的总结:

可以使用 Alter 语句支持 DDL 特性 ，比如可以用 LOCK = NONE 无锁变更。

percona是一个开源产品 , 是管理Mysql的工具。

PT-OSC(Percona Toolkit Online Schema Change)

Percona Toolkit 包含很多 mysql 管理的功能 ，现在要说的是 online-schema-change上

PT-OSC 原理是建表 ，使用触发器同步数据 ，然后原子性rename。

这样可以支持在线无锁，不停机Online-DDL 。

具体步骤如下：

Percona 有一些限制和缺陷 ，根据它的原理 ，原表不能存在触发器 ，这玩意是唯一。另外原表必须存在PK或者UK。另外就是触发器的问题了，触发器带来性能开销，并且无法停止，那我就不能控制我同步的开关和速度。

但是gh-ost说它可以。

go-ost基于bin-log同步 ， 基于binlog肯定都是伪装成一个replica。

由于使用单线程回放binlog来替换触发器，所以增量DML回放效率不如触发器，因为pt-osc的增量回放并发度是与业务DML并发度相同的，是多线程的。

相对于percona的优势是:

因为出的太晚了 ，然后percona 和 gh-ost等等开源产品已经大规模实践了，Mysql就更加没什么实践案例和经验了，大家就不太愿意尝试或者迁移了。

大厂来说基本上都是平台封装了，类似idb ，会把无锁变更细节屏蔽了，只需要提工单就可以了 ，但是底层基本上也是建表同步rename个思路。

小公司的话，可以使用percona 、 go-ost 等工具。

MySQL 8.0 Online DDL和pt-osc、gh-ost深度对比分析

Mysql Online DDL

pt-online-schema-change

gh-ost

MySQL5.6在线表结构变更（online ddl）总结

mysql 8 新特性一 ALGORITHM=INSTANT 实现的快速DDL

参考

在线DDL之 快速增加列（秒级别的），并不会造成业务抖动。该功能自 MySQL 8.0.12 版本引入，是由腾讯游戏DBA团队贡献，我国程序员还是挺厉害的嘛。注意一下，此功能只适用于 InnoDB 表。实际上MySQL 5.7就已支持 Online DDL，虽说大部分 DDL 不影响对表DML操作，但是依然会消耗非常多的时间，且占用额外的磁盘空间，并会造成主从延迟，或者影响表的查询速度。有了这个ALGORITHM=INSTANT 就可应对瞬息万变的需求了。。

ALGORITHM=INSTANT 目前对6种ddl有效：

实际试验下，使用 mysql5.7的INPLACE 算法 时间: 52s。

使用 Instant Add Column ，时间:0.39 s。 果然是秒级别添加

当然我们不需要显式指定algorithm=instant;mysql会优先使用INSTANT算法来进行ddl的；若显式指定algorithm=instant 同时目标ddl不支持就会报错。如下，DROP COLUMN 时指定则报错

添加或删除virtual 列

添加或删除列默认值

修改 ENUM 定义

修改索引类型

重命名表，好像和5.7的INPLACE算法也没啥时间上的区别。INPLACE的rename table已经足够快了

还有一些特殊情况不能使用ALGORITHM=INSTANT的：

Instant Add Column只能将新字段添加到表的尾巴上，不能添加到中间!

不支持压缩表，即该表行格式不能是 COMPRESSED。

不支持包含全文索引的表；不支持临时表；不支持那些在数据字典表空间中创建的表。这些就不一一验证了。平时操作时要注意下！

mysql online ddl

看过这些文章：

文章之后，我觉得 mysql 5.x 的 online ddl 只是在 prepare 阶段、commit 阶段会有写锁，但是在真正执行的阶段都是读锁，不会阻塞读写。online ddl 过程如下：

1、拿 MDL 写锁

2、降级成 MDL 读锁

3、真正做 DDL

4、升级成 MDL 写锁

5、释放 MDL 锁。

1，2，4，5 阶段没有表冲突，执行时间非常短，只是第3阶段占用了 DDL 的绝大部分时间，这个期间表可以正常读写数据。

最后，还想说一点，通过 DBA 得知，8.0 加列只需要一秒，牛逼

技术分享 | MySQL 并行 DDL

随着 MySQL 版本的不断更新，对 DDL 操作的支持也在不断的完善和更新：比如从 MySQL 5.6 引入 Online DDL ，在 MySQL 5.7 对 Online DDL 进一步完善，到现在的 8.0 版本，则对 DDL 的实现重新进行了设计，比如 DDL 操作支持原子特性，在 MySQL 8.0.27 引入并行 DDL 。本篇就来探究一下 MySQL 8.0.27 的并行 DDL 对于 DDL 操作速度的提升。

MySQL 8.0.14 引入了 innodb_parallel_read_threads 变量来控制扫描聚簇索引的并行线程。MySQL 8.0.27 引入了 innodb_ddl_threads 变量来控制用于创建二级索引时的并行线程数量，此参数一般和一并引入的 innodb_ddl_buffer_size 一起使用，innodb_ddl_buffer_size 用于指定进行并行 DDL 操作时能够使用的 buffer 大小，buffer 是在所有的 DDL 并行线程中平均分配的，所以一般如果调大 innodb_ddl_threads 变量时，也需要调大 innodb_ddl_buffer_size 的大小。

innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 的默认大小分别为：

接下来测试一下调大 innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 参数值对 DDL 操作的性能提升。

首先创建一张 5000 万的表：

分别测试不同的线程数量和缓冲区大小的 DDL 操作时间，例如：

通过不断调整相关参数得到以下结果：

可以看到，随着并发线程的增多和 buffer 的增加，DDL 操作所占用的资源也越多，而 DDL 操作所花费的时间则越少。不过通过对比资源的消耗和 DDL 速度的提升比例，最合理的并行线程数量为4-8个，而 buffer 大小可以根据情况进行调整。

参考链接：

mysql 对一个大表做在线ddl，怎么进行实施的才能尽可能降低影响

可以采用中间表。假设你原始表名是“test”，那么步骤如下

建立一个和“test”一样表结构的新表，表名为test_new。create table test_new like test;

将test表中数据拷贝到test_new中。insert into test_new select * from test;

在test_new上执行ddl操作

最后将执行过ddl更新的test_new表改名为test，原test表改名为test_old。Rename table test to test_old, test_new to test;

确认检查无误后drop掉test_old表

如果test表很大，在第二步会消耗很长时间，那么第二步可以以主键ID为准，采用分段导入，一次导入比如5000条数据，多次导入，这样不会对生产环境造成太大影响，假设test表上有自增主键“form_id"，那么上面第二步命令变为：

insert into test_new select * from test where form_id between '1' and '5000';

insert into test_new select * from test where form_id between '5001' and '10000';

.

.

.