Mysql修改表结构、添加索引会锁表吗？（不同版本不同）

向右看齐 2024-04-03 11:41 65阅读 0赞

> 我们将分类讨论在业务场景不同版本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  
本博客参考资料为下面链接

> see [Mysql5.7 alter table][]  
> see [Mysql8.0 alter table][]

MySQL官方文档也给出了Online DDL的支持矩阵，列下常用的DDL操作，对比项主要包括是否重建表，允许并发的DML操作以及只修改元数据，表数据不受影响。

![在这里插入图片描述][b16a222fcb5443ef95b2863a0eb124fd.png]  
![在这里插入图片描述][2cde724d318a4467b80a772eb3f91668.png]

[Mysql5.7 alter table]: https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
[Mysql8.0 alter table]: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
[b16a222fcb5443ef95b2863a0eb124fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/410764d03a524b3cbe1c8e1ad142ac2e.png
[2cde724d318a4467b80a772eb3f91668.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/08c2b107af2e4a2faebae2dedeb9385b.png