数据库是个人网站最容易出现性能瓶颈的环节。网站访问量小的时候，一条 SQL 慢个几百毫秒毫无感觉；一旦数据量涨到几十万行，索引设计不合理的话，一次全表扫描就能把数据库 CPU 打满，整站卡死。这篇文章总结我在自己的站点上排查 MySQL 慢查询的完整流程：怎么开启慢查询日志、怎么分析定位、怎么用 EXPLAIN 看执行计划、以及最常见的索引优化手段，全部基于真实案例。

一、开启慢查询日志

MySQL 默认不开启慢查询日志，需要手动打开。可以在配置文件的 mysqld 段里设置，也可以运行时用 SET GLOBAL 临时打开：

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

long_query_time 指超过多少秒算慢查询，个人站建议设为 1 秒，先别设太严，否则日志会非常吵。log_queries_not_using_indexes 会额外记录那些没走索引的查询，这个开关非常有用，能帮你发现潜在隐患。注意 MySQL 8.0 里还有一个 log_slow_admin_statements 选项，默认不记录 ALTER TABLE 之类的管理语句，排查大表结构变更耗时的时候需要把它打开。改完配置重启 MySQL 或者用 SET GLOBAL 动态开启都可以，动态开启的好处是不用重启、不影响在线业务。

开启之后可以用 SHOW VARIABLES LIKE 'slow_query%' 和 SHOW GLOBAL STATUS LIKE 'Slow_queries' 确认状态：前者看配置是否生效，后者看累计记录的慢查询条数。另外要留意日志目录的磁盘空间，慢查询日志在问题严重的时候增长很快，建议给 /var/log 单独分区，或者把日志文件放到容量充足的磁盘上，避免日志把系统盘写满。

二、用 mysqldumpslow 快速定位

慢查询日志开了一天之后，直接看日志文件往往很长，而且同一条 SQL 会反复出现。MySQL 自带的 mysqldumpslow 工具可以把相似的 SQL 归并统计，比如按平均耗时排序看前 10 条：

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

输出里会显示每条 SQL 出现了多少次、平均耗时、总耗时、以及归并后的 SQL 模板，参数会用 N 代替。对于个人站，每天重点看这条命令的输出就够了：总耗时最高的几条，往往就是拖垮数据库的元凶。如果装了 Percona Toolkit，还可以用 pt-query-digest 生成更详细的报告，不过对于小站来说 mysqldumpslow 已经完全够用，没有必要为了分析慢查询去折腾一套复杂的监控系统。

mysqldumpslow 的几个常用参数值得记住：-s at 按平均耗时排序，-s c 按出现次数排序，-s t 按总耗时排序；-t N 只显示前 N 条；-g 后面可以跟正则表达式过滤，比如只想看某个表的查询，-g 'posts' 就能把包含 posts 的 SQL 筛出来。组合使用可以快速缩小排查范围。还有一个细节：mysqldumpslow 读取的是汇总后的抽象 SQL，参数值都变成了 N 和 S，所以不用担心隐私问题，也方便直接对比同一条 SQL 在不同时期的表现。

三、EXPLAIN 解读执行计划

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

EXPLAIN SELECT * FROM posts WHERE author_id = 123 ORDER BY created_at DESC;

EXPLAIN 的输出里，最需要关注四列。type 表示访问类型，从好到差依次是 system、const、eq_ref、ref、range、index、ALL，看到 ALL 就说明是全表扫描，基本可以断定这条 SQL 有问题。key 表示实际用到的索引，如果是 NULL 说明没走索引。rows 是预估扫描的行数，这个数字越大越危险。Extra 里如果出现 Using filesort 或 Using temporary，说明排序或去重用了临时文件，通常也是索引设计不合理的信号。判断标准很简单：type 不是 ALL、key 不为 NULL、rows 尽量小，这条 SQL 就是健康的。

四、索引优化实战：最左前缀与覆盖索引

搞清楚执行计划之后，索引优化就是有的放矢了。个人站长最常用的 InnoDB 引擎，索引底层是 B+ 树，优化时有几条铁律。第一，联合索引遵循最左前缀原则，比如建立了 (author_id, created_at) 联合索引，那么 WHERE author_id = 1 和 WHERE author_id = 1 ORDER BY created_at 都能用上，但只有 WHERE created_at > '2024-01-01' 就用不上。所以联合索引的字段顺序，要把等值查询的字段放前面、范围查询和排序字段放后面。第二，尽量用覆盖索引，也就是让查询需要的所有列都在索引里，这样 MySQL 不需要回表。比如上面那条 SQL，如果只需要 id 和 title 两个字段，可以建 (author_id, created_at, id, title) 联合索引，Extra 里会出现 Using index，性能会好很多。第三，索引不是越多越好，每个索引都会拖慢写入，个人站一张表三到五个索引足够，多余的及时删掉。

再补充两个实践中常用的索引技巧。一个是前缀索引：如果要在很长的字符串列上建索引，比如文章摘要，可以只索引前一部分字符，ALTER TABLE posts ADD INDEX idx_summary (summary(50))，这样索引体积大幅缩小，检索速度更快，代价是区分度可能下降，选择前缀长度的时候要保证选择性足够高。另一个是主键的选择：InnoDB 的二级索引叶子节点存的是主键值，所以主键越短越好，最好用自增整型，不要用 UUID 之类的长字符串做主键，否则二级索引会变得又大又慢。

五、常见的慢查询写法

很多慢查询不是缺索引，而是 SQL 写得不规范导致索引失效。最典型的有三类。第一类是在索引列上做函数运算，比如 WHERE DATE(created_at) = '2024-01-01'，这样即使 created_at 有索引也用不上，应该改成范围查询 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。第二类是隐式类型转换，比如索引列是 varchar 类型，却用数字去比较 WHERE phone = 13800138000，MySQL 会先对列做类型转换再比较，索引失效。第三类是前导通配符，WHERE title LIKE '%关键词%' 无法走索引，如果只是前缀匹配 WHERE title LIKE '关键词%' 就能走。写 SQL 的时候养成习惯：索引列保持干净，不做函数、不隐式转换、不用前导通配符，能避免一大半慢查询。

还有一个非常典型的性能杀手是深分页。SELECT * FROM posts ORDER BY id LIMIT 100000, 10 这条 SQL，MySQL 需要先扫描前 10 万行再丢弃，越往后翻越慢。解决办法是延迟关联：先用覆盖索引查出主键，再回表取数据，SELECT p.* FROM posts p JOIN (SELECT id FROM posts ORDER BY id LIMIT 100000, 10) t ON p.id = t.id，配合覆盖索引，翻到很深的页也不会明显变慢。或者干脆改成游标分页，用 WHERE id > 上次的最大 id LIMIT 10 的方式，性能最优，适合无限滚动类的列表。

六、InnoDB 参数调优

SQL 层面优化完之后，再调整几个关键参数，个人站的数据库性能还能再上一个台阶。最重要的是 innodb_buffer_pool_size，它是 InnoDB 的缓冲池，建议设置为物理内存的百分之五十到七十，比如 4G 内存的服务器设 2G 到 3G。设置太小会导致频繁读磁盘，这是个人站最常见的数据库瓶颈。其次是 innodb_flush_log_at_trx_commit，默认值是 1，也就是每次事务提交都刷盘，最安全但最慢；如果网站对数据安全要求没那么极端，可以设为 2，性能提升明显。还有 max_connections，个人站不用设太大，默认 151 通常够用，如果经常报 too many connections，优先排查有没有连接泄漏，而不是盲目调大。最后，redo 日志文件大小 innodb_log_file_size 建议至少 256M，太小会导致频繁的日志切换和刷盘。

这里多说一句：不少老教程会推荐开启 query cache 来加速重复查询，但 query cache 在 MySQL 8.0 里已经被彻底移除了，5.7 里也默认关闭，因为它带来的锁竞争问题在并发场景下弊大于利。如果你的服务器还是 5.7 且开了 query cache，建议直接关掉，把内存留给 buffer pool。对于重复查询多的站点，正确做法是在应用层或者 Nginx 层做缓存，而不是依赖数据库自带的查询缓存。

七、一个真实的排查案例

前阵子我的一个分类页越来越慢，从打开页面到出内容要两秒多。先开慢查询日志，第二天用 mysqldumpslow 一看，排名第一的是一条按分类查文章的 SQL，平均耗时 1.8 秒。EXPLAIN 一看，type 是 ALL，rows 是 18 万，典型的全表扫描。原因是我在文章表上只建了主键索引，分类字段根本没有索引。解决也很简单，加一条索引：

ALTER TABLE posts ADD INDEX idx_category_id (category_id);

加完索引的瞬间，这条 SQL 的扫描行数从 18 万降到几百，页面打开时间从两秒多降到几十毫秒。整个过程从发现问题到解决，不到十分钟，而数据库 CPU 使用率也明显降了下来。这就是慢查询日志加 EXPLAIN 的价值：问题定位精确，优化一步到位。

数据库优化是一个持续的过程，随着数据增长，曾经的优秀索引也会慢慢失效。建议把慢查询日志常开、每周花十分钟看一眼 mysqldumpslow 的输出，把隐患消灭在萌芽阶段，而不是等网站卡死了再手忙脚乱地抢救。