MySQL——关于删除/purge/删除加锁/删除大量数据/truncatedelete

2023-10-14 15:30

本文主要是介绍MySQL——关于删除/purge/删除加锁/删除大量数据/truncatedelete,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

本篇介绍MySQL执行删除命令时的一些机制,包括:

  • MySQL如何删除一行数据?

  • 解释为什么删除数据后表文件大小不变?

  • purge线程

  • 为什么建议逻辑删除数据而非物理删除?为什么建议自增主键?

  • 为什么建议删除数据的语句条件上加索引?

  • 如何删除大量数据?truncate与delete/drop的区别?

MySQL如何删除一行数据?

InnoDB里的数据都是用B+树的结构组织的;

如图,要删掉R4这个记录,InnoDB引擎只会把R4这个记录标记为"删除";如果之后要再插入一个ID在300和600之间的记录时,可能会复用这个位置;但是,磁盘文件的大小并不会缩小

InnoDB的数据是按页存储的,如果删掉了一个数据页上的所有记录,那么整个页都可以被复用;

数据"空洞"导致页合并

  • 如果相邻的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用;

  • 或者一次删除了整个页上的所有数据,这一整个数据页也会被标记为可复用;

为什么删除数据后表文件大小不变?

无论是删除记录还是整个页数据删除,磁盘上的文件不会变小,因为delete只是标记为已删除,而不是真正的物理删除,即表空间数据回收;

delete命令其实只是把记录的位置或者数据页标记为了mark del,在后台purge执行回收后,被删除数据部分对应的磁盘空间标记为"可用",可以被后续写入操作使用,但磁盘文件的大小是不会变的;也就是说,通过delete命令是不能回收表物理空间的;

purge线程

本节介绍跟删除相关的purge线程相关的知识;

为什么MySQL InnoDB需要Purge操作?

明确这个问题的答案,首先还得从InnoDB的多版本并发控制(MVCC)开始;

"快照"是InnoDB在实现MVCC时用到的一致性读视图,用于支持RC(Read Committed,读提交)和RR(Repeatable Read,可重复读)隔离级别的实现;它没有物理结构,作用是事务执行期间用来定义“我能看到什么数据”;

InnoDB 利用了“所有数据都有多个版本”的这个特性,实现了“秒级创建快照”的能力;

但是这里"快照"并不是说把此刻的整库数据拷贝一份,而是类似git,对于每一行数据,仅记录其在某一次事务中的增量更新,因此大部分的数据都是没有更新的,从而不会像"全量物理拷贝"那样占用巨大的内存;

也就是说,对于数据表中的一行记录,在数据库的不断更新下,可能存在多个数据版本 (row),而每个数据版本有自己的 row trx_id;如下图所示,就是一条记录被多个事务连续更新后的过程;

由图可知:语句所在的事务ID与语句更新结果的数据版本的row trx_id一一对应;

实际上,历史版本数据的值并不会物理存在,而是在每次需要的时候根据当前版本和 undo log 计算出来的;

在事务不断的执行过程中,undo log占用的空间会不断地扩张;

另外,对于更新和删除操作,InnoDB并不是真正的删除原来的记录,而是设置原记录的delete mark标志位为1,也就是说原数据依然存于数据页上;那么可以标记为mark delete的数据都有哪些类型呢?包括主键记录、二级索引记录

因此为了清除数据Page和Undo Log膨胀的问题,需要引入purge机制进行回收

purge流程简介

purge的主要任务是将数据库中已经mark del的数据删除,另外也会批量回收undo pages;而数据库的数据页很多,要清除被删除的数据,不可能遍历所有的数据页

由于所有的变更都有undo log, 因此,执行purge时,MySQL从undo作为切入点,在清理过期的undo的同时,也将数据页中的被删除的记录一并清除

一个关于删除数据后磁盘空间再次利用的实验

1.初始化表t1; session1插入一定数量的数据;在 session2中观察到表的t1.ibd大小在增加;

2. session1删除t1所有记录;执行后稍等等片刻(等purge线程自动清理数据、master线程将数据从缓存落盘),这时候在 session2中观察到t1.ibd文件体积一点也没有减少;

3. session1重新执行少量的插入操作;在 session2中观察到t1.ibd文件体积并没有再次增长;

原因:purge线程将上述实验中被删除数据部分对应的磁盘空间标记为可用,可以被后续写入操作使用,这样就不用再次分配磁盘空间了;

本章参考:

阿里云-MySQL Innodb Purge简介

MySQL purge线程 相关参数

为什么建议逻辑删除数据而非物理删除?为什么建议自增主键?

逻辑删除的一个天然的好处是方便数据的恢复和归档查询;此外,另一个是避免删完数据导致的页利用率降低,低于阈值时会产生的相邻叶子节点的页合并;

使用自增主键的好处就是避免频繁的"页分裂";结合B+数的结构,叶子节点是有序的,如果数据是按照索引递增顺序插入的,页写满申请新页时,是不移动原有页面的任何记录的,因此索引是紧凑的;注意,这里的"按照索引递增顺序插入"并不是说一定要自然数值连续,而是大小关系连续即可,如 1 2 4 7 9;这也是为什么推荐使用自增主键的原因;

如图,传统B+树页面分裂是按照原页面中50%的数据量进行分裂,随机插入就可能导致原先的数据页放不下了,造成索引的数据页分裂,从而导致分裂后的数据页产生了"空洞";

出现数据空洞时,一般使用重建表DDL语句来优化表存储空间;

为什么建议删除数据的语句条件上加索引?

从性能和加锁来看delete操作;

一般来说,DELETE的加锁和SELECT FOR UPDATE 或 UPDATE 并没有太大的差异;

因为,在MySQL数据库中,执行DELETE语句其实并没有直接删除记录,而是在记录上打上一个删除标记,然后通过后台的一个叫做purge的线程来清理;从这一点来看,DELETE和UPDATE确实是非常相像;事实上,DELETE和UPDATE的加锁也几乎是一样的;

前面有文章分析过全表扫描的加锁方式和更新记录时加锁的规则,可知:如果更新条件可以走索引,则间隙锁会加在条件所在的索引位置的前后间隙;如果查询条件没走索引走全表扫描,则对全表所有行之间加间隙锁

MySQL delete语句的加锁分析总结可参考:

  • 《MySQL DELETE 删除语句加锁分析》

  • 《mysql delete语句 MySQL死锁系列-常见加锁场景分析》

因此建议删除语句的条件尽量走索引查询,或者先查出这条记录的主键ID,再根据主键ID(唯一索引)条件删除;

如何删除大量数据?

根据where条件删除数据前,会先扫描数据检查是否符合where条件,该阶段会对扫描中所有数据行及行的间隙加锁;若表的数据量大且delete操作无法有效利用索引减少扫描数据量,该步骤对于数据库带来的锁争用、CPU/IO资源的消耗都是巨大的

单次删除大量数据属于大事务,由于一次修改的表记录太多,无论是产生的binlog日志的数量,还是加锁的范围,都会比较大;较大的加锁范围很容易出现事务执行超时的情况,或阻塞其他更新操作

此外,大事务的执行时间长,会导致主从延迟不断增加;在一些读写分离(写主库读从库)的场景下,导致读库读不到最新的数据引发业务问题;

下面介绍删除大量数据时,常用的方案及建议:

方案:分批次删除

减小单次删除的数据的数量,拆成分批次执行,减小批次执行间隔,控制执行的速度;此方案一定程度缓解主从延迟不断增加的问题,减小事务涉及的记录行数,减小事务的执行时间,避免一次锁住太多数据阻塞后序SQL命令;

方案:新建表迁移数据

批量删除大量数据的场景一般是做历史数据归档,释放表空间;考虑到删除数据的成本和数据迁移的成本,例如1000W数据表中清理时间小于某个时刻的100W条数据,则可以通过以下步骤操作来减少锁表的时间:

  1. 选择不需要删除的数据,并把它们迁移插入到一张相同结构的空表里;

  1. 重命名原始表,并给新表命名为原始表的原始表名;

  1. 删掉原始表;

此外,建议

  • 建议在delete的SQL语句中使用limit,防止一次删除全部满足条件的大量的记录;且limit后面的数量也不能太大,要选择一个合适的量;

  • 如果delete的查询条件不走索引,建议先查出满足条件的记录的主键,然后根据主键删除记录

  • 为了临时提高删除速度,在不影响业务的情况下,如凌晨业务低谷期,可以临时删除部分索引;因为MySQL官方手册提到:删除数据的速度和索引数量是成正比的;因此可以先删除这个表中的其他索引,即除了delete的where条件命中的索引,等删除完数据后再重新加上原来的索引;

truncate与delete的区别?

truncate都做了哪些操作?

truncate操作实际上分为drop、re-create两步:

  • drop操作的第一个阶段,是对buffer pool页面进行清除的过程,将表相关的数据页从flush链中删除;该操作会导致其他事务在获取buffer pool instance的锁时被阻塞,从而影响数据库性能;

  • drop操作的第二个阶段,是删除ibd磁盘文件的过程;删除数据库物理文件越大,则占用I/O资源消耗越大,删除操作耗时越久;

  • re-create操作阶段,只要被删除的表的.frm文件完好无损,在drop table之后就可以按照原表结构信息进行重建,重建后表的auto_increment值会被重置;

truncate与delete的区别?

  • 关于删除的数据范围:

truncate不带任何条件,属于表级别删除;

而delete可以根据where条件删除多条满足where条件的数据;

  • 关于回滚:

truncate操作是一个不可回滚的ddl操作;MySQL为了提高删除整张表数据的性能,truncate操作其本质上其实是先drop table一次性删除所有数据,然后再re-create table,因此不可回滚;

delete语句是一个可回滚的dml语句;delete语句可以通过where条件对要删除的记录进行选择,数据一行一行的删除,binlog日志会记录每行数据的删除记录,事务提交前可通过undolog回滚,通过undolog恢复数据;

  • 对自增主键的影响:

如果一个表中有自增字段,使用TRUNCATE TABLE和没有WHERE子句的DELETE删除所有记录后,这个自增字段将起始值恢复成1;

否则,delete语句不会重置自增主键的起点;

  • 对表空间的影响:

truncate对表数据直接进行一次性的物理删除;当表被truncate后,这个表和索引所占用的空间会恢复到初始大小;

delete操作不会减少表和索引所占用的空间,会产生数据空洞导致空间利用率低,可能引发页合并;

  • 执行效率:

truncate执行效率高;无需逐行扫描表数据删除,而是直接一次性进行物理删除,快速释放空间占用;

delete效率依赖where条件的编写;大表删除会产品大量的binlog且删除效率低,删除操作可能出现较多的碎片空间而不是直接释放空间占用;

其他:对于由FOREIGN KEY约束引用的表,不能使用TRUNCATE TABLE,而应使用不带WHERE子句的DELETE语句;由于TRUNCATE TABLE不记录在日志中,所以它不能激活触发器;

这篇关于MySQL——关于删除/purge/删除加锁/删除大量数据/truncatedelete的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/211392

相关文章

macOS无效Launchpad图标轻松删除的4 种实用方法

《macOS无效Launchpad图标轻松删除的4种实用方法》mac中不在appstore上下载的应用经常在删除后它的图标还残留在launchpad中,并且长按图标也不会出现删除符号,下面解决这个问... 在 MACOS 上,Launchpad(也就是「启动台」)是一个便捷的 App 启动工具。但有时候,应

Java利用JSONPath操作JSON数据的技术指南

《Java利用JSONPath操作JSON数据的技术指南》JSONPath是一种强大的工具,用于查询和操作JSON数据,类似于SQL的语法,它为处理复杂的JSON数据结构提供了简单且高效... 目录1、简述2、什么是 jsONPath?3、Java 示例3.1 基本查询3.2 过滤查询3.3 递归搜索3.4

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们

Python Dash框架在数据可视化仪表板中的应用与实践记录

《PythonDash框架在数据可视化仪表板中的应用与实践记录》Python的PlotlyDash库提供了一种简便且强大的方式来构建和展示互动式数据仪表板,本篇文章将深入探讨如何使用Dash设计一... 目录python Dash框架在数据可视化仪表板中的应用与实践1. 什么是Plotly Dash?1.1