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

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：这个可以过滤掉一些虽然慢但扫描行数很少的查询，有时候这些查询慢只是因为等待其他资源，并不是它本身效率低。

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

工具选择：

1. 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和my.ini0视为同一类），然后给出这类查询的统计信息。

2. my.ini1：这是Percona Toolkit里的一个工具，功能远比mysqldumpslow强大。它能生成非常详细的慢查询报告，包括查询的执行次数、总耗时、平均耗时、I/O情况、是否使用了临时表、是否进行了文件排序等等。它还能分析my.ini3的输出，给出优化建议。

   • 常用命令示例：

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

     这份报告通常会把最耗资源的查询排在前面，并提供详尽的统计数据，比如每个查询的平均响应时间、最大响应时间、总响应时间占所有慢查询的百分比、扫描行数、返回行数、是否使用了临时表或文件排序等。

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

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

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

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

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

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

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

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

1. 日志轮转（Log Rotation）：这是最常见的做法。在Linux系统上，你可以使用slow_query_log = ON7工具来管理MySQL的慢查询日志。配置slow_query_log = ON7定期（比如每天或每周）对日志文件进行归档、压缩和删除旧日志。这样既能保留历史数据用于分析，又能防止单个日志文件无限膨胀。

   • 配置示例（slow_query_log = ON9）：

     /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 }

     slow_query_log_file = /var/log/mysql/mysql-slow.log0里的slow_query_log_file = /var/log/mysql/mysql-slow.log1命令非常关键，它会告诉MySQL重新打开日志文件，这样新的慢查询就会写入新的日志文件，而旧的日志文件就可以被slow_query_log = ON7安全地移动、压缩了。

2. 存储位置和介质： 如果可能的话，将慢查询日志文件放在一个独立的、高性能的存储介质上，比如SSD。虽然慢查询日志的写入频率通常不如binlog或relaylog那么高，但如果日志量巨大，I/O压力依然不容小觑。独立的存储可以避免日志写入对数据文件I/O造成影响。

   

   蓝心千询

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

   34

   查看详情

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

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

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

1. slow_query_log_file = /var/log/mysql/mysql-slow.log3： 这个命令简直是排查实时问题的“瑞士军刀”。当你发现系统突然变慢，或者某个请求迟迟不返回时，立刻运行slow_query_log_file = /var/log/mysql/mysql-slow.log3。它会显示所有正在运行的线程（连接），包括它们的ID、用户、主机、数据库、命令、状态、执行时间以及正在执行的SQL语句。

   • 关注点：
     • slow_query_log_file = /var/log/mysql/mysql-slow.log5列：执行时间过长的查询。
     • slow_query_log_file = /var/log/mysql/mysql-slow.log6列：slow_query_log_file = /var/log/mysql/mysql-slow.log7、slow_query_log_file = /var/log/mysql/mysql-slow.log8、slow_query_log_file = /var/log/mysql/mysql-slow.log9、long_query_time = 10等状态都可能指示问题。
     • long_query_time = 11列：正在执行的完整SQL语句。 通过它，你可以快速识别出哪些查询正在消耗大量资源，甚至可以发现死锁或长时间的锁等待。

2. my.ini3 命令： 这是分析单条SQL语句执行计划的“显微镜”。当慢查询日志告诉你某个查询很慢时，my.ini3就是你深入了解其执行细节的下一步。

   • 用法： long_query_time = 14
   • 关注点：
     • long_query_time = 15：连接类型，long_query_time = 16（全表扫描）通常是最差的，long_query_time = 17（全索引扫描）次之，long_query_time = 18、long_query_time = 19、log_queries_not_using_indexes = ON0、log_queries_not_using_indexes = ON1是比较好的。
     • log_queries_not_using_indexes = ON2：实际使用的索引。
     • log_queries_not_using_indexes = ON3：MySQL预估需要扫描的行数，越小越好。
     • log_queries_not_using_indexes = ON4：非常重要，my.ini6（文件排序）、my.ini7（使用临时表）、log_queries_not_using_indexes = ON7（使用where条件过滤）、log_queries_not_using_indexes = ON8（覆盖索引）等信息，能直接指出优化方向。

3. log_queries_not_using_indexes = ON9 和 min_examined_row_limit = 1000： 这两个命令提供了MySQL服务器和InnoDB存储引擎的运行时统计信息，是理解数据库整体健康状况的关键。

   • log_queries_not_using_indexes = ON9： 提供了大量的全局状态变量，比如连接数、查询数、各种Handler_read_xxx（索引使用情况）、Created_tmp_disk_tables（临时表写入磁盘次数）、Innodb_buffer_pool_reads（缓冲池未命中次数）等。通过观察这些指标的趋势变化，可以判断数据库是否存在I/O瓶颈、内存不足、连接过多等问题。
   • min_examined_row_limit = 1000： 提供了InnoDB存储引擎的详细信息，包括死锁信息、缓冲池使用情况、文件I/O、信号量、事务信息等。当出现死锁或InnoDB内部问题时，这个报告是必看的。

4. Performance Schema 和 Information Schema： 这是MySQL提供的高级监控和诊断工具。

   • Performance Schema： 提供细粒度的事件监控，包括SQL语句、文件I/O、互斥锁、内存分配等各种操作的耗时和统计信息。虽然会带来一定的性能开销，但它能提供极其详细的数据，帮助你分析各种等待事件和资源消耗。
   • Information Schema： 提供了数据库元数据，比如表结构、索引信息、视图定义等。虽然不直接用于性能监控，但可以辅助查询优化，比如检查表的索引是否合理。

5. 外部监控工具： 专业的监控系统，如Percona Monitoring and Management (PMM)、Prometheus + Grafana、Zabbix等，能长时间收集MySQL的各项指标，并以图表形式展现，让你能直观地看到性能趋势、发现异常峰值，并进行历史数据对比分析。这些工具通常还能结合OS级别的监控数据（CPU、内存、磁盘I/O、网络），提供更全面的视角。

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

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

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

1. 索引优化：

   • 创建缺失索引： 这是最常见也是最有效的优化手段。根据min_examined_row_limit = 1007子句、min_examined_row_limit = 1008条件、min_examined_row_limit = 1009和mysqldumpslow0子句中使用的列，创建合适的索引。
     • 经验法则： 在选择性高的列上创建索引（即列中不重复值的比例高）。
     • 复合索引： 如果查询条件涉及多个列，考虑创建复合索引。注意“最左前缀原则”，即复合索引的第一个列必须在查询条件中出现，索引才能被有效利用。
   • 覆盖索引： 如果一个索引包含了查询所需的所有列，那么MySQL就不需要再回表（回到数据行）去获取数据了，这能大大减少I/O操作。my.ini3结果中的log_queries_not_using_indexes = ON4列显示log_queries_not_using_indexes = ON8就代表使用了覆盖索引。
   • 删除冗余/未使用索引： 过多的索引会增加写入操作的开销，并占用存储空间。定期检查并删除那些不必要的索引。

2. 查询重写与SQL优化：

   • *避免`SELECT `：** 只选择你需要的列，减少网络传输和MySQL处理的数据量。
   • 优化min_examined_row_limit = 1008操作： 确保min_examined_row_limit = 1008的列上都有索引。避免使用mysqldumpslow6。合理选择mysqldumpslow7、mysqldumpslow8、mysqldumpslow9，有时子查询可以改写成min_examined_row_limit = 1008，效率更高。
   • 优化min_examined_row_limit = 1007子句： 避免在min_examined_row_limit = 1007子句中对列进行函数操作（如mysqldumpslow3），这会导致索引失效。尽量使用mysqldumpslow4、mysqldumpslow5、mysqldumpslow6等能有效利用索引的操作符。
   • mysqldumpslow7条件的优化： 多个mysqldumpslow7条件可能会导致全表扫描，有时可以改写成mysqldumpslow9。
   • SELECT * FROM users WHERE id = 10优化： 对于大数据量分页查询，SELECT * FROM users WHERE id = 11在offset很大时效率很低。可以考虑通过子查询或记录上次查询的最大ID来优化。
   • 避免隐式类型转换： 比如SELECT * FROM users WHERE id = 12，如果SELECT * FROM users WHERE id = 13是字符串类型，MySQL可能会进行隐式转换，导致索引失效。

3. 数据库结构优化（Schema Optimization）：

   • 数据类型选择： 使用最小但足够存储数据的类型。比如，如果一个整数列最大值是1000，使用SELECT * FROM users WHERE id = 14比SELECT * FROM users WHERE id = 15更节省空间，I/O也会更少。
   • 范式与反范式： 在某些读密集型场景，适当的反范式（数据冗余）可以减少min_examined_row_limit = 1008操作，提高查询效率。但需要权衡数据一致性和更新复杂性。
   • 分区表： 对于超大表，可以考虑使用分区表，将数据分散到不同的物理存储中。在某些查询场景下，可以只扫描特定分区，提高效率。

4. MySQL配置参数优化：

   • SELECT * FROM users WHERE id = 17： 这是最重要的参数之一，决定了InnoDB缓存数据和索引的大小。设置得足够大，能让更多数据留在内存中，减少磁盘I/O。通常设置为物理内存的50%-80%。
   • SELECT * FROM users WHERE id = 18 和 SELECT * FROM users WHERE id = 19： 这两个参数决定了内存中临时表的大小。如果内存临时表不够大，MySQL会将临时表写入磁盘（my.ini00），这会严重影响性能。
   • my.ini01 和 my.ini02： 调整这些缓冲区大小，可以减少my.ini6和my.ini7的发生。
   • my.ini05： 合理设置最大连接数，避免连接过多导致服务器资源耗尽。

5. 硬件升级与架构优化：

   • 硬件升级： 当软件优化达到瓶颈，且业务量持续增长时，升级硬件（更快的CPU、更大的内存、更快的SSD）是直接有效的手段。
   • 读写分离： 将读操作分散到多个只读副本，减轻主库压力。
   • 分库分表： 对于超大规模的数据，垂直分库（按业务模块分）和水平分表（按某个键值范围或哈希分）是常见的解决方案。
   • 缓存： 在应用层引入缓存（如Redis、Memcached），减少对数据库的直接访问。

制定策略时，我通常会遵循“二八原则”，即先优化那些最耗资源、出现频率最高的慢查询，因为它们带来的收益最大。每进行一项优化后，务必进行充分的测试和验证，观察其对性能的影响，确保解决了问题而没有引入新的问题。这本身就是一个迭代和持续优化的过程。