使用 MergeTree 加速分析

将开放表格式中的数据加载到 ClickHouse MergeTree 表中，以显著加快分析查询。

在上一节中，你已将 ClickHouse 连接到数据目录，并直接查询开放表格式中的数据。虽然直接查询原位数据很方便，但开放表格式并未针对支撑仪表盘和运营报表的低延迟、高并发工作负载进行优化。对于这类用例，将数据加载到 ClickHouse 的 MergeTree 引擎中，能带来显著更优的性能。

与直接读取开放表格式相比，MergeTree 具备以下优势：

• 稀疏主索引 - 按所选键对磁盘上的数据进行排序，使 ClickHouse 能在查询时跳过大范围无关的行。
• 增强的数据类型 - 原生支持 JSON、LowCardinality 和 Enum 等类型，从而实现更紧凑的存储和更快的处理。
• 跳过索引 和 全文索引 - 这类二级索引结构使 ClickHouse 能跳过不匹配查询过滤谓词的粒度，尤其适用于文本搜索工作负载。
• 借助自动合并整理实现快速插入 - ClickHouse 专为高吞吐量插入而设计，并会在后台自动合并数据分区片段，这与开放表格式中的合并整理类似。
• 针对并发读取进行了优化 - MergeTree 的列式存储布局结合多层缓存，可支持高并发的实时分析工作负载——而这并非开放表格式的设计目标。

本指南将介绍如何使用 INSERT INTO SELECT 将目录中的数据加载到 MergeTree 表中，以加快分析速度。

​

连接到目录

我们将使用与上一篇指南中相同的 Unity Catalog 连接，通过 Iceberg REST 端点接入：

SET allow_database_iceberg = 1;

CREATE DATABASE unity
ENGINE = DataLakeCatalog('https://<workspace-id>.cloud.databricks.com/api/2.1/unity-catalog/iceberg-rest')
SETTINGS catalog_type = 'rest', catalog_credential = '<client-id>:<client-secret>', warehouse = 'workspace',
oauth_server_uri = 'https://<workspace-id>.cloud.databricks.com/oidc/v1/token', auth_scope = 'all-apis,sql';

​

查看表

SHOW TABLES FROM unity

┌─name───────────────────────────────────────────────┐
│ unity.logs                                         │
│ unity.single_day_log                               │
└────────────────────────────────────────────────────┘

​

查看 schema

SHOW CREATE TABLE unity.`icebench.single_day_log`

CREATE TABLE unity.`icebench.single_day_log`
(
    `pull_request_number` Nullable(Int64),
    `commit_sha` Nullable(String),
    `check_start_time` Nullable(DateTime64(6, 'UTC')),
    `check_name` Nullable(String),
    `instance_type` Nullable(String),
    `instance_id` Nullable(String),
    `event_date` Nullable(Date32),
    `event_time` Nullable(DateTime64(6, 'UTC')),
    `event_time_microseconds` Nullable(DateTime64(6, 'UTC')),
    `thread_name` Nullable(String),
    `thread_id` Nullable(Decimal(20, 0)),
    `level` Nullable(String),
    `query_id` Nullable(String),
    `logger_name` Nullable(String),
    `message` Nullable(String),
    `revision` Nullable(Int64),
    `source_file` Nullable(String),
    `source_line` Nullable(Decimal(20, 0)),
    `message_format_string` Nullable(String)
)
ENGINE = Iceberg('s3://...')

此表包含约 2.83 亿条来自 ClickHouse CI 测试运行的日志记录，是一个适合探索分析性能的真实数据集。

SELECT count()
FROM unity.`icebench.single_day_log`

┌───count()─┐
│ 282634391 │ -- 2.8263 亿
└───────────┘

1 row in set. Elapsed: 1.265 sec.

​

查询数据湖表中的数据

运行以下查询：按线程名称和实例类型过滤日志，在消息文本中搜索错误，并按 logger 对结果进行分组：

SELECT
    logger_name,
    count() AS c
FROM icebench.`icebench.single_day_log`
WHERE (thread_name = 'TCPHandler')
    AND (instance_type = 'm6i.4xlarge')
    AND hasToken(message, 'error')
GROUP BY logger_name
ORDER BY c DESC
LIMIT 5

┌─logger_name──────────────┬────c─┐
│ executeQuery             │ 6907 │
│ TCPHandler               │ 4145 │
│ TCP-Session              │  790 │
│ PostgreSQLConnectionPool │  530 │
│ ContextAccess (default)  │  392 │
└──────────────────────────┴──────┘

5 rows in set. Elapsed: 8.921 sec. Processed 282.63 million rows, 5.42 GB (31.68 million rows/s., 607.26 MB/s.)
Peak memory usage: 4.35 GiB.

该查询耗时接近 9 秒，因为 ClickHouse 必须对对象存储中的所有 Parquet 文件执行全表扫描。虽然可以通过分区提升性能，但像 logger_name 这样的列基数可能过高，难以通过分区获得理想效果。我们也没有诸如文本索引之类的索引来进一步缩小扫描范围。这正是 MergeTree 的优势所在。

​

将数据导入 MergeTree

​

创建优化后的表

我们创建一个 MergeTree 表，并对 schema 做了一些优化。注意，它与 Iceberg schema 有以下几个关键区别：

• 不使用 Nullable 包装 - 去掉 Nullable 可提升存储效率和查询性能。
• 在 level、instance_type、thread_name 和 check_name 列上使用 LowCardinality(String) - 对不同值较少的列进行字典编码，以获得更好的压缩效果和更快的过滤速度。
• 在 message 列上创建一个全文索引 - 可加速基于标记的文本搜索，例如 hasToken(message, 'error')。
• ORDER BY 键为 (instance_type, thread_name, toStartOfMinute(event_time)) - 让磁盘上的数据布局与常见过滤模式对齐，从而使稀疏主索引能够跳过无关的粒度。

SET enable_full_text_index = 1;

CREATE TABLE single_day_log
(
    `pull_request_number` Int64,
    `commit_sha` String,
    `check_start_time` DateTime64(6, 'UTC'),
    `check_name` LowCardinality(String),
    `instance_type` LowCardinality(String),
    `instance_id` String,
    `event_date` Date32,
    `event_time` DateTime64(6, 'UTC'),
    `event_time_microseconds` DateTime64(6, 'UTC'),
    `thread_name` LowCardinality(String),
    `thread_id` Decimal(20, 0),
    `level` LowCardinality(String),
    `query_id` String,
    `logger_name` String,
    `message` String,
    `revision` Int64,
    `source_file` String,
    `source_line` Decimal(20, 0),
    `message_format_string` String,
    INDEX text_idx(message) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY (instance_type, thread_name, toStartOfMinute(event_time))

​

从目录中插入数据

使用 INSERT INTO SELECT 将数据湖表中的约 3 亿行数据加载到我们的 ClickHouse 表中：

INSERT INTO single_day_log SELECT * FROM icebench.`icebench.single_day_log`

282634391 rows in set. Elapsed: 237.680 sec. Processed 282.63 million rows, 5.42 GB (1.19 million rows/s., 22.79 MB/s.)
Peak memory usage: 18.62 GiB.

​

再次执行查询

如果现在对 MergeTree 表执行相同的查询，就会发现性能有了显著提升：

SELECT
    logger_name,
    count() AS c
FROM single_day_log
WHERE (thread_name = 'TCPHandler')
    AND (instance_type = 'm6i.4xlarge')
    AND hasToken(message, 'error')
GROUP BY logger_name
ORDER BY c DESC
LIMIT 5

┌─logger_name──────────────┬────c─┐
│ executeQuery             │ 6907 │
│ TCPHandler               │ 4145 │
│ TCP-Session              │  790 │
│ PostgreSQLConnectionPool │  530 │
│ ContextAccess (default)  │  392 │
└──────────────────────────┴──────┘

5 rows in set. Elapsed: 0.220 sec. Processed 13.84 million rows, 2.85 GB (62.97 million rows/s., 12.94 GB/s.)
Peak memory usage: 1.12 GiB.

现在，同一查询只需 0.22 秒即可完成——速度提升了 约 40 倍。这一改进主要来自两项关键优化：

• 稀疏主索引 - ORDER BY (instance_type, thread_name, ...) 键意味着 ClickHouse 可以直接跳到与 instance_type = 'm6i.4xlarge' 和 `thread_name = ‘TCPHandler’“ 匹配的粒度，将处理的行数从 2.83 亿减少到仅 1400 万。
• 全文索引 - message 列上的 text_idx 索引使 hasToken(message, 'error') 可以通过索引命中，而不必扫描每一条消息字符串，从而进一步减少 ClickHouse 需要读取的数据量。

最终，这条查询已经能够轻松支撑实时仪表板——无论是规模还是延迟表现，都不是在对象存储中查询 Parquet 文件所能比拟的。