IN子查询易致锁等待，因未物化时每行重执行并加锁；改写为JOIN需同步建被驱动表索引，否则更慢；类型不一致会致索引失效，须严格校验字段定义。
子查询引发锁等待，本质是执行过程串行化、索引失效、事务持有时间拉长；直接改写为 JOIN 是最有效解法，但必须同步补上被驱动表的索引，否则只是把问题从“慢”变成“更慢的锁”。
为什么 IN 子查询容易导致锁等待
MySQL 在执行 WHERE col IN (SELECT ...) 时，若子查询未被物化（尤其在旧版本或复杂条件下），优化器可能退化为“对主表每行都重执行一次子查询”，且该子查询若涉及更新中的表（如 UPDATE t1 SET x=... WHERE id IN (SELECT id FROM t2 WHERE ...)），就会反复加读锁甚至间隙锁，造成阻塞链。常见现象是 SHOW ENGINE INNODB STATUS 中看到大量 LOCK WAIT 和 waiting for table metadata lock。
- 子查询本身含
FOR UPDATE或在事务中未提交，会持续持锁 - 子查询字段无索引 → 全表扫描 → 锁住更多行或页
- 主表与子查询表存在外键约束时，MySQL 可能升级为范围锁
- 5.7 及之前版本对
IN子查询的 semi-join 转换默认关闭（optimizer_switch='semijoin=off'），无法自动优化
用 INNER JOIN + DISTINCT 替代 IN 子查询
语义等价、优化器友好、天然规避重复执行。关键不是“写出来”，而是确保被驱动表连接字段有索引。
- 原语句：
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active'); - 改写后：
SELECT DISTINCT o.* FROM orders o INNER JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active'; - 必须执行：
CREATE INDEX idx_customers_status_id ON customers(status, id);（联合索引覆盖 WHERE + JOIN） - 若
customer_id本身是外键，确认它在orders表上已有索引，否则JOIN时驱动表仍要全表扫
LEFT JOIN + IS NOT NULL 实现 NOT IN 逻辑
NOT IN 遇到 NULL 会整体返回空结果集，而 LEFT JOIN 天然可表达“不存在”语义，且不惧 NULL 干扰。
- 原语句：
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_logs WHERE created_at > '2026-01-01'); - 改写后：
SELECT u.* FROM users u LEFT JOIN banned_logs b ON u.id = b.user_id AND b.created_at > '2026-01-01' WHERE b.user_id IS NULL; - 注意：
AND b.created_at > ...必须写在ON子句，不能挪到WHERE，否则变成交叉过滤 - 被驱动表
banned_logs上需建索引：CREATE INDEX idx_banned_user_time ON banned_logs(user_id, created_at);
JOIN 字段类型不一致导致索引失效的坑
这是线上最隐蔽的性能杀手——看着 SQL 改了、索引也建了，EXPLAIN 却仍显示 type=ALL。
- 检查两表连接字段类型是否完全一致：
orders.customer_id是INT UNSIGNED，而customers.id是INT SIGNED→ 自动隐式转换，索引失效 - 字符集/排序规则不同也会触发全表扫描，例如
utf8mb4_0900_as_csvsutf8mb4_general_ci - 用
SHOW CREATE TABLE对比字段定义，别只看名字 - 修改类型前先确认业务影响，必要时用
ALTER TABLE ... MODIFY COLUMN统一
真正卡住人的从来不是“会不会写 JOIN”，而是改完之后没验证被驱动表是否真走了索引、锁等待是否还在 —— 建议每次上线前跑一遍 EXPLAIN FORMAT=TREE，盯紧 rows_examined 和 filtered 字段。
