81.【数据库】ClickHouse从入门到放弃-SummingMergeTree开启掘金成长之旅！这是我参与「掘金日新

在先前介绍MergeTree原理时曾提及，在MergeTree的每个数据分区内，数据会按照ORDER BY表达式排序。主键索引也会按照PRIMARY KEY表达式取值并排序。而ORDER BY可以指代主键，所以在一般情形下，只单独声明ORDER BY即可。此时，ORDER BY与PRIMARY KEY定义相同，数据排序与主键索引相同。

如果需要同时定义ORDER BY与PRIMARY KEY，通常只有一种可能，那便是明确希望ORDER BY与PRIMARY KEY不同。这种情况通常只会在使用SummingMergeTree或AggregatingMergeTree时才会出现。这是为何呢？这是因为SummingMergeTree与AggregatingMergeTree的聚合都是根据ORDER BY进行的。由此可以引出两点原因：主键与聚合的条件定义分离，为修改聚合条件留下空间。

现在用一个示例说明。假设一张SummingMergeTree数据表有A、B、C、D、E、F六个字段，如果需要按照A、B、C、D汇总，则有：

ORDER BY (A，B，C，D)

但是如此一来，此表的主键也被定义成了A、B、C、D。而在业务层面，其实只需要对字段A进行查询过滤，应该只使用A字段创建主键。所以，一种更加优雅的定义形式应该是：

ORDER BY (A、B、C、D)
PRIMARY KEY A

如果同时声明了ORDER BY与PRIMARY KEY，MergeTree会强制要求PRIMARY KEY列字段必须是ORDER BY的前缀。例如下面的定义是错误的：

ORDER BY (B、C)
PRIMARY KEY A

PRIMARY KEY必须是ORDER BY的前缀：

ORDER BY (B、C)
PRIMARY KEY B

这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。

假设现在业务发生了细微的变化，需要减少字段，将先前的A、B、C、D改为按照A、B聚合汇总，则可以按如下方式修改排序键：

ALTER TABLE table_name MODIFY ORDER BY (A,B)

在修改ORDER BY时会有一些限制，只能在现有的基础上减少字段。如果是新增排序字段，则只能添加通过ALTER ADD COLUMN新增的字段。但是ALTER是一种元数据的操作，修改成本很低，相比不能被修改的主键，这已经非常便利了。

现在开始正式介绍SummingMergeTree的使用方法。表引擎的声明方式如下所示：

ENGINE = SummingMergeTree((col1,col2,…))

其中，col1、col2为columns参数值，这是一个选填参数，用于设置除主键外的其他数值类型字段，以指定被SUM汇总的列字段。如若不填写此参数，则会将所有非主键的数值类型字段进行SUM汇总。接来下用一组示例说明它的使用方法：

CREATE TABLE summing_table(
    id String,
    city String,
    v1 UInt32,
    v2 Float64,
    create_time DateTime
)ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id, city)
PRIMARY KEY id

注意，这里的ORDER BY是一项关键配置，SummingMergeTree在进行数据汇总时，会根据ORDER BY表达式的取值进行聚合操作。假设此时表内的数据如下所示：

┌─id──┬─city───┬─v1─┬─v2─┬────────create_time─┐
│ A001  │ wuhan    │ 10  │ 20 │ 2019-08-10 17:00:00      │
│ A001  │ wuhan    │ 20  │ 30 │ 2019-08-20 17:00:00      │
│ A001  │ zhuhai   │ 20  │ 30 │ 2019-08-10 17:00:00      │
└─────┴───────┴───┴───┴────────────────┘
┌─id──┬─city───┬─v1─┬─v2─┬────────create_time─┐
│ A001  │ wuhan    │ 10 │ 20  │ 2019-02-10 09:00:00     │
└─────┴───────┴───┴───┴───────────────┘
┌─id──┬─city───┬─v1─┬─v2─┬────────create_time─┐
│ A002  │ wuhan    │ 60  │ 50  │ 2019-10-10 17:00:00     │
└────┴──────┴───┴───┴───────────────┘

执行optimize强制进行触发和合并操作：

optimize TABLE summing_table FINAL

再次查询，表内数据会变成下面的样子：

┌─id──┬─city───┬─v1─┬─v2─┬─────────create_time─┐
│ A001  │ wuhan    │  30  │  50  │ 2019-08-10 17:00:00       │
│ A001  │ zhuhai   │  20  │  30  │ 2019-08-10 17:00:00       │
└─────┴──────┴────┴────┴─────────────────┘
┌─id──┬─city───┬─v1─┬─v2─┬─────────create_time─┐
│ A001  │ wuhan    │  10  │  20 │ 2019-02-10 09:00:00       │
└────┴──────┴────┴────┴─────────────────┘
┌─id──┬─city───┬─v1─┬─v2─┬─────────create_time─┐
│ A002  │ wuhan    │  60  │  50  │ 2019-10-10 17:00:00       │
└────┴──────┴────┴────┴─────────────────┘

至此能够看到，在第一个分区内，同为A001:wuhan的两条数据汇总成了一行。其中，v1和v2被SUM汇总，不在汇总字段之列的create_time则选取了同组内第一行数据的取值。而不同分区之间，数据没有被汇总合并。

SummingMergeTree也支持嵌套类型的字段，在使用嵌套类型字段时，需要被SUM汇总的字段名称必须以Map后缀结尾，例如：

CREATE TABLE summing_table_nested(
    id String,
    nestMap Nested(
        id UInt32,
        key UInt32,
        val UInt64
    ),
    create_time DateTime
)ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY id

在使用嵌套数据类型的时候，默认情况下，会以嵌套类型中第一个字段作为聚合条件Key。假设表内的数据如下所示：

┌─id──┬─nestMap.id─┬─nestMap.key─┬─nestMap.val─┬─────create_time─┐
│ A001  │ [1,1,2]     │ [10,20,30]   │ [40,50,60]   │ 2019-08-10 17:00:00 │
└─────┴────────┴─────────┴─────────┴─────────────┘

上述示例中数据会按照第一个字段id聚合，汇总后的数据会变成下面的样子：

┌─id──┬─nestMap.id─┬─nestMap.key─┬─nestMap.val─┬─────create_time─┐
│ A001  │ [1,2]       │ [30,30]      │ [90,60]       │ 2019-08-10 17:00:00 │
└────┴────────┴─────────┴─────────┴─────────────┘

数据汇总的逻辑示意如下所示：

[(1, 10, 40)] + [(1, 20, 50)] -> [(1, 30, 90)]
 
[(2, 30, 60)] -> [(2, 30, 60)]

在使用嵌套数据类型的时候，也支持使用复合Key作为数据聚合的条件。为了使用复合Key，在嵌套类型的字段中，除第一个字段以外，任何名称是以Key、Id或Type为后缀结尾的字段，都将和第一个字段一起组成复合Key。例如将上面的例子中小写key改为Key：

nestMap Nested(
        id UInt32,
        Key UInt32,
        val UInt64
    ),

上述例子中数据会以id和Key作为聚合条件。

在知道了SummingMergeTree的使用方法后，现在简单梳理一下它的处理逻辑。

（1）用ORBER BY排序键作为聚合数据的条件Key。

（2）只有在合并分区的时候才会触发汇总的逻辑。

（3）以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。

（4）如果在定义引擎时指定了columns汇总列（非主键的数值类型字段），则SUM汇总这些列字段；如果未指定，则聚合所有非主键的数值类型字段。

（5）在进行数据汇总时，因为分区内的数据已经基于ORBER BY排序，所以能够找到相邻且拥有相同聚合Key的数据。

（6）在汇总数据时，同一分区内，相同聚合Key的多行数据会合并成一行。其中，汇总字段会进行SUM计算；对于那些非汇总字段，则会使用第一行数据的取值。

（7）支持嵌套结构，但列字段名称必须以Map后缀结尾。嵌套类型中，默认以第一个字段作为聚合Key。除第一个字段以外，任何名称以Key、Id或Type为后缀结尾的字段，都将和第一个字段一起组成复合Key。