5.10. 表分区

PostgreSQL 支持基本的表分区。本节描述了为什么以及如何在数据库设计中实现分区。

5.10.1. Overview

分区是指将一张大表从逻辑上拆分为较小的物理块。分区可以带来以下好处:

  • 在某些情况下,尤其是当表中大多数被频繁访问的行位于单个分区或少量分区中时,查询性能可以得到显着提高。分区替代了索引的前几列,从而减小了索引的大小,并使索引中频繁使用的部分更有可能装入内存。

  • 当查询或更新访问单个分区的很大一部分时,可以通过对该分区进行 Sequences 扫描而不是使用索引和分散在整个表中的随机访问读取来提高性能。

  • 如果计划将这种需求计划在分区设计中,则可以通过添加或删除分区来完成批量加载和删除。使用ALTER TABLE DETACH PARTITION或使用DROP TABLE删除单个分区比批量操作要快得多。这些命令还完全避免了由于DELETE造成的VACUUM开销。

  • 很少使用的数据可以迁移到更便宜,更慢的存储介质上。

通常只有在表很大的情况下,这些好处才是值得的。表可以从分区中受益的确切时间取决于应用程序,尽管经验法则是表的大小应超过数据库服务器的物理内存。

PostgreSQL 为以下形式的分区提供内置支持:

  • Range Partitioning

    • 该表被划分为由键列或列集定义的“范围”,分配给不同分区的值的范围之间没有重叠。例如,可以按日期范围或特定业务对象的标识符范围进行分区。
  • List Partitioning

    • 通过显式列出哪些键值出现在每个分区中来对表进行分区。

如果您的应用程序需要使用上面未列出的其他分区形式,则可以使用替代方法,例如继承和UNION ALL视图。此类方法提供了灵 Active,但没有内置声明性分区的某些性能优势。

5 .10.2. 声明式分区

PostgreSQL 提供了一种指定如何将表划分为多个部分的方法。被划分的表称为分区表。规范包括分区方法和用作分区键的列或表达式列表。

插入分区表中的所有行将根据分区键的值被路由到* partitions 之一。每个分区都有一个由其 partition bounds *定义的数据子集。当前支持的分区方法包括范围和列表,其中分别为每个分区分配了一个键范围和一个键列表。

可以使用所谓的* sub-partitioning *将分区本身定义为分区表。分区可能具有自己的索引,约束和默认值,与其他分区的索引,约束和默认值不同。必须为每个分区分别创建索引。有关创建分区表和分区的更多详细信息,请参见CREATE TABLE

无法将常规表转换为分区表,反之亦然。但是,可以将包含数据的常规表或分区表添加为分区表的分区,也可以从分区表中删除分区,从而将其转变为独立表;请参阅ALTER TABLE,以了解有关ATTACH PARTITIONDETACH PARTITION子命令的更多信息。

各个分区通过后台继承链接到分区表。但是,不可能将上一部分中讨论的某些继承功能与分区表和分区一起使用。例如,一个分区除了作为分区的分区表之外,不能有其他任何父级,常规表也不能从使其成为其父级的分区表继承。这意味着分区表和分区不参与常规表的继承。由于由分区表及其分区组成的分区层次结构仍然是继承层次结构,因此所有正常的继承规则都按照Section 5.9中的描述适用,但有一些 exception,最值得注意的是:

  • 分区表的CHECKNOT NULL约束始终由其所有分区继承。标记为NO INHERITCHECK约束不允许在分区表上创建。

  • 当没有分区时,仅支持使用ONLY在分区表上添加或删除约束。一旦存在分区,使用ONLY将导致错误,因为不支持存在分区时仅在分区表上添加或删除约束。而是可以在父表中不存在约束时直接在分区上添加或删除约束。由于分区表没有直接任何数据,因此尝试在分区表上使用TRUNCATE ONLY总是会返回错误。

  • 分区不能具有父级中不存在的列。使用CREATE TABLE创建分区时既不能指定列,也不能使用ALTER TABLE向事后添加分区中的列。仅当表的列与父级(包括任何oid列)完全匹配时,才可以将它们添加为带有ALTER TABLE ... ATTACH PARTITION的分区。

  • 如果父表中存在NOT NULL约束,则不能将其删除。

分区也可以是外部表(请参见创建外表),尽管它们具有普通表所没有的一些限制。例如,插入分区表中的数据不会路由到外部表分区。

5.10.2.1. Example

假设我们正在为一家大型冰淇淋公司构建数据库。该公司每天测量最高温度以及每个地区的冰淇淋销售量。从概念上讲,我们想要一个像这样的表:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

我们知道大多数查询将仅访问上周,月或季度的数据,因为此表的主要用途是为 Management 准备在线报告。为了减少需要存储的旧数据量,我们决定仅保留最近 3 年的数据。在每个月初,我们将删除最早的月份的数据。在这种情况下,我们可以使用分区来帮助我们满足测量表的所有不同要求。

要在这种情况下使用声明性分区,请执行以下步骤:

  • 通过指定PARTITION BY子句将measurement表创建为分区表,该子句包括分区方法(在这种情况下为RANGE)和用作分区键的列列表。
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

如果需要,您可以决定在分区键中使用多个列进行范围分区。当然,这通常会导致分区数量更多,每个分区分别更小。另一方面,使用较少的列可能会导致分区数量较少的分区准则更粗糙。如果条件涉及某些或所有这些列,则访问分区表的查询将必须扫描较少的分区。例如,考虑使用范围lastnamefirstname(按此 Sequences)分区的表范围作为分区键。

  • 创建分区。每个分区的定义必须指定与父级的分区方法和分区键相对应的界限。请注意,指定范围以使新分区的值与一个或多个现有分区中的值重叠将导致错误。将数据插入未 Map 到现有分区之一的父表将导致错误;必须手动添加适当的分区。

这样创建的分区在所有方面都是普通的 PostgreSQL 表(或者可能是外部表)。可以为每个分区分别指定表空间和存储参数。

不必创建描述分区分区边界条件的表约束。相反,每当需要引用分区约束时,都会从分区绑定规范隐式生成分区约束。

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

要实现子分区,请在用于创建单个分区的命令中指定PARTITION BY子句,例如:

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
    PARTITION BY RANGE (peaktemp);

创建measurement_y2006m02的分区后,Map 到measurement_y2006m02的任何插入measurement的数据(或满足其分区约束的情况下直接插入measurement_y2006m02的数据)将根据peaktemp列进一步重定向到其分区之一。指定的分区键可能与父级的分区键重叠,尽管在指定子分区的边界时应格外小心,以使其接受的数据集构成该分区自身边界所允许的子集;系统不会尝试检查是否确实如此。

  • 在键列上以及每个分区上可能需要的其他索引上创建索引。 (键索引不是严格必需的,但在大多数情况下很有用.如果您希望键值是唯一的,则应始终为每个分区创建唯一或主键约束.)
CREATE INDEX ON measurement_y2006m02 (logdate);
CREATE INDEX ON measurement_y2006m03 (logdate);
...
CREATE INDEX ON measurement_y2007m11 (logdate);
CREATE INDEX ON measurement_y2007m12 (logdate);
CREATE INDEX ON measurement_y2008m01 (logdate);
  • 确保postgresql.conf中未禁用constraint_exclusion配置参数。如果是这样,查询将不会根据需要进行优化。

在上面的示例中,我们每个月都会创建一个新分区,因此编写一个脚本自动生成所需的 DDL 可能是明智的。

5 .10.2.2. 分区维护

通常,最初定义表时构建的分区集并不打算保持静态。通常希望删除数据的旧分区,并定期为新数据添加新分区。分区的最重要优点之一就是,它允许通过操纵分区结构而不是物理地移动大量数据来几乎立即执行该原本痛苦的任务。

删除旧数据的最简单选择是删除不再需要的分区:

DROP TABLE measurement_y2006m02;

由于不必单独删除每条记录,因此可以很快删除数百万条记录。但是请注意,上述命令要求对父表进行ACCESS EXCLUSIVE锁定。

通常更可取的另一种选择是从分区表中删除该分区,但保留其本身作为表的访问权限:

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

这允许在删除数据之前对数据执行进一步的操作。例如,这通常是使用COPY,pg_dump 或类似工具备份数据的有用时间。这也是将数据聚合为较小格式,执行其他数据操作或运行报告的有用时间。

同样,我们可以添加一个新分区来处理新数据。我们可以在分区表中创建一个空分区,就像在上面创建原始分区一样:

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

作为替代方案,有时在分区结构之外创建新表并在以后使其成为适当的分区更为方便。这允许在数据出现在分区表中之前对其进行加载,检查和转换:

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

在运行ATTACH PARTITION命令之前,建议在要附加的表上创建与所需分区约束匹配的CHECK约束。这样,系统将能够跳过扫描以验证隐式分区约束。没有CHECK约束,将扫描表以验证分区约束,同时在父表上保持ACCESS EXCLUSIVE锁。可能需要在ATTACH PARTITION完成后删除冗余CHECK约束。

5.10.2.3. Limitations

以下限制适用于分区表:

  • 没有可用于在所有分区上自动创建匹配索引的工具。必须使用单独的命令将索引添加到每个分区。这也意味着无法创建跨越所有分区的主键,唯一约束或排除约束。只能单独约束每个叶分区。

  • 由于分区表不支持主键,因此不支持引用分区表的外键,也不支持从分区表到其他表的外键引用。

  • 对分区表使用ON CONFLICT子句会导致错误,因为唯一约束或排除约束只能在单个分区上创建。不支持在整个分区层次结构中强制唯一性(或排除约束)。

  • 导致行从一个分区移动到另一个分区的UPDATE失败,因为该行的新值无法满足原始分区的隐式分区约束。

  • 如果需要,行触发器必须在单个分区上定义,而不是在分区表上定义。

  • 不允许在同一分区树中混合临时和永久关系。因此,如果分区表是永久性的,则分区表也必须是永久性的,分区表也是临时的。使用临时关系时,分区树的所有成员必须来自同一会话。

5 .10.3. 使用继承的实现

尽管内置的声明性分区适用于大多数常见用例,但在某些情况下,可能会使用更灵活的方法。可以使用表继承来实现分区,该继承允许声明性分区不支持的一些功能,例如:

  • 分区强制执行以下规则:所有分区都必须具有与父分区完全相同的列集,但是表继承允许子级具有父级中不存在的额外列。

  • 表继承允许多重继承。

  • 声明性分区仅支持列表和范围分区,而表继承允许按用户选择的方式对数据进行拆分。 (但是请注意,如果约束排除无法有效地修剪分区,则查询性能将非常差.)

  • 与使用表继承相比,使用声明性分区时,某些操作需要更强的锁定。例如,在分区表中添加分区或从分区表中删除分区都需要对父表进行ACCESS EXCLUSIVE锁定,而对于常规继承,使用SHARE UPDATE EXCLUSIVE锁定就足够了。

5.10.3.1. Example

我们使用上面未分区的measurement表。要使用继承实现分区,请使用以下步骤:

  • 创建“ master”表,所有分区都将从该表继承。该表将不包含任何数据。不要在此表上定义任何检查约束,除非您打算将它们平等地应用于所有分区。定义任何索引或唯一约束都没有意义。对于我们的示例,主表是最初定义的measurement表。

  • 创建几个“子”表,每个子表都从主表继承。通常,这些表不会在从主服务器继承的集合中添加任何列。就像声明性分区一样,这些分区在所有方面都是普通的 PostgreSQL 表(或外部表)。

CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
  • 将非重叠表约束添加到分区表,以在每个分区中定义允许的键值。

典型示例为:

CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )

确保约束条件确保不同分区中允许的键值之间没有重叠。一个常见的错误是设置范围约束,例如:

CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )

这是错误的,因为不清楚键值 200 属于哪个分区。

最好按以下方式创建分区:

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2006m03 (
    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);

...
CREATE TABLE measurement_y2007m11 (
    CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
    CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
    CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);
  • 对于每个分区,在键列上创建索引,以及您可能需要的任何其他索引。
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
  • 我们希望我们的应用程序能够说INSERT INTO measurement ...并将数据重定向到适当的分区表中。我们可以通过在主表上附加合适的触发函数来安排它。如果仅将数据添加到最新分区,则可以使用非常简单的触发函数:
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

创建函数之后,我们创建一个触发器,该触发器调用该触发器函数:

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

我们必须每月重新定义触发函数,以便它始终指向当前分区。但是,触发器定义不需要更新。

我们可能要插入数据,并让服务器自动找到应在其中添加行的分区。我们可以使用更复杂的触发函数来做到这一点,例如:

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

触发器定义与以前相同。请注意,每个IF测试必须与其分区完全匹配CHECK约束。

尽管此功能比单月情况更为复杂,但由于需要先添加分支机构,因此无需经常更新。

Note

实际上,如果大多数插入内容进入该分区,最好先检查最新的分区。为简单起见,我们以与本示例其他部分相同的 Sequences 显示了触发器的测试。

将插入重定向到适当的分区表的另一种方法是在主表上设置规则,而不是触发器。例如:

CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);

规则比触发器具有更多的开销,但是开销为每个查询支付一次,而不是为每行支付一次,因此此方法对于批量插入情况可能是有利的。但是,在大多数情况下,触发方法将提供更好的性能。

请注意,COPY会忽略规则。如果要使用COPY插入数据,则需要复制到正确的分区表中,而不是复制到主表中。 COPY会触发触发器,因此如果使用触发器方法,则可以正常使用它。

规则方法的另一个缺点是,如果规则集不能涵盖插入日期,则没有简单的方法来强制执行错误。数据将以静默方式进入主表。

  • 确保postgresql.conf中未禁用constraint_exclusion配置参数。如果是这样,查询将不会根据需要进行优化。

如我们所见,复杂的分区方案可能需要大量的 DDL。在上面的示例中,我们每个月都会创建一个新分区,因此编写一个脚本自动生成所需的 DDL 可能是明智的。

5 .10.3.2. 分区维护

要快速删除旧数据,只需删除不再需要的分区:

DROP TABLE measurement_y2006m02;

要从分区表中删除分区,但保留其本身作为表的访问权限:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

要添加新分区来处理新数据,请创建一个空分区,就像在上面创建原始分区一样:

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

或者,可能要在分区结构之外创建新表,并在加载,检查和转换数据后将其设为分区。

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.10.3.3. Caveats

以下警告适用于使用继承实现的分区表:

  • 没有自动的方法来验证所有CHECK约束是否互斥。创建代码来生成分区,创建和/或修改关联对象比手工编写每个对象更为安全。

  • 此处显示的方案假定一行的分区键列从不改变,或者至少没有改变到足以使其移动到另一个分区的程度。 UPDATE尝试执行此操作将由于CHECK约束而失败。如果需要处理此类情况,可以在分区表上放置适当的更新触发器,但这会使结构的 Management 变得更加复杂。

  • 如果您使用的是手动VACUUMANALYZE命令,请不要忘记需要分别在每个分区上运行它们。像这样的命令:

ANALYZE measurement;

将只处理主表。

  • 带有ON CONFLICT子句的INSERT语句不太可能按预期工作,因为ON CONFLICT操作仅在指定目标关系(而不是其子关系)发生唯一违规的情况下执行。

  • 除非应用程序明确知道分区方案,否则将需要触发器或规则将行路由到所需的分区。触发器的编写可能很复杂,并且将比声明性分区在内部执行的 Tuples 路由慢得多。

5 .10.4. 分区和约束排除

约束排除是一种查询优化技术,可提高以上述方式定义的分区表(声明性分区表和使用继承实现的分区表)的性能。举个例子:

SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

在没有约束排除的情况下,上述查询将扫描measurement表的每个分区。启用约束排除后,计划人员将检查每个分区的约束,并尝试证明不需要扫描该分区,因为该分区不能包含满足查询的WHERE子句的任何行。当计划者可以证明这一点时,它将从查询计划中排除该分区。

您可以使用EXPLAIN命令来显示启用constraint_exclusion的计划和禁用constraint_exclusion的计划之间的差异。对于这种类型的表设置,典型的未优化计划是:

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

部分或全部分区可能使用索引扫描而不是全表 Sequences 扫描,但是这里的要点是根本不需要扫描旧的分区来回答此查询。当启用约束排除时,我们将获得便宜得多的计划,该计划将提供相同的答案:

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

请注意,约束排除仅由CHECK约束驱动,而不由索引的存在驱动。因此,不必在键列上定义索引。是否需要为给定分区创建索引取决于您是否希望扫描分区的查询通常会扫描大部分分区还是仅扫描一小部分。在后一种情况下,索引将有所帮助,但对于前者则无济于事。

constraint_exclusion的默认(推荐设置)实际上既不是on也不是off,而是称为partition的中间设置,这导致该技术仅应用于可能在分区表上运行的查询。 on设置使计划人员在所有查询中检查CHECK约束,即使是不太可能受益的简单查询。

以下警告适用于约束排除,继承表和分区表均使用此约束:

  • 仅当查询的WHERE子句包含常量(或外部提供的参数)时,约束排除才有效。例如,无法优化与诸如CURRENT_TIMESTAMP之类的不可变函数的比较,因为计划者无法知道函数值在运行时可能属于哪个分区。

  • 保持分区约束简单,否则计划者可能无法证明不需要访问分区。如上例所示,对列表分区使用简单的相等条件,对范围分区使用简单的范围测试。一条好的经验法则是,分区约束应仅包含使用 B-tree-indexable 运算符将分区列与常量的比较,这甚至适用于分区表,因为仅允许使用 B-tree-indexable 列在分区键中。 (使用声明性分区时这不是问题,因为自动生成的约束足够简单,计划人员可以理解.)

  • 在约束排除期间,将检查主表所有分区上的所有约束,因此,大量分区可能会大大增加查询计划时间。使用这些技术进行分区可以很好地处理多达一百个分区。不要尝试使用成千上万个分区。

5 .10.5. 声明式分区最佳实践

应该仔细选择如何对表进行分区,因为糟糕的设计会对查询计划和执行的性能产生负面影响。

最关键的设计决策之一将是您用来划分数据的一个或多个列。通常,最好的选择是按最常出现在对分区表执行的查询的WHERE子句中的列或列集进行分区。与分区键匹配并兼容的WHERE子句项可用于修剪不需要的分区。在计划分区策略时,删除不需要的数据也是要考虑的因素。整个分区可以相当快地分离,因此设计分区策略可能会有益,因为所有要立即删除的数据都位于单个分区中。

选择应将表划分为分区的目标数量也是一个至关重要的决定。没有足够的分区可能意味着索引仍然太大,数据局部性仍然很差,这可能导致低的缓存命中率。但是,将表分成太多分区也会引起问题。过多的分区可能意味着更长的查询计划时间和更高的内存使用量,在查询计划和执行期间。在选择如何对表进行分区时,考虑将来可能发生的更改也很重要。例如,如果您选择为每个 Client 分配一个分区,而您目前只有少量大 Client,那么考虑几年后如果您却发现自己拥有大量小 Client,将产生什么影响。在这种情况下,最好选择按RANGE进行分区,然后选择合理数量的分区,每个分区都包含固定数量的 Client,而不是尝试按LIST进行分区,并希望 Client 数量不会增加到超出范围对数据进行分区很实用。

子分区对于进一步划分预期会比其他分区更大的分区很有用,尽管过多的子分区很容易导致大量分区,并可能导致上一节中提到的相同问题。

在查询计划和执行过程中考虑分区的开销也很重要。查询计划程序通常能够处理多达几百个分区的分区层次结构。随着添加更多分区,计划时间变得更长,内存消耗也越来越高。对于UPDATEDELETE命令尤其如此。担心拥有大量分区的另一个原因是,服务器的内存消耗可能会在一段时间内显着增长,尤其是在许多会话接触大量分区的情况下。这是因为每个分区都需要将其元数据加载到与之接触的每个会话的本地内存中。

对于数据仓库类型的工作负载,与使用 OLTP 类型的工作负载相比,使用更多数量的分区是有意义的。通常,在数据仓库中,由于大多数处理时间是在查询执行过程中花费的,因此查询计划时间就不再那么重要了。对于这两种类型的工作负载中的任何一种,重要的是及早做出正确的决定,因为对大量数据进行重新分区可能会非常缓慢。预期工作负载的模拟通常有助于优化分区策略。永远不要认为更多的分区比更少的分区更好,反之亦然。