ClickHouse性能调优之排序和数据类型

每个DBA都关心数据库性能调优，我们知道不同数据类型可以描述不能业务场景，同时也影响数据访问和有效存储。ClickHoue支持高级压缩算法提升速度和降低存储成本，优化ClickHoue存储架构提升内存和网络带宽的性能。那我们如何选择压缩算法和数据类型呢？

比较排序键

创建表并指定排序键：

CREATE TABLE default.table_one
(
    `id` UInt64
)
ENGINE = MergeTree
ORDER BY `id`

insert into table_one select number/10 from numbers(5000000)

导出数据并生成json文件：

clickhouse-client -q "select * from table_one" > table_one.json
 
gzip table_one.json

创建表不指定排序键，并插入示例数据：

CREATE TABLE default.table_two
(
    `id` UInt64
)
ENGINE = MergeTree
ORDER BY tuple()

insert into table_two select number/10 from numbers(5000000) order by rand()

ORDER BY tuple() 表示不排序，因为ORDER BY子句不能省略，ClickHouse提供了tuple()默认写法。生成json文件：

clickhouse-client -q "select * from table_two" > table_two.json
 
gzip table_two.json

比较两者的结果，该表仅包含5百万行记录，但可以看到压缩文件的大小差异非常大：

-rw-r--r-- 1 root root   837985 Jan  8 01:47 table_one.json.gz
-rw-r--r-- 1 root root 15615551 Jan  8 01:48 table_two.json.gz

“为什么ClickHouse要为一个简单的查询占用这么多内存，我们如何在生产环境中降低这一要求?”

这是经常提及的问题，重要的是通过排序键可以让内存使用大幅减少，尤其是select查询中按排序键排序。对表选择合适的排序键会极大提升性能，这勿用质疑。

已存在表排序键

对于已存在的表，排序表达式仅可以使用新增列，举例：

CREATE TABLE default.test_exp
(
    `id` UInt64,
    `duration` UInt64
)
ENGINE = MergeTree
ORDER BY id

ALTER TABLE default.test_exp ADD COLUMN version Int32, MODIFY ORDER BY (id, version)

我们现在知道ORDER BY子句是多么重要。在创建表时选择适当的排序键至关重要，同样对于高效的SELECT操作也至关重要。此外，在压缩表之前，建议重新排序当前列以减小大小;但ClickHouse可以包含新插入的列作为排序键的表达式，就如上列中的version列，不能包括duration列。

因此，正确使用排序键可以提升压缩因子20多倍，重复值相较于随机值更有利于压缩。此外，对于性能增强的SELECT查询，请记住使用排序列，减少内存使用，降低计算成本。

选择正确数据类型

在处理大型表并寻找最佳性能查询时，需要仔细选择数据类型。这里列举三个基本规则：

• 不要把整形设置为float型
• 对数值设置合适的精度，精度越低越好
• 对于文本类型尽可能使用LowCardinality(String) 或FixedString

下面通过示例查看查询中存储和处理字节的情况：

CREATE TABLE deleteme_wrong_type
(
    `number` UInt64
)
ENGINE = MergeTree
PARTITION BY number % 10
ORDER BY number AS
SELECT number % 100
FROM numbers(10000000)

ClickHoue 默认对数据进行了压缩：

SELECT
    total_rows,
    formatReadableSize(total_bytes) AS bytes
FROM system.tables
WHERE name = 'deleteme_wrong_type'
FORMAT Vertical

Row 1:
──────
total_rows: 10000000
bytes:      397.03 KiB

但是，当我们执行查询时，却处理了80MB数据：

SELECT sum(number)
FROM deleteme_wrong_type

┌─sum(number)─┐
│ 49500000000 │
└─────────────┘

1 rows in set. Elapsed: 0.013 sec. Processed 10.00 million rows, 80.00 MB (767.57 million rows/s., 6.14 GB/s.)

在上面的示例中，数据按100取余，不会超过Uint8的数据范围，所以我们采用合适的数据类型进行对比：

CREATE TABLE deleteme_right_type
(
    `number` UInt8
)
ENGINE = MergeTree
PARTITION BY number % 10
ORDER BY number AS
SELECT number % 100
FROM numbers(10000000)

存储空间变小了。。。

SELECT
    total_rows,
    formatReadableSize(total_bytes) AS bytes
FROM system.tables
WHERE name = 'deleteme_right_type'
FORMAT Vertical

Row 1:
──────
total_rows: 1000000000
bytes:      110.89 KiB

执行查询处理的字节数也有巨大差异，仅有10MB，少了8倍：

SELECT sum(number)
FROM deleteme_right_type

Query id: 8df38fab-2251-4814-aa1f-9434ca942525

┌─sum(number)─┐
│   495000000 │
└─────────────┘

1 rows in set. Elapsed: 0.005 sec. Processed 10.00 million rows, 10.00 MB (1.98 billion rows/s., 1.98 GB/s.)

假如上面的查询需要多次反复执行，对比差异会更明显。因此，小心选择字段的数据类型。

总结

本文通过简单示例说明了排序和选择合适的数据类型，对ClickHouse存占用和处理性能有较大影响。