开启和配置mysql慢查询日志的方法是：1.通过show variables检查slow_query_log状态；2.在配置文件中设置slow_query_log=1、slow_query_log_file路径、long_query_time阈值及log_queries_not_using_indexes；3.重启服务或用set global命令生效配置。慢查询日志记录了query_time、lock_time、rows_examined等关键信息，用于识别性能瓶颈。分析工具包括mysqldumpslow、pt-query-digest和sql监控平台。优化方法包括添加索引、重写sql、优化表结构或升级硬件。开启log_queries_not_using_indexes可能导致日志增长过快，需谨慎评估。预防慢查询应规范sql编写、定期检查索引、使用explain分析执行计划并实时监控数据库性能。
慢查询日志是定位SQL性能瓶颈的关键。它记录了执行时间超过设定阈值的SQL语句，帮助我们找出需要优化的查询。理解并正确配置慢查询日志，是数据库性能优化的第一步。
慢查询日志记录了执行时间超过long_query_time的SQL语句。通过分析这些日志，我们可以识别出耗时较长的查询，进而进行优化，比如添加索引、重写SQL等。
如何开启和配置MySQL慢查询日志？
首先，你需要确认MySQL服务器是否开启了慢查询日志。可以通过以下SQL语句查看：
SHOW VARIABLES LIKE 'slow_query_log%';
如果slow_query_log的值是OFF，则需要开启它。修改MySQL配置文件（通常是my.cnf或my.ini），在[mysqld]段中添加或修改以下配置：
slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log # 日志文件路径，根据实际情况修改 long_query_time = 2 # 设置慢查询阈值，单位为秒。建议从2秒开始，逐步调整 log_queries_not_using_indexes = 1 # 记录未使用索引的查询
修改完成后，需要重启MySQL服务或执行以下命令使配置生效：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; SET GLOBAL long_query_time = 2; SET GLOBAL log_queries_not_using_indexes = 'ON';
注意：使用SET GLOBAL修改的配置，服务器重启后会失效。建议直接修改配置文件。
如何解读慢查询日志？
慢查询日志包含了大量信息，包括执行时间、锁等待时间、查询语句等。一个典型的慢查询日志条目如下：
# Time: 2023-10-27T10:00:00.123456Z # User@Host: root[root] @ localhost [] Id: 5 Query_time: 5.234567 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 10000 SET timestamp=1698400800; SELECT * FROM users WHERE name LIKE '%test%';
-
Time: 查询执行的时间戳。 -
User@Host: 执行查询的用户和主机。 -
Query_time: 查询执行的总时间，单位为秒。这是最重要的指标。 -
Lock_time: 查询等待锁的时间，单位为秒。如果Lock_time过长，说明存在锁竞争。 -
Rows_sent: 查询返回的行数。 -
Rows_examined: 查询扫描的行数。Rows_examined远大于Rows_sent，说明查询效率不高，可能需要优化。
关注Query_time和Rows_examined，找出执行时间长且扫描行数多的查询。这些查询往往是性能瓶颈所在。
除了查看日志文件，还有哪些工具可以分析慢查询？
直接分析日志文件比较繁琐，可以使用一些工具来辅助分析，例如：
- mysqldumpslow: MySQL自带的工具，可以对慢查询日志进行统计分析，例如按查询时间排序、统计查询次数等。
- pt-query-digest: Percona Toolkit中的工具，功能更强大，可以生成更详细的报告，包括查询的频率、平均执行时间、最坏执行时间等。
- SQL监控平台: 一些数据库监控平台也提供了慢查询分析功能，可以图形化展示慢查询信息，更直观地发现问题。
选择合适的工具可以大大提高分析效率。
如何针对慢查询进行优化？
找到慢查询后，就需要进行优化。常见的优化方法包括：
- 添加索引: 这是最常用的优化方法。通过添加合适的索引，可以减少查询扫描的行数，提高查询效率。
-
重写SQL: 优化SQL语句的结构，例如避免使用
SELECT *、避免在WHERE子句中使用函数等。 - 优化表结构: 调整表结构，例如拆分大表、使用更合适的数据类型等。
- 升级硬件: 如果以上方法都无效，可能需要考虑升级硬件，例如增加内存、更换更快的磁盘等。
优化是一个迭代的过程，需要不断尝试和验证。
开启log_queries_not_using_indexes有什么风险？
开启log_queries_not_using_indexes会记录所有未使用索引的查询，这可能会导致慢查询日志文件迅速增大，占用大量磁盘空间。因此，建议在测试环境开启，观察日志增长情况，并根据实际情况调整。在生产环境开启时，需要谨慎评估风险。
另外，即使开启了log_queries_not_using_indexes，也并不意味着所有未命中索引的查询都需要优化。有些查询的数据量很小，即使不使用索引，执行速度也很快。我们需要根据实际情况进行判断。
如何避免慢查询？
与其事后分析慢查询日志，不如事前预防慢查询的发生。以下是一些预防慢查询的建议：
-
规范SQL编写: 遵循SQL编写规范，例如避免使用
SELECT *、避免在WHERE子句中使用函数等。 - 定期检查索引: 定期检查索引的使用情况，删除不必要的索引，添加缺失的索引。
-
使用Explain分析SQL: 在执行SQL之前，使用
EXPLAIN命令分析SQL的执行计划，了解SQL是否会使用索引，以及扫描的行数。 - 监控数据库性能: 使用数据库监控工具，实时监控数据库的性能指标，及时发现潜在的性能问题。
通过以上方法，可以有效避免慢查询的发生，提高数据库的整体性能。
