ClickHouse 主键索引实用入门指南
引言
在本指南中，我们将深入探讨 ClickHouse 的索引机制。我们将详细说明并讨论：
你也可以在本地机器上选择性地执行本指南中给出的所有 ClickHouse SQL 语句和查询。 有关 ClickHouse 的安装和快速入门说明，请参阅快速开始。
数据集
在本指南中，我们将使用一个经过匿名化的示例网站流量数据集。
- 我们将使用该示例数据集中的一个子集，共 887 万行（事件）。
- 未压缩的数据大小为 887 万个事件，大约 700 MB；在 ClickHouse 中存储时压缩后约为 200 MB。
- 在我们的子集中，每一行包含三列，表示某个互联网用户（
UserID列）在特定时间（EventTime列）点击了某个 URL（URL列）。
仅凭这三列，我们就可以编写一些典型的网站分析查询，例如：
- “对于某个特定用户，点击次数最多的前 10 个 URL 是什么？”
- “对于某个特定 URL，点击最频繁的前 10 个用户是谁？”
- “用户在什么时间（例如一周中的哪些天）点击某个特定 URL 最频繁？”
测试机器
本文档中给出的所有运行时数据均基于在一台本地运行 ClickHouse 22.2.1、配备 Apple M1 Pro 芯片和 16GB 内存的 MacBook Pro 上获得的结果。
全表扫描
为了了解在没有主键的情况下查询是如何在数据集上执行的，我们通过执行以下 SQL DDL 语句创建一张表（使用 MergeTree 表引擎）：
接下来，使用以下 SQL INSERT 语句将 hits 数据集的一个子集插入到表中。 这里使用了 URL table function，以便从托管在 clickhouse.com 上的完整数据集中加载一个子集：
返回结果为：
ClickHouse 客户端的结果输出显示，上述语句向该表中插入了 887 万行数据。
最后，为了简化本指南后续的讨论，并使图示和结果可复现，我们使用 FINAL 关键字对该表执行 optimize：
一般来说，在将数据加载到表中后，不需要也不建议立即对表进行优化。为什么在本示例中需要这样做，后文会变得一目了然。
现在我们来执行第一个 Web 分析查询。下面的查询将统计 UserID 为 749927693 的互联网用户点击次数最多的前 10 个 URL：
返回结果为：
ClickHouse 客户端的结果输出表明，ClickHouse 执行了一次全表扫描！我们表中 887 万行数据的每一行都被流式传入 ClickHouse。这在规模上根本行不通。
要让这一过程更加高效、运行得更快，我们需要使用一个具有合适主键的表。这样 ClickHouse 就能基于主键列自动创建一个稀疏主索引，从而显著加快我们示例查询的执行速度。
ClickHouse 索引设计
面向海量数据规模的索引设计
在传统的关系型数据库管理系统中，主索引会为表中的每一行包含一个条目。对于我们的数据集，这意味着主索引中将包含 887 万个条目。这样的索引可以快速定位特定行，从而在查找类查询和点更新中具备很高的效率。在 B(+)-Tree 数据结构中查找一个条目，其平均时间复杂度为 O(log n)；更精确地说，log_b n = log_2 n / log_2 b，其中 b 是 B(+)-Tree 的分支因子，n 是被索引的行数。由于 b 通常在数百到数千之间，B(+)-Trees 是一种层级非常浅的结构，因此只需要很少的磁盘寻道即可定位记录。在具有 887 万行、分支因子为 1000 的情况下，平均需要 2.3 次磁盘寻道。这种能力也有代价：额外的磁盘和内存开销、向表中插入新行及向索引中添加条目时更高的写入成本，以及有时需要对 B-Tree 进行重平衡。
考虑到与 B-Tree 索引相关的这些挑战，ClickHouse 中的表引擎采用了不同的方法。ClickHouse 的 MergeTree Engine Family 被设计并优化用于处理海量数据。这些表可以每秒接收数百万行的插入，并存储极其庞大的数据量（数百 PB）。数据会被快速写入表中，按分区片段逐个写入，并在后台按照规则对这些分区片段进行合并。在 ClickHouse 中，每个分区片段都有自己的主索引。当分区片段被合并时，合并后的分区片段的主索引也会被合并。对于 ClickHouse 所面向的这种超大规模场景，极高的磁盘和内存效率至关重要。因此，主索引不会为每一行建立索引，而是对每个分区片段中的一组行（称为“粒度 granule”）建立一个索引条目（称为“标记 mark”）——这种技术称为 稀疏索引。
稀疏索引之所以可行，是因为 ClickHouse 会按主键列对某个分区片段中的行在磁盘上进行有序存储。与基于 B-Tree 的索引那样直接定位单行不同，稀疏主索引通过在索引条目上执行二分查找，可以快速识别出可能与查询匹配的行分组。随后，这些可能匹配的行分组（粒度）会被并行地流式传入 ClickHouse 引擎，以找到真正的匹配记录。这种索引设计使主索引可以非常小（它可以并且必须完全放入主内存中），同时仍能显著加速查询执行时间——尤其是对于数据分析场景中常见的区间查询。
下面将详细说明 ClickHouse 如何构建和使用其稀疏主索引。本文后续部分还将讨论在选择、移除以及排序用于构建索引的表列（主键列）时的一些最佳实践。
带有主键的表
创建一个具有复合主键的表，其键列为 UserID 和 URL：
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。
compress_primary_key：设置为 0 以禁用主索引压缩。这将允许我们在需要时于稍后检查其内容。
上述 DDL 语句中的主键会基于指定的两个键列创建主索引。
接下来插入数据：
返回结果类似如下：
并优化该表：
我们可以使用以下查询来获取关于表的元数据：
返回结果如下：
ClickHouse 客户端的输出显示：
- 该表的数据以 宽格式 存储在磁盘上的特定目录中，这意味着该目录中每个表的列各自对应一个数据文件（以及一个标记文件）。
- 该表有 887 万行。
- 所有行未压缩的数据总大小为 733.28 MB。
- 所有行在磁盘上压缩后的总大小为 206.94 MB。
- 该表具有一个包含 1083 个条目的主索引（称为“marks”），索引大小为 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 内部用于日志消息的行编号方案保持一致。
为并行数据处理而将数据组织成粒度
出于数据处理的目的，表的列值在逻辑上被划分为若干粒度（granule）。 粒度是在以流式方式将数据送入 ClickHouse 进行处理时，最小且不可再分的数据集。 这意味着 ClickHouse 并不是读取单独的行，而是始终以流式并行的方式读取一整组（粒度）行。
列值并不是物理地存储在粒度内部：粒度只是对列值进行的一种逻辑组织，用于查询处理。
下图展示了我们表中 887 万行（其列值）如何组织成 1083 个粒度，
这是由于表的 DDL 语句中包含的 index_granularity 设置（设置为其默认值 8192）所决定的。
按磁盘物理顺序，最前面的 8192 行（其列值）在逻辑上属于粒度 0，接下来的 8192 行（其列值）属于粒度 1，以此类推。
-
最后一个粒度（粒度 1082）“包含”的行数少于 8192 行。
-
我们在本指南开头的 “DDL Statement Details” 中提到过，为了简化本指南中的讨论，并使图示和结果可复现，我们禁用了自适应索引粒度。
因此，在我们的示例表中，除最后一个粒度外，所有粒度的大小都相同。
-
对于启用了自适应索引粒度的表（索引粒度默认为自适应），部分粒度的大小可能会小于 8192 行，这取决于行数据的大小。
-
我们用橙色标记了部分主键列（
UserID、URL）中的列值。 这些橙色标记的列值是每个粒度中第一行的主键列值。 正如下文将看到的，这些橙色标记的列值将成为表主键索引中的条目。 -
我们从 0 开始对粒度进行编号，以便与 ClickHouse 内部编号方案保持一致，该方案也用于日志消息。
主索引每个粒度有一个条目
主索引是基于上图中所示的粒度创建的。该索引是一个未压缩的扁平数组文件（primary.idx），包含从 0 开始的数值索引标记。
下图显示，索引为每个粒度的第一行存储主键列值（即上图中以橙色标记的值）。 换句话说：主索引存储的是表中每第 8192 行的主键列值（基于由主键列定义的物理行顺序）。 例如：
- 第一个索引条目（下图中的“mark 0”）存储的是上图中粒度 0 的第一行的键列值，
- 第二个索引条目（下图中的“mark 1”）存储的是上图中粒度 1 的第一行的键列值，依此类推。
总体而言，对于我们的这张包含 887 万行和 1083 个粒度的表，索引共有 1083 个条目：
-
对于启用了自适应索引粒度的表，主索引中还会存储一个额外的“最终”标记，用于记录表最后一行的主键列值。但由于我们在本指南中禁用了自适应索引粒度（以简化讨论，并使图示和结果可复现），因此示例表的索引并不包含这个最终标记。
-
主索引文件会被完整加载到内存中。如果该文件大于可用的空闲内存空间，ClickHouse 会抛出错误。
检查主索引的内容
在自管理 ClickHouse 集群上，我们可以使用 file 表函数 来检查示例表主索引的内容。
为此，我们首先需要将主索引文件复制到运行中集群某个节点的 user_files_path 中：
- 步骤 1：获取包含主索引文件的分区片段路径（part path）
- 步骤 2：获取 user_files_path Linux 上的 默认 user_files_path 为
- 步骤 3：将主索引文件复制到 user_files_path 中
SELECT path FROM system.parts WHERE table = 'hits_UserID_URL' AND active = 1在测试机器上，返回 /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4。
/var/lib/clickhouse/user_files/在 Linux 上可以通过以下方式检查它是否被修改过：$ grep user_files_path /etc/clickhouse-server/config.xml
在测试机器上，该路径为 /Users/tomschreiber/Clickhouse/user_files/
cp /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4/primary.idx /Users/tomschreiber/Clickhouse/user_files/primary-hits_UserID_URL.idx
现在我们可以通过 SQL 检查主索引的内容：
- 获取条目数量
- 获取前两个索引标记
- 获取最后一个索引标记
SELECT count( )<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String');
返回 1083SELECT UserID, URL<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 0, 2;返回
240923, http://showtopics.html%3...<br/> 4073710, http://mk.ru&pos=3_0
SELECT UserID, URL FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 1082, 1;
返回
4292714039 │ http://sosyal-mansetleri...这与我们示例表中主索引内容的示意图完全一致：
主键条目被称为索引标记（index marks），因为每个索引条目都标记了一个特定数据范围的起始位置。就示例表而言：
-
UserID 索引标记：
主索引中存储的
UserID值按升序排列。
因此，上图中的 “mark 1” 表示：粒度 1 中，以及所有后续粒度中，所有表行的UserID值都保证大于或等于 4.073.710。
正如我们稍后将看到的，当查询在主键的第一列上进行过滤时，这种全局排序使 ClickHouse 能够在第一键列的索引标记上 使用二分查找算法。
-
URL 索引标记：
主键列
UserID和URL具有非常接近的基数，这意味着对于从第二个键列开始的所有键列，其索引标记一般只有在前一个键列的值在至少当前粒度内的所有表行中都保持不变时，才能指示一个数据范围。
例如，由于上图中标记 0 和标记 1 的 UserID 值不同，ClickHouse 无法假设粒度 0 中所有表行的 URL 值都大于或等于'http://showtopics.html%3...'。但是，如果上图中标记 0 和标记 1 的 UserID 值相同（意味着粒度 0 中所有表行的 UserID 值都相同），那么 ClickHouse 就可以假设粒度 0 中所有表行的 URL 值都大于或等于'http://showtopics.html%3...'。我们将在后文更详细地讨论这对查询执行性能的影响。
主索引用于选择数据块（granule）
现在我们可以在主索引的支持下执行查询。
下面的查询将计算 UserID 749927693 点击次数最多的前 10 个 URL。
返回结果为：
ClickHouse 客户端的输出现在显示，ClickHouse 不再执行全表扫描，而是仅有 8.19 千行数据被流式写入 ClickHouse。
如果启用了trace 级别日志，则在 ClickHouse 服务器日志文件中可以看到，ClickHouse 正在对 1083 个 UserID 索引标记执行二分查找，以识别那些可能包含 UserID 列值为 749927693 的行的 granule。该过程需要 19 步，平均时间复杂度为 O(log2 n)：
我们可以从上面的 trace 日志中看到，在现有的 1083 个 mark 中，只有一个 mark 满足了该查询。
Trace 日志详情
识别到的是 Mark 176（'found left boundary mark' 为包含性边界，'found right boundary mark' 为不包含性边界），因此来自 granule 176 的全部 8192 行（该 granule 从第 1.441.792 行开始——我们将在本指南后面看到这一点）会被流式传入 ClickHouse，以便找到实际 UserID 列值为 749927693 的那些行。
我们也可以在示例查询中使用 EXPLAIN 子句 来重现这一点：
返回结果类似如下：
客户端输出显示，在 1083 个 granule 中，有 1 个被选为可能包含 UserID 列值为 749927693 的行。
当查询对某个既属于复合键又是该复合键首列的列进行过滤时，ClickHouse 会在该键列的索引标记上运行二分查找算法。
如上所述，ClickHouse 使用其稀疏主键索引，通过二分查找快速选择那些可能包含满足查询条件行的 granule。
这是 ClickHouse 查询执行的第一阶段（granule 选择）。
在**第二阶段（数据读取）**中，ClickHouse 会定位已选中的 granule，将其中所有行流式传入 ClickHouse 引擎，以便找出真正满足查询条件的行。
我们将在下一节中更详细地讨论第二阶段。
标记文件用于定位粒度
下图展示了我们这张表的主索引文件的一部分。
如上文所述，通过在索引中的 1083 个 UserID 标记上执行二分查找，找到了标记 176。与之对应的粒度 176 因此可能包含 UserID 列值为 749.927.693 的行。
Granule Selection Details
上图表明，标记 176 是第一个索引项，其中，与其关联的粒度 176 的最小 UserID 值小于 749.927.693，而下一条标记（标记 177）所对应的粒度 177 的最小 UserID 值大于该值。因此，只有标记 176 对应的粒度 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）用于定位块（block）在压缩列数据文件中的位置，该块包含所选粒度的压缩版本。这个压缩块可能包含若干个被压缩的粒度。定位到的压缩文件块在读取时会被解压到内存中。 -
第二个偏移量（上图中的
granule_offset）来自标记文件，给出了粒度在未压缩块数据中的位置。
随后，属于该未压缩粒度的全部 8192 行都会被流式读取到 ClickHouse 中以进行后续处理。
索引粒度在默认情况下是自适应的，但在我们的示例表中，我们禁用了自适应索引粒度（以简化本指南中的讨论，并使图示和结果可复现）。我们的表使用宽格式，是因为数据大小大于 min_bytes_for_wide_part（对于自管理集群，其默认值为 10 MB）。
-
对于使用宽格式且启用了自适应索引粒度的表，ClickHouse 使用
.mrk2标记文件，它们包含与.mrk标记文件类似的条目，但每条记录额外包含第三个值：当前条目关联的粒度的行数。 -
对于使用紧凑格式的表，ClickHouse 使用
.mrk3标记文件。
为什么主键索引不直接包含与索引标记对应的粒度的物理位置？
因为在 ClickHouse 设计所针对的这一超大规模下，高效使用磁盘和内存非常重要。
主键索引文件需要能够装入主内存。
针对我们的示例查询，ClickHouse 使用主键索引并选择了一个可能包含与查询匹配行的粒度。只有对于这一粒度，ClickHouse 才需要物理位置信息，以便将相应的行以流式方式传入进行进一步处理。
此外，这些偏移量信息只对 UserID 和 URL 列是必需的。
对于未在查询中使用的列（例如 EventTime），则不需要偏移量信息。
对于我们的示例查询，ClickHouse 只需要 UserID 数据文件（UserID.bin）中粒度 176 的两个物理位置偏移，以及 URL 数据文件（URL.bin）中粒度 176 的两个物理位置偏移。
标记文件提供的这种间接方式，避免了在主键索引中直接存储所有 3 列的全部 1083 个粒度的物理位置信息条目，从而避免在主内存中保留不必要的（潜在不会使用的）数据。
下图及其后面的文字说明了在我们的示例查询中，ClickHouse 是如何在 UserID.bin 数据文件中定位粒度 176 的。
我们在本指南前面已经讨论过，ClickHouse 选择了主键索引标记 176，因此选择了粒度 176 作为可能包含与我们的查询匹配行的粒度。
ClickHouse 现在使用索引中选定的标记编号（176），在 UserID.mrk 标记文件中进行基于位置的数组查找，以获取定位粒度 176 所需的两个偏移量。
如图所示，第一个偏移量用于定位 UserID.bin 数据文件中包含粒度 176 压缩版本的压缩文件块。
一旦将定位到的文件块解压到主内存中，就可以使用来自标记文件的第二个偏移量，在未压缩数据中定位粒度 176。
为了执行我们的示例查询（对于 UserID 为 749.927.693 的互联网用户，获取点击次数最多的前 10 个 URL），ClickHouse 需要从 UserID.bin 数据文件和 URL.bin 数据文件中定位（并流式读取所有值）粒度 176。
上图展示了 ClickHouse 是如何为 UserID.bin 数据文件定位该粒度的。
与此同时，ClickHouse 正在对 URL.bin 数据文件中的粒度 176 执行同样的操作。两个对应的粒度会被对齐并流式传入 ClickHouse 引擎进行进一步处理，即对所有 UserID 为 749.927.693 的行，以分组的方式聚合并统计 URL 值，最后按计数降序输出前 10 个计数最高的 URL 分组。
使用多个主索引
二级键列可能（并非）低效
当一个查询在过滤一个属于复合键且是第一个键列的列时，ClickHouse 会在该键列的索引标记上运行二分查找算法。
但是，当查询在过滤一个属于复合键但不是第一个键列的列时，会发生什么？
我们讨论的场景是：查询明确地不在第一个键列上过滤，而是在某个二级键列上过滤。
当查询同时在第一个键列和第一个之后的任意键列上过滤时，ClickHouse 会在第一个键列的索引标记上运行二分查找。
我们使用一个查询来统计点击 URL "http://public_search" 最频繁的前 10 个用户：
客户端输出表明，尽管 URL 列是复合主键的一部分，ClickHouse 仍然几乎执行了全表扫描！在这张表的 887 万行数据中，ClickHouse 读取了 881 万行。
如果启用了 trace_logging，那么在 ClickHouse 服务器日志文件中可以看到，ClickHouse 在 1083 个 URL 索引标记上使用了通用排除搜索，以识别那些可能包含 URL 列值为 "http://public_search" 的行的数据粒度（granule）：
从上面的示例 trace 日志中可以看到，通过 marks，在 1083 个 granule 中有 1076 个被选为可能包含具有匹配 URL 值的行。
这会导致有 881 万行数据被流式加载到 ClickHouse 引擎中（通过 10 个流并行处理），以便识别哪些行实际上包含 URL 值 "http://public_search"。
然而，正如我们稍后将看到的，在选出的这 1076 个 granule 中，实际上只有 39 个 granule 包含匹配的行。
尽管基于复合主键 (UserID, URL) 的主索引对于加速按特定 UserID 值过滤行的查询非常有用，但在加速按特定 URL 值过滤行的查询时，该索引并未提供显著帮助。
其原因在于 URL 列不是第一个键列，因此 ClickHouse 在 URL 列的 index marks 上使用的是通用排除搜索算法（而不是二分查找），并且该算法的有效性取决于 URL 列与其前驱键列 UserID 之间的基数差异。
为此，我们给出一些关于通用排除搜索是如何工作的细节。
通用排除搜索算法
下面通过一个示例说明，当通过一个次级键列选择颗粒、且其前置键列具有较低或较高基数时，ClickHouse 通用排除搜索算法是如何工作的。
在这两种情况下，我们都假设：
- 查询在搜索 URL 值为 "W3" 的行。
- 一个抽象化的 hits 表，其中 UserID 和 URL 的值被简化。
- 用于索引的相同复合主键 (UserID, URL)。这意味着行首先按 UserID 值排序，具有相同 UserID 值的行随后按 URL 排序。
- 颗粒大小为 2，即每个颗粒包含两行。
在下图中，我们用橙色标记了每个颗粒首行的键列值。
假设 UserID 具有较低基数。在这种情况下，相同的 UserID 值很可能分布在多个表行和颗粒中，从而跨越多个索引标记。对于具有相同 UserID 的索引标记，其 URL 值是按升序排序的（因为表行首先按 UserID 然后按 URL 排序）。这使得可以按如下方式进行高效过滤：
对于上图中的抽象示例数据，在颗粒选择过程中有三种不同场景：
-
对于索引标记 0，其 URL 值小于 W3，且其直接后继索引标记的 URL 值也小于 W3，可以将其排除，因为标记 0 和 1 具有相同的 UserID 值。请注意，此排除前提条件确保颗粒 0 完全由 UserID 为 U1 的行组成，因此 ClickHouse 可以假设颗粒 0 中的最大 URL 值也小于 W3，从而排除该颗粒。
-
对于索引标记 1，其 URL 值小于（或等于）W3，且其直接后继索引标记的 URL 值大于（或等于）W3，需要选择该标记，因为这意味着颗粒 1 可能包含 URL 为 W3 的行。
-
对于索引标记 2 和 3，其 URL 值大于 W3，可以将其排除，因为主索引的索引标记为每个颗粒的首行存储键列值，而表行在磁盘上按键列值排序，因此颗粒 2 和 3 不可能包含 URL 值为 W3 的行。
当 UserID 具有较高基数时，相同的 UserID 值分布在多个表行和颗粒上的可能性就很低。这意味着索引标记的 URL 值不再单调递增：
如上图所示，所有 URL 值小于 W3 的索引标记都会被选中，用于将其关联颗粒中的行流式传入 ClickHouse 引擎。
这是因为尽管图中的所有索引标记都落在前面描述的场景 1 中，但它们并不满足前面提到的排除前提条件：直接后继索引标记与当前标记具有相同的 UserID 值，因此无法被排除。
例如，考虑索引标记 0，其 URL 值小于 W3，且其直接后继索引标记的 URL 值也小于 W3。该标记不能被排除，因为其直接后继索引标记 1 的 UserID 值与当前标记 0 的 UserID 值不相同。
这最终使得 ClickHouse 无法对颗粒 0 中的最大 URL 值做出任何假设。相反，它必须假设颗粒 0 可能包含 URL 值为 W3 的行，因此被迫选择标记 0。
对于标记 1、2 和 3，同样适用这一情形。
当查询在一个属于复合键但不是第一个键列的列上进行过滤时，ClickHouse 会使用 通用排除搜索算法（generic exclusion search algorithm） 来替代 二分查找算法（binary search algorithm）。当前导键列的基数较低时，这种通用排除搜索算法效果最佳。
在我们的示例数据集中，这两个键列（UserID、URL）都具有类似的高基数。正如前文所述，当 URL 列的前导键列具有较高或相近的基数时，通用排除搜索算法的效果并不理想。
关于 data skipping 索引的说明
由于 UserID 和 URL 都具有类似的高基数，我们在 URL 上的查询过滤，即使在 URL 列上创建一个次级 data skipping 索引，对于带有复合主键 (UserID, URL) 的表来说，收益也不会太大。
例如，下面这两个语句会在我们表的 URL 列上创建并填充一个 minmax data skipping 索引：
ClickHouse 现在创建了一个额外的索引,该索引为每组 4 个连续的颗粒存储最小和最大 URL 值(注意上面 ALTER TABLE 语句中的 GRANULARITY 4 子句):
第一个索引条目(上图中的 'mark 0')存储了属于表中前 4 个颗粒的行的最小和最大 URL 值。
第二个索引条目('mark 1')存储了属于表中接下来 4 个颗粒的行的最小和最大 URL 值,依此类推。
(ClickHouse 还为数据跳过索引创建了一个特殊的标记文件,用于定位与索引标记关联的颗粒组。)
由于 UserID 和 URL 的基数同样很高,当执行按 URL 过滤的查询时,这个辅助数据跳过索引无法帮助排除颗粒。
查询要查找的特定 URL 值(即 'http://public_search')很可能位于索引为每组颗粒存储的最小值和最大值之间,这导致 ClickHouse 被迫选择该颗粒组(因为它们可能包含与查询匹配的行)。
需要使用多个主索引
因此，如果我们想要显著加速按特定 URL 过滤行的示例查询，就需要使用针对该查询优化的主索引。
如果同时还想保持按特定 UserID 过滤行的示例查询的良好性能，那么就需要使用多个主索引。
下面展示了实现这一点的方法。
创建额外主索引的选项
如果我们想要显著加速这两个示例查询——一个是过滤具有特定 UserID 的行，另一个是过滤具有特定 URL 的行——那么我们需要通过以下三种方式之一来使用多个主索引：
- 创建一张具有不同主键的第二张表。
- 在现有表上创建一个 materialized view。
- 向现有表中添加一个 projection。
这三种选项都会有效地将我们的示例数据复制到一个额外的表中，以便重新组织表的主索引和行排序顺序。
不过，这三种选项在额外表对用户的透明程度上有所不同，特别是在查询和插入语句的路由方式方面。
当创建一张具有不同主键的第二张表时，查询必须显式发送到最适合该查询的表版本，并且为了保持两张表的数据同步，新数据必须显式插入到这两张表中：
使用 materialized view 时，会隐式创建额外的表，并且两张表之间的数据会自动保持同步：
而 projection 是最透明的选项，因为除了会自动将隐式创建（且对用户隐藏）的额外表与数据变更保持同步之外，ClickHouse 还会自动为查询选择最有效的表版本：
在接下来的内容中，我们将更详细地讨论这三种创建和使用多个主索引的选项，并配以实际示例。
选项 1：辅助表
我们将创建一个新的辅助表，在该表的主键中，将键列的顺序（与原始表相比）对调：
将我们原始表中的全部 887 万行数据插入到这个辅助表中：
返回结果大致如下：
最后，优化该表：
由于我们调整了主键中列的顺序，插入的行现在在磁盘上的字典序顺序（与我们的原始表相比）也发生了变化，因此该表的 1083 个 granule 中所包含的值也与之前不同：
这是新的主键：
现在可以利用它显著加速我们示例查询的执行：该查询在 URL 列上进行过滤，用于计算在 URL "http://public_search" 上点击最频繁的前 10 个用户：
响应如下：
现在，ClickHouse 在执行该查询时效率高得多，而不再需要几乎进行一次全表扫描。
在原始表的主索引中，UserID 是第一列，URL 是第二个键列，ClickHouse 使用了通用排除搜索在索引标记上执行该查询；由于 UserID 和 URL 都具有相近且较高的基数，这种方式效率并不高。
当 URL 作为主索引中的第一列时，ClickHouse 现在会在索引标记上运行二分查找。 ClickHouse 服务器日志文件中的相应 trace 日志证实了这一点：
ClickHouse 只选择了 39 个索引标记，而在使用通用排除搜索时会选择 1076 个。
请注意，这个附加表是为了加速我们按 URL 过滤的示例查询的执行而优化的。
与使用原始表时该查询性能较差类似，我们按 UserIDs 过滤的示例查询在这个新的附加表上也无法高效运行，因为在该表的主键中，UserID 现在是第二个键列，因此 ClickHouse 会在粒度选择上使用通用排除搜索，而这对于 UserID 和 URL 这类同样具有高基数的列来说效果并不理想。
打开详情框查看具体信息。
我们现在有两个表：一个针对按 UserIDs 过滤的查询进行了优化，另一个则针对按 URL 过滤的查询进行了优化：
选项 2：materialized view
在现有表上创建一个 materialized view。
响应结果如下：
- 我们在 view 的主键中调整了键列的顺序（相较于原始表）
- materialized view 背后有一个隐式创建的表作为底层表，其行顺序和主索引基于给定的主键定义
- 这个隐式创建的表会被
SHOW TABLES查询列出，并且其名称以.inner开头 - 也可以先显式创建一个用于 materialized view 的底层表，然后在创建 view 时通过
TO [db].[table]子句 指向该表 - 我们使用
POPULATE关键字，以便立即用源表 hits_UserID_URL 中全部 887 万行数据填充这个隐式创建的表 - 如果有新行插入到源表 hits_UserID_URL 中，那么这些行也会自动插入到该隐式创建的表中
- 实际上，这个隐式创建的表与我们显式创建的二级表具有相同的行顺序和主索引：
ClickHouse 会将该隐式创建表的列数据文件（.bin）、mark 文件（.mrk2）以及主索引（primary.idx）存储在 ClickHouse 服务器数据目录中的一个特殊目录中：
现在，可以利用这个作为 materialized view 底层表的隐式创建表（及其主索引），显著加速在 URL 列上进行过滤的示例查询的执行：
返回结果为：
由于支撑该 materialized view 的（隐式创建的）表及其主键索引，与我们显式创建的二级表本质上相同，因此该查询的实际执行方式与使用显式创建的表时相同。
ClickHouse 服务器日志文件中的对应 trace 日志条目确认，ClickHouse 正在对索引标记执行二分查找：
选项 3：PROJECTION
在现有表上创建一个 PROJECTION：
并将该 PROJECTION 物化：
- 投影会创建一个隐藏表，其行顺序和主索引基于该投影指定的
ORDER BY子句 - 隐藏表不会出现在
SHOW TABLES查询结果中 - 我们使用
MATERIALIZE关键字，以便立即用源表 hits_UserID_URL 中的全部 887 万行数据填充隐藏表 - 如果向源表 hits_UserID_URL 插入新行，这些行也会自动插入到隐藏表中
- 查询在语法上始终以源表 hits_UserID_URL 为目标，但如果隐藏表的行顺序和主索引能够实现更高效的查询执行，则会优先使用该隐藏表
- 请注意，投影并不会让使用 ORDER BY 的查询更高效，即使该 ORDER BY 与投影的 ORDER BY 语句完全匹配也是如此（参见 https://github.com/ClickHouse/ClickHouse/issues/47333）
- 实际上，隐式创建的隐藏表与我们显式创建的二级表具有相同的行顺序和主索引：
ClickHouse 将隐藏表的列数据文件（.bin）、mark 文件（.mrk2）以及主索引（primary.idx）存储在一个特殊文件夹中（在下方截图中用橙色标出），该文件夹与源表的数据文件、mark 文件和主索引文件位于同一位置：
现在，由投影创建的隐藏表（及其主索引）可以（隐式地）用于显著加速我们对 URL 列进行过滤的示例查询的执行。请注意，该查询在语法上仍然以该投影的源表为目标。
响应如下：
由于投影创建的隐藏表（及其主索引）在本质上与我们显式创建的辅助表相同，因此该查询的实际执行方式与使用显式创建的表时是一样的。
ClickHouse 服务器日志文件中的对应 trace 日志表明，ClickHouse 正在对索引标记执行二分查找：
总结
我们针对具有复合主键 (UserID, URL) 的表 的主索引，在加速按 UserID 过滤的查询 时非常有用。
但这个索引在加速按 URL 过滤的查询 时并没有提供显著帮助，尽管 URL 列也是该复合主键的一部分。
反过来也一样：
我们针对具有复合主键 (URL, UserID) 的表 的主索引，可以加速按 URL 过滤的查询，但对按 UserID 过滤的查询 的加速效果却不明显。
由于主键列 UserID 和 URL 都具有相近的高基数，对于一个按第二个键列进行过滤的查询，第二个键列出现在索引中并不能带来太多收益。
因此，将第二个键列从主索引中移除（从而减少索引的内存占用），并改为使用多个主索引，是合理的做法。
但是，如果复合主键中的各键列在基数上存在较大的差异，那么按照基数升序对主键列进行排序，会对查询更有利。
键列之间的基数差异越大，这些列在键中的顺序就越重要。我们将在下一节中演示这一点。
高效排列主键列顺序
在复合主键中，键列的顺序会显著影响：
- 在查询中对后续键列进行过滤的效率，以及
- 表数据文件的压缩比。
为演示这一点，我们将使用一个网页流量示例数据集的变体，其中每一行包含三列，用来指示某个互联网“用户”（UserID 列）对某个 URL（URL 列）的访问是否被标记为机器人流量（IsRobot 列）。
我们将使用一个包含上述三列的复合主键，用于加速典型的网页分析查询，这些查询会计算：
- 指向某个特定 URL 的流量中有多少（百分比）来自机器人，或
- 我们对某个特定用户是否为机器人的置信度（来自该用户的流量中有多少百分比被认为是（不是）机器人流量）
我们使用下面这个查询来计算计划作为复合主键键列的三列的基数（注意我们使用 URL table function 来对 TSV 数据进行即席查询，而无需创建本地表）。在 clickhouse client 中运行此查询：
返回结果为：
我们可以看到基数存在很大的差异，尤其是 URL 和 IsRobot 列之间的差异。因此，在复合主键中这些列的排列顺序，对于提高在这些列上进行过滤的查询效率，以及使表的列数据文件达到最佳压缩率，都是非常重要的。
为此，我们将为机器人流量分析数据创建两个表版本：
- 表
hits_URL_UserID_IsRobot，其复合主键为(URL, UserID, IsRobot)，其中按基数对键列进行降序排列 - 表
hits_IsRobot_UserID_URL，其复合主键为(IsRobot, UserID, URL)，其中按基数对键列进行升序排列
创建表 hits_URL_UserID_IsRobot，并使用复合主键 (URL, UserID, IsRobot)：
并向其中插入 887 万行数据：
响应如下：
接下来，创建表 hits_IsRobot_UserID_URL，其复合主键为 (IsRobot, UserID, URL)：
并向其中导入与上一张表相同的 887 万行数据：
返回结果为：
在次级键列上进行高效过滤
当查询在至少一个属于复合键且是第一个键列的列上进行过滤时，ClickHouse 会在该键列的索引标记上运行二分查找算法。
当查询只在一个属于复合键但不是第一个键列的列上进行过滤时，ClickHouse 会在该键列的索引标记上使用通用排除搜索算法。
在第二种情况下，复合主键中各键列的排列顺序会显著影响通用排除搜索算法的有效性。
下面是一个查询，它在 UserID 列上进行过滤，该表的键列 (URL, UserID, IsRobot) 按基数降序排列：
返回结果为：
这是在这样一张表上执行的同一个查询：该表的键列 (IsRobot, UserID, URL) 按基数从小到大排列。
返回结果为：
我们可以看到，在按基数升序排列键列的表上，查询执行显著更加高效且更快。
其原因在于，当通过某个次级键列来选择粒度，且其前面的键列具有更低的基数时，通用排除搜索算法 的效果最佳。我们已经在本指南的前一节中对此进行了详细说明。
数据文件的最优压缩率
下面这个查询比较了我们在上面创建的两个表中 UserID 列的压缩率：
查询结果如下：
我们可以看到，对于按基数升序排列键列 (IsRobot, UserID, URL) 的那张表，UserID 列的压缩率显著更高。
虽然这两张表中存储的完全是相同的数据（我们向两张表中都插入了相同的 887 万行），但复合主键中键列的顺序会显著影响表中压缩数据在列数据文件上占用的磁盘空间：
- 在表
hits_URL_UserID_IsRobot中，复合主键为(URL, UserID, IsRobot)，我们按基数降序排列键列，此时UserID.bin数据文件占用 11.24 MiB 的磁盘空间 - 在表
hits_IsRobot_UserID_URL中，复合主键为(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 表中的一行（每次变更一行）。
标识和检索（某个特定版本的）粘贴内容的一种方式，是使用内容的哈希值作为包含该内容的表行的 UUID。
下图展示了
- 当内容发生变化时（例如由于在文本区域中输入按键）行的插入顺序，以及
- 当使用
PRIMARY KEY (hash)时，插入行的数据在磁盘上的存储顺序：
由于使用了 hash 列作为主键列
- 可以非常快速地检索特定行，但
- 表的行（其列数据）在磁盘上按照（唯一且随机的）哈希值升序排列。因此，
content列的值也以随机顺序存储，没有数据局部性，导致content列数据文件的压缩比不理想。
为了在仍然能够快速检索特定行的同时，显著提升 content 列的压缩比，pastila.nl 使用了两个哈希（以及一个复合主键）来标识特定行：
- 如上所述的内容哈希，对不同数据生成不同哈希值，并且
- 一个在数据发生小幅变更时不会改变的局部敏感哈希（fingerprint）。
下图展示了
- 当内容发生变化时（例如由于在文本区域中输入按键）行的插入顺序，以及
- 当使用复合
PRIMARY KEY (fingerprint, hash)时，插入行的数据在磁盘上的存储顺序：
现在，磁盘上的行首先按照 fingerprint 排序；对于 fingerprint 值相同的行，其 hash 值决定最终顺序。
由于仅有小幅差异的数据会得到相同的 fingerprint 值，因此在 content 列中，相似的数据现在会在磁盘上彼此相邻地存储。这对 content 列的压缩比非常有利，因为一般来说，压缩算法会从数据局部性中获益（数据越相似，压缩比通常越好）。
这种权衡在于：为了最优地利用由复合 PRIMARY KEY (fingerprint, hash) 得到的主索引，在检索特定行时需要使用两个字段（fingerprint 和 hash）。
