亲宝软件园·资讯

展开

MySQL 大表删除 浅谈MySQL怎样优雅的做大表删除

来呀,一起学习呀~ 人气:0
想了解浅谈MySQL怎样优雅的做大表删除的相关内容吗,来呀,一起学习呀~在本文为您仔细讲解MySQL 大表删除的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:MySQL,大表删除,下面大家一起来学习吧。

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

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

一、Truncate操作

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

1.1 MySQL truncate 都做了哪些操作?

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

二、Delete操作

2.1 MySQL delete 都做了哪些操作?

2.2 如何优化delete操作?

2.3 delete常见的两个场景

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

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

-- 利用自增长主键索引
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且删除效率低,删除操作可能出现较多的碎片空间而不是直接释放空间占用

加载全部内容

相关教程
猜你喜欢
用户评论