Jayin Taung
Jayin

Jayin

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

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

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

Subscribe to my newsletter and never miss my upcoming articles

Play this article

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
.....

其他方式

把这些市面上提及的方案合在统一在这里,说明不推荐使用,有点奇淫技巧的味道:

方式一

针对 MyISAM 表,可以采用增大mysql配置项key_buffer_size,例如128MB,删除时使用Delete QUICK table where ..., 由于 QUICK 参数使用后,存储引擎在删除过程中不会合并索引叶子, 实现了快速删除。

缺点: 只能针对 MyISAM 引擎,而我建议无脑使用 InnoDB 引擎。

方式二

使用分区(PARTITION)来存储数据,按照日期分区,删除时非常快。缺点也非常明显,分区是有上限,拓展性一般,其次分区的复杂度不亚于分表,宁愿分表呢。所以分区在大部分的情况下,不建议使用。

频繁删数据影响

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

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

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

参考:

 
Share this