MergeTree

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

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

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

• 表的

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

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

• 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 tuple() 会隐式添加到 CREATE TABLE 语句中。请参阅 选择主键。

PARTITION BY

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

要按月进行分区，请使用 toYYYYMM(date_column) 表达式，其中 date_column 是包含

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 —
• 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 格式存储。

每个数据部分在逻辑上都分为多个颗粒。颗粒是 ClickHouse 在选择数据时读取的最小不可分割的数据集。ClickHouse 不会拆分行或值，因此每个颗粒始终包含整数行数。颗粒的第一行用该行的主键值标记。对于每个数据部分，ClickHouse 会创建一个索引文件，其中存储标记。对于每一列（无论它是否位于主键中），ClickHouse 也会存储相同的标记。这些标记允许您直接在列文件中查找数据。

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

查询中的主键和索引

以(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

可用的索引类型

MinMax

存储指定表达式的极值（如果表达式是tuple，则为tuple的每个元素存储极值），使用存储的信息像主键一样跳过数据块。

语法：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 - ngram大小，
• size_of_bloom_filter_in_bytes - 布隆过滤器大小（以字节为单位）（此处可以使用较大的值，例如256或512，因为它可以很好地压缩）。
• number_of_hash_functions - 布隆过滤器中使用的哈希函数数量。
• random_seed - 布隆过滤器哈希函数的种子。

用户可以创建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个ngrams，并且我们希望错误肯定率小于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布隆过滤器

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

语法：tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

专用

• 支持近似最近邻（ANN）搜索的实验性索引。有关详细信息，请参阅此处。
• 支持全文搜索的实验性全文索引。有关详细信息，请参阅此处。

函数支持

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 (*)	✗	✗	✗	✔	✗	✗

ngrambf_v1无法使用ngram大小小于的常量参数的函数进行查询优化。

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

注意

布隆过滤器可能存在误报匹配，因此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')

投影

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

注意

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

在使用FINAL修饰符的SELECT语句中不支持投影。

投影查询

投影查询是定义投影的内容。它隐式地从父表中选择数据。**语法**

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

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

投影存储

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

查询分析

1. 检查是否可以使用投影来回答给定的查询，也就是说，它生成的答案与查询基本表的结果相同。
2. 选择最佳的可行匹配，其中包含最少的需要读取的块。
3. 使用投影的查询管道将与使用原始块的查询管道不同。如果某些块中不存在投影，我们可以添加管道来动态“投影”它。

并发数据访问

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

从表中读取会自动并行化。

列和表的TTL

确定值的生存期。

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

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

语法

设置列的生存期

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

删除过期数据

当 ClickHouse 合并数据块时，带有过期 TTL 的数据会被删除。

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

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

另请参阅

• ttl_only_drop_parts 设置

磁盘类型

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

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

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

简介

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

数据块是 MergeTree 引擎表的最小可移动单元。属于一个块的数据存储在一个磁盘上。数据块可以在后台（根据用户设置）以及通过 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 — 可以存储在卷的任何磁盘上的块的最大大小。如果估计合并块的大小大于 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 表的情况下，数据以不同的方式到达磁盘

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

在所有这些情况下（变异和分区冻结除外），块根据给定的存储策略存储在卷和磁盘上。

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

列统计信息（实验性）

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

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