Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

OPTIMIZE

View as MarkdownEdit this pageReport an issueSet up docs MCP

Esta consulta intenta iniciar una fusión no programada de partes de datos en tablas. Ten en cuenta que, por lo general, desaconsejamos usar OPTIMIZE TABLE ... FINAL (consulta esta documentación), ya que su uso está pensado para tareas de administración, no para las operaciones diarias.

Sintaxis

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]

La consulta OPTIMIZE es compatible con la familia MergeTree (incluidas las vistas materializadas) y con los motores Buffer. Otros motores de tablas no son compatibles.

Cuando OPTIMIZE se usa con la familia ReplicatedMergeTree de motores de tablas, ClickHouse crea una tarea de fusión y espera a que se ejecute en todas las réplicas (si el SETTING alter_sync está establecido en 2) o en la réplica actual (si el SETTING alter_sync está establecido en 1).

  • Si OPTIMIZE no realiza ninguna fusión por cualquier motivo, no se notifica al cliente. Para habilitar las notificaciones, use el SETTING optimize_throw_if_noop.
  • Si especifica una PARTITION, solo se optimiza la partición indicada. Cómo establecer la expresión de partición.
  • Si especifica FINAL o FORCE, la optimización se realiza incluso cuando todos los datos ya están en una sola parte. Puede controlar este comportamiento con optimize_skip_merged_partitions. Además, la fusión se fuerza incluso si se están realizando fusiones concurrentes.
  • Si especifica DEDUPLICATE, se realizará la deduplicación de las filas completamente idénticas (salvo que se especifique una cláusula BY; se comparan todas las columnas); esto solo tiene sentido para el motor MergeTree.

Puede especificar cuánto tiempo (en segundos) se debe esperar a que las réplicas inactivas ejecuten consultas OPTIMIZE mediante el SETTING replication_wait_for_inactive_replica_timeout.

DRY RUN

La cláusula DRY RUN simula una fusión de las partes especificadas sin aplicar el resultado. La parte fusionada se escribe en una ubicación temporal, se valida y luego se descarta. Las partes originales y los datos de la tabla permanecen sin cambios.

Esto resulta útil para:

  • Probar la corrección de la fusión entre versiones de ClickHouse.
  • Reproducir de forma determinista errores relacionados con la fusión.
  • Realizar benchmarks del rendimiento de la fusión.

DRY RUN solo es compatible con tablas de la familia MergeTree. La palabra clave PARTS, junto con una lista de nombres de partes, es obligatoria. Todas las partes especificadas deben existir, estar activas y pertenecer a la misma partición.

DRY RUN es incompatible con FINAL y PARTITION. Puede combinarse con DEDUPLICATE (con especificación opcional de columnas) y CLEANUP (para tablas ReplacingMergeTree).

Sintaxis

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

De forma predeterminada, la parte fusionada resultante se valida de forma similar a la consulta CHECK TABLE. Este comportamiento está controlado por la configuración optimize_dry_run_check_part (habilitada de forma predeterminada). Deshabilitarla omite la validación, lo que puede ser útil para hacer benchmark de la operación de fusión en sí.

Ejemplo

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 │
└─────────────┴──────┘

Expresión BY

Si desea realizar la deduplicación sobre un conjunto personalizado de columnas en lugar de sobre todas ellas, puede especificar explícitamente la lista de columnas o usar cualquier combinación de expresiones *, COLUMNS o EXCEPT. La lista de columnas escrita explícitamente o expandida implícitamente debe incluir todas las columnas especificadas en la expresión de ordenación de filas (tanto la clave primaria como la clave de ordenación) y en la expresión de particionado (la clave de particionado).

Sintaxis

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

Ejemplos

Considere esta tabla:

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

┌

Todos los ejemplos que siguen se ejecutan sobre este estado de 5 filas.

DEDUPLICATE

Cuando no se especifican las columnas para la deduplicación, se tienen en cuenta todas. La fila se elimina solo si todos los valores de todas las columnas coinciden con los valores correspondientes de la fila anterior:

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 *

Cuando las columnas se especifican de forma implícita, la tabla se deduplica por todas las columnas que no son ALIAS ni MATERIALIZED. Teniendo en cuenta la tabla anterior, estas son las columnas primary_key, secondary_key, value y 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

Deduplica por todas las columnas que no sean ALIAS ni MATERIALIZED, y excluyendo explícitamente value: las columnas primary_key, secondary_key y 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>

Deduplica explícitamente según las columnas primary_key, secondary_key y 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>)

Deduplica en todas las columnas que coincidan con una expresión regular: las columnas primary_key, secondary_key y 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