我们将分类讨论在业务场景不同版本Mysql修改表结构添加索引是否会锁表 alter table add index 操作
数据量小
当数据量较小时,即使锁表也没有关系,其他的DML等待执行即可,业务中可以以一千万作为一个判定值,可以直接执行修改表结构操作,短暂性锁表无伤大雅
数据量大
当数据量大时,例如几千万、几亿数据,mysql建立索引默认需要锁住整个表,然后再建立索引,由于数据量巨大,从磁盘读取数据创建索引所需时间会比较长,此时业务是不能停的,因此不允许这样操作,会导致业务无法使用该数据库。
解决办法:使用inplace算法
在mysql 5.7版本及以后支持了设置algrothm=default/copy/inplace
在mysql8.0版本支持了instant方式,直接修改元数据完成,不修改表数据,建议使用8.0版本mysql服务器(亲测8.0版本一千万条数据下,创建索引,查询数据不走索引不会阻塞,走索引就会阻塞等待,千万行数据增加字段45毫秒执行完了,真快,看来没有直接更新表内数据,创建索引同时还允许插入数据,不阻塞)
- default 默认算法,会锁表
- copy 算法,允许DQL不支持DML,能读不能写,操作是创建一个新的空表,然后从旧表逐个读数据修改表结构写入新表,最后重命名
- inplace算法 支持DQL、DML,操作时能读写 文档里没有写具体是怎么操作的,后续找到再补充
algrothm参数为可选参数,默认使用default算法,若支持copy算法则默认用copy算法,若支持inplace算法则默认使用inplace算法,默认选取优先级 inplace>copy>default
本博客参考资料为下面链接
MySQL官方文档也给出了Online DDL的支持矩阵,列下常用的DDL操作,对比项主要包括是否重建表,允许并发的DML操作以及只修改元数据,表数据不受影响。