MySQL5.6-5.7-8.0在线DDL和DML如何避免阻塞说明

操作

是否需要重建表(影响性能)

MySQL5.6是否允许并发DML

MySQL5.7是否允许并发DML

MySQL8.0是否允许并发DML

备注

创建普通索引

不需要

允许

允许

允许

无。

创建全文索引

不需要

不允许

不允许

不允许

第一个全文索引需要通过Copy Table的方式创建；其后的全文索引可以通过Inplace方式创建。

删除索引

不需要

允许

允许

允许

仅修改表元数据metadata。

优化表

需要

允许

允许

允许

如果表上创建有全文索引，则不支持algorithm=inplace选项。

设置列默认值

不需要

允许

允许

允许

仅修改表元数据metadata。

修改自增列值

不需要

允许

允许

允许

仅修改表元数据metadata。

添加外键约束

不需要

允许

允许

允许

set foreign_key_checks=0; 来关闭 foreign_key_checks，避免拷贝表。

删除外键约束

不需要

允许

允许

允许

foreign_key_checks选项开启或者关闭都可以。

重命名列

不需要

允许

允许

允许

如果仅仅修改字段名称，而不要修改字段类型，是支持并发DML操作的。

添加列

需要

（mysql8.0不需要）

允许

允许

允许

在添加auto_increment自增列时，是不允许并发 DML 操作的。

尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。

删除列

需要

允许

允许

允许

尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。

修改各列顺序

需要

允许

允许

允许

尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。

修改Row_Format属性

需要

允许

允许

允许

尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。

设置列为空值Null

需要

允许

允许

允许

尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。

设置列不为空值NOT Null

需要

允许

允许

允许

该操作需要将SQL_MODE 参数设置为STRICT_ALL_TABLES或STRICT_TRANS_TABLES才能成功。如果列值中包含空值（NULL），则该DDL 操作会失败。

尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。

修改列的数据类型

需要

不允许

不允许

不允许

无。

扩展VARCHAR列大小

需要

（mysql8.0不需要）

不允许

允许

允许

无

添加主键

需要

允许

允许

允许

尽管支持Algorithm=INPLACE ，但因为数据实质上需要重新组织，因此操作的开销高昂。

如果涉及的列需要转换为NOT NULL，则不支持Algorithm=INPLACE。

删除主键并添加新主键

需要

允许

允许

允许

仅当在同一个Alter Table语句中（删除主键的DDL语句）添加新主键才支持Algorithm=INPLACE。

因为数据实质上需要重新组织，因此操作的开销高昂。

删除主键

需要

不允许

不允许

不允许

无。

Convert character set

需要

不允许

不允许

不允许

如果新的字符集编码不同，需要重建表。

重建表

alter table ... engine=innodb

需要

允许

允许

允许

如果表上有全文索引，则不支持Algorithm=Inplace选项。

修改表注释

不需要

允许

允许

允许

无。

对比维度

INSTANT

INPLACE

COPY TABLE

定义

仅更新数据字典（元数据），不涉及表数据的物理修改或移动，操作耗时极短（毫秒级）。

在原表位置直接修改表结构，可能重建索引或重组数据，但不复制全表数据。

创建临时表（目标结构），将原表数据全量复制到临时表，再替换原表。

实现原理

仅修改元数据，不接触实际数据。

原表直接操作，可能重建索引 / 重组数据。

全量复制数据到临时表，再替换原表。

适用场景

元数据变更（如添加 nullable 列、改默认值）。

结构变更（如改列类型、增删索引、重命名）。

复杂变更或旧版本兼容，需全量重建表。

性能

最优（毫秒级，几乎无影响）。

中等（依赖数据量，需部分 IO/CPU）。

最差（全量复制，耗时久、资源占用高）。

空间占用

几乎不占用额外空间。

少量临时空间（用于索引重建等）。

需约 1 倍原表空间（临时表存储）。

支持版本

MySQL 8.0.12+

MySQL 5.6+（随版本优化）

所有版本（传统模式）

锁与阻塞

仅短暂元数据锁，不阻塞读写。

可能加排他锁（DDL 期间锁表时间较短）。

锁表时间长（复制数据时阻塞写操作）。

数据影响

无数据移动或转换。

部分数据可能重组（如索引碎片整理）。

全量数据复制，可能涉及字符集转换等。

锁表场景

原因分析

示例 SQL

优化技巧

长事务阻塞

大事务包含大量 DML 操作且未及时提交，长时间持有行锁 / 表锁，阻塞其他事务。

SQL> START TRANSACTION;

SQL> UPDATE tablename SET status='processed' WHERE create_time < '2023-01-01'; // 未提交

1. 拆分事务：将大事务拆分为小批次（如每次处理 1000 条），分批提交。

2. 避免事务内耗时操作：如逻辑计算、外部接口调用等移至事务外。

无索引 / 索引失效导致全表锁

当 WHERE 条件未命中索引或索引失效时，InnoDB 会退化为表锁（全表扫描）。

-- 字段无索引，触发全表扫描

SQL> UPDATE tablename SET email='new@example.com' WHERE user_name='old_user'; // user_name 无索引

1. 添加合适索引：为高频查询 / 更新的字段创建索引（如 user_name）。

2. 检查索引使用：通过 EXPLAIN 确认是否走索引，避免隐式类型转换（如字段为 INT 却传入字符串）。

批量 DML 锁范围过大

单次操作涉及大量数据（如全表更新 / 删除），锁数量超过阈值，可能触发锁升级或长时间阻塞。

-- 全表更新（无过滤条件或条件命中全表）

SQL> UPDATE tablename SET stock=stock-1;

1. 分批操作：通过 LIMIT 分页处理，减少单次锁范围。

外键约束检查

父表与子表存在外键关联时，DML 操作会触发外键一致性检查，可能锁定关联表。

-- 删除父表数据时，子表外键约束导致锁表

SQL> DELETE FROM tablename WHERE id=1; // 子表 employees.department_id 外键关联 departments.id

1. 临时禁用外键：批量操作前禁用外键，完成后恢复（需谨慎，确保数据一致性）。

SET foreign_key_checks = 0;

执行批量操作

SET foreign_key_checks = 1;

2. 索引优化：为外键字段添加索引（InnoDB 外键字段自动加索引）。

锁升级（行锁→表锁）

InnoDB 行锁基于索引，若操作未命中索引或索引不唯一，行锁会退化为表锁。

-- 唯一索引字段使用模糊查询，导致索引失效SQL> UPDATE tablename SET amount=100 WHERE order_no LIKE '2023%'; -- order_no 为唯一索引，模糊查询无法命中<br>

1. 精确索引匹配：避免在索引字段使用模糊查询（如 LIKE '前缀%' 可命中索引，%前缀% 不可）。

2. 唯一索引完整性：确保唯一索引字段查询条件精确，避免范围查询。

死锁与锁竞争

多个事务互相等待对方释放锁，或高并发下锁竞争激烈，导致长时间阻塞。

-- 事务 A 与事务 B 交叉更新两行数据，引发死锁

TRANSACTION A:

SQL> UPDATE tablename SET balance=balance-100 WHERE id=1;

SQL> UPDATE tablename SET balance=balance+100 WHERE id=2;

TRANSACTION B:

SQL> UPDATE tablename SET balance=balance-100 WHERE id=2;

SQL> UPDATE tablename SET balance=balance+100 WHERE id=1;

1. 优化事务顺序：确保多个事务以相同顺序操作资源（如按 id 升序更新）。