浅谈MySQL如何优雅的做大表删除

随着时间的推移或者业务量的增长,数据库空间使用率也不断的呈稳定上升状态,当数据库空间将要达到瓶颈的时候,可能我们才会发现数据库有那么一两张的超级大表!他们堆积了从业务开始到现在的全部数据,但是90%的数据都是没有业务价值的,这时候该如何处理这些大表?

既然是没有价值的数据,我们通常一般会选择直接删除或者归档后删除两种,对于数据删除的操作方式来说又可分为两大类:

  • 通过truncate直接删除表中全部数据
  • 通过delete删除表中满足条件记录

一、Truncate操作

从逻辑意义上来讲,truncate操作就是删除表中所有记录行,但是又与delete from table_name wehre 1=1这种操作不一样。MySQL为了提高删除整张表数据的性能,truncate操作其本质上其实是先drop table然后在re-create table。也真因如此,truncate操作是一个不可回滚的DDL操作。

1.1 MySQL truncate 都做了哪些操作?

  • truncate操作实际上分为drop、re-create两步
  • drop操作的第一个阶段,是对Buffer pool页面进行清除的过程,将表相关的数据页从flush链中删除,而不需要做flush操作。该步骤的瓶颈点主要在于flush队列的删除操作必须持有对应buffer pool instance的锁并进行遍历搜索,如果buffer pool instance比较大且flush链中需要删除的数据页很多,该操作会导致其他事务在获取buffer pool instance的锁时被阻塞,从而影响数据库的性能
  • drop操作的第二个阶段,是删除ibd磁盘文件的过程。删除数据库物理文件越大I/O资源消耗越大,删除操作耗时越久
  • re-create操作阶段,只要删除表的.frm文件完好无损,在drop table之后就可以按照原表结构信息进行重建,重建后表的auto_increment值会被重置

1.2 如何优化truncate操作带来的资源消耗?

  • 对于truncate操作中的drop表第一阶段,当分配给MySQL实例的innodb_buffer_pool_size超过1GB时,合理的设置innodb_buffer_pool_instances参数,提高并发的同时也变相的减少扫描buffer pool instance时锁资源占用耗时
  • 对于truncate操作中的drop表第二阶段,在删除对应表之前,先对改表的.ibd文件创建一个硬连接,加快MySQL层面的drop操作执行效率,减少对数据库层面的性能损耗。后续手动对操作系统层面我们做的硬连接进行清理

二、Delete操作

2.1 MySQL delete 都做了哪些操作?

  • 根据where条件对删除表进行索引/全表扫描,检查是否符合where条件,该阶段会对扫描中所有行进行加锁。该阶段是最大的资源消耗隐患,若表的数据量大且delete操作无法有效利用索引减少扫描数据量,该步骤对于数据库带来的锁争用、cpu/io资源的消耗都是巨大的
  • 对不能够被where条件匹配的行施加的锁会在条件检查后予以释放,InnoDB仅锁定需要删除的行。这可以有效地降低锁争用,但是我们仍需要关注的一点是,一次性删除大批量的数据,该操作将会产生巨大的binlog事务日志,这对于MySQL自身以及主从架构中的从库都是不友好的,可能带来叫的复制延迟。

2.2 如何优化delete操作?

  • delete全表删除操作需要谨慎,可考虑使用truncate操作
  • delete … where … 中,where过滤条件尽量保证可有效利用索引减少数据扫描量,避免全表扫描
  • 对于大批量数据删除且where条件无索引的情况,delete操作可额外增加自增长主键或者含索引的时间字段,进行分批删除操作,每次删除少量数据,分多批次执行。
  • 对于保留近期数据删除历史数据的经典场景,可创建同结构的xxx_tmp表并通过insert xxx_tmp select …操作将需要的数据保留至tmp表中、然后通过rename操作将当前业务表xxx替换为xxx_bak表,xxx_tmp表替换为当前业务表名xxx,后续手动删除无用的大表xxx_bak

2.3 delete常见的两个场景

2.3.1 delete where条件无有效索引过滤

比较常见的一个场景是,业务上需要删除t1 condition1=xxx的值,condition字段无法有效利用索引,这种情况下我们通常的做法是:

  • 查看当前表结构中可有效利用的索引,尽量是表的自增长主键或者时间索引字段
  • 有效利用自增长主键索引或者时间索引,将delete操作添加索引字段的范围过滤,每次删除少量数据,分多批次执行。具体分批需要根据业务实际进行评估,避免一次性删除大批量数据。
-- 利用自增长主键索引
delete from t1 where condition1=xxx and id >=1 and id < 50000;
delete from t1 where condition1=xxx and id >=50000 and id < 100000;

-- 利用时间索引
delete from t1 where condition1=xxx and create_time >= '2021-01-01 00:00:00' and create_time < '2021-02-01 00:00:00';
delete from t1 where condition1=xxx and create_time >= '2021-02-01 00:00:00' and create_time < '2021-03-01 00:00:00';

2.3.2 保留近期数据删除历史数据

比较常见的一个场景是,需要仅保留t1表近3个月数据,其余历史数据删除,我们通常的做法是:

创建一张t1_tmp表用来临时存储需要保留的数据

create table t1_tmp like t1;

根据有索引的时间字段,分批次的将需要保留的数据写入t1_tmp表中,该步骤需要注意的是,最后一批次时间的操作可暂时不处理

-- 根据实例业务数量进行分批,尽量每批次处理数据量不要太大
insert into t1_tmp select * from t1 where create_time >= '2021-01-01 00:00:00' and create_time < '2021-02-01 00:00:00';
insert into t1_tmp select * from t1 where create_time >= '2021-02-01 00:00:00' and create_time < '2021-03-01 00:00:00';

-- 当前最后一批次数据先不操作
-- insert into t1_tmp select * from t1 where create_time >= '2021-03-01 00:00:00' and create_time < '2021-04-01 00:00:00';

通过rename操作将当前业务表t1替换为t1_bak表,t1_tmp表替换为当前业务表名t1,被删除表若有频繁的DML操作,该步骤会造成短暂的业务访问失败

alter table t1 rename to t1_bak;
alter table t1_tmp rename to t1;

将最后一批次数据写入当前业务表,该步骤的目的是为了减少变更操作流程中的数据丢失

insert into t1 select * from t1_bak where create_time >= '2021-03-01 00:00:00' and create_time < '2021-04-01 00:00:00';

在rename操作步骤中,还有一点我们需要关注的是,变更表主键是自增长还是业务唯一的uuid,若为自增长主键,我们还需要注意修改t1_tmp表的自增长值,保证最终设置值包含变更期间数据写入

alter table t1_tmp auto_increment={t1表当前auto值}+{变更期间预估增长值}

三、Truncate/Delete优劣势对比

操作类型 描述 优势 劣势
Truncate 表的全量删除操作 无需扫描表数据,执行效率高,直接进行物理删除,快速释放空间占用 DDL操作无法进行回滚,无法按条件进行删除
Delete 根据指定条件进行过滤删除操作 可根据指定条件进行过滤删除 删除效率依赖where条件的编写,大表删除会产品大量的binlog且删除效率低,删除操作可能出现较多的碎片空间而不是直接释放空间占用

到此这篇关于浅谈MySQL如何优雅的做大表删除的文章就介绍到这了,更多相关MySQL 大表删除内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2021-03-30

mysql 大表批量删除大量数据的实现方法

问题参考自:https://www.zhihu.com/question/440066129/answer/1685329456 ,mysql中,一张表里有3亿数据,未分表,其中一个字段是企业类型,企业类型是一般企业和个体户,个体户的数据量差不多占50%,根据条件把个体户的行都删掉.请问如何操作?答案为个人原创 假设表的引擎是 Innodb, MySQL 5.7+ 删除一条记录,首先锁住这条记录,数据原有的被废弃,记录头发生变化,主要是打上了删除标记.也就是原有的数据 deleted_flag

Innodb中mysql快速删除2T的大表方法示例

前言 本文主要给大家介绍了关于Innodb中mysql快速删除2T的大表的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 来,先来看小漫画陶冶一下情操 OK,这里就说了.假设,你有一个表erp,如果你直接进行下面的命令 drop table erp 这个时候所有的mysql的相关进程都会停止,直到drop结束,mysql才会恢复执行.出现这个情况的原因就是因为,在drop table的时候,innodb维护了一个全局锁,drop完毕锁就释放了. 这意味着,如果在白天,访

MySQL 删除大表的性能问题解决方案

微博上讨论MySQL在删除大表engine=innodb(30G+)时,如何减少MySQL hang的时间,现做一下简单总结: 当buffer_pool很大的时候(30G+),由于删除表时,会遍历整个buffer pool来清理数据,会导致MySQL hang住,解决的办法是: 1.当innodb_file_per_table=0的时候,以上不是问题,因为采用共享表空间的时候,该表所占用的空间不会被删除,buffer pool中的相关页不会 被discard. 2.当innodb_file_pe

MySQL如何优雅的删除大表实例详解

前言 删除表,大家下意识想到的命令可能是直接使用DROP TABLE "表名",这是初生牛犊的做法,因为当要删除的表达空间到几十G,甚至是几百G的表时候.这样一条命令下去,MySQL可能就直接夯住了,外在表现就是QPS急速下降,客户请求变慢. 解决办法 1.业务低峰时间手动执行删除 这个可能就需要DBA不辞辛劳,大晚上爬起来删表了. 2.先清除数据,最后再删除的方式 譬如1000万条数据,写脚本每次删除20万,睡眠一段时间,继续执行.这样也能做到对用户无感知. 3.对表文件(idb文件

mysql的存储过程、游标 、事务实例详解

mysql的存储过程.游标 .事务实例详解 下面是自己曾经编写过的mysql数据库存储过程,留作存档,以后用到的时候拿来参考. 其中,涉及到了存储过程.游标(双层循环).事务. [说明]:代码中的注释只针对当时业务而言,无须理会. 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS `transferEmailTempData`$$ CREATE PROCEDURE transferEmailTempData(IN jobId VARCHAR(24)) BEG

MySql批量插入优化Sql执行效率实例详解

MySql批量插入优化Sql执行效率实例详解 itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志. updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},

Java集合删除元素ArrayList实例详解

Java集合删除元素ArrayList实例详解 AbstractCollection集合类中有一个remove方法,该方法为了适配多种不同的集合,允许删除空的元素,看这部分代码的时候产生了疑问,为什么这里直接用it.remove()就直接删除了? public boolean remove(Object o) { Iterator<E> it = iterator(); if (o==null) { while (it.hasNext()) { if (it.next()==null) { i

Linux下使用killall命令终止进程的8大用法实例详解

Linux 的命令行提供很多命令来杀死进程.比如,你可以向 kill 命传递一个PID来杀死进程:pkill 命令使用一个正则表达式作为输入,所以和该模式匹配的进程都被杀死. 但是还有一个命令叫 killall ,默认情况下,它精确地匹配参数名,然后杀死匹配进程.在这篇文章中,我们将讨论有关这个命令的实际应用. 默认情况下,killall 命令将向一个/组进程发送一个 SIGTERM 信号,但是,也可以通过参数发送一个指定的信号. 下面我们通过例子详细介绍 killall 的 8 大用法. 1.

MySQL死锁问题分析及解决方法实例详解

MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1.MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 2.各种锁特点 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 行级锁:开销大,加锁慢;

C语言实现散列表(哈希Hash表)实例详解

C语言实现散列表(哈希Hash表) 实例代码: //散列表查找算法(Hash) #include <stdio.h> #include <stdlib.h> #define OK 1 #define ERROR 0 #define TRUE 1 #define FALSE 0 #define SUCCESS 1 #define UNSUCCESS 0 #define HASHSIZE 7 #define NULLKEY -32768 typedef int Status; type

MySQL使用集合函数进行查询操作实例详解

本文实例讲述了MySQL使用集合函数进行查询操作.分享给大家供大家参考,具体如下: COUNT函数 SELECT COUNT(*) AS cust_num from customers; SELECT COUNT(c_email) AS email_num FROM customers; SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num; SUM函数 SELECT SUM(quantity) AS items_total FROM

mysql存储过程之if语句用法实例详解

本文实例讲述了mysql存储过程之if语句用法.分享给大家供大家参考,具体如下: mysql中的 IF语句允许我们根据表达式的某个条件或值结果来执行一组SQL语句,所以我们要在MySQL中形成一个表达式,可以结合文字,变量,运算符,甚至函数来组合.表达式可以返回TRUE,FALSE或NULL,这三个值之一.来看下语法结构: IF expression THEN statements; END IF; 如果上述表达式(expression)计算结果为TRUE,那么将执行statements语句,否

mysql外键的三种关系实例详解

本文实例讲述了mysql外键的三种关系.分享给大家供大家参考,具体如下: 因为有foreign key的约束,使得两张表形成了三种了关系: 多对一 多对多 一对一 一对多或多对一 多对一 create table press( id int primary key auto_increment, name varchar(20) ); create table book( id int primary key auto_increment, name varchar(20), press_id

MySQL 的启动选项和系统变量实例详解

本文实例讲述了MySQL 的启动选项和系统变量.分享给大家供大家参考,具体如下: MySQL的配置信息可以通过两种方式实现,一种是命令行形式,在启动MySQL服务时后边带上相关配置参数,此种方式会在MySQL重启后失效.另外一种是通过写入配置文件,如my.cnf,启动或者重启MySQL服务都会生效,此种方式是永久生效. 启动选项 命令行 在MySQL服务命令启动时,带上配置参数 启动方式可参考这篇:MySQL 的启动和连接方式 命令格式: 启动命令 --启动选项1[=值1] --启动选项2[=值