MySQL加索引语句不加锁：ALGORITHM=INPLACE, LOCK=NONE

最新推荐文章于 2025-02-23 10:49:38 发布

飘然渡沧海

最新推荐文章于 2025-02-23 10:49:38 发布

阅读量4.2k

点赞数

分类专栏： mysql 文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/zhougubei/article/details/125521475

版权

mysql 专栏收录该内容

23 篇文章

订阅专栏

本文介绍了MySQL中在线DDL（Data Definition Language）操作的两种方式——INPLACE和COPY，以及它们对并发DML和查询的影响。重点讨论了如何在不锁定表的情况下添加索引，包括ALGORITHM=INPLACE和ALGORITHM=COPY的执行步骤和优缺点，并解释了LOCK选项的不同选择对并发读写操作的控制。此外，还提到了处理表元数据锁问题的方法和策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

线上无锁添加索引：加索引的语句不加锁

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE
更优秀的解决方案，在当前表加索引，步骤：
1.创建索引(二级索引)数据字典
2.加共享表锁，禁止DML，允许查询
3.读取聚簇索引，构造新的索引项，排序并插
入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束
 
ALGORITHM=COPY
通过临时表创建索引，需要多一倍存储，还有更多的IO，步骤：
1.新建带索引（主键索引）的临时表
2.锁原表，禁止DML，允许查询
3.将原表数据拷贝到临时表
4.禁止读写,进行rename，升级字典锁
5.完成创建索引操作
 
LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操
作不支持对表的继续写入，则DDL操作失败，对表修改无效
LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

然后在阿里云的帮助上找到了一个解决方法

In-Place？：对应 DDL语句的 Algorithm 选项，通过 inplace 方式执行 DDL。相比表拷贝方式，可以减少空间和 I/O 消耗。
Copies Table？：对应 DDL语句的 Algorithm 选项，通过表拷贝方式执行 DDL。DDL 执行期间会占用更大的磁盘空间和消耗更多的 I/O。
允许并发 DML？：对应 DDL语句的 Lock 选项，DDL 执行期间是否支持并发 DML 操作。
允许并发查询？：DDL 语句执行期间是否支持并发查询操作（通常都是支持的）。
MySQL官方文档请参考：Online DDL 概览
DDL 操作执行时需要修改表的元数据（metadata），有可能会遇到等待表元数据锁的情况（waiting for table metadata lock），该情况的处理方式请参考：RDS MySQL 表上 Metadata lock 的产生和处理
Inplace 和 Copy Table 是相反的 2 种处理方式；但即使 DDL 支持 Inplace 选项，某些操作在整个执行过程中也会部分涉及到表拷贝，比如上表中的添加列操作。