Siam博客

mysql加索引的时候到底会不会锁表.深入解析

2022-09-30

Mysql索引 MetaData DDL锁表

问题背景

没找到关于加索引的时候导致锁表的解析,在百度上偶尔几篇此标题的文章也只是简单说一句kill掉进程就恢复,至于为什么发生根本没有进行解析。

在工作中和学习中,哪怕可以暂时解决问题也得不到帮助。

排查思路

Online DDL

跟朋友讨论中,首先提到的是mysql可以在线DDL官方文档

在线DDL

Mysql默认是可以支持在线DDL的,将会尽可能地少占用或者不占用锁来进行DDL(有限制条件)

符合在线DDL的场景下,在创建索引时,该表仍然可用于读写操作。

CREATE INDEX 语句仅在访问该表的所有事务完成之后才结束,以便索引的初始状态反映该表的最新数据内容。

其他俩个扩展相关的知识和语法

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...)
USING BTREE, ALGORITHM=INPLACE, LOCK=NONE;

  • ALGORITHM可选: INPLACE / COPY
  • LOCK可选: NONE SHARED 等加锁情况 -> 在 ALTER TABLE 语句上指定一个子句,如 LOCK = NONE (许可读和写)或 LOCK = SHARED (许可读)。如果请求的并发级别不可用,操作将立即停止。
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操作期间不允许对锁表进行任何操作

默认大部分情况下 mysql的在线DDL可以让我们加索引的时候不锁表,但是也有一些限制的场景,跟本次问题相关的限制情况是

在线DDL 操作完成之前,它必须等待在表上持有元数据锁(Metadata Lock)的事务提交或回滚。

在线DDL限制

Metadata Lock

元数据(MetaData)指的是定义数据结构的数据。实际上,除了表本身之外的数据都是元数据。

举例:表的状态信息,属性和权限等,操作结果信息,某条指令影响的记录数,MySQL服务器信息

Metadata Lock有很多类型 可以参考

总结

所以综合以上资料的整理,如果DDL(如加索引或者字段)的时候,有存在活动中的事务(慢查询或者死锁等情况),DDL需要等待获取Metadata Lock,并且由于DDL获取的是写锁,

写锁优先级大于读锁,将会堵塞后续的其他新查询,先处理DDL的写锁请求

造成DDL操作时候可能影响业务数据运行

我们在进行DDL之前要先检查是否有慢查询或者异常的事务进程 先kill掉,再可以通过ALGORITHM和LOCK子句来限制取锁情况

本文链接:
版权声明: 本文由 Siam原创发布,转载请遵循《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》许可协议授权

扫描二维码,分享此文章