定位 MySQL 慢查询

原创已于 2024-10-15 10:47:41 修改 · 1.9k 阅读

28 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #adb #数据库

于 2024-09-19 11:59:21 首次发布

数据库专栏收录该内容

8 篇文章

订阅专栏

定位 MySQL 慢查询可以通过以下几个步骤进行，主要是启用慢查询日志、分析查询性能、优化 SQL 语句及数据库结构。

1. 启用慢查询日志

慢查询日志可以帮助记录执行时间较长的 SQL 语句。可以通过修改 MySQL 配置文件（my.cnf 或 my.ini）来启用它。

步骤：

打开 MySQL 配置文件：

sudo nano /etc/my.cnf

或

sudo nano /etc/mysql/my.cnf

在 [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 服务：
```
sudo service mysql restart
```
验证是否启用成功：登录 MySQL，执行以下命令查看慢查询日志状态：
```
SHOW VARIABLES LIKE 'slow_query_log';
```

2. 分析慢查询日志

启用慢查询日志后，可以通过 MySQL 自带的工具 mysqldumpslow 来分析日志文件，找出频率高、执行时间长的查询。

常见用法：

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

这条命令按查询的执行时间排序，输出慢查询日志中的内容。

其他参数：

-s：指定排序方式，常用有 t (执行时间)、c (查询次数)。
-t N：显示前 N 条记录。
-a：显示查询的完整内容（不做缩略）。

例如，查看执行时间最长的 10 条查询：

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

3. 使用 MySQL 自带的性能工具

MySQL 提供了内置的性能分析工具，例如 EXPLAIN 和 SHOW PROFILE，可以帮助分析查询执行的效率和细节。

1. 使用 `EXPLAIN` 分析 SQL 查询

EXPLAIN 命令可以显示查询的执行计划，帮助了解查询是如何处理的，是否使用了索引等。

EXPLAIN SELECT * FROM users WHERE id = 123;

EXPLAIN 的关键字段：

type：查询类型，理想情况下应为 index 或 ref，避免 ALL（全表扫描）。
key：使用的索引，如果该字段为空，表示没有使用索引。
rows：预估扫描的行数，行数越大，查询的性能通常越差。
extra：额外信息，常见的如 Using filesort、Using temporary，这些通常会影响查询性能。

通过 EXPLAIN，可以知道查询是否使用了索引，是否有全表扫描的情况，从而针对性地优化 SQL 语句和数据库结构。

2. 使用 `SHOW PROFILE` 分析查询的执行过程

SHOW PROFILE 可以用来查看 SQL 查询的详细执行过程。

SET profiling = 1;

执行你要分析的查询：

SELECT * FROM users WHERE id = 123;

然后查看查询的执行过程：

SHOW PROFILES; SHOW PROFILE FOR QUERY <query_id>;

关键步骤：

查看查询执行的各个阶段耗时。
关注查询中的瓶颈，比如是否有等待锁、文件 I/O 等。

4. 使用性能监控工具

除了 MySQL 内置工具，也可以使用外部工具来分析慢查询，以下是一些常用的性能监控和优化工具：

1. MySQL Enterprise Monitor

MySQL 官方提供的监控工具，可以帮助监控和分析 MySQL 的性能瓶颈，适合企业级应用。

2. pt-query-digest

pt-query-digest 是 Percona 提供的一个工具，能够分析慢查询日志、普通查询日志或 binlog，并生成详细的分析报告。

安装方式：

sudo apt install percona-toolkit

使用 pt-query-digest 分析慢查询日志：

pt-query-digest /var/log/mysql/mysql-slow.log

它可以帮你分析出哪些查询占用了大量的资源、频率较高的查询是什么，并提供优化建议。

3. MySQL Workbench

MySQL Workbench 提供了一个图形化界面，可以用来分析慢查询日志、查看查询执行计划、优化 SQL 语句等。

5. 优化查询和数据库设计

找到慢查询后，进行优化时可参考以下思路：

1. 创建索引

对常用的查询条件（如 WHERE、JOIN、GROUP BY 中的字段）创建索引。
对频繁访问的列，如主键和外键列，建立索引。
注意不要对每个列都创建索引，过多的索引会影响写操作性能。

2. 避免全表扫描

确保查询语句尽可能使用索引，避免全表扫描。
对于大表的查询，可以考虑分页查询、分区表等策略来减少扫描的行数。

3. 优化 SQL 语句

避免使用 SELECT *，只选择需要的字段。
避免在 WHERE 子句中使用不带索引的函数或计算，可能会导致索引失效。

4. 缓存

使用缓存（如 Redis、Memcached）来存储频繁查询的数据，避免每次都访问数据库。

5. 垂直或水平拆分

如果单张表数据过大，可以考虑对表进行垂直（按列）或水平（按行）拆分，减小表的大小，提升查询效率。

6.结束慢sql进程

1.通过SHOW FULL PROCESSLIST，查找正在执行的慢查询的进程ID

2.使用KILL <process_id>命令，终止对应慢查询的进程ID