性能调优

发表于 2024-12-05 更新于 2025-06-24 分类于 MySQL

选择合适的 CPU

一般而言，当前数据库的应用类型可分为两大类：OLTP（Online Transaction Processing，在线事务处理）和 OLAP（Online Analytical Processing，在线分析处理）。这是两种截然不同的数据库应用。OLAP 多用于数据仓库或数据集中，一般需要执行复杂的 SQL 语句来进行查询；OLTP 多用于日常的事务性应用中，如银行交易、在线商品交易、Blog、网络游戏等应用。相对于 OLAP，OLTP 应用中的数据库容量较小。

InnoDB 存储引擎一般都应用于 OLTP 的数据库应用，这种应用的特点如下：

用户操作的并发量大；
事务处理的时间一般比较短；
查询的语句较为简单，一般走索引；
复杂的查询较少。

可以看出，OLTP 的数据库应用本身对 CPU 的要求并不是很高，因为复杂的查询（如排序、连接等）非常耗费 CPU，而这些操作在 OLTP 应用中较少发生。因此，可以说：OLAP 是 CPU 密集型的操作，OLTP 是 I/O 密集型的操作。建议在采购服务器时，将更多注意力放在 I/O 性能的配置上。

OLTP 为主：优先关注存储子系统性能，CPU 核数 4–8 核即可满足大多数场景，多核有助于并发连接处理；单语句并行受限于 MySQL 内核，需依赖多会话并发提升吞吐。

OLAP 为主：建议选用多核高主频机器，如 16–32 核以上，同时配合支持并行查询的引擎（Aurora/MySQL HeatWave）以充分发挥 CPU 性能。

为了支持多核应用，我们可以考虑 InnoDB 并行特性与调优：

并行 I/O 线程

innodb_read_io_threads 与 innodb_write_io_threads 默认均为 4，或为可用逻辑处理器数的一半，且最小为 4；可手动增至与 CPU 核数相同，提升 I/O 吞吐。
合理设置范围为 1–64，具体值需结合存储性能与并发需求测试。

并行索引扫描

MySQL 8.0.14 起支持 innodb_parallel_read_threads，可并行扫描聚簇索引子树，加速诸如 CHECK TABLE、COUNT(*) 等操作。默认值为可用 CPU 数，或根据系统特性自动调整；在多核机上带来显著读取性能提升。

并行 DDL 构建

从 MySQL 8.0.27 起，innodb_ddl_threads 使在线创建二级索引时可并发执行多线程，显著缩短大表的 DDL 时间。

内存的重要性

InnoDB 将数据和索引缓存在一个很大的缓冲池中，即 InnoDB Buffer Pool。因此，内存的大小直接影响了数据库的性能。

Percona 公司的 CTO Vadim 对此做了一次测试，以此反映内存的重要性：数据和索引总大小为 18 GB，然后将缓冲池的大小分别设为 2 GB、4 GB、6 GB、8 GB、10 GB、12 GB、14 GB、16 GB、18 GB、20 GB、22 GB，再进行 sysbench 的测试。可以发现，随着缓冲池的增大，测试结果 TPS（Transactions Per Second）会线性增长。当缓冲池增大到 20 GB 和 22 GB 时，数据库的性能有了极大的提高，因为此时缓冲池的大小已经大于数据文件本身的大小，所有对数据文件的操作都可以在内存中进行。因此，这时的性能应该是最优的，再增大缓冲池并不能再提高数据库的性能。

所以，应该在开发应用前预估活跃数据库的大小是多少，并以此确定数据库服务器内存的大小。当然，要使用更多的内存还必须使用 64 位的操作系统。

如何判断当前数据库的内存是否已经达到瓶颈了呢？可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，通常 InnoDB 存储引擎的缓冲池命中率不应该小于 99%，如：

mysql> SHOW GLOBAL STATUS LIKE 'innodb%read%'\G
*************************** 1. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead
Value: 0
*************************** 2. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_evicted
Value: 0
*************************** 3. row ***************************
Variable_name: Innodb_buffer_pool_read_requests
Value: 167051313
*************************** 4. row ***************************
Variable_name: Innodb_buffer_pool_reads
Value: 129236
*************************** 5. row ***************************
Variable_name: Innodb_data_pending_reads
Value: 0
*************************** 6. row ***************************
Variable_name: Innodb_data_read
Value: 2135642112
*************************** 7. row ***************************
Variable_name: Innodb_data_reads
Value: 130309
*************************** 8. row ***************************
Variable_name: Innodb_pages_read
Value: 130215
*************************** 9. row ***************************
Variable_name: Innodb_rows_read
Value: 17651085
9 rows in set (0.00 sec)

上述参数的具体含义如下表所示：

参数	说明
Innodb_buffer_pool_reads	表示从物理磁盘读取页的次数
Innodb_buffer_pool_read_ahead	预读的次数
Innodb_buffer_pool_read_ahead_evicted	预读的页，但是没有被读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率
Innodb_buffer_pool_read_requests	从缓冲池中读取页的次数
Innodb_data_read	总共读入的字节数
Innodb_data_reads	发起读请求的次数，每次读取可能需要读取多个页

以下公式可以计算各种对缓冲池的操作：
$$\text{缓冲池命中率} =
\frac{\text{Innodb_buffer_pool_read_requests}}
{\text{Innodb_buffer_pool_read_requests} + \text{Innodb_buffer_pool_read_ahead} + \text{Innodb_buffer_pool_reads}}$$
$$\text{平均每次读取的字节数} =
\frac{\text{Innodb_data_read}}{\text{Innodb_data_reads}}$$

从上面的例子看，缓冲池命中率 = 167 051 313 / (167 051 313 + 129 236 + 0) ≈ 99.92%。

即使缓冲池的大小已经大于数据库文件的大小，这也并不意味着没有磁盘操作。数据库的缓冲池只是一个用来存放热点的区域，后台的线程还负责将脏页异步地写入到磁盘。此外，每次事务提交时还需要将日志写入重做日志文件。

硬盘的选择

传统机械硬盘（HDD）

在现代数据库系统中，存储子系统的性能直接影响到应用的响应速度和吞吐能力。传统机械硬盘依赖旋转盘片和寻道臂，虽然成熟稳定，却在随机访问场景中存在较高的延迟；固态硬盘以闪存为存储介质，通过消除机械部件带来了数百倍的随机 I/O 性能提升；而 NVMe 接口的固态硬盘则更进一步，通过 PCIe 通道实现更低的延迟和更高的并发吞吐。针对这些不同类型的硬盘，MySQL 提供了多项优化参数和社区方案，以充分利用闪存特性并扩展内存缓冲池，从而在 OLTP 和 OLAP 场景中都能获得优异的数据库性能。

传统机械硬盘的主要性能指标包括寻道时间和转速。寻道时间指从发出读写指令到磁头到达目标扇区所需的时间，目前高端 SAS 硬盘的平均寻道时间约为三毫秒；转速以每分钟转数（RPM）计量，常见值为 7200RPM 和 15000RPM。由于机械硬盘在数据访问时需要进行磁头定位和盘片旋转，随机 I/O 操作的响应时间通常在数毫秒级别，而顺序读写则能达到上百兆字节每秒的吞吐。为提高性能和可靠性，生产环境中常将多块机械硬盘通过 RAID 0 或 RAID 10 组合，以提升顺序吞吐和分散热点。

固态硬盘

固态硬盘，也称基于闪存的固态硬盘，是近年来出现的一种新型存储设备，它内部由闪存颗粒（Flash Memory）组成，具有低延迟、低功耗和抗震性等优点。传统机械硬盘依赖磁头寻道和旋转延迟，固态硬盘无需机械移动部件，因而能提供一致的随机访问时间，一般小于0.1毫秒。闪存中的数据不可直接覆盖写入，只能通过扇区（sector）或块级的擦除（erase）操作来重写，在擦除前需要先对整个擦除块进行擦除，该擦除块大小通常是128KB或256KB，并且每个擦除块有写入次数的寿命限制，需要配合垃圾回收和磨损均衡算法来解决写入次数限制问题。固态硬盘的控制器会将主机的逻辑地址映射成实际的物理地址，写操作同时需要更新映射表，带来额外的开销，但相对于机械硬盘的寻道和旋转延迟，这些开销仍然较低。

在数据库领域，尤其是 MySQL 的 InnoDB 存储引擎中，充分利用固态硬盘所带来的高 IOPS 特性是提升性能的重要手段之一。在 InnoDB 中，可以通过调整 innodb_io_capacity 和 innodb_io_capacity_max 参数来控制后台 I/O 的并发数和速率，使得刷新操作更加均匀且高效，对于 SSD 存储，一般建议将这两个参数设置为几万甚至更高的值，以充分发挥闪存的并行读写能力。除此之外，还可以使用 InnoDB 8.0 系列中提供的 L2 Cache 解决方案，将 SSD 作为二级缓存层，以进一步扩充内存缓冲池的容量，从而降低对主存的依赖并提升数据库整体吞吐量。Facebook 推出的 Flash Cache 和 Percona 的 Flashcache 等方案也能在操作系统层面提供类似功能，但 InnoDB 原生的 L2 Cache 与存储引擎深度集成，能够获得更好的效果。

优化 InnoDB 磁盘 I/O 还可以从以下几个方面入手：首先，将 innodb_buffer_pool_size 设置为系统内存的 50% 到 75%，这样大部分热数据可以缓存在内存中，减少对磁盘的访问需求。其次，将 innodb_flush_method 设置为 O_DIRECT，避免文件系统缓存与 InnoDB 缓冲池之间的双重缓存带来的不必要 I/O 开销。再者，根据操作系统和硬件特性，选择合适的 I/O 调度器，例如在 Linux 上使用 noop 或 deadline 调度器，以减少调度延迟并配合 SSD 的并行特性。另外，当系统物理内存充足时，应尽量避免使用交换空间，因为即使是 SSD，频繁的交换也会缩短其寿命并带来不必要的写放大效应。

操作系统的选择

操作系统的选择和调优对数据库的响应速度、并发处理能力以及 I/O 性能具有至关重要的影响。合适的 OS 及其配置不仅能够发挥底层硬件的最大性能，还能减少不必要的系统开销，从而提升 MySQL 的整体吞吐量与稳定性。

Linux vs. Windows vs. BSD

Linux：MySQL 在 Linux/Unix 平台上经过了长期优化，社区与厂商（包括 Oracle）主要以 Linux 为开发和测试环境，因此在性能和兼容性方面通常优于 Windows。Linux 的轻量内核和丰富的工具链也有助于更精细的调优和故障排查。
Windows：尽管 MySQL 支持 Windows，但在高并发、大规模生产环境下，不如 Linux 稳定，且 Windows Server 的授权费用和系统开销较高。
BSD/macOS：较少用于生产环境，社区支持与文档相对有限，调优经验不如 Linux 丰富。

Linux 发行版与版本选择

发行版推荐

RHEL 及衍生版（CentOS、Rocky Linux、Oracle Linux）：长期支持（LTS）版本稳定，企业级特性丰富，文档与社区调优经验充足。
Debian/Ubuntu Server：包管理灵活，社区活跃，适合快速迭代与测试环境；Ubuntu LTS 版本也可用于生产，但需注意内核参数差异。
其他（SUSE、Arch 等）：在特定场景下可用，但社区调优资料相对较少。

内核版本的话，优先选择稳定的 LTS 内核（如 5.x 系列），以获得长期安全和性能更新；可根据硬件特性（如最新 NVMe SSD）适当测试更高版本内核的性能优势。

文件系统的选择

不同文件系统在并发 I/O 性能、数据完整性保障、快照支持和对闪存设备的优化上各有侧重。一般而言，若以大文件吞吐和并发写入为主，可优先考虑 XFS；若追求成熟稳定和通用场景，可选用 ext4；若需要原生快照、子卷和校验功能，可考虑 Btrfs；若对数据完整性和自我修复有极致需求，可采用 ZFS；若存储介质为 NAND 闪存且写放大需严格控制，则 F2FS 是最佳方案。

在需要处理海量大文件或日志聚合的场景下，XFS 拥有优异的并发写入性能和在线扩展能力，能够在多线程写入时保持稳定吞吐。

对于大量小文件的创建、删除和元数据访问，ext4 表现更加均衡，元数据操作延迟较低，且社区支持度极高。

Btrfs 和 ZFS 均内置数据与元数据校验和机制，可以在读写时检测并自动修复错误，显著提高数据完整性保障。

ZFS 采用 Copy-On-Write 事务模型，所有写入先在新块上完成，元数据与数据层层校验后再引用，有助于避免因崩溃导致的不一致状态。

Btrfs 原生支持轻量级子卷与快照，通过写时复制技术，创建快照几乎零成本，便于在线备份和回滚。

ZFS 快照同样高效，一旦创建可立即生效且节省空间，并可通过 zfs send/recv 在集群或异地环境中可靠地迁移数据集。

Btrfs 支持透明在线压缩，既能节省存储空间，也能降低 SSD 写入量；ZFS 则提供多种压缩算法（如 LZ4），可根据性能与压缩率需求灵活选择。

F2FS 是针对 NAND 闪存设计的日志结构文件系统，通过减少写放大和高效的清理机制，提高中低端 SSD 和 eMMC 的使用寿命与性能。

RAID 的设置

RAID（Redundant Array of Independent Disks，独立磁盘冗余阵列）的基本思想就是把多个相对便宜的硬盘组合起来，成为一个磁盘数组，使性能达到甚至超过一个价格昂贵、容量巨大的硬盘。由于将多个硬盘组合成一个逻辑扇区，RAID 看起来就像一个单独的硬盘或逻辑存储单元，因此操作系统只会把它当作一个硬盘。

RAID 的作用是：

增强数据集成度；
增强容错功能；
增加处理量或容量。

根据不同磁盘的组合方式，常见的 RAID 组合方式可分为 RAID 0、RAID 1、RAID 5、RAID 10 和 RAID 50 等。

RAID 0：将多个磁盘合并成一个大的磁盘，不会有冗余，并行 I/O，速度最快。RAID 0 亦称为带区集（Striping），它将多个磁盘并列起来，使之成为一个大磁盘，如下图所示。

在存放数据时，会将数据按磁盘的个数进行分段，同时将这些分段并行写入各个磁盘。所以，在所有的 RAID 级别中，RAID 0 的速度是最快的。但是 RAID 0 没有冗余功能，如果一个磁盘（物理）损坏，则所有的数据都会丢失。

理论上，多磁盘的效能等于（单一磁盘效能）×（磁盘数），但实际会受总线 I/O 瓶颈及其他因素的影响，RAID 效能会随边际递减。也就是说，假设一个磁盘的效能是 50 MB/s，两个磁盘的 RAID 0 效能约为 96 MB/s，三个磁盘的 RAID 0 也许是 130 MB/s 而不是理论值 150 MB/s。

RAID 1：两组以上的 N 个磁盘相互作为镜像（如下图所示）。在一些多线程操作系统中，RAID 1 能有很好的读取速度，但写入速度略有降低。除非主磁盘和镜像磁盘同时损坏，否则只要有一块磁盘正常就能维持运行，可靠性最高。RAID 1 就是镜像，其原理是在主硬盘上存放数据的同时，也在镜像硬盘上写入相同的数据。当主硬盘（物理）损坏时，镜像硬盘就顶替其工作。由于有镜像磁盘做数据备份，RAID 1 的数据安全性在所有 RAID 级别中最高。但是，无论用多少块磁盘作为 RAID 1，仅有一块磁盘有效，是所有 RAID 级别中磁盘利用率最低的一个级别。

RAID 5：是一种兼顾存储性能、数据安全和存储成本的存储解决方案。它使用 Disk Striping（硬盘分区）技术，至少需要三块硬盘。RAID 5 不对存储的数据进行完整备份，而是将数据和对应的奇偶校验信息存储到 RAID 5 的各个磁盘上，且奇偶校验信息和对应的数据分别存储在不同的磁盘上。当 RAID 5 中的一块磁盘发生故障后，可利用其余磁盘上的数据和奇偶校验信息，恢复丢失的数据。RAID 5 可视为 RAID 0 和 RAID 1 的折中方案，为系统提供数据安全保障，但其安全性低于镜像，磁盘空间利用率高于镜像。RAID 5 具有与 RAID 0 相近的读取速度，只是多了一条奇偶校验信息；写入速度相对较慢，若使用 Write Back 可有所改善。同时，由于多个数据块共用一条奇偶校验信息，RAID 5 的磁盘空间利用率高于 RAID 1，存储成本相对较低。RAID 5 的结构如下图所示。

RAID 10 是先镜像再分区数据，将所有硬盘分为两组，视为 RAID 0 的最低组合，然后将这两组各自视为 RAID 1 运行。RAID 10 有着不错的读取速度，而且拥有比 RAID 0 更高的数据保护性。RAID 01 则与 RAID 10 程序相反，先以 RAID 0 将数据划分到两组硬盘，RAID 1 将所有的硬盘分为两组，变成 RAID 1 的最低组合，而将两组硬盘各自视为 RAID 0 运行。RAID 01 比 RAID 10 有着更快的读写速度，不过也多了一些会让整个硬盘组停止运转的几率，因为只要同一组的硬盘全部损毁，RAID 01 就会停止运作，而 RAID 10 可以在牺牲 RAID 0 的优势下正常运作。RAID 10 巧妙地利用了 RAID 0 的速度及 RAID 1 的安全（保护）两种特性，它的缺点是需要较多的硬盘，因为至少必须拥有四个以上的偶数硬盘才能使用。RAID 10 和 RAID 01 的结构如下图所示。

假设有 4 块磁盘 D0、D1、D2、D3：

RAID 01
- 条带组 A：D0、D1 做 RAID 0
- 镜像组 B：D2、D3 做 RAID 0，然后镜像 A←→B

一旦 D0、D1 同时损坏，A 组失效，整个逻辑盘挂掉——尽管 D2、D3 还健康，但无法自动接管。

RAID 10
- 镜像对 1：D0⇄D1 做 RAID 1
- 镜像对 2：D2⇄D3 做 RAID 1
- 条带化：将数据交替写到 “对 1” 和 “对 2”

即便 D0、D2 同时故障，只要 D1、D3 健康，每个条带仍有一个副本存活，整个卷继续可用。

条带的实现原理如下：系统首先将待存储的数据按预定义的条带大小（如 64 KB、128 KB）切分为若干数据块，每个数据块称为一个“条带”。分割后的条带按照轮询（round-robin）方式依次写入多块磁盘或 SSD。比如，第一条带写入设备 A，第二条带写入设备 B，第三条带再回到设备 A，依此循环。

RAID 50：RAID 50 也被称为镜像阵列条带，由至少六块硬盘组成，像 RAID 0 一样，数据被分区成条带，在同一时间内向多块磁盘写入；像 RAID 5 一样，也是以数据和校验位来保证数据的安全，且校验条带均匀分布在各个磁盘上，其目的是提高 RAID 5 的读写性能。

对于数据库应用来说，RAID 10 是最好的选择，它同时兼顾了 RAID 1 和 RAID 0 的特性。但是，当一个磁盘失效时，性能可能会受到很大的影响，因为条带会成为瓶颈。也就是说，当 RAID 10 中的某块磁盘发生故障时，阵列不会像 RAID 0 那样整体挂掉，但其条带并行度会立刻下降，进而造成 I/O 吞吐能力的衰减。具体来说，RAID 10 在正常状态下，将数据在多对镜像组之间条带化写入，可并行利用所有镜像对的读写能力；而一旦某个镜像对失去一块盘，整个镜像对的条带操作只能依赖剩余的单盘，导致该镜像对“窄化”，从而拖慢整组的并行访问速度。

RAID Write Back 功能

RAID Write Back 功能是指 RAID 控制器能够将写入的数据放入自身的缓存中，并把它们安排到后面再执行。这样做的好处是，不用等待物理磁盘实际写入的完成，因此写入变得更快了。对于数据库来说，这显得十分重要。例如，对重做日志的写入，在将 sync_binlog 设置为 1 的情况下二进制日志的写入、脏页的刷新等都可以使得性能得到明显的提升。

但是，当操作系统或数据库关机时，Write Back 功能可能会破坏数据库的数据。这是由于已经写入的数据可能还在 RAID 卡的缓存中，数据可能并没有完全写入磁盘，而这时故障发生了。为了解决这个问题，目前大部分的硬件 RAID 卡都提供了电池备份单元 (BBU, Battery Backup Unit)，因此可以放心地开启 Write Back 的功能。不过我发现每台服务器的出厂设置都不相同，应该将 RAID 设置要求告知服务器提供商，开启一些认为需要的参数。

如果没有启用 Write Back 功能，那么在 RAID 卡设置中显示的就是 Write Through。Write Through 没有缓存写入，因此写入性能可能不是很好，但它却是最安全的写入。

即使用户开启了 Write Back 功能，RAID 卡也可能只是在 Write Through 模式下工作。这是因为安全使用 Write Back 的前提是 RAID 卡有电池备份单元。为了确保电池的有效性，RAID 卡会定期检查电池状态，并在电池电量不足时对其进行充电，在充电的这段时间内会将 Write Back 功能切换为最为安全的 Write Through。

用户可以在没有电池备份单元的情况下强制启用 Write Back 功能，也可以在电池充电时强制使用 Write Back 功能，只是写入是不安全的。用户在启用前应该确认这一点，否则不应该在没有电池备份单元的情况下启用 Write Back。

备份与恢复

发表于 2024-11-30 更新于 2025-06-24 分类于 MySQL

数据库的备份与恢复是一项最基本的操作与工作。在意外情况下（如服务器宕机、磁盘损坏、RAID 卡损坏等）要保证数据不丢失，或者是最小程度地丢失，每个开发者应该时刻关心所负责的数据库备份情况。

可以根据不同的类型来划分备份的方法。根据备份的方法不同可以将备份分为：

Hot Backup（热备）
Cold Backup（冷备）
Warm Backup（温备）

Hot Backup 是指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响。这种方式在 MySQL 官方手册中称为 Online Backup（在线备份）。Cold Backup 是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup（离线备份）。Warm Backup 备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性。

按照备份后文件的内容，备份又可以分为：

逻辑备份
裸文件备份

在 MySQL 数据库中，逻辑备份是指备份出的文件内容是可读的，一般是文本文件。内容一般是一条条 SQL 语句，或者是表内实际数据组成。如 mysqldump 和 SELECT * INTO OUTFILE 的方法。这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复所需要的时间往往较长。

裸文件备份是指复制数据库的物理文件，既可以是在数据库运行中的复制（如 ibbackup, xtrabackup 这些工具），也可以是在数据库停止运行时直接的数据文件复制。这类备份的恢复时间往往较逻辑备份短很多。

若按照备份数据库的内容来分，备份又可以分为：

完全备份
增量备份
日志备份

完全备份 是指对数据库进行一个完整的备份。增量备份 是指在上次完全备份的基础上，对下次更改的数据进行备份。日志备份 主要是指对 MySQL 数据库二进制日志的备份，通过对一个完全备份做完二进制日志的重做（replay）来完成数据库的 point-in-time 的恢复工作。MySQL 数据库复制（replication）的原理就是异步实时地将二进制日志重做传送并应用到从（slave/standby）数据库。

对于 MySQL 数据库来说，官方没有提供真正的增量备份的方法，大部分是通过二进制日志完成增量备份的工作。这种备份较之真正的增量备份来说，效率还是很低的。假设有一个 100GB 的数据库，要通过二进制日志完成备份，可能同一个页面要执行十多次的 SQL 语句完成真正做的工作。但是对于真正的增量备份来说，只需要记录当前页或最后的检查点的 LSN，如果大于之前全备时的 LSN，则备份该页，否则不用备份，这大大加快了备份的速度和恢复的时间，同时这也是 xtrabackup 工具增量备份的原理。

此外还需要理解数据库备份的一致性，这种备份要求在备份的时候数据在这一时间点上是一致的。举例来说，在一个网络游戏中有一个玩家购买了道具，这个事务的过程是：先扣除相应的金钱，然后向其装备表中插入道具，确保扣费和得到道具是互相一致的。否则，在恢复时，可能出现金钱被扣除了而道具丢失的问题。

对于 InnoDB 存储引擎来说，因为其支持 MVCC 功能，因此实现一致的备份比较简单。用户可以先开启一个事务，然后导出一组相关的表，最后提交。当然用户的事务隔离级别必须设置为 REPEATABLE READ，这种做法就可以给出一个完美的一致性备份。然而这个方法的前提是需要用户事先设计应用程序。对于上述的购买道具的过程，不可以分为两个事务来完成，如一个完成扣费，一个完成道具的购买。若备份读操作发生在这两者之间，则由于逻辑设计的问题，导致备份出的数据依然不是一致的。

对于 mysqldump 备份工具来说，可以通过添加 --single-transaction 选项获得 InnoDB 存储引擎的一致性备份，原理和之前所说的相同，也就是，这时的备份是在一个执行时间很长的只读事务中完成的，来保证所有导出的表处于同一时间点的数据视图中。另外，对于 InnoDB 存储引擎的备份，务必加上 --single-transaction 的选项。如果不加这个选项，mysqldump 会对每个表分别 LOCK TABLES 并导出，会导致数据之间不一致（因为表导出存在先后顺序，前面表导出完了，后面表导出前可能已经被修改）。

最后，任何时候都需要做好远程异地备份，也就是容灾的防范。只是同一机房的两台服务器的备份是远远不够的。

冷备份

对于 InnoDB 存储引擎 的冷备非常简单，只需要备份 MySQL 数据库的 frm 文件、共享表空间文件、独立表空间文件（*.ibd）、重做日志文件。另外建议定期备份 MySQL 数据库的配置文件 my.cnf，这样有利于恢复的操作。

通常我们会写一个脚本来进行冷备的操作，可能还会对备份完成的数据库进行打包和压缩。关键在于不要遗漏原本需要备份的物理文件，如共享表空间和重做日志文件，少了这些文件可能数据库都无法启动。另一种经常发生的情况是由于磁盘空间已满而导致的备份失败，我们可能习惯性地认为运行脚本的备份是没有问题的，少了检验的机制。

正如前面所说的，在同一台机器上对数据库进行冷备是远远不够的，至少还需要将本地产生的备份存放到一台远程的服务器中，确保不会因为本地数据库的宕机而影响备份文件的使用。

冷备的优点是：

备份简单，只要复制相关文件即可。
备份文件易于在不同操作系统、不同 MySQL 版本上进行恢复。
恢复相当简单，只需要把文件恢复到指定位置即可。
恢复速度快，不需要执行任何 SQL 语句，也不需要重建索引。

冷备的缺点是：

InnoDB 存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他的数据，如 undo 区、插入缓冲等信息。
冷备也不总是可以轻易地跨平台。操作系统、MySQL 的版本、文件大小写敏感和浮点数格式都可能成为问题。

逻辑备份

mysqldump

mysqldump 是 MySQL 官方提供的逻辑备份工具，用于将数据库中的结构和数据导出为 SQL 语句或其它文本格式，便于恢复、迁移或复制库。它支持单库、多库、单表或全库的备份，也能生成 CSV、XML 等格式文件。导出的文件可以在目标服务器上直接重执行，从而重建原始数据库对象和数据。

基本语法：mysqldump [连接选项] [备份选项] 库名 [表名...] > 备份文件.sql

如果要备份单库，可使用：mysqldump -u root -p --single-transaction mydb > mydb_backup.sql

如果要备份多库，可使用：mysqldump -u root -p --databases db1 db2 > multi_backup.sql

如果要备份全库，可使用：mysqldump -u root -p --all-databases > alldb.sql

如果是备份单表并按条件导出，可使用：mysqldump -u root -p mydb orders --where="order_date >= '2025-01-01'" > orders_jan.sql

如果是流式压缩备份，可使用：mysqldump -u root -p mydb | gzip > mydb.sql.gz

如果要恢复数据，可使用：mysql -u root -p mydb < mydb_backup.sql

mysqlpump

mysqlpump 采用队列 + 线程模型，对象级并行：

队列：可通过 --parallel-schemas 创建多个队列，每个队列可绑定一个或多个数据库。
- 线程：在每个队列下可指定线程数（--default-parallelism），对同一队列内的对象（表、视图、存储过程等）并行导出。

导出对象为一系列可执行的 SQL 语句，包括 CREATE、INSERT、GRANT、CREATE TRIGGER/EVENT 等，可跨平台重现数据库结构与数据。

支持对象过滤：通过 --exclude-databases、--include-tables、--exclude-users 等选项灵活选择导出范围。

如果要全库并行备份，可使用：

mysqlpump \
 --default-parallelism=4 \
 --parallel-schemas=2 \
 --add-drop-database \
 --routines --triggers --events \
 --users \
 --compress \
 --exclude-databases=information_schema,performance_schema \
 > full_backup.sql.gz

恢复的操作和 mysqldump 的方法一致。

SELECT … INTO OUTFILE

SELECT … INTO OUTFILE 用于将查询结果直接写入服务器主机上的文件，生成的文件可用于后续的批量导入或数据交换。该语句创建的文件必须在服务器文件系统中不存在，并且需要具备 FILE 权限才可执行。

如果要导出为 CSV 格式，可使用：

SELECT customer_id, firstname, surname
INTO OUTFILE '/var/lib/mysql-files/customers.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

如果要恢复备份，可使用：

LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE mytable
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

或者

mysqlimport \
 --local \              
 --fields-terminated-by=',' \
 --fields-enclosed-by='"' \
 --lines-terminated-by='\r\n' \
 -u 用户 -p 数据库名 /var/lib/mysql-files/customers.csv

二进制日志的备份

二进制日志是 MySQL 实现 point-in-time 恢复和异步复制的关键：

point-in-time 恢复：在发生故障后，可以将完全备份与二进制日志配合，重放指定时间段或位置的变更，实现回滚到任意时间点。
复制：主库将写入的二进制日志发送给从库，从库重放日志以保持数据同步。

默认情况下 MySQL 并不启用二进制日志，必须在 my.cnf 中添加：

1 2	[mysqld] log-bin = mysql-bin

仅启用 log-bin 不够保险，建议在 my.cnf 中也加上：

[mysqld]
log-bin = mysql-bin
sync_binlog = 1
innodb_support_xa = 1

sync_binlog=1：每次提交时强制将二进制日志刷盘，防止故障时丢失已提交的事务。

innodb_support_xa=1：开启 InnoDB 的 XA（分布式事务）支持，保证 binlog 与 InnoDB redo-log 在发生崩溃恢复时的一致性。

在备份二进制日志文件前，可通过 FLUSH LOGS 关闭当前日志文件并新建一个 binlog 文件，便于把之前那些日志一起备份。之后将 mysql-bin.00000* 等文件拷贝到安全位置，与完全备份一起存档。

恢复二进制日志

shell> mysqlbinlog [options] mysql-bin.000001 | mysql -u root -p test 可以将指定日志内容通过管道重放到目标库 test。
如果要同时恢复多文件，可以使用：shell> mysqlbinlog mysql-bin.00000[1-10] | mysql -u root -p test

先导出再 SOURCE 导入：

1
2
3

shell> mysqlbinlog mysql-bin.000001 > /tmp/stmts.sql
shell> mysqlbinlog mysql-bin.000002 >> /tmp/stmts.sql
shell> mysql -u root -p -e "SOURCE /tmp/stmts.sql"

我们也可以指定恢复的起始点：

按照偏移量：mysqlbinlog --start-position=107856 mysql-bin.000001 | mysql -u root -p test

按照时间：

1
2
3

mysqlbinlog --start-datetime="2025-06-14 12:00:00" \
       			--stop-datetime="2025-06-14 18:00:00" \
       			mysql-bin.000001 | mysql -u root -p test

偏移量和时间选项的效果类似，都能实现仅重放二进制日志的部分内容。

热备份

ibbackup

ibbackup 是 InnoDB 存储引擎官方提供的热备工具，可以同时备份 MyISAM 存储引擎和 InnoDB 存储引擎表。对于 InnoDB 存储引擎表，其备份工作原理如下：

记录备份开始时，InnoDB 存储引擎重做日志文件检查点的 LSN。
复制共享表空间文件以及独立表空间文件。
记录复制完表空间文件后，InnoDB 存储引擎重做日志文件检查点的 LSN。
复制在备份时产生的重做日志。

对于事务型数据库，如 Microsoft SQL Server 数据库和 Oracle 数据库，热备的原理大致相同。可以发现，在备份期间不会对数据库本身有任何影响，所做操作只是复制数据库文件，因此任何对数据库的正常操作都是允许的，不会被阻塞。

ibbackup 的优点有：

在线备份，不阻塞任何 SQL 语句。
备份性能好，实质上是复制数据库文件和重做日志文件。
支持压缩备份，通过选项可实现不同级别的压缩。
跨平台支持，可运行于 Linux、Windows 及主流 UNIX 平台。

ibbackup 对 InnoDB 存储引擎表的恢复步骤为：

恢复表空间文件。
应用重做日志文件。

ibbackup 提供了一种高性能的热备方式，是 InnoDB 存储引擎备份的首选方式。不过它是收费软件，并非免费。好在开源社区力量强大，Percona 公司推出了开源、免费的 XtraBackup 热备工具，它不仅实现了 ibbackup 的所有功能，还扩展了真正的增量备份能力。因此，更好的选择是使用 XtraBackup 来完成热备工作。

XtraBackup 文档请参考：https://docs.percona.com/percona-xtrabackup/8.4/

快照备份

MySQL 数据库本身不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一个文件分区中，然后对该分区进行快照操作。支持快照功能的文件系统和设备包括 FreeBSD 的 UFS 文件系统、Solaris 的 ZFS 文件系统、GNU/Linux 的逻辑管理器（Logical Volume Manager，LVM）等。这里以 LVM 为例进行介绍。

LVM 是 LINUX 系统下对磁盘分区进行管理的一种机制。LVM 在硬盘和分区之上建立一个逻辑层，来提高磁盘分区管理的灵活性。管理员可以通过 LVM 系统轻松管理磁盘分区，例如，将若干个磁盘分区连接为一个整体的卷组（Volume Group），形成一个存储池。管理员可以在卷组上随意创建逻辑卷（Logical Volumes），并进一步在逻辑卷上创建文件系统。管理人员通过 LVM 可以方便地调整卷组的大小，并且可以对磁盘存储按照组的方式进行命名、管理和分配。简单地说，用户可以通过 LVM 由物理块设备（如硬盘等）创建物理卷，由一个或多个物理卷创建卷组，最后从卷组中创建任意个逻辑卷（不超过卷组大小），如下图所示。

下图显示了由多块物理磁盘分区组成的逻辑卷 LV0。

Physical disk 0 拥有分区 /dev/hda1, /dev/hda2, /dev/hda3, /dev/hda4；
Physical disk 1 拥有分区 /dev/hdb；
Physical disk 2 拥有分区 /dev/hdd。

这些所有物理分区一起被加入到卷组 VG0 中，VG0 上划分出一个逻辑卷 LV0（图中左侧已分配区域），其余空间则作为 free space（图中右侧虚线区域）可供以后创建更多逻辑卷或扩展现有逻辑卷使用。

LVM 使用了写时复制（Copy-on-write）技术来创建快照。当创建一个快照时，仅复制原始卷中数据的元数据，并不会有数据的物理操作，因此快照的创建过程是非常快的。当快照创建完成，原始卷上有写操作时，快照会跟踪原始卷块的改变，将要改变的数据在改变之前复制到快照预留的空间里，因此这个原理的实现叫做写时复制。而对于快照的读取操作，如果读取的数据块是创建快照后没有修改过的，那么会将读取操作直接定向到原始卷上；如果读取的是已修改过的块，则将读取保存在快照中该块在原始卷上改变之前的数据。因此，采用写时复制机制保证了读取快照时得到的数据与快照创建时一致。

下图显示了 LVM 的快照读取，可见 B 区块被修改了，因此历史数据放入了快照区域。读取快照数据时，A、C、D 块还是从原有卷中读取，而 B 块就需要从快照读取了。

快照在最初创建时总是很小，当数据源卷的数据不断被修改时，这些数据才会放入快照空间，这时快照的大小才会慢慢增大。

为了让快照包含所有必要的数据，只要把 InnoDB 的所有相关文件（共享表空间文件、独立表空间文件、redo log 文件等）都放在同一个逻辑卷里。创建快照时，就会对整个逻辑卷进行一次时间点一致性的镜像。

在创建和使用 LVM 快照备份时，MySQL / InnoDB 不需要停机，应用仍可以继续正常读写。虽然备份过程中还有写操作在往磁盘上提交，但快照机制会保证备份那一刻的数据完整性，不会捕获到部分写入的脏状态。

当你用 LVM 快照恢复文件后，InnoDB 会像意外断电重启那样：自动扫描数据页和 redo log，决定哪些事务需要重做或回滚，最后恢复到一个一致的、可用的数据库状态。因此，用 LVM 快照做备份，恢复后就像给数据库做了一次意外重启，但数据完全一致且不会丢失已提交的事务。

复制

复制（replication）是 MySQL 数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication 的工作原理分为以下 3 个步骤：

1）主服务器把数据更改记录到二进制日志中。

2）从服务器把主服务器的二进制日志复制到自己的中继日志（relay log）中。

3）从服务器重做中继日志中的日志项，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。复制的工作原理如下图所示。

从服务器有 2 个线程，一个是 I/O 线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是 SQL 线程，复制执行中继日志。因此如果查看一个从服务器的状态，应该可以看到类似如下内容：

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
  Id: 1
  User: system user
  Host:
  db: NULL
	Command: Connect
  Time: 6501
 	State: Waiting for master to send event
  Info: NULL
*************************** 2. row ***************************
  Id: 2
  User: system user
  Host:
  db: NULL
	Command: Connect
  Time: 0
 	State: Has read all relay log; waiting for the slave I/O thread to update it
  Info: NULL
*************************** 3. row ***************************
  Id: 206
  User: root
  Host: localhost
  db: NULL
	Command: Query
  Time: 0
 	State: NULL
  Info: SHOW FULL PROCESSLIST
3 rows in set (0.00 sec)

可以看到 ID 为 1 的线程就是 I/O 线程，当前的状态是等待主服务器发送二进制日志。

ID 为 2 的线程是 SQL 线程，负责读取中继日志并执行。目前的状态是已读取所有的中继日志，等待中继日志被 I/O 线程更新。

在 replication 的主服务器上应该可以看到一个线程负责发送二进制日志，类似内容如下：

mysql> SHOW FULL PROCESSLIST\G 
…… 
****************************** 65. row ****************************** 
  Id: 26541 
  User: rep 
  Host: 192.168.190.98:39549 
  db: NULL 
	Command: Binlog Dump 
  Time: 6857 
 	State: Has sent all binlog to slave; waiting for binlog to be updated 
  Info: NULL 
……

之前提到 MySQL 的复制是异步实时的，并非完全的主从同步。若用户要想得知当前的延迟，可以通过命令 SHOW SLAVE STATUS 和 SHOW MASTER STATUS 得知。

示例：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
   Slave_IO_State: Waiting for master to send event
   Master_Host: 192.168.190.10
   Master_User: rep
   Master_Port: 3306
   Connect_Retry: 60
   Master_Log_File: mysql-bin.000007
   Read_Master_Log_Pos: 555176471
   Relay_Log_File: gamedb-relay-bin.000048
   Relay_Log_Pos: 224355889
   Relay_Master_Log_File: mysql-bin.000007
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Replicate_Do_DB:
   Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
   Replicate_Wild_Do_Table:
	 Replicate_Wild_Ignore_Table: mysql.%,DBA.%
   Last_Errno: 0
   Last_Error:
   Skip_Counter: 0
   Exec_Master_Log_Pos: 555176471
   Relay_Log_Space: 224356045
   Until_Condition: None
   Until_Log_File:
   Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
   Master_SSL_Cert:
   Master_SSL_Cipher:
   Master_SSL_Key:
 	 Seconds_Behind_Master: 0
	 Master_SSL_Verify_Server_Cert: No
   Last_IO_Errno: 0
   Last_IO_Error:
   Last_SQL_Errno: 0
   Last_SQL_Error:
1 row in set (0.00 sec)

以上结果中的各个字段的含义如下所示：

变量	说明
Slave_IO_State	当前 I/O 线程的状态，此例为 “Waiting for master to send event”（等待主库发送新的 binlog 事件）
Master_Log_File	当前从库正在读取的主库 binlog 文件名，本例为 mysql-bin.000007
Read_Master_Log_Pos	从库已读取到的主库 binlog 偏移位置（字节）；本例 555176471 表示已读入约 529 MB（555176471/1024²）
Relay_Master_Log_File	从库中继日志对应的主库 binlog 文件名
Relay_Log_File	当前写入的中继日志文件名
Relay_Log_Pos	已执行到中继日志的偏移位置（字节）
Slave_IO_Running	从库 I/O 线程运行状态，YES 表示正常
Slave_SQL_Running	从库 SQL 线程运行状态，YES 表示正常
Exec_Master_Log_Pos	SQL 线程已执行到的主库 binlog 偏移位置；Read_Master_Log_Pos - Exec_Master_Log_Pos 即 I/O 与 SQL 线程之间的“字节延迟”

SHOW MASTER STATUS 可以用来查看主服务器中二进制日志的状态，如：

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
 File: mysql-bin.000007
 Position: 606181078
 Binlog_Do_DB:
 Binlog_Ignore_DB:
1 row in set (0.01 sec)

可以看到，当前二进制日志记录了偏移量 606181078 的位置，该值减去这一时间点时从服务器上的 Read_Master_Log_Pos，就可以得知 I/O 线程的延时。

，用户不应仅监控从服务器上 I/O 线程和 SQL 线程是否运行正常，同时也应监控从服务器与主服务器之间的延迟，确保从服务器上的数据尽可能接近主服务器上的状态。

快照 + 复制的备份架构

复制可以用来作为备份，但功能不仅限于备份，其主要功能如下：

数据分布。由于 MySQL 数据库提供的复制并不需要很大的带宽要求，因此可以在不同的数据中心之间实现数据的复制。
读取的负载平衡。通过建立多个从服务器，可将读取平均地分布到这些从服务器中，并且减少了主服务器的压力。一般通过 DNS 的 Round-Robin 和 Linux 的 LVS 功能都可以实现负载平衡。
数据库备份。复制对备份很有帮助，但是从服务器不是备份，不能完全代替备份。
高可用性和故障转移。通过复制建立的从服务器有助于故障转移，减少故障的停机时间和恢复时间。

可见，只是用复制来进行备份是远远不够的。也就是说，仅靠主从复制无法完全防护数据丢失或误操作，需要结合从库的存储快照和二进制日志重放来实现对任意时间点的恢复与一致性保障。

假设当前应用采用了主从的复制架构，从服务器作为备份。此时，一个开发人员执行了误操作，如 DROP DATABASE 或 DROP TABLE，这时从服务器也跟着运行了。用户怎样从从服务器进行恢复呢？

因此，一个比较好的方法是通过对从服务器上的数据库所在分区做快照，以此来避免误操作对复制造成影响。当发生主服务器上的误操作时，只需要将从服务器上的快照进行恢复，然后再根据二进制日志进行 point-in-time 的恢复即可。因此快照 + 复制的备份架构如下图所示。

还有一些其他的方法来调整复制，比如采用延时复制，即间歇性地开启从服务器上的同步，保证大约一小时的延时，可对抗误操作。这的确也是一个方法，只是数据库在高峰和非高峰期间每小时产生的二进制日志量是不同的，用户很难精确地控制。另外，这种方法也不能完全起到对误操作的防范作用。

此外，建议在从服务器上启用 read-only 选项，这样能保证从服务器上的数据仅与主服务器进行同步，避免其他线程修改数据。如：

1 2	[mysqld] read-only

在启用 read-only 选项后，如果操作从服务器的用户没有 SUPER 权限，则对从服务器进行任何的修改操作会抛出一个错误，如：

1 2	mysql> INSERT INTO z SELECT 2; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

分库分表

发表于 2024-11-26 更新于 2025-06-23 分类于 MySQL

分库分表主要是为了解决单库单表在海量数据和高并发场景下的性能瓶颈：当数据量达到千万级甚至亿级时，单表查询效率和索引更新速度都会明显下降，备份恢复也变得极其缓慢；而在高并发写入时，单实例的 CPU、内存、I/O 资源易成为瓶颈，锁竞争也会导致事务阻塞。通过将大表拆成多张子表、或将数据分散到多个数据库实例，不仅能降低单表、单库的数据规模，提升读写性能，还能分散并发压力、减少锁冲突，从而显著提高系统的可用性和扩展能力。

垂直分库通过将不同业务模块或功能独立到各自的数据库，既降低了数据之间的耦合度，又提升了整体可用性；水平分库则将同一业务的数据按一定策略分散到多台实例，分担了单库的 CPU、I/O 和网络压力；垂直分表是把表中不同类型的数据拆分到多张表中，进一步削弱耦合；水平分表则把一张大表按范围或哈希划分成多张子表，从而减少索引深度并加快查询。

若仍使用单库单表模式，会因热点数据频繁访问导致缓冲区不足、磁盘 I/O 激增，又因大量请求而引发网络带宽瓶颈，此外 SQL 处理也会占用过多 CPU 资源，最终形成性能瓶颈。分库分表的核心思想就是通过分散存储，将单一数据库或表的数据规模控制在可承载范围内，进而显著缓解 I/O、CPU 和网络方面的压力。

拆分策略

垂直拆分

垂直拆分主要包括两种形式：垂直分库和垂直分表。其中，垂直分库是以表为单位、根据业务模块将不同的表拆分到各自独立的数据库实例中，使得每个库只包含某一类业务的表，从而降低数据耦合度并提升可用性；而垂直分表则是以字段为依据，将同一张宽表中访问频次不同或性质相异的字段拆分到多张子表，通过主键—外键关联保持数据完整性，以减小单表宽度、优化查询性能。在实践中，垂直分库常用于按业务边界隔离数据，而垂直分表则侧重于对单表内部结构的精细化拆分，两者结合能够更好地满足系统的可扩展性与维护性需求。

水平拆分

水平拆分是一种以行为单位对数据进行切割的策略：水平分库是将同一表的若干行数据按照某种分片键（如用户ID范围或哈希）分散到多个数据库实例，每个实例存储一部分数据，从而分担 CPU、I/O 和网络负载；而水平分表则是在同一数据库实例内部将表数据行按相同策略分布到多张结构相同的子表中，以减少单表的索引层数，提升查询性能和并发处理能力。无论是分库还是分表，都可通过范围切片、哈希切片或列表切片等算法来保证数据的均衡分布，进一步降低锁竞争与热点访问问题。

在业界，这种做法通常也被称为分片（sharding），每个分片可以是独立的数据存储节点。此外，在云服务环境下，水平分区是实现水平扩展（scale-out）的核心方式，通过动态增加分片或实例来处理更大规模的请求和数据量；它还能与读写分离、缓存策略等机制结合，共同构建高可用、高性能的数据库架构。

水平分表的路由方式

要实现水平分表，必须设计一个路由策略，根据分片键（Sharding Key）决定每条记录应写入哪张子表。理想的分片键应该具备以下特征：

一是高区分度，使数据均匀分布，避免某些表过热或过载；

二是查询频率高，优先选取常在 WHERE 条件中出现的字段，以确保绝大多数查询能直接定位到目标表，提升查询效率；

三是写入频率高，将频繁更新或插入的字段作为分片键，有助于将写负载均衡地分散到各个子表，从而减少单表写入瓶颈。

范围路由、哈希路由和配置路由是水平分表中最常用的三种策略。

范围路由通过将分片键按值的连续区间映射到不同的表，例如按时间戳或订单号切分，优势在于实现简单且可以平滑扩容，但容易出现部分分片数据过多的倾斜问题。

哈希路由则对分片键取哈希值并取模分表，可以较均匀地分散数据，避免单表热点，但执行范围查询时需要访问多个分片，查询性能有所下降。

配置路由则通过维护一张映射表，显式指定每个分片键对应的目标表，灵活性最高，能够应对分片键分布不均或规则多变的场景，但需要额外的配置表来管理路由映射，运维成本和复杂度也相对更高。

以上的路由方式可以根据场景的不同组合使用，比如：首先根据业务维度（如订单月份）将数据划分到不同的分组，每个分组再映射到若干物理节点，形成按月分组的“范围切分”策略。然后在每个分组内部，对分片键（如 orderId）计算哈希并取模，将其均匀分配到该组的各节点上，兼顾了哈希路由的负载均衡效果。

不停机扩容

不停机扩容通常分为三个阶段，以确保旧库和新库在业务不中断的情况下平滑过渡。

第一阶段是在线双写、老库查询：在此阶段，先在新环境中建立与旧库完全相同的库表结构，然后将所有新增写操作同时写入旧库和新库，业务查询仍然走旧库；接着，通过专用迁移程序对旧库中的历史数据进行全量迁移，并通过定时校验任务对比新旧库的数据一致性，实时补齐任何差异。

第二阶段是完成同步并切换读流量：当确认历史数据已经成功迁移且新库中的写入与旧库始终保持一致后，就可以将所有读操作从旧库切换到新库，从而开始验证新库的查询性能和稳定性。

第三阶段是停止旧库写入并下线：在确认旧库不再接收任何新写后，需要等待一段时间以清空剩余连接与缓冲，然后可以安全地关闭或拆除旧库，实现真正的无缝下线。通过这种三阶段流程，在整个扩容过程中既保证了业务的连续性，也维持了数据的一致性和可用性。

尽管在线双写与写时复制在延迟复制、零停机思想上有交集，但它们在层级、触发条件、技术依赖和一致性模型上都有本质区别。在线双写更适合数据库扩容与数据迁移场景；写时复制则是操作系统与存储层面优化内存和文件复制的通用技术。两者均为提升可用性和性能的重要手段，但并非同一机制。

分库分表的问题

在分库后，单机事务的强一致性优势不再适用，必须引入分布式事务（如两阶段提交或 TCC）来保证跨库的事务完整性；同时，由于数据库实例被拆分，原生的跨库 JOIN 无法直接执行，只能在业务代码中先查询一个库的数据再查询另一个库并进行合并，或通过冗余字段将常用关联信息（如名称）复制到当前表，减少关联请求；另一种思路是利用 binlog 同步等机制将需要跨库关联的数据异构到 Elasticsearch 等专用存储，再由 ES 实现联合查询。

在分表场景下，跨分片的聚合计算（如 COUNT、ORDER BY、GROUP BY）只能通过业务端或中间件对各分表结果进行汇总、排序与分页才能实现；同时，需要在切分前对数据迁移、容量规划及未来扩容的可行性进行充分评估，以避免二次拆分带来的复杂度和风险。更重要的是，表被水平切分后已不能再依赖数据库自身的自增主键机制来保证全局唯一性，常见的替代方案包括：设置不同的自增步长与初始值（例如三张表分别以步长 3、初始值 1、2、3 生成 ID），从而避免冲突；使用 UUID 生成全局唯一主键，但要警惕随机主键可能导致 B-Tree 页分裂、写放大和性能下降；或者采用分布式 ID 生成算法（如 Twitter Snowflake），通过时间戳、节点 ID 及序列号的组合方式高效生成可排序的全局唯一 ID，兼顾性能与一致性。

更多内容可参考：

MapReduce

发表于 2024-11-22 更新于 2025-06-28 分类于数据库系统，分布式系统

编程模型 (Programming Model)

输入与输出 (Input and Output):

接收一组输入键/值对 (input key/value pairs)。
生成一组输出键/值对 (output key/value pairs)。

用户自定义函数 (User-Defined Functions):

映射函数 (Map Function):
- 由用户编写。
- 处理每个输入键/值对。
- 生成一组中间键/值对 (intermediate key/value pairs)。
归约函数 (Reduce Function):
- 同样由用户编写。
- 接收一个中间键 I 及其关联的值集合 (set of values)。
- 合并这些值以产生一个更小集合 (smaller set) 的输出值，通常为零个或一个值。

中间数据处理 (Intermediate Data Handling):

MapReduce 库 (MapReduce library) 将中间值按其键 (I) 分组，并将它们发送给归约函数。
中间值通过迭代器 (iterator) 提供给归约函数，从而能够高效处理因数据量过大而无法全部放入内存的数据集。

容错性与可扩展性 (Fault Tolerance and Scalability):

通过将任务分解成更小的独立计算单元，MapReduce 确保了即使在大型分布式环境中也能实现可扩展性和容错性。

实现 (Implementation)

数据分割与任务分配 (Data Splitting and Task Assignment):

输入数据划分: MapReduce 库自动将输入文件分割成 M 个片段（通常每个片段大小为 16MB 到 64MB，可由用户控制）。
启动程序实例: 在集群中启动多个程序副本。
角色分配: 其中一个程序实例被指定为主节点 (master)，其余的作为工作节点 (workers)。

任务调度 (Task Scheduling):

主节点的职责: 主节点负责管理 M 个 map 任务和 R 个 reduce 任务。
任务分配: 主节点将空闲的工作节点分配给 map 任务或 reduce 任务。

Map 阶段 (Map Phase):

读取数据: 被分配 map 任务的工作节点读取对应的输入片段。
处理数据: 解析出键/值对，并将其传递给用户定义的 Map 函数。
生成中间结果: Map 函数产生的中间键/值对会存储在本地磁盘中。

中间数据处理 (Intermediate Data Processing):

写入本地磁盘: **缓存的中间结果会定期写入本地磁盘，并根据分区函数划分为 R 个区域 (partitioned into R regions)。
通知主节点: 工作节点将这些中间数据的位置告知主节点，主节点负责将这些信息传递给 reduce 工作节点。

Reduce 阶段准备 (Reduce Phase Preparation):

读取中间数据: reduce 工作节点收到主节点的通知后，通过远程过程调用 (RPC - Remote Procedure Call) 从 map 工作节点的本地磁盘读取中间数据。
排序数据: reduce 工作节点将所有中间数据按键排序，以确保相同的键聚集在一起。如果数据量过大，无法全部加载到内存，会采用外部排序 (external sort)。

Reduce 阶段 (Reduce Phase):

执行 Reduce 函数: reduce 工作节点遍历排序后的中间数据，对于每个唯一的中间键，将键和对应的值列表传递给用户定义的 Reduce 函数。
生成最终输出: Reduce 函数的输出被追加到该 reduce 分区的最终输出文件中。

任务完成与结果返回 (Task Completion and Result Retrieval):

任务监控: 当所有的 map 和 reduce 任务都完成后，主节点会唤醒用户程序。
返回结果: 此时，用户程序中的 MapReduce 调用返回，用户可以获取 R 个输出文件（每个 reduce 任务对应一个输出文件）。

额外说明 (Additional Notes):

数据处理链: 通常用户不需要将这 R 个输出文件合并成一个文件，因为这些文件可以直接作为下一个 MapReduce 调用的输入，或者被能够处理多文件输入的分布式应用程序使用。
流程图参考: 上图👆用于展示 MapReduce 操作的整体流程（对应上述 7 个步骤）。

主节点数据结构 (Master Data Structure)

任务状态跟踪 (Task State Tracking):

对于每个 map 和 reduce 任务，主节点存储：
- 状态:
  - idle (空闲): 任务尚未分配。
  - in-progress (执行中): 任务正在被执行。
  - completed (已完成): 任务执行完毕。
- 工作节点标识 (Worker Identity): 处理该任务的工作机器（针对非空闲任务）。

中间数据管理 (Intermediate Data Management):

主节点充当将中间数据从 map 任务传递到 reduce 任务的管道 (conduit)。
对于每个已完成的 map 任务：
- 它记录所生成的 R 个中间文件区域的位置和大小。
- 这些数据对于 reduce 任务从相应的 map 工作节点获取中间结果至关重要。

动态更新 (Dynamic Updates):

随着 map 任务完成，主节点持续更新其记录的中间文件位置和大小。
这些更新会增量式地推送给当前正在执行中的 reduce 工作节点。

容错机制 (Fault Tolerance)

工作节点故障 (Worker Failure)

故障检测 (Failure Detection):
- 主节点定期向每个工作节点发送 ping。
- 如果工作节点在特定时间窗口内未响应，主节点将其标记为故障。
任务重新调度 (Task Rescheduling):
- Map 任务 (Map Tasks):
  - 已完成的 Map 任务 (Completed Map Tasks):
    - 如果故障工作节点已完成 map 任务，其输出将变得不可访问（存储在故障机器的本地磁盘上）。
    - 这些任务被重置为空闲状态 (idle state) 并在其他工作节点上重新执行。
  - 执行中的 Map 任务 (In-Progress Map Tasks):
    - 类似地，执行中的任务被标记为空闲并重新分配给可用的工作节点。
- Reduce 任务 (Reduce Tasks):
  - 已完成的 Reduce 任务 (Completed Reduce Tasks):
    - 这些任务不需要重新执行，因为它们的输出存储在全局文件系统 (global file system) 中，即使发生故障也仍然可访问。
  - 执行中的 Reduce 任务 (In-Progress Reduce Tasks):
    - 如果某些 reduce 工作节点尚未读取中间数据，它们会从新的（重新执行的 map 任务的）结果中读取数据。
- 数据协调 (Data Coordination):
  - 当 map 任务在新的工作节点上重新执行时：
    - 通知 (Notification): 所有 reduce 工作节点会被告知该重新执行。
    - 数据重定向 (Data Redirection): 尚未从故障工作节点获取中间数据的 reduce 工作节点将改为从新的工作节点获取数据。

主节点故障 (Master Failure)

可以很方便地让主节点定期将上述主节点数据结构写入检查点 (checkpoints)。如果主节点任务终止，可以从最后一个检查点状态启动一个新的副本。
然而，考虑到只有一个主节点，其故障的可能性很低；因此我们当前的实现在主节点故障时会中止 MapReduce 计算。 客户端可以检测到此情况，并在需要时重试 MapReduce 操作。

数据本地化 (Locality)

存储设计: 数据存储在 Google 文件系统 (GFS - Google File System) 中。GFS 将每个文件分割为 64 MB 的块 (blocks)，并在不同的机器上保存多个副本（通常是 3 个）。
任务调度优先级:
- 优先本地化调度: 主节点优先将 map 任务分配给包含对应数据块副本的同一台机器上的工作节点。
- 次优调度: 如果本地调度不可行（例如，拥有数据块副本的工作节点繁忙），主节点将任务分配给靠近副本的机器，例如同一机架 (rack) 或数据中心 (data center) 内的机器。
实际效果: 在运行大型 MapReduce 操作时，大部分输入数据会从本地磁盘读取。因为数据本地化，减少了跨网络传输的数据量，从而节省网络带宽。

任务粒度 (Task Granularity)

Map 和 Reduce 阶段的划分
- 任务数量 (M 和 R): Map 阶段被划分为 M 个任务。Reduce 阶段被划分为 R 个任务。
- 划分原则: 理想情况下，M 和 R 的数量应该远大于工作节点的数量（即机器的数量）。
多任务划分的好处
- 动态负载均衡: 每个工作节点可执行多个任务，这样可以动态调整任务分配，避免某些节点过载或闲置。
- 故障恢复加速: 如果某个工作节点失败，其已完成的多个任务可以分散到其他节点重新执行，恢复速度更快。
任务划分的实际限制
- 调度开销: 主节点需要进行 O(M + R) 次调度决策，且需要存储 O(M × R) 的状态信息。虽然每对 map/reduce 任务对仅占用约 1 字节内存，但过多任务会增加内存需求和调度复杂性。
- 输出文件限制: R 的大小往往受到用户需求限制，因为每个 reduce 任务会生成一个独立的输出文件。输出文件过多会导致文件管理复杂。
实际任务大小选择
- Map 阶段: 每个 map 任务通常处理 16 MB 到 64 MB 的输入数据。这样的任务大小可以充分利用数据本地化优化（即尽量从本地磁盘读取数据）。
- Reduce 阶段: R 通常是工作节点数量的几倍，以充分利用并行能力。在一个典型的大规模 MapReduce 计算中：
  - M = 200,000（Map 阶段任务数）。
  - R = 5,000（Reduce 阶段任务数）。
  - 工作节点 = 2,000（机器数量）。

备份任务 (Backup Tasks)

什么是拖后腿的任务（Straggler Tasks）？
- 定义: 拖后腿任务指的是 MapReduce 作业中运行速度远慢于其他任务的任务（map 或 reduce），从而延迟整个作业的完成。
解决方法:
- 备份任务机制: 当 MapReduce 计算接近完成时，主节点会为未完成的任务安排备份执行 (Backup Executions)。同一任务的多个副本在不同的工作节点上同时运行。只要其中一个副本完成，任务即被标记为完成。
- 资源开销: 调整后的机制只增加少量（通常是几个百分点）的计算资源使用。通过备份执行，能够显著缩短总执行时间。

分区函数 (Partitioning Function)

Reduce 任务与分区
- 用户通过设置 R 来指定需要的 reduce 任务数或输出文件数。
- 数据在这些 reduce 任务之间分区，分区方式取决于分区函数。
默认分区方式
- 默认使用哈希函数。
- 分区规则: hash(key) mod R。
- 优势: 通常能实现较为均衡的分区（即数据均匀分布到不同 reduce 任务中）。
自定义分区方式
- 有时默认的哈希分区不满足实际需求，需要根据特定逻辑对数据进行分区。例如：数据的键是 URL，用户希望所有来自同一主机 (host) 的条目存储在同一个输出文件中。
- 解决方案: 用户可以定义自己的分区函数，例如：hash(Hostname(urlkey)) mod R：根据 URL 的主机名 (hostname) 分区。这样，来自同一主机的所有条目会被分配到相同的 reduce 任务中。

排序保证 (Ordering Guarantees)

排序保证
- 在 MapReduce 的每个分区内，中间的键/值对（key/value pairs）会按照键的递增顺序 进行处理。
- 目标: 确保每个分区的输出文件是有序的。
排序的作用
- 生成有序输出文件: 每个 reduce 任务生成的输出文件是按键排序的，直接支持有序数据的存储。
- 支持高效随机访问: 有序数据便于通过键值实现高效的随机访问。
- 用户便利: 用户使用这些输出文件时，通常不需要额外排序。

合并函数 (Combiner Function)

问题背景
- 在某些情况下，中间键重复率较高，每个 map 任务可能会生成大量重复的中间键记录。示例: 在单词计数任务中（例如 <the, 1>），常见单词（如 “the”）会频繁出现。
- 结果: 这些重复记录需要通过网络传输到同一个 reduce 任务，增加了网络负载。
Combiner 函数的解决方案
- 定义: Combiner 是一个可选的、局部的聚合函数，用于在 map 任务所在机器上对中间数据进行部分合并。
- 工作原理:
  - 执行位置: Combiner 在 map 任务的机器上运行。
  - 功能: 对重复键的中间结果进行局部汇总，减少需要传输的数据量。
  - 例如: 将 <the, 1>、<the, 1>、<the, 1> 合并为 <the, 3>。
Combiner 和 Reduce 的区别
- 相同点: 通常，Combiner 的代码与 Reduce 函数的代码相同。都用于对数据进行聚合处理。
- 不同点:
  - Combiner: 输出的是中间结果，数据会继续传递给 Reduce 任务。
  - Reduce: 输出的是最终结果，数据写入最终的输出文件。
优化效果
- 减少网络传输量: 通过提前合并数据，Combiner 显著减少了从 map 任务到 reduce 任务的数据量。例如，不传输 1000 条 <the, 1>，而是只传输 1 条 <the, 1000>。
- 提升性能: 对于重复率高的任务，Combiner 能显著加快 MapReduce 操作的速度。

输入与输出类型 (Input and Output Types)

输入数据格式的支持
- 预定义格式:
  - 文本模式: 每行数据被视为一个键/值对。
    - 键：文件中该行的偏移量。
    - 值：该行的内容。
  - 排序键/值对模式 (sorted key/value mode): 存储的键/值对按键排序，便于按范围处理。
- 自动分割范围: 每种输入格式都有分割机制，可将输入数据划分为适合 map 任务处理的范围。例如，文本模式会确保分割发生在行边界，而不是行中间，保证数据的完整性。
- 用户自定义格式: 用户可以通过实现简单的读取接口 (reader interface)，支持新的输入类型。
  - 非文件输入: 数据可以来自其他来源，如数据库或内存中的数据结构，而不一定是文件。
输出数据格式的支持
- 类似输入格式，MapReduce 也支持多种输出格式：
  - 预定义格式: 提供了一些常用的输出格式。
  - 自定义格式: 用户可以通过实现新的接口定义输出数据格式。

跳过错误记录 (Skipping Bad Records)

问题背景
- 用户代码缺陷: Map 或 Reduce 函数中可能存在错误（如某些记录引发崩溃）。
- 确定性崩溃: 对特定记录，每次处理都会发生崩溃。
- 问题影响: 这类错误可能阻止整个 MapReduce 操作完成。
- 无法修复的情况: 错误可能在第三方库中，用户无法访问源代码。
MapReduce 提供的解决方案
- 跳过问题记录: MapReduce 允许系统检测引发崩溃的记录，并跳过这些记录以继续操作。
- 实现机制:
  - 信号处理: 每个工作节点安装信号处理器，捕获段错误 (segmentation violations) 和总线错误 (bus errors)。
  - 记录错误序号: 在调用用户的 Map 或 Reduce 函数之前，系统将参数的序列号 (sequence number) 存储在全局变量中。
  - 发送错误报告: 如果用户代码触发错误，信号处理器会发送一个 “最后的喘息” (last gasp) UDP 数据包，包含引发错误的记录序号，通知主节点。
  - 主节点决策: 如果一条记录多次导致失败，主节点指示在下次重试该任务时跳过 (skip) 这条记录。

本地执行 (Local Execution)

分布式调试的挑战
- 复杂性: Map 和 Reduce 函数的实际计算是在分布式系统上完成，涉及数千台机器。主节点动态分配任务，调试难以直接定位问题。
- 常见问题: 分布式环境下的日志、任务状态和数据流使得问题排查更加困难。
本地执行模式的设计
- 功能: MapReduce 提供了一种本地执行的替代实现，在单台机器上顺序执行整个 MapReduce 操作。
- 特点: 所有任务按顺序运行，无需分布式调度。用户可以限制计算范围，仅调试特定的 map 任务。

计数器 (Counter)

计数器用于跟踪 MapReduce 操作期间特定事件的发生次数，例如：
- 用户定义的自定义事件（例如，单词计数、检测特定模式）。
- 系统定义的指标，如处理的输入/输出键值对数量。

计数器工作原理 (How Counters Work)

传播到主节点 (Propagation to the Master): 来自各个工作节点的计数器值通过 ping 响应 发送到主节点。
聚合 (Aggregation):
- 主节点聚合所有已完成任务的计数器值。
- 它通过忽略重复的任务执行（例如，由于重新执行或备份任务）来确保没有重复计数。

监控与报告 (Monitoring and Reporting)

实时监控 (Real-Time Monitoring): 当前的计数器值显示在主节点状态页面 上，允许用户观察计算的进度。
最终报告 (Final Reporting): 当 MapReduce 作业完成时，聚合后的计数器值返回给用户程序。

问题 (Questions)

假设 M=10 且 R=20，映射器 (mappers) 产生的文件总数是多少？

总文件数 = M × R = 10 × 20 = 200

为什么 MapReduce 将 Reduce 的输出存储在 Google 文件系统 (GFS) 中？

高可用性 (High Availability): GFS 通过在多个机器上复制数据 (replicating data) 提供容错能力。这确保了即使一台机器故障，输出也不会丢失。

可扩展性 (Scalability): GFS 专为处理大规模数据存储而设计，适用于 MapReduce 作业产生的大量输出。

拖后腿任务 (straggler) 的目的是什么？

“拖后腿任务 (Straggler)” 指的是运行缓慢的任务，通常是 map 或 reduce 任务，它们会显著延迟 MapReduce 作业的完成。

解决方法:

备份执行 (Backup Execution): 主节点在其它可用工作节点上为拖后腿任务安排备份执行。

判断对错：可以在没有模式 (schema) 的情况下，对 CSV 数据文件使用 SQL++。

正确 (True): SQL++ 可以操作半结构化数据，包括 CSV 文件，而不需要预定义的模式。

在 SQL++ 中，pivot 和 unpivot 有什么区别？

Pivot (透视):

目的: 将行 (rows) 转换为属性 (attributes) / 列 (columns)。

示例:

输入: [ { "symbol": "amzn", "price": 1900 }, { "symbol": "goog", "price": 1120 }, { "symbol": "fb", "price": 180 } ]

查询: PIVOT sp.price AT sp.symbol FROM today_stock_prices sp;

输出: { "amzn": 1900, "goog": 1120, "fb": 180 }

Unpivot (逆透视):

目的: 将属性 (attributes) / 列 (columns) 转换为行 (rows)。

示例:

输入: { "date": "4/1/2019", "amzn": 1900, "goog": 1120, "fb": 180 }

查询: UNPIVOT c AS price AT sym FROM closing_prices c WHERE sym != 'date';

输出: [ { "date": "4/1/2019", "symbol": "amzn", "price": 1900 }, { "date": "4/1/2019", "symbol": "goog", "price": 1120 }, { "date": "4/1/2019", "symbol": "fb", "price": 180 } ]

使用 BG ，可以通过其 SoAR (Satisfaction of Agreement Ratio) 来总结数据存储的性能。计算数据存储 SoAR 的 BG 输入是什么？

1. SLA 规范 (SLA Specifications)

服务等级协议 (SLA) 定义了计算 SoAR 的条件。SLA 包括：

α: 必须观察到响应时间小于或等于 β 的请求百分比（例如，95%）。

β: 最大可接受响应时间（例如，100 毫秒）。

τ: 观察到不可预测（过时或不一致）数据的请求的最大允许百分比（例如，0.01%）。

Δ: SLA 必须被满足的持续时间（例如，10 分钟）。

2. 数据库配置 (Database Configuration)

关于被测数据存储的详细信息：

逻辑模式 (Logical Schema): 数据存储使用的数据模型（例如，关系模式、NoSQL 的类 JSON 模式）。

物理设置 (Physical Setup): 硬件配置，包括：

节点数量。

存储和内存资源。

网络能力。

数据量大小 (Population Size):

M: 数据库中的成员数量。

ϕ: 每个成员的关注者/朋友数量。

ρ: 每个成员的资源数量。

3. 工作负载参数 (Workload Parameters)

工作负载指定了 BG 将模拟的操作的性质和强度：

操作混合比例 (Mix of Actions):

社交网络操作的类型（例如，查看个人资料、列出朋友、查看好友请求）。

每种操作类型的百分比（读密集型、写密集型或混合工作负载）。

思考时间 (ϵ - Think Time): 单个线程执行连续操作之间的延迟。

到达间隔时间 (ψ - Inter-Arrival Time): 新用户会话之间的延迟。

4. 环境参数 (Environmental Parameters)

关于 BG 如何生成和管理工作负载的详细信息：

BGClients 数量 (N): 负责生成请求的实例数。

线程数量 (T): 并发级别（每个 BGClient 的线程数）。

D-Zipfian 分布参数 (θ): 定义访问模式（例如，热门数据与冷门数据的访问频率）。

考虑键值对优先级 (priority) 的以下二进制表示：00101001。其精度为 4 的 CAMP 舍入 (CAMP rounding) 结果是什么？

00101000

什么是惊群效应 (thundering herd)？IQ 框架如何防止它导致持久化数据存储成为瓶颈？

惊群效应问题 (Thundering Herd Problem):

当一个键值对在键值存储 (KVS) 中未找到（发生 KVS 未命中 (KVS miss)）时，多个读取会话可能会同时查询关系数据库管理系统 (RDBMS) 以获取该值。

这可能在高并发情况下使 RDBMS 过载并导致性能下降。

IQ 框架的解决方案:

当第一个读取会话遇到 KVS 未命中时，它会为该键请求一个 I 租约 (I lease)。

一旦 I 租约被授予，KVS 会阻止其他读取会话为同一个键查询 RDBMS。

所有其他读取会话必须 “回退 (back off)” 并等待持有 I 租约的会话将值更新到 KVS 中。

(补充解释) 惊群效应发生在特定键经历大量读写活动时。

写入操作重复地使缓存失效 (invalidate the cache)。

所有读取操作都被迫查询数据库。

I 租约解决了这个问题：

对特定键的第一次读取被授予 I 租约。

所有其他读取观察到未命中并回退。

持有 I 租约的读取查询 RDBMS，计算缺失的值，并将该值填充 (populate) 到缓存中。

所有其他读取随后会观察到缓存命中 (cache hit)。

参考: https://static.googleusercontent.com/media/research.google.com/zh-CN//archive/mapreduce-osdi04.pdf

主从复制

发表于 2024-11-20 更新于 2025-06-23 分类于 MySQL

在基于 Binlog 的异步复制模式中，主库将所有 DDL 与 DML 操作写入二进制日志，并在本地完成事务提交后立即返回，无需等待从库确认，从而实现松散耦合的复制流程。随后，从库的 IO 线程会与主库保持长连接，不断读取新生成的 binlog 事件并将它们写入本地的 Relay Log。接着，从库的 SQL 线程会顺序读取 Relay Log 中的事件，并在从库上重做这些 DDL 与 DML，以达到数据同步的目的。

MySQL 支持一主多从复制拓扑，且从库本身也可以配置为其他从库的源库，实现链式复制。这种异步复制模式允许在主库出现故障时，通过提升任一从库为新的主库来快速切换并恢复服务，极大增强了系统的高可用性。同时，通过将写操作集中到主库、将读操作分散到从库，也减轻了主库的负载，并可在从库上执行备份任务，避免对主库性能产生影响。由于主库无需等待从库完成写入，可获得更高的写入吞吐量，但也可能引入复制延迟问题，需要结合监控指标加以管理。为进一步降低从库的应用延迟，可使用并行复制功能，让 SQL 线程并发地执行多个复制通道中的事件。总体而言，基于 Binlog 的异步复制以其配置简单、可扩展性强和高可用性好等特点，成为 MySQL 最常用的主从复制方案。

在主从复制中，Relay Log 起到四大关键作用：

首先，它充当从库的缓冲区——IO 线程不断地将主库的二进制日志拉取并写入本地的 Relay Log，即使 SQL 线程处理变更较慢，也不会影响向主库的读取，从而有效降低延迟；

其次，Relay Log 支持异步处理——IO 线程与 SQL 线程分离，前者专注拉取日志，后者专注重放语句，两者互不阻塞，大幅提升同步的并发能力；

第三，它增强了数据可靠性——日志落盘后即便网络波动或从库重启，SQL 线程也能从已持久化的 Relay Log 继续执行，无需重新拉取；

最后，在链式复制场景中，从库的 Relay Log 同样可以对下游从库提供日志源，成为二进制日志传递链上的一环。这样，Relay Log 不仅保证了数据的连续、高效复制，还提升了系统的容错与可扩展性。

同步延迟的原因

主从同步延迟通常是因为从库的复制流程存在瓶颈：主库可以同时接收大量并发写请求，但从库只有一个 IO 线程负责拉取 binlog、一个 SQL 线程负责执行，如果某条 SQL 在从库上执行耗时较长或因锁等待而阻塞，就会造成 binlog 在从库端堆积，进而引起读写不一致。为缓解这一问题，一方面可以在写操作后立即把紧接着的读操作路由到主库，以确保及时读取最新数据；另一方面也可在读从库失败时自动再去主库重试二次读取，这种方式改动最小，但会增加主库的读负载；此外，还可以将关键业务（如注册、登录）的所有读写都定向主库处理，而对一致性要求较低的查询（如用户资料展示）继续采用读写分离，从而在保证关键路径数据正确性的同时，将大部分查询压力分散到从库。

同步模式

异步复制

异步复制（Asynchronous Replication）是 MySQL 最基础也是最常用的复制方式，其中主库在本地提交事务并写入二进制日志后即可立即返回客户端，从库的 IO 线程随后异步地拉取这些 binlog 并将其写入 Relay Log，最后由 SQL 线程在从库上执行重放，从而完成数据同步。由于主库无需等待从库确认即完成提交，异步复制能够获得最高的写入吞吐量，但会产生一定的复制延迟，若主库故障，尚未同步的事务可能丢失。

半同步复制

半同步复制（Semisynchronous Replication）介于异步与全同步之间，当主库提交一个事务时，会阻塞等待至少一个从库的确认（即从库已将该事务事件接收并写入 Relay Log，但不必执行完毕）后才向客户端返回，从而保证提交成功的事务至少存在于主库和一个从库上。相比纯异步复制，半同步复制在保证可用性的同时，显著提升了数据安全性；但因需等待确认，写延迟会略高于异步模式，尤其在网络或从库性能较差时更为明显。

全同步复制（组复制）

全同步复制通常由 MySQL Group Replication 或 NDB Cluster 实现，要求主库在提交时，所有或一定多数的副本必须完成提交确认后才继续执行，从而实现真正的强同步。该模式在保证各节点数据实时一致性方面最强，但也带来最高的写延迟和最复杂的部署需求，通常用于对一致性和故障切换要求极高的场景。

百万千万级大表如何添加字段？

在面对上千万行的大表时，直接执行 ALTER TABLE … ADD COLUMN 往往会长时间锁表，影响线上业务；常见的无停机扩容方案包括：

一是在新库/新表中先创建完整的表结构并添加字段，然后通过分批次或全量迁移程序将旧表数据复制过去，最后再原子化地用新表替换旧表（此法简洁但需要额外迁移逻辑）；

二是借助 Percona 的 pt-online-schema-change 或 GitHub 的 gh-ost 等在线变更工具，它们会在后台创建影子表、实时捕获并同步写入，通过触发器或 binlog 流将增量写入合并到新表，待数据迁移完成后再用重命名操作切换，无需停机且复制过程中表仍可读写；

三是针对极高并发的热表，可先在从库执行字段添加操作，待从库完成同步并切换读写角色后，再依次在其他节点上部署新字段，最大程度上降低主库负载和用户感知的停机风险。

当 MySQL 表的数据量增长过快，导致查询性能下降时，你会采取哪些措施来优化表的设计或查询？

当表数据量激增导致单表查询与写入性能下降时，可采取以下优化措施：

首先，通过水平分表将数据按用户 ID 或时间等分片键拆分到多张子表或多库中，以减少单表大小、降低锁竞争并分散 I/O 压力；

其次，合理设计索引，包括对经常用于过滤的单个字段创建单列索引、对多字段组合查询创建复合索引，以及使用覆盖索引来避免回表，但切忌在索引字段上使用函数或表达式以免导致索引失效；

其三，可在应用层或独立缓存层（如 Redis）缓存热点数据或热点查询结果，显著减少数据库访问次数并缓解压力；

第四，定期将历史或冷数据归档到独立的存档表、数据仓库或专用归档系统中，保持主表的数据量在可控范围内，从而提升主表的查询效率；

最后，当单机 MySQL 无法再满足性能与扩展需求时，可考虑迁移到分布式数据库（如 TiDB、PolarDB），利用其水平可扩展架构和分布式事务能力，实现更大规模的数据存储与高并发访问。

读写分离

在主从复制架构中，可以将数据库服务器组织成一主一从或一主多从的集群模式：所有写操作（INSERT、UPDATE、DELETE 等）都集中发送到主库，以保证强一致性；主库会将数据变更实时同步到一个或多个从库，而从库则专注于处理只读查询，提供最终一致性的读服务。业务服务器根据操作类型，将写请求路由到主库，将读请求路由到从库，从而在不影响写入性能和一致性的前提下，大幅提升整体系统的读扩展能力和稳定性。

这样做有几个好处：

第一，写入流量只作用于主库，从库则专注于处理查询请求，可显著减轻单台数据库的压力；

第二，通过增加从库数量，可以水平扩展读能力，提升整体系统的并发查询性能；

第三，即使在从库进行备份或维护时，也能保证主库持续提供写入服务，增强了系统的可用性和抗故障能力。

需要注意的是，从库与主库之间存在微小的复制延迟，因此对于对实时性要求极高的查询，应优先访问主库或使用读写路由策略进行合理调度；同时，还要考虑事务隔离和一致性需求，确保读写分离不会引入数据不一致风险。通过合理配置负载均衡和故障切换机制，读写分离能够在保证数据安全与一致性的前提下，大幅提升数据库集群的性能和稳定性。

在程序层面，实现读写分离通常有两种方式：

一种是在业务代码中抽象出一个数据访问层（或“中间层”），由这一层统一管理主从库的连接和路由逻辑，业务逻辑只需调用该层提供的接口即可完成读写分离，如下图；

另一种是通过独立部署的中间件系统来承担这项工作，它对外提供与数据库协议兼容的接口，自动将写请求路由到主库、读请求分发到从库，业务服务器无需关心具体的分库路由和连接管理。这样既能保持程序的清晰简洁，又能在中间件层面灵活扩展和运维读写分离的能力。具体结构如下图：

如果要是用 MyCat 作为中间件的话，可参考：https://bbs.huaweicloud.com/blogs/343277