MySQL是应用最广泛的关系型数据库，但其性能优化一直是开发者和运维人员的核心课题。很多项目在数据量增长后出现查询变慢、CPU飙升、连接数打满等问题，根源往往在于索引设计不合理、SQL写法不规范、配置参数未优化。本文将从索引优化、SQL调优、配置优化、架构设计四个维度，分享MySQL性能优化的实战经验。
一、索引优化原则
索引是MySQL性能优化的核心。正确的索引可以让查询速度提升几个数量级，错误的索引则会拖慢写入并浪费空间。
| 索引类型 | 适用场景 | 注意事项 |
|---|---|---|
| 主键索引 | 唯一标识，自增ID | 尽量用自增整数，避免UUID |
| 唯一索引 | 字段值唯一（邮箱、手机号） | 保证数据唯一性 |
| 普通索引 | 频繁查询的字段 | 区分度低的字段不建议建 |
| 联合索引 | 多字段组合查询 | 遵循最左前缀原则 |
| 覆盖索引 | 索引包含所有查询字段 | 避免回表，性能最佳 |
联合索引的最左前缀原则：
-- 联合索引 (a, b, c)
-- 以下查询可以使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3 -- 只用到a
-- 以下查询无法使用索引
WHERE b = 2
WHERE b = 2 AND c = 3
WHERE c = 3二、SQL查询优化
使用EXPLAIN分析查询执行计划，重点关注type、key、rows、Extra字段：
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;
-- 理想的执行计划
-- type: ref或range（避免ALL全表扫描）
-- key: 实际使用的索引
-- rows: 扫描行数（越少越好）
-- Extra: Using index（覆盖索引）最佳，避免Using filesort、Using temporary常见SQL优化技巧：
- 避免SELECT *，只查询需要的字段
- 避免在索引字段上使用函数或运算：WHERE YEAR(created_at) = 2026 → WHERE created_at >= ‘2026-01-01’
- 避免LIKE ‘%xxx’前置通配符，无法使用索引
- 大表分页优化：WHERE id > last_id LIMIT 20 替代 LIMIT 10000, 20
- 用EXISTS替代IN（大数据量场景）
- 合理使用UNION ALL替代OR
-- 慢查询：深分页
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 优化：游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- 慢查询：函数导致索引失效
SELECT * FROM users WHERE DATE(created_at) = '2026-01-01';
-- 优化：范围查询
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';三、配置参数优化
| 参数 | 建议值 | 说明 |
|---|---|---|
| innodb_buffer_pool_size | 物理内存的60-70% | 最重要的参数，缓存数据和索引 |
| innodb_log_file_size | 256M-1G | 重做日志大小，影响写入性能 |
| innodb_flush_log_at_trx_commit | 2（允许丢1秒数据） | 1最安全，2性能最好 |
| max_connections | 500-1000 | 最大连接数，避免OOM |
| slow_query_log | ON | 开启慢查询日志 |
| long_query_time | 1（秒） | 慢查询阈值 |
| query_cache_type | OFF | MySQL 8.0已移除 |
# my.cnf关键配置
[mysqld]
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
max_connections = 500
slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON四、慢查询分析与优化
开启慢查询日志后，使用pt-query-digest分析：
# 安装percona-toolkit
yum install percona-toolkit
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_analysis.txt
# 查看前10条最慢查询
pt-query-digest --limit 10 /var/log/mysql/slow.log五、分库分表与读写分离
当单表数据量超过千万级，需要考虑分库分表：
- 垂直分表：将大字段、低频字段拆分到扩展表
- 水平分表：按用户ID或时间范围拆分数据
- 读写分离：主库写入，从库读取，提升读性能
- 分库分表中间件：ShardingSphere、MyCat、Vitess
-- 读写分离配置（应用层）
# 主库：写入操作
INSERT, UPDATE, DELETE → master
# 从库：读取操作
SELECT → slave1, slave2（负载均衡）
# 注意：读写分离延迟问题
# 写入后立即读取可能读不到数据，需要强制走主库六、日常运维监控
关键监控指标：QPS、TPS、连接数、慢查询数、缓冲池命中率、锁等待时间。使用Prometheus + Grafana + mysqld_exporter搭建监控体系，设置告警阈值，及时发现性能问题。