ClickHouse复合主键调优技巧
ClickHouse复合主键调优技巧
当我们在做ClickHouse查询性能优化时，一个通用原则就是尽可能减少扫描数据的范围。这个时候索引就起到至关重要的作用。(对索引基础知识还不甚了解的可以看看我的书)
在真实的业务中，一般都有用到复合主键，例如：
CREATE TABLE xxx (...)
ENGINE = MergeTree
ORDER BY (UserID, URL); 复合主键有多个字段，怎么摆放顺序才能获得最好的性能？
要解答这个问题，我们先来看看复合主键的查询规则：
1、当使用主键的所有列或者前缀时，Clickhouse 可以使用高效的二分法
2、其他情况就会使用通用查询算法
举个例子：假设有3个字段
ORDER BY (a, b, c)那么下面的查询索引会走二分法：
where a = xxx
where a = xxx and b = xxx
where a = xxx and b = xxx and c = xxx而这样的查询会走通用查询算法：
where b = xxx
where c = xxx
where b = xxx and c = xxx如果你不确定自己的查询走的哪种算法，可以看下查询日志：
二分法
(SelectExecutor): Running binary search on index range for part xxx
通用排除算法
(SelectExecutor): Used generic exclusion search over index for part xxx所以如果我们的查询都是按照前缀的方式查询，通常能取得不错的性能。差异主要体现在不按前缀查询的场景上。
真实的业务经常会出现查询复合主键其他字段的情况，这个时候有没有什么优化原则呢？
答案是有的，接下来我用一个例子做个测试。
准备5000w的数据，建表&准备数据：
CREATE TABLE test_cardinality (a String, b String, c String)
ENGINE = MergeTree ORDER BY (a, b, c)
INSERT INTO test_cardinality
SELECT round(number / 100000), round(number / 1000), number
FROM numbers(50000000)a, b ,c 三个字段基数从小到 a->b->c ：
SELECT
uniq(a),
uniq(b),
uniq(c)
FROM test_cardinality
Query id: 47182e7c-4796-45cc-82a9-7f498953ea36
┌─uniq(a)─┬─uniq(b)─┬──uniq(c)─┐
│ 501 │ 50001 │ 50148092 │
└─────────┴─────────┴──────────┘查询复合主键第一个字段，只需要扫描 1.6w 行数据：
select count(1) from test_cardinality where a = '10000';
┌─count()─┐
│ 0 │
└─────────┘
1 rows in set. Elapsed: 0.006 sec. Processed 16.38 thousand rows, 188.42 KB (2.60 million rows/s., 29.87 MB/s.)查询复合主键第二个字段，需要扫描 400w 行数据，注意这个结果，后面会拿来比较：
select count(1) from test_cardinality where b = '100';
┌─count()─┐
│ 1001 │
└─────────┘
1 rows in set. Elapsed: 0.026 sec. Processed 4.10 million rows, 56.53 MB (157.82 million rows/s., 2.17 GB/s.)查询复合主键第三个字段，需要全表扫描 5000w 行数据：
select count(1) from test_cardinality where c = '10000';
┌─count()─┐
│ 1 │
└─────────┘
1 rows in set. Elapsed: 0.149 sec. Processed 50.00 million rows, 838.89 MB (334.89 million rows/s., 5.62 GB/s.)再有了上面的性能基线以后，我们调换一下基数大小：
CREATE TABLE test_cardinality_1 (a String, b String, c String)
ENGINE = MergeTree ORDER BY (a, b, c)
INSERT INTO test_cardinality_1
SELECT number, round(number / 100000), round(number / 1000)
FROM numbers(50000000)现在复合主键 a, b, c 按照基数大、小、中排序
SELECT
uniq(a),
uniq(b),
uniq(c)
FROM test_cardinality_1
Query id: c5951621-8135-4790-8a48-ab5acda14fa7
┌──uniq(a)─┬─uniq(b)─┬─uniq(c)─┐
│ 50148092 │ 501 │ 50001 │
└──────────┴─────────┴─────────┘这次我们同样查询 b，结果就触发了全表扫描：
select count(1) from test_cardinality_1 where b = '100';
┌─count()─┐
│ 100001 │
└─────────┘
1 rows in set. Elapsed: 0.080 sec. Processed 50.00 million rows, 589.10 MB (622.05 million rows/s., 7.33 GB/s.)这是为什么呢？
复合主键 a,b,c ，数据的排序也会按照这个顺序排序，即a先排序，相同的a再按照b排序，相同b再排c。
所以如果能够按基数从小到大，ClickHouse的索引可以做到单调递增，从而通过当前匹配到的MarkRange就能快速推断出前后的数据是否可以排除，从而减少数据扫描范围。
所以当复合主键的多个字段，基数相差较大时，按基数从小到大的顺序性能最好。
如果复合主键的多个字段，基数相差不大呢?以后有时间再分享給大家。
本文分享自 ClickHouse的秘密基地 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！