加索引仍被锁住是因为普通索引未覆盖查询字段，导致回表引发更长行锁或间隙锁；真正有效的覆盖索引需包含WHERE、ORDER BY和SELECT所有字段，且须通过EXPLAIN确认Extra为“Using index”。
为什么加了索引还是被锁住？
因为普通索引不覆盖查询字段，MySQL 在 WHERE 条件走索引后，仍要回表查 SELECT 的列——这个过程会触发更长的行锁（甚至升级为间隙锁），尤其在 UPDATE 或 SELECT ... FOR UPDATE 场景下特别明显。
常见错误现象：SHOW ENGINE INNODB STATUS 里看到大量 lock_mode X locks rec but not gap waiting，同时 EXPLAIN 显示 type = ref 但 Extra 有 Using index condition; Using where; Using filesort 或干脆没写 Using index。
- 覆盖索引必须包含
WHERE、ORDER BY、SELECT所有字段，缺一个就可能回表 - 联合索引顺序很重要：等值条件字段放最左，范围条件（如
>、BETWEEN）放右，SELECT列补在最后 - 注意隐式类型转换：比如
user_id是VARCHAR，但查询用WHERE user_id = 123（整数），会导致索引失效，锁范围扩大
怎么写出真正有效的覆盖索引？
不是“把所有字段塞进索引”就行。MySQL 的 B+ 树索引只在叶子节点存完整索引列值，覆盖索引的本质是让优化器能从这棵树上直接拿到全部需要的数据，不碰聚簇索引。
使用场景举例：高频执行的 SELECT status, created_at FROM orders WHERE user_id = ? AND status IN (?, ?) ORDER BY created_at DESC LIMIT 20，且常被 UPDATE ... WHERE user_id = ? AND status = ? 并发修改。
- 正确建法：
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at) INCLUDE (status, created_at)—— 但注意 MySQL 8.0.13+ 才支持INCLUDE；老版本得写成(user_id, status, created_at, status, created_at)（重复列无害，但冗余） - 更稳妥的老版本写法：
(user_id, status, created_at)—— 因为SELECT只要这两个字段，且WHERE和ORDER BY都命中了 - 别把
TEXT或超长VARCHAR放进索引：会截断或直接报错，且大幅增加 B+ 树层级，反而拖慢锁获取速度
哪些情况覆盖索引也救不了锁竞争？
覆盖索引能减少扫描和锁粒度，但不能消除锁本身。当多个事务操作同一索引范围时，冲突照旧发生。
典型错误现象：QPS 不高，但 innodb_row_lock_waits 持续上涨，EXPLAIN 显示用了覆盖索引，SHOW PROFILE 却卡在 statistics 或 locking 阶段。
-
IN子句参数过多（如WHERE id IN (1,2,3,...,2000)）会让优化器放弃使用索引，退化为全索引扫描 → 锁住整个索引范围 - 使用函数或表达式：如
WHERE DATE(created_at) = '2024-01-01'，即使created_at有索引，也无法走覆盖路径 - 事务中混用
SELECT ... FOR UPDATE和非覆盖查询：前一句走了覆盖索引，后一句查了没索引的字段，锁就从单行扩展到多行甚至间隙
验证是否真的“覆盖”了？
别信 EXPLAIN 里出现 key 就万事大吉。关键看 Extra 字段有没有 Using index —— 它表示“只用索引树，不回表”，这才是覆盖索引生效的铁证。
实操建议：
- 执行
EXPLAIN FORMAT=TRADITIONAL SELECT status, created_at FROM orders WHERE user_id = 123 AND status = 'paid'，确认Extra是Using index，不是Using where; Using index（后者可能仍需过滤） - 在测试库开两个会话：一个跑
SELECT ... FOR UPDATE，另一个立刻UPDATE同一主键 → 如果秒级返回而非等待，说明锁范围确实缩小了 - 用
SELECT * FROM performance_schema.data_locks查当前锁记录，对比加覆盖索引前后LOCK_DATA的值是否从多行变成单行或更窄范围
最容易被忽略的是：覆盖索引对 INSERT ... ON DUPLICATE KEY UPDATE 无效，因为唯一键冲突检测仍需访问聚簇索引，这时候锁行为和没索引几乎一样。
