mysql中执行UPDATE与DELETE语句的流程与优化_网络技术

UPDATE执行时先定位、加锁、写日志、再更新数据页，受引擎、索引和隔离级别影响；未走索引易致锁升级甚至全表扫描。

MySQL 执行 UPDATE 不是简单地“改一行”，而是先定位、再加锁、再写日志、最后更新数据页。整个过程受存储引擎、索引、事务隔离级别共同影响。

常见错误现象：UPDATE 卡住、被阻塞、甚至触发全表扫描导致锁表——往往是因为没走索引或 WHERE 条件不精确。

DELETE 不仅要查数据，还要释放空间、维护索引、生成 undo/redo 日志，并可能触发外键检查与触发器。InnoDB 中删除不是物理擦除，而是标记为“可复用”，后续插入才可能覆盖。

典型问题：大表 DELETE 耗时长、磁盘 I/O 飙升、主从延迟加剧。

永远不要在没有 WHERE 的 DELETE FROM t 上操作大表；清空用 TRUNCATE TABLE t（但注意它会重置自增计数器且不可回滚）
分批删除更安全：
```
DELETE FROM orders WHERE status = 'cancelled' ORDER BY id LIMIT 1000;
```
配合循环执行，每次提交事务，避免长事务拖慢 MVCC
确认是否真需要删除：归档旧数据到历史表（INSERT INTO archive_orders SELECT ... + DELETE）通常比直接删更可控
删除后若空间未回收，可能是 innodb_file_per_table = OFF 或未执行 OPTIMIZE TABLE（但该操作会锁表，生产慎用）

别猜，用 EXPLAIN 看执行计划。重点看 type、key、rows 和 Extra 字段。

type = ALL 表示全表扫描，危险信号
key = NULL 表示没用上索引
rows 值远大于实际匹配行数，说明索引选择性差或统计信息过期（可运行 ANALYZE TABLE t 更新）
Extra 出现 Using where; Using index condition 是理想状态；出现 Using filesort 或 Using temporary 则说明语句结构可能诱发额外开销

注意：对 UPDATE 和 DELETE 使用 EXPLAIN 时，MySQL 5.6+ 支持直接解释（如 EXPLAIN UPDATE ...），低版本需改写为等价 SELECT 分析。

InnoDB 对 UPDATE 和 DELETE 默认加 next-key lock（记录锁 + 间隙锁），目的是防止幻读。但两者的锁范围和持续时间不同。

UPDATE 只锁满足 WHERE 条件的行（及对应间隙），但如果更新了索引列，还可能触发二级索引记录的锁升级
DELETE 同样锁匹配行，但因涉及索引树结构调整，锁持有时间略长，尤其在唯一索引冲突检测时可能短暂升级为意向锁等待
显式加锁（SELECT ... FOR UPDATE）后再 UPDATE，比直接 UPDATE 更容易暴露死锁，因为前者提前占锁，后者在执行路径中才加锁
避免在事务中混合 UPDATE 和 DELETE 操作同一张表的不同子集，极易因锁顺序不一致引发死锁

真正难调的从来不是语法对不对，而是锁怎么加、什么时候放、谁在等谁——这些细节藏在 INFORMATION_SCHEMA.INNODB_TRX 和 SHOW ENGINE INNODB STATUS 里。