Mysql索引 MetaData DDL锁表
问题背景
没找到关于加索引的时候导致锁表的解析,在百度上偶尔几篇此标题的文章也只是简单说一句kill掉进程就恢复
,至于为什么发生根本没有进行解析。
在工作中和学习中,哪怕可以暂时解决问题也得不到帮助。
排查思路
Online DDL
跟朋友讨论中,首先提到的是mysql可以在线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)的事务提交或回滚。
Metadata Lock
元数据(MetaData)指的是定义数据结构的数据。实际上,除了表本身之外的数据都是元数据。
举例:表的状态信息,属性和权限等,操作结果信息,某条指令影响的记录数,MySQL服务器信息
Metadata Lock有很多类型 可以参考
总结
所以综合以上资料的整理,如果DDL(如加索引或者字段)的时候,有存在活动中的事务(慢查询或者死锁等情况),DDL需要等待获取Metadata Lock,并且由于DDL获取的是写锁,
写锁优先级大于读锁,将会堵塞后续的其他新查询
,先处理DDL的写锁请求
造成DDL操作时候可能影响业务数据运行
我们在进行DDL之前要先检查是否有慢查询或者异常的事务进程 先kill掉,再可以通过ALGORITHM和LOCK子句来限制取锁情况
扫描二维码,分享此文章