【Clickhouse从入门到精通】第03篇：ClickHouse适用场景深度剖析

上一篇【第02篇】ClickHouse横空出世——天下武功唯快不破下一篇【第04篇】ClickHouse生态全景与生产实践者巡礼摘要技术选型是数据架构设计的核心命题。再优秀的工具若用错了场景也会事倍功半。ClickHouse 以极速分析查询著称但它并非万能解决方案。本文从实际业务场景出发系统梳理了 ClickHouse 的适用场景与不适用场景在 Web/App 流量分析、广告投放分析、用户行为分析、金融风控、IoT 时序数据等场景中ClickHouse 展现了卓越的性能优势而在强事务性需求、随机 Key-Value 查询、按行删除更新等场景下ClickHouse 则存在明显短板。通过对比分析以及 CERN、CloudFlare、字节跳动、阿里巴巴、腾讯、新浪等知名企业的生产实践帮助读者在实际项目中做出明智的 ClickHouse 技术选型决策。关键词ClickHouse、技术选型、场景分析、Web流量分析、广告分析、IoT、CERN、CloudFlare1. 引言工具没有最好只有最适合在数据技术领域有一个广为流传的教训** Hammer looking for nails**。意思是当你手中有一把锤子某个熟悉的工具时看什么都像钉子都想去敲一下。很多技术选型失败的根本原因不是技术本身不好而是用错了场景。ClickHouse 无疑是近年来最炙手可热的 OLAP 数据库之一。它的性能数据令人印象深刻——毫秒级查询响应、数十亿行数据秒级聚合、百倍于 MySQL 的查询速度。然而这并不意味着 ClickHouse 可以取代一切。如果你的业务场景是高频点查、海量小文件存储、或需要强事务保障的 ACID 操作强行使用 ClickHouse 只会带来痛苦。本文的目标很明确帮助你在充分理解 ClickHouse 能力边界的基础上做出正确的技术选型决策。我们将从适用场景、不适用场景、知名用户案例三个维度对 ClickHouse 进行一次全面而立体的剖析。2. ClickHouse适用场景深度剖析2.1 Web/App流量分析场景特点Web/App 流量分析是 ClickHouse 最典型的应用场景之一也是 ClickHouse 最初诞生的业务背景。典型的流量分析数据具有以下特征数据量大日均数十亿条访问日志写入吞吐高实时涌入的用户点击数据分析维度多时间、地域、设备、渠道、来源等数十个维度查询模式固定主要是多维度聚合分析典型业务需求-- 场景1实时大屏数据统计SELECTtoStartOfInterval(event_time,INTERVAL1minute)ASminute,count()ASpv,uniqExact(user_id)ASuv,countIf(event_typepurchase)ASconversionsFROMapp_eventsWHEREapp_idcom.example.appANDevent_timenow()-INTERVAL1HOURGROUPBYminuteORDERBYminute;-- 场景2渠道转化漏斗分析SELECTstep_name,count(DISTINCTuser_id)ASusers,round(count(DISTINCTuser_id)*100.0/lagInFrame(count(DISTINCTuser_id))OVER(),2)ASstep_rateFROM(SELECTuser_id,arrayJoin(arrayZip([首页,商品详情,加购,下单,支付],arrayMap(x-xargMin(event_sequence,event_time),[首页,商品详情,加购,下单,支付])))ASstepFROM(SELECTuser_id,groupArrayIf(event_type,event_timeORDERBYevent_time)ASevent_sequenceFROMapp_eventsWHEREevent_date2024-06-01GROUPBYuser_id))ARRAYJOINstepAS(step_name,has_event)WHEREhas_event1GROUPBYstep_name;-- 场景3用户留存分析SELECTinstall_date,retention_day,count(DISTINCTuser_id)ASretained_users,round(count(DISTINCTuser_id)*100.0/retention_array[1],2)ASretention_rateFROM(SELECTinstall_date,d,uniqExactIf(user_id,dretention_day)ASretention_arrayFROM(SELECTuser_id,toDate(first_install_time)ASinstall_date,dateDiff(day,install_date,event_date)ASdFROMuser_eventsWHEREevent_typeactiveANDevent_date2024-01-01)GROUPBYinstall_date,d)ARRAYJOINarrayMap(x-retention_array[x],range(1,retention_day1))ASretained_users,range(1,retention_day1)ASretention_dayGROUPBYinstall_date,retention_dayORDERBYinstall_date,retention_day;ClickHouse 的优势维度ClickHouse 表现写入性能支持每秒百万行写入无需预先建表压缩效率文本类数据URL、User-Agent压缩比可达 10:1聚合速度千万级 UV 去重可在 100ms 内完成实时分析支持近实时数据写入延迟可达秒级存储成本相比 MySQL存储空间节省 80%2.2 广告投放分析场景特点广告投放分析是另一个 ClickHouse 的主场场景。广告系统的核心诉求是实时评估投放效果、优化竞价策略、保障广告主的 ROI。典型需求包括实时报表今日消耗、点击、花费效果归因各渠道、各创意的转化效果人群定向分析目标人群的覆盖率和精准度竞价策略优化基于历史数据的智能出价典型业务需求-- 场景1广告实时消耗监控SELECTcampaign_id,campaign_name,sum(ad_impression)ASimpressions,sum(ad_click)ASclicks,round(sum(ad_click)/sum(ad_impression)*100,4)ASctr,sum(ad_cost)AScost,round(sum(ad_cost)/sum(ad_click),4)AScpc,round(sum(ad_cost)/sum(ad_conversion),2)AScpaFROMad_stats_hourlyWHEREstat_hourtoStartOfHour(now()-INTERVAL24HOUR)ANDadvertiser_idADV_12345GROUPBYcampaign_id,campaign_nameORDERBYcostDESC;-- 场景2广告归因分析7日窗口内转化WITHuser_ad_exposureAS(SELECTuser_id,ad_id,campaign_id,min(exposure_time)ASfirst_exposure_timeFROMad_impressionsWHEREexposure_date2024-06-01GROUPBYuser_id,ad_id,campaign_id),user_conversionAS(SELECTuser_id,order_id,order_time,order_valueFROMconversionsWHEREorder_date2024-06-01)SELECTc.campaign_id,count(DISTINCTc.user_id)ASconverted_users,sum(c.order_value)AStotal_gmv,round(sum(c.order_value)/count(DISTINCTc.user_id),2)ASarpu,round(count(DISTINCTc.user_id)*100.0/(SELECTcount(DISTINCTuser_id)FROMad_impressionsWHEREcampaign_idc.campaign_id),2)AScvrFROMuser_conversion cJOINuser_ad_exposure eONc.user_ide.user_idANDe.first_exposure_timec.order_timeANDdateDiff(day,e.first_exposure_time,c.order_time)7GROUPBYc.campaign_idORDERBYtotal_gmvDESC;ClickHouse 的优势广告分析的典型查询需要 JOIN 多张事实表和维度表并在数十亿行数据上进行复杂的聚合计算。ClickHouse 的数组函数groupArray、arrayJoin和窗口函数lagInFrame、runningAccumulate提供了强大的分析能力同时保持着毫秒级的查询响应速度。2.3 电信与网络数据场景特点运营商每天处理 PB 级别的网络信令数据、CDN 访问日志、用户上网记录等。这些数据的共同特点是超高数据量单日数十亿条记录字段相似度高大量重复的网络地址、协议名称分析模式固定以时间、地域、设备为维度的流量统计合规要求高部分数据需要长期归档保存典型业务需求-- 场景1CDN 流量实时统计SELECTtoStartOfInterval(event_time,INTERVAL5minute)AStime_bucket,cdn_node_id,cdn_node_region,sum(bytes_sent)AStotal_bytes,sum(requests)AStotal_requests,round(sum(bytes_hit)/sum(requests)*100,2)AScache_hit_rateFROMcdn_access_logsWHEREevent_datetoday()ANDevent_timenow()-INTERVAL1HOURGROUPBYtime_bucket,cdn_node_id,cdn_node_regionORDERBYtime_bucket,total_bytesDESC;-- 场景2用户上网行为分析SELECTuser_id,user_segment,sum(up_bytesdown_bytes)AStotal_traffic_gb,sum(http_requests)AStotal_requests,topK(10)(domain)AStop_domainsFROMuser_internet_usageWHEREstat_date2024-06-01ANDstat_date2024-06-30GROUPBYuser_id,user_segmentHAVINGtotal_traffic_gb100ORDERBYtotal_traffic_gbDESC;2.4 金融风控与交易分析场景特点金融场景对数据的实时性和准确性要求极高。ClickHouse 在以下金融分析场景中表现出色交易实时监控秒级感知异常交易用户行为反欺诈基于历史行为模式识别欺诈实时大盘交易量、金额、清算状态的实时展示典型业务需求-- 场景1实时交易风控告警SELECTtoStartOfInterval(event_time,INTERVAL1minute)ASminute,alert_type,count()ASalert_count,uniqExact(user_id)ASaffected_users,sum(transaction_amount)AStotal_amount_riskFROMtransaction_alertsWHEREevent_timenow()-INTERVAL30MINUTEANDalert_level3-- 高风险告警GROUPBYminute,alert_typeHAVINGalert_count10ORDERBYtotal_amount_riskDESC;-- 场景2用户交易行为画像SELECTuser_id,-- 基本统计count()AStxn_count,sum(txn_amount)AStotal_amount,round(avg(txn_amount),2)ASavg_amount,stddevPop(txn_amount)ASamount_stddev,-- 时间特征max(txn_amount)ASmax_amount,min(txn_amount)ASmin_amount,countIf(txn_hourBETWEEN22AND6)ASnight_txn_count,-- 夜间交易-- 地点特征uniqExact(txn_city)AScity_count,-- 设备特征uniqExact(device_id)ASdevice_count,-- 商户类型偏好topKWeighted(5)(merchant_category,toFloat64(txn_amount))AStop_categoriesFROMtransactionsWHEREtxn_datedateSub(day,30,today())ANDtxn_statussuccessGROUPBYuser_idORDERBYtotal_amountDESCLIMIT100;ClickHouse 在金融场景的独特优势精确去重uniqExact函数保证在数十亿用户规模下的精确去重避免近似算法带来的误差窗口函数runningAccumulate、lagInFrame等函数支持滑动窗口计算实现动态阈值风控高吞吐写入Kafka ClickHouse 的实时数据管道可支持每秒百万级事件写入2.5 IoT时序数据场景特点物联网设备产生的时序数据具有以下特征写入远多于读取设备持续上报数据但查询以聚合统计为主时间有序数据天然按时间顺序产生高基数大量设备 ID、时间戳、数值指标生命周期明确数据通常只需要保留一段时间典型业务需求-- 场景1设备监控实时大屏SELECTdevice_group,countIf(statusonline)ASonline_count,countIf(statusoffline)ASoffline_count,round(avg(cpu_usage),2)ASavg_cpu,round(avg(memory_usage),2)ASavg_memory,round(avg(temperature),2)ASavg_temp,quantile(0.95)(response_time_ms)ASp95_responseFROMdevice_telemetryWHEREreport_timenow()-INTERVAL10MINUTEGROUPBYdevice_group;-- 场景2设备告警规则引擎SELECTdevice_id,alert_rule_id,alert_rule_name,trigger_time,metric_name,metric_value,threshold_value,round(metric_value/threshold_value,2)ASoverload_ratioFROM(SELECTdevice_id,cpu_overloadASalert_rule_id,CPU 使用率超阈值ASalert_rule_name,report_timeAStrigger_time,cpu_usageASmetric_name,cpu_usageASmetric_value,80ASthreshold_valueFROMdevice_telemetryWHEREcpu_usage80ANDreport_timenow()-INTERVAL1HOURUNIONALLSELECTdevice_id,temp_overloadASalert_rule_id,温度超阈值ASalert_rule_name,report_timeAStrigger_time,temperatureASmetric_name,temperatureASmetric_value,70ASthreshold_valueFROMdevice_telemetryWHEREtemperature70ANDreport_timenow()-INTERVAL1HOUR)ORDERBYoverload_ratioDESC;3. ClickHouse不适用场景3.1 强事务性需求OLTP场景问题描述ClickHouse 的设计目标是极速分析而非事务处理。如果你的业务场景需要强事务保障如银行转账、订单支付ClickHouse 并不是合适的选择。-- ❌ 不推荐ClickHouse 不适合这类强事务操作-- 场景用户余额扣减BEGINTRANSACTION;UPDATEuser_accountsSETbalancebalance-100WHEREuser_id1;INSERTINTOtransactionsVALUES(1,1,purchase,100,now());UPDATEproductsSETstockstock-1WHEREproduct_id10;COMMIT;ClickHouse 的局限性没有真正的 UPDATE/DELETE虽然有ALTER TABLE ... DELETE语法但实际是标记删除后台异步执行不支持事务不支持跨表、跨语句的事务语义数据一致性弱最终一致性写入后需要等待 MergeTree 合并才能看到一致结果推荐替代方案场景推荐方案强事务 OLTPMySQL、PostgreSQL、TiDB分布式事务Apache ShardingSphere、TiDB混合负载HTAP 方案TiDB、Snowflake3.2 随机Key-Value查询问题描述ClickHouse 的 MergeTree 引擎针对范围扫描进行了深度优化但对于随机单行查询点查场景性能远不如专门的 Key-Value 存储。-- ❌ 不推荐随机单行查询ClickHouse 性能不佳SELECT*FROMuser_profilesWHEREuser_id12345678;SELECTorder_statusFROMordersWHEREorder_idORD_987654321;ClickHouse 点查的性能瓶颈稀疏索引MergeTree 的稀疏索引适合范围查询随机点查需要扫描多个 Data Part压缩数据读取点查也需要解压整个 Data Part 的相关列无缓存优化ClickHouse 默认不缓存点查结果推荐替代方案场景推荐方案高频点查Redis、Memcached中频点查MySQL 索引、TiDB点查 聚合混合ClickHouse 物化视图加速点查优化方案物化视图加速点查如果必须用 ClickHouse 做点查可以通过物化视图优化-- 创建点查加速表CREATETABLEuser_profiles_cache(user_id UInt64,user_name String,email String,...)ENGINEReplacingMergeTree(user_id)ORDERBYuser_id;-- 通过物化视图预加载热点数据CREATEMATERIALIZEDVIEWmv_hot_usersENGINESummingMergeTree()ORDERBYuser_idASSELECTuser_id,...FROMuser_profilesWHEREuser_idIN(SELECTuser_idFROMhot_user_list-- 热点用户列表);3.3 频繁小量数据更新/删除问题描述ClickHouse 虽然支持 UPDATE 和 DELETE 操作但这些操作的设计目标是用于数据清理和归档而非频繁的实时更新。-- ⚠️ 不推荐频繁的逐行更新操作-- 场景订单状态实时更新UPDATEordersSETorder_statusshipped,shipped_timenow()WHEREorder_idORD_12345;UPDATEordersSETorder_statusdelivered,delivered_timenow()WHEREorder_idORD_12346;DELETEFROMordersWHEREorder_date2024-01-01ANDstatuscancelled;ClickHouse 更新/删除的局限性异步执行UPDATE/DELETE 是异步后台任务有延迟合并开销大量小更新会产生大量小 Data Part增加合并负担写入放大更新操作实际是写入新数据存储空间增长优化方案批量操作 异步合并如果必须使用 ClickHouse 进行数据更新建议批量操作-- ✅ 推荐批量更新合并为一次后台任务-- 将实时更新请求写入增量表CREATETABLEorders_delta(order_id String,field_name String,new_value String,update_timeDateTime)ENGINENull();-- 定期通过 ALTER TABLE ... UPDATE 批量执行ALTERTABLEordersUPDATEorder_statusifNull((SELECTnew_valueFROMorders_deltaWHEREfield_nameorder_statusANDorder_idorders.order_idORDERBYupdate_timeDESCLIMIT1),order_status)WHEREorder_idIN(SELECTDISTINCTorder_idFROMorders_deltaWHEREfield_nameorder_status);4. 适用与不适用场景全景对比场景类型典型业务ClickHouse 适配度备注Web/App 流量分析用户行为日志、埋点数据⭐⭐⭐⭐⭐ 极推荐最典型的主场场景广告投放分析曝光、点击、转化归因⭐⭐⭐⭐⭐ 极推荐高吞吐 多维分析完美契合电信网络数据CDN 日志、信令数据⭐⭐⭐⭐⭐ 极推荐极高数据量场景的不二之选金融风控分析交易监控、反欺诈⭐⭐⭐⭐ 推荐实时性要求高的场景需注意架构设计IoT 时序数据设备遥测、传感器数据⭐⭐⭐⭐ 推荐MergeTree 的时间有序性天然适配用户画像分析人群圈选、标签统计⭐⭐⭐⭐ 推荐数组函数极大简化人群操作数据湖分析历史数据归档查询⭐⭐⭐⭐ 推荐相比 Parquet 有更好的压缩和查询性能OLTP 事务处理订单支付、转账⭐ 不推荐请使用 MySQL、TiDB 等随机 Key-Value 查询用户详情查询⭐ 不推荐请使用 Redis、MySQL频繁实时更新库存实时同步⭐ 不推荐ClickHouse 适合追加写入毫秒级点查延迟实时推荐系统⭐⭐ 勉强需要配合缓存层使用小文件存储图片、元数据存储⭐ 不推荐请使用对象存储高并发简单查询千万 QPS 的 ID 查询⭐ 不推荐请使用专用缓存或 KV 存储5. 知名用户案例5.1 CERN欧洲核子研究组织CERN 是 ClickHouse 的早期采用者之一。作为世界顶级的科研机构CERN 运行着全球最大的粒子对撞实验每天产生 PB 级别的实验数据。CERN 的 ClickHouse 应用场景探测器运行状态监控实验数据质量分析集群资源使用统计CERN 选择 ClickHouse 的原因超大规模数据PB 级的快速查询能力高写入吞吐支持实时数据采集优秀的压缩比节省存储成本5.2 CloudFlareCloudFlare 是全球领先的 CDN 和网络安全公司每天处理数万亿 HTTP 请求。CloudFlare 的 ClickHouse 应用场景全球 CDN 流量实时分析DDoS 攻击检测与监控客户流量报表技术成果单集群规模数千亿行数据查询延迟P99 延迟低于 1 秒查询吞吐支持数千 QPS5.3 字节跳动字节跳动是中国最大的内容平台之一旗下产品包括抖音、今日头条、TikTok 等。字节跳动的 ClickHouse 应用场景抖音/今日头条内容推荐系统用户行为实时分析广告投放效果监控AB 测试数据分析技术成果国内 ClickHouse 最大的用户之一集群规模达到数万台服务器日均查询量超过百亿次5.4 阿里巴巴阿里巴巴在多个业务线中使用了 ClickHouse。阿里巴巴的 ClickHouse 应用场景阿里云日志分析服务Log Service电商实时数据大屏用户行为分析技术成果作为 ClickHouse 的重要代码贡献者持续回馈开源社区基于 ClickHouse 打造了阿里云分析型数据库产品5.5 腾讯与新浪腾讯和新浪同样是 ClickHouse 的积极采用者公司应用场景规模腾讯微信数据分析、游戏运营监控单集群 PB 级新浪微博用户行为分析、内容推荐日均数千亿行写入京东订单数据分析、用户画像实时报表与分析快手短视频用户行为分析大规模生产部署6. 技术选型决策树面对一个新的业务场景如何快速判断 ClickHouse 是否是合适的选择以下是决策树业务场景分析 │ ├── 数据来源 │ ├── 埋点/日志/时序数据 ──→ [推荐] ClickHouse ✓ │ ├── 业务数据库变更 ──→ 需要 CDC 接入可选 ClickHouse ✓ │ └── 用户上传文件 ──→ 需要导入流程可选 ClickHouse ✓ │ ├── 查询模式 │ ├── 多维聚合分析 ──→ [强烈推荐] ClickHouse ✓✓✓ │ ├── 明细数据查询 ──→ 需要分页限制可选 ClickHouse ✓ │ ├── 全文检索 ──→ 需要 ES/MySQL 分词可选 ClickHouse ✓ │ └── 随机点查 ──→ [不推荐] ClickHouse ✗ → Redis/MySQL │ ├── 性能要求 │ ├── 毫秒级响应 ──→ [强烈推荐] ClickHouse ✓✓✓ │ ├── 秒级响应 ──→ ClickHouse 可满足 ✓ │ └── 分钟级响应 ──→ Hive/Spark 亦可考虑成本 │ └── 数据特征 ├── 数据量大10亿行──→ [强烈推荐] ClickHouse ✓✓✓ ├── 字段多50列──→ ClickHouse 列式存储优势明显 ✓✓ └── 需要强事务 ──→ [不推荐] ClickHouse ✗ → TiDB/PG7. 总结扬长避短物尽其用本文从适用场景与不适用场景两个维度对 ClickHouse 进行了一次全面剖析ClickHouse 的核心竞争力超大规模数据数十亿至数千亿行下的极速分析查询列式存储带来的高压缩比和低存储成本高写入吞吐适合实时数据采集场景丰富的 SQL 扩展函数支持复杂的分析需求活跃的开源社区和成熟的商业支持ClickHouse 的能力边界不适合强事务性 OLTP 操作不适合高频随机 Key-Value 点查不适合频繁的实时数据更新删除单机部署场景下资源扩展能力有限选型建议✅ 选择 ClickHouse 当 - 数据量大10亿行分析查询是主要场景 - 查询以聚合、统计为主需要毫秒级响应 - 写入吞吐高需要实时或准实时数据可见 - 存储成本敏感需要高压缩比 ❌ 避开 ClickHouse 当 - 需要强事务保障使用 MySQL/TiDB - 高频点查为主使用 Redis - 频繁更新数据使用 MySQL/ES - 小团队缺乏运维能力考虑 ClickHouse Cloud技术选型没有银弹但有最优解。在充分理解 ClickHouse 的能力与边界后你完全可以自信地说“这个场景ClickHouse 就是答案。”上一篇【第02篇】ClickHouse横空出世——天下武功唯快不破下一篇【第04篇】ClickHouse生态全景与生产实践者巡礼参考资料ClickHouse GitHub Repository《ClickHouse原理解析与应用实践》ClickHouse DocumentationOLAP查询执行引擎设计研究