Skip to content

查询单行数据

一、查询长时间不返回（通常由锁等待引起）

当一个简单的单行查询长时间没有结果时，大概率是遇到了锁等待。

诊断步骤： 1. SHOW PROCESSLIST;: 查看当前查询线程的状态。 2. Performance Schema / sys schema: (需开启 Performance Schema) 提供更详细的锁等待信息。

常见等待状态及原因：

1. 等待 MDL (Metadata Lock) 锁 - Waiting for table metadata lock

  • 原因： 当前会话需要获取表的 MDL 读锁来执行查询，但有其他会话持有该表的 MDL 写锁（通常由 DDL 操作、LOCK TABLES ... WRITE 或某些 FLUSH 命令引起）或正在请求 MDL 写锁且优先级更高。
  • 诊断：
    • SHOW PROCESSLIST; 显示状态为 Waiting for table metadata lock。
    • 查询 sys.schema_table_lock_waits 可以直接定位持有写锁或阻塞的线程 ID。注意持有写锁的线程可能处于 Sleep 状态。
  • 解决：
    • 找到持有 MDL 写锁的线程 ID。
    • 使用 KILL <thread_id>; 命令终止该连接。

2. 等待 Flush 操作 - Waiting for table flush

  • 原因： 当前会话的 SELECT 被一个 FLUSH TABLES [t] [WITH READ LOCK]; 命令阻塞了。而这个 FLUSH 命令本身可能又被另一个长时间持有该表“打开”状态的查询（即使是 SELECT）所阻塞。
  • 诊断： SHOW PROCESSLIST; 显示状态为 Waiting for table flush。需要找到链式阻塞的源头。
  • 解决：
    • 识别出阻塞 FLUSH 命令的那个长时间查询线程。
    • KILL 掉阻塞 FLUSH 的线程，或者 KILL 掉 FLUSH 命令本身。

3. 等待行锁 (InnoDB)

  • 场景： 执行当前读语句，如 SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; 或 SELECT * FROM t WHERE id = 1 FOR UPDATE;。
  • 原因： 该语句需要获取 id=1 这一行的读锁（共享锁）或写锁（排他锁），但另一个未提交的事务已经持有了该行的写锁。
  • 诊断：
    • 查询语句长时间不返回。
    • 查询 sys.innodb_lock_waits 可以清晰地看到等待锁的线程 (requesting_trx_id) 和持有锁的线程 (blocking_trx_id)。
  • 解决：
    • 从 sys.innodb_lock_waits 找到 blocking_trx_id 对应的 MySQL 线程 ID。
    • 执行 KILL <blocking_thread_id>; （注意：不是 KILL QUERY）。断开连接会强制回滚其未提交的事务，从而释放行锁。KILL QUERY 无效，因为持有锁的 UPDATE/DELETE/INSERT 语句可能已经执行完毕，只是事务未提交。

二、查询返回但执行缓慢

即使查询最终返回了结果，也可能耗时很长。

1. 低效扫描 (无索引或索引选择不当)

  • 场景： SELECT * FROM t WHERE c = 50000 LIMIT 1; （假设 c 字段无索引）。
  • 原因： 查询条件作用在非索引列上，导致 InnoDB 必须进行全表扫描（或全主键索引扫描）来找到满足 c=50000 的行。虽然 LIMIT 1 使得找到一行后即可停止，但可能已经扫描了大量数据。
  • 诊断：
    • EXPLAIN 显示未使用索引 (key: NULL) 或全索引扫描 (type: index)。
    • 慢查询日志 (slow log) 显示 Rows_examined 很大。
  • 解决： 在 c 字段上创建索引。

2. 一致性读遭遇大量 Undo Log

  • 场景： 一个标准的 SELECT * FROM t WHERE id = 1; （一致性读）执行非常慢，但相同条件的 SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; （当前读）却非常快。
  • 原因：
    • 事务 A 启动（使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 或默认的 REPEATABLE READ/READ COMMITTED 开启事务）。
    • 之后，事务 B 对 id=1 这一行执行了大量的 UPDATE 操作（例如，循环更新 100 万次）并未提交或在事务 A 读取前提交了。
    • 当事务 A 执行 SELECT * FROM t WHERE id = 1; 时，它需要读取在其事务开始时该行可见的版本。为此，MySQL 必须从该行的最新版本开始，利用 undo log 链条逆向应用历史变更，直到找到符合事务 A 可见性快照的版本。如果 undo log 链非常长（因为事务 B 的大量更新），这个过程会非常耗时。
    • 而 LOCK IN SHARE MODE 执行的是当前读，它直接读取该行的最新版本（可能是事务 B 未提交的版本或最新已提交版本，取决于隔离级别和锁情况），无需遍历 undo log，因此速度快。
  • 诊断：
    • 慢查询日志显示 Rows_examined: 1 但 Query_time 很高。
    • 对比相同条件的当前读语句，执行时间差异巨大。
  • 解决：
    • 避免长事务： 长时间运行的事务会持有较旧的 Read View，增加其他查询遇到长 undo 链的可能性。
    • 优化更新逻辑： 审视为何同一行会在短时间内被高频更新，是否可以优化业务逻辑。
    • 理解一致性读成本： 认识到在特定场景下，一致性读的开销可能很大。

三、 总结与建议

  1. 排查锁问题： 遇到查询不返回，优先使用 SHOW PROCESSLIST 和 sys schema（schema_table_lock_waits, innodb_lock_waits）排查 MDL 锁、表锁、行锁。
  2. 理解锁解决方式： 区分 KILL QUERY 和 KILL CONNECTION 的适用场景。对于持有锁的未提交事务，通常需要 KILL CONNECTION。
  3. 慢查询分析： 对于返回慢的查询，结合 EXPLAIN 和慢查询日志 (Rows_examined) 判断是否为扫描效率问题。
  4. 警惕一致性读开销： 理解 MVCC (多版本并发控制) 和 undo log 的工作原理。在特定高并发更新场景下，一致性读可能因遍历 undo 记录而变慢。
  5. 性能分析工具： 熟练使用 EXPLAIN, SHOW PROCESSLIST, Performance Schema, sys schema, 慢查询日志等工具进行诊断。