MySQL数据清理的需求分析和改进

昨天帮一个朋友看了MySQL数据清理的问题,感觉比较有意思,具体的实施这位朋友还在做,已经差不多了,我就发出来大家一起参考借鉴下。

为了保证信息的敏感,里面的问题描述可能和真实情况不符,但是问题的处理方式是真实的。

首先这位朋友在昨天下午反馈说他有一个表大小是近600G,现在需要清理数据,只保留近几个月的数据。按照这个量级,我发现这个问题应该不是很好解决,得非常谨慎才对。如果是通用的思路和方法,我建议是使用冷热数据分离的方式。大体有下面的几类玩法:

  1. exchange partition,这是亮点的特性,可以把分区数据和表数据交换,效率还不错。
  2. rename table,这是MySQL归档数据的一大利器,在其他商业数据库里很难实现。

但是为了保险起见,我说还是得看看表结构再说。结果看到表结构,我发现这个问题和我预想的完全不一样。

这个表的ibd文件大概是600G,不是分区表,InnoDB存储引擎。字段看起来也不多。需要根据时间字段update_time抽取时间字段来删除数据。

我看了下这个表结构,字段不多,除了索引的设计上有些冗余外,直接看不到其他的问题,但是根据数据的存储情况来看,我发现这个问题有些奇怪。不知道大家发现问题没有。

这个表的主键是基于字段id,而且是主键自增,这样来看,如果要存储600G的数据,表里的数据量至少得是亿级别。但是大家再仔细看看自增列的值,会发现只有150万左右。这个差别也实在太大了。

为了进一步验证,我让朋友查询一下这个表的数据量,早上的时候他发给了我最新的数据,一看更加验证了我的猜想。

mysql> select max(Id) from test_data;
+---------+
| max(Id) |
+---------+
| 1603474 |
+---------+
1 row in set (0.00 sec)

现在的问题很明确,表里的数据不到200万,但是占用的空间近600G,这个存储比例也实在太高了,或者说碎片也实在太多了吧。

按照这个思路来想,自己还有些成就感,发现这么大的一个问题症结,如果数据没有特别的存储,200万的数据其实也不算大,清理起来还是很容易的。

朋友听了下觉得也有道理,从安全的角度来说,只是需要注意一些技巧而已,但是没过多久,他给我反馈,说表里的数据除过碎片,大概也有100多G,可能还有更多。这个问题和我之前的分析还是有一些冲突的。至少差别没有这么大。200万的数据量,基本就在1G以内。但是这里却是100多个G,远远超出我的预期。

mysql> select round(sum(data_length+index_length)/1024/1024) as total_mb,
    -> round(sum(data_length)/1024/1024) as data_mb,
    -> round(sum(index_length)/1024/1024) as index_mb
    -> from information_schema.tables where  table_name='hl_base_data';
+----------+---------+----------+
| total_mb | data_mb | index_mb |
+----------+---------+----------+
|   139202 |  139156 |       47 |
+----------+---------+----------+
1 row in set (0.00 sec)

这个问题接下来该怎么解释呢。我给这位朋友说,作为DBA,不光要对物理的操作要熟练,还要对数据需要保持敏感。

怎么理解呢,update_time没有索引,id是主键,我们完全可以估算数据的变化情况。

怎么估算呢,如果大家观察仔细,会发现两次提供的信息相差近半天,自增利的值相差是大概4000左右。一天的数据变化基本是1万。

现在距离10月1日已经有24天了,就可以直接估算出数据大概是在1363474附近。

mysql> select current_date-'20171001'; 
+-------------------------+
| current_date-'20171001' |
+-------------------------+
|                      24 |
+-------------------------+
1 row in set (0.00 sec)

按照这个思路,我提供了语句给朋友,他一检查,和我初步的估算值差不了太多。

mysql> select id , create_time ,update_time from test_data  where id=1363474;
+---------+---------------------+---------------------+
| id      | create_time         | update_time         |
+---------+---------------------+---------------------+
| 1363474 | 2017-09-29 10:37:29 | 2017-09-29 10:37:29 |
+---------+---------------------+---------------------+
1 row in set (0.07 sec)

简单调整一下,就可以完全按照id来过滤数据来删除数据了,这个过程还是建议做到批量的删除,小步快进 。

前提还是做好备份,然后慢慢自动化完成。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-11-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏desperate633

第一课 了解sql准备样例表sql初探

首先阅读这本书,必须先了解这本书里sql语句所操作的数据库表。 也就是书附录中的样例表,深入理解这个样例表不仅可以帮助我们设计好的数据库结构,也能打下基础,更...

13020
来自专栏架构师之路

1分钟了解基于内容的推荐,pm又懂了

前天的《1分钟了解“协同过滤”》,很多同学点了赞,今天接着用通俗的语言说说“基于内容的推荐”,也保证pm弄懂。 什么是基于内容的推荐(Content-based...

40060
来自专栏沃趣科技

【技术分享】沃趣大牛现身解说,教你分分钟掌握MySQL

导读 7月19日,乐商【IT达摩院】再次迎来了一场技术大牛的分享盛会,本次分享会邀请了来自杭州沃趣科技联合创始人、高级MySQL数据库专家—李春,以《MySQL...

43590
来自专栏数据和云

嘉年华专访 | 我有故事,你有酒吗?

潘娟,京东金融高级DBA,主要负责京东金融生产数据库运维及数据库平台、中间件开发工作。多次参与京东金融6.18、11.11大促活动的护航工作。曾负责京东金融数据...

12730
来自专栏数据和云

杨廷琨Oracle Code大会分享:如何编写高效SQL(含PPT)

2018 Oracle Code 于5月17日在新加坡拉开帷幕。作为全球开发者交流分享的年度盛会,为吸引所有领域的开发者,Oracle今年将自1996年开始的J...

12920
来自专栏游戏杂谈

node.js的request模块

更多使用方法和说明可点击这里继续阅读:https://github.com/mikeal/request/

24930
来自专栏马洪彪

SqlServer示例数据库Northwind(一)——实体关系

在学习Spss统计分析、EA画实体关系图、PowerDesigner画数据库模型图等时,苦于找不到一个好的实例。由于实际工作中项目使用的表结构属于公司的商业保密...

44690
来自专栏养码场

记一次大厂面试,成功拿到offer!

若大家看到这类干货文或者觉得很不错的技术文,可后台或者留言区留言,场主会优选,将好文分享给更多的技术人!

12230
来自专栏Java架构

Java程序员从京东、阿里、携程面试回来,已成功拿到京东offer携程(一面)京东(笔试+两面技术+一面hr,拿到offer)总结

32530
来自专栏SDNLAB

借助Barefoot Tofino,Arista提供多功能可编程交换平台

22760

扫码关注云+社区

领取腾讯云代金券