优化索引粒度参数提升ClickHouse查询性能

news/2024/10/18 5:49:33/

当对高基数列进行过滤查询时，总是希望尽可能跳过更多的行。否则需要处理更多数据、需要更多资源。ClickHouse缺省在MergeTree表读取8192行数据块，但我们可以在创建表时调整该index_granularity 参数。本文通过示例说明如何调整该参数优化查询性能。

`index_granularity` 参数默认值

下面示例，创建表并插入1亿条记录，从1到100M-1：

CREATE TABLE deleteme
(`number` UInt64
)
ENGINE = MergeTree
PARTITION BY number % 10
ORDER BY number AS
SELECT number 
FROM numbers(100000000)

下面过滤行，条件为10000倍数的行：

SELECT *
FROM deleteme
WHERE number IN (SELECT number * 10000FROM numbers(100000)
)
FORMAT `Null`Query id: 11412bc3-05de-4790-9b65-06b139761e0cOk.0 rows in set. Elapsed: 1.211 sec. Processed 100.00 million rows, 800.00 MB (82.56 million rows/s., 660.45 MB/s.)

这里使用 FORMAT Null，我们仅想了解查询性能，无需返回结果。可以看到扫描了全表，这是因为index_granularity 默认为8192，大约10000行，意味着读所有数据块，大小800MB。

计算过程大概为，首先确定数据在哪个块，因为默认8192，因此所有块都有我们需要的数据。进入具体某个块之后再次采用二分法进行查找，虽然算法选择正确，但仍需要全表扫描。下面我们看减少index_granularity参数情况呢。

index_granularity=128

下面通过设置index_granularity=128，缩小处理数据量：

CREATE TABLE deleteme
(`number` UInt64
)
ENGINE = MergeTree
PARTITION BY number % 10
ORDER BY number
SETTINGS index_granularity=128 AS
SELECT number
FROM numbers(100000000)

下面运行上面相同查询：

SELECT *
FROM deleteme
WHERE number IN (SELECT number * 10000FROM numbers(100000)
)
FORMAT `Null`0 rows in set. Elapsed: 0.785 sec. Processed 12.84 million rows, 102.73 MB (16.35 million rows/s., 130.81 MB/s.)

可以看到仅扫描了12.84百万行，仅102.73MB大小。极大地降低了查询时间和计算成本。因为粒度变小，自动跳过了很多数据块，具体到某个快时，数据量很小，查找速度自然很快，因此在这种场景下粒度小，查询速度更快。当然，调整index_granularity也会带来存储成本，索引会变大，因此插入变慢。有时读取较小的数据块并不能提升查询性能，这取决与查询方式。尽管如此index_granularity是一个很好的技巧，可以使提升查询性能。

总结

index_granularity参数默认为8192，在基数特别大的场景中，针对单条记录查询时，选择较低的index_granularity参数值，会有效提升查询性能。参考文档：Minimize processed bytes with index granularity | ClickHouse Knowledge Base (tinybird.co)