MySQL 定時清理舊數據：保留最新 1000 條記錄的 PHP 腳本與 SQL 方案對比

MySQL 定時清理舊數據：保留最新 1000 條記錄的 PHP 腳本與 SQL 方案對比

日誌表、消息表需定期清理舊數據保留最新 N 條。本文對比 PHP 腳本循環刪除、單條 SQL 刪除、分區表自動過期三種方案，分析性能、鎖影響、維護成本，給出生產環境推薦做法。

方案一：PHP 腳本 (原文思路，不推薦生產)

<?php
mysql_connect("localhost","root","root");
mysql_select_db("test");
$limit = 1000;
$query = "SELECT `id` FROM `news`";
$result = mysql_query($query);
$num = mysql_num_rows($result);
if ($num > $limit) {
    $query = "SELECT `id` FROM `news` ORDER BY `id` DESC LIMIT " . $limit;
    $result = mysql_query($query);
    mysql_data_seek($result, $limit - 1);
    $data = mysql_fetch_array($result);
    $query = "DELETE FROM `news` WHERE `id` < '" . $data['id'] . "'";
    if (mysql_query($query)) {
        echo "刪除 " . ($num - $limit) . " 條，剩餘 " . $limit . " 條";
    }
} else {
    echo "不足 $limit 條，無需刪除";
}
?>

缺點：mysql_* 函數已棄用 (PHP 7 移除)；兩次全表掃描；大事務鎖表久；無事務保證；需外部調度 (crontab)。

方案二：單條 SQL (推薦中小表)

-- 保留最新 1000 條 (按 id 遞減，id 為自增主鍵)
DELETE FROM news
WHERE id < (
    SELECT id FROM (
        SELECT id FROM news ORDER BY id DESC LIMIT 1000
    ) AS t
    ORDER BY id ASC LIMIT 1
);

原理：子查詢取第 1000 大的 ID (最小保留 ID)，外層刪除小於該 ID 的行。MySQL 要求衍生表起別名 (AS t)。

優化：id 有主鍵索引，子查詢 LIMIT 極快；刪除走主鍵範圍刪除，效率高。

方案三：分區表自動過期 (推薦大表/高頻)

CREATE TABLE news (
    id BIGINT NOT NULL AUTO_INCREMENT,
    title VARCHAR(256),
    create_time DATETIME NOT NULL,
    PRIMARY KEY (id, create_time)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 定時掛載新分區、刪除舊分區 (秒級、無鎖、不產生碎片)
ALTER TABLE news DROP PARTITION p202301;
ALTER TABLE news ADD PARTITION (PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')));

優勢：DROP PARTITION 瞬間完成、不鎖表、不產生 Binlog 膨脹、磁盤空間即時釋放。適合時序數據 (日誌、監控、流水)。

方案對比總結

生產環境最佳實踐

1. 中小表：MySQL Event Scheduler 定時跑單條 SQL，或應用層定時任務調用。
2. 大表/時序數據：**強制使用分區表**，按天/月分區，定時 DROP PARTITION + ADD PARTITION。
3. 避免大事務：若單次刪除超 1 萬行，分批刪除 (每批 1000 行 LIMIT 循環)，避免長事務阻塞、Binlog 暴漲、回滾耗時。
4. 監控告警：監控表行數、磁盤佔用、刪除任務耗時/成功率。

測試表結構 (原文附件)

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `title` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;