介绍
ClickHouse 稀疏主键索引的最佳实践
在本文中，我们将深入研究 ClickHouse 索引。我们将详细说明以下问题：
您可以选择在自己的机器上自行执行本文中给出的所有 ClickHouse SQL 语句和查询。有关 ClickHouse 的安装和入门说明，请参阅快速入门。
本文重点介绍 ClickHouse 稀疏主键索引。
数据集
通过本次教程，我们将使用一个匿名的Web 流量数据集作为示例。
- 我们将使用样本数据集中 887 万行（事件）的子数据集。
- 未压缩的数据大小大约为 700 MB，并包含887 万个事件。当存储在 ClickHouse 中时，它会被压缩到 200 MB。
- 在我们的子数据集中，每行包含三列：网络用户（
UserID）在特定时间（EventTime）点击了一条URL（URL）
通过这三列，我们已经可以制定一些典型的 Web 分析查询，例如：
- 指定用户的点击次数排名前10的网址是哪些？
- 指定URL 的前10 位活跃用户是哪些？
- 用户点击指定URL 的最通常的时间（例如一周中的哪几天）是什么时候？
测试机器
本文档给出的所有运行数据，均基于在配备 Apple M1 Pro 芯片和 16GB RAM 的 MacBook Pro 上本地运行的ClickHouse 22.2.1。
全表扫描
为了展现如何在不使用主键的情况下，对我们的数据集执行查询。我们通过执行以下 SQL DDL 语句来创建一个表（使用 MergeTree 表引擎）：
CREATE TABLE hits_NoPrimaryKey
(
`UserID` UInt32,
`URL` String,
`EventTime` DateTime
)
ENGINE = MergeTree
PRIMARY KEY tuple();接下来使用以下 SQL 插入语句将hits数据集的子数据插入到表中。通过URL 表函数与结构推断结合使用，以加载在 clickhouse.com 远程托管的完整数据集的子集：
INSERT INTO hits_NoPrimaryKey SELECT
intHash32(c11::UInt64) AS UserID,
c15 AS URL,
c5 AS EventTime
FROM url('https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz')
WHERE URL != '';响应如下：
Ok.
0 rows in set. Elapsed: 145.993 sec. Processed 8.87 million rows, 18.40 GB (60.78 thousand rows/s., 126.06 MB/s.)ClickHouse 客户端的输出结果表明，上面的语句向表中成功写入了 887 万行。
最后，为了简化本文后面的讨论，并使图表和结果可重现，我们使用 FINAL 关键字[optimize] 表：
OPTIMIZE TABLE hits_NoPrimaryKey FINAL;通常，不需要也不建议在将数据加载到表后立即对其进行optimize 。但这对于这个例子为什么有必要则是显而易见的。
现在执行我们的第一个web分析查询。以下是计算 ID 为 749927693 的互联网用户的点击次数前 10 的网址：
SELECT URL, count(URL) as Count
FROM hits_NoPrimaryKey
WHERE UserID = 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;结果如下：
┌─URL────────────────────────────┬─Count─┐
│ http://auto.ru/chatay-barana.. │ 170 │
│ http://auto.ru/chatay-id=371...│ 52 │
│ http://public_search │ 45 │
│ http://kovrik-medvedevushku-...│ 36 │
│ http://forumal │ 33 │
│ http://korablitz.ru/L_1OFFER...│ 14 │
│ http://auto.ru/chatay-id=371...│ 14 │
│ http://auto.ru/chatay-john-D...│ 13 │
│ http://auto.ru/chatay-john-D...│ 10 │
│ http://wot/html?page/23600_m...│ 9 │
└────────────────────────────────┴───────┘
10 rows in set. Elapsed: 0.022 sec.
Processed 8.87 million rows,
70.45 MB (398.53 million rows/s., 3.17 GB/s.)ClickHouse 客户端的输出结果表明， ClickHouse 执行了全表扫描！我们表的 887 万行中的每一行都流式传输到了ClickHouse。这是行不通的。
为了使这种查询更有效并且更快，我们需要使用具有适当主键的表。这将允许 ClickHouse 自动（基于主键的列）创建稀疏主键索引，然后可以使用该索引明显加快示例查询的执行速度。
ClickHouse 索引设计
海量数据规模下的索引设计
在传统的关系型数据库中，表的每一行都包含一个主键索引。对于我们的数据集，这将导致主键索引: 通常是B(+)-Tree数据结构，包含 887 万条。
这样的索引可以快速定位指定行，从而提高查询和定点更新的效率。在 B(+)-Tree 数据结构中查找索引的平均时间复杂度为O(log2 n)。对于 887 万行的表，这意味着只需要23 次搜索就能定位任何索引项。
这种能力是有代价的：在向表添加新行和向索引中添加项时（有时还会重新平衡 B-Tree），会产生额外的磁盘和内存开销，以及更高的插入成本。
考虑到与 B-Tree 索引相关的挑战，ClickHouse 中的表引擎采用了不同的方法。ClickHouse MergeTree 引擎系列经过设计和优化，可处理大量数据。
这些表旨在每秒接收数百万行插入并且存储非常大（100 PB+）的数据量。
数据被快速的逐块写入表，并在后台应用规则合并各块。
在 ClickHouse 中，每个数据块都有自己的主键索引。当数据块被合并时，合并部分的主键索引也被合并。
在 ClickHouse 设计的数据规模非常大的情况下，最重要的是要非常高效地使用磁盘和内存。因此，数据块的主键索引不是对每一行都进行索引，而是在每组行数据（称为“粒度”）中有一个索引项（称为“mark”）。
这种稀疏索引是能实现的，因为 ClickHouse 将数据块的行存储在按主键列排序的磁盘上。
稀疏索引不是直接定位单行（如基于 B-Tree 的索引），而是通过它快速（通过对索引项的二分搜索）识别可能与查询匹配的行组。
然后将定位的潜在匹配行（粒度）组并行的流式传输到 ClickHouse 引擎中以找到匹配项。
这种索引设计允许索引很小（它可以而且必须完全满足主内存），同时仍然显着加快查询执行时间：特别是对于数据分析用例中典型的范围查询。
下面详细说明 ClickHouse是如何构建和使用稀疏索引的。稍后在本文中，我们将讨论一些如何选择、删除和排序用于构建索引的表列（主键列）的最佳实践。
具有主键的表
创建一个具有复合主键的表，其中包含键列 UserID 和 URL：
CREATE TABLE hits_UserID_URL
(
`UserID` UInt32,
`URL` String,
`EventTime` DateTime
)
ENGINE = MergeTree
PRIMARY KEY (UserID, URL)
ORDER BY (UserID, URL, EventTime)
SETTINGS index_granularity = 8192, index_granularity_bytes = 0;DDL语句详情
为了简化本文后面的探索，并使图表和结果可重现，DDL 声明为
通过ORDER BY子句为表指定组合排序键
通过以下设置显式控制主键索引将具有的索引项：
- index_granularity：显式设置为其默认值 8192。这意味着对于每组的 8192 行，主键索引将包含一个索引项，例如，如果表包含 16384 行，那么索引将有两个索引项。
- index_granularity_bytes：设置为 0 以禁用
自适应索引粒度。自适应索引粒度意味着 ClickHouse 自动为一组 n 行数据创建一个索引项
- 如果 n 小于 8192，但该 n 行的组合行数据的大小大于或等于 10 MB（index_granularity_bytes 的默认值）或
- 如果 n 行的组合行数据大小小于 10 MB，但 n 为 8192。
通过上面 DDL 语句中的主键索引声明，将基于两个指定列创建主键索引。
接下来写入数据：
INSERT INTO hits_UserID_URL SELECT
intHash32(c11::UInt64) AS UserID,
c15 AS URL,
c5 AS EventTime
FROM url('https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz')
WHERE URL != '';响应如下所示：
0 rows in set. Elapsed: 149.432 sec. Processed 8.87 million rows, 18.40 GB (59.38 thousand rows/s., 123.16 MB/s.)然后optimize表：
OPTIMIZE TABLE hits_UserID_URL FINAL;我们可以使用以下查询来获取有关我们表的元数据：
SELECT
part_type,
path,
formatReadableQuantity(rows) AS rows,
formatReadableSize(data_uncompressed_bytes) AS data_uncompressed_bytes,
formatReadableSize(data_compressed_bytes) AS data_compressed_bytes,
formatReadableSize(primary_key_bytes_in_memory) AS primary_key_bytes_in_memory,
marks,
formatReadableSize(bytes_on_disk) AS bytes_on_disk
FROM system.parts
WHERE (table = 'hits_UserID_URL') AND (active = 1)
FORMAT Vertical;结果如下：
part_type: Wide
path: ./store/d9f/d9f36a1a-d2e6-46d4-8fb5-ffe9ad0d5aed/all_1_9_2/
rows: 8.87 million
data_uncompressed_bytes: 733.28 MiB
data_compressed_bytes: 206.94 MiB
primary_key_bytes_in_memory: 96.93 KiB
marks: 1083
bytes_on_disk: 207.07 MiB
1 rows in set. Elapsed: 0.003 sec.ClickHouse 客户端的输出表明：
- 表数据以宽格式存储在磁盘上的特定目录中，这意味着该目录中的每个表列将有一个数据文件（和一个标记文件）。
- 该表有 887 万行。
- 所有行的未压缩数据大小合计为 733.28 MB。
- 所有行的压缩磁盘数据大小合计为 206.94 MB。
- 该表有一个包含 1083 项（称为“mark”）的主键索引，索引的大小为 96.93 KB。
- 表的数据和标记文件以及主键索引文件总共占用了 207.07 MB 磁盘空间。
数据按主键列顺序存储在磁盘上
我们在上面创建的表包含有
如果我们只指定排序键，那么主键将被隐式定义为排序键。
为了提高内存效率，我们明确的指定了一个主键，它只包含我们查询需要过滤的列。因为基于主键的索引会被完全加载到主存中。
为了使文章的图表中保持一致性，并为了最大限度地提高压缩率，我们定义了一个单独的排序键，其中包括我们表的所有列（如果在一列中相似的数据彼此接近，例如通过排序，那么数据将被更好地压缩）。
如果两者都指定，则主键必须为排序键的前缀。
插入的行按主键列（以及排序键中的附加 EventTime 列）按字典顺序（升序）存储在磁盘上。
ClickHouse 允许插入具有相同主键列值的多行。在这种情况下（参见下图中的第 1 行和第 2 行），最终顺序由指定的排序键决定，因此由EventTime列的值决定。
ClickHouse 是一个面向列的数据库管理系统。如下图所示
- 对于磁盘来说，表的每个列都有一个数据文件（*.bin），其中该列的所有值都以压缩格式存储。
- 887 万行按主键列（和附加的排序键列）按字典升序存储在磁盘上，即在这种情况下
- 首先由UserID排序，
- 然后通过URL排序，
- 最后是EventTime排序：
UserID.bin、URL.bin 和 EventTime.bin 是磁盘上的数据文件，其中存储了UserID、URL和EventTime列的值。
注意
- 由于主键定义了磁盘上行的字典顺序，因此一张表只能有一个主键。
- 我们从 0 开始对行进行编号，以便与 ClickHouse 内部行编号结构对齐，该方案也用于记录消息。
数据被分成粒度以进行并行数据处理
出于数据处理的目的，一个表的列值在逻辑上被划分为粒度组。粒度组是ClickHouse 进行流式数据处理的最小不可分割的数据集。这意味着，ClickHouse 不是读取单个行，而是始终读取（以流式和并行方式）整个组（粒度组）的行数据。
列值并不是实际存储在粒度组内部：粒度组只是用于查询处理的列值的 逻辑 组织。
下图说明了我们表的 887 万行（列值）是如何被组织成 1083 个粒度组的，这是由于表的 DDL 语句设置了index_granularity（设置为其默认值 8192）。
第一个（基于磁盘上的物理顺序）8192 行（它们的列值）逻辑上属于粒度 0，然后接下来的 8192 行（它们的列值）属于粒度1，依此类推。
注意
- 最后一个粒度组（粒度值1082）“包含”少于 8192 行。
- 我们将主键列（UserID、URL）中的一些列值标记为橙色。
这些橙色标记的列值是每个粒度组中每个主键列的最小值。这里的例外是我们标记最后一个粒度组（上图中的1082粒度组）的最大值。
正如我们将在下面看到的，这些橙色标记的列值将是表的主键索引中的索引项。
- 我们从 0 开始对粒度组进行编号，以便与 ClickHouse 内部编号方案保持一致，该方案也用于记录消息。
主键索引每个粒度组有一个索引项
主键索引是基于上图所示的粒度组创建的。该索引是一个未压缩的扁平化数组文件 (primary.idx)，包含从 0 开始的所谓数字索引标记。
如下图显示，索引文件存储了每个粒度组的最小主键列值（上图中橙色标记的值）。例如
- 第一个索引项（下图中的’mark 0’）存储上图中粒度组0的主键列的最小值，
- 第二个索引项（下图中的’mark 1’）存储上图中粒度组1的主键列的最小值，依此类推。
我们的表总共有 1083 个索引项，887 万行数据和 1083 个粒度组：
注意
- 最后一个索引项（上图中的’mark 1082’）是存储上图中粒度组1082的主键列的最大值。
- 索引项（索引标记）不是基于我们表中的特定行，而是基于粒度组。例如，对于上图中的索引项’mark 0’，我们的表中没有UserID为240.923且URL为”goal://metry=10000467796a411…”的行。相反，表中有一个粒度组 0，其中在该粒度组中，最小 UserID 值为240.923，最小 URL 值为“goal://metry=10000467796a411…”，这两个值来自不同的行。
- 主键索引文件被完全加载到主内存中。如果文件大于可用的内存空间，那么ClickHouse 将抛出错误。
主键索引项被称为索引标记，因为每个索引项都标记特定数据范围的最小值。对于示例表：
UserID索引标记：
主键索引中存储的UserID值按升序排列。
上图中的“mark 1”因此代表了在粒度组 1 中的所有行的UserID值，以及在所有后续粒度组中，都保证大于或等于 4.073.710。
正如我们稍后将看到的，当查询在主键的第一列上进行过滤时，这种全局顺序使 ClickHouse 能够在第一个主键列的索引标记上使用二分查找算法。
URL 索引标记：
主键列UserID和URL的基数非常相似，这意味着第一列之后的所有主键列的索引标记通常只代表每个粒度组的数据范围。
例如，上图中的URL列的’mark 0’表示粒度组0中所有行的URL值都保证大于等于goal://metry=10000467796a411 …。但是，对于粒度组1中所有行的URL值则不能给出同样的保证，因为UserID列的“mark 1”与“mark 0”具有不同的 UserID 值。稍后我们将更详细地讨论这对查询执行性能的影响。
主键索引用于选择粒度组
我们现在可以在主键索引的支持下执行以下查询。
下面计算UserID 为 749927693 点击次数前十的 url。
SELECT URL, count(URL) AS Count
FROM hits_UserID_URL
WHERE UserID = 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;响应如下：
┌─URL────────────────────────────┬─Count─┐
│ http://auto.ru/chatay-barana.. │ 170 │
│ http://auto.ru/chatay-id=371...│ 52 │
│ http://public_search │ 45 │
│ http://kovrik-medvedevushku-...│ 36 │
│ http://forumal │ 33 │
│ http://korablitz.ru/L_1OFFER...│ 14 │
│ http://auto.ru/chatay-id=371...│ 14 │
│ http://auto.ru/chatay-john-D...│ 13 │
│ http://auto.ru/chatay-john-D...│ 10 │
│ http://wot/html?page/23600_m...│ 9 │
└────────────────────────────────┴───────┘
10 rows in set. Elapsed: 0.005 sec.
Processed 8.19 thousand rows,
740.18 KB (1.53 million rows/s., 138.59 MB/s.)ClickHouse 客户端的输出此时说明，不是进行全表扫描，而是只有 8190行数据流式传输到 ClickHouse。
如果启用了跟踪日志，ClickHouse 服务器日志文件将显示 ClickHouse 正在对 1083 个 UserID 索引标记执行二分查找，从而找到可能包含 UserID 列值为749927693的行的粒度组。这需要平均时间复杂度为O(log2 n)的 19 个步骤：
...Executor): Key condition: (column 0 in [749927693, 749927693])
...Executor): Running binary search on index range for part all_1_9_2 (1083 marks)
...Executor): Found (LEFT) boundary mark: 176
...Executor): Found (RIGHT) boundary mark: 177
...Executor): Found continuous range in 19 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
1/1083 marks by primary key, 1 marks to read from 1 ranges
...Reading ...approx. 8192 rows starting from 1441792我们从上面的跟踪日志中可以看出，1083 个现有标记中的一个标记满足查询。
跟踪日志细节
标记 176 被识别（包含“找到的左边界标记”，排除“找到的右边界标记”），因此来自粒度 176 的所有 8192 行（从第 1.441.792 行开始 - 我们将在稍后看到）流式传输到 ClickHouse 中，以查找 UserID 列值为749927693的实际行。
我们还可以通过在示例查询中使用EXPLAIN 子句来重现这一点：
EXPLAIN indexes = 1
SELECT URL, count(URL) AS Count
FROM hits_UserID_URL
WHERE UserID = 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;响应如下所示：
┌─explain───────────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection) │
│ Limit (preliminary LIMIT (without OFFSET)) │
│ Sorting (Sorting for ORDER BY) │
│ Expression (Before ORDER BY) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ Filter (WHERE) │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ ReadFromMergeTree │
│ Indexes: │
│ PrimaryKey │
│ Keys: │
│ UserID │
│ Condition: (UserID in [749927693, 749927693]) │
│ Parts: 1/1 │
│ Granules: 1/1083 │
└───────────────────────────────────────────────────────────────────────────────────────┘
16 rows in set. Elapsed: 0.003 sec.客户端输出表明1083 个粒度组中的一个被选为可能包含 UserID 列值为 749927693 的行。
结论
当对作为组合主键的一部分且是第一个主键列进行查询过滤时，ClickHouse 将在主键列的索引标记上运行二分查找算法。
如上所述，ClickHouse 正在使用其稀疏主键索引来快速（通过二分查找）选择可能包含与查询匹配的行的粒度组。
这是 ClickHouse 执行查询的第一阶段（粒度组选择）。
在第二阶段（数据读取），ClickHouse 定位到选定的粒度组，以便将它们的所有行数据流式传输到 ClickHouse 引擎，从而找到实际匹配查询的行。
我们接下来将再更详细地讨论第二阶段。
标记文件用于定位粒度组
下图展示了我们表的主键索引文件的一部分。
如上所述，通过对索引的 1083 个UserID标记进行二分搜索，识别出标记 176。因此，其对应的粒度组176 可能包含 UserID 列值为 749.927.693 的行。
粒度组选择细节
上图显示，标记 176 是第一个匹配的索引项，其中粒度组 176 的最小 UserID 值小于 749.927.693，并且下一个标记（标记 177）的粒度组177 的最小 UserID 值大于此值。因此，只有176 标记对应的粒度组可能包含 UserID 列值为 749.927.693 的行。
为了确定（或不确定）粒度组 176 中的某些行包含 UserID 列值为749.927.693 ，属于该粒度组的所有 8192 行都需要流式传输到 ClickHouse。
为此，ClickHouse 需要知道粒度组 176 的物理位置。
在 ClickHouse 中，我们表的所有粒度组的物理位置都存储在标记文件中。与数据文件类似，每个列都有一个标记文件。
下图展示了三个标记文件 UserID.mrk、URL.mrk 和 EventTime.mrk，它们存储了表的 UserID、URL 和 EventTime 列的粒度组的物理位置。
我们已经讨论了主键索引为什么是一个扁平的未压缩数组文件 (primary.idx)，其中包含从 0 开始编号的索引标记。
同样，标记文件也是一个扁平未压缩的数组文件 (*.mrk)，其中包含从 0 开始编号的标记。
一旦 ClickHouse 识别并选择了可能包含查询匹配行的粒度组的索引标记，就可以在标记文件中执行查找数组位置，以获得粒度组的实际物理位置。
特定列的每个标记文件项都以偏移量的形式存储两个位置：
- 第一个偏移量（上图中的“block_offset”）用于在压缩列数据文件中定位块，包含所选粒度组的压缩版本。该压缩块可能包含一些压缩粒度组。定位的压缩文件块在读取时被解压到主内存中。
- 标记文件的第二个偏移量（上图中的“granule_offset”）提供了未压缩块数据中颗粒的位置。
然后将被定位的未压缩粒度的所有 8192 行流式传输到 ClickHouse 以进行进一步处理。
为什么用标记文件
为什么主索引不直接包含索引标记对应的粒度的物理位置？
因为在 ClickHouse 的非常大的数据规模下，十分重要的是磁盘和内存高效使用。
主索引文件需要加载到主内存。
对于我们的示例查询，ClickHouse 使用了主索引并选择了一个可能包含与我们的查询匹配的行的单个粒度。只有对于那个粒度，ClickHouse 才需要物理位置，以便流式传输相应的行以进行进一步处理。
此外，仅 UserID 和 URL 列需要此偏移信息。
查询中未使用的列不需要偏移信息，例如 EventTime。
对于我们的示例查询，Clickhouse 只需要 UserID 数据文件 (UserID.bin) 中粒度 176 的两个物理位置偏移量和 URL 数据文件 (URL.data) 中粒度 176 的两个物理位置偏移量。
标记文件的存在，间接地避免了在主索引中直接存储所有三列的所有 1083 个粒度的物理位置的条目：从而避免在主内存中存在不必要的（可能未使用的）数据。
下图和下面的文本说明了我们的示例是如何查询 ClickHouse 在 UserID.bin 数据文件中定位粒度 176的。
我们在本文前面讨论过 ClickHouse选择了主索引标记 176，因此粒度 176 可能包含我们查询的匹配行。
ClickHouse 现在使用从索引中选择的标记编号 (176) 在 UserID.mrk 标记文件中进行数组的位置查找，以获得用于定位颗粒 176 的两个偏移量。
如图所示，第一个偏移量是在 UserID.bin 数据文件中定位压缩文件块，该文件包含粒度 176 的压缩版本。
一旦定位的文件块被解压到主内存中，标记文件的第二个偏移量可用于在未压缩数据中定位粒度176。
ClickHouse 需要从 UserID.bin 数据文件和 URL.bin 数据文件中定位（并从其中流式传输所有值）粒度 176 以执行我们的示例查询（UserID 为749.927. 693的互联网用户的前 10 个点击次数最多的 url )。
上图展示了 ClickHouse 如何定位 UserID.bin 数据文件的粒度。
此外，ClickHouse 对 URL.bin 数据文件的粒度 176 执行相同的操作。将两个各自的粒度对齐，并流式传输到 ClickHouse 引擎中进行进一步处理，即对 UserID 为 749.927.693 的所有行的每个组的 URL 值进行聚合和计数，最后按降序输出最大的 10 个 URL 组。
使用多个主键索引
辅助键是否有效
当查询对复合主键的一部分并且是第一个键列进行过滤时，ClickHouse 将在键列的索引标记上执行二分搜索算法。
但是，当查询对复合主键的一部分但不是第一个键列进行过滤时会发生什么？
注意
我们讨论一个查询保证不是在第一个键列上过滤，而是在辅助键列上过滤的场景。
当查询同时对第一个键列和第一个键列之后的任何键列进行过滤时，ClickHouse 将对第一个键列的索引标记执行二分搜索。
我们使用一个查询来计算最常点击 URL”http ://public_search”的前 10 个用户：
SELECT UserID, count(UserID) AS Count
FROM hits_UserID_URL
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;响应如下：
┌─────UserID─┬─Count─┐
│ 2459550954 │ 3741 │
│ 1084649151 │ 2484 │
│ 723361875 │ 729 │
│ 3087145896 │ 695 │
│ 2754931092 │ 672 │
│ 1509037307 │ 582 │
│ 3085460200 │ 573 │
│ 2454360090 │ 556 │
│ 3884990840 │ 539 │
│ 765730816 │ 536 │
└────────────┴───────┘
10 rows in set. Elapsed: 0.086 sec.
Processed 8.81 million rows,
799.69 MB (102.11 million rows/s., 9.27 GB/s.)客户端的输出表明 ClickHouse 几乎执行了全表扫描，尽管URL 列作为复合主键的一部分！ClickHouse 从表的 887 万行中读取了 881 万行。
如果启用了trace_logging，则 ClickHouse 服务器日志文件显示 了ClickHouse 对 1083 个 URL 索引标记使用了通用排除搜索，以识别可能包含 URL 列值为“http: //public_search”的行的那些粒度：
...Executor): Key condition: (column 1 in ['http://public_search',
'http://public_search'])
...Executor): Used generic exclusion search over index for part all_1_9_2
with 1537 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
1076/1083 marks by primary key, 1076 marks to read from 5 ranges
...Executor): Reading approx. 8814592 rows with 10 streams我们可以在上面的示例跟踪日志中看到，1083 个粒度中的 1076 个（通过标记）被选择为可能包含具有匹配 URL 值的行。
这导致 881 万行被流式传输到 ClickHouse 引擎（并行使用 10 个流），以识别实际包含 URL 值“http: //public_search”的行。
然而，正如我们稍后将看到的那样，在选定的 1076 个粒度中只有 39 个粒度实际上包含匹配的行。
虽然基于复合主键 (UserID, URL) 的主索引对于加快具有特定 UserID 值的行的查询过滤非常有用，但该索引在加快过滤具有特定 URL 值的行的查询方面没有提供重要帮助。
原因是 URL 列不是第一个键列，因此 ClickHouse 在 URL 列的索引标记上使用通用排除搜索算法（而不是二分搜索），并且该算法的效率取决于URL 列和它的前置键列 UserID的 基数差异。
为了说明这一点，我们给出了一些关于通用排除搜索是如何执行的细节。
通用排除搜索算法
下面说明了ClickHouse 通用排除搜索算法在前置键列具有低（er）或高（er）基数的情况下，辅助键筛选粒度的工作原理。
作为这两种情况的示例，我们将假设：
- 查询 URL 值 = “W3” 的行。
- 我们的 hits 表的抽象版本，其中包含 UserID 和 URL 的简化值。
- 索引的相同复合主键 (UserID, URL)。这意味着行首先按 UserID 值排序。然后按 URL 对具有相同 UserID 值的行进行排序。
- 粒度大小为 2，即每个粒度包含两行。
我们在下图中用橙色标记了每个颗粒的最小键列值。
前置键列的基数较低
假设 UserID 具有低基数。在这种情况下，相同的 UserID 值很可能分布在多个表行和粒度上。对于具有相同 UserID 的索引标记，索引标记的 URL 值按升序排序（因为表行首先按 UserID 排序，然后按 URL 排序）。这能进行有效过滤，如下所示：
上图中我们的抽象样本数据的粒度选择过程有三种不同的场景：
- 由于标记索引0、1和2具有相同的UserID值，因此可以排除（最小）URL值小于W3并且直接后续索引标记的URL值也小于W3的索引标记0。请注意，此排除的前提是，确保了粒度 0 和下一个粒度1 完全由 U1的 UserID 值组成，因此 ClickHouse 可以推断粒度 0 中的最大 URL 值也小于 W3， 并排除该颗粒。
- 选择其URL 值小于（或等于）W3 并且其直接后续索引标记的 URL 值大于（或等于）W3的索引标记 1，因为这意味着粒度1 可能包含具有 URL值为W3 的行。
- 可以排除URL 值大于 W3的索引标记 2 和 3 ，因为主键索引的索引标记存储每个粒度的最小键列值，因此粒度 2 和 3 不可能包含 URL 值为 W3的行。
前置键列的基数的较高
当 UserID 具有高基数时，相同的 UserID 值不太可能分布在多个表行和粒度上。这意味着索引标记的 URL 值不是单调递增的：
正如我们在上图中看到的，所有显示的 URL 值小于 W3 的标记都被选中，用于将其关联的粒度行都流式传输到 ClickHouse 引擎。
这是因为虽然图中的所有索引标记都属于上述场景 1，但它们不满足上述排除的前提条件，即两个直接随后的索引标记都具有与当前标记相同的 UserID 值，因此不能被排除.
例如，考虑索引标记 0 ，其URL 值小于 W3 并且直接后续索引标记的 URL 值也存在小于 W3。这不能排除，因为两个紧随其后的索引标记 1 和 2没有与当前标记 0 相同的 UserID 值。
请注意，要求两个后续索引标记具有相同的 UserID 值。这确保了当前和下一个标记的粒度完全由 U1的 UserID 值组成。如果只有下一个标记具有相同的 UserID，则下一个标记的 URL 值可能源自具有不同 UserID 的表行 - 当您查看上图时确实是这种情况，即 W2 源自 U2，而不是UserID 为U1的行。
这最终会阻止 ClickHouse 对粒度 0 中的最大 URL 值做出推断。相反，它必须假设粒度 0 可能包含 URL 值为 W3 的行，并被迫选择标记 0。
同理，标记 1、2 和 3 也是如此。
结论
当查询对作为复合键的一部分但不是第一个键的列进行过滤，并且前置键列具有较低基数时，ClickHouse 使用通用排除搜索算法而不是二分搜索算法。
在我们的示例数据集中，两个键列（UserID、URL）具有相似的高基数，并且如上所述，当 URL 列的前一个键列具有较高或相似的基数时，通用排除搜索算法并不是十分有效.
注意跳数索引
由于 UserID 和 URL 值具有同样高的基数，在使用复合主键 (UserID, URL)的表上的URL列上创建二级跳数索引，并不会对 URL 的查询过滤有太大的帮助。
例如，以下两个语句在我们表的 URL 列上创建并填充了一个minmax跳数索引：
ALTER TABLE hits_UserID_URL ADD INDEX url_skipping_index URL TYPE minmax GRANULARITY 4;
ALTER TABLE hits_UserID_URL MATERIALIZE INDEX url_skipping_index;ClickHouse 现在创建了一个附加索引，用于存储 - 每组 4 个连续粒度（注意上面ALTER TABLE语句中的GRANULARITY 4子句） - 的最小和最大 URL 值：
第一个索引项（上图中的“mark 0”）存储表的前 4 个粒度的行的最小和最大 URL 值。
第二个索引项（’mark 1’）存储表的后续 4 个粒度的行的最小和最大 URL 值，依此类推。
（ClickHouse 还为跳数索引创建了一个特殊的标记文件，用于定位与索引标记关联的粒度组。）
由于 UserID 和 URL 的基数同样高，所以当我们对 URL执行查询过滤时，这个二级跳数索引无法帮助排除被选中的粒度。
查询正在查找的特定 URL 值（如 “http: //public_search”）很可能介于索引为每组粒度存储的最小值和最大值之间，导致 ClickHouse 被迫选择粒度组（因为它们可能包含与查询匹配的行）。
需要使用多个主键索引
因此，如果我们想要显着加快过滤具有特定 URL 的行的示例查询，那么我们需要使用针对该查询优化的主键索引。
此外，如果我们希望保持过滤具有特定 UserID 的行的示例查询的良好性能，那么我们需要使用多个主键索引。
以下是实现这一目标的方法。
用于创建附加主键索引的选项
如果我们想显着加快我们的两个示例查询 - 一个过滤具有特定 UserID 的行和一个过滤具有特定 URL 的行 - 那么我们需要通过使用这三个选项之一来使用多个主索引：
- 使用不同的主键创建第二个表。
- 在我们现有的表上创建一个物化视图。
- 向我们现有的表添加投影。
所有的三个选项都会有效地将我们的样本数据复制到一个附加表中，以便重新组织表的主键索引和行排序顺序。
但是，这三个选项的不同之处在于附加表对于用户查询和插入语句处理的方便程度。
当创建了具有不同主键的第二个表后，必须将查询显式发送到最适合查询的表中，并且必须将新数据显式插入两个表中以保持表同步：
使用物化视图会隐式创建附加表，并且两个表之间的数据会自动保持同步：
投影是最方便的选项，因为除了自动保持隐式创建（和隐藏）附加表与同步数据更改之外，ClickHouse 将自动选择最有效的表版本进行查询：
在下文中，我们将通过实例来更详细地讨论创建和使用多个主索引的这三个选项。
选项 1：辅助表
创建一个新的附加表，在主键中切换了键列的顺序（与原始表相比）：
CREATE TABLE hits_URL_UserID
(
`UserID` UInt32,
`URL` String,
`EventTime` DateTime
)
ENGINE = MergeTree
PRIMARY KEY (URL, UserID)
ORDER BY (URL, UserID, EventTime)
SETTINGS index_granularity = 8192, index_granularity_bytes = 0;将原始表中的所有的 887 万行数据插入到附加表中：
INSERT INTO hits_URL_UserID
SELECT * from hits_UserID_URL;响应如下所示：
Ok.
0 rows in set. Elapsed: 2.898 sec. Processed 8.87 million rows, 838.84 MB (3.06 million rows/s., 289.46 MB/s.)最后优化表：
OPTIMIZE TABLE hits_URL_UserID FINAL;因为我们切换了主键中列的顺序，插入的行现在以不同的字典顺序存储在磁盘上（与我们的原始表相比），因此该表的 1083 个粒度包含与之前不同的值：
这是生成的主键：
现在可以使用它来显着加快我们对 URL 列的示例查询过滤，以计算最常点击的 URL”http: //public_search”的前 10 个用户：
SELECT UserID, count(UserID) AS Count
FROM hits_URL_UserID
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;响应如下：
┌─────UserID─┬─Count─┐
│ 2459550954 │ 3741 │
│ 1084649151 │ 2484 │
│ 723361875 │ 729 │
│ 3087145896 │ 695 │
│ 2754931092 │ 672 │
│ 1509037307 │ 582 │
│ 3085460200 │ 573 │
│ 2454360090 │ 556 │
│ 3884990840 │ 539 │
│ 765730816 │ 536 │
└────────────┴───────┘
10 rows in set. Elapsed: 0.017 sec.
Processed 319.49 thousand rows,
11.38 MB (18.41 million rows/s., 655.75 MB/s.)现在， ClickHouse没有进行全表扫描，而是更有效地执行了该查询。
当使用原始表的主索引时，其中 UserID 是第一个，URL 是第二个键列，ClickHouse 对索引标记使用通用排除搜索来执行该查询。并且由于 UserID 和URL具有同样高的基数，使得该查询并不高效。
将 URL 作为主索引中的第一列，ClickHouse 现在对索引标记运行二分搜索。ClickHouse 服务器日志文件中的相应跟踪日志也验证了这一点：
...Executor): Key condition: (column 0 in ['http://public_search',
'http://public_search'])
...Executor): Running binary search on index range for part all_1_9_2 (1083 marks)
...Executor): Found (LEFT) boundary mark: 644
...Executor): Found (RIGHT) boundary mark: 683
...Executor): Found continuous range in 19 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
39/1083 marks by primary key, 39 marks to read from 1 ranges
...Executor): Reading approx. 319488 rows with 2 streamsClickHouse 仅选择了 39 个索引标记，而不是使用通用排除搜索时的 1076 个。
请注意，附加表经过优化，可加快我们对 URL 的示例查询过滤的执行速度。
与使用原始表时该查询的性能不佳同理，我们对 UserID 的示例查询过滤在新的附加表中不会有效地执行，因为 UserID 现在是该表主键索引中的第二个键列，因此 ClickHouse 将使用粒度选择的通用排除搜索，对于相似高基数的 UserID 和 URL列不是很有效。以下为详细信息。
对 UserID 的查询过滤现在性能很差
SELECT URL, count(URL) AS Count FROM hits_URL_UserID WHERE UserID = 749927693 GROUP BY URL ORDER BY Count DESC LIMIT 10;响应如下
┌─URL────────────────────────────┬─Count─┐ │ http://auto.ru/chatay-barana.. │ 170 │ │ http://auto.ru/chatay-id=371...│ 52 │ │ http://public_search │ 45 │ │ http://kovrik-medvedevushku-...│ 36 │ │ http://forumal │ 33 │ │ http://korablitz.ru/L_1OFFER...│ 14 │ │ http://auto.ru/chatay-id=371...│ 14 │ │ http://auto.ru/chatay-john-D...│ 13 │ │ http://auto.ru/chatay-john-D...│ 10 │ │ http://wot/html?page/23600_m...│ 9 │ └────────────────────────────────┴───────┘ 10 rows in set. Elapsed: 0.024 sec. Processed 8.02 million rows, 73.04 MB (340.26 million rows/s., 3.10 GB/s.)服务器日志
...Executor): Key condition: (column 1 in [749927693, 749927693]) ...Executor): Used generic exclusion search over index for part all_1_9_2 with 1453 steps ...Executor): Selected 1/1 parts by partition key, 1 parts by primary key, 980/1083 marks by primary key, 980 marks to read from 23 ranges ...Executor): Reading approx. 8028160 rows with 10 streams
我们现在有两张表。分别针对 UserID 的查询过滤和对 URL 的查询过滤进行了优化加速：
选项 2：物化视图
在我们现有的表上创建一个物化视图。
CREATE MATERIALIZED VIEW mv_hits_URL_UserID
ENGINE = MergeTree()
PRIMARY KEY (URL, UserID)
ORDER BY (URL, UserID, EventTime)
POPULATE
AS SELECT * FROM hits_UserID_URL;响应如下所示：
Ok.
0 rows in set. Elapsed: 2.935 sec. Processed 8.87 million rows, 838.84 MB (3.02 million rows/s., 285.84 MB/s.)注意
- 我们在视图中的主键切换了主键列的顺序（与我们的原始表相比）
- 物化视图由隐式创建的表支持，该表的行顺序和主键索引基于给定的主键定义
- 隐式创建的表由SHOW TABLES查询列出，名称以.inner 开头
- 也可以首先显式地为物化视图创建依赖表，然后视图可以通过TO [db].[table] 子句定位该表。
- 我们使用POPULATE关键字来立即使用887 万行的源表hits_UserID_URL来填充隐式创建表
- 如果将新行插入到源表 hits_UserID_URL 中，那么这些行也会自动插入到隐式创建的表中
- 实际上，隐式创建的表与我们显式创建的辅助表具有相同的行顺序和主索引：
ClickHouse 将隐式创建的表的列数据文件( .bin)、标记文件( .mrk2) 和主键索引文件(primary.idx) 存储在 ClickHouse 服务器数据目录的特殊文件夹中：
支持物化视图的隐式创建的表（以及它的主索引）现在可用于显着加快我们对 URL 列的示例查询过滤的执行速度：
SELECT UserID, count(UserID) AS Count
FROM mv_hits_URL_UserID
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;响应如下：
┌─────UserID─┬─Count─┐
│ 2459550954 │ 3741 │
│ 1084649151 │ 2484 │
│ 723361875 │ 729 │
│ 3087145896 │ 695 │
│ 2754931092 │ 672 │
│ 1509037307 │ 582 │
│ 3085460200 │ 573 │
│ 2454360090 │ 556 │
│ 3884990840 │ 539 │
│ 765730816 │ 536 │
└────────────┴───────┘
10 rows in set. Elapsed: 0.026 sec.
Processed 335.87 thousand rows,
13.54 MB (12.91 million rows/s., 520.38 MB/s.)因为实际上支持物化视图的隐式创建的表（以及它的主索引）与我们显式创建的辅助表相同，所以查询的执行方式与显式创建的表相同。
ClickHouse 服务器日志文件中的相应跟踪日志验证了 ClickHouse 正在对索引标记执行二分搜索：
...Executor): Key condition: (column 0 in ['http://public_search',
'http://public_search'])
...Executor): Running binary search on index range ...
...
...Executor): Selected 4/4 parts by partition key, 4 parts by primary key,
41/1083 marks by primary key, 41 marks to read from 4 ranges
...Executor): Reading approx. 335872 rows with 4 streams选项 3：投影
在我们现有的表上创建一个投影：
ALTER TABLE hits_UserID_URL
ADD PROJECTION prj_url_userid
(
SELECT *
ORDER BY (URL, UserID)
);并实现投影：
ALTER TABLE hits_UserID_URL
MATERIALIZE PROJECTION prj_url_userid;注意
- 投影创建一个隐藏表，其行顺序和主键索引基于投影给定的ORDER BY子句
- SHOW TABLES查询不能列出隐藏的投影表
- 我们使用MATERIALIZE关键字，以便立即使用源表hits_UserID_URL中的所有 887 万行填充隐藏表
- 如果将新行插入到源表 hits_UserID_URL 中，那么这些行也会自动插入到隐藏表中
- 查询始终（在语法上）以源表 hits_UserID_URL 为目标，但如果隐藏表的行顺序和主索引允许更有效的查询执行，则将使用该隐藏表代替
- 实际上，隐式创建的隐藏表与我们显式创建的辅助表具有相同的行顺序和主键索引：
ClickHouse 将隐藏表的列数据文件( .bin)、标记文件( .mrk2) 和主键索引文件(primary.idx) 存储在源表数据文件、标记文件和主索引文件旁边的特殊文件夹中（在下面的截图中标记为橙色）：
由投影创建的隐藏表（以及它的主键索引）现在可以（隐式地）用于显着加快我们在 URL 列上执行示例查询过滤的速度。请注意，查询在语法上以投影的源表为目标。
SELECT UserID, count(UserID) AS Count
FROM hits_UserID_URL
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;响应如下：
┌─────UserID─┬─Count─┐
│ 2459550954 │ 3741 │
│ 1084649151 │ 2484 │
│ 723361875 │ 729 │
│ 3087145896 │ 695 │
│ 2754931092 │ 672 │
│ 1509037307 │ 582 │
│ 3085460200 │ 573 │
│ 2454360090 │ 556 │
│ 3884990840 │ 539 │
│ 765730816 │ 536 │
└────────────┴───────┘
10 rows in set. Elapsed: 0.029 sec.
Processed 319.49 thousand rows, 1
1.38 MB (11.05 million rows/s., 393.58 MB/s.)由于投影创建的隐藏表（以及它的主索引）实际上与我们显式创建的辅助表相同，所以查询的执行方式与显式创建的表相同。
ClickHouse 服务器日志文件中的相应跟踪日志证实了ClickHouse 正在对索引标记执行二分搜索：
...Executor): Key condition: (column 0 in ['http://public_search',
'http://public_search'])
...Executor): Running binary search on index range for part prj_url_userid (1083 marks)
...Executor): ...
...Executor): Choose complete Normal projection prj_url_userid
...Executor): projection required columns: URL, UserID
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
39/1083 marks by primary key, 39 marks to read from 1 ranges
...Executor): Reading approx. 319488 rows with 2 streams总结
具有复合主键 (UserID, URL)的表的主键索引对于加快UserID 的查询过滤非常有用。但是，尽管 URL 列是复合主键的一部分，但该索引并没有为加快 URL 的查询过滤提供重要帮助。
反之亦然：具有复合主键 (URL, UserID) 的表的主键索引能加快对 URL 的查询过滤，但对 UserID的查询过滤没有提供太多帮助。
由于主键列 UserID 和 URL 具有相似的高基数，对第二个键列进行过滤的查询不会从索引中的第二个键列中获得太多帮助。
因此，从主索引中删除第二个键列（从而减少索引的内存消耗）并改用多个主索引是有意义的。
但是，如果复合主键中的键列在基数上有很大差异，则查询按基数升序排列主键列是有益的。
键列之间的基数差异越大，这些列在键中的顺序就越重要。我们将在下一节中证明这一点。
因此，从主键索引中删除第二个键列（从而减少索引的内存消耗）并改用多个主键索引是有意义的。
但是，如果复合主键中的键列在基数上有很大差异，则按基数升序排列的主键列对查询是有帮助的。
键列之间的基数差异越大，这些列在键中的顺序就越重要。 我们将在下一节中说明这一点。
有效地对键列进行排序
在复合主键中，键列的顺序会明显影响以下两点：
- 查询中辅助键列的过滤效率，以及
- 表的数据文件的压缩率。
为了证明这一点，我们将使用我们的网络流量示例数据集的一个版本， 其中每行包含三列，代表互联网“用户”（UserID列）对 URL（URL列）的访问是否被标记为机器人流量（IsRobot列）。
我们将使用包含所有上述三个列的复合主键，可用于加速典型的 Web 分析查询
- 到特定 URL 的流量有多少（百分比）来自机器人
- 我们判断特定用户是否为机器人的准确程度（来自该用户的流量有多少百分比是否被假定为机器人流量）
我们使用此查询来计算要用作复合主键中的键列的三列的基数（请注意，我们正在使用URL 表函数来临时查询 TSV 数据，而无需创建本地表） . 在clickhouse client运行此查询：
SELECT
formatReadableQuantity(uniq(URL)) AS cardinality_URL,
formatReadableQuantity(uniq(UserID)) AS cardinality_UserID,
formatReadableQuantity(uniq(IsRobot)) AS cardinality_IsRobot
FROM
(
SELECT
c11::UInt64 AS UserID,
c15::String AS URL,
c20::UInt8 AS IsRobot
FROM url('https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz')
WHERE URL != ''
)响应如下：
┌─cardinality_URL─┬─cardinality_UserID─┬─cardinality_IsRobot─┐
│ 2.39 million │ 119.08 thousand │ 4.00 │
└─────────────────┴────────────────────┴─────────────────────┘
1 row in set. Elapsed: 118.334 sec. Processed 8.87 million rows, 15.88 GB (74.99 thousand rows/s., 134.21 MB/s.)我们可以看到基数之间存在很大差异，尤其是URL和IsRobot列之间。因此复合主键中列的顺序，对于有效加快对该列的查询过滤和实现最佳表的列数据文件压缩比都十分重要。
为了说明这一点，流量分析数据创建了两个表版本：
- 具有复合主键
(URL, UserID, IsRobot)的表hits_URL_UserID_IsRobot，我们按基数降序对键列进行排序 - 具有复合主键
(IsRobot, UserID, URL)的表hits_IsRobot_UserID_URL，我们按基数升序对键列进行排序
使用复合主键(URL, UserID, IsRobot)创建表hits_URL_UserID_IsRobot：
CREATE TABLE hits_URL_UserID_IsRobot
(
`UserID` UInt32,
`URL` String,
`IsRobot` UInt8
)
ENGINE = MergeTree
PRIMARY KEY (URL, UserID, IsRobot);并用 887 万行填充它：
INSERT INTO hits_URL_UserID_IsRobot SELECT
intHash32(c11::UInt64) AS UserID,
c15 AS URL,
c20 AS IsRobot
FROM url('https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz')
WHERE URL != '';响应如下：
0 rows in set. Elapsed: 104.729 sec. Processed 8.87 million rows, 15.88 GB (84.73 thousand rows/s., 151.64 MB/s.)接下来，使用复合主键(IsRobot, UserID, URL)创建表hits_IsRobot_UserID_URL：
CREATE TABLE hits_IsRobot_UserID_URL
(
`UserID` UInt32,
`URL` String,
`IsRobot` UInt8
)
ENGINE = MergeTree
PRIMARY KEY (IsRobot, UserID, URL);并用我们填充上一个表的相同的 887 万行来填充它：
INSERT INTO hits_IsRobot_UserID_URL SELECT
intHash32(c11::UInt64) AS UserID,
c15 AS URL,
c20 AS IsRobot
FROM url('https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz')
WHERE URL != '';响应如下：
0 rows in set. Elapsed: 95.959 sec. Processed 8.87 million rows, 15.88 GB (92.48 thousand rows/s., 165.50 MB/s.)对辅助键列的有效过滤
当查询过滤在作为复合键的一部分并且是第一个键列的至少一列上时，ClickHouse 将在键列的索引标记上运行二分搜索算法。
当查询（仅）对作为复合键的一部分但不是第一个键列的列进行过滤时，ClickHouse 将在键列的索引标记上使用通用排除搜索算法。
对于第二种情况，复合主键中键列的排序对于通用排除搜索算法的高效性非常重要。
这是一个过滤UserID列的查询，其中表(URL, UserID, IsRobot)按基数降序对键列进行排序：
SELECT count(*)
FROM hits_URL_UserID_IsRobot
WHERE UserID = 112304响应为：
┌─count()─┐
│ 73 │
└─────────┘
1 row in set. Elapsed: 0.026 sec.
Processed 7.92 million rows,
31.67 MB (306.90 million rows/s., 1.23 GB/s.)这是对表(IsRobot, UserID, URL)执行的相同查询，其中表按基数升序对键列进行排序：
SELECT count(*)
FROM hits_IsRobot_UserID_URL
WHERE UserID = 112304响应是：
┌─count()─┐
│ 73 │
└─────────┘
1 row in set. Elapsed: 0.003 sec.
Processed 20.32 thousand rows,
81.28 KB (6.61 million rows/s., 26.44 MB/s.)我们可以看到，在我们按基数以升序对键列进行排序的表上，查询执行明显更有效、更快。
原因是通用排除搜索算法最有效，当通过辅助键列选择粒度时，前导键列具有较低的基数。我们在本指南的前一节中详细说明了这一点。
数据文件的最佳压缩率
此查询比较我们在上面创建的两个表之间的UserID列的压缩率：
SELECT
table AS Table,
name AS Column,
formatReadableSize(data_uncompressed_bytes) AS Uncompressed,
formatReadableSize(data_compressed_bytes) AS Compressed,
round(data_uncompressed_bytes / data_compressed_bytes, 0) AS Ratio
FROM system.columns
WHERE (table = 'hits_URL_UserID_IsRobot' OR table = 'hits_IsRobot_UserID_URL') AND (name = 'UserID')
ORDER BY Ratio ASC响应如下：
┌─Table───────────────────┬─Column─┬─Uncompressed─┬─Compressed─┬─Ratio─┐
│ hits_URL_UserID_IsRobot │ UserID │ 33.83 MiB │ 11.24 MiB │ 3 │
│ hits_IsRobot_UserID_URL │ UserID │ 33.83 MiB │ 877.47 KiB │ 39 │
└─────────────────────────┴────────┴──────────────┴────────────┴───────┘
2 rows in set. Elapsed: 0.006 sec. 我们可以看到，对于我们按基数以升序对键列(IsRobot, UserID, URL)进行排序的表，UserID列的压缩率明显更高。
尽管在两个表中存储了完全相同的数据（我们在两个表中插入了相同的 887 万行），但复合主键中键列的顺序对表的列数据文件中的压缩数据占用多少磁盘空间有很大影响：
- 在具有复合主键
(URL, UserID, IsRobot)的表hits_URL_UserID_IsRobot中，我们按基数降序排列键列，UserID.bin数据文件占用11.24 MiB的磁盘空间 - 在具有复合主键
(IsRobot, UserID, URL)的表hits_IsRobot_UserID_URL中，我们按基数升序对键列进行排序，UserID.bin数据文件仅占用877.47 KiB磁盘空间
磁盘上的表列数据具有良好的压缩比不仅可以节省磁盘空间，而且还可以使需要从该列读取数据的查询（尤其是分析查询）更快，因为从磁盘到主存（操作系统的文件缓存）移动列的数据所需的 i/o 更少。
下面我们将说明为什么主键按基数升序排列对表列的压缩率有好处。
下图描绘了主键在磁盘上的行顺序，其中键列按基数升序排列：
我们说明了表的行数据按主键列排序存储在磁盘上。
在上图中，表的行（它们在磁盘上的列值）首先按cl值排序，具有相同cl值的行按其ch值排序。并且由于第一个键列cl的基数较低，很可能存在具有相同cl值的行。因此，ch值也可能是有序的（局部 - 对于具有相同cl值的行）。
如果在一列中，相似的数据彼此靠近放置，例如通过排序，那么该数据将被更好地进行压缩。一般来说，压缩算法受益于数据的长度（数据越多，压缩效果越好）和局部性（数据越相似，压缩比越好）。
与上图相反，下图描绘了磁盘上行顺序按主键基数降序排列：
现在表的行首先按它们的ch值排序，具有相同ch值的行按它们的cl值排序。但是因为第一个键列ch的基数很高，所以不可能有相同ch值的行。并且因此也不太可能对cl值进行排序（局部 - 对于具有相同ch值的行）。
因此，这些cl值很可能是随机顺序的，因此分别具有较差的局部性和压缩比。
总结
对于提升查询中的辅助键列的有效过滤和表的列数据文件的压缩率，将主键中的列按其基数升序排列是有帮助的。
有效识别单行
虽然通常它不是ClickHouse 的最佳用例，但有时在 构建ClickHouse 之上的应用程序需要识别 ClickHouse 表的单行。
一个直观的解决方案是使用每行具有唯一值的UUID列，并将该列用作主键列以快速检索行。
为了最快的检索，UUID 列需要是第一个键列。
我们讨论了因为ClickHouse 表的行数据存储在按主键列排序的磁盘上，因此在主键或复合主键中具有非常高的基数的列（如 UUID 列）在具有较低基数的列之外是不利于表列的压缩比。
最快检索和最佳数据压缩之间的折衷方案是使用复合主键，其中 UUID 是最后一个键列，位于用于确保某些表列的良好压缩比的低基数键列之后。
一个具体的例子
一个具体的例子是Alexey Milovidov 开发并提到的文本粘贴服务https://pastila.nl。
每次更改文本区域时，数据都会自动保存到 ClickHouse 表行（每次更改一行）。
识别和检索（特定版本）粘贴内容的一种方法是使用内容的hash作为包含该内容的表行的 UUID。
下图说明
- 内容更改时行的插入顺序（例如，由于在文本区域中输入文本）和
- 使用
PRIMARY KEY (hash)时插入的行数据在磁盘上的顺序：
因为该hash列被用作主键列
- 可以非常快速地检索特定的行，但是
- 表的行（它们的列数据）按（唯一和随机）哈希值升序排列存储在磁盘上。因此，内容列的值也以随机顺序存储，没有数据局部性，导致内容列数据文件的压缩率次优。
为了显着提高内容列的压缩率，同时仍然实现对特定行的快速检索，pasila.nl 使用两个哈希值（和一个复合主键）来识别特定行：
- 如上所述，内容的哈希值对于不同的数据是不同的，以及
- 一个局部敏感的hash（指纹），不会因数据的微小变化而改变。
下图显示
- 内容更改时行的插入顺序（例如，由于在文本区域中输入文本）和
- 使用复合主键
PRIMARY KEY (fingerprint, hash)时插入行数据在磁盘上的顺序：
现在磁盘上的行首先按fingerprint排序，对于具有相同指纹值的行，它们的hash值决定了最终的顺序。
因为只有微小变化的数据会获得相同的指纹值，所以现在相似的数据存储在磁盘上的内容列中彼此靠近。这对内容列的压缩率非常有利，因为压缩算法通常受益于数据局部性（数据越相似，压缩率越好）。
折衷方案是检索特定行需要两个字段 (fingerprint和hash)，以便最佳地利用产生的复合主索引PRIMARY KEY (fingerprint, hash)。