Mysql修改表结构、添加索引会锁表吗？

前言

在MySQL5.7和MySQL8.0中，表结构修改和索引添加通常不会锁定整个表。但是，在某些情况下，MySQL可能需要锁定整个表。

MySQL5.7

在MySQL5.7中，对于表结构变更、索引添加，MySQL使用了一种称为“In-Place Alter”的技术。这种技术允许在不锁定表的情况下进行修改。In-Place Alter使用了一种称为“Fast Index Creation”的技术来优化索引添加，它可以在不拷贝原始表数据的情况下创建新索引。

**对于表结构修改，MySQL5.7使用了一种称为“Online DDL”的技术。Online DDL允许在不锁定表的情况下进行表结构修改。**当修改表结构时，MySQL会创建一个新表，并将原始表中的数据复制到新表中。在复制数据期间，原始表仍然可读和可写。完成数据复制后，MySQL会将新表重命名为原始表的名称，并删除原始表。此过程通常只需要几秒钟或几分钟，并且可以在应用程序运行期间执行。

虽然In-Place Alter和Online DDL可以减少对表的锁定，但它们并不适用于所有情况。例如，在进行某些类型的表结构修改时，MySQL可能需要锁定整个表。在这种情况下，In-Place Alter和Online DDL可能不适用。

此外，在MySQL5.7中，如果使用ALTER TABLE语句添加索引，将会锁定表。但是，如果使用CREATE INDEX语句添加索引，则不会锁定表。这是因为CREATE INDEX语句使用了“Concurrent Inserts”的技术，它允许在不锁定表的情况下进行索引添加。Concurrent Inserts在索引添加期间允许其他并发操作，从而减少了锁定的需求。

MySQL8.0

MySQL8.0在表结构修改和索引添加方面引入了一些新的功能。其中最重要的功能是“Invisible Indexes”和“In-Place Alter”升级。

Invisible Indexes

“Invisible Indexes”允许您创建不会影响查询执行计划的索引。这些索引对于实时修改和测试查询计划非常有用。此外，如果某个索引不再需要，您可以将其设置为“不可见”，而不是完全删除它。这可以减少表锁定的时间，因为在删除可见索引之前不需要锁定整个表。

In-Place Alter

“In-Place Alter”在MySQL8.0中进行了升级，可以处理更多类型的表结构修改。例如，现在可以在不锁定表的情况下添加、删除和更改列。如果使用In-Place Alter进行表结构修改，则MySQL将创建一个新的表，将新表中的数据与原始表中的数据进行同步，然后在删除原始表之前将新表重命名为原始表的名称。

小结

在MySQL8.0中，索引添加的行为与MySQL5.7相同。如果使用ALTER TABLE语句添加索引，则将锁定表。相反，如果使用CREATE INDEX语句添加索引，则不会锁定表。使用“Concurrent Inserts”技术添加索引可以避免表锁定，这使得索引添加变得更加高效。

**此外，在MySQL8.0中，还引入了“Instant DDL”功能。这种功能允许您在不锁定表的情况下执行某些DDL操作，例如添加、删除和更改列，以及更改表类型和重命名表。**这可以显著提高MySQL的可用性和可维护性。

转自：https://baijiahao.baidu.com/s?id=1760662405953282994&wfr=spider&for=pc

其他

在线 DDL：https://juejin.cn/post/7360583337512271882?searchId=202405101639163447C73DA4D09D29DFD7
后续补充变更列说明…
1、查看数据库版本
select version();
2、创建索引
CREATE INDEX index_name ON table_name(column1, column2, …);
3、变更列
ALTER TABLE your_table_name MODIFY COLUMN column_name new_column_definition;

注意

**1、确保你的MySQL版本支持Instant DDL，DDL即时Instant 功能自MySQL 8.0.12版本引入。**此外，变更操作通常是在数据库的表级别执行的，所以在执行这些操作时，请确保选择合适的时机，避免对业务造成影响。
3、变更列长度。Varchar对于小于等于255字节以内的长度可以使用一个byte 存储。大于255个字节的长度则需要使用2个byte存储。转自：https://blog.csdn.net/JSWANGCHANG/article/details/132027990

1）如果是255长度之内的扩展，或者255之外的扩展，则不锁表，采用in-place方式执行
2）从varchar长度从(0,255) 到 255+, 则会锁表，采用copy方式执行；
3）如果字段的缩小会锁表，采用copy方式执行；

3、Mysql5.7特性：Online DDL说明。转自：https://www.cnblogs.com/zmc60/p/14533123.html

总结

锁表的关键在于以下几点：
1、MySQL版本。每个版本所支持的算法是不一样的。
2、当前表的容量（5.6/5.7）。
3、8.0版本增加列等少量 DDL 类型需指定算法。
4、变更列字段长度。