MySQL 性能优化实战——从慢查询到配置调优的完整指南

在服务器运维中，MySQL 数据库的性能直接影响整个应用的响应速度。一个没有优化过的 MySQL 实例，在数据量增长到百万级后，很容易出现查询变慢、CPU 占用过高等问题。本文将从慢查询分析开始，一步步带你完成 MySQL 的性能优化。

一、开启慢查询日志，定位问题源头

优化的第一步是找到哪些查询拖了后腿。MySQL 的慢查询日志可以帮我们记录执行时间超过阈值的 SQL 语句。

1.1 临时开启慢查询（重启后失效）

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值（单位：秒，这里设置为1秒）
SET GLOBAL long_query_time = 1;

-- 设置日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

1.2 永久配置（修改 my.cnf 或 my.ini）

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

修改后重启 MySQL 服务：

systemctl restart mysql

二、使用 mysqldumpslow 分析慢查询日志

慢查询日志记录了所有超时的 SQL，但直接查看日志文件会很混乱。MySQL 自带的 mysqldumpslow 工具可以帮我们归类分析。

2.1 常用命令

# 查看访问次数最多的10条慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 查看返回记录集最多的10条慢查询
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 查看查询时间最长的10条慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按照时间排序，含有左连接的前10条SQL
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

2.2 输出结果说明

输出格式通常是：

Count: 2  Time=0.89s (1s)  Lock=0.00s (0s)  Rows=500.0 (1000), root[root]@localhost
  SELECT * FROM users WHERE create_time > '2024-01-01'

Count：该 SQL 执行了多少次
Time：平均执行时间，括号内是总时间
Lock：锁表时间
Rows：返回的行数

三、EXPLAIN 分析查询执行计划

找到慢查询后，用 EXPLAIN 命令看看 MySQL 是怎么执行这条 SQL 的。

3.1 基本用法

EXPLAIN SELECT * FROM users WHERE create_time > '2024-01-01' ORDER BY id DESC;

3.2 重点关注字段

字段	说明	优化建议
type	访问类型	从好到坏：system > const > eq_ref > ref > range > index > ALL。出现 ALL 说明全表扫描，必须优化
key	实际使用的索引	NULL 表示没用到索引，需要建索引
rows	预计扫描的行数	越大性能越差，索引优化的目标就是减少这个值
Extra	额外信息	出现 Using filesort 或 Using temporary 需要重点优化

3.3 常见问题及解决

Using filesort：MySQL 无法利用索引完成排序，需要额外排序操作
- 解决方案：让排序字段和 where 条件字段使用同一个联合索引
Using temporary：使用了临时表保存中间结果
- 解决方案：优化 GROUP BY 和 ORDER BY 字段，尽量让它们走索引

四、索引优化原则

索引是性能优化的利器，但不是越多越好。索引会占用磁盘空间，还会降低 INSERT/UPDATE/DELETE 的速度。

4.1 适合建索引的字段

WHERE 子句中的字段
JOIN 连接条件中的字段
ORDER BY 排序中的字段
GROUP BY 分组中的字段

4.2 索引优化原则

最左前缀原则：联合索引要从最左的字段开始匹配
- 比如有索引 (a,b,c)，查询条件 a=? 、a=? AND b=? 、a=? AND b=? AND c=? 会走索引，但 b=? 或 b=? AND c=? 不会走
不在索引列上做操作
- ❌ 错误：WHERE DATE(create_time) = '2024-01-01'
- ✅ 正确：WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'
避免使用 SELECT *
- 只查询需要的字段，减少数据传输和磁盘 IO
使用覆盖索引
- 查询的字段都在索引中，不需要回表查询
- 例子：SELECT id, name FROM users WHERE name = '张三'，建联合索引 (name, id)

4.3 索引失效的常见情况

以 % 开头的 LIKE 查询：LIKE '%abc'（但 'abc%' 可以走索引）
字符串类型的字段不加引号：WHERE phone = 13800138000（phone 是 varchar 类型）
使用 != 或 <> 操作符
OR 连接的条件，只要有一个字段没有索引
在索引字段上使用函数或计算

五、MySQL 配置优化

除了 SQL 和索引优化，配置参数也很重要。以下是几个核心配置。

5.1 InnoDB 缓冲池大小（innodb_buffer_pool_size）

这是最重要的配置项，决定了 InnoDB 能缓存多少数据和索引。

建议值：服务器内存的 50% - 70%
示例：16G 内存的服务器，设置为 10G
```
innodb_buffer_pool_size = 10G
```

5.2 日志文件大小（innodb_log_file_size）

控制重做日志文件的大小，影响写入性能。

建议值：256M - 1G
示例：
```
innodb_log_file_size = 512M
```

5.3 连接数配置（max_connections）

MySQL 允许的最大同时连接数。

查看当前连接数：SHOW STATUS LIKE 'Threads_connected'
建议值：根据业务量设置，通常 500 - 2000
```
max_connections = 1000
```

5.4 临时表大小（tmp_table_size 和 max_heap_table_size）

控制内存临时表的大小，超过这个值会转成磁盘临时表。

建议值：64M - 256M

tmp_table_size = 128M
max_heap_table_size = 128M

5.5 排序缓存（sort_buffer_size）

每个会话执行排序时分配的缓存大小。

建议值：不要设置太大，通常 2M - 8M
```
sort_buffer_size = 4M
```

六、表结构优化

6.1 选择合适的数据类型

尽量使用 TINYINT/INT/BIGINT，不要用字符串存数字
日期时间用 DATETIME 或 TIMESTAMP，不要用 VARCHAR
金额用 DECIMAL，不要用 FLOAT/DOUBLE（有精度丢失问题）
VARCHAR 长度只分配真正需要的空间

6.2 避免使用 NULL

NULL 字段会让索引、索引统计和值比较都更复杂。建议给字段设置默认值。

6.3 大表拆分

当单表数据量超过千万级时，考虑分库分表：

垂直拆分：把大字段拆到单独的表
水平拆分：按时间、用户 ID 等维度拆分到多个表

七、定期维护

7.1 分析表

更新索引统计信息，让优化器更准确：

ANALYZE TABLE table_name;

7.2 优化表

整理碎片，回收空间：

OPTIMIZE TABLE table_name;

7.3 检查损坏的表

CHECK TABLE table_name;

总结

MySQL 性能优化是一个系统性的工作：

先开启慢查询日志，找到问题 SQL
用 EXPLAIN 分析执行计划
优化索引和 SQL 语句
调整配置参数
优化表结构
定期维护

优化没有终点，需要根据业务发展持续监控和调整。记住：过早优化是万恶之源，但出现问题不优化也是万万不能的。