Jayin Taung
Jayin

Jayin

MySQL数据库批量删除大量日志数据

Photo by Caspar Camille Rubin on Unsplash

MySQL数据库批量删除大量日志数据

场景:日志表每日产生约30W行数据数据,需要定时定理30日前的日志数据。

Jayin Taung's photo
Jayin Taung
·Jun 16, 2022·

1 min read

Subscribe to my newsletter and never miss my upcoming articles

Table of contents

  • 常见的做法
  • 频繁删数据影响

场景:日志表每日产生约30W行数据数据,需要定时定理30日前的日志数据。

常见的做法

1、直接执行 如下SQL

delete from table_name where create_time < 8888888

优点:数据量少的情况下可以接受

缺点:create_time不是索引时,导致全表扫描,锁表; 不限制删除数量不是个好习惯,非常耗时,IO会飙升

2、添加索引条件,减少扫描行,循环分批次删除

例如 SQL 可以简单优化,为create_time 添加索引;每次删除20个,这样可以减少行锁的范围

delete from table_name where create_time < 8888888 order by id  limit 20

实例

/**
 * 队列日志清理
 * 建议每日凌晨1:00执行一次
 */
class QueueCleanLogScript extends CronScript
{
    public function run($cronId)
    {
        // 日志保留天数
        $key_log_day = env('queue.keep_log_day', 7);
        $limit_time = strtotime(date('Y-m-d')) - $key_log_day * 24 * 60 * 60;
        $limit = 20;

        $total_delete = $delete_amount = ExecutedJobModel::where('create_time', '<', $limit_time)->limit($limit)->delete();

        while ($delete_amount > 0) {
            $delete_amount = ExecutedJobModel::where('create_time', '<', $limit_time)->limit($limit)->delete();
            $total_delete += $delete_amount;
        }

        return self::createReturn(true, [
            'total_delete' => $total_delete,
        ]);
    }
}

也可以根据实际业务场景增加更多的搜索条件,已减少搜索范围。例如每次删除时保留最后的操作时间last_time

delete from table_name where create_time < 8888888 and create_time > {$last_time}

3、日志按日期分表,删除时直接删除对应的表

drop table_xxx_20220101
drop table_xxx_20220102
.....

频繁删数据影响

  1. 产生大量碎片,影响磁盘IO
  2. 影响索引的基数 Cardinality 值,从而导致关联 sql 时使用不当的索引
  3. 如果数据库做主从同步,一次性 delete 大量数据,会出现主从同步延迟

问题1,2: 可以通过执行OPTIMIZE TABLE 表名来优化表,重新组织表数据和关联索引数据的物理存储

问题3:分批间隔几分钟删除,把大事务化成小事务去执行

 
Share this