MySQL添加索引时会锁表吗?

目录

  • 简介
  • Online DDL概念
  • Online DDL用法
  • 总结

简介

在MySQL5.5以及之前的版本,通常更改数据表结构操作(DDL)会阻塞对表数据的增删改操作(DML)。
MySQL5.6提供Online DDL之后可支持DDL与DML操作同时执行,降低了DDL期间对业务延迟带来的影响。

下面进行案例演示。我们准备了一个分数表,有600万的测试数据,接下来我们验证一下我们日常使用的ALTER语句(不提交),看看是否会全程锁表

#事务A 添加索引 不提交
begin;
ALTER TABLE scores ADD index idx_student_id (student_id);
# commit;
#事务B 查询数据与修改数据
begin;
select id from scores where id= 1;
commit;begin;
update scores set course_name = '张三' where id = 1;
commit;

发现查询事务和修改事务都是可以正常返回的,发现这条DDL语句不会全程锁表,执行过程中真的不会锁表吗?我们再看一种情况,首先删除索引:

ALTER TABLE scores drop index idx_student_id;

然后开启添加索引事务(不提交),然后再开启查询事务,并且也不提交,这个时候通过show processlist命令查看mysql的执行信息,观察加索引时ddl语句的执行情况。

show processlist;

观察到ddl语句正在执行中:

在这里插入图片描述

再show一下,发现ddl语句变成了等待我们的元数据锁释放中,即查询语句持有了我们的一个元数据锁。这个时候我们提交查询事务,可以发现索引就添加成功了。说明我们这条sql本质上还是有个加锁的过程。

在这里插入图片描述

再看一种情况,也先删除索引,然后开启查询事务不提交,去持有元数据锁,然后再执行添加索引,然后show一下执行信息,发现DDL语句已经在等待元数据锁释放了。然后我们提交一下查询事务,再show一下,发现DDL语句正在往下执行,这个时候我们再开启一个事务进行修改(也不提交),多show几下直到DDL执行完毕,发现我们开启的修改事务阻塞了我们的DDL语句继续执行。提交修改事务,发现索引就添加成功了。那么也就是说我们这个DDL语句实际上有两个加锁的过程,并且在这个加锁的时候如果说我们的元数据锁被其他的事务给占有了,那么我们这个DDL语句就会被阻塞,第二次也是一样的。

MySQL 5.6或更高版本上进行表结构修改操作(如添加索引),MySQL默认情况下会尝试使用Online DDL来最小化操作对读写操作的影响,MySQL会选择默认的最佳方式进行操作。



Online DDL概念

概念:在不中断现有数据读写操作的情况下,自动执行 DDL语句(例如创建、修改、删除表等)的机制。Online DDL 可以在MySQL进行表空间或数据文件的变化时,自动执行 DDL语句,从而避免了传统方式中,执行 DDL 语句时对数据库读写操作的干扰和中断。

执行过程:Online ddl 执行大致可分为三个阶段:初始化阶段、执行阶段和提交表定义阶段:

  • 初始化阶段:
    • 评估存储引擎能力与DDL语句
    • 评估ALGORITHM 和 LOCK
    • 创建可升级的MDL读锁(元数据读锁)
  • 执行阶段:
    • 此阶段分为两个步骤准备和执行DDL语句
    • 此阶段是否需要MDL写锁取决于初始化阶段评估的因素。如果需要MDL写锁的话,仅在准备过程会短暂的使用MDL写锁,然后降级为MDL读锁
    • DDL执行过程(最耗时)
  • 提交表定义阶段:
    • 此阶段会将MDL读锁升级到MDL写锁,此阶段一般较快,因此独占锁的时间也较短
    • 用新的表定义替换旧的表定义,释放MDL锁

Online DDL用法

区别与我们日常使用的DLL语句,多了两个参数

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=INPLACE, LOCK=NONE;ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=COPY, LOCK=EXCLUSIVE;

ALGORITHM有三个可选项

ALGORITHM=DEFAULT:默认算法,使用最高效的算法


ALGORITHM=INPLACE:解决全程锁表的一个方式,在原表上进行更改,不需要生成临时表,不需要进行数据copy的过程。
添加索引步骤:
1.创建索引(二级索引)数据字典
2.加共享表锁,禁止DML,允许查询
3.读取聚簇索引,构造新的索引项,排序并插入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束


ALGORITHM=COPY:最原始的方式,通过临时表创建索引,需要多一倍存储,还有更多的I0(类似5.6版本之前的处理过程)添加索引步骤:
1.新建带索引(主键索引)的临时表心
2.锁原表,禁止DML,允许查询
3.将原表数据拷贝到临时表
4.禁止读写,进行rename,升级字典锁
5.完成创建索引操作

LOCK有四种

LOCK=DEFAULT:默认方式,MySQL自行判断使用哪种LOCK模式,尽量不锁表
LOCK=NONE:无锁:允许Online DDL期间进行并发读写操作。通常与INPLACE搭配使用。如果Online DDL操作不支持对表的继续写入,则DDL操作失败,对表修改无效
LOCK=SHARED:共享锁:Online DDL操作期间堵塞写入,不影响读取
LOCK=EXCLUSIVE:排它锁:Online DDL操作期间不允许对锁表进行任何操作

接下来我们使用这两种执行方式来看一下是不是会全程锁表。先是第一种的INPLACE:

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=INPLACE, LOCK=NONE;

先删除刚刚的索引,然后开启一个事务添加索引(不提交),执行一下查询事务(提交),是可以正常查询的。再开启一个修改事务(提交),是可以正常修改的。即,INPLACE方式是不会全程锁表的。那么它在执行的过程中会加锁吗?

还是删除索引,然后开启一个事务添加索引(不提交),执行一下查询事务(不提交),此时show一下执行信息,发现DDL语句正在执行ALTER语句,再show一下,发现DDL语句已经被阻塞了,因为它在等待元数据锁释放,也就是说,DDL语句实际上还是会有个加锁的过程。提交一下查询事务,发现索引就添加成功了。

然后我们再次验证一种场景,还是删除索引,然后先开启一个查询事务(不提交)先持有元数据锁,然后再开启事务添加索引(不提交),这个时候show一下执行信息,发现DDL语句正在等待元数据锁释放,提交一下查询事务,show一下发现ddl语句正在往下执行,这个时候再开启一个修改事务(不提交),多show几下直到DDL执行完毕,发现我们的DDL语句也被这个修改事务阻塞了,因为它正在等待元数据锁的释放,这个时候我们再开一个修改事务(提交)、查询事务(提交),show一下发现他们都被阻塞住。也就是说online ddl语句被阻塞后它就会进一步的将后续过来的DML事务全部阻塞住,将修改事务提交,索引就添加成功了。

所以日常使用的DDL语句的INPLACE方式执行过程有两次加锁,加锁过程中如果有其他事务持有了元数据锁,DDL语句就会被阻塞,后续来的DML操作都会被阻塞住。

再看一下COPY方式:

ALTER TABLE scores ADD index idx_student_id (student_id) , ALGORITHM=COPY, LOCK=EXCLUSIVE;

还是删除索引,然后开启一个事务添加索引(不提交),然后执行查询事务(提交),修改事务(提交),执行信息发现两个DML语句都被阻塞。即COPY方式是全程锁表的,它不允许DDL和DML的并发。

总结

从宏观上看,Online DDL的事务相当于会和其他事务并行执行,只不过Online DDL会在表空闲时进行执行,所以Online DDL不会阻塞其他操作,在Online DDL执行过程会两次获取MDL锁(1.申请MDL写锁 2. 降级成MDL读锁 3.执行DDL --耗时 4. 升级MDL写锁 5. 释放MDL锁),并且需要等待已经持有MDL锁的并发事务提交或回滚后才能继续执行,在实际执行时需注意以下几点:

  • 进行DDL操作时尽量在业务低峰期进行操作。尽量的降低我们online ddll的阻塞时间,进而减少整个表的死锁
  • 在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。
  • 设置超时时间lock_wait_timeout,避免长时间的metedata锁等待。
set global lock_wait_timeout = 60;
# 单位是s 默认好像是一年show variables like '%timeout%';

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/381842.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

算法通关:005对数器

就是你有优解,但是不知道对不对,或者你遇到了题,但是没有在线网站能跑,无法检查你的思路是否正确。 写一个随机生成符合输入要求的方法。 此时用暴力解法写一个,因为答案肯定是对的,再写一个优解方法。将两…

斐波那契数列的多种解法 C++实现,绘图部分用Python实现

斐波那契数列的多种解法 C实现,绘图部分用Python实现 flyfish 斐波那契数列(Fibonacci sequence)是一个经典的数列,定义如下: { 0 if n 0 1 if n 1 F ( n − 1 ) F ( n − 2 ) if n > 1 \begin{cases} 0 &…

HackTheBox--Knife

Knife 测试过程 1 信息收集 端口扫描 80端口测试 echo "10.129.63.56 knife.htb" | sudo tee -a /etc/hosts网站是纯静态的,无任何交互功能,检查网页源代码也未发现任何可利用的文件。 检查页面请求时,请求与响应内容&#xff0…

高频面试题-CSS

BFC 介绍下BFC (块级格式化上下文) 1>什么是BFC BFC即块级格式化上下文,是CSS可视化渲染的一部分, 它是一块独立的渲染区域,只有属于同一个BFC的元素才会互相影响,且不会影响其它外部元素。 2>如何创建BFC 根元素,即HTM…

RabbitMQ的学习和模拟实现|sqlite轻量级数据库的介绍和简单使用

SQLite3 项目仓库:https://github.com/ffengc/HareMQ SQLite3 什么是SQLite为什么需要用SQLite官方文档封装Helper进行一些实验 什么是SQLite SQLite是一个进程内的轻量级数据库,它实现了自给自足的、无服务器的、零配置的、事务性的 SQL数据库引擎…

lua 游戏架构 之 LoaderWallet 异步加载

定义了一个名为LoaderWallet class,用于管理资源加载器(Loader)。这个类封装了资源加载的功能,包括异步加载,以及资源的释放和状态查询。下面是对代码的详细解释: ### 类定义和初始化 这里定义了一个名为…

Inconsistent Query Results Based on Output Fields Selection in Milvus Dashboard

题意:在Milvus仪表盘中基于输出字段选择的不一致查询结果 问题背景: Im experiencing an issue with the Milvus dashboard where the search results change based on the selected output fields. Im working on a RAG project using text data conv…

AndroidStudio 编辑xml布局文件卡死问题解决

之前项目编写的都是正常,升级AndroidStudio后编辑布局文件就卡死,还以为是AndroidStudio文件。 其实不然,我给整个项目增加了版权声明。所以全部跟新后,布局文件也增加了版权声明。估计AndroidStudio在 解析布局文件时候因为有版…

推荐丨SSL证书是什么?该怎么申请,需要准备哪些材料?

SSL证书是什么? SSL证书(Secure Sockets Layer Certificate),又称为数字证书,是一种用于在互联网上验证网站身份和加密通信的技术。它遵守SSL协议,由受信任的数字证书颁发机构(CA)在…

在 CI/CD Pipeline 中实施持续测试的最佳实践!

随着软件开发周期的不断加快,持续集成(CI)和持续交付/部署(CD)已经成为现代软件开发的重要组成部分。在这一过程中,持续测试的实施对于确保代码质量、提高发布效率至关重要。本文将详细介绍在CI/CD流水线中…

STM32高级运动控制系统教程

目录 引言环境准备高级运动控制系统基础代码实现:实现高级运动控制系统 4.1 传感器数据采集模块 4.2 数据处理与运动控制模块 4.3 通信与网络系统实现 4.4 用户界面与数据可视化应用场景:运动控制与优化问题解决方案与优化收尾与总结 1. 引言 高级运动…

深入理解Linux网络(五):TCP接收唤醒

深入理解Linux网络(五):TCP接收唤醒 TCP接收唤醒由软中断提供服务。 软中断(也就是 Linux ⾥的 ksoftirqd 进程)⾥收到数据包以后,发现是 tcp 的包的话就会执⾏到 tcp_v4_rcv 函数。接着如果是 ESTABLISH…

mysql JSON特性优化

有朋友问到,mysql如果要根据json中的某个属性过滤,数据量大的话,性能很差,要如何提高性能? 为什么要用json串? 由于一些特定场景,mysql需要用到json串,例如文档,不同的…

【LabVIEW作业篇 - 5】:水仙花数、数组与for循环的连接

文章目录 水仙花数数组与for循环的连接 水仙花数 水仙花数,是指一个3位数,它的每个位上的数字的3次幂之和等于它本身。如371 3^3 7^3 1^3,则371是一个水仙花数。 思路:水仙花数是一个三位数,通过使用for循环&#xf…

RabbitMQ的学习和模拟实现|muduo库的介绍和使用

muduo库 项目仓库:https://github.com/ffengc/HareMQ muduo库 muduo库是什么快速上手搭建服务端快速上手搭建客户端上面搭建的服务端-客户端通信还有什么问题?muduo库中的protobuf基于muduo库中的protobuf协议实现一个服务器 muduo库是什么 Muduo由陈硕大佬开…

ReadAgent,一款具有要点记忆的人工智能阅读代理

人工智能咨询培训老师叶梓 转载标明出处 现有的大模型(LLMs)在处理长文本时受限于固定的最大上下文长度,并且当输入文本越来越长时,性能往往会下降,即使在没有超出明确上下文窗口的情况下,LLMs 的性能也会随…

pytorch 笔记:torch.optim.Adam

torch.optim.Adam 是一个实现 Adam 优化算法的类。Adam 是一个常用的梯度下降优化方法,特别适合处理大规模数据集和参数的深度学习模型 torch.optim.Adam(params, lr0.001, betas(0.9, 0.999), eps1e-08, weight_decay0, amsgradFalse, *, foreachNone, maximizeFa…

OpenAI从GPT-4V到GPT-4O,再到GPT-4OMini简介

OpenAI从GPT-4V到GPT-4O,再到GPT-4OMini简介 一、引言 在人工智能领域,OpenAI的GPT系列模型一直是自然语言处理的标杆。随着技术的不断进步,OpenAI推出了多个版本的GPT模型,包括视觉增强的GPT-4V(GPT-4 with Vision&…

HarmonyOS应用开发者高级认证,Next版本发布后最新题库 - 单选题序号3

基础认证题库请移步:HarmonyOS应用开发者基础认证题库 注:有读者反馈,题库的代码块比较多,打开文章时会卡死。所以笔者将题库拆分,单选题20个为一组,多选题10个为一组,题库目录如下,…

hive3 hql脚本传递参数

在数仓的构建过程中,需要配置hive的调度任务,这时就需要对hive hql脚本进行封装,将参数提取出来,作为变量进行配置,比如日期、类型等。 hive3版本,hive -f 在执行sql脚本文件的时候是可以传递参数。 具体…