前言

在ClickHouse中，PARTITION BY和ORDER BY是两个非常重要的子句，它们在建表时用于定义数据的分区和排序方式。以下是关于这两个子句的详细解释：

PARTITION BY

作用

PARTITION BY子句用于将数据根据指定的字段或表达式进行分区存储。每个分区在文件系统中都以目录的形式存在，这有助于减少扫描范围，优化查询速度。当查询涉及跨分区的统计时，ClickHouse会以分区为单位进行并行处理，进一步提高查询效率。

如何使用

在建表语句中，PARTITION BY子句紧随列定义之后声明。你可以指定一个字段或任意合法的表达式作为分区键。例如，常用时间字段作为分区字段，数据量大的表可以按照小时分区，数据量小的表可以按天或月分区。

CREATE TABLE test_table 
( 
  `create_time` Date, 
  `user_id` UInt32, 
  `page_views` UInt32 
) 
ENGINE = MergeTree() 
PARTITION BY toYYYYMMDD(create_time);

在这个例子中，create_time被用作分区键，数据将按照日期进行分区存储。

分区目录的命名规则

MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。MergeTree分区目录的完整 物理名称并不是只有ID而已，在ID之后还跟着一串奇怪的数字，例如 20190511_0，分区ID如何生成的。

命名规则

PartitionID_MinBlockNum_MaxBlockNum_Level

• PartitionID：分区ID, 201905 表示分区目录的ID
• MinBlockNum和MaxBlockNum：顾名思义，最小数据块编号 与最大数据块编号,1_1 表示最小的数据块编码和最大的数据块编码.
• Level :合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄。

分区目录的合并过程

属于同一个分区的多个目录，在合并之后会生成一个全新的目 录，目录中的索引和数据文件也会相应地进行合并。新目录名称的合并方式遵循以下规则，其中：

• MinBlockNum：取同一分区内所有目录中最小的MinBlockNum 值。
• MaxBlockNum：取同一分区内所有目录中最大的MaxBlockNum 值。
• Level：取同一分区内最大Level值并加1。

分区目录从创建、合并到删除的整个过程

分区目录在发生合并之后，旧的分区目 录并没有被立即删除，而是会存留一段时间。但是旧的分区目录已不 再是激活状态（active=0），所以在数据查询时，它们会被自动过滤 掉。

ORDER BY

作用

ORDER BY子句定义了表数据的物理存储顺序。这意味着数据在磁盘上会按照指定的ORDER BY列进行排序存储。这种物理排序能够极大地优化范围查询和分组查询的性能，因为相邻的数据在磁盘上也是有序的。

如何使用

在建表语句中，ORDER BY子句紧随PARTITION BY（如果指定了分区键）之后声明。如果没有指定分区键，则ORDER BY直接在表定义中紧随列定义之后。ORDER BY可以指定一列或多列，用于定义数据的排序顺序。

CREATE TABLE test_table 
( 
  `create_time` Date, 
  `user_id` UInt32, 
  `page_views` UInt32 
) 
ENGINE = MergeTree() 
ORDER BY (create_time, user_id);

在这个例子中，create_time和user_id被用作ORDER BY列，数据将首先按照create_time排序，然后在每个create_time内部按照user_id排序。

PARTITION BY 和 ORDER BY 的关系

• 共同作用：PARTITION BY和ORDER BY共同作用于数据的存储和查询优化。PARTITION BY将数据按分区键进行分区存储，而ORDER BY则定义了每个分区内部数据的排序顺序。
• 独立性：尽管它们在建表时通常一起使用，但它们是独立的子句，可以单独指定。你可以只指定PARTITION BY而不指定ORDER BY，或者只指定ORDER BY而不指定PARTITION BY（但通常不推荐这样做，因为缺少ORDER BY可能会影响查询性能）。
• 查询优化：在查询时，ClickHouse会利用PARTITION BY和ORDER BY来优化数据扫描和排序操作。通过减少扫描范围和利用有序数据，ClickHouse能够显著提高查询效率。

综上所述，PARTITION BY和ORDER BY是ClickHouse建表中非常重要的两个子句，它们共同作用于数据的存储和查询优化。在建表时，应根据业务场景和数据量大小合理选择分区键和排序键，以充分发挥ClickHouse的性能优势。