怎么在MySQL中批量DELETE千万级历史数据才不会锁全表-mysql教程-PHP中文网

单条 DELETE 可能长时间阻塞，因 InnoDB 在 RR 级别下对扫描行/间隙加锁；无索引则全表扫描加锁，有索引删大量行也会持锁数十秒，且触发 undo/redo/binlog 膨胀。

怎么在mysql中批量delete千万级历史数据才不会锁全表

为什么单条 DELETE 会锁全表或长时间阻塞

不是“锁全表”，而是 InnoDB 在 RR 隔离级别下，对扫描到的每一条匹配行（甚至间隙）都加行锁或间隙锁。如果 WHERE 条件没走索引，就会全表扫描 + 全表加锁；就算走了索引，删几百万行 = 持有几百万个行锁几十秒到几分钟，其他事务一碰这张表就排队等待。

同时，每删一行都要写 undo log 和 redo log，大量删除会让 undo log 瞬间暴涨，binlog 也同步膨胀，磁盘 IO 和主从延迟都会恶化。

确认 WHERE 字段是否已建索引（用 EXPLAIN 看 type 是否为 range 或 ref，不是 ALL）
避免在业务高峰期执行，尤其别在凌晨自动任务里扔一个没限流的 DELETE FROM t WHERE create_time
别信“我加了索引就安全”——索引失效很常见（比如对字段做函数操作：DATE(create_time) ）

LIMIT 分批删除必须带 ORDER BY 主键

只写 DELETE ... LIMIT 1000 是危险的：InnoDB 不保证每次删的是“最早一批”，可能重复删、漏删，更糟的是，MySQL 优化器可能每次重新选执行计划，导致越删越慢（扫描范围越来越大）。

正确姿势是显式按主键推进，让每次扫描范围可控、可预测：

DELETE FROM orders 
WHERE create_time

OFFSET 要配合 ORDER BY id 才有效，否则 OFFSET 行为未定义
实际生产中更推荐用游标式推进：记下上一批删掉的最大 id，下一批从 id > last_max_id 开始查
LIMIT 值建议设为 500–5000，太小（如 100）会导致循环次数爆炸；太大（如 50000）仍可能触发长事务和锁堆积

用主键范围分批比 LIMIT 更稳

当你要删的数据在主键（比如自增 id）上天然连续或可预估区间时，直接按 id BETWEEN x AND y 切片，性能远超 LIMIT，因为不依赖全表扫描找“第 N 条”。

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

示例逻辑（应用层控制）：

start_id = 0
while True:
    cursor.execute("SELECT MAX(id) FROM orders WHERE create_time  %s", (cut_off, start_id))
    max_id = cursor.fetchone()[0]
    if not max_id: break
    end_id = min(start_id + 4999, max_id)  # 每批最多删 5000 行
    cursor.execute("DELETE FROM orders WHERE id BETWEEN %s AND %s AND create_time

必须确保 WHERE 中的非主键条件（如 create_time）也有索引，否则 BETWEEN 优势会被抵消
注意 id 不一定严格连续（比如有回滚、批量插入失败），所以不能假设 id=10000 之后就是 10001，要用 MAX(id) 动态探边界
如果主键是 UUID 或无序值，这条路基本走不通，优先考虑临时表或换表方案

删完不 OPTIMIZE TABLE 就等于没删干净

InnoDB 删除只是标记记录为“可复用”，物理空间不会立即归还给操作系统，data_length 不变，后续 INSERT 还可能复用这些空洞，但查询性能、备份体积、缓冲池效率都受影响。

但 OPTIMIZE TABLE 本身是锁表操作，且重建聚簇索引开销极大，千万级表可能要数小时。更稳妥的做法是：

分批删完后，用 ALTER TABLE ... ENGINE=InnoDB（MySQL 5.6+ 支持 online DDL）替代 OPTIMIZE，它支持并发读写
如果允许短时停写，直接 CREATE TABLE new_t AS SELECT * FROM old_t WHERE ... 留下要保留的数据，再原子替换（RENAME TABLE），这是最干净的“删旧留新”
千万别在大表上频繁跑 OPTIMIZE TABLE——它本质是 copy + rebuild，IO 和 CPU 压力双高

真正容易被忽略的点是：删完后没检查 information_schema.TABLES 里的 data_free，误以为空间已释放；也没监控 innodb_buffer_pool_pages_data 是否因碎片升高而挤占有效缓存。这些数字不看，优化就只是自我安慰。