跳到主要内容
跳到主要内容
编辑此页

ClickHouse 中的主键索引实用介绍

简介​

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

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

注意

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

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

数据集​

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

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

通过这三列，我们已经可以制定一些典型的网络分析查询，例如

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

测试机器​

本文档中给出的所有运行时数字均基于在配备 Apple M1 Pro 芯片和 16GB RAM 的 MacBook Pro 上本地运行 ClickHouse 22.2.1。

全表扫描​

为了了解在没有主键的情况下如何对我们的数据集执行查询，我们通过执行以下 SQL DDL 语句创建一个表（使用 MergeTree 表引擎）

CREATE TABLE hits_NoPrimaryKey
(
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime
)
ENGINE = MergeTree
PRIMARY KEY tuple();

接下来，使用以下 SQL 插入语句将点击数据集的子集插入到表中。这使用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;
注意

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

现在我们执行我们的第一个网络分析查询。以下是计算 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.
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(+)-Tree 是非常浅的结构，并且只需要少量磁盘寻道即可定位记录。对于 887 万行和 1000 的分支因子，平均需要 2.3 次磁盘寻道。此功能是有代价的：额外的磁盘和内存开销、向表中添加新行和向索引添加条目时更高的插入成本，以及有时 B 树的重新平衡。

考虑到与 B 树索引相关的挑战，ClickHouse 中的表引擎采用了不同的方法。ClickHouse MergeTree 引擎家族旨在优化处理海量数据。这些表旨在每秒接收数百万行的插入，并存储非常大（数百 PB）的数据量。数据快速写入表，一部分一部分地写入，并应用规则在后台合并这些部分。在 ClickHouse 中，每个部分都有自己的主键索引。当部分合并时，合并部分的主键索引也会合并。在 ClickHouse 设计的非常大的规模下，磁盘和内存效率至关重要。因此，主键索引不是索引每一行，而是每个行组（称为“granule”）有一个索引条目（称为“mark”） - 这种技术称为稀疏索引。

稀疏索引是可能的，因为 ClickHouse 将部分行存储在磁盘上，并按主键列排序。稀疏主键索引不是直接定位单个行（如基于 B 树的索引），而是可以快速（通过对索引条目进行二进制搜索）识别可能与查询匹配的行组。然后，并行地将定位到的潜在匹配行组（granule）流式传输到 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 = 8192, 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 个条目（称为“marks”）的主键索引，索引的大小为 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 内部行编号方案保持一致，该方案也用于日志消息。

数据组织成 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 语句详情”中提到，我们禁用了自适应索引粒度（为了简化本指南中的讨论，并使图表和结果可重现）。

    因此，我们示例表的所有 granule（最后一个除外）都具有相同的大小。

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

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

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

主键索引每个 granule 有一个条目​

主键索引是根据上图所示的 granule 创建的。此索引是一个未压缩的平面数组文件 (primary.idx)，其中包含从 0 开始的所谓数值索引标记。

下图显示，索引存储了每个 granule 的第一行的主键列值（上图中以橙色标记的值）。或者换句话说：主键索引存储了表中每 8192 行（基于主键列定义的物理行顺序）的主键列值。例如

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

我们的表总共有 1083 个条目的索引，其中包含 887 万行和 1083 个 granule

注意

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

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

检查主键索引的内容

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

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

  • 步骤 1：获取包含主键索引文件的部件路径
    • 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<br/>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...'。

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

主索引用于选择粒度​

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

以下代码计算了 UserID 为 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.
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])
...Executor): Running binary search on index range for part all_1_9_2 (1083 marks)
...Executor): Found (LEFT) boundary mark: 176
...Executor): Found (RIGHT) boundary mark: 177
...Executor): Found continuous range in 19 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
              1/1083 marks by primary key, 1 marks to read from 1 ranges
...Reading ...approx. 8192 rows starting from 1441792

我们可以在上面的跟踪日志中看到，1083 个现有标记中有一个标记满足查询条件。

跟踪日志详情

标记 176 被识别（“找到的左边界标记”是包含性的，“找到的右边界标记”是排他性的），因此来自粒度 176 的所有 8192 行（从第 1.441.792 行开始 - 我们将在本指南的后面看到）都被流式传输到 ClickHouse 中，以便找到 UserID 列值为 749927693 的实际行。

我们还可以通过在示例查询中使用 EXPLAIN 子句 来重现这一点。

EXPLAIN indexes = 1
SELECT URL, count(URL) AS Count
FROM hits_UserID_URL
WHERE UserID = 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;

响应看起来像

┌─explain───────────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection)                                                               │
│   Limit (preliminary LIMIT (without OFFSET))                                          │
│     Sorting (Sorting for ORDER BY)                                                    │
│       Expression (Before ORDER BY)                                                    │
│         Aggregating                                                                   │
│           Expression (Before GROUP BY)                                                │
│             Filter (WHERE)                                                            │
│               SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│                 ReadFromMergeTree                                                     │
│                 Indexes:                                                              │
│                   PrimaryKey                                                          │
│                     Keys:                                                             │
│                       UserID                                                          │
│                     Condition: (UserID in [749927693, 749927693])                     │
│                     Parts: 1/1                                                        │
│                     Granules: 1/1083                                                  │
└───────────────────────────────────────────────────────────────────────────────────────┘

16 rows in set. Elapsed: 0.003 sec.

客户端输出显示，在 1083 个粒度中，有一个粒度被选定为可能包含 UserID 列值为 749927693 的行。

结论

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


如上所述，ClickHouse 使用其稀疏主索引来快速（通过二分搜索）选择可能包含与查询匹配的行的粒度。

这是 ClickHouse 查询执行的第一阶段（粒度选择）。

在第二阶段（数据读取）中，ClickHouse 定位选定的粒度，以便将它们的所有行流式传输到 ClickHouse 引擎中，以便找到实际与查询匹配的行。

我们将在以下部分更详细地讨论第二阶段。

标记文件用于定位粒度​

下图说明了我们表的主索引文件的一部分。

如上所述，通过对索引的 1083 个 UserID 标记进行二分搜索，标记 176 被识别出来。因此，其对应的粒度 176 可能包含 UserID 列值为 749.927.693 的行。

粒度选择详情

上图显示，标记 176 是第一个索引条目，其中关联的粒度 176 的最小 UserID 值小于 749.927.693，并且下一个标记（标记 177）的粒度 177 的最小 UserID 值大于此值。因此，只有标记 176 对应的粒度 176 才可能包含 UserID 列值为 749.927.693 的行。

为了确认（或不确认）粒度 176 中的某些行包含 UserID 列值为 749.927.693，需要将属于此粒度的所有 8192 行流式传输到 ClickHouse 中。

为了实现这一点，ClickHouse 需要知道粒度 176 的物理位置。

在 ClickHouse 中，我们表中所有粒度的物理位置都存储在标记文件中。与数据文件类似，每个表列都有一个标记文件。

下图显示了三个标记文件 UserID.mrk、URL.mrk 和 EventTime.mrk，它们存储了表 UserID、URL 和 EventTime 列的粒度的物理位置。

我们已经讨论过主索引是一个扁平的未压缩数组文件 (primary.idx)，其中包含从 0 开始编号的索引标记。

类似地，标记文件也是一个扁平的未压缩数组文件 (*.mrk)，其中包含从 0 开始编号的标记。

一旦 ClickHouse 识别并选择了可能包含与查询匹配的行的粒度的索引标记，就可以在标记文件中执行位置数组查找，以获取粒度的物理位置。

每个特定列的标记文件条目都以偏移量的形式存储两个位置。

  • 第一个偏移量（上图中的 'block_offset'）定位了 <块 (block)，该块位于 压缩列数据文件中，其中包含所选粒度的压缩版本。这个压缩块可能包含几个压缩粒度。读取时，位于其中的压缩文件块将被解压缩到主内存中。

  • 标记文件中的第二个偏移量（上图中的 'granule_offset'）提供了粒度在解压缩块数据中的位置。

然后，属于位于其中的解压缩粒度的所有 8192 行都被流式传输到 ClickHouse 中进行进一步处理。

注意
  • 对于具有宽格式且没有自适应索引粒度的表，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 引擎中进行进一步处理，即聚合和计数 UserID 为 749.927.693 的所有行的每个组的 URL 值，然后在降序计数顺序中最终输出 10 个最大的 URL 组。

使用多个主索引​

辅助键列可能（并非一定）效率低下​

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

但是，当查询在复合键的一部分但不是第一个键列的列上进行过滤时，会发生什么情况？

注意

我们讨论一个场景，其中查询明确不筛选第一个键列，而是筛选辅助键列。

当查询同时筛选第一个键列和第一个键列之后的任何键列时，ClickHouse 会对第一个键列的索引标记运行二分搜索。



我们使用一个查询来计算最常点击 URL "http://public_search" 的前 10 名用户。

SELECT UserID, count(UserID) AS Count
FROM hits_UserID_URL
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;

响应是：

┌─────UserID─┬─Count─┐
│ 2459550954 │  3741 │
│ 1084649151 │  2484 │
│  723361875 │   729 │
│ 3087145896 │   695 │
│ 2754931092 │   672 │
│ 1509037307 │   582 │
│ 3085460200 │   573 │
│ 2454360090 │   556 │
│ 3884990840 │   539 │
│  765730816 │   536 │
└────────────┴───────┘

10 rows in set. Elapsed: 0.086 sec.
Processed 8.81 million rows,
799.69 MB (102.11 million rows/s., 9.27 GB/s.)

客户端输出表明，尽管 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'])
...Executor): Used generic exclusion search over index for part all_1_9_2
              with 1537 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
              1076/1083 marks by primary key, 1076 marks to read from 5 ranges
...Executor): Reading approx. 8814592 rows with 10 streams

我们可以在上面的示例跟踪日志中看到，1083 个粒度中有 1076 个（通过标记）被选定为可能包含匹配的 URL 值的行。

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

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

虽然基于复合主键 (UserID, URL) 的主索引对于加速筛选具有特定 UserID 值的行的查询非常有用，但该索引对于加速筛选具有特定 URL 值的行的查询没有提供显著的帮助。

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

为了说明这一点，我们将详细介绍通用排除搜索的工作原理。

通用排除搜索算法​

以下说明了当通过辅助列选择粒度时，ClickHouse 通用排除搜索算法如何工作，其中前一个键列具有较低或较高的基数。

作为两种情况的示例，我们将假设：

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

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

前一个键列具有较低的基数

假设 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 过滤查询 也不会从在我们的 具有复合主键 (UserID, URL) 的表 的 URL 列上创建 辅助数据跳过索引 中受益太多。

例如，以下两个语句在表的 URL 列上创建并填充一个 minmax 数据跳过索引。

ALTER TABLE hits_UserID_URL ADD INDEX url_skipping_index URL TYPE minmax GRANULARITY 4;
ALTER TABLE hits_UserID_URL MATERIALIZE INDEX url_skipping_index;

ClickHouse 现在创建了一个额外的索引，该索引存储 - 每组 4 个连续的粒度（请注意上面 ALTER TABLE 语句中的 GRANULARITY 4 子句）- 最小和最大 URL 值。

第一个索引条目（上图中的“标记 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 = 8192, 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 列，以便计算最常点击 URL "http://public_search" 的前 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.
Processed 319.49 thousand rows,
11.38 MB (18.41 million rows/s., 655.75 MB/s.)

现在，ClickHouse 没有几乎执行全表扫描，而是更有效地执行了该查询。

对于 原始表 中的主索引，其中 UserID 是第一个键列，URL 是第二个键列，ClickHouse 使用 通用排除搜索 来执行该查询的索引标记，由于 UserID 和 URL 的基数相似地高，因此效率不高。

以 URL 作为主索引中的第一列，ClickHouse 现在正在对索引标记运行 二分搜索。ClickHouse 服务器日志文件中的相应跟踪日志证实了这一点。

...Executor): Key condition: (column 0 in ['http://public_search',
                                           'http://public_search'])
...Executor): Running binary search on index range for part all_1_9_2 (1083 marks)
...Executor): Found (LEFT) boundary mark: 644
...Executor): Found (RIGHT) boundary mark: 683
...Executor): Found continuous range in 19 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
              39/1083 marks by primary key, 39 marks to read from 1 ranges
...Executor): Reading approx. 319488 rows with 2 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.
Processed 8.02 million rows,
73.04 MB (340.26 million rows/s., 3.10 GB/s.)

服务器日志

...Executor): Key condition: (column 1 in [749927693, 749927693])
...Executor): Used generic exclusion search over index for part all_1_9_2
              with 1453 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
              980/1083 marks by primary key, 980 marks to read from 23 ranges
...Executor): Reading approx. 8028160 rows with 10 streams

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

选项 2：物化视图​

在我们的现有表上创建 物化视图。

CREATE MATERIALIZED VIEW mv_hits_URL_UserID
ENGINE = MergeTree()
PRIMARY KEY (URL, UserID)
ORDER BY (URL, UserID, EventTime)
POPULATE
AS SELECT * FROM hits_UserID_URL;

响应看起来像

Ok.

0 rows in set. Elapsed: 2.935 sec. Processed 8.87 million rows, 838.84 MB (3.02 million rows/s., 285.84 MB/s.)
注意
  • 我们在视图的主键中切换了键列的顺序（与我们的 原始表 相比）。
  • 物化视图由一个隐式创建的表支持，该表的行顺序和主索引基于给定的主键定义。
  • 隐式创建的表由 SHOW TABLES 查询列出，并且名称以 .inner 开头。
  • 也可以首先显式创建物化视图的后备表，然后视图可以通过 TO [db].[table] 子句 将目标指向该表。
  • 我们使用 POPULATE 关键字，以便立即使用源表 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.
Processed 335.87 thousand rows,
13.54 MB (12.91 million rows/s., 520.38 MB/s.)

因为实际上，物化视图支持的隐式创建的表（及其主索引）与我们显式创建的辅助表相同，所以查询的执行方式与显式创建的表相同。

ClickHouse 服务器日志文件中的相应跟踪日志证实，ClickHouse 正在对索引标记运行二分搜索。

...Executor): Key condition: (column 0 in ['http://public_search',
                                           'http://public_search'])
...Executor): Running binary search on index range ...
...
...Executor): Selected 4/4 parts by partition key, 4 parts by primary key,
              41/1083 marks by primary key, 41 marks to read from 4 ranges
...Executor): Reading approx. 335872 rows with 4 streams

选项 3：投影​

在我们的现有表上创建投影。

ALTER TABLE hits_UserID_URL
    ADD PROJECTION prj_url_userid
    (
        SELECT *
        ORDER BY (URL, UserID)
    );

并物化投影。

ALTER TABLE hits_UserID_URL
    MATERIALIZE PROJECTION prj_url_userid;
注意
  • 投影正在创建一个隐藏表，该表的行顺序和主索引基于投影的给定 ORDER BY 子句。
  • 隐藏表未在 SHOW TABLES 查询中列出。
  • 我们使用 MATERIALIZE 关键字，以便立即使用源表 hits_UserID_URL 中的所有 887 万行填充隐藏表。
  • 如果将新行插入到源表 hits_UserID_URL 中，则这些行也会自动插入到隐藏表中。
  • 查询始终（在语法上）以源表 hits_UserID_URL 为目标，但是如果隐藏表的行顺序和主索引允许更有效地执行查询，则将使用该隐藏表。
  • 请注意，即使 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.
Processed 319.49 thousand rows, 1
1.38 MB (11.05 million rows/s., 393.58 MB/s.)

因为实际上，投影创建的隐藏表（及其主索引）与我们显式创建的辅助表相同，所以查询的执行方式与显式创建的表相同。

ClickHouse 服务器日志文件中的相应跟踪日志证实，ClickHouse 正在对索引标记运行二分搜索。

...Executor): Key condition: (column 0 in ['http://public_search',
                                           'http://public_search'])
...Executor): Running binary search on index range for part prj_url_userid (1083 marks)
...Executor): ...
...Executor): Choose complete Normal projection prj_url_userid
...Executor): projection required columns: URL, UserID
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
              39/1083 marks by primary key, 39 marks to read from 1 ranges
...Executor): Reading approx. 319488 rows with 2 streams

总结​

我们的具有复合主键 (UserID, URL) 的表的主索引对于加速 筛选 UserID 的查询 非常有用。但是，尽管 URL 列是复合主键的一部分，但该索引对于加速 筛选 URL 的查询 没有提供显著的帮助。

反之亦然：我们的具有复合主键 (URL, UserID) 的表的主索引加速了 筛选 URL 的查询，但对于 筛选 UserID 的查询 没有提供太多支持。

由于主键列 UserID 和 URL 的基数相似地高，因此筛选第二个键列的查询 从第二个键列位于索引中获益不多。

因此，从主索引中删除第二个键列（从而减少索引的内存消耗）并使用多个主索引是有意义的。

但是，如果复合主键中的键列在基数方面存在很大差异，那么按基数升序排列主键列的顺序对于查询是有益的。

键列之间的基数差异越大，这些列在键中的顺序就越重要。我们将在下一节中演示这一点。

高效地排序键列​

在复合主键中，键列的顺序会显著影响：

  • 查询中筛选辅助键列的效率，以及
  • 表的数据文件的压缩率。

为了演示这一点，我们将使用我们的 Web 流量示例数据集 的一个版本，其中每行包含三列，指示互联网“用户”（UserID 列）对 URL（URL 列）的访问是否被标记为机器人流量（IsRobot 列）。

我们将使用包含所有上述三列的复合主键，该主键可用于加速典型的 Web 分析查询，这些查询计算：

  • 特定 URL 的流量中有多少（百分比）来自机器人，或者
  • 我们需要多大程度确信特定用户是（或不是）机器人（来自该用户的流量中有多少百分比被（或不被）认为是机器人流量）

我们使用此查询来计算我们想要用作复合主键中的键列的三个列的基数（请注意，我们正在使用 URL 表函数 临时查询 TSV 数据，而无需创建本地表）。在 clickhouse client 中运行此查询

SELECT
    formatReadableQuantity(uniq(URL)) AS cardinality_URL,
    formatReadableQuantity(uniq(UserID)) AS cardinality_UserID,
    formatReadableQuantity(uniq(IsRobot)) AS cardinality_IsRobot
FROM
(
    SELECT
        c11::UInt64 AS UserID,
        c15::String AS URL,
        c20::UInt8 AS IsRobot
    FROM url('https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz')
    WHERE URL != ''
)

响应是

┌─cardinality_URL─┬─cardinality_UserID─┬─cardinality_IsRobot─┐
│ 2.39 million    │ 119.08 thousand    │ 4.00                │
└─────────────────┴────────────────────┴─────────────────────┘

1 row in set. Elapsed: 118.334 sec. Processed 8.87 million rows, 15.88 GB (74.99 thousand rows/s., 134.21 MB/s.)

我们可以看到基数之间存在很大差异，尤其是在 URL 和 IsRobot 列之间，因此，这些列在复合主键中的顺序对于有效加速在这些列上进行过滤的查询以及实现表列数据文件的最佳压缩比都非常重要。

为了演示我们正在为我们的机器人流量分析数据创建两个表版本

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

创建表 hits_URL_UserID_IsRobot，其复合主键为 (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.
Processed 7.92 million rows,
31.67 MB (306.90 million rows/s., 1.23 GB/s.)

这是在表上执行的相同查询，在该表中，我们按基数升序排列了键列 (IsRobot, UserID, URL)

SELECT count(*)
FROM hits_IsRobot_UserID_URL
WHERE UserID = 112304

响应是

┌─count()─┐
│      73 │
└─────────┘

1 row in set. Elapsed: 0.003 sec.
Processed 20.32 thousand rows,
81.28 KB (6.61 million rows/s., 26.44 MB/s.)

我们可以看到，在按基数升序排列键列的表上，查询执行效率更高、速度更快。

原因是当通过二级键列选择 granules 时，通用排除搜索算法 最有效，其中前导键列的基数较低。我们在本指南的 前一节 中详细说明了这一点。

数据文件的最佳压缩比​

此查询比较了我们在上面创建的两个表之间 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）来检索特定行。