文章首发到公众号:月伴飞鱼
文章内容收录到个人网站,方便阅读:hardyfish.top/
文章内容收录到个人网站,方便阅读:hardyfish.top/
文章内容收录到个人网站,方便阅读:hardyfish.top/
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
INSTANT
MySQL8.0.12
出现的(腾讯DBA团队贡献)。
基本原理
在
DDL
操作,执行时,不管何种算法,都会经历三个阶段:
- 准备阶段、执行阶段
DDL
、提交阶段。不同之处是,在三个阶段中分别做了不同的优化处理。
具体实现细节可以见官方文档:
总结
MySQL5.6
之后,实际单纯的增加一个字段,表结构修改和索引添加通常不会锁定整个表。在某些情况下,
MySQL
可能需要锁定整个表。
- 同时数据量过大的时候,会出现一些性能问题。
所以实际操作的过程中,要关注表的数据多小,最终的数据大小(要关注索引数据)。
同时如果你的
MySQL
版本较旧或出于某些特殊原因不支持在线DDL
操作。
- 需要特别注意在非高峰期执行
ALTER TABLE
操作,以尽量减少对业务的影响。
1、本站所有资源均从互联网上收集整理而来,仅供学习交流之用,因此不包含技术服务请大家谅解!
2、本站不提供任何实质性的付费和支付资源,所有需要积分下载的资源均为网站运营赞助费用或者线下劳务费用!
3、本站所有资源仅用于学习及研究使用,您必须在下载后的24小时内删除所下载资源,切勿用于商业用途,否则由此引发的法律纠纷及连带责任本站和发布者概不承担!
4、本站站内提供的所有可下载资源,本站保证未做任何负面改动(不包含修复bug和完善功能等正面优化或二次开发),但本站不保证资源的准确性、安全性和完整性,用户下载后自行斟酌,我们以交流学习为目的,并不是所有的源码都100%无错或无bug!如有链接无法下载、失效或广告,请联系客服处理!
5、本站资源除标明原创外均来自网络整理,版权归原作者或本站特约原创作者所有,如侵犯到您的合法权益,请立即告知本站,本站将及时予与删除并致以最深的歉意!
6、如果您也有好的资源或教程,您可以投稿发布,成功分享后有站币奖励和额外收入!
7、如果您喜欢该资源,请支持官方正版资源,以得到更好的正版服务!
8、请您认真阅读上述内容,注册本站用户或下载本站资源即您同意上述内容!
原文链接:https://www.shuli.cc/?p=21066,转载请注明出处。
评论0