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*1024),6),' G') AS 'Data Size',
  CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' ,
  CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total'
FROM information_schema.TABLES
WHERE table_schema LIKE 'olderdb';

3. 查询主键索引

SHOW INDEX FROM orbit

采取策略

前提：目前需要删除80%的数据

① delete语句

我们知道delete语句的删除速度与索引量成正比，此表中的索引量已经很大了，而且数据量非常之巨大，若采用常规delete语句删除，必定会话费几天的时间。

Delete语句删除不会释放出磁盘空间，所以肯定还是会出现报警，所以此种方式不可取。

② drop掉表

新建结构相同的表，命名“cc”,'将需要保存的数据插入到此表中，然后drop掉老表。

Sql语句如下：

基于老表新建新表

CREATE TABLE cc LIKE orbit ;

插入数据（几百万的数据量一定要分批插入，一次30万-40万为最佳，毕竟mysql的数据处理能力有限）

按日期查询后插入（每天大概会产生30多万数据量，所以采用日期插入）

INSERT INTO cc SELECT * FROM orbit WHERE xttime > '2018-04-16 00:00:00' AND xttime<='2018-04-17 00:00:00';

结果如下：

可以看到50多万的数据量用了不到5分钟，还是比较快的。

清理后数据表空间得以释放

然后drop掉老表

DROP TABLE orbit

目测只用了3秒左右

重命名新表“cc”

ALTER TABLE cc RENAME TO orbit

总结

以上所述是小编给大家介绍的mysql数据库如何实现亿级数据快速清理 ，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对我们网站的支持！

赞 (0)

相关推荐

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

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

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

  • 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数据库如何实现亿级数据快速清理

    今天收到磁盘报警异常,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 亿级数据分页的优化

    浅谈MySQL 亿级数据分页的优化

    背景 下班后愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排. 突然电话响了起来,一看是我们的一个开发同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了. 果然,沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用,这个操作直接导致线上的MySql集群被拖慢了. 好吧,这问题算是严重了,下了地铁匆匆赶到家,开电脑,跟同事把Pinpoint上的慢查询日志捞出来.看到一个很奇怪的查询,如下 POST domain/v1.0/module/method?ord

  • MySQL 亿级数据导入导出及迁移笔记

    MySQL 亿级数据导入导出及迁移笔记

    最近MySQL的笔记有点多了,主要是公司Oracle比较稳定维护较少,上周被安排做了一个MySQL亿级数据的迁移,趁此记录下学习笔记: 数据迁移,工作原理和技术支持数据导出.BI报表之类的相似,差异较大的地方是导入和导出数据量区别,一般报表数据量不会超过几百万,而做数据迁移,如果是互联网企业经常会涉及到千万级.亿级以上的数据量. 导入和导出是两个过程,即使做数据迁移我们也要分开来看,同时,导入/导出方式又分为: 1.MySQL自带导入/导出方式 2.各类客户端导入/导出方式 先总结下导出: 1.

  • MySQL数据库误操作后快速回滚的方法

    基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了.假如这还是线上环境核心业务数据,那这事就闹大了.误操作后,能快速回滚数据是非常重要的. binlog2sql快速回滚 首先,确认你的MySQL server开启了binlog,设置了以下参数: [mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin.log max_binlog_siz

  • Shell下实现免密码快速登陆MySQL数据库的方法

    背景 当我们在 Shell 下想要通过 mysql-client 登陆到 MySQL 数据库的时候,我们总是需要非常麻烦的输入一次又一次的密码. 而且,如果你的 root 密码是高度随机的话(LastPass大法好),那么你登陆一次 MySQL 数据库的成本就会非常大了. 通常我们登陆数据库的时候都是这样登陆的,如下 root@imlonghao:~# mysql -uroot -p Enter password: 那么,有没有一个办法能够既安全,又能简单方便地登陆到数据库去呢? 方法 答案当然

  • MySQL数据库重命名的快速且安全方法(3种)

    MySQL数据库重命名的快速且安全方法(3种)

    MySQL数据库重命名的方法 Innodb引擎的表如何改数据库名,MyISAM引擎又该如何操作. 如果表是MyISAM引擎可以直接去到数据库目录mv重命名文件夹就可以. Innodb完全不行,会提示相关表不存在. 第一种方法:rename database 弃用了 RENAME database old_db_name TO new_db_name 这个是5.1.7到5.1.23版本可以用的,但是官方不推荐,会有丢失数据的危险 第二种方法:mysqldump 备份 1.创建需要改成新名的数据库.

  • nodejs环境快速操作mysql数据库的方法详解

    nodejs环境快速操作mysql数据库的方法详解

    github地址https://github.com/dmhsq/dmhsq-mysql-db 可用于腾讯云SCF以及云开发环境 错误处理尚未完善 错误参考mysql错误 引入依赖包 npm install dmhsq-mysql-db 效果如下 简化了mysql的使用 安装依赖 npm install dmhsq-mysql-db 使用示例 快速操作mysql 错误处理尚未完善 部分错误参考mysql错误 引入资源 const database = require("dmhsq-mysql-d

  • 详解MySQL数据库千万级数据查询和存储

    百万级数据处理方案 数据存储结构设计 表字段设计 表字段 not null,因为 null 值很难查询优化且占用额外的索引空间,推荐默认数字 0. 数据状态类型的字段,比如 status, type 等等,尽量不要定义负数,如 -1.因为这样可以加上 UNSIGNED,数值容量就会扩大一倍. 可以的话用 TINYINT.SMALLINT 等代替 INT,尽量不使用 BIGINT,因为占的空间更小. 字符串类型的字段会比数字类型占的空间更大,所以尽量用整型代替字符串,很多场景是可以通过编码逻辑来实

  • 运用Python快速的对MySQL数据库进行重命名

    对数据库的表进行重命名可以使用以下原生sql: RENAME TABLE old_table TO new_table; 窘境:但是MySQL并没有直接支持对数据库进行重命名 那么如何运用Python快速的对现有的数据库进行重命名呢? 比如项目初期,对数据库的命名(db_ridingroad)没有规划好, 然后在下面创建了大量的表和写入了大量的数据,现在需要对数据库的名字进行重命名为(db_news_website) 常规思路 下面的方法步骤较为繁琐 -- 数据库备份 mysqldump –u

  • 如何使用分区处理MySQL的亿级数据优化

    mysql在查询上千万级数据的时候,通过索引可以解决大部分查询优化问题.但是在处理上亿数据的时候,索引就不那么友好了. 数据表(日志)是这样的: 表大小:1T,约24亿行: 表分区:按时间分区,每个月为一个分区,一个分区约2-3亿行数据(40-70G左右). 由于数据不需要全量处理,经过与需求方讨论后,我们按时间段抽样一部分数据,比如抽样一个月的数据,约3.5亿行. 数据处理的思路: 1)建表引擎选择Innodb.由于数据是按月分区的,我们将该月分区的数据单独copy出来,源表为myisam引擎

随机推荐