游戏数据分析有一个绕不开的矛盾业务迭代快数据量涨得更快而运营同学要的报表又往往很急。我之前在负责一款中大型卡牌游戏的数据平台时遇到过最典型的一个场景——运营早上十点提需求要看昨天的付费转化漏斗和重点副本的通关率同时还要能下钻到具体等级区间、职业维度。按照传统关系型数据库的思路这种多维度、大时间跨度的聚合查询写出来的SQL基本要把一张几千万行的明细表反复扫几遍别说十点了中午能不能出结果都是问题。后来我们把OLAP层重做核心引擎换成了ClickHouse整套报表的响应时间从分钟级压缩到秒级甚至毫秒级数据平台才真正能跟上业务的节奏。这篇内容我就结合实战项目把ClickHouse在游戏大数据分析中的选型思路、部署方案、数据建模和查询优化一整套实践梳理出来也把踩过的坑一并记上。1. 游戏数据分析的困境为什么需要ClickHouse1.1 游戏业务数据的特点与常见分析需求游戏行业的数据形态和电商、金融有明显差异。先说量。一个DAU过两百万的游戏玩家行为日志一天下来就是几十亿条单条事件比如登录、登出、副本开始、副本结束、抽卡、升级、购买道具都有几十个属性字段。这些数据如果落在MySQL或者普通关系型数据库里不考虑磁盘占用光是写入这一关就扛不住后续查询更是灾难。再说维度。游戏分析的核心指标里DNU新增用户、DAU/WAU/MAU日/周/月活跃、留存率、付费率、ARPU、ARPPU、LTV这些每一个都需要按渠道、版本、角色职业、等级区间、服务器分组去切分。玩家生命周期分析还会涉及首登时间、首付时间、流失前最后活跃时间这些事件型的维度。这种事件明细 多维切片 累计聚合的组合正是OLAP联机分析处理引擎最擅长的场景而传统OLTP联机事务处理数据库的设计初衷根本不在这里。游戏业务还有一个特点热数据变化快冷数据越积越多。一个新区开服前两周新玩家的行为数据占绝对比重查询热点集中在最近七天的数据上但LTV分析和长线留存分析又必须要看90天甚至180天前的老数据。这时候系统就得既能快速写入当前数据又能对历史大数据做低延迟的聚合扫描。ClickHouse的列式存储和稀疏索引在这种场景下具备天然优势读取时只需要扫描相关列而不是整行整行地读磁盘。1.2 传统方案在游戏分析场景中的瓶颈早期我们的分析查询走的是MySQL主从加一张大宽表把玩家注册信息和主要行为字段都冗余进去。一开始数据量在几千万行时还能应付但等到日活起来事件数据过亿之后问题集中爆发聚合查询极慢。一个含多个GROUP BY字段、加HAVING过滤的查询在innodb引擎下要走全表扫描动辄几十秒经常把数据库CPU打满。运营同学反馈最多的就是报表打不开。索引失效严重。要在几十个维度字段上建索引根本不现实普通B树索引对高基数维度如玩家ID、设备ID基本没有选择性优化器最终还是会选择全扫。写入与查询互相争抢资源。业务库本身在跑事务分析查询一来慢SQL直接把主库拖垮最后只能靠从库分担但数据延迟又导致报表数据对不上。我们也尝试过用Elasticsearch来替代。ES对日志类全文检索很友好在做玩家操作流水追踪时确实能用。但一旦涉及按渠道统计三天内不同等级玩家的平均在线时长这种多条件聚合ES需要把符合条件的文档全部拉出来做bucket聚合内存开销大深度分页更是噩梦最终效果也不理想。1.3 ClickHouse进来之后解决了什么问题ClickHouse接手之后最直接的变化就是查询引擎不再怕扫全表。因为它本身就是为这种方式设计的列式存储保证每条查询只读取用到的列向量化执行让CPU在单核上能做更多数据计算。比如我们日常跑某活动期间各服务器男性玩家付费率这样的查询明细表7亿行按服务器分组、性别过滤、付费金额聚合在ClickHouse里基本一两秒内出结果换以前MySQL这个量级基本只能放弃实时查询。另一个大改善是热数据实时写入能力。ClickHouse的MergeTree家族表引擎能把小批量写入落成小的数据片段data part后台再自动合并写入吞吐可以打得很高。配合Kafka消息队列我们把玩家行为事件流做近实时的ETL入库端到端延迟控制在秒级。运营后台的实时大屏和离线报表共用同一份数据底座第一天接进来的时候数据组同学都在感慨原来分析平台也能像业务系统一样秒回。2. 选型ClickHouse与Doris、ES的对比逻辑2.1 游戏业务场景对OLAP引擎的核心诉求选型的本质是匹配业务场景。我们当时把需求拆成四条第一查询并发不能太低。运营、数分、投放大概几十人日常在看各种看板报表系统后端会同时发起大量查询引擎要能撑住几十到上百的QPS每秒查询数。第二写入吞吐要够。实时行为日志每天几十亿条要求单节点每秒至少写入几万事件且不能在写入端压出明显瓶颈。第三运维成本可控。团队不大不希望引入太复杂的依赖组件最好一个引擎能自带高可用和横向扩展能力。第四查询语言要灵活。游戏分析经常要做同环比、留存矩阵、漏斗计算SQL风格的接口最实用业务同学也容易上手。2.2 三款主流引擎的对比与取舍先看Elasticsearch。它在倒排索引和全文检索上确实强大查询接口也灵活但做分析聚合时内存消耗很大尤其在大量GROUP BY场景下节点堆内存经常报警。我们实际测过同样一个多维度聚合查询ES在数据量过亿后响应时间明显上涨超时率变高ClickHouse在同等资源下能保持稳定低延迟。最终ES在我们的架构里退回到日志检索定位负责查流水、查单条行为链路不再承接分析聚合。再看Apache Doris。Doris这年头的社区发展很快它的优点在于自带分布式事务、强一致的物化视图并且整体架构像传统MPP数据库用起来对MySQL习惯的人非常友好。在需要频繁更新明细数据、且一致性要求极高的场景下Doris确实有优势。但我们当时的业务以append-only的日志型数据为主体更新需求很少时效性也不敏感到要秒级强一致。ClickHouse在这种场景下的运维模型更省心加上生态里与Kafka、Flink的对接成熟最终我们选择把ClickHouse作为离线实时统一分析引擎。如果你现在的场景是需要对订单数据做高频upsert、偶尔还要跨库join那Doris值得重点评估但要是每天海量事件日志做聚合分析ClickHouse的性价比更高这个结论到现在我依然这么认为。2.3 选型清单参考做技术选型时建议不要只看舆情拿自己业务的数据集做一个标准的压测脚本最靠谱。我整理了一套简单的选型基准项对比项测试方法参考结论写入吞吐用Flink写模拟日志统计每秒写入行数ClickHouse和Doris都能达到数万行/秒ES明显偏低聚合查询跑按三个维度分组、两个聚合指标的标准查询ClickHouse平均响应最快ES在高基数分组下内存易爆点查按主键ID查一条最新值ClickHouse用主键索引可以做但不如ES/Lucene方案对文本检索那么强数据更新模拟更新已有明细行Doris的unique模型更顺手ClickHouse走ReplacingMergeTree或DELETE/UPDATE能力受限运维复杂度部署三节点观察日常监控ClickHouse依赖较少副本同步机制重负载下要做好Merge、ZooKeeper队列监控每个团队情况不同但把这套基准跑完选哪个基本就清楚了。3. 集群部署与基础配置从单机到分布式的踩坑记录3.1 硬件选型与资源规划很多新手一上来就问部署参数却忽略了硬件规划才是第一步。ClickHouse是CPU密集型的分析引擎尤其靠向量化执行和SIMD指令集吃饭CPU主频高、核心数多带来的提升非常明显。我们生产环境用的是16核32线程、128GB内存的机器本地盘用NVMe SSD。磁盘方面有个重要经验临时数据和合并过程中的中间产物都很吃IO如果预算允许尽量用SSD而不是机械盘。我们早期测试在机械盘上跑大聚合查询响应时间比SSD高了差不多3到5倍MergeTree合并时IO也更容易成为瓶颈。关于内存官方建议单个查询的内存上限要控制好不能把物理内存全部吃光。我们按每台128GB的机器在用户配置里把max_memory_usage设置到80GB左右保留一些余量给系统页缓存。页缓存对ClickHouse很重要重复查询同一张热表数据可能直接命中OS缓存延迟会低到毫秒级。如果数据规模在百亿行往上单机会很吃力。我们的路径是先用单节点验证schema和查询逻辑业务逻辑跑通后再扩展到3分片2副本的集群。ClickHouse分布式方案是基于分布式表Distributed table加本地表Local table数据通过分片键均匀打散到各个节点查询走分布式表做结果汇聚。扩容时需要注意Distributed表的写入是异步转发延迟高时容易积累在内存里需要监控system.asynchronous_metrics里的分布式发送队列长度。3.2 版本选择与集群部署详细步骤版本选型上我们的经验是尽量选稳定版且闭源特性未大改的版本。这里以在生产环境实际验证过的21.8.15.7为例简单记录部署过程。先准备三台Linux服务器都配置好Java环境和基础网络然后每台机器执行安装。以CentOS/RHEL系的rpm安装为例sudo yum install -y yum-utils sudo rpm --import https://packages.clickhouse.com/rpm/clickhouse.asc sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse-rpm-stable.repo sudo yum install -y clickhouse-server clickhouse-client安装完成后先不要急着启动。编辑主配置文件/etc/clickhouse-server/config.xml建议先检查几个关键项listen_host改成内网IP开放给其他节点和上层应用访问max_connections适当调大默认值对高并发查询不太够数据目录和日志目录如果放在独立数据盘记得修改path和tmp_path并配置好目录权限zookeeper配置多节点地址副本同步依赖它。首次启动可以用sudo systemctl start clickhouse-server确认没报错后用clickhouse-client连入执行SELECT version()。启动之后还有一步容易被忽略修改默认用户密码。默认配置里default用户是免密的这个在公网环境非常危险。在/etc/clickhouse-server/users.d/下新建一个default-user.xml覆盖密码配置yandex users default password你的强密码/password networks ip::/0/ip /networks /default /users /yandex一个常见的报错是DB::Exception: Cannot create directory /var/lib/clickhouse多半是数据目录权限不对把目录owner改为clickhouse:clickhouse再重启就行。3.3 核心配置参数与优化点生产环境跑起来后有几个参数建议反复调优。第一个是max_memory_usage。默认值太大时多个大查询并发过来容易直接把节点内存打爆。我们按QPS和单查询复杂度权衡每个查询限制在50GB左右加上max_memory_usage_for_all_queries在节点维度做总量控制实测可以避免绝大多数内存溢出问题。第二个是后台合并线程数。MergeTree的merge过程是影响查询性能的关键。如果分区过多、合并跟不上system.merges里能看到堆积。可以适当调高background_pool_size但注意合并线程会同时占用IO盘弱的时候反而会拖慢写入。我们SSD盘上设为8遇到大促活动日写入猛增时再临时调低给写入让路。第三个是index_granularity。默认8192对于大部分日志分析场景是合理的。如果做的是点查比较多的业务可以适当把粒度调小提升索引精准度但副作用是索引文件变大。游戏行为日志聚合多我们保留默认值效果最好。还有一点如果有多个查询用户和写入用户建议分开账号。分析账号只读权限写入账号只能insert/select避免误操作drop表。这个纯属经验教训曾经有同事在临时查询时写错表名把一张累计表drop掉还好有备份才没酿成大祸。4. 数据链路与建模Flink同步MySQL与埋点流的落地4.1 从业务库同步Flink CDC到ClickHouse游戏业务里账号、角色、装备、公会这类维度数据还是存在MySQL里的。要支撑分析我们得把这些主数据实时或者准实时同步到ClickHouse和事件明细关联着用。这里的基础方案是Flink CDC。Flink CDC直接解析MySQL的binlog把增删改操作层层包装成DataStream再通过Flink的ClickHouse连接器批量写入。整个链路大概是MySQL binlog - Flink CDC - Flink任务 - ClickHouseFlink任务的实现大致是用MySqlSource构建SourceFunction配置好数据库地址、用户名密码和监听表再用ClickHouseSink输出。需要注意的一点Flink CDC默认是精确一次语义但要真正实现端到端的exactly-onceClickHouse侧还要开启insert_distributed_sync或者依赖分布式表的事务机制配置上更繁琐。我们实际容忍了最多几秒的数据可见延迟所以采用at-least-once模式靠ClickHouse的ReplacingMergeTree按业务主键去重效果完全够用。同步表结构时我们不会把MySQL里所有字段1:1照搬。维度表通常只保留分析要用的字段这样可以减少存储占用和查询扫描宽度。比如玩家角色表同步时只保留角色ID、账号ID、区服、职业、创建时间、最后登录时间这些字段文案类的字段直接丢弃同步速度和分析速度都快很多。4.2 埋点行为事件实时写入Kafka到ClickHouse行为事件这部分我们的架构是客户端埋点数据先汇集到Kafka再由Logstash或者Flink做轻清洗写入ClickHouse。事件表设计有一个经典选择一张大表放所有事件还是按事件类型分表我的经验是不要把所有事件都塞进一张表。游戏事件差异太大了战斗事件的字段有伤害数值、通关时间、剩余血量付费事件的字段是商品ID、金额、渠道回调把这些字段全部塞进一张宽表空值会占大量空间而且查询计划会变复杂。我们按几个核心域拆开event_login、event_pay、event_battle、event_item_flow每个建独立的MergeTree表字段都贴合该域的事件模型。确实需要跨域统计时ClickHouse的join能力可以兜底但尽量少用。Kafka到ClickHouse的写入我们用了一个小技巧Kafka的partition数量最好和ClickHouse分片数量成倍数关系并且指定一个稳定key比如server_id或者player_id这样同一个玩家的行为大概率会落在同一个分片后续按玩家维度做漏斗分析时能减少分布式查询的网络开销。4.3 表引擎选择MergeTree家族在游戏业务中的应用表引擎是ClickHouse建模的核心选错了后面再改成本很高。我按游戏业务的几种典型场景拆开说。最常见的原始事件明细表用标准的MergeTree就够。它的优点是写入快、查询快后台自动合并排序。虽然它不支持主键唯一约束但游戏行为日志本来就是不可变流式数据根本不需要update所以这是最自然的选择。玩家档案、账号信息这类需要持续更新的维度表用ReplacingMergeTree。它在合并时按指定的去重键保留最新版本。比如我们做当前账号等级分布就用这张表按account_id去重合并时保留最新等级。有一点坑必须提醒ReplacingMergeTree的最新是按插入顺序判断的如果你用Flink回放历史数据时不按事件时间排序可能会把旧数据当成新数据保留同步任务里要按更新时间字段排序。做留存或者付费聚合时可以用AggregatingMergeTree它能把相同维度的聚合状态自动合并比如存sumState、uniqState。我们用它做每日渠道×版本×新老用户的uniq留存状态查询时直接用uniqMerge取值计算量被大大摊薄报表响应速度飞快。至于SummingMergeTree适合做累计型计数器比如在线时长、金币产出消耗总量。我们比较少用因为聚合维度组合太多用一个低基数的分区键反而不灵活。4.4 排序键、分区键与TTL设计排序键和分区键经常有人混淆。分区键是数据块物理隔离的单位排序键是每个数据part内部行的排序规则。分区键的选择对写入和查询影响很大但选择过多分区也会导致小part数量爆炸。我们的事件表分区键按天toYYYYMMDD(event_time)因为绝大多数游戏报表都是按天粒度查看这样查询某一天的数据时可以快速跳过其他分区。如果你的游戏常做按小时粒度的分析可以改按小时分区但分区数量要多很多后台merge压力也更大按天是更稳妥的默认选择。排序键设计要遵循一个原则让高频过滤字段排在前面。游戏事件表里的高频过滤条件是event_time、server_id、player_id所以我们的排序键就按(event_time, server_id, player_id)排。这样查询自然能利用稀疏索引快速跳过无关数据。TTL数据生命周期这块是很多团队忽略的。游戏明细数据的价值随时间衰减很快半年后的行为日志基本没人查但存储和备份成本还在。建议在表创建时就规划好TTL比如事件表设TTL 180天到期自动删除维度表可以设久一点或者不设。要注意TTL的合并执行是异步的如果数据量大光靠TTL回收不够及时可以手动OPTIMIZE TABLE ... FINAL触发但会消耗资源尽量放在低谷期执行。5. 查询优化让复杂玩法分析秒级返回5.1 典型游戏分析SQL的写法与优化在游戏数据分析里有几个高频SQL模式写法和优化思路基本固定。第一个是留存计算。游戏运营经常问7月1日新增用户在3日、7日、30日后还有多少活跃。最直观的写法是join新用户表和时间表但跨多天join在ClickHouse里很重。推荐方式是把每日活跃用户表按player_id做uniq聚合两次再计算留存用户数。用Retention函数在ClickHouse里也能做但要注意参数顺序。我们实际更常用预处理每天凌晨跑一个任务把某日新增用户在后续第N天是否活跃提前算好写入结果表运营查报表时直接点条件毫秒级返回。第二个是漏斗分析。比如注册-创建角色-首次付费-再次付费的四步漏斗事件可能是多个表的。这里推荐用windowFunnel函数它能在一条SQL内按时间窗口对每个玩家做有序事件匹配SELECT level, count() AS user_count FROM ( SELECT player_id, windowFunnel(86400)(event_time, event_type register, event_type create_role, event_type first_pay, event_type second_pay) AS level FROM event_all WHERE event_date 2024-11-01 GROUP BY player_id ) GROUP BY level ORDER BY level;这个函数在ClickHouse 21.x版本已经很成熟底层是向量化实现跑几千万玩家也能秒级返回。注意时间窗口单位是秒跨天漏斗要把窗口设成超过24小时。第三个是排行分析。比如全服各服务器玩家等级Top100。如果全量排序很消耗内存。建议用LIMIT n BY子句按server_id分组后再每组取前n行然后在分布式表上做汇总能有效减少节点间传输量。5.2 物化视图与预聚合ClickHouse的物化视图是增量式的数据插入时实时触发视图计算结果追加存储到目标表。这里要强调物化视图不是传统数据库里查询时动态计算后返回的视图它更像一个insert触发器。所以非常适合做高频报表的预聚合。游戏里典型的应用场景是实时活动监控。我们建了mv_activity_realtime_stats物化视图按活动ID、区服、事件类型、分钟级时间窗口去聚合在线人数和付费金额。写入事件明细表后视图结果表实时更新大屏和运营看板直接查询结果表无论底层明细涨到多大查询都保持恒定快。物化视图也有几个限制需要接受。第一它不会主动重算历史数据如果改动了视图逻辑只能手动INSERT INTO SELECT刷历史。第二多级物化视图的链路太长出了问题不好排查建议最多两级。第三聚合状态要选对函数uniqState和sumState可以嵌套计算但avgState这种要小心底层的中间状态不具备完全可加性跨多天视图做再聚合时会有精度差异。5.3 索引与投影的取舍ClickHouse除了主键稀疏索引还支持跳数索引Skip index。对游戏数据比较常用的跳数索引是bloom_filter或minmax。比如玩家战斗事件表里经常按battle_typepvp、pve、gvg过滤。这个字段基数不高可以直接建一个bloom_filter跳数索引查询时就能跳过大量不含目标类型的数据part块。跳数索引不是越多越好每个小part都要计算和存储索引数据写入侧有额外开销一般只对过滤频率高且区分度高的字段设置。投影projection是21.8后很实用的特性。它允许你在原表内维护一份按不同排序方式组织的数据副本查询时优化器自动选择是否命中投影。比如事件表主排序是(event_time, server_id, player_id)但经常跑按player_id统计总付费金额的查询如果只是按player_id聚合扫描整张表就很浪费。可以建这样一份投影它把player_id放到排序键前面。这样查询时若走投影路径数据扫描量和聚合时间会明显减少。要注意投影不是银弹每份投影都会增加写放大和存储成本。我们只在最热的三四个查询模式上建立了投影其他场景靠合理排序键硬扛整体收益很正面。6. 常见问题与排查技巧实录6.1 高频异常速查表把生产环境遇到的问题整理成速查表先给结论再展开。问题现象可能原因处理建议查询报Too many parts写入频率太高小part数量超过阈值优化写入端batch大小降低insert次数后台调大merge线程临时用OPTIMIZE ... FINAL合并单查询内存爆掉Memory limit exceeded聚合的维度组合太多数据量超过内存预算在users配置里降低max_memory_usage改写SQL减少GROUP BY字段或加过滤条件分布式表查询很慢数据分片不均或节点间网络带宽不足检查分片键是否均匀必要时按server_id或player_id重分布看system.query_log里remote查询耗时占比数据同步延迟越来越大Flink到ClickHouse写入batch太小或后端merge跟不上加大batch size和flush间隔适当调低写入并发数瓶颈经常在磁盘IO副本间数据不一致ZooKeeper会话超时或网络抖动检查核心节点上system.replicas状态必要时SYSTEM RESTART REPLICA恢复监控ZK队列长度TTL数据没有如期删除TTL的merge触发周期较长或数据一直处于非active part等后台merge执行或手动触发分区合并6.2 Too many parts的深度排查与解决这个报错在游戏业务大促、新服开服期间几乎必现。原因是合并速度跟不上小数据片段part产生速度。我们当初接到线上告警时第一反应是调合并线程但合并在高峰期和写入抢IO反而让写入更慢形成恶性循环。正确思路是先量化再调整。查看当前parts数量SELECT partition, count() AS parts_count FROM system.parts WHERE active AND table event_battle GROUP BY partition ORDER BY parts_count DESC LIMIT 10;如果某个分区parts数量超过300基本扛不住了。我们的处理方案分三层第一层是写入端把单次insert的数据量从1万行提到5万到10万行降低插入频率第二层是合并端错峰调高background_pool_size让大合并任务在凌晨执行第三层是表结构端检查是否分区过细如果为了按小时查而建了小时分区维护成本高且收益有限改成按天分区更稳。6.3 慢查询定位与改写思路一旦出现慢查询先用system.query_log查那段SQL的实际耗时和内存占用。定位到具体语句后基本从三个方向找原因是否select了不需要的列。很多人习惯SELECT *对列式存储来说等于要扫描全部列存储和IO都白白浪费。是否在全表扫描后做复杂计算。比如GROUP BY了超高基数的字段如player_id动辄千万级的分组。如果可以先去重再聚合或者利用物化视图预计算效果完全不同。是否出现不合理的JOIN。ClickHouse的JOIN右侧表会加载到内存如果右侧是大表会炸内存把JOIN改成IN或者用字典表dictionary关联维度性能提升很明显。6.4 同步延迟问题的一个反直觉案例分享一个比较隐蔽的坑。我们某个Flink同步任务连续几天延迟都在增大看了Kafka的lag不高Flink反压也不严重问题出在ClickHouse端——因为目标表的分区键不是按天而是按server_id每天的数据part数量暴增merge队列积压写入吞吐直线下降。后来把目标表改成按天分区把server_id放进排序键问题立刻缓解。这个案例的教训是同步链路的性能瓶颈往往不在最直观的环节要顺着数据流逐段排查而ClickHouse分区设计对写入端的影响比很多人想象得大。建议做同步任务时先关注目标表的分区设计和分区part增长速度而不是一味堆写入并发。写到这里我想再分享一个真实的体会。ClickHouse在游戏分析里确实是利器但它不是万能药。早期我们总想着把所有查询都往里塞后来发现对于高频固定报表物化视图和预聚合才是最优解对于即席探索式查询直接跑明细表也能很快而对于偶尔才跑一次的跨月超大结果集先估算好资源再执行别把任务裸奔到生产集群上。这套实践做下来数据平台稳定性和研发同学加班量都得到了很大改善。如果让我给后来者一条最重要的建议那就是吃透MergeTree、分区和merge的本质比背一百条优化技巧都管用理解了底层机制任何新玩法分析需求过来建表和写SQL都能自然往高效的方向走。希望这篇记录能帮到正在做游戏数据分析并且被性能问题困扰的朋友们。