如何实现SQL定时删除过期历史数据_使用分区表与定时任务-SQL-PHP中文网

直接delete from history_table where create_time

为什么直接 DELETE FROM history_table WHERE create_time

大表上执行全表扫描 + 行级删除，会持续占用大量 I/O、锁住表（尤其在 MySQL 的 InnoDB 下可能触发 gap lock），还容易导致主从延迟飙升。更糟的是，如果没加索引或索引失效，WHERE create_time 会变成全表扫描，几千万行的数据删几个小时都完不了。

实操建议：

确认 create_time 字段有有效索引（且类型匹配，比如 DATETIME 列别用字符串比较）

避免在高并发写入期间执行大范围 DELETE；改用分区裁剪 + DROP PARTITION

先用 SELECT COUNT(*) 验证条件命中行数，再决定是否走删除逻辑

MySQL 8.0+ 如何按月建 RANGE 分区并自动清理

MySQL 支持按 DATE 或 DATETIME 列做 RANGE 分区，只要分区键是单调递增的（如时间戳），就能让过期分区“物理隔离”，DROP PARTITION 是瞬时操作，不走行级删除逻辑。

示例：假设表结构含 created_at DATETIME，建表时指定分区：
CREATE TABLE history_log (
  id BIGINT PRIMARY KEY,
  content TEXT,
  created_at DATETIME NOT NULL
) PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
  PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
  PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);
关键点：

TO_DAYS() 比直接用 DATETIME 做分区键更稳定（避免时区/精度问题）

必须保留一个 p_future 分区承接新数据，否则插入会失败

新增分区需手动 ALTER TABLE ... ADD PARTITION，不能自动创建

删除过期分区用 ALTER TABLE history_log DROP PARTITION p202301，毫秒级完成

如何用系统定时任务（cron）安全触发分区清理

不要把清理逻辑写进应用代码里——一旦应用重启或部署，就可能漏掉某次清理。用系统级 cron 更可靠，但要注意权限、路径和错误捕获。

推荐做法：写一个轻量 shell 脚本封装 MySQL 命令，再由 cron 每月初调用：
#!/bin/bash
# /opt/scripts/clean_history_partition.sh
DB_NAME="myapp"
TABLE_NAME="history_log"
EXPIRE_MONTHS=6
MYSQL_CMD="mysql -u clean_user -p'xxx' -D $DB_NAME"
<h1>计算要删的分区名（格式 pYYYYMM）</h1><p>TO_DROP=$(date -d "$EXPIRE_MONTHS months ago" +p%Y%m)</p><p>if $MYSQL_CMD -e "SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='$DB_NAME' AND TABLE_NAME='$TABLE_NAME' AND PARTITION_NAME='$TO_DROP';" | grep -q "$TO_DROP"; then
$MYSQL_CMD -e "ALTER TABLE $TABLE_NAME DROP PARTITION $TO_DROP;"
echo "$(date): dropped partition $TO_DROP" >> /var/log/clean_partition.log
else
echo "$(date): partition $TO_DROP not found" >> /var/log/clean_partition.log
fi</p>
注意：

MySQL 用户 clean_user 只需对目标表有 ALTER 权限，**不要给 SUPER 或 FILE 权限**

密码写在命令行里不安全，建议用 ~/.my.cnf 配置文件 + chmod 600

务必验证分区是否存在再执行 DROP，否则语句失败会导致 cron 后续不重试

日志要记录成功/失败，方便排查“明明设了 cron 却没删”的问题

PostgreSQL 用户别硬套 MySQL 分区语法

PostgreSQL 10+ 的原生分区是 PARTITION BY RANGE (created_at)，但语法和行为差异很大：它不支持 DROP PARTITION，而是用 DROP TABLE 删除子表，且子表名不是你定义的字符串，而是自动生成的（如 history_log_1）。更关键的是，PostgreSQL 的分区裁剪依赖查询条件能被 planner 精确识别，WHERE created_at 必须用常量，变量参数（如 prepared statement 中的 $1）可能无法裁剪。

实操要点：

建分区表后，用 \d+ history_log 查看实际子表名，再写清理脚本

清理时先 TRUNCATE 子表确保无外键引用，再 DROP TABLE

定期运行 VACUUM 不够，必须靠 DROP TABLE 才能真正释放磁盘空间

如果用 pg_partman 扩展，它会自动管理子表和定时任务，但得额外部署和维护该扩展

分区表不是银弹——表结构变更（如加字段）需要同步到所有子表或模板表，而跨分区的 UPDATE 或 JOIN 可能比预想中慢。最常被忽略的一点是：分区键必须出现在每个查询的 WHERE 条件里，否则 planner 会扫描全部分区，这时候你以为的“高效删除”，其实连带拖垮了所有查询。