mysql并发访问如何减少锁冲突_mysql设计优化方法

SELECT ... FOR UPDATE 在高并发下易锁表，因其在满足条件的行上加排他锁，若无索引则升级为表级锁或锁整个索引范围，且事务间更新顺序不一致易致死锁；应确保WHERE字段有有效索引、避免混合操作、缩短事务、优先用INSERT ... ON DUPLICATE KEY UPDATE，并根据读写比例选择合适隔离级别。

为什么 SELECT ... FOR UPDATE 在高并发下容易锁表？

本质是它会在满足条件的行上加排他锁（X锁），如果查询没走索引，InnoDB 会升级为表级锁或锁住整个索引范围；更常见的是多个事务按不同顺序更新同一组行，导致死锁或长时间等待。

• 必须确保 WHERE 条件字段有有效索引，否则会触发全表扫描+间隙锁
• 避免在事务中混合执行 SELECT ... FOR UPDATE 和非关联的 UPDATE，顺序不一致极易引发死锁
• 尽量缩短事务生命周期：查完立刻更新，不要在事务里做 HTTP 调用、日志写入等耗时操作

用 INSERT ... ON DUPLICATE KEY UPDATE 替代“先查后更”逻辑

典型场景如计数器、状态机流转、幂等插入——这类操作若拆成 SELECT + INSERT/UPDATE，在并发下必然出现竞态：两个事务同时查到旧值，都执行更新，最终只生效一次。

INSERT INTO user_login_log (user_id, login_time, ip)
VALUES (123, NOW(), '192.168.1.1')
ON DUPLICATE KEY UPDATE login_time = VALUES(login_time), ip = VALUES(ip);

• 前提是 user_id 或组合字段有唯一索引（如 UNIQUE KEY (user_id)）
• 注意 VALUES(col) 引用的是本次 INSERT 中该列的值，不是原记录值
• 不支持在 ON DUPLICATE KEY UPDATE 子句中使用子查询，复杂逻辑需另寻方案

读多写少场景下，用 READ COMMITTED 隔离级别降低锁粒度

MySQL 默认隔离级别 REPEATABLE READ 会启用间隙锁（Gap Lock）防止幻读，但代价是更大范围的锁冲突；而 READ COMMITTED 只对实际命中的记录加行锁，不锁间隙，适合订单查询、商品详情等以读为主、写频次低的业务。

• 修改方式：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 或在配置文件中设 transaction_isolation = 'READ-COMMITTED'
• 副作用：可能读到“不可重复读”，但多数 Web 应用对此不敏感
• 无法解决写-写冲突，仍需靠主键/唯一键约束 + 原子语句控制

分库分表前，先确认是不是索引和语句本身的问题

很多团队一遇到并发慢就想着分片，结果发现慢查询日志里全是 type: ALL 或 Extra: Using filesort —— 根本问题在缺失索引或 ORDER BY/GROUP BY 没利用索引。

• 用 EXPLAIN FORMAT=TRADITIONAL 看执行计划，重点关注 key、rows、Extra
• 复合索引要遵循最左匹配，比如 (a,b,c) 支持 WHERE a=1 AND b>2，但不支持 WHERE b=2
• pt-query-digest 分析慢日志比肉眼扫更可靠，尤其能暴露低频但高延迟的“隐形杀手”语句

真正需要分片的信号是单表超千万行且写入持续增长、二级索引维护成本明显上升，而不是单纯因为某条 UPDATE 偶尔锁了 200ms。