MySQL DDL详情揭秘
发布时间：2022-04-04 11:09:59 所属栏目：MySql教程 来源：互联网
导读：MySQL中DDL语句，即数据定义语言，用于创建、删除、修改、库或表结构，对数据库或表的结构操作。常见的有create，alter，drop等。这类语句通常会耗费很大代价，特别是对于大表做表结构变更。本篇文章会揭露各类DDL语句执行的详细情况。 1.Online DDL简介
|
MySQL中DDL语句，即数据定义语言，用于创建、删除、修改、库或表结构，对数据库或表的结构操作。常见的有create，alter，drop等。这类语句通常会耗费很大代价，特别是对于大表做表结构变更。本篇文章会揭露各类DDL语句执行的详细情况。 1.Online DDL简介 在MySQL的早期版本中，DDL操作因为锁表会和DML操作发生锁冲突，大大降低并发性。在早期版本中，大部分DDL操作的执行原理就是通过重建表的方式，因为要复制原表数据，所以会长时间锁表，只能读不能写，DDL操作和DML操作有很严重的冲突。从MySQL5.6开始，很多DDL操作过程都进行了改进，出现了Online DDL，用于支持DDL执行期间DML语句的并行操作，提高数据库的吞吐量。 ALGORITHM=INPLACE，可以避免重建表带来的IO和CPU消耗，保证ddl期间依然有良好的性能和并发。 ALGORITHM=COPY，需要拷贝原始表，所以不允许并发DML写操作，可读。这种copy方式的效率还是不如 inplace ，因为前者需要记录undo和redo log，而且因为临时占用buffer pool引起短时间内性能受影响。 上面只是 Online DDL 内部的实现方式，此外还有 LOCK 选项控制是否锁表，根据不同的DDL操作类型有不同的表现：默认MySQL尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。 2.不同类DDL操作详情 不同种类DDL语句具体的执行情况是不同的，下表列举出常见DDL语句具体的执行详情，包括是否允许读写及是否锁表。这个表格希望大家可以详细对比看下，特别要关注下需要copy table的DDL操作。 操作 支持方式 Allow R/W 说明 add/create index online 允许读写 当表上有FULLTEXT索引除外，需要锁表，阻塞写 drop index online 允许读写 操作元数据，不涉及表数据。所以很快，可以放心操作 optimize table online 允许读写 当带有fulltext index的表用copy table方式并且阻塞写 alter table...engine=innodb online 允许读写 当带有fulltext index的表用copy table方式并且阻塞写 add column online 允许读写(增加自增列除外) 1、添加auto_increment列要锁表，阻塞写;2、虽采用online方式，但是表数据需要重新组织，所以增加列依然是昂贵的操作 drop column online 允许读写(增加自增列除外) 同add column，重新组织表数据，，昂贵的操作 Rename a column online 允许读写 操作元数据;不能改列的类型，否则就锁表 Reorder columns online 允许读写 重新组织表数据，昂贵的操作 Make column NOT NULL online 允许读写 重新组织表数据，昂贵的操作 Change data type of column copy table 仅支持读，阻塞写 创建临时表，复制表数据，昂贵的操作 Set default value for a column online 允许读写 操作元数据，因为default value存储在frm文件中，不涉及表数据。所以很快，可以放心操作 alter table xxx auto_increment=xx online 允许读写 操作元数据，不涉及表数据。所以很快，可以放心操作 Add primary key online 允许读写 昂贵的操作 Convert character set copy table 仅支持读，阻塞写 如果新字符集不同，需要重建表，昂贵的操作 3.DDL最佳实践 虽然MySQL 5.6和5.7版本提供了Online DDL操作，但Online DDL仍存在以下问题： 执行DDL前查看下该表有没有被事务占用，防止出现MDL锁。 执行DDL前确保datadir，tmpdir磁盘空间足够。 能业务低峰期操作的DDL，都尽量安排在业务低峰期进行。 对于大表和较大表，如果对复制延迟和主库性能敏感，建议改为gh-ost或pt-osc工具。 对于并发操作较高的表，无论表数据量多少，不能在业务高峰期操作。 同个表的多个DDL语句可以合并在一起进行，避免多次table rebuild带来的消耗。但是也要注意分组，比如需要copy table和只需inplace就能完成的，应该分两个alter语句。 （编辑：海南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐