MySQL Online DDL原理解读

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL Online DDL原理解读

一、背景与意义

在传统的数据库系统中,执行DDL操作时通常需要锁定表,以防止数据不一致。然而,这种锁定会导致表在DDL操作期间不可用,从而影响数据库的可用性。MySQL原生Online DDL解决了这个问题,它允许DDL操作在表仍然可用时执行,大大提高了数据库的可用性,特别是对于需要24/7高可用性的应用来说,这是一个重要的特性。

二、工作机制

MySQL原生Online DDL的工作机制涉及多个步骤和内部原理:

1. 准备阶段

  • 检查与评估:在执行DDL操作之前,MySQL会进行一系列的检查和评估工作。这包括验证DDL操作的语法正确性、检查用户权限以及评估所需资源等。
  • 选择执行策略:根据DDL操作的类型和表的结构,MySQL会选择一个合适的执行策略。这通常涉及决定是使用COPY算法、INPLACE算法还是INSTANT算法。

2. 执行DDL操作

  • COPY算法
  • 创建临时表:首先,MySQL会创建一个与原始表结构相似的新临时表。
  • 数据拷贝:接着,原始表中的数据会被逐行拷贝到新的临时表中。这个过程可能需要一些时间,具体取决于表的大小和系统的性能。
  • 重命名与替换:数据拷贝完成后,临时表会被重命名为原始表的名字,从而替换掉原始表。在这个过程中,原始表会被加上写锁,以阻止对数据进行修改,确保数据的一致性。
  • INPLACE算法
  • 直接修改:与COPY算法不同,INPLACE算法直接在原始表上进行修改,无需创建临时表和拷贝数据。
  • 记录DML操作:在DDL操作执行期间,如果有DML操作(如INSERT、UPDATE、DELETE)尝试修改表,这些操作会被记录下来。
  • 应用DML更改:DDL操作完成后,之前记录的DML更改会被应用到表上,确保数据的完整性和一致性。
  • INSTANT算法
  • 元数据修改:对于某些简单的DDL操作(如修改表的默认字符集),INSTANT算法可以直接修改数据字典中的元数据,而无需对表数据进行任何更改。
  • 无锁操作:由于只修改元数据,因此这种算法可以在不锁定表的情况下完成,实现了真正的“瞬间”完成DDL操作。

3. 完成与清理

  • 释放资源:DDL操作完成后,系统会释放所有在操作过程中分配的资源,如临时表、内存等。
  • 更新统计信息:MySQL会更新与表相关的统计信息,以便优化器能够更好地制定查询计划。
  • 通知与日志记录:操作完成后,系统会生成相应的日志记录,以便在必要时进行恢复或审计。同时,也可能通过某种机制(如触发器)通知应用程序DDL操作的完成。

MySQL原生Online DDL通过不同的算法和策略来实现在线修改数据库结构的目标,从而提高了数据库的可用性和灵活性。这些工作原理确保了即使在执行DDL操作时,数据库仍然能够处理正常的DML操作,减少了停机时间和维护成本。

三、实现原理与优化

在线DDL的核心实现原理涉及几个关键环节,同时也有一些关键的优化策略:

  1. 构建临时表:为了不影响原表的正常读写,系统会创建一个具备新结构的临时表。这一步骤为后续的DDL操作提供了基础。
  2. 数据迁移与实时同步:旧表中的数据会被高效地迁移到临时表中,同时确保数据的实时同步。这种迁移策略旨在保障DDL过程中数据的完整性和一致性,避免数据丢失或损坏。
  3. 变更追踪与重播:利用日志机制,系统会追踪DDL执行期间旧表上的数据变更,并将这些变更实时重播到临时表中。这一优化确保了数据在DDL操作完成后的准确性。
  4. 无缝切换:当DDL操作完成且数据完全同步后,数据库引擎会在合适的时机将临时表提升为新表,从而实现无缝切换。此后,所有的读写操作都将基于新表进行。

四、使用场景与优势

MySQL原生Online DDL适用于多种场景,如添加或删除列、修改数据类型、添加或删除索引等。这些操作都可以在不中断服务的情况下完成,大大提高了数据库的灵活性和可用性。此外,由于Online DDL减少了停机时间,因此也降低了维护成本和数据丢失的风险。

五、使用约束与注意事项

尽管MySQL 5.7的在线DDL带来了诸多便利,但在实际使用中仍需注意以下几点:

  • 操作支持范围:并非所有类型的DDL操作都支持在线执行。某些特定操作可能仍需要锁定整张表,因此在执行前需确认操作类型。
  • 资源占用:DDL操作期间可能会显著增加系统资源的消耗,特别是在数据迁移和同步阶段。因此,在高负载环境下应谨慎规划并执行此类操作。
  • 测试与验证:为确保数据的完整性和业务的连续性,执行在线DDL之前应进行充分的测试和验证。这包括但不限于数据的备份、恢复以及一致性检查等步骤。

六、锁在Online DDL中的作用

在Online DDL过程中,锁主要用于确保数据的一致性。不同类型的锁对表的可访问性有不同的影响:

  1. 共享锁(S锁):允许多个事务读取同一资源,但不允许写入。在Online DDL中,这可能用于允许读取操作继续进行,同时阻止写入操作。
  2. 排他锁(X锁):阻止其他事务读取或写入资源。在DDL操作中,如果需要修改表的结构或数据,则可能需要使用排他锁。

ALGORITHM和LOCK选项

在MySQL中,你可以通过ALGORITHMLOCK关键字来控制DDL操作的行为。

ALGORITHM选项
  • INPLACE:这个选项指示MySQL直接在原表上进行修改,而不是创建一个新表。这通常可以减少锁的使用和时间,从而提高并发性。但是,并非所有的DDL操作都支持INPLACE算法。
  • COPY:这个选项告诉MySQL创建一个新表,将原表的数据复制到新表中,然后在新表上执行DDL操作。完成后,新表会替换原表。这个过程中,原表通常会被锁定,以防止数据不一致。COPY算法通常需要更多的时间和资源。
  • DEFAULT:如果不指定ALGORITHM选项,MySQL将选择默认的行为。这通常是尝试使用INPLACE算法,如果不可能,则回退到COPY算法。
LOCK选项
  • NONE:这个选项指示MySQL在执行DDL操作时不要对表加锁(如果可能的话)。但是,如果DDL操作需要保证数据的一致性,MySQL可能会忽略这个选项并加锁。
  • SHARED:允许读取操作继续进行,但阻止写入操作。
  • EXCLUSIVE:阻止读取和写入操作。这是最强的锁类型,用于确保DDL操作期间数据的一致性。

如何使用这些选项

当你想要执行一个DDL操作时,你可以通过添加ALGORITHMLOCK选项来控制操作的行为。例如:

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

这条命令尝试在原地(INPLACE)添加一个新列,并且尽量不使用锁(LOCK=NONE)。但是,需要注意的是,如果MySQL判断无法保证数据的一致性而不使用锁,它可能会忽略这些选项。

可以通过如下的SQL语句查看是否有事务和锁等信息。

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;
select * from information_schema.processlist;

注意事项

  • 不是所有的DDL操作都支持所有的ALGORITHM和LOCK组合。在执行DDL操作之前,最好查阅MySQL的官方文档以了解具体的支持情况。

  • 即使指定了LOCK=NONE,MySQL也可能在必要时自动加锁以确保数据的一致性。因此,这些选项应被视为指导性的,而不是强制性的。

更多online DDL原理,请移步 : MySQL Online DDL详解:从历史演进到原理及使用

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
39
分享
相关文章
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
20 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
MySQL原理简介—12.MySQL主从同步
本文介绍了四种为MySQL搭建主从复制架构的方法:异步复制、半同步复制、GTID复制和并行复制。异步复制通过配置主库和从库实现简单的主从架构,但存在数据丢失风险;半同步复制确保日志复制到从库后再提交事务,提高了数据安全性;GTID复制简化了配置过程,增强了复制的可靠性和管理性;并行复制通过多线程技术降低主从同步延迟,保证数据一致性。此外,还讨论了如何使用工具监控主从延迟及应对策略,如强制读主库以确保即时读取最新数据。
MySQL原理简介—12.MySQL主从同步
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL原理简介—8.MySQL并发事务处理
这段内容深入探讨了SQL语句执行原理、事务并发问题、MySQL事务隔离级别及其实现机制、锁机制以及数据库性能优化等多个方面。
MySQL原理简介—7.redo日志的底层原理
本文介绍了MySQL中redo日志和undo日志的主要内容: 1. redo日志的意义:确保事务提交后数据不丢失,通过记录修改操作并在系统宕机后重做日志恢复数据。 2. redo日志文件构成:记录表空间号、数据页号、偏移量及修改内容。 3. redo日志写入机制:redo日志先写入Redo Log Buffer,再批量刷入磁盘文件,减少随机写以提高性能。 4. Redo Log Buffer解析:描述Redo Log Buffer的内存结构及刷盘时机,如事务提交、Buffer过半或后台线程定时刷新。 5. undo日志原理:用于事务回滚,记录插入、删除和更新前的数据状态,确保事务可完整回滚。
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
MySQL进阶突击系列(08)年少不知BufferPool核心原理 | 大哥送来三条大金链子LRU、Flush、Free
本文深入探讨了MySQL中InnoDB存储引擎的buffer pool机制,包括其内存管理、数据页加载与淘汰策略。Buffer pool作为高并发读写的缓存池,默认大小为128MB,通过free链表、flush链表和LRU链表管理数据页的存取与淘汰。其中,改进型LRU链表采用冷热分离设计,确保预读机制不会影响缓存公平性。文章还介绍了缓存数据页的刷盘机制及参数配置,帮助读者理解buffer pool的运行原理,优化MySQL性能。

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等