在MySQL的实际使用中，复杂查询常常涉及多表关联、大批量数据过滤、聚合计算等操作，这类查询如果缺乏合理的锁控制，很容易出现资源争抢、重复扫描、数据不一致等问题，最终导致查询性能大幅下降。针对不同的业务场景选择合适的锁策略，是优化复杂查询性能的重要手段。
MySQL常见锁类型及特性
要合理运用锁优化查询，首先需要了解MySQL中常见的锁类型及其适用场景：
- 共享锁（S锁）：允许事务读取一行数据，多个事务可以同时持有同一行数据的共享锁，但共享锁与排他锁互斥，加共享锁后其他事务无法对该行加排他锁修改数据。
- 排他锁（X锁）：允许事务修改或删除一行数据，加排他锁后其他事务无法对该行加任何类型的锁，直到当前事务释放锁。
- 意向锁：分为意向共享锁（IS）和意向排他锁（IX），是表级锁，用于标识事务后续要对表中的行加对应的行级锁，避免表级锁和行级锁的冲突检查开销。
- 间隙锁：在可重复读隔离级别下生效，锁定索引记录之间的间隙，防止其他事务插入数据，避免幻读问题。
- 临键锁：是记录锁和间隙锁的组合，锁定索引记录本身和该记录之前的间隙，同样是可重复读隔离级别下的锁类型。
适合用锁优化复杂查询的典型场景
场景一：多表关联查询且结果集需要多次复用
当某个复杂关联查询的结果集需要在同一个事务中多次使用，且查询过程中不希望其他事务修改关联表的数据，避免后续读取结果不一致，此时可以给关联涉及的表加共享锁。
假设我们需要查询用户订单的详细信息，关联用户表和订单表，且后续还会基于这个结果做统计，示例代码如下：
-- 开启事务
START TRANSACTION;
-- 关联查询并加共享锁，锁定符合条件的用户和订单数据
SELECT u.user_id, u.user_name, o.order_id, o.order_amount, o.create_time
FROM user u
INNER JOIN order_table o ON u.user_id = o.user_id
WHERE u.user_status = 1 AND o.order_status = 2
LOCK IN SHARE MODE;
-- 后续可以基于这个结果做其他业务逻辑，比如统计订单总金额
SELECT SUM(order_amount) AS total_amount FROM (
SELECT u.user_id, u.user_name, o.order_id, o.order_amount, o.create_time
FROM user u
INNER JOIN order_table o ON u.user_id = o.user_id
WHERE u.user_status = 1 AND o.order_status = 2
LOCK IN SHARE MODE
) t;
-- 事务提交后释放锁
COMMIT;
场景二：先查询再更新的复杂业务逻辑
很多业务需要先查询某条数据的当前状态，再根据状态做更新操作，如果查询和更新之间没有加锁，很容易出现并发下的数据不一致问题，比如超卖场景。此时可以在查询时加排他锁，保证查询和更新操作的原子性。
以库存扣减的复杂查询场景为例，需要先查询商品库存是否充足，再扣减库存，示例代码如下：
-- 开启事务 START TRANSACTION; -- 查询商品库存并加排他锁，锁定该行数据，其他事务无法修改 SELECT stock_num FROM product WHERE product_id = 1001 FOR UPDATE; -- 判断库存是否充足，这里假设查询到的stock_num为10，需要扣减3个 -- 实际业务中这里会有程序逻辑判断，确认充足后再执行更新 UPDATE product SET stock_num = stock_num - 3 WHERE product_id = 1001; -- 提交事务释放锁 COMMIT;
场景三：大结果集的聚合查询避免重复扫描
对于需要扫描大量数据的聚合查询，如果查询过程中数据被其他事务修改，可能会导致多次执行查询结果不一致，或者重复扫描相同的数据。此时可以结合事务和合适的锁，减少不必要的重复扫描开销。
比如统计某个时间段内所有有效用户的订单总金额，示例代码如下：
-- 开启事务，设置隔离级别为可重复读，避免查询过程中数据变化 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; -- 聚合查询加共享锁，避免查询过程中订单数据被修改，减少重复校验开销 SELECT SUM(o.order_amount) AS total_order_amount FROM user u INNER JOIN order_table o ON u.user_id = o.user_id WHERE u.user_status = 1 AND o.order_status = 2 AND o.create_time BETWEEN '2024-01-01' AND '2024-01-31' LOCK IN SHARE MODE; -- 提交事务 COMMIT;
锁优化复杂查询的注意事项
- 控制加锁范围：尽量基于索引加行级锁，避免无索引查询导致锁升级为表锁，大幅降低并发能力。可以通过
EXPLAIN语句查看查询是否走索引，确保加锁的范围最小。 - 缩短锁持有时间：锁的持有时间越长，阻塞其他事务的概率越高，因此尽量把加锁的查询放在事务的前面，处理逻辑尽量精简，完成后尽快提交事务释放锁。
- 避免死锁：多个事务加锁的顺序尽量保持一致，比如都按照用户ID升序的顺序加锁，避免出现事务A锁了资源1等资源2，事务B锁了资源2等资源1的死锁情况。如果出现死锁，MySQL会自动回滚代价较小的事务，业务层需要做好重试逻辑。
- 结合隔离级别选择锁：不同的隔离级别下锁的行为不同，比如读已提交隔离级别下没有间隙锁，可重复读隔离级别下才有间隙锁和临键锁，需要根据业务对数据一致性的要求选择合适的隔离级别和锁类型。
锁优化效果验证
可以通过MySQL的慢查询日志和SHOW ENGINE INNODB STATUS命令查看锁的使用情况和查询性能变化。添加合适的锁之后，复杂查询的响应时间应该会有明显下降，同时事务的阻塞率不会大幅上升。如果加锁后并发性能下降严重，说明锁的范围或者类型选择不合理，需要调整策略。
需要注意的是，锁不是优化复杂查询的唯一手段，还需要结合索引优化、查询语句改写、分库分表等方法共同提升性能，锁的使用需要平衡数据一致性和并发性能，不能盲目加锁。