三表轮换实现清理MySQL大表数据

小柏笔记

已于 2023-01-29 14:39:26 修改

阅读量569

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

于 2023-01-11 19:03:57 首次发布

本文链接：https://blog.csdn.net/leibris/article/details/128649513

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

应用背景

先了解下MySQL可以删除表格数据的方法

使用DORP TABLE删除整个表

DROP TABLE IF EXISTS <table>

特点：

会删除整个表格(包括表的结构包括依赖的约束，触发器，索引等)及其数据
仅将该SQL记录到MySQL日志文件中，因此删掉的数据无法找回。
执行速度非常快

使用TRUNCATE TABLE清空整个表

TRUNCATE TABLE <table>

特点：

会保留表结构
会清空表中所有数据
会重建索引
仅将该SQL记录到MySQL日志文件中，因此清空的数据无法找回。
执行速度比较快

使用DELETE FROM TABLE删除表格中的相应数据

DELETE FROM  <table> WHERE C=?

特点：

会保留表结构
仅会删除符合条件的数据
不会删除索引
会将删除每条数据的SQL记录到日志文件中，因此够找回被删除的每一条数据。
受查询条件与删除数据量的影响，执行速度非常慢

如果仅是在少量数据的表中，删除几条数据，常规操作使用DELETE FROM <table>即可，而且还相对其它两种方式比较安全，因为删错了可以从日志中回滚。但是在大数据表中用这种常规删除方式会面临如下问题：

如果使用where按条件删除数据，查询相应数据非常耗时，甚至查询超时。
因为每删除一条数据就会写一条记录到日志文件中，如果执行会删除十万条数据,就会逐一将这十万条删除语句写入日志文件中，这就需要很长的执行时间，甚至将导致磁盘容量不足。主要磁盘IO增加，会造成执行超时。
主实例的DDL语句执行时间较长，会造成主备数据库同步延时。

应对场景

数据库存储容量一定是有限的，太多的表数据不仅会造成存储成本高昂，甚至会查询超时，影响数据的应用。通常会将大数据划分几个阶段来应对，比如简单地按日期将数据分为热数据与冷数据。怎样区分，在此不再展开讨论了。回到主题，如果热数据保存，冷数据为过期数据，那么只要删除过期的冷数据即可回收容量且不影响数据正常应用。一个应用的日志表，假设每天十多万条数据落库，应用中仅需要查询其最近七天的数据来计算报表，这种情况下，超过了七天的数据就是过期数据，如果要快速删除这种过期数据就不能用DELETE来操作了。大道至简，如上所述，删除大表数据最快的方式就是删除整个表。如果能将过期的数据分离存储到一个表里，那么只要一条DORP TABLE语句就能轻松清理成千上万的过期数据。

实现方式

将原来的数据表按月的天数拆分为a,b,c三个表，这三个表按月份中的天数轮流切换读写，因此过期的数据就自动隔离在过期的数据表里了，只要删除表即删除了过期数据。拆分规则如下：

/*计算绿表名*/
function getGreenTableName(int $day):string
{
        if ($day < 11) {
            $table = "a";
        } else if ($day < 21) {
            $table = "b";
        } else {
            $table = "c";
        }
        return $table;
}
/*计算蓝表名*/
function getBlueTableName(int $day, string $green):?string
{
        $days = date("t");//当月总天数,可以固定该值模拟大小月，平年二月28天或闰年二月29天
        $x = ($day + 6) % $days;
        if ($x == 0) $x = $days;
        $day = $x;
        if ($day < 1 || $day > $days) return null;
        $table = getGreenTableName($day);
        if ($table == $green) return null;
        return $table;
 }
 /*计算红表名*/
function getRedTableName(string $green):?string
{
        if ($green == "a") {
            $table = "c";
        } else if ($green == "b") {
            $table = "a";
        } else if ($green == "c") {
            $table = "b";
        } else {
            $table = null;
        }
        return $table;
 }

三个表的R,G,B状态是通过当天日期动态计算所得。在删除数据或者读写数据时，以当天在月份中的天数来计算该使用哪个表来操作。
R：红色状态，当天已经过期的数据表，该表可删除了。
G：绿色状态，当天应用读写的数据表。
B：蓝色状态，当天应用要写的冗余数据表，如果当前G等于B，也就是同一个表；此时就不能写冗余数据，否则在蓝色状态表里写入相同的数据。冗余表的作用是当某天来临，该表由蓝色切换为绿色状态时，就已存储了前6天的数据，避免了多表查询，且让前一个表转换成红色状态后的数据可以删除。

$ret='';
for ($i = 1; $i <= 31; $i++) {//假设从1号到31号
            $g = getGreenTableName($prefix, $i);
            $b = getBlueTableName($prefix, $i, $g);
            $r = getRedTableName($prefix, $g);
            $ret. = "第".$i."天 R：" . $r . " G：" . $g." B：" . $b."<br/>";
}
echo $ret;

执行结果

平年二月28天
第1天 R：c G：a B：
第2天 R：c G：a B：
第3天 R：c G：a B：
第4天 R：c G：a B：
第5天 R：c G：a B：b
第6天 R：c G：a B：b
第7天 R：c G：a B：b
第8天 R：c G：a B：b
第9天 R：c G：a B：b
第10天 R：c G：a B：b
第11天 R：a_A G：b B：
第12天 R：a_A G：b B：
第13天 R：a_A G：b B：
第14天 R：a_A G：b B：
第15天 R：a_A G：b B：c
第16天 R：a_A G：b B：c
第17天 R：a_A G：b B：c
第18天 R：a_A G：b B：c
第19天 R：a_A G：b B：c
第20天 R：a_A G：b B：c
第21天 R：b G：c B：
第22天 R：b G：c B：
第23天 R：b G：c B：a
第24天 R：b G：c B：a
第25天 R：b G：c B：a
第26天 R：b G：c B：a
第27天 R：b G：c B：a
第28天 R：b G：c B：a

润年二月29天
第1天 R：c G：a B：
第2天 R：c G：a B：
第3天 R：c G：a B：
第4天 R：c G：a B：
第5天 R：c G：a B：b
第6天 R：c G：a B：b
第7天 R：c G：a B：b
第8天 R：c G：a B：b
第9天 R：c G：a B：b
第10天 R：c G：a B：b
第11天 R：a_A G：b B：
第12天 R：a_A G：b B：
第13天 R：a_A G：b B：
第14天 R：a_A G：b B：
第15天 R：a_A G：b B：c
第16天 R：a_A G：b B：c
第17天 R：a_A G：b B：c
第18天 R：a_A G：b B：c
第19天 R：a_A G：b B：c
第20天 R：a_A G：b B：c
第21天 R：b G：c B：
第22天 R：b G：c B：
第23天 R：b G：c B：
第24天 R：b G：c B：a
第25天 R：b G：c B：a
第26天 R：b G：c B：a
第27天 R：b G：c B：a
第28天 R：b G：c B：a
第29天 R：b G：c B：a

小月
第1天 R：c G：a B：
第2天 R：c G：a B：
第3天 R：c G：a B：
第4天 R：c G：a B：
第5天 R：c G：a B：b
第6天 R：c G：a B：b
第7天 R：c G：a B：b
第8天 R：c G：a B：b
第9天 R：c G：a B：b
第10天 R：c G：a B：b
第11天 R：a_A G：b B：
第12天 R：a_A G：b B：
第13天 R：a_A G：b B：
第14天 R：a_A G：b B：
第15天 R：a_A G：b B：c
第16天 R：a_A G：b B：c
第17天 R：a_A G：b B：c
第18天 R：a_A G：b B：c
第19天 R：a_A G：b B：c
第20天 R：a_A G：b B：c
第21天 R：b G：c B：
第22天 R：b G：c B：
第23天 R：b G：c B：
第24天 R：b G：c B：
第25天 R：b G：c B：a
第26天 R：b G：c B：a
第27天 R：b G：c B：a
第28天 R：b G：c B：a
第29天 R：b G：c B：a
第30天 R：b G：c B：a

大月
第1天 R：c G：a B：
第2天 R：c G：a B：
第3天 R：c G：a B：
第4天 R：c G：a B：
第5天 R：c G：a B：b
第6天 R：c G：a B：b
第7天 R：c G：a B：b
第8天 R：c G：a B：b
第9天 R：c G：a B：b
第10天 R：c G：a B：b
第11天 R：a_A G：b B：
第12天 R：a_A G：b B：
第13天 R：a_A G：b B：
第14天 R：a_A G：b B：
第15天 R：a_A G：b B：c
第16天 R：a_A G：b B：c
第17天 R：a_A G：b B：c
第18天 R：a_A G：b B：c
第19天 R：a_A G：b B：c
第20天 R：a_A G：b B：c
第21天 R：b G：c B：
第22天 R：b G：c B：
第23天 R：b G：c B：
第24天 R：b G：c B：
第25天 R：b G：c B：
第26天 R：b G：c B：a
第27天 R：b G：c B：a
第28天 R：b G：c B：a
第29天 R：b G：c B：a
第30天 R：b G：c B：a
第31天 R：b G：c B：a

小柏笔记

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
三表轮换实现清理MySQL大表数据

一个应用的日志表，假设每天十多万条数据落库，应用中仅需要查询其最近七天的数据来计算报表，这种情况下，超过了七天的数据就是过期数据，如果要快速删除这种过期数据就不能用DELETE来操作了。冗余表的作用是当某天来临，该表由蓝色切换为绿色状态时，就已存储了前6天的数据，避免了多表查询，且让前一个表转换成红色状态后的数据可以删除。将原来的数据表按月的天数动态拆分为A,B,C 三个表，这三个表按月份中的天数轮流切换写，因此过期的数据就自动隔离在过期的数据表里了，只要删除表即删除了过期数据。
复制链接

扫一扫