排序键之外的条件 —— 用索引和投影跳过数据

目标

在排序键为 ts 的日志表中，用跳数索引和投影来缩小按键之外的列过滤的查询，并通过 EXPLAIN、rows_read、system 表确认各自何时有效、何时无效，以及付出了什么代价。

为什么重要

跳数索引与名字不同，并不是 B 树，而是颗粒组的摘要。如果数据的分布不合适，一个颗粒也跳不过，而对已有的数据片段，在 MATERIALIZE 之前不会有任何效果。投影无需修改查询，就能使用另一种排序顺序，但会占用大量磁盘。本实验的评分器不会相信你写的数字——它会把你保存的 SELECT 以只读方式重新运行，关闭条件缓存，并在关闭索引的情况下也运行一次，以确认减少的行数真的是这个索引、投影带来的。

步骤

创建数据库 skp 和表 skp.logs——列 ts DateTime, seq UInt64, service LowCardinality(String), user_id UInt32, trace_id UInt64, latency_ms UInt32, path String（按此顺序），MergeTree，ORDER BY ts。用 /opt/lab/fixtures/skip/logs.sql 一次写入 100 万行，并用 OPTIMIZE TABLE skp.logs FINAL 把数据片段变成一个。
执行 ALTER TABLE skp.logs ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 1（先不要做 MATERIALIZE），并把 EXPLAIN indexes = 1 SELECT count(), sum(latency_ms) FROM skp.logs WHERE trace_id = 12249378055674284861 的输出保存到 /root/ch/skip/explain_before.txt。
用 mutations_sync = 2 执行 MATERIALIZE INDEX idx_trace，把上面的 SELECT 保存为 /root/ch/skip/q_trace.sql，然后把关闭条件缓存后测得的 rows_read，以及该变形的 mutation_id，写入 /root/ch/skip/trace.json。
在 seq 上添加名为 idx_seq、在 user_id 上添加名为 idx_user 的 minmax 索引（GRANULARITY 1），并做 MATERIALIZE。创建求 seq BETWEEN 1001500000 AND 1001520000 的行的 sum(latency_ms) 的 /root/ch/skip/q_seq.sql，以及求 user_id BETWEEN 100 AND 120 的行的 sum(latency_ms) 的 /root/ch/skip/q_user_range.sql，并把两个查询的 rows_read 以 seq_rows_read、user_rows_read 写入 /root/ch/skip/minmax.json。
添加投影 p_user (SELECT * ORDER BY user_id) 并做 MATERIALIZE。创建求 user_id = 4242 的行的 count(), sum(latency_ms) 的 /root/ch/skip/q_user.sql，把使用投影时的 rows_read，以及用 --optimize_use_projections 0 关闭投影时的 rows_read，以 with_projection、without_projection 写入 /root/ch/skip/proj.json。
在第 5 步的查询上加上 SETTINGS log_comment = 'chs-skip-06' 运行，然后从 system.query_log 的结束（QueryFinish）记录中，把 query_id、read_rows、projections 写入 /root/ch/skip/qlog.json。
添加聚合投影 p_svc_hour (SELECT service, toStartOfHour(ts), count(), avg(latency_ms) GROUP BY service, toStartOfHour(ts)) 并做 MATERIALIZE。创建按服务输出 service, count(), round(avg(latency_ms), 3) 并按服务排序的 /root/ch/skip/q_svc.sql，把它的 rows_read 和 system.projection_parts 中 p_svc_hour 的 rows，以 rows_read、projection_rows 写入 /root/ch/skip/agg.json。
把活动数据片段的 bytes_on_disk（part_bytes）、两个投影数据片段的 bytes_on_disk（p_user_bytes、p_svc_hour_bytes）、两个索引的 data_compressed_bytes（idx_trace_bytes、idx_seq_bytes）写入 /root/ch/skip/cost.json。

参考

读取的行数用 clickhouse-client --queries-file 파일.sql --format JSON --use_query_condition_cache 0 | jq .statistics（占位符为文件名）测量。如果开着条件缓存，同一个查询第二次执行时，可能会显示读取了 0 行。
ADD INDEX、ADD PROJECTION 只修改定义。已有的数据片段需要 MATERIALIZE INDEX、MATERIALIZE PROJECTION，它们是变形，所以加上 SETTINGS mutations_sync = 2 就会等到结束。
在 EXPLAIN 的 Skip 一栏中读取 Granules: 남은/전체（占位符依次为剩余颗粒数与总数）。26.8 默认以树形（├──）打印。
常见错误：在查询中混入 ts 条件，让排序键代为过滤（评分器会在关闭索引的情况下再测一次）；在第 2 步之前就做了 MATERIALIZE。
官方文档：Understanding data skipping indexes · Use data skipping indices where appropriate · Projections · Materialized views versus projections · EXPLAIN · system.data_skipping_indices · system.projection_parts

创建按时间排序的日志表

创建数据库 skp 和表 skp.logs。列依次为 ts DateTime, seq UInt64, service LowCardinality(String), user_id UInt32, trace_id UInt64, latency_ms UInt32, path String，MergeTree，ORDER BY ts。用 /opt/lab/fixtures/skip/logs.sql 一次写入 100 万行，并用 OPTIMIZE TABLE skp.logs FINAL 把数据片段变成一个。

把数据片段合并成一个，颗粒数就固定下来，索引前后就可以用同样的刻度比较。100 万行有 123 个 8192 行的颗粒。seq 随时间增大，user_id 与时间无关、分布均匀，trace_id 每行都不同。

只定义索引时的 EXPLAIN

执行 ALTER TABLE skp.logs ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 1（先不要做 MATERIALIZE）。并把 EXPLAIN indexes = 1 SELECT count(), sum(latency_ms) FROM skp.logs WHERE trace_id = 12249378055674284861 的输出保存到 /root/ch/skip/explain_before.txt。

EXPLAIN 输出的 Indexes 之下有 PrimaryKey 一栏和 Skip 一栏。Skip 一栏的 Granules 是剩余/全部。已有的数据片段里还没有索引文件，所以索引什么也过滤不掉——也请确认 system.data_skipping_indices 中的大小。

MATERIALIZE 之后读取的行数

执行 ALTER TABLE skp.logs MATERIALIZE INDEX idx_trace SETTINGS mutations_sync = 2，并把第 2 步的 SELECT（不带 EXPLAIN）保存为 /root/ch/skip/q_trace.sql。把关闭条件缓存后测得的 statistics.rows_read 作为 rows_read，在 system.mutations 中找到的该变形的 id 作为 mutation_id，写入 /root/ch/skip/trace.json。

MATERIALIZE INDEX 是给所有数据片段重新写出索引文件的变形——请看数据片段名称末尾会带上变形编号。布隆过滤器只舍弃能断言不含该值的颗粒，所以即使要找的行只有一行，也可能因假阳性多读几个颗粒。

同样的 minmax，不同的效果

在 seq 上添加名为 idx_seq、在 user_id 上添加名为 idx_user 的 minmax 索引（GRANULARITY 1），并把两个都 MATERIALIZE。创建求 seq BETWEEN 1001500000 AND 1001520000 的行的 sum(latency_ms) 的 /root/ch/skip/q_seq.sql，以及求 user_id BETWEEN 100 AND 120 的行的 sum(latency_ms) 的 /root/ch/skip/q_user_range.sql，并把两个查询的 rows_read 以 seq_rows_read、user_rows_read 写入 /root/ch/skip/minmax.json。

minmax 只记录每个颗粒的最小值和最大值。如果是与排序键（ts）一起增大的列，每个颗粒的范围很窄，条件之外的颗粒大部分都会被排除；如果是与时间无关、分布均匀的列，所有颗粒的范围几乎都是全部，一个也排除不了。

另一种排序顺序的隐藏拷贝

执行 ALTER TABLE skp.logs ADD PROJECTION p_user (SELECT * ORDER BY user_id)，再用 mutations_sync = 2 执行 MATERIALIZE PROJECTION p_user。创建求 user_id = 4242 的行的 count(), sum(latency_ms) 的 /root/ch/skip/q_user.sql，把关闭条件缓存后测得的 rows_read 作为 with_projection，再加上 --optimize_use_projections 0 测得的值作为 without_projection，写入 /root/ch/skip/proj.json。

投影是作为子目录放进每个数据片段里的拷贝。查询保持原来的表名不变，由优化器选择读取量最少的一方。在按 user_id 排序的拷贝中，user_id 就是排序键，所以主索引能把它缩小到一个颗粒。

从记录中查看选择了哪个投影

运行 SELECT count(), sum(latency_ms) FROM skp.logs WHERE user_id = 4242 SETTINGS log_comment = 'chs-skip-06'，并从 system.query_log 的结束（type = 'QueryFinish'）记录中，把 query_id、read_rows、projections 写入 /root/ch/skip/qlog.json。

查询语句里没有投影的名称，所以用到了什么，只能从记录中得知。query_log 的 projections 列会以数据库.表.名称形式的数组，留下所使用的投影。日志大约每 1 秒清空一次，所以先执行 SYSTEM FLUSH LOGS。

预聚合的投影

添加投影 p_svc_hour (SELECT service, toStartOfHour(ts), count(), avg(latency_ms) GROUP BY service, toStartOfHour(ts)) 并做 MATERIALIZE。创建按服务输出 service, count(), round(avg(latency_ms), 3) 并按服务排序的 /root/ch/skip/q_svc.sql，把关闭条件缓存后的 rows_read，以及 system.projection_parts 中 p_svc_hour 活动数据片段的 rows，以 rows_read、projection_rows 写入 /root/ch/skip/agg.json。

带有 GROUP BY 的投影会变成隐藏的 AggregatingMergeTree，为每个小时、每个服务各保存一行 count 和 avg 的中间状态。按服务的聚合只需要把这些行再合并，所以不读取原始的 100 万行，只读取拷贝中的几千行。

让它们变快所付出的磁盘代价

把 skp.logs 活动数据片段的 bytes_on_disk 作为 part_bytes，system.projection_parts 中 p_user、p_svc_hour 活动数据片段的 bytes_on_disk 作为 p_user_bytes、p_svc_hour_bytes，system.data_skipping_indices 中 idx_trace、idx_seq 的 data_compressed_bytes 作为 idx_trace_bytes、idx_seq_bytes，写入 /root/ch/skip/cost.json。

数据片段的 bytes_on_disk 包含其中的投影子目录和索引文件。把全部重新排序的拷贝、只有几千行的聚合拷贝、布隆过滤器、minmax 并排放在一起，就能一眼看出什么最贵。