《高性能 MySQL》阅读笔记：可扩展的 MySQL

[kyle-ip]

可扩展性

定义：当增加资源以处理负载和增加容量时系统能获得的投资产出率（ROI）。

引用作者的解释：当需要增加资源以执行更多工作时系统可得到划算的等同提升，缺乏扩展能力即系统达到收益递减转折点后无法继续增长。

• 使用 容量（capacity）表示系统处理负载的能力，即在 保证可接受的性能 情况下可达到的 吞吐量。
• 不依赖于性能，系统性能高低与系统是否具备可扩展性无关。
• 即使 MySQL 架构可扩展，应用本身也可能无法扩展。

除了最直观的数据量之外，可从以下角度考虑负载：

• 用户量：更多的用户意味着更多的数据、更多的事务处理、更复杂的关系查询，而且这些指标的都比用户量增长更快。
• 用户活跃度：比如应用引入新特性或发生突发事件，部分用户的活跃度会发生不同程度的变化。
• 相关数据集的大小：比如关联用户群体上执行查询或计算，比处理独立的用户数据复杂得多。

系统每投入多 1 倍的资源，可得到 k 倍的能力提升，可称之为「线性扩展」。

而绝大部分系统存在线性扩展偏差：

• 只能以比线性扩展更低的扩展系数进行扩展。
• 达到最大吞吐量临界点后投入更多反而降低系统吞吐量。

影响因素包括：必要的串行工作、交互工作，节点间或进程间通信（信道数量增长率为 worker 数量增长率的二次方）等，这些都不是通过投入更多资源就能充分解决的。

因此传统关系型数据库的能力很大程度上取决于单机资源配置。

扩展 MySQL

当系统无法满足负载增长时需要扩展：从 CPU 密集型变成 I/O 密集型、高并发查询、延迟增长等。

而规划系统如何扩展、扩展多少，则要思考应用功能完成情况（复杂度）、预期的最大负载、系统部分失效时如何处理请求等问题。

参考@canji Zhou 的分享 高性能 MySQL 第三版读书笔记 ，就有提到「单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表」，而且「如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表」。

笔者认为还要考虑索引规模，如果表字段不多也没有特别大的索引，有时上千万行的「小表」也是可以接受的。

性能调优

在扩展系统前通过性能调优来避免不必要的扩展或推迟扩展是很有必要的。

可以先从以下角度考虑，尝试优化现有系统：

• SQL 优化：比如减少操作数据量、优化关联与聚合查询，结合 Explain 分析出更好的 SQL 语句。
• 索引优化：了解索引选取规则（Anslyze）、合理使用（筛选和排序）和维护索引（唯一键、覆盖索引等）。
• 锁与事务优化：控制锁粒度、避免长事务、避免死锁等。

SQL 优化最直接可观，而索引、锁与事务优化则需要谨慎：

• 对于大数据量、高负载系统而言变更的副作用可能是灾难性的（如造成锁表、停机）。
• 代码逻辑的变更可能出现意外的结果，需要严格全面地测试。

总结：MySQL 优化基础

向上扩展

即垂直扩展，比如升级设备：使用更高大的内存、多核 CPU 可处理更多更复杂的任务，结合磁盘与网络 I/O 优化效果也相当可观。

这种扩展是有限制的，一方面是经济性考量，另一方面是系统本身的限制：比如单一复杂 SQL 导致的慢查询无法利用多核 CPU，或数据集庞大以至于无法有效缓存、磁盘成为瓶颈等，这些都不是用更强的硬件设备就能解决的。

向外扩展

即横向扩展、水平扩展。

• 复制：系统扩展的基础，比如将数据分发到多个节点，备库用作查询（读写分离）。
• 拆分：将工作负载分布到多个节点。而 MySQL 本身不支持或无法实现自动化，需要引入 DB 中间件。

在冗余系统的节点架构中，一个节点可以是：主备与主从（一对一或一对多），主 + 分布式块设备（DRBD），存储区域网络（SAN）。

「节点」在这里非严格定义，在这里指的是存储相同的数据服务器，而非一台服务器。

按功能拆分

按系统的职责拆分任务、交由不同的节点负责处理；或是对单个节点的数据进行划分，且划分的表集合之间不执行关联。

即以应用层关联代替数据库表关联，这种做法只是为了扩展，不通用且难以做到高效。

虽然业务关联动作本身也许不高效，但在拆分数据库关联，改为只查单表、在应用层关联的好处是引入缓存时可对缓存做更细粒度的管理，也更容易复用。

问题是当一个被划分出来功能足够庞大后就只能进行垂直扩展，且一套系统进行太多的功能划分后就难以采用扩展性更好的设计。

比如当某些模块用户数据规模随业务增长迅速，由于整套系统功能已经切分得足够细（业务层面难以划分出更小粒度），而该模块数据捆绑在单一节点，可考虑垂直扩展。

而当垂直扩展走到尽头，此时只能对原系统做业务重构和迁移。

数据分片

把数据集切分，存储到不同的节点上。

比如按功能拆分的系统，其中的某些功能模块还可根据分片键对数据进行分片；或不拆分功能、直接设计为分片架构。

区别于传统主从架构、从库只读的方式，分片架构可扩展写容量。

字节 RDS 主备库、分片库是由公司自研的 Dbatman 提供 SQL 转发，数据分库，读写分离等支持。

其中使用分片库的业务代码 SQL 需要指定分片键。比如在 【技术方案】Tabular 存储设计 中，以 SheetID 为分库的 key（对应一个算薪活动），再以算薪活动 Seq 作为分表的 key。

因为用户指定了逻辑分库，而底层存储还会将其映射到物理分库上，由中间件实现并对应用屏蔽了路由选表的能力，理论上存储容量不限。

	说明
分片键	往往取自 E-R 图中可使关联实体靠得更近的字段，设置合理的分片键可减少跨分片操作、减少访问分片的个数。关键在于设计早期就考虑到将以什么方式查找数据。如果数据存在两个及以上的维度，还可以有多个分片键：设计两个冗余的数据存储，指定不同的分片键。一并存储跨越两个分片边界的关键字段。
分片规模	让分片足够小而均衡易于维护（alter table、check table、optimize table 等。备份和恢复、表变更时可能的阻塞，平衡容量时的数据转移），但分片太小也会导致跨分片查询增多。
分片部署	分库分表，可自行创建带分片键后缀的库表名称，或使用 DB 中间件直接建立分片库表。如果数据具备某种分片亲和性，应在物理存储上「把数据存放得更近」（服务器、子网、机房）。
分配策略	固定分配：分片函数只依赖于分片键，Range 与 Hash 策略都属于这种。优点是设计简单，开销低，但存在问题：当数据多且分片少时分片的负载很难平衡（即数据倾斜）。无法自定义数据放在哪个分片（比如热点数据）。修改分片策略需要重新分配数据。动态分配：使用分片表存放每行数据 ID 与分片 ID 的关系（数据目录可延迟更新，可利用缓存优化），优点是 数据存储控制更灵活。也可以 先将数据哈希映射到桶，再将桶动态映射到分片。显式分配：插入数据时显式选择分片，比如把分片号编码到数据 ID 中，或用独立的列存储分片键（有存储开销）。优点是可减少一次关联和查找，缺点是固定分片难以做到分片间负载均衡。
重新均衡	在分片间移动数据以达到负载均衡。
全局 ID	分片架构下无法使用自增 ID，解决方法：使用 auto_increment_offset 为每个分片节点设置不同的自增步长。全局 ID 生成器（Snowflakes、ID Generator）。全局节点 ID 自增表，或用全局节点为分片节点批量分配 ID。分片号 + 自增 ID 的复合值。UUID / GUID、NanoID.
分片工具	负责选择分片和执行查询、分布式一致性校验、跨分片数据聚合和关联、锁与事务管理、创建和平衡分片，把这些操作隐藏在抽象层。
事务支持	对于相同分片内的数据集可用本地事务，对于跨分片数据集需要引入分布式事务组件来支持。
能力限制	已知分库分表后一些功能将无法支持，比如修改需要带上分片列，复杂查询（包括跨分片的 Join、带聚合函数的 Order By 等）不支持，具体参考 Dbatman语句支持 。

关于「分片键的选取取决于如何使用数据」的例子：

原文中讲述一个社交网站有「读取某个用户的所有文章」和「读取某篇文章下所有用户评论」的需求。如果以文章分片则无法支持前者，而以用户分片则无法支持后者。

由于评论数据有文章 ID 和用户 ID，解决方法是评论和用户数据一起存储（满足需求 2），把评论标题和文章数据一起存储，要读取完整评论内容则再访问用户数据（满足需求 1）。

向内扩展

（在本书中清理也可称为扩展，毕竟这也算是应对数据和负载增长的方法嘛...)

如及时归档和清理数据，但需要注意：

• 锁和事务的约束：比如使用脚本更新删除操作造成锁表。
• 维护数据一致性：比如更新关联表的引用完整性。
• 避免数据丢失等：了解数据删除规则，是软删还是硬删，操作是否可逆等。
• ...

另一方面是在设计上可做冷热数据分离以方便做缓存，或利用分区表提升内存利用率。

分区表

可以理解为存储引擎层的分表，但对于 Server 层而言是一个表。

特点是对业务透明，而且清理历史数据更方便，如按照时间分区的分区表，可直接通过 ALTER TABLE t DROP PARTITION ... 删除历史数据分区（速度快，对系统影响小）。

CREATE TABLE `tb` (
  `t` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(t))
(PARTITION p_2022 VALUES LESS THAN (2022) ENGINE = InnoDB,
 PARTITION p_2023 VALUES LESS THAN (2023) ENGINE = InnoDB,
 PARTITION p_2024 VALUES LESS THAN (2024) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

-- tb.frm
-- tb#P#p_2022.ibd
-- tb#P#p_2023.ibd
-- tb#P#p_2024.ibd
-- tb#P#p_other.ibd

多实例扩展

当分片足够小，可在一台服务器运行多个分片实例，可以更充分利用服务器性能。

原因是一个 MySQL 实例无法充分利用多核 CPU（24 核+）和大内存（128G+），对于单实例而言，这台服务器是性能过剩而浪费的。这样部署多个实例以充分利用 CPU 和内存，同时还要避免网络成为瓶颈：多网卡绑定、更新系统内核版本等。

这是很多应用的传统部署方式（单节点多 JVM 应用、Tomcat 集群等）。同时这也是云计算资源池的做法，但在物理机上部署多实例可节省虚拟化本身的开销。

如果将每个 MySQL 进程实例绑定到指定的（具有相同物理套接字的）CPU 核心上，可在每个核心上获得更好的性能，且工作线程无需跨核心同步共享数据。

跨 CPU 通信是很复杂的问题，在多核架构中，NUMA 效应、Cache 的大小、设备 offload、应用本身的特征都会对应用具体表现有很大影响。

集群扩展

以上的架构要么是在多台服务器上复制存放相同的数据（单节点，如主从库），要么在不同的服务器上分别 固定地 存放不同的一部分数据（多节点，如分片库），在逻辑上在物理上都属于以 多个数据库 对外提供能力，本质上还是 单体数据库。

而 分布式数据库 是多节点的集群，但却以 单一逻辑数据库 提供能力的。

笔者认为分布式数据库与单体数据库最大的区别是 分片管理机制：

• 单体数据库：在固定或动态分配策略下的分片，数据所在的分片是唯一确定的。存放分片的节点之间并不对等，可理解为是不同的库。扩展方式一般是客户端组件或代理中间件 + 单体数据库。
• 分布式数据库：分片通过元数据管理，可动态地合并拆分，再基于分布式共识算法进行选举、复制、保证高可靠等。由于技术细节收敛到数据库系统内部，对于外部应用而言它是单一的数据库，无需引入中间抽象层即可完成扩展。

如果关于分布式数据库有更多想了解，可参考：分布式数据库 30 讲

文中举出 MySQL Cluster、ScaleBase、GenieDB、Akiban 等例子。而在@kun Xiao 的分享高性能MySQL_第三版学习笔记-高可用性中也介绍过字节实现的 ByteNDB，在此也不会谈太多。

MySQL Cluster

即 NDB Cluster，其主要特点：

• NDB 数据库和作为 SQL 前端的 MySQL 存储引擎（以 MySQL 的方式控制 NDB）。
• 分布式、具备容错性、非共享，支持同步复制和节点间数据自动分片。
• 复杂查询支持不足，不支持 MVCC，无死锁检测。

负载均衡

负载均衡可以多种读写策略、部署在架构的任一层次上。主要目的是：

• 可扩展性：读写分离。
• 高效性：高性能节点处理更多的工作。
• 可用性
• 透明性：多节点配置对用户无感知。
• 一致性：对有状态应用的访问路由。

直接连接

复制上的读写分离，比如在应用中为负载均衡做决策，常见的读写分离方式：

• 基于查询
• 基于脏数据
• 基于会话
• 基于版本
• 基于全局版本 / 会话

这些都是在应用中实现，一般是对访问数据有「足够新」的要求：比如选择在某些实时性要求很高接口或某些用户请求访问主库、其他访问从库，以避免主从同步延时造成读取到脏数据。

除此之外还有修改应用配置、修改 DNS、转移 IP 地址等方式实现负载均衡，但在现在看来已经很少用到，在此不再赘述。

引入中间件

这里的负载均衡不是专门为 MySQL 而设的，而对于多种应用服务器通用，想要了解更多可参考：

https://icyfenix.cn/architect-perspective/general-architecture/diversion-system/load-balancing.html

一主多备间的负载均衡

一主多备架构结合负载均衡可支持以下特性：

• 功能分区：配置一组备库支持不同的功能需求。
• 过滤和数据分区：使用复制过滤技术在相似的备库上进行数据分区，或对读请求进行分区以提高缓存效率。
• 将部分写操作转移到备库：即主库上不需要处理写操作中的所有工作时，可分解写查询、在备库上执行一部分。
• 保证备库跟上主库：比如使用复制心跳检查延迟情况，或使用 master_pos_wait() 阻塞主库直到备库跟上主库同步点。
• 同步写操作：使用 master_pos_wait() 还可以确保写操作已经同步到一或多个备库上。

由于 MySQL 支持的复制方案非常灵活，可以考虑做一些有针对性的配置：

比如由某些分区或备库处理具备某种特征的数据请求（只存储一部分主库数据），由于局部性原理，可以更充分地利用备库内存；同时由于备库只同步主库一部分的写入负载、同步延迟更稳定。

总结

1. 复杂的系统架构是演进而非设计出来的，数据库扩展是在明确需要、做好规划的基础上一步步完成。
2. 只在有必要的时候扩展，先考虑读写分离，再到数据分片或按功能分区。
3. 只要是多节点架构就可能出现事务和数据一致性问题，可通过数据库中间件、负载均衡策略解决。而分布式数据库由系统内部提供支持，因此扩展性比单体数据库更好。

参考

• RDS 服务概览
• 数据库平台使用说明
• Dbatman 介绍
• Payroll系统分库存储方案
• 【技术方案】Tabular 存储设计
• 负载均衡 | 凤凰架构
• MySQL 思维导图