ClickHouse 执行计划与优化策略解析

一、执行计划分析方法

1. EXPLAIN 命令族
ClickHouse 提供多维度执行计划分析工具，核心语法包括：

• EXPLAIN PLAN：默认选项，展示查询执行流程（如数据读取、过滤、聚合等）。

  EXPLAIN SELECT count() FROM hits WHERE EventDate='2023-01-01';
  

  输出示例：

  ┌─explain───────────────────────────────────────────────────────┐
  │ Expression ((Projection + Before ORDER BY))                 │
  │ SettingQuotaAndLimits (Set limits and quota after reading) │
  │ ReadFromMergeTree (table: hits, index: EventDate)            │
  └───────────────────────────────────────────────────────────────┘
  

  通过 header=1、description=1、actions=1 等参数可查看步骤详情、索引使用情况等。

• EXPLAIN SYNTAX：优化语法并返回优化后的 SQL。例如，三元运算符优化：

  EXPLAIN SYNTAX SELECT IF(age > 18, 'Adult', 'Child') FROM users;
  

  优化后可能移除冗余计算。

• EXPLAIN AST：查看抽象语法树（AST），分析查询结构。

  EXPLAIN AST SELECT arrayJoin([1, 2, 3]) FROM system.numbers LIMIT 10;
  

2. 执行计划关键节点

• ReadFromMergeTree：从 MergeTree 表读取数据，索引使用情况通过 EXPLAIN indexes=1 查看。
• Filter：基于 WHERE 条件过滤行。
• Aggregate：执行 GROUP BY 聚合。
• Sort：排序操作（成本较高，需优化）。
• Join：表连接（需注意算法选择和内存限制）。

3. 分布式查询分析
使用 EXPLAIN distributed=1 查看数据在节点间的分布和传输：

EXPLAIN distributed=1 SELECT toDate(EventTime) AS dt, count() 
FROM distributed_hits GROUP BY dt ORDER BY dt;

关注数据传输量（Data Transfer）和本地聚合（Local Aggregation）。

二、优化策略与实践

1. 数据模型优化

• 选择合适的数据类型：

  • 避免使用 String 存储日期/时间，优先用 DateTime、Date 类型。例如：

    -- 低效：字符串存储需转换
    CREATE TABLE t_string (create_time String) ENGINE=MergeTree() ORDER BY toDate(create_time);
    -- 高效：直接使用日期类型
    CREATE TABLE t_date (create_time Date) ENGINE=MergeTree() ORDER BY create_time;
    

  • 避免 Nullable 列，用默认值或业务无效值（如 -1）表示空值。

• 分区与索引设计：

  • 按天分区（PARTITION BY toYYYYMM(event_date)），控制分区大小（10-30 个分区/亿级数据）。
  • 主键（ORDER BY）包含高频查询列，基数大的列慎用索引。
  • 跳数索引（Skip Index）加速范围查询：

    CREATE TABLE hits (
        event_date Date,
        user_id UInt32,
        url String
    ) ENGINE=MergeTree()
    PARTITION BY toYYYYMM(event_date)
    ORDER BY (event_date, user_id)
    SETTINGS index_granularity=8192;
    -- 添加跳数索引
    CREATE INDEX idx_url ON hits (url) TYPE minmax GRANULARITY 4;
    

2. 查询优化技巧

• 使用 Prewhere 替代 Where：
  MergeTree 系列引擎支持 Prewhere，先加载索引列过滤数据，再读取所需字段，减少 IO。

  -- 低效：WHERE 先读取所有列再过滤
  SELECT url FROM hits WHERE event_date='2023-01-01' AND url LIKE '%clickhouse%';
  -- 高效：PREWHERE 先过滤再加载
  SELECT url FROM hits PREWHERE event_date='2023-01-01' AND url LIKE '%clickhouse%';
  

• 避免全表扫描：

  • 千万级数据查询时，ORDER BY 需搭配 WHERE 和 LIMIT。
  • 使用 SAMPLE 进行数据采样（如 SAMPLE 0.1 查询 10%数据）。

• 优化 JOIN 操作：

  • 小表在右，避免右表过大导致内存溢出：

    -- 低效：大表在右可能内存不足
    SELECT * FROM large_table JOIN small_table ON large_table.id=small_table.id;
    -- 高效：小表在右
    SELECT * FROM small_table JOIN large_table ON small_table.id=large_table.id;
    

  • 分布式表使用 GLOBAL 选项减少数据传输：

    SELECT * FROM global_table1 ALL INNER JOIN global_table2 ON table1.id=table2.id;
    

• 物化视图预聚合：
  创建物化视图自动存储预计算结果，加速查询：

  CREATE TABLE orders_raw (
      event_date Date,
      user_id UInt32,
      order_count UInt32
  ) ENGINE=MergeTree() ORDER BY (event_date, user_id);
  
  CREATE MATERIALIZED VIEW orders_summing TO orders_summed
  AS SELECT event_date, sum(order_count) AS total_orders
     FROM orders_raw GROUP BY event_date;
  

3. 写入与配置优化

• 批量写入：

  • 避免单条或小批量插入，建议每次写入 2W-5W 条数据，速率控制在每秒 2-3 次。
  • 使用 INSERT INTO ... FORMAT ... 批量导入。

• 资源限制：

  • 内存：max_memory_usage 设置为物理内存的 80%-90%。
  • 并发：max_concurrent_queries 默认 100，根据 CPU 核心数调整（线程池建议为 CPU 核心数的 2 倍）。
  • 存储：挂载虚拟卷组（多块物理磁盘绑定）提升 IO 性能。

4. 语法优化规则

• COUNT 优化：
  COUNT() 或 COUNT(*) 直接查询 system.tables 的 total_rows，无需扫描数据：

  EXPLAIN SELECT count() FROM hits;
  -- 输出可能包含：Optimized trivial count
  

• 谓词下推：
  HAVING 条件提前到 WHERE 过滤：

  -- 优化前
  SELECT user_id, sum(score) FROM scores GROUP BY user_id HAVING sum(score) > 100;
  -- 优化后（可能被改写为 WHERE 过滤）
  SELECT user_id, sum(score) FROM scores WHERE score > 0 GROUP BY user_id HAVING sum(score) > 100;
  

• 聚合计算外推：
  sum(col * 2) 优化为 sum(col) * 2：

  EXPLAIN SELECT sum(user_id * 2) FROM users;
  -- 优化后
  SELECT sum(user_id) * 2 FROM users;
  

三、常见性能问题与解决方案

1. 索引未使用

• 问题：查询条件中使用函数导致索引失效。

  -- 低效：索引列上使用函数
  SELECT count() FROM logs WHERE toUInt32(user_id)=12345;
  -- 高效：改写为直接比较
  SELECT count() FROM logs WHERE user_id='12345';
  

• 解决：避免索引列函数操作，或创建函数索引（ClickHouse 21.12+）：

  CREATE INDEX idx_uid ON logs (toUInt32(user_id)) TYPE minmax GRANULARITY 8192;
  

2. 内存不足

• 问题：大表 JOIN 或聚合时内存溢出。
• 解决：
  • 调整 max_bytes_before_external_group_by 和 max_bytes_before_external_sort，将溢出数据写入磁盘（性能下降）。
  • 优化查询，减少中间结果集大小。

3. 分布式查询性能差

• 问题：数据倾斜或网络传输过大。
• 解决：
  • 使用 DISTRIBUTED_PRODUCT_MODE 控制 JOIN 行为（如 local 或 global）。
  • 检查分片键是否均匀分布数据。

四、监控与诊断工具

• 系统表查询：

  -- 查看当前运行的查询
  SELECT * FROM system.processes;
  -- 查看查询性能指标
  SELECT query_id, duration_ms, memory_usage FROM system.query_log ORDER BY event_time DESC LIMIT 10;
  

• 慢查询熔断：
  配置 query_profiler_real_time_period_ns 和 max_execution_time 限制慢查询资源占用。

五、优化案例

案例 1：订单表聚合查询优化

• 原始表（MergeTree）：

  CREATE TABLE orders_raw (
      event_date Date,
      user_id UInt32,
      order_count UInt32
  ) ENGINE=MergeTree() ORDER BY (event_date, user_id);
  

  每次查询需执行 SUM(order_count)，耗时 1.2 秒（1 亿行数据）。

• 优化表（SummingMergeTree）：

  CREATE TABLE orders_summed (
      event_date Date,
      order_count UInt32
  ) ENGINE=SummingMergeTree(order_count) ORDER BY event_date;
  

  通过物化视图同步数据：

  CREATE MATERIALIZED VIEW orders_summed_mv TO orders_summed
  AS SELECT event_date, sum(order_count) AS order_count
     FROM orders_raw GROUP BY event_date;
  

  优化后查询耗时 0.1 秒。

案例 2：分布式表 JOIN 优化

• 问题：大表 JOIN 导致内存溢出。
• 解决：
  • 调整 JOIN 顺序，小表在右。
  • 使用 GLOBAL IN 替代 JOIN：

    -- 低效
    SELECT * FROM large_table JOIN small_table ON large_table.id=small_table.id;
    -- 高效
    SELECT * FROM large_table WHERE id IN (SELECT id FROM small_table);
    

六、总结

• 执行计划分析：利用 EXPLAIN 定位性能瓶颈，关注索引使用、数据扫描范围和操作顺序。
• 数据模型优化：选择合适的数据类型、分区策略和索引，避免 Nullable 列。
• 查询优化：使用 Prewhere、物化视图、批量写入和分布式优化技巧。
• 资源配置：根据业务负载调整内存、并发和存储参数。
• 监控诊断：通过系统表和慢查询日志持续优化。