大数据量下 delete 无法命中索引问题

一、现象

一张千万级数据表，按 create_time 删除数据，明明 create_time 有索引，但 EXPLAIN 显示是全表扫描，删除极慢：

DELETE FROM t_order WHERE create_time < '2024-01-01';

二、为什么没走索引

1. 优化器认为走索引更慢

MySQL 优化器基于成本估算。如果要删除的行数占整张表比例很大（比如超过 20%~30%），优化器认为：

• 走索引：要回表大量数据，随机 IO。
• 全表扫描：顺序 IO 扫一遍。

它会选择全表扫描。

2. 隐式类型转换

字段是 varchar，SQL 传了数字：

DELETE FROM t WHERE phone = 13800001111;  -- phone 是 varchar

等价于 WHERE CAST(phone AS DECIMAL) = 13800001111，索引失效。

3. 函数 / 运算包裹列

DELETE FROM t WHERE DATE(create_time) < '2024-01-01';
DELETE FROM t WHERE id + 1 = 100;

4. 前导列失效

联合索引 (a, b, c)，WHERE b = ? 不走索引。

5. != / NOT IN / OR 可能导致全表扫描

三、解决方案

方案 1：分批删除

不要一次删几十万行，按主键分批：

-- 每次删 1000 条
DELETE FROM t_order
WHERE create_time < '2024-01-01'
ORDER BY id
LIMIT 1000;
-- 循环执行，直到 affected_rows = 0

这样每次走主键索引，且事务小、锁时间短。

方案 2：强制走索引

DELETE FROM t_order FORCE INDEX (idx_create_time)
WHERE create_time < '2024-01-01';

但如果数据量真的很大，强制走索引未必更快。

3：业务上避免大删除

• 历史数据归档：定期把老数据搬到历史库。
• 按时间分区表：PARTITION BY RANGE (TO_DAYS(create_time))，直接 DROP PARTITION，秒级完成。

ALTER TABLE t_order DROP PARTITION p2023;

四、为什么不建议一次大 delete

1. 产生大量 undo log，主从延迟。
2. 锁范围大，阻塞正常业务。
3. Buffer Pool 被污染：大量被删页从磁盘读进来，挤出热点数据。
4. 主从复制延迟：row 格式 Binlog 传输大量行变更。

生产建议

• 千万级以上的清理，首选分区表 DROP PARTITION 或归档表。
• 必须 delete 时，按主键分批 + 限速（每批 sleep 几百毫秒）。
• 业务低峰期执行。