如何避免MySQL锁带来的性能问题

以下内容将围绕在实际项目中常见的 MySQL 锁所带来的性能问题，从底层原理、典型场景入手，结合代码示例、ASCII 图解和详细说明，并给出一系列实用的优化策略，帮助你在日常开发和运维中避开锁冲突、降低锁等待、提升并发性能。

1. 概述：MySQL 锁“坏朋友”与性能挑战

在 MySQL（以 InnoDB 引擎为例）中，为了保证事务的 ACID 特性，会对数据行或表加锁。常见锁类型包括：

记录锁（Record Lock）：针对索引上的单行数据加锁。
间隙锁（Gap Lock）：锁定索引记录之间的空隙，用于防止幻读。
临键锁（Next-key Lock）：记录锁 + 左侧间隙锁，用于在 REPEATABLE READ 隔离级别下防止幻读。
意向锁（Intention Lock）：在表级做标记，表示事务想要对表中某行加行锁。

由于并发场景下锁会串行化对同一资源的访问，一旦锁竞争激烈，就会带来锁等待、死锁、甚至吞吐量下降等一系列性能问题。

性能痛点总结
长事务持锁：拖慢后续事务，导致大量锁等待。
范围查询锁住大范围行：使用 FOR UPDATE 或大范围 UPDATE 时加了大量“临键锁/间隙锁”，阻塞其他插入或更新。
索引缺失导致全表锁或大范围锁：无索引或错误索引走全表扫描，锁范围放大。
隔离级别过高（如 REPEATABLE READ）：会加更多的间隙锁，导致写操作冲突。
死锁回滚开销：大量死锁导致事务不断被系统回滚、应用重试，严重浪费资源。

接下来，我们通过几个典型示例，分析锁冲突的具体成因，并给出对应的优化方案。

2. 常见锁冲突“重现”：代码演示与分析

下面通过一个最常见的“行锁冲突”场景，演示锁等待对性能的影响。

2.1 示例表与初始数据

-- 示例数据库与表结构（InnoDB 引擎）
CREATE DATABASE IF NOT EXISTS lock_demo;
USE lock_demo;

DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id        INT        PRIMARY KEY AUTO_INCREMENT,
    user_id   INT        NOT NULL,
    status    VARCHAR(20) NOT NULL,
    amount    DECIMAL(10,2),
    INDEX idx_user_status(user_id, status)
) ENGINE=InnoDB;

-- 插入 3 行样本数据
INSERT INTO orders (user_id, status, amount) VALUES
(100, 'pending',  99.99),
(100, 'shipped', 199.00),
(200, 'pending',  49.50);

此时 orders 表中共有三条订单记录，主键为 id，并在 (user_id, status) 上建立了复合索引。

2.2 场景：两会话并发更新相同 `user_id` 记录

会话 A（Session A）：

-- 会话 A
USE lock_demo;
START TRANSACTION;

-- Step A1：锁定 user_id=100 且 status='pending' 的行
SELECT * FROM orders
 WHERE user_id = 100 AND status = 'pending'
 FOR UPDATE;     -- 加上记录锁与临键锁
-- 这里会锁定 id=1 这一行（记录锁），并锁定 (user_id=100,status='pending') 对应的索引项。

会话 A 此时持有对 (user_id=100,status='pending') 的记录锁。
下游若要修改这行或对相同索引范围插入，都将被阻塞。

会话 B（Session B）：

-- 会话 B，不同终端
USE lock_demo;
START TRANSACTION;

-- Step B1：尝试更新相同条件的行
UPDATE orders
   SET amount = amount + 10
 WHERE user_id = 100 AND status = 'pending';
-- 由于 A 已经对该行加了记录锁，B 会在此处阻塞等待 A 提交或回滚。

此时会话 B 阻塞，直到 A 执行 COMMIT 或 ROLLBACK。如果 A 的事务逻辑很长（如在应用中有复杂计算或业务操作），B 可能长时间处于等待状态，造成延迟和吞吐率下降。

ASCII 图解：“行锁冲突”示意

Session A:                      Session B:
-----------                     -----------
START TRANSACTION;              START TRANSACTION;
SELECT ... FOR UPDATE   ──┐     UPDATE ...         ──┐
(锁定 idx_user_status)    │     (等待锁释放)       │
                          │                       │
-- 记录锁: orders.id=1 -- │                       │
(持有至 COMMIT)           │                       │
                          │                       │
                          └───────────────────────>│
                                                  │
-- 会话 B 阻塞在此处 -------------------------------┘

如果 A 事务持续时间很长，B 会一直在等待，严重时会导致应用线程阻塞积压。

3. 避免策略一：缩短事务时间与锁持有周期

3.1 原因

事务开启后，只要没提交（COMMIT）或回滚（ROLLBACK），InnoDB 持有的锁就不会释放。长事务在并发场景下最容易引发锁等待或死锁。

3.2 优化思路

只在必要时开启事务
- 在可拆分的业务逻辑中，尽量先做不需要锁的读操作，等到需要写时再开启事务。
事务逻辑尽量精简
- 避免在事务中进行用户交互、耗时计算、网络调用。
提前获取锁，快速执行数据库操作后立即提交
- 如果需要锁定行做一系列读取+判断+写操作，尽量在获取到锁后，马上完成相关 SQL 并提交，减少锁持有时间。

3.3 代码示例：对比“长事务”与“短事务”

不佳做法：长事务（容易造成锁等待）

-- 会话 A
USE lock_demo;
START TRANSACTION;

-- Step 1：查询业务数据
SELECT * FROM orders WHERE user_id=200 AND status='pending' FOR UPDATE;
-- (假设下游要调用远程接口或做大量计算)
-- ↓ 这里假装睡眠 10 秒，模拟复杂业务逻辑
SELECT SLEEP(10);

-- Step 2：更新数据
UPDATE orders
   SET status = 'completed'
 WHERE user_id = 200 AND status = 'pending';

COMMIT;

在 SLEEP(10) 期间，事务一直未提交，会阻塞其他对 user_id=200、status='pending' 相关的更新或插入。

改进做法：短事务（锁持有时间极短）

-- 会话 A
USE lock_demo;

-- Step 1：先进行不需锁的业务逻辑（如缓存读取、验证等）
-- （此时不在事务中，可并发执行，不影响其他人）

-- Step 2：真正需要更新时，才开启事务并快速提交
START TRANSACTION;
  -- 仅获取锁和更新操作
  SELECT * FROM orders WHERE user_id=200 AND status='pending' FOR UPDATE;
  UPDATE orders
     SET status = 'completed'
   WHERE user_id = 200 AND status = 'pending';
COMMIT;

此时“锁定→更新→提交”仅需要非常短时间，不会长时间阻塞其他事务。

4. 避免策略二：合理使用索引，避免全表扫描带来的大范围锁

4.1 原因

在 InnoDB 中，如果 WHERE 条件未命中索引，MySQL 可能进行全表扫描，会为每行加“临键锁/间隙锁”或隐式升级为“行锁→表锁”，导致锁范围非常大。
此时即使只想更新一两行，也会阻塞整张表的大批并发操作。

4.2 优化思路

为常用查询列创建合适的索引，让 InnoDB 精确定位要更新的记录。
审查慢查询日志，发现高耗时的 UPDATE/DELETE 语句，对应的 EXPLAIN 看有没有走索引。
避免在 WHERE 中对索引列进行函数运算或隐式类型转换，否则索引失效。

4.3 代码示例：索引 vs 无索引

假设我们想删除 status='canceled' 的所有老订单。

情况 A：无索引，导致全表扫描

-- 假设 orders 表没有索引在 status 上
EXPLAIN DELETE FROM orders WHERE status='canceled';

+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | DELETE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10000 |     10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+

type=ALL 表示全表扫描，其中 InnoDB 会对大范围行加“行锁”或“临键锁”，阻塞其它并发写。

情况 B：为 `status` 建立索引

ALTER TABLE orders ADD INDEX idx_status(status);

EXPLAIN DELETE FROM orders WHERE status='canceled';

+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | DELETE      | orders | NULL       | ref  | idx_status    | idx_status| 22      | const | 100  |   100.00 |       |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

type=ref 表示通过索引定位要删除的那 100 条行，只锁住这 100 行，不会锁住全表，极大减少锁冲突面。

5. 避免策略三：选择合适的隔离级别，减少“临键锁”带来的额外阻塞

5.1 BBarrier：隔离级别对锁行为的影响

隔离级别	主要特点	锁行为示例
READ UNCOMMITTED	允许脏读，极少行锁	SELECT … FOR UPDATE 会加记录锁，但普通读不加任何锁。
READ COMMITTED	只读取已提交数据，无幻读保障；每次查询都取最新数据	SELECT … FOR UPDATE 仅加记录锁，无临键锁，不锁范围之间的“间隙”。
REPEATABLE READ (默认)	保证同一事务内多次读取结果一致，防止幻读	SELECT … FOR UPDATE 加记录锁＋间隙锁，即“临键锁”，可产生较多范围锁。
SERIALIZABLE	提供完全串行化读写，性能最差	常用的 SELECT 会加 S-lock，基本所有读写会串行化，极易阻塞并发查询。

REPEATABLE READ 在 InnoDB 中，会对范围扫描的表加“临键锁”，防止幻读，但也带来更多写冲突。
如果业务允许“幻读”出现，可以将隔离级别调整为 READ COMMITTED，这样 InnoDB 对范围查询仅加记录锁，不加间隙锁，减少锁冲突。

5.2 代码示例：对比 REPEATABLE READ vs READ COMMITTED

5.2.1 REPEATABLE READ 下范围查询加临键锁

-- 会话 A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM orders WHERE user_id BETWEEN 100 AND 200 FOR UPDATE;
-- 此时对 user_id=100、200 及 (100,200) 间隙加“临键锁”。
-- 会阻塞其他并发插入 user_id=150 的操作。

-- 会话 B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
INSERT INTO orders (user_id,status,amount) VALUES (150,'pending',120.00);
-- B 在 (100,200) 区间插入，要等待 A 提交或回滚。

5.2.2 READ COMMITTED 下仅加记录锁

-- 会话 A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM orders WHERE user_id BETWEEN 100 AND 200 FOR UPDATE;
-- 仅锁住满足条件的现有行，比如 id=1、2，(100,200) 区间不加临键锁。
-- 允许其他人在 (100,200) 区间插入新行。

-- 会话 B
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
INSERT INTO orders (user_id,status,amount) VALUES (150,'pending',120.00);
-- 可以立即插入，因为 A 只锁了现有行，不锁间隙。

提示：切换隔离级别仅对当前会话生效，可通过程序在必要时动态调整。若全局改为 READ COMMITTED，要评估应用中是否依赖于 REPEATABLE READ 的幻读隔离保证。

6. 避免策略四：尽量使用乐观锁，减少悲观锁带来的锁等待

6.1 悲观锁 vs 乐观锁

悲观锁：通过显式 SELECT … FOR UPDATE、UPDATE、DELETE 等操作，让数据库层面加锁，确保修改不会被并发事务冲突。
乐观锁：不在数据库层面加锁，而是通过在行中维护版本号（或时间戳）字段，在更新时检查版本是否一致，若不一致则说明有并发更新冲突，需要重试或报错。

乐观锁适用于冲突概率较低、读多写少的场景，可以极大减少锁等待。

6.2 代码示例：使用版本号实现乐观锁

表结构：增加 `version` 列

ALTER TABLE orders 
  ADD COLUMN version INT NOT NULL DEFAULT 1;

A. 悲观锁示例

-- 会话 A：悲观锁
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 修改
UPDATE orders
   SET amount = amount + 10
 WHERE id = 1;
COMMIT;

此时其他事务在更新 id=1 前都会阻塞等待。

B. 乐观锁示例

读取数据并获取版本号

-- 会话 A
START TRANSACTION;
SELECT amount, version FROM orders WHERE id = 1;
-- 假设返回 amount=100, version=1

业务层计算新值，然后尝试更新时加上 WHERE version=?
```
-- 会话 A 计算出 new_amount=110, old_version=1
UPDATE orders
   SET amount = 110,
       version = version + 1
 WHERE id = 1 AND version = 1;
```
- 如果执行成功（影响行数 = 1），说明无并发冲突，可以 COMMIT。
- 如果执行影响行数 = 0，说明有人在此期间修改了该行，版本号已变；则抛出冲突异常，进行业务层重试或返回错误。
提交事务
```
COMMIT;
```

由于没有显式行锁，如果并发非常低，就不会产生任何锁等待；只有在真正冲突时，才回退一条更新并重试。

注意：乐观锁适合写少读多、低冲突场景。如果并发写冲突概率很高，可能频繁重试，反而降低性能；需要根据业务特点选择合适方案。

7. 避免策略五：批量操作拆分、分页更新或分区表减少锁冲突

7.1 原因

批量更新或删除大数据量时，MySQL 会一次性扫描并加锁大量行，导致其他事务长时间等待。
此时可以将大事务拆分成多个小批次来执行，每批只加锁一部分数据。

7.2 优化思路

分批分页更新
- 例如想把 status='pending' 的 100 万行订单都标记为 status='completed'，不要一次性 UPDATE orders SET status='completed' WHERE status='pending';
- 而应该用循环分页的方式，分批量小范围 UPDATE，每批执行完可使锁更快释放给其他事务。
分区表
- 根据某些列（如日期、用户 ID 等）做分区，让查询和更新只锁某个分区，减少对全表的锁冲突。

7.3 代码示例：分批分页更新

假设要将 status='pending' 的记录疫情批量更新为 status='completed'，每次处理 1000 条。

-- 步骤 A：获取总计待处理行数
SELECT COUNT(*) AS cnt FROM orders WHERE status='pending';

-- 步骤 B：分批处理
-- 在应用层用循环或脚本模拟
SET @batch_size = 1000;
SET @offset = 0;

-- 伪代码循环逻辑（可用存储过程或应用脚本实现）
-- while true:
--   rows = SELECT id FROM orders WHERE status='pending' LIMIT @offset, @batch_size;
--   if rows is empty: break
--   START TRANSACTION;
--     UPDATE orders
--       SET status = 'completed'
--     WHERE id IN (rows);
--   COMMIT;
--   SET @offset = @offset + @batch_size;
-- end while

每次只锁定 1000 条 id，马上提交后释放锁，让其他事务能插入、更新不相关 status 的行。

如果采用空分页 LIMIT @offset,@batch_size 随着 @offset 变大效率会大幅下降，可改为用“主键增量”方式分页：

-- 用上一轮更新的最大 id 作为游标，避免 OFFSET 大量跳过
SET @last_id = 0;
WHILE 1=1 DO
  SELECT id INTO @id_list
    FROM orders
   WHERE status='pending' AND id > @last_id
   ORDER BY id
   LIMIT @batch_size;
  IF @id_list IS NULL THEN
     LEAVE;
  END IF;
  -- 更新这批 id
  START TRANSACTION;
    UPDATE orders
       SET status='completed'
     WHERE id IN (@id_list);
  COMMIT;
  -- 取出本批最大 id
  SET @last_id = MAX(@id_list);
END WHILE;

7.4 代码示例：分区表减少锁范围

假设 orders 表按月做 RANGE 分区，以 order_date 为分区键（需先在表中加 order_date 字段，以下仅示例分区语法）：

CREATE TABLE orders (
    id         INT        PRIMARY KEY AUTO_INCREMENT,
    user_id    INT        NOT NULL,
    status     VARCHAR(20) NOT NULL,
    amount     DECIMAL(10,2),
    order_date DATE       NOT NULL,
    INDEX idx_user_status(user_id, status),
    INDEX idx_date(order_date)
) ENGINE=InnoDB
PARTITION BY RANGE( YEAR(order_date) ) (
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

当执行 UPDATE orders SET status='expired' WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31'; 时，仅锁 p2021 分区中的行，不会触及 p2019、p2020 等。
极大减少锁冲突面，其他分区的并发操作不会阻塞。

8. 监控与诊断：及时发现锁等待与死锁

8.1 SHOW ENGINE INNODB STATUS 用法

SHOW ENGINE INNODB STATUS\G

查看输出中的 LATEST DETECTED DEADLOCK 段，可定位最近一次死锁的详细信息，包括哪些事务、哪些锁、SQL 语句等。
在 TRANSACTIONS 段中可看到当前正在等待的锁、锁持有者、等待时间等信息。

8.2 performance\_schema 和 INFORMATION\_SCHEMA

INFORMATION\_SCHEMA.INNODB\_LOCKS：当前 InnoDB 锁清单，包含锁类型、表、索引、锁模式等。
INFORMATION\_SCHEMA.INNODB\_LOCK\_WAITS：当前锁等待图，表示哪个事务在等待哪个锁。

-- 查看当前所有锁
SELECT * FROM information_schema.INNODB_LOCKS\G

-- 查看锁等待关系
SELECT * FROM information_schema.INNODB_LOCK_WAITS\G

将这些信息与 performance_schema 中的线程、事务信息结合，可绘制出当前锁等待链，帮助快速定位冲突热点。

8.3 查询锁等待次数与死锁总数

-- 查看系统累计死锁次数
SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';
-- 查看当前锁等待次数
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';

如果 Innodb_deadlocks 数值持续上升，说明系统中死锁频繁，应结合应用逻辑和索引设计进行排查。
Innodb_row_lock_waits 代表因行锁等待导致的睡眠次数，可作为锁冲突的指标。

9. 小结与最佳实践清单

9.1 核心结论

锁是一把双刃剑：保证数据一致性与隔离性的同时，也会对并发性能带来成本。
长事务与大范围查询 是锁争用和死锁的主要“罪魁祸首”。
合理索引、短事务、合适隔离级别、乐观锁、分批分区 等是避开锁性能问题的主流手段。
监控与诊断 是保证数据库健康的常态化运维操作，及时发现锁等待和死锁才能快速定位并优化。

9.2 实用优化要点清单

缩短事务生命周期
- 事务中只包含必要的读写操作，尽快提交，避免长时间持锁。
使用合适的隔离级别
- 如果业务允许，可将全局或会话隔离级别设置为 READ COMMITTED，减少临键锁产生。
确保查询走索引
- 针对高并发的 UPDATE/DELETE/SELECT … FOR UPDATE，需要为 WHERE 条件列建立合适索引，避免全表扫描。
分批处理大事务
- 对大数据量更新/删除，采用分页或主键范围分批执行，减少单次锁住的行数。
使用乐观锁
- 在冲突概率较低的场景中，用版本号（version）或时间戳字段做乐观锁，避免行锁等待。
分区表/分库分表
- 对于数据量和并发非常大的表，考虑垂直/水平拆分，或者使用表分区，让锁只作用在小范围。
避免范围扫描加大范围锁
- 如果确实要做范围更新，先查出行主键再通过主键批量更新；或者将查询条件拆分成多个小范围。
监控锁等待与死锁
- 定期检查 SHOW ENGINE INNODB STATUS、INFORMATION_SCHEMA.INNODB_LOCK_WAITS、Innodb_deadlocks 等，发现热点及时优化。

通过上述详尽示例与代码对比，你应能清晰理解 MySQL 锁带来的性能问题背后“锁粒度”、“锁范围”与“事务时间”三者之间的关系，并掌握常见的预防策略。在实际项目中，结合业务场景选择最合适的隔离级别与锁方案，及时监控并逐步优化，才能让数据库在高并发环境下既保证数据安全，又拥有良好吞吐。