17370845950

MySQL执行DELETE需经历解析、权限校验、加锁、查记录、写redo/undo日志、标记删除、提交事务；InnoDB默认加next-key lock防幻读，索引类型影响锁粒度，删除不释放磁盘空间，需ALTER TABLE或分批删除。

MySQL 执行 DELETE 语句时到底发生了什么

不是简单删掉几行数据就完事。MySQL 会走一套完整流程：解析 SQL → 检查权限 → 加锁 → 查找记录 → 写 redo log 和 undo log → 真正标记删除（或物理清除）→ 提交事务。这个过程直接受存储引擎影响，InnoDB 和 MyISAM 行为差异极大，而绝大多数生产环境用的是 InnoDB。

InnoDB 下 DELETE 的加锁行为与常见死锁场景

InnoDB 默认在 DELETE 时对匹配的**聚簇索引记录加 next-key lock（间隙锁 + 记录锁）**，目的是防止幻读。这意味着即使你只删一条，也可能锁住一个范围。

• 没走索引？全表扫描 → 锁所有行 + 所有间隙 → 极易阻塞甚至死锁
• 条件字段有索引但不是唯一索引？比如 DELETE FROM t WHERE status = 1，会锁住所有 status = 1 的记录及其前后间隙
• 用主键或唯一索引精确删除（如 DELETE FROM t WHERE id = 100）→ 只加 record lock，不加间隙锁，锁粒度最小
• 执行前用 EXPLAIN 确认是否命中索引，否则先优化查询条件或补索引

DELETE 后磁盘空间为什么没释放

InnoDB 不会立即归还磁盘空间给操作系统。删除只是把记录标记为“已删除”，空间保留在页内供后续插入复用；只有当整页都空了，才可能被加入 FSEG_FREE 链表，但不会自动 shrink 数据文件（.ibd）。

• 想真正回收空间，必须重建表：ALTER TABLE t ENGINE=InnoDB 或 OPTIMIZE TABLE t
• 线上大表慎用 OPTIMIZE TABLE，它会锁表（8.0+ 支持 online DDL，但仍需注意 copy table 阶段 I/O 压力）
• 如果只是批量删旧数据，建议按主键分批删（如 WHERE id BETWEEN ? AND ?），并配合 sleep 控制节奏，避免长事务和日志暴涨

如何安全地执行大表 DELETE 避免拖垮数据库

直接 DELETE FROM huge_table WHERE create_time 很可能触发锁升级、undo log 膨胀、binlog 巨大、从库延迟飙升。

• 务必在低峰期操作，提前确认 binlog_format 是 ROW（否则可能主从不一致）
• 用小批量 + 循环方式，每次删 1000~5000 行，用 SELECT ... FOR UPDATE 先定位再删，避免重复扫描
• 监控 SHOW ENGINE INNODB STATUS 中的 TRANSACTIONS 和 LOG 部分，观察 undo log size 和活跃事务数
• 删除后及时 ANALYZE TABLE 更新统计信息，避免后续查询走错执行计划

DELETE FROM orders 
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM orders 
    WHERE create_time < '2022-01-01' 
    ORDER BY id 
    LIMIT 1000
  ) AS tmp
);

注意：MySQL 5.7+ 对这种子查询写法做了优化，但 5.6 及更早版本会报错 “You can’t specify target table for update in FROM clause”，得改用临时表中转。

最常被忽略的一点：DELETE 触发器、外键级联、FULLTEXT 索引更新、二级索引条目清理——这些都会隐式增加开销，尤其是外键多且未建索引时，删父表一行可能引发子表全表扫描。