设计无需频繁维护的数据库分区

# 设计无需人工照看的分区策略 来源：https://explainanalyze.com/p/designing-partitioning-you-dont-have-to-babysit/ 文章《设计无需人工照看的分区策略》的题图 数据库架构 系统设计 ### 六个月后，p\_future 分区容纳了 8 亿行数据，因为当初的增长预测没能经受住实际工作负载的考验，而每一次修复所需的 ALTER 操作都需要安排一个没人想做的维护窗口。边界管理只是两行 DDL 代码；更难的部分是选择一个不会泄露到应用代码中的分区键。 **TL;DR** 按主键分区，而不是按 `created_at` 分区，并让一个后台服务根据观察到的增长情况来管理边界。查询继续使用已有的键，分区裁剪自动生效，分区列也不会泄露到应用代码中。同样的“服务监控并调整”模式也适用于哈希分区和列表分区，只是操作不同。 订单仪表盘在部署分区后的一周开始变慢，团队的第一反应是归咎于新的索引策略。但真正的原因在 `EXPLAIN` 中显现：对于一个简单的 `SELECT * FROM orders WHERE id = 12345` 查询，计划中显示了三十六行 `Partitions: orders_p2025_01, orders_p2025_02, ...`。 计划读取了所有分区，因为 WHERE 子句没有包含 `created_at`，而 `created_at` 正是分区键。原本一次索引探测的查找变成了三十六次。 提出的修复方案总是老一套：在仪表盘查询中添加 `created_at >= '2024-11-01'`。这确实有效，计划降到了一个分区。 然后审计页面也做了同样的修改，接着是管理工具，然后是迁移脚本。三个月后，团队内部出现了一个 lint 规则，标记任何没有日期过滤条件的 `SELECT FROM orders` 查询；代码审查中，“你添加分区过滤条件了吗？”成了常规检查项。分区键不再只是一个存储决策，而是变成了每个查询都必须遵守的契约。即使忘记，也不会产生错误，只会变慢。 ## 分区键问题 PostgreSQL 和 MySQL 都要求分区键必须是表上任何主键或唯一约束的一部分。这条规则的存在是为了保证正确性：如果主键不包含分区键，数据库就无法在不扫描所有分区的情况下保证唯一性。 结果是，如果你想按 `created_at` 分区，就不能再简单地使用 `PRIMARY KEY (id)`，而需要 `PRIMARY KEY (id, created_at)`。日期列现在成了主键的一部分，无论你的应用是否需要。 更微妙的代价是，`id` 在数据库眼中不再唯一。唯一性是在元组 `(id, created_at)` 上强制执行的：数据库会欣然接受两行具有相同 `id` 但不同时间戳的数据。应用可能仍然认为 `id` 是唯一的，但模式中没有任何东西能保证这一点。并且你无法通过单独一个 `UNIQUE (id)` 约束来恢复这个保证：MySQL 和 PostgreSQL 都要求分区表上的每个唯一约束都必须包含分区键列。唯一性属性实际上已被舍弃。 这不仅仅是表面问题；它改变了优化器愿意生成的查询计划： - 对于 `PRIMARY KEY (id)`，`WHERE id = 1` 是一个常量时间查找。MySQL 的 EXPLAIN 显示为 `const` 访问类型；优化器知道恰好匹配一行，执行器在找到后就停止。基于 `id` 的 JOIN 是 `eq_ref`，这是最快的 JOIN 访问类型。 - 对于 `PRIMARY KEY (id, created_at)`，同样的查询变成了 `ref` 查找：在索引最左列上的前缀扫描，就数据库而言，可能返回多行。原本是 `eq_ref` 的 JOIN 变成了 `ref`。基数估计回退到索引统计信息，而不是保证的“单行”假设，这可能导致优化器在查询树更上层选择更差的计划。 要恢复旧的 `const` 计划，每个查找都必须指定完整的主键： ```sql -- 曾经是 const 查找，现在是 ref 查找（可能返回多行中的一行） SELECT * FROM orders WHERE id = 1; -- 回到 const，但前提是调用者知道 created_at SELECT * FROM orders WHERE id = 1 AND created_at = '2026-04-01 12:34:56'; ``` 这与分区裁剪的泄漏问题如出一辙，只是角度不同：分区键已经强行进入了与日期无关的查询中，首先是为了获得裁剪，现在是为了获得单行访问。 ```sql -- 分区前 CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL ); -- 按月分区后 CREATE TABLE orders ( id BIGINT AUTO_INCREMENT, customer_id BIGINT NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id, created_at) -- created_at 被强制加入主键 ) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')), PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')), ... ); ``` 此时一切仍然正常。表接受插入，查询返回正确结果，分区边界也存在。问题会在第一次有人运行一个 WHERE 子句中没有包含 `created_at` 的查询时显现。 ## 分区裁剪只在主动请求时有效 分区裁剪是使分区值得做的优化。当查询的 WHERE 子句限制分区键时，数据库可以跳过不可能匹配的分区。例如，查询上周的订单只读取包含上周数据的一个或两个分区。 这种优化依赖于分区键出现在 WHERE 子句中。过滤其他字段的查询不会得到裁剪；它会扫描所有分区。 ```sql -- 这个查询扫描所有分区，共 36 个 SELECT * FROM orders WHERE id = 12345; -- 这个查询裁剪到单个分区 SELECT * FROM orders WHERE id = 12345 AND created_at >= '2026-03-01' AND created_at < '2026-04-01'; ``` 第一个查询是那种经常发生的查找：通过主键获取订单。在非分区表上，这是一次单一的索引查找。在分区表上，如果 WHERE 子句中缺少裁剪键，则是对每个分区进行一次单独的索引探测：三十六次索引查找代替了一次。 绝对时间上可能仍然很快，但比非分区版本差得多，这完全违背了引入分区的初衷。 团队通常采用的“修复”方法是将分区键添加到所有涉及该表的查询中。这是一个泄漏的抽象。存储决策变成了与所有调用者的契约：新代码必须记住分区过滤条件，旧代码需要审计，ORM 需要围绕它进行配置。 **没有错误，只有变慢** 本应裁剪却没有裁剪的查询仍然返回正确的结果。只是计划扫描了所有分区。没有异常，没有警告，应用日志中没有标记，只有没人会去看的 `EXPLAIN`，直到某个仪表盘超时。大多数团队在分区部署后通过查看慢查询日志才发现失败，而不是因为数据库在查询执行期间暴露了任何信息。 ## 静态分区边界经不起时间考验 另一个容易出错的地方是在表创建时硬编码分区边界。最初的布局反映了团队当时对增长的预测。六个月后，流量模式变了，有些分区比其他分区大 10 倍，而 `p_future` 这个兜底分区承载了表的一半数据。 ```sql -- 创建时定义：看起来合理 PARTITION p2026_q1 VALUES LESS THAN (100000000), PARTITION p2026_q2 VALUES LESS THAN (200000000), ... -- 六个月后：增长加速，p_future 变成了整个活跃工作负载 PARTITION p_future VALUES LESS THAN MAXVALUE -- 8 亿行且仍在增长 ``` 手动拆分和重新平衡分区是没人愿意承担的运维工作。它需要安排维护窗口，对可能数百 GB 的表执行 `ALTER TABLE ... REORGANIZE PARTITION`，与应用程序团队协调，并且不能出错。通常直到出现性能事故才会去做，而那时修复成本很高。 ## 更好方法的形态 主键已经存在。对于使用 `BIGINT AUTO_INCREMENT` 的表，它是单调递增的：较新的行具有更大的 ID。这正是范围分区所需要的性质。主键就是分区键。 ```sql CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL ) PARTITION BY RANGE (id) ( PARTITION p0001 VALUES LESS THAN (100000000), PARTITION p0002 VALUES LESS THAN (200000000), PARTITION p0003 VALUES LESS THAN (300000000), PARTITION p_future VALUES LESS THAN MAXVALUE ); ``` 所有通过 `id` 过滤的查询（大多数查询都是如此）都能自动获得分区裁剪，无需修改应用代码。按 ID 的范围查询可以裁剪到少量分区。点查找则精确裁剪到一个分区。主键已经在每个需要它的 WHERE 子句中了，因为它就是主键。 代价是分区边界不再直接由时间定义，这看起来破坏了基于时间的保留策略。实际上，这并不像看起来那么大的代价；分区的目的通常不是保留数据，而是保持索引大小可控、使维护操作廉价以及限制不良查询的影响范围。当保留是目标时，仍然可以选择与时间对齐的边界，只是这些边界是在 DDL 时由分区服务选择的，而不是硬编码在模式中。请参见“时间对齐的边界，无需键中包含日期”。 ## 自动化范围分区管理 以上所有内容都假设了范围分区：由有序值（ID 范围、日期范围）上的连续边界定义的分区。运维工作是机械性的：监控活动分区填满，在发生之前将 `MAXVALUE` 兜底分区拆分为一个新的有界分区，并删除超过保留阈值的分区。一个按计划运行的小型服务就足以保持布局健康。 困难的部分不在于逻辑，而在于安全地执行：在不锁住写入的情况下对大型表运行 DDL，处理部分失败，并在服务在操作中崩溃时干净地恢复。 ```sql -- 将兜底分区拆分为一个新的有界分区 + 新的兜底分区 -- 这是服务定期执行的操作 ALTER TABLE orders REORGANIZE PARTITION p_future INTO ( PARTITION p0037 VALUES LESS THAN (3700000000), PARTITION p_future VALUES LESS THAN MAXVALUE ); ``` 在空的兜底分区上执行 `REORGANIZE PARTITION` 很快，因为没有数据需要移动。如果在任何行落到拆分点之上之前拆分兜底分区，那么该操作只是元数据层面的。服务的任务是领先于写入工作负载：在兜底分区仍然很小或为空时进行拆分，而不是在它已经容纳了数亿行时。 **什么让服务在生产中变得棘手** 逻辑只是几条 DDL 语句。困难的部分在于它们周围的一切：在 `REORGANIZE` 期间不锁住写入，在 DDL 中途崩溃时能够生存下来（重试时幂等），处理同时对表使用 `ACCESS EXCLUSIVE` 的并发迁移工具，以及拥有清晰的运行手册（“服务已停滞，兜底分区现在有 2 亿行，该怎么办”）。生产级的分区管理器通常花费更多代码在操作支架上，而不是在 DDL 本身。 没有唯一正确的目标；这取决于最初驱动分区的原因。如果目标是通过保留策略保持 OLTP 工作集较小，那么边界间距是一个业务决策：数据需要在热存储中保持可查询多长时间，一年、七年或某个中间值。如果目标是性能，那么将每个分区的大小调整为使其索引能舒适地放入内存是一个合理的经验法则，前提是没有显著的键倾斜将读写集中在单个分区上。服务可以针对任一目标进行配置，并根据观察到的增长调整边界间距。 ### 时间对齐的边界，无需键中包含日期 按 `id` 分区并不意味着放弃基于时间的边界；它只是意味着事后选择这些边界。服务可以对活动表执行一个简单查询，以找到任何时间点的 ID 边界： ```sql -- 三月初的 ID 指针在哪里？ SELECT MAX(id) FROM orders WHERE created_at < '2026-03-01'; -- -> 3700842139 ``` 该值成为下一个有界分区的上界。兜底分区保持在其上，未来的分区将在与时间对齐的 ID 边界处切割： ```sql -- 分区仍然由 ID 范围定义，但选择与月份边界对齐 ALTER TABLE orders REORGANIZE PARTITION p_future INTO ( PARTITION p2026_03 VALUES LESS THAN (3700842140), PARTITION p_future VALUES LESS THAN MAXVALUE ); ``` 生成的分区 `p2026_03` 大致包含 2026 年 3 月的所有订单，但 `created_at` 从未出现在主键中，不需要出现在任何 WHERE 子句中就能获得裁剪，也不会泄漏到应用代码中。日期列只在边界创建时由运行 DDL 的服务使用一次。查询继续通过 `id` 过滤并免费获得裁剪。 保留策略的工作方式相同。要删除超过 12 个月的数据，服务执行 `SELECT MAX(id) FROM orders WHERE created_at < NOW() - INTERVAL 12 MONTH`，识别所有上界低于该 ID 的分区，并将其删除。 `MAXVALUE` 兜底分区是实现这种模式的关键；在服务决定下一个切割点之前，新行总是有地方可以放置。 ### 服务的样子 服务本身很小。它按计划运行（对于高吞吐量表每小时一次，对于较慢的表每天一次），每次 tick 执行以下几件事： - **盘点**。从 catalog 读取当前分区布局：分区名称、上界和近似行数。 - **大小检查**。查看活动分区（紧邻兜底分区下方的有界分区）。如果它已填充到目标大小阈值以上，就该切割下一个边界了。 - **边界选择**。选择切割点。对于时间对齐的分区，查询活动表以获取下一个月份边界对应时间的 ID；该 ID 成为新分区的上界。 - **拆分**。将兜底分区重组为一个新的有界分区和一个新的兜底分区。只要兜底分区在拆分时仍然为空，这就是元数据操作。 - **保留裁剪**。通过相同的 `created_at` 到 `id` 的映射，将保留窗口（例如 12 个月）转换为一个 ID，然后删除所有上界低于该 ID 的分区。 - **并发守卫**。使用单个咨询锁或领导者选举，确保两个实例不会同时对同一张表运行 DDL。 - **指标和告警**。每个分区的大小和行数、自上次 tick 以来的时间，以及当活动分区开始以快于服务处理能力的速度填充时发出明确告警。