深夜两点,生产环境的告警群突然炸锅了。前端同事发来截图,页面加载转圈转得比磨盘还慢,用户投诉如潮水般涌来。这时候,作为后端开发或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?
- 可视化强: 它提供了 Grafana 风格的仪表盘,直观展示 QPS(每秒查询数)、TPS(每秒事务数)、连接数、Buffer Pool 命中率、锁等待时间等关键指标。
- 查询分析: PMM 集成了
pt-query-digest的功能,可以自动收集慢查询日志,并进行聚类分析。它能告诉你:“过去一小时,哪几条SQL最慢?”、“哪些SQL被执行次数最多?”、“哪些SQL占用了最多的CPU?” - 实时追踪: 它可以捕获当前的活动查询,让你看到正在运行的SQL长什么样,而不是事后诸葛亮。
如何使用 PMM 定位瓶颈?
假设你在 PMM 的 Dashboard 上看到,CPU 使用率飙升,同时 InnoDB row lock wait time 曲线也急剧上升。
- 查看 Top SQL: 进入 PMM 的 “Query Analytics” 页面,按 “Total Time” 排序。你可能会发现一条看似普通的
UPDATE语句,执行时间长达几秒。 - 深入分析: 点击该SQL,查看其执行计划的历史变化。也许之前是有索引的,但因为数据分布变化导致优化器选择了错误的执行路径。
- 关联资源: 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)监控整体健康状况,排除锁、连接数、内存等其他潜在问题。
结语:预防胜于治疗
排查慢查询只是治标,治本之道在于日常的性能优化和规范。
- 规范SQL编写: 避免
SELECT *,尽量使用覆盖索引,避免在索引列上进行函数运算或类型转换。 - 合理设计索引: 索引不是越多越好,每个索引都会增加写入开销。要根据查询频率和模式精心设计。
- 定期审查: 利用 PMM 或
pt-query-digest定期回顾慢查询日志,优化高频慢SQL。 - 容量规划: 根据业务增长趋势,提前预判数据库的资源需求,及时扩容或优化架构。
记住,数据库性能优化是一场持久战,没有一劳永逸的解决方案。保持好奇心,善用工具,你就能在任何卡顿面前游刃有余。现在,去检查一下你的数据库吧,说不定下一个“侦探故事”的主角就是你。
