子查询引发mysql锁等待的原因
mysql执行子查询时，尤其是关联子查询，往往会对子查询涉及的表进行多次扫描，若子查询中包含更新类操作或者查询未使用合适的索引，就可能导致锁的持有时间变长。比如子查询在where条件中对主表的每一行都执行一次，每次执行都可能获取对应的行锁，当主表数据量较大时，锁的累积等待时间会显著增加，进而引发锁等待问题。
常见引发锁等待的子查询场景
最常见的场景是使用IN关键字的子查询，当子查询返回的结果集较大，且主查询的表没有合适的索引支持时，mysql可能会选择全表扫描，同时子查询的执行过程中会锁定相关的数据行，导致其他事务无法及时获取锁资源。
-- 存在锁等待风险的子查询示例
SELECT * FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE age > 18
);
优化子查询为Join连接查询的方法
将子查询改写为Join连接查询，可以让mysql优化器更合理地选择执行计划，减少不必要的表扫描次数，从而缩短锁的持有时间。改写时需要根据子查询的逻辑选择合适的Join类型，通常IN子查询可以改写为INNER JOIN，NOT IN子查询可以改写为LEFT JOIN ... WHERE ... IS NULL。
IN子查询改写为INNER JOIN
针对上面提到的用户订单查询示例，我们可以将子查询改写为内连接查询，让mysql一次性关联两张表，减少重复扫描带来的锁开销。
-- 改写为INNER JOIN后的查询 SELECT o.* FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.age > 18;
NOT IN子查询改写为LEFT JOIN
当子查询使用NOT IN时，直接改写为INNER JOIN无法得到正确结果，此时需要使用左连接筛选右表无匹配的记录。
-- 原始NOT IN子查询
SELECT * FROM orders
WHERE user_id NOT IN (
SELECT id FROM users WHERE age > 18
);
-- 改写为LEFT JOIN后的查询
SELECT o.* FROM orders o
LEFT JOIN users u ON o.user_id = u.id AND u.age > 18
WHERE u.id IS NULL;
优化后的效果验证与注意事项
改写完成后，可以通过EXPLAIN命令查看执行计划，对比改写前后的扫描行数、索引使用情况。通常改写后的Join查询会减少全表扫描的次数，锁的持有时间也会相应缩短。
| 对比项 | 子查询方案 | Join改写方案 |
|---|---|---|
| 扫描次数 | 主表全扫描+子查询多次执行 | 两表一次关联扫描 |
| 锁持有时间 | 较长，易引发等待 | 较短，减少等待概率 |
| 执行效率 | 低，数据量大时明显变慢 | 高，适合大数据量场景 |
注意事项
- 改写Join查询时需要确保连接条件的准确性，避免产生笛卡尔积导致结果错误。
- 两张关联表都需要建立合适的索引，尤其是连接字段上的索引，否则Join查询也可能出现全表扫描。
- 如果子查询中包含聚合函数，需要先对子查询的结果做聚合后再和主表Join，而不是直接改写。
需要注意的是，并非所有的子查询都需要改写为Join，部分简单的子查询mysql优化器会自动优化为Join执行，此时无需手动改写，避免不必要的代码调整。
-- 带聚合的子查询改写示例
-- 原始子查询：查询订单数大于5的用户订单
SELECT * FROM orders
WHERE user_id IN (
SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 5
);
-- 改写后的Join查询，先聚合得到用户列表再关联
SELECT o.* FROM orders o
INNER JOIN (
SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 5
) t ON o.user_id = t.user_id;