ClickHouse实战：Flink CDC实时同步MySQL，破解亿级数据分析性能瓶颈

我最早认真考虑 ClickHouse不是在看性能测评的时候而是被大数据体育分析的业务数据逼到墙角之后。2021年初我接手一个足球赛事数据平台的重构摄像机追踪系统每秒吐过来上千条坐标比赛事件流在MySQL里堆到两千多万行几个常用聚合SQL跑一次要三四十秒。比赛直播期间转播方和教练组等着看实时控球率那边主播都开嗓了这边数据还没算出来那个场面真的很抓狂。后面我做了个决定线上业务继续留在MySQL分析负载全部切到ClickHouse再用Flink把MySQL数据实时同步进ClickHouse。调整完之后效果很直接——原来三四十秒的聚合查询压到几十毫秒直播中按分钟刷新的全场统计也稳定扛住。这篇文章就是这套方案从0到1的完整记录包括数据管道设计、Flink同步细节、ClickHouse表建模、Linux部署集群的实操以及最后跟Doris做选型对比的思考。如果你也在做体育类数据分析或者手里压着千万级事件流不知道往哪放这篇应该能帮你少走不少弯路。1. 体育数据平台的瓶颈为什么一场比赛能把MySQL拖到崩溃1.1 先算清一笔账单场比赛与整个赛季的数据量写代码的人经常低估体育数据量级。我拿足球赛举例让大家有个体感。场上22名球员加1颗球定位追踪系统按25Hz采样每秒产生575条坐标记录一场比赛按110分钟净时间算定位数据就有接近380万行。事件类数据虽然没这么夸张但射门、传球、盘带、抢断每条都带起止坐标、球员ID、对手ID和速度方向场均也在几十万条。如果把摄像机捕捉的战术标签、裁判判罚事件也加进去一场主流赛事大约能产出400万到500万行明细数据。赛季维度更夸张。五大联赛一个赛季每家俱乐部打38轮你分析范围如果是几个联赛加杯赛再配合多年历史数据表轻松过亿。我这边平台当时持有8个赛季的历史数据事件明细超过5亿行聚合查询在MySQL上基本做不了。数据源单场数据量备注定位坐标~380万行22人球25Hz90分钟补时事件流30-50万行传球/射门/抢断/犯规等传感器体能10-20万行心率、跑动加速度周边数据数十万行票务、转播观看、社交互动所以这个场景的本质是高吞吐写入、大跨度历史、秒级聚合、多维度切片。传统的行存数据库在这里是很吃亏的。1.2 传统架构的三个痛点第一聚合慢。MySQL这类行存引擎要按行读取整条记录统计控球率时要扫全表IO开销大。数据量到千万行带group by和多个countIf的查询执行计划再优化也很难进秒级。第二热点与锁竞争。比赛进行中事件流持续写入同一场比赛的分区数据MySQL的锁机制和主从同步在写入高峰容易顶不住加上分析查询占IO经常把上游业务库拖慢。第三扩展困难。为了支撑分析需求MySQL方案最后总会演进成一堆冗余统计表加定时任务。每加一个指标就要写一段汇总逻辑凌晨批量算好白天拉报表。指标一多表数量爆炸口径还经常对不上——技术债全部变成运营和数据的吵架现场。我并不是说MySQL烂。业务系统、订单、用户中心放在MySQL上用得好好的但当分析负载压过来行存引擎真心不合适。这也是我后来坚持业务归业务、分析归分析把两个体系彻底分开的原因。1.3 为什么是ClickHouse列存、向量化和MergeTreeClickHouse能解决上面问题靠的是三个核心机制这也是后面所有表设计和SQL优化的底层依据。列式存储同一列的数据连续存放在一起做聚合时只需要把涉及的列读进内存跟行存整行读入再裁剪完全是两个能耗水平。体育事件表动辄60多个字段但统计控球率只需要team_id、event_type、time几个字段列存天然占优。向量化执行聚合、过滤、函数计算一次处理一批数据充分发挥CPU的SIMD能力。实际测试里5亿行事件明细做一次countIf聚合ClickHouse在普通服务器上跑约1到2秒MySQL在同机直接洗洗睡。MergeTree家族MergeTree是ClickHouse的存储引擎基座支持分区、排序、稀疏索引、后台合并还能通过副本复制。ReplacingMergeTree做幂等去重SummingMergeTree做预聚合——这两个引擎在我们体育场景里几乎是日常主力。外加ClickHouse支持大宽表几百列都没有问题可以把常用维度全部冗余在明细表里从根上躲开Join。这一点在第六节讲Doris对比时还要重点说。2. 整体链路设计Flink实时同步MySQL到ClickHouse的落地方案2.1 四层大数据架构里的分工整个数据链路我按标准四层切采集层、存储层、计算层、应用层。说得直白点就是数据源体育赛事供应商的接口、赛道追踪系统、MySQL业务库球队、球员、用户、票务采集与同步Flink CDC负责MySQL binlog到ClickHouse的实时同步接口数据通过定时任务落Kafka再进ClickHouse存储与计算ClickHouse既是存储层也是计算引擎承担所有分析查询和预聚合应用层BI报表、实时比赛大屏、教练组移动端、对外数据API这套设计解决了两个关键问题MySQL继续扮演OLTP角色承受高频读写ClickHouse专职OLAP承受高吞吐分析和秒级响应。两边井水不犯河水出问题也不会互相拖垮。2.2 Flink CDC同步的完整配置同步工具我选Flink CDC原因很现实Flink CDC直接订阅MySQL binlog不需要额外部署中间件增量捕获延迟低且Flink SQL写同步任务无需写大量Java代码运维同学也能上手。下面这套配置就是我们生产环境在用的骨架。Flink SQL里先定义MySQL源表CREATE TABLE mysql_match_events ( event_id BIGINT, match_id BIGINT, team_id INT, player_id BIGINT, event_type STRING, event_time TIMESTAMP(3), x_coord DOUBLE, y_coord DOUBLE, is_goal INT, updated_at TIMESTAMP(3), PRIMARY KEY (event_id) NOT ENFORCED ) WITH ( connector mysql-cdc, hostname 192.168.10.21, port 3306, username cdc_user, password ********, database-name sports_db, table-name match_events, scan.startup.mode initial, server-time-zone Asia/Shanghai );再定义ClickHouse目标表CREATE TABLE clickhouse_match_events ( event_id BIGINT, match_id BIGINT, team_id INT, player_id BIGINT, event_type STRING, event_time TIMESTAMP(3), x_coord DOUBLE, y_coord DOUBLE, is_goal INT ) WITH ( connector clickhouse, url clickhouse://node1:8123,node2:8123, sink.batch-size 1000, sink.flush-interval 1000, sink.max-retries 3, format json );最后一条INSERT INTO完成同步INSERT INTO clickhouse_match_events SELECT event_id, match_id, team_id, player_id, event_type, event_time, x_coord, y_coord, is_goal FROM mysql_match_events;scan.startup.modeinitial的意思是任务启动时先做一次全量快照再无缝切换成binlog增量历史数据和新数据一次性搞定。生产上注意把binlog格式设为ROW且给cdc_user授REPLICATION SLAVE、REPLICATION CLIENT相关权限否则任务起不来。2.3 同步链路里常见的三个坑第一个坑是时区。MySQL的DATETIME不带时区Flink CDC在server-time-zone配置不对时会跟ClickHouse的DateTime字段产生8小时偏差。我们统一约定MySQL连接串和Flink都指定Asia/ShanghaiClickHouse表字段带明确时区语义报表层再转UTC输出。这个坑造成的脏数据排查了我整整一天说出来都是泪。第二个坑是ClickHouse的并发写入合并。ClickHouse官方不推荐每批次一条地写Flink的ClickHouse connector默认按batch-size和flush-interval攒批我生产配置1000条攒一个批次写入写入吞吐稳定在每秒几万行。如果业务要求秒级可见可以把flush-interval调成500毫秒代价是ClickHouse的part数量增多合并压力变大。这个需要根据线上写入量来动态调没有绝对最优值。第三个坑是更新与删除。MySQL业务库经常会有撤销数据、人工纠错等操作binlog里对应是UPDATE和DELETE事件。ClickHouse不是为单行更新设计的我们把目标表设计成ReplacingMergeTree并在同步SQL里带上update事件重新INSERT全字段靠updated_at版本字段做去重。DELETE事件则需要单独写删除标记列或者定期用轻量删除清理。没有这一步你会看到同一个event_id在表里出现多行聚合口径直接错乱。提示改动MySQL表结构加列时Flink CDC同步任务通常需要重启才能拿到新的schema映射。体育建模里这个很常见——业务库今天加一个VAR审核状态明天加一个门线技术标记我们专门排了一个同步任务重启窗口。还有一个小提示ClickHouse连接串里多写几个节点Flink写入任务偶发断连时会自动切换我经历过一次node1重启靠这个配置稳住了整整一个赛季的数据同步没有断流。3. ClickHouse表模型设计让体育指标查询跑进毫秒级3.1 分区键、排序键和主键怎么定到了ClickHouse里建表不是一个SQL的事儿是先搭骨架再填肉的过程。分区键、排序键和主键三者职责完全不一样我见过太多人把三者混为一谈结果查询越跑越慢。分区键PARTITION BY控制数据在物理上按什么粒度切分主要用于数据生命周期管理。体育数据我按月份分区toYYYYMM(event_time)这样删三个月前的明细直接DROP PARTITION一秒钟的事不用DELETE扫全表。有人喜欢按天分区但按天会产生大量小part合并压力大查询反而退化。按月是体感和运维的平衡点。排序键ORDER BY这是ClickHouse索引的核心决定了稀疏索引的排布方式直接影响查询裁剪能力。我们的事件表明细排序键是(match_id, event_id)。为什么match_id放最前因为几乎所有分析查询都带比赛维度——某场比赛某队本赛季所有比赛——把match_id放第一位ClickHouse能利用索引直接跳过大量无关行。那为什么排序键里没有event_time这里有个容易被忽略的点ReplacingMergeTree的去重逻辑是以排序键为唯一标识的。同步链路上UPDATE事件会把整行重新插入一次想让它替换旧行排序键里必须有不会变化的主键标识event_id。match_id加event_time的组合可能因为人工修正而改变一旦event_time被更新新旧两行排序键不一致去重就失效了。所以我把event_time踢出排序键靠月份分区来兜住时间范围过滤这个设计在后续查询中验证是够用的。主键PRIMARY KEY注意ClickHouse的主键只是索引项不是唯一约束可以跟排序键重叠或者作为排序键的前缀。建表时如果只写ORDER BY不写PRIMARY KEY主键默认等于排序键。主键不要设置太长因为每个数据块的主键都要放内存太长了内存吃紧。我们主键就直接用match_id。举例我们的核心事件表明细生产脱敏CREATE TABLE match_events ( event_id UInt64, match_id UInt64, team_id UInt16, player_id UInt64, event_type LowCardinality(String), event_time DateTime, x_coord Float32, y_coord Float32, is_goal UInt8, is_success UInt8, updated_at DateTime ) ENGINE ReplacingMergeTree(updated_at) PARTITION BY toYYYYMM(event_time) ORDER BY (match_id, event_id) SETTINGS index_granularity 8192;event_type用LowCardinality做编码因为足球事件类型撑死就二三十种低基数字段开启字典编码后存储和CPU都有收益。3.2 宽表化与字典告别Join地狱ClickHouse的Join能力是相对弱项特别是大表跟大表做关联。体育分析恰恰需要把球员基础信息球队信息这些维度拼到明细上如果每次查询都去JOIN查询性能和代码复杂度都很糟。我的做法是宽表化字典双管齐下。宽表化在同步阶段直接把队伍名、联赛、赛季、主客场、球员姓名、位置等维度冗余进事件明细表。反正列存不心疼列数这张事件表从最初的20列慢慢扩到70多列单查询完全不需要Join。坏处是同步链路复杂一点但换来的是查询的简单和快——这笔账非常划算。字典低维度查找比如按球队ID拿球队名用ClickHouse自带的dict词典加载到内存后SQL里直接dictGet没有Join成本。SELECT team_id, countIf(event_type pass) AS total_pass, countIf(event_type pass AND is_success 1) AS success_pass, round(success_pass / total_pass, 4) AS pass_rate FROM match_events WHERE match_id 2024091501 GROUP BY team_id;3.3 物化视图把比赛指标预先算好有了明细表还不够。比赛进行中转播方和教练组高频刷新当前控球率双方射门比跑动距离每次都去扫几百万行明细即使ClickHouse能扛住也别这么糟蹋算力。物化视图是这里的正解。MATERIALIZED VIEW在数据写入时触发增量聚合结果落到物化表里查询时几乎零延迟。我们给分钟级比赛指标建了一张物化视图CREATE MATERIALIZED VIEW mv_minute_stats ENGINE SummingMergeTree() PARTITION BY toYYYYMM(event_time) ORDER BY (match_id, team_id, minute_bucket) AS SELECT match_id, team_id, toStartOfMinute(event_time) AS minute_bucket, count() AS event_count, sum(is_goal) AS goals, sum(is_success) AS success_events FROM match_events GROUP BY match_id, team_id, minute_bucket;查询实时比赛大屏时直接按match_id和minute_bucket查这张物化表毫秒级返回。另一个思路是定期把半场统计全场统计任务落成AggregatingMergeTree比如控球率、xG这样的复杂指标可以用中途聚合结果进一步压榨延迟。物化视图的代价是占存储、占写入CPU所以设计时要挑选真正高频的指标而不是把每个group by都建成物化视图。我是按被转播大屏或教练组高频拉取的指标优先物化这个原则来收敛视图数量目前线上11张物化视图明细表5亿行写入瓶颈远没到。4. Linux部署ClickHouse 21.8.15.7单机验证到集群分片4.1 安装方式与初始配置我们生产环境用的是LinuxCentOS系的rpm安装方式版本固定21.8.15.7。为什么固定版本大数据项目最怕版本漂移21.8这个分支稳定、新特性够用而且社区和运维文档齐全。升级的事后面再说先把业务跑稳。# 安装服务端和客户端注意版本号一致 sudo yum install -y clickhouse-server-21.8.15.7.noarch.rpm \ clickhouse-client-21.8.15.7.x86_64.rpm # 启动并设置开机自启 sudo systemctl enable clickhouse-server sudo systemctl start clickhouse-server # 用客户端验证 clickhouse-client --query SELECT version()如果公司内网严格没有外网yum源就用tar.gz离线包解压到指定目录写一个systemd脚本托管效果完全一样。需要提醒的是先把/etc/clickhouse-server/users.xml里default用户的密码改掉并把远程访问的listen_host配成0.0.0.0或内网IP否则服务开在localhost上等于白装或者裸奔在公网等于给别人送肉鸡。提示离线包安装时需要手工创建clickhouse用户和目录/var/lib/clickhouse、/var/log/clickhouse-serverchown好权限再启动。rpm包会自动处理但tar.gz离线包不会漏掉目录权限问题启动直接报错。ClickHouse默认就监听两个端口8123是HTTP接口用来给BI和Flink连接9000是原生TCP接口给clickhouse-client和集群内部通信。两个端口都要在防火墙白名单里按需放开。4.2 分片副本集群配置与ZooKeeper单机验证完性能后我们上了4节点集群2个分片、每个分片2副本。先说基础组件ReplicatedMergeTree引擎的副本同步依赖ZooKeeper所以集群搭建第一步是把ZK集群起来3节点即可然后在config.xml里配置。config.xml的remote_servers节点定义了集群拓扑remote_servers sports_cluster shard replica hostck-node1/host port9000/port /replica replica hostck-node2/host port9000/port /replica /shard shard replica hostck-node3/host port9000/port /replica replica hostck-node4/host port9000/port /replica /shard /sports_cluster /remote_servers然后配置ZooKeeper节点和remote_servers在config.xml里都是顶层节点zookeeper node hostzk-node1/host port2181/port /node node hostzk-node2/host port2181/port /node node hostzk-node3/host port2181/port /node /zookeeper四台节点的config.xml全部同步这份配置然后分别重启服务。建表时用ON CLUSTER让集群所有节点同步执行副本路径里用{shard}和{replica}宏替换CREATE TABLE match_details ON CLUSTER sports_cluster ( match_id UInt64, team_id UInt16, event_type LowCardinality(String), event_time DateTime, is_goal UInt8 ) ENGINE ReplicatedMergeTree(/clickhouse/tables/{shard}/match_details, {replica}) PARTITION BY toYYYYMM(event_time) ORDER BY (match_id, event_time);接着再用Distributed引擎建一张逻辑表把两个分片的数据合并视图提供给上层查询和Flink写入端CREATE TABLE match_details_dist ON CLUSTER sports_cluster AS match_details ENGINE Distributed(sports_cluster, default, match_details, rand());分布式表是查询的入口写入端写它查询端查它。数据按rand()轮询落到分片上对于按比赛整体分析的体育场景是够用的。如果后续某个分析场景有明确的shard key需求——比如想保证同一场比赛的数据落在同一个分片——可以把rand()替换成match_id这样还有机会做分片裁剪。4.3 权限与参数调优权限方面ClickHouse的grant体系支持列级和行级。我们给不同角色开了差异化权限分析师只能查select运营可以查明细但看不到球员薪资和商业敏感字段同步账号只能写不能查。列级授权按列名控制行级授权靠行策略row policy过滤这套东西在体育数据对外商业化时特别有用——至少不会因为权限问题把客户数据和内部数据搞混。参数调优我提三个最见效的max_memory_usage单查询内存上限默认10G我们按节点内存的60%调、max_threads默认CPU核数即可但并发大的时候要限制单查询线程数防止一个查询打满全集群、background_pool_size后台合并线程写入量大时调高避免part积压。另外如果大量使用低基数枚举字段建议把allow_suspicious_low_cardinality_types打开避免建表时报错。调优一定以监控为准。我用Grafana加clickhouse-exporter盯着系统表和进程指标看parts数量、merge队列、查询耗时分位数再做调节而不是凭感觉乱改配置。5. 实战场三类典型分析查询的性能表现5.1 比赛进行中的实时统计先看直播场景。转播大屏唤醒时需要实时拉取双方球队的进球、射门、射正、控球率、传球成功率还要每60秒刷新。生产上我们直接查mv_minute_stats物化视图SELECT team_id, sum(goals) AS goals, count() AS active_minutes FROM mv_minute_stats WHERE match_id 2024091501 GROUP BY team_id;这个查询在物化视图上毫秒级返回。如果没有物化视图直接扫match_events明细按team_id累加在千万行级别的单场数据上也只要一两百毫秒ClickHouse扛得住。所以物化视图解决的不是能不能查而是高并发刷新时不给集群制造压力。5.2 球员与球队多维对比赛后分析场景经常是本轮所有比赛各队传球成功率某球员最近5场的跑动距离、冲刺次数对比两位中后卫的防守动作分布。这类查询的特征是过滤条件多变、聚合维度不同。明细表配排序键基本都能应付。跑动距离这类指标在追踪坐标表上做用neighbor函数取相邻采样点求欧氏距离SELECT player_id, sum(distance_m) AS distance_m FROM ( SELECT player_id, if(player_id neighbor(player_id, 1), sqrt(pow(x2 - x1, 2) pow(y2 - y1, 2)), 0) AS distance_m FROM ( SELECT player_id, x_coord AS x1, y_coord AS y1, neighbor(x_coord, 1) AS x2, neighbor(y_coord, 1) AS y2 FROM player_tracking WHERE match_id 2024091501 ORDER BY player_id, sample_time ) ) GROUP BY player_id ORDER BY distance_m DESC;注意这里的关键是ORDER BY player_id, sample_time它是窗口逻辑的顺序基础ClickHouse的neighbor函数依赖这个顺序才能正确计算相邻点。if(player_id neighbor(player_id, 1), ...)是防止跨球员边界把两个不同球员的距离累加起来。我见过有人漏了这行结果跑动距离算出来的值全在瞎编——不用惊讶坐标追踪数据不按时间排序距离计算就是纯随机数。5.3 赛季大跨度趋势分析最后是运营和教练组最常用的趋势类查询按轮次看球队的状态曲线、球员赛季热力、射门转化率月度变化。这类查询的特点是时间跨度大如果按事件明细直接聚合需要扫很长的时间分区可以先按轮次聚合出中间结果再在外面套窗口函数算移动平均。一点说明ClickHouse的窗口函数在21.x上可以用但需要先开开关老版本执行SET allow_experimental_window_functions 1我们实际是把窗口函数跑在子查询聚合后的数据上把扫描量控制在最小范围。-- 老版本需要先 SET allow_experimental_window_functions 1; SELECT match_round, team_id, goals, avg(goals) OVER (PARTITION BY team_id ORDER BY match_round ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS rolling_avg_goals FROM ( SELECT match_round, team_id, sum(is_goal) AS goals FROM match_events WHERE season 2024-2025 GROUP BY match_round, team_id ) ORDER BY team_id, match_round;这种子查询先聚合缩小数据量外层再上窗口函数的写法是把窗口分析成本控制在最小范围的通用套路。直接对5亿行明细上窗口函数不是不行但数据扫描量会成倍增加运维账单也会成倍增加。最后给大家一个性能数据参考。线上配置是4节点每节点32核128G明细表5亿行单场比赛事件扫描加聚合在80到300毫秒之间波动赛季级查询按轮次聚合在1秒内返回物化视图查询普遍小于50毫秒。这套性能在同配置下MySQL做不到2020年我用MySQL的同样5亿行做季度统计跑了快20分钟。技术选型正确省掉的都是真金白银。6. ClickHouse与Doris的选型我在体育分析场景下的最终判断6.1 两类引擎的定位差异做技术选型时团队里也讨论过用Apache Doris。这里不谈营销话术只说我实际感受。ClickHouse是为单一大宽表聚合分析而生的偏科选手列存加向量化加稀疏索引聚合性能极其强悍但对分布式Join、高并发点查、高频单行更新这些场景设计上就是短板。Doris是MPP架构更均衡兼容MySQL协议、自带完整SQL优化器、支持分布式Join、Unique模型可以高效做upsert适合需要频繁更新、多表关联、高并发服务化查询的BI场景。体育分析平台如果用Doris球队、球员、赛事这些维度表是要频繁修改的——比如转会、换教练、改球员号码——Unique模型更新起来确实爽。但我们把维度字段全部冗余进事件宽表之后更新维度变成了重刷宽表频率大大降低。6.2 关键维度对比对比维度ClickHouseDoris大表聚合极强向量化列存专长强MPP并行点查询并发弱适合低频精确查找强适合高并发报表服务主键更新需ReplacingMergeTree合并非实时Unique模型实时upsert多表Join弱建议宽表/字典原生分布式Join运维复杂度单机即用集群依赖ZKFE/BE组件多部署门槛高MySQL协议有限兼容性好6.3 什么情况下我会换成Doris这个表不意味着Doris全面优于ClickHouse。纯粹比亿级事件明细做聚合Doris对比ClickHouse没有明显优势运维成本还更高。但如果你的体育分析平台要对外面向大量用户服务——比如球迷App里的实时积分榜、球员详情接口每个请求都是几十毫秒的点查或小范围查询并发几千——那Doris的MPP架构和对MySQL协议的兼容性会让它从容很多。ClickHouse硬顶这种高并发小查询会吃力需要在前面加一层缓存或查询网关架构变复杂。一句话总结我的选型逻辑分析为主、内部使用、数据量大、查询模式以聚合为主选ClickHouse服务化查询、高并发点查、频繁更新维度选Doris。我们业务后来多了一个球迷端实时排行榜需求我确实为那一小撮查询单独起了个Doris实例放着两边互不干扰。没有架构洁癖的人不会把鸡蛋放一个篮子。最后聊点跟技术无关但跟工作有关的体会。体育数据分析这个赛道表面是在处理一行行SQL本质是跟教练、转播方、运营的人性和需求打交道。教练要看这个球员为什么状态下滑转播要下一分钟给镜头哪边运营要本赛季哪个环节可以出内容。工具选对了只是第一步把技术指标翻译成业务语言才是长期被认可的关键。ClickHouse帮我省出了大量本应该做重复报表的时间让我有余力去思考这些问题。再分享一个很便宜但很值钱的习惯定期翻ClickHouse的system.query_log里面记录了每一条SQL的耗时和扫描行数每个月按耗时倒序看Top查询。一半的慢查询其实不是引擎慢而是查询写得太贪婪——比如明明只要team_id维度却把60多列的明细全select出来。每次从query_log里抓出这种查询优化一下一个月下来集群查询水位能肉眼可见地降一截。这个习惯比任何参数调优都省钱也最容易被忽略。