MySQL表的结构修改往往伴随着表级锁的问题。
特别是对于那些数据量较大的表，这会对业务系统的性能产生显著影响。
通过优化表结构修改的操作，开发者可以避免或最小化锁表时间，从而保证系统的正常运行。
表级锁介绍
表级锁指在执行某些操作时，为了保证数据的一致性，对整个表加锁。
具体来说：
当对表执行ALTER TABLE操作时，MySQL默认会对表进行加锁，阻止其他事务对该表的读写操作，直到ALTER TABLE操作完成。
这种锁表行为对于小表或非高并发场景下影响不大。
但当数据量巨大或业务高并发时，锁表问题可能会导致严重的性能瓶颈，甚至引发服务崩溃。
锁表的具体影响
当MySQL执行加字段操作时，锁表会导致以下问题：
查询等待：
所有查询该表的SQL语句将被阻塞，直到表锁释放。
写操作被阻止：
所有写入该表的操作（如INSERT、UPDATE、DELETE）将处于等待状态，直到锁释放。
系统响应变慢：
当锁表操作长时间未完成时，业务系统的整体性能将会显著下降，甚至引发宕机。
不过，在新版的MySQL 中，使用 InnoDB 存储引擎给数据表增加一列时，并不一定会锁表。
InnoDB 存储引擎提供了一些机制来减少对表的锁定，以提高并发性能。
在 MySQL 中，给数据表增加一列，是否会锁表取决于使用的存储引擎以及 MySQL 的版本。
MySQL5.6之前
之前版本的 MySQL 中，如果使用 ALTER TABLE 命令来增加一列，对于使用 InnoDB 存储引擎的表，默认情况下会锁表。
这意味着在操作执行期间，表将被锁定，其他读取和写入操作将被阻止，直到操作完成。
这种全表锁定行为会导致在大型表上执行 ALTER TABLE 操作时，产生长时间的锁等待和应用的停顿。
所以：MySQL5.6版本之前，直接修改表结构的过程中会锁表。
具体操作步骤如下：
首先创建新的临时表，表结构通过命令ALTAR TABLE新定义的结构。
然后把原表中数据导入到临时表。
删除原表。
最后把临时表重命名为原来的表名。
MySQL5.6和MySQL8.0版本中对锁表问题做了优化！
MySQL5.6
从 MySQL5.6 开始，InnoDB 引入了在线 DDL (Online DDL)操作，允许一些表修改操作在不锁定表的情况下进行。
增加一列是一个在线操作，可以使用 ALGORITHM=INPLACE 来避免全表锁定。
ALTER TABLE your_table ADD COLUMN new_column INT ALGORITHM=INPLACE, LOCK=NONE;
ALGORITHM=INPLACE 表明使用就地算法来进行修改，这是在线DDL操作的一部分。
明确指示 MySQL 尝试在原地修改表结构。
MySQL 将尝试尽可能在不重新创建整个表的情况下应用修改。
LOCK=NONE 表示尽量不锁表，最大程度减少对并发查询的影响。
允许其他会话对表进行读写操作。
但可能会导致数据不一致的情况。
这样执行效率会高很多。而且不会锁表。
不过也分为2种情况：
增加非空列：
会执行一个快速的元数据操作，不会锁定整个表：
在修改期间，其他会话可以继续读取和写入表数据。
增加可为空列：
会执行一个快速的元数据操作，不会锁定整个表：
其他会话可以继续读取和写入表数据，但在修改期间，可能会有一些短暂的行锁定。
注意：
尽管 InnoDB 存储引擎提供了较少的锁定，但在执行 ALTER TABLE 语句时仍可能会有一些性能影响。
由于内部的元数据操作、数据重组或日志写入等引起的。
因此，在对大型表进行结构修改时，仍建议在低负载时执行，以最小化对应用程序的影响。
MySQL8.0
MySQL8.0 引入了一些新的特性，使得大多数的 ALTER TABLE 操作可以在不锁定表的情况下完成。
简单说：提高了在线 DDL 操作的能力。
在 MySQL8.0 中默认情况下，简单的 ALTER TABLE 操作（如增加一列）通常不会锁定表。
要确认某个特定的 ALTER TABLE 操作是否会锁表，可以在操作执行前使用 EXPLAIN 语句：
EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;
该命令将显示操作的执行计划信息，包括是否会锁定表。
下面是MySQL8.0的一些具体优化！
原子DDL：
MySQL8.0 引入了原子 DDL（Atomic DDL）操作，这意味着 ALTER TABLE 语句的执行过程中将会有更少的阻塞。
在增加字段的情况下，原子 DDL 机制可以减少对表的锁定时间，并允许其他会话继续读取和写入数据。
立即更新元数据：
MySQL8.0 在增加字段时立即更新表的元数据，而不需要等待整个操作完成。
这样可以更快地完成 ALTER TABLE 操作，并减少对表的锁定时间。
InnoDB引擎优化：
MySQL8.0 的 InnoDB 存储引擎针对大数据表的结构修改进行了一些优化。
例如，对于增加非空字段，InnoDB 不再需要复制整个表的数据。
相反，它会使用一种更轻量级的操作来添加新字段，从而减少锁定时间和资源消耗。
增量元数据更新：
MySQL8.0 引入了增量元数据更新，这意味着在 ALTER TABLE 操作期间只需更新受影响的元数据信息，而不是整个表。
这样可以减少锁定时间和操作的开销。
Online DDL
在线DDL（Online DDL）是指在数据库运行状态下执行（DDL）操作。
例如创建、修改或删除表结构、索引等操作，而不会造成数据库的长时间锁定或无法使用。
传统的DDL操作通常需要对受影响的表进行排他锁定。
这可能导致其他会话无法对该表进行读写操作，从而影响了数据库的正常使用。
目前支持的主流算法有三种：
COPY
MySQL5.6之前非Online，都是执行这种算法。
INPLACE
MySQL5.6出现的。
INSTANT
MySQL8.0.12出现的（腾讯DBA团队贡献）。
基本原理
在DDL操作，执行时，不管何种算法，都会经历三个阶段：
准备阶段、执行阶段DDL、提交阶段。
不同之处是，在三个阶段中分别做了不同的优化处理。
具体实现细节可以见官方文档:
dev.mysql.com/doc/refman/…
dev.mysql.com/doc/refman/…
总结
MySQL5.6之后，实际单纯的增加一个字段，表结构修改和索引添加通常不会锁定整个表。
在某些情况下，MySQL可能需要锁定整个表。
同时数据量过大的时候，会出现一些性能问题。
所以实际操作的过程中，要关注表的数据多小，最终的数据大小（要关注索引数据）。
同时如果你的 MySQL 版本较旧或出于某些特殊原因不支持在线 DDL 操作。
需要特别注意在非高峰期执行 ALTER TABLE 操作，以尽量减少对业务的影响。
作者：程序员飞鱼
链接：https://juejin.cn