如何突破单库性能瓶颈

0. 引言

单机 MySQL 的性能天花板取决于：硬件（CPU/内存/磁盘）→ 参数（InnoDB 配置）→ 架构（复制拓扑） 三层。前三层大约能榨出单机 10 万级 QPS，再往上必须引入读写分离与分库分表。本文按"从软到硬、从单机到集群"的路径，给出可落地的突破方案。

1. 硬件选型

1.1 CPU

MySQL 的瓶颈通常先出现在 IO，后出现在 CPU；OLTP 场景 CPU 主要消耗在解析、排序、锁竞争与事务处理；
选型建议：高频小事务场景，主频优先（高主频 4-8 核优于低频 32 核）；复杂分析场景，核数优先；
8.0 的并行能力持续增强，多核收益更明显。

1.2 内存

Buffer Pool 是内存的第一优先级：目标是把热点数据页全部装进内存；
经验值：单机单实例 60%~80% 物理内存；多实例合计不超过 80%；
8.0 可动态调整 innodb_buffer_pool_size（SET GLOBAL 生效），配合 innodb_buffer_pool_instances 分片减少锁竞争；
内存不足的代价：物理读（磁盘 IO）→ 延迟从毫秒级劣化到 10ms+ 级。

1.3 磁盘

顺序 IO（redo/binlog）与随机 IO（数据页）并重；SSD 是底线（NVMe 更好），随机读 4KB 延迟从 HDD 的 10ms 降到 0.1ms 级；
redo 日志与数据文件分盘放置（避免写入互相干扰）；
8.0 支持 innodb_directories 多目录管理；备份/归档盘独立。

2. 核心参数调优（8.0）

2.1 内存类

参数	建议	说明
`innodb_buffer_pool_size`	物理内存 60%~80%	最大内存消耗项
`innodb_buffer_pool_instances`	8.0 默认按 128MB 分片	减少并发访问锁竞争
`innodb_log_buffer_size`	16~64MB	redo 缓冲，大事务批量提交受益
`sort_buffer_size`/`join_buffer_size`	2~8MB（会话级，勿全局过大）	每连接一份，过大浪费内存
`tmp_table_size`/`max_heap_table_size`	16~64MB	内存临时表上限，超限转磁盘临时表

2.2 日志与刷盘类

参数	建议	说明
`innodb_redo_log_capacity`	8.0.30+，写入密集 2~8GB	动态容量，替代旧 `innodb_log_file_size`
`innodb_flush_log_at_trx_commit`	1（默认，最安全）；性能优先可 2	1=每次提交刷盘；2=每秒刷盘（OS 缓存）；0=每秒由后台刷
`sync_binlog`	1（配合双一）；性能优先 0/N	1=每次提交刷 binlog
`innodb_flush_method`	Linux 用 `O_DIRECT`（8.0 默认）	绕过 OS 缓存，减少双缓存与刷盘抖动
`innodb_io_capacity`/`innodb_io_capacity_max`	SSD：2000/4000+	控制后台刷脏页速率
`innodb_max_dirty_pages_pct`	75~90	脏页占比阈值，触发异步刷盘

"双 1"配置（innodb_flush_log_at_trx_commit=1 + sync_binlog=1）：最安全但最慢；对一致性要求高的金融场景必须，一般业务可用 sync_binlog=1 + 日志刷盘 1 或折中方案。

2.3 并发与连接类

参数	建议	说明
`max_connections`	按业务压测（常见 1000~5000）	过小拒绝连接，过大引发连接风暴
`innodb_thread_concurrency`	0（8.0 默认不限制）	并发控制，通常保持默认
`innodb_purge_threads`	4（默认）	undo 回收并行度
`innodb_page_cleaners`	等于 buffer pool 实例数	刷脏页并行度

3. 复制架构：从单点走向集群

3.1 经典主从复制（异步）

图表渲染中…

复制链路：主库写 binlog → 从库 IO 线程拉取写入 relay log → SQL 线程回放；
异步复制的风险：主库宕机时，未同步到从库的事务可能丢失（主从延迟的本质）；
用途：读写分离、容灾、备份（从库备份不占主库资源）。

复制拓扑变体：

拓扑	结构	适用
一主一从/一主多从	星型	读写分离入门
级联复制（树形）	Master → 中转 → 多个从	从库数量多、减轻主库 dump 压力
环形复制	A→B→C→A	历史遗留，不推荐（环路风暴难排查）
双主复制	A↔B 互为主从	主备切换场景（配合 VIP），写入只在单侧

3.2 GTID 复制（8.0 推荐）

8.0 复制默认支持 GTID（全局事务标识）：每个事务有全局唯一 ID（source_id:transaction_id），从库按 GTID 自动定位，取代传统"binlog 文件名 + 位点"：

sql

-- 主从都开启
gtid_mode = ON
enforce_gtid_consistency = ON
-- 从库连接：自动定位
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='...', SOURCE_USER='repl', SOURCE_PASSWORD='...',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;
SHOW REPLICA STATUS\G   -- 观察 Replica_IO_Running / Replica_SQL_Running / Seconds_Behind_Source

GTID 优势：故障切换时无需人工找位点（新主上任，从库自动对齐）；复制一致性校验（gtid_executed）方便。

3.3 半同步复制（Semisync）

解决异步复制"可能丢数据"的问题：主库提交事务前，至少等待一个从库确认收到 binlog（收到 ≠ 已回放）：

5.7+ 插件式（rpl_semi_sync_master_enabled 等），8.0.22+ 默认内置（replication_semi_sync_master_enabled）；
参数：rpl_semi_sync_master_wait_point（AFTER_SYNC 默认：从库确认后主库才提交；AFTER_COMMIT）；
rpl_semi_sync_master_wait_for_slave_count：等待几个从库确认（1 即可，多了增延迟）；
超时降级：等待超时自动退回异步（rpl_semi_sync_master_timeout），保证可用性优先；
代价：每次提交多一次网络往返，延迟增加（通常 1-5ms）。

3.4 Group Replication 组复制（MGR）

图表渲染中…

MGR = 分布式共识 + 组内复制：事务在组内通过类 Paxos 协议达成一致（group_replication_consistency），多数派确认才算提交 → 真正的高可用基础；
8.0 中 MGR 是 InnoDB Cluster 的底座：MySQL Shell 管理 + MySQL Router 路由 + MGR 共识，一条命令搭建完整高可用集群；
配置要点：所有节点 gtid_mode=ON、enforce_gtid_consistency=ON、binlog_checksum=NONE（8.0.21+ 不需要）、disabled_storage_engines="MyISAM,..."；
模式：单主模式（单写点，故障自动切换）与多主模式（多写点，需业务容忍冲突检测回滚）；
适用：对 RPO=0（零丢失）有要求、节点 3-9 个的中型集群。

4. 读写分离架构落地

4.1 架构与组件

图表渲染中…

4.2 关键设计

路由策略：写请求 + 强一致读走主库；容忍延迟的读走从库（按业务打标，如 /*master*/ 注解）；
延迟监控：Seconds_Behind_Source 只反映 SQL 线程回放延迟（不精确），用 SHOW REPLICA STATUS + 心跳表（从库写入时间戳对比）更可靠；
一致性兜底：写后立即读的场景（如支付结果）强制走主库；一般读允许 50-200ms 延迟；
扩容：从库可水平加（读扩展），主库仍是写单点——写瓶颈需分库分表（下一章）；
切换：主库故障 → 提升从库（MGR 自动，传统主从用 MHA/Orchestrator 或自研）。

5. 经典架构选型

业务规模	架构	说明
起步（单机）	单实例 + 从库备份	从库只做容灾/备份
读多写少	一主多从 + 读写分离	读 QPS 扩展至 10 万级
高可用要求	半同步 + 故障自动切换	RPO≈0
自动化运维	InnoDB Cluster（MGR + Shell + Router）	官方一体化方案
写超单机	分库分表	见《如何做到MySQL高扩展性》

6. 小结

硬件三件套：CPU 主频优先、内存装下热点（Buffer Pool 60-80%）、NVMe SSD + redo 分盘；
参数调优：redo 容量（8.0.30+ 动态）、双 1 取舍、O_DIRECT、刷盘并发参数；
复制演进：异步 → 半同步 → MGR（Paxos 共识），GTID 让复制与切换不再依赖人工位点；
读写分离解决读扩展，写扩展必须分库分表——下一章进入高可用与高扩展体系。

下一章讲解如何做到 MySQL 的高可用：故障切换、MHA/Oracle MGR、InnoDB Cluster 全栈方案与演练。