移除表数据

• 1: 数据移除操作的比较
• 2: 优化 DELETE 和 UPDATE
• 3: 清除删除的数据
• 3.1: 设置清除策略
• 3.2: 手动清除数据
• 4: 截断表

1 - 数据移除操作的比较

下表汇总了各种数据移除操作之间的差异。

选择最佳操作

下表可以帮助您确定最适合移除表数据的操作：

2 - 优化 DELETE 和 UPDATE

Vertica 已针对查询密集型工作负载进行了优化，因此，DELETE 和 UPDATE 查询可能无法达到与其他查询相同的性能水平。DELETE 和 UPDATE 操作必须更新所有投影，因此这些操作只能与最慢的投影一样快。

要提高 DELETE 和 UPDATE 查询的性能，请考虑以下问题：

• 大型 DELETE 操作后的查询性能：Vertica 的 DELETE 实施不同于传统数据库：它不会从磁盘存储中删除数据；相反，它将行标记为已删除，以便它们可用于历史查询。删除表中总行数的 10% 或更多可能会对该表上的查询产生负面影响。在这种情况下，请考虑清除这些行以提高性能。
• 恢复性能：恢复是群集在崩溃后还原 K-safety 所需的操作。已删除记录较多可能会降低恢复性能。要提高恢复性能，请清除已删除行。
• 并发性：DELETE 和 UPDATE 对表采用独占锁。在表上，一次只能运行一个 DELETE 或 UPDATE 事务，并且这些事务只能在没有加载操作的情况下运行。不同表上的 DELETE 和 UPDATE 操作可以并发运行。

  提示

  要优化大批量 DELETE，请考虑在可能的情况下删除分区。

经过优化的 DELETE 的投影列要求

如果投影包含查询谓词所需的所有列，则投影已针对 DELETE 和 UPDATE 操作进行了优化。通常，对经过优化的投影执行 DML 操作时，速度明显快于未经优化的投影。

例如，假设有以下表和投影：

=> CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER);
=> CREATE PROJECTION p1 (a, b, c) AS SELECT * FROM t ORDER BY a;
=> CREATE PROJECTION p2 (a, c) AS SELECT a, c FROM t ORDER BY c, a;

在以下查询中，p1 和 p2 都符合 DELETE 和 UPDATE 优化条件，因为列 a 可用：

=> DELETE from t WHERE a = 1;

在以下示例中，只有投影 p1 符合 DELETE 和 UPDATE 优化条件，因为 b 列在 p2 中不可用：

=> DELETE from t WHERE b = 1;

子查询中经过优化的 DELETE

为了符合 DELETE 优化条件，在 DELETE 或 UPDATE 语句的 WHERE 子句中引用的所有目标表列都必须位于投影定义中。

例如，以下简单架构包含两个表和三个投影：

=> CREATE TABLE tb1 (a INT, b INT, c INT, d INT);
=> CREATE TABLE tb2 (g INT, h INT, i INT, j INT);

第一个投影引用 tb1 中的所有列并按列 a 进行排序：

=> CREATE PROJECTION tb1_p AS SELECT a, b, c, d FROM tb1 ORDER BY a;

伙伴实例投影引用 tb1 中的列 a 并按此列进行排序：

=> CREATE PROJECTION tb1_p_2 AS SELECT a FROM tb1 ORDER BY a;

以下投影引用 tb2 中的所有列并按列 i 进行排序：

=> CREATE PROJECTION tb2_p AS SELECT g, h, i, j FROM tb2 ORDER BY i;

考虑以下 DML 语句，其 WHERE 子句引用 tb1.a。由于 tb1 的两个投影都包含列 a，因此二者都符合经过优化的 DELETE 的条件：

=> DELETE FROM tb1 WHERE tb1.a IN (SELECT tb2.i FROM tb2);

限制

在以下条件下，不支持经过优化的 DELETE 操作：

• 子查询引用目标表时存在复制的投影。例如不支持以下语法：

  => DELETE FROM tb1 WHERE tb1.a IN (SELECT e FROM tb2, tb2 WHERE tb2.e = tb1.e);
  

• 子查询不返回多个行。例如不支持以下语法：

  => DELETE FROM tb1 WHERE tb1.a = (SELECT k from tb2);
  

用于优化 DELETE 的投影排序顺序

设计投影，使得经常使用的 DELETE 或 UPDATE 谓词列按照大型 DELETE 和 UPDATE 操作的所有投影的排序顺序显示。

例如，假设您对投影执行的大多数 DELETE 查询如下所示：

=> DELETE from t where time_key < '1-1-2007'

要优化 DELETE 操作，请使得 time_key 出现在所有投影的 ORDER BY 子句中。此架构设计可以提高 DELETE 操作的性能。

此外，将排序列添加到排序顺序中，以便每个排序键值组合都唯一标识一行或一小组行。有关详细信息，请参阅选择排序顺序：最佳实践。要分析投影是否存在排序顺序问题，请使用 EVALUATE_DELETE_PERFORMANCE 函数。

3 - 清除删除的数据

在 Vertica 中，删除操作不会从物理存储中移除行。 DELETE 会将行标记为已删除，UPDATE 也是如此，后者会合并删除和插入操作。在这两种情况下，Vertica 都会将丢弃的行保留为历史数据，在清除这些历史数据之前，仍然可通过历史查询访问这些数据。

保留历史数据的成本有双部分：

• 向已删除的行和删除标记分配磁盘空间。

• 典型（非历史）查询必须读取并跳过已删除的数据，这可能会影响性能。

清除操作将从物理存储中永久移除历史数据，并释放磁盘空间供重复使用。只有 Ancient History Mark (AHM) 之前的历史数据才符合清除条件。

可以通过两种方式清除数据：

• 设置清除策略。

• 手动清除数据。

在这两种情况下，Vertica 都会清除直到并包括 AHM 时期的所有历史数据，然后重置 AHM。有关 Vertica 如何使用时期的详细信息，请参阅时期。

3.1 - 设置清除策略

清除数据的首选方法是建立一个策略来确定哪个已删除的数据符合清除条件。当 Tuple Mover 执行 合并操作时，会自动清除符合条件的数据。

Vertica 提供了两种方法用于确定已删除的数据何时符合清除条件：

• 指定保存删除数据的时间

• 指定保存的 时期数

指定保存删除数据的时间

指定保存删除数据的时间是确定可以清除哪些已删除数据的首选方法。默认情况下，仅当节点处于关闭状态时，Vertica 才会保存历史数据。

要更改保存已删除数据的指定时间，请使用 HistoryRetentionTime 配置参数：

=> ALTER DATABASE DEFAULT SET HistoryRetentionTime = {seconds | -1};

在上述语法中：

• Seconds 是保存已删除数据的时长（单位为秒）。

• -1 表示您不想使用 HistoryRetentionTime 配置参数确定哪些已删除数据符合清除条件。如果您更想使用其他方法 (HistoryRetentionEpochs) 来确定可以清除哪些已删除数据，可使用此设置。

以下示例将历史时期保留级别设置为 240 秒：

=> ALTER DATABASE DEFAULT SET HistoryRetentionTime = 240;

指定保存的时期数

除非您有理由限制时期数，否则 Vertica 建议您指定保存删除数据的时间。

要通过 HistoryRetentionEpochs 配置参数指定保存的历史时期数：

1. 关闭 HistoryRetentionTime 配置参数：

   => ALTER DATABASE DEFAULT SET HistoryRetentionTime = -1;
   

2. 通过 HistoryRetentionEpochs 配置参数设置历史时期保留级别：

   => ALTER DATABASE DEFAULT SET HistoryRetentionEpochs = {num_epochs | -1};
   

   • num_epochs 是要保存的历史时期数。

   • -1 表示您不想使用 HistoryRetentionEpochs 配置参数从时期映射中截取历史时期。默认情况下，HistoryRetentionEpochs 设置为 -1。

以下示例将要保存的历史时期数设置为 40：

=> ALTER DATABASE DEFAULT SET HistoryRetentionEpochs = 40;

将立即在数据库群集内的所有节点上实施修改。您无需重新启动数据库。

有关更多详细信息，请参阅时期管理参数。有关 Vertica 如何使用时期的信息，请参阅时期。

禁用清除

如果要保留所有历史数据，可按以下方式将历史时期保留参数的值设置为 -1：

=> ALTER DABABASE mydb SET HistoryRetentionTime = -1;

=> ALTER DATABASE DEFAULT SET HistoryRetentionEpochs = -1;

3.2 - 手动清除数据

可以按如下方式手动清除已删除的数据：

1. 设置清除已删除数据的截止日期。首先，调用以下函数之一以验证当前的 Ancient History Mark ( AHM)：

   • GET_AHM_TIME 返回 AHM 的 TIMESTAMP 值。

   • GET_AHM_EPOCH 返回 AHM 所在的时期编号。

2. 使用以下函数之一将 AHM 设置为所需的截止日期：

   • SET_AHM_TIME 将 AHM 设置为包含 启动程序节点上指定的 TIMESTAMP 值的 时期。

   • SET_AHM_EPOCH 将 AHM 设置为指定的时期。

   • MAKE_AHM_NOW 将 AHM 设置为最大允许值。这让您可以清除所有已删除的数据。

   如果调用 SET_AHM_TIME，请记住指定的时间戳将映射到默认具有三分钟粒度的时期。因此，如果将 AHM 时间指定为 2008-01-01 00:00:00.00，则 Vertica 可能会清除 2008 年前三分钟的数据，或保留 2007 年最后三分钟的数据。

   注意

   不能将 AHM 推进到 Vertica 无法为故障节点恢复数据的时间点。

3. 使用以下函数之一从所需的投影中清除已删除的数据：

   • PURGE 清除物理架构中的所有投影。

   • PURGE_TABLE 清除锚定到指定表的所有投影。

   • PURGE_PROJECTION 清除指定的投影。

   • PURGE_PARTITION 清除指定的分区。

   Tuple Mover 执行 合并操作以清除数据。Vertica 会定期调用 Tuple Mover 以执行合并操作，如 Tuple Mover 参数所配置的那样。可以通过调用函数 DO_TM_TASK 来手动调用 Tuple Mover。

有关 Vertica 如何使用时期的详细信息，请参阅时期。

4 - 截断表

TRUNCATE TABLE 移除与目标表及其投影相关联的所有存储。Vertica 会保留表和投影定义。如果截断后的表包含过时投影，则当 TRUNCATE TABLE 返回时，这些投影将被清除并标记为最新。

TRUNCATE TABLE 在语句执行后提交整个事务，即使未能截断表也是如此。不能回退 TRUNCATE TABLE 语句。

使用 TRUNCATE TABLE 进行测试。可以使用它来移除表中的所有数据并向其中加载新数据，而无需重新创建表及其投影。

表锁定

TRUNCATE TABLE 会对表采用 O（所有者）锁，直到截断过程完成。随后释放保存点。

如果操作无法在目标表上获取 O lock，Vertica 将尝试关闭该表上运行的任何内部 tuple mover 会话。如果成功，则可以继续操作。在用户会话中运行的显式 Tuple Mover 操作不会关闭。如果显式 Tuple Mover 操作在表上运行，则该操作仅在 Tuple Mover 操作完成后继续。

限制

不能截断外部表。

示例

=> INSERT INTO sample_table (a) VALUES (3);
=> SELECT * FROM sample_table;
a
---
3
(1 row)

=> TRUNCATE TABLE sample_table;
TRUNCATE TABLE
=> SELECT * FROM sample_table;
a
---
(0 rows)