"
为了简化本指南后续的讨论，并使图表和结果可复现，该 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 语句中的主键会基于指定的两个键列创建主索引。
接下来插入数据：
```sql theme={null}
INSERT INTO hits_UserID_URL SELECT
intHash32(UserID) AS UserID,
URL,
EventTime
FROM url('https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz', 'TSV', 'WatchID UInt64, JavaEnable UInt8, Title String, GoodEvent Int16, EventTime DateTime, EventDate Date, CounterID UInt32, ClientIP UInt32, ClientIP6 FixedString(16), RegionID UInt32, UserID UInt64, CounterClass Int8, OS UInt8, UserAgent UInt8, URL String, Referer String, URLDomain String, RefererDomain String, Refresh UInt8, IsRobot UInt8, RefererCategories Array(UInt16), URLCategories Array(UInt16), URLRegions Array(UInt32), RefererRegions Array(UInt32), ResolutionWidth UInt16, ResolutionHeight UInt16, ResolutionDepth UInt8, FlashMajor UInt8, FlashMinor UInt8, FlashMinor2 String, NetMajor UInt8, NetMinor UInt8, UserAgentMajor UInt16, UserAgentMinor FixedString(2), CookieEnable UInt8, JavascriptEnable UInt8, IsMobile UInt8, MobilePhone UInt8, MobilePhoneModel String, Params String, IPNetworkID UInt32, TraficSourceID Int8, SearchEngineID UInt16, SearchPhrase String, AdvEngineID UInt8, IsArtifical UInt8, WindowClientWidth UInt16, WindowClientHeight UInt16, ClientTimeZone Int16, ClientEventTime DateTime, SilverlightVersion1 UInt8, SilverlightVersion2 UInt8, SilverlightVersion3 UInt32, SilverlightVersion4 UInt16, PageCharset String, CodeVersion UInt32, IsLink UInt8, IsDownload UInt8, IsNotBounce UInt8, FUniqID UInt64, HID UInt32, IsOldCounter UInt8, IsEvent UInt8, IsParameter UInt8, DontCountHits UInt8, WithHash UInt8, HitColor FixedString(1), UTCEventTime DateTime, Age UInt8, Sex UInt8, Income UInt8, Interests UInt16, Robotness UInt8, GeneralInterests Array(UInt16), RemoteIP UInt32, RemoteIP6 FixedString(16), WindowName Int32, OpenerName Int32, HistoryLength Int16, BrowserLanguage FixedString(2), BrowserCountry FixedString(2), SocialNetwork String, SocialAction String, HTTPError UInt16, SendTiming Int32, DNSTiming Int32, ConnectTiming Int32, ResponseStartTiming Int32, ResponseEndTiming Int32, FetchTiming Int32, RedirectTiming Int32, DOMInteractiveTiming Int32, DOMContentLoadedTiming Int32, DOMCompleteTiming Int32, LoadEventStartTiming Int32, LoadEventEndTiming Int32, NSToDOMContentLoadedTiming Int32, FirstPaintTiming Int32, RedirectCount Int8, SocialSourceNetworkID UInt8, SocialSourcePage String, ParamPrice Int64, ParamOrderID String, ParamCurrency FixedString(3), ParamCurrencyID UInt16, GoalsReached Array(UInt32), OpenstatServiceName String, OpenstatCampaignID String, OpenstatAdID String, OpenstatSourceID String, UTMSource String, UTMMedium String, UTMCampaign String, UTMContent String, UTMTerm String, FromTag String, HasGCLID UInt8, RefererHash UInt64, URLHash UInt64, CLID UInt32, YCLID UInt64, ShareService String, ShareURL String, ShareTitle String, ParsedParams Nested(Key1 String, Key2 String, Key3 String, Key4 String, Key5 String, ValueDouble Float64), IslandID FixedString(16), RequestNum UInt32, RequestTry UInt8')
WHERE URL != '';
```
返回结果如下：
```response theme={null}
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.)
```
并对该表进行优化：
```sql theme={null}
OPTIMIZE TABLE hits_UserID_URL FINAL;
```
我们可以使用以下查询来获取该表的元数据：
```sql theme={null}
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;
```
返回结果如下：
```response theme={null}
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 客户端的输出显示：
* 该表的数据以[wide format](/docs/zh/reference/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage)存储在磁盘上的某个特定目录中，这意味着该目录内表的每一列都对应一个数据文件 (以及一个标记文件) 。
* 该表有 887 万行。
* 所有行合计的未压缩数据大小为 733.28 MB。
* 所有行合计在磁盘上的压缩大小为 206.94 MB。
* 该表有一个主索引，包含 1083 个条目 (称为 '标记') ，索引大小为 96.93 KB。
* 总计而言，该表的数据文件、标记文件和主索引文件在磁盘上共占用 207.07 MB。
### 数据在磁盘上按主键列排序存储
我们在上面创建的表具有：
* 一个复合[主键](/docs/zh/reference/engines/table-engines/mergetree-family/mergetree#primary-keys-and-indexes-in-queries) `(UserID, URL)`，以及
* 一个复合[排序键](/docs/zh/reference/engines/table-engines/mergetree-family/mergetree#choosing-a-primary-key-that-differs-from-the-sorting-key) `(UserID, URL, EventTime)`。
- 如果我们只指定排序键，那么主键会被隐式定义为与排序键相同。
- 为了提高内存使用效率，我们显式指定了一个主键，其中只包含查询中过滤条件使用的列。基于主键构建的主索引会完整加载到主内存中。
- 为了让本指南中的示意图保持一致，并尽可能提高压缩率，我们单独定义了一个包含表中所有列的排序键 (如果某一列中的相似数据彼此更接近，例如通过排序实现，那么这些数据通常会有更好的压缩效果)。
- 如果两者都指定了，主键必须是排序键的前缀。
插入的行会按照主键列 (以及排序键中的附加列 `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 行。
* 我们在本指南开头的“DDL Statement Details”中提到过，我们禁用了[自适应索引粒度](/docs/zh/resources/changelogs/oss/2019#experimental-features-1) (这是为了简化本指南中的讨论，同时让图示和结果可以复现) 。
因此，示例表中的所有粒度 (最后一个除外) 大小都相同。
* 对于启用了 自适应索引粒度 的表 (默认情况下 index granularity 是自适应的，参见 [default](/docs/zh/reference/settings/merge-tree-settings#index_granularity_bytes)) ，某些粒度的大小可能会因行数据大小不同而少于 8192 行。
* 我们将主键列 (`UserID`、`URL`) 中的一些列值标记为橙色。
这些橙色标记的列值，就是每个粒度第一行的主键列值。
如下文所示，这些橙色标记的列值将成为表主索引中的条目。
* 我们从 0 开始对粒度编号，以与 ClickHouse 的内部编号方案保持一致，该方案也用于日志消息。
### 主索引中每个粒度对应一个条目
主索引是根据上图所示的粒度创建的。该索引是一个未压缩的一维数组文件 (primary.idx) ，其中包含所谓的数值索引标记，编号从 0 开始。
下图显示，索引会为每个粒度的第一行存储主键列的值 (即上图中以橙色标出的值) 。
换句话说：主索引存储的是表中每隔 8192 行的主键列值 (基于由主键列定义的物理行顺序) 。
例如
* 第一个索引条目 (下图中的“标记 0”) 存储的是上图中粒度 0 的第一行的键列值，
* 第二个索引条目 (下图中的“标记 1”) 存储的是上图中粒度 1 的第一行的键列值，以此类推。
对于这个包含 887 万行和 1083 个粒度的表，该索引总共有 1083 个条目：
* 对于启用了[自适应索引粒度](/docs/zh/resources/changelogs/oss/2019#experimental-features-1)的表，主索引中还会额外存储一个最后的“final”标记，用于记录表最后一行的主键列值。但由于我们禁用了自适应索引粒度 (为了简化本指南中的讨论，并使图示和结果可复现) ，因此示例表的索引中不包含这个最终标记。
* 主索引文件会完整加载到主内存中。如果该文件大于可用的空闲内存，ClickHouse 将报错。
在自管理 ClickHouse 集群中，我们可以使用 [file table function](/docs/zh/reference/functions/table-functions/file) 来检查示例表主索引的内容。
为此，我们首先需要将主索引文件复制到运行中集群某个节点的 [user\_files\_path](/docs/zh/reference/settings/server-settings/settings#user_files_path) 中：
**步骤 1：获取包含主索引文件的 part 路径**
```sql theme={null}
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`。
**步骤 2：获取 user\_files\_path**
Linux 上的[默认 user\_files\_path](https://github.com/ClickHouse/ClickHouse/blob/22.12/programs/server/config.xml#L505)是 `/var/lib/clickhouse/user_files/`
在 Linux 上，你可以检查它是否已被修改：`$ grep user_files_path /etc/clickhouse-server/config.xml`
在测试机器上，该路径是 `/Users/tomschreiber/Clickhouse/user_files/`
**步骤 3：将主索引文件复制到 user\_files\_path**
```bash theme={null}
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 检查主索引的内容：
**获取条目数量**
```sql theme={null}
SELECT count()
FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String');
```
返回 `1083`
**获取前两个索引标记**
```sql theme={null}
SELECT UserID, URL
FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')
LIMIT 0, 2;
```
返回
```response theme={null}
240923, http://showtopics.html%3...
4073710, http://mk.ru&pos=3_0
```
**获取最后一个索引标记**
```sql theme={null}
SELECT UserID, URL FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')
LIMIT 1082, 1;
```
返回
```response theme={null}
4292714039 │ http://sosyal-mansetleri...
```
这与我们为示例表绘制的主索引内容示意图完全一致：
主键条目之所以称为索引标记，是因为每个索引条目都标示着特定数据范围的起始位置。对于该示例表，具体来说：
* UserID 索引标记：
主索引中存储的 `UserID` 值按升序排列。
因此，上图中的“标记 1”表示，粒度 1 以及后续所有粒度中的所有表行，其 `UserID` 值都保证大于或等于 4.073.710。
[正如我们稍后将看到的](#the-primary-index-is-used-for-selecting-granules)，这种全局有序性使 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...'`。
我们稍后会更详细地讨论这对查询执行性能的影响。
### 主索引用于筛选粒度
现在，我们可以借助主索引来执行查询。
下面计算 UserID 749927693 点击次数最多的 10 个 URL。
```sql theme={null}
SELECT URL, count(URL) AS Count
FROM hits_UserID_URL
WHERE UserID = 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;
```
返回结果如下：
```response highlight={15} theme={null}
┌─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 客户端的输出现在显示，ClickHouse 仅流入了 8190 行数据，而不是执行全表扫描。
如果启用了 trace 日志，那么 ClickHouse 服务器日志文件会显示，ClickHouse 在 1083 个 UserID 索引标记上执行了 二分查找，以识别可能包含 UserID 列值为 `749927693` 的行的粒度。这需要 19 个步骤，平均时间复杂度为 `O(log2 n)`：
```response highlight={2,7} theme={null}
...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
```
我们可以从上面的 trace 日志中看到，在现有的 1083 个标记中，只有 1 个标记满足该查询。
已定位到标记 176 ('found left boundary mark' 为包含边界，'found right boundary mark' 为排除边界) ，因此会将粒度 176 中的全部 8192 行 (该粒度从第 1.441.792 行开始——我们将在本指南后文看到) 读入 ClickHouse，以找出 UserID 列值为 `749927693` 的实际行。
我们也可以在示例查询中使用 EXPLAIN 子句 来复现这一点：
```sql theme={null}
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;
```
响应类似如下：
```response highlight={17} theme={null}
┌─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 个粒度中，有 1 个被选中，因为它可能包含 `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 对应的粒度 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 中做进一步处理。
* 对于使用 [wide format](/docs/zh/reference/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage) 且未启用 [自适应索引粒度](/docs/zh/resources/changelogs/oss/2019#experimental-features-1) 的表，ClickHouse 会使用如上图所示的 `.mrk` 标记文件，其中每个条目包含两个 8 字节长的地址。这些条目记录的是粒度的物理位置，而这些粒度的大小都相同。
索引粒度默认是自适应的，参见 [default](/docs/zh/reference/settings/merge-tree-settings#index_granularity_bytes)；但在本示例表中，我们禁用了自适应索引粒度 (以简化本指南中的说明，并使图示和结果可复现) 。我们的表使用 wide format，是因为数据大小超过了 [min\_bytes\_for\_wide\_part](/docs/zh/reference/settings/merge-tree-settings#min_bytes_for_wide_part) (对于自管理 cluster，其默认值为 10 MB) 。
* 对于使用 wide format 且启用了自适应索引粒度的表，ClickHouse 会使用 `.mrk2` 标记文件。它与 `.mrk` 标记文件中的条目类似，但每个条目额外包含第三个值：当前条目所对应粒度的行数。
* 对于使用 [compact format](/docs/zh/reference/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage) 的表，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 会在该键列的索引标记上运行二分查找算法](#the-primary-index-is-used-for-selecting-granules)。
但如果查询按复合键中的某一列进行过滤，而该列并不是第一个键列，会发生什么呢？
这里讨论的是这样一种场景：查询明确不是按第一个键列过滤，而是按次级键列过滤。
当查询同时按第一个键列以及其后的任意键列进行过滤时，ClickHouse 会在第一个键列的索引标记上运行二分查找。
我们使用以下查询来计算点击 URL "[http://public\_search](http://public\_search)" 次数最多的前 10 位用户：
```sql theme={null}
SELECT UserID, count(UserID) AS Count
FROM hits_UserID_URL
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;
```
响应如下：
```response highlight={15} theme={null}
┌─────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.)
```
客户端输出表明，尽管 [URL 列是复合主键的一部分](#a-table-with-a-primary-key)，ClickHouse 仍几乎执行了全表扫描！ClickHouse 从该表的 887 万行中读取了 881 万行。
如果启用了 [trace\_logging](/docs/zh/reference/settings/server-settings/settings#logger)，那么 ClickHouse server 日志文件会显示，ClickHouse 对 1083 个 URL 索引标记使用了通用排除搜索，以识别那些可能包含 URL 列值为 "[http://public\_search](http://public\_search)" 的行的粒度：
```response highlight={3,6} theme={null}
...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
```
我们可以从上面的样本 trace 日志中看到，在 1083 个粒度中，有 1076 个 (通过标记) 被选为可能包含 URL 匹配值的行。
因此，为了找出实际包含 URL 值 "[http://public\_search](http://public\_search)" 的行，881 万行被流式传入 ClickHouse 引擎 (通过 10 个流并行处理) 。
不过，正如我们稍后将看到的，在选中的 1076 个粒度中，实际上只有 39 个粒度包含匹配的行。
虽然基于复合主键 (UserID, URL) 的主索引对于加速按特定 UserID 值过滤行的查询非常有用，但对于按特定 URL 值过滤行的查询，该索引并没有提供明显帮助。
原因在于，URL 列不是第一个键列，因此 ClickHouse 在 URL 列的索引标记上使用的是通用排除搜索算法 (而不是二分查找) ，并且**该算法的有效性取决于** URL 列与其前一个键列 UserID 之间的基数差异。
为了说明这一点，我们先介绍一下通用排除搜索的工作原理。
### 通用排除搜索算法
下面说明了当通过次级列选择粒度，且前一个键列具有较低或较高基数时，ClickHouse 通用排除搜索算法是如何工作的。
作为这两种情况的示例，我们假设：
* 一个查询，用于查找 URL 值为 "W3" 的行。
* 一个抽象化的 hits 表版本，其中 UserID 和 URL 采用简化后的值。
* 索引使用相同的复合主键 (UserID, URL)。这意味着行会先按 UserID 值排序，再按 URL 排序。
* 粒度大小为 2，即每个粒度包含两行。
在下图中，我们用橙色标出了每个粒度首行的键列值。
**前一个键列具有较低基数**
假设 UserID 的基数较低。在这种情况下，相同的 UserID 值很可能会分布在多个表行、粒度以及相应的索引标记中。对于 UserID 相同的索引标记，其 URL 值会按升序排列 (因为表行先按 UserID、再按 URL 排序) 。这就可以实现如下所述的高效过滤：
对于上图中抽象样本数据的粒度选择过程，有三种不同场景：
1. 索引标记 0 的 **URL 值小于 W3，并且其紧随其后的索引标记的 URL 值也小于 W3**，因此可以被排除，因为标记 0 和 1 具有相同的 UserID 值。请注意，这个排除前提保证了粒度 0 完全由 UserID 值为 U1 的行组成，因此 ClickHouse 可以推断粒度 0 中的最大 URL 值也小于 W3，并将该粒度排除。
2. 索引标记 1 的 **URL 值小于 (或等于) W3，并且其紧随其后的索引标记的 URL 值大于 (或等于) W3**，因此会被选中，因为这意味着粒度 1 可能包含 URL 为 W3 的行。
3. 索引标记 2 和 3 的 **URL 值大于 W3**，因此可以被排除，因为主索引的索引标记存储的是每个粒度首个表行的键列值，而表行在磁盘上是按键列值排序的，所以粒度 2 和 3 不可能包含 URL 值为 W3 的行。
**前一个键列具有较高基数**
当 UserID 具有较高基数时，相同的 UserID 值不太可能分布在多个表行和粒度中。这意味着索引标记中的 URL 值并不是单调递增的：
正如我们在上图中看到的，所有显示出的 URL 值小于 W3 的标记都会被选中，以将其关联粒度中的行流式传输到 ClickHouse 引擎中。
这是因为，虽然图中的所有索引标记都属于上文所述的场景 1，但它们不满足前面提到的排除前提，即 *紧随其后的索引标记与当前标记具有相同的 UserID 值*，因此不能被排除。
例如，考虑索引标记 0：其 **URL 值小于 W3，并且其紧随其后的索引标记的 URL 值也小于 W3**。它*不能*被排除，因为紧随其后的索引标记 1 与当前标记 0 的 UserID 值*不*相同。
这最终使 ClickHouse 无法对粒度 0 中的最大 URL 值作出推断。相反，它只能假设粒度 0 可能包含 URL 值为 W3 的行，因此不得不选择标记 0。
标记 1、2 和 3 也是同样的情况。
**结论**
当查询按某个属于复合键但不是第一个键列的列进行过滤时，ClickHouse 使用的不是二分查找算法，而是通用排除搜索算法；当前置键列的基数较低时，这种算法的效果最好。
在我们的样本数据集中，两个键列 (UserID、URL) 的基数都较高且相近。正如前文所述，当 URL 列的前置键列具有较高或相近的基数时，通用排除搜索算法并不太有效。
### 关于数据跳过索引的说明
由于 UserID 和 URL 都具有较高且相近的基数，我们的[按 URL 过滤的查询](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#secondary-key-columns-can-not-be-inefficient)即使在[具有复合主键 (UserID, URL) 的表](#a-table-with-a-primary-key)的 URL 列上创建[二级数据跳过索引](/docs/zh/concepts/features/performance/skip-indexes/skipping-indexes)，收益也不会太大。
例如，下面这两条语句会在我们表的 URL 列上创建并填充一个 [minmax](/docs/zh/reference/engines/table-engines/mergetree-family/mergetree#primary-keys-and-indexes-in-queries) 数据跳过索引：
```sql theme={null}
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 个连续的[粒度](#data-is-organized-into-granules-for-parallel-data-processing)存储 URL 的最小值和最大值 (请注意上文 `ALTER TABLE` 语句中的 `GRANULARITY 4` 子句) ：
第一个索引条目 (上图中的“mark 0”) 存储的是[表中前 4 个粒度对应的行](#data-is-organized-into-granules-for-parallel-data-processing)的 URL 最小值和最大值。
第二个索引条目 (“mark 1”) 存储的是表中接下来 4 个粒度对应的行的 URL 最小值和最大值，依此类推。
(ClickHouse 还为该数据跳过索引创建了一个特殊的[标记文件](#mark-files-are-used-for-locating-granules)，用于[定位](#mark-files-are-used-for-locating-granules)与这些索引标记对应的粒度组。)
由于 UserID 和 URL 都具有类似的高基数，因此在执行[按 URL 过滤的查询](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#secondary-key-columns-can-not-be-inefficient)时，这个二级数据跳过索引无法帮助排除可不选取的粒度。
查询要查找的特定 URL 值 (即“[http://public\_search”](http://public\_search”)) 极有可能落在索引为每组粒度存储的最小值和最大值之间，因此 ClickHouse 不得不选取这些粒度组 (因为其中可能包含与查询匹配的行) 。
### 需要使用多个主索引
因此，如果想显著加快按特定 URL 过滤行的示例查询，就需要使用针对该查询优化的主索引。
此外，如果还想保持按特定 UserID 过滤行的示例查询的良好性能，就需要使用多个主索引。
下面将介绍实现这一点的方法。
### 创建额外主索引的选项
如果我们想同时显著加快两个样本查询——一个按特定 UserID 过滤行，另一个按特定 URL 过滤行——就需要通过以下三种方式之一使用多个主索引：
* 创建一个具有不同主键的**第二张表**。
* 在现有表上创建一个 **materialized view**。
* 为现有表添加一个**投影**。
这三种方式本质上都会将样本数据复制到另一张附加表中，以便重新组织表的主索引和行排序顺序。
不过，这三种方式在这个附加表对用户的透明程度上有所不同，尤其体现在查询和 insert 语句的路由方面。
创建具有不同主键的**第二张表**时，必须显式将查询发送到最适合该查询的表版本，并且还必须将新数据显式插入到两张表中，以保持两张表同步：
使用 **materialized view** 时，附加表会被隐式创建，并且数据会在两张表之间自动保持同步：
而**投影**是透明度最高的选项，因为除了会自动让隐式创建的 (且隐藏的) 附加表与数据变更保持同步之外，ClickHouse 还会自动为查询选择最高效的表版本：
下面我们将通过真实示例，更详细地讨论这三种创建和使用多个主索引的方式。
### 选项 1：辅助表
我们将新建一个附加表，并在主键中调整键列的顺序 (相对于原始表) ：
```sql highlight={8} theme={null}
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_bytes = 0, compress_primary_key = 0;
```
将原始[表](#a-table-with-a-primary-key)中的 887 万行全部插入到附加表中：
```sql theme={null}
INSERT INTO hits_URL_UserID
SELECT * FROM hits_UserID_URL;
```
返回结果如下：
```response theme={null}
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.)
```
最后，对该表执行优化：
```sql theme={null}
OPTIMIZE TABLE hits_URL_UserID FINAL;
```
由于我们调整了主键中各列的顺序，现在插入的行会以不同的词典序存储在磁盘上 (相较于我们的[原始表](#a-table-with-a-primary-key)) ，因此该表的 1083 个粒度所包含的值也与之前不同：
这就是得到的主键：
现在，它可以用来显著加快示例查询的执行速度。该查询会过滤 URL 列，以计算最常点击 URL "[http://public\_search](http://public\_search)" 的前 10 位用户：
```sql highlight={2} theme={null}
SELECT UserID, count(UserID) AS Count
FROM hits_URL_UserID
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;
```
响应如下：
```response highlight={15} theme={null}
┌─────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 不再[几乎进行整表扫描](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#efficient-filtering-on-secondary-key-columns)，而是能够更高效地执行该查询。
在[原始表](#a-table-with-a-primary-key)的主索引中，UserID 是第一主键列，URL 是第二主键列。ClickHouse 为执行该查询，对索引标记使用了 [通用排除搜索](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#generic-exclusion-search-algorithm)，但由于 UserID 和 URL 的基数都很高，效果并不理想。
而当 URL 成为主索引中的第一列后，ClickHouse 现在会对索引标记执行 二分查找。
ClickHouse server 日志文件中相应的 trace 日志也证实了这一点：
```response highlight={3,8} theme={null}
...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 streams
```
ClickHouse 仅选择了 39 个索引标记，而使用 通用排除搜索 时则会选择 1076 个。
请注意，这个附加表经过了优化，可加快我们按 URL 过滤的示例查询的执行。
与该查询在我们的[原始表](#a-table-with-a-primary-key)上表现出的[较差性能](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#secondary-key-columns-can-not-be-inefficient)类似，我们[按 `UserIDs` 过滤的示例查询](#the-primary-index-is-used-for-selecting-granules)在这个新的附加表上运行时也不会很高效，因为 UserID 现在是该表主索引中的第二个键列，因此 ClickHouse 会使用 通用排除搜索 来选择粒度；而对于 UserID 和 URL 这种[同样基数很高](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#generic-exclusion-search-algorithm)的列，这种方式[效果并不理想](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#generic-exclusion-search-algorithm)。
打开详情框查看具体信息。
```sql theme={null}
SELECT URL, count(URL) AS Count
FROM hits_URL_UserID
WHERE UserID = 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;
```
响应如下：
```response highlight={15} theme={null}
┌─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.)
```
服务器日志：
```response highlight={2,5} theme={null}
...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
```
现在我们有两个表，分别针对加快按 `UserIDs` 过滤的查询和按 URL 过滤的查询进行了优化：
### 选项 2：Materialized Views
基于现有表创建一个 [materialized view](/docs/zh/reference/statements/create/view)。
```sql theme={null}
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;
```
响应如下：
```response theme={null}
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.)
```
* 与我们的[原始表](#a-table-with-a-primary-key)相比，我们在视图的主键中调整了键列的顺序
* materialized view 由一个**隐式创建的表**支撑，该表的行顺序和主索引基于给定的主键定义
* 这个隐式创建的表会显示在 `SHOW TABLES` 查询结果中，其名称以 `.inner` 开头
* 也可以先为 materialized view 显式创建其支撑表，然后让该视图通过 `TO [db].[table]` [子句](/docs/zh/reference/statements/create/view) 以该表为目标
* 我们使用 `POPULATE` 关键字，以便立即将源表 [hits\_UserID\_URL](#a-table-with-a-primary-key) 中全部 887 万行填充到这个隐式创建的表中
* 如果新行被插入到源表 hits\_UserID\_URL 中，这些行也会自动插入到这个隐式创建的表中
* 实际上，这个隐式创建的表具有与我们[显式创建的辅助表](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#option-1-secondary-tables)相同的行顺序和主索引：
ClickHouse 会将该隐式创建表的[列数据文件](#data-is-stored-on-disk-ordered-by-primary-key-columns) (*.bin) 、[标记文件](#mark-files-are-used-for-locating-granules) (*.mrk2) 以及[主索引](#the-primary-index-has-one-entry-per-granule) (primary.idx) 存储在 ClickHouse server 数据目录中的一个特殊文件夹内：
现在，可利用支撑该 materialized view 的隐式创建表 (及其主索引) ，显著加快我们这个按 URL 列过滤的示例查询的执行速度：
```sql highlight={2} theme={null}
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;
```
响应如下：
```response highlight={15} theme={null}
┌─────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.)
```
因为实际上，为 materialized view 提供底层支撑而隐式创建的表 (及其主索引) 与[我们显式创建的辅助表](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#option-1-secondary-tables)完全相同，所以该查询的执行方式实际上与使用显式创建的表时相同。
ClickHouse server 日志文件中相应的 trace 日志证实，ClickHouse 正在对索引标记执行二分查找：
```response highlight={3,6} theme={null}
...Executor): Key condition: (column 0 in ['http://public_search',
'http://public_search'])
...Executor): 对索引范围执行二分查找 ...
...
...Executor): 按分区键选中 4/4 个 parts，按主键选中 4 个 parts，
按主键选中 41/1083 个标记，从 4 个范围读取 41 个标记
...Executor): 使用 4 个流读取约 335872 行
```
### 选项 3：投影
在现有表上创建投影：
```sql theme={null}
ALTER TABLE hits_UserID_URL
ADD PROJECTION prj_url_userid
(
SELECT *
ORDER BY (URL, UserID)
);
```
并对该投影进行物化：
```sql theme={null}
ALTER TABLE hits_UserID_URL
MATERIALIZE PROJECTION prj_url_userid;
```
* 该投影会创建一个**隐藏表**，其行顺序和主索引基于投影中给定的 `ORDER BY` 子句
* 该隐藏表不会出现在 `SHOW TABLES` 查询结果中
* 我们使用 `MATERIALIZE` 关键字，以便立即将源表 [hits\_UserID\_URL](#a-table-with-a-primary-key) 中的全部 887 万行填充到这个隐藏表中
* 如果有新行插入源表 hits\_UserID\_URL，这些行也会自动插入隐藏表
* 查询在语法上始终针对源表 hits\_UserID\_URL，但如果隐藏表的行顺序和主索引能让查询执行得更高效，则会改用该隐藏表
* 请注意，投影并不会让使用 ORDER BY 的查询变得更高效，即使该 ORDER BY 与投影的 ORDER BY 语句一致也是如此 (参见 [https://github.com/ClickHouse/ClickHouse/issues/47333](https://github.com/ClickHouse/ClickHouse/issues/47333))
* 实际上，这个隐式创建的隐藏表，与[我们显式创建的辅助表](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#option-1-secondary-tables)具有相同的行顺序和主索引：
ClickHouse 会将隐藏表的[列数据文件](#data-is-stored-on-disk-ordered-by-primary-key-columns) (*.bin)、[标记文件](#mark-files-are-used-for-locating-granules) (*.mrk2) 和[主索引](#the-primary-index-has-one-entry-per-granule) (primary.idx) 存储在一个特殊文件夹中 (如下图中橙色标出的部分) ，该文件夹与源表的数据文件、标记文件和主索引文件位于同一级目录下：
投影创建的隐藏表 (及其主索引) 现在可以 (隐式地) 用于显著加快按 URL 列过滤的示例查询的执行。请注意，从语法上看，该查询针对的仍然是投影的源表。
```sql highlight={2} theme={null}
SELECT UserID, count(UserID) AS Count
FROM hits_UserID_URL
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;
```
响应如下：
```response highlight={15} theme={null}
┌─────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.)
```
由于 projection 创建的隐藏表 (及其主索引) 实际上与[我们显式创建的辅助表](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#option-1-secondary-tables)完全一致，因此，该查询的实际执行方式与使用显式创建的表时相同。
ClickHouse server 的日志文件中对应的 trace 日志证实，ClickHouse 正在对索引标记执行二分查找：
```response highlight={3,5,8} theme={null}
...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) 的表](#a-table-with-a-primary-key)的主索引，对于加速[按 UserID 过滤的查询](#the-primary-index-is-used-for-selecting-granules)非常有用。但尽管 URL 列也是复合主键的一部分，该索引对加速[按 URL 过滤的查询](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#secondary-key-columns-can-not-be-inefficient)并没有明显帮助。
反过来也一样：
我们的[复合主键为 (URL, UserID) 的表](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#option-1-secondary-tables)的主索引能够加速[按 URL 过滤的查询](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#secondary-key-columns-can-not-be-inefficient)，但对[按 UserID 过滤的查询](#the-primary-index-is-used-for-selecting-granules)帮助不大。
由于主键列 UserID 和 URL 的基数都较高且相近，按第二个键列过滤的查询[不会从索引中包含第二个键列这一点获得太多收益](#generic-exclusion-search-algorithm)。
因此，将第二个键列从主索引中移除是合理的 (这样可以减少索引的内存占用) ，并改为[使用多个主索引](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#using-multiple-primary-indexes)。
不过，如果复合主键中的键列在基数上差异很大，那么对于[查询](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#generic-exclusion-search-algorithm)来说，按基数升序排列主键列会更有利。
键列之间的基数差异越大，这些列在键中的顺序就越重要。我们将在下一节中演示这一点。
## 高效安排排序键列
在复合主键中，键列的顺序会显著影响以下两方面：
* 查询中对次级键列进行过滤的效率，以及
* 表的数据文件的压缩率。
为了说明这一点，我们将使用[网站流量样本数据集](#data-set)的一个版本，
其中每一行都包含三列，用于指示某个互联网“用户” (`UserID` 列) 对某个 URL (`URL` 列) 的访问是否被标记为机器人流量 (`IsRobot` 列) 。
我们将使用一个包含上述三列的复合主键，它可用于加速典型的网站分析查询，这类查询用于计算：
* 某个特定 URL 的流量中有多少 (百分比) 来自机器人，或者
* 我们有多大把握认定某个特定用户是 (或不是) 机器人 (该用户流量中有多大比例被视为机器人流量或非机器人流量)
我们使用以下查询来计算这三列的基数，这三列将作为复合主键中的键列 (请注意，我们使用 [URL 表函数](/docs/zh/reference/functions/table-functions/url) 对 TSV 数据进行临时查询，而无需创建本地表) 。请在 `clickhouse client` 中运行此查询：
```sql theme={null}
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 != ''
)
```
返回结果如下：
```response theme={null}
┌─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` 列之间。因此，在复合主键中，这些列的顺序非常重要：它既会影响基于这些列进行过滤的查询加速效果，也会影响表列数据文件能否达到最佳压缩率。
为了演示这一点，我们为机器人流量分析数据创建两个版本的表：
* 表 `hits_URL_UserID_IsRobot`，其复合主键为 `(URL, UserID, IsRobot)`，键列按基数从高到低排序
* 表 `hits_IsRobot_UserID_URL`，其复合主键为 `(IsRobot, UserID, URL)`，键列按基数从低到高排序
使用复合主键 `(URL, UserID, IsRobot)` 创建表 `hits_URL_UserID_IsRobot`：
```sql highlight={8} theme={null}
CREATE TABLE hits_URL_UserID_IsRobot
(
`UserID` UInt32,
`URL` String,
`IsRobot` UInt8
)
ENGINE = MergeTree
PRIMARY KEY (URL, UserID, IsRobot);
```
并向其中插入 887 万行数据：
```sql theme={null}
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 != '';
```
响应如下：
```response theme={null}
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.)
```
接下来，创建表 `hits_IsRobot_UserID_URL`，并使用复合主键 `(IsRobot, UserID, URL)`：
```sql highlight={8} theme={null}
CREATE TABLE hits_IsRobot_UserID_URL
(
`UserID` UInt32,
`URL` String,
`IsRobot` UInt8
)
ENGINE = MergeTree
PRIMARY KEY (IsRobot, UserID, URL);
```
并使用与填充前一个表相同的 887 万行数据来填充该表：
```sql theme={null}
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 != '';
```
响应如下：
```response theme={null}
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 会在该键列的索引标记上运行二分查找算法](#the-primary-index-is-used-for-selecting-granules)。
当查询 (仅) 按复合键中的某一列进行过滤，但该列不是第一个键列时，[ClickHouse 会在该键列的索引标记上使用通用排除搜索算法](/docs/zh/guides/clickhouse/data-modelling/sparse-primary-indexes#secondary-key-columns-can-not-be-inefficient)。
对于第二种情况，复合主键中各键列的顺序会显著影响 [通用排除搜索算法](https://github.com/ClickHouse/ClickHouse/blob/22.3/src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp#L1444) 的效果。
下面这个查询按表中的 `UserID` 列进行过滤；在该表中，我们按基数从高到低排列了键列 `(URL, UserID, IsRobot)`：
```sql theme={null}
SELECT count(*)
FROM hits_URL_UserID_IsRobot
WHERE UserID = 112304
```
响应如下：
```response highlight={6} theme={null}
┌─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)` 进行了排序：
```sql theme={null}
SELECT count(*)
FROM hits_IsRobot_UserID_URL
WHERE UserID = 112304
```
响应如下：
```response highlight={6} theme={null}
┌─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.)
```
我们可以看到，在按基数升序排列键列的表上，查询执行的效率明显更高，速度也更快。
原因在于，当通过次级键列选择[粒度](#the-primary-index-is-used-for-selecting-granules)，且其前一个键列的基数更低时，[通用排除搜索算法](https://github.com/ClickHouse/ClickHouse/blob/22.3/src/Storages/MergeTree/MergeTreeDataSelectExecutor.cpp#L1444)的效果最好。我们已在本指南的[前一节](#generic-exclusion-search-algorithm)中对此进行了详细说明。
### 数据文件的最佳压缩率
此查询比较了我们上面创建的两个表中 `UserID` 列的压缩率：
```sql theme={null}
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
```
响应如下：
```response theme={null}
┌─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 万行数据) ，复合主键中键列的顺序会显著影响表中压缩后数据所需的磁盘空间，也就是表的[列数据文件](#data-is-stored-on-disk-ordered-by-primary-key-columns)占用的空间：
* 在表 `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 更少。
下面我们来说明，为什么按基数升序排列主键列有利于提升表中各列的压缩率。
下图示意了当键列按基数升序排列时，主键对应的行在磁盘上的顺序：
我们前面已经讨论过，[表的行数据在磁盘上会按主键列的顺序存储](#data-is-stored-on-disk-ordered-by-primary-key-columns)。
在上图中，表中的行 (即它们在磁盘上的列值) 首先按 `cl` 值排序，而 `cl` 值相同的行再按 `ch` 值排序。由于第一个键列 `cl` 的基数较低，因此很可能存在多行具有相同的 `cl` 值。正因如此，`ch` 值也很可能呈现有序状态 (局部有序——即对于那些 `cl` 值相同的行而言) 。
如果某一列中的相似数据彼此相邻，例如通过排序实现，那么这些数据通常会有更好的压缩效果。
一般来说，压缩算法会受益于数据的连续长度 (看到的相似数据越多，通常越有利于压缩)
以及局部性 (数据越相似，压缩率就越高) 。
与上图相对，下图示意了当键列按基数降序排列时，主键对应的行在磁盘上的顺序：
现在，表中的行会先按其 `ch` 值排序，而 `ch` 值相同的行再按其 `cl` 值排序。
但由于第一个键列 `ch` 的基数很高，出现 `ch` 值相同的行的可能性很低。因此，`cl` 值也不太可能是有序的 (局部来看——即在 `ch` 值相同的那些行中) 。
因此，`cl` 值很可能是随机排列的，相应地，其局部性和压缩率通常都会比较差。
### 总结
无论是为了在查询中高效过滤二级键列，还是为了提高表列数据文件的压缩率，按基数从低到高排列主键中的各列都更有利。
## 高效识别单行
虽然总的来说，这[并不是](/docs/zh/resources/support-center/knowledge-base/general-faqs/key-value) ClickHouse 的最佳适用场景，
但有时构建在 ClickHouse 之上的应用确实需要识别 ClickHouse 表中的单行。
一个直观的解决方案是使用一个 [UUID](https://en.wikipedia.org/wiki/Universally_unique_identifier) 列，为每一行分配唯一值，并将该列用作主键列，以便快速检索行。
为了实现最快的检索，UUID 列[需要作为第一个键列](#the-primary-index-is-used-for-selecting-granules)。
正如我们前面所讨论的，由于 [ClickHouse 表的行数据在磁盘上按主键列顺序存储](#data-is-stored-on-disk-ordered-by-primary-key-columns)，因此在主键或复合主键中，将基数很高的列 (例如 UUID 列) 放在基数较低的列之前，[会损害其他表列的压缩率](#optimal-compression-ratio-of-data-files)。
兼顾最快检索速度和最佳数据压缩效果的一种折中方案，是使用复合主键，并将 UUID 作为最后一个键列，放在基数较低的键列之后；这些列可用于确保表中某些列获得良好的压缩率。
### 一个具体示例
一个具体示例是明文粘贴服务 [https://pastila.nl](https://pastila.nl)。该服务由 Alexey Milovidov 开发，并曾在[博客中介绍](https://clickhouse.com/blog/building-a-paste-service-with-clickhouse/)。
文本区域每发生一次变化，数据都会自动保存到 ClickHouse 表中的一行 (每次变更对应一行) 。
识别并检索粘贴内容的某个特定版本的一种方法，是将内容的哈希值用作包含该内容的表行 UUID。
下图展示了：
* 内容发生变化时各行的插入顺序 (例如在文本区域中输入文本时产生的击键) ，以及
* 使用 `PRIMARY KEY (hash)` 时，这些已插入行的数据在磁盘上的排列顺序：
由于 `hash` 列被用作主键列，
* 可以[非常快速地](#the-primary-index-is-used-for-selecting-granules)检索特定行，但
* 表中的行 (即其列数据) 会按哈希值 (唯一且随机) 升序存储在磁盘上。因此，content 列的值也会以随机顺序存储，缺乏数据局部性，从而导致**content 列数据文件的压缩率不理想**。
为了在仍能快速检索特定行的同时，显著提升 content 列的压缩率，pastila.nl 使用两个哈希值 (以及一个复合主键) 来标识特定行：
* 如上所述，内容的一个哈希值，对不同数据会产生不同的值；以及
* 一个在数据仅发生细微变化时**不会**改变的[局部敏感哈希 (指纹) ](https://en.wikipedia.org/wiki/Locality-sensitive_hashing)。
下图展示了：
* 内容发生变化时各行的插入顺序 (例如在文本区域中输入文本时产生的击键) ，以及
* 使用复合 `PRIMARY KEY (fingerprint, hash)` 时，这些已插入行的数据在磁盘上的排列顺序：
现在，磁盘上的行会先按 `fingerprint` 排序；对于 `fingerprint` 值相同的行，再由其 `hash` 值决定最终顺序。
由于仅有细微差异的数据会得到相同的指纹值，相似的数据如今会在磁盘上的 content 列中彼此相邻存储。这对 content 列的压缩率非常有利，因为压缩算法通常能从数据局部性中受益 (数据越相似，压缩率通常越高) 。
这种折中在于：为了最优地利用由复合 `PRIMARY KEY (fingerprint, hash)` 产生的主索引，检索特定行时需要使用两个字段 (`fingerprint` 和 `hash`) 。