MySQL性能调优深度解析：从配置到SQL优化
前言
MySQL是互联网公司使用最广泛的开源数据库，其性能直接影响业务系统的响应速度。本文将从实例配置、索引设计、SQL优化三个层面，系统地介绍MySQL性能调优的完整方法论。
一、实例级配置优化
1.1 InnoDB核心参数
# my.cnf
[mysqld]
# 缓冲池大小 - 最重要的参数
# 建议设置为物理内存的 50%-70%
innodb_buffer_pool_size = 8G
# 缓冲池实例数 - 减少并发争用
# 当 buffer_pool_size > 1G 时建议设置
innodb_buffer_pool_instances = 8
# 日志文件大小 - 影响写入性能
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
# 刷新策略
innodb_flush_log_at_trx_commit = 2 # 性能优先
innodb_flush_method = O_DIRECT # 绕过OS缓存
# IO容量
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# 线程并发
innodb_thread_concurrency = 0 # 让MySQL自动管理
1.2 连接与线程
max_connections = 1000
thread_cache_size = 100
table_open_cache = 4000
table_definition_cache = 2000
# 临时表
tmp_table_size = 64M
max_heap_table_size = 64M
1.3 查询缓存（MySQL 8.0已移除）
MySQL 8.0 移除了Query Cache，替代方案是使用Redis等外部缓存。
二、索引设计与优化
2.1 索引类型选择
| 索引类型 | 适用场景 | 注意事项 |
|---|---|---|
| B-Tree（默认） | 等值查询、范围查询、排序 | 最通用 |
| Hash | 等值查询（仅MEMORY引擎） | 不支持范围查询 |
| FULLTEXT | 全文搜索 | 中文需配置分词器 |
| Spatial | 地理位置数据 | GIS应用 |
2.2 索引设计原则
-- ❌ 低选择性列不适合单独建索引
-- gender 只有 M/F 两个值
CREATE INDEX idx_gender ON users(gender); -- 无效索引
-- ✅ 高选择性的列
CREATE INDEX idx_email ON users(email); -- 几乎唯一
-- ✅ 复合索引：遵循最左前缀原则
-- where a=? and b=? and c=?
CREATE INDEX idx_a_b_c ON orders(a, b, c);
-- 等值条件放前面，范围条件放后面
-- where status=? and create_time > ?
CREATE INDEX idx_status_time ON orders(status, create_time);
2.3 覆盖索引
-- ❌ 需要回表
SELECT id, name, email FROM users WHERE age > 25;
-- ✅ 覆盖索引避免回表
CREATE INDEX idx_age_cover ON users(age, id, name, email);
-- 直接从索引获取所有数据，无需访问数据行
2.4 索引监控
-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;
-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;
-- 索引使用统计
SELECT
object_schema, object_name, index_name,
count_read, count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
ORDER BY count_read DESC;
三、SQL优化实战
3.1 执行计划解读
EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active' AND o.create_time > '2025-01-01';
关键字段解读：
| 字段 | 含义 | 优化目标 |
|---|---|---|
| type | 访问类型 | 至少达到 range，最好 const/eq_ref |
| rows | 扫描行数 | 越少越好 |
| Extra | 额外信息 | 避免 Using filesort, Using temporary |
| key | 使用的索引 | 确保用到合适的索引 |
| filtered | 过滤比例 | 越接近100越好 |
3.2 常见优化模式
-- ❌ 隐式类型转换导致索引失效
SELECT * FROM orders WHERE phone = 13800138000;
-- phone是varchar，应改为
SELECT * FROM orders WHERE phone = '13800138000';
-- ❌ 对索引列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2025-06-28';
-- 改为范围查询
SELECT * FROM orders WHERE create_time >= '2025-06-28'
AND create_time < '2025-06-29';
-- ❌ OR条件可能不走索引
SELECT * FROM orders WHERE status = 1 OR amount > 100;
-- 改为UNION
SELECT * FROM orders WHERE status = 1
UNION
SELECT * FROM orders WHERE amount > 100;
3.3 LIMIT 深度分页优化
-- ❌ 大偏移量分页
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- ✅ 基于主键的延迟关联
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) tmp ON o.id = tmp.id;
-- ✅ 基于上次的结果（适用于分页浏览场景）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
3.4 COUNT 优化
-- ❌ COUNT(*) 全表扫描
SELECT COUNT(*) FROM orders;
-- ✅ 使用统计信息（允许误差）
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';
-- ✅ 使用汇总表
-- 创建汇总表，定时更新
INSERT INTO order_stats (stat_date, total_count)
SELECT CURDATE(), COUNT(*) FROM orders
ON DUPLICATE KEY UPDATE total_count = VALUES(total_count);
四、慢查询分析
4.1 开启慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 超过1秒记录
log_queries_not_using_indexes = 1 # 记录未使用索引的查询
log_slow_admin_statements = 1
min_examined_row_limit = 1000 # 至少扫描1000行
4.2 pt-query-digest 分析
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# 分析tcpdump抓包
tcpdump -s 65535 -x -nn -q -tttt -i eth0 port 3306 -c 5000 > mysql.tcp
pt-query-digest --type tcpdump mysql.tcp
# 实时分析
pt-query-digest --processlist h=localhost,u=root,p=secret --interval 1
4.3 Performance Schema
-- 找出最耗时的查询
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
AVG_TIMER_WAIT/1000000000000 AS avg_time_sec,
SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;
五、实战案例
案例：电商订单查询优化
问题：订单列表页加载超过5秒
排查：
EXPLAIN SELECT * FROM orders
WHERE user_id = 12345 AND status IN ('paid','shipped')
ORDER BY create_time DESC LIMIT 20;
-- type: ALL, rows: 5000000 ❌
优化：
-- 创建复合索引
CREATE INDEX idx_user_status_time
ON orders(user_id, status, create_time);
-- 优化后
-- type: range, rows: 47 ✅
效果：查询时间从 5.2s → 12ms
总结
MySQL优化是一个系统工程：
- 配置先行：先确保实例参数合理
- 索引为本：好索引解决80%的性能问题
- SQL优化：改写低效查询
- 持续监控：建立慢查询追踪机制
- 架构兜底：读写分离、分库分表是终极方案