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
.....
频繁删数据影响
- 产生大量碎片,影响磁盘IO
- 影响索引的基数 Cardinality 值,从而导致关联 sql 时使用不当的索引
- 如果数据库做主从同步,一次性 delete 大量数据,会出现主从同步延迟
问题1,2: 可以通过执行OPTIMIZE TABLE 表名
来优化表,重新组织表数据和关联索引数据的物理存储
问题3:分批间隔几分钟删除,把大事务化成小事务去执行