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

问题

为了提高查询速度和存储效率，应该在 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 就可以工作，它可以容纳多达 16,000 个值。

建议：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
通常，可能有一个包含动态属性的表。搜索键或值通常较慢。可以通过几种方式加快 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

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

问题​

答案​

问题

答案