mysqlmysql如何监控慢查询执行时间-mysql教程-PHP中文网

启用慢查询日志并设置long_query_time阈值，结合log_queries_not_using_indexes和min_examined_row_limit等高级配置，可精准捕获问题SQL；通过mysqldumpslow或pt-query-digest工具分析日志，再利用EXPLAIN执行计划、索引优化、SQL重写和数据库结构优化等手段提升性能。

mysqlmysql如何监控慢查询执行时间

要监控MySQL的慢查询执行时间，最直接有效的方法就是启用MySQL自带的慢查询日志功能。通过配置合适的阈值，MySQL会自动记录那些执行时间超过设定的SQL语句，并将它们写入一个专门的日志文件，之后我们就可以对这个日志文件进行分析，找出潜在的性能瓶颈。

解决方案

MySQL提供了一套成熟的机制来捕获和记录慢查询。核心在于两个配置项：

slow_query_log

登录后复制

和

long_query_time

登录后复制

。

slow_query_log

登录后复制

是一个开关，设置为

ON

登录后复制

即可启用慢查询日志。

long_query_time

登录后复制

则定义了“慢”的标准，单位是秒。任何执行时间超过这个值的查询都会被记录下来。默认值通常是10秒，但在实际生产环境中，我个人觉得这个值可能太高了，很多时候，一个1秒甚至0.5秒的查询都可能对用户体验造成影响，所以我会倾向于把它设置得更低一些，比如0.5秒或1秒，具体看业务响应速度的要求。

要启用和配置这些参数，你可以通过两种方式：

临时修改（运行时）：连接到MySQL客户端后，执行：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 设置阈值为1秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; -- 指定日志文件路径

登录后复制

这种方式修改的配置在MySQL服务重启后会失效。

永久修改（配置文件）：编辑MySQL的配置文件
```
my.cnf
```
登录后复制
(或
```
my.ini
```
登录后复制
，具体路径因操作系统和安装方式而异，通常在
```
/etc/mysql/my.cnf
```
登录后复制
或
```
/etc/my.cnf
```
登录后复制
)，在
```
[mysqld]
```
登录后复制
段下添加或修改以下行：
```
[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
# 还可以加上其他有用的配置，比如：
# log_queries_not_using_indexes = 1
# min_examined_row_limit = 100
```
登录后复制
修改配置文件后，需要重启MySQL服务才能生效：
```
sudo systemctl restart mysql # 或者 service mysql restart
```
登录后复制
日志文件路径 (
```
slow_query_log_file
```
登录后复制
) 需要确保MySQL用户对该路径有写入权限。如果路径不存在，MySQL会尝试创建它。

一旦慢查询日志启用并配置好，MySQL就会开始将符合条件的查询写入指定的日志文件。这个日志文件通常是纯文本格式，可以直接查看。

MySQL慢查询日志有哪些高级配置，能更精准地捕获问题？

仅仅启用慢查询日志并设置一个

long_query_time

登录后复制

可能还不够精细。MySQL提供了一些额外的配置项，能帮助我们更精准地捕获那些真正有问题的查询，避免日志文件过大或记录无关紧要的查询。

一个我个人觉得非常重要的配置是

log_queries_not_using_indexes

登录后复制

。把它设置为

ON

登录后复制

后，MySQL会记录所有没有使用索引的查询，即使它们的执行时间没有超过

long_query_time

登录后复制

阈值。这对于发现潜在的索引缺失或索引使用不当的问题非常有帮助，因为很多时候，一个查询在数据量小时可能很快，但随着数据量增长，没有索引的查询会迅速变成性能杀手。

还有

min_examined_row_limit

登录后复制

。这个参数指定了慢查询日志记录的查询至少需要扫描多少行数据。例如，如果设置为100，那么即使一个查询执行时间超过

long_query_time

登录后复制

，但它只扫描了50行，也不会被记录。这有助于过滤掉那些虽然慢，但实际上操作数据量很小的查询（比如，在等待锁或网络延迟导致的慢），让我们更专注于那些真正需要优化数据访问的查询。

另外，

log_output

登录后复制

参数可以控制慢查询日志的输出方式。默认是

FILE

登录后复制

，即将日志写入文件。你也可以设置为

TABLE

登录后复制

，这样慢查询日志就会被写入

mysql.slow_log

登录后复制

表中。写入表的好处是你可以直接通过SQL查询来分析日志，比如统计某个时间段内某个用户执行的慢查询次数，或者找出某个特定SQL模式的慢查询。不过，需要注意的是，将日志写入表可能会对数据库性能产生轻微影响，并且表会持续增长，需要定期清理。我通常还是倾向于文件日志，然后用外部工具分析，这样对数据库本身的压力最小。

还有一些不那么常用但偶尔有用的配置，比如

log_slow_admin_statements

登录后复制

会记录慢的管理语句（如

ALTER TABLE

登录后复制

），

log_slow_slave_statements

登录后复制

则会记录从库上执行的慢查询。这些在特定场景下，比如排查主从延迟问题时，会提供额外的线索。

除了直接查看日志文件，还有哪些工具可以高效分析MySQL慢查询？

直接查看慢查询日志文件虽然可行，但日志文件往往非常庞大，人工分析效率极低。好在，我们有很多工具可以帮助我们高效地分析慢查询日志。

mysqldumpslow

登录后复制

这是MySQL官方自带的一个命令行工具，简单实用，我经常用它来快速对慢查询日志进行初步的聚合分析。它能根据不同的维度（如访问次数、总耗时、平均耗时、发送数据量、锁定时间等）对慢查询进行分组和排序。

常用的用法是：

美间AI

美间AI：让设计更简单

查看详情

mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

登录后复制

这个命令的含义是：

```
-s at
```
登录后复制
: 按平均查询时间（Average Time）排序。常用的排序方式还有
```
c
```
登录后复制
(count，查询次数),
```
l
```
登录后复制
(lock time，锁定时间),
```
r
```
登录后复制
(rows sent，返回行数),
```
t
```
登录后复制
(time，总耗时)。
```
-t 10
```
登录后复制
: 显示前10条慢查询。
```
/var/log/mysql/mysql-slow.log
```
登录后复制
: 慢查询日志文件路径。

mysqldumpslow

登录后复制

会将相似的SQL语句（通过参数化处理，比如把

SELECT * FROM users WHERE id = 1

登录后复制

和

SELECT * FROM users WHERE id = 2

登录后复制

视为同一条语句）聚合起来，然后给出它们的统计信息。这能让你迅速看到哪些类型的查询是主要的慢查询来源。不过，它的功能相对简单，对于复杂的分析场景可能力不从心。

2. Percona Toolkit 的

pt-query-digest

登录后复制

这是我个人在生产环境中更偏爱、也更强大的慢查询分析工具。

pt-query-digest

登录后复制

是 Percona Toolkit 中的一个组件，它不仅能分析慢查询日志文件，还能分析

tcpdump

登录后复制

捕获的网络流量、

SHOW PROCESSLIST

登录后复制

的输出，甚至是

mysql.slow_log

登录后复制

表中的数据。

pt-query-digest

登录后复制

的强大之处在于它能生成非常详细的报告，包括：

慢查询的聚合统计，比
```
mysqldumpslow
```
登录后复制
更细致。
每个慢查询的详细信息，包括执行次数、总耗时、平均耗时、最大耗时、锁定时间、返回行数、扫描行数等。
对每个慢查询给出潜在的优化建议，比如是否缺少索引。
能够识别出那些“最慢的查询”，并以易读的方式呈现。

一个典型的用法：

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

登录后复制

这会将分析报告输出到一个文本文件。你也可以直接在终端查看。它有很多参数可以用来过滤、排序、格式化输出，例如：

```
--since
```
登录后复制
和
```
--until
```
登录后复制
: 指定分析时间范围。
```
--limit
```
登录后复制
: 限制报告中显示的查询数量。
```
--filter
```
登录后复制
: 使用Perl表达式过滤查询。

pt-query-digest

登录后复制

的报告非常详尽，能帮助我们深入理解慢查询的模式和瓶颈所在，是慢查询分析的利器。

慢查询日志分析后，如何优化SQL语句以提升性能？

慢查询日志和分析工具帮我们找到了问题，但真正的挑战在于如何解决这些问题。SQL优化是一个复杂且需要经验的过程，但有一些通用的原则和方法可以遵循。

1. 使用

EXPLAIN

登录后复制

分析执行计划 这是SQL优化最基础也是最关键的一步。当你找到一条慢查询后，把它放到

EXPLAIN

登录后复制

语句前面执行，MySQL会告诉你这条SQL是如何执行的，比如它使用了哪个索引，扫描了多少行，以及连接类型等。

EXPLAIN SELECT * FROM users WHERE username = 'testuser';

登录后复制

关注

EXPLAIN

登录后复制

结果中的几个关键列：

```
type
```
登录后复制
: 连接类型，
```
const
```
登录后复制
,
```
eq_ref
```
登录后复制
,
```
ref
```
登录后复制
,
```
range
```
登录后复制
是比较好的，
```
index
```
登录后复制
,
```
ALL
```
登录后复制
则通常意味着性能问题。
```
rows
```
登录后复制
: MySQL估算需要扫描的行数，越少越好。
```
Extra
```
登录后复制
: 额外信息，
```
Using filesort
```
登录后复制
和
```
Using temporary
```
登录后复制
通常是性能瓶颈的信号，需要重点关注。
```
Using index
```
登录后复制
(覆盖索引) 是非常好的。
```
key
```
登录后复制
: 实际使用的索引。
```
key_len
```
登录后复制
是索引的长度。

通过分析

EXPLAIN

登录后复制

结果，你可以判断是否缺少索引、索引是否失效、连接顺序是否合理等。

2. 索引优化

创建缺失的索引：这是最常见的优化手段。根据
```
WHERE
```
登录后复制
子句、
```
JOIN
```
登录后复制
条件和
```
ORDER BY
```
登录后复制
子句中的列来创建索引。
复合索引：如果查询条件涉及多个列，可以考虑创建复合索引。例如，
```
WHERE col1 = 'A' AND col2 = 'B'
```
登录后复制
，可以创建
```
(col1, col2)
```
登录后复制
的复合索引。复合索引的列顺序很重要，遵循“最左前缀原则”。
覆盖索引：如果一个索引包含了查询需要的所有列（
```
SELECT
```
登录后复制
列表和
```
WHERE
```
登录后复制
列表），那么MySQL可以直接从索引中获取数据，而不需要回表查询，这会大大提高查询效率。
```
EXPLAIN
```
登录后复制
结果的
```
Extra
```
登录后复制
列显示
```
Using index
```
登录后复制
就表示使用了覆盖索引。
避免索引失效：
- 不要在索引列上使用函数（如
```
WHERE DATE(create_time) = CURDATE()
```
  登录后复制
  ）。
- 避免在
```
WHERE
```
  登录后复制
  子句中对索引列进行隐式类型转换。
- ```
LIKE '%keyword'
```
  登录后复制
  这样的模糊匹配，如果
```
%
```
  登录后复制
  在前面，索引会失效。
- ```
OR
```
  登录后复制
  条件可能导致索引失效，可以考虑
```
UNION ALL
```
  登录后复制
  或拆分查询。

3. SQL语句重写

*避免 `SELECT `**：只选择你需要的列，减少数据传输和内存消耗。
优化
JOIN
登录后复制
语句：确保
```
JOIN
```
登录后复制
条件有索引，并且连接顺序合理。小表驱动大表通常是好的策略。
减少子查询：很多子查询可以通过
```
JOIN
```
登录后复制
或
```
EXISTS
```
登录后复制
语句来优化。
分页优化：对于大的偏移量分页（如
```
LIMIT 100000, 10
```
登录后复制
），可以通过先查询ID再关联的方式进行优化，避免全表扫描大量数据。
批量操作：将多条
```
INSERT
```
登录后复制
或
```
UPDATE
```
登录后复制
语句合并成一条批量语句，减少网络往返和事务开销。

4. 数据库结构优化

数据类型选择：选择最小且合适的数据类型，比如能用
```
INT
```
登录后复制
就不用
```
BIGINT
```
登录后复制
，能用
```
VARCHAR(100)
```
登录后复制
就不用
```
VARCHAR(255)
```
登录后复制
。
范式与反范式：在性能要求高的场景，可以考虑适当的反范式设计，通过增加冗余来减少
```
JOIN
```
登录后复制
操作，但要权衡数据一致性维护的成本。
分区表：对于超大表，可以考虑使用分区表，将数据分散到不同的物理存储中，提高查询效率，尤其是针对时间范围的查询。