ClickHouse支持查询结果缓存啦

Nauu

发布于 2023-09-02 02:54:28

1.6K00

代码可运行

文章被收录于专栏：ClickHouse的秘密基地ClickHouse的秘密基地

运行总次数：0

代码可运行

众所周知，OLAP 数据库相较于传统的 OLTP 数据库，QPS肯定是比不过的。所以为了提升QPS、提升查询性能会做一些额外的优化，比如：

将动态查询转为"静态"，也就是提前将一些常用的查询主题落表
增加查询结果缓存层
...

今天我们要聊的是查询结果缓存，在以往，这项功能要放在下游的应用层自行实现。自己时常会想，要是数据库自己就有这个功能该多方便。

现在 ClickHouse 还真就内置这项功能了，从 V23.1 开始引入了查询结果缓存，支持 SELECT 结果缓存。在缓存 TTL 期间，基于AST语法树分析，相同的 SELECT 将仅执行第一次查询，后续查询直接从缓存返回。

是不是很兴奋呢？这还不赶快试试？

我们用官方的一亿的测试数据集 hits_100m_obfuscated 进行演示。

先试试没有查询缓存的感觉：

SELECT
    RegionID,
    uniq(UserID) AS u
FROM hits_100m_obfuscated
GROUP BY RegionID
ORDER BY u DESC
LIMIT 10

Query id: 2944d569-6d9f-49c9-9b82-2f2f81fcfbd4

┌─RegionID─┬───────u─┐
│      229 │ 2853403 │
│        2 │ 1080698 │
│      208 │  831401 │
│      169 │  608301 │
│      184 │  322302 │
│      158 │  306236 │
│       34 │  298706 │
│       55 │  287611 │
│      107 │  271657 │
│       42 │  242793 │
└──────────┴─────────┘

10 rows in set. Elapsed: 0.819 sec. Processed 100.00 million rows, 1.20 GB (122.16 million rows/s., 1.47 GB/s.)

OK，现在步入正题，要使用查询结果缓存，首先要开启配置

SET allow_experimental_query_cache = true

然后在需要缓存的查询后面带上：

SETTINGS use_query_cache = true

例如第一次执行，会生成查询结果缓存：

SELECT
    RegionID,
    uniq(UserID) AS u
FROM hits_100m_obfuscated
GROUP BY RegionID
ORDER BY u DESC
LIMIT 10
SETTINGS use_query_cache = 1

Query id: 18057c67-ac9a-4dca-815f-f741b4610a0b

┌─RegionID─┬───────u─┐
│      229 │ 2853403 │
│        2 │ 1080698 │
│      208 │  831401 │
│      169 │  608301 │
│      184 │  322302 │
│      158 │  306236 │
│       34 │  298706 │
│       55 │  287611 │
│      107 │  271657 │
│       42 │  242793 │
└──────────┴─────────┘

10 rows in set. Elapsed: 0.869 sec. Processed 100.00 million rows, 1.20 GB (115.06 million rows/s., 1.38 GB/s.)

第二次执行，瞬间直接从缓存返回：

SELECT
    RegionID,
    uniq(UserID) AS u
FROM hits_100m_obfuscated
GROUP BY RegionID
ORDER BY u DESC
LIMIT 10
SETTINGS use_query_cache = 1

Query id: 89e1a32b-dc0a-4aa9-96cc-912531a34861

┌─RegionID─┬───────u─┐
│      229 │ 2853403 │
│        2 │ 1080698 │
│      208 │  831401 │
│      169 │  608301 │
│      184 │  322302 │
│      158 │  306236 │
│       34 │  298706 │
│       55 │  287611 │
│      107 │  271657 │
│       42 │  242793 │
└──────────┴─────────┘

10 rows in set. Elapsed: 0.006 sec.

是不是很爽呢？

从资源消耗层面看看缓存的威力吧，查看缓存命中率和资源消耗：

SELECT
    query,
    ProfileEvents['QueryCacheHits'] AS query_cache,
    query_duration_ms / 1000 AS query_duration,
    formatReadableSize(memory_usage) AS memory_usage,
    formatReadableQuantity(read_rows) AS read_rows,
    formatReadableSize(read_bytes) AS read_data
FROM system.query_log
WHERE (type = 'QueryFinish') AND hasAll(tables, ['cluster_test.hits_100m_obfuscated'])
ORDER BY initial_query_start_time DESC
LIMIT 2
FORMAT Vertical

Query id: 05f101cf-e250-4d3a-9fbb-cb62daf90956

Row 1:
──────
query:          SELECT RegionID, uniq(UserID) AS u FROM hits_100m_obfuscated 
GROUP BY RegionID ORDER BY u DESC LIMIT 10
SETTINGS use_query_cache = true;
query_cache:    1
query_duration: 0.005
memory_usage:   17.27 KiB
read_rows:      10.00
read_data:      120.00 B

Row 2:
──────
query:          SELECT RegionID, uniq(UserID) AS u FROM hits_100m_obfuscated 
GROUP BY RegionID ORDER BY u DESC LIMIT 10
SETTINGS use_query_cache = true;
query_cache:    0
query_duration: 0.813
memory_usage:   241.74 MiB
read_rows:      100.00 million
read_data:      1.12 GiB

时间： 0.005 vs 0.813

内存：17.27K vs 241.74MB

扫描范围：10行 vs 全表

除了上面这条运维SQL以外，我们还可以从系统表看到缓存信息：

SELECT *
FROM system.query_cache
FORMAT Vertical

Query id: e10857e9-da10-4b53-892a-b9a4707c6216

Row 1:
──────
query:       SELECT RegionID, uniq(UserID) AS u FROM hits_100m_obfuscated GROUP BY RegionID ORDER BY u DESC LIMIT 10 SETTINGS 
result_size: 512
stale:       1
shared:      1
compressed:  1
expires_at:  2023-06-24 11:28:20
key_hash:    16226283579053502780

可以清楚的看到当前缓存了哪些SQL，是否过期。

在使用 ClickHouse 查询结果缓存的时候，还有2点需要值得注意：

1、缓存TTL时间

SELECT 结果缓存的 TTL 时间默认是60秒，可以自行设置，超过了时间之后缓存会失效，例如将时间改为2秒：

SELECT RegionID, uniq(UserID) AS u FROM hits_100m_obfuscated 
GROUP BY RegionID ORDER BY u DESC LIMIT 10
SETTINGS use_query_cache = true ,query_cache_ttl = 2;

2秒以后再次查询，不会走缓存。

2、缓存大小

单个SELECT查询结果缓存大小限制默认1M，超过1M的结果不会被缓存，例如下面这条返回字符串的SQL，不能被缓存：

SELECT URL AS u FROM hits_100m_obfuscated  LIMIT 1000000
FORMAT `Null`
SETTINGS use_query_cache = true;

查询日志会发现超过阈值的提示：

QueryCache: Skipped insert (query result too big), new_entry_size_in_bytes: 1179648 (1048576), new_entry_size_in_rows: 10653 (30000000)

当前我们可以在 cong.xml 修改阈值，包括缓存大小、个数等：

<query_cache>
    <size>1073741824</size>
    <max_entries>1024</max_entries>
    <max_entry_size>1048576</max_entry_size>
    <max_entry_records>30000000</max_entry_records>
</query_cache>