MySQL新增字段/索引会不会锁表?

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL新增字段/索引会不会锁表?

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.6MySQL8.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.0InnoDB 存储引擎针对大数据表的结构修改进行了一些优化。

  • 例如,对于增加非空字段,InnoDB 不再需要复制整个表的数据。

相反,它会使用一种更轻量级的操作来添加新字段,从而减少锁定时间和资源消耗。

增量元数据更新:

MySQL8.0 引入了增量元数据更新,这意味着在 ALTER TABLE 操作期间只需更新受影响的元数据信息,而不是整个表。

  • 这样可以减少锁定时间和操作的开销。

Online DDL

在线DDLOnline 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 操作,以尽量减少对业务的影响。


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
17天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
2月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
24天前
|
分布式计算 关系型数据库 MySQL
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型 图像处理 光通信 分布式计算 算法语言 信息技术 计算机应用
41 8
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
171 1
|
1月前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
46 1
|
2月前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理?
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理?
74 1
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
76 0
|
1月前
|
关系型数据库 MySQL Java
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
28 0
|
2月前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述,深入理解和有效实施这些索引优化策略,是解锁MySQL 8.0数据库高性能查询的关键。
47 0
|
2月前
|
SQL 关系型数据库 MySQL
美团面试:mysql 索引失效?怎么解决? (重点知识,建议收藏,读10遍+)
本文详细解析了MySQL索引失效的多种场景及解决方法,包括破坏最左匹配原则、索引覆盖原则、前缀匹配原则、`ORDER BY`排序不当、`OR`关键字使用不当、索引列上有计算或函数、使用`NOT IN`和`NOT EXISTS`不当、列的比对等。通过实例演示和`EXPLAIN`命令分析,帮助读者深入理解索引失效的原因,并提供相应的优化建议。文章还推荐了《尼恩Java面试宝典》等资源,助力面试者提升技术水平,顺利通过面试。