MySQL UPDATE/DELETE 没走索引行锁升级为表锁怎么办？

Question

仓乃文 · Accepted Answer

InnoDB 的行锁是加在索引上的，如果 UPDATE/DELETE 的 WHERE 条件没有命中任何索引，InnoDB 无法定位到具体行，会对全表所有记录加临键锁，相当于锁全表。高并发下瞬间导致所有写操作排队，服务雪崩。

问题复现：
```sql
-- name 字段没有索引
UPDATE users SET status = 1 WHERE name = "Tom";
-- EXPLAIN 结果：type=ALL，全表扫描
-- InnoDB 对所有行加临键锁 → 等同于表锁
-- 其他事务更新任何行都会被阻塞！

-- 更危险的场景：大表删除
DELETE FROM logs WHERE create_time < "2024-01-01";
-- create_time 无索引 → 锁全表 → 所有写操作阻塞
```

排查方法：
```sql
-- 1. EXPLAIN 确认是否走索引
EXPLAIN UPDATE users SET status = 1 WHERE name = "Tom";
-- type=ALL → 未走索引，行锁升级为表锁

-- 2. 查看当前锁情况
SELECT * FROM performance_schema.data_locks;
-- lock_mode 为 X 但无具体索引记录 → 表锁

-- 3. 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;
```

修复方案：
```sql
-- 方案 1：给 WHERE 条件加索引（根本解决）
ALTER TABLE users ADD INDEX idx_name (name);
-- 加索引后 UPDATE 走索引，只锁命中的行

-- 方案 2：强制走索引
UPDATE users FORCE INDEX (idx_name)
SET status = 1 WHERE name = "Tom";

-- 方案 3：大表删除改为分批+索引
-- 先加索引
ALTER TABLE logs ADD INDEX idx_create_time (create_time);
-- 分批删除
DELETE FROM logs WHERE create_time < "2024-01-01"
ORDER BY id LIMIT 5000;
-- 循环执行直到 ROW_COUNT() = 0

-- 方案 4：用主键范围替代无索引条件
-- 先查出符合条件的 id
SELECT id FROM logs WHERE create_time < "2024-01-01";
-- 再按 id 批量删除
DELETE FROM logs WHERE id IN (...);
```

补充说明：
- RC 隔离级别下，未命中索引的行不会锁全表，只锁实际匹配的行（因为无间隙锁）
- RR 隔离级别下，未命中索引会锁全表所有记录的间隙
- 规则：UPDATE/DELETE 的 WHERE 条件必须有索引，上线前必须 EXPLAIN 验证
- 大表 DML 操作必须分批执行，单次不超过 5000 行