MySQL 性能调优与慢查询优化实战指南

前言

对于个人站长来说，MySQL 数据库往往是整个网站性能的瓶颈所在。很多站长把大量精力花在 Nginx 配置、PHP 加速和前端优化上，却忽视了数据库本身的性能调优。实际上，一次慢查询足以让整个页面的加载时间从 200ms 飙升到 5 秒以上。本文将系统性地介绍 MySQL 性能调优的核心方法和慢查询优化的完整流程。

找到慢查询：开启慢查询日志

做性能优化之前，首先要搞清楚问题出在哪里。MySQL 提供了慢查询日志功能，可以记录所有执行时间超过特定阈值的 SQL 语句。

查看当前慢查询配置

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

默认情况下，慢查询日志是关闭的，需要手动开启：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置慢查询阈值为 1 秒（生产环境建议 0.5-2 秒）
SET GLOBAL long_query_time = 1;
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

对于个人站长来说，建议将 long_query_time 设置为 1 秒。如果网站访问量不大，可以设得更低（0.5 秒），以便捕捉更多慢查询。

静态配置写入 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

使用 mysqldumpslow 分析日志

开启慢查询日志运行一段时间后，可以使用 MySQL 自带的工具来分析：

# 按平均查询时间排序，查看前 10 条最慢的查询
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 按执行次数排序，查看最频繁的慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

mysqldumpslow 会自动将参数替换为 N，方便对相似的查询进行聚合统计。这对于个人站长来说是最快速的慢查询分析手段。

使用 EXPLAIN 分析查询计划

找到慢查询之后，下一步是用 EXPLAIN 来分析查询的执行计划：

EXPLAIN SELECT * FROM posts WHERE status = 'publish' ORDER BY created DESC LIMIT 20;

输出结果中需要重点关注的字段：

• type：连接类型，效率从高到低依次是 system > const > eq_ref > ref > range > index > ALL。看到 ALL 意味着全表扫描，需要立刻优化。
• key：实际使用的索引。如果为 NULL，说明没有使用索引。
• rows：扫描的行数估算值。数值越大，查询越慢。
• Extra：出现 Using filesort 或 Using temporary 意味着查询需要额外的排序或临时表，通常是优化的重点。

索引优化：最有效的性能提升手段

合理设计索引

对于个人站长常用的 Typecho 或 WordPress 博客，以下索引策略非常有效：

-- 为经常查询的状态和时间字段创建复合索引
ALTER TABLE typecho_contents ADD INDEX idx_status_created (status, created);

-- 为分类和标签关联表创建索引
ALTER TABLE typecho_relationships ADD INDEX idx_cid_mid (cid, mid);
ALTER TABLE typecho_relationships ADD INDEX idx_mid_cid (mid, cid);

复合索引的最左前缀原则

这是一个非常容易被忽略的问题。假设你创建了一个复合索引 (a, b, c)，那么以下查询能用到索引：

WHERE a = 1          -- 使用索引
WHERE a = 1 AND b = 2 -- 使用索引
WHERE a = 1 AND b = 2 AND c = 3 -- 使用索引

但以下查询无法走索引：

WHERE b = 2          -- 跳过 a，无法使用索引
WHERE c = 3          -- 跳过 a 和 b，无法使用索引

避免索引失效的场景

• 对索引列使用函数：WHERE DATE(created) = '2026-01-01' 不会走索引，应改为 WHERE created >= '2026-01-01' AND created < '2026-01-02'
• 隐式类型转换：WHERE id = '123' 可能让 int 类型的索引失效
• LIKE 以 % 开头：WHERE title LIKE '%关键词%' 无法使用索引
• OR 条件中有非索引列：WHERE id = 1 OR title = 'test' 可能导致全表扫描

配置参数调优

InnoDB 缓冲池

InnoDB 缓冲池（innodb_buffer_pool_size）是 MySQL 最重要的内存参数。建议设置为服务器物理内存的 70%-80%（仅用于 MySQL 的服务器）：

[mysqld]
# 假设服务器有 2GB 内存
innodb_buffer_pool_size = 1.5G
# 如果服务器只有 512MB 内存，设置为 256MB
# innodb_buffer_pool_size = 256M

查询缓存

MySQL 8.0 已经移除了查询缓存功能。对于 MySQL 5.7 及更早版本，建议关闭查询缓存，因为它在高并发场景下反而会成为性能瓶颈：

query_cache_type = 0
query_cache_size = 0

连接数配置

个人站长的网站通常并发不高，默认的 151 连接数已经足够。但如果你的服务器内存较少，可以适当降低：

max_connections = 100
# 每个连接占用约 10MB 内存，100 个连接约 1GB

临时表优化

# 避免使用磁盘临时表
tmp_table_size = 64M
max_heap_table_size = 64M

实战案例：优化一个真实慢查询

假设你的博客首页加载缓慢，通过慢查询日志找到以下查询：

SELECT * FROM typecho_contents
WHERE status = 'publish' AND type = 'post'
ORDER BY created DESC LIMIT 10;

这是个人博客最常见的查询之一。EXPLAIN 显示 type 为 ALL（全表扫描），rows 为 50000+。

优化方案：创建一个复合索引

ALTER TABLE typecho_contents
ADD INDEX idx_status_type_created (status, type, created);

优化后，EXPLAIN 显示 type 变为 ref，rows 减少到 10。查询时间从 800ms 降到了 2ms。

定期维护

数据库性能不是一劳永逸的事情，需要定期维护：

-- 分析表，更新统计信息
ANALYZE TABLE typecho_contents;
ANALYZE TABLE typecho_comments;

-- 优化表，回收碎片空间（注意：会锁表）
OPTIMIZE TABLE typecho_contents;

建议在 crontab 中设置每周执行一次：

0 4 * * 0 mysqlcheck -o --all-databases -uroot -p'密码'

总结

MySQL 性能调优的关键步骤可以总结为：开启慢查询日志找到问题 -> 使用 EXPLAIN 分析执行计划 -> 合理设计索引 -> 调整配置参数 -> 定期维护。对于个人站长来说，最常见的性能问题都是索引不当引起的，花时间学习索引优化是性价比最高的投资。希望本文的实战经验能帮助你解决数据库性能瓶颈，让网站跑得更快。