ClickHouse 主索引实用入门指南
介绍
在本指南中，我们将深入探讨 ClickHouse 的索引机制。我们将详细说明：
你可以选择在本地环境中自行执行本指南中给出的所有 ClickHouse SQL 语句和查询。 有关 ClickHouse 的安装和入门说明，请参阅快速开始。
数据集
在整个指南中，我们将使用一个匿名化的 Web 流量示例数据集。
- 我们将使用该示例数据集中的一个子集，共 887 万行（事件）。
- 未压缩的数据大小为 887 万个事件，大约 700 MB。存储在 ClickHouse 中时压缩后约为 200 MB。
- 在我们的子集中，每一行包含三列，分别表示某个互联网用户（
UserID列）在特定时间（EventTime列）点击了某个 URL（URL列）。
仅凭这三列，我们已经可以编写一些典型的 Web 分析查询，例如：
- “对于某个特定用户，被点击次数最多的 10 个 URL 是哪些？”
- “对于某个特定 URL，点击该 URL 最频繁的前 10 个用户是谁？”
- “用户点击某个特定 URL 时，最热门的时间段是什么（例如一周中的哪几天）？”
测试机器
本文中给出的所有运行时数据均基于在一台配备 Apple M1 Pro 芯片和 16GB 内存的 MacBook Pro 上本地运行 ClickHouse 22.2.1 所得。
全表扫描
为了了解在没有主键的数据集上查询是如何执行的，我们通过执行以下 SQL DDL 语句来创建一张使用 MergeTree 表引擎的表：
接下来，使用以下 SQL INSERT 语句将 hits 数据集的一个子集插入到该表中。
这里使用了 URL 表函数，以便从 clickhouse.com 上托管的完整数据集中远程加载一个子集：
响应如下：
ClickHouse 客户端的结果输出显示，上述语句向该表插入了 887 万行记录。
最后，为了简化本指南后续的讨论，并使图示和结果便于复现，我们使用 FINAL 关键字对该表进行 优化：
一般来说，在将数据加载到表之后，既不需要也不建议立即对表进行优化。至于为什么在本示例中需要这样做，稍后就会变得清楚。
现在我们来执行第一个网站分析查询。下面的查询会计算出针对 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）。数据会被快速写入表的各个 part，并在后台根据规则对这些 part 进行合并。在 ClickHouse 中，每个 part 都有自己的主索引。当 part 被合并时，合并后 part 的主索引也会相应合并。由于 ClickHouse 是为极大规模而设计的，因此在磁盘和内存使用方面必须极其高效。因此，ClickHouse 并不是对每一行进行索引，而是让每个 part 的主索引对一组行（称为 “granule”）维护一个索引条目（称为 “mark”）——这种技术称为 稀疏索引（sparse index）。
稀疏索引之所以可行，是因为 ClickHouse 在磁盘上存储某个 part 的行时，会按主键列的顺序进行组织。稀疏主索引并不像基于 B-Tree 的索引那样直接定位单行，而是通过对索引条目进行二分查找，快速识别出可能匹配查询的行组。被定位出的这些可能匹配的行组（granule）随后会并行地流式传入 ClickHouse 引擎，以查找真正的匹配行。这种索引设计使得主索引可以保持很小（并且可以、也必须完全常驻内存），同时仍能显著加速查询执行时间，特别是对数据分析场景中常见的范围查询。
下面将详细说明 ClickHouse 如何构建和使用其稀疏主索引。在本文后续部分，我们还将讨论在构建索引（主键列）时，关于选择、移除和排序相关表列的一些最佳实践。
带主键的表
创建一个具有复合主键的表，主键列为 UserID 和 URL：
DDL 语句详解
为了简化本指南后续的讨论，并使图示和结果便于复现，这条 DDL 语句：
通过
ORDER BY子句为该表指定了一个复合排序键。通过以下设置显式控制主索引将包含多少条索引条目：
index_granularity：显式设置为其默认值 8192。这意味着对于每 8192 行数据，主索引会有一条索引条目。比如，如果表中包含 16384 行，那么索引会有两条索引条目。index_granularity_bytes：设置为 0 以禁用 自适应索引粒度（adaptive index granularity）。自适应索引粒度意味着，当下列任一条件满足时，ClickHouse 会自动为一组 n 行创建一条索引条目：如果
n小于 8192，并且这n行合并后的行数据大小大于或等于 10 MB（index_granularity_bytes的默认值）。如果这
n行合并后的行数据大小小于 10 MB，但n为 8192。
compress_primary_key：设置为 0 以禁用 主索引压缩。这将允许我们在后续按需检查其内容。
上面的 DDL 语句中的主键会基于这两个指定的键列创建主索引。
接下来插入数据：
响应结果如下所示：
并优化该表：
我们可以使用以下查询来获取该表的元数据信息：
响应如下：
ClickHouse 客户端的输出显示：
- 表的数据以磁盘上特定目录中的宽格式存储，这意味着在该目录中，表中的每一列都会对应一个数据文件（以及一个标记文件）。
- 该表包含 8.87 百万行。
- 所有行未压缩的数据总大小为 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 以便并行数据处理
出于数据处理的目的，表的列值在逻辑上被划分为多个 granule。 granule 是以流式方式送入 ClickHouse 进行数据处理的最小不可再分的数据集合。 这意味着，ClickHouse 不是按单行读取数据，而是始终以流式且并行的方式读取一整组（granule）行。
列值并不是以 granule 为单位进行物理存储：granule 只是为查询处理而对列值进行的一种逻辑组织方式。
下图展示了我们这张表的 887 万行（的列值）如何被组织成 1083 个 granule，这源于表的 DDL 语句中包含了 index_granularity 设置（其值为默认的 8192）。
第一批（基于磁盘上的物理顺序）8192 行（它们的列值）在逻辑上属于 granule 0，接下来的 8192 行（它们的列值）属于 granule 1，如此类推。
-
最后一个 granule（granule 1082）“包含”的行数少于 8192 行。
-
我们在本指南开头的 “DDL Statement Details” 中提到过，我们禁用了 adaptive index granularity（以简化本指南中的讨论，并使图示和结果可复现）。
因此，在我们的示例表中，所有 granule（除最后一个外）大小都相同。
-
对于启用了 adaptive index granularity 的表（索引粒度在默认 情况下是自适应的），部分 granule 的大小可能会小于 8192 行，这取决于行数据的大小。
-
我们用橙色标出了主键列（
UserID、URL）中的某些列值。 这些被橙色标出的列值是每个 granule 第一行的主键列值。 正如下文所示，这些被橙色标出的列值将会成为表主索引中的条目。 -
我们从 0 开始为 granule 编号，这是为了与 ClickHouse 的内部编号方案保持一致，该方案也用于日志消息中。
主索引对每个数据粒度都有一个条目
主索引是基于上图所示的数据粒度创建的。该索引是一个未压缩的扁平数组文件（primary.idx），其中包含从 0 开始编号的数值索引标记。
下图展示了该索引为每个数据粒度的第一行存储主键列的值（即上图中用橙色标出的值）。 换句话说：主索引存储的是表中每隔 8192 行的主键列值（基于由主键列定义的物理行顺序）。 例如：
- 第一个索引条目（下图中的 “mark 0”）存储的是上图中数据粒度 0 的第一行的键列值，
- 第二个索引条目（下图中的 “mark 1”）存储的是上图中数据粒度 1 的第一行的键列值，依此类推。
对于我们这个拥有 887 万行和 1083 个数据粒度的表，索引总共有 1083 个条目：
-
对于启用了 自适应索引粒度 的表，主索引中还会额外存储一个“最终”标记，用来记录表最后一行的主键列值。但由于我们在本指南中禁用了自适应索引粒度（这样可以简化讨论，并使图示和结果可复现），因此示例表的索引中不包含这个最终标记。
-
主索引文件会被完整加载到内存中。如果该文件大于可用的空闲内存空间，ClickHouse 将报错。
检查主索引的内容
在自托管的 ClickHouse 集群中，我们可以使用 file 表函数 来检查示例表的主索引内容。
为此，我们首先需要将主索引文件复制到正在运行的集群中某个节点的 user_files_path 中：
- 步骤 1：获取包含主索引文件的 part 路径
- 步骤 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的基数非常接近，这意味着总体来说，对于除第一列之外的所有键列，其索引标记通常只能表示一个数据范围，前提是前一键列的值在至少当前 granule 内的所有表行中都保持不变。
例如，由于上图中标记 0 和标记 1 的 UserID 值不同，ClickHouse 无法假定 granule 0 中所有表行的 URL 值都大于或等于'http://showtopics.html%3...'。但是，如果上图中标记 0 和标记 1 的 UserID 值相同（意味着 granule 0 中所有表行的 UserID 值都相同），那么 ClickHouse 就可以假定 granule 0 中所有表行的 URL 值都大于或等于'http://showtopics.html%3...'。我们将在后面更详细地讨论这对查询执行性能的影响。
主索引用于选择索引颗粒
现在我们可以在主索引的支持下执行查询。
下面的查询计算了 UserID 749927693 点击次数最多的前 10 个 URL。
响应如下：
现在 ClickHouse 客户端的输出显示，ClickHouse 不再进行全表扫描，而是只向 ClickHouse 流入了 8.19 千行数据。
如果启用了 trace 级别日志，那么 ClickHouse 服务器日志文件会显示，ClickHouse 正在对 1083 个 UserID 索引标记执行二分查找，以定位那些可能包含 UserID 列值为 749927693 的 granule。这个过程需要 19 步，平均时间复杂度为 O(log2 n)：
从上面的 trace 日志中可以看到，在现有的 1083 个 mark 中，只有 1 个 mark 满足该查询。
Trace 日志详情
标识为 176 的 mark 被识别出来（'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。其对应的粒度（granule）176 因此有可能包含 UserID 列值为 749.927.693 的行。
粒度选择细节
上图显示，标记 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 中，我们这张表所有粒度的物理位置都存储在标记文件（mark file）中。与数据文件类似，每个表列都有一个对应的标记文件。
下图展示了三个标记文件 UserID.mrk、URL.mrk 和 EventTime.mrk，它们存储了该表 UserID、URL 和 EventTime 列各个粒度的物理位置。
我们已经讨论过，主索引是一个扁平的未压缩数组文件（primary.idx），其中包含的索引标记从 0 开始编号。
类似地，标记文件也是一个扁平的未压缩数组文件（*.mrk），其中的标记同样从 0 开始编号。
一旦 ClickHouse 已经为某个查询识别并选定了可能包含匹配行的粒度对应的索引标记，就可以在标记文件中基于该位置进行数组查找，以获取该粒度的物理位置。
每个特定列的标记文件条目以偏移量（offset）的形式存储两个位置：
-
第一个偏移量（上图中的
block_offset）用于定位压缩列数据文件中包含所选粒度压缩版本的块（block），该列数据文件是压缩过的。这个压缩块中可能包含若干个被压缩的粒度。被定位到的压缩文件块在读取时会被解压到主内存中。 -
第二个偏移量（上图中的
granule_offset）来自标记文件，提供了该粒度在解压后的块数据中的位置。
随后，属于被定位到的未压缩粒度的全部 8192 行会被流式读取到 ClickHouse 中以供进一步处理。
索引粒度在默认情况下是自适应的，但对于本示例表，我们禁用了自适应索引粒度（以简化本指南中的讨论，并使图示和结果可复现）。我们的表使用宽格式，是因为其数据大小大于 min_bytes_for_wide_part（在自管集群中默认值为 10 MB）。
-
对于采用宽格式且启用了自适应索引粒度的表，ClickHouse 使用
.mrk2标记文件，其中的条目与.mrk标记文件类似，但每个条目中额外包含第三个值：当前条目所关联粒度的行数。 -
对于采用紧凑格式的表，ClickHouse 使用
.mrk3标记文件。
为什么主索引不会直接包含与索引标记对应的粒度（granule）的物理位置？
因为在 ClickHouse 所设计的超大规模场景下，磁盘和内存的高效利用至关重要。
主索引文件需要能够完全装入主内存。
在我们的示例查询中，ClickHouse 使用主索引并只选择了一个可能包含匹配行的粒度。只有对于这个粒度，ClickHouse 才需要知道物理位置，以便将对应的行流式读取出来进行后续处理。
此外，这些偏移量信息只需要用于 UserID 和 URL 两列。
对于未在查询中使用的列（例如 EventTime），则不需要偏移量信息。
在我们的示例查询中，ClickHouse 只需要 UserID 数据文件（UserID.bin）中粒度 176 的两个物理位置偏移量，以及 URL 数据文件（URL.bin）中粒度 176 的两个物理位置偏移量。
通过 mark 文件引入这一层间接寻址，可以避免在主索引中直接存储三列全部 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 还是几乎执行了一次全表扫描！ClickHouse 从该表的 887 万行中读取了 881 万行。
如果启用了 trace_logging，则 ClickHouse 服务器日志文件会显示 ClickHouse 在 1083 个 URL 索引标记上使用了通用排除搜索（generic exclusion search），以识别那些可能包含 URL 列值为 "http://public_search" 的行的粒度（granule）：
从上面的示例 trace 日志中可以看到，通过索引标记，在 1083 个 granule 中有 1076 个被选为“可能包含具有匹配 URL 值的行”的 granule。
这会导致有 881 万行数据被流式读取到 ClickHouse 引擎中（通过 10 个并行数据流完成），以便找出那些实际包含 URL 值 "http://public_search" 的行。
然而，我们稍后会看到，在选中的这 1076 个 granule 中，实际上只有 39 个 granule 包含匹配的行。
虽然基于复合主键 (UserID, URL) 的主键索引对于加速按特定 UserID 值过滤行的查询非常有用，但对于仅按特定 URL 值过滤行的查询，这个索引并没有提供显著的加速效果。
原因在于 URL 列不是第一个键列，因此 ClickHouse 在 URL 列的索引标记上使用的是通用排除搜索算法（而不是二分搜索），并且该算法的有效性取决于 URL 列与其前一个键列 UserID 之间的基数差异。
为说明这一点，我们将介绍一些关于通用排除搜索如何工作的细节。
通用排除搜索算法
下面将展示当通过一个次级列来选择 granule，而它前面的键列具有较低或较高基数时，ClickHouse 通用排除搜索算法 是如何工作的。
针对这两种情况，我们作如下假设：
- 查询在搜索 URL 值为 "W3" 的行。
- 使用一个抽象化的 hits 表，其中 UserID 和 URL 使用了简化的值。
- 索引采用相同的复合主键 (UserID, URL)。这意味着行首先按 UserID 排序，具有相同 UserID 的行再按 URL 排序。
- granule 大小为 2，即每个 granule 包含两行。
在下面的图示中，我们用橙色标出了每个 granule 的首行的键列值。
假设 UserID 的基数较低。在这种情况下，相同的 UserID 值很可能分布在多行和多个 granule 中，因此也分布在多个 index mark 上。对于具有相同 UserID 的 index mark，这些 mark 的 URL 值按升序排列（因为表行首先按 UserID 排序，然后按 URL 排序）。这使得可以进行如下所述的高效过滤：
对于上图中抽象样本数据的 granule 选择过程，有三种不同的情况：
-
对于 index mark 0，其 URL 值小于 W3，且其直接后继 index mark 的 URL 值也小于 W3，可以将其排除，因为 mark 0 和 1 具有相同的 UserID 值。请注意，此排除前提条件确保 granule 0 完全由 UserID 为 U1 的行组成，因此 ClickHouse 可以假定 granule 0 中的最大 URL 值也小于 W3，从而排除该 granule。
-
对于 index mark 1，其 URL 值小于（或等于）W3，且其直接后继 index mark 的 URL 值大于（或等于）W3，需要选择该 mark，因为这意味着 granule 1 可能包含 URL 为 W3 的行。
-
对于 index mark 2 和 3，其 URL 值大于 W3，可以将其排除，因为主索引的 index mark 存储的是每个 granule 首行的键列值，并且表行在磁盘上按键列值排序，因此 granule 2 和 3 不可能包含 URL 值为 W3 的行。
当 UserID 具有较高基数时，相同的 UserID 值分布在多行和多个 granule 中的情况就不太可能发生。这意味着 index mark 的 URL 值不再是单调递增的：
如上图所示，所有 URL 值小于 W3 的 mark 都会被选中，以便将其关联 granule 的行流式传入 ClickHouse 引擎。
这是因为，尽管图中的所有 index mark 都符合前面描述的场景 1，但它们不满足前述排除前提条件——直接后继 index mark 的 UserID 值与当前 mark 相同——因此无法被排除。
例如，对于 index mark 0，其 URL 值小于 W3，且其直接后继 index mark 的 URL 值也小于 W3，它不能被排除，因为直接后继的 index mark 1 的 UserID 值并不与当前 mark 0 相同。
这最终使 ClickHouse 无法对 granule 0 中的最大 URL 值做出假设。相反，它必须假定 granule 0 可能包含 URL 值为 W3 的行，因此被迫选择 mark 0。
对于 mark 1、2 和 3，同样适用这一情况。
在我们的示例数据集中，两个键列（UserID、URL）都具有类似的高基数。如前所述，当位于 URL 列之前的键列具有较高或相近的基数时，通用排除搜索算法的效果并不理想。
关于 data skipping index 的说明
由于 UserID 和 URL 都具有类似的高基数，我们在 URL 上的查询过滤，即使在来自我们复合主键表 (UserID, URL)的 URL 列上创建一个辅助 data skipping index，收益也不会太大。
例如，下面这两个语句会在我们表的 URL 列上创建并填充一个 minmax data 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 过滤行——那么我们需要通过以下三种方式之一来使用多个主索引：
- 创建一张具有不同主键的第二张表。
- 在现有表上创建一个物化视图。
- 在现有表上添加一个 projection。
这三种选项都会有效地将我们的示例数据复制到一张额外的表中，以便重新组织表的主索引和行排序顺序。
不过，这三种选项在查询和插入语句的路由方式上，对用户而言该额外表的透明程度有所不同。
当创建一张具有不同主键的第二张表时，查询必须显式发送到最适合该查询的表版本，并且必须显式地向两张表中插入新数据，以保持两张表的数据同步：
使用物化视图时，会隐式创建额外的表，并且会在两张表之间自动保持数据同步：
而 projection 则是透明度最高的选项，因为除了自动保持隐式创建（且对用户隐藏）的额外表与数据变更同步之外，ClickHouse 还会自动为查询选择最有效的表版本：
在下文中，我们将结合实际示例，更详细地讨论这三种创建和使用多个主索引的选项。
选项 1：辅助表
我们将创建一个新的辅助表，在该表的主键中交换键列的顺序（相对于原始表）：
将 原始表 中的全部 887 万行插入到这个新增的表中：
响应类似如下：
最后，对表进行优化：
由于我们在主键中调整了列的顺序，插入的行现在在磁盘上的字典序（与原始表相比）发生了变化，因此该表的 1083 个数据粒度中所包含的值也与之前不同：
这是得到的主键：
现在可以利用它显著加速我们示例查询的执行：该查询在 URL 列上进行过滤，以计算最频繁点击 URL "http://public_search" 的前 10 位用户：
返回结果为：
现在，ClickHouse 不再需要几乎做一次全表扫描，而是能够更加高效地执行该查询。
在原始表的主键设计中，UserID 是第一列，URL 是第二个键列。ClickHouse 在执行该查询时，会在索引标记上使用通用排除搜索算法，但由于 UserID 和 URL 都具有类似的高基数，这种方式的效果并不理想。
当在主键中将 URL 调整为第一列后，ClickHouse 现在会在索引标记上运行二分搜索。 ClickHouse 服务器日志文件中的相应 trace 日志也印证了这一点：
ClickHouse 只选择了 39 个索引标记，而在使用通用排除搜索时会选择 1076 个。
注意，这个额外的表是专门为加速我们按 URL 过滤的示例查询的执行而优化的。
类似于该查询在我们原始表上的性能不佳，我们按 UserIDs 过滤的示例查询在新的额外表上执行也不会很高效，因为在这个表的主键中，UserID 现在是第二个键列，因此 ClickHouse 在 granule 选择时会使用通用排除搜索，而当 UserID 和 URL 都具有类似的高基数时，这种方式并不高效。
打开详情框以查看具体信息。
我们现在有两个表，分别针对按 UserIDs 过滤的查询以及按 URL 过滤的查询进行了加速优化：
选项 2：物化视图
在现有的表上创建一个物化视图。
响应如下：
- 我们在视图的主键中调整了键列的顺序（相对于原始表）
- 该物化视图由一个隐式创建的表作为底层表支撑，该表的行顺序和主索引基于给定的主键定义
- 这个隐式创建的表会出现在
SHOW TABLES查询结果中，并且名称以.inner开头 - 也可以先显式创建物化视图所依赖的底层表，然后通过
TO [db].[table]子句 让视图指向该表 - 我们使用
POPULATE关键字，以便立刻用源表 hits_UserID_URL 中全部 887 万行数据填充该隐式创建的表 - 如果向源表 hits_UserID_URL 插入新行，这些行也会自动插入到该隐式创建的表中
- 从效果上看，这个隐式创建的表拥有与我们显式创建的二级表相同的行顺序和主索引：
ClickHouse 会将这个隐式创建的表的列数据文件（.bin）、mark 文件（.mrk2）以及主索引（primary.idx）存储在 ClickHouse 服务器数据目录中的一个特殊文件夹内：
现在，这个作为物化视图底层表的隐式创建表（及其主索引）可以用于显著加速我们在 URL 列上进行过滤的示例查询的执行：
响应如下：
由于为该物化视图隐式创建的底层表（及其主索引）实际上与我们显式创建的辅助表完全相同，因此查询的实际执行方式与使用显式创建的表时是一样的。
ClickHouse 服务器日志文件中的对应跟踪日志确认，ClickHouse 正在对索引标记执行二分查找：
选项 3：投影（Projection）
在现有表上创建一个投影：
并物化该投影：
- 该 projection 会创建一个隐藏表，其行顺序和主键索引基于该 projection 中给定的
ORDER BY子句 - 该隐藏表不会出现在
SHOW TABLES查询的结果中 - 我们使用
MATERIALIZE关键字，以便立刻将源表 hits_UserID_URL 中全部 887 万行数据填充到隐藏表中 - 如果向源表 hits_UserID_URL 中插入新行，则这些行也会自动插入到隐藏表中
- 查询在语法上始终是针对源表 hits_UserID_URL，但如果隐藏表的行顺序和主键索引可以实现更高效的查询执行，则会改为使用该隐藏表来执行查询
- 请注意，projection 并不会让使用 ORDER BY 的查询变得更高效，即使该 ORDER BY 与 projection 的 ORDER BY 语句完全匹配（参见 https://github.com/ClickHouse/ClickHouse/issues/47333）
- 实际上，隐式创建的隐藏表拥有与我们显式创建的二级表相同的行顺序和主键索引：
ClickHouse 会将隐藏表的列数据文件（.bin）、mark 文件（.mrk2）以及主键索引（primary.idx）存储在一个特殊的文件夹中（在下方截图中用橙色标记），该文件夹位于源表的数据文件、mark 文件和主键索引文件所在位置的旁边：
由 projection 创建的隐藏表（及其主键索引）现在可以（隐式地）用来显著加速我们示例查询对 URL 列的过滤执行。请注意，该查询在语法上仍是针对该 projection 的源表。
返回结果为：
由于投影创建的隐藏表（及其主索引）本质上与我们显式创建的辅助表相同，查询的执行方式与使用显式创建的表在实际效果上没有区别。
ClickHouse 服务器日志文件中的相应 trace 日志证实 ClickHouse 正在对索引标记执行二分查找：
总结
我们的复合主键表 (UserID, URL) 的主索引在加速按 UserID 过滤的查询方面非常有用。但该索引在加速按 URL 过滤的查询方面并没有提供显著帮助，尽管 URL 列也是复合主键的一部分。
反之亦然： 我们的复合主键表 (URL, UserID) 的主索引在加速按 URL 过滤的查询方面非常有效，但对按 UserID 过滤的查询帮助不大。
由于主键列 UserID 和 URL 的基数都较高且相近，对第二个键列进行过滤的查询，并不能从第二个键列包含在索引中获得太多收益。
因此，将第二个键列从主索引中移除（从而减少索引的内存消耗），并改为使用多个主索引是合理的做法。
不过，如果复合主键中的各键列在基数上存在较大差异，那么按基数从小到大排列主键列，对查询是有益的。
键列之间的基数差异越大，它们在主键中的排列顺序就越重要。我们将在下一节中对此进行演示。
高效地为键列排序
在复合主键中，键列的顺序会显著影响以下两点：
- 查询中过滤次级键列的效率，以及
- 表数据文件的压缩率。
为演示这一点，我们将使用网页流量示例数据集的一个变体，
其中每一行包含三列，用于指示某次互联网「用户」（UserID 列）访问某个 URL（URL 列）时，该访问是否被标记为机器人流量（IsRobot 列）。
我们将使用一个包含上述三列的复合主键，用于加速典型的网页分析查询，这类查询会计算：
- 某个特定 URL 的流量中有多少（百分比）来自机器人，或者
- 我们对某个特定用户是否为机器人的判断有多大把握（来自该用户的流量中有多少百分比被认为是机器人流量或非机器人流量）
我们使用下列查询来计算计划在复合主键中用作键列的这三列的基数（注意我们使用了 URL 表函数，以便对 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) 按基数从小到大排列：
响应如下：
我们可以看到，在那张对键列按基数升序排序的表上，查询执行明显更加高效且更快。
其原因在于，当通过某个次级键列来选择 granules，且其前一个键列具有更低的基数时，generic exclusion search algorithm 的效果最佳。我们已经在本指南的前一节中对这一点进行了详细说明。
数据文件的最佳压缩率
此查询比较了我们在上面创建的两个表中 UserID 列的压缩率：
响应如下：
我们可以看到，对于 UserID 列，当我们按照基数的升序对键列 (IsRobot, UserID, URL) 排序时，其压缩比显著更高。
尽管在这两张表中存储的完全是相同的数据（我们向两张表都插入了相同的 8.87 百万行数据），但复合主键中键列的顺序会对表中压缩数据在该表列数据文件上所需的磁盘空间产生显著影响：
- 在表
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 值的行。也正因为如此，对于具有相同 cl 值的行，其 ch 值也很可能是（局部地）有序的。
如果在某一列中，相似的数据彼此靠得很近，例如通过排序实现，那么这些数据会被压缩得更好。 一般来说，压缩算法会从数据的“运行长度”（看到的数据越多，压缩效果越好） 和“局部性”（数据越相似，压缩比越好）中获益。
与上图相反，下面的示意图展示了当主键列按基数降序排序时，行在磁盘上的顺序：
现在，表的行首先按它们的 ch 值排序，具有相同 ch 值的行再按它们的 cl 值排序。
但是因为第一个键列 ch 具有很高的基数，因此几乎不可能存在具有相同 ch 值的行。也正因为如此，cl 值在局部范围内（对具有相同 ch 值的行而言）也几乎不可能是有序的。
因此，cl 值极有可能近似于随机顺序，从而局部性较差，相应地压缩率也会较低。
总结
为了在查询中高效过滤次键列并提高表列数据文件的压缩比，建议按基数从小到大的顺序排列主键中的各个列。
高效地定位单行记录
尽管总体而言，将 ClickHouse 用作键值存储并不是最理想的用例，但有时构建在 ClickHouse 之上的应用程序需要在 ClickHouse 表中定位单行记录。
一个直观的解决方案是使用一个 UUID 列，使每一行都有唯一值，并将该列用作主键列，以便可以快速检索行。
为了实现最快的检索，UUID 列需要作为第一个主键列。
我们已经讨论过，由于ClickHouse 表的行数据在磁盘上的存储顺序是按主键列排序的，在主键中，或者在复合主键中将一个基数非常高的列（例如 UUID 列）放在基数较低的列之前，会降低表中其他列的压缩比。
在最快检索与最优数据压缩之间的一种折中做法，是使用复合主键，并将 UUID 作为最后一个键列，放在用于保证部分表列良好压缩比的低（或较低）基数键列之后。
一个具体示例
一个具体的示例是纯文本粘贴服务 https://pastila.nl，由 Alexey Milovidov 开发，并在博客中进行了介绍。
每当文本区域发生变更时，数据都会自动保存为 ClickHouse 表中的一行记录（每次变更一行）。
识别并检索（某个特定版本的）粘贴内容的一种方式，是使用内容的哈希值作为包含该内容的表行的 UUID。
下图展示了
- 当内容发生变化时（例如由于用户在文本区域中键入文本）行被插入的顺序，以及
- 当使用
PRIMARY KEY (hash)时，插入行在磁盘上的数据存储顺序：
由于 hash 列被用作主键列：
- 可以非常快速地检索特定行，但
- 表中的行（其列数据）在磁盘上按（唯一且随机的）hash 值升序存储。因此，content 列的值也会以随机顺序存储，没有数据局部性，从而导致 content 列数据文件的压缩比不理想。
为了在仍然能够快速检索特定行的同时显著提升 content 列的压缩比，pastila.nl 使用两个哈希值（以及一个复合主键）来标识一行记录：
- 一个内容哈希，如上所述，对不同数据产生不同的值；
- 一个局部敏感哈希（fingerprint），在数据发生少量变化时不会改变。
下图展示了
- 当内容发生变化时（例如由于用户在文本区域中键入文本）行被插入的顺序，以及
- 当使用复合主键
PRIMARY KEY (fingerprint, hash)时，插入行在磁盘上的数据存储顺序：
现在，磁盘上的行首先按 fingerprint 排序；对于 fingerprint 值相同的行，再由其 hash 值决定最终顺序。
因为仅有少量差异的数据会获得相同的 fingerprint 值，相似的数据现在在 content 列中会在磁盘上彼此相邻存储。这对 content 列的压缩比非常有利，因为压缩算法通常会从数据局部性中获益（数据越相似，压缩比通常越好）。
这种权衡在于：为充分利用由复合主键 PRIMARY KEY (fingerprint, hash) 产生的主索引，在检索特定行时需要使用两个字段（fingerprint 和 hash）。
