文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL数据库中DDL语句的介绍和使用

2024-04-02 19:55

关注

这篇文章的知识点包括:DDL语句的简介、DDL语句的操作以及DDL语句的使用注意事项,阅读完整文相信大家对MySQL数据库中DDL语句有了一定的认识。

1.Online DDL简介

在MySQL的早期版本中,DDL操作因为锁表会和DML操作发生锁冲突,大大降低并发性。在早期版本中,大部分DDL操作的执行原理就是通过重建表的方式,因为要复制原表数据,所以会长时间锁表,只能读不能写,DDL操作和DML操作有很严重的冲突。从MySQL5.6开始,很多DDL操作过程都进行了改进,出现了Online DDL,用于支持DDL执行期间DML语句的并行操作,提高数据库的吞吐量。

MySQL 在线DDL分为 INPLACECOPY 两种方式,通过在ALTER语句的ALGORITHM参数指定。

上面只是 Online DDL 内部的实现方式,此外还有 LOCK 选项控制是否锁表,根据不同的DDL操作类型有不同的表现:默认MySQL尽可能不去锁表,但是像修改主键这样的昂贵操作不得不选择锁表。

但是有一点需要说明,无论任何模式下,Online DDL开始之前都需要一个短时间排它锁(exclusive)来准备环境,所以alter命令发出后,会首先等待该表上的其它操作完成,在alter命令之后的请求会出现等待waiting meta data lock。同样在DDL结束之前,也要等待alter期间所有的事务完成,也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行,否则一样出现连环锁表。

2.不同类DDL操作详情

不同种类DDL语句具体的执行情况是不同的,下表列举出常见DDL语句具体的执行详情,包括是否允许读写及是否锁表。这个表格希望大家可以详细对比看下,特别要关注下需要copy table的DDL操作。

操作支持方式Allow R/W说明
add/create indexonline允许读写当表上有FULLTEXT索引除外,需要锁表,阻塞写
drop indexonline允许读写操作元数据,不涉及表数据。所以很快,可以放心操作
optimize tableonline允许读写当带有fulltext index的表用copy table方式并且阻塞写
alter table...engine=innodbonline允许读写当带有fulltext index的表用copy table方式并且阻塞写
add columnonline允许读写(增加自增列除外)1、添加auto_increment列要锁表,阻塞写;2、虽采用online方式,但是表数据需要重新组织,所以增加列依然是昂贵的操作
drop columnonline允许读写(增加自增列除外)同add column,重新组织表数据,,昂贵的操作
Rename a columnonline允许读写操作元数据;不能改列的类型,否则就锁表
Reorder columnsonline允许读写重新组织表数据,昂贵的操作
Make column NOT NULLonline允许读写重新组织表数据,昂贵的操作
Change data type of column copy table仅支持读,阻塞写 创建临时表,复制表数据,昂贵的操作
Set default value for a columnonline允许读写操作元数据,因为default value存储在frm文件中,不涉及表数据。所以很快,可以放心操作
alter table xxx auto_increment=xx online允许读写操作元数据,不涉及表数据。所以很快,可以放心操作 
Add primary keyonline允许读写昂贵的操作
Convert character set copy table仅支持读,阻塞写如果新字符集不同,需要重建表,昂贵的操作
3.DDL最佳实践

虽然MySQL 5.6和5.7版本提供了Online DDL操作,但Online DDL仍存在以下问题:

针对DDL,下面整理下几点干货建议,之后执行DDL语句时可以参考下:

  1. 执行DDL前查看下该表有没有被事务占用,防止出现MDL锁。
  2. 执行DDL前确保datadir,tmpdir磁盘空间足够。
  3. 能业务低峰期操作的DDL,都尽量安排在业务低峰期进行。
  4. 对于大表和较大表,如果对复制延迟和主库性能敏感,建议改为gh-ost或pt-osc工具。
  5. 对于并发操作较高的表,无论表数据量多少,不能在业务高峰期操作。
  6. 同个表的多个DDL语句可以合并在一起进行,避免多次table rebuild带来的消耗。但是也要注意分组,比如需要copy table和只需inplace就能完成的,应该分两个alter语句。

以上就是MySQL数据库中DDL语句的介绍和使用,看完之后是否有所收获呢?如果想了解更多相关内容,欢迎关注亿速云行业资讯!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯