MySQL 线上数据库清理数据的方法

01 场景分析

今天下午,开发的同事提来一个需求,需要在线上要删除一些数据记录,简单看了看数据的分布,大概是要删除数据表中的两千七百多万条记录,数据表的总记录是两千八百多万,也就是说,要删除的记录占了总记录的绝大部分比重,两千七百多万的数据记录,要是删除的话,使用的时间是相当长的,对线上的业务肯定会造成影响。这里将实际的应用案例简单重构为以下方法:

mysql> select date,count(*) from test.tbl_a  group by date;
+----------+----------+
|   date | count(*) |
+----------+----------+
| |  63103 |
| 20190118 | |
| |  125916 |
| 20190120 |   |
| |  129198 |
| 20190122 |  |
| | 5191247 |
+----------+----------+
 rows in set (13.21 sec)

上面就是重构之后的表的结构,我们可以看到,test数据库中的表tbl_b按照date分组之后,每个组的数据量都不小,而我们的需求是将date为20190118和20190123的记录删除,可以看到这两种记录总计有两千多万条,占了表中数据的绝大部分,如果直接删除的话,线上的业务肯定会受到阻塞。

以下是操作方法,需要注意的是,应用下面的操作方法的前提是:

要删除的数据占了数据表中的绝大部分。

02 操作办法

通过分析,知道剩余的表数据对于全表来说是很小的一部分,这个操作我们分为4步:

1.我们先把剩余的数据存入到另外一个数据库test1中:

create table test1.tbl_b_new as
select * from test.tbl_b
where date in(,,,,);

这个操作的时间只有3s左右;

2.我们在test1数据库中创建一个同名的表tbl_b,它的结构和test数据库中的tbl_b数据结构一致:

create table test1.tbl_b like test.tbl_b;

3.紧接着,我们使用数据库中的rename操作将表test中的表tbl_b和test1中的表tbl_b进行交换,等价于将test数据库中的表所有数据清除。

RENAME TABLE test.tbl_b  TO test1.tbl_b_bak,
         test1.tbl_b  TO test.tbl_b,
         test1.tbl_b_bak TO test1.tbl_b;

4.再讲第一步保存的剩余数据填充到新表中来,如下:

insert into test.tbl_b select * from  test1.tbl_b_new;

03 结果分析

看上去我们好像把问题搞复杂了,直接删除的事情,被我们搞的多了好几个步骤,但是实际上不是这样的,这一套操作可以帮我们节省好几分钟时间,对线上业务的影响也更小,原因如下:

  1. 我们使用create table as的方法创建剩余的数据表,这种方法使我们仅对数据表中的少部分数据进行了操作。避免了我们对过多的数据进行扫描。
  2. mysql中对大表进行rename的操作,rename命令会直接修改底层的.frm文件,所以它的速度是相当之快的。

第二个特点给我们提供了一种思路,在一个很着急的业务中,要使用一个表的时候,往往不给我们留充足的时间备份表,如果我们想要删除一个大表里面的数据,而且需要进行相关备份,我们可以通过rename操作迅速处理,然后再想办法去备份rename之后的表。

上面的例子中使用create table as 和create table like两种克隆表的方式,有几点需要注意:

create table like方式会完整地克隆表结构,但不会插入数据,需要单独使用insert into或load data方式加载数据
create table as  方式会部分克隆表结构,完整保留数据

如果有兴趣,可以做做试验进行验证。

以上就是MySQL 线上数据库清理数据的方法的详细内容,更多关于MySQL 线上数据库清理数据的资料请关注我们其它相关文章!

时间: 2021-03-28

mysql数据库如何实现亿级数据快速清理

今天收到磁盘报警异常,50G的磁盘被撑爆了,分析解决过程如下: 1. 进入linux服务器,查看mysql文件夹中各个数据库所占的磁盘空间大小 看到了吗,光olderdb就占了25G 2. 用SQLyog登录mysql数据库,查看数据库各个表的占用空间情况 SELECT CONCAT(table_schema,'.',table_name) AS 'aaa', table_rows AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*

阿里云mysql空间清理的方法

今天收到阿里云磁盘告警通知,查看了一个100G的空间已达到80G的使用量,如果决定删除2018年1月1日之前的数据,可delete后,再去查看发现磁盘可用空间并没有减少,还飞速的上涨,这可把我急坏了,不一会儿数据库就锁死了. 敢忙找度娘,原来delete后,磁盘不会减少,还得执行一下 OPTIMIZE TABLE +表名,以后找到救星了,可执行此命信不成功,原来是空间不足,数据库存补锁不能执行这条指令,一下没了头绪,如是决定先把服务器暂停,就在暂停时奇迹发生了,可用空间有5G多了,这下可以执行O

用批处理实现自动备份和清理mysql数据库的代码

有网友问我在win2003下如何自动备份MySQL数据库,既然是自动备份,那肯定得写脚本.我想了想,这个并不是很困难,是很容易实现的,备份可以用脚本实现,那自动又该如何实现呢?也很简单,就用windows自带的"任务计划"功能,设定一个时间,让系统定时跑脚本,不就实现了自动备份数据库的功能了吗? 不过到现在已经有很多的mysql备份软件,例如我比较喜欢使用的是护卫神的好备份软件. 下载地址:http://www.jb51.net/softs/42944.html 首先把脚本代码贴出来:

Shell脚本自动备份MySQL到FTP并定期清理过期备份

以前公司用的一个网上找的MySQL自动备份到FTP的Shell脚本,一直用着不错.但是问题就是随着备份文件的越来越多,不得不定期清理FTP上的老备份文件,十分的不便. 实际上MySQL备份无需保留太长时间的,如果能有自动清理旧的备份文件的功能就好了.不过网上找了一下,虽然也有这方面的脚本,不过可能不太符合我的需求吧.于是只能自己手动改下以前的那个Shell脚本来实现咯. 首先我们要确定保留备份的时间,我觉得一个月的备份保留就足够了.因为基本不会出现1个月后要恢复1个月之前备份的情况.(如果有请自

Mysql数据库清理binlog日志命令详解

概述 今天主要分享下mysql数据库应该如何正确的删除binlog日志,这里要注意不要强制使用rm命令进行清除.否则mysq-bin.index错乱,最终导致后期expire-log-days配置项失效. 1.查看binlog日志 mysql> show binary logs; 2.删除某个日志文件之前的所有日志文件 purge binary logs to 'mysql-bin.000035'; 3.清理2019-09-09 13:00:00前binlog日志 PURGE MASTER LO

mysql 正确清理binlog日志的两种方法

mysq 正确清理binlog日志 前言: MySQL中的binlog日志记录了数据库中数据的变动,便于对数据的基于时间点和基于位置的恢复,但是binlog也会日渐增大,占用很大的磁盘空间,因此,要对binlog使用正确安全的方法清理掉一部分没用的日志. [方法一]手动清理binlog 清理前的准备: ① 查看主库和从库正在使用的binlog是哪个文件 show master status\G show slave status\G ② 在删除binlog日志之前,首先对binlog日志备份,以

如何彻底删除mysql服务(清理注册表)详解

前言 由于安装某个项目的执行文件,提示要卸载MySQL以便它自身MySQL安装,然后我禁用了MYSQL服务,再把这个文件夹删除后,发现还是提示请卸载MYSQL服务. -------------------------------------------------------------------- 禁用服务方式如下: 或者 我的电脑右键->管理->服务,进入后手动禁用. ------------------------------------------------------------

MySQL 自动清理binlog日志的方法

说明: 开启MySQL binlog日志的服务器,如果不设置自动清理日志,默认binlog日志一直保留着,时间一长,服务器磁盘空间被binlog日志占满,导致MySQL数据库出错. 使用下面方法可以安全清理binlog日志 一.没有主从同步的情况下清理日志 mysql -uroot -p123456 -e 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ),INTERVAL 5 DAY)'; #mysql 定时清理5天前的binlog mysql -u root

解析mysql 表中的碎片产生原因以及清理

大量删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来 .对于不同的存储引擎整理碎片的方式不一样.myisam可以有以下方式:mysql> show table status from test like 'testusers'\G*************************** 1. row ***************************  ....           Rows: 3 Avg_row_length: 45        

php数据入库前清理 注意php intval与mysql的int取值范围不同

php保存数据到mysql 打算在dao层进行数据入库前的清理,比如varchar进行trim,int进行intval. 有一天突然想起,php intval的取值范围与mysql的int类型一样吗? 查了一下,不一样-- http://php.net/manual/en/function.intval.phphttp://dev.mysql.com/doc/refman/5.1/zh/column-types.html#numeric-typesphp intval的取值范围:与操作系统相关,

清理Mysql general_log的方法总结

方法1: SET GLOBAL general_log = 'OFF'; RENAME TABLE mysql.general_log TO mysql.general_log2; DELETE FROM mysql.general_log2; 注意:当DELETE FROM mysql.general_log2执行删除表数据时,发现操作系统的数据文件还是存在的,需要手动删除该数据文件,再继续下面数据操作步骤 OPTIMIZE TABLE general_log2; RENAME TABLE m

PHP Yii清理缓存的实现方法

PHP  Yii清理缓存的实现方法 本文实例讲述了Yii清理缓存的方法.分享给大家供大家参考,具体如下: html: <button onclick="clearCache()">ClearCache</button> js: function clearCache() { $.get('../eng/index.php?r=site/clear&'+new Date().getTime(),function(){ alert('Clear eng ca

Delphi远程连接Mysql的实现方法

本文实例讲述了Delphi远程连接Mysql的实现方法,是Delphi数据库程序设计中非常重要的技巧.分享给大家供大家参考之用.具体方法如下: Delphi 通过ADOConnection链接远程Mysql数据库,要在安装软件的电脑上安装Mysql 驱动,没有的在百度下载,搜索mysql ODBC驱动就出来了 主要的链接代码如下: var srstring:string; srstring:='DRIVER={MySQL ODBC 3.51 Driver};SERVER='+Trim(SERVE

MySQL去重的方法整理

MySQL去重的方法整理 [初级]有极少的重复行 使用distinct查出来,然后手动一行一行删除. [中级]按照单个字段的重复去重 例如:对id字段去重 使用方法:获取id的重复字段的值,利用相同id字段所在的行中,比较出数据不同的字段,删除 除了最小(或最大)的字段所在的该行之外的所有重复的行.一般使用主键来比较,因为主键的值一定是唯一值,绝对不相同. id name 1 a 1 b 2 c 2 a 3 c 结果: id name 1 a 2 a 操作: delete from a_tmp

JSP使用JDBC连接MYSQL数据库的方法

本文实例讲述了JSP使用JDBC连接MYSQL数据库的方法.分享给大家供大家参考,具体如下: 1. 可在 http://www.mysql.com/products/connector-j/index.html下载MySQL JDBC驱动程序mysql-connector-java-*.jar,如我下载的是mysql-connector-java-5.1.18-bin.jar并加入到ClassPath下面,或加入到项目中.   2. 注册JDBC驱动程序 try { Class.forName(

MySQL数据库修复方法(MyISAM/InnoDB)

在网上找了篇MySQL的技术文章,感觉不错,把它翻译过来共享下.   原文作者:Mike Peters   我整理了7条修复MySQL数据库的方法,当简单的重启对数据库不起作用,或者有表崩溃时.   简单的MySQL重启:   /usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown /usr/local/mysql/bin/mysqld_safe &    1.MyISAM表崩溃    MySQL数据库允许不同的表使用不同的存

Centos下 修改mysql密码的方法

1.修改MySQL的登录设置: # vim /etc/my.cnf 加上一句:skip-grant-tables 如: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock 2.重启mysql # service mysqld restart Stopping MySQL: [ OK ] Starting MySQL: [ OK ] 3.修改密码 mysql> USE mysql ; Database changed m

java实现清理DNS Cache的方法

本文实例讲述了java实现清理DNS Cache的方法.分享给大家供大家参考.具体分析如下: 一.测试环境 OS:Windows7 x64 JDK:1.6.0_45 二.本人找到四种方式清理jvm的DNS缓存,大家可以根据自己的情况选用. 1. 在首次调用InetAddress.getByName()前,设置java.security.Security.setProperty("networkaddress.cache.ttl", "0"); 2. 修改jre/li

iOS缓存文件大小显示功能和一键清理功能的实现方法

缓存占用了系统的大量空间,如何实时动态的显示缓存的大小,使用户清晰的了解缓存的积累情况,有效的进行一键清理呢? 为方便读者和未来自己更好理解,我们创建这样场景.(在表视图的清除缓存一单元格内创建一个UILabel *cacheLabel用于显示当前缓存,当点击单元格弹出提示框,点击确定,清除缓存). 下面是实现代码: #pragma mark - 计算缓存大小 - (NSString *)getCacheSize { //定义变量存储总的缓存大小 long long sumSize = 0; /