MySQL性能调优教程：从慢查询定位到参数调整的完整实操

MySQL 跑得慢，通常不是某一个参数的问题，而是慢查询、索引缺失和资源配置不合理叠加的结果。
这篇教程按“先找到慢在哪里，再针对性调整，最后用数据验证”的顺序展开，每一步都给出可以在服务器上直接执行的命令。

调优前需要确认的环境信息

动手之前先确认三件事，避免盲目改参数。

• 确认 MySQL 版本和存储引擎。执行 mysql -e "SELECT VERSION();" 查看版本，执行 SHOW ENGINES; 确认 InnoDB 是否为默认引擎。
• 确认服务器内存大小。执行 free -h 查看可用内存，InnoDB 缓冲池通常建议设置为物理内存的 50% 到 70%，但不要超过实际可用内存。
• 确认是否有备份。修改配置文件前先备份：cp /etc/my.cnf /etc/my.cnf.bak，Windows 下对应 my.ini。

不同发行版和安装方式下，
配置文件路径可能是 /etc/my.cnf、/etc/mysql/my.cnf 或宝塔面板的 /www/server/mysql/etc/my.cnf，
可以用 mysql --help | grep my.cnf 确认实际加载顺序。

用慢查询日志找到真正的瓶颈

不要凭感觉猜哪条 SQL 慢，先让 MySQL 自己记录下来。

在 MySQL 命令行中执行以下语句（临时生效，重启后失效）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

如果希望永久生效，在配置文件 [mysqld] 段落下添加：

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

然后重启 MySQL：systemctl restart mysqld。

运行一段时间后，用 mysqldumpslow 汇总分析：

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

-s t 表示按总耗时排序，-t 10 表示取前 10 条。
执行后你会看到最耗时的 SQL 模板，这些就是优先处理的对象。

用 EXPLAIN 判断索引是否生效

找到慢 SQL 后，在它前面加 EXPLAIN 查看执行计划：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

重点看这几列：

• type：理想值是 ref、range 或 const，如果出现 ALL 说明走了全表扫描。
• key：实际使用的索引名，为 NULL 表示没有用到索引。
• rows：预估扫描行数，数值越大越需要优化。
• Extra：出现 Using filesort 或 Using temporary 通常意味着排序或分组开销较大。

如果 type 是 ALL 且 rows 很大，就该考虑加索引了。
例如上面这条查询，可以添加联合索引：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

联合索引的字段顺序有讲究：区分度高、查询条件中最常用的字段放在前面。
加完索引后重新执行 EXPLAIN，确认 type 变为 ref 且 rows 明显下降。

InnoDB 核心参数怎么调

索引解决的是查询效率，参数解决的是资源利用效率。
以下参数在配置文件 [mysqld] 段落下调整，改完需要重启。

• innodb_buffer_pool_size：InnoDB 最重要的参数，缓存数据和索引。内存 8GB 的服务器可以设为 4G 到 5G。设置过小会导致频繁读磁盘，过大可能引发系统 OOM。
• innodb_log_file_size：重做日志大小，适当增大可以减少 checkpoint 频率。但修改前需要先正常关闭 MySQL 并删除旧日志文件，操作有风险，建议在有备份的前提下进行。
• max_connections：最大连接数。用 SHOW STATUS LIKE 'Max_used_connections'; 查看历史峰值，设置为峰值的 1.5 倍左右即可，盲目调大反而消耗内存。
• innodb_flush_log_at_trx_commit：控制事务日志刷盘策略。默认值 1 最安全，如果对数据安全性要求不高且追求写入性能，可以改为 2，但要知道这意味着极端情况下可能丢失最近 1 秒的事务。

修改示例：

[mysqld]
innodb_buffer_pool_size = 4G
max_connections = 300
innodb_flush_log_at_trx_commit = 2

调整后如何验证效果

改完参数和索引后，不要凭感觉判断，用数据对比。

• 重新跑一轮慢查询日志，观察 long_query_time 阈值以上的 SQL 数量是否减少。
• 用 SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests'; 和 SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; 计算缓存命中率，命中率越高说明内存利用越好。
• 对关键 SQL 执行 EXPLAIN，确认 type 和 rows 有改善。
• 如果条件允许，用 sysbench 或业务侧压测工具做前后对比，记录 QPS 和平均响应时间。

几个容易踩的坑

• 不要一次性改多个参数。每次只改一个，重启后观察效果，否则出问题很难定位是哪个参数导致的。
• 索引不是越多越好。每个索引都会增加写入时的维护成本，对写入频繁的表要控制索引数量，定期用 SHOW INDEX FROM 表名; 检查冗余索引。
• innodb_log_file_size 修改不当可能导致启动失败。如果不确定操作步骤，先跳过这项，优先调整缓冲池和连接数。
• 调优前务必确认有可用的备份，涉及重启和日志文件变更的操作，一旦失败需要能回滚。
• 如果服务器本身内存不足或磁盘 IO 已经饱和，单靠 MySQL 参数调整效果有限，需要先解决硬件资源问题。

常见疑问

问：慢查询日志一直开着会影响性能吗？

答：会有少量写入开销，但通常可以接受。
如果写入量极大，可以只在排查阶段开启，平时关闭。

问：加了索引为什么查询还是慢？

答：可能因为查询条件没有命中索引字段，或者索引区分度太低。
用 EXPLAIN 确认 key 列是否显示了你创建的索引名。

问：innodb_buffer_pool_size 设置多大合适？

答：一般建议为物理内存的 50% 到 70%，前提是服务器上不只运行 MySQL。
如果还有 PHP、Nginx 等服务，需要预留足够内存给它们。

问：调优后需要重启 MySQL 吗？

答：通过 SET GLOBAL 修改的参数立即生效但重启后失效；
通过配置文件修改的参数需要重启才生效。
生产环境重启前建议安排在低峰期并确认备份可用。

MySQL 性能调优没有一劳永逸的配置，关键是养成先测量再调整的习惯。
从慢查询日志入手，配合 EXPLAIN 分析索引，再根据服务器实际内存调整 InnoDB 参数，最后用命中率和响应时间验证效果，这套流程适用于大多数中小型业务场景。