跳至主要内容
跳至主要内容
编辑此页

MergeTree 表引擎

MergeTree 引擎和其他 MergeTree 系列引擎（例如 ReplacingMergeTree、AggregatingMergeTree）是 ClickHouse 中最常用和最健壮的表引擎。

MergeTree 系列表引擎专为高数据摄取速率和海量数据而设计。插入操作会创建表部件，这些部件由后台进程与其他表部件合并。

MergeTree 系列表引擎的主要特性。

  • 表的 первичный ключ 决定了每个表部件内的排序顺序（聚簇索引）。 первичный ключ 也不引用单个行，而是引用称为 granules 的 8192 行的块。这使得大型数据集的 первичный ключ 足够小，可以保存在主内存中，同时仍然提供对磁盘上数据的快速访问。

  • 可以使用任意分区表达式对表进行分区。分区剪枝确保在查询允许的情况下省略分区。

  • 可以将数据复制到多个集群节点，以实现高可用性、故障转移和零停机时间升级。请参阅 数据复制。

  • MergeTree 表引擎支持各种统计类型和采样方法，以帮助查询优化。

注意

尽管名称相似，但 Merge 引擎与 *MergeTree 引擎不同。

创建表​

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [[NOT] NULL] [DEFAULT|MATERIALIZED|ALIAS|EPHEMERAL expr1] [COMMENT ...] [CODEC(codec1)] [STATISTICS(stat1)] [TTL expr1] [PRIMARY KEY] [SETTINGS (name = value, ...)],
    name2 [type2] [[NOT] NULL] [DEFAULT|MATERIALIZED|ALIAS|EPHEMERAL expr2] [COMMENT ...] [CODEC(codec2)] [STATISTICS(stat2)] [TTL expr2] [PRIMARY KEY] [SETTINGS (name = value, ...)],
    ...
    INDEX index_name1 expr1 TYPE type1(...) [GRANULARITY value1],
    INDEX index_name2 expr2 TYPE type2(...) [GRANULARITY value2],
    ...
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name = value, ...]

有关参数的详细说明，请参阅 CREATE TABLE 语句

查询子句​

ENGINE​

ENGINE — 引擎的名称和参数。ENGINE = MergeTree()。MergeTree 引擎没有参数。

ORDER BY​

ORDER BY — 排序键。

列名或任意表达式的元组。例如：ORDER BY (CounterID + 1, EventDate)。

如果未定义 первичный ключ（即未指定 PRIMARY KEY），ClickHouse 会将排序键用作 первичный ключ。

如果不需要排序，可以使用语法 ORDER BY tuple()。或者，如果启用了 create_table_empty_primary_key_by_default，则隐式地将 ORDER BY () 添加到 CREATE TABLE 语句中。请参阅 选择 первичный ключ。

PARTITION BY​

PARTITION BY — 分区键。可选。在大多数情况下，您不需要分区键，并且如果需要分区，通常不需要比按月更细粒度的分区键。分区不会加快查询速度（与 ORDER BY 表达式相反）。您不应使用过于细粒度的分区。不要按客户端标识符或名称对数据进行分区（而是将客户端标识符或名称放在 ORDER BY 表达式中的第一列）。

对于按月分区，请使用表达式 toYYYYMM(date_column)，其中 date_column 是具有 Date 类型日期的列。 这里的分区名称具有 "YYYYMM" 格式。

PRIMARY KEY​

PRIMARY KEY — 如果它 与排序键不同，则为 первичный ключ。可选。

指定排序键（使用 ORDER BY 子句）会隐式指定 первичный ключ。通常没有必要在排序键之外再指定 первичный ключ。

SAMPLE BY​

SAMPLE BY — 采样表达式。可选。

如果指定，它必须包含在 первичный ключ 中。采样表达式必须产生一个无符号整数。

示例：SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。

TTL​

TTL — 一组规则，用于指定行存储持续时间以及自动部件移动 在磁盘和卷之间的逻辑。可选。

表达式必须产生一个 Date 或 DateTime，例如 TTL date + INTERVAL 1 DAY。

规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'|GROUP BY 指定了如果表达式满足（达到当前时间），则对部件执行的操作：删除过期的行，将部件（如果表达式满足部件中的所有行）移动到指定的磁盘（TO DISK 'xxx'）或卷（TO VOLUME 'xxx'），或聚合过期行中的值。默认规则类型是删除 (DELETE)。可以指定多个规则，但不应超过一个 DELETE 规则。

有关更多详细信息，请参阅 列和表的 TTL

SETTINGS​

请参阅 MergeTree 设置。

示例设置部分

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

在此示例中，我们设置按月分区。

我们还设置了一个按用户 ID 散列的采样表达式。这允许您为每个 CounterID 和 EventDate 对表中的数据进行伪随机化。如果您在选择数据时定义 SAMPLE 子句，ClickHouse 将为用户子集返回均匀的伪随机数据样本。

可以省略 index_granularity 设置，因为 8192 是默认值。

创建表的弃用方法
注意

请勿在新项目中使用此方法。如果可能，请将旧项目切换到上述方法。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE [=] MergeTree(date-column [, sampling_expression], (primary, key), index_granularity)

MergeTree() 参数

  • date-column — Date 类型的列的名称。ClickHouse 会根据此列自动创建按月分区。分区名称的格式为 "YYYYMM"。
  • sampling_expression — 采样表达式。
  • (primary, key) — первичный ключ。类型：Tuple()
  • index_granularity — 索引的粒度。数据行之间“标记”的数量。对于大多数任务，8192 的值是合适的。

示例

MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)

MergeTree 引擎的配置方式与上述主引擎配置方法相同。

数据存储​

表由按 первичный ключ 排序的数据部件组成。

当数据插入到表中时，会创建单独的数据部件，并且每个部件都按 первичный ключ 进行词法排序。例如，如果 первичный ключ 是 (CounterID, Date)，则部件中的数据按 CounterID 排序，并且在每个 CounterID 内，按 Date 排序。

属于不同分区的属于不同部件的数据。在后台，ClickHouse 会合并数据部件以实现更高效的存储。属于不同分区的部件不会合并。合并机制不能保证具有相同 первичный ключ 的所有行都在同一个数据部件中。

数据部件可以存储为 Wide 或 Compact 格式。在 Wide 格式中，每个列都存储在文件系统中的单独文件中，而在 Compact 格式中，所有列都存储在一个文件中。可以使用 Compact 格式来提高小型和频繁插入的性能。

数据存储格式由表引擎的 min_bytes_for_wide_part 和 min_rows_for_wide_part 设置控制。如果数据部件中的字节数或行数小于相应设置的值，则部件以 Compact 格式存储。否则，它以 Wide 格式存储。如果未设置这些设置中的任何一个，则数据部件以 Wide 格式存储。

每个数据部件在逻辑上划分为 granules。granule 是 ClickHouse 在选择数据时读取的最小不可分割的数据集。ClickHouse 不会拆分行或值，因此每个 granule 始终包含整数行数。granule 的第一行用行的 первичный ключ 值标记。对于每个数据部件，ClickHouse 会创建一个索引文件，该文件存储标记。对于每个列，无论它是否在 первичный ключ 中，ClickHouse 也存储相同的标记。这些标记允许您直接在列文件中找到数据。

granule 的大小受表引擎的 index_granularity 和 index_granularity_bytes 设置限制。granule 中的行数位于 [1, index_granularity] 范围内，具体取决于行的大小。如果单行的尺寸大于设置的值，granule 的尺寸可以超过 index_granularity_bytes。在这种情况下，granule 的尺寸等于行的尺寸。

查询中的 первичный ключ 和索引​

以 первичный ключ (CounterID, Date) 为例。在这种情况下，排序和索引可以如下说明

Whole data:     [---------------------------------------------]
CounterID:      [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
Date:           [1111111222222233331233211111222222333211111112122222223111112223311122333]
Marks:           |      |      |      |      |      |      |      |      |      |      |
                a,1    a,2    a,3    b,3    e,2    e,3    g,1    h,2    i,1    i,3    l,3
Marks numbers:   0      1      2      3      4      5      6      7      8      9      10

如果数据查询指定

  • CounterID in ('a', 'h')，服务器读取标记范围 [0, 3) 和 [6, 8) 中的数据。
  • CounterID IN ('a', 'h') AND Date = 3，服务器读取标记范围 [1, 3) 和 [7, 8) 中的数据。
  • Date = 3，服务器读取标记范围 [1, 10] 中的数据。

上面的示例表明，使用索引始终比全表扫描更有效。

稀疏索引允许读取额外的数据。在读取 первичный ключ 的单个范围时，每个数据块中最多可以读取 index_granularity * 2 额外行。

稀疏索引允许您使用非常大量的表行，因为在大多数情况下，这些索引可以适应计算机的 RAM。

ClickHouse 不需要唯一的 первичный ключ。您可以插入具有相同 первичный ключ 的多个行。

您可以在 PRIMARY KEY 和 ORDER BY 子句中使用 Nullable 类型的表达式，但不建议这样做。要允许此功能，请启用 allow_nullable_key 设置。ORDER BY 子句中的 NULL 值适用 NULLS_LAST 原则。

选择 первичный ключ​

первичный ключ 中的列数没有明确限制。根据数据结构，您可以在 первичный ключ 中包含更多或更少的列。这可能

  • 提高索引的性能。

    如果 первичный ключ 是 (a, b)，则添加另一个列 c 将提高性能，如果满足以下条件

    • 存在对列 c 有条件的查询。
    • 常见的情况是，对于 (a, b) 具有相同值的长数据范围（比 index_granularity 长几倍）。换句话说，添加另一个列可以跳过相当长的数据范围。

  • 提高数据压缩率。

    ClickHouse 按照主键对数据进行排序，因此一致性越高，压缩率越高。

  • 在 CollapsingMergeTree 和 SummingMergeTree 引擎中合并数据部分时，提供额外的逻辑。

    在这种情况下，指定与主键不同的排序键是有意义的。

较长的主键会对插入性能和内存消耗产生负面影响，但主键中的额外列不会影响 ClickHouse 在 SELECT 查询期间的性能。

您可以使用 ORDER BY tuple() 语法创建没有主键的表。在这种情况下，ClickHouse 以插入的顺序存储数据。如果您希望在通过 INSERT ... SELECT 查询插入数据时保存数据顺序，请设置 max_insert_threads = 1。

要以初始顺序选择数据，请使用 单线程 SELECT 查询。

选择与排序键不同的主键​

可以指定一个主键（一个在索引文件中为每个标记写入的值的表达式），该主键与排序键（一个用于对数据部分中的行进行排序的表达式）不同。在这种情况下，主键表达式元组必须是排序键表达式元组的前缀。

当使用 SummingMergeTree 和 AggregatingMergeTree 表引擎时，此功能很有用。在通常使用这些引擎的情况下，表具有两种类型的列：维度和度量。典型的查询聚合度量列的值，并使用任意 GROUP BY 和按维度进行过滤。由于 SummingMergeTree 和 AggregatingMergeTree 聚合排序键值相同的行，因此将所有维度添加到其中是自然的。结果是，键表达式由很长的列列表组成，并且此列表必须使用新添加的维度进行频繁更新。

在这种情况下，将少量列保留在主键中以提供高效的范围扫描，并将剩余的维度列添加到排序键元组中是有意义的。

ALTER 排序键是一个轻量级操作，因为当同时将新列添加到表和排序键时，现有的数据部分不需要更改。由于旧排序键是新排序键的前缀，并且新添加的列中没有数据，因此在修改表时，数据将按旧排序键和新排序键进行排序。

查询中使用索引和分区的用法​

对于 SELECT 查询，ClickHouse 会分析是否可以使用索引。如果 WHERE/PREWHERE 子句具有一个表达式（作为连词元素之一，或全部），该表达式表示相等或不等比较运算，或者它具有在主键或分区键中的列或表达式上的 IN 或具有固定前缀的 LIKE，或者这些列或表达式的某些部分重复函数，或者这些表达式的逻辑关系，则可以使用索引。

因此，可以快速运行主键的一个或多个范围上的查询。例如，当针对特定的跟踪标签、特定的标签和日期范围、特定的标签和日期、具有日期范围的多个标签等运行查询时，查询将很快。

让我们看一下配置如下的引擎

ENGINE MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate)
SETTINGS index_granularity=8192

在这种情况下，在查询中

SELECT count() FROM table
WHERE EventDate = toDate(now())
AND CounterID = 34

SELECT count() FROM table
WHERE EventDate = toDate(now())
AND (CounterID = 34 OR CounterID = 42)

SELECT count() FROM table
WHERE ((EventDate >= toDate('2014-01-01')
AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01'))
AND CounterID IN (101500, 731962, 160656)
AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse 将使用主键索引来修剪不当的数据，并使用每月分区键来修剪不当日期范围内的分区。

上面的查询表明，即使对于复杂的表达式，也可以使用索引。从表中读取数据的方式是，使用索引不能比全表扫描慢。

在下面的示例中，无法使用索引。

SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

要检查 ClickHouse 在运行查询时是否可以使用索引，请使用设置 force_index_by_date 和 force_primary_key。

按月进行分区键允许仅读取包含适当范围日期的那些数据块。在这种情况下，数据块可能包含许多日期的数据（最多一个月）。在块内，数据按主键排序，而主键可能不包含日期作为第一列。因此，仅使用不指定主键前缀的日期条件的查询将导致读取比单个日期更多的数据。

对部分单调主键使用索引​

例如，考虑月份中的天数。它们在一个月内形成一个 单调序列，但对于更长的时期则不单调。这是一个部分单调序列。如果用户使用部分单调主键创建表，ClickHouse 会像往常一样创建一个稀疏索引。当用户从这种表选择数据时，ClickHouse 会分析查询条件。如果用户想要获取索引的两个标记之间的所有数据，并且这两个标记都落在同一个月内，ClickHouse 可以在这种特定情况下使用索引，因为它能够计算查询参数和索引标记之间的距离。

如果查询参数范围内的主键值不表示单调序列，ClickHouse 无法使用索引。在这种情况下，ClickHouse 使用全表扫描方法。

ClickHouse 不仅对月份的天数序列使用此逻辑，还对表示部分单调序列的任何主键使用此逻辑。

数据跳过索引​

索引声明位于 CREATE 查询的列部分中。

INDEX index_name expr TYPE type(...) [GRANULARITY granularity_value]

对于来自 *MergeTree 系列的表，可以指定数据跳过索引。

这些索引聚合了关于指定表达式在块上的某些信息，这些块由 granularity_value 粒度组成（粒度大小使用表引擎中的 index_granularity 设置指定）。然后，这些聚合用于 SELECT 查询，以减少从磁盘读取的数据量，从而跳过 where 查询无法满足的大量数据块。

可以省略 GRANULARITY 子句，granularity_value 的默认值为 1。

示例

CREATE TABLE table_name
(
    u64 UInt64,
    i32 Int32,
    s String,
    ...
    INDEX idx1 u64 TYPE bloom_filter GRANULARITY 3,
    INDEX idx2 u64 * i32 TYPE minmax GRANULARITY 3,
    INDEX idx3 u64 * length(s) TYPE set(1000) GRANULARITY 4
) ENGINE = MergeTree()
...

示例中的索引可用于 ClickHouse 以减少从磁盘读取的数据量，用于以下查询

SELECT count() FROM table WHERE u64 == 10;
SELECT count() FROM table WHERE u64 * i32 >= 1234
SELECT count() FROM table WHERE u64 * length(s) == 1234

数据跳过索引也可以在复合列上创建

-- on columns of type Map:
INDEX map_key_index mapKeys(map_column) TYPE bloom_filter
INDEX map_value_index mapValues(map_column) TYPE bloom_filter

-- on columns of type Tuple:
INDEX tuple_1_index tuple_column.1 TYPE bloom_filter
INDEX tuple_2_index tuple_column.2 TYPE bloom_filter

-- on columns of type Nested:
INDEX nested_1_index col.nested_col1 TYPE bloom_filter
INDEX nested_2_index col.nested_col2 TYPE bloom_filter

跳过索引类型​

MergeTree 表引擎支持以下类型的跳过索引。有关如何使用跳过索引进行性能优化，请参阅 “了解 ClickHouse 数据跳过索引”。

MinMax 跳过索引​

对于每个索引粒度，存储表达式的最小值和最大值。（如果表达式的类型为 tuple，则它为每个元组元素存储最小值和最大值。）

minmax

Set​

对于每个索引粒度，最多存储指定表达式的 max_rows 个唯一值。max_rows = 0 表示“存储所有唯一值”。

set(max_rows)

Bloom 过滤器​

对于每个索引粒度，存储指定列的 bloom 过滤器。

bloom_filter([false_positive_rate])

false_positive_rate 参数可以取介于 0 和 1 之间的值（默认值：0.025），并指定生成阳性的概率（这会增加要读取的数据量）。

支持以下数据类型

  • (U)Int*
  • Float*
  • Enum
  • Date
  • DateTime
  • String
  • FixedString
  • Array
  • LowCardinality
  • 可为空
  • UUID
  • Map
Map 数据类型：指定使用键或值创建索引

对于 Map 数据类型，客户端可以使用 mapKeys 或 mapValues 函数指定是否应该为键或值创建索引。

N-gram bloom 过滤器​

对于每个索引粒度，存储指定列的 bloom 过滤器 的 n-gram。

ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)
参数描述
nngram 大小
bloom_filter_in_bytes 的大小bloom 过滤器的大小（以字节为单位）。您可以在这里使用一个较大的值，例如 256 或 512，因为它可以通过良好的压缩。
哈希函数的数量bloom 过滤器中使用的哈希函数的数量。
随机种子bloom 过滤器哈希函数的种子。

此索引仅适用于以下数据类型

要估计 ngrambf_v1 的参数，可以使用以下 用户定义函数 (UDF)。

CREATE FUNCTION bfEstimateFunctions [ON CLUSTER cluster]
AS
(total_number_of_all_grams, size_of_bloom_filter_in_bits) -> round((size_of_bloom_filter_in_bits / total_number_of_all_grams) * log(2));

CREATE FUNCTION bfEstimateBmSize [ON CLUSTER cluster]
AS
(total_number_of_all_grams,  probability_of_false_positives) -> ceil((total_number_of_all_grams * log(probability_of_false_positives)) / log(1 / pow(2, log(2))));

CREATE FUNCTION bfEstimateFalsePositive [ON CLUSTER cluster]
AS
(total_number_of_all_grams, number_of_hash_functions, size_of_bloom_filter_in_bytes) -> pow(1 - exp(-number_of_hash_functions/ (size_of_bloom_filter_in_bytes / total_number_of_all_grams)), number_of_hash_functions);

CREATE FUNCTION bfEstimateGramNumber [ON CLUSTER cluster]
AS
(number_of_hash_functions, probability_of_false_positives, size_of_bloom_filter_in_bytes) -> ceil(size_of_bloom_filter_in_bytes / (-number_of_hash_functions / log(1 - exp(log(probability_of_false_positives) / number_of_hash_functions))))

要使用这些函数，您需要指定至少两个参数

  • 所有 gram 的总数
  • 假阳性的概率

例如，granule 中有 4300 个 ngram，并且您希望假阳性小于 0.0001。然后可以通过执行以下查询来估计其他参数

--- estimate number of bits in the filter
SELECT bfEstimateBmSize(4300, 0.0001) / 8 AS size_of_bloom_filter_in_bytes;

┌─size_of_bloom_filter_in_bytes─┐
│                         10304 │
└───────────────────────────────┘

--- estimate number of hash functions
SELECT bfEstimateFunctions(4300, bfEstimateBmSize(4300, 0.0001)) as number_of_hash_functions

┌─number_of_hash_functions─┐
│                       13 │
└──────────────────────────┘

当然，您也可以使用上述函数来估计其他条件的参数。上述函数参考 bloom 过滤器计算器 此处。

Token bloom 过滤器​

token bloom 过滤器与 ngrambf_v1 相同，但存储 token（由非字母数字字符分隔的序列）而不是 ngram。

tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

稀疏 gram bloom 过滤器​

稀疏 gram bloom 过滤器类似于 ngrambf_v1，但使用 稀疏 gram token 代替 ngram。

sparse_grams(min_ngram_length, max_ngram_length, min_cutoff_length, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

文本索引​

支持全文搜索，有关详细信息，请参阅 此处。

向量相似度​

支持近似最近邻搜索，有关详细信息，请参阅 此处。

函数支持​

在 WHERE 子句中，条件包含对操作列的函数调用。如果该列是索引的一部分，ClickHouse 会尝试在执行函数时使用此索引。ClickHouse 支持用于使用索引的不同函数子集。

set 类型的索引可被所有函数利用。其他索引类型支持如下

函数（运算符）/ 索引主键minmaxngrambf_v1tokenbf_v1bloom_filtersparse_gramstext
等于 (=, ==)✔✔✔✔✔✔✔
不等于 (!=, <>)✔✔✔✔✔✔✔
like✔✔✔✔✗✔✔
notLike✔✔✔✔✗✔✔
match✗✗✔✔✗✔✔
startsWith✔✔✔✔✗✔✔
endsWith✗✗✔✔✗✔✔
multiSearchAny✗✗✔✗✗✗✗
in✔✔✔✔✔✔✔
notIn✔✔✔✔✔✔✔
小于 (<)✔✔✗✗✗✗✗
大于 (>)✔✔✗✗✗✗✗
小于等于 (<=)✔✔✗✗✗✗✗
大于等于 (>=)✔✔✗✗✗✗✗
empty✔✔✗✗✗✗✗
notEmpty✗✔✗✗✗✔✗
has✔✔✔✔✔✔✔
hasAny✗✗✔✔✔✔✗
hasAll✗✗✔✔✔✔✗
hasToken✗✗✗✔✗✗✔
hasTokenOrNull✗✗✗✔✗✗✔
hasTokenCaseInsensitive (*)✗✗✗✔✗✗✗
hasTokenCaseInsensitiveOrNull (*)✗✗✗✔✗✗✗
hasAnyTokens✗✗✗✗✗✗✔
hasAllTokens✗✗✗✗✗✗✔
mapContains (mapContainsKey)✗✗✗✗✗✗✔
mapContainsKeyLike✗✗✗✗✗✗✔
mapContainsValue✗✗✗✗✗✗✔
mapContainsValueLike✗✗✗✗✗✗✔

具有小于 ngram 大小的常数参数的函数不能被 ngrambf_v1 用于查询优化。

(*) 对于 hasTokenCaseInsensitive 和 hasTokenCaseInsensitiveOrNull 才能有效，必须在小写数据上创建 tokenbf_v1 索引，例如 INDEX idx (lower(str_col)) TYPE tokenbf_v1(512, 3, 0)。

注意

Bloom 过滤器可能存在误报，因此 ngrambf_v1、tokenbf_v1、sparse_grams 和 bloom_filter 索引不能用于优化期望函数结果为 false 的查询。

例如

  • 可以优化
    • s LIKE '%test%'
    • NOT s NOT LIKE '%test%'
    • s = 1
    • NOT s != 1
    • startsWith(s, 'test')
  • 不能优化
    • NOT s LIKE '%test%'
    • s NOT LIKE '%test%'
    • NOT s = 1
    • s != 1
    • NOT startsWith(s, 'test')

投影​

投影类似于 物化视图，但定义在分片级别。它提供一致性保证以及查询中的自动使用。

注意

在实现投影时，还应考虑 force_optimize_projection 设置。

在带有 FINAL 修饰符的 SELECT 语句中，不支持投影。

投影查询​

投影查询定义了投影。它隐式地从父表选择数据。语法

SELECT <column list expr> [GROUP BY] <group keys expr> [ORDER BY] <expr>

可以使用 ALTER 语句修改或删除投影。

投影索引​

投影索引通过提供一种轻量级、显式的方式来定义投影级别索引来扩展投影子系统。从概念上讲，投影索引仍然是一个投影，但具有简化的语法和更清晰的意图：它定义一个专门用于过滤的表达式，而不是作为物化数据提供。

语法​

PROJECTION <name> INDEX <index_expr> TYPE <index_type>

示例

CREATE TABLE example
(
    id UInt64,
    region String,
    user_id UInt32,
    PROJECTION region_proj INDEX region TYPE basic,
    PROJECTION uid_proj INDEX user_id TYPE basic
)
ENGINE = MergeTree
ORDER BY id;

索引类型​

当前支持

  • basic：等效于 MergeTree 索引在表达式上的正常索引。

该框架允许将来添加更多索引类型。

投影存储​

投影存储在分片目录中。它类似于索引，但包含一个子目录，该子目录存储匿名 MergeTree 表的分片。该表由投影的定义查询导出。如果存在 GROUP BY 子句，则底层存储引擎变为 AggregatingMergeTree，并且所有聚合函数都转换为 AggregateFunction。如果存在 ORDER BY 子句，则 MergeTree 表将其用作主键表达式。在合并过程中，投影分片通过其存储的合并例程进行合并。父表分片的校验和与投影的分片组合。其他维护作业类似于跳跃索引。

查询分析​

  1. 检查投影是否可以用来回答给定的查询，即，它是否生成与查询基本表相同的答案。
  2. 选择最佳可行匹配项，其中包含读取的粒度最少。
  3. 使用投影的查询管道将与使用原始分片的不同。如果某些分片中缺少投影，我们可以添加管道来“投影”它。

并发数据访问​

对于并发表访问，我们使用多版本控制。换句话说，当表同时读取和更新时，数据是从查询时当前的分片集合中读取的。没有长时间的锁。插入不会妨碍读取操作。

从表读取会自动并行化。

列和表的 TTL​

确定值的生命周期。

TTL 子句可以设置为整个表和每个单独的列。表级别的 TTL 还可以指定自动将数据在磁盘和卷之间移动或重新压缩已过期数据的逻辑。

表达式必须评估为 Date、Date32、DateTime 或 DateTime64 数据类型。

语法

设置列的生存时间

TTL time_column
TTL time_column + interval

要定义 interval，请使用 时间间隔 运算符，例如

TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR

列 TTL​

当列中的值过期时，ClickHouse 会用列数据类型的默认值替换它们。如果数据分片中的所有列值都已过期，ClickHouse 会从文件系统中删除该列。

TTL 子句不能用于键列。

示例

使用 TTL 创建表:​

CREATE TABLE tab
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

将 TTL 添加到现有表的列​

ALTER TABLE tab
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 DAY;

更改列的 TTL​

ALTER TABLE tab
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 MONTH;

表 TTL​

表可以具有用于删除过期行的表达式，以及多个表达式用于自动在 磁盘或卷 之间移动分片。当表中的行过期时，ClickHouse 会删除所有相应的行。对于移动或重新压缩分片，分片中的所有行都必须满足 TTL 表达式标准。

TTL expr
    [DELETE|RECOMPRESS codec_name1|TO DISK 'xxx'|TO VOLUME 'xxx'][, DELETE|RECOMPRESS codec_name2|TO DISK 'aaa'|TO VOLUME 'bbb'] ...
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ]

每个 TTL 表达式后都可以跟上 TTL 规则的类型。它会影响在表达式满足（达到当前时间）时要执行的操作

  • DELETE - 删除过期的行（默认操作）；
  • RECOMPRESS codec_name - 使用 codec_name 重新压缩数据分片；
  • TO DISK 'aaa' - 将分片移动到磁盘 aaa；
  • TO VOLUME 'bbb' - 将分片移动到磁盘 bbb；
  • GROUP BY - 聚合过期行。

DELETE 操作可以与 WHERE 子句一起使用，以根据过滤条件删除一些过期的行

TTL time_column + INTERVAL 1 MONTH DELETE WHERE column = 'value'

GROUP BY 表达式必须是表主键的前缀。

如果列不是 GROUP BY 表达式的一部分，并且未在 SET 子句中显式设置，则在结果行中包含来自分组行的偶然值（就像应用了聚合函数 any 一样）。

示例

使用 TTL 创建表:​

CREATE TABLE tab
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH DELETE,
    d + INTERVAL 1 WEEK TO VOLUME 'aaa',
    d + INTERVAL 2 WEEK TO DISK 'bbb';

更改表的 TTL:​

ALTER TABLE tab
    MODIFY TTL d + INTERVAL 1 DAY;

创建一个表，其中行在一个月后过期。其中日期为周一的过期行将被删除

CREATE TABLE table_with_where
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH DELETE WHERE toDayOfWeek(d) = 1;

创建一个表，其中过期行被重新压缩:​

CREATE TABLE table_for_recompression
(
    d DateTime,
    key UInt64,
    value String
) ENGINE MergeTree()
ORDER BY tuple()
PARTITION BY key
TTL d + INTERVAL 1 MONTH RECOMPRESS CODEC(ZSTD(17)), d + INTERVAL 1 YEAR RECOMPRESS CODEC(LZ4HC(10))
SETTINGS min_rows_for_wide_part = 0, min_bytes_for_wide_part = 0;

创建一个表，其中过期行被聚合。在结果行中，x 包含分组行中的最大值，y — 最小值，而 d — 来自分组行的任何偶然值。

CREATE TABLE table_for_aggregation
(
    d DateTime,
    k1 Int,
    k2 Int,
    x Int,
    y Int
)
ENGINE = MergeTree
ORDER BY (k1, k2)
TTL d + INTERVAL 1 MONTH GROUP BY k1, k2 SET x = max(x), y = min(y);

删除过期数据​

具有过期 TTL 的数据在 ClickHouse 合并数据分片时被删除。

当 ClickHouse 检测到数据已过期时，它会执行非计划合并。要控制此类合并的频率，可以设置 merge_with_ttl_timeout。如果该值太低，它将执行许多非计划合并，这可能会消耗大量资源。

如果在合并之间执行 SELECT 查询，则可能会获得过期数据。为了避免这种情况，请在 SELECT 之前使用 OPTIMIZE 查询。

参见

磁盘类型​

除了本地块设备之外，ClickHouse 支持以下存储类型

使用多个块设备进行数据存储​

简介​

MergeTree 系列表引擎可以将数据存储在多个块设备上。例如，当某个表的的数据隐式地分成“热”数据和“冷”数据时，这会很有用。最近的数据经常被请求，但只需要少量空间。相反，尾部数据很少被请求。如果可用多个磁盘，可以将“热”数据放在快速磁盘上（例如，NVMe SSD 或内存中），而将“冷”数据放在相对较慢的磁盘上（例如，HDD）。

数据分片是 MergeTree 引擎表的最小可移动单元。属于一个分片的数据存储在一个磁盘上。数据分片可以在后台（根据用户设置）以及通过 ALTER 查询在磁盘之间移动。

术语​

  • 磁盘 — 挂载到文件系统的块设备。
  • 默认磁盘 — 存储 path 服务器设置中指定的路径的磁盘。
  • 卷 — 相同磁盘的有序集合（类似于 JBOD）。
  • 存储策略 — 卷的集合以及在它们之间移动数据的规则。

可以在系统表 system.storage_policies 和 system.disks 中找到描述的实体的名称。要将其中一种配置的存储策略应用于表，请使用 MergeTree 系列表的 storage_policy 设置。

配置​

磁盘、卷和存储策略应在 <storage_configuration> 标签内声明，位于 config.d 目录中的文件中。

提示

磁盘也可以在查询的 SETTINGS 部分声明。这对于临时附加位于 URL 托管的磁盘的临时分析很有用。有关更多详细信息，请参阅 动态存储。

配置结构

<storage_configuration>
    <disks>
        <disk_name_1> <!-- disk name -->
            <path>/mnt/fast_ssd/clickhouse/</path>
        </disk_name_1>
        <disk_name_2>
            <path>/mnt/hdd1/clickhouse/</path>
            <keep_free_space_bytes>10485760</keep_free_space_bytes>
        </disk_name_2>
        <disk_name_3>
            <path>/mnt/hdd2/clickhouse/</path>
            <keep_free_space_bytes>10485760</keep_free_space_bytes>
        </disk_name_3>

        ...
    </disks>

    ...
</storage_configuration>

Tags

  • <disk_name_N> — 磁盘名称。所有磁盘的名称必须不同。
  • path — 服务器将存储数据（data 和 shadow 文件夹）的路径，应以 '/' 结尾。
  • keep_free_space_bytes — 要保留的磁盘可用空间量。

磁盘定义的顺序并不重要。

存储策略配置标记

<storage_configuration>
    ...
    <policies>
        <policy_name_1>
            <volumes>
                <volume_name_1>
                    <disk>disk_name_from_disks_configuration</disk>
                    <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
                    <load_balancing>round_robin</load_balancing>
                </volume_name_1>
                <volume_name_2>
                    <!-- configuration -->
                </volume_name_2>
                <!-- more volumes -->
            </volumes>
            <move_factor>0.2</move_factor>
        </policy_name_1>
        <policy_name_2>
            <!-- configuration -->
        </policy_name_2>

        <!-- more policies -->
    </policies>
    ...
</storage_configuration>

Tags

  • policy_name_N — 策略名称。策略名称必须唯一。
  • volume_name_N — 卷名称。卷名称必须唯一。
  • disk — 卷中的磁盘。
  • max_data_part_size_bytes — 可以存储在卷的任何磁盘上的分片的最大大小。如果合并后的分片的大小估计大于 max_data_part_size_bytes，则该分片将被写入下一个卷。基本上，此功能允许将新的/小分片保留在热（SSD）卷上，并在它们达到大尺寸时将它们移动到冷（HDD）卷上。如果您的策略只有一个卷，请不要使用此设置。
  • move_factor — 当可用空间量低于此系数时，如果存在任何可用空间，数据会自动开始移动到下一个卷（默认值为 0.1）。ClickHouse 按从大到小（降序）的顺序对现有分片进行排序，并选择总大小足以满足 move_factor 条件的分片。如果所有分片的总大小不足，则将移动所有分片。
  • perform_ttl_move_on_insert — 禁用数据分片插入时的 TTL 移动。默认情况下（如果启用），如果插入的数据分片已经过期，则立即根据 TTL 移动规则移动到在移动规则中声明的卷/磁盘。如果目标卷/磁盘速度较慢（例如，S3），这会显著降低插入速度。如果禁用，则已过期的分片数据将被写入默认卷，然后立即移动到 TTL 卷。
  • load_balancing - 磁盘平衡策略，round_robin 或 least_used。
  • least_used_ttl_ms - 配置更新所有磁盘上可用空间的超时时间（以毫秒为单位）（0 - 始终更新，-1 - 从不更新，默认值为 60000）。请注意，如果 ClickHouse 只能使用该磁盘并且不受在线文件系统调整大小/缩小影响，则可以使用 -1，在所有其他情况下不建议使用，因为它最终会导致不正确的空间分布。
  • prefer_not_to_merge — 您不应该使用此设置。禁用此卷上的数据分片合并（这有害并导致性能下降）。当此设置启用时（不要这样做），不允许在此卷上合并数据（这很糟糕）。这允许（但您不需要它）控制（如果您想控制某些事情，您就是在犯错误）ClickHouse 如何处理慢速磁盘（但 ClickHouse 知道得更好，所以请不要使用此设置）。
  • volume_priority — 定义填充卷的优先级（顺序）。较低的值表示较高的优先级。参数值应为自然数，并集体覆盖从 1 到 N（最低优先级）的范围，而不跳过任何数字。
    • 如果所有卷都已标记，则它们按给定的顺序优先。
    • 如果仅标记了某些卷，则未标记的卷具有最低优先级，并且按在配置中定义的顺序优先。
    • 如果没有卷被标记，则它们的优先级根据它们在配置中声明的顺序设置。
    • 两个卷不能具有相同的优先级值。

配置示例

<storage_configuration>
    ...
    <policies>
        <hdd_in_order> <!-- policy name -->
            <volumes>
                <single> <!-- volume name -->
                    <disk>disk1</disk>
                    <disk>disk2</disk>
                </single>
            </volumes>
        </hdd_in_order>

        <moving_from_ssd_to_hdd>
            <volumes>
                <hot>
                    <disk>fast_ssd</disk>
                    <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
                </hot>
                <cold>
                    <disk>disk1</disk>
                </cold>
            </volumes>
            <move_factor>0.2</move_factor>
        </moving_from_ssd_to_hdd>

        <small_jbod_with_external_no_merges>
            <volumes>
                <main>
                    <disk>jbod1</disk>
                </main>
                <external>
                    <disk>external</disk>
                </external>
            </volumes>
        </small_jbod_with_external_no_merges>
    </policies>
    ...
</storage_configuration>

在给定的示例中，hdd_in_order 策略实现了 轮询 方法。因此，此策略仅定义一个卷（single），数据分片以循环顺序存储在其所有磁盘上。如果将多个类似的磁盘安装到系统，但未配置 RAID，则此策略可能非常有用。请记住，每个单独的磁盘驱动器不可靠，您可能需要通过 3 或更高的复制因子来补偿它。

如果系统中可用不同类型的磁盘，则可以使用 moving_from_ssd_to_hdd 策略。卷 hot 包含一个 SSD 磁盘（fast_ssd），并且可以存储在此卷上的分片的最大大小为 1GB。所有大于 1GB 的分片将直接存储在 cold 卷上，其中包含 HDD 磁盘 disk1。此外，一旦 fast_ssd 磁盘被填充超过 80%，数据将通过后台进程传输到 disk1。

在存储策略中，卷的枚举顺序很重要，如果至少有一个卷没有显式 volume_priority 参数。一旦一个卷被填满，数据就会移动到下一个卷。磁盘的枚举顺序也很重要，因为数据会以轮流的方式存储在它们上。

创建表时，可以将其中一种配置的存储策略应用于它

CREATE TABLE table_with_non_default_policy (
    EventDate Date,
    OrderID UInt64,
    BannerID UInt64,
    SearchPhrase String
) ENGINE = MergeTree
ORDER BY (OrderID, BannerID)
PARTITION BY toYYYYMM(EventDate)
SETTINGS storage_policy = 'moving_from_ssd_to_hdd'

default 存储策略意味着仅使用一个卷，该卷由 <path> 中给定的单个磁盘组成。可以使用 [ALTER TABLE ... MODIFY SETTING] 查询在表创建后更改存储策略，新的策略应包含所有旧磁盘和卷，并具有相同的名称。

可以使用 background_move_pool_size 设置更改执行数据分片后台移动的线程数。

详细信息​

对于 MergeTree 表，数据以不同的方式进入磁盘

在所有这些情况下，除了突变和分区冻结，分片都根据给定的存储策略存储在卷和磁盘上

  1. 选择具有足够磁盘空间存储分片（unreserved_space > current_part_size）并允许存储给定大小的分片（max_data_part_size_bytes > current_part_size）的第一个卷（按定义顺序）。
  2. 在此卷内，选择遵循用于存储先前数据块的磁盘，并且具有大于分片大小的可用空间（unreserved_space - keep_free_space_bytes > current_part_size）的磁盘。

在幕后，突变和分区冻结使用 硬链接。不支持不同磁盘之间的硬链接，因此在这些情况下，生成的分片存储在与初始分片相同的磁盘上。

在后台，分片根据可用空间量（move_factor 参数）根据配置文件中声明的卷的顺序在卷之间移动。数据永远不会从最后一个卷传输到第一个卷。可以使用系统表 system.part_log（字段 type = MOVE_PART）和 system.parts（字段 path 和 disk）来监视后台移动。此外，可以在服务器日志中找到详细信息。

用户可以使用查询 ALTER TABLE ... MOVE PART|PARTITION ... TO VOLUME|DISK ... 将分片或分区从一个卷移动到另一个卷，并考虑所有后台操作的限制。该查询自行启动移动，而不等待后台操作完成。如果可用空间不足或未满足任何必需条件，用户将收到错误消息。

数据移动不会干扰数据复制。因此，可以为同一表的不同副本指定不同的存储策略。

在后台合并和突变完成后，只有在一段时间后（old_parts_lifetime）才会删除旧分片。在此期间，它们不会移动到其他卷或磁盘。因此，在最终删除分片之前，它们仍然被考虑在内以评估已占用的磁盘空间。

用户可以使用 min_bytes_to_rebalance_partition_over_jbod 设置以平衡的方式将新的大分片分配给 JBOD 卷的不同磁盘。

使用外部存储进行数据存储​

MergeTree 系列表引擎可以将数据存储到 S3、AzureBlobStorage、HDFS，使用类型为 s3、azure_blob_storage、hdfs 的磁盘。有关更多详细信息，请参阅 配置外部存储选项。

S3 作为外部存储的示例，使用类型为 s3 的磁盘。

配置标记

<storage_configuration>
    ...
    <disks>
        <s3>
            <type>s3</type>
            <support_batch_delete>true</support_batch_delete>
            <endpoint>https://clickhouse-public-datasets.s3.amazonaws.com/my-bucket/root-path/</endpoint>
            <access_key_id>your_access_key_id</access_key_id>
            <secret_access_key>your_secret_access_key</secret_access_key>
            <region></region>
            <header>Authorization: Bearer SOME-TOKEN</header>
            <server_side_encryption_customer_key_base64>your_base64_encoded_customer_key</server_side_encryption_customer_key_base64>
            <server_side_encryption_kms_key_id>your_kms_key_id</server_side_encryption_kms_key_id>
            <server_side_encryption_kms_encryption_context>your_kms_encryption_context</server_side_encryption_kms_encryption_context>
            <server_side_encryption_kms_bucket_key_enabled>true</server_side_encryption_kms_bucket_key_enabled>
            <proxy>
                <uri>http://proxy1</uri>
                <uri>http://proxy2</uri>
            </proxy>
            <connect_timeout_ms>10000</connect_timeout_ms>
            <request_timeout_ms>5000</request_timeout_ms>
            <retry_attempts>10</retry_attempts>
            <single_read_retries>4</single_read_retries>
            <min_bytes_for_seek>1000</min_bytes_for_seek>
            <metadata_path>/var/lib/clickhouse/disks/s3/</metadata_path>
            <skip_access_check>false</skip_access_check>
        </s3>
        <s3_cache>
            <type>cache</type>
            <disk>s3</disk>
            <path>/var/lib/clickhouse/disks/s3_cache/</path>
            <max_size>10Gi</max_size>
        </s3_cache>
    </disks>
    ...
</storage_configuration>

另请参阅 配置外部存储选项。

可以在共享存储上设置非复制的 MergeTree 表，采用一个写入者、多个读取者的场景。这是通过读取器上的零件列表自动刷新提供的。请注意，这需要在副本之间共享文件系统元数据（或者使用 table_disk = true 和表本地磁盘）。请参阅 refresh_parts_interval 和 table_disk。

缓存配置

ClickHouse 22.3 到 22.7 版本使用不同的缓存配置，请参阅 使用本地缓存，如果您使用的是其中一个版本。

虚拟列​

  • _part — 零件的名称。
  • _part_index — 查询结果中零件的顺序索引。
  • _part_starting_offset — 查询结果中零件的累积起始行。
  • _part_offset — 零件中的行数。
  • _part_granule_offset — 零件中的粒度数。
  • _partition_id — 分区的名称。
  • _part_uuid — 唯一的零件标识符（如果启用了 MergeTree 设置 assign_part_uuids）。
  • _part_data_version — 零件的数据版本（最小块号或变更版本）。
  • _partition_value — partition by 表达式的值（一个元组）。
  • _sample_factor — 采样因子（来自查询）。
  • _block_number — 在插入时分配给行的原始块号，在启用 enable_block_number_column 设置时在合并时持久化。
  • _block_offset — 在插入时分配给块中行的原始行号，在启用 enable_block_offset_column 设置时在合并时持久化。
  • _disk_name — 用于存储的磁盘名称。

列统计信息​

实验性功能。 了解更多。
ClickHouse Cloud 中不支持

统计信息声明位于 CREATE 查询中 *MergeTree* 系列表的列部分，当我们启用 set allow_experimental_statistics = 1 时。

CREATE TABLE tab
(
    a Int64 STATISTICS(TDigest, Uniq),
    b Float64
)
ENGINE = MergeTree
ORDER BY a

我们还可以使用 ALTER 语句操作统计信息。

ALTER TABLE tab ADD STATISTICS b TYPE TDigest, Uniq;
ALTER TABLE tab DROP STATISTICS a;

这些轻量级统计信息聚合了有关列中值分布的信息。统计信息存储在每个零件中，并在每次插入时更新。如果我们启用 set use_statistics = 1，则只能用于 prewhere 优化。

可用的列统计信息类型​

  • MinMax

    允许估计数字列上范围筛选器的选择性的最小和最大列值。

    语法：minmax

  • TDigest

    TDigest 草图，允许计算数字列的近似百分位数（例如，第 90 个百分位数）。

    语法：tdigest

  • Uniq

    HyperLogLog 草图，提供一个估计，说明一列包含多少个不同的值。

    语法：uniq

  • CountMin

    CountMin 草图，提供一列中每个值的频率的近似计数。

    语法 countmin

支持的数据类型​

(U)Int*, Float*, Decimal(), Date, Boolean, Enum*String 或 FixedString
CountMin✔✔
MinMax✔✗
TDigest✔✗
Uniq✔✔

支持的操作​

相等性筛选器 (==)范围筛选器 (>, >=, <, <=)
CountMin✔✗
MinMax✗✔
TDigest✗✔
Uniq✔✗

列级别设置​

某些 MergeTree 设置可以在列级别覆盖

  • max_compress_block_size — 在压缩写入表之前，未压缩数据的块的最大大小。
  • min_compress_block_size — 写入下一个标记时，压缩所需的未压缩数据的块的最小大小。

示例

CREATE TABLE tab
(
    id Int64,
    document String SETTINGS (min_compress_block_size = 16777216, max_compress_block_size = 16777216)
)
ENGINE = MergeTree
ORDER BY id

可以使用 ALTER MODIFY COLUMN 修改或删除列级别设置，例如

  • 删除列声明中的 SETTINGS
ALTER TABLE tab MODIFY COLUMN document REMOVE SETTINGS;
  • 修改设置
ALTER TABLE tab MODIFY COLUMN document MODIFY SETTING min_compress_block_size = 8192;
  • 重置一个或多个设置，还会删除表中 CREATE 查询的列表达式中的设置声明。
ALTER TABLE tab MODIFY COLUMN document RESET SETTING min_compress_block_size;
    © . This site is unofficial and not affiliated with ClickHouse, Inc.