MySQL 性能优化

适用版本：MySQL 8.x
适用场景：生产集群、K8s 部署、高并发业务、高可用架构
内容体系：查询优化 -> 索引优化 -> 表结构优化 -> InnoDB 引擎优化 -> my.cnf 推荐配置 -> 架构优化 -> 系统优化 -> 运维优化 -> 性能监控 -> 最终黄金法则

1. SQL 查询优化（最重要的优化方向）

SQL 是 MySQL 性能的决定因素，80% 的问题来自 SQL 设计错误。

1.1 使用 EXPLAIN 分析 SQL

EXPLAIN 是 SQL 优化的入口

重点字段：

字段	说明
type	访问类型，决定扫描效率（最关键）
possible_keys	哪些索引可使用
key	实际使用的索
rows	扫描行数（越小越好）
Extra	是否出现 filesort、temporary

type：高性能访问路径从好到坏

system > const > eq_ref > ref > range > index > ALL

目标：避免 ALL（全表扫描），避免 Using filesort / Using temporary。

1.2 常见慢 SQL 优化策略（增强版）

🔸1）避免 SELECT *

明确字段，减少 IO、网络、行格式解析
减少读取、减少网络传输
避免覆盖索引失效

🔸2）LIMIT 大偏移优化

SELECT * FROM log ORDER BY id LIMIT 100000, 20;

-> 会扫描 100000 行

改为：

SELECT * FROM log WHERE id > 100000 ORDER BY id LIMIT 20;

或使用 覆盖索引 + 子查询。

🔸3）函数导致索引失效（核心原则）

WHERE DATE(create_time) = '2024-01-01'

导致索引失效，因为 MySQL 需要逐行计算函数。

正确写法：

WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'

🔸4）隐式类型转换导致全表扫描

手机号字段为 VARCHAR 时：

WHERE phone = 12345678901   -- 索引失效

phone 是 varchar -> 会导致全表扫描

改为：

WHERE phone = '12345678901'

🔸5）避免在 OR 中混用未索引字段

WHERE a = ? OR b = ?

优化：

用 UNION ALL
或将 b 加索引

2. 索引优化（高性能核心）

2.1 索引适用场景

WHERE 条件高选择性字段
JOIN 的 ON 字段
ORDER BY / GROUP BY 字段
长字符串改 HASH 索引（前缀索引）

2.2 索引设计黄金法则（增强版）

🔸1）最左前缀原则

索引 (a, b, c)：

✅ 可用于 WHERE a = ?
✅ 可用于 WHERE a = ? AND b = ?
❌ 不可用于 WHERE b = ? 或 WHERE b = ? AND c = ?

🔸2）等值优先、范围滞后

范围之后的字段失效：

WHERE a = 1 AND b > 10 AND c = 20

索引 (a, b, c) 中的 c 不会被使用。

🔸3）覆盖索引最强（避免回表）

select 和 where 都在索引里 -> 极快

🔸4）避免重复索引、多余索引

如同时存在：

idx(a)
idx(a,b)

-> idx(a) 多余。

减少维护成本、减少数据写入开销。

🔸5）不适合加索引的情况

低选择性字段（如性别、布尔）
高频更新字段
大文本字段（TEXT、JSON）
表太小（索引反而慢）

3. 表结构设计优化（增强版）

3.1 字段类型选择原则

字段类型	推荐程度	原因
INT / BIGINT	⭐⭐⭐⭐⭐	性能最好，存储小
VARCHAR	⭐⭐⭐⭐	灵活、节省空间
DATETIME(6)	⭐⭐⭐⭐	精确到微秒
DECIMAL	⭐⭐⭐	精确计算
TEXT / BLOB	⭐	慎用（溢出页、慢）

类型	优先级	原因
INT(M)	快、紧凑	固定长度
BIGINT	大范围	兼容未来
VARCHAR	节省空间	动态长度
DATETIME(6)	推荐	支持微秒
TIMESTAMP	不推荐	2038 年问题

避免使用 TEXT、BLOB，能拆表就拆表。

3.2 行格式优化（important）

使用合适的行格式 ROW_FORMAT

3.3 避免大字段（TEXT/BLOB）进入主表

大字段应该：

放在独立表中
通过主键引用

原因：

InnoDB page = 16KB，一行越大，越容易产生溢出页（性能急剧下降）

4. InnoDB 引擎优化（底层增强版）

4.1 Buffer Pool（性能决定因素）

4.2 Buffer Pool 实例

innodb_buffer_pool_instances = 8

推荐: 8

适用：buffer_pool > 8GB

减少锁竞争，提高并发。

4.3 Redo Log（提高写入性能）

innodb_log_file_size = 2G~4G
innodb_log_files_in_group = 2

作用：

顺序写入，提供高 IO 性能
崩溃恢复时保证一致性

4.4 Flush 策略（崩溃安全 vs 性能）

最安全：

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

如业务可接受丢失 1 秒数据，可以调为：

innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

4.5 Doublewrite 打开

保证崩溃恢复一致性（强一致性必需）

5. 生产级 my.cnf（16G~64G 服务器推荐）

（融合优化参数与原理）

[mysqld]
#-------------------------------------
# 基础
#-------------------------------------
max_connections = 2000
max_connect_errors = 1000000

#-------------------------------------
# 缓存与表
#-------------------------------------
table_open_cache = 4096
table_definition_cache = 4096

query_cache_type = 0
query_cache_size = 0

#-------------------------------------
# InnoDB
#-------------------------------------
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 256M
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_default_row_format = dynamic

#-------------------------------------
# Binlog
#-------------------------------------
log_bin = mysql-bin
binlog_format = ROW
sync_binlog = 1
expire_logs_days = 7
binlog_row_image = FULL

#-------------------------------------
# 性能
#-------------------------------------
performance_schema = ON

6. MySQL 架构级优化（增强版）

6.1 最高一致性的方案（生产推荐）

架构	强一致性	可用性	推荐情况
InnoDB Cluster（MGR）	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	官方最推荐
Percona XtraDB Cluster（PXC）	⭐⭐⭐⭐⭐	⭐⭐⭐	写入延迟高
半同步复制	⭐⭐⭐	⭐⭐⭐⭐⭐	写增多情况下强一致性不够
异步复制（主从）	⭐	⭐⭐⭐⭐⭐	最常见方案

强一致性推荐：

🔸1）MySQL InnoDB Cluster（官方高可用）

基于 Group Replication
自动脑裂检测
自动 Failover
强一致性

🔸2）Percona XtraDB Cluster（PXC）

分布式同步复制
写入强一致性（但延迟高）

6.2 读写分离

主库写，多个从库读
减轻主库压力
配合 ProxySQL / MySQL Router 使用

避免在应用侧做负载均衡，会有一致性问题。

6.3 分库分表

触发条件：

单表 > 2000 万行
数据文件 > 100GB
索引失效频繁
查询延迟 > 50ms 且无 SQL 优化空间
巨大写入量（如日志系统）

7. 系统级优化（操作系统 + 文件系统）

7.1 文件系统

推荐优先级：XFS > EXT4 > ZFS

7.2 IO 调度

echo mq-deadline > /sys/block/nvme0n1/queue/scheduler

减少写放大，提高稳定性。

7.3 ulimit（提高 open files 限制）

ulimit -n 1000000

8. 运维级优化（DBA 最佳实践）

8.1 慢 SQL 监控策略

开启：

slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

8.2 数据库巡检（建议每日）

QPS / TPS
InnoDB 缓存命中率
全表扫描次数
线程数
复制延迟
临时表数量
Binlog 增长速度
大事务数量

9. 监控与巡检

推荐监控指标：

CPU

用户态 > 70% -> SQL 计算重
I/O wait > 20% -> 盘很慢

内存

Buffer Pool 命中率 < 99% -> 内存不足

磁盘

IOPS 不够 -> 调整 SSD/NVMe

SQL

慢查询数量
全表扫描数量
排序数量

连接数

max_used_connections > 80% -> 风险

10. 运维最佳实践

禁止强制 kill -9 进程（易损坏数据）
禁止使用 MyISAM（不支持事务）
表行数 > 2000 万要分库分表
定期表 ANALYZE / OPTIMIZE
业务变更前做 explain
限制 ORM 自动生成 SQL
限制大事务（超过 1 秒）
每日自动备份 + binlog 归档

11. MySQL 性能优化黄金法则（最终总结）

SQL 优化优先级最高，永远排第一。
索引要设计好，而不是越多越好。
Buffer Pool 要足够大（70%～80% 内存）。
表避免 TEXT/BLOB 放在主表。
ORDER BY / GROUP BY 必须命中索引。
不要大事务。
主从复制一定要监控延迟。
跨机房写入一定用 MGR 或 PXC。
每条 SQL 上线前必须 explain。
数据量大于 2000 万必须规划分表。