ByteHouse CDW 查询优化最佳实践--ByteHouse云数仓版-火山引擎

文档中心

ByteHouse云数仓版

查询优化

ByteHouse CDW 查询优化最佳实践

1. 前言

1.1. 目的与读者对象

本指南旨在为 ByteHouse CDW（Cloud Data Warehouse）的用户，包括数据分析师、数据工程师和数据库管理员（DBA），提供一个系统性的 SQL 性能优化框架和实践方法。无论您是刚刚接触 ByteHouse，还是希望进一步提升现有查询的性能，本指南都将为您提供清晰、可行的指导。
我们的目标是：

建立性能意识：帮助您理解 SQL 查询性能的核心要素和 ByteHouse 的工作原理。
提供系统方法：提供从性能诊断、SQL 编写、数据模型设计到监控管理的全链路优化方法论。
赋能实践：通过丰富的代码示例、场景化分析和真实案例，让您能够解决工作中遇到的实际性能问题。

1.2. ByteHouse CDW 查询优化核心思想

减少数据扫描（Reduce Data Scan）：尽可能读取最少的数据。这是性能优化的第一黄金法则。通过有效利用分区键、排序键和索引，从源头上避免全表扫描。
提升计算效率（Increase Computation Efficiency）：在分布式计算框架下，优化计算的并行度和效率。例如，选择最优的 JOIN 算法（如 Broadcast Join）、避免数据倾斜、高效使用聚合函数等。
合理利用资源（Utilize Resources Wisely）：通过配置合适的计算组（Virtual Warehouse，VW）规格、管理并发查询和优化资源队列，确保关键任务获得充足的计算资源。
拥抱自动化（Embrace Automation）：充分利用 ByteHouse 提供的自动化工具，如自动统计信息收集，来简化优化工作并保证查询计划的准确性。

1.3. 如何使用本指南

本指南按照从基础到进阶，再到实践的逻辑顺序组织。

2. ByteHouse CDW 查询优化基础和3 SQL 优化核心技术：讲解优化的基础知识和核心技术，适合所有用户阅读，以建立坚实的理论基础。
4 场景化调优实践：聚焦于具体的业务场景，提供针对性的优化策略和案例，帮助您解决特定问题。
5 性能监控与管理：介绍性能的监控与管理，适合需要进行系统级性能治理的用户。

建议您从头至尾阅读，以建立完整的知识体系。对于有经验的用户，可以直接跳转到感兴趣的章节。

2. ByteHouse CDW 查询优化基础

2.1 查询优化快速入门

理解查询生命周期

一个 SQL 查询在 ByteHouse 中的旅程通常包括以下几个阶段：

解析（Parsing）：客户端提交 SQL 文本，ByteHouse 的解析器会进行词法、语法分析，生成抽象语法树（AST）。
优化（Optimization）：查询优化器是性能优化的核心。它会根据统计信息、系统参数和预设规则，生成多个候选的执行计划，并从中选择一个成本最低的计划。
执行（Execution）：执行引擎按照选定的执行计划，在各个计算节点上执行具体的计算任务（如数据扫描、过滤、聚合、JOIN 等）。
返回（Return）：将最终的计算结果返回给客户端。

性能瓶颈可能出现在任何一个环节，但绝大多数问题都集中在执行阶段，而优化的关键则在于影响优化器决策。

识别性能瓶颈：关键指标与工具

在开始优化前，首先要定位问题。以下是关键的性能衡量指标：

查询延迟（Query Latency）：查询从提交到返回结果的总耗时。
CPU 时间（CPU Time）：所有计算节点为该查询消耗的 CPU 总时间。
扫描数据量（Data Scanned）：查询读取的原始数据大小。
峰值内存（Peak Memory Usage）：查询执行过程中消耗的最大内存。

核心工具：

EXPLAIN：查看查询的逻辑和物理执行计划，是 SQL 优化的“第一神器”。
bh_system.query_log：记录了所有已执行查询的详细日志，包括上述所有性能指标，是事后分析性能问题的必备工具，启用操作详情请参考详细日志（query_log）。

优化实例：简单的慢查询优化之旅

假设我们有一张销售记录表 sales，包含分区键 dt (日期)。
优化前：一个常见的慢查询是忘记指定分区，导致全表扫描。

性能表现：扫描数据量 10TB，耗时 5 分钟。
- 优化后：通过添加分区键过滤条件，可以极大地减少扫描的数据量。
性能表现：扫描数据量 10GB，耗时 3 秒。这个简单的例子体现了优化的核心思想：尽可能只读取需要的数据。

优化前 SQL：

-- 意图：查询昨天的销售总额
-- 问题：没有使用 WHERE dt = ... 来限制分区，导致全表扫描
SELECT sum(amount)
FROM sales;

优化后 SQL：

-- 优化：精确指定分区
SELECT sum(amount)
FROM sales
WHERE dt = '2025-08-24';

2.2 理解 EXPLAIN 执行计划

EXPLAIN 是理解查询如何被执行的窗口。通过它，我们可以看到优化器为我们的 SQL 选择了怎样的执行路径。执行计划详情请参见执行计划 EXPLAIN。

2.2.1. 如何阅读和分析 EXPLAIN ANALYZE

在 ByteHouse 中，可以直接在 SQL 前加上 EXPLAIN ANALYZE（后面可以加 Distributed 和 Pipeline）关键字来获取执行计划。

EXPLAIN ANALYZE SELECT ...;

输出结果是一个树状结构，从下往上读。每一行代表一个算子（Operator），表示一个具体的计算步骤。缩进代表了算子之间的父子关系。
分析 EXPLAIN ANALYZE 的关键在于：

关注底层算子：特别是 TableScan，检查它扫描的分区数（partitions）和数据行数（rows），确认是否扫描了不必要的数据。
检查 Filter 算子：确认 WHERE 条件是否被有效下推（pushed down）到了存储层，从而在扫描时就过滤掉数据。
分析 Join 类型：确认是否使用了最高效的 Join 算法，例如是否小表作为右表。
寻找高成本操作：注意那些通常资源消耗较大的算子，如 Shuffle、Sorting 等。

2.2.2. 关键算子（Operator）解读

TableScan, Filter, Project
- TableScan：表示从表中读取数据。关键信息是它读取的分区和行数。如果这里的数据量远超预期，通常是分区裁剪或索引未生效。
- Filter：执行 WHERE 子句中的过滤条件。理想情况下，Filter 应该尽可能靠近 TableScan。
- Project：对应 SELECT 列表，表示选择哪些列输出。
Join 类型（Broadcast, Shuffle）
- Broadcast Join: 当一张表（通常是右表）较小时，ByteHouse 会将其全量数据广播到参与 Join 的所有节点上。这避免了左表数据的重分布（Shuffle），性能极高。
- Shuffle Join: 当两张表都很大时，必须根据 Join Key 对两张表的数据进行重新哈希分布（Shuffle），以确保相同的 Join Key 分布到同一个节点上。Shuffle 会带来大量的网络 IO，是主要的性能开销之一。
- Colocation Join：JOIN key 和左右表的 bucket key 一致，可以使用Colocation Join。
Aggregation
- Aggregation: 执行 GROUP BY 聚合操作。ByteHouse 会进行两阶段聚合：先在各个节点上进行局部聚合（Partial Aggregation），然后将局部结果 Shuffle 到少数节点上进行最终聚合（Final Aggregation），以提升并行度。
Runtime Filter
- 运行时过滤器（Runtime Filter）是 ByteHouse 在 Join 优化中的一项关键技术。在 Join 的构建阶段（Build side，通常是小表），它会根据 Join Key 生成一个过滤器，然后将这个过滤器下推到事实表（Probe side，通常是大表）的扫描阶段。这样可以在扫描大表时就提前过滤掉大量不可能匹配 Join 条件的数据，极大地减少了 Join 过程中的数据传输和计算量。在 EXPLAIN 中看到 Runtime Filter 通常意味着 Join 性能会得到显著提升，依赖统计信息，可以适当调大 wait_runtime_filter_timeout 参数值。

2.2.3. 通过 EXPLAIN 发现常见问题

全表扫描：TableScan 读取了所有分区。
谓词下推失败：Filter 算子离 TableScan 很远，中间隔着 Join 或其他复杂操作。
不合理的 Join 类型：大表和小表的 Join 顺序不对，如果统计信息不足，可以通过 SQL hint 调整。
数据倾斜：某个 Aggregation 或 Join 算子的实例耗时远超其他实例（需要通过 query_log 分析）。

3. SQL 优化核心技术

3.1 数据模型与 DDL 优化

数据模型是数据仓库的基石，良好的设计是高性能的起点。在 ByteHouse 中，表结构的设计直接决定了数据的物理存储和查询时的数据扫描效率。

表结构设计最佳实践

合理选择分区键（Partition Key）
分区键是 ByteHouse 中最重要、最有效的剪枝工具。通过在 WHERE 子句中指定分区键的过滤条件，ByteHouse 可以仅扫描相关的分区，跳过大量无关数据。
选择原则：
1. 必须是查询过滤的常用字段：选择那些在绝大多数查询中都会用来过滤数据的列，最典型的就是日期或时间字段（如 dt, event_date）。
2. 基数适中（Cardinality）：分区的数量不宜过多或过少。分区过多（如按秒分区）会增加元数据管理的开销；分区过少（如按年分区）则失去了分区裁剪的意义。通常建议按月、周或者按天分区，总分区数建议在10 万以下。
3. 分区粒度均匀：确保每个分区的数据量大致均衡，避免数据倾斜。
  示例：
```
-- 创建一个按天分区的事件表
CREATE TABLE events (
    event_id String,
    user_id UInt64,
    event_time DateTime,
    -- ... 其他列
) ENGINE = CNCHMergeTree
PARTITION BY toYYYYMMDD(event_time) -- 使用日期作为分区键
ORDER BY (user_id, event_time);
```
排序键（Order Key）与数据分布
排序键决定了在每个分区内部，数据是如何物理排序存储的。这种有序性带来了以下好处：
1. 加速过滤：对于排序列的范围查询，ByteHouse 可以利用其有序性快速定位数据块，类似索引效果。
2. 提升压缩率：相同的值聚集在一起，可以获得更高的压缩比。
  选择原则：
3. 高频过滤列在前：将最常用于 WHERE 条件的列放在排序键的最前面。
4. 低基数列在前：将基数较低的列（如 country, gender）放在基数较高的列（如 user_id）前面，可以提升压缩效果。
数据类型选择
选择最紧凑、最合适的数据类型，不仅可以节省存储空间，还能提升计算效率。
- 数值类型：用 UInt 系列代替 Int（如果无负数），并选择能容纳数据范围的最小类型，如用 UInt8 或 UInt16 代替 UInt64。
- 字符串类型：对于基数有限的字符串，使用 LowCardinality(String) 或 Enum 类型，可以将其转换为字典编码存储，极大减少存储并加速过滤和聚合。
- 避免 ****NULL：尽量为列定义 NOT NULL 属性，Nullable 列会带来额外的存储和计算开销。

3.2 跳数索引（Data Skipping Index）

跳数索引是 ByteHouse 支持的一种查询加速功能。在处理大量数据时，查询性能可能会因为需要进行全列扫描来应用WHERE子句而下降。跳数索引通过让查询跳过那些确认不包含匹配值的数据块来解决这个问题。

创建跳数索引

创建索引时涉及四个主要参数：

索引名称（Index Name）：作为创建、删除或物化索引文件的标识符。
索引表达式（Index Expression）：用于计算和确定索引中存储值范围的公式，可包含列、基本运算符和函数。
类型（Type）：定义了用于跳过读取和评估每个索引块的计算方式。
粒度（Granularity）：定义每个索引块包含的颗粒数量。例如，若主表索引粒度为 8192 行，跳数索引粒度为 4，则每个索引块将包含 32,768 行（8192 x 4）。

语法示例

/*
索引名称: key_i_idx
索引表达式: key_i
类型: minmax
粒度: 1
*/
INDEX key_i_idx key_i TYPE minmax GRANULARITY 1

支持的数据类型

1. 最大最小值（minmax）

描述：这是一种高效的索引方法，它为每个数据块保留索引表达式的最小值和最大值。对于松散排序的列最有效，通常是查询执行中最具成本效益的索引类型。
限制: 仅适用于标量或元组表达式，不能与生成数组或映射数据类型的表达式一起使用。

2. 数据集（Set）

描述：这种轻量级索引包含一个参数 max_size，用于设置每个块的值集大小。当 max_size 为 0 时，值集大小不受限制。它适用于每组颗粒中值聚集在一起的列。
成本：索引的成本和性能取决于块内的基数。如果唯一值过多，评估成本会变高；如果值集由于超过 max_size 而为空，则索引不会被应用。

3. 布隆过滤器类型（Bloom filter types）

布隆过滤器是一种紧凑的数据结构，用于判断集合成员是否存在，可能存在极小的误报率。在跳数索引中，误报仅会导致读取一些不必要的块。

tokenbf_v1：专为增强的布隆过滤器功能定制，适用于String和FixedString类型。它将输入表达式按非字母数字字符分割成字符序列。此索引可用于LIKE、EQUALS、in等操作，适合在非结构化日志中搜索少量特定值。其参数包括：
- 过滤器大小（字节）
- 哈希函数数量
- 哈希函数种子
ngrambf_v1：功能与 tokenbf_v1 类似，但增加了一个额外参数，即要索引的 ngram 的大小。它对于没有单词间断的语言（如中文）的文本搜索很有用。
bloom_filter([false_positive])：通用布隆过滤器，可通过 false_positive 参数（范围 0-1，默认 0.025）指定假阳性概率。支持多种数据类型，包括 Int*, UInt*, Float*, String, Array 等。

分析索引效果

EXPLAIN ANALYZE indexes = 1 SQL

效果示例如下：

3.3 使用举例

1. 载入数据集到 ByteHouse

首先，创建一个数据库和表用于加载样本数据。

DROP DATABASE IF EXISTS test_si;
CREATE DATABASE test_si;

-- 创建表以加载 numbers_source.csv 数据集
CREATE TABLE test_si.numbers
(
    `number` UInt64
)
ENGINE = CnchMergeTree
ORDER BY number;

然后通过数据加载功能将 numbers_source.csv 数据集加载到 test_si.numbers 表中。

2. 创建不带索引的数据表

创建一个没有索引的表并插入数据。

CREATE TABLE test_skipindex
(
    `id` UInt64,
    `key_i` UInt64,
    `p_date` Date
)
ENGINE = CnchMergeTree
PARTITION BY p_date
ORDER BY id;

INSERT INTO test_skipindex SELECT
    number,
    number,
    today()
FROM test_si.numbers
LIMIT 10000000;

执行以下查询，会处理全部 1000 万行数据。

SELECT * FROM test_skipindex WHERE key_i in (200, 700);

3. 增加索引

为新表增加索引

在创建表时直接定义索引。

CREATE TABLE test_skipindex
(
    `id` UInt64,
    `key_i` UInt64,
    `p_date` Date,
    -- 在建表时添加索引
    INDEX key_i_idx key_i TYPE minmax GRANULARITY 1
)
ENGINE = CnchMergeTree
PARTITION BY p_date
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO test_skipindex SELECT
    number,
    number,
    today()
FROM test_si.numbers
LIMIT 1000000;

再次执行以下查询，结果显示仅处理了 8192 行数据，执行速度显著提升。

SELECt * FROM test_skipindex WHERE key_i in (200, 700);

为历史数据增加索引

如果在数据插入后才添加索引，该索引默认只对新写入的数据生效。对于已存在的历史数据，必须执行物化（materialize）操作。

添加索引：

ALTER TABLE test_si.test_skipindex ADD index key_i_idx key_i TYPE minmax GRANULARITY 1;

添加物化索引：

-- 为所有历史数据构建索引
ALTER TABLE test_si.test_skipindex MATERIALIZE INDEX key_i_idx;

执行后，查询将能利用索引，处理的数据行数大幅减少。

为历史数据分区增加索引

如果只想为特定分区构建索引，可以使用 IN PARTITION 子句。只有指定分区的数据会生成索引，而其他分区的历史数据则不受影响。

-- 假设前一天的日期是 '20230927'
ALTER TABLE test_si.test_skipindex MATERIALIZE INDEX key_i_idx IN PARTITION '20230927';

4. 删除索引

使用 DROP INDEX 命令来删除一个已存在的索引。

ALTER TABLE test_si.test_skipindex DROP INDEX key_i_idx;

使用建议

当数据量较小时，建立索引的成本不高。但如果需要为非常大的全量历史数据建立索引，直接使用 MATERIALIZE INDEX 可能会对集群造成较大负载。在这种情况下，推荐使用 IN PARTITION 按分区逐个建立索引，以减轻对集群的压力。
当满足查询条件的数据有明显的“局部性”时，可以使用调数索引来加速查询。

3.4 高效的 SQL 编写技巧

好的数据模型需要高效的 SQL 来配合，遵循最佳实践可以避免许多常见的性能陷阱。

SELECT 查询优化

减少扫描数据量：WHERE 子句最佳实践

分区键过滤：永远在 WHERE 中包含分区键的过滤条件。
谓词下推：确保过滤条件能被下推到存储层。避免在 WHERE 子句的列上使用非单调函数，这可能导致索引和分区裁剪失效。

反模式：在列上使用函数

WHERE toYYYYMM(event_time) > 202508;

推荐：在值上进行计算

WHERE event_time >= '2025-08-01 00:00:00' AND event_time < '2025-09-01 00:00:00';

该示例使用值过滤条件，在分区过滤基础上引擎可以进一步通过 Part 分区键上的 minmax 信息，裁剪掉不必要的 Part。

使用 PREWHERE

对于非排序键的过滤，PREWHERE 比 WHERE 更高效。ByteHouse 会先读取 PREWHERE 相关的列，过滤数据，然后再读取 SELECT 所需的其他列。
正常引擎会自动把 WHERE 条件里合适的条件下推到 PREWHERE，如果业务能自己判断某个条件过滤率高，可以手动把 WHERE 条件写到 PREWHERE 里，尤其是 MAP 类的子列（引擎不会自动下推）。

SELECT 
    order_id, 
    user_id, 
    total_amount, 
    payment_method,
    province,
    items  -- 大字段最后读取
FROM order_data
PREWHERE 
    order_date BETWEEN '2024-04-01' AND '2024-06-30'  --先过滤日期范围
    AND status = 'paid'  -- 过滤已支付状态
WHERE 
    payment_method = 'credit_card'  -- 进一步过滤支付方式
    AND total_amount > 1000  -- 过滤金额条件

优化 JOIN 操作

JOIN 顺序：将大表放在 JOIN 子句的左侧，小表放在右侧，通常引擎会自动 reorder，如果没有，可以先收集统计信息，详情请参考4.1 统计信息收集。
JOIN 前预过滤：先用 CTE 或子查询对 JOIN 的表进行过滤和聚合，减少 JOIN 的数据量。

4. 场景化调优实践

理论知识需要结合实际场景才能发挥最大价值。本部分将针对三类典型的查询场景——复杂查询、批量处理和实时查询——提供具体的优化策略和案例分析。

4.1 统计信息收集

自动收集

对于 ByteHouse CDW，需要指定用于执行统计信息收集的计算组。

注意

该功能目前处于 Beta 阶段，如需使用，可提交工单或联系 ByteHouse 团队开启。

-- 指定计算组用于收集统计信息
CREATE AUTO STATS *.* WITH statistics_virtual_warehouse='vw-stats-collector';

手动触发收集

在数据发生重大变化后（如导入大批量数据），可以手动触发一次统计信息收集，以确保优化器能立即使用最新的信息。

-- 为单张表手动收集统计信息
CREATE STATS table_name;

-- 为整个数据库收集
CREATE STATS DATABASE db_name;

如何判断统计信息是否过时或不准确

查看 EXPLAIN 计划：如果 EXPLAIN 中显示的 rows 估算值与实际值差距巨大（一个数量级以上），通常意味着统计信息不准。
查询性能波动：如果一个稳定的查询突然性能变差，可能是因为数据变化导致原有的统计信息失效，而新的统计信息尚未收集。

4.2 复杂查询优化

复杂查询通常涉及多个大表的 JOIN、嵌套子查询、CTE 以及窗口函数，是性能问题的重灾区。

多表 `JOIN` 查询

核心策略：

减少 JOIN 的数据量：在 JOIN 之前，通过 WHERE 或 CTE 尽可能地过滤掉无效数据。
Join 顺序：确保小表在右侧。
JOIN 键类型一致并利用 Runtime Filter：这是 ByteHouse 的王牌功能。确保 JOIN 的事实表（大表）和维度表（小表）的 JOIN 键类型完全一致，以使 Runtime Filter 生效，也避免隐式类型转换带来的性能开销。

包含子查询或 CTE 的查询

核心策略：

使用 CTE 提高可读性：优先使用 WITH 子句（CTE）代替嵌套子查询，代码更清晰，也便于优化器分析。
物化 CTE（MATERIALIZE）：如果一个 CTE 被多次引用，可以考虑使用物化视图（空间换时间）。

案例研究：复杂报表查询优化

一个典型的场景是计算每个城市、每个品类的销售额及用户数。

性能问题：sales 表和 users 表都非常大，直接 JOIN 导致巨大的数据 Shuffle。count(DISTINCT) 操作在高基数列上性能低下。
优化前性能：查询耗时 95 秒。
优化思路：先对最大的表 sales 按周进行预聚合，极大减少了参与 JOIN 的数据量。同时使用 uniqCombined 近似去重函数，避免了高成本的精确去重。
优化后性能：查询耗时 8 秒。

优化前 SQL：

-- 直接 JOIN 三张大表，没有预先过滤
SELECT
    ci.city_name,
    p.category_name,
    sum(s.sale_amount) AS total_sales,
    count(DISTINCT s.user_id) AS total_users
FROM sales s
JOIN users u ON s.user_id = u.user_id
JOIN cities ci ON u.city_id = ci.city_id
JOIN products p ON s.product_id = p.product_id
WHERE s.dt BETWEEN '2025-08-01' AND '2025-08-07'
GROUP BY 1, 2;

优化后 SQL：

-- 使用 CTE 预聚合，并用近似计算函数代替精确去重
WITH weekly_sales AS (
    -- 步骤 1: 先对最大的事实表进行预聚合和过滤
    SELECT
        user_id,
        product_id,
        sum(sale_amount) AS sale_amount
    FROM sales
    WHERE dt BETWEEN '2025-08-01' AND '2025-08-07'
    GROUP BY 1, 2
)
SELECT
    ci.city_name,
    p.category_name,
    sum(s.sale_amount) AS total_sales,
    uniqCombined(s.user_id) AS approx_total_users -- 步骤2: 使用 uniqCombined 进行高性能近似去重
FROM weekly_sales s
-- 步骤 3: 将聚合后的小结果集与维度表 JOIN
JOIN users u ON s.user_id = u.user_id
JOIN cities ci ON u.city_id = ci.city_id
JOIN products p ON s.product_id = p.product_id
GROUP BY 1, 2;

4.3 批量数据处理优化

批量处理任务（如 ETL/ELT）的特点是数据量大、定时执行，优化的重点在于吞吐量和资源效率。

大批量数据导入（INSERT INTO ... SELECT）

核心策略：

调整 Block Size：适当增大写入的块大小（max_insert_block_size），可以生成更规整的数据 Part，减少后台 Merge 的压力。
SELECT 端优化：INSERT INTO ... SELECT 的瓶颈往往在 SELECT 端，应用前面章节的所有 SELECT 优化技巧。

周期性 ETL/ELT 任务调优

核心策略：

任务拆分：将一个大的、复杂的 ETL 任务拆分成多个独立的、可并行的子任务。
资源隔离：为 ETL 任务配置专门的计算组（VW），避免与在线查询业务争抢资源。
开启 BSP 模式：操作详情请参考 ELT 开发。

案例研究：夜间批处理任务性能提升

该任务每天凌晨需要将当日的日志数据清洗、转换后，写入一张用户行为汇总天表。

性能问题：单个 SQL 过于复杂，优化器难以生成最优计划。所有计算都挤在一个任务里，无法并行。
优化前性能：任务耗时 2.5 小时，经常延迟，影响下游报表。
优化思路：将单体任务“分而治之”。每一步只做一件事，逻辑清晰。步骤之间可以并行执行（如果无依赖），且每一步的中间结果都可以被复用和检查。这降低了优化的复杂度，也提升了整体的鲁棒性。
优化后性能：整体任务耗时 40 分钟。

优化前 SQL：

-- 一个巨大的 INSERT ... SELECT 语句，包含了复杂的 JOIN 和转换逻辑
INSERT INTO user_behavior_daily
SELECT
    user_id,
    toDate(event_time) AS dt,
    -- ... 大量复杂的 UDF 和多表 JOIN 逻辑 ...
FROM raw_logs
WHERE toDate(event_time) = yesterday();

优化后方案：

-- 步骤 1: 清洗和初步处理日志，写入临时表 1
CREATE TABLE cleaned_logs_tmp ...;
INSERT INTO cleaned_logs_tmp
SELECT ... FROM raw_logs WHERE toDate(event_time) = yesterday();

-- 步骤 2: 关联维度信息，写入临时表 2
CREATE TABLE enriched_logs_tmp ...;
INSERT INTO enriched_logs_tmp
SELECT ... FROM cleaned_logs_tmp JOIN users ON ...;

-- 步骤 3: 最终聚合，写入目标表
INSERT INTO user_behavior_daily
SELECT ... FROM enriched_logs_tmp GROUP BY ...;

-- 清理临时表
DROP TABLE cleaned_logs_tmp;
DROP TABLE enriched_logs_tmp;

4.4 实时查询优化

实时查询场景（如在线 Dashboard）要求极低的查询延迟和较高的并发能力。

低延迟查询的设计要点

核心策略：

数据预聚合：不要在实时查询时进行复杂的计算。创建预聚合的汇总表（Flink 宽表打平和物化视图都是实现这一点的绝佳工具），让查询直接命中结果。
极致的 SELECT 优化：应用所有 SELECT 优化技巧，确保查询能最大限度地利用分区和索引，扫描最少的数据。
避免 JOIN 大表：实时查询应尽可能避免 JOIN 操作，特别是 Shuffle Join。如果必须 JOIN，通过“大表宽表化”的模式，将维度信息提前拍平到事实表中。

并发查询性能调优

核心策略：

扩展计算组：在高峰期，通过增加计算组的节点数（Scale Out）或提升节点规格（Scale Up）来应对高并发请求。
查询队列与优先级：为不同重要程度的查询设置不同的资源队列和优先级，确保核心业务的查询不会被低优先级任务阻塞。
利用中间结果缓存：对于重复性高的查询，ByteHouse 的中间结果缓存可以秒级返回结果，极大降低后端负载，开启参数 enable_intermediate_result_cache。

案例研究：高并发 Dashboard 查询响应时间优化

一个监控大盘需要每 5 秒刷新一次最近 1 分钟的核心业务指标。

性能问题：api_calls 表每秒写入数万行，即使只查 1 分钟数据，聚合计算的开销在高并发下也无法接受。
优化前性能：平均响应时间 500 ms，并发量上升时延迟飙升至 2-3 秒。
优化思路：物化视图在后台持续地、增量地进行预聚合。前端查询不再需要计算原始数据，而是查询一个行数极少（只有几种 status*6 个时间窗口）的物化视图。countState 和 sumState 等函数使得聚合结果可以被高效合并。
优化后性能：平均响应时间 < 50ms，高并发下保持稳定。

优化前 SQL：

-- 直接在原始数据表上进行聚合
SELECT
    status,
    count(*),
    avg(response_time)
FROM api_calls
WHERE event_time >= now() - INTERVAL '1 MINUTE'
GROUP BY status;

优化后方案：

-- 步骤 1 : 创建一个每 10 秒刷新一次的物化视图
CREATE MATERIALIZED VIEW api_calls_10s_mv
ENGINE = CnchMergeTree
AS SELECT
    toStartOfInterval(event_time, INTERVAL 10 SECOND) AS t_10s,
    status,
    countState() AS calls,
    sumState(response_time) AS total_response_time
FROM api_calls
GROUP BY t_10s, status;

-- 步骤 2: Dashboard 查询物化视图
SELECT
    status,
    sum(calls),
    sum(total_response_time) / sum(calls)
FROM api_calls_10s_mv
WHERE t_10s >= toStartOfInterval(now() - INTERVAL '1 MINUTE', INTERVAL 10 SECOND)
GROUP BY status;

5. 性能监控与管理

SQL 优化不是一次性的任务，而是一个持续的过程。建立有效的监控和管理机制，是保障数据仓库长期健康、高效运行的关键。

5.1 查询监控与分析

使用系统表（bh_system.query_log）进行性能分析

bh_system.query_log 是 ByteHouse 的“黑匣子”，它记录了每一个执行过的查询的详尽信息。这是进行事后性能诊断和审计的最重要数据源。
常用关键字段：

query: 执行的 SQL 语句。
type: 查询事件类型（如 QueryStart, QueryFinish）。
query_duration_ms: 查询总耗时（毫秒）。
read_rows, read_bytes: 读取的行数和字节数。
written_rows, written_bytes: 写入的行数和字节数。
memory_usage: 峰值内存使用。
exception_code: 异常码，用于定位失败的查询。

分析示例：

-- 查找过去 24 小时内最耗时的 TOP 10 查询
SELECT
    query_duration_ms,
    read_rows,
    memory_usage,
    query
FROM bh_system.query_log
WHERE type = 'QueryFinish'
  AND query_start_time >= now() - INTERVAL '1 DAY'
ORDER BY query_duration_ms DESC
LIMIT 10;

定位慢查询和资源消耗大的查询

通过对 bh_system.query_log 的聚合分析，可以轻松定位各类有问题的查询。

查找 CPU 消耗大户：按 ProfileEvents['UserTimeMicroseconds'] + ProfileEvents['SystemTimeMicroseconds'] 排序。
查找内存消耗大户：按 memory_usage 排序。
查找失败率高的查询：统计 exception_code != 0 的查询。

可视化性能监控工具

可以根据 Bytehouse 控制台中暴露出来的 metrics 来分析，详情请参考监控告警。

5.2 资源管理与并发控制

计算组（Virtual Warehouse）的配置与扩展

计算组（Virtual Warehouse，VW）是 ByteHouse 中计算资源的管理单元。通过合理配置计算组，可以实现资源隔离和弹性伸缩。

为不同业务创建不同计算组：例如，为 ETL 任务、BI 报表、Ad-hoc 即席查询分别创建独立的计算组，避免它们之间互相干扰。
弹性伸缩：
- Scale Up（垂直扩展）：提升计算组的节点规格（如 L -> XL -> 2XL），适用于需要更大内存或更强单点计算能力的复杂查询。
- Scale Out（水平扩展）：增加计算组的节点数量，适用于应对高并发的查询场景。

并发查询调优与排队策略

最大并发数设置：可以为每个计算组设置 max_concurrent_queries，防止过多的查询同时涌入，导致所有查询都变慢。
限制大查询：限制单个查询的 CPU 使用上限，设置参数 max_query_cpu_seconds。
查询队列：当并发数达到上限时，后续的查询会进入队列等待。合理的队列设置可以保证系统的稳定性。
优先级：可以为不同的用户或角色设置查询优先级，确保核心业务的请求被优先处理。

最近更新时间：2025.10.15 17:41:40

这个页面对您有帮助吗？

有用

无用

ByteHouse云数仓版

1.1. 目的与读者对象 #

1.2. ByteHouse CDW 查询优化核心思想 #

1.3. 如何使用本指南 #

2.1 查询优化快速入门 #

理解查询生命周期 #

识别性能瓶颈：关键指标与工具 #

优化实例：简单的慢查询优化之旅 #

2.2 理解 EXPLAIN 执行计划 #

2.2.1. 如何阅读和分析 EXPLAIN ANALYZE #

2.2.2. 关键算子（Operator）解读 #

2.2.3. 通过 EXPLAIN 发现常见问题 #

3.1 数据模型与 DDL 优化 #

表结构设计最佳实践 #

3.2 跳数索引（Data Skipping Index） #

创建跳数索引 #

支持的数据类型 #

分析索引效果

3.3 使用举例 #

1. 载入数据集到 ByteHouse #

2. 创建不带索引的数据表 #

3. 增加索引 #

为新表增加索引

为历史数据增加索引

为历史数据分区增加索引

4. 删除索引 #

使用建议 #

3.4 高效的 SQL 编写技巧 #

SELECT 查询优化 #

减少扫描数据量：WHERE 子句最佳实践

使用 PREWHERE

优化 JOIN 操作

4.1 统计信息收集 #

自动收集 #

手动触发收集 #

如何判断统计信息是否过时或不准确 #

4.2 复杂查询优化 #

多表 JOIN 查询 #

包含子查询或 CTE 的查询 #

案例研究：复杂报表查询优化 #

4.3 批量数据处理优化 #

大批量数据导入（INSERT INTO ... SELECT） #

周期性 ETL/ELT 任务调优 #

案例研究：夜间批处理任务性能提升 #

4.4 实时查询优化 #

低延迟查询的设计要点 #

并发查询性能调优 #

案例研究：高并发 Dashboard 查询响应时间优化 #

5.1 查询监控与分析 #

使用系统表（bh_system.query_log）进行性能分析 #

定位慢查询和资源消耗大的查询 #

可视化性能监控工具 #

5.2 资源管理与并发控制 #

计算组（Virtual Warehouse）的配置与扩展 #

并发查询调优与排队策略 #

1.1. 目的与读者对象

1.2. ByteHouse CDW 查询优化核心思想

1.3. 如何使用本指南

2.1 查询优化快速入门

理解查询生命周期

识别性能瓶颈：关键指标与工具

优化实例：简单的慢查询优化之旅

2.2 理解 EXPLAIN 执行计划

2.2.1. 如何阅读和分析 EXPLAIN ANALYZE

2.2.2. 关键算子（Operator）解读

2.2.3. 通过 EXPLAIN 发现常见问题

3.1 数据模型与 DDL 优化

表结构设计最佳实践

3.2 跳数索引（Data Skipping Index）

创建跳数索引

支持的数据类型

3.3 使用举例

1. 载入数据集到 ByteHouse

2. 创建不带索引的数据表

3. 增加索引

4. 删除索引

使用建议

3.4 高效的 SQL 编写技巧

SELECT 查询优化

4.1 统计信息收集

自动收集

手动触发收集

如何判断统计信息是否过时或不准确

4.2 复杂查询优化

多表 `JOIN` 查询

包含子查询或 CTE 的查询

案例研究：复杂报表查询优化

4.3 批量数据处理优化

大批量数据导入（INSERT INTO ... SELECT）

周期性 ETL/ELT 任务调优

案例研究：夜间批处理任务性能提升

4.4 实时查询优化

低延迟查询的设计要点

并发查询性能调优

案例研究：高并发 Dashboard 查询响应时间优化

5.1 查询监控与分析

使用系统表（bh_system.query_log）进行性能分析

定位慢查询和资源消耗大的查询

可视化性能监控工具

5.2 资源管理与并发控制

计算组（Virtual Warehouse）的配置与扩展

并发查询调优与排队策略