索引失效时UPDATE/DELETE会扩大锁范围
MySQL 索引失效时，UPDATE/DELETE 确实会扩大锁范围
答案是肯定的。当 WHERE 子句中的查询条件无法有效命中索引时，InnoDB 存储引擎将无法精确定位到目标数据行。此时，引擎会退而求其次，转而采用全表扫描或全索引扫描的方式来查找数据。问题的核心在于这个“扫描”过程：引擎会对它扫描到的每一行聚簇索引记录都施加临键锁或间隙锁——即使最终只修改或删除其中一行数据。
这意味着什么？锁定的范围将从“精准锁定”演变为“范围锁定”。原本可能只锁定单行记录，现在却可能锁定数百上千行，甚至整个索引区间。这种锁范围的急剧扩张，正是线上高并发场景下更新阻塞、死锁频发的常见根源。
- 哪些操作容易触发？ 典型场景包括：使用左模糊匹配（如
WHERE name LIKE '%abc'）、在索引列上进行函数运算或隐式类型转换（如WHERE status + 0 = 1），或者查询条件与复合索引的列顺序不匹配（例如，查询条件是WHERE created_at > '2024-01-01' AND deleted = 0，但索引是(deleted, created_at)）。 - 如何验证锁范围？ 可以通过执行
SHOW ENGINE INNODB STATUS\G命令，重点关注输出结果中TRANSACTIONS部分的lock_mode和lock_trx_id信息，并结合LOCK WAIT或RECORD LOCKS部分展示的锁定行数进行综合判断。 - 一个关键提醒： 不仅仅是
UPDATE和DELETE语句，即便是SELECT ... FOR UPDATE这类锁定读操作，如果未能有效利用索引，同样会导致锁范围扩大。
如何快速确认 WHERE 条件是否走索引
不要依赖猜测，最可靠的方法是分析 SQL 执行计划。使用 EXPLAIN 命令，并重点关注以下几个关键字段：
type字段： 如果显示为ALL（全表扫描）或index（全索引扫描），通常意味着没有有效利用索引进行行检索。key字段： 如果显示为NULL，则表明本次查询没有使用任何索引。rows字段： 这个预估的扫描行数，如果远大于实际匹配的行数，通常暗示索引选择性不佳或索引根本未生效。Extra字段： 这里出现Using filesort或Using temporary不一定直接等同于索引失效，但它们往往是伴随低效扫描出现的信号。反之，如果看到Using index condition，则是个积极信号，说明索引条件下推（ICP）优化已生效。
缩小锁范围的关键操作：让 WHERE 精准命中索引
核心思路并非盲目添加索引，而是要让查询条件能够贴合现有索引的结构——即严格遵循“最左前缀匹配原则”，并优先使用等值匹配条件，再配合范围查询。
- 避免在索引列上做计算或函数操作： 像
WHERE YEAR(create_time) = 2024这样的写法会导致索引失效。应优化为WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'。 - 杜绝隐式类型转换： 如果
user_id字段定义为整型，那么WHERE user_id = '123'就会引发隐式转换，导致索引失效。应规范写为WHERE user_id = 123。 - 确保复合索引顺序匹配高频查询模式： 对于查询
WHERE a = 1 AND b > 10，最高效的索引顺序是(a, b)，而非(b, a)。如果查询中还包含ORDER BY c，则该索引无法用于排序，此时可考虑创建覆盖索引来满足查询需求。 - 慎用 LIKE 左模糊匹配：
name LIKE '%abc'这种写法必然导致索引失效。若业务允许，可尝试改为右模糊匹配（name LIKE 'abc%'），或考虑引入全文索引等更专业的模糊搜索方案。
锁范围还受事务隔离级别和语句类型影响
即便索引有效，在不同的隔离级别和语句类型下，InnoDB 的加锁行为也存在显著差异。例如，在读已提交（RC）隔离级别下，普通的 SELECT 语句不加锁，但 UPDATE/DELETE 仍会锁定命中的行。而在可重复读（RR）级别下，InnoDB 默认会使用临键锁，同时锁定记录本身及其前后的间隙。
- 在 RR 级别下：
- 执行
WHERE id = ?（基于主键的等值查询）通常只加记录锁，锁定单行。 - 执行
WHERE name = ?（基于非唯一索引的等值查询）则会加临键锁，锁定所有匹配该值的行以及相邻的间隙，这是为了防止“幻读”现象。
- 执行
- 在 RC 级别下： 上述语句通常只锁定命中的行，不锁间隙。但需注意，从 MySQL 8.0 开始，即使在 RC 级别下，对唯一索引的等值查询也可能加间隙锁以避免主从复制的不一致，具体行为需根据版本进行实测验证。
- 关于
SELECT ... FOR UPDATE： 即使语句走了索引，如果其中包含了ORDER BY或LIMIT子句也需要格外小心。例如，LIMIT 1并不会减少锁范围，InnoDB 仍然会扫描直到找到满足条件的第一行，在此过程中扫描过的所有记录都会被锁定。
归根结底，索引是否有效利用，直接决定了 InnoDB 的加锁策略是“精确行锁”还是“粗放范围锁”。许多线上棘手的死锁和更新性能瓶颈问题，根源往往不在于 SQL 语句本身有多复杂，而在于 WHERE 条件悄无声息地绕过了索引。养成良好习惯，在执行前用 EXPLAIN 分析一下执行计划，这应当像查看系统日志一样成为开发者的条件反射，而不是等到接口超时报警后才被动进行问题排查。
