mysql如何通过创建索引减少锁竞争_mysql并发性能优化-mysql教程-PHP中文网

加索引能减少锁竞争，因为可避免全表扫描和大范围临键锁，使锁仅作用于目标行；适合建索引的字段是dml中where或on子句高频出现且选择性好的列，如(user_id, status)联合索引。

mysql如何通过创建索引减少锁竞争_mysql并发性能优化

为什么加索引能减少锁竞争

MySQL 在执行 UPDATE 或 DELETE 时，如果 WHERE 条件没走索引，会触发全表扫描，InnoDB 就不得不对所有扫描到的行（甚至间隙）加临键锁（Next-Key Lock），导致大量无关行被锁定。加了合适索引后，查询能快速定位目标行，锁范围收缩到极小——可能只锁住几行甚至一行，其他事务操作非冲突数据时几乎不受影响。

哪些字段适合建索引以缓解锁竞争

重点不是“业务上常用”，而是“DML 语句里高频出现在 WHERE 或 ON 子句中、且选择性较好”的字段。常见典型场景：

UPDATE orders SET status = 'shipped' WHERE user_id = ? AND status = 'paid' → 应在 (user_id, status) 上建联合索引，顺序不能颠倒（最左前缀原则）
DELETE FROM logs WHERE created_at → 单独为 <code>created_at 建索引即可，但要注意该字段更新频繁时索引维护开销
SELECT ... FOR UPDATE WHERE order_no = ? → order_no 必须有唯一索引或主键，否则会退化为范围锁

建索引时容易踩的坑

索引不是越多越好，尤其在高并发写入场景下：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

冗余索引（如已有 (a, b)，再单独建 a）会增加 INSERT/UPDATE/DELETE 的 B+ 树分裂和缓冲池压力
过长的字符串字段（如 VARCHAR(500)）直接建索引会导致页分裂加剧，建议用前缀索引（INDEX idx_name (content(100))），但需确认前 100 字节已具备足够区分度
在 TEXT 或 BLOB 字段上建普通索引会失败，必须指定前缀长度；若用于排序或分组，还要注意 max_sort_length 配置是否够用
联合索引字段顺序错误：把低选择性字段放前面（如 (is_deleted, create_time)），会导致范围查询无法有效截断，锁范围扩大

验证索引是否真正生效于锁优化

不能只看 EXPLAIN 显示 type=range 就认为锁减少了。要结合实际锁行为观察：

用 SELECT * FROM performance_schema.data_locks 查看当前事务持有的锁（MySQL 8.0+），确认锁行数是否与预期一致
开启 innodb_status_output 并定期查 SHOW ENGINE INNODB STATUS，关注 TRANSACTIONS 部分的 lock struct 数量和 lock mode
模拟并发 UPDATE，用 sys.innodb_lock_waits 视图定位阻塞源头，对比加索引前后等待次数和平均等待时间

真正起作用的索引，会让 UPDATE ... WHERE indexed_col = ? 只产生 RECORD LOCK，而不是混杂大量 GAP LOCK 或 LOCK WAIT；这点在唯一索引 vs 普通索引、等值查询 vs 范围查询之间差异极大，不能凭经验跳过验证。