MySQL如何优雅的做大表删除？
发布时间：2022-11-03 11:06:41 所属栏目：MySql教程 来源：
导读： 随着时间的推移或者业务量的增长，数据库空间使用率也不断的呈稳定上升状态，当数据库空间将要达到瓶颈的时候，可能我们才会发现数据库有那么一两张的超级大表！他们堆积了从业务开始到现在的全部数据，但是90%的
|
随着时间的推移或者业务量的增长，数据库空间使用率也不断的呈稳定上升状态，当数据库空间将要达到瓶颈的时候，可能我们才会发现数据库有那么一两张的超级大表！他们堆积了从业务开始到现在的全部数据，但是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条件无有效索引过滤 比较常见的一个场景是mysql删除表，业务上需要删除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个月数据，其余历史数据删除，我们通常的做法是： create table t1_tmp like t1; -- 根据实例业务数量进行分批，尽量每批次处理数据量不要太大 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'; 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'; alter table t1_tmp auto_increment={t1表当前auto值}+{变更期间预估增长值} 三、Truncate/Delete优劣势对比 操作类型描述优势劣势 Truncate 表的全量删除操作 无需扫描表数据，执行效率高，直接进行物理删除，快速释放空间占用 DDL操作无法进行回滚,无法按条件进行删除 Delete 根据指定条件进行过滤删除操作 可根据指定条件进行过滤删除 删除效率依赖where条件的编写，大表删除会产品大量的binlog且删除效率低，删除操作可能出现较多的碎片空间而不是直接释放空间占用 （编辑：天瑞地安资讯网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐