ClickHouse 查詢優化詳細介紹

来源:https://www.cnblogs.com/88223100/archive/2023/02/14/ClickHouse-query-optimization-in-detail.html
-Advertisement-
Play Games

你想要的 ClickHouse 優化，都在這裡。 ClickHouse 是 OLAP（Online analytical processing）資料庫，以速度見長[1]。ClickHouse 為什麼能這麼快？有兩點原因[2]： 架構優越 列式存儲 索引 數據壓縮 向量化執行 資源利用 關註底層細節... ...


 

你想要的 ClickHouse 優化，都在這裡。

ClickHouse 是 OLAP（Online analytical processing）資料庫，以速度見長[1]。ClickHouse 為什麼能這麼快？有兩點原因[2]：

  • 架構優越
    • 列式存儲
    • 索引
    • 數據壓縮
    • 向量化執行
    • 資源利用
  • 關註底層細節

但是，資料庫設計再優越也拯救不了錯誤的使用方式，本文以 MergeTree 引擎家族為例講解如何對查詢優化。

ClickHouse 查詢執行過程

⚠️ 本節基於 ClickHouse 22.3 版本分析

clickhouser-server啟動後會在 while 迴圈中等待請求，接收到查詢後會調用executeQueryImpl()行數構建 AST、優化並生成執行計劃 pipeline，最後在executeImpl()中多線程執行 DAG 獲取結果，這篇文章只關心 SQL 執行，省略掉網路交互部分，查詢執行流程如下圖所示：

圖片

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 等）。

  • 曾經 GCC 使用 yacc/bison 作為語法解析器，在 3.x 某個版本之後改為手寫遞歸下降語法分析[3]
  • clang 一直是手寫遞歸下降語法分析[4]

手寫語法分析比起語法分析工具有幾個優勢（當然要寫得好的情況）：

  • 性能更好。可以優化熱點路徑等
  • 診斷和錯誤恢復更清晰明瞭。手寫狀態機可以完全掌控系統狀態，錯誤處理更容易
  • 簡單。不需要掌握新語法

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，這裡只畫流程圖：

圖片

是否初始化 settings 優化 with 優化 joins 謂詞下推將 where 下推到 prewhere 是否要再次優化檢查 storage 許可權生成 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()函數是讀取所需列的階段。從代碼中可以看到它也做了很多的優化：

  • count()優化
  • 只有 LIMIT 情況的優化
  • quota限制

可以看到：

  1. limit 大部分情況下是計算完成後再執行，而 quota 是在讀取數據時執行的
  2. 加速的關鍵是減少讀入的數據量，也就是說善用索引
  3. 用count()、count(1)和count(*)，ClickHouse 都有優化，但不要count(any_field)

索引設計

索引是 ClickHouse 快速查詢最重要的一環，分為主鍵索引（sparse indexes）和跳錶索引（data skipping indexes）。在執行查詢時，索引命中順序如下圖所示：

圖片

Partition Key MinMax IndexPartitionPrimary Key Sparse IndexData 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]：

  • 一個(Replicated)MergeTree 的 partition 大概 1 ～ 300GB
    • Summing/ReplacingMergeTree 的 partition 大概 400MB ～ 40GB
  • 查詢時涉及儘量少 partition
  • 插入時最好只有 1 ～ 2 個分區
  • 一張表維持 100 個分區以內

Primary key index

主鍵是 ClickHouse 最重要的索引，沒有之一。好的主鍵應該能有效排除大量無關的數據 granules，減少磁碟讀取的位元組數。

先講幾個主鍵的背景知識：

  • 主鍵用於數據排序
    • ClickHouse 講數據按主鍵排序，再按index_granularity設置的大小（預設 8192）將數據分為一個個 granules[7]
    • 每個 granules 的第一行作為主鍵索引中的一個元素[8]
  • 查詢時在主鍵上使用二分查找跳過無關 granules[9]
  • 主鍵只能通過首碼命中索引[10]
  • 每一個 part 內的.bin文件存儲了 n 個 granules，用.mrk文件記錄每一個 granules 在.bin文件的地址偏移[11]
  • ClickHouse 會在後臺不斷合併同一個 partition 的不同 parts，直到大小/分佈達到“預期”

主鍵的選擇應該儘可能考慮周全，因為主鍵是無法修改的，只能建新表後數據遷移。

最佳實踐[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等等欄位。

優點：

  • 簡單
  • 只讀取所需要的列，非常高效
  • 每個指標、標記都可以有特殊類型
  • 適合密集記錄（所有預留欄位幾乎全用上）

缺點：

  • 添加欄位需要改變 schema
  • 預留欄位不能過多，最多 100 ～ 200 個
  • 如果使用很稀疏，會創建大量 sparse file 欄位
  • 需要標識“數據缺失”的情況（Null 或者預設值）
  • 讀取的列越多，需要讀取文件越多，IO 次數越多

arrays/nested/map 欄位

這是我們 ctree 功能正在使用的類型。將業務欄位塞入嵌套數據類型中，比如 array、nested struct 和 map。後文以 array 舉例：metric_array、tag_array。

優點：

  • 動態擴展
  • ClickHouse 有大量高效的相關處理函數，甚至可以針對 Array、Map 設置索引
  • 適合稀疏記錄（每行存儲少量值，儘管總基數很高）

缺點：

  • 只需要其中一個 metric/tag 時，需要將整個 array 全部讀入記憶體
  • 不通用，與其他系統交互時比較麻煩。比如 spark 使用 jdbc 時，嵌套類型無法支持比如 array(array(string))
  • 不通意義的值存儲在相同欄位，壓縮率變低
  • 需要不同類型的預留欄位時需要創建不同類型

總結

關於 Schema 設計這裡，讀者可以考慮 28 原則，理論上 80%查詢只會用到 20%的業務欄位，因此可以將使用頻率高的業務欄位平鋪，將使用頻率低的欄位放入嵌套結構中。

CODEC

CODEC 分為壓縮演算法 CODEC、存儲格式 CODEC 和加密 CODEC，一般可以組合一起使用。在 ClickHouse 中，未顯示指定 CODEC 的欄位都會被分配一個 DEFAULT 預設 CODEC LZ4（除非用戶修改 clickhouse 配置 compression 部分[17]）。

壓縮演算法 CODEC 的選擇是一個平衡板問題，更高的壓縮度可以有更少的 IO 但是更高的 CPU，更低的壓縮度有更多的 IO 但是更少的 CPU。這需要讀者根據部署機器配置自行選擇合適的壓縮演算法和壓縮等級。

這裡提供兩個判斷策略：

  • 存在索引的欄位可以設置更高的壓縮等級
  • 用於 where 條件的欄位應該設置更低壓縮等級

存儲格式 CODEC 主要是Delta、DoubleDelta、Gorilla、FPC和T64幾種。

  • Delta存儲行之間的變化值，適合變化較小且比較固定的列，比如時間戳。需要配合 ZSTD 使用
  • DoubleDelta存儲Delta的Delta。適合變化很慢的序列
  • Gorilla適合不怎麼變動的 integer、float 類型[18]
  • FPC適合於 float 類型，由於我們未使用 float 欄位這裡略過
  • T64存儲編碼範圍內最大、最小值，以轉為 64bit 存儲，適合較小的 integer 類型

擴展閱讀：

緩存

mark_cache_size可以調整.mrk文件的緩存大小，預設為 5GB。適當調大可以減少查詢時 IO 次數，有效降低磁碟壓力。[19]

  • 欄位越多，.mrk文件越大
  • index_granularity與.mrk文件大小成負相關

可以通過如下 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 asynchronous_metrics
WHERE metric = 'MarkCacheBytes'

以及 mrk 緩存具體緩存多少文件：

SELECT value
FROM asynchronous_metrics
WHERE metric = 'MarkCacheFiles'

除此之外，ClickHouse 還可以調整uncompressed_cache緩存一定量原始數據於記憶體中。[20]但是這個緩存只對大量短查詢有效，對於 OLAP 來說，查詢千奇百怪，不太建議調整這個配置。

業務優化

到了最難的部分，由於接下來的部分和不同業務息息相關，為了講解我們業務上的優化，我先介紹下我們業務情況：

QAPM 主打應用性能監控，主要分為指標、個例兩張表。個例表包含更多基礎欄位，一般用戶展示；指標表主要用於聚合計算。

首先確定主鍵，毋庸置疑的前兩個一定是

  • app_id。放首位，因為可能存在同一個產品不同功能聯動的情況，比如會話分析
  • category。放第二位，因為功能之間獨立，大量查詢只涉及單功能

指標沒有特征鍵值，因此只添加處理時間作為第三個主鍵。

對於指標表，設置的主鍵為：app_id, category, entrance_time

個例存在特征 feature，由於：

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

將特征的 md5 增加到主鍵中，用於加速查詢、提高壓縮率。但是這裡有兩個方向：

  • 若 feature_md5 是高基數、大量長尾的欄位
    • 設置的主鍵為：app_id, category, intDiv(entrance_time, 3600000), feature_md5
  • 若 feature_md5 基數可以降低到千、萬量級
    • 設置的主鍵為：app_id, category, feature_md5, entrance_time
分區鍵設置為`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]：

  • 數據直接插入MergeTree表（不能有 Buffer 表）
  • 數據只插入一個 partition（註意前文提到的 partition 和 part 的區別）
  • 對於 INSERT FORMAT
    • 插入行數少於max_insert_block_size（預設 1048545）
    • 關閉並行格式化input_format_parallel_parsing=0
  • 對於 INSERT SELECT
    • 插入行數少於max_block_size
  • 小 block 被合併到合適的 block 大小min_insert_block_size_rows and min_insert_block_size_bytes
  • MergeTree表不包含物化視圖

這裡貼一下我們生產的配置（users.xml）。

經過統計，個例表每行大約 2KB，指標表每行大約 100B（未壓縮）。

設置min_insert_block_size_rows為 10000000，指標會先滿足這個條件，大概一個 block 原始大小 1GB。設置min_insert_block_size_bytes為 4096000000，個例會先滿足這個條件，大概一個 block 原始大小 1G，約 1024000 行。

這三個配置項是客戶端配置，需要在插入的 session 中設置，而不是在那幾個.xml中配置。

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 可以配置，作用如下圖所示：

圖片

所謂讀寫分離也就是將 Shard 分為兩半，一半隻用於查詢，只要讓分散式表查詢都導入到 Shard1 即可（在users.xml中配置load_balancing為first_or_random）；一半用於寫入，插入的程式手動控制插入 Shard2 的節點，由 ClickHouse 的 ReplicatedMergeTree 不同 Shard 數據依靠 zookeeper 自動同步的策略將數據同步到 Shard1。[24]

這種策略有天然的缺陷：

  • 寫的那半 Shard 持續有一定量（不會很高）的資源消耗用於寫入
  • 讀的那半 Shard 會有資源消耗用於同步寫入（由於不用處理，會比直接寫入的情況資源消耗更低），但是讀請求會導致資源消耗突增
  • 併發增加時性能不如混合情況，因為讀寫分離相當於將讀資源砍半


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • nRF5340開發——晶振篇
    Vim 簡介{#vim-簡介} Vim 是 Linux 系統上的最著名的文本/ 代碼編輯器,也是早年的 Vi編輯器的加強版，而 gVim 則是其 Windows 版。它的最大特色是完全使用鍵盤命令進行編輯，脫離了滑鼠操作雖然使得入門變得困難,但上手之後鍵盤流的各種巧妙組合操作卻能帶來極為大幅的效率提 ...
  • redis georadius源碼分析與性能優化
    原文地址: https://blog.fanscore.cn/a/51/ 背景 最近接到一個需求，開發中使用了redis georadius命令取附近給定距離內的點。完工後對服務進行壓測後發現georadius的性能比預期要差，因此我分析了georadius的源碼，並對原始的實現方案進行了優化，總結 ...
  • sql語氣查詢去重的兩種方法、sql的模糊查詢、sql中的日期函數、mysql字元串截取之substring_index、sql中字元串的切割、截取、刪除、替換、計算字元串長度函數、返回某個日期之間的解法
    sql語氣查詢去重的兩種方法 以下兩種都可以進行去重查詢，區別是： 用distinct去重，只能查詢到去重的屬性那一列，無法查詢其他欄位 用group by分組查詢，可以根據需求查詢對應的其他欄位，推薦用group by 第一種：使用distinct(關鍵詞distinct用於返回唯一不同的值) s ...
  • MRS+LakeFormation：打造一站式湖倉，釋放數據價值
    摘要：華為LakeFormation是企業級的一站式湖倉構建服務。 本文分享自華為雲社區《華為雲MRS支持LakeFormation能力,打造一站式湖倉，釋放數據價值】》，作者：breakDawn 。 1 背景 1.1 數倉和數據湖的概念 數據分析技術在2010~2019年間，以湖倉兩層架構技術作為 ...
  • 新增或修改mysql表欄位--可重覆執行腳本
    想必大家在版本更新迭代中，不免需要對資料庫表的欄位進行修改欄位類型或者長度的情況， 如果是修改存儲過程或者自定義函數, 那還是很好改的, 不用擔心表功能收到影響. 如果是改欄位呢? 首先要判斷欄位是不是已經在系統裡面, 沒有的話, 需要新增上去, 有的話, 需不需要修改? 其次, 腳本如果報錯, 是 ...
  • 倒帶ChunJun，同心前行｜2022年度回顧&2023年共建規劃
    ChunJun是一個開始於2018年的批流一體數據集成框架項目，原名FlinkX。2022年2月22日，在FlinkX進行初版開源的整整四年後，技術團隊決定對FlinkX進行整體升級，並更名為ChunJun，希望為大家真正提供一個穩定、高效、易用的批流一體的數據集成框架。 2022年的ChunJun ...
  • GaussDB(DWS)性能調優：indexscan導致的性能問題識別與優化
    摘要：通常跑批加工場景下，都是大數量做關聯操作，通常不建議使用索引。有些時候因為計劃誤判導致使用索引的可能會導致嚴重的性能問題。本文從一個典型的索引導致性能的場景重發，剖析此類問題的特征，定位方法和解決方法 本文分享自華為雲社區《GaussDB(DWS)性能調優：indexscan導致的性能問題識別 ...
  • Redis避坑指南：為什麼要有分散式鎖？
    JUC提供的鎖機制，可以保證在同一個JVM進程中同一時刻只有一個線程執行操作邏輯； 多服務多節點的情況下，就意味著有多個JVM進程，要做到這樣，就需要有一個中間人； 分散式鎖就是用來保證在同一時刻，僅有一個JVM進程中的一個線程在執行操作邏輯； 換句話說，JUC的鎖和分散式鎖都是一種保護系統資... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類