完整教程：clickhouse 学习总结

clickhouse 历史发展

20.5.3 开始支持多线程

20.6.3 支持explain

mysql 20.8 实时同步mysql

‌一、早期版本阶段（1.1.x系列）‌

  • ‌版本范围‌：1.1.54245（2017-07）→ 1.1.54394（2018-07）12
  • ‌核心特性‌：
    • 首次开源发布，奠定‌列式存储引擎‌基础架构3；
    • 支持基础‌MergeTree引擎‌，搭建分区和排序能力；
    • 献出简单SQL查询接口，初步支持聚合函数。

‌二、版本命名变革阶段（18.x → 19.x）‌

  • ‌版本范围‌：18.1.0（2018-07）→ 19.17.6.36（2019-12）12
  • ‌重大变革‌：
    • ‌版本号重构‌：采用 Year.Major.Minor.patch 格式（例：18.1.0表示2018年首个稳定版）2；
    • ‌分布式架构增强‌：
      • 引入ReplicatedMergeTree引擎，通过ZooKeeper实现资料复制38；
      • 支持Distributed引擎，原生跨节点查询分发3；
    • ‌存储引擎扩展‌：新增Kafka、MySQL等外部表引擎，支持流式内容接入3。

‌三、现代LTS版本阶段（20.x及以后）‌

‌1. 版本20.x（2020年起）‌
  • ‌核心特性‌：
    • ‌窗口函数协助‌：实现ROW_NUMBER()、RANK()等分析函数，增强OLAP能力4；
    • ‌资源隔离‌：引入资源队列（Resource Queues），限制查询并发资源8。
‌2. 版本22.8 LTS（2022年）‌
  • ‌里程碑特性‌：
    • ‌轻量级DELETE/UPDATE‌：
      • 支持异步删除（DELETE WHERE）和更新（ALTER TABLE UPDATE），突破传统批量写入限制5；
    • ‌日期类型扩展‌：
      • Date32和DateTime64支持1900-2299年范围（原仅1925-2283年）5；
      • 时间精度提升至微秒级（最高8位）5。
‌3. 版本23.x → 24.x‌
  • ‌关键优化‌：
    • ‌查询优化器升级‌：增强JOIN重排序和子查询解关联能力7；
    • ‌并行哈希连接（Parallel Hash Join）‌：大幅提升多表关联性能7。
‌4. 版本25.2（2025年）‌
  • ‌突破性改进‌：
    • ‌并行哈希连接性能强化‌：优化构建（Build）阶段线程争用，降低阻塞7；
    • ‌Parquet布隆过滤器支持‌：提升过滤查询效率7；
    • ‌数据库备份引擎‌：原生支持分布式备份（Backup引擎）7。

️ 一、表引擎分类

1. ‌MergeTree 系列（核心生产引擎）‌
  • ‌MergeTree‌：支撑主键索引、数据分区（PARTITION BY）、数据排序（ORDER BY），适合大规模数据分析15。
  • ‌ReplacingMergeTree‌：自动去重相同排序键的数据（保留最新版本）16。
  • ‌SummingMergeTree‌：预聚合数值列，加速 SUM 类查询16。
  • ‌Distributed‌：实现跨服务器分片与副本管理，支持分布式查询19。
2. ‌日志引擎（轻量级场景）‌
  • ‌TinyLog‌：
    • 每列独立存储为压缩文件，追加写入
    • 不支持索引、并发读写和原子操作，适用小表（≤100万行）311。
  • ‌Log‌：
    • 与 TinyLog 类似，但添加了 .mark 文件拥护并行读
    • 仍不支持索引和高效更新11。
3. ‌集成引擎（外部数据源）‌
  • ‌Hive‌：直接查询 HDFS 上的 Hive 表，支持文本/ORC/Parquet 格式4。
  • ‌Memory‌：数据全内存存储，重启丢失，适合临时数据处理10。
4. ‌其他引擎‌
  • ‌Null‌：写入信息自动丢弃，常用于测试9。
  • ‌Buffer‌：内存缓冲后异步写入目标表9。

⚠️ ‌引擎选择建议‌：

  • OLAP 场景优先采用 ‌MergeTree 系列‌69
  • 避免对大数据集使用 Memory 引擎（内存限制易崩溃）10

二、数据类型体系

1. ‌基础类型‌
类别类型示例说明
‌数值类型‌Int8/16/32/64带符号整数（如 Int32 范围：-2147483648 ~ 2147483647）112
UInt8/16/32/64无符号整数（如 UInt16 范围：0 ~ 65535）812
Float32/64浮点数（避免精确计算，可能丢失精度）112
Decimal(P, S)高精度小数（P 总位数，S 小数位）18
‌时间类型‌Date日期（YYYY-MM-DD）18
DateTime时间戳（精确到秒）1
DateTime64高精度时间戳（可至亚秒级）18
‌字符串类型‌String任意长度文本（替代 VARCHAR/BLOB）812
FixedString(N)定长字符串（N 为字节数，查询性能更优）8
LowCardinality(String)低基数枚举优化，减少存储提升查询速度7
2. ‌复合类型‌
  • ‌数组‌：Array(T)（如 Array(Int32)），元素类型需一致8
  • ‌Nullable‌：允许字段为 NULL（但影响性能，慎用）28
  • ‌UUID‌：128 位唯一标识符，经过 generateUUIDv4() 生成8

‌最佳实践‌：

  • 优先选择明确类型（如用 Int32 而非 Nullable(Int32)）2
  • 时间字段使用 DateTime 或 DateTime64 而非字符串存储1
  • 低基数字符串列转换为 LowCardinality(String) 优化性能7

功能对比摘要

‌特性‌MergeTree 系列日志引擎（TinyLog/Log）Memory 引擎
‌索引支持‌✅ 主键索引❌❌
‌并发读写‌✅❌（写入阻塞查询）3⚠️ 受限10
‌数据持久化‌✅ 磁盘存储✅ 磁盘存储❌ 重启丢失
‌适用场景‌大数据分析、高频查询一次性写入小表11临时计算中间结果10

 一、时间日期函数‌

  • SELECT dateDiff('day', '2025-06-01', '2025-06-06')‌

  • SELECT (toUnixTimestamp('2025-06-06') - toUnixTimestamp('2025-06-01')) / 86400 -- 86400=24*3600

  • ‌动态计算（如距离当前日期的天数）‌

    SELECT dateDiff('day', today(), toDate('2025-12-31')) -- 计算今天到年底的天数:‌SELECT toDateTime(now(), 'Asia/Shanghai') -- 指定时区转换:ml-citation{ref="7" SELECT toDate(now(), 'UTC')

  1. ‌基础转换‌

    • toDate()：字符串/时间戳 → 日期（YYYY-MM-DD）

      SELECT toDate('2025-06-06 12:34:56') → 2025-06-06 :ml-citation{ref="11"

    • toDateTime()：字符串 → 时间戳（精确到秒）

      SELECT toDateTime('2025-06-06 12:34:56') → 2025-06-06 12:34:56 

    • toDateTime64()：高精度时间戳（支持毫秒/微秒）

      SELECT toDateTime64('2025-06-06 12:34:56.789', 3) → 2025-06-06 12:34:56.789 

  2. ‌提取时间分量‌

    • toYear()/toMonth()/toDayOfMonth()：提取年/月/日

      SELECT toMonth(now()) → 6 

    • toHour()/toMinute()/toSecond()：提取时/分/秒

      SELECT toMinute(now()) → 30 

  3. ‌时区转换‌

    • toTimeZone()：调整时区

      SELECT toTimeZone(now(), 'Asia/Shanghai') 

二、字符串函数

  1. ‌基础操作‌

    • length()：字节长度（非字符数）

      SELECT length('中文') → 6 -- UTF-8中文字符占3字节 

    • empty()/notEmpty()：检测空字符串

      SELECT empty('') → 1 :ml-citation{ref="14" data="citationList"}

    • substring(str, start, length)：截取子串

      SELECT substring('ClickHouse', 6, 5) → 'House' :ml-citation{ref="13" data="citationList"}

  2. ‌编码处理‌

    • lengthUTF8()：UTF-8字符数

      SELECT lengthUTF8('中文') → 2 :ml-citation{ref="14" data="citationList"}

    • lower()/upper()：大小写转换

      SELECT upper('hello') → 'HELLO' 

三、聚合函数

一、核心排名函数对比‌

‌函数‌‌排序特性‌‌相同值处理‌‌典型应用场景‌
ROW_NUMBER()绝对连续编号 (1,2,3...)相同值分配不同序号分页查询、TopN筛选12
RANK()允许并列排名 (如1,1,3)跳过后续序号竞赛排名、成绩榜单23
DENSE_RANK()连续排名 (如1,1,2)不跳过序号得连续排名的业务场景35
NTILE(n)将数据均分到n个桶按比例分组数据分位数分析46

‌二、ClickHouse实现方式‌

  1. ‌新版（21.3+）原生支持‌

    SELECT id, val, ROW_NUMBER() OVER(PARTITION BY id ORDER BY val DESC) AS row_num, RANK() OVER(PARTITION BY id ORDER BY val DESC) AS rank_val FROM test_data:ml-citation{ref="13,14" data="citationList"}

  2. ‌旧版（<21.3）替代方案‌
    使用数组函数模拟：

    SELECT id, val, arrayEnumerate(groupArray(val)) AS row_number, arrayEnumerateDense(groupArray(val)) AS dense_rank FROM ( SELECT * FROM test_data ORDER BY id, val DESC ) GROUP BY id:ml-citation{ref="12,14" data="citationList"}

‌三、实战应用场景‌

  1. ‌用户行为分析‌

    -- 计算每个用户的点击事件排名 SELECT user_id, event_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY event_time) AS event_seq FROM user_events

  2. ‌销售排行榜‌

    -- 按销售额计算店铺排名（允许并列） SELECT shop_id, sales, RANK() OVER(ORDER BY sales DESC) AS sales_rank FROM shop_data

  3. ‌材料分桶分析‌

    -- 将学生成绩分为4个等级SELECT student_name, score, NTILE(4) OVER(ORDER BY score DESC) AS score_level FROM exam_results:ml-citation{ref="4,6" data="citationList"}

  4. ‌统计计算‌

    • sum()/avg()：求和/平均值

      SELECT avg(salary) FROM employees

    • min()/max()：最小值/最大值

      SELECT max(temperature) FROM sensors 

    • topK(N)(column)：返回出现频率TOP N的值

      SELECT topK(3)(product) FROM orders

  5. ‌高级分析‌

    • varPop()：总体方差

      SELECT varPop(score) FROM exams 

    • covarPop(x, y)：协方差

    • SELECT covarPop(revenue, ad_cost) FROM ads 

⚖️ 四、条件函数

  1. ‌逻辑控制‌

    • if(cond, true_val, false_val)：条件分支

      SELECT if(age > 18, 'Adult', 'Minor') FROM users :ml-citation{ref="9" data="citationList"}

    • multiIf(cond1, val1, cond2, val2, ..., else_val)：多条件分支

      SELECT multiIf(score >= 90, 'A', score >= 80, 'B', 'C') FROM grades 

  2. ‌空值处理‌

    • isNull()/isNotNull()：检测空值

      SELECT isNull(email) FROM contacts

性能优化提示

  • 对‌低基数字符串列‌使用 LowCardinality(String) 类型，可提升聚合函数性能 7
  • 避免在 WHERE 子句中对字段进行函数转换（如 WHERE toDate(timestamp) = ...），优先存储预计算值 2

‌示例：综合查询‌

sqlCopy Code

-- 统计各月销售额TOP 3商品 SELECT toMonth(order_date) AS month, topK(3)(product_name) AS top_products FROM orders GROUP BY month;

 

clickhouse 核心配置

user.xml

::/0  default default 1 1  GRANT ALL ON *.*         3600 0 0 0 0 0

Java 执行ClickHouse

com.clickhouse clickhouse-jdbc 0.7.2
String url = "jdbc:ch:http://192.168.64.145:8123/clickhouse?compress=true&socket_timeout=300000"; String user = "clickhouse"; String password = "123456"; Connection connection = DriverManager.getConnection(url, user, password);

一、架构设计对比

‌维度‌‌ClickHouse‌‌MySQL (InnoDB)‌
‌存储模型‌列式存储（按列压缩/读取）57行式存储（按行处理事务）67
‌架构类型‌MPP分布式架构（并行计算）411单机/主从架构（无原生分布式计算）46
‌表引擎‌支持MergeTree、Log等20+引擎（场景定制化）810固定InnoDB/MyISAM引擎（能力通用）6
‌索引机制‌稀疏索引（按排序键分区）10B+树索引（支持点查询/范围查询）68

⚡ 二、性能表现对比

  1. ‌查询性能‌

    • ‌OLAP场景‌：ClickHouse在10亿级数据聚合查询速度超MySQL数百倍（列读取+向量化引擎）47
    • ‌OLTP场景‌：MySQL点查询/事务处理更快（行锁+B+树索引）
    • ‌实测案例‌：
      • 同量级数据艰难查询：ClickHouse（30秒） vs MySQL（5分18秒）
      • 1亿行聚合：ClickHouse比MySQL快801倍11

  2. ‌写入性能‌

    • ClickHouse：适合‌批量插入‌（高吞吐），单条插入延迟高
    • MySQL：支持‌实时事务写入‌（行级锁保证一致性）
    • 相同1.6亿行数据插入：两者耗时接近（15-20分钟）

  3. ‌并发能力‌

    • ClickHouse：高并发读优化，写入并发受限
    • MySQL：支持高并发读写（MVCC机制）

三、机制特性对比

‌能力‌ClickHouseMySQL (InnoDB)
‌事务支持‌❌ 无ACID事务✅ 完整ACID事务（redo log/undo log）6
‌数据压缩‌✅ 列式压缩率高达90%+10⚠️ 有限压缩（取决于数据类型）6
‌分布式扩展‌✅ 原生分片/副本（如ReplicatedMergeTree）11❌ 需中间件（如ShardingSphere）
‌困难分析函数‌✅ 支持窗口函数/数组操作/机器学习模型811⚠️ 基础聚合函数（高阶需代码实现）6

四、适用场景对比

‌场景‌‌推荐数据库‌‌原因‌
实时分析/数据仓库ClickHouse列存储+向量化引擎适配海量扫描聚合
交易系统（如支付/订单）MySQLACID事务保障数据一致性
时序数据/日志分析ClickHouse高效压缩+时间分区优化
频繁更新的业务数据MySQL行锁+MVCC支撑高并发更新

⚠️ 五、关键限制对比

  • ‌ClickHouse缺点‌：
    • 不拥护事务与单行更新
    • JOIN操作性能较弱（推荐预关联宽表）
    • 内存消耗较高（需SSD+大内存配置）
  • ‌MySQL缺点‌：
    • 大数据量下麻烦查询效率骤降
    • 水平扩展复杂（分库分表维护成本高）

总结：技术选型建议

  • ‌选ClickHouse若‌：
    ≥TB级分析场景、低延迟聚合查询、批量材料注入
  • ‌选MySQL若‌：
    高并发事务处理、频繁单行读写、强数据一致性保障

两者可组合采用：MySQL处理事务，ClickHouse同步资料加速分析。

 

EXPLAIN [AST|SYNTAX|PLAN|PIPELINE] SELECT ... -- 查看各阶段执行逻辑

  • ‌关键指标‌：关注ReadFromStorage（数据扫描量）、Aggregating（聚合耗时）
  • ‌优化点‌：索引命中率、分区裁剪效果

️ ‌建表优化‌

CREATE TABLE events (

dt Date,

user_id UInt64,

event_type String

)

ENGINE = MergeTree()

PARTITION BY toYYYYMM(dt) -- 按时间分区 ORDER BY (dt, user_id) -- 排序键需匹配查询条件 TTL dt + INTERVAL 3 MONTH -- 自动过期旧数据

SETTINGS index_granularity = 8192; -- 减少稀疏索引内存占用

  • ‌分区策略‌：优先选择时间字段，避免超过1000分区
  • ‌TTL应用‌：自动清理冷数据

⚡ ‌写入/删除优化‌

‌场景‌‌优化方案‌
‌高频写入‌批量写入（≥1000行/次），使用Buffer表引擎
‌删除数据‌22.8+版本用DELETE WHERE替代ALTER TABLE DROP PARTITION
‌更新数据‌优先设计为不可变数据模型，用ReplacingMergeTree去重

⚙️ ‌硬件参数调优‌

<!-- config.xml -->

<max_threads>16</max_threads> -- 并发查询线程数（≤CPU核心数） <max_memory_usage>10000000000</max_memory_usage> -- 单查询内存限制(10GB) <load_balancing>random</load_balancing> -- 分布式查询负载策略

‌语法规则优化‌

  1. ‌避免SELECT *‌：列式存储需明确指定字段
  2. ‌JOIN改进‌：
    • 小表在右（SET join_algorithm='auto'）
    • 用GLOBAL JOIN减少分布式查询网络传输
  3. ‌函数优化‌：
    • 用toStartOfHour()替代date_trunc('hour', dt)
  • 用IN代替JOIN：小表驱动大表时性能更优
  • 函数计算下推：WHERE toDate(ts) = '2023-01-01' → WHERE ts >= '2023-01-01 00:00:00' AND ts < '2023-01-02 00:00:00'1

ClickHouse 高级

 ‌一、列式存储与向量化引擎‌

  1. ‌列式存储优势‌
    • 按列存储数据，查询时仅读取所需列，显著降低I/O开销6
    • 同列数据类型一致，压缩率更高（如Delta编码压缩整数列）6
  2. ‌向量化执行引擎‌
    • 以数据块（Block）为单位处理，利用CPU SIMD指令并行计算6
    • 提升聚合函数（sum/avg）性能5-10倍6

⚙️ ‌二、存储引擎深度优化‌

1. ‌MergeTree引擎关键参数‌

CREATE TABLE logs ( ts DateTime, msg String )

ENGINE = MergeTree() PARTITION BY toYYYYMM(ts)

ORDER BY (ts, msg) SETTINGS index_granularity = 4096, -- 高频查询调小粒度 min_bytes_for_wide_part = 100M -- 小分区不启用宽表存储

  • ‌索引策略‌：ORDER BY字段需匹配高频查询条件（如时间范围过滤）1
  • ‌TTL自动化管理‌：自动删除过期分区（TTL ts + INTERVAL 1 YEAR）1
2. ‌数据更新与删除‌
‌方法‌‌适用场景‌‌原理‌
ALTER TABLE UPDATE少量内容更新（20.8+版本）标记删除旧材料，异步插入新版本8
ALTER TABLE DROP PARTITION批量删除整个分区直接移除分区目录，瞬时生效8
ReplacingMergeTree去重场景（如最新状态记录）后台合并时保留版本最高行8

‌三、分布式集群高级技巧‌

1. 分片策略优化
  • ‌写入路由‌：Distributed表引擎支持sharding_key自定义分片规则

    ENGINE = Distributed(cluster, db, table, cityHash64(user_id)) -- 按user_id哈希分片

  • ‌副本同步‌：ReplicatedMergeTree自动跨节点同步数据，通过ZooKeeper协调8
2. ‌查询负载均衡‌
3  random

‌四、查询解析与执行优化‌

  1. ‌双解析器机制‌
    • ‌全SQL解析器‌：处理SELECT/CREATE等复杂语法
    • ‌流式解析器‌：高效解析INSERT数据（如CSV/JSON格式）

clickHouse 数据分析中常用SQL 函数

‌一、基础聚合函数‌

  1. ‌计数统计‌ 
    SELECT count() AS total_rows, uniq(user_id) AS distinct_users FROM logs -- 精确去重计数:ml-citation{ref="2,10" data="citationList"}
  2. ‌数值聚合‌ 
    SELECT sum(revenue) AS total_revenue, avg(price) AS avg_price, median(duration) AS median_time -- 中位数计算:ml-citation{ref="2,13" data="citationList"} FROM transactions

‌二、高级统计分析函数‌

  1. ‌分布分析‌ 
    SELECT quantile(0.9)(response_time) AS p90, -- 百分位数:ml-citation{ref="2" data="citationList"} stddevPop(latency) AS population_std -- 总体标准差:ml-citation{ref="2,4" data="citationList"} FROM api_metrics
  2. ‌直方图生成‌ 
    SELECT histogram(5)(age) AS age_distribution FROM users -- 自适应分箱直方图

‌三、组合器增强特性‌

  1. ‌条件聚合‌ 
    SELECT sumIf(amount, status = 'paid') AS paid_total, -- 带条件的求和:ml-avgIf(salary, department = 'IT') AS it_avg_salary FROM orders
  2. ‌数组/Map聚合‌

    SELECT sumMap(status_code, request_count) FROM web_logs -- Map结构聚合:ml-citation{ref="10" data="citationList"}

⏱️ ‌四、时间序列聚合‌

  1. ‌滑动窗口计算‌

    SELECT timestamp, avg(metric) OVER (PARTITION BY device_id ORDER BY timestamp RANGE INTERVAL 1 HOUR PRECEDING) -- 1小时滑动平 FROM iot_data

  2. ‌时间维度聚合‌

    SELECT toStartOfHour(event_time) AS hour, count() AS events_per_hour FROM events GROUP BY hour -- 按小时聚合:ml-citation{ref="12" data="citationList"}

️ ‌五、工程优化技巧‌

  1. ‌预聚合物化视图‌

    CREATE MATERIALIZED VIEW daily_stats ENGINE = AggregatingMergeTree() AS SELECT toDate(time) AS day, sumState(amount) AS total_amount, uniqState(user_id) AS unique_users FROM orders GROUP BY day -- 增量聚合存储:ml-citation{ref="14" data="citationList"}

  2. ‌NULL值处理‌

    SELECT avg(coalesce(score, 0)) FROM tests -- 将NULL替换为默认值计算

posted on 2025-07-13 20:38  ljbguanli  阅读(47)  评论(0)    收藏  举报

博客园  ©  2004-2025
浙公网安备 33010602011771号 浙ICP备2021040463号-3