MySQL修改表结构到底会不会锁表?

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: DDL(Data Definition Languages)语句:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter 等。DML(Data Manipulation Language)语句:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性。常用的语句关键字主要包括 insert、delete、udpate 和 select 等。(增删改查)DCL(Data Control Language)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了


〇、关于DDL、DML和DCL



DDL(Data Definition Languages)语句:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。

常用的语句关键字主要包括 create、drop、alter 等。

DML(Data Manipulation Language)语句:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性。

常用的语句关键字主要包括 insert、delete、udpate 和 select 等。(增删改查)

DCL(Data Control Language)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。

主要的语句关键字包括 grant、revoke 等。


一、DDL 实现方式



MySQL5.6 版本以前,执行 DDL 主要有两种方式:Copy 方式In-place 方式

Copy 方式执行DDL操作


  1. 创建与原表结构定义完全相同的临时表
  2. 为原表加MDL(meta data lock,元数据锁)锁,禁止对表中数据进行增删改,允许查询
  3. 在临时表上执行DDL语句
  4. 按照主键 ID 递增的顺序,把数据一行一行地从原表里读出来再插入到临时表中
  5. 升级原表上的锁,禁止对原表中数据进行读写操作
  6. 将原表删除,将临时表重命名为原表名,DDL操作完成


In-place 方式执行DDL操作


In-place 方式 又称为 Fast Index Creation 。与 Copy 方式相比,In-place方式不复制数据,因此大大加快了执行速度。但是这种方式仅支持对二级索引进行添加、删除操作,而且与Copy方式一样需要全程锁表。下面以添加索引为例,简单介绍In-place方式的实现流程:


  1. 创建新索引的数据字典
  2. 为原表加MDL(meta data lock,元数据锁)锁,禁止对表中数据进行增删改,允许查询
  3. 按照聚簇索引的顺序,查询数据,找到需要的索引列数据,排序后插入到新的索引页中
  4. 等待打开当前表的所有只读事务提交
  5. 创建索引结束


二、Online DDL



MySQL5.6 版本之后加入了 Online DDL 新特性,用于支持DDL执行期间DML语句的并行操作,提高数据库的吞吐量。

与 Copy 方式和 In-place 方式相比,Online 方式在执行DDL的时候可以对表中数据进行读写操作。


Online DDL可以有效改善DDL期间对数据库的影响:

  • Online DDL期间,查询和DML操作在多数情况下可以正常执行,对表格的锁时间也会大大减少,尽可能的保证数据库的可扩展性;
  • 允许 In-place 操作的 DDL,避免重建表格占用过多磁盘IO及CPU资源,减少对数据库的整体负荷,使得在DDL期间,能够维持数据库的高性能及高吞吐量;
  • 允许 In-place 操作的 DDL,比需要COPY到临时文件的操作要更少占用buffer pool,避免以往DDL过程中性能的临时下降,因为以前需要拷贝数据到临时表,这个过程会占用到buffer pool ,导致内存中的部分频繁访问的数据会被清理出去。


Online DDL 实现实质上也可以分为2种方式:Copy 方式和 In-place 方式:

  • 对于不支持Online DDL的 SQL,则采用 Copy 方式,比如删除主键修改列数据类型变更表字符集等。这些操作都会导致记录格式发生变化,无法通过简单的全量+增量的方式实现Online DDL。
  • 对于支持Online DDL的 SQL,则采用In-place方式,MySQL 内部以“是否修改行记录格式”为标准,又将In-place方式分为两类:
  • 如果修改了行记录格式,则需要重建表,比如 添加主键添加、删除字段修行格式ROW_FORMATOPTIMIZE优化表 等操作,这种方式被称为 rebuild方式
  • 如果没有修改行记录格式,仅修改表的元数据,则不需要重建表,比如 添加、删除、重命名二级索引设置、删除字段的默认值重命名字段重命名表 等操作,这种方式被称为 no-rebuild方式

image.png


三、Online DDL 实现流程



Online DDL 主要包括3个阶段,Prepare阶段,Execute阶段,Commit阶段。

下面将主要介绍Online DDL执行过程中三个阶段的流程。


Prepare 阶段

  • 持有 EXCLUSIVE-MDL 锁,禁止DML语句读写
  • 根据DDL类型,确定执行方式(Copy,Online-rebuild,Online-no-rebuild)
  • 创建新的 frm 和 ibd 临时文件(ibd临时文件仅rebuild类型需要)
  • 分配 row_log 空间,用来记录 DDL Execute 阶段产生的DML操作(仅rebuild类型需要)


Execute 阶段

  • 降级 EXCLUSIVE-MDL 锁,允许DML语句读写
  • 扫描原表主键以及二级索引的所有数据页,生成B+树,存储到临时文件中
  • 将 DDL Execute 阶段产生的DML操作记录到 row_log(仅rebuild类型需要)


Commit 阶段

  • 升级到 EXCLUSIVE-MDL 锁,禁止DML语句读写
  • 将 row_log 中记录的DML操作应用到临时文件,得到一个逻辑数据上与原表相同的数据文件(仅rebuild类型需要)
  • 重命名 frm 和 idb 临时文件,替换原表,将原表文件删除
  • 提交事务(刷事务的redo日志),变更完成


由上面的流程可知,Prepare阶段和Commit阶段都禁止读写,只有Execute允许读写,那为什么说Online DDL 方式在执行过程中可以对表中数据进行读写操作

其实是因为Prepare和Commit阶段相对于Execute阶段时间特别短,因此基本可以认为是全程允许读写的。

Prepare阶段和Commit阶段都禁止读写,主要是为了保证数据一致性。


四、Online DDL 的语法与可选参数



ALTER TABLE …. , ALGORITHM[=]{DEFAULT|INPLACE|COPY}, LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}

例句:

ALTER TABLE tablename DROP COLUMN age,ALGORITHM=INPLACE,LOCK=DEFAULT;


ALGORITHM 选项

COPY:使用 Copy 方式 执行DDL操作,DDL 执行过程中,不允许DML操作。

INPLACE:使用 In-place 方式 执行DDL操作,DDL 执行过程中,允许DML操作。

DEFAULT:默认选项,根据DDL的操作类型,自动选择DDL执行方式,优先选择 In-place 方式,不满足条件时选择 Copy 方式


LOCK 选项

EXCLUSIVE:对整个表添加排他锁(X锁),不允许DML操作

SHARED:对整个表添加共享锁(S锁),允许查询操作,但是不允许数据变更操作

NONE:不对表加锁,既允许查询操作,也支持数据变更操作,即允许所有的 DML 操作,该模式下并发最好

DEFAULT:默认选项,根据DDL的操作类型,最小程度的加锁,尽可能支持DML操作

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3月前
|
存储 SQL 关系型数据库
MySQL 给数据表增加一列,一定会锁表吗?
【8月更文挑战第8天】在数据库管理和开发中,给数据表增加一列是一个常见的操作。然而,当面试官提出“MySQL 给数据表增加一列,一定会锁表吗?”这一问题时,答案并非绝对。这主要取决于MySQL的版本、存储引擎以及具体的操作方式。
342 0
|
19天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
38 1
|
2月前
|
存储 关系型数据库 MySQL
深入解析MySQL数据存储机制:从表结构到物理存储
深入解析MySQL数据存储机制:从表结构到物理存储
162 1
|
2月前
|
SQL 关系型数据库 MySQL
springboot项目操作mysql出现锁表问题情况
springboot项目操作mysql出现锁表问题情况
44 2
|
1月前
|
SQL 存储 关系型数据库
MySQL新增字段/索引会不会锁表?
MySQL新增字段/索引会不会锁表?
101 0
|
3月前
|
SQL 存储 关系型数据库
mysql加索引真的会锁表吗?揭秘背后的技术细节与规避策略
【8月更文挑战第16天】在数据库管理中,添加索引能大幅提升查询效率。MySQL执行此操作时的锁定行为常引起关注。文章详细解析MySQL中索引添加时的锁定机制及其原理。不同存储引擎及SQL语句影响锁定策略:MyISAM需全表锁定;InnoDB提供更灵活选项,如使用`ALTER TABLE... LOCK=NONE`可在加索引时允许读写访问,尽管可能延长索引构建时间。自MySQL 5.6起,在线DDL技术可进一步减少锁定时间,通过`ALGORITHM=INPLACE`和`LOCK=NONE`实现近乎无锁的表结构变更。合理配置这些选项有助于最小化对业务的影响并保持数据库高效运行。
411 4
|
3月前
|
关系型数据库 MySQL 数据库
MySQL 复制A的表结构和数据到表B
在MySQL中复制表A至表B可通过不同方法实现。一种是先用`CREATE TABLE B LIKE A;`复制结构,再用`INSERT INTO B SELECT * FROM A;`填充数据。另一种更简便的方法是直接使用`CREATE TABLE B AS SELECT * FROM A;`一次性完成结构和数据的复制。还有一种高级方法是通过`SHOW CREATE TABLE A;`获取表A的创建语句,手动调整后创建表B,如有需要再用`INSERT INTO ... SELECT`复制数据。注意权限问题、跨数据库复制时需指定数据库名,以及大表复制时可能影响性能。
142 1
|
3月前
|
存储 关系型数据库 MySQL
Mysql表结构同步存储过程(适用于模版表)
Mysql表结构同步存储过程(适用于模版表)
47 0
|
4月前
|
SQL 关系型数据库 MySQL
Mysql:如何自定义导出表结构
通过以上方法,你可以灵活地自定义导出MySQL中的表结构,以满足不同的需求和场景。在进行操作的时候要注意权限问题以及路径问题,确保MySQL用户有权限写入指定的文件路径。在执行导出任务之前,还应确保你对数据库及其内容有足够的了解,以避免不必要的数据丢失或损坏。
71 1
|
4月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用问题之要将MySQL同步到Doris,并设置整库同步,只变更库名、表名和表结构都不变,该如何设置
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。