如何在高并发场景下优化MySQL行锁竞争以提升系统QPS？

WHERE条件未走索引会导致行锁退化为表级竞争，引发锁扩大；必须确保WHERE命中最左匹配的唯一索引，配合短事务与合适隔离级别（如READ COMMITTED）才能有效避免。

WHERE条件没走索引 → 行锁退化成表级竞争

这不是慢查询，是锁扩大。哪怕你只更新id = 123这一行，只要WHERE没命中索引，InnoDB 就会全表扫描，对每行加意向锁甚至行锁——其他事务一来就排队等这把“本不该存在”的锁。

• 用EXPLAIN查type字段：出现ALL或index必须停手优化
• 避免隐式类型转换：WHERE user_id = '123'（user_id是INT）→ 改成WHERE user_id = 123
• 别用函数包裹索引字段：WHERE DATE(create_time) = '2026-05-01' → 改成WHERE create_time >= '2026-05-01' AND create_time
• 联合索引注意最左前缀：建了(status, category)，但只查WHERE category = 'A'，照样全表扫

SELECT FOR UPDATE / INSERT ON DUPLICATE KEY UPDATE 锁范围失控

这两个语句内部依赖快速定位。定位不准，就会在查找阶段误加大量间隙锁（Gap Lock），导致插入新记录也被阻塞，甚至死锁。

• SELECT * FROM orders WHERE order_no = 'ORD-123' FOR UPDATE：如果order_no有唯一索引 → 只加记录锁，安全
• SELECT * FROM orders WHERE user_id = 1001 FOR UPDATE：若user_id无索引 → 全表扫描 + 每行加锁 → 实际退化为表级竞争
• INSERT INTO orders (...) ON DUPLICATE KEY UPDATE ...：必须确保ON DUPLICATE KEY依赖的字段（如order_no）有唯一索引，否则查找阶段会锁住整个可能插入的间隙
• 多个唯一索引（如UNIQUE(email)和UNIQUE(phone)）并发冲突时，InnoDB 加锁顺序不一致，也可能死锁

热点行更新 → 单点锁排队成瓶颈

自增主键 + 聚簇索引 = 物理位置固定，没法靠数据分布摊薄压力。8000 QPS 全挤在UPDATE goods SET stock = stock - 1 WHERE id = 123这一行上，不是并行，是排队。

• 用 Redis 分片预扣减：比如按goods_id % 10拆成 10 个 key，DECRBY goods:123:shard_3 1原子操作，返回 ≥ 0 才放行
• MySQL 层只做最终落库：后台消费者从队列（如Redis Streams）按可控速率（如 500 QPS）批量执行UPDATE goods SET stock = stock - ? WHERE id = 123 AND stock >= ?
• 虚拟分片改造表结构：CREATE TABLE goods_stock_shard (goods_id INT, shard_id TINYINT, stock INT, PRIMARY KEY(goods_id, shard_id))，把单行热变成多行温
• 隔离级别降为READ COMMITTED：它不加间隙锁，死锁概率骤降；多数业务根本不需要REPEATABLE READ语义

事务边界与锁持有时间 → 长事务是锁滞留头号元凶

锁不是被抢走的，是被“占着不放”的。事务里调一次 HTTP、解析一段 JSON、甚至sleep(100)，X 锁就挂着不动，后面所有请求全卡住。

• 事务只包一行UPDATE + ROW_COUNT()检查，绝不在里面调外部接口、写日志文件或做复杂计算
• WHERE 条件必须命中**最左匹配的唯一索引**，用EXPLAIN看key和rows是硬指标
• 乐观锁（如version字段）只是把重试甩给应用层；高并发下重试风暴可能压垮服务本身，不适合秒杀库存、资金余额等强一致高频写场景
• 别迷信“加索引就万事大吉”——索引建错、写法不对、事务太长，三者漏一，锁竞争照旧

真正容易被忽略的是：你改完索引、调完参数、上了缓存，却忘了检查事务是否真的短小精悍，或者SELECT FOR UPDATE是否真落在唯一索引上。锁不会自己变聪明，它只忠实地执行你写的 SQL 和事务结构。