如何在MySQL中优化慢查询？慢查询日志分析与优化的实用技巧！-mysql教程-PHP中文网

优化MySQL慢查询的核心是通过慢查询日志识别问题SQL，利用EXPLAIN分析执行计划，针对性地进行索引优化、SQL重写、结构调整和配置调优，并持续验证效果。

如何在mysql中优化慢查询？慢查询日志分析与优化的实用技巧！

优化MySQL慢查询的核心，在于一套系统性的识别、分析和改进流程。这不仅仅是技术活，更像是一场侦探游戏，你需要从蛛丝马迹中找出性能瓶颈，然后用最合适的方法去解决它。通常，这涉及对SQL语句本身的优化、合理地创建和使用索引、以及在必要时调整数据库结构或服务器配置。

解决方案

解决MySQL慢查询问题，我通常会遵循以下几个步骤，这几乎成了一种肌肉记忆：

第一步，也是最重要的一步，是识别问题。我们需要知道哪些查询是慢的，它们到底慢在哪里。这通常通过开启MySQL的慢查询日志（slow query log）来实现。日志会记录执行时间超过

long_query_time

登录后复制

阈值的SQL语句。有时候，我也会关注那些没有使用索引的查询，即使它们执行得不算太慢，但长期来看，也可能成为潜在的性能隐患。

第二步是分析问题。拿到慢查询日志后，直接看原始日志会很头疼，因为数据量可能非常大。这时，我通常会借助

mysqldumpslow

登录后复制

或

pt-query-digest

登录后复制

这类工具来聚合和分析日志，它们能帮我找出出现频率最高、总耗时最长的那些“罪魁祸首”。然后，我会针对这些高风险查询，使用

EXPLAIN

登录后复制

命令来查看它们的执行计划。

EXPLAIN

登录后复制

的输出是理解查询如何被MySQL执行的关键，它会告诉我是否使用了索引、使用了哪个索引、扫描了多少行、是否进行了文件排序（filesort）或使用了临时表（using temporary），这些都是判断查询效率的重要指标。

第三步是制定并实施优化策略。根据

EXPLAIN

登录后复制

的分析结果，我通常会从几个方面着手：

索引优化： 这是最常见也最有效的手段。如果
```
EXPLAIN
```
登录后复制
显示查询没有使用到索引，或者使用了效率低下的全表扫描（
```
type: ALL
```
登录后复制
），那么我首先会考虑为
```
WHERE
```
登录后复制
、
```
JOIN
```
登录后复制
、
```
ORDER BY
```
登录后复制
或
```
GROUP BY
```
登录后复制
子句中涉及的列创建合适的索引。这可能是一个单列索引，也可能是一个复合索引，甚至是一个覆盖索引（covering index），让查询直接从索引中获取所有需要的数据，避免回表。
SQL语句重写： 有时候，SQL语句本身的写法就有问题。例如，避免在
```
WHERE
```
登录后复制
子句的列上使用函数操作，这会导致索引失效。减少
```
SELECT *
```
登录后复制
的使用，只选择需要的列。优化
```
JOIN
```
登录后复制
的顺序，确保小表驱动大表。对于复杂的查询，可以考虑拆分成多个简单查询，或者使用子查询、
```
UNION
```
登录后复制
等进行重构。
```
LIMIT
```
登录后复制
子句在分页查询中尤其重要，结合合适的
```
ORDER BY
```
登录后复制
和索引，能大幅提升性能。
数据库结构优化： 在某些极端情况下，可能需要重新审视数据库表结构。比如，字段类型是否合适？是否可以进行适当的冗余（反范式化）来避免复杂的JOIN操作？分区表（Partitioning）在处理超大表时也能发挥作用。
服务器配置调整： 这通常是最后的手段，但也很关键。例如，增加
```
innodb_buffer_pool_size
```
登录后复制
以缓存更多数据和索引，调整
```
tmp_table_size
```
登录后复制
和
```
max_heap_table_size
```
登录后复制
来减少磁盘上的临时表操作，或者优化
```
query_cache_size
```
登录后复制
（尽管在MySQL 8.0中已被移除，但在老版本中仍有用）。

最后一步是验证优化效果。我不会盲目地认为优化就成功了，而是会再次运行慢查询，查看其执行时间，并再次使用

EXPLAIN

登录后复制

确认执行计划是否真的改善了。有时候，一个优化可能会引入新的问题，或者在不同的负载下表现不一，所以持续的监控和调整是必不可少的。

慢查询日志是你的第一道防线：如何有效配置与解读？

说实话，每次我接手一个新项目，第一件事就是检查慢查询日志是否开启。如果没开，那感觉就像蒙着眼睛开车，你根本不知道问题出在哪里。有效配置慢查询日志是识别性能瓶颈的基础。

要开启慢查询日志，你需要在

my.cnf

登录后复制

（或者

my.ini

登录后复制

）配置文件中添加或修改以下几行：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

登录后复制

```
slow_query_log = 1
```
登录后复制
：这行是开启慢查询日志的开关。
```
slow_query_log_file
```
登录后复制
：指定日志文件的路径。确保MySQL用户对这个路径有写入权限。
```
long_query_time = 1
```
登录后复制
：这个参数定义了查询执行时间的阈值（单位：秒）。任何执行时间超过1秒的查询都会被记录。这个值可以根据实际情况调整，比如设置为0.5秒，或者在压力测试时设为0，记录所有查询。
```
log_queries_not_using_indexes = 1
```
登录后复制
：这行非常重要。它会记录那些没有使用索引的查询，即使它们的执行时间没有超过
```
long_query_time
```
登录后复制
。很多时候，一个看似很快的查询，如果没用到索引，在大数据量下就会变成定时炸弹。

配置完成后，重启MySQL服务。日志文件就会开始记录慢查询了。

日志文件本身是纯文本格式，直接看会比较吃力，尤其是在高并发的生产环境中。这时，

mysqldumpslow

登录后复制

和

pt-query-digest

登录后复制

就派上用场了。

mysqldumpslow

登录后复制

是MySQL自带的工具，用起来比较简单：

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

登录后复制

这个命令会按平均查询时间（

at

登录后复制

）排序，显示前10条（

t 10

登录后复制

）慢查询。你还可以用

-s c

登录后复制

按查询次数排序，或者

-s r

登录后复制

按返回行数排序。它会将相似的查询（参数不同）聚合起来，方便你一眼看到问题最多的SQL模式。

蓝心千询

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

查看详情

而

pt-query-digest

登录后复制

（Percona Toolkit的一部分）则功能更强大，分析结果更详细，能生成HTML报告，包含各种统计数据和图表，对性能分析非常有用。它的用法也类似：

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

登录后复制

它会提供每个查询的执行次数、总耗时、平均耗时、最大耗时、锁定时间等详细信息，让你对慢查询的“画像”一目了然。我个人更倾向于

pt-query-digest

登录后复制

，因为它提供的深度分析能帮助我更快地定位问题。

掌握EXPLAIN：深入理解SQL执行计划的关键。

EXPLAIN

登录后复制

命令，在我看来，是MySQL慢查询优化中最具洞察力的工具。它能揭示MySQL是如何执行你的SQL语句的，就像一张藏宝图，告诉你数据是如何被检索、连接和排序的。

使用方法很简单，在任何

SELECT

登录后复制

、

INSERT

登录后复制

、

UPDATE

登录后复制

、

DELETE

登录后复制

语句前加上

EXPLAIN

登录后复制

即可：

EXPLAIN SELECT * FROM users WHERE age > 30 AND city = 'New York';

登录后复制

输出结果会是一个表格，其中包含多列信息，每一列都至关重要：

id: 查询的序列号。对于复杂的查询（如子查询、UNION），会有多个id，表示查询执行的顺序。
select_type: 查询的类型，比如
```
SIMPLE
```
登录后复制
（简单查询）、
```
PRIMARY
```
登录后复制
（最外层查询）、
```
SUBQUERY
```
登录后复制
（子查询）、
```
DERIVED
```
登录后复制
（派生表）等。
table: 当前操作的表名。
type: 这是最重要的列之一，表示MySQL如何找到行。它的值从好到坏依次是：
- ```
system
```
  登录后复制
  >
```
const
```
  登录后复制
  >
```
eq_ref
```
  登录后复制
  >
```
ref
```
  登录后复制
  >
```
range
```
  登录后复制
  >
```
index
```
  登录后复制
  >
```
ALL
```
  登录后复制
  。
- ```
ALL
```
  登录后复制
  ：全表扫描，性能最差。看到它，通常意味着你需要加索引或者优化SQL。
- ```
index
```
  登录后复制
  ：全索引扫描，比
```
ALL
```
  登录后复制
  好，但仍然扫描了整个索引。
- ```
range
```
  登录后复制
  ：范围扫描，通常是索引用于
```
WHERE
```
  登录后复制
  子句的
```
>
```
  登录后复制
  、
```
<
```
  登录后复制
  、
```
BETWEEN
```
  登录后复制
  等操作。
- ```
ref
```
  登录后复制
  ：非唯一索引扫描，或唯一索引的前缀扫描。
- ```
eq_ref
```
  登录后复制
  ：唯一索引或主键查找，通常用于
```
JOIN
```
  登录后复制
  操作，效率很高。
- ```
const
```
  登录后复制
  /
```
system
```
  登录后复制
  ：查询优化器将查询转换为一个常量，或者表只有一行，效率极高。
possible_keys: MySQL认为可能用到的索引。
key: MySQL实际使用的索引。如果
```
key
```
登录后复制
为NULL，说明没有使用索引。
key_len: 使用的索引的长度。对于复合索引，可以判断是否使用了索引的所有部分。
ref: 哪些列或常量被用于查找索引列上的值。
rows: MySQL估计为了找到所需的行而需要读取的行数。这个值越小越好。
Extra: 这一列提供了额外的信息，比如：
- ```
Using filesort
```
  登录后复制
  ：MySQL需要对结果进行外部排序，通常发生在没有索引支持
```
ORDER BY
```
  登录后复制
  或
```
GROUP BY
```
  登录后复制
  时，非常耗时。
- ```
Using temporary
```
  登录后复制
  ：MySQL需要创建临时表来处理查询，通常发生在
```
GROUP BY
```
  登录后复制
  或
```
DISTINCT
```
  登录后复制
  操作中，没有合适的索引支持。
- ```
Using index
```
  登录后复制
  ：表示查询直接从索引中获取所有数据，无需回表，效率极高（覆盖索引）。
- ```
Using where
```
  登录后复制
  ：表示MySQL将通过
```
WHERE
```
  登录后复制
  子句过滤结果。
- ```
Using join buffer
```
  登录后复制
  ：表示使用了连接缓冲区，通常在全连接（
```
type: ALL
```
  登录后复制
  ）或范围连接（
```
type: range
```
  登录后复制
  ）时出现。

我个人最怕在

Extra

登录后复制

列看到

Using filesort

登录后复制

和

Using temporary

登录后复制

，这通常意味着查询会很慢，需要优先优化。而

type

登录后复制

列中的

ALL

登录后复制

更是亮眼的红灯，它告诉我，我的SQL语句可能需要一次大手术。通过深入理解

EXPLAIN

登录后复制

的输出，我能精准地找到查询的症结所在，然后对症下药。

索引不是万能药：何时以及如何构建高效索引？

索引无疑是优化MySQL查询性能的“银弹”，但它绝非万能。不恰当的索引不仅不会提升性能，反而可能因为增加了写入负担、占据存储空间而拖累系统。所以，构建索引是一门艺术，需要深思熟虑。

何时需要构建索引？

我通常会考虑以下几种情况：

WHERE子句中的条件列： 这是最常见的场景。如果你的查询经常在某个列上进行过滤，比如
```
WHERE user_id = 123
```
登录后复制
或
```
WHERE status IN ('active', 'pending')
```
登录后复制
，那么这个列就非常适合建立索引。
JOIN子句中的连接列： 在多表连接查询中，
```
ON
```
登录后复制
子句中用于连接的列（外键列）应该建立索引，这能显著提升连接效率。
ORDER BY和GROUP BY子句中的列： 如果查询结果需要排序或分组，并且没有合适的索引支持，MySQL可能会进行文件排序（
```
Using filesort
```
登录后复制
）或创建临时表（
```
Using temporary
```
登录后复制
），这会非常慢。为这些列建立索引可以避免这些操作。
高基数列： 索引在那些值分布广泛、重复值较少的列上效果最好（例如用户ID、邮箱地址）。如果一个列的重复值非常多（例如性别、状态），索引的区分度不高，效果可能不明显，甚至不如全表扫描。

如何构建高效索引？

选择合适的索引类型： MySQL主要使用B-tree索引，适用于等值匹配、范围查询、排序等。对于文本列，如果只需要前缀匹配，可以考虑使用前缀索引来节省空间。
单列索引 vs. 复合索引：
- 单列索引： 当一个列频繁出现在
```
WHERE
```
  登录后复制
  子句中，且没有其他列与之组合时，可以考虑创建单列索引。
- 复合索引（联合索引）： 当查询条件中经常同时包含多个列时，可以创建复合索引。例如，
```
WHERE city = 'New York' AND age > 30
```
  登录后复制
  ，可以考虑在
```
(city, age)
```
  登录后复制
  上创建复合索引。记住“最左前缀原则”：MySQL会从索引的最左边列开始匹配。如果你的查询条件只使用了复合索引的中间或右边部分，索引可能不会被完全利用。例如，
```
(a, b, c)
```
  登录后复制
  的索引，
```
WHERE b = 1
```
  登录后复制
  就不会用到这个索引，而
```
WHERE a = 1
```
  登录后复制
  或者
```
WHERE a = 1 AND b = 2
```
  登录后复制
  则会。
覆盖索引： 如果一个查询所需的所有列都包含在索引中，那么MySQL可以直接从索引中获取数据，而无需回表查询数据行。这能极大地提升查询速度，因为避免了随机I/O。例如，
```
SELECT name, email FROM users WHERE city = 'New York'
```
登录后复制
，如果在
```
(city, name, email)
```
登录后复制
上建立复合索引，就可能实现覆盖索引。
避免过度索引： 索引会占用磁盘空间，并且在数据插入、更新、删除时需要维护，这会增加写操作的开销。对于写操作频繁的表，过多的索引反而会降低整体性能。我通常会根据实际的查询模式来创建索引，而不是盲目地为每个可能被查询的列都加上索引。
定期审查和优化： 数据库的访问模式会随着时间变化。一个曾经高效的索引可能变得不再适用，或者新的查询模式需要新的索引。定期使用
```
pt-index-usage
```
登录后复制
或
```
sys.schema_unused_indexes
```
登录后复制
来检查索引的使用情况，删除不常用的索引，调整现有索引，是保持数据库性能的关键。

构建索引，就像是在图书馆里给书分类和编号。分类越合理，找书就越快。但如果分类太多太乱，或者把每本书都编了好几个号，那管理起来就会变成噩梦。平衡读写需求，找到那个最佳点，才是索引优化的真谛。

以上就是如何在MySQL中优化慢查询？慢查询日志分析与优化的实用技巧！的详细内容，更多请关注php中文网其它相关文章！