MySQL / 系统课时

慢查询与常见故障排查

慢查询与常见故障排查

这一课建立一套可复用的 MySQL 排查顺序:先判断问题发生在连接、认证、SQL、锁还是执行性能层,再用对应命令收集证据。完成后,你可以避免只凭报错猜原因,并能对常见慢查询和锁等待给出基础修复方案。

学习目标

  • 按连接、权限、语法、锁等待和执行性能的顺序缩小故障范围
  • 使用 MySQL 状态、锁等待视图和执行计划定位基础问题
  • 根据证据采取修复动作,并再次执行查询完成验证

先按故障层级定位

排查时最容易浪费时间的做法,是看到“数据库有问题”就立即改 SQL 或重启服务。更稳妥的顺序是先确认能否建立连接,再确认账号权限和 SQL 本身,最后处理锁等待与慢查询。

MySQL 常见故障定位流程

可以先用命令行直接测试连接。<HOST> 和 <PORT> 使用实际数据库地址和端口。

bash
mysql -h <HOST> -P <PORT> -u app_user -p

预期结果

text
Enter password:
Welcome to the MySQL monitor.

如果出现 Can't connect to MySQL server,优先检查 MySQL 服务是否监听目标端口、防火墙和网络是否可达,以及主机名和端口是否写错。此时还没有进入 SQL 执行阶段,不应先从索引或语法入手。

区分权限错误与 SQL 错误

连接成功后,Access denied 或对象权限错误通常属于认证与授权问题。管理员可以使用 SHOW GRANTS 核对账号实际权限,其中用户名和来源主机必须与报错中的账号一致。

sql
SHOW GRANTS FOR 'app_user'@'localhost';

预期结果

text
Grants for app_user@localhost
GRANT USAGE ON *.* TO `app_user`@`localhost`
GRANT SELECT ON `demo`.* TO `app_user`@`localhost`

如果账号能连接但执行 UPDATE 被拒绝,就检查是否缺少对应对象上的 UPDATE 权限,而不是重新创建用户。

SQL 语法错误通常会返回 ERROR 1064。这类问题应先缩小到最短可复现 SQL,并检查关键字、括号、逗号、字符串引号和对象名。

sql
SELECT id, username
FROM users
WHERE id = 1;

预期结果

text
id  username
1   <VALUE>

实际数据值会不同;如果这条最小查询可以执行,再逐步恢复原 SQL 的连接、过滤和聚合部分,更容易找到出错位置。

识别锁等待与阻塞事务

SQL 语法正确但长时间不返回时,需要判断是否正在等待锁。MySQL 8.x 可以从 Performance Schema 中查看数据锁等待关系。

sql
SELECT
    REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
    BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx
FROM performance_schema.data_lock_waits;

预期结果

text
waiting_trx  blocking_trx
<ID>         <ID>

没有锁等待时结果集为空;存在记录时,说明等待事务被另一个事务阻塞。接下来应结合业务连接、事务持续时间和正在执行的 SQL 判断哪个事务应提交、回滚或终止,而不是看到阻塞就立即 KILL。

用执行计划排查慢查询

排除锁等待后,响应慢通常需要继续检查扫描范围和索引使用情况。EXPLAIN 不会真正执行普通 SELECT,适合先观察优化器计划。

sql
EXPLAIN
SELECT id, username
FROM users
WHERE email = 'alice@example.com';

预期结果

text
id  select_type  table  type  possible_keys  key   rows  Extra
1   SIMPLE       users  <TYPE> <KEYS>         <KEY> <N>   <VALUE>

type、key、rows 等值由表结构、索引和统计信息决定,因此不能要求固定输出。重点观察查询是否使用预期索引、预计扫描行数是否过大,以及 WHERE 条件是否具备合适的索引入口。

如果 email 是高选择性的常用查询条件,却没有合适索引,可以在确认数据分布和写入成本后添加索引。下面示例假设索引名尚未存在。

sql
CREATE INDEX idx_users_email ON users(email);

预期结果

text
Query OK, 0 rows affected

随后再次执行同一条 EXPLAIN,验证 key 是否选择 idx_users_email,并比较预计扫描行数。实际 Query OK 中的耗时和附加信息会随版本与数据量变化。

建立证据到修复的闭环

连接失败应验证服务、地址和网络;权限错误应核对账号与授权;语法错误应缩减 SQL;锁等待应找到阻塞事务;慢查询则应结合执行计划、数据量和索引判断。每次修改后都要重新执行原请求,并比较错误是否消失、耗时是否恢复,而不是把“执行了一个修复命令”当成排查结束。

对于持续性的慢查询,还可以开启慢查询日志或使用 Performance Schema 做长期采样,但基础排障的核心始终相同:先确定故障层级,再收集与该层级直接相关的证据,最后验证修复结果。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

慢查询与常见故障排查

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通