跳至主要内容

OPTIMIZE 语句

此查询尝试初始化表的数据部分的非计划合并。请注意,我们通常建议不要使用 OPTIMIZE TABLE ... FINAL(请参阅这些 文档),因为它的用例 предназначен для администрирования,而不是 для повседневных операций。

注意

OPTIMIZE 无法修复 Too many parts 错误。

语法

OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]

OPTIMIZE 查询支持 MergeTree 系列(包括 物化视图)和 Buffer 引擎。其他表引擎不受支持。

OPTIMIZEReplicatedMergeTree 系列表引擎一起使用时,ClickHouse 会创建一个合并任务,并在所有副本上等待执行(如果 alter_sync 设置为 2)或在当前副本上等待执行(如果 alter_sync 设置为 1)。

  • 如果 OPTIMIZE 由于任何原因未执行合并,它不会通知客户端。要启用通知,请使用 optimize_throw_if_noop 设置。
  • 如果指定了 PARTITION,则仅优化指定的 partition。 如何设置 partition 表达式
  • 如果指定了 FINAL,即使所有数据已在一个部分中,也会执行优化。您可以使用 optimize_skip_merged_partitions 控制此行为。此外,即使正在执行并发合并,也会强制执行合并。
  • 如果指定了 DEDUPLICATE,则完全相同的行(除非指定了 by 子句)将被去重(比较所有列),这仅对 MergeTree 引擎有意义。

您可以通过 replication_wait_for_inactive_replica_timeout 设置指定等待非活动副本执行 OPTIMIZE 查询的时间长度(以秒为单位)。

注意

如果 alter_sync 设置为 2,并且某些副本在超过由 replication_wait_for_inactive_replica_timeout 设置指定的时间内未处于活动状态,则会抛出异常 UNFINISHED

BY 表达式

如果要对自定义列集而不是所有列执行去重,可以显式指定列列表或使用 *COLUMNSEXCEPT 表达式的任意组合。显式编写或隐式扩展的列列表必须包含行排序表达式(主键和排序键)和分区表达式(分区键)中指定的所有列。

注意

请注意,* 的行为与 SELECT 中相同:MATERIALIZEDALIAS 列不用于扩展。

此外,指定空列列表、编写导致空列列表的表达式或按 ALIAS 列去重都是错误的。

语法

OPTIMIZE TABLE table DEDUPLICATE; -- all columns
OPTIMIZE TABLE table DEDUPLICATE BY *; -- excludes MATERIALIZED and ALIAS columns
OPTIMIZE TABLE table DEDUPLICATE BY colX,colY,colZ;
OPTIMIZE TABLE table DEDUPLICATE BY * EXCEPT colX;
OPTIMIZE TABLE table DEDUPLICATE BY * EXCEPT (colX, colY);
OPTIMIZE TABLE table DEDUPLICATE BY COLUMNS('column-matched-by-regex');
OPTIMIZE TABLE table DEDUPLICATE BY COLUMNS('column-matched-by-regex') EXCEPT colX;
OPTIMIZE TABLE table DEDUPLICATE BY COLUMNS('column-matched-by-regex') EXCEPT (colX, colY);

示例

考虑表

CREATE TABLE example (
primary_key Int32,
secondary_key Int32,
value UInt32,
partition_key UInt32,
materialized_value UInt32 MATERIALIZED 12345,
aliased_value UInt32 ALIAS 2,
PRIMARY KEY primary_key
) ENGINE=MergeTree
PARTITION BY partition_key
ORDER BY (primary_key, secondary_key);
INSERT INTO example (primary_key, secondary_key, value, partition_key)
VALUES (0, 0, 0, 0), (0, 0, 0, 0), (1, 1, 2, 2), (1, 1, 2, 3), (1, 1, 3, 3);
SELECT * FROM example;

结果


┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 0 │ 0 │ 0 │ 0 │
│ 0 │ 0 │ 0 │ 0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 3 │
│ 1 │ 1 │ 3 │ 3 │
└─────────────┴───────────────┴───────┴───────────────┘

以下所有示例都针对此状态(包含 5 行)执行。

DEDUPLICATE

当未指定去重列时,将考虑所有列。仅当所有列中的所有值都等于前一行中相应的值时,才会删除该行。

OPTIMIZE TABLE example FINAL DEDUPLICATE;
SELECT * FROM example;

结果

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 0 │ 0 │ 0 │ 0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 3 │
│ 1 │ 1 │ 3 │ 3 │
└─────────────┴───────────────┴───────┴───────────────┘

DEDUPLICATE BY *

当隐式指定列时,表将按所有不是 ALIASMATERIALIZED 的列去重。考虑到上面的表,这些是 primary_keysecondary_keyvaluepartition_key 列。

OPTIMIZE TABLE example FINAL DEDUPLICATE BY *;
SELECT * FROM example;

结果

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 0 │ 0 │ 0 │ 0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 3 │
│ 1 │ 1 │ 3 │ 3 │
└─────────────┴───────────────┴───────┴───────────────┘

DEDUPLICATE BY * EXCEPT

按所有不是 ALIASMATERIALIZED 且明确不是 value 的列去重:primary_keysecondary_keypartition_key 列。

OPTIMIZE TABLE example FINAL DEDUPLICATE BY * EXCEPT value;
SELECT * FROM example;

结果

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 0 │ 0 │ 0 │ 0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 3 │
└─────────────┴───────────────┴───────┴───────────────┘

DEDUPLICATE BY <列列表>

primary_keysecondary_keypartition_key 列显式去重。

OPTIMIZE TABLE example FINAL DEDUPLICATE BY primary_key, secondary_key, partition_key;
SELECT * FROM example;

结果

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 0 │ 0 │ 0 │ 0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 3 │
└─────────────┴───────────────┴───────┴───────────────┘

DEDUPLICATE BY COLUMNS(<正则表达式>)

按与正则表达式匹配的所有列去重:primary_keysecondary_keypartition_key 列。

OPTIMIZE TABLE example FINAL DEDUPLICATE BY COLUMNS('.*_key');
SELECT * FROM example;

结果

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 0 │ 0 │ 0 │ 0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│ 1 │ 1 │ 2 │ 3 │
└─────────────┴───────────────┴───────┴───────────────┘