改变排序键顺序，数出跳过的颗粒

目标

把同样的 200 万行放进排序键不同的多张表，通过 EXPLAIN indexes = 1 的颗粒数和 rows_read，确认稀疏主索引跳过了什么。最后亲自为给定的查询选出合适的排序键。

为什么重要

ClickHouse 表的排序键在创建之后很难修改，选错了，即使有索引也每次都要全部读取。把哪一列放在前面，不靠感觉，而是看“这个查询会选出多少个颗粒”。本实验的评分器不会相信你写的数字——它会给你保存的 SELECT 加上 EXPLAIN，以只读方式重新运行，并再次执行同一个 SELECT 测量读取的行数进行核对。

步骤

1. 创建数据库 sparse 和表 sparse.hits——列 site LowCardinality(String), user_id UInt32, ts DateTime, dur_ms UInt32（按此顺序），引擎 MergeTree，排序键 ORDER BY (site, user_id)。
2. 运行一次 /opt/lab/fixtures/sparse/hits.sql，写入 200 万行，并用 OPTIMIZE TABLE sparse.hits FINAL 把数据片段合并成一个。
3. 创建求 site = 'docs.example' 的行的 dur_ms 之和的 /root/ch/sparse/q_site.sql，把给该 SELECT 加上 EXPLAIN indexes = 1 的输出保存到 /root/ch/sparse/explain_site.txt，然后把 PrimaryKey 阶段选出的/全部颗粒和查找方式以 selected、total、search 写入 /root/ch/sparse/granules.json。
4. 创建求 user_id = 4242 的行的 dur_ms 之和的 /root/ch/sparse/q_user.sql，把 EXPLAIN 中选出的颗粒数、查找方式，以及这个查询的 rows_read，以 selected、search、rows_read 写入 /root/ch/sparse/user.json。
5. 创建列相同、仅排序键为 ORDER BY (user_id, site) 的 sparse.hits_us，把 sparse.hits 的行迁移过去并把数据片段合并成一个。创建在该表上求 user_id = 4242 之和的 /root/ch/sparse/q_user_us.sql，以及求 site = 'docs.example' 之和的 /root/ch/sparse/q_site_us.sql，并把两个查询的 rows_read 以 user_rows_read、site_rows_read 写入 /root/ch/sparse/order.json。
6. 创建与 sparse.hits 列和排序键相同、带有 SETTINGS index_granularity = 1024 的 sparse.hits_g1k，迁移行并合并。把该表的 marks、primary_key_size（system.parts），以及求 user_id = 4242 之和的 /root/ch/sparse/q_user_g1k.sql 的 rows_read，以 marks、primary_key_size、user_rows_read 写入 /root/ch/sparse/granularity.json。
7. 创建排序键为 ORDER BY (site, user_id, ts)、另行设置 PRIMARY KEY (site, user_id) 的 sparse.hits_pk，以及排序键相同但不写 PRIMARY KEY 的 sparse.hits_full，迁移行并合并。把两张表的 primary_key_size 以 hits_pk、hits_full 写入 /root/ch/sparse/pk.json。
8. 创建两个查询，用一天的站点报表条件 WHERE site = 'api.example' AND ts >= '2026-09-10 00:00:00' AND ts < '2026-09-11 00:00:00' 求 dur_ms 之和——在 sparse.hits 上读取的 /root/ch/sparse/q_day_hits.sql，以及在你选择排序键创建的 sparse.hits_day（列和行都相同、一个数据片段）上读取的 /root/ch/sparse/q_day.sql。q_day.sql 的 rows_read 必须是 q_day_hits.sql 的四分之一以下。把两个值以 hits_rows_read、day_rows_read 写入 /root/ch/sparse/day.json。

参考

• 服务器在 Pod 启动时就已经运行（127.0.0.1:9000）。如果停了，就运行 ch-up。
• 保存 EXPLAIN：clickhouse-client -q "EXPLAIN indexes = 1 $(cat q_site.sql)" > explain_site.txt。请看 PrimaryKey 块内部的 Granules: 고른/전체（占位符依次为选中的颗粒数与总颗粒数）（不要与上一行 ReadFromMergeTree 中的数字混淆）。
• 测量读取的行数：clickhouse-client --use_query_condition_cache 0 --queries-file q_user.sql --format JSON | jq .statistics.rows_read。一定要关闭查询条件缓存——26.8 默认是开启的，同一个条件第二次运行时，得到的是读取行数变少之后的值。评分器也是关闭后测量的。
• 可以用 CREATE TABLE 새표 AS sparse.hits ENGINE = MergeTree ORDER BY (...)（占位符为新表名）只复制列并重新指定排序键。行用 INSERT INTO 새표 SELECT * FROM sparse.hits（占位符为新表名）。
• 常见错误：不合并（没有 OPTIMIZE FINAL）就测量——有多个数据片段时，会分别统计每个数据片段的颗粒，数字就不同了。如果在 PRIMARY KEY 中写入不是排序键前缀的列，表就创建不出来。
• 官方文档：A practical introduction to primary indexes · Primary indexes · Choosing a primary key · MergeTree · EXPLAIN · system.parts

把取值种类少的列放在前面的表

创建数据库 sparse 和表 sparse.hits。列依次为 site LowCardinality(String), user_id UInt32, ts DateTime, dur_ms UInt32，引擎为 MergeTree，排序键为 ORDER BY (site, user_id)。

就是 CREATE DATABASE 和 CREATE TABLE 两条语句。site 有五种取值，user_id 有 5 万种——这是取值种类少的列在前的顺序。列名、类型、顺序不同，下一步的源脚本就写不进去。

写入 200 万行，并合并成一个数据片段

运行一次 /opt/lab/fixtures/sparse/hits.sql，向 sparse.hits 写入 2,000,000 行，并用 OPTIMIZE TABLE sparse.hits FINAL 把活动数据片段变成一个。

一次 INSERT 可能会按块大小产生多个数据片段。数据片段有多个时，EXPLAIN 会分别统计每个数据片段的颗粒，所以比较之前要先合并成一个。如果行数是 400 万，说明写了两次——TRUNCATE 之后重来。

用第一个键列过滤——二分查找

创建求 site = 'docs.example' 的行的 dur_ms 之和的 /root/ch/sparse/q_site.sql，把给该 SELECT 加上 EXPLAIN indexes = 1 的输出保存到 /root/ch/sparse/explain_site.txt。把 PrimaryKey 阶段的 Granules: 고른/전체（占位符依次为选中的颗粒数与总颗粒数）和 Search Algorithm 以 selected、total、search 写入 /root/ch/sparse/granules.json。

EXPLAIN 的输出中 Granules 会出现两次——ReadFromMergeTree 正下方的一行是最终结果，而这一步要的是 Indexes 下 PrimaryKey 块中的数字。查找方式的字符串连空格都要原样抄写。第一个键列是有序的，所以可以直接找到范围的起点和终点。

只用第二个键列过滤会怎样

创建求 user_id = 4242 的行的 dur_ms 之和的 /root/ch/sparse/q_user.sql。把 EXPLAIN 的 PrimaryKey 阶段选出的颗粒数和查找方式，与用 --use_query_condition_cache 0 运行的这个查询的 statistics.rows_read 一起，以 selected、search、rows_read 写入 /root/ch/sparse/user.json。

user_id 在每个站点段都重新从头排序，所以整体上并不是有序的。因此不用二分查找，而是采用只排除相邻标记之间前面列的值没有变化的区间的方式。请把“站点段有五个”与选出的颗粒数联系起来想一想。rows_read 会接近选出的颗粒数 × 8192。

颠倒键顺序的表

创建列相同、仅排序键为 ORDER BY (user_id, site) 的 sparse.hits_us，把 sparse.hits 的行迁移过去，并用 OPTIMIZE ... FINAL 合并。创建在该表上求 user_id = 4242 的 dur_ms 之和的 /root/ch/sparse/q_user_us.sql，以及求 site = 'docs.example' 之和的 /root/ch/sparse/q_site_us.sql，并把两个查询的 rows_read 以 user_rows_read、site_rows_read 写入 /root/ch/sparse/order.json。

用 CREATE TABLE ... AS sparse.hits ENGINE = MergeTree ORDER BY (...) 可以在复制列的同时只重新指定排序键。如果一个 8192 行的颗粒里有约 200 个用户，相邻两个标记之间，第一列 user_id 会有相同的情况吗？没有的话，后面的列 site 就无法排除任何区间。

把颗粒缩小到 1024 行会怎样

创建与 sparse.hits 列和排序键相同、带有 SETTINGS index_granularity = 1024 的 sparse.hits_g1k，迁移行并合并。把 system.parts 的 marks、primary_key_size，以及求 user_id = 4242 之和的 /root/ch/sparse/q_user_g1k.sql 的 rows_read，以 marks、primary_key_size、user_rows_read 写入 /root/ch/sparse/granularity.json。

SETTINGS 子句写在 ORDER BY 之后。颗粒变小之后，同一个条件选出的颗粒数相近，但每个颗粒读取的行数减少了。代价是标记增多，索引文件（primary_key_size）变大——设计是把索引放在内存里，所以这就是成本。marks 比颗粒数多一个。

把 PRIMARY KEY 设为排序键的前缀

创建在排序键 ORDER BY (site, user_id, ts) 上另写了 PRIMARY KEY (site, user_id) 的 sparse.hits_pk，以及排序键相同但没有写 PRIMARY KEY 的 sparse.hits_full，分别迁移行并合并。把两张表在 system.parts 中的 primary_key_size 以 hits_pk、hits_full 写入 /root/ch/sparse/pk.json。

不写 PRIMARY KEY，整个排序键就是主键。另行写出时，必须是排序键的前缀——如果写入像 (user_id) 这样不是前缀的内容，表就创建不出来。排序顺序相同，所以文件大小的差别来自索引中记录的列数。

选出适合查询的排序键

创建两个查询，用一天的站点报表条件 site = 'api.example' AND ts >= '2026-09-10 00:00:00' AND ts < '2026-09-11 00:00:00' 求 dur_ms 之和——读取 sparse.hits 的 /root/ch/sparse/q_day_hits.sql，以及读取你选择排序键创建的 sparse.hits_day（列相同，包含 sparse.hits 的全部行，一个数据片段）的 /root/ch/sparse/q_day.sql。选择键使 q_day.sql 的 rows_read 不超过 q_day_hits.sql 的四分之一，并把两个值以 hits_rows_read、day_rows_read 写入 /root/ch/sparse/day.json。

sparse.hits 可以用 site 缩小范围，但其内部是按 user_id 排序的，所以 ts 范围用不上索引。想一想条件中以等号出现的列和以范围出现的列分别是什么，以及第二列要用上索引，前面的列必须是什么样子（第 4 步）。选好之后，先用 EXPLAIN 确认。