Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

OPTIMIZE

View as MarkdownEdit this pageReport an issueSet up docs MCP

该查询会尝试对表的数据分片发起一次计划外合并。请注意，我们通常不建议使用 OPTIMIZE TABLE ... FINAL (参见相关文档) ，因为它适用于管理场景，而非日常操作。

语法

OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL | FORCE] [DEDUPLICATE [BY expression]]
OPTIMIZE TABLE [db.]name DRY RUN PARTS 'part_name1', 'part_name2' [, ...] [DEDUPLICATE [BY expression]] [CLEANUP]

OPTIMIZE 查询支持 MergeTree 家族 (包括 materialized views) 和 Buffer 引擎。其他表引擎不支持。

当 OPTIMIZE 与 ReplicatedMergeTree 家族的表引擎一起使用时，ClickHouse 会创建一个合并任务，并等待它在所有副本上执行完成 (如果 alter_sync 设置为 2) ，或在当前副本上执行完成 (如果 alter_sync 设置为 1) 。

  • 如果 OPTIMIZE 因任何原因未执行合并，不会通知客户端。要启用通知，请使用 optimize_throw_if_noop 设置。
  • 如果指定了 PARTITION，则只会优化指定分区。如何设置分区表达式。
  • 如果指定了 FINAL 或 FORCE，即使所有数据已经在一个分片中，也会执行优化。你可以使用 optimize_skip_merged_partitions 控制此行为。此外，即使当前正在执行并发合并，也会强制执行合并。
  • 如果指定了 DEDUPLICATE，则会对完全相同的行进行去重 (除非指定了 BY 子句) ；也就是说，会比较所有列。这仅对 MergeTree 引擎有意义。

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

DRY RUN

DRY RUN 子句会在不提交结果的情况下模拟合并指定的 parts。合并后的分片会写入临时位置，经过校验后再丢弃。原始 parts 和表数据均保持不变。

这在以下场景中很有用：

  • 测试不同 ClickHouse 版本中的合并正确性。
  • 以确定性的方式复现与合并相关的 bug。
  • 对合并性能进行基准测试。

DRY RUN 仅支持 MergeTree 家族的表。必须使用 PARTS 关键字并提供分片 名称列表。所有指定的 parts 都必须存在、处于活动状态，并且属于同一个分区。

DRY RUN 与 FINAL 和 PARTITION 不兼容。它可以与 DEDUPLICATE (可选择指定列) 以及 CLEANUP (用于 ReplacingMergeTree 表) 结合使用。

语法

OPTIMIZE TABLE [db.]name DRY RUN PARTS 'part_name1', 'part_name2' [, ...] [DEDUPLICATE [BY expression]] [CLEANUP]

默认情况下，生成的合并后分片会以类似 CHECK TABLE 查询的方式进行校验。此行为由 optimize_dry_run_check_part 设置控制 (默认启用) 。禁用后将跳过校验，这对仅对合并过程本身进行基准测试很有帮助。

示例

CREATE TABLE dry_run_example (key UInt64, value String) ENGINE = MergeTree ORDER BY key;

INSERT INTO dry_run_example VALUES (1, 'a'), (2, 'b');
INSERT INTO dry_run_example VALUES (1, 'c'), (4, 'd');

-- Simulate merging using two parts
OPTIMIZE TABLE dry_run_example DRY RUN PARTS 'all_1_1_0', 'all_2_2_0';

-- Simulate merging with deduplication
OPTIMIZE TABLE dry_run_example DRY RUN PARTS 'all_1_1_0', 'all_2_2_0' DEDUPLICATE;

-- Parts and data remain unchanged after DRY RUN
SELECT name, rows FROM system.parts
WHERE database = currentDatabase() AND table = 'dry_run_example' AND active
ORDER BY name;
┌─name────────┬─rows─┐
│ all_1_1_0   │    2 │
│ all_2_2_0   │    2 │
└─────────────┴──────┘

BY 表达式

如果你想基于自定义的一组列而不是所有列执行去重，可以显式指定列列表，也可以使用 *、COLUMNS 或 EXCEPT 表达式的任意组合。显式写出的或隐式展开的列列表，必须包含行排序表达式 (包括主键和排序键) 以及分区表达式 (分区键) 中指定的所有列。

语法

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);

示例

假设有下表：

Querysql
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);
Querysql
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);
Querysql
SELECT * FROM example;
Responsesql

┌

以下所有示例均基于这一包含 5 行的状态执行。

DEDUPLICATE

如果未指定去重列，则默认考虑所有列。只有当所有列的值都与上一行中对应的值相等时，才会删除该行：

Querysql
OPTIMIZE TABLE example FINAL DEDUPLICATE;
Querysql
SELECT * FROM example;
Responseresponse
┌─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 *

当列为隐式指定时，表会按所有不是 ALIAS 或 MATERIALIZED 的列去重。以上表为例，这些列包括 primary_key、secondary_key、value 和 partition_key：

Querysql
OPTIMIZE TABLE example FINAL DEDUPLICATE BY *;
Querysql
SELECT * FROM example;
Responseresponse
┌─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

按所有既非 ALIAS、也非 MATERIALIZED，且明确排除 value 的列进行去重：primary_key、secondary_key 和 partition_key 列。

Querysql
OPTIMIZE TABLE example FINAL DEDUPLICATE BY * EXCEPT value;
Querysql
SELECT * FROM example;
Responseresponse
┌─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 <list of columns>

按 primary_key、secondary_key 和 partition_key 列进行显式去重：

Querysql
OPTIMIZE TABLE example FINAL DEDUPLICATE BY primary_key, secondary_key, partition_key;
Querysql
SELECT * FROM example;
Responseresponse
┌─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(<regex>)

按所有匹配正则表达式的列去重：primary_key、secondary_key 和 partition_key 列：

Querysql
OPTIMIZE TABLE example FINAL DEDUPLICATE BY COLUMNS('.*_key');
Querysql
SELECT * FROM example;
Responseresponse
┌─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 │
└─────────────┴───────────────┴───────┴───────────────┘
Navigation