优化Key顺序提升ClickHouse查询性能

ClickHouse 键列顺序不仅影响表压缩效果，对查询性能也有很大影响，正确使用键列的顺序可以跳过大粒度数据范围，提高查询效率。本文通过示例进行测试不同场景的查询性能，从而让我们了解如何选择键列及其顺序。

测试数据

首先创建并生成表数据，用于测试：

CREATE TABLE default.test (`a` String, `b` String, `c` String)
ENGINE = MergeTree ORDER BY (a, b, c);

INSERT INTO test SELECT
    round(number / 100000), round(number / 1000), number
FROM numbers(50000000);

SELECT    uniq(a),    uniq(b),    uniq(c) FROM test;

显示结果：

┌─uniq(a)─┬─uniq(b)─┬──uniq(c)─┐
│     501 │   50001 │ 50148092 │
└─────────┴─────────┴──────────┘

我们看到每列的基数不同，因为插入数据时分母逐次变小，基数结果自然逐步增大。

因为order键的顺序位(a->b->c), 下面测试基于每列执行查询测试性能：

yfjd-990 :) select count(*) from test where a = '10000';

SELECT count(*)
FROM test
WHERE a = '10000'

返回结果：

Query id: 2105628f-252f-4d12-8cc0-4eeac10b3042

┌─count()─┐
│       0 │
└─────────┘

1 rows in set. Elapsed: 0.007 sec. Processed 8.19 thousand rows, 98.30 KB (1.16 million rows/s., 13.88 MB/s.)

扫描了8千多行，使用0.007 秒。

再看b列的查询情况：

yfjd-990 :) select count(*) from test where b = '100';

SELECT count(*)
FROM test
WHERE b = '100'

返回结果：

Query id: 2af64765-5eac-4993-b977-b7fbc77f4e9b

┌─count()─┐
│    1001 │
└─────────┘
1 rows in set. Elapsed: 0.023 sec. Processed 4.10 million rows, 56.53 MB (178.16 million rows/s., 2.45 GB/s.)

扫描了4百多万行，用了0.023 秒。慢了三倍多，扫描记录也增加了很多。

最后测试c列查询：

yfjd-990 :) select count(*) from test where c = '10000';

SELECT count(*)
FROM test
WHERE c = '10000'

返回结果：

Query id: 593aa69c-f83a-475b-aa04-063c20ed0aa4

┌─count()─┐
│       1 │
└─────────┘

1 rows in set. Elapsed: 0.122 sec. Processed 50.00 million rows, 838.89 MB (409.89 million rows/s., 6.88 GB/s.)

扫描了5千万行，使用了0.122 秒，进行了全表扫描，速度最慢。

对比三个查询，我们看到a列仅扫描8k多行，c列进行全表扫描，这是因为数据采用下面结果进行存储：

二分搜索与通用排除算法

当我们使用主键的所有列或第一列（前缀），ClickHouse采用高效的二分查找算法基于主键定位所需的粒度。

但如果跳过主键的第一列，仅使用右边部分（从索引的第二列开始），ClickHouse使用通用排除搜索算法：

该方法非常简单，我们知道ClickHouse主键按粒度组织数据。ClickHouse尝试过滤不包含目标值的粒度：

Clickhouse尝试排除不包含搜索值的粒度，通过检查目标列在每个粒度中的最大、最小值，然后跳过没在min…max范围内的粒度。

通用排除搜索性能

通用排除跳过粒度越大性能越好，可以通过让索引前缀包括更多重复值（基数更小），基数越大跳过粒度越多。这就是为什么将基数低的列放在键前面，使得二级索引列上查询效率更高。下面比较使用高基数列作为索引的第一列时的性能:

INSERT INTO test (b, c, a) SELECT
    round(number / 100000),
    round(number / 1000),
    number
FROM numbers(50000000)

查看每列的基数：

SELECT    uniq(a),    uniq(b),    uniq(c) FROM test;

┌──uniq(a)─┬─uniq(b)─┬─uniq(c)─┐
│ 50148092 │     501 │   50001 │
└──────────┴─────────┴─────────┘

现在测试性能：

select count(*) from test where b = '100';

结果如下：

yfjd-990 :) select count(*) from test where b = '100';

SELECT count(*)
FROM test
WHERE b = '100'

Query id: 8df66645-3323-4973-a64e-c36eb95e407f

┌─count()─┐
│  100001 │
└─────────┘

1 rows in set. Elapsed: 0.195 sec. Processed 50.00 million rows, 589.10 MB (256.38 million rows/s., 3.02 GB/s.)

这次ClickHouse不能有效过滤粒度，不得不执行全表扫描。因此，在表中拥有完全相同的数据、且执行相同的查询会导致10倍的性能差异，这取决于关键列的顺序:

总结

如果不确定order键顺序，使用低基数列作为第一列，高基数列作为最后列，从而确保第二索引列的查询性能。参考文档：https://medium.com/datadenys/improving-clickhouse-query-performance-tuning-key-order-f406db7cfeb9