MySQL 慢查询优化实战：从慢日志定位到索引设计的完整思路

  1.  Home
  2. Text  
    3. Share to：

网站变慢，很多人第一反应是换服务器、上 CDN，折腾一圈发现治标不治本。其实个人网站最常见的性能瓶颈根本不在网络，而在数据库：一条 SQL 语句执行 3 秒，用户一多，MySQL 连接被慢查询占满，后面所有请求排队，整个站就"卡死"了。数据库优化的第一步，是把慢查询找出来，而 MySQL 自带的慢查询日志就是最直接的入口。这篇文章从开启慢查询日志讲起，到用工具分析、用 EXPLAIN 看执行计划、设计索引，最后给出几个实战优化案例，给你一条完整的 MySQL 调优路径。

一、为什么要关注慢查询

个人网站的数据库通常不大，几千篇文章、几万条评论，正常查询都是毫秒级。但架不住查询写得不好：评论表没有索引，每次加载文章都要全表扫描；统计接口用函数包住索引列，索引失效；列表页深翻页一次扫几万行。这些慢查询平时单个看不出来，一旦有搜索、采集、攻击流量进来，立刻放大成整站故障。慢查询日志记录了执行时间超过阈值的所有 SQL，是定位数据库问题的第一手资料，开启它不需要重启，随时可以关，成本几乎为零。

二、开启慢查询日志

在 my.cnf 的 [mysqld] 段加入以下配置（改完需要重启 MySQL 生效）：

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

各参数含义：slow_query_log 开启慢查询日志；slow_query_log_file 指定日志文件路径，注意目录要有 mysql 用户的写权限；long_query_time = 2 表示执行时间超过 2 秒的查询才会被记录；log_queries_not_using_indexes 把没有使用索引的查询也记录下来，哪怕它执行得很快——这类查询是隐患，数据量一大就会变慢；min_examined_row_limit = 100 过滤掉扫描行数少于 100 的查询，避免日志被琐碎语句刷屏。如果不想重启 MySQL，也可以动态开启：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

注意两点：long_query_time 修改后，已经建立的连接不生效，新连接才会用新阈值；如果设置后想验证，执行 SHOW VARIABLES LIKE 'slow_query%'; 查看当前值即可。

慢查询日志会一直增长，跑上一年可能占好几个 GB 磁盘，别忘了用 logrotate 做轮转。在 /etc/logrotate.d/ 下建一个 mysql 配置文件，按天切分、保留 30 份、旧日志压缩：

/var/log/mysql/mysql-slow.log {
    daily
    rotate 30
    compress
    missingok
    notifempty
}

这里有个容易踩的坑：logrotate 把日志文件改名后，MySQL 进程还握着旧文件的句柄，继续往旧文件里写，新日志不会进新文件，轮转等于没转。解决方法是在 logrotate 配置里加上 postrotate 脚本，轮转后执行 mysqladmin flush-logs 让 MySQL 重新打开日志文件：

    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript

如果是 Debian/Ubuntu 用 apt 安装的 MySQL，安装包自带 /etc/logrotate.d/mysql-server 配置，一般已经处理好这些细节，直接确认一下是否生效即可。

三、用 mysqldumpslow 和 pt-query-digest 分析慢日志

慢日志跑一段时间后会有大量记录，人工逐条看效率太低，用工具按维度汇总。MySQL 自带的 mysqldumpslow 最常用：

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log   # 按总执行时间排序，取前 10 条
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log   # 按出现次数排序，找出高频慢查询
mysqldumpslow -s l -t 10 /var/log/mysql/mysql-slow.log   # 按锁等待时间排序

mysqldumpslow 会把结构相同、参数不同的 SQL 归并成一条（用 N 代替具体数字），这样能一眼看出"哪一类"查询最慢。进阶工具是 Percona Toolkit 里的 pt-query-digest，它给出每个查询的耗时占比、扫描行数统计和优化建议，是 DBA 的标配：

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

慢日志里每条记录都带执行时间（Query_time）、锁等待时间（Lock_time）、扫描行数（Rows_examined）和返回行数（Rows_sent）。看到 Rows_examined 几万而 Rows_sent 只有几十，基本可以断定是全表扫描或索引没生效，这就是优化的方向。

四、用 EXPLAIN 读懂执行计划

定位到慢 SQL 之后，下一步是看 MySQL 到底怎么执行它的：

EXPLAIN SELECT * FROM comments WHERE post_id = 123 ORDER BY created DESC;

EXPLAIN 输出的每一行代表一个表，重点看这几列：

  • type：访问类型，性能从好到差大致是 const、eq_ref、ref、range、index、ALL。ALL 是全表扫描，index 是扫全索引，这两种都要警惕；range 是范围扫描（比如 >、<、BETWEEN、IN），ref 是等值匹配，都算健康；
  • key：实际使用的索引，为 NULL 说明这条查询没用上任何索引，是优化重点；possible_keys 列出可能用到的索引，两者对比能看出 MySQL 为什么没选；
  • rows：预估扫描的行数，这个数字从几万降到几十，说明索引生效了；
  • Extra：最值得关注的提示。Using index 表示覆盖索引，性能最好；Using where 是普通过滤；Using filesort 表示排序没走索引，要额外排序；Using temporary 表示用了临时表，常见于 GROUP BY 和去重，这两者都是优化信号。

举个典型例子：一条查询 type=ALL、rows=50000，给 where 条件里的列加上索引后，再 EXPLAIN 一次，type 变成 ref、rows 变成 5，执行时间从秒级降到毫秒级，这就是索引的威力。

五、索引设计的核心规则

索引不是越多越好，而是要建在刀刃上。几条核心规则：第一，单列索引直接建在 where、order by、join 用到的列上：

CREATE INDEX idx_post_id ON comments(post_id);
CREATE INDEX idx_created ON posts(created);

第二，联合索引遵循最左前缀原则。比如建了联合索引 (cat_id, created, status)，那么 cat_id、cat_id+created、三列全用都能命中索引，但单独用 created 或 status 开头就命中不了。设计联合索引时，把区分度高的列放前面，把最常用的查询条件放最左；第三，覆盖索引：如果查询的列全部包含在索引里，Extra 会显示 Using index，MySQL 直接读索引就能返回结果，不用回表，性能最好，可以把高频查询的列一起放进索引；第四，注意索引失效的典型场景：

  • 对索引列使用函数：WHERE YEAR(created) = 2026 会让索引失效，应改写成 WHERE created >= '2026-01-01' AND created < '2027-01-01'；
  • 隐式类型转换：varchar 类型的手机号列用数字去查（WHERE phone = 13800000000），MySQL 会把列转成数字比较，索引失效，应写成 WHERE phone = '13800000000'；
  • LIKE 前导通配符：LIKE '%关键词%' 无法用索引，只有 LIKE '关键词%' 才能命中；
  • OR 连接非索引列：WHERE a = 1 OR b = 2，只要 b 没索引，整个查询就可能全表扫描；
  • 联合索引不满足最左前缀。

最后，别过度建索引：每个索引都要占磁盘空间，拖慢写入速度。个人站先把最频繁的查询语句收集起来，给它们的 where 和 order by 列建索引就够了。

六、三个实战优化案例

案例一：深分页。列表页常见的 SELECT * FROM posts ORDER BY id DESC LIMIT 100000, 20，MySQL 要先扫描前 10 万行再丢掉，越往后翻越慢。两种改法：游标分页（记住上一页最后一条的 id，用 WHERE id < 上次的 id ORDER BY id DESC LIMIT 20）；或者延迟关联，先查主键再回表取全字段：SELECT p.* FROM (SELECT id FROM posts ORDER BY id DESC LIMIT 100000, 20) t JOIN posts p ON p.id = t.id。案例二：ORDER BY 排序慢。Extra 出现 Using filesort 时，把排序字段和 where 条件按最左前缀建联合索引，让排序直接走索引。案例三：多表 JOIN 慢。关联字段两边都要有索引，且字段类型要一致（int 对 int、varchar 对 varchar），类型不一致会引发隐式转换导致索引失效，小表驱动大表，JOIN 条件别用函数包列。

七、优化后的验证与长期监控

优化完不是结束，要验证效果：再看一次 EXPLAIN，对比 rows 和 type 的变化；清空慢日志（可以用 truncate 或直接删文件后重建）重新积累，观察目标 SQL 是否还出现在新日志里；执行时间可以用 SHOW PROFILE 或直接在客户端计时确认。日常运维中，用 SHOW PROCESSLIST; 能实时看到正在执行的慢查询，连接占用多、某个查询卡了很久，一眼就能揪出来。建议每季度跑一次 mysqldumpslow 汇总，把新出现的慢查询逐个优化，让慢日志里的记录越来越少。

八、总结

数据库优化没有玄学，路径很清晰：开启慢查询日志找到慢 SQL，用 mysqldumpslow 汇总归类，EXPLAIN 看执行计划，对症下药建索引，改完再验证。个人网站的数据库一般都不大，绝大多数慢查询都是"没建索引"和"索引失效"两个原因，把这两类问题清干净，数据库性能基本就够用了。记住：索引是为查询服务的，先收集高频查询，再设计索引，比盲目建一堆索引高效得多。

Last modification：August 20th, 2026 at 08:47 am
© 允许规范转载

Leave a Comment