知识卡片

结构图卡

分区表:约束排除跳过扫描,触发器与规则的路由权衡

理解等级:未标注

表分区把一张巨大的表拆解成若干个小表,从而只需扫描更少的数据块就能取到需要的数据——这与[[物化视图与视图的关键区别:新鲜度换存储,存储换性能|物化视图]]异曲同工:物化视图靠预聚合、缩小要扫描的数据量,分区靠把大表拆小、缩小单次查询实际命中的数据量。创建分区时用约束(比如按日期范围)确保每个分区只存放特定的数据,PostgreSQL规划器可以利用这些约束避免扫描那些明显不包含所需数据的分区——这个能力称为约束排除:规划器检查每个分区自身的约束条件,一旦发现某个分区不可能包含指定的值,就直接跳过对它的扫描。分区还带来几个附加好处:分区上的索引比单一大表上的索引小得多,更容易整个装入内存;可以按访问频率把分区分层存储(不常访问的旧数据放慢磁盘表空间,常访问的新数据放快磁盘表空间);维护也更方便——数据仓库场景下要清理最早一年的数据,若没有分区就要在一条DELETE语句里处理海量记录(资源密集,还会留下大量待清空的空白空间),而合理分区之后,一条TRUNCATE语句就能删光整个分区。创建分区表需要三步:建主表(只是子表的模板,本身不存数据)、建子表(用INHERITS继承主表,真正存数据,各自带约束)、建触发器把对主表的SQL操作重定向到匹配的子表——但重要的是这个”性能提升只在查询对驱动分区的列做过滤时才会发生”,否则规划器不得不扫描所有子表。数据路由除了触发器,还可以用PostgreSQL规则系统实现:规则的开销通常比触发器高,但规则是按查询评估一次,触发器是逐行触发一次——对于插入量特别大的表,用规则实现分区反而可能更省开销,这是一个需要按实际写入模式权衡的取舍点,而非”触发器总是更快”这种一概而论的结论。

结构图:

flowchart TB
  P["分区表"]
  P --> C["约束排除:规划器依据分区自身约束<br/>跳过不可能命中的分区"]
  P --> S["附加收益:索引更小易入内存<br/>可分层存储/维护更简单(TRUNCATE替代DELETE)"]
  P --> R["数据路由方式"]
  R --> T["触发器:逐行触发,开销较低<br/>适合常规插入量"]
  R --> RU["规则:按查询评估一次,开销较高<br/>但对超大插入量可能更省"]
  C -.前提.-> Q["仅当查询对分区驱动列做过滤时才生效<br/>否则退化为扫描所有子表"]
这张卡片看不懂?

已有反馈 · 0 / 10 条

    还没有人提交反馈。

    原书出处

    参考来源

    - 位置:《数据架构师的PostgreSQL修炼:高效设计、开发与维护数据库应用》第8章《服务器调优》之"8.5 分区表"(源文件:_epub-src/OEBPS/text00100.html) - 结论依据:原文说明"PostgreSQL的查询规划器可以使用这些约束从而避免扫描部分分区。此功能称为约束排除……从这一方法得到的性能提升,只有在查询对驱动分区逻辑的列上应用过滤的情况下发生……使用触发器会减慢数据插入。另一种方法是使用PostgreSQL规则系统……规则引起的开销通常高于由触发器引起的开销。然而,规则是每次查询时候评估,而触发器是每行发生。对于具有更大容量插入的表,实施使用规则的分区可能是有效的", 直接支撑本卡关于约束排除及触发器/规则路由权衡的结构图。 - 原始内容:如果数据做了合理的分区,一个TRUNCATE语句就可以删除所有旧的数据。