mysql完全快速清空一个表(浅谈MySQL如何优雅的做大表删除)
mysql完全快速清空一个表
浅谈MySQL如何优雅的做大表删除目录
-
一、Truncate操作
- 1.1 MySQL truncate 都做了哪些操作?
- 1.2 如何优化truncate操作带来的资源消耗?
-
二、Delete操作
- 2.1 MySQL delete 都做了哪些操作?
- 2.2 如何优化delete操作?
- 2.3 delete常见的两个场景
- 三、Truncate/Delete优劣势对比
随着时间的推移或者业务量的增长,数据库空间使用率也不断的呈稳定上升状态,当数据库空间将要达到瓶颈的时候,可能我们才会发现数据库有那么一两张的超级大表!他们堆积了从业务开始到现在的全部数据,但是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 大表删除内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://blog.csdn.net/weixin_37692493/article/details/115283520
- mysql死锁情况(MySQL kill不掉线程的原因)
- phpmysql完全学习手册教程(Windows下搭建PHP开发环境Apache+PHP+MySQL)
- 怎么查看mysql运行日志(通过Query Profiler查看MySQL语句运行时间的操作方法)
- mysql的sql语句优化5种方式(MySQL:五个常见优化SQL的技巧)
- mysql发生系统错误(MySQL too many connections错误的原因及解决)
- mysql常用的sql语句大全(mysql建表常用的sql语句汇总)
- mysql如何给一个表批量添加字段(mysql从一张表查询批量数据并插入到另一表中的完整实例)
- 如何使java与sqlserver数据库连接(java连接mysql数据库 java连接sql server数据库)
- mysql mvcc 底层原理(浅析MySQL - MVCC)
- mysql并发控制的重要方式(Mysql服务添加 iptables防火墙策略的方案)
- mysql常用四种日志(MySQL 撤销日志与重做日志Undo Log与Redo Log相关总结)
- mysql mvcc 流程(Mysql MVCC机制原理详解)
- mysql如何删除外键约束数据(MySQL中外键的创建、约束以及删除)
- myeclipse连接mysql数据库的方法(教你用eclipse连接mysql数据库)
- MySQL中ROUND四舍五入函数需要注意的地方
- mysql数据表实例教程(mysql数据库入门第一步之创建表)
- 2020年大众7.5代高尔夫R终结特别版 最后的呐喊(2020年大众7.5代高尔夫R终结特别版)
- 七年前的这部剧有毒,全剧只有女主红到发紫,男主至今无人认识(七年前的这部剧有毒)
- 宋轶除了演过于曼丽,原来还演过一个青楼女子(宋轶除了演过于曼丽)
- 赵丽颖第一部当女主的戏,主角配角个个都是实力演员(赵丽颖第一部当女主的戏)
- 乾隆为何这么喜爱白塔原因是什么(乾隆为何这么喜爱白塔原因是什么)
- 逐渐消失的东北八大怪现象,进步的社会里我们遗失的是什么(逐渐消失的东北八大怪现象)
热门推荐
- sqlserver2008手动备份方法(MSSQL 2008 自动备份数据库的设置方法)
- linux系统各种执行命令(Linux调整命令历史方法详解)
- tomcat作用及原理(详解Tomcat常用的过滤器)
- vue加element ui弹窗(Vue中ElementUI分页组件Pagination的使用方法)
- tomcat集群如何实现线程安全(如何通过LambdaProbe实现监控Tomcat)
- 微信小程序映射设置(微信小程序虚拟列表的实现示例)
- python 时间戳转化为格式(Python datetime和unix时间戳之间相互转换的讲解)
- vue接口请求类封装(Vue接口封装的完整步骤记录)
- mysql中length、char_length区别
- python设置微信(利用python实现在微信群刷屏的方法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9