SQL存储过程如何分批删除历史数据-SQL-PHP中文网

安全删除百万级大表数据必须分批delete，用where id > last_id order by id limit 1000–5000控制范围，每次删完显式commit并sleep(0.1)，避免锁表、日志膨胀和主从延迟。

sql存储过程如何分批删除历史数据

直接用 DELETE FROM table WHERE 清历史数据，90% 的线上事故都从这儿开始。它不是“慢”，而是会锁表、撑爆日志、拖垮主从、触发 Lock wait timeout exceeded ——尤其当表超 500 万行时，DBA 往往在你刚执行完就 kill 掉连接。

MySQL 存储过程里怎么写安全的 `DELETE` 循环

关键不是语法多复杂，而是每次只动一小块、删完立刻提交、游标能稳定推进。

必须用带索引的单调字段驱动（推荐 id 或 created_at），别依赖 OFFSET：它越往后越慢，且无法避免重复或漏删
每次 DELETE 后必须调 ROW_COUNT() 判断是否还有数据可删，不能硬写循环次数
显式加 COMMIT（或确保 autocommit = 1），否则整个 WHILE 被包在一个事务里，undo log 不释放
加 DO SLEEP(0.1) 让出 I/O，高负载库上这句不是可选，是保命
LIMIT 值设 1000–5000：SSD 环境可试 5000，HDD 或写入密集场景建议 1000–2000

示例骨架（id 主键 + created_at 有索引）：

TextCortex

一款集AI写作、改写和知识辅助于一体的内容创作工具，可帮助用户快速完成多种类型的文本撰写与编辑任务。

下载

DELIMITER $$
CREATE PROCEDURE batch_delete_old()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE last_id BIGINT DEFAULT 0;
  DECLARE batch_size INT DEFAULT 2000;
<p>WHILE NOT done DO
DELETE FROM logs 
WHERE id > last_id AND created_at </p><pre class="brush:php;toolbar:false;">IF ROW_COUNT()  last_id AND created_at <p>END WHILE;
END$$
DELIMITER ;</p>

PostgreSQL 为什么不用 `OFFSET` 而要用 `RETURNING`

PG 没有 MySQL 那种带 LIMIT 的非事务安全 DELETE，靠先 SELECT id 再 DELETE WHERE id IN () 容易因并发导致重复删或漏删。

必须基于索引字段（如 (created_at, id) 复合索引）做条件，否则 CTE 扫描成本爆炸
用 WITH batch AS (DELETE ... RETURNING id) 一次定位、一次删除、拿到结果继续下一批
别用 ctid 当游标：VACUUM 后失效；优先用业务主键或时间+自增组合
每批删完立刻 COMMIT，避免长事务阻塞 autovacuum

典型模式：

WITH batch AS (
  DELETE FROM logs 
  WHERE created_at <h3>WHERE 条件字段没索引，分批也没用</h3>
哪怕你把 <code>LIMIT</code> 设成 10，只要 <code>created_at</code> 没索引，每次 <code>DELETE</code> 前都要全表扫描——不仅慢，还可能让优化器误判，锁住大量无关行。