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

2024年7月2日 ·4 分钟阅读

问题

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

答案

很多时候，当使用从另一个系统自动转换或尝试选择数据类型时，用户通常会选择“多多益善”或“选择更容易的”或“选择最通用的”方法。这可能适用于数百万的小型数据集，甚至数十亿行。这可能不明显，并且对于那些用户查询差异在其用例中很小的类型集是可以接受的。

然而，随着数据的增长，这将变得不可接受，并且更加明显。

查询耗时 50 毫秒和 500 毫秒之间的差异对于大多数用例来说可能是可以接受的，例如在 webUI 中，但一个比另一个慢 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
有一个字符串值“paid”或“not_paid”。对于可能只有两个值的情况，请使用布尔值。

建议：Bool

country_code : String
有时有些列可以满足多种优化。在此示例中，只有一定数量的国家/地区代码，并且它们都是两个字符的标识符。

建议：LowCardinality(FixedString(2))

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

建议：Decimal(10,2)

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

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

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

问题​

答案​

问题

答案