ClickHouse 中基本数据类型的优化技巧

问题

我应该在 ClickHouse 中使用哪些数据类型来优化查询的速度和存储？

答案

在使用来自其他系统的自动转换或尝试选择数据类型时，用户通常会选择“多多益善”或“选择简单”或“选择最通用的”方法。这对于数百万甚至数十亿行的小型数据集可能有效。对于用户查询在用例中差异很小的那类数据集，它可能并不明显，是可以接受的。

但是，随着数据的增长和变得更加明显，它将变得不可接受。

例如，在 WebUI 中，查询需要 50 毫秒和 500 毫秒之间的差异对于大多数用例来说是可以接受的，但其中一个比另一个慢 10 倍，即使对于前端用户来说，它并不十分明显。

示例初始表

timestamp Datetime64(9),
group_id Int64,
vendor_id String,
product_id String,
category1 Int64,
code_name String,
paid_status String,
country_code String,
description String,
price Float64,
attributes Map(String, String)

示例数据

3456, 0123456789, bd6087b7-6026-4974-9122-bc99faae5d84, "2024-03-01 01:00:01.000", 98, "bear", paid", "us", "corvette model car", 123.45, {"color" : "blue", "size" : "S"}
156, 0000012345, bd6087b7-6026-4974-9122-bc99faae5d84, "2024-03-01 01:00:02:123", 45, "tiger", "not paid", "uk", "electric car", 53432.10, {"color" : "red", "model" : "X"} 
...

以下是一些可以优化此数据的建议

timestamp : DateTime64(9)
除非需要科学精度，否则 9 位精度（纳秒）可能没有必要。可能用于显示或排序，但通常不用于搜索、主键等的查询。

建议
用于 PK、按顺序排序：DateTime
用于显示或排序：添加额外的列 - 例如 timestamp_microseconds : DateTime64(6)

group_id : Int64
这似乎是一个整数，选择最小的整数类型，它将适合列所需的最大数量。从该示例数据集中，以及该列的名称，它不太可能需要五千万亿个值，可能可以使用 Int16，它最多可以有 16k 个值。

建议：Int16

vendor_id : String
该列看起来像是一个数字，但有前导零，可能需要保留格式。此外，它似乎只有特定数量的字符。

建议：FixedString(10)

product_id : String
这个是字母数字的，所以直觉上会是一个字符串，但是，它也是一个 UUID。

建议：UUID

category1 : Int64
这些值很小，可能没有很多类别，并且不会增长太多或有限制。小于 255

建议：UInt8

code_name : String
该字段看起来可能只使用有限数量的字符串。对于这种可能只有数百或数千个字符串值的低基数字段情况，它非常有用。

建议：LowCardinality(String)

paid_status : String
有一个字符串值为“已支付”或“未支付”。对于可能只有两个值的 situation，使用布尔值。

建议：Bool

country_code : String
有时，有些列会满足多个优化条件。在本例中，只有特定数量的国家代码，并且它们都是两个字符的标识符。

建议：LowCardinality(FixedString(2))

price : Float64
当已知存在固定精度时，不建议使用浮点数，尤其是对于财务数据和计算。最好使用 Decimal 类型以实现必要的精度。对于此用例，商品价格可能不会超过 999,999.00。

建议：Decimal(10,2)

attributes : map
通常，可能存在一个表，其中包含映射中的动态属性。搜索键或值通常会更慢。有几种方法可以使映射更快。如果大多数记录中存在键，最好将这些键放置在单独的列中作为低基数，并将那些稀疏的键放置在另一个列中作为高基数。从那里，创建跳跃索引会更有效，尽管它可能会增加查询的复杂性。

建议：lc_attributes: Map(String, String), hc_attributes: Map(String, String)。

根据查询，也可以使用以下选项来创建跳跃索引和/或提取属性
使用数组连接将数据提取到列中，并使用物化视图： https://clickhouse.ac.cn/docs/knowledgebase/using-array-join-to-extract-and-query-attributes
使用跳过索引来查询键值对： https://clickhouse.ac.cn/docs/knowledgebase/improve-map-performance

问题​

答案​

问题

答案