MySQL给数据表增加一列，一定会锁表吗？

如果看到这个标题直接回答会或者不会，哪说明你没有真正的认识和思考过这个问题！

注意：以下所有的内容都是基于InnoDB存储引擎

一、锁的概念

1.1 锁的分类

锁的分类可以从多个方面来划分。

graph LR
A(锁的分类)
B(按照操作类型)
C(按照操作粒度)
D(读锁 共享锁)
E(写锁 排它锁)
F(表锁)
G(行锁)


A ---> B
A ---> C
B ---> D
B ---> E
C ---> F
C ---> G

style B fill:#FFC0CB,stroke:#FFC0CB,stroke-width:2px
style C fill:#FFA07A,stroke:#FFA07A,stroke-width:2px
style D fill:#FFFFE0,stroke:#FFFFE0,stroke-width:2px
style E fill:#98FB98,stroke:#98FB98,stroke-width:2px
style F fill:#B2FFFF,stroke:#B2FFFF,stroke-width:2px
style G fill:#ADD8E6,stroke:#ADD8E6,stroke-width:2px

• 从对数据操作类型分

  • 读锁（共享锁） ：针对同一份数据，多个读操作可以同时进行而不会互相影响。

  • 写锁（排它锁） ：当前写操作没有完成前，它会阻断其他写锁和读锁。

• 从对数据操作粒度分

  • 表锁

  • 行锁

表锁也是是InnoDB存储引擎中的一种锁机制，用于控制对表的并发访问。InnoDB表锁包括共享锁（Shared Lock）和排他锁（Exclusive Lock），用于实现不同程度的并发读写操作。严格意义上来说，InnoDB存储引擎没有MyISAM存储引擎那种直接的完整的表级锁。

1.2 InnoDB表锁的特点和使用方式

graph LR
A(InnoDB表锁的特点和使用方式)

B(特点)
C(使用方式)

BD(自动隐式锁定)
BE(锁定粒度更细)
BF(多版本并发控制 MVCC)
BG(锁的持有时间尽可能短)
BH(受事务隔离级别和锁定模式影响)

CD(不需要手动指定)
CE(采用行级锁 支持并发读写操作)
CF(读写操作不会相互阻塞)
CG(减少阻塞和冲突的可能性)
CH(设置合适的隔离级别和锁定模式以平衡并发性能和数据一致性要求)

A ---> B
A ---> C

B ---> BD
B ---> BE
B ---> BF
B ---> BG
B ---> BH

C ---> CD
C ---> CE
C ---> CF
C ---> CG
C ---> CH

style B fill:#FFC0CB,stroke:#FFC0CB,stroke-width:2px
style C fill:#FFA07A,stroke:#FFA07A,stroke-width:2px

style BD fill:#FFFFE0,stroke:#FFFFE0,stroke-width:2px
style BE fill:#98FB98,stroke:#98FB98,stroke-width:2px
style BF fill:#B2FFFF,stroke:#B2FFFF,stroke-width:2px
style BG fill:#ADD8E6,stroke:#ADD8E6,stroke-width:2px
style BH fill:#E6E6FA,stroke:#E6E6FA,stroke-width:2px

style CD fill:#FFFFE0,stroke:#FFFFE0,stroke-width:2px
style CE fill:#98FB98,stroke:#98FB98,stroke-width:2px
style CF fill:#B2FFFF,stroke:#B2FFFF,stroke-width:2px
style CG fill:#ADD8E6,stroke:#ADD8E6,stroke-width:2px
style CH fill:#E6E6FA,stroke:#E6E6FA,stroke-width:2px

• InnoDB表锁是自动隐式锁定的，不需要手动指定。

• InnoDB存储引擎默认采用行级锁（Row-Level Locking）而不是表级锁。这意味着在InnoDB中，锁定的粒度更细，可以同时支持并发读写操作，减少了锁冲突和阻塞的可能性。

• InnoDB采用多版本并发控制（MVCC）机制，读取操作不会阻塞写入操作，写入操作也不会阻塞读取操作，提高了并发性能。

• InnoDB表锁在事务中使用，并且锁的持有时间尽可能短，以减少阻塞和冲突的可能性。

• InnoDB表锁的具体行为受到事务的隔离级别和锁定模式的影响。通过设置合适的隔离级别和锁定模式，可以平衡并发性能和数据一致性的要求。

二、给数据表增加一列，一定会锁表吗？

在 MySQL 中，使用 InnoDB 存储引擎给数据表增加一列时，并不一定会锁表。InnoDB 存储引擎提供了一些机制来减少对表的锁定，以提高并发性能。

2.1 MySQL 5.6 会不会锁表

• 从 MySQL 5.6 版本开始，InnoDB 引擎引入了在线数据定义语言（DDL）操作，其中包括对表结构的修改。这些在线 DDL 操作允许在不锁定整个表的情况下进行结构更改。具体来说，以下是可能的情况：

  • 对于增加非空列：在 InnoDB 中，增加非空列时，会执行一个快速的元数据操作，不会锁定整个表。这意味着在修改期间，其他会话可以继续读取和写入表数据。

  • 对于增加可为空列：在 InnoDB 中，增加可为空列时，也会执行一个快速的元数据操作，不会锁定整个表。其他会话可以继续读取和写入表数据，但在修改期间，可能会有一些短暂的行锁定。

注意：，尽管 InnoDB 存储引擎提供了较少的锁定，但在执行 ALTER TABLE 语句时仍可能会有一些性能影响。这可能是由于内部的元数据操作、数据重组或日志写入等引起的。

因此，在对大型表进行结构修改时，仍建议在低负载时执行，以最小化对应用程序的影响。

针对上面的问题,MySQL8.0做了更多的优化和升级

2.1 MySQL 8.0 会不会锁表

• MySQL 8.0 在处理大数据表增加字段的情况下进行了一些优化，进一步优化了减少对表的锁定时间和降低性能影响。在MySQL8.0中，还引入了Invisible Indexes、Instant DDL和In-Place Alter升级等新功能，可以进一步提高MySQL的性能和可维护性。

  • 原子 DDL：MySQL 8.0 引入了原子 DDL（Atomic DDL）操作，这意味着 ALTER TABLE 语句的执行过程中将会有更少的阻塞。在增加字段的情况下，原子 DDL 机制可以减少对表的锁定时间，并允许其他会话继续读取和写入数据。

  • 立即更新元数据：MySQL 8.0 在增加字段时立即更新表的元数据，而不需要等待整个操作完成。这样可以更快地完成 ALTER TABLE 操作，并减少对表的锁定时间。

  • InnoDB 引擎优化：MySQL 8.0 的 InnoDB 存储引擎针对大数据表的结构修改进行了一些优化。例如，对于增加非空字段，InnoDB 不再需要复制整个表的数据。相反，它会使用一种更轻量级的操作来添加新字段，从而减少锁定时间和资源消耗。

  • 增量元数据更新：MySQL 8.0 引入了增量元数据更新，这意味着在 ALTER TABLE 操作期间只需更新受影响的元数据信息，而不是整个表。这样可以减少锁定时间和操作的开销。

三、总结

总结上面的所有内容，实际就是单纯的增加一个字段，表结构修改和索引添加通常不会锁定整个表，在某些情况下，MySQL可能需要锁定整个表。同时数据量过大的时候，会出现一些性能问题，所以我们实际操作的过程中，一定要关注表的数据多小，最终的数据大小（这里要关注索引数据）。

希望本文对您有所帮助。如果有任何错误或建议，请随时指正和提出。

同时，如果您觉得这篇文章有价值，请考虑点赞和收藏。这将激励我进一步改进和创作更多有用的内容。

感谢您的支持和理解！