ClickHouse查询执行与优化

简介: 本文详细介绍了SQL语法扩展、执行计划分析及优化策略,涵盖特殊函数与子句(如`WITH`、`ANY JOIN`)、聚合函数扩展(如`uniqCombined`、`quantileTDigest`)以及执行计划优化技巧。同时深入解析了ClickHouse的索引原理,包括主键索引和跳数索引的工作机制与优化方法。针对查询优化,文章提供了过滤条件下推、分布式查询优化和数据预聚合等策略,并探讨了资源管理与并发控制的核心参数(如`max_memory_usage`、`max_threads`)及队列优先级调度机制,助力高效使用ClickHouse。

SQL语法扩展与执行计划分析

特殊函数与子句

  • WITH子句:定义临时表达式(CTE),复用中间结果。
    • WITH tmp AS (SELECT ...) SELECT * FROM tmp
  • ANY修饰符:在JOIN时仅保留第一个匹配的行(避免笛卡尔积爆炸)。
    • SELECT ... FROM table1 ANY LEFT JOIN table2 USING (key)
  • GLOBAL IN/GLOBAL JOIN:在分布式查询中,将右表广播到所有节点执行。
    • SELECT ... WHERE id GLOBAL IN (SELECT id FROM remote_table)
  • LIMIT n BY expr:按expr分组后,每组取前n条数据(类似窗口函数ROW_NUMBER)。
    • SELECT * FROM table LIMIT 3 BY date ORDER BY time DESC
  • SAMPLE子句:快速采样数据(基于哈希或分块)。
    • SELECT * FROM table LIMIT 3 BY date ORDER BY time DESC
  • ARRAY JOIN:展开数组或嵌套结构为多行。
    • SELECT * FROM table ARRAY JOIN tags

聚合函数扩展

  • uniqCombined:近似计算UV(误差率<1%),内存占用低。
    • SELECT uniqCombined(user_id) FROM logs
  • quantileTDigest:近似计算分位数(如P99延迟)。
    • SELECT quantileTDigest(0.99)(latency) FROM requests
  • sumMap/minMap:对Map类型(Key-Value)的聚合计算。
    • SELECT sumMap(keys, values) FROM metrics
  • anyLast:返回最后一次出现的非NULL值(适合状态跟踪)。
    • SELECT anyLast(status) FROM events GROUP BY user_id

执行计划优化策略

  • 主键索引:确保WHERE条件命中ORDER BY键的前缀。
  • 跳数索引:对高基数列使用SETMINMAX索引加速过滤。
  • 避免全表扫描:使用分区裁剪(PARTITION BY)和索引过滤;避免在WHERE中对非索引列进行复杂计算。
  • 聚合优化

    • 预聚合:使用SummingMergeTreeAggregatingMergeTree减少实时计算。
    • Combiner优化:在分布式查询中,优先在本地节点预聚合。
  • JOIN优化

    • ANY JOIN:右表存在重复键,只需保留第一个匹配值。
    • GLOBAL JOIN:右表数据量小,广播到所有节点避免Shuffle。
    • DISTRIBUTED JOIN:右表数据量大,按Join键分布数据。
  • 资源控制:设置内存限制max_memory_usage;调整线程数max_threadsbackground_pool_size

索引原理

索引类型与适用场景

  • 主键索引:基于排序键(ORDER BY)的粗粒度索引,定位数据块(Granule)范围,如时间范围过滤。
  • 跳数索引:基于列值统计的细粒度索引,跳过不满足条件的数据块,如高基数列的等值或范围过滤。

主键索引(Primary Key)

  • 数据排序:MergeTree表的数据按ORDER BY指定的列严格排序后存储在磁盘,形成​​有序数据块(Granule)​​默认包含8192行数据。
  • 索引结构:主键索引记录每个Granule中​​第一个行的排序键值​​,并持久化为primary.idx文件。索引条目与Granule一一对应。
  • 工作流程

    • 查询条件解析:优化器提取WHERE条件中涉及主键的过滤表达式(如date >= '2023-01-01')。
    • 索引匹配:通过二分查找定位到满足条件的Granule范围。
    • 数据读取:仅加载相关Granule的数据文件(.bin),跳过无关数据块。
  • 优化策略

    • 前缀匹配原则:主键索引仅对ORDER BY键的​​前缀列​​有效。若查询条件不包含前缀列,索引将失效。
    • 合理选择排序键:将高频过滤条件(如时间列)放在ORDER BY最左侧。

跳数索引(Data Skipping Indexes)

  • 数据块级统计:跳数索引为每个Granule生成统计信息(如最大/小值),记录在skp_idx_[index_name].idx.mrk文件中。
  • 跳过机制:查询时根据索引统计信息,跳过不满足条件的Granule,减少数据读取量。
  • 索引类型
    • minmax:记录Granule中列的最小值/最大值,适用于范围查询(如数值列、日期列)。
    • set(max_rows):记录Granule中列的取值集合(最多max_rows个),适用于低基数枚举列(如状态码)。
    • bloom_filter:基于布隆过滤器判断列值是否存在,适用于高基数列的等值查询(如UserID)。
    • ngrambf_v1:支持文本子串的布隆过滤器,适用于模糊查询(如LIKE '%error%')。
    • tokenbf_v1:基于分词后的布隆过滤器,适用于分词后的文本搜索(如日志关键词)。

索引的存储与维护

  • 存储结构

    • 主键索引:每个MergeTree表对应一个primary.idx文件,按Granule顺序存储排序键的起始值。

    • 跳数索引

      1..idx:存储索引数据(如minmax值、布隆过滤器位数组)。

      2..mrk:记录索引条目与数据文件的偏移量映射。

  • 数据写入与合并

    • 写入阶段:数据插入时,每个新生成的Part(数据部分)独立维护其索引文件。
    • 合并阶段(Merge):多个Parts合并时,重新生成合并后的索引文件,确保索引的连续性。

索引优化策略

  • 主键设计原则

    • 高频过滤列前置ORDER BY的第一列应为最常用的范围过滤条件(如时间列)
    • 避免过长主键:主键列过多会增加索引文件大小,降低查询效率。
  • 跳数索引选择

    • 低基数列setminmax索引。

    • 高基数等值查询bloom_filter

    • 文本搜索ngrambf_v1tokenbf_v1

  • 参数调优

    • index_granularity:减小此值(如4096)可提升索引精度,但会增加索引文件大小。
    • GRANULARITY:跳数索引的粒度需平衡精度与存储开销。较小的粒度(如2)过滤更精确,但索引条目更多。

查询优化的核心策略

执行计划分析与优化

  • 执行计划分析

    • ReadFromMergeTree:检查是否命中主键索引或跳数索引(如显示Index ... used)。

    • Aggregating:判断是否启用并行聚合(ParallelAggregating)。

    • Sorting:是否使用外部排序(ExternalSort)或内存排序(InMemorySort)。

  • 执行优化

    • 并行化:设置parallel_aggregation_min_rows(默认100000)触发多线程聚合。

    • 索引覆盖:确保WHERE条件匹配主键前缀或跳数索引。

查询优化

  • 过滤条件下推:将过滤条件尽可能提前,减少中间结果集。
  • ClickHouse语法扩展:LIMIT BY替代窗口函数、ANY JOIN减少Shuffle。
  • 分布式查询优化

    • GLOBAL修饰符:小表右连接时使用GLOBAL INGLOBAL JOIN,避免重复计算。
    • 本地预聚合:分布式聚合前启用distributed_aggregation_memory_efficient
  • 数据预聚合与存储优化

    • 物化视图加速:预计算高频指标;直接查询物化视图,避免全量扫描
    • 冷热数据分层:按时间自动迁移冷数据到廉价存储(TTL策略)。

分布式场景优化

  • 分片键选择
    • 均匀分布:选择高基数列(如user_id)作为分片键,避免数据倾斜。
    • 本地性优化:按业务属性分片(如region),减少跨节点查询。
  • 副本与一致性
    • 异步复制:使用ReplicatedMergeTree实现副本同步,设置max_replicated_queries限制并发。
    • 最终一致性:查询时添加SET allow_experimental_consistency=1容忍副本延迟。

资源管理与并发控制

资源管理:核心参数与策略

  • 内存管理

    • max_memory_usage:单个查询最大内存使用量(默认10GB,推荐根据节点内存调整)。
    • max_bytes_before_external_sort:触发外部排序的阈值,超过后使用磁盘暂存数据(默认1GB,推荐设为总内存的50%)。
    • max_memory_usage_for_all_queries:所有查询的总内存限制(默认0,表示无限制,推荐设为物理内存的80%)。
    • memory_overcommit_ratio:允许内存超卖的比例(默认0,不允许超卖)。
  • CPU管理

    • max_threads:单查询最大线程数(默认物理CPU核数,推荐设为逻辑CPU核数的75%)。
    • background_pool_size:后台任务(合并、删除)的线程池大小,推荐生产环境设为16
    • load_balancing:分布式查询的分片调度策略(随机/就近优先)。
  • 磁盘与网络

    • max_concurrent_queries:最大默认并发查询数(默认100),推荐根据磁盘IOPS进行调整。
    • max_bytes_to_read:单查询最大读取数据量(默认0,无限制),需要限制大查询。
    • network_bandwidth:网络带宽限制(默认0,无限制),推荐网络带宽限制(默认0,无限制)。

并发控制:队列与优先级s

  • 查询队列机制

    • max_running_queries:同时运行的查询数(默认100),超出后新查询排队。

    • max_waiting_queries:最大等待查询数(默认1000),超出后拒绝新查询。

  • 优先级调度:通过priority参数设置查询优先级(范围1-10,默认0)。

  • 资源组(Resource Groups):配置资源组,实现物理资源隔离。
目录
打赏
0
7
9
1
19
分享
相关文章
|
6月前
|
优化ClickHouse查询性能:最佳实践与调优技巧
【10月更文挑战第26天】在大数据分析领域,ClickHouse 以其卓越的查询性能和高效的列式存储机制受到了广泛的关注。作为一名已经有一定 ClickHouse 使用经验的开发者,我深知在实际应用中,合理的表设计、索引优化以及查询优化对于提升 ClickHouse 性能的重要性。本文将结合我的实践经验,分享一些有效的优化策略。
438 3
ClickHouse大规模数据导入优化:批处理与并行处理
【10月更文挑战第27天】在数据驱动的时代,高效的数据导入和处理能力是企业竞争力的重要组成部分。作为一位数据工程师,我在实际工作中经常遇到需要将大量数据导入ClickHouse的需求。ClickHouse是一款高性能的列式数据库系统,非常适合进行大规模数据的分析和查询。然而,如何优化ClickHouse的数据导入过程,提高导入的效率和速度,是我们面临的一个重要挑战。本文将从我个人的角度出发,详细介绍如何通过批处理、并行处理和数据预处理等技术优化ClickHouse的数据导入过程。
410 0
【DDIA笔记】【ch2】 数据模型和查询语言 -- 多对一和多对多
【6月更文挑战第7天】该文探讨数据模型,比较了“多对一”和“多对多”关系。通过使用ID而不是纯文本(如region_id代替&quot;Greater Seattle Area&quot;),可以实现统一、避免歧义、简化修改、支持本地化及优化搜索。在数据库设计中,需权衡冗余和范式。文档型数据库适合一对多但处理多对多复杂,若无Join,需应用程序处理。关系型数据库则通过外键和JOIN处理这些关系。文章还提及文档模型与70年代层次模型的相似性,层次模型以树形结构限制了多对多关系处理。为克服层次模型局限,发展出了关系模型和网状模型。
96 6
【DDIA笔记】【ch2】 数据模型和查询语言 -- 文档模型中Schema的灵活性
【6月更文挑战第8天】网状模型是层次模型的扩展,允许节点有多重父节点,但导航复杂,需要预知数据库结构。关系模型将数据组织为元组和关系,强调声明式查询,解耦查询语句与执行路径,简化了访问并通过查询优化器提高效率。文档型数据库适合树形结构数据,提供弱模式灵活性,但在Join支持和访问局部性上不如关系型。关系型数据库通过外键和Join处理多对多关系,适合高度关联数据。文档型数据库的模式灵活性体现在schema-on-read,写入时不校验,读取时解析,牺牲性能换取灵活性。适用于不同类型或结构变化的数据场景。
89 0
|
11月前
|
【DDIA笔记】【ch2】 数据模型和查询语言 -- 关系模型与文档模型
【6月更文挑战第6天】关系模型是主流数据库模型,以二维表形式展示数据,支持关系算子。分为事务型、分析型和混合型。尽管有其他模型挑战,如网状和层次模型,但关系模型仍占主导。然而,随着大数据增长和NoSQL的出现(如MongoDB、Redis),强调伸缩性、专业化查询和表达力,关系模型的局限性显现。面向对象编程与SQL的不匹配导致“阻抗不匹配”问题,ORM框架缓解但未完全解决。文档模型(如JSON)提供更自然的嵌套结构,适合表示复杂关系,具备模式灵活性和更好的数据局部性。
88 0
常用ClickHouse问题诊断查询
Clickhouse是一个性能强大的OLAP数据库,在实际使用中会遇到各种各样的问题,同时也有很多可以调优的地方。诊断调优所用到的SQL查询必不可少。本文就是一个ClickHouse日常运维的常用SQL查询手册。这个手册本人就在用,非常实用。
74528 48
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等