为了实现最佳性能，数据库需要优化其内部数据存储和处理管道的每一步。但是数据库执行的最好的工作是根本没有完成的工作！缓存是一种特别流行的技术，它通过存储早期计算的结果或远程数据来避免不必要的工作，而访问这些数据的成本往往很高。在今天的博文中，介绍一下 ClickHouse 缓存系列的最新成员——查询缓存，在 v23.1 版本中作为实验性特性。
一、缓存一致性问题
在实操 clickhouse 的查询缓存前需要先了解一下缓存事务问题，查询缓存通常可以分为事务一致和事务不一致。
在事务一致缓存中，如果 SELECT 查询的结果发生更改或可能发生更改，则数据库会使缓存的查询结果无效（丢弃）。在 ClickHouse 中，更改数据的操作包括在表中插入/更新/删除或折叠合并。事务一致性缓存特别适合 OLTP 数据库，例如MySQL（在v8.0之后删除了查询缓存）和 Oracle。
在事务不一致缓存中，所有缓存条目都被分配了一个有效期，之后它们就会过期，并且基础数据在此期间仅发生很小的变化，那么查询结果中的轻微不准确是可以接受的，这种方法总体上更适合 OLAP 数据库。在一些应用场景中数据的变化假如很慢，数据库就只需要计算一次报告（由第一个 SELECT 查询表示）。可以直接从查询缓存提供进一步的查询。
事务上不一致的缓存通常是由与数据库交互的客户端工具或代理包提供的
二、查询缓存实操
2.1 前期准备
这里使用 clickhouse 官方提供的 Anonymized Web Analytics Data，数据集下载
准备数据表
CREATE TABLE hits_100m_obfuscated
(
WatchID UInt64,
JavaEnable UInt8,
Title String,
GoodEvent Int16,
EventTime DateTime,
EventDate Date,
CounterID UInt32,
ClientIP UInt32,
RegionID UInt32,
UserID UInt64,
CounterClass Int8,
OS UInt8,
UserAgent UInt8,
URL String,
Referer String,
Refresh UInt8,
RefererCategoryID UInt16,
RefererRegionID UInt32,
URLCategoryID UInt16,
URLRegionID UInt32,
ResolutionWidth UInt16,
ResolutionHeight UInt16,
ResolutionDepth UInt8,
FlashMajor UInt8,
FlashMinor UInt8,
FlashMinor2 String,
NetMajor UInt8,
NetMinor UInt8,
UserAgentMajor UInt16,
UserAgentMinor FixedString(2),
CookieEnable UInt8,
JavascriptEnable UInt8,
IsMobile UInt8,
MobilePhone UInt8,
MobilePhoneModel String,
Params String,
IPNetworkID UInt32,
TraficSourceID Int8,
SearchEngineID UInt16,
SearchPhrase String,
AdvEngineID UInt8,
IsArtifical UInt8,
WindowClientWidth UInt16,
WindowClientHeight UInt16,
ClientTimeZone Int16,
ClientEventTime DateTime,
SilverlightVersion1 UInt8,
SilverlightVersion2 UInt8,
SilverlightVersion3 UInt32,
SilverlightVersion4 UInt16,
PageCharset String,
CodeVersion UInt32,
IsLink UInt8,
IsDownload UInt8,
IsNotBounce UInt8,
FUniqID UInt64,
OriginalURL String,
HID UInt32,
IsOldCounter UInt8,
IsEvent UInt8,
IsParameter UInt8,
DontCountHits UInt8,
WithHash UInt8,
HitColor FixedString(1),
LocalEventTime DateTime,
Age UInt8,
Sex UInt8,
Income UInt8,
Interests UInt16,
Robotness UInt8,
RemoteIP UInt32,
WindowName Int32,
OpenerName Int32,
HistoryLength Int16,
BrowserLanguage FixedString(2),
BrowserCountry FixedString(2),
SocialNetwork String,
SocialAction String,
HTTPError UInt16,
SendTiming UInt32,
DNSTiming UInt32,
ConnectTiming UInt32,
ResponseStartTiming UInt32,
ResponseEndTiming UInt32,
FetchTiming UInt32,
SocialSourceNetworkID UInt8,
SocialSourcePage String,
ParamPrice Int64,
ParamOrderID String,
ParamCurrency FixedString(3),
ParamCurrencyID UInt16,
OpenstatServiceName String,
OpenstatCampaignID String,
OpenstatAdID String,
OpenstatSourceID String,
UTMSource String,
UTMMedium String,
UTMCampaign String,
UTMContent String,
UTMTerm String,
FromTag String,
HasGCLID UInt8,
RefererHash UInt64,
URLHash UInt64,
CLID UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192;
导入数据建议使用 clickhouse-client 来操作，下面基于 centos 或 rpm 安装客户端
yum install -y yum-utils
yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
yum install -y clickhouse-client
导入数据
# 解压数据文件
xz -dk hits_100m_obfuscated_v1.tsv.xz
# 导入数据
cat hits_100m_obfuscated_v1.tsv | clickhouse-client -h 192.168.0.190 -u admin --password admin --query "insert into hits_100m_obfuscated FORMAT TSV" --max_insert_block_size=100000
查看数据量
select count() from hits_100m_obfuscated;
Query id: 9152e4a1-fea1-4869-9857-656fc0d4d68a
┌───count()─┐
│ 100000000 │
└───────────┘
1 row in set. Elapsed: 0.007 sec.
2.2 查询缓存
假象一个需求：根据操作系统、浏览器和引用页面（Referer），计算总访问量和访问者数量，sql 极其执行结果如下
SELECT OS AS OperatingSystem,
UserAgent AS Browser,
Referer AS ReferringPage,
COUNT(*) AS TotalVisits,
COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10;
Query id: 458deafe-25fb-4695-bbbf-87bd14e0b7ff
┌─OperatingSystem─┬─Browser─┬─ReferringPage───────┬─TotalVisits─┬─UniqueVisitors─┐
│ 44 │ 5 │ │ 2724345 │ 1261517 │
│ 44 │ 7 │ │ 2236143 │ 798198 │
│ 44 │ 2 │ │ 1713149 │ 633544 │
│ 44 │ 3 │ │ 1815864 │ 625035 │
│ 2 │ 5 │ │ 1075898 │ 515312 │
│ 2 │ 3 │ │ 1378892 │ 504849 │
│ 159 │ 32 │ │ 924871 │ 432929 │
│ 2 │ 2 │ │ 1064491 │ 407627 │
│ 2 │ 7 │ │ 914442 │ 338232 │
│ 44 │ 5 │ http://новострашная │ 464194 │ 316512 │
└─────────────────┴─────────┴─────────────────────┴─────────────┴────────────────┘
10 rows in set. Elapsed: 6.145 sec. Processed 100.00 million rows, 8.56 GB (16.27 million rows/s., 1.39 GB/s.)
平均执行时长 6 秒。
作为实验性功能查询缓存默认关闭，通过下面方式开启
set allow_experimental_query_cache = true;
同时在查询语句中显式指定启用缓存
SELECT OS AS OperatingSystem,
UserAgent AS Browser,
Referer AS ReferringPage,
COUNT(*) AS TotalVisits,
COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS use_query_cache = true;
Query id: 93098a52-adcb-421f-bc68-acbfd5b1af8b
┌─OperatingSystem─┬─Browser─┬─ReferringPage───────┬─TotalVisits─┬─UniqueVisitors─┐
│ 44 │ 5 │ │ 2724345 │ 1261517 │
│ 44 │ 7 │ │ 2236143 │ 798198 │
│ 44 │ 2 │ │ 1713149 │ 633544 │
│ 44 │ 3 │ │ 1815864 │ 625035 │
│ 2 │ 5 │ │ 1075898 │ 515312 │
│ 2 │ 3 │ │ 1378892 │ 504849 │
│ 159 │ 32 │ │ 924871 │ 432929 │
│ 2 │ 2 │ │ 1064491 │ 407627 │
│ 2 │ 7 │ │ 914442 │ 338232 │
│ 44 │ 5 │ http://новострашная │ 464194 │ 316512 │
└─────────────────┴─────────┴─────────────────────┴─────────────┴────────────────┘
10 rows in set. Elapsed: 0.003 sec.
上述结果是第二次查询，发现几乎不消耗时间，同时打印查询日志
select query_duration_ms, read_rows, read_bytes, memory_usage
from system.query_log
where query_id in ('93098a52-adcb-421f-bc68-acbfd5b1af8b', '458deafe-25fb-4695-bbbf-87bd14e0b7ff')
and type = 'QueryFinish';
Query id: b224a866-6eed-42a5-b81f-d186568e2570
┌─query_duration_ms─┬─read_rows─┬─read_bytes─┬─memory_usage─┐
│ 6125 │ 100000000 │ 8562787759 │ 14943181799 │
│ 2 │ 10 │ 301 │ 9912 │
└───────────────────┴───────────┴────────────┴──────────────┘
2 rows in set. Elapsed: 0.049 sec. Processed 1.97 thousand rows, 153.00 KB (40.50 thousand rows/s., 3.15 MB/s.)
可以看出查询缓存对用户体验的提升是极高的
虽然可以在配置文件中全局开启查询缓存，但是这样所有的 SELECT 查询（包括对系统表的监视或调试查询）都可能会返回缓存，所以还是针对特定查询语句提供缓存功能
三、进阶
3.1 缓存配置
如何确定查询是否命中缓存？语法如下
select query_id,
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 query_id in ('93098a52-adcb-421f-bc68-acbfd5b1af8b', '458deafe-25fb-4695-bbbf-87bd14e0b7ff');
Query id: 04744ba4-d3cb-4f28-84fc-81a2e7598789
┌─query_id─────────────────────────────┬─query_cache─┬─query_duration─┬─memory_usage─┬─read_rows──────┬─read_data─┐
│ 458deafe-25fb-4695-bbbf-87bd14e0b7ff │ 0 │ 6.125 │ 13.92 GiB │ 100.00 million │ 7.97 GiB │
│ 93098a52-adcb-421f-bc68-acbfd5b1af8b │ 1 │ 0.002 │ 9.68 KiB │ 10.00 │ 301.00 B │
└──────────────────────────────────────┴─────────────┴────────────────┴──────────────┴────────────────┴───────────┘
2 rows in set. Elapsed: 0.024 sec. Processed 2.00 thousand rows, 364.80 KB (83.00 thousand rows/s., 15.17 MB/s.)
如果想要更详细的了解系统中存在哪些缓存，可以查询 system.query_cache 表(结果展示太长，直接使用工具查询后截图)
其中
- query：缓存的查询语句
- result_size：缓存数据的大小，单位 byte
- stale：缓存是否可用，0 表示可用，否则为不可用，需要重新执行缓存 query 将新的结果存储到缓存中
- shard：缓存所存储的分片节点
- compressed：是否被压缩，1 表示压缩，否则缓存没有被压缩
- expires_at：缓存过期时间
- key_hash：缓存的唯一标识
key_hash 主要被用来标识哪个缓存，在 clickhouse 中查询缓存会以 hash 表的形式存储在内存中
下面来介绍一下缓存的高级用法及其配置
3.1.1 更精细的缓存控制
use_query_cache 用户开启查询缓存，但如果我们需要更精细的控制查询缓存则需要额外的配置，例如：我只需要从缓存中读取数据而不想将新的查询结果写入缓存中。
SELECT OS AS OperatingSystem,
UserAgent AS Browser,
Referer AS ReferringPage,
COUNT(*) AS TotalVisits,
COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true,
enable_writes_to_query_cache = false,
enable_reads_from_query_cache = true;
enable_writes_to_query_cache: 是否将查询缓存写入缓存中，禁止时所有的缓存都不会被写入。即：缓存如果存在直接获取，缓存失效后改查询不在缓存
enable_reads_from_query_cache: 是否从缓存中读取数据，禁止时及时缓存命中也不会获取缓存数据而是直接查询原始数据
该参数可以精细控制缓存，让用户可以精准把控业务查询是否要走缓存，因为缓存在带来查询效率提升的同时，也带来了查询不一致的情况需要在生产中结合实际场景进行合理配置
上述的两个配置需要在
use_query_cache开启的情况下才会起作用
3.1.2 缓存时间控制
从 system.query_cache 表的 expires_at 字段可以获知缓存的过期时间，默认为 1min，该配置允许用户根据实际业务需求自己配置
SELECT OS AS OperatingSystem,
UserAgent AS Browser,
Referer AS ReferringPage,
COUNT(*) AS TotalVisits,
COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true,
query_cache_ttl = 30;
query_cache_ttl: 缓存的过期时间，单位：秒
该配置交给各位看官自己去验证
3.1.3 缓存大小控制
缓存虽好，但不能过度使用。如果不加以限制服务器 OOM 随时可能发生，例如某个用户在查询明细表时开启了缓存那么将是灾难级的。好在 clickhouse 提供了缓存大小的控制。
从粗粒度层面可以控制当前节点的缓存大小和个数，在config.xml中
<query_cache>
<!-- 查询缓存总大小，单位：byte -->
<size>1073741824</size>
<!-- 可以缓存的查询条数 -->
<max_entries>1024</max_entries>
<!-- 允许缓存的单个查询最大容量 单位：byte -->
<max_entry_size>1048576</max_entry_size>
<!-- 许缓存的单个查询最大行数 -->
<max_entry_records>30000000</max_entry_records>
</query_cache>
- size：限制节点可以缓存的总大小，上面配置了 1G，如果超过阈值会删除所有过期的缓存，此时如果没有足够空间则不会插入新的条目
- max_entries：限制节点可以缓存的总条数，上面配置了 1024 条，如果超过阈值会删除所有过期的缓存，此时如果没有足够空间则不会插入新的条目
- max_entry_size：限制单个查询可以缓存的容留上限，上面配置了 1M，如果超过这个阈值该查询不会被缓存
- max_entry_records：限制单个查询可以缓存的行数上线，上面配置了 3000w，如果超过这个阈值该查询不会被缓存
从用户细粒度控制可以缓存的大小和个数，在用户独立的配置文件或用户配置域内
<profiles>
<default>
<!-- default 用户可以缓存的最大空间，单位字节 -->
<query_cache_max_size_in_bytes>10000</query_cache_max_size_in_bytes>
<!-- default 用户可以缓存的查询条数 -->
<query_cache_max_entries>100</query_cache_max_entries>
<!-- 设置配置只读，不允许修改 -->
<constraints>
<query_cache_max_size_in_bytes>
<readonly/>
</query_cache_max_size_in_bytes>
<query_cache_max_entries>
<readonly/>
<query_cache_max_entries>
</constraints>
</default>
</profiles>
如果用户需要尽可能多的缓存大数据集的话可以开启缓存压缩，当然默认就是开启的。
SELECT ...
SETTINGS use_query_cache = true,query_cache_compress_entries = true;
缓存压缩可以大幅降低内存消耗，但查询缓存的写入和读取效率将会被降低
3.1.4 缓存行为控制
为了让缓存可以被应用在频繁且耗时的查询中，可以控制查询次数和查询耗时来避免一些本身相对较快的查询来消耗缓存空间
SELECT OS AS OperatingSystem,
UserAgent AS Browser,
Referer AS ReferringPage,
COUNT(*) AS TotalVisits,
COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true,
query_cache_min_query_duration = 5000,
query_cache_min_query_runs = 2;
use_query_cache_min_query_duration: 查询至少耗时 5000 毫秒才会被缓存
use_query_cache_min_query_runs: 查询至少运行 2 次以上才会被缓存
如果都配置则需要同时满足才会被缓存
上述配置主要是为了约束将缓存空间用在真正需要被缓存的 sql 上
3.1.5 事务不一致的缓存
在使用一些带有随机语义函数的查询时 clickhouse 默认是不缓存的，例如：now() 和 rand() 函数，例如：
SELECT OS AS OperatingSystem,
UserAgent AS Browser,
Referer AS ReferringPage,
COUNT(*) AS TotalVisits,
COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
where EventDate >= toDateTime('2013-07-10 00:00:00')
and EventDate <= now()
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true;
即使开启了use_query_cache也不会被缓存，因为查询中存在不确定函数 now()，clickhouse 并不知道原表的数据何时发生变化，这就会导致此类函数的查询存在数据不一致情况。当然如果业务场景允许，需要追求极致的查询体验，可以开启query_cache_store_results_of_queries_with_nondeterministic_functions
SELECT OS AS OperatingSystem,
UserAgent AS Browser,
Referer AS ReferringPage,
COUNT(*) AS TotalVisits,
COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
where EventDate >= toDateTime('2013-07-10 00:00:00')
and EventDate <= now()
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true,
query_cache_store_results_of_queries_with_nondeterministic_functions = true;
此时查询 system.query_cache 就可以看到
3.1.6 缓存共享
clickhouse 默认不允许多个用户之间共享缓存，因为这个操作太过于危险。如果有必要通过query_cache_share_between_users开启
SELECT ...
SETTINGS use_query_cache = true, query_cache_share_between_users = true;
3.1.7 删除缓存
system drop query cache [on cluster cluster_name];
此操作会删除该节点所有缓存（过期不过期都会被删除）
3.2 不足
- 从目前来看，我并没有找到删除指定缓存的方式只能删除全部缓存，显然这个操作是被禁止的
- 暂不支持缓存淘汰策略（如：LRU），当前的做法是当缓存达到上限自动删除所有过期缓存
- 当前缓存被存储在内存的哈希表中并没有持久化，当服务器重启后缓存将会失效
当然上述的不足在 clickhouse 的 roadmap 均有体现，相信在不久将来的新版本中查询缓存将越来越优秀
![P1219 [USACO1.5] 八皇后 Checker Challenge](https://img-blog.csdnimg.cn/3b425915f3474cce816069b5b5a74f1f.png#pic_center)
![解决：请求的资源[/xxx/]不可用 描述 源服务器未能找到目标资源的表示或者是不愿公开一个已经存在的资源表示。](https://img-blog.csdnimg.cn/c31a78186b2d45cc9f3f67c80ff958c2.png)
