MergeTree

MergeTree 引擎和 MergeTree 家族的其他引擎（例如 ReplacingMergeTree、AggregatingMergeTree）是 ClickHouse 中最常用和最强大的表引擎。

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

MergeTree 家族表引擎的主要特性。

• 表的 primary key 决定了每个表部件内的排序顺序（聚簇索引）。primary key 也不引用单个行，而是引用称为粒度 (granules) 的 8192 行数据块。这使得海量数据集的 primary key 足够小，可以保留在主内存中，同时仍然提供对磁盘数据的快速访问。

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

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

• 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（即未指定 PRIMARY KEY），ClickHouse 将排序键用作 primary key。

如果不需要排序，可以使用语法 ORDER BY tuple()。 或者，如果启用了设置 create_table_empty_primary_key_by_default，则 ORDER BY tuple() 会隐式添加到 CREATE TABLE 语句中。请参阅 选择 Primary Key。

PARTITION BY

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

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

PRIMARY KEY

PRIMARY KEY — primary key，如果它 与排序键不同。 可选。

指定排序键（使用 ORDER BY 子句）隐式指定 primary key。 通常没有必要在排序键之外另外指定 primary key。

SAMPLE BY

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

如果指定，则必须包含在 primary key 中。 抽样表达式必须产生一个无符号整数。

示例：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) — Primary key。 类型：Tuple()
• index_granularity — 索引的粒度。 索引的“标记”之间的数据行数。 值 8192 适用于大多数任务。

示例

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

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

数据存储

表由按 primary key 排序的数据部件组成。

当数据插入到表中时，会创建单独的数据部件，并且每个部件都按 primary key 进行字典序排序。 例如，如果 primary key 是 (CounterID, Date)，则部件中的数据按 CounterID 排序，并且在每个 CounterID 中，数据按 Date 排序。

属于不同分区的数据被分隔到不同的部件中。 在后台，ClickHouse 合并数据部件以实现更高效的存储。 属于不同分区的部件不会合并。 合并机制不保证具有相同 primary key 的所有行都将位于同一数据部件中。

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

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

每个数据部件在逻辑上都分为粒度 (granules)。 粒度是 ClickHouse 在选择数据时读取的最小不可分割数据集。 ClickHouse 不拆分行或值，因此每个粒度始终包含整数行。 粒度的第一行标有该行的 primary key 值。 对于每个数据部件，ClickHouse 创建一个索引文件，用于存储标记。 对于每一列，无论它是否在 primary key 中，ClickHouse 也存储相同的标记。 这些标记使您可以直接在列文件中查找数据。

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

查询中的 Primary Key 和索引

以 (CounterID, Date) primary key 为例。 在这种情况下，排序和索引可以说明如下

完整数据: [---------------------------------------------] CounterID: [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll] Date: [1111111222222233331233211111222222333211111112122222223111112223311122333] 标记: | | | | | | | | | | | a,1 a,2 a,3 b,3 e,2 e,3 g,1 h,2 i,1 i,3 l,3 标记编号: 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] 中的数据。

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

稀疏索引允许读取额外的数据。 当读取 primary key 的单个范围时，每个数据块中最多可以读取 index_granularity * 2 个额外的行。

稀疏索引允许您处理大量的表行，因为在大多数情况下，此类索引都适合计算机的 RAM。

ClickHouse 不需要唯一的 primary key。 您可以插入具有相同 primary key 的多行。

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

选择 Primary Key

primary key 中的列数没有明确的限制。 根据数据结构，您可以在 primary key 中包含更多或更少的列。 这可能

• 提高索引的性能。

  如果 primary key 是 (a, b)，那么如果满足以下条件，则添加另一列 c 将提高性能

  • 存在对列 c 有条件的查询。
  • 对于 (a, b) 具有相同值的长数据范围（比 index_granularity 长数倍）很常见。 换句话说，当添加另一列允许您跳过相当长的数据范围时。

• 提高数据压缩率。

  ClickHouse 按 primary key 对数据进行排序，因此一致性越高，压缩效果越好。

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

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

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

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

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

选择与排序键不同的 Primary Key

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

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

在这种情况下，仅在 primary key 中保留一些列是有意义的，这些列将提供有效的范围扫描，并将剩余的维度列添加到排序键元组中。

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

在查询中使用索引和分区

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

因此，可以快速地对 primary key 的一个或多个范围运行查询。 在此示例中，当针对特定跟踪标记、特定标记和日期范围、特定标记和日期、具有日期范围的多个标记等运行时，查询将很快。

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

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 将使用 primary key 索引来修剪不正确的数据，并使用每月分区键来修剪不正确日期范围内的分区。

以上查询表明，即使对于复杂的表达式，也使用了索引。 从表中读取的组织方式是，使用索引的速度不会比全表扫描慢。

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

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

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

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

对部分单调 Primary Key 使用索引

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

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

ClickHouse 不仅对月份天数序列使用此逻辑，而且对任何表示部分单调序列的 primary key 都使用此逻辑。

数据跳过索引

索引声明位于 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

可用索引类型

MinMax

存储指定表达式的极值（如果表达式是 tuple，则存储 tuple 每个元素的极值），使用存储的信息来跳过数据块，例如 primary key。

语法：minmax

Set

存储指定表达式的唯一值（不超过 max_rows 行，max_rows=0 表示“无限制”）。 使用这些值来检查 WHERE 表达式在数据块上是否不可满足。

语法：set(max_rows)

布隆过滤器

为指定的列存储布隆过滤器。 可选的 false_positive 参数，其可能值介于 0 和 1 之间，指定从过滤器接收误报响应的概率。 默认值：0.025。 支持的数据类型：Int*、UInt*、Float*、Enum、Date、DateTime、String、FixedString、Array、LowCardinality、Nullable、UUID 和 Map。 对于 Map 数据类型，客户端可以使用 mapKeys 或 mapValues 函数指定是否应为键或值创建索引。

语法：bloom_filter([false_positive])

N-gram 布隆过滤器

存储一个布隆过滤器，其中包含来自数据块的所有 n-gram。 仅适用于数据类型：String、FixedString 和 Map。 可用于优化 EQUALS、LIKE 和 IN 表达式。

语法：ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

• n — n-gram 大小，
• size_of_bloom_filter_in_bytes — 布隆过滤器大小（以字节为单位）（您可以在此处使用较大的值，例如 256 或 512，因为它可以很好地压缩）。
• number_of_hash_functions — 布隆过滤器中使用的哈希函数数量。
• random_seed — Bloom 过滤器哈希函数的种子。

用户可以创建 UDF 来估计 ngrambf_v1 的参数集。查询语句如下：

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

要使用这些函数，我们至少需要指定两个参数。例如，如果一个数据粒度中有 4300 个 n-gram，并且我们期望假阳性率小于 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 │
└──────────────────────────┘

当然，您也可以使用这些函数通过其他条件来估计参数。这些函数参考了 这里 的内容。

Token Bloom 过滤器

与 ngrambf_v1 相同，但存储的是 token 而不是 n-gram。Token 是由非字母数字字符分隔的序列。

语法：tokenbf_v1(bloom_filter_in_bytes 的大小, 哈希函数数量, random_seed)

专用

• 一个实验性索引，用于支持近似最近邻搜索。有关详细信息，请参见 此处。
• 一个实验性全文索引，用于支持全文搜索。有关详细信息，请参见 此处。

函数支持

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

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

函数（运算符）/ 索引	主键	minmax	ngrambf_v1	tokenbf_v1	bloom_filter	full_text
等于 (=, ==)	✔	✔	✔	✔	✔	✔
不等于 (!=, <>)	✔	✔	✔	✔	✔	✔
like	✔	✔	✔	✔	✗	✔
notLike	✔	✔	✔	✔	✗	✔
match	✗	✗	✔	✔	✗	✔
startsWith	✔	✔	✔	✔	✗	✔
endsWith	✗	✗	✔	✔	✗	✔
multiSearchAny	✗	✗	✔	✗	✗	✔
in	✔	✔	✔	✔	✔	✔
notIn	✔	✔	✔	✔	✔	✔
小于 (<)	✔	✔	✗	✗	✗	✗
大于 (>)	✔	✔	✗	✗	✗	✗
小于或等于 (<=)	✔	✔	✗	✗	✗	✗
大于或等于 (>=)	✔	✔	✗	✗	✗	✗
empty	✔	✔	✗	✗	✗	✗
notEmpty	✔	✔	✗	✗	✗	✗
has	✗	✗	✔	✔	✔	✔
hasAny	✗	✗	✔	✔	✔	✗
hasAll	✗	✗	✗	✗	✔	✗
hasToken	✗	✗	✗	✔	✗	✔
hasTokenOrNull	✗	✗	✗	✔	✗	✔
hasTokenCaseInsensitive (*)	✗	✗	✗	✔	✗	✗
hasTokenCaseInsensitiveOrNull (*)	✗	✗	✗	✔	✗	✗

对于常量参数小于 n-gram 大小的函数，ngrambf_v1 无法用于查询优化。

(*) 为了使 hasTokenCaseInsensitive 和 hasTokenCaseInsensitiveOrNull 生效，必须在小写数据上创建 tokenbf_v1 索引，例如 INDEX idx (lower(str_col)) TYPE tokenbf_v1(512, 3, 0)。

注意

Bloom 过滤器可能存在假阳性匹配，因此 ngrambf_v1、tokenbf_v1 和 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')

投影

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

注意

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

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

投影查询

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

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

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

投影存储

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

查询分析

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

并发数据访问

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

从表读取数据是自动并行化的。

列和表的 TTL

确定值的生命周期。

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

表达式必须计算结果为 Date 或 DateTime 数据类型。

语法

为列设置生存时间

TTL time_column
TTL time_column + interval

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

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

列 TTL

当列中的值过期时，ClickHouse 会将它们替换为列数据类型的默认值。如果数据 part 中所有列值都过期，ClickHouse 会从文件系统中的数据 part 中删除此列。

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

表可以具有用于删除过期行的表达式，以及用于在 磁盘或卷 之间自动移动 part 的多个表达式。当表中的行过期时，ClickHouse 会删除所有相应的行。对于 part 移动或重新压缩，part 的所有行都必须满足 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 重新压缩数据 part；
• TO DISK 'aaa' - 将 part 移动到磁盘 aaa；
• TO VOLUME 'bbb' - 将 part 移动到磁盘 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);

删除过期数据

当 ClickHouse 合并数据 part 时，会删除具有过期 TTL 的数据。

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

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

另请参阅

• ttl_only_drop_parts 设置

磁盘类型

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

• s3 用于 S3 和 MinIO
• gcs 用于 GCS
• blob_storage_disk 用于 Azure Blob Storage
• hdfs 用于 HDFS
• web 用于从 Web 只读
• cache 用于本地缓存
• s3_plain 用于备份到 S3
• s3_plain_rewritable 用于 S3 中不可变、非复制的表

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

简介

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

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

术语

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

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

配置

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

提示

磁盘也可以在查询的 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>

标签

• <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>

标签

• policy_name_N — 策略名称。策略名称必须唯一。
• volume_name_N — 卷名称。卷名称必须唯一。
• disk — 卷内的磁盘。
• max_data_part_size_bytes — 可以存储在任何卷磁盘上的 part 的最大大小。如果估计合并后的 part 大小大于 max_data_part_size_bytes，则此 part 将写入下一个卷。基本上，此功能允许将新的/小的 part 保留在热（SSD）卷上，并在它们达到较大尺寸时将其移动到冷（HDD）卷。如果您的策略只有一个卷，请勿使用此设置。
• move_factor — 当可用空间量低于此因子时，数据会自动开始移动到下一个卷（如果有）（默认值为 0.1）。ClickHouse 按大小从大到小（降序）对现有 part 进行排序，并选择总大小足以满足 move_factor 条件的 part。如果所有 part 的总大小不足，则将移动所有 part。
• perform_ttl_move_on_insert — 禁用在数据 part INSERT 上的 TTL 移动。默认情况下（如果启用），如果我们插入一个已经通过 TTL 移动规则过期的数据 part，它会立即转到移动规则中声明的卷/磁盘。如果目标卷/磁盘速度较慢（例如 S3），这可能会显着减慢插入速度。如果禁用，则已过期的数据 part 将写入默认卷，然后立即移动到 TTL 卷。
• load_balancing - 磁盘平衡策略，round_robin 或 least_used。
• least_used_ttl_ms - 配置更新所有磁盘上的可用空间的超时时间（以毫秒为单位）（0 - 始终更新，-1 - 永不更新，默认值为 60000）。注意，如果磁盘只能由 ClickHouse 使用，并且不会受到在线文件系统大小调整/缩小的影响，则可以使用 -1，在所有其他情况下，不建议使用，因为最终会导致不正确的空间分配。
• prefer_not_to_merge — 您不应使用此设置。禁用在此卷上合并数据 part（这有害并导致性能下降）。启用此设置后（不要这样做），不允许在此卷上合并数据（这很糟糕）。这允许（但您不需要它）控制（如果您想控制某些东西，您就犯了一个错误）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)，数据 part 以循环顺序存储在其所有磁盘上。如果系统安装了多个类似的磁盘，但未配置 RAID，则此策略可能非常有用。请记住，每个单独的磁盘驱动器都不可靠，您可能希望使用 3 或更高的复制因子来弥补它。

如果系统中有不同类型的磁盘可用，则可以使用 moving_from_ssd_to_hdd 策略。卷 hot 由 SSD 磁盘 (fast_ssd) 组成，并且可以存储在此卷上的 part 的最大大小为 1GB。所有大小大于 1GB 的 part 将直接存储在 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] 查询在表创建后更改存储策略，新策略应包含所有旧磁盘和同名卷。

执行数据 part 后台移动的线程数可以通过 background_move_pool_size 设置进行更改。

详细信息

在 MergeTree 表的情况下，数据以不同的方式进入磁盘：

• 作为插入的结果 (INSERT 查询)。
• 在后台合并和 mutation 期间。
• 从另一个副本下载时。
• 作为分区冻结 ALTER TABLE ... FREEZE PARTITION 的结果。

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

1. 选择第一个卷（按定义顺序），该卷具有足够的磁盘空间来存储 part（unreserved_space > current_part_size）并允许存储给定大小的 part（max_data_part_size_bytes > current_part_size）。
2. 在此卷中，选择的磁盘是跟随用于存储上一个数据块的磁盘，并且具有的可用空间大于 part 大小（unreserved_space - keep_free_space_bytes > current_part_size）的磁盘。

在底层，mutation 和分区冻结使用了 硬链接。不支持不同磁盘之间的硬链接，因此在这种情况下，生成的 part 与初始 part 存储在相同的磁盘上。

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

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

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

在后台合并和 mutation 完成后，旧的 part 仅在一定时间后（old_parts_lifetime）才会被删除。在此期间，它们不会移动到其他卷或磁盘。因此，在 part 最终删除之前，它们仍然会被考虑用于评估已占用的磁盘空间。

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

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

MergeTree 系列表引擎可以使用类型为 s3、azure_blob_storage、hdfs 的磁盘将数据存储到 S3、AzureBlobStorage、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>

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

缓存配置

ClickHouse 22.3 到 22.7 版本使用不同的缓存配置，如果您正在使用这些版本之一，请参见 使用本地缓存。

虚拟列

• _part — part 的名称。
• _part_index — 查询结果中 part 的顺序索引。
• _partition_id — 分区的名称。
• _part_uuid — 唯一的 part 标识符（如果启用了 MergeTree 设置 assign_part_uuids）。
• _partition_value — partition by 表达式的值（元组）。
• _sample_factor — 样本因子（来自查询）。
• _block_number — 行的块号，当 allow_experimental_block_number_column 设置为 true 时，它会在合并时持久化。

列统计信息

实验性功能。 了解更多。

ClickHouse Cloud 中不支持

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

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;

这些轻量级统计信息聚合有关列中值分布的信息。统计信息存储在每个 part 中，并在每次插入时更新。仅当我们启用 set allow_statistics_optimize = 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;