优化 DELETE 和 UPDATE

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

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

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

经过优化的 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 函数。