乐趣区

关于mysql:MySQL修改表结构到底会不会锁表

jiezi

〇、对于 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. 创立索引完结

In-place 形式执行 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_FORMAT，OPTIMIZE 优化表 等操作，这种形式被称为 rebuild 形式。
    • 如果没有批改行记录格局，仅批改表的元数据，则不须要重建表，比方 增加、删除、重命名二级索引 ， 设置、删除字段的默认值 ， 重命名字段 ， 重命名表 等操作，这种形式被称为 no-rebuild 形式。

更多详情可查阅官网文档：https://dev.mysql.com/doc/ref…

三、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 操作

退出移动版