MySQL表的结构修改往往伴随着表级锁的问题。
通过优化表结构修改的操作,开发者可以避免或最小化锁表时间,从而保证系统的正常运行。
表级锁介绍
表级锁指在执行某些操作时,为了保证数据的一致性,对整个表加锁。
具体来说:
这种锁表行为对于小表或非高并发场景下影响不大。
锁表的具体影响
当MySQL执行加字段操作时,锁表会导致以下问题:
所有查询该表的SQL语句将被阻塞,直到表锁释放。
写操作被阻止:
系统响应变慢:
不过,在新版的MySQL 中,使用 InnoDB 存储引擎给数据表增加一列时,并不一定会锁表。
InnoDB 存储引擎提供了一些机制来减少对表的锁定,以提高并发性能。
在 MySQL 中,给数据表增加一列,是否会锁表取决于使用的存储引擎以及 MySQL 的版本。
MySQL5.6之前
之前版本的 MySQL 中,如果使用 ALTER TABLE 命令来增加一列,对于使用 InnoDB 存储引擎的表,默认情况下会锁表。
这种全表锁定行为会导致在大型表上执行 ALTER TABLE 操作时,产生长时间的锁等待和应用的停顿。
所以:MySQL5.6版本之前,直接修改表结构的过程中会锁表。
具体操作步骤如下:
首先创建新的临时表,表结构通过命令ALTAR TABLE新定义的结构。
然后把原表中数据导入到临时表。
删除原表。
最后把临时表重命名为原来的表名。
MySQL5.6和MySQL8.0版本中对锁表问题做了优化!
从 MySQL5.6 开始,InnoDB 引入了在线 DDL (Online DDL)操作,允许一些表修改操作在不锁定表的情况下进行。
增加一列是一个在线操作,可以使用 ALGORITHM=INPLACE 来避免全表锁定。
your_table new_column INPLACE NONE
ALGORITHM=INPLACE 表明使用就地算法来进行修改,这是在线DDL操作的一部分。
LOCK=NONE 表示尽量不锁表,最大程度减少对并发查询的影响。
这样执行效率会高很多。而且不会锁表。
不过也分为2种情况:
增加非空列:
会执行一个快速的元数据操作,不会锁定整个表:
增加可为空列:
会执行一个快速的元数据操作,不会锁定整个表:
注意:
尽管 InnoDB 存储引擎提供了较少的锁定,但在执行 ALTER TABLE 语句时仍可能会有一些性能影响。
因此,在对大型表进行结构修改时,仍建议在低负载时执行,以最小化对应用程序的影响。
MySQL8.0 引入了一些新的特性,使得大多数的 ALTER TABLE 操作可以在不锁定表的情况下完成。
在 MySQL8.0 中默认情况下,简单的 ALTER TABLE 操作(如增加一列)通常不会锁定表。
要确认某个特定的 ALTER TABLE 操作是否会锁表,可以在操作执行前使用 EXPLAIN 语句:
your_table new_column
该命令将显示操作的执行计划信息,包括是否会锁定表。
下面是MySQL8.0的一些具体优化!
原子DDL:
MySQL8.0 引入了原子 DDL(Atomic DDL)操作,这意味着 ALTER TABLE 语句的执行过程中将会有更少的阻塞。
立即更新元数据:
MySQL8.0 在增加字段时立即更新表的元数据,而不需要等待整个操作完成。
InnoDB引擎优化:
MySQL8.0 的 InnoDB 存储引擎针对大数据表的结构修改进行了一些优化。
相反,它会使用一种更轻量级的操作来添加新字段,从而减少锁定时间和资源消耗。
增量元数据更新:
MySQL8.0 引入了增量元数据更新,这意味着在 ALTER TABLE 操作期间只需更新受影响的元数据信息,而不是整个表。
在线DDL(Online DDL)是指在数据库运行状态下执行(DDL)操作。
传统的DDL操作通常需要对受影响的表进行排他锁定。
目前支持的主流算法有三种:
基本原理
在DDL操作,执行时,不管何种算法,都会经历三个阶段:
不同之处是,在三个阶段中分别做了不同的优化处理。
具体实现细节可以见官方文档:
总结
MySQL5.6之后,实际单纯的增加一个字段,表结构修改和索引添加通常不会锁定整个表。
在某些情况下,MySQL可能需要锁定整个表。
所以实际操作的过程中,要关注表的数据多小,最终的数据大小(要关注索引数据)。
同时如果你的 MySQL 版本较旧或出于某些特殊原因不支持在线 DDL 操作。