线上「偶发变慢」的报告里，SQL 扫太多行和锁等待各占一半。两者排查路径完全不同，先分清再动手。

MySQL 慢查询与锁等待：从慢日志定位到行锁排查 示意图

先把慢查询记下来

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

这些是运行时参数，重启失效；要长期生效写进配置文件。日志量大时用 pt-query-digest 聚合，按总耗时排序，比逐条看有效得多：

pt-query-digest /var/log/mysql/slow.log | head -50

关注三类 SQL：总耗时占比最高的、执行次数异常多的、扫描行数远大于返回行数的。

EXPLAIN 之后再 EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT id, title FROM posts
WHERE category_id = 3 AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

EXPLAIN 给的是优化器预估，EXPLAIN ANALYZE 会真正执行并输出每一步的实际耗时与实际行数。预估行数与实际行数差一个数量级，通常意味着统计信息过期，可以 ANALYZE TABLE 刷新后重看。

锁等待：先找阻塞者

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
       trx_query, trx_rows_locked
FROM information_schema.innodb_trx
ORDER BY trx_started;

trx_started 很早但 trx_query 为空，说明事务开着却没提交，是典型的「代码里忘了 commit」。再看具体哪把锁在等：

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

MySQL 8 的 data_lock_waits 直接给出「谁等谁」，拿到 BLOCKING_THREAD_ID 后可以决定是杀掉阻塞会话还是催业务方提交。5.7 及更早版本查 information_schema.innodb_lock_waits。

死锁：读最后一次日志

SHOW ENGINE INNODB STATUS;

输出里 LATEST DETECTED DEADLOCK 段落会列出两个事务各自持有什么锁、等待什么锁。多数死锁来自「不同顺序更新同一批行」：事务 A 先更 id 1 再更 id 2，事务 B 反过来。统一加锁顺序（例如按主键排序后批量更新）能消除大部分死锁。

三个常见根因

  • 长事务：把 HTTP 请求、RPC 调用包在事务里，锁持有时间被外部依赖拉长；
  • 缺索引导致锁范围扩大：没有索引时 UPDATE ... WHERE status = 'paid' 会锁住扫描到的所有行；
  • 大事务批量更新：拆成小批次、每批提交，减少锁持有时间与回滚成本。

小结

排查顺序固定为：慢日志找出高频 SQL → EXPLAIN ANALYZE 看实际行数 → innodb_trx 与 data_locks 找阻塞源 → 回到业务代码检查事务边界。索引本身的写法见索引设计与 EXPLAIN 读法。