ClickHouse 主索引的实用介绍

简介

在本指南中，我们将深入研究 ClickHouse 索引。我们将详细说明和讨论

• ClickHouse 中的索引与传统关系数据库管理系统的不同之处
• ClickHouse 如何构建和使用表的稀疏主索引
• ClickHouse 中索引的一些最佳实践

您可以选择性地在您自己的机器上执行本指南中给出的所有 ClickHouse SQL 语句和查询。有关 ClickHouse 的安装和入门说明，请参阅 快速入门。

注意

本指南重点介绍 ClickHouse 稀疏主索引。

有关 ClickHouse 二级数据跳过索引，请参阅 教程。

数据集

在本指南中，我们将使用一个匿名的示例 Web 流量数据集。

• 我们将使用示例数据集中的 887 万行（事件）子集。
• 未压缩的数据大小为 887 万个事件，约为 700 MB。存储在 ClickHouse 中时，压缩到 200 MB。
• 在我们的子集中，每一行包含三个列，指示一个互联网用户（UserID 列）点击了一个 URL（URL 列）在特定时间（EventTime 列）。

有了这三个列，我们已经可以制定一些典型的 Web 分析查询，例如

• “对于特定用户，点击次数最多的前 10 个 URL 是什么？”
• “最频繁点击特定 URL 的前 10 名用户是谁？”
• “用户点击特定 URL 的最受欢迎时间（例如，一周中的某天）是什么？”

测试机器

本文档中给出的所有运行时数字均基于在配备 Apple M1 Pro 芯片和 16GB 内存的 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(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 != '';

响应是

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 TABLE hits_NoPrimaryKey FINAL;

注意

通常，在将数据加载到表中后立即优化表是不必要的，也不推荐的。为什么这对于本示例是必要的，将会变得显而易见。

现在我们执行我们的第一个 Web 分析查询。以下是计算 UserID 为 749927693 的互联网用户点击次数最多的前 10 个 URL

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.
# highlight-next-line
Processed 8.87 million rows,
70.45 MB (398.53 million rows/s., 3.17 GB/s.)

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 树的重新平衡。

考虑到与 B 树索引相关的挑战，ClickHouse 中的表引擎采用不同的方法。ClickHouse 的 MergeTree Engine Family 旨在处理海量数据。这些表旨在每秒接收数百万行插入，并存储非常大的（数百 PB）数据量。数据快速写入表 逐部分，并应用规则以在后台合并这些部分。在 ClickHouse 设计的大规模应用中，必须非常高效地利用磁盘和内存。因此，与其索引每一行，不如为每个部分的主索引创建一个索引条目（称为“标记”）每组行（称为“粒度”）-这种技术称为稀疏索引。

稀疏索引是可能的，因为 ClickHouse 将部分中的行按主键列排序存储在磁盘上。与其直接定位单个行（如基于 B 树的索引），稀疏主索引允许它快速（通过在索引条目上进行二进制搜索）识别可能匹配查询的行组。定位的潜在匹配行组（粒度）然后并行流式传输到 ClickHouse 引擎以查找匹配项。这种索引设计允许主索引很小（它可以，并且必须，完全适合主内存），同时仍然显著加快查询执行时间：特别是对于数据分析用例中常见的范围查询。

以下详细说明了 ClickHouse 如何构建和使用其稀疏主索引。稍后在文章中，我们将讨论选择、删除和排序用于构建索引（主键列）的表列的一些最佳实践。

具有主键的表

创建一个具有 UserID 和 URL 作为键列的复合主键的表

CREATE TABLE hits_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime
)
ENGINE = MergeTree
-- highlight-next-line
PRIMARY KEY (UserID, URL)
ORDER BY (UserID, URL, EventTime)
SETTINGS index_granularity_bytes = 0, compress_primary_key = 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。

  • compress_primary_key：设置为 0 以禁用 主索引压缩。这将允许我们选择性地检查其内容。

DDL 语句中的主键导致基于指定的两个键列创建主索引。

接下来插入数据

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 != '';

响应如下所示

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 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 个条目（称为“标记”），索引大小为 96.93 KB。
• 总而言之，表的数据、标记文件和主索引文件一起占用磁盘上的 207.07 MB。

数据按主键列排序后存储在磁盘上

我们上面创建的表包含

• 一个复合 主键 (UserID, URL) 和
• 一个复合 排序键 (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 语句详细信息”中提到，我们禁用了 自适应索引颗粒度（为了简化本指南中的讨论，并使图表和结果可重现）。

  因此，我们示例表的除最后一个颗粒外的所有颗粒的大小都相同。

• 对于具有自适应索引颗粒度的表（索引颗粒度默认情况下由 index_granularity_bytes 调整），某些颗粒的大小可能小于 8192 行，具体取决于行数据的大小。

• 我们用橙色标记了来自主键列（UserID、URL）的一些列值。这些橙色标记的列值是每个颗粒的第一行的主键列值。正如我们将在下面看到的那样，这些橙色标记的列值将是表的主索引中的条目。

• 我们从 0 开始对颗粒进行编号，以便与 ClickHouse 内部编号方案保持一致，该方案也用于记录消息。

主索引每个颗粒有一个条目

主索引是基于上述图表中显示的颗粒创建的。该索引是一个未压缩的平面数组文件（primary.idx），包含从 0 开始的所谓数值索引标记。

下图显示了索引存储了每个颗粒的第一行的主键列值（或换句话说：主索引存储了表每 8192 行的主键列值（基于由主键列定义物理行顺序））。例如

• 第一个索引条目（下图中的“标记 0”）存储了来自上述图表中的颗粒 0 的第一行的键列值，
• 第二个索引条目（下图中的“标记 1”）存储了来自上述图表中的颗粒 1 的第一行的键列值，依此类推。

对于我们具有 887 万行和 1083 个颗粒的表，索引总共有 1083 个条目

注意

• 对于具有 自适应索引颗粒度的表，主索引中还存储一个“最终”附加标记，该标记记录了最后一个表行的主键列值，但由于我们禁用了自适应索引颗粒度（为了简化本指南中的讨论，并使图表和结果可重现），我们示例表的索引不包含此最终标记。

• 主索引文件完全加载到主内存中。如果文件大于可用的可用内存空间，则 ClickHouse 将引发错误。

检查主索引的内容

在自助管理的 ClickHouse 集群上，我们可以使用 file 表函数来检查我们示例表的主索引内容。

为此，我们首先需要将主索引文件复制到运行集群中一个节点的 user_files_path 中

• 步骤 1：获取包含主索引文件的 part-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。

• 步骤 2：获取 user_files_path
Linux 上的 默认 user_files_path 是 /var/lib/clickhouse/user_files/

并且在 Linux 上您可以检查它是否已更改：$ grep user_files_path /etc/clickhouse-server/config.xml

在测试机器上，路径是 /Users/tomschreiber/Clickhouse/user_files/

• 步骤 3：将主索引文件复制到 user_files_path

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'); 返回 1083
• 获取前两个索引标记
SELECT 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...

这与我们示例表的主索引内容的图表完全匹配

主键条目称为索引标记，因为每个索引条目都在标记特定的数据范围的开始。具体来说，对于示例表

• UserID 索引标记

  主索引中存储的 UserID 值按升序排序。
  因此，图表中的“标记 1”表示表颗粒 1 以及所有后续颗粒的 UserID 值都保证大于或等于 4.073.710。

如我们稍后将看到，这种全局顺序使 ClickHouse 能够 使用二分搜索算法在查询过滤主键的第一列时，对索引标记进行搜索。

• URL 索引标记

  主键列 UserID 和 URL 的相当相似的基数意味着，在第一个列之后的所有键列的索引标记通常仅指示一个数据范围，只要至少当前颗粒的所有表行，前一个键列值保持相同。
  例如，由于图表中标记 0 和标记 1 的 UserID 值不同，ClickHouse 无法假定颗粒 0 中所有表行的所有 URL 值都大于或等于 'http://showtopics.html%3...'。但是，如果图表中标记 0 和标记 1 的 UserID 值相同（这意味着 UserID 值在颗粒 0 中的所有表行保持相同），ClickHouse 可以假定颗粒 0 中所有表行的所有 URL 值都大于或等于 'http://showtopics.html%3...'。

  我们将在后面更详细地讨论这对查询执行性能的影响。

主索引用于选择数据粒度

现在我们可以使用主索引的支持来执行我们的查询。

以下计算用户ID为749927693的前10个点击最多的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.
# highlight-next-line
Processed 8.19 thousand rows,
740.18 KB (1.53 million rows/s., 138.59 MB/s.)

ClickHouse客户端的输出现在显示，不是执行全表扫描，而是仅流式传输了8.19千行数据到ClickHouse。

如果启用了追踪日志，那么ClickHouse服务器日志文件会显示ClickHouse正在对1083个UserID索引标记执行二分搜索，以识别可能包含UserID列值为749927693的数据粒度。这需要19步，平均时间复杂度为O(log2 n)

...Executor): Key condition: (column 0 in [749927693, 749927693])
# highlight-next-line
...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,
# highlight-next-line
              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                                                        │
# highlight-next-line
│                     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对应的粒度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使用如上图所示的.mrk标记文件，其中包含每个条目有两个8字节长地址。

索引粒度默认情况下是自适应的，但对于我们的示例表格，我们禁用了自适应索引粒度（为了简化本指南中的讨论，并使图表和结果可重现）。我们的表格使用宽格式，因为数据的大小大于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的两个物理位置偏移量。

标记文件提供的间接寻址避免在主索引中直接存储所有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引擎进行进一步处理，即聚合和计数URL值，为所有UserID为749.927.693的行，然后最终按降序计数输出前10个最大的URL组。

使用多个主索引

二级键列可能（不）效率低下

当查询过滤的是复合键的一部分并且是第一个键列时，ClickHouse将在键列的索引标记上运行二分搜索算法。

但是，如果查询过滤的是复合键的一部分，但不是第一个键列呢？

注意

我们讨论一个查询明确不在第一个键列上进行过滤，而是在二级键列上进行过滤的情况。

当查询同时过滤第一个键列和第一个键列之后的任何键列时，ClickHouse将在第一个键列的索引标记上运行二分搜索。

我们使用一个计算点击“http://public_search”URL次数最多的前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.
# highlight-next-line
Processed 8.81 million rows,
799.69 MB (102.11 million rows/s., 9.27 GB/s.)

客户端输出表明，尽管URL列是复合主键的一部分，但ClickHouse几乎执行了全表扫描！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'])
# highlight-next-line
...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,
# highlight-next-line
              1076/1083 marks by primary key, 1076 marks to read from 5 ranges
...Executor): Reading approx. 8814592 rows with 10 streams

我们可以在上面的示例追踪日志中看到，通过（标记）选择了1076个（通过标记）粒度，作为可能包含匹配URL值的粒度。

这导致881万行被流式传输到ClickHouse引擎（通过使用10个流并行），以识别实际包含URL值“http://public_search”的行。

然而，正如我们稍后将看到的那样，只有39个粒度在选定的1076个粒度中实际包含匹配的行。

虽然基于复合主键（UserID，URL）的主索引对于加速过滤具有特定UserID值的行非常有用，但该索引对于加速过滤具有特定URL值的查询没有提供显着帮助。

造成这种情况的原因是 URL 列不是第一个主键列，因此 ClickHouse 使用通用的排除搜索算法（而不是二元搜索）来搜索 URL 列的索引标记，并且该算法的有效性取决于 URL 列与其前置主键列 UserID 之间的基数差异。

为了说明这一点，我们给出一些关于通用排除搜索工作原理的细节。

通用排除搜索算法

以下说明了当通过二级列选择颗粒时，ClickHouse 通用排除搜索算法在先导主键列具有较低或较高的基数时的运作方式。

作为两个案例的示例，我们假设

• 一个查询，用于搜索 URL 值为 "W3" 的行。
• 一个抽象化的 hits 表，其中 UserID 和 URL 的值已简化。
• 相同的复合主键 (UserID, URL) 用于索引。这意味着行首先按 UserID 值排序。具有相同 UserID 值的行然后按 URL 排序。
• 颗粒大小为两个，即每个颗粒包含两行。

我们在下面的图中用橙色标记了每个颗粒的第一行表数据的键列值。

先导主键列具有较低的基数

假设 UserID 具有较低的基数。在这种情况下，相同的 UserID 值很可能分布在多个表行和颗粒以及索引标记上。对于具有相同 UserID 的索引标记，URL 值按升序排序（因为表行首先按 UserID 然后按 URL 排序）。这允许高效的过滤，如下所述

对于我们抽象样本数据，在上面的图中，颗粒选择过程有三种不同的场景

1. 索引标记 0，其 URL 值小于 W3，并且直接后续索引标记的 URL 值也小于 W3 可以排除，因为标记 0 和 1 具有相同的 UserID 值。请注意，此排除前提条件确保颗粒 0 完全由 U1 UserID 值组成，因此 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 过滤的查询从 URL 列创建二级数据跳过索引也无法获得太大收益。

例如，这两个语句创建并填充一个minmax数据跳过索引到我们具有复合主键 (UserID, URL) 的表的 URL 列。

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 值

第一个索引条目（图中的“标记 0”）存储了属于我们表的前 4 个颗粒的行的最小和最大 URL 值。

第二个索引条目（“标记 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
-- highlight-next-line
PRIMARY KEY (URL, UserID)
ORDER BY (URL, UserID, EventTime)
SETTINGS index_granularity_bytes = 0, compress_primary_key = 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 列以计算最频繁点击“http://public_search”URL 的前 10 名用户

SELECT UserID, count(UserID) AS Count
-- highlight-next-line
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.
# highlight-next-line
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'])
# highlight-next-line
...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,
# highlight-next-line
              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。

与我们的 原始表 中该查询的 性能不佳 类似，我们 示例查询过滤 UserIDs 使用新的附加表将无法有效运行，因为 UserID 现在是该表主索引中的第二个键列，因此 ClickHouse 将使用通用排除搜索来选择颗粒，这对于 UserID 和 URL 的相似高基数来说 效率不高。打开详细信息框以获取具体信息。

现在过滤 UserIDs 的查询性能不佳

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.
# highlight-next-line
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])
# highlight-next-line
...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,
# highlight-next-line
              980/1083 marks by primary key, 980 marks to read from 23 ranges
...Executor): Reading approx. 8028160 rows with 10 streams

我们现在有两个表。分别针对加速过滤 UserIDs 的查询和加速过滤 URLs 的查询进行了优化

选项 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 关键字以便立即使用源表 hits_UserID_URL 中的所有 887 万行填充隐式创建的表
• 如果将新行插入到源表 hits_UserID_URL 中，则这些行也会自动插入到隐式创建的表中
• 实际上，隐式创建的表具有与 我们显式创建的辅助表 相同的行顺序和主索引

ClickHouse 将隐式创建的表的 列数据文件 (.bin)、标记文件 (.mrk2) 和 主索引 (primary.idx) 存储在 ClickHouse 服务器数据目录中的一个特殊文件夹中

支持物化视图的隐式创建的表（及其主索引）现在可以显著加速我们示例查询的执行，该查询过滤 URL 列

SELECT UserID, count(UserID) AS Count
-- highlight-next-line
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.
# highlight-next-line
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'])
# highlight-next-line
...Executor): Running binary search on index range ...
...
...Executor): Selected 4/4 parts by partition key, 4 parts by primary key,
# highlight-next-line
              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，但如果隐藏表的行顺序和主索引允许更有效的查询执行，则将使用该隐藏表
• 请注意，即使 ORDER BY 与投影的 ORDER BY 语句匹配，投影也不会使使用 ORDER BY 的查询更有效（请参阅 https://github.com/ClickHouse/ClickHouse/issues/47333）
• 实际上，隐式创建的隐藏表具有与 我们显式创建的辅助表 相同的行顺序和主索引

ClickHouse 将隐藏表的 列数据文件 (.bin)、标记文件 (.mrk2) 和 主索引 (primary.idx) 存储在源表数据文件、标记文件和主索引文件旁边的特殊文件夹（如下截图中的橙色标记）中

通过投影创建的隐藏表（及其主索引）现在可以（隐式地）用于显著加速我们示例查询的执行，该查询过滤 URL 列。请注意，查询在语法上针对投影的源表。

SELECT UserID, count(UserID) AS Count
-- highlight-next-line
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.
# highlight-next-line
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'])
# highlight-next-line
...Executor): Running binary search on index range for part prj_url_userid (1083 marks)
...Executor): ...
# highlight-next-line
...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,
# highlight-next-line
              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 列）。

我们将使用包含上述三个列的复合主键，该主键可用于加速典型的网络分析查询，以计算

• 有多少（百分比）流量到特定 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 列之间存在很大差异，因此这些列在复合主键中的顺序对于加速过滤这些列的查询和实现表列数据文件的最佳压缩率都非常重要。

为了证明这一点，我们正在为我们的机器人流量分析数据创建两个表版本

• 一个表 hits_URL_UserID_IsRobot，其复合主键为 (URL, UserID, IsRobot)，我们按基数降序排列键列
• 一个表 hits_IsRobot_UserID_URL，其复合主键为 (IsRobot, UserID, URL)，我们按基数升序排列键列

创建具有复合主键 (URL, UserID, IsRobot) 的表 hits_URL_UserID_IsRobot

CREATE TABLE hits_URL_UserID_IsRobot
(
    `UserID` UInt32,
    `URL` String,
    `IsRobot` UInt8
)
ENGINE = MergeTree
-- highlight-next-line
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.)

接下来，创建表 hits_IsRobot_UserID_URL，并使用复合主键 (IsRobot, UserID, URL)

CREATE TABLE hits_IsRobot_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `IsRobot` UInt8
)
ENGINE = MergeTree
-- highlight-next-line
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.
# highlight-next-line
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.
# highlight-next-line
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 万行插入到两个表中），但复合主键中键列的顺序对表 压缩 数据所需的磁盘空间有重大影响，以及表的 列数据文件。

• 在表 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 列用作主键列

• 可以 非常快速地 检索特定行，但是
• 表的行（它们的列数据）按（唯一且随机的）哈希值升序存储在磁盘上。因此，内容列的值也以随机顺序存储，没有数据局部性，导致内容列数据文件的 **次优压缩率**。

为了显著提高内容列的压缩率，同时仍然实现特定行的快速检索，pastila.nl 使用两个哈希（和一个复合主键）来标识特定行

• 内容的哈希，如上所述，对于不同的数据是不同的，以及
• 一个 局部敏感哈希（指纹），它不会在数据的微小变化中发生变化。

下图显示了

• 当内容更改时（例如，由于键入文本到文本区域中的按键）行的插入顺序，以及
• 当使用复合 PRIMARY KEY (fingerprint, hash) 时，插入行的数据的磁盘顺序

现在磁盘上的行首先按 fingerprint 排序，并且对于具有相同指纹值的行，它们的 hash 值决定了最终顺序。

因为仅在微小变化中不同的数据获得相同的指纹值，相似的数据现在以局部性方式存储在磁盘上的内容列中。这对于内容列的压缩率非常好，因为压缩算法通常受益于数据局部性（数据越相似，压缩率越高）。

妥协方案是，为了能够最佳利用由复合主键 PRIMARY KEY (fingerprint, hash) 产生的 первичный индекс，检索特定行需要两个字段（fingerprint 和 hash）。