MySQL性能优化：深入分析慢查询的排查与解决

rafavi

一、什么是MySQL慢查询？
MySQL慢查询是指执行时间超过预设阈值的查询操作。在查询量大的情况下，慢查询会占用大量资源，影响系统的响应速度。因此，通过排查慢查询并进行优化，是提升系统性能的一个重要环节。

二、MySQL慢查询日志开启
首先，我们需要确保MySQL慢查询日志功能已经开启，才能记录慢查询信息。

1. 配置慢查询日志
可以通过修改my.cnf（或者my.ini）配置文件来启用慢查询日志，或者通过动态修改MySQL的参数。



[mysqld]
# 启用慢查询日志
slow_query_log = 1
# 慢查询日志的保存路径
slow_query_log_file = /var/log/mysql/slow-query.log
# 设置查询时间阈值，单位为秒。默认值为10秒，可以根据需要调整
long_query_time = 2
# 是否记录不使用索引的查询，0表示不记录，1表示记录
log_queries_not_using_indexes = 1


修改完配置文件后，重启MySQL服务：

1. sudo systemctl restart mysql

复制代码

2. 动态启用慢查询日志
除了配置文件中的设置，还可以通过MySQL命令行动态启用慢查询日志，方便进行临时监控。


SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

执行完以上命令后，慢查询日志将开始记录并保存到指定文件。

三、查看慢查询日志
MySQL会将慢查询日志保存在指定的文件中（如/var/log/mysql/slow-query.log）。你可以使用cat、less等命令查看日志内容，也可以利用MySQL自带的mysqldumpslow工具进行分析。

# 查看慢查询日志

1. cat /var/log/mysql/slow-query.log

复制代码

# 使用mysqldumpslow工具进行分析，汇总查询统计

1. mysqldumpslow /var/log/mysql/slow-query.log

复制代码

四、慢查询排查步骤
1. 分析慢查询日志
慢查询日志中会记录查询的SQL语句、执行时间、锁定时间、扫描的行数等信息。通过日志内容，我们可以快速发现哪些SQL语句执行较慢。

例如，以下是一条典型的慢查询日志内容：

# Time: 2025-03-25T12:34:56.000000Z
# User@Host: root[root] @ localhost []
# Query_time: 12.345678  Lock_time: 0.000234  Rows_sent: 1000  Rows_examined: 5000
# SELECT * FROM orders WHERE status = 'pending' AND order_date < '2025-01-01';

其中，Query_time表示查询时间，Rows_sent表示查询返回的行数，Rows_examined表示扫描的行数，Lock_time表示锁定时间。通过这些信息，我们可以判断出是哪个SQL语句的执行时间过长。

2. 分析高查询时间的SQL
通过日志分析，找到执行时间较长的SQL语句后，下一步是优化这些SQL语句。常见的优化方法包括：

优化索引：检查查询是否使用了合适的索引。通过EXPLAIN命令可以查看SQL执行计划，判断是否使用了索引。

1. EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND order_date < '2025-01-01';

复制代码

如果查询没有使用索引，可以通过创建合适的索引来提高查询性能。

避免全表扫描：如果查询涉及的表数据量很大，且没有合适的索引，查询可能会进行全表扫描。应考虑创建合适的索引或优化查询条件。

避免不必要的SELECT *：SELECT *会返回所有列，而如果只需要部分列数据，应该显式指定需要的列，以减少查询的返回量。

查询拆分与分页：对于涉及大量数据的查询，可以考虑通过分页查询或者拆分查询，减少单次查询的数据量。

3. 使用SHOW PROCESSLIST查看当前正在执行的查询
当数据库性能较差时，可以通过SHOW PROCESSLIST查看当前正在执行的SQL语句，分析是否存在锁等待、长时间未完成的查询等问题。

1. SHOW PROCESSLIST;

复制代码

这条命令将列出当前所有连接的状态信息，包括执行的查询、查询执行的时间等。通过这些信息，可以判断出哪些查询正在执行较长时间，并进一步进行优化。

4. 分析系统性能瓶颈
如果某些SQL查询在优化后仍然很慢，可能是由于系统资源不足导致的。可以通过以下方法检查系统的性能瓶颈：

查看CPU使用情况：可以使用top命令查看CPU的使用情况，如果CPU使用率过高，可能是由于查询操作占用了过多的CPU资源。

1. top

复制代码

查看磁盘I/O：如果磁盘I/O瓶颈严重，查询性能可能会受到影响。可以通过iostat命令查看磁盘I/O情况。

1. iostat -x 1

复制代码

查看内存使用情况：MySQL需要足够的内存来缓存数据，内存不足可能导致频繁的磁盘读取，进而影响查询性能。

1. free -h

复制代码

5. 优化数据库配置
除了SQL语句和索引的优化，数据库的配置也是影响性能的重要因素。例如，调整innodb_buffer_pool_size、query_cache_size等参数，能够显著提高数据库性能。

调整InnoDB Buffer Pool大小：InnoDB使用Buffer Pool来缓存数据和索引，适当增加其大小可以减少磁盘I/O，提高查询性能。

1. SET GLOBAL innodb_buffer_pool_size = 2G;

复制代码

调整查询缓存：如果查询缓存未启用，可以考虑启用查询缓存，尤其是对于读多写少的应用场景。

1. SET GLOBAL query_cache_size = 64M;

复制代码

五、实战案例
1. 慢查询案例
假设我们有一个订单表orders，需要查询所有“待处理”状态且订单日期早于2025年1月1日的订单。在慢查询日志中，我们发现如下查询：

1. SELECT * FROM orders WHERE status = 'pending' AND order_date < '2025-01-01';

复制代码

执行时间为15s，扫描了5000行，返回了1000行数据。

2. 优化步骤
使用索引：通过EXPLAIN查看，发现没有使用索引，针对status和order_date字段创建复合索引。

1. CREATE INDEX idx_status_order_date ON orders(status, order_date);

复制代码

避免全表扫描：创建复合索引后，查询只需要扫描相关的索引，而不是全表扫描。

修改查询：避免使用SELECT *，只查询需要的列。

1. SELECT order_id, customer_id, order_date FROM orders WHERE status = 'pending' AND order_date < '2025-01-01';

复制代码

通过以上优化，查询性能得到了显著提升，执行时间从15秒降低到1秒以内。

六、EXPLAIN及SHOW PROCESSLIST执行结果分析
1. EXPLAIN命令分析
EXPLAIN命令用于展示MySQL执行SQL查询时的执行计划，它能帮助开发者理解查询的执行过程，并指导索引优化。以下是对上面查询的EXPLAIN分析：

1. EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND order_date < '2025-01-01';

复制代码

执行结果可能如下所示：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type       | key  | key_len       | ref  | rows    | Extra|
+----+-------------+--------+------------+------+---------------+------+---------+------|------|
|  1 | SIMPLE      | orders | ALL        | NULL | NULL          | NULL | 5000    | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------|------+

table: 目标表格是orders。
type: 显示查询的连接类型，ALL表示全表扫描，效率较低。这里表明没有使用索引，导致查询的执行效率低。
rows: 显示MySQL估算需要扫描的行数，这里为5000行。
通过创建复合索引idx_status_order_date后，再次执行EXPLAIN命令，结果可能如下：

+----+-------------+--------+--------+--------------------------+---------+------+------+-------------+
| id | select_type | table  | type   | key                      | key_len | ref  | rows    | Extra       |
+----+-------------+--------+--------+--------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | range  | idx_status_order_date     | 5       | NULL | 1000    | Using where |
+----+-------------+--------+--------+--------------------------+---------+------+------+-------------+

type: 这里变成了range，表示MySQL使用了索引来执行范围查询，相比全表扫描有了显著提高。
rows: 扫描的行数从5000减少到了1000行，证明索引有效地减少了查询的范围。
2. SHOW PROCESSLIST分析
SHOW PROCESSLIST命令可以显示当前MySQL的所有活动线程，它能帮助我们查看哪些查询正在执行，并分析其执行时间。

例如，执行：

1. SHOW PROCESSLIST;

复制代码

输出结果可能如下：

+----+------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host      | db      | Command | Time | State | Info             |
+----+------+-----------+---------+---------+------+-------+------------------+
| 1  | root | localhost | testdb  | Query   | 45   | Locked| SELECT * FROM orders WHERE status = 'pending' AND order_date < '2025-01-01'; |
+----+------+-----------+---------+---------+------+-------+------------------+

Time: 查询执行时间为45秒，表示该查询已运行了较长时间，可能存在性能问题。
State: 显示查询的当前状态，Locked表示该查询可能正在等待锁，可能是因为表或行正在被其他查询占用。
通过分析SHOW PROCESSLIST的输出结果，可以进一步确定系统中是否存在长时间运行的查询，帮助开发者找到可能的瓶颈和锁等待问题。

七、总结
MySQL慢查询排查和优化是一个系统化的过程，涵盖了日志分析、SQL优化、索引设计、系统配置等多个方面。通过合理配置慢查询日志、分析慢查询日志中的信息、优化SQL语句、调整数据库配置等方法，我们可以有效提高数据库的性能。希望本文提供的步骤和技巧能帮助大家在实际生产环境中排查和解决慢查询问题。


原文链接