ClickHouse支持查询结果缓存啦

原创凯朱 ClickHouse的秘密基地

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

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

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

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

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

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

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

SELECT    RegionID,    uniq(UserID) AS uFROM hits_100m_obfuscatedGROUP BY RegionIDORDER BY u DESCLIMIT 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 uFROM hits_100m_obfuscatedGROUP BY RegionIDORDER BY u DESCLIMIT 10SETTINGS 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 uFROM hits_100m_obfuscatedGROUP BY RegionIDORDER BY u DESCLIMIT 10SETTINGS 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_dataFROM system.query_logWHERE (type = 'QueryFinish') AND hasAll(tables, ['cluster_test.hits_100m_obfuscated'])ORDER BY initial_query_start_time DESCLIMIT 2FORMAT 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 10SETTINGS use_query_cache = true;query_cache:    1query_duration: 0.005memory_usage:   17.27 KiBread_rows:      10.00read_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 10SETTINGS use_query_cache = true;query_cache:    0query_duration: 0.813memory_usage:   241.74 MiBread_rows:      100.00 millionread_data:      1.12 GiB

时间： 0.005 vs 0.813

内存：17.27K vs 241.74MB

扫描范围：10行 vs 全表

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

SELECT *FROM system.query_cacheFORMAT 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: 512stale:       1shared:      1compressed:  1expires_at:  2023-06-24 11:28:20key_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 10SETTINGS use_query_cache = true ,query_cache_ttl = 2;

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

2、缓存大小

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

SELECT URL AS u FROM hits_100m_obfuscated  LIMIT 1000000FORMAT `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>

总体来说，ClickHouse 内置的查询结果缓存还方便的，赶快去试试吧

原创不易，如果这篇文章对你有帮助，欢迎点赞、转发、在看三连击

欢迎大家扫码关注我的公众号:

ClickHouse的秘密基地

往期精彩推荐:

继续滑动看下一个