正文

MySQL数据库卡顿排查指南从慢查询日志分析到性能监控工具实战解决高并发场景下的响应延迟问题让运维小白也能轻松上手优化数据库性能

/0 浏览量
0704

哎哟，朋友，先别急着拍桌子。我知道你现在盯着屏幕上那个转圈圈的加载图标，心里肯定像有一万只蚂蚁在爬。业务方催得紧，老板问“怎么又慢了”，而你作为一个刚接触运维或者开发不久的“小白”，看着密密麻麻的错误日志和突然飙升的CPU使用率，脑子是一片空白的。

别慌，深呼吸。数据库变慢，就像人生病了，不能一上来就乱吃药（随便重启服务或加配置）。我们需要的是“望闻问切”。今天我不给你甩一堆晦涩难懂的术语，咱们就像聊天一样，一步步把这个“病根”找出来，治好它。我会用最通俗的大白话，配合真实的代码和命令，带你走完这个排查全过程。

第一步：冷静观察，确认“病情”有多重

在动手之前，你得先搞清楚，到底是真的卡了，还是网络抖动？是只有这一张表卡，还是整个库都卡？

1. 简单的“脉搏”检查

首先，连上你的服务器，看看资源使用情况。很多时候，卡顿是因为硬件资源被吃光了。

# 查看CPU和内存使用情况，按q退出
top

# 或者更直观的，查看磁盘IO等待情况，这往往是数据库慢的元凶
iostat -x 1 5

怎么解读？

  • %wa (iowait)：如果这个值很高（比如超过20%-30%），说明磁盘读写太慢了，数据库在苦苦等待硬盘返回数据。这时候你去调SQL优化基本没用，得考虑换SSD或者优化写入策略。
  • Load Average：看负载是否远高于CPU核心数。如果是，说明系统忙不过来了。

2. 连接数爆炸？

高并发场景下，最容易出现的情况就是连接数满了。

-- 登录MySQL后执行
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

如果 Threads_connected 接近 max_connections，那恭喜你，你的数据库正在经历“挤地铁”般的压力。这时候新来的请求会被拒绝或排队，导致超时。

小白建议： 临时增加 max_connections 只是治标，根本原因通常是应用层没有正确关闭连接，或者代码里存在长事务。

第二步：抓住真凶——慢查询日志（Slow Query Log）

如果说数据库是医院，那么慢查询日志就是病人的“病历本”。它记录了所有执行时间超过阈值的SQL。这是排查性能问题最直接、最有效的手段。

1. 开启慢查询日志

很多新手以为日志默认是开的，其实不然。你需要确认并配置它。

-- 查看当前慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 查看慢查询的时间阈值（默认10秒，建议改为1秒甚至更低用于排查）
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（假设文件路径为 /var/log/mysql/slow.log）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 设置为1秒以上才记录

注意： 在生产环境直接 SET GLOBAL 重启后会失效。建议修改配置文件 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1  # 记录未使用索引的查询，这点非常重要！

2. 分析慢查询日志

日志生成了，但里面可能成千上万条记录，怎么看？别用文本编辑器打开，你会卡死的。我们要用专门的工具。

神器推荐：pt-query-digest

Percona Toolkit 里的 pt-query-digest 是分析慢查询日志的神器。如果你还没装，赶紧装上：

sudo apt-get install percona-toolkit
# 或者 yum install percona-toolkit

运行命令：

pt-query-digest /var/log/mysql/slow.log > report.txt
cat report.txt

它会给你一个非常清晰的报告，告诉你：

  • 哪条SQL最耗时？
  • 这条SQL执行的频率是多少？
  • 平均执行时间、最大执行时间是多少？
  • 最关键：它使用了什么索引？

手动解读示例

假设你在报告中看到这样一条SQL：

SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' ORDER BY create_time DESC LIMIT 10;

报告显示它的平均执行时间是 2.5秒，且 Rows_examined（扫描行数）是 100万，而 Rows_sent（返回行数）只有 10。

这意味着什么？ 这意味着数据库为了找到这10条数据，把表翻了个底朝天！这就是典型的“全表扫描”或者“索引失效”。

第三步：深入骨髓——EXPLAIN 分析执行计划

找到了慢SQL，下一步就是看它是怎么执行的。使用 EXPLAIN 关键字。

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' ORDER BY create_time DESC LIMIT 10;

重点看这几个字段：

  1. type: 访问类型。

    • ALL: 全表扫描，最差，必须优化！
    • index: 全索引扫描，比ALL好点，但依然慢。
    • range: 索引范围扫描，常见于 >, <, BETWEEN。
    • ref: 非唯一索引扫描，返回匹配某个单独值的行。
    • eq_ref: 唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。
    • const/system: 常量/系统表，最快。
    • 目标：至少要是 range，最好是 ref 或 const。

  2. key: 实际使用的索引。

    • 如果是 NULL，说明没用到索引，大概率走了全表扫描。

  3. rows: 估计需要扫描的行数。

    • 这个数字越小越好。如果这个数字等于表的总行数，那就是全表扫描。

  4. Extra: 额外信息。

    • Using filesort: 需要额外的排序操作，很耗时。
    • Using temporary: 使用了临时表，通常用于 GROUP BY 或 DISTINCT，性能杀手。
    • Using index: 覆盖索引，非常好，不用回表。

案例实战：如何优化上面的SQL？

回到刚才的例子： SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' ORDER BY create_time DESC LIMIT 10;

问题诊断：

  • type 可能是 range 或 ALL。
  • Extra 出现了 Using filesort。

优化思路： 我们需要一个复合索引，同时包含 user_id, status, 和 create_time。

-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

为什么这么建？ 根据最左前缀原则，MySQL会先根据 user_id 定位，再过滤 status，最后 create_time 已经是有序的（或者是范围有序），所以不需要额外的 filesort。

再次执行 EXPLAIN，你会发现 key 变成了 idx_user_status_time，Extra 里的 Using filesort 消失了，rows 也大幅减少。速度从2.5秒降到0.01秒。爽不爽？

第四步：高并发下的隐形杀手——锁等待

有时候，SQL本身没问题，但就是卡住不动了。这时候要去查是不是被“锁”住了。在高并发场景下，事务竞争非常激烈。

1. 查看当前锁等待

-- MySQL 8.0+
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 老版本 MySQL
SHOW ENGINE INNODB STATUS\G

在 INNODB STATUS 的输出中，找到 LATEST DETECTED DEADLOCK 部分，它会告诉你哪个事务被哪个事务阻塞了。

2. 常见的锁问题及解法

  • 长事务：一个事务开启了很久，一直持有锁，其他事务都在排队。
    • 解法：检查代码，确保事务尽快提交。设置 innodb_lock_wait_timeout 避免无限等待。
  • 间隙锁（Gap Lock）：在可重复读隔离级别下，为了防止幻读，MySQL会对记录之间的间隙加锁。
    • 解法：如果不需要强一致性，可以考虑降低隔离级别到 RC（Read Committed），或者优化SQL避免范围查询。
  • 死锁：两个事务互相等待对方的锁。
    • 解法：统一加锁顺序，或者减小事务粒度。

第五步：全局视角——性能监控工具实战

光靠日志和EXPLAIN是事后诸葛亮，我们需要实时监控，防患于未然。对于小白来说，部署一套轻量级的监控系统是性价比最高的投资。

推荐方案：Prometheus + Grafana + mysqld_exporter

这套组合拳是业界标准，虽然听起来高大上，但配置起来其实不难。

1. 安装 mysqld_exporter

这是一个专门用来抓取MySQL指标的Agent。

# 下载并解压
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
tar xvf mysqld_exporter-0.14.0.linux-amd64.tar.gz
cd mysqld_exporter-0.14.0.linux-amd64/

# 创建一个用于监控的MySQL用户
mysql -u root -p
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;

# 创建配置文件 .my.cnf
cat > .my.cnf <<EOF
[client]
host=localhost
user=exporter
password=password
EOF

# 启动 exporter
./mysqld_exporter --config.my-cnf=".my.cnf" &

2. 配置 Prometheus

编辑 prometheus.yml：

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104'] # mysqld_exporter 默认端口

启动 Prometheus 并访问 http://your-server:9090，在 Status -> Targets 里应该能看到 MySQL 状态是 UP。

3. 导入 Grafana Dashboard

去 Grafana 官网找一个 MySQL Dashboard（比如 ID: 7362），导入到你的 Grafana 实例中。

你能看到什么？

  • QPS/TPS：每秒查询/事务数。
  • 慢查询数量：实时统计。
  • 连接数：当前活跃连接、空闲连接。
  • InnoDB Buffer Pool 命中率：如果低于95%，说明内存不够，数据经常要从磁盘读，这是性能瓶颈！
  • 线程状态分布：比如有多少线程在 Sending data，多少在 Sleeping。

通过 Grafana，你可以一眼看出：哦，原来每天下午3点，QPS突然飙升，导致Buffer Pool命中率下降，进而引发卡顿。这时候你就可以提前扩容或者优化缓存策略了。

第六步：给小白的“防坑”锦囊

在排查过程中，有几个坑是新手最容易踩的，我特意整理出来，帮你避雷。

1. 不要盲目加索引

  • 误区：“我觉得这里需要索引，加一个吧。”
  • 真相：索引虽然加速查询，但会降低插入和更新的速度，因为每次写数据都要更新索引树。而且索引占用磁盘空间。
  • 建议：只在高频查询、过滤条件多的字段上加索引。用 pt-query-digest 找出的真正慢的SQL再去加索引。

2. 避免 SELECT *

  • 误区：“方便嘛，反正以后可能要用到所有字段。”
  • 真相：SELECT * 会取出所有列，包括那些你不需要的。这不仅浪费网络带宽，还可能导致无法使用“覆盖索引”（Covering Index），迫使数据库进行回表查询。
  • 建议：明确指定需要的字段，如 SELECT id, name, price FROM ...。

3. 分页查询的深分页问题

  • 场景：LIMIT 1000000, 10。
  • 问题：MySQL需要扫描前100万条数据，然后丢弃它们，只取最后10条。这非常慢。
  • 优化方案：使用“延迟关联”或“游标分页”。
-- 优化前：慢
SELECT * FROM orders LIMIT 1000000, 10;

-- 优化后：快
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders LIMIT 1000000, 10) tmp
ON o.id = tmp.id;

原理是先通过索引快速定位ID，再回表查详情。

4. 字符集陷阱

  • 问题：如果数据库是 utf8mb4，而查询条件是 latin1，或者索引字段和查询条件字符集不一致，会导致隐式转换，索引失效！
  • 建议：确保应用连接数据库时指定的字符集与数据库一致，且字段类型统一。

结语：优化是一场持久战

朋友，看完这一大篇，你是不是觉得心里有底了？

数据库优化不是一次性的工作，而是随着业务发展不断演进的过程。

  1. 先监控：装上 Prometheus + Grafana，让你对系统状态心中有数。
  2. 抓日志：开启慢查询日志，定期用 pt-query-digest 分析。
  3. 精SQL：对慢SQL进行 EXPLAIN 分析，通过加索引、改写SQL来优化。
  4. 查锁：遇到阻塞，查锁等待，解决事务竞争。
  5. 调参数：在了解业务特性的基础上，微调 my.cnf 中的参数（如 innodb_buffer_pool_size 设为物理内存的50%-70%）。

记住，没有银弹。有时候，最极致的优化可能就是加一台服务器，或者引入Redis做缓存。但在做这些昂贵动作之前，请先花几个小时把SQL和索引优化到位，往往能带来十倍的性能提升。

现在，关掉那个转圈圈的页面，打开终端，开始你的第一次 EXPLAIN 吧。如果遇到具体的报错或奇怪的慢SQL，随时回来问我，我们一起拆解它。加油，运维大神之路，就是从解决第一个卡顿开始的！

-- 展开阅读全文 --