Database Designer 会为数据库创建可提供最优查询性能的物理架构。首次运行 Database Designer 时，您应该创建全面设计，其中包括作为设计起点的相关示例查询和数据。如果以后出现性能问题，请考虑加载您经常运行的其他查询，并重新运行 Database Designer 来创建增量设计。

运行 Database Designer 时，请选择“更新统计数据 (Update Statistics)”选项。Vertica 查询优化器使用数据的统计数据来创建查询计划。统计数据可以帮助优化器确定：

多个合格的投影以响应查询
执行联接所采用的最佳顺序
数据分布算法，例如广播和重新分段

如果您的统计数据已过时，请运行 Vertica 函数 ANALYZE_STATISTICS 来更新特定架构、表或列的统计数据。有关详细信息，请参阅收集数据库统计信息。

主动检查查询事件

QUERY_EVENTS 系统表返回有关查询计划、优化和执行事件的信息。

EVENT_TYPE 列提供了各种事件类型：

属于信息性类型的某些事件。
其他您应当查看的用于获取可能的纠正措施的事件。
最重要的、最需要处理的某些事件。

查看查询计划

查询计划由一系列类似步骤的路径组成，而 Vertica 查询优化器会选择这些路径来访问或修改 Vertical 数据库中的信息。获取查询计划信息的方式有两种：

运行 EXPLAIN 命令。每个步骤（路径）都代表了优化器为其执行策略使用的一个操作。
查询 QUERY_PLAN_PROFILES 系统表：此表详细介绍了当前运行的查询的执行状态。QUERY_PLAN_PROFILES 表的输出显示了实时数据流以及每个查询计划中的每个路径使用的时间和资源。

另请参阅

QUERY_EVENTS

2 - 列编码

更改列编码也有可能会提高查询速度。编码可减少数据在磁盘上的大小，因此会减少查询所需的 I/O，进而提高查询执行速度。确保查询中包括的所有列和投影都使用了正确的数据编码。为此，请执行以下步骤：

运行 Database Designer 来创建增量设计。Database Designer 会实施最佳编码和投影设计。
创建增量设计后，使用 ANALYZE_STATISTICS 函数更新统计数据。
使用您向设计提交的一个或多个查询运行 EXPLAIN，确保它正在使用新投影。

或者，运行 DESIGNER_DESIGN_PROJECTION_ENCODINGS 重新评估当前编码并在必要时对其进行更新。

2.1 - 改善列压缩

如果发现性能较慢，或者 FLOAT 数据占用了大量存储空间，请评估数据和业务需要，确认数据能否放入精度在 18 位及以下的 NUMERIC 列中。将 FLOAT 列转换为 NUMERIC 列可以提升数据压缩率，降低数据库的磁盘大小，并提升该列的查询性能。

定义 NUMERIC 数据类型时，您要指定精度和小数位数；NUMERIC 数据是数据的精确表示。FLOAT 数据类型代表了可变精度和近似值；它们在数据库中占用的空间更多。

在以下情况下，将 FLOAT 列转换为 NUMERIC 列最有效：

NUMERIC 精度为 18 位及以下。NUMERIC 数据的性能针对最常见的 18 位精度进行了优化。仅当需要 18 位及以下精度时，Vertica 才会建议将 FLOAT 列转换为 NUMERIC 列。
FLOAT 精度会受到限制，并且值将全部限定在 NUMERIC 列的指定精度范围内。一个示例是货币值，例如产品价格或财务交易金额。例如，定义为 NUMERIC(11,2) 的列可以包括从 0 到几百万美元的价格，可以存储分币，而且压缩效率也比 FLOAT 列更高。

如果您尝试将值加载到超过指定精度的 NUMERIC 列，Vertica 会返回错误，并且不会加载数据。如果您分配的值的小数位数大于指定小数位数，那么该值会进行四舍五入以匹配指定的小数位数，然后再存储到该列中。

另请参阅

数字数据类型

2.2 - 使用运行长度编码

当您运行 Database Designer 时，您可以选择对加载进行优化，以最大限度地减少数据库占用空间。在这种情况下，Database Designer 会对列应用编码，以最大限度地提供查询的性能。编码选项包括运行长度编码 (RLE)，此编码将列中唯一值的序列（运行长度）替换为一组值对，其中每个值对表示给定值连续出现的次数：（出现次数, 值）。

RLE 一般适用于低基数列，并且其中的唯一值通常是连续的，因为在此列中对表数据进行了排序。例如，客户个人资料表通常包含性别列，而此列仅包含 F 值和 M 值。对性别进行排序可确保 F 值或 M 值的运行长度可以表示为一组两个值对：（出现次数, F）和（出现次数, M）。因此，假设 F 出现 8,147 次，M 出现 7,956 次，而投影主要根据性别排序，Vertica 可以应用 RLE，将这些值存储为一组两个值对：(8147, F) 和 (7956, M)。这样可以减少投影占用空间，提高查询性能。

3 - 具有谓词的查询的投影

如果查询包含一个或多个谓词，您可以修改投影来提升查询的性能，如以下两个示例所述。

使用日期范围的查询

此示例显示了如何使用 RLE 对数据进行编码，以及更改投影排序顺序以提升检索给定日期范围内所有数据的查询的性能。

假设您的查询类似于如下内容：

=> SELECT * FROM trades
   WHERE trade_date BETWEEN '2016-11-01' AND '2016-12-01';

要优化此查询，请确定所有投影是否可以及时执行 SELECT 操作。为每个投影运行指定了日期范围的 SELECT COUNT(*) 语句，并记下响应时间。例如：

=> SELECT COUNT(*) FROM [ projection_name ]
   WHERE trade_date BETWEEN '2016-11-01' AND '2016-12-01;

如果一个或多个查询很慢，请检查 trade_date 列的唯一性，并确定它是否需要位于投影的 ORDER BY 子句中和/或是否可以使用 RLE 进行编码。RLE 会将列中的相同数据值的序列替换为代表值和计数的配对。要获得最佳效果，请从最低基数到最高基数对投影中的列进行排序，并使用 RLE 对低基数列中的数据进行编码。

注意

有关使用排序和 RLE 的示例，请参阅选择排序顺序：最佳实践。

如果唯一列的数量未经过排序，或者重复行的平均数量小于 10，那么 trade_date 会因为太过于接近而难以变成唯一，而且不能使用 RLE 进行编码。在这种情况下，请添加新列以最大程度缩小搜索范围。

以下示例会添加一个新列 trade_year：

确定新列 trade_year 是否返回了可管理的结果集。以下查询返回了按 trade_year 进行分组的数据：

=> SELECT DATE_TRUNC('trade_year', trade_date), COUNT(*)
   FROM trades
   GROUP BY DATE_TRUNC('trade_year',trade_date);

假设 trade_year = 2007 接近 8k，请将 trade_year 的列添加到 trades 表。然后，SELECT 语句就会变为：
```
=> SELECT * FROM trades
   WHERE trade_year = 2007
   AND trade_date BETWEEN '2016-11-01' AND '2016-12-01';
```
因此，您会具有对 trade_year 进行排序的投影，而且它可以使用 RLE 进行编码。

具有高基数主键的表的查询

此示例说明了您可以如何修改投影来提升从具有高基数主键的表中选择数据的查询的性能。

假设您有这样的一个查询：

=> SELECT FROM [table]
   WHERE pk IN (12345, 12346, 12347,...);

由于主键是高基数列，因此 Vertica 必须搜索大量数据。

要优化此查询的架构，请创建名为 buckets 的新列，并向其分配主键除以 10000 的值。在此示例中，buckets=(int) pk/10000。请按如下所示使用 buckets 列限制搜索范围：

=> SELECT FROM [table]
   WHERE buckets IN (1,...)
   AND pk IN (12345, 12346, 12347,...);

创建较低基数的列并将其添加到查询会限制搜索范围并提升查询性能。此外，如果您创建 buckets 在排序顺序中位于第一位的投影，查询可能会运行地更快。

4 - GROUP BY 查询

以下章节中包含的示例展示了如何设计投影以优化 GROUP BY 查询的性能。

4.1 - GROUP BY 实施选项

Vertica 将使用以下算法之一来实施查询 GROUP BY 子句：GROUPBY PIPELINED 或 GROUPBY HASH。这两种算法会返回相同的结果。对于返回少量不同组（通常每个节点一千个）的查询，两者的性能基本相似。

您可以使用 EXPLAIN 来确定查询优化器为给定查询选择的算法。以下条件通常决定选择哪种算法：

GROUPBY PIPELINED 要求在投影的 ORDER BY 子句中指定所有 GROUP BY 数据。有关详细信息，请参阅下面的 GROUPBY PIPELINED 要求。

由于 GROUPBY PIPELINED 只需在内存中保留当前组数据，因此相比 GROUPBY HASH，该算法通常需要的内存更少且执行速度更快。对于聚合大量不同组的查询，性能改进尤为显著。
GROUPBY HASH 将用于任何不符合 GROUPBY PIPELINED 排序顺序要求的查询。在这种情况下，Vertica 必须先对各个 GROUP BY 列构建哈希表，然后才能开始对数据进行分组。

GROUPBY PIPELINED 要求

通过确保查询及其投影之一符合 GROUPBY PIPELINED 要求，可以启用 GROUPBY PIPELINED 算法。以下条件适用于 GROUPBY PIPELINED。如果查询不满足任何条件，则 Vertica 使用 GROUPBY HASH。

后续所有示例均假定使用此架构：

CREATE TABLE sortopt (
    a INT NOT NULL,
    b INT NOT NULL,
    c INT,
    d INT
);
CREATE PROJECTION sortopt_p (
   a_proj,
   b_proj,
   c_proj,
   d_proj )
AS SELECT * FROM sortopt
ORDER BY a,b,c
UNSEGMENTED ALL NODES;
INSERT INTO sortopt VALUES(5,2,13,84);
INSERT INTO sortopt VALUES(14,22,8,115);
INSERT INTO sortopt VALUES(79,9,401,33);

条件 1

所有 GROUP BY 列也包含在投影 ORDER BY 子句中。例如：

条件 2

如果查询的 GROUP BY 子句比投影的 ORDER BY 子句拥有的列数少，则 GROUP BY 列必须：

是连续的 ORDER BY 列的子集。
包含第一个 ORDER BY 列。

例如：

条件 3

如果查询的 GROUP BY 列不是先出现在投影的 GROUP BY 子句中，则在查询的 GROUP BY 子句中缺失的任何先出现的投影排序列必须在查询的 WHERE 子句中显示为单列恒定相等谓词。

例如：

控制选择的 GROUPBY 算法

通常最好让 Vertica 来确定最适合用于给定查询的 GROUP BY 算法。有时，您可能希望使用某种算法，而不是另一种算法。在这种情况下，您可以使用 GBYTYPE 提示来限定 GROUP BY 子句：

GROUP BY /*+ GBYTYPE( HASH | PIPE ) */

例如，指定以下查询后，查询优化器将使用 GROUPBY PIPELINED 算法：

=> EXPLAIN SELECT SUM(a) FROM sortopt GROUP BY a,b;
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT SUM(a) FROM sortopt GROUP BY a,b;

 Access Path:
 +-GROUPBY PIPELINED [Cost: 11, Rows: 3 (NO STATISTICS)] (PATH ID: 1)
 |  Aggregates: sum(sortopt.a)
 |  Group By: sortopt.a, sortopt.b

...

您可以使用 GBYTYPE 提示来强制查询优化器使用 GROUPBY HASH 算法：

=> EXPLAIN SELECT SUM(a) FROM sortopt GROUP BY /*+GBYTYPE(HASH) */ a,b;
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT SUM(a) FROM sortopt GROUP BY /*+GBYTYPE(HASH) */ a,b;

 Access Path:
 +-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 11, Rows: 3 (NO STATISTICS)] (PATH ID: 1)
 |  Aggregates: sum(sortopt.a)
 |  Group By: sortopt.a, sortopt.b

...

仅当查询及其投影之一符合 GROUPBY PIPELINED 要求时，GBYTYPE 提示才能指定 PIPE（GROUPBY PIPELINED 算法）实参。否则，Vertica 会发出警告并使用 GROUPBY HASH。

例如，以下查询不能使用 GROUPBY PIPELINED 算法，因为 GROUP BY 列 {b,c} 不包含投影的第一个 ORDER BY 列 a：

=> SELECT SUM(a) FROM sortopt GROUP BY /*+GBYTYPE(PIPE) */ b,c;
WARNING 7765:  Cannot apply Group By Pipe algorithm. Proceeding with Group By Hash and hint will be ignored
 SUM
-----
  79
  14
   5
(3 rows)

4.2 - 使用投影设计避免在 GROUP BY 优化期间重新分段

要计算包含 GROUP BY 子句的查询的正确结果，Vertica 必须确保 GROUP BY 表达式中具有相同值的所有行最终都会在相同节点上以便于最终计算。如果投影设计已经确保数据按 GROUP BY 列进行分段，那么运行时则不需要重新分段。

要避免重新分段，GROUP BY 子句必须包含投影的所有分段列，但是它也可以包含其他列。

当查询包括 GROUP BY 子句和联接时，如果联接取决于以下示例所示的 GROUP BY 的结果，那么 Vertica 会首先执行 GROUP BY：

=> EXPLAIN SELECT * FROM (SELECT b from foo GROUP BY b) AS F, foo WHERE foo.a = F.b;

Access Path:
+-JOIN MERGEJOIN(inputs presorted) [Cost: 649, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
|  Join Cond: (foo.a = F.b)
|  Materialize at Output: foo.b
|  Execute on: All Nodes
| +-- Outer -> STORAGE ACCESS for foo [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 2)
| |      Projection: public.foo_super
| |      Materialize: foo.a
| |      Execute on: All Nodes
| |      Runtime Filter: (SIP1(MergeJoin): foo.a)
| +-- Inner -> SELECT [Cost: 245, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
| |      Execute on: All Nodes
| | +---> GROUPBY HASH (SORT OUTPUT) (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 245, Rows: 10K (NO STATISTICS)] (PATH ID:
4)
| | |      Group By: foo.b
| | |      Execute on: All Nodes
| | | +---> STORAGE ACCESS for foo [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 5)
| | | |      Projection: public.foo_super
| | | |      Materialize: foo.b
| | | |      Execute on: All Nodes

如果联接操作的结果是 GROUP BY 子句的输入，Vertica 会首先执行联接，如以下示例所示。这些中间结果的分段可能不会与查询的 GROUP BY 子句保持一致，从而导致运行时要重新分段。

=> EXPLAIN SELECT * FROM foo AS F, foo WHERE foo.a = F.b GROUP BY 1,2,3,4;

Access Path:
+-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 869, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
|  Group By: F.a, F.b, foo.a, foo.b
|  Execute on: All Nodes
| +---> JOIN HASH [Cost: 853, Rows: 10K (NO STATISTICS)] (PATH ID: 2) Outer (RESEGMENT)(LOCAL ROUND ROBIN)
| |      Join Cond: (foo.a = F.b)
| |      Execute on: All Nodes
| | +-- Outer -> STORAGE ACCESS for F [Cost: 403, Rows: 10K (NO STATISTICS)] (PUSHED GROUPING) (PATH ID: 3)
| | |      Projection: public.foo_super
| | |      Materialize: F.a, F.b
| | |      Execute on: All Nodes
| | +-- Inner -> STORAGE ACCESS for foo [Cost: 403, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
| | |      Projection: public.foo_super
| | |      Materialize: foo.a, foo.b
| | |      Execute on: All Nodes

如果查询不包括联接，GROUP BY 子句会使用现有数据库投影进行处理。

示例

假设有以下投影：

CREATE PROJECTION ... SEGMENTED BY HASH(a,b) ALL NODES

下表说明了运行时是否会发生重新分段以及相关原因。

要确定在 GROUP BY 查询期间是否会发生重新分段，请查看 EXPLAIN 生成的查询计划。

例如，以下计划使用了 GROUPBY PIPELINED 排序优化，并需要重新分段以执行 GROUP BY 计算：

+-GROUPBY PIPELINEDRESEGMENT GROUPS[Cost: 194, Rows: 10K (NO STATISTICS)] (PATH ID: 1)

以下计划使用了 GROUPBY PIPELINED 排序优化，但是不需要重新分段：

+-GROUPBY PIPELINED [Cost: 459, Rows: 10K (NO STATISTICS)] (PATH ID: 1)

5 - SELECT 查询列表中的 DISTINCT

本节介绍了如何优化在其 SELECT 列表中具有 DISTINCT 关键字的查询。优化 DISTINCT 查询的技术类似于优化 GROUP BY 查询的技术，因为处理使用 DISTINCT 的查询时，Vertica 优化器会将查询重写为 GROUP BY 查询。

本页下的各节介绍了各种具体情况：

这几节中的示例使用了以下表：

=> CREATE TABLE table1 (
    a INT,
    b INT,
    c INT
);

5.1 - 查询在 SELECT 列表中没有聚合

如果查询内部的 SELECT 列表中没有聚合，Vertica 会将查询视为使用了 GROUP BY 的查询进行处理。

例如，您可以将以下查询：

SELECT DISTINCT a, b, c FROM table1;

重写为：

SELECT a, b, c FROM table1 GROUP BY a, b, c;

为了最大限度提高执行速度，请为 GROUP BY 查询应用GROUP BY 查询中所述的优化技术。

5.2 - COUNT (DISTINCT) 和其他 DISTINCT 聚合

计算 DISTINCT 聚合通常需要比其他聚合更多的工作。另外，与具有多个 DISTINCT 聚合的查询相比，使用单个 DISTINCT 聚合的查询使用的资源较少。

提示

当所有不同聚合列具有相似数量的不同值时，Vertica 会更高效地执行具有多个不同聚合的查询。

以下查询会返回 date_dimension 表的 date_key 列中不同值的数量：

=> SELECT COUNT (DISTINCT date_key) FROM date_dimension;


 COUNT
-------
  1826
(1 row)

此示例会返回对所有 inventory_fact 记录计算表达式 x+y 得到的所有不同值。

=> SELECT COUNT (DISTINCT date_key + product_key) FROM inventory_fact;


 COUNT
-------
 21560
(1 row)

可以使用 LIMIT 关键字限制返回的行数来创建等同的查询：

=> SELECT COUNT(date_key + product_key) FROM inventory_fact GROUP BY date_key LIMIT 10;

此查询会返回具有特定不同 date_key 值的所有记录中不同 product_key 值的数量。

=> SELECT product_key, COUNT (DISTINCT date_key)  FROM  inventory_fact
   GROUP BY product_key LIMIT 10;


 product_key | count
-------------+-------
           1 |    12
           2 |    18
           3 |    13
           4 |    17
           5 |    11
           6 |    14
           7 |    13
           8 |    17
           9 |    15
          10 |    12
(10 rows)

该查询通过常数 1 对 product_key 表中每个不同的 inventory_fact 值进行计数。

=> SELECT product_key, COUNT (DISTINCT product_key) FROM inventory_fact
   GROUP BY product_key LIMIT 10;


 product_key | count
-------------+-------
           1 |     1
           2 |     1
           3 |     1
           4 |     1
           5 |     1
           6 |     1
           7 |     1
           8 |     1
           9 |     1
          10 |     1
(10 rows)

该查询会选择每个不同的 date_key 值，并针对具有特定 product_key 值的所有记录为不同的product_key 值进行计数。然后，它会对具有特定 qty_in_stock 值的所有记录中的 product_key 值进行求和，并按照 date_key 对结果进行分组。

=> SELECT date_key, COUNT (DISTINCT product_key), SUM(qty_in_stock) FROM inventory_fact
   GROUP BY date_key LIMIT 10;


 date_key | count |  sum
----------+-------+--------
        1 |   173 |  88953
        2 |    31 |  16315
        3 |   318 | 156003
        4 |   113 |  53341
        5 |   285 | 148380
        6 |    84 |  42421
        7 |   241 | 119315
        8 |   238 | 122380
        9 |   142 |  70151
       10 |   202 |  95274
(10 rows)

该查询会选择每个不同的 product_key 值，然后针对具有特定 date_key 值的所有记录为不同的product_key 值进行计数。它还会对具有特定 warehouse_key 值的所有记录中的不同 product_key 值进行计数。

=> SELECT product_key, COUNT (DISTINCT date_key), COUNT (DISTINCT warehouse_key) FROM inventory_fact
   GROUP BY product_key LIMIT 15;


 product_key | count | count
-------------+-------+-------
           1 |    12 |    12
           2 |    18 |    18
           3 |    13 |    12
           4 |    17 |    18
           5 |    11 |     9
           6 |    14 |    13
           7 |    13 |    13
           8 |    17 |    15
           9 |    15 |    14
          10 |    12 |    12
          11 |    11 |    11
          12 |    13 |    12
          13 |     9 |     7
          14 |    13 |    13
          15 |    18 |    17
(15 rows)

该查询会选择每个不同的 product_key 值，为具有特定 date_key 值的所有记录的不同 warehouse_key 和 product_key 值进行计数，然后对具有特定 qty_in_stock 值的记录中的所有 product_key 值进行求和。然后，它会返回具有特定 product_version 值的记录中 product_key 值的数量。

=> SELECT product_key, COUNT (DISTINCT date_key),
      COUNT (DISTINCT warehouse_key),
      SUM (qty_in_stock),
      COUNT (product_version)
      FROM inventory_fact GROUP BY product_key LIMIT 15;


 product_key | count | count |  sum  | count
-------------+-------+-------+-------+-------
           1 |    12 |    12 |  5530 |    12
           2 |    18 |    18 |  9605 |    18
           3 |    13 |    12 |  8404 |    13
           4 |    17 |    18 | 10006 |    18
           5 |    11 |     9 |  4794 |    11
           6 |    14 |    13 |  7359 |    14
           7 |    13 |    13 |  7828 |    13
           8 |    17 |    15 |  9074 |    17
           9 |    15 |    14 |  7032 |    15
          10 |    12 |    12 |  5359 |    12
          11 |    11 |    11 |  6049 |    11
          12 |    13 |    12 |  6075 |    13
          13 |     9 |     7 |  3470 |     9
          14 |    13 |    13 |  5125 |    13
          15 |    18 |    17 |  9277 |    18
(15 rows)

以下示例会返回 warehouse 维度表中的仓库数。

=> SELECT COUNT(warehouse_name) FROM warehouse_dimension;


 COUNT
-------
   100
(1 row)

下一个示例会返回供应商总数：

=> SELECT COUNT(*) FROM vendor_dimension;


 COUNT
-------
    50
(1 row)

5.3 - 近似计数区分函数

聚合函数 COUNT(DISTINCT) 计算数据集中非重复值的确切数量。COUNT(DISTINCT) 在使用 GROUPBY PIPELINED 算法执行计算时表现良好。

当满足以下条件时，聚合 COUNT 操作在数据集上表现良好：

目标表投影之一包含 ORDER BY 子句，便于对聚合进行排序。
非重复值的数量非常小。
需要使用哈希聚合来执行查询。

或者，当满足以下条件时，考虑使用 APPROXIMATE_COUNT_DISTINCT 函数而不是 COUNT(DISTINCT)：

您有一个大型数据集，而且您不需要获得非重复值的确切数量。
COUNT(DISTINCT) 对于给定数据集的性能是不足的。
您在同一个查询中计算几个非重复值计数。
COUNT(DISTINCT) 的计划使用哈希聚合。

APPROXIMATE_COUNT_DISTINCT 返回的预期值等于 COUNT(DISTINCT)，误差呈对数正态分布，标准差为 s。您可以通过设置函数的可选误差容限实参来控制标准偏差（默认情况下为 1.25%）。

其他 APPROXIMATE_COUNT_DISTINCT 函数

下面是 Vertica 支持您一起使用的其他两个函数（而不是 APPROXIMATE_COUNT_DISTINCT）： APPROXIMATE_COUNT_DISTINCT_SYNOPSIS 和 APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS。当满足以下条件时使用这些函数：

您有一个大型数据集，而且您不需要获得非重复值的确切数量。
COUNT(DISTINCT) 对于给定数据集的性能是不足的。
您希望预先计算非重复值计数，并在之后使用不同的方法合并它们。

将这两个函数一起使用，如下所示：

向 APPROXIMATE_COUNT_DISTINCT_SYNOPSIS 传递数据集以及正态分布的置信区间。此函数会返回称为“二进制概要对象”的数据子集。
将概要传递给 APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS 函数，然后对概要计算非重复值近似计数。

您还可以使用 APPROXIMATE_COUNT_DISTINCT_SYNOPSIS_MERGE，它将多个概要合并为一个概要。使用此功能，您可以通过合并一个或多个涵盖最近、较短时间段的概要来不断更新“主”概要。

示例

以下示例显示如何使用 APPROXIMATE_COUNT_DISTINCT 函数来保持给定时间段内单击给定网页的用户的大致计数。

创建 pviews 表来存储有关网站访问的数据（访问时间、访问的网页和访问者）：
```
=> CREATE TABLE pviews(
visit_time TIMESTAMP NOT NULL,
page_id INTEGER NOT NULL,
user_id INTEGER NOT NULL)
ORDER BY page_id, visit_time
SEGMENTED BY HASH(user_id) ALL NODES KSAFE
PARTITION BY visit_time::DATE GROUP BY CALENDAR_HIERARCHY_DAY(visit_time::DATE, 2, 2);
```
pviews 通过散列 user_id 数据进行分段，因此给定用户的所有访问都存储在同一节点的同一段上。这可以防止稍后执行 COUNT (DISTINCT user_id) 时数据低效跨节点传输。

该表还在访问时使用分层分区来优化 ROS 存储。这样做可以提高按时间筛选数据时的性能。

将数据加载到 pviews 中：

=> INSERT INTO pviews VALUES
     ('2022-02-01 10:00:02',1002,1),
     ('2022-02-01 10:00:03',1002,2),
     ('2022-02-01 10:00:04',1002,1),
     ('2022-02-01 10:00:05',1002,3),
     ('2022-02-01 10:00:01',1000,1),
     ('2022-02-01 10:00:06',1002,1),
     ('2022-02-01 10:00:07',1002,3),
     ('2022-02-01 10:00:08',1002,1),
     ('2022-02-01 10:00:09',1002,3),
     ('2022-02-01 10:00:12',1002,2),
     ('2022-02-02 10:00:01',1000,1),
     ('2022-02-02 10:00:02',1002,4),
     ('2022-02-02 10:00:03',1002,2),
     ('2022-02-02 10:00:04',1002,1),
     ('2022-02-02 10:00:05',1002,3),
     ('2022-02-02 10:00:06',1002,4),
     ('2022-02-02 10:00:07',1002,3),
     ('2022-02-02 10:00:08',1002,4),
     ('2022-02-02 10:00:09',1002,3),
     ('2022-02-02 10:00:12',1002,2),
     ('2022-03-02 10:00:01',1000,1),
     ('2022-03-02 10:00:02',1002,1),
     ('2022-03-02 10:00:03',1002,2),
     ('2022-03-02 10:00:04',1002,1),
     ('2022-03-02 10:00:05',1002,3),
     ('2022-03-02 10:00:06',1002,4),
     ('2022-03-02 10:00:07',1002,3),
     ('2022-03-02 10:00:08',1002,6),
     ('2022-03-02 10:00:09',1002,5),
     ('2022-03-02 10:00:12',1002,2),
     ('2022-03-02 11:00:01',1000,5),
     ('2022-03-02 11:00:02',1002,6),
     ('2022-03-02 11:00:03',1002,7),
     ('2022-03-02 11:00:04',1002,4),
     ('2022-03-02 11:00:05',1002,1),
     ('2022-03-02 11:00:06',1002,6),
     ('2022-03-02 11:00:07',1002,8),
     ('2022-03-02 11:00:08',1002,6),
     ('2022-03-02 11:00:09',1002,7),
     ('2022-03-02 11:00:12',1002,1),
     ('2022-03-03 10:00:01',1000,1),
     ('2022-03-03 10:00:02',1002,2),
     ('2022-03-03 10:00:03',1002,4),
     ('2022-03-03 10:00:04',1002,1),
     ('2022-03-03 10:00:05',1002,2),
     ('2022-03-03 10:00:06',1002,6),
     ('2022-03-03 10:00:07',1002,9),
     ('2022-03-03 10:00:08',1002,10),
     ('2022-03-03 10:00:09',1002,7),
     ('2022-03-03 10:00:12',1002,1);
 OUTPUT
--------
     50
(1 row)

=> COMMIT;
COMMIT

通过使用 CREATE TABLE...AS SELECT 查询 pviews 来创建 pview_summary 表。此表的每一行汇总了从 pviews 中选择的给定日期的数据：
- partial_visit_count 在 pviews 中存储具有该日期的行数（网站访问）。
- daily_users_acdp 使用 APPROXIMATE_COUNT_DISTINCT_SYNOPSIS 构建一个概要，该概要近似计算在该日期访问该网站的不同用户 (user_id) 的数量。
```
=> CREATE TABLE pview_summary AS SELECT
      visit_time::DATE "date",
      COUNT(*) partial_visit_count,
      APPROXIMATE_COUNT_DISTINCT_SYNOPSIS(user_id) AS daily_users_acdp
   FROM pviews GROUP BY 1;
CREATE TABLE
=> ALTER TABLE pview_summary ALTER COLUMN "date" SET NOT NULL;
```

更新 pview_summary 表，使其像 pviews 一样进行分区。REORGANIZE 关键字强制表数据的立即重新分区：

=> ALTER TABLE pview_summary
     PARTITION BY "date"
     GROUP BY CALENDAR_HIERARCHY_DAY("date", 2, 2) REORGANIZE;
vsql:/home/ale/acd_ex4.sql:93: NOTICE 8364:  The new partitioning scheme will produce partitions in 2 physical storage containers per projection
vsql:/home/ale/acd_ex4.sql:93: NOTICE 4785:  Started background repartition table task
ALTER TABLE

使用 CREATE TABLE...LIKE 创建两个 ETL 表 pviews_etl 和 pview_summary_etl，它们分别具有与 pviews 和 pview_summary 相同的 DDL。这些表用于处理传入数据：
```
=> CREATE TABLE pviews_etl LIKE pviews INCLUDING PROJECTIONS;
CREATE TABLE
=> CREATE TABLE pview_summary_etl LIKE pview_summary INCLUDING PROJECTIONS;
CREATE TABLE
```

将新数据加载到 pviews_etl：

=> INSERT INTO pviews_etl VALUES
     ('2022-03-03 11:00:01',1000,8),
     ('2022-03-03 11:00:02',1002,9),
     ('2022-03-03 11:00:03',1002,1),
     ('2022-03-03 11:00:04',1002,11),
     ('2022-03-03 11:00:05',1002,10),
     ('2022-03-03 11:00:06',1002,12),
     ('2022-03-03 11:00:07',1002,3),
     ('2022-03-03 11:00:08',1002,10),
     ('2022-03-03 11:00:09',1002,1),
     ('2022-03-03 11:00:12',1002,1);
 OUTPUT
--------
     10
(1 row)

=> COMMIT;
COMMIT

在 pview_summary_etl 中汇总新数据：

=> INSERT INTO pview_summary_etl SELECT
      visit_time::DATE visit_date,
      COUNT(*) partial_visit_count,
      APPROXIMATE_COUNT_DISTINCT_SYNOPSIS(user_id) AS daily_users_acdp
    FROM pviews_etl GROUP BY visit_date;
 OUTPUT
--------
      1
(1 row)

使用 COPY_PARTITIONS_TO_TABLE 将 pviews_etl 数据附加到 pviews：

=> SELECT COPY_PARTITIONS_TO_TABLE('pviews_etl', '01-01-0000'::DATE, '01-01-9999'::DATE, 'pviews');
              COPY_PARTITIONS_TO_TABLE
----------------------------------------------------
 1 distinct partition values copied at epoch 1403.

(1 row)

=> SELECT COPY_PARTITIONS_TO_TABLE('pview_summary_etl', '01-01-0000'::DATE, '01-01-9999'::DATE, 'pview_summary');
              COPY_PARTITIONS_TO_TABLE
----------------------------------------------------
 1 distinct partition values copied at epoch 1404.

(1 row)

为所有数据（包括刚刚从 pviews_etl 复制的分区）创建视图和不同（近似）视图：

=> SELECT
     "date" visit_date,
     SUM(partial_visit_count) visit_count,
     APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS(daily_users_acdp) AS daily_users_acd
   FROM pview_summary GROUP BY visit_date ORDER BY visit_date;
 visit_date | visit_count | daily_users_acd
------------+-------------+-----------------
 2022-02-01 |          10 |               3
 2022-02-02 |          10 |               4
 2022-03-02 |          20 |               8
 2022-03-03 |          20 |              11
(4 rows)

按月创建视图和不同（近似）视图：

=> SELECT
     DATE_TRUNC('MONTH', "date")::DATE "month",
     SUM(partial_visit_count) visit_count,
     APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS(daily_users_acdp) AS monthly_users_acd
   FROM pview_summary GROUP BY month ORDER BY month;
   month    | visit_count | monthly_users_acd
------------+-------------+-------------------
 2022-02-01 |          20 |                 4
 2022-03-01 |          40 |                12
(2 rows)

将每日概要合并到每月概要：

=> CREATE TABLE pview_monthly_summary AS SELECT
     DATE_TRUNC('MONTH', "date")::DATE "month",
     SUM(partial_visit_count) partial_visit_count,
     APPROXIMATE_COUNT_DISTINCT_SYNOPSIS_MERGE(daily_users_acdp) AS monthly_users_acdp
   FROM pview_summary GROUP BY month ORDER BY month;
CREATE TABLE

按月创建根据合并的概要生成的视图和不同的视图：

=> SELECT
     month,
     SUM(partial_visit_count) monthly_visit_count,
     APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS(monthly_users_acdp) AS monthly_users_acd
   FROM pview_monthly_summary GROUP BY month ORDER BY month;
   month    | monthly_visit_count | monthly_users_acd
------------+---------------------+-------------------
 2019-02-01 |                  20 |                 4
 2019-03-01 |                  40 |                12
(2 rows)

您可以使用每月摘要生成年度摘要。当需要处理大量数据时，这种方法可能比使用每日摘要快：

=> SELECT
     DATE_TRUNC('YEAR', "month")::DATE "year",
     SUM(partial_visit_count) yearly_visit_count,
     APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS(monthly_users_acdp) AS yearly_users_acd
   FROM pview_monthly_summary GROUP BY year ORDER BY year;
    year    | yearly_visit_count | yearly_users_acd
------------+--------------------+------------------
 2022-01-01 |                 60 |               12
(1 row)

删除 ETL 表：

=> DROP TABLE IF EXISTS pviews_etl, pview_summary_etl;
DROP TABLE

另请参阅

5.4 - 单个 DISTINCT 聚合

计算 DISTINCT 聚合时，Vertica 首先会删除聚合参数中的所有重复值，从而找到非重复值。然后，它会计算聚合。

例如，您可以将以下查询：

SELECT a, b, COUNT(DISTINCT c) AS dcnt FROM table1 GROUP BY a, b;

重写为：

SELECT a, b, COUNT(dcnt) FROM
  (SELECT a, b, c AS dcnt FROM table1 GROUP BY a, b, c)
GROUP BY a, b;

为了最大限度提高执行速度，请为 GROUP BY 查询应用优化技术。

5.5 - 多个 DISTINCT 聚合

如果查询具有多个 DISTINCT 聚合，则无法通过简单地重写 SQL 来计算它们。以下查询就无法通过简单地重写来提高性能：

SELECT a, COUNT(DISTINCT b), COUNT(DISTINCT c) AS dcnt FROM table1 GROUP BY a;

对于具有多个 DISTINCT 聚合的查询，没有一种投影设计可以避免使用 GROUPBY HASH 以及对数据进行重新分段。要提升此查询的性能，请确保它具有大量内存可用。有关为查询分配内存的详细信息，请参阅资源管理器。

6 - JOIN 查询

通常，可以通过以下几种方式优化联接多个表的查询的执行：

为按联接谓词列排序的联接表创建投影。这有助于使用合并联接算法，该算法通常比哈希联接算法更高效地联接表。
创建在联接键上相同分段的投影。

其他最佳实践

如果满足以下条件，Vertica 还会更高效地执行联接：

查询构造使查询优化器能够创建计划，其中较大的表被定义为外部输入。
等式谓词两边的列来自同一个表。例如，在以下查询中，等式谓词的左侧和右侧分别仅包含表 T 和 X 中的列：
```
=> SELECT * FROM T JOIN X ON T.a + T.b = X.x1 - X.x2;
```
相反，以下查询需要处理更多工作，因为谓词的右侧包含表 T 和 X 中的列：
```
=> SELECT * FROM T JOIN X WHERE T.a = X.x1 + T.b
```

6.1 - 哈希联接与合并联接

Vertica 优化器使用以下算法之一实施联接：

当联接表的投影已在联接列中排序时，使用合并联接。与哈希联接相比，合并联接较快并且使用较少的内存。
当联接表的投影尚未在联接列上排序时，使用哈希联接。在这种情况下，优化器会在内表的联接列上构建一个内存中的哈希表。优化器然后扫描外部表以查找与哈希表的匹配，并相应地联接两个表中的数据。如果整个哈希表可以拟合到内存中，则执行哈希联接的成本将非常低。如果哈希表必须写入磁盘，成本会显着增加。

在给定可用的投影的情况下，优化器会自动选择最合适的算法来执行查询。

促进合并联接

为了促进合并联接，请为联接表创建在联接谓词列上排序的投影。联接谓词列应该是 ORDER BY 子句中的第一列。

例如，表 first 和 second 定义如下，分别带有投影 first_p1 和 second_p1。投影在 data_first 和 data_second 上排序：

CREATE TABLE first ( id INT, data_first INT );
CREATE PROJECTION first_p1 AS SELECT * FROM first ORDER BY data_first;

CREATE TABLE second ( id INT, data_second INT );
CREATE PROJECTION second_p1 AS SELECT * FROM second ORDER BY data_second;

当您在未排序的列 first.id 和 second.id 上联接这些表时，Vertica 使用哈希联接算法：

 EXPLAIN SELECT first.data_first, second.data_second FROM first JOIN second ON first.id = second.id;

 Access Path:
 +-JOIN HASH [Cost: 752, Rows: 300K] (PATH ID: 1) Inner (BROADCAST)

您可以创建投影 first_p2 和 second_p2（它们分别在联接列 first_p2.id 和 second_p2.id 上排序），从而使用合并联接算法促进执行此查询：


CREATE PROJECTION first_p2 AS SELECT id, data_first FROM first ORDER BY id SEGMENTED BY hash(id, data_first) ALL NODES;
CREATE PROJECTION second_p2 AS SELECT id, data_second FROM second ORDER BY id SEGMENTED BY hash(id, data_second) ALL NODES;

如果查询联接了大量数据，则查询优化器使用合并算法：

EXPLAIN SELECT first.data_first, second.data_second FROM first JOIN second ON first.id = second.id;

 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 731, Rows: 300K] (PATH ID: 1) Inner (BROADCAST)

您还可以使用子查询对联接谓词列进行预排序，从而促进合并联接。例如：

SELECT first.id, first.data_first, second.data_second FROM
  (SELECT * FROM first ORDER BY id ) first JOIN (SELECT * FROM second ORDER BY id) second ON first.id = second.id;

6.2 - 相同分段

要在联接多个表时提升查询性能，请创建在联接键上进行相同分段的投影。使用相同分段的投影时，联接可以在每个节点本地进行，因此有助于在查询处理期间减少整个网络中的数据移动。

要确定投影是否在查询联接键中进行了相同分段，请创建具有 EXPLAIN 的查询计划。如果查询计划包括 RESEGMENT 或 BROADCAST，则表示投影没有进行相同分段。

Vertica 优化器会选择投影为查询中的每个表提供行。如果要联接的投影进行了分段，那么优化器则会根据查询联接表达式评估其分段。因此，它会确定行是否放置在每个节点上，以便于它联接行，而无需从其他节点获取数据。

相同分段的投影的联接条件

如果 p 的分段表达式的所有列引用是联接表达式中的列的子集，那么投影 p 会在联接列上进行分段。

如果表 t1 的分段投影 p1 和表 t2 的分段投影 p2 这两个分段投影要使 t1 联接到 t2，则必须满足以下条件：

联接条件必须采用以下形式：

t1.j1 = t2.j1 AND t1.j2 = t2.j2 AND ... t1.jN = t2.jN

联接列必须共享相同的基本数据类型。例如：
- 如果 t1.j1 是 INTEGER，则 t2.j1 可以是 INTEGER，但它不能是 FLOAT。
- 如果 t1.j1 是 CHAR(10)，则 t2.j1 可以是任何 CHAR 或 VARCHAR（例如，CHAR(10)、VARCHAR(10)、VARCHAR(20)），但是 t2.j1 不能是 INTEGER。
如果 p1 按列 {t1.s1, t1.s2, ... t1.sN} 上的表达式分段，则每个分段列 t1.sX 必须位于联接列集 {t1.jX} 中。
如果 p2 按列 {t2.s1, t2.s2, ... t2.sN} 上的表达式分段，则每个分段列 t2.sX 必须位于联接列集 {t2.jX} 中。
p1 和 p2 的分段表达式必须在结构上等效。例如：
- 如果 p1 是 SEGMENTED BY hash(t1.x) 并且 p2 是 SEGMENTED BY hash(t2.x)，那么 p1 和 p2 进行了相同分段。
- 如果 p1 是 SEGMENTED BY hash(t1.x) 并且 p2 是 SEGMENTED BY hash(t2.x + 1)，那么 p1 和 p2 没有进行相同分段。
p1 和 p2 必须有相同的分段计数。
向节点分配的分段必须匹配。例如，如果 p1 和 p2 使用了 OFFSET 子句，它们的偏移必须匹配。
如果 Vertica 发现 t1 和 t2 的投影没有进行相同分段，必要时数据会在查询运行时在整个网络中重新分布。

提示
如果您创建了自定义设计，请尝试在条件允许时为联接使用分段投影。

示例

以下语句创建了两个表，并指定要创建相同分段：

=> CREATE TABLE t1 (id INT, x1 INT, y1 INT) SEGMENTED BY HASH(id, x1) ALL NODES;
=> CREATE TABLE t2 (id INT, x1 INT, y1 INT) SEGMENTED BY HASH(id, x1) ALL NODES;

以此设计为例，以下查询中的联接条件可以利用相同分段：

=> SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
=> SELECT * FROM t1 JOIN t2 ON t1.id = t2.id AND t1.x1 = t2.x1;

相反，以下查询中的联接条件需要重新分段：

=> SELECT * FROM t1 JOIN t2 ON t1.x1 = t2.x1;
=> SELECT * FROM t1 JOIN t2 ON t1.id = t2.x1;

另请参阅

6.3 - 联接可变长度字符串数据

当您在 VARCHAR 列上联接表时，Vertica 会计算缓冲联接列数据所需的存储空间。它通过采用以下两种方式之一设置列数据格式来完成此操作：

使用联接列元数据将列数据的大小调整为固定长度，并相应地缓冲。例如，给定一个定义为 VARCHAR(1000) 的列，Vertica 始终缓冲 1000 个字符。
使用联接列数据的实际长度，因此每个联接的缓冲区大小不同。例如，给定字符串 Xi、John 和 Amrita 的联接，Vertica 仅缓冲每个联接所需的存储空间 — 在本例中，分别为 2、4 和 6 个字节。

第二种方法可以提高联接查询性能。它还可以减少内存消耗，这有助于防止联接溢出并最大限度地减少从资源管理器借用内存的频率。通常，在联接列的定义大小显著超过其数据的平均长度的情况下，这些好处尤其显著。

设置和验证可变长度格式

您可以通过配置参数 JoinDefaultTupleFormat 控制 Vertica 如何在会话或数据库级别实施联接，或者通过 JFMT 提示控制针对单个查询实施联接。Vertica 支持除 merge 和 event series 联接之外的所有联接的可变长度格式。

使用 EXPLAIN VERBOSE 通过检查以下标志来验证给定查询是否使用可变字符格式：

JF_EE_VARIABLE_FORMAT
JF_EE_FIXED_FORMAT

7 - ORDER BY 查询

如果投影的 ORDER BY 子句中的列与查询中的列相同，那么您可以提升仅包含 ORDER BY 子句的查询的性能。

如果在 CREATE PROJECTION 语句中定义投影排序顺序，则 Vertica 查询优化器并不一定要在执行特定的 ORDER BY 查询之前对投影数据进行排序。

下面的 sortopt 表中包含 a、b、c 和 d 列。投影 sortopt_p 指定按 a、b 和 c 列排序。

CREATE TABLE sortopt (
    a INT NOT NULL,
    b INT NOT NULL,
    c INT,
    d INT
);
CREATE PROJECTION sortopt_p (
   a_proj,
   b_proj,
   c_proj,
   d_proj )
AS SELECT * FROM sortopt
ORDER BY a,b,c
UNSEGMENTED ALL NODES;
INSERT INTO sortopt VALUES(5,2,13,84);
INSERT INTO sortopt VALUES(14,22,8,115);
INSERT INTO sortopt VALUES(79,9,401,33);

根据这一排序顺序，如果 SELECT * FROM sortopt 查询中包含以下 ORDER BY 子句之一，则该查询不必对投影重新排序：

ORDER BY a
ORDER BY a, b
ORDER BY a, b, c

例如，在以下查询中，Vertica 不必对投影重新排序，因为其排序顺序包含了 CREATE PROJECTION..ORDER BY a, b, c 子句中指定的列，该子句镜像了该查询的 ORDER BY a, b, c 子句：

=> SELECT * FROM sortopt ORDER BY a, b, c;
 a  | b  |  c  |  d
----+----+-----+-----
  5 |  2 |  13 |  84
 14 | 22 |   8 | 115
 79 |  9 | 401 |  33
(3 rows)

如果在查询中包含 d 列，则 Vertica 必须对投影重新排序，因为列 d 并未在 CREATE PROJECTION..ORDER BY 子句中定义。因此，该 ORDER BY d 查询不会受益于任何排序优化。

您不能在 CREATE PROJECTION 语句的 ORDER BY 子句中指定 ASC 或 DESC 子句。Vertica 总是在物理存储中使用升序排序顺序，因此如果您的查询对其任何列指定了降序顺序，该查询仍然会导致 Vertica 对投影数据重新排序。例如，以下查询需要 Vertica 对结果进行排序：

=> SELECT * FROM sortopt ORDER BY a DESC, b, c;
 a  | b  |  c  |  d
----+----+-----+-----
 79 |  9 | 401 |  33
 14 | 22 |   8 | 115
  5 |  2 |  13 |  84
(3 rows)

另请参阅

CREATE PROJECTION

8 - 分析函数

以下几节介绍了如何优化 Vertica 支持的 SQL-99 分析函数。

8.1 - 空的 OVER 子句

OVER() 子句不需要窗口化子句。如果您的查询使用类似 SUM(x) 的分析函数，而且您指定了一个空 OVER() 子句，分析函数则会用作报告函数，其中整个输入会被视为单个分区；聚合会为结果集的每行返回相同的聚合值。查询在单个节点上执行，有可能导致性能低下。

如果您将 PARTITION BY 子句添加到 OVER() 子句，查询会在多个节点上执行，从而提升其性能。

8.2 - NULL 排序顺序

默认情况下，投影列值按升序存储，但是 NULL 值的位置取决于列的数据类型。

ORDER BY 子句的 NULL 位置差异

分析 OVER(window-order-clause) 与 SQL ORDER BY 子句的语义稍有不同：

OVER(ORDER BY ...)

分析窗口排序子句使用 ASC 或 DESC 排序顺序来确定分析函数结果的 NULLS FIRST 或 NULLS LAST 位置。NULL 值按如下方式放置：

ASC, NULLS LAST — NULL 值出现在排序结果的末尾。
DESC, NULLS FIRST — NULL 值出现在排序结果的开头。

(SQL) ORDER BY

SQL 和 Vertica ORDER BY 子句产生不同的结果。SQL ORDER BY 子句只指定升序或降序排序顺序。Vertica ORDER BY 子句根据列数据类型确定 NULL 位置：

NUMERIC、INTEGER、DATE、TIME、TIMESTAMP 和 INTERVAL 列： NULLS FIRST （NULL 值出现在排序投影的开头。）
FLOAT、STRING 和 BOOLEAN 列： NULLS LAST （NULL 值出现在排序投影的末尾。）

NULL 排序选项

在执行分析计算的查询中，如果不在意 NULL 的位置，或者您知道列中不包含任何 NULL 值，请指定 NULLS AUTO，而不考虑数据类型。Vertica 选择性能最快的放置，如以下查询所示。否则，请指定 NULLS FIRST 或 NULLS LAST。

=> SELECT x, RANK() OVER (ORDER BY x NULLS AUTO) FROM t;

如以下示例所示，您可以仔细构建查询，使 Vertica 避免对数据排序并提高查询速度。如 OVER(ORDER BY) 子句指定的那样，Vertica 会在列 x 上对来自表 t 的输入进行排序，然后对 RANK() 求值：

=> CREATE TABLE t (
    x FLOAT,
    y FLOAT );
=> CREATE PROJECTION t_p (x, y) AS SELECT * FROM t
   ORDER BY x, y UNSEGMENTED ALL NODES;
=> SELECT x, RANK() OVER (ORDER BY x) FROM t;

在之前的 SELECT 语句中，Vertica 不再使用 ORDER BY 子句，而且能迅速执行查询，因为列 x 是 FLOAT 数据类型。因此，投影排序顺序与分析默认排序 (ASC + NULLS LAST) 相匹配。当底层投影已经经过排序后，Vertica 也可以避免对数据进行排序。

但是，如果列 x 是 INTEGER 数据类型，则 Vertica 必须对数据进行排序，因为 INTEGER 数据类型的投影排序顺序 (ASC + NULLS FIRST) 与默认分析排序 (ASC + NULLS LAST) 不匹配。要帮助 Vertica 消除排序，请指定 NULL 的位置以与默认排序匹配：

=> SELECT x, RANK() OVER (ORDER BY x NULLS FIRST) FROM t;

如果列 x 是 STRING，以下查询就会消除排序：

=> SELECT x, RANK() OVER (ORDER BY x NULLS LAST) FROM t;

如果在之前的查询中忽略了 NULLS LAST，Vertica 会消除排序，因为 ASC + NULLS LAST 对于分析 ORDER BY 子句和 Vertica 中字符串相关的列来说是默认排序规范。

另请参阅

8.3 - 分析函数中 NULL 值的运行时排序

通过认真编写查询或创建设计（或同时开展这两项工作），您可以帮助 Vertica 查询优化器在执行分析函数时跳过对表中的所有列进行排序这项操作，从而提升查询性能。

要最大程度降低 Vertica 在查询执行期间对投影进行排序的需要，请重新定义 employee 表并指定排序字段不允许出现 NULL 值：

=> DROP TABLE employee CASCADE;
=> CREATE TABLE employee
   (empno INT,
    deptno INT NOT NULL,
    sal INT NOT NULL);
CREATE TABLE
=> CREATE PROJECTION employee_p AS
   SELECT * FROM employee
   ORDER BY deptno, sal;
CREATE PROJECTION
=> INSERT INTO employee VALUES(101,10,50000);
=> INSERT INTO employee VALUES(103,10,43000);
=> INSERT INTO employee VALUES(104,10,45000);
=> INSERT INTO employee VALUES(105,20,97000);
=> INSERT INTO employee VALUES(108,20,33000);
=> INSERT INTO employee VALUES(109,20,51000);
=> COMMIT;
COMMIT

=> SELECT * FROM employee;
 empno | deptno |  sal
-------+--------+-------
   101 |     10 | 50000
   103 |     10 | 43000
   104 |     10 | 45000
   105 |     20 | 97000
   108 |     20 | 33000
   109 |     20 | 51000
(6 rows)
=> SELECT deptno, sal, empno, RANK() OVER
     (PARTITION BY deptno ORDER BY sal)
   FROM employee;
 deptno |  sal  | empno | ?column?
--------+-------+-------+----------
     10 | 43000 |   103 |        1
     10 | 45000 |   104 |        2
     10 | 50000 |   101 |        3
     20 | 33000 |   108 |        1
     20 | 51000 |   109 |        2
     20 | 97000 |   105 |        3
(6 rows)

提示

在执行分析计算的查询中，如果不在意 NULL 的位置，或者您知道列中不包含 NULL 值，请NULLS AUTO在查询中.指定 Vertica 会尝试选择有助于实现最快性能的位置。否则，请指定 NULLS FIRST 或 NULLS LAST。

9 - LIMIT 查询

查询可以包含一个 LIMIT 子句，采用以下两种方式限制其结果集：

从整个结果集返回行的子集。
在结果集上设置窗口分区并限制每个窗口中的行数。

限制查询结果集

查询使用具有 ORDER BY 的 LIMIT 子句时，它会从查询数据集返回特定行子集。Vertica 使用数据库查询排名流程 Top-K 优化高效地处理这些查询。Top-K 优化避免对整个数据集进行排序（以及可能的磁盘写入）来找到少量的行。这可以极大地提升查询性能。

例如，以下查询返回表 customer_dimension 中的前 20 行数据，按 number_of_employees 排序：

=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees
     FROM store.store_dimension ORDER BY number_of_employees DESC LIMIT 20;
 store_region |       location       | store_name | number_of_employees
--------------+----------------------+------------+---------------------
 East         | Nashville, TN        | Store141   |                  50
 East         | Manchester, NH       | Store225   |                  50
 East         | Portsmouth, VA       | Store169   |                  50
 SouthWest    | Fort Collins, CO     | Store116   |                  50
 SouthWest    | Phoenix, AZ          | Store232   |                  50
 South        | Savannah, GA         | Store201   |                  50
 South        | Carrollton, TX       | Store8     |                  50
 West         | Rancho Cucamonga, CA | Store102   |                  50
 MidWest      | Lansing, MI          | Store105   |                  50
 West         | Provo, UT            | Store73    |                  50
 East         | Washington, DC       | Store180   |                  49
 MidWest      | Sioux Falls, SD      | Store45    |                  49
 NorthWest    | Seattle, WA          | Store241   |                  49
 SouthWest    | Las Vegas, NV        | Store104   |                  49
 West         | El Monte, CA         | Store100   |                  49
 SouthWest    | Fort Collins, CO     | Store20    |                  49
 East         | Lowell, MA           | Store57    |                  48
 SouthWest    | Arvada, CO           | Store188   |                  48
 MidWest      | Joliet, IL           | Store82    |                  48
 West         | Berkeley, CA         | Store248   |                  48
(20 rows)

重要

如果 LIMIT 子句省略 ORDER BY，则结果可能是不确定的。

限制窗口分区结果

您可以使用 LIMIT 对查询结果设置窗口分区，并限制每个窗口中返回的行数：

SELECT ... FROM dataset LIMIT num‑rows OVER ( PARTITION BY column‑expr‑x, ORDER BY column‑expr‑y [ASC | DESC] )

其中查询 dataset 在每个 column‑expr‑x 分区中返回具有最高或最低 column‑expr‑y 值的 num‑rows 个行。

例如，以下语句查询表 store.store_dimension 并包含一个指定窗口分区的 LIMIT 子句。在本例中，Vertica 按 store_region 对结果集进行分区，其中每个分区窗口显示一个区域中两个员工最少的商店：

=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees FROM store.store_dimension
     LIMIT 2 OVER (PARTITION BY store_region ORDER BY number_of_employees ASC);
 store_region |      location       | store_name | number_of_employees
--------------+---------------------+------------+---------------------
 West         | Norwalk, CA         | Store43    |                  10
 West         | Lancaster, CA       | Store95    |                  11
 East         | Stamford, CT        | Store219   |                  12
 East         | New York, NY        | Store122   |                  12
 SouthWest    | North Las Vegas, NV | Store170   |                  10
 SouthWest    | Phoenix, AZ         | Store228   |                  11
 NorthWest    | Bellevue, WA        | Store200   |                  19
 NorthWest    | Portland, OR        | Store39    |                  22
 MidWest      | South Bend, IN      | Store134   |                  10
 MidWest      | Evansville, IN      | Store30    |                  11
 South        | Mesquite, TX        | Store124   |                  10
 South        | Beaumont, TX        | Store226   |                  11
(12 rows)

10 - INSERT-SELECT 操作

优化具有以下格式的 INSERT-SELECT 查询的方法有以下几种：

INSERT /*+direct*/ INTO destination SELECT * FROM source;

10.1 - 匹配排序顺序

在执行 INSERT-SELECT 操作时，要避免 INSERT 的排序阶段，请确保 SELECT 查询的排序顺序与目标表的投影排序顺序相匹配。

例如，在单节点数据库中：

=> CREATE TABLE source (col1 INT, col2 INT, col3 INT);
=> CREATE PROJECTION source_p (col1, col2, col3)
     AS SELECT col1, col2, col3 FROM source
     ORDER BY col1, col2, col3
     SEGMENTED BY HASH(col3)
     ALL NODES;
=> CREATE TABLE destination (col1 INT, col2 INT, col3 INT);
=> CREATE PROJECTION destination_p (col1, col2, col3)
     AS SELECT col1, col2, col3 FROM destination
     ORDER BY col1, col2, col3
     SEGMENTED BY HASH(col3)
     ALL NODES;

以下 INSERT 不需要排序，因为查询结果具有投影的列顺序：

=> INSERT /*+direct*/ INTO destination SELECT * FROM source;

以下 INSERT 需要排序，因为 SELECT 语句中的列顺序与投影顺序不匹配：

=> INSERT /*+direct*/ INTO destination SELECT col1, col3, col2 FROM source;

以下 INSERT 不需要排序。列的顺序不匹配，但是显式 ORDER BY 可以使输出在 Vertica 中按 c1、c3、c2 排序：

=> INSERT /*+direct*/ INTO destination SELECT col1, col3, col2 FROM source
      GROUP BY col1, col3, col2
      ORDER BY col1, col2, col3 ;

10.2 - 相同分段

执行从分段的源表到分段的目标表的 INSERT-SELECT 操作时，请在相同列上对两个投影都进行分段以避免重新分段数据，如以下示例所示：

CREATE TABLE source (col1 INT, col2 INT, col3 INT);
CREATE PROJECTION source_p (col1, col2, col3) AS
   SELECT col1, col2, col3 FROM source
   SEGMENTED BY HASH(col3) ALL NODES;
CREATE TABLE destination (col1 INT, col2 INT, col3 INT);
CREATE PROJECTION destination_p (col1, col2, col3) AS
    SELECT col1, col2, col3 FROM destination
    SEGMENTED BY HASH(col3) ALL NODES;
INSERT /*+direct*/ INTO destination SELECT * FROM source;

11 - DELETE 和 UPDATE 查询

Vertica 已针对查询密集型工作负载进行了优化，因此，DELETE 和 UPDATE 查询可能无法达到与其他查询相同的性能水平。DELETE 和 UPDATE 操作必须更新所有投影，因此这些操作可能比最慢的投影还慢。有关详细信息，请参阅优化 DELETE 和 UPDATE。

12 - 数据收集器表查询

Vertica 数据收集器可收集和保留有关数据库群集的信息，从而扩展了系统表功能。数据收集器会在系统表中提供这些信息。

Vertica Analytic Database 会将数据收集数据存储在 Vertica 或编录路径下的数据收集器目录中。使用数据收集器信息可以查询系统表的过去状态并提取聚合信息。

一般来说，如果数据收集器表只有包含所需数据的列，查询这些表的效率会更高。在以下情况下，查询也会更加高效：

避免重新分段
使用时间谓词

避免重新分段

在 session_id 或 transaction_id 上联接以下 DC 表时，您可以避免重新分段，因为所有数据都是本地数据：

dc_session_starts
dc_session_ends
dc_requests_issued
dc_requests_completed

查询包括 node_name 列时，不需要重新分段。例如：

=> SELECT dri.transaction_id, dri.request, drc.processed_row_count
    FROM dc_requests_issued dri
    JOIN dc_requests_completed drc
    USING (node_name, session_id, request_id)     WHERE dri.time between 'April 7,2015'::timestamptz and 'April 8,2015'::timestamptz
    AND drc.time between 'April 7,2015'::timestamptz and 'April 8,2015'::timestamptz;

此查询会高效运行，因为：

启动程序节点仅写入到 dc_requests_issued 和 dc_requests_completed。
列 session_id 和 node_name 彼此相关。

使用时间谓词

为时间范围谓词使用非易变函数以及 TIMESTAMP。Vertica Analytic Database 会优化使用时间谓词的 DC 表的 SQL 性能。

每个 DC 表都有一个 time 列。使用此列可将时间范围作为查询谓词输入。

例如，此查询会返回日期在 9 月 1 日到 9 月 10 日之间的数据： select * from dc_foo where time > 'Sept 1, 2015::timestamptz and time < 'Sept 10 2015':: timestamptz; 您可以更改最小或最大时间值，调整要检索数据的时间范围。

您必须将非可变函数用作时间谓词。可变函数导致查询运行效率低下。此示例返回了在 2015 年 4 月 7 日开始和结束的所有查询。但是，查询并未以最佳性能运行，因为 trunc 和 timestamp 是可变的：

=> SELECT dri.transaction_id, dri.request, drc.processed_row_count
    FROM dc_requests_issued dri
    LEFT JOIN dc_requests_completed drc
    USING (session_id, request_id)
    WHERE trunc(dri.time, ‘DDD’) > 'April 7,2015'::timestamp
    AND trunc(drc.time, ‘DDD’) < 'April 8,2015'::timestamp;