为什么SQL关联查询在并发高时变慢_分析行级锁与意向锁的冲突-SQL-PHP中文网

Sending data耗时主因是InnoDB缓冲池过小导致频繁磁盘IO，将innodb_buffer_pool_size从8M调至2G后查询达毫秒级。

为什么sql关联查询在并发高时变慢_分析行级锁与意向锁的冲突

EXPLAIN 显示 rows 正常但查询卡在 Sending data？可能是意向锁阻塞了 DDL

看到 EXPLAIN 的 rows 值合理、type 是 ref 或 eq_ref，但实际执行时大量连接卡在 Sending data 或 Waiting for table metadata lock，这不是 SQL 本身的问题，而是表级元数据锁（MDL）被意向锁间接拖住。

典型场景：一个长事务正在执行 SELECT ... FOR UPDATE（加行 X 锁 → 自动持有一个 IX 意向排他锁），此时另一个会话尝试对同一张表执行 ALTER TABLE，就会被阻塞在 Waiting for table metadata lock
IX 锁本身不阻塞其他 IX 或 IS 锁，但它会与 DDL 所需的 S（共享元数据锁）冲突——因为 DDL 要求排他性地修改表结构
用 SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA = 'db' AND OBJECT_NAME = 't1' 查当前 MDL 状态（MySQL 8.0+）；5.7 可查 information_schema.INNODB_TRX + PROCESSLIST 关联定位源头事务
临时解法：杀掉长时间未提交的事务（KILL [id]）；长期方案：应用层控制事务粒度，避免在事务里做耗时操作或网络调用

JOIN 中某张表被频繁更新，为什么关联查询反而变慢？

不是因为索引缺失，而是行级锁升级成间隙锁（gap lock）后扩大了锁范围，导致并发 JOIN 查询被串行化。

在 REPEATABLE READ 隔离级别下，WHERE user_id = ? 如果 user_id 是二级索引（非唯一），InnoDB 不仅锁匹配行，还会锁前后间隙，防止幻读——这会让并发的 SELECT ... JOIN 在扫描该索引时反复等待
现象：SHOW ENGINE INNODB STATUS\G 中出现大量 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 后跟 lock_mode X locks gap before rec
验证方式：把隔离级别临时切到 READ COMMITTED（SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED），再跑相同 JOIN —— 若明显变快，基本可确认是间隙锁干扰
权衡点：RC 级别下间隙锁关闭，幻读风险存在，但高并发读多写少场景下更稳；若业务强依赖 RR，就只能优化索引结构（比如把 user_id 改成唯一索引，让 gap lock 退化为行锁）

为什么 SHOW PROCESSLIST 里一堆 Sleep 连接，但 JOIN 还是慢？

别只盯着 Sleep，真正卡住 JOIN 的是那些没显示在列表前端的“隐形持有者”——它们可能已经拿到行锁或意向锁，却还没开始执行关联逻辑。

Sleep 状态只是连接空闲，不代表它没持有锁；一个已提交但未断开的连接，仍可能持有未释放的意向锁（如刚执行完 SELECT ... FOR SHARE）
更危险的是 Updating 或 Locked 状态的连接：它们正在更新某张被 JOIN 的表，而你那个慢查询恰好要读同一行或同一索引范围，就会被 X 锁挡住
查真实锁等待链：MySQL 8.0+ 用 SELECT * FROM performance_schema.data_lock_waits；5.7 用 SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.INNODB_TRX b INNER JOIN information_schema.INNODB_LOCK_WAITS w ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
注意：INNODB_TRX 里的 trx_state = 'LOCK WAIT' 表示正在等锁，但不一定出现在 PROCESSLIST 的第一屏——得用 WHERE TIME > 30 过滤才容易捞出来

关联字段有索引，为什么还是触发全表扫描？

索引存在 ≠ 被使用。当 JOIN 条件涉及隐式类型转换或函数包装时，MySQL 会直接放弃索引，哪怕 EXPLAIN 显示 key 非空。

常见诱因：JOIN users u ON u.id = CAST(o.user_id AS SIGNED)、ON u.phone = CONCAT('+86', o.phone)、甚至 ON u.status = UPPER(o.status) —— 这些都会让 u.id 或 u.phone 上的索引失效
检查方法：用 EXPLAIN FORMAT=JSON 查 used_columns 和 key_parts 字段；或者在 WHERE 子句中单独测试该条件是否走索引（EXPLAIN SELECT * FROM users WHERE id = 123）
修复原则：确保 JOIN 字段两端类型一致（比如都是 BIGINT）、避免在索引列上套函数；如果必须转换，改用生成列（generated column）+ 函数索引（MySQL 5.7+）
额外坑：utf8mb4_unicode_ci 和 utf8mb4_bin 排序规则混用也会导致索引失效，尤其在跨库 JOIN 时容易忽略

意向锁本身不争资源，但它像一张看不见的网——只要有一条线绷紧（比如一个未提交事务持着 IX），整张表的 DDL 和部分 DML 就可能被拖进等待队列。排查时别只盯行锁，得顺着 metadata_locks 和 data_lock_waits 往上摸，才能抓住那个真正卡住 JOIN 的“安静持有者”。