mysqlmysql慢查询日志如何分析性能瓶颈-mysql教程-PHP中文网

答案是分析MySQL慢查询日志可定位性能瓶颈，需合理配置参数、使用工具分析并结合多种方法优化。首先开启慢查询日志，设置long_query_time、log_queries_not_using_indexes等参数，配合min_examined_row_limit减少冗余日志；通过mysqldumpslow或pt-query-digest分析日志，关注总耗时高、扫描行数多、未用索引、文件排序等关键指标；结合SHOW PROCESSLIST、EXPLAIN、SHOW GLOBAL STATUS等命令深入诊断；优化策略包括创建缺失索引、重写SQL、调整数据库结构、优化配置参数，并辅以硬件升级与缓存机制，最终实现系统性能提升。

mysqlmysql慢查询日志如何分析性能瓶颈

MySQL慢查询日志是定位数据库性能瓶颈的一把利器，它直接记录了那些执行时间超过预设阈值的SQL语句。通过分析这些日志，我们可以迅速找出导致系统响应缓慢的“元凶”，无论是缺少索引、查询写法不当，还是资源争抢，都能从中找到线索，进而有针对性地进行优化。这就像给数据库做了一次体检，慢查询日志就是那份详尽的体检报告，指明了哪些地方需要我们特别关注和治疗。

解决方案

要分析MySQL慢查询日志来定位性能瓶颈，这事儿说起来简单，但真要做好，需要一套比较系统的方法。我个人觉得，这不仅仅是工具的使用，更是一种思维模式的建立。

首先，你得确保慢查询日志是开启的。这通常在my.cnf或my.ini配置文件里设置。几个关键参数：

slow_query_log = ON：这个是开关，必须打开。
slow_query_log_file = /var/log/mysql/mysql-slow.log：指定日志文件路径，建议放在单独的磁盘分区，避免和数据文件抢I/O。
long_query_time = 1：这是阈值，单位是秒。我通常会从1秒开始，如果业务对响应时间要求极高，可以设为0.1秒甚至更低。但设得太低，日志量会非常大，需要权衡。
log_queries_not_using_indexes = ON：这个参数我强烈建议打开，它能帮你发现那些本该走索引却没走的查询，这常常是隐形的性能杀手。
min_examined_row_limit = 100：这个可以过滤掉一些虽然慢但扫描行数很少的查询，有时候这些查询慢只是因为等待其他资源，并不是它本身效率低。

日志文件生成后，直接看纯文本日志会很头疼，因为信息量太大。这时候就需要工具了。

工具选择：

mysqldumpslow：MySQL自带的工具，虽然功能相对简单，但对于初步分析已经足够。它能对慢查询日志进行汇总，按执行时间、锁定时间、扫描行数等进行排序。
- 常用命令示例：
```
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log
# -s at: 按平均查询时间排序 (avg time)
# -t 10: 显示前10条
# 也可以用 -s c (按计数), -s l (按锁定时间), -s r (按返回行数)
```
  登录后复制
  mysqldumpslow会将相似的查询语句进行归类（比如把SELECT * FROM users WHERE id = 1和SELECT * FROM users WHERE id = 2视为同一类），然后给出这类查询的统计信息。
pt-query-digest：这是Percona Toolkit里的一个工具，功能远比mysqldumpslow强大。它能生成非常详细的慢查询报告，包括查询的执行次数、总耗时、平均耗时、I/O情况、是否使用了临时表、是否进行了文件排序等等。它还能分析EXPLAIN的输出，给出优化建议。
- 常用命令示例：
```
pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt
```
  登录后复制
  这份报告通常会把最耗资源的查询排在前面，并提供详尽的统计数据，比如每个查询的平均响应时间、最大响应时间、总响应时间占所有慢查询的百分比、扫描行数、返回行数、是否使用了临时表或文件排序等。

分析重点： 拿到这些工具生成的报告后，我们关注的点就比较明确了：

总耗时占比高的查询： 即使单次执行很快，但如果执行次数极其频繁，累积起来的总耗时也会非常可观。
平均耗时长的查询： 这通常意味着查询本身效率低下，需要深入优化。
扫描行数（Rows_examined）远大于返回行数（Rows_sent）的查询： 这很可能是索引失效或者查询条件不精准，导致数据库扫描了大量无关数据。
使用了Full Scan或Full Join的查询： 意味着没有走索引，或者连接方式有问题。
涉及Using filesort或Using temporary的查询： 这表示MySQL需要额外进行文件排序或创建临时表来完成操作，非常消耗资源。
锁定时间长的查询： 可能与其他事务产生了锁竞争。

找到这些“嫌疑犯”后，下一步就是用EXPLAIN命令去分析它们的执行计划，理解MySQL是如何处理这些查询的。EXPLAIN的输出会告诉你查询是否使用了索引、使用了哪个索引、扫描了多少行、是否进行了文件排序、是否使用了临时表等等。根据EXPLAIN的结果，我们就能知道是该加索引、改写查询，还是调整数据库结构了。

如何高效配置MySQL慢查询日志，避免日志文件过大影响性能？

说实话，很多人对慢查询日志是又爱又恨。爱它能找出问题，恨它可能导致日志文件暴增，甚至把磁盘撑爆。我见过不少因为慢查询日志配置不当，导致系统不稳定的案例。所以，高效配置，真的是一门学问。

首先，long_query_time这个阈值设定是核心。不是越低越好，也不是越高越好。我通常建议根据业务SLA（服务等级协议）来定。如果你的业务要求所有请求必须在500毫秒内响应，那么long_query_time设为0.5秒就比较合理。如果日志量依然巨大，可以适当调高到1秒，或者配合min_examined_row_limit来过滤掉一些“伪慢查询”。比如，一个查询执行了0.8秒，但只扫描了10行，这可能不是查询本身的问题，而是网络延迟或其他等待造成的，这类查询的优化优先级通常不高。

其次，log_queries_not_using_indexes这个参数，我之前就提过，非常有用。它能捕捉到那些“漏网之鱼”，即使查询执行时间没超过long_query_time，但如果它没走索引，也可能在未来成为隐患。当然，打开这个参数会增加日志量，所以需要和long_query_time以及日志轮转策略配合使用。

关于日志文件过大，这几乎是必然会发生的问题。解决办法主要有两个：

日志轮转（Log Rotation）：这是最常见的做法。在Linux系统上，你可以使用logrotate工具来管理MySQL的慢查询日志。配置logrotate定期（比如每天或每周）对日志文件进行归档、压缩和删除旧日志。这样既能保留历史数据用于分析，又能防止单个日志文件无限膨胀。
- 配置示例（/etc/logrotate.d/mysql）：
```
/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        # MySQL 8.0+ 使用 FLUSH SLOW LOGS;
        # MySQL 5.7 及以下版本使用 mysqladmin flush-logs
        if test -x /usr/bin/mysqladmin && \
           /usr/bin/mysqladmin ping &>/dev/null; then
            /usr/bin/mysqladmin flush-logs
        fi
    endscript
}
```
  登录后复制
  postrotate里的mysqladmin flush-logs命令非常关键，它会告诉MySQL重新打开日志文件，这样新的慢查询就会写入新的日志文件，而旧的日志文件就可以被logrotate安全地移动、压缩了。
存储位置和介质： 如果可能的话，将慢查询日志文件放在一个独立的、高性能的存储介质上，比如SSD。虽然慢查询日志的写入频率通常不如binlog或relaylog那么高，但如果日志量巨大，I/O压力依然不容小觑。独立的存储可以避免日志写入对数据文件I/O造成影响。

蓝心千询
蓝心千询是vivo推出的一个多功能AI智能助手

34

查看详情

最后，别忘了定期清理旧的、不再需要的归档日志。日志是用来解决问题的，不是用来堆满磁盘的。

除了慢查询日志，还有哪些工具或方法可以辅助定位MySQL性能瓶颈？

光靠慢查询日志，有时候确实会显得有点“盲人摸象”，它能告诉你哪些查询慢，但可能无法直接告诉你为什么慢，或者当前系统整体状况如何。所以，我个人在做性能分析时，通常会结合多种工具和方法，形成一个更全面的视图。

SHOW PROCESSLIST： 这个命令简直是排查实时问题的“瑞士军刀”。当你发现系统突然变慢，或者某个请求迟迟不返回时，立刻运行SHOW PROCESSLIST。它会显示所有正在运行的线程（连接），包括它们的ID、用户、主机、数据库、命令、状态、执行时间以及正在执行的SQL语句。
- 关注点：
  - Time列：执行时间过长的查询。
  - State列：Locked、Sending data、Sorting result、Copying to tmp table等状态都可能指示问题。
  - Info列：正在执行的完整SQL语句。通过它，你可以快速识别出哪些查询正在消耗大量资源，甚至可以发现死锁或长时间的锁等待。
EXPLAIN 命令： 这是分析单条SQL语句执行计划的“显微镜”。当慢查询日志告诉你某个查询很慢时，EXPLAIN就是你深入了解其执行细节的下一步。
- 用法： EXPLAIN SELECT ... FROM ... WHERE ...;
- 关注点：
  - type：连接类型，ALL（全表扫描）通常是最差的，index（全索引扫描）次之，ref、eq_ref、const、system是比较好的。
  - key：实际使用的索引。
  - rows：MySQL预估需要扫描的行数，越小越好。
  - Extra：非常重要，Using filesort（文件排序）、Using temporary（使用临时表）、Using where（使用where条件过滤）、Using index（覆盖索引）等信息，能直接指出优化方向。
SHOW GLOBAL STATUS 和 SHOW ENGINE INNODB STATUS： 这两个命令提供了MySQL服务器和InnoDB存储引擎的运行时统计信息，是理解数据库整体健康状况的关键。
- SHOW GLOBAL STATUS： 提供了大量的全局状态变量，比如连接数、查询数、各种Handler_read_xxx（索引使用情况）、Created_tmp_disk_tables（临时表写入磁盘次数）、Innodb_buffer_pool_reads（缓冲池未命中次数）等。通过观察这些指标的趋势变化，可以判断数据库是否存在I/O瓶颈、内存不足、连接过多等问题。
- SHOW ENGINE INNODB STATUS： 提供了InnoDB存储引擎的详细信息，包括死锁信息、缓冲池使用情况、文件I/O、信号量、事务信息等。当出现死锁或InnoDB内部问题时，这个报告是必看的。
Performance Schema 和 Information Schema： 这是MySQL提供的高级监控和诊断工具。
- Performance Schema： 提供细粒度的事件监控，包括SQL语句、文件I/O、互斥锁、内存分配等各种操作的耗时和统计信息。虽然会带来一定的性能开销，但它能提供极其详细的数据，帮助你分析各种等待事件和资源消耗。
- Information Schema： 提供了数据库元数据，比如表结构、索引信息、视图定义等。虽然不直接用于性能监控，但可以辅助查询优化，比如检查表的索引是否合理。
外部监控工具： 专业的监控系统，如Percona Monitoring and Management (PMM)、Prometheus + Grafana、Zabbix等，能长时间收集MySQL的各项指标，并以图表形式展现，让你能直观地看到性能趋势、发现异常峰值，并进行历史数据对比分析。这些工具通常还能结合OS级别的监控数据（CPU、内存、磁盘I/O、网络），提供更全面的视角。

我通常的做法是：先看外部监控工具的宏观趋势，发现异常点；然后用SHOW PROCESSLIST定位实时问题；接着结合慢查询日志和EXPLAIN深入分析具体SQL；最后再用SHOW GLOBAL STATUS和Performance Schema进行更细致的诊断。这种从宏观到微观的分析路径，能让我更高效地找到并解决问题。

如何根据慢查询日志的分析结果，制定有效的优化策略？

分析慢查询日志和EXPLAIN结果，只是解决了“发现问题”的阶段。真正的挑战在于如何“解决问题”，也就是制定并实施有效的优化策略。这需要我们对MySQL的工作原理、SQL优化技巧以及业务逻辑都有比较深入的理解。

索引优化：
- 创建缺失索引： 这是最常见也是最有效的优化手段。根据WHERE子句、JOIN条件、ORDER BY和GROUP BY子句中使用的列，创建合适的索引。
  - 经验法则： 在选择性高的列上创建索引（即列中不重复值的比例高）。
  - 复合索引： 如果查询条件涉及多个列，考虑创建复合索引。注意“最左前缀原则”，即复合索引的第一个列必须在查询条件中出现，索引才能被有效利用。
- 覆盖索引： 如果一个索引包含了查询所需的所有列，那么MySQL就不需要再回表（回到数据行）去获取数据了，这能大大减少I/O操作。EXPLAIN结果中的Extra列显示Using index就代表使用了覆盖索引。
- 删除冗余/未使用索引： 过多的索引会增加写入操作的开销，并占用存储空间。定期检查并删除那些不必要的索引。
查询重写与SQL优化：
- *避免`SELECT `：** 只选择你需要的列，减少网络传输和MySQL处理的数据量。
- 优化JOIN操作： 确保JOIN的列上都有索引。避免使用CROSS JOIN。合理选择LEFT JOIN、RIGHT JOIN、INNER JOIN，有时子查询可以改写成JOIN，效率更高。
- 优化WHERE子句： 避免在WHERE子句中对列进行函数操作（如WHERE DATE(create_time) = CURDATE()），这会导致索引失效。尽量使用=、IN、BETWEEN等能有效利用索引的操作符。
- OR条件的优化： 多个OR条件可能会导致全表扫描，有时可以改写成UNION ALL。
- LIMIT优化： 对于大数据量分页查询，LIMIT offset, rows在offset很大时效率很低。可以考虑通过子查询或记录上次查询的最大ID来优化。
- 避免隐式类型转换： 比如WHERE phone_number = 1234567890，如果phone_number是字符串类型，MySQL可能会进行隐式转换，导致索引失效。
数据库结构优化（Schema Optimization）：
- 数据类型选择： 使用最小但足够存储数据的类型。比如，如果一个整数列最大值是1000，使用SMALLINT比INT更节省空间，I/O也会更少。
- 范式与反范式： 在某些读密集型场景，适当的反范式（数据冗余）可以减少JOIN操作，提高查询效率。但需要权衡数据一致性和更新复杂性。
- 分区表： 对于超大表，可以考虑使用分区表，将数据分散到不同的物理存储中。在某些查询场景下，可以只扫描特定分区，提高效率。
MySQL配置参数优化：
- innodb_buffer_pool_size： 这是最重要的参数之一，决定了InnoDB缓存数据和索引的大小。设置得足够大，能让更多数据留在内存中，减少磁盘I/O。通常设置为物理内存的50%-80%。
- tmp_table_size 和 max_heap_table_size： 这两个参数决定了内存中临时表的大小。如果内存临时表不够大，MySQL会将临时表写入磁盘（Created_tmp_disk_tables），这会严重影响性能。
- sort_buffer_size 和 join_buffer_size： 调整这些缓冲区大小，可以减少Using filesort和Using temporary的发生。
- max_connections： 合理设置最大连接数，避免连接过多导致服务器资源耗尽。
硬件升级与架构优化：
- 硬件升级： 当软件优化达到瓶颈，且业务量持续增长时，升级硬件（更快的CPU、更大的内存、更快的SSD）是直接有效的手段。
- 读写分离： 将读操作分散到多个只读副本，减轻主库压力。
- 分库分表： 对于超大规模的数据，垂直分库（按业务模块分）和水平分表（按某个键值范围或哈希分）是常见的解决方案。
- 缓存： 在应用层引入缓存（如Redis、Memcached），减少对数据库的直接访问。