MySQL 调优：缓冲区设置、慢查询优化与索引提速

MySQL 跑得慢，别急着换服务器，先从这三个方向动手。主机选这篇 Linux 教程会直接告诉你：改缓冲区参数、抓慢查询、修索引结构，90% 的性能问题都能压下来。

一、缓冲区设置：别让 MySQL 饿死在内存外

缓冲区是 MySQL 的“临时仓库”，设置小了，磁盘 I/O 频繁，响应慢得像老牛拉车。

1. InnoDB 缓冲池大小

InnoDB 缓冲池（`innodb_buffer_pool_size`）是 MySQL 内存占用的主力。建议设为物理内存的 60%-80%，但别超过 80%，否则系统可能因 OOM 被 Kill。

查看当前缓冲池大小（单位字节）

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

临时调整（重启失效）

SET GLOBAL innodb_buffer_pool_size = 2147483648; # 2G

永久生效，编辑 /etc/my.cnf 或 /etc/mysql/my.cnf

[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 4 # 建议设为 1-4，减少锁竞争

老鸟叮嘱：调整后必须重启 MySQL，然后观察 `SHOW ENGINE INNODB STATUS` 中的 `Buffer pool hit rate`，如果低于 95%，说明内存不够，继续加。

2. 查询缓存别乱开

MySQL 8.0 已经移除了查询缓存（Query Cache）。如果你还在用 5.7 及以下版本，默认也建议关闭。因为写操作频繁时，缓存刷新反而拖慢性能。

检查查询缓存状态

SHOW VARIABLES LIKE 'query_cache_type';

关闭（5.7 及以下）

[mysqld]
query_cache_type = 0
query_cache_size = 0

二、慢查询优化：从日志里抓出“罪魁祸首”

慢查询日志是排查性能瓶颈的第一手资料。别问为什么，先开起来再说。

1. 开启慢查询日志

查看当前慢查询设置

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

永久开启（my.cnf）

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # 超过2秒就算慢查询
log_queries_not_using_indexes = 1 # 记录未用索引的查询

2. 分析慢查询日志

用 `mysqldumpslow` 工具快速统计：

按平均查询时间排序，显示前5条

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

输出类似：

Count: 10 Time=4.5s (45s) Lock=0.1s (1s) Rows=10000 (100000)
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

这条查询没用到索引，而且扫描了 10000 行。接下来就要看索引怎么修。

三、索引提速：用 EXPLAIN 找到“全表扫描”的元凶

索引是 MySQL 的“目录”，没索引就等于翻书一页页找。

1. 用 EXPLAIN 诊断查询计划

sql
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';

重点关注：

• `type`：如果是 `ALL`，说明全表扫描，必须加索引。

• `rows`：扫描行数，越小越好。

• `Extra`：出现 `Using filesort` 或 `Using temporary`，说明排序或分组没用到索引。

2. 添加复合索引

根据慢查询日志中的 SQL，建一个复合索引：

sql
— 把 where 条件中的字段放前面，排序字段放后面
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

老鸟叮嘱：复合索引遵循“最左前缀原则”。比如 `(status, created_at)` 能加速 `WHERE status = 'pending'` 和 `WHERE status = 'pending' ORDER BY created_at`，但不能加速 `WHERE created_at > '2024-01-01'`。

3. 索引别建太多

每张表的索引数量建议控制在 5 个以内。索引越多，写入越慢。定期用 `SHOW INDEX FROM table_name` 检查重复或冗余索引。

四、常见排障

1. 改了 my.cnf 没生效

确认 MySQL 读取的配置文件路径

mysql –help | grep 'Default options' -A 1

2. 慢查询日志没内容

检查文件权限

ls -l /var/log/mysql/slow.log

临时触发一条慢查询测试

SELECT SLEEP(3);

3. 索引明明有，EXPLAIN 还是全表扫描

原因：表数据量太小（几百行），MySQL 认为全表扫描更快。或者查询中使用了函数、隐式类型转换，导致索引失效。

sql
— 错误示例：status 是字符串，但查询用了数字
SELECT * FROM orders WHERE status = 1;
— 正确写法
SELECT * FROM orders WHERE status = '1';

五、老鸟叮嘱

千万不要在生产库上直接改 `innodb_buffer_pool_size` 为过大的值，比如 32G 内存的机器，你设成 30G，系统直接 OOM。先看 `free -h` 确认空闲内存，再逐步上调。

FAQ

Q：MySQL 缓冲区设置多大合适？

A：InnoDB 缓冲池设为物理内存的 60%-80%，但留出 20% 给操作系统和其他进程。用 `SHOW ENGINE INNODB STATUS` 观察 `Buffer pool hit rate`，低于 95% 就加内存。

Q：慢查询日志文件太大怎么办？

A：设置 `long_query_time` 为 2-5 秒，避免记录太多小查询。也可以定期用 `mysqldumpslow` 汇总后清空日志，或者用 `pt-query-digest` 工具分析。

Q：索引越多查询越快吗？

A：不是。索引会拖慢 INSERT、UPDATE、DELETE 操作。一张表建议不超过 5 个索引，定期用 `SHOW INDEX` 检查冗余索引。

Q：EXPLAIN 中 type 字段出现 ALL 是什么意思？

A：ALL 表示全表扫描，是性能最差的类型。必须加索引。理想情况下是 const、eq_ref、ref、range。

Q：修改 my.cnf 后 MySQL 启动报错怎么办？

A：先注释掉刚加的配置，重启确认能启动。然后逐步排查：检查参数名是否写错（如 `innodb_buffer_pool_size` 拼成 `innodb_buffer_pool`），或者值超出限制。

Q：MySQL 8.0 和 5.7 在调优上有什么区别？

A：8.0 移除了查询缓存，但优化了 InnoDB 性能。8.0 的 `innodb_buffer_pool_size` 可以动态调整（需设置 `innodb_buffer_pool_chunk_size`），但建议还是重启生效。