MySQL 慢查询定位与优化实战：从慢日志、EXPLAIN 到复合索引设计

  1.  Home
  2. Text  
    3. Share to：

慢查询才是个人网站最隐蔽的性能杀手

很多站长优化网站时，第一反应是折腾 nginx 缓存、上 CDN、压缩图片，却忽略了一个事实：一个没有索引的 SQL 查询，能让你的页面从 50 毫秒变成 5 秒。而更要命的是，随着数据量增长，这个问题是"渐进式恶化"的——今天不慢，不代表半年后不慢。

这篇文章以 MySQL 为例，讲一套完整的慢查询定位与优化流程：怎么开慢日志、怎么看懂 EXPLAIN、索引到底该怎么建、以及那些看起来"应该走索引"却死活不走索引的经典陷阱。

一、先把慢查询日志打开

MySQL 5.7 和 8.0 都已经内置慢查询日志，但默认是关闭的。登录 MySQL 后执行：

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

如果 slow_query_log 是 OFF，说明还没开。推荐在 my.cnf 里永久配置，而不是每次用 SET GLOBAL（重启就丢）：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
min_examined_row_limit = 100

几个参数值得解释：

  • long_query_time = 1：超过 1 秒的记录。刚开始排查时可以先设成 0.5 甚至 0.1，把问题捞干净了再调回来，避免日志爆炸。
  • log_queries_not_using_indexes：记录所有没走索引的查询。这个非常重要，因为很多"够快但没索引"的查询是未来的定时炸弹。注意打开后日志量会很大，只建议临时开。
  • min_examined_row_limit：扫描行数低于这个值的不记录，可以有效过滤掉大量琐碎的小表查询。

配置好之后别忘了 systemctl restart mysql，然后确认目录权限——/var/log/mysql/ 必须属于 mysql 用户，否则 MySQL 起不来，这个坑新手很容易踩。

二、用 mysqldumpslow 快速找出 TOP 问题

慢日志直接看是没效率的，几百行里混着各种重复查询。用官方自带的 mysqldumpslow 做聚合：

# 按总耗时排序，取前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按平均耗时排序（找出"单次最慢"的）
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# 按执行次数排序（找出"最频繁"的）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

这里有个实战经验：平均耗时高的查询和总耗时高的查询，要分开看。

  • 平均耗时高 = 单次执行就很慢，通常是 SQL 写法或索引问题；
  • 总耗时高 = 单次可能只有 200ms，但一秒执行几百次，这种往往是缺少缓存或者业务逻辑有问题。

如果觉得 mysqldumpslow 太简陋，可以装一个 pt-query-digest（Percona Toolkit 的一部分），它输出的报告要细致得多，还能直接看查询的"响应时间分布直方图"。

三、看懂 EXPLAIN：三个字段最关键

定位到具体 SQL 后，第一步永远是加 EXPLAIN。MySQL 8.0 还可以用 EXPLAIN ANALYZE，输出真实的执行耗时。

EXPLAIN SELECT * FROM typecho_contents
WHERE authorId = 1 AND created > 1700000000
ORDER BY created DESC LIMIT 20;

输出里字段很多，但对个人站长来说，先盯住这三个：

1. type —— 访问类型

从好到坏的顺序大致是：system > const > eq_ref > ref > range > index > ALL。

  • 看到 ALL 就是全表扫描，100% 要优化（除非表真的只有几十行）；
  • index 表示扫了整个索引树，虽然用了索引但依然是全扫，也要警惕；
  • 目标是 ref 或 range。

2. rows —— 预估扫描行数

这个数字是 MySQL 优化器估算的，不精确但有参考价值。如果一条查询只需要 20 行结果，但 rows 显示 50 万，说明索引没起作用。

3. Extra —— 额外信息里藏着真相

这个字段最容易被人忽略，但信息量最大：

  • Using filesort：需要额外排序，通常意味着 ORDER BY 的字段没有用上索引顺序。数据量大时非常致命。
  • Using temporary：用了临时表，常见于 GROUP BY 或 DISTINCT。大表上遇到这个要高度警惕。
  • Using index：好现象，说明是"覆盖索引"——查询需要的所有列都在索引里，不需要回表。
  • Using where：正常，表示在存储引擎返回结果后又过滤了一次。

四、索引到底该怎么建

索引不是越多越好。每多一个索引，写入时就要多维护一棵 B+ 树，还会占磁盘空间。核心原则是最左前缀原则——复合索引 (a, b, c) 相当于同时提供了 (a)、(a,b)、(a,b,c) 三种索引能力，但单独查 b 或 c 用不上。

回到前面的查询：

SELECT * FROM typecho_contents
WHERE authorId = 1 AND created > 1700000000
ORDER BY created DESC LIMIT 20;

理想的复合索引是：

ALTER TABLE typecho_contents ADD INDEX idx_author_created (authorId, created);

为什么是这个顺序？记住两个规则：

  1. 等值条件放前面，范围条件放后面。因为 B+ 树在遇到范围条件后，后面的列就无法再用索引有序性来加速了。
  2. ORDER BY 的列要尽量接在等值列之后。这样 MySQL 可以直接按索引顺序读，省掉 filesort。

如果反过来建成 (created, authorId)，那么 created > ... 这个范围条件后面的 authorId 就走不了索引，效果会差很多。

五、那些"看起来该走索引却不走"的陷阱

陷阱一：对索引列做函数运算

-- 走不了索引
SELECT * FROM posts WHERE DATE(created) = '2026-09-19';

-- 改成范围查询，可以走索引
SELECT * FROM posts
WHERE created >= '2026-09-19 00:00:00'
  AND created < '2026-09-20 00:00:00';

只要在索引列外面套了函数，MySQL 就无法使用索引的有序性，只能全表扫描。

陷阱二：隐式类型转换

-- phone 是 varchar 类型，但传了数字，会发生隐式转换
SELECT * FROM users WHERE phone = 13800138000;

-- 加引号，类型匹配，索引生效
SELECT * FROM users WHERE phone = '13800138000';

这个坑特别隐蔽，因为 EXPLAIN 里 type 会变成 ALL，而很多人看到 SQL 写得"很干净"就不会怀疑。注意 PHP 里拼接 SQL 时很容易踩到——$_GET['id'] 拿到的是字符串，但如果用 intval() 转成整数去比 varchar 字段，就会触发转换。

陷阱三：前导通配符的 LIKE

-- 不走索引
SELECT * FROM posts WHERE title LIKE '%nginx%';

-- 走索引
SELECT * FROM posts WHERE title LIKE 'nginx%';

前缀不确定的模糊查询，B+ 树无能为力。真要做全文搜索，应该用 MySQL 的全文索引（FULLTEXT）或者上 Elasticsearch / Meilisearch，而不是靠 LIKE 硬撑。

陷阱四：OR 条件连接不同列

-- 通常不走索引
SELECT * FROM posts WHERE authorId = 1 OR categoryId = 2;

-- 改成 UNION，两边各自走索引
SELECT * FROM posts WHERE authorId = 1
UNION
SELECT * FROM posts WHERE categoryId = 2;

六、优化不只是加索引

有些慢查询，加索引解决不了，得从别的角度想：

  • 分页深翻页：LIMIT 100000, 20 这种写法要扫描 10 万行再丢掉。改用游标分页：WHERE id < 上一页最后ID ORDER BY id DESC LIMIT 20，性能是数量级的差别。
  • SELECT * 改按需取列：少取列有时能让查询命中覆盖索引，直接免掉回表。
  • 大字段单独拆表：文章正文这种 TEXT 字段如果和列表查询混在一张表，每次查询都会拖慢。拆出去单独存，列表页查询会快很多。
  • 加缓存层：热门页面结果直接塞 Redis，把 90% 的读压力挡在数据库外面。

七、小结

MySQL 慢查询优化的完整链路是：开慢日志 → 聚合排序找 TOP → EXPLAIN 定位 → 建对的索引 → 验证效果。整个过程不需要什么高深技术，但需要耐心。

对个人站长来说，最实用的建议是：在网站上线时就打开慢查询日志，并且设一个简单的自动巡检——每天看一眼慢查询数量，一旦突然上涨就提前介入。等到用户反馈"你网站好慢"再动手，往往已经流失了一批访客了。

Last modification：September 19th, 2026 at 12:23 pm
© 允许规范转载

相关文章

Leave a Comment