Search CTRL + K

ClickHouse 查询优化

#IT/ClickHouse

ClickHouse 是 OLAP（Online analytical processing）数据库，以速度见长 [1]。ClickHouse 为什么能这么快？有两点原因 [2]：

但是，数据库设计再优越也拯救不了错误的使用方式，本文以 MergeTree 引擎家族为例讲解如何对查询优化。

ClickHouse 查询执行过程

⚠️ 本节基于 ClickHouse 22.3 版本分析

clickhouser-server 启动后会在 while 循环中等待请求，接收到查询后会调用 executeQueryImpl() 行数构建 AST、优化并生成执行计划 pipeline，最后在 executeImpl() 中多线程执行 DAG 获取结果，这篇文章只关心 SQL 执行，省略掉网络交互部分，查询执行流程如下图所示：

20230202172234.7B51d5

SQL 的解析优化和编译原理息息相关，本节将包含大量编译原理和代码细节，属扩展知识。

词法解析和语法解析

ClickHouse 拿到需要执行的 SQL，首先需要将 String 格式的字符串解析为它能理解的数据结构，也就是 AST 和执行计划。构造 AST 部分代码如下所示：

// src/Interpreters/executeQuery.cpp

static std::tuple<ASTPtr, BlockIO> executeQueryImpl()
{
    // 构造Parser
    ParserQuery parser(end, settings.allow_settings_after_format_in_insert);

    // 将SQL转为抽象语法树
    ast = parseQuery(parser, begin, end, "", max_query_size, settings.max_parser_depth);

    // 设置query的上下文，比如SETTINGS
    ...

    if (async_insert)
    {
        ...
    } else {
        // 生成interpreter实例
        interpreter = InterpreterFactory::get(ast, context, SelectQueryOptions(stage).setInternal(internal));

        // interpreter优化AST并返回执行计划
        res = interpreter->execute();
    }

    // 返回抽象语法树和执行计划
    return std::make_tuple(ast, std::move(res));
}

值得一提的是，解析 SQL 生成语法树这是编译原理中词法分析和语法分析部分覆盖的事情。词法分析只是简单拆解数据流为一个个 token，而语法分析分为自顶向下和自底向上两种方式，常见的语法分析方式也分为手写词法分析（往往是自顶向下的有限状态机，递归下降分析）和语法分析工具（往往是自底向上，如 Flex、Yacc/Bison 等）。

手写语法分析比起语法分析器有几个优势（当然要写得好的情况）：

ClickHouse 解析 SQL 的函数如下所示：

// src/Parsers/parseQuery.cpp

ASTPtr tryParseQuery()
{
    // 将SQL拆分为token流
    Tokens tokens(query_begin, all_queries_end, max_query_size);
    IParser::Pos token_iterator(tokens, max_parser_depth);

    // 将token流解析为语法树
    ASTPtr res;
    const bool parse_res = parser.parse(token_iterator, res, expected);

    return res;
}

可以看到先将 SQL 字符串拆解为 token 流（词法分析），再调用 perser.parse() 函数进行语法分析，它的实现如下：

// src/Parsers/ParserQuery.cpp

bool ParserQuery::parseImpl(Pos & pos, ASTPtr & node, Expected & expected)
{
    ParserQueryWithOutput query_with_output_p(end, allow_settings_after_format_in_insert);
    ParserInsertQuery insert_p(end, allow_settings_after_format_in_insert);
    ParserUseQuery use_p;
    ParserSetQuery set_p;
    ParserSystemQuery system_p;
    ParserCreateUserQuery create_user_p;
    ParserCreateRoleQuery create_role_p;
    ParserCreateQuotaQuery create_quota_p;
    ParserCreateRowPolicyQuery create_row_policy_p;
    ParserCreateSettingsProfileQuery create_settings_profile_p;
    ParserCreateFunctionQuery create_function_p;
    ParserDropFunctionQuery drop_function_p;
    ParserDropAccessEntityQuery drop_access_entity_p;
    ParserGrantQuery grant_p;
    ParserSetRoleQuery set_role_p;
    ParserExternalDDLQuery external_ddl_p;
    ParserTransactionControl transaction_control_p;
    ParserBackupQuery backup_p;

    bool res = query_with_output_p.parse(pos, node, expected)
        || insert_p.parse(pos, node, expected)
        || use_p.parse(pos, node, expected)
        || set_role_p.parse(pos, node, expected)
        || set_p.parse(pos, node, expected)
        || system_p.parse(pos, node, expected)
        || create_user_p.parse(pos, node, expected)
        || create_role_p.parse(pos, node, expected)
        || create_quota_p.parse(pos, node, expected)
        || create_row_policy_p.parse(pos, node, expected)
        || create_settings_profile_p.parse(pos, node, expected)
        || create_function_p.parse(pos, node, expected)
        || drop_function_p.parse(pos, node, expected)
        || drop_access_entity_p.parse(pos, node, expected)
        || grant_p.parse(pos, node, expected)
        || external_ddl_p.parse(pos, node, expected)
        || transaction_control_p.parse(pos, node, expected)
        || backup_p.parse(pos, node, expected);

    return res;
}

可以发现 ClickHouse 将 Query 分为了 18 种类型（截止 2022-11-12 日），每种 Query 都有自己的 Parser，通过关键词匹配构造 AST 上的节点，最终生成语法树。递归下降部分超纲了，这里就不铺开讲。

优化器

经过语法分析后生成的 AST 并不是执行最优解，ClickHouse 包含大量基于规则的优化（rule based optimization），每个 Query 会遍历一遍优化规则，将满足的情况进行 不改变查询语义地重写。

每一种 Query 类型都有对应的 Interpreter，后文都以 Select 查询举例，代码如下：

// src/Interpreters/InterpreterFactory.cpp

std::unique_ptr<IInterpreter> InterpreterFactory::get()
{
    ...
    if (query->as<ASTSelectQuery>())
    {
        return std::make_unique<InterpreterSelectQuery>(query, context, options);
    }
    ...
}

在 InterpreterSelectQuery 类的构造函数中将 AST 优化、重写，代码详见 src/Interpreters/InterpreterSelectQuery.cpp，这里只画流程图：

graph TD
    A[初始化settings] --> B[优化with]
    B --> C[优化joins]
    C --> D[谓词下推将where下推到prewhere]
    D --> E{是否要再次优化}
    E --> |是| A
    E --> |否| F[检查storage权限]
    F --> G[生成analysis_result和result_header]

构造执行计划

src/Interpreters/InterpreterSelectQuery.cpp 文件 InterpreterSelectQuery::executeImpl() 方法将优化分析得到的中间数据辅助生成最终的执行计划，代码如下：

// src/Interpreters/InterpreterSelectQuery.cpp

void InterpreterSelectQuery::executeImpl()
{
    ...
    // 个人理解针对EXPLAIN PLAN，只构建执行计划不执行
    if (options.only_analyze)
    {
        ...
    }
    else
    {
        // 从磁盘读取所需列，注意这一行，后文跳转进去分析
        executeFetchColumns(from_stage, query_plan);
    }
    if (options.to_stage > QueryProcessingStage::FetchColumns)
    {
        // 在分布式执行Query时只在远程节点执行
        if (expressions.first_stage)
        {
            // 当storage不支持prewhere时添加FilterStep
            if (!query_info.projection && expressions.filter_info)
            {
                ...
            }
            if (expressions.before_array_join)
            {
                ...
            }
            if (expressions.array_join)
            {
                ...
            }
            if (expressions.before_join)
            {
                ...
            }
            // 可选步骤：将join key转为一致的supertype
            if (expressions.converting_join_columns)
            {
                ...
            }
            // 添加Join
            if (expressions.hasJoin())
            {
                ...
            }
            // 添加where
            if (!query_info.projection && expressions.hasWhere())
                executeWhere(query_plan, expressions.before_where, expressions.remove_where_filter);
            // 添加aggregation
            if (expressions.need_aggregate)
            {
                executeAggregation(
                    query_plan, expressions.before_aggregation, aggregate_overflow_row, aggregate_final, query_info.input_order_info);
                /// We need to reset input order info, so that executeOrder can't use it
                query_info.input_order_info.reset();
                if (query_info.projection)
                    query_info.projection->input_order_info.reset();
            }
            // 准备执行：
            // 1. before windows函数
            // 2. windows函数
            // 3. after windows函数
            // 4. 准备DISTINCT
            if (expressions.need_aggregate)
            {
                // 存在聚合函数，在windows函数/ORDER BY之前不执行
            }
            else
            {
                // 不存在聚合函数
                // 存在windows函数，应该在初始节点运行
                // 并且，ORDER BY和DISTINCT依赖于windows函数，这里也不能运行
                if (query_analyzer->hasWindow())
                {
                    executeExpression(query_plan, expressions.before_window, "Before window functions");
                }
                else
                {
                    // 没有windows函数，执行before ORDER BY、准备DISTINCT
                    assert(!expressions.before_window);
                    executeExpression(query_plan, expressions.before_order_by, "Before ORDER BY");
                    executeDistinct(query_plan, true, expressions.selected_columns, true);
                }
            }
            // 如果查询没有GROUP、HAVING，有ORDER或LIMIT，会在远程排序、LIMIT
            preliminary_sort();
        }
        // 在分布式执行Query时只在初始节点执行或optimize_distributed_group_by_sharding_key开启时
        if (expressions.second_stage || from_aggregation_stage)
        {
             if (from_aggregation_stage)
            {
                // 远程节点聚合过，这里啥也不干
            }
            else if (expressions.need_aggregate)
            {
                // 从不同节点拉取数据合并
                if (!expressions.first_stage)
                    executeMergeAggregated(query_plan, aggregate_overflow_row, aggregate_final);
                
                if (!aggregate_final)
                {
                    // 执行group by with totals/rollup/cube
                    ...
                }
                // 添加Having
                else if (expressions.hasHaving())
                    executeHaving(query_plan, expressions.before_having, expressions.remove_having_filter);
            }
            // 报个错
            else if (query.group_by_with_totals || query.group_by_with_rollup || query.group_by_with_cube)
                throw Exception("WITH TOTALS, ROLLUP or CUBE are not supported without aggregation", ErrorCodes::NOT_IMPLEMENTED);
            // 准备执行：
            // 1. before windows函数
            // 2. windows函数
            // 3. after windows函数
            // 4. 准备DISTINCT
            if (from_aggregation_stage)
            {
                if (query_analyzer->hasWindow())
                    throw Exception(
                        "Window functions does not support processing from WithMergeableStateAfterAggregation",
                        ErrorCodes::NOT_IMPLEMENTED);
            }
            else if (expressions.need_aggregate)
            {
                executeExpression(query_plan, expressions.before_window,
                    "Before window functions");
                executeWindow(query_plan);
                executeExpression(query_plan, expressions.before_order_by, "Before ORDER BY");
                executeDistinct(query_plan, true, expressions.selected_columns, true);
            }
            else
            {
                if (query_analyzer->hasWindow())
                {
                    executeWindow(query_plan);
                    executeExpression(query_plan, expressions.before_order_by, "Before ORDER BY");
                    executeDistinct(query_plan, true, expressions.selected_columns, true);
                }
                else
                {
                    // Neither aggregation nor windows, all expressions before
                    // ORDER BY executed on shards.
                }
            }
            // 添加order by
            if (expressions.has_order_by)
            {
                // 在分布式查询中，没有聚合函数却有order by，将会在远端节点order by
                ...
            }
            // 多source order by优化
            ...
            
            // 多条流时再次执行distinct
            if (!from_aggregation_stage && query.distinct)
                executeDistinct(query_plan, false, expressions.selected_columns, false);

            // 处理limit
            ...
            // 处理projection
            ...
            // 处理offset
            ...
        }
        
        // 需要子查询结果构建set
        if (!subqueries_for_sets.empty())
            executeSubqueriesInSetsAndJoins(query_plan, subqueries_for_sets);
    }
}

其中 InterpreterSelectQuery::executeFetchColumns() 函数是读取所需列的阶段。从代码中可以看到它也做了很多的优化：

可以看到：

  1. limit 大部分情况下是计算完成后再执行，而 quota 是在读取数据时执行的
  2. 加速的关键是减少读入的数据量，也就是说善用索引
  3. 用 count()、count(1) 和 count(*)，ClickHouse 都有优化，但不要 count(any_field)

索引设计

索引是 ClickHouse 快速查询最重要的一环，分为主键索引（sparse indexes）和跳表索引（data skipping indexes）。在执行查询时，索引命中顺序如下图所示：

graph TD
    A[Partition Key MinMax Index] --> B[Partition]
    B --> C[Primary Key Sparse Index]
    C --> D[Data Skipping Indexes]

详见代码：

// src/Processors/QueryPlan/ReadFromMergeTree.cpp

MergeTreeDataSelectAnalysisResultPtr ReadFromMergeTree::selectRangesToRead()
{
    ...
    try
    {
        // 使用partition by选取需要parts
        MergeTreeDataSelectExecutor::filterPartsByPartition(...);
        // 处理抽样
        ...
        // 使用主键索引和跳表索引
        result.parts_with_ranges = MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(...);
    }
    catch(...)
    {
        ...
    }
    ...
}

值得注意的是，主键的 sparse index 使用二分查找直接缩小范围到所需要的 parts，而跳表索引就需要在选出来的 parts 里，每 n 个（用户自定义）granules 就需要比较 n 次。

最佳实践：

partition by 需要一个可以转为时间的列，比如 Datatime、Date 或者时间戳，而如果 primary key 中也有时间字段，可以使用同一个字段避免查询时需要同时指定两个时间字段。比如：指定为数据处理时间。

Partition

首先要辨析 part 和 partition 的区别，ClickHouse 应用层面定义了 partition，用户指定 partition by 关键词设置不同的 partition，但是 partition 只是逻辑分区。真正存储到磁盘时按 part 来存储，每一个 part 一个文件夹，里面存储不同字段的 .mrk 和 .bin 文件，以及一个 minmax_{PARTITION_KEY_COLUMN}.idx 文件，不同 part 的 minmax 作为一个索引存储于内存。

当查询的 WHERE 带有 partition key 时，首先会比较每一个 part 的 minmax 索引过滤不相关 parts。之后再根据 PARTITION BY 定义的规则过滤不相关 partition。

可是 partition 不是越小越好。

partitioning 并不会加速查询（有主键存在），过小的 partition 反而会导致大量的 parts 无法合并（MergeTree 引擎家族会在后台不断合并 parts），因为属于不同 partition 的 parts 无法合并。[5]

最佳实践[6]：

Primary key index

主键是 ClickHouse 最重要的索引，没有之一。好的主键应该能有效排除大量无关的数据 granules，减少磁盘读取的字节数。

先讲几个主键的背景知识：

主键的选择应该尽可能考虑周全，因为主键是无法修改的，只能建新表后数据迁移。

最佳实践[12]（针对 (Replicated)MergeTree 引擎）：

  1. 选择永远会用于过滤条件的列
  2. 越重要的、基数越低的放左边
  3. 主键中不要出现两个高基数字段，一般最后一列可以为总体增长的时间字段
  4. 将行的特征字段加入，将相似的行放一起，提高压缩率
  5. 若主键包含主从关系，主放左边，从放右边

Data skipping indexes

最后一步是跳表索引，这个没有太多可以讲的地方，和其他数据库相同，跳表索引用于尽量减少读取的行数。具体参看 官方文档。

配置优化

配置优化分为两部分，全局配置优化和 MergeTree 表配置优化。

全局配置优化

参看 Altinity 选择性配置优化项。

这里写三个推荐的配置：

  1. 添加 force_index_by_date 和 force_primary_key 避免全盘读取
  2. 调整内存配置，参考 Altinity
  3. 系统表添加 TTL 和 ttl_only_drop_parts 表配置

表配置优化

除了全局配置，MergeTree 引擎家族每张表也有自己的配置项。[13]

推荐设置如下配置：

  1. ttl_only_drop_parts=1。只有 parts 中所有数据都过期了才会 DROP，可以有效减少 TTL_MERGE 发生的频率，降低磁盘负载。
  2. merge_with_ttl_timeout=86400。配合上一项配置，将 TTL 检查调整为 1 天一次（默认 4 小时一次）。
  3. use_minimalistic_part_header_in_zookeeper=1。可以有效降低 Zookeeper 负载，避免 Zookeeeper 成为性能瓶颈（插入）。

字段优化

除了索引、分区和配置外，还有表字段可以优化。接下来将讲述 Schema 类型、CODEC 和缓存三个方面。

注意，尽量避免使用 Null，在 ClickHouse 中 Null 会用一个单独 Null masks 文件存储哪些行为 Null[14]，因此读取某个普通字段只需要 .bin 和 .mrk 两个文件，而读取 Nullable 字段时需要 .bin、.mrk 和 masks 文件。社区查询验证，最高会有 2 倍性能损失。[15]

Schema 类型

使用 ClickHouse 存储时，一般用户都会创建大宽表，包含大量数值、字符串类型的字段。这里提及两种 Schema 类型 [16]，没有哪个更优越，由读者执行评估业务适合哪一种。

平铺字段

这是我们主表正在使用的类型，将可能用到的字段预留平铺，除了一系列基础字段外，增加大量 metric1, metric2...metricN 和 tag1, tag2...tagN 等等字段。

优点：

缺点：

arrays/nested/map 字段

这是我们 ctree 功能正在使用的类型。将业务字段塞入嵌套数据类型中，比如 array、nested struct 和 map。后文以 array 举例：metric_array、tag_array。

优点：

缺点：

总结

关于 Schema 设计这里，读者可以考虑 28 原则，理论上 80% 查询只会用到 20% 的业务字段，因此可以将使用频率高的业务字段平铺，将使用频率低的字段放入嵌套结构中。

CODEC

CODEC 分为压缩算法 CODEC、存储格式 CODEC 和加密 CODEC，一般可以组合一起使用。在 ClickHouse 中，未显示指定 CODEC 的字段都会被分配一个 DEFAULT 默认 CODEC LZ4（除非用户修改 clickhouse 配置 compression 部分 [17]）。

压缩算法 CODEC 的选择是一个平衡板问题，更高的压缩度可以有更少的 IO 但是更高的 CPU，更低的压缩度有更多的 IO 但是更少的 CPU。这需要读者根据部署机器配置自行选择合适的压缩算法和压缩等级。

这里提供两个判断策略：

存储格式 CODEC 主要是 Delta、DoubleDelta、Gorilla、FPC 和 T64 几种。

扩展阅读：

缓存

mark_cache_size 可以调整 .mrk 文件的缓存大小，默认为 5GB。适当调大可以减少查询时 IO 次数，有效降低磁盘压力。[19]

MarkCache 是 ClickHouse 自己实现的 LRU 缓存，因此内存占用是被动的，参见源码：src/Storages/MarkCache.h。

可以通过如下 SQL 查询当前所有表的 parts 信息：

SELECT
    database,
    table,
    count() AS parts,
    uniqExact(partition_id) AS partition_cnt,
    sum(rows),
    formatReadableSize(sum(data_compressed_bytes) AS comp_bytes) AS comp,
    formatReadableSize(sum(data_uncompressed_bytes) AS uncomp_bytes) AS uncomp,
    uncomp_bytes / comp_bytes AS ratio,
    formatReadableSize(sum(marks_bytes) AS mark_sum) AS marks,
    mark_sum / uncomp_bytes AS mark_ratio
FROM cluster(default_cluster, system.parts)
WHERE active
GROUP BY
    database,
    table
ORDER BY comp_bytes DESC

可以通过如下查询获取当天 mrk 缓存命中情况：

WITH (ProfileEvents.Values[indexOf(ProfileEvents.Names, 'MarkCacheHits')]) AS MARK_CACHE_HITS
SELECT
    toHour(event_time) AS time,
    countIf(MARK_CACHE_HITS != 0) AS hit_query_count,
    count() AS total_query_count,
    hit_query_count / total_query_count AS hit_percent,
    avg(MARK_CACHE_HITS) AS average_hit_files,
    min(MARK_CACHE_HITS) AS minimal_hit_files,
    max(MARK_CACHE_HITS) AS maximal_hit_files,
    quantile(0.5)(MARK_CACHE_HITS) AS "50",
    quantile(0.9)(MARK_CACHE_HITS) AS "90",
    quantile(0.99)(MARK_CACHE_HITS) AS "99"
FROM clusterAllReplicas('default_cluster', system.query_log)
WHERE event_date = toDate(now())
  AND (type = 2 OR type = 4)
  AND query_kind = 'Select'
GROUP BY time
ORDER BY time ASC

以及如下查询获取当前 mrk 缓存内存占用情况：

SELECT formatReadableSize(value)
FROM system.asynchronous_metrics
WHERE metric = 'MarkCacheBytes'

以及 mrk 缓存具体缓存多少文件：

SELECT value
FROM system.asynchronous_metrics
WHERE metric = 'MarkCacheFiles'

除此之外，ClickHouse 还可以调整 uncompressed_cache 缓存一定量原始数据于内存中。[20] 但是这个缓存只对大量短查询有效，对于 OLAP 来说，查询千奇百怪，不太建议调整这个配置。

业务优化

到了最难的部分，由于接下来的部分和不同业务息息相关，为了讲解我们业务上的优化，我先介绍下我们业务情况：

QAPM 主打应用性能监控，主要分为指标、个例两张表。个例表包含更多基础字段，一般用户展示；指标表主要用于聚合计算。

首先确定主键，毋庸置疑的前两个一定是

指标没有特征键值，因此只添加处理时间作为第三个主键。

对于指标表，设置的主键为：app_id, category, entrance_time

个例存在特征 feature，由于：

  1. 大量查询都包含 feature_md5
  2. feature 是行的特征，相同的特征表明两行相似，

将特征的 md5 增加到主键中，用于加速查询、提高压缩率。但是这里有两个方向：

分区键设置为PARTITION BY intDiv(entrance_time, 2592000000)`

鉴于 SAMPLE BY 需要将 xxHash 字段放在主键中，主键都包含高基数字段，就不设置抽样键，而是在需要的时候软抽样 [21]：

SELECT count() FROM table WHERE ... AND cityHash64(some_high_card_key) % 10 = 0; -- Deterministic
SELECT count() FROM table WHERE ... AND rand() % 10 = 0; -- Non-deterministic

插入优化

数据插入看起来和查询性能没什么联系，但是有间接影响。不合理的插入会导致更多的写盘、更多的数据 merge 甚至有可能插入失败，影响读盘性能。

聚合写入

ClickHouse 作为 OLAP 并不适合小批量、大并发写入，相反而适合大批量、小并发写入，官方建议插入数据每批次至少 1000 行，或者每秒钟最多 1 次插入。[22]

这一小节我想强调原子（Atomic Insert）写入的概念：一次插入创建一个数据 part。

前文提及，ClickHouse 一个 part 是一个文件夹，后台有个 merge 线程池不断 merge 不同的 part。原子插入可以减少 merge 次数，让 ClickHouse 负载更低，性能更好。

原子写入的充分条件 [23]：

这里贴一下我们生产的配置（users.xml）。

经过统计，个例表每行大约 2KB，指标表每行大约 100B（未压缩）。

设置 min_insert_block_size_rows 为 10000000，指标会先满足这个条件，大概一个 block 原始大小 1GB。设置 min_insert_block_size_bytes 为 4096000000，个例会先满足这个条件，大概一个 block 原始大小 1G，约 1024000 行。

这三个配置项是 客户端配置，需要在插入的 session 中设置，而不是在那几个 .xml 中配置。[24]

max_insert_block_size: 16777216
input_format_parallel_parsing: 0
min_insert_block_size_rows: 10000000
min_insert_block_size_bytes: 1024000000

注意，min_insert_block_size_rows 和 min_insert_block_size_bytes 是“或”的关系：

// src/Interpreters/SquashingTransform.cpp

bool SquashingTransform::isEnoughSize(size_t rows, size_t bytes) const
{
    return (!min_block_size_rows && !min_block_size_bytes)
        || (min_block_size_rows && rows >= min_block_size_rows)
        || (min_block_size_bytes && bytes >= min_block_size_bytes);
}

读写分离

⚠️：本方案并没有经过生产验证，酌情考虑

ClickHouse 有 Shard 和 Replica 可以配置，作用如下图所示：

20230202172336.EBiir7

所谓读写分离也就是将 Shard 分为两半，一半只用于查询，只要让分布式表查询都导入到 Shard1 即可（在 users.xml 中配置 load_balancing 为 first_or_random）；一半用于写入，插入的程序手动控制插入 Shard2 的节点，由 ClickHouse 的 ReplicatedMergeTree 不同 Shard 数据依靠 zookeeper 自动同步的策略将数据同步到 Shard1。[25]

这种策略有天然的缺陷：

🤔：或许可以配置两边 Shard 资源不一致来解决问题，比如写入的 Shard 资源拉低，专用于处理数据插入；读的 Shard 资源更高，专门用于处理突增并发流量。

BufferEngine

Buffer 并不推荐常规业务使用，只有在迫切需要查询实时性 + 插入无法大批量预聚合时使用：

KafkaEngine+MV

该部分待补充，想看的同学可以在评论区踢踢😄

预聚合

预聚合有三种方法，ETL、物化视图和投影，他们的区别如下 [27]：

ETL MV Projections
Realtime no yes yes
How complex queries can be used to build the preaggregaton any complex very simple
Impacts the insert speed no yes yes
Are inconsistancies possible Depends on ETL. If it process the errors properly - no. yes (no transactions / atomicity) no
Lifetime of aggregation any any Same as the raw data
Requirements need external tools/scripting is a part of database schema is a part of table schema
How complex to use in queries Depends on aggregation, usually simple, quering a separate table Depends on aggregation, sometimes quite complex, quering a separate table Very simple, quering the main table
Can work correctly with ReplacingMergeTree as a source Yes No No
Can work correctly with CollapsingMergeTree as a source Yes For simple aggregations For simple aggregations
Can be chained Yes (Usually with DAGs / special scripts) Yes (but may be not straightforward, and often is a bad idea) No
Resources needed to calculate the increment May be signigicant Usually tiny Usually tiny

在我们业务中，个例是不应该预聚合的，因为数据需要被拉取展示而不用计算。指标需要聚合，数据量较大，每次实时计算对 ClickHouse 负载太大。

其实还有一种聚合方式，过期数据聚合。可以参考，同样限制要求 group by 的键值为主键前缀。

在我们业务使用时，什么时候用哪一个呢？

  1. 需要针对某个功能加速时，可以考虑物化视图/投影
  2. 全表预聚合加速查询，需要使用 ETL

资源控制

最后，为了避免集群被某个查询、插入弄垮，需要合理安排内存使用，需要给访问账户分权限，在我们业务分为：

viewer 账户配置如下所示：

<yandex>
    <profiles>
        <query>
            <max_memory_usage>10000000000</max_memory_usage>
            <max_memory_usage_for_all_queries>100000000000</max_memory_usage_for_all_queries>
            <max_rows_to_read>1000000000</max_rows_to_read>
            <max_bytes_to_read>100000000000</max_bytes_to_read>
            <max_rows_to_group_by>1000000</max_rows_to_group_by>
            <group_by_overflow_mode>any</group_by_overflow_mode>
            <max_rows_to_sort>1000000</max_rows_to_sort>
            <max_bytes_to_sort>1000000000</max_bytes_to_sort>
            <max_result_rows>100000</max_result_rows>
            <max_result_bytes>100000000</max_result_bytes>
            <result_overflow_mode>break</result_overflow_mode>
            <max_execution_time>60</max_execution_time>
            <min_execution_speed>1000000</min_execution_speed>
            <timeout_before_checking_execution_speed>15</timeout_before_checking_execution_speed>
            <max_columns_to_read>25</max_columns_to_read>
            <max_temporary_columns>100</max_temporary_columns>
            <max_temporary_non_const_columns>50</max_temporary_non_const_columns>
            <max_subquery_depth>2</max_subquery_depth>
            <max_pipeline_depth>25</max_pipeline_depth>
            <max_ast_depth>50</max_ast_depth>
            <max_ast_elements>100</max_ast_elements>
            <readonly>1</readonly>
        </query>
    </profiles>
</yandex>

同时建议设置 quota，减少大量读盘计算、LIMIT 少量数据返回的情况发生。

参考

  1. https://clickhouse.com/docs/en/faq/general/why-clickhouse-is-so-fast/ ↩︎

  2. https://benchmark.clickhouse.com ↩︎

  3. https://gcc.gnu.org/wiki/New_C_Parser ↩︎

  4. https://clang.llvm.org/features.html ↩︎

  5. https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-data-storage ↩︎

  6. https://kb.altinity.com/engines/mergetree-table-engine-family/pick-keys/#partition-by ↩︎

  7. https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#data-is-organized-into-granules-for-parallel-data-processing ↩︎

  8. https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#the-primary-index-has-one-entry-per-granule ↩︎

  9. https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#the-primary-index-is-used-for-selecting-granules ↩︎

  10. https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#primary-keys-and-indexes-in-queries ↩︎

  11. https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#mark-files-are-used-for-locating-granules ↩︎

  12. https://kb.altinity.com/engines/mergetree-table-engine-family/pick-keys/#how-to-pick-an-order-by--primary-key ↩︎

  13. https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#settings ↩︎

  14. https://clickhouse.com/docs/en/sql-reference/data-types/nullable/#storage-features ↩︎

  15. https://groups.google.com/g/clickhouse/c/AP2FbQ-uoj8 ↩︎

  16. https://kb.altinity.com/altinity-kb-schema-design/best-schema-for-storing-many-metrics-registered-from-the-single-source/ ↩︎

  17. https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/#server-settings-compression ↩︎

  18. http://www.vldb.org/pvldb/vol8/p1816-teller.pdf ↩︎

  19. https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/#server-mark-cache-size ↩︎

  20. https://clickhouse.com/docs/en/operations/caches/ ↩︎

  21. https://kb.altinity.com/altinity-kb-queries-and-syntax/altinity-kb-sample-by/#sample-emulation-via-where-condition ↩︎

  22. https://clickhouse.com/docs/en/about-us/performance/#performance-when-inserting-data ↩︎

  23. https://github.com/ClickHouse/ClickHouse/issues/9195#issuecomment-587500824 ↩︎

  24. https://github.com/ClickHouse/ClickHouse/issues/11546#issuecomment-660344896 ↩︎

  25. https://www.jianshu.com/p/c3a4cc528ce8 ↩︎

  26. https://github.com/ClickHouse/ClickHouse/issues/11783#issuecomment-647778852 ↩︎

  27. https://kb.altinity.com/altinity-kb-schema-design/preaggregations/ ↩︎