【架构实战】ClickHouse实时分析海量日志的秒级查询一、查个UV等了3小时2020年做运营分析平台日活数据从Hive里跑。运营想查昨天各渠道的UV和订单转化率我在Hive上跑了3个小时才出结果。运营等不及自己用Excel手算去了。后来接了一个实时大屏需求要求5秒刷新展示当天实时GMV、订单量、TOP10商品——Hive彻底歇菜了。这就是OLAP场景的经典矛盾数据量大每天数十亿条日志查询要求快秒级响应传统方案做不到。ClickHouse就是为这个场景而生的。二、ClickHouse为什么这么快2.1 列式存储——按列读写【行存储MySQL/InnoDB】 Row1: |id|name|age|city|amount|time| Row2: |id|name|age|city|amount|time| Row3: |id|name|age|city|amount|time| SELECT SUM(amount) FROM orders WHERE city 北京; → 需要读取所有列但只用到了city和amount 【列存储ClickHouse】 Col1(id): |1|2|3|... Col2(name): |张三|李四|王五|... Col3(age): |25|30|28|... Col4(city): |北京|上海|北京|... Col5(amount): |100|200|150|... Col6(time): |t1|t2|t3|... SELECT SUM(amount) FROM orders WHERE city 北京; → 只读取city和amount两列IO降低90%2.2 向量化执行——批次处理-- 传统数据库逐行处理-- 1000万行 1000万次函数调用-- ClickHouse按批次处理-- 一批8192行1000万行 ≈ 1220次函数调用-- 配合SIMD指令CPU效率极高2.3 数据压缩-- 由于同列数据相似度高压缩率极高CREATETABLEevents(event_dateDate,event_timeDateTime,user_id UInt64,event_type String,platform String)ENGINEMergeTree()PARTITIONBYtoYYYYMM(event_date)ORDERBY(event_type,event_time)SETTINGS index_granularity8192;-- 原始数据 1TB → 压缩后 ≈ 100GB压缩比10:1三、ClickHouse表引擎选择引擎适用场景特点注意事项MergeTree通用OLAP按主键排序自动合并最常用首选ReplacingMergeTree去重场景自动去重非实时去重SummingMergeTree预聚合自动求和只支持SUMAggregatingMergeTree复杂聚合配合物化视图需要理解聚合函数CollapsingMergeTree更新删除CDC场景使用复杂Distributed分布式表分片查询配合MergeTree使用3.1 MergeTree建表实战-- 用户行为日志表CREATETABLEevents(event_dateDate,event_timeDateTime,user_id UInt64,session_id String,event_type LowCardinality(String),-- 低基数字段优化page_url String,referrer String,device_type LowCardinality(String),os LowCardinality(String),duration UInt32,properties String-- JSON格式扩展字段)ENGINEMergeTree()PARTITIONBYtoYYYYMM(event_date)-- 按月分区ORDERBY(event_type,event_time,user_id)-- 排序键核心TTL event_timeINTERVAL90DAY-- 90天过期自动删除SETTINGS index_granularity8192;3.2 ORDER BY设计——查询性能的命脉-- 假设80%的查询都是按 event_type 时间范围-- 好的ORDER BYORDERBY(event_type,event_time)SELECT*FROMeventsWHEREevent_typeclickANDevent_time2025-06-01ANDevent_time2025-06-02;-- 索引命中扫描数据量极小-- 差的ORDER BYORDERBY(event_time,event_type)-- 同样查询需要扫描整个时间范围过滤效率低四、实时数据写入架构4.1 整体架构业务服务 → Kafka → ClickHouse实时 → HDFS离线备份 详细流程 ┌──────────┐ ┌──────────┐ ┌───────────────┐ │ Nginx │───│ Kafka │───│ Flink 清洗 │ │ 日志采集 │ │ 缓冲层 │ │ 格式统一 │ └──────────┘ └──────────┘ └───────┬───────┘ │ ┌────────▼───────┐ │ ClickHouse │ │ 物化视图预聚合 │ └────────────────┘4.2 Kafka引擎——零代码接入-- 创建Kafka消费表CREATETABLEevents_kafka_queue(event_timeDateTime,user_id UInt64,event_type String,page_url String,device_type String,duration UInt32)ENGINEKafka()SETTINGS kafka_broker_listkafka1:9092,kafka2:9092,kafka_topic_listuser_events,kafka_group_nameclickhouse_consumer,kafka_formatJSONEachRow,kafka_num_consumers4;-- 创建物化视图自动将Kafka数据写入MergeTreeCREATEMATERIALIZEDVIEWevents_mvTOeventsASSELECTtoDate(event_time)ASevent_date,event_time,user_id,event_type,page_url,device_type,durationFROMevents_kafka_queue;五、实战查询场景5.1 实时大屏——秒级聚合-- 实时GMV当天累计5秒刷新SELECTsum(multiIf(event_typeorder,amount,0))ASgmv,countIf(event_typeorder)ASorder_cnt,countIf(event_typepv)ASpv,uniqExactIf(user_id,event_typepv)ASuvFROMeventsWHEREevent_datetoday()ANDevent_timenow()-INTERVAL5SECOND;-- 执行时间50ms数据量最近5秒约100万行5.2 漏斗分析-- 用户行为漏斗首页→商品详情→加入购物车→下单SELECTwindowFunnel(300)(event_time,event_typehome_page,event_typeproduct_detail,event_typeadd_cart,event_typeplace_order)ASlevel,count()ASuser_countFROMeventsWHEREevent_datetoday()-7ANDevent_typeIN(home_page,product_detail,add_cart,place_order)GROUPBYlevelORDERBYlevel;-- 结果-- Level 1: 100万访问首页-- Level 2: 40万查看详情-- Level 3: 8万加购物车-- Level 4: 2万下单-- 转化率2万/100万 2%5.3 留存分析-- 次日留存率SELECTfirst_day,uniqExact(user_id)ASnew_users,uniqExact(retention_day_2)ASday2_retention,round(day2_retention/new_users*100,2)ASretention_pctFROM(SELECTuser_id,min(event_date)ASfirst_day,-- 次日是否活跃maxIf(event_date,event_datefirst_day1)ASretention_day_2FROMeventsWHEREevent_datetoday()-30ANDevent_typeapp_launchGROUPBYuser_id)WHEREfirst_daytoday()-30GROUPBYfirst_dayORDERBYfirst_day;六、踩过的坑坑1单条INSERT灾难-- 错误逐条插入INSERTINTOeventsVALUES(...);-- 1000次/秒 → ClickHouse崩溃-- 正确批量插入INSERTINTOeventsVALUES(...),(...),(...);-- 每次5000-10000条坑2大量的ALTER DELETE-- 错误频繁删除数据Mutation操作ALTERTABLEeventsDELETEWHEREevent_typespam;-- 正确使用TTL或分区DROPALTERTABLEeventsDROPPARTITION202501;坑3JOIN大表ClickHouse的JOIN性能不如MySQL尽量用物化视图预计算替代JOIN查询。七、总结ClickHouse解决的核心问题海量数据下的秒级分析查询。选型决策数据量小于1亿条 → MySQL/PostgreSQL足够数据量1亿~100亿OLAP场景 → ClickHouse需要OLTPOLAP → 考虑TiDB HTAP核心实践ORDER BY设计决定查询性能高频过滤字段放前面写入要批量单条INSERT会拖垮整个集群物化视图做预聚合是提升查询速度的王牌用好TTL自动清理避免磁盘爆满监控system.merges和system.mutations确保后台合并正常ClickHouse上线后运营大屏从3小时等待变成了3秒刷新再也不用来催IT了。个人观点仅供参考