Clickhouse 主键索引

ClickHouse的MergeTree引擎系列经过专门设计和优化，能够处理海量数据。这类表可支持每秒插入数百万行数据，并能存储极大规模的数据（达数百PB级别）。数据以分块（part）的形式快速写入表中，并通过后台机制按规则合并这些数据块。在ClickHouse中，每个数据块都有其独立的主索引。当数据块被合并时，其对应的主索引也会随之合并。鉴于ClickHouse的设计目标是应对超大规模数据场景，因此高效利用磁盘和内存资源至关重要。为此，ClickHouse并未对每一行数据都建立索引，而是采用稀疏索引（sparse index）技术：每个数据块的主索引仅针对一组行（称为“粒度”granule）创建一个索引项（称为“标记”mark）。

示例1：

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，以禁用主索引的压缩。这将允许我们在稍后选择性地检查其内容。

插入数据

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

优化表

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 客户端输出显示：

表的数据以宽格式存储在磁盘上的特定目录中，意味着每一列在该目录中将有一个数据文件（和一个标记文件）。
表具有 8.87 百万行。
所有行的未压缩数据总大小为 733.28 MB。
所有行在磁盘上的压缩大小为 206.94 MB。
表具有 1083 个条目的主索引（称为“标记”），索引的大小为 96.93 KB。
总体而言，表的数据和标记文件以及主索引文件在磁盘上一起占用 207.07 MB。

1、数据按主键列的顺序存储在磁盘上

表的每个列有一个单独的数据文件（*.bin），该列的所有值都是以压缩格式存储的，并且8.87 百万行在磁盘上以主键列（和额外排序键列）的字典顺序存储，首先按 UserID，然后按 URL，最后按 EventTime。

2、数据被组织为 granules 以进行并行数据处理

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

3、主索引每个 granule 有一个条目

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

示例2：

以下查询计算用户 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.

Processed 8.19 thousand rows,
740.18 KB (1.53 million rows/s., 138.59 MB/s.)

4、主索引用于选择 granule （第一阶段粒度选择）

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

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

分析一下：

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 的行。

4.1、选择 granule

示例 2 的筛选条件是 UserID = 749927693。通过对如下的索引的 1083 个 UserID 标记进行二分查找，识别出标记 176。因此，其对应的 granule 176 可能包含 UserID 列值为 749.927.693 的行。

5、标记文件用于定位 granules（第二阶段数据读取）

在 ClickHouse 中，所有 granule 的物理位置都存储在标记文件中。类似于数据文件，每表列都有一个标记文件。主索引是一个扁平的无压缩数组文件（primary.idx），其中包含从 0 开始编号的索引标记。类似地，标记文件也是一个扁平的无压缩数组文件（*.mrk），其中包含从 0 开始编号的标记。一旦 ClickHouse 确定并选择了可能包含查询匹配行的 granule 的索引标记，就可以在标记文件中执行位置数组查找，以获得该 granule 的物理位置。

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

第一个偏移量（上图中的 'block_offset'）定位了包含所选 granule 压缩版本的块，该块位于压缩列数据文件中。这个压缩块可能包含几个压缩的 granules。定位到的压缩文件块在读取时被解压缩到主内存中。
第二个偏移量（上图中的 'granule_offset'）来自标记文件，提供了未压缩块数据中 granule 的位置。

① 根据 UserID.mrk 中的 block_offset 找到 UserID.bin 中某个区块文件
② 解压区块文件，根据 UserID.mrk 中的 granule_offset 找到 granule 176。
③ 读取 granule 176 中的 8192 行的数据

与此同时，ClickHouse 正在对 URL.bin 数据文件的第 176 号数据块（granule）执行相同的操作。这两个对应的数据块被对齐后，流式传入 ClickHouse 引擎进行后续处理，即针对 UserID 为 749,927,693 的所有行，按组聚合并统计各个 URL 的出现次数，最后输出按计数降序排列的前 10 个最大的 URL 组。

为什么使用标记文件？
主索引文件需要加载到主内存中。对于我们的示例查询，ClickHouse 仅需要在 UserID 数据文件 (UserID.bin) 中 granule 176 的两个物理位置偏移量，以及在 URL 数据文件 (URL.bin) 中 granule 176 的两个物理位置偏移量。通过标记文件提供的间接性避免将所有 1083 个 granules 的物理位置条目直接存储在主索引中：从而避免在主内存中存储不必要（可能未使用）的数据。

6、使用多个主索引

6.1、通用排除搜索算法

假设：

一个查询，用于查找 URL 值等于 "W3" 的行；
一个抽象化的 hits 表，其中 UserID 和 URL 字段使用简化的值；
使用相同的复合主键（UserID, URL）作为索引。这意味着数据行首先按 UserID 排序；对于 UserID 相同的行，则再按 URL 排序；
每个数据块（granule）的大小为 2，即每个数据块包含两行数据。

① 前驱键列具有低（或较低的）基数时，效果不错。

假设 UserID 的基数较低。在这种情况下，可能同一个 UserID 值分布在多个表行和 granules 以及索引标记中。对于具有相同 UserID 的索引标记，URL 值按升序排序（因为表行首先按 UserID 排序，然后按 URL 排序）。

② 前驱键列具有高（或较高的）基数时，效果不佳。

当 UserID 具有较高基数时，同一个 UserID 值分布在多个表行和 granules 的可能性较小。这意味着索引标记的 URL 值不是单调增加的。

6.2、创建附加主索引

如果我们希望显著加快两个示例查询 - 一个是过滤特定 UserID 的行，另一个是过滤特定 URL 的行 - 那么我们需要使用多个主索引，使用以下三种选项中的一种：

创建一个第二张表，其主键不同。
在我们现有的表上创建一个物化视图。
向我们现有的表添加一个投影。

物化视图：

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;

备注：

我们在视图的主键中（与我们原始表相比）切换了键列的顺序

物化视图由一个隐式创建的表支持，其行顺序和主索引基于给定的主键定义

隐式创建的表可以通过 SHOW TABLES 查询列出，并且其名称以 .inner 开头

也可以首先显式创建物化视图的支持表，然后视图可以通过 TO [db].[table] 子句定位该表

我们使用 POPULATE 关键字以便立即用来自源表 hits_UserID_URL 的所有 8.87 百万行填充隐式创建的表

如果向源表 hits_UserID_URL 插入新行，则也会自动插入到隐式创建的表中

实际上，隐式创建的表具有与我们显式创建的次级表相同的行顺序和主索引：

投影：

-- 在我们现有的表上创建投影
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 的所有 8.87 百万行填充隐藏表

如果向源表 hits_UserID_URL 插入新行，则也会自动插入到隐藏表中

查询总是（在语法上）针对源表 hits_UserID_URL，但如果隐藏表的行顺序和主索引允许更有效的查询执行，则会使用该隐藏表

请注意，投影不会使使用 ORDER BY 的查询更有效，即使 ORDER BY 与投影的 ORDER BY 声明匹配。见 https://github.com/ClickHouse/ClickHouse/issues/47333

多个主索引的使用总结

我们带有复合主键（UserID，URL）的表的主索引在加速过滤 UserID 的查询时非常有用。但是，该索引在加速过滤 URL 的查询时并没有提供显著的帮助，尽管 URL 列是复合主键的一部分。反之亦然：我们带有复合主键（URL，UserID）的表在加速过滤 URL 的查询时表现良好，但在过滤 UserID 的查询中没有提供太多支持。
由于主键列 UserID 和 URL 的基数相似且均很高，过滤第二个键列的查询并不太受益于第二个键列在索引中的存在。因此，移除主索引中的第二个键列（从而降低索引的内存消耗）并使用多个主索引是合理的。
如果复合主键中的键列在基数上存在较大差异，那么按照基数升序排列主键列对于查询是有益的。

7、高效排序键列

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

查询中过滤二级键列的效率。
表的数据文件的压缩比。

示例3：

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

查询我们希望用作复合主键键列的三个列的基数：

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.)

可以观察到，3个列的基数之间的差异很大。为了测试哪种主键排序效果更好，我们创建两个表版本：

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

-- 表1 hits_URL_UserID_IsRobot
CREATE TABLE hits_URL_UserID_IsRobot
(
    `UserID` UInt32,
    `URL` String,
    `IsRobot` UInt8
)
ENGINE = MergeTree
PRIMARY KEY (URL, UserID, IsRobot);

-- 填充数据
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 != '';

-- 表2 hits_IsRobot_UserID_URL
CREATE TABLE hits_IsRobot_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `IsRobot` UInt8
)
ENGINE = MergeTree
PRIMARY KEY (IsRobot, UserID, URL);

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

① 测试查询效率：

表1 hits_URL_UserID_IsRobot (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.)

表2 hits_IsRobot_UserID_URL (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.

在按基数升序排列键列的表中，UserID 列的压缩比显著更高。原因在于在一列中，相似的数据相互靠近，例如通过排序，那么该数据将被更好地压缩。一般而言，压缩算法受益于数据的运行长度（它看到的数据越多，压缩效果越好）和局部性（数据越相似，压缩比越好）。

高效排序键列总结
对于查询中二级键列的高效过滤和表的列数据文件的压缩比，将主键中的列按基数升序排列是有益的。

posted on 2025-08-19 16:24 Lemo_wd 阅读(44) 评论(0) 收藏举报

刷新页面返回顶部