跳至主要内容

ClickHouse 中主键的实用介绍

介绍​

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

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

注意

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

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

数据集​

在本指南中，我们将使用匿名网络流量样本数据集。

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

有了这三列，我们就可以提出一些典型的网络分析查询，例如

  • “特定用户的 10 个点击次数最多的 URL 是哪些？”
  • “哪些用户最频繁地点击了特定 URL？”
  • “用户点击特定 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;
注意

通常，在将数据加载到表后，无需立即优化表。为什么此示例需要这样做将变得很明显。

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

考虑到与 B-Tree 索引相关的挑战，ClickHouse 中的表引擎采用了不同的方法。ClickHouse MergeTree 引擎系列 旨在处理海量数据。这些表旨在每秒接收数百万行插入，并存储非常庞大的数据（数百 PB）。数据快速写入表 部分，并应用规则在后台合并部分。在 ClickHouse 中，每个部分都有自己的主键索引。当部分合并时，合并后的部分的主键索引也会合并。在 ClickHouse 旨在处理的超大规模下，磁盘和内存效率至关重要。因此，与其索引每一行，不如为部分创建主键索引，该索引每组行（称为“颗粒”）有一个索引条目（称为“标记”）——这种技术称为**稀疏索引**。

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

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

具有主键的表​

创建一个具有主键的表，该主键包含 UserID 和 URL 键列

CREATE TABLE hits_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime
)
ENGINE = MergeTree
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 个条目（称为“标记”，索引尺寸为 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` 列的值。

注意

  • 由于主键定义了磁盘上行的字典顺序，因此一个表只能有一个主键。

  • 为了与 ClickHouse 内部行编号方案一致，我们从 0 开始对行进行编号，该方案也用于记录消息。

数据被组织成数据块以进行并行数据处理​

为了进行数据处理，表的列值在逻辑上被分成数据块。数据块是流入 ClickHouse 进行数据处理的最小不可分割的数据集。这意味着，ClickHouse 始终不是读取单个行，而是始终（以流式方式和并行方式）读取一组完整的行（数据块）。

注意

列值不会物理存储在数据块中：数据块只是为了查询处理而对列值进行逻辑组织。

下图显示了我们表中的 887 万行（列值）是如何组织成 1083 个数据块的，这是由于表的 DDL 语句包含设置 `index_granularity`（设置为其默认值 8192）。

第一个（基于磁盘上的物理顺序）8192 行（它们的列值）在逻辑上属于数据块 0，然后接下来的 8192 行（它们的列值）属于数据块 1，依此类推。

注意

  • 最后一个数据块（数据块 1082）“包含”不到 8192 行。

  • 我们在本指南开头“DDL 语句详细信息”中提到，我们禁用了自适应索引粒度（为了简化本指南中的讨论，以及使图表和结果可重现）。

    因此，我们示例表的所有数据块（最后一个除外）都具有相同的尺寸。

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

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

  • 为了与 ClickHouse 内部编号方案一致，我们从 0 开始对数据块进行编号，该方案也用于记录消息。

主键索引每个数据块有一个条目​

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

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

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

我们的表总共有 1083 个条目，有 887 万行和 1083 个数据块

注意

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

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

检查主键内容

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

为此，我们首先需要将主键文件复制到运行的集群中某个节点的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( )
    FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String'); `

    返回 `1083`

  • 获取前两个索引标记
    • ` SELECT UserID, URL
    FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')
    LIMIT 0, 2; `

    返回
    ` 240923, http://showtopics.html%3...
    4073710, http://mk.ru&pos=3_0 `

  • 获取最后一个索引标记
    • ` SELECT UserID, URL
    FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')
    LIMIT 1082, 1; `

    返回
    ` 4292714039 │ http://sosyal-mansetleri... `

这与我们示例表主键内容的图表完全一致

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

  • UserID 索引标记
    主键索引中存储的 `UserID` 值按升序排序。
    上面图表中的“标记 1”因此表明，数据块 1 中所有表行的 `UserID` 值，以及所有后续数据块中的 `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 客户端的输出现在显示，ClickHouse 只流入了 8.19 千行数据，而不是执行全表扫描。

如果启用了跟踪日志记录，则 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，它们存储表的用户 ID、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 只需要数据块 176 在 UserID 数据文件（UserID.bin）中的两个物理位置偏移量，以及数据块 176 在 URL 数据文件（URL.bin）中的两个物理位置偏移量。

标记文件提供的间接寻址方式避免在主索引中直接存储所有三列的所有 1083 个数据块的物理位置的条目：从而避免在主内存中拥有不必要的（可能未使用的）数据。

下图和下面的文本说明了对于我们的示例查询，ClickHouse 如何定位 UserID.bin 数据文件中的数据块 176。

我们在本指南的前面部分讨论过，ClickHouse 选择了主索引标记 176，因此数据块 176 可能包含与我们的查询匹配的行。

ClickHouse 现在使用从索引中选择的标记号（176）在 UserID.mrk 标记文件中进行位置数组查找，以获取定位数据块 176 的两个偏移量。

如所示，第一个偏移量定位了 UserID.bin 数据文件中包含数据块 176 压缩版本的压缩文件块。

一旦定位的文件块被解压缩到主内存中，就可以使用来自标记文件的第二个偏移量在未压缩的数据中定位数据块 176。

ClickHouse 需要定位（并流入所有来自）数据块 176 来自 UserID.bin 数据文件和 URL.bin 数据文件，以执行我们的示例查询（互联网用户 UserID 为 749.927.693 的前 10 个点击最多的 URL）。

上图显示了 ClickHouse 如何定位 UserID.bin 数据文件的数据块。

同时，ClickHouse 也对 URL.bin 数据文件的数据块 176 执行相同的操作。这两个数据块被对齐并流入 ClickHouse 引擎进行进一步处理，即聚合和计算每个组的 URL 值（对于所有 UserID 为 749.927.693 的行），最后以降序计数顺序输出 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
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
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 上进行过滤的速度进行了优化。

类似于使用我们 原始表格 时该查询的 性能低下，我们的 示例查询在 UserID 上进行过滤 时，在新添加的表格中也不会运行得很好，因为 UserID 现在是该表格主键中的第二个主键列，因此 ClickHouse 将使用通用排除搜索来选择数据块，而这对于 UserID 和 URL 的基数相近的情况来说 效率并不高。打开详细信息框查看具体情况。

在 UserID 上进行过滤的查询现在性能低下

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

现在我们有两个表格。一个针对加快在 UserID 上进行过滤的查询的速度进行了优化，另一个针对加快在 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
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
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 的基数非常接近，因此在第二主键列上进行过滤的查询 不会从第二主键列出现在索引中获得很大的好处。

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

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

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

有效地排列主键列​

在复合主键中，主键列的顺序会显著影响以下两方面：

  • 查询中对第二主键列进行过滤的效率，以及
  • 表格数据文件的压缩率。

为了演示这一点，我们将使用我们 网络流量示例数据集 的一个版本，其中每一行包含三个列，分别表示互联网“用户”（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
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.)

接下来，创建具有复合主键 (IsRobot, UserID, URL) 的表格 hits_IsRobot_UserID_URL。

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

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

原因是 通用排除搜索算法 在 数据块 通过辅助键列进行选择时最有效，前提是前一个键列的基数较低。我们在本指南的 上一节 中详细说明了这一点。

数据文件的最佳压缩率​

此查询比较了我们在上面创建的两个表格中 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 万行），但复合主键中键列的顺序对表格 压缩 的数据在表格的 列数据文件 中占用的磁盘空间有很大影响。

  • 在复合主键为 (URL, UserID, IsRobot) 的 hits_URL_UserID_IsRobot 表格中，我们按基数降序对键列进行排序，UserID.bin 数据文件占用 11.24 MiB 的磁盘空间。
  • 在复合主键为 (IsRobot, UserID, URL) 的 hits_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 值决定最终顺序。

因为仅在细微变化的情况下不同的数据将获得相同的指纹值，所以现在相似数据在内容列中彼此靠近存储在磁盘上。这对内容列的压缩率非常有利，因为压缩算法通常会从数据局部性中获益（数据越相似，压缩率越高）。

折衷方案是需要使用两个字段（fingerprint 和 hash）来检索特定行，以便最大限度地利用由复合 PRIMARY KEY (fingerprint, hash) 生成的主键。