做游戏数据分析的人应该都有过这种体验DAU 才几百万Hive 跑每日留存要十分钟MySQL 里查七日活跃直接超时老板还要你在看板上实时看付费转化率。去年我们团队被各种慢查询逼到墙角最后被同事拉去研究 ClickHouse前后折腾了两个月算是把一套完整的游戏分析链路跑通了。这篇东西就把我的实践过程、踩过的坑、和最终沉淀下来的方案完整写出来给准备引入 ClickHouse 做大数据分析的团队一个真实参考。如果你也正要处理这种“量大、维度多、要秒级响应”的游戏日志数据这篇内容应该能帮上忙。1. 为什么要给游戏分析引入 ClickHouse1.1 游戏日志数据到底有多“任性”游戏数据分析的场景相比普通互联网业务有几个特别麻烦的地方第一事件量特别大用户的每次点击、每局对战、每个道具掉落、每次付费都会产生一条日志光是行为埋点一天就能积累几十亿条第二维度特别多同一份分析里面可能要同时看渠道、版本、地图、角色、设备、网络环境、深浅链路等各种维度第三查询模式高度聚合大部分分析都是先按天、按小时分组然后做 COUNT、SUM、AVG计算 DAU、留存、付费 ARPU这种查询如果走传统行存储或者 Hadoop 全套链路往往要等很久。我们团队早期用的是 Hive MySQL 的组合。晚上跑离线宽表任务早上的日报才能出来白天临时想看一个线上活动效果要写 Hive SQL 然后排队跑十分钟。后来数据量继续涨MySQL 里业务表越来越大单表查询经常九十秒起步这就到了非换不可的地步。这种背景下我们需要的是一个能直接应对“高频写入 高维度聚合 秒级响应”的 OLAP 引擎而不是再堆一层批处理。ClickHouse 在列式存储和向量化执行这两件事上做得足够极致才让我们在选型时下定决心。1.2 ClickHouse 与 Doris、Hive 的选型对比当时我们花了两周做调研其实圈内可选的 OLAP 引擎不少主要对比了 ClickHouse、Doris、以及原有 Hive 体系升级方案。ClickHouse俄罗斯 Yandex 公司开源列式存储、向量化执行单表聚合性能极强这一点在游戏事件分析里太重要了因为我们的查询几乎都是“按维度聚合计数”。短板是多表 JOIN 不是强项复杂 JOIN 容易爆内存好在游戏分析通过宽表建模可以绕开。Doris百度开源、Apache 孵化MPP 架构MySQL 协议兼容性好多表 JOIN 支持更好在明细加聚合混合场景、实时更新场景有优势。不过我们团队对 SQL 方言和数据导入的多样性不如 Kafka 体系熟悉而且针对“高频事件写入 大宽表分析”的场景ClickHouse 的 MergeTree 引擎体系更成熟一些。如果你们的主要诉求是“以用户为主的报表系统有大量 JOIN”Doris 也是一个不错的选项但我们这类以事件流为主的游戏日志用 ClickHouse 更对味。Hive用来做离线全量计算没问题但交互式查询和实时看板基本没戏。Spark SQL 也一样调度、资源池、metric 耗时摆在那里本质上是批处理思维不适合做在线分析。我们最终选了 ClickHouse还要加一句如果你们已有 Doris 团队且精力充足走 Doris 也完全可以选型这件事没有绝对对错只有场景匹配度。1.3 整体架构与数据链路设计整个架构分成四个模块。埋点采集客户端打点统一走日志服务过滤脏数据后写入 Kafka。实时处理Flink 消费 Kafka 数据做格式清洗、字段补全、维表关联同时把需要同步的 MySQL 业务表通过 Flink CDC 拉进 ClickHouse。存储与计算ClickHouse 作为 OLAP 引擎承载事件明细表、聚合结果表、宽表以及给报表和多维分析提供 SQL 接口。查询服务数据平台后端用 API 封装 ClickHouse 查询支撑自助分析、监控大屏和运营后台。这套链路里我最满意的是把明细和聚合分开了。明细数据进 MergeTree聚合结果通过物化视图和定时任务生成查询全部走聚合表没有 Hive 时代那么复杂的调度半夜很少因为某个依赖任务失败导致第二天报表出不来。后面我们逐步把原本跑在 Spark 上的十几个粉丝报表迁移到了这套链路运维压力小了很多。2. ClickHouse 部署与核心配置文档里不会明说的细节2.1 ClickHouse 21.8 在 Linux 上的安装记录部署版本我们选了 21.8.15.7这个版本生命周期比较健康、社区反馈稳定问问题也好查。服务器用的是 16 核 64GB 的云主机磁盘走了 SSD。安装时用的是官方 RPM 仓库也可以下载 tgz 包离线部署。tgz 方式从 packages 页面下载 clickhouse-server、clickhouse-common-static、clickhouse-client 三个包解压到 /opt/clickhouse然后创建独立系统用户修改 config.xml 里的listen_host、path、tmp_path等关键路径再启动服务。我建议至少给 ClickHouse 配一块独立的 SSD 数据盘别和系统盘混在一起因为 ClickHouse 的数据写入是批量追加的默认的 merge 和分区重排会吃不少磁盘 IO。生产环境最好用 NVMe SSD如果预算有限冷数据可以放到普通机械盘配合后面会讲的 TTL 冷热分离策略成本能压下来。安装后第一件事是压测。我当时直接灌了一个 200GB 的样本数据测了 group by 和 filter 查询结果相当震撼以前 Hive 跑三分钟的任务在 ClickHouse 里几十毫秒到几百毫秒就能返回。当然这是理想情况但确实坚定了我们换平台的信心。注意安装前检查 CPU 是否支持 SSE 4.2 指令集老机器上很容易踩到这个坑启动直接报 Illegal instruction。2.2 表引擎选型与事件表建模ClickHouse 的 MergeTree 家族是我认为最值得花时间研究的部分没有之一。基础引擎常用四种MergeTree、ReplacingMergeTree、SummingMergeTree、AggregatingMergeTree。游戏事件明细表用的 MergeTree。分区用toYYYYMMDD(event_time)按天分区排序键ORDER BY (event_date, event_type, game_id)因为绝大多数查询都是先按天、按事件类型、按游戏维度过滤。这里有个特别重要的认知主键和排序键在 MergeTree 里的逻辑不是一回事索引是稀疏索引数据按排序键物理有序所以排序键的选择直接决定查询能不能高效剪枝。我们最开始把 timestamp 放第一位查询老是慢后来意识到事件类型本来一屏就几个应该放前面这一改查询性能直接翻倍。维度关联表则用 ReplacingMergeTree配合写入时按唯一键去重适合 MySQL 业务表的同步场景。如果你们用 Flink CDC 同步 MySQL重启任务后会有 binlog 重复消费ReplacingMergeTree 能用版本列自动在后台合并时去重这是兜底保障。指标汇总表用 SummingMergeTree当我们需要对某些数值字段做累加而不用明细时这个引擎可以在合并阶段把同键的数据预聚合查询直接读部分行性能收益非常大。比如日活分时曲线用 SummingMergeTree 预聚合小时维度的活跃次数接口响应就一直在 100ms 上下。建表参考CREATE TABLE game.event_detail ( event_date Date, event_time DateTime, game_id UInt32, channel_id String, user_id UInt64, event_type LowCardinality(String), map_id UInt32, level_id UInt32, duration_sec UInt32, pay_amount Decimal(18,2), ... ) ENGINE MergeTree() PARTITION BY toYYYYMMDD(event_date) ORDER BY (event_date, event_type, game_id) TTL event_date INTERVAL 180 DAY SETTINGS index_granularity 8192;其中event_type用了LowCardinality这是一个非常实用的优化。游戏里事件类型就几十个字符串转换成类似字典 ID 的表示存储空间和查询速度都能大幅提升实测单个字段压缩比能达到几十倍。注意 LowCardinality 不太适合高基数数据比如用户 ID如果强行用反而会拖慢查询。2.3 行级/列级权限设计让数据安全落地数据平台的权限问题一直很头疼游戏行业尤其如此——不同运营、不同数值策划只能看自己负责的游戏和渠道不能看到全量数据。ClickHouse 从 21.x 开始对 RBAC 支持得很好我们做了行列权限设计。权限设计的核心思路有三层。第一层是列级权限直接通过GRANT SELECT(event_date, game_id, event_type, user_id) ON game.event_detail TO role_operator;让普通运营只能看到指定列。第二层是行级权限ClickHouse 原生没有行级权限关键词但可以通过创建带过滤条件的 VIEW 来模拟比如CREATE VIEW game.operator_data AS SELECT ... WHERE game_id IN (1,2,3) AND channel_idofficial;然后把这个 VIEW 的查询权限授给对应角色。第三层是角色管理用CREATE ROLE role_analyst;定义权限模板再挂载到用户上避免一个一个 grant。这里有个我踩过的坑创建只读用户时把readonly1/readonly和allow_ddl0/allow_ddl写在 users.xml却发现 SQL 层还能 grant权限行为很迷惑。后来才意识到ClickHouse 的权限模型更新后之前通过表级别配置做的设置在某些版本有优先级冲突。我的建议是统一用 SQL 层管理权限不要一半用 users.xml 一半用 SQL不然会有一堆“看到了但操作不了”的莫名其妙的问题。另外如果用分布式表行级过滤 VIEW 最好建在分布式表外层统一入口过滤权限更集中。否则每个 shard 都要执行一遍视图过滤管理起来容易乱。3. Flink Kafka 同步 MySQL 入 ClickHouse数据管道实战3.1 同步方案选型为什么用 Flink CDC游戏分析里有几类数据必须实时或准实时地从 MySQL 同步到 ClickHouse玩家基础信息、游戏配置表道具、地图、任务、支付订单、客服工单。MySQL 里这些表每天有大量增删改如果直接定时全量拉会对 MySQL 造成压力而且延迟不可控。我们最终选择 Flink CDC Kafka 的方式。Flink CDC 底层通过 Debezium 解析 binlog对 MySQL 只读不需要改业务表结构支持增量与全量自动切换。具体链路是Flink CDC 读 binlog 后把变更数据转成统一的 JSON 消息发到 Kafka然后由另一个 Flink 任务消费 Kafka写入 ClickHouse。当然如果只是单表同步、要求不高Flink 直接接 ClickHouse 连接器写入也可以但我们中间加 Kafka 一是解耦二是多个下游比如实时数仓的 DWD 层可以复用同一个数据流这个架构在团队扩展业务时非常舒服。实测下来每秒万级的 binlog 事件Flink 侧延迟在秒级对游戏实时分析足够了。如果数据量再大可以调 Flink 并行度或者把一个大表按 ID 范围拆成多个同步任务。选型时还有一个可以讨论的方案如果你们有现成的 Canal 组件可以直接 Canal 到 Kafka、再用 Flink 或 Spark 或官方 clickhouse-kafka-connect 写 CK。这个无所谓对错只要延迟和吞吐满足需求即可。如果嫌 binlog 太重只是同步配置表每小时全量 load 一次可能都够用。关键是想清楚数据是给谁用的、延迟要求多高。3.2 数据同步管道搭建细节这部分我用 Flink SQL 来演示典型链路。第一步在 Kafka 建 Topic原表 binlog 以 JSON 格式写入。第二步创建 Kafka Source 表CREATE TABLE kafka_source_orders ( op_type STRING, database_name STRING, table_name STRING, id BIGINT, user_id BIGINT, order_amount DECIMAL(18,2), created_at TIMESTAMP(3), PRIMARY KEY (id) NOT ENFORCED ) WITH ( connector kafka, topic game_orders_binlog, properties.bootstrap.servers kafka01:9092, properties.group.id flink-ck-sync, scan.startup.mode earliest-offset, format json );第三步创建 ClickHouse Sink 表CREATE TABLE ck_sink_orders ( id BIGINT, user_id BIGINT, order_amount DECIMAL(18,2), created_at TIMESTAMP(3), PRIMARY KEY (id) NOT ENFORCED ) WITH ( connector clickhouse, url clickhouse://ck01:8123, table-name game.orders, sink.batch-size 1000, sink.flush-interval 1s );第四步执行同步写入语句INSERT INTO ck_sink_orders SELECT id, user_id, order_amount, created_at FROM kafka_source_orders WHERE op_type INSERT;同步时注意字段类型映射MySQL 的 datetime 到 CK 通常是 DateTime 或 DateTime64注意精度MySQL 的 decimal 到 CK 用 Decimal(18,2)别用 Float否则金额精度会丢字符串字段要处理 Nullable 和空字符串默认值否则写入报错。我踩过的一个坑是 binlog 里日期字段是字符串直接写入 DateTime 列时 Flink 默认格式不匹配导致大量脏数据进入 error topic。解决方法是给时间字段在 SELECT 时用CAST或DATE_FORMAT函数标准化。另外写入 CK 的 Sink 并行度不建议开太高ClickHouse 是批量写入友好型数据库单批次大点写比高并发小批次更优。我们最终配置是 4 并行、1 秒 flush 或者 1000 条批量写效果最稳。3.3 查询优化三板斧排序键、物化视图与预聚合ClickHouse 查询性能好但前提是你会建表、会设计否则照样能跑到十秒。我的优化三板斧是第一排序键。所有 MergeTree 表的 ORDER BY 必须围绕主流查询模式设计。越靠左的字段会被优先用于索引剪枝和排序。比如我们有按渠道分析的需求渠道基数不大但查询频率很高可以考虑把 channel_id 放进排序键。如果索引设计不理想可以用ALTER TABLE ... MODIFY ORDER BY修改但这会触发后台 merge数据量大时会有一段时间的 IO 压力最好在低峰期做。第二物化视图。ClickHouse 的物化视图本质是插入触发器写数据时会同步写一份预聚合结果表。我们给常用指标建了小时级聚合视图比如每分钟的用户活跃数、付费金额、关卡通过率。查询时直接从视图读避免扫描全表明细。注意视图数据是实时写的但如果主表有历史数据补录视图不会自动回填历史需要自己写回填任务。第三预聚合与应用层缓存。对留存率这类复杂指标直接用 Flink 或定时任务算好结果写进 SummingMergeTree 表分析接口直接查聚合表不再通过明细表计算。这套模式的查询响应基本在 100ms 左右。给出一个物化视图片段CREATE MATERIALIZED VIEW game.mv_user_dau_hourly ENGINE SummingMergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_type, hour) AS SELECT toDate(event_time) AS event_date, toHour(event_time) AS hour, event_type, uniqExact(user_id) AS uv, count() AS pv FROM game.event_detail GROUP BY event_date, hour, event_type;这里有个非常容易踩的细节物化视图里的uniqExact写的是状态查询时仍需调用uniqExact(user_id)或uniqCombined(user_id)读取不能直接 SUM 那个 uv 字段否则大量内存会花在状态合并上很容易 OOM。4. 日常运维中的问题排查实录4.1 查询从 60ms 变成 6s一次典型的性能调查上线一个月后监控群里有人反馈运营后台一个“渠道留存漏斗”报表变慢了从之前的 60ms 变成 6 秒。排查过程还挺典型。第一步看 query log。ClickHouse 自带system.query_log表能查每次执行耗时、内存、读写字节。我们发现这个查询扫描了事件明细表几乎全量分区partition 数量达到 200 多个说明 WHERE 里时间条件没有生效。深入看 SQL发现报表工具把时间条件写成了字符串传参参数类型是 String而表里是 DateClickHouse 做了隐式转换整个查询走了全表过滤。解决方法是把时间参数在客户端转成 Date 类型再拼进 SQL。建议在应用层统一用toDate()函数包裹时间字段或者在 SQL 里写WHERE event_date toDate(?) AND event_date toDate(?)。第二步看 parts 是否健康。某个分区可能有几万个 parts说明合并线程跟不上写入查询要读更多片段。用SELECT table, count() FROM system.parts WHERE active GROUP BY table检查必要时通过OPTIMIZE TABLE ... FINAL手动触发合并注意选低峰期。第三步用EXPLAIN indexes 1 SELECT ...看查询计划确认是否走索引、读取了多少 granule。如果 granularity 很高但 reads 很多那排序键大概率设计不合理。4.2 数据重复ReplacingMergeTree 的最终一致性用 Flink CDC 同步 MySQL 到 CK 后运营发现订单金额偶发翻倍。排查后发现Flink 任务重启时binlog 重复消费而 CK 的表如果用 MergeTree写入的重复行就是真实重复数据用 SUM 计算总额时自然翻倍。解决办法一是用 ReplacingMergeTree按业务主键设置 ORDER BY 和 version 列例如ORDER BY (order_id) SETTINGS version updated_at。注意 ReplacingMergeTree 的去重发生在后台 merge 时不是写入时立即去重所以查询时如果用FINAL或配合GROUP BY加argMax才能拿到准确结果。如果对一致性要求更高可以在 Flink 端做 keyBy 去重用 state 记录最新记录但这样会增加 State 开销。最终我们生产环境采取的策略是CK 层用 ReplacingMergeTree 做兜底Flink 端再按主键做去重双保险。查询层在实时大屏用SELECT SUM(order_amount) FROM ... GROUP BY date时会出现偶发偏差但心理预期是最终一致通常在几分钟内后台 merge 完成后自动恢复正常。4.3 磁盘告警与 TTL 数据生命周期管理游戏日志明细数据特别大一直不清理会撑爆磁盘。我们的策略很简单业务要求明细数据保留 180 天超过部分直接物理删除。这个需求用 ClickHouse 的 TTL 非常方便建表时加TTL event_date INTERVAL 180 DAY再配合TTL event_date INTERVAL 90 DAY TO DISK cold可以把 90 天前的数据移动到冷盘180 天前删除。但 TTL 也不是完全没有坑。某次我们的 TTL 不生效原因是一台机器的merge_with_ttl_timeout被改大了且后台 merge 压力大TTL 合并迟迟不执行。建议定期用SYSTEM STOP/START MERGES控制合并窗口低峰期强制触发合并同时监控system.mutations队列长度。另一个坑是分区目录包含 TTL 偏移刚设置 TTL 的旧分区可能不立刻重写只有新写入的数据才会正确带上 TTL 路径。所以我们在迁移大表时先手动把历史分区卸载到冷盘或直接 truncate让规则表从头跑。另外不要把过期数据都丢进一个冷盘分区ClickHouse 支持 Disk 分卷可以把冷热分离做得更细。如果数据量没那么大TTL to DISK 冷盘就够用了再省一点可以直接 drop partition。4.4 性能调优清单最后汇总一份我实际调优时反复对照的清单按优先级排列检查项默认值建议值或策略max_threads0自适应大查询可设为 CPU 核数max_memory_usage默认大单次查询内存限制防 OOMmax_bytes_before_external_group_by0无限制超限时用磁盘外部聚合防止 GROUP BY 内存爆background_pool_size默认 16磁盘合并线程可按 CPU 调整max_concurrent_queries默认 0限制并发数避免高峰期拖垮集群index_granularity8192数据量不大可保持量大可适当调大flatten_nested1Nested 结构选择需要时了解嵌套效果这些参数改完后通常要重启集群才能生效修改前记得备份 config.xml 和 users.xml。还要提一句ClickHouse 单机性能虽然强但到了更大规模还是要做集群分片。我们目前单机 16 核 64GB 能扛住几十亿条明细但随着游戏数和埋点事件变多后面还是要上多副本加分片集群。扩容时横向加节点、配置 Distributed 表又是一个大课题。最后分享一点我的个人体会引入 ClickHouse 之前我一直以为数据分析的瓶颈在计算引擎后来折腾完整个链路才发现真正的工程难度在数据建模和管道稳定性。CK 把查询从分钟级拉到秒级很容易但如果排序键、物化视图、权限设计没做好后面每个新需求都会让你想骂人。如果你的团队也在调研 OLAP 引擎建议先在真实业务数据上做一版完整的小链路验证别只看官方 benchmark。毕竟引擎好用是一回事能不能在你家复杂的埋点、审批和变更流程里扎稳根那才是另一回事。