正文

MySQL数据库卡顿查什么慢查询日志与性能监控神器帮你秒定位瓶颈

/0 浏览量
0627

深夜两点，生产环境的告警群突然炸锅了。前端同事发来截图，页面加载转圈转得比磨盘还慢，用户投诉如潮水般涌来。这时候，作为后端开发或DBA的你，心跳可能比CPU频率还高。别慌，这种时刻拼的不是运气，而是你对数据库“体检”流程的肌肉记忆。今天咱们不聊枯燥的理论，直接切入实战，看看当MySQL开始“打嗝”甚至“昏迷”时，我们该如何像侦探一样，通过慢查询日志和性能监控工具，在几分钟内揪出那个拖后腿的“真凶”。

第一步：别急着重启，先听听数据库的“呼吸声”

很多新手遇到卡顿，第一反应是：“是不是数据库挂了？重启一下？”这是大忌。重启就像给病人打强心针，虽然暂时清醒了，但病因没除，下次发作更猛。

我们要做的第一件事，是确认数据库现在的状态。这时候，SHOW PROCESSLIST 是你的听诊器。

SHOW FULL PROCESSLIST;

执行这条命令后，你会看到一长串列表。重点关注 State 列和 Time 列。

  • 如果看到大量的 Sending data 且 Time 很长，说明数据库正在从磁盘读取大量数据发给客户端，这通常意味着全表扫描或者索引失效。
  • 如果看到 Locked，那恭喜你，锁冲突是元凶。
  • 如果看到 Waiting for table metadata lock，那可能是某个DDL操作（比如加字段）阻塞了其他所有操作。

光看进程列表还不够，我们需要更宏观的视角。这时候，Performance Schema 和 Sys Schema 就登场了。它们是MySQL自带的“黑匣子”，记录了海量的运行时数据。

第二步：慢查询日志——捕捉“作案现场”的录像

慢查询日志（Slow Query Log）是MySQL提供的一个功能，用于记录执行时间超过指定阈值的SQL语句。它是排查性能问题的黄金标准。

1. 开启并配置慢查询日志

首先，你得确保你的慢查询日志是开着的。登录MySQL，检查当前配置：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

如果 slow_query_log 是 OFF，你需要开启它。在生产环境中，建议动态开启（无需重启），并设置合理的阈值。比如，执行时间超过 1 秒的SQL才算“慢”：

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

注意：long_query_time 的单位是秒，默认值是 10。如果你发现很多SQL都在1秒左右徘徊，适当调低这个值（如 0.5 或 0.1），但不要太低，否则日志量会爆炸，影响性能。

2. 记录未使用索引的查询

除了记录慢SQL，还有一个非常有用的参数：log_queries_not_using_indexes。开启它后，所有没有使用索引的SQL都会被记录下来，哪怕它们执行得很快。这对于发现代码中的潜在隐患至关重要。

SET GLOBAL log_queries_not_using_indexes = ON;

3. 分析日志文件

日志文件通常位于 MySQL 的数据目录下，文件名可能是 hostname-slow.log。你可以直接用文本编辑器打开，但更好的方式是使用官方提供的分析工具 mysqldumpslow 或者更强大的第三方工具 pt-query-digest。

假设我们有一堆慢查询日志，内容如下：

# Time: 2023-10-27T10:00:00.000000Z
# User@Host: app_user[app_user] @ localhost []  Id:    42
# Query_time: 5.123456  Lock_time: 0.000001 Rows_sent: 1  Rows_examined: 1000000
SET timestamp=1698384000;
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' ORDER BY create_time DESC;

从这段日志中，我们可以提取出关键信息：

  • Query_time: 5.12秒，太慢了！
  • Rows_examined: 100万行。这意味着数据库扫描了大量数据才找到结果。
  • Rows_sent: 1行。效率极低，查了100万行只返回1行。

这就是典型的“全表扫描”特征。接下来，我们需要验证猜想。

第三步：EXPLAIN——SQL医生的X光机

拿到那条耗时5秒的SQL，不要猜，直接让它“裸奔”。使用 EXPLAIN 关键字来分析它的执行计划。

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

输出结果大致如下：

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orders ALL NULL NULL NULL NULL 1000000 Using where; Using filesort

看这里的关键列：

  • type: ALL。这是最差的情况，表示全表扫描。
  • key: NULL。表示没有使用任何索引。
  • Extra: Using where 表示在存储引擎层进行了过滤，Using filesort 表示需要额外的排序操作。

这就坐实了嫌疑：因为没有合适的索引，数据库被迫扫描了100万行数据，然后再进行过滤和排序。

如何修复？

解决方案很直接：创建复合索引。考虑到查询条件中有 user_id 和 status，以及排序字段 create_time，我们可以创建一个覆盖索引：

CREATE INDEX idx_user_status_time ON orders (user_id, status, create_time);

再次执行 EXPLAIN，你会发现 type 变成了 ref 或 range，rows 大幅下降，Extra 中的 Using filesort 也可能消失。问题迎刃而解。

第四步：性能监控神器——Percona Monitoring and Tools (PMM)

虽然慢查询日志和 EXPLAIN 能解决大部分问题，但在高并发、复杂的分布式系统中，单点排查往往不够。你需要一个持续监控的系统，像雷达一样实时扫描数据库的健康状况。

这里不得不提 PMM (Percona Monitoring and Management)。它是一个开源的平台，专门用于监控和管理MySQL、PostgreSQL等数据库的性能。

为什么选择 PMM？

  1. 可视化强: 它提供了 Grafana 风格的仪表盘，直观展示 QPS（每秒查询数）、TPS（每秒事务数）、连接数、Buffer Pool 命中率、锁等待时间等关键指标。
  2. 查询分析: PMM 集成了 pt-query-digest 的功能，可以自动收集慢查询日志，并进行聚类分析。它能告诉你：“过去一小时，哪几条SQL最慢？”、“哪些SQL被执行次数最多？”、“哪些SQL占用了最多的CPU？”
  3. 实时追踪: 它可以捕获当前的活动查询，让你看到正在运行的SQL长什么样，而不是事后诸葛亮。

如何使用 PMM 定位瓶颈？

假设你在 PMM 的 Dashboard 上看到，CPU 使用率飙升，同时 InnoDB row lock wait time 曲线也急剧上升。

  1. 查看 Top SQL: 进入 PMM 的 “Query Analytics” 页面，按 “Total Time” 排序。你可能会发现一条看似普通的 UPDATE 语句，执行时间长达几秒。
  2. 深入分析: 点击该SQL，查看其执行计划的历史变化。也许之前是有索引的，但因为数据分布变化导致优化器选择了错误的执行路径。
  3. 关联资源: PMM 还能展示该SQL执行期间，数据库服务器的 I/O 等待情况。如果发现 iowait 很高，说明瓶颈可能在磁盘，而不是CPU。这时，你可能需要优化SQL以减少I/O，或者升级SSD。

第五步：除了SQL，还有这些“隐形杀手”

有时候，慢查询日志里找不到明显的烂SQL，但数据库就是卡。这时候，你要把目光投向其他地方。

1. 锁等待 (Lock Contention)

锁是并发控制的基石，也是性能杀手。如果事务A持有了一把排他锁（X锁），事务B想要获取同样的锁，就必须等待。如果等待时间过长，就会表现为数据库卡顿。

如何检测？

-- 查看当前被锁住的表
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待关系
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

如果 INNODB_TRX 表中有一个事务运行时间很长，且 trx_state 为 LOCK WAIT，那么它就是受害者。你需要找到持有锁的事务（通过 trx_mysql_thread_id 关联到 PROCESSLIST），然后决定是杀死那个长时间运行的事务，还是优化它。

2. Buffer Pool 命中率

Buffer Pool 是 MySQL InnoDB 引擎在内存中缓存数据和索引的地方。如果命中率低，意味着大量请求需要从磁盘读取，速度自然慢。

如何监控？

在 PMM 中查看 “Buffer Pool Hit Rate” 图表。理想情况下，这个值应该在 99% 以上。如果低于 95%，你需要考虑增加 innodb_buffer_pool_size 的大小。

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

计算公式： Hit Rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

3. 连接数爆满

如果应用端的连接池配置不当，或者出现连接泄露，会导致数据库连接数达到上限 (max_connections)。新的请求无法建立连接，只能排队或直接报错。

如何监控？

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

Threads_connected 是当前建立的连接总数，Threads_running 是正在执行查询的连接数。如果 Threads_running 接近 max_connections，说明数据库已经不堪重负。

第六步：给小朋友也能听懂的总结

好了，说了这么多技术细节，咱们换个角度，用给小朋友讲故事的方式，把这些知识串起来。

想象一下，MySQL 数据库就像一个超级繁忙的图书馆管理员。

  • 慢查询日志 就像是管理员的“记账本”。每当有读者（程序）借书（查数据）花了太长时间，管理员就把这本书的名字、哪个读者借的、花了多久记下来。这样，管理员就能知道最近谁借的书最慢。
  • EXPLAIN 就像是管理员的“思考过程”。当你问管理员“为什么借这本书这么慢？”时，管理员会告诉你：“哦，因为你没告诉我书在哪一排（没走索引），所以我得把整个书架都翻一遍（全表扫描）。如果你告诉我书在第3排第5层（加了索引），我一眼就能找到。”
  • PMM 监控工具 就像是一个安装在图书馆里的“摄像头+智能分析仪”。它不仅记录谁借了书，还实时监控图书馆里有多少人（连接数）、书架满了没（Buffer Pool）、有没有人在打架（锁等待）。一旦发现有异常情况，它马上报警，告诉管理员：“嘿，那边有个读者卡住了！”
  • 锁等待 就像是两个小朋友都想玩同一个玩具。如果一个小朋友拿着玩具不肯放手，另一个小朋友就得在旁边等着。如果拿玩具的小朋友玩太久，后面的小朋友就会急得团团转。这时候，管理员（DBA）就得去劝那个玩太久的小朋友：“好了好了，给你玩够了，让别人也玩玩吧！”

所以，当数据库卡顿时，不要慌。先看看“记账本”（慢查询日志），找出最慢的那几条SQL；然后用“思考过程”（EXPLAIN）看看为什么慢；最后借助“摄像头”（PMM）监控整体健康状况，排除锁、连接数、内存等其他潜在问题。

结语：预防胜于治疗

排查慢查询只是治标，治本之道在于日常的性能优化和规范。

  1. 规范SQL编写: 避免 SELECT *，尽量使用覆盖索引，避免在索引列上进行函数运算或类型转换。
  2. 合理设计索引: 索引不是越多越好，每个索引都会增加写入开销。要根据查询频率和模式精心设计。
  3. 定期审查: 利用 PMM 或 pt-query-digest 定期回顾慢查询日志，优化高频慢SQL。
  4. 容量规划: 根据业务增长趋势，提前预判数据库的资源需求，及时扩容或优化架构。

记住，数据库性能优化是一场持久战，没有一劳永逸的解决方案。保持好奇心，善用工具，你就能在任何卡顿面前游刃有余。现在，去检查一下你的数据库吧，说不定下一个“侦探故事”的主角就是你。

-- 展开阅读全文 --