本文是《中间件性能调优完全指南》的补充章节，系统梳理 MySQL 数据库的性能调优方法，涵盖 SQL 优化、索引设计、参数调优、架构演进等维度。
一、MySQL 调优概述
1.1 调优金字塔
graph TB
A[SQL与索引优化<br>性价比最高] --> B[参数配置调优<br>数据库层面]
B --> C[架构优化<br>读写分离/分库分表]
C --> D[硬件升级<br>SSD/内存扩容]
核心原则：从上到下，性价比递减。优先优化 SQL 和索引，最后才考虑加钱升级硬件。
1.2 调优目标
指标
说明
健康标准
查询响应时间
SQL 执行耗时
OLTP < 100ms，OLAP < 5s
QPS/TPS
每秒查询/事务数
根据业务评估
慢查询比例
慢查询占总查询比例
< 1%
连接数使用率
当前连接数/最大连接数
< 70%
InnoDB 缓冲池命中率
数据从内存读取的比例
> 99%
二、SQL 语句优化
2.1 执行计划分析（EXPLAIN）
-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- 关键指标解读
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
type（越往下越差）
含义
优化方向
system/const
最多一条匹配
最优
eq_ref
唯一索引匹配
很好
ref
非唯一索引匹配
可接受
range
范围扫描
需关注
index
全索引扫描
需优化
ALL
全表扫描
🔴 必须优化
2.2 常见 SQL 反模式与优化
反模式
错误写法
正确写法
提升
**SELECT ***
SELECT * FROM users
SELECT id, name FROM users
减少 I/O 50%+
无分页
一次查全部
LIMIT 100 OFFSET 0
避免 OOM
函数在条件列
WHERE DATE(create_time) = '2024-01-01'
WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'
索引生效
隐式类型转换
WHERE phone = 13800138000（phone 是 varchar）
WHERE phone = '13800138000'
索引生效
前导通配符
WHERE name LIKE '%张三%'
考虑使用全文索引或 ES
避免全表扫描
OR 乱用
WHERE id = 1 OR id = 2 OR id = 3
WHERE id IN (1,2,3)
优化器更好优化
NOT IN 子查询
WHERE id NOT IN (SELECT user_id FROM orders)
LEFT JOIN ... WHERE orders.user_id IS NULL
性能提升 10 倍+
2.3 批量操作优化
-- ❌ 不推荐：逐条插入（1000 次网络往返）
INSERT INTO users (name) VALUES ('a');
INSERT INTO users (name) VALUES ('b');
-- ...
-- ✅ 推荐：批量插入（1 次网络往返）
INSERT INTO users (name) VALUES ('a'), ('b'), ('c'), ...;
-- ❌ 不推荐：逐条更新
UPDATE users SET status = 1 WHERE id = 1;
UPDATE users SET status = 1 WHERE id = 2;
-- ✅ 推荐：IN 条件批量更新
UPDATE users SET status = 1 WHERE id IN (1,2,3,4,5);
三、索引设计与优化
3.1 索引类型与选择
索引类型
适用场景
注意事项
B-Tree（默认）
等值查询、范围查询、排序
最通用
Hash（Memory引擎）
精确等值查询
不支持范围查询
全文索引
全文搜索（LIKE ‘%xxx%’）
中文需要分词插件
联合索引
多条件查询
遵循最左前缀原则
覆盖索引
查询字段都在索引中
避免回表，性能翻倍
唯一索引
需要唯一性约束
兼具查询加速
降序索引（8.0）
ORDER BY 降序
避免 filesort
3.2 索引设计黄金法则
法则
说明
示例
最左前缀
联合索引按最左列匹配
INDEX(a,b,c) 可匹配 (a)、(a,b)、(a,b,c)
区分度优先
高区分度列放前面
手机号 > 性别
索引覆盖
查询字段尽量都在索引中
SELECT id,name FROM users WHERE phone='xxx'
避免重复索引
相同前缀的索引冗余
INDEX(a) 和 INDEX(a,b) 保留后者即可
索引下推
MySQL 5.6+ 自动优化
减少回表次数
3.3 索引失效场景
-- 🔴 索引失效：使用函数
WHERE LEFT(phone, 3) = '138' -- 失效
-- ✅ 改为
WHERE phone LIKE '138%' -- 有效
-- 🔴 索引失效：隐式类型转换
WHERE phone = 13800138000 -- phone 是 varchar，失效
-- ✅ 改为
WHERE phone = '13800138000' -- 有效
-- 🔴 索引失效：OR 连接非索引字段
WHERE id = 1 OR name = '张三' -- name 无索引，失效
-- ✅ 改为两次查询或用 UNION
-- 🔴 索引失效：NOT IN / <>
WHERE status <> 0 -- 失效（数据量小时可能走索引）
3.4 索引监控与维护
-- 查看表索引
SHOW INDEX FROM users;
-- 查看索引使用情况（MySQL 5.6+）
SELECT * FROM sys.schema_unused_indexes;
-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;
-- 重建索引（回收空间）
ALTER TABLE users ENGINE=InnoDB;
-- 删除未使用的索引
DROP INDEX unused_idx ON users;
四、数据库参数调优
4.1 核心参数全景
参数分类
参数名
默认值
推荐值
说明
内存类
innodb_buffer_pool_size
128MB
物理内存 50-70%
InnoDB 缓冲池
innodb_log_buffer_size
16MB
64-256MB
日志缓冲区
key_buffer_size
8MB
64MB（MyISAM 用）
MyISAM 索引缓冲
query_cache_size
1MB
0（8.0 已移除）
查询缓存（不推荐）
日志类
innodb_log_file_size
48MB
1-4GB
Redo 日志文件大小
innodb_flush_log_at_trx_commit
1
1/2
刷盘频率
sync_binlog
1
1/100
Binlog 同步频率
连接类
max_connections
151
500-2000
最大连接数
innodb_thread_concurrency
0
64-128
并发线程数
IO类
innodb_io_capacity
200
2000-20000
IO 吞吐能力
innodb_flush_neighbors
1
0（SSD）
刷盘邻居页
其他
tmp_table_size
16MB
64-256MB
内存临时表阈值
4.2 InnoDB 缓冲池调优（最重要）
-- 查看当前缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 设置为物理内存的 50-70%（以 16GB 内存为例）
SET GLOBAL innodb_buffer_pool_size = 10 * 1024 * 1024 * 1024; -- 10GB
-- 查看缓冲池命中率（应 > 99%）
SHOW ENGINE INNODB STATUS\G
-- 关注 Buffer pool hit rate: 1000/1000 (100%)
-- MySQL 5.7+ 可在线调整
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;
-- 缓冲池预热（重启后）
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录无索引查询
SET GLOBAL log_slow_admin_statements = ON; -- 记录慢 DDL
-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';
graph LR
A[监控发现慢查询] --> B[EXPLAIN 分析执行计划]
B --> C{是否用到索引?}
C -->|否| D[添加/优化索引]
C -->|是| E{SQL 是否可优化?}
E -->|是| F[重写 SQL]
E -->|否| G[考虑架构优化]
D --> H[压测验证]
F --> H
G --> H
H --> I[上线]
六、架构层面优化
6.1 读写分离
graph LR
A[业务应用] --> B[读写分离中间件]
B -->|写| C[主库]
B -->|读| D[从库1]
B -->|读| E[从库2]
适用场景：读多写少（读写比 > 5:1）
注意事项：
主从延迟问题（业务容忍度决定是否读主库）
从库故障自动摘除
6.2 分库分表
策略
说明
示例
水平分表
同库，表拆分
user_0, user_1, user_2
水平分库
不同库
db0.user, db1.user
垂直分表
列拆分
user_base, user_extend
垂直分库
不同业务独立
订单库、用户库
分片键选择原则：
高频查询字段（如 user_id）
分布均匀（避免热点）
不可变（一旦确定不再更改）
6.3 缓存策略
层级
缓存方案
适用场景
应用本地缓存
Caffeine、Guava
字典类、配置类
分布式缓存
Redis、Memcached
用户会话、热点数据
数据库查询缓存
MySQL Query Cache（8.0 移除）
不推荐使用
6.4 冷热分离
-- 热数据（近期订单）
CREATE TABLE orders_hot LIKE orders;
-- 冷数据（历史订单）
CREATE TABLE orders_cold LIKE orders;
-- 定期归档
INSERT INTO orders_cold SELECT * FROM orders WHERE create_time < '2024-01-01';
DELETE FROM orders WHERE create_time < '2024-01-01';
七、MySQL 8.0 新特性与调优
7.1 新特性带来的调优机会
特性
调优价值
不可见索引
安全删除索引：先设为不可见，观察无影响再删除
降序索引
优化 ORDER BY DESC 查询，避免 filesort
隐藏索引
测试新索引效果，不影响生产
资源组
限制查询使用 CPU 核数
即时 DDL
添加列不复制表，操作秒级完成
EXPLAIN ANALYZE
获取真实执行计划耗时
7.2 实战示例
-- 1. 不可见索引（安全删除）
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;
-- 观察一段时间，确认无影响后删除
ALTER TABLE users DROP INDEX idx_name;
-- 2. 降序索引（8.0 原生支持）
CREATE INDEX idx_time_desc ON orders (create_time DESC);
-- 3. EXPLAIN ANALYZE（获取真实执行时间）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123\G
-- 4. 资源组（限制分析查询）
CREATE RESOURCE GROUP adhoc TYPE = USER VCPU = 0-1 THREAD_PRIORITY = 10;
SET RESOURCE GROUP adhoc FOR SELECT COUNT(*) FROM large_table;
