# 实时数据查询优化实战

大数据开发高阶技能ClickHouse/Doris/StarRocks 选型、实时查询优化、索引设计、物化视图、分区分桶、生产环境案例打造秒级查询体验 前言真实生产问题问题场景某电商公司数据平台遇到的问题 问题 1实时报表太慢 - 运营打开实时大屏加载要 30 秒 - 刷新一次等半天 - 业务投诉还不如看 Excel 问题 2并发一高就挂 - 平时查询 3 秒还能接受 - 大促期间10 个人同时查直接超时 - 错误Too many connections / Timeout 问题 3数据量一大就慢 - 1000 万数据3 秒 - 1 亿数据30 秒 - 10 亿数据直接超时 问题 4复杂查询跑不动 - 单表查询还行 - 多表 Join直接卡死 - 聚合查询内存溢出实时查询优化解决- 选型合适ClickHouse/Doris/StarRocks - 表设计分区/分桶/索引 - 查询优化物化视图/预聚合 - 资源隔离读写分离/队列控制优化后效果- 查询速度30 秒 → 0.3 秒100 倍 - 并发能力10 → 20020 倍 - 数据规模1 亿 → 100 亿100 倍 - 复杂查询从超时到 2 秒 实时 OLAP 引擎选型主流引擎对比特性ClickHouseDorisStarRocks开源时间201620182020公司Yandex百度星环科技查询速度⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐并发能力⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐Join 能力⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐运维复杂度⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐社区活跃度⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐国内支持⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐选型建议选择 ClickHouse 的场景✓ 单表查询为主宽表 ✓ 数据量超大10 亿 ✓ 对查询速度要求极高 ✓ 并发不高 50 QPS ✓ 团队技术能力强 典型案例 - 用户行为分析 - 日志分析 - 监控指标查询选择 Doris 的场景✓ 需要高并发 100 QPS ✓ 多表 Join 较多 ✓ 需要实时更新 ✓ 运维能力一般 ✓ 国内团队中文支持好 典型案例 - 实时报表 - 用户画像 - 广告分析选择 StarRocks 的场景✓ 复杂查询多表 Join ✓ 对查询速度要求高 ✓ 需要高并发 ✓ 需要物化视图 ✓ 预算充足企业版功能强 典型案例 - 实时数仓 - 自助分析 - 数据服务 API 表设计优化分区设计Partition原则1. 按时间分区最常用 按天/周/月分区便于 TTL 和历史数据管理 2. 分区粒度适中 - 太细分区太多管理困难 - 太粗分区裁剪效果差 3. 分区数量控制 - 单表分区数100-1000 个 - 单分区数据量1GB-10GBClickHouse 示例-- 按天分区CREATETABLEorders_realtime(order_idBIGINT,user_idBIGINT,amountDECIMAL(18,2),create_timeDateTime,dt String-- 分区字段)ENGINEMergeTree()PARTITIONBYdt-- 按天分区ORDERBY(create_time,order_id)TTL create_timeINTERVAL90DAY;-- 90 天后自动删除-- 插入数据INSERTINTOorders_realtimeSELECTorder_id,user_id,amount,create_time,toYYYYMMDD(create_time)asdtFROMorders_source;Doris 示例-- 按范围分区时间CREATETABLEorders_realtime(order_idBIGINT,user_idBIGINT,amountDECIMAL(18,2),create_timeDATETIME)ENGINEOLAPUNIQUEKEY(order_id,create_time)PARTITIONBYRANGE(create_time)(FROM(2026-01-01)TO(2026-04-01)INTERVAL1DAY)DISTRIBUTEDBYHASH(order_id)BUCKETS32PROPERTIES(replication_num3,storage_mediumSSD);分桶设计Bucket原则1. 选择高基数列 用户 ID、订单 ID 等避免数据倾斜 2. 分桶数量适中 - 太少并行度不够 - 太多小文件太多 3. 计算公式 分桶数 数据量 / 单桶大小1-10GB 示例 - 100GB 数据10-100 个桶 - 1TB 数据100-1000 个桶ClickHouse 示例-- 按用户 ID 分桶通过 ORDER BY 实现CREATETABLEuser_behavior(event_idBIGINT,user_idBIGINT,event_type String,event_timeDateTime)ENGINEMergeTree()PARTITIONBYtoYYYYMMDD(event_time)ORDERBY(user_id,event_time);-- 先按 user_id 排序-- 查询时利用分桶SELECTuser_id,count()FROMuser_behaviorWHEREuser_id12345-- 快速定位GROUPBYuser_id;Doris 示例-- 哈希分桶CREATETABLEorders(order_idBIGINT,user_idBIGINT,amountDECIMAL(18,2))ENGINEOLAPUNIQUEKEY(order_id)PARTITIONBYRANGE(create_time)(...)DISTRIBUTEDBYHASH(order_id)BUCKETS64;-- 64 个桶-- 随机分桶均匀分布DISTRIBUTEDBYRANDOM BUCKETS64;索引设计ClickHouse 索引-- 主键索引ORDER BY 字段CREATETABLEorders(order_idBIGINT,user_idBIGINT,create_timeDateTime)ENGINEMergeTree()ORDERBY(create_time,order_id);-- 主键索引-- 跳数索引MinMaxCREATETABLEorders(...)ENGINEMergeTree()ORDERBYcreate_time SETTINGS index_granularity8192;-- 跳数索引粒度-- 自定义索引CREATETABLEorders(order_idBIGINT,user_idBIGINT,amountDECIMAL(18,2),INDEXidx_amount(amount)TYPEminmax GRANULARITY1024)ENGINEMergeTree()ORDERBYcreate_time;-- 使用索引SELECT*FROMordersWHEREamount1000;-- 使用 idx_amount 索引Doris 索引-- 前缀索引自动创建CREATETABLEorders(order_idBIGINT,user_idBIGINT,create_timeDATETIME,INDEXidx_user(user_id)USINGINVERTED)ENGINEOLAPUNIQUEKEY(order_id)...;-- 倒排索引全文检索CREATEINDEXidx_product_nameONorders(product_name)USINGINVERTED;-- 位图索引用户画像CREATEINDEXidx_user_tagsONorders(user_tags)USINGBITMAP; 查询优化技巧物化视图预聚合ClickHouse 物化视图-- 创建物化视图CREATEMATERIALIZEDVIEWmv_user_daily_gmvENGINESummingMergeTree()PARTITIONBYdtORDERBY(user_id,dt)ASSELECTuser_id,toYYYYMMDD(create_time)asdt,SUM(amount)asgmv,COUNT(1)asorder_countFROMorders_realtimeGROUPBYuser_id,dt;-- 查询物化视图自动命中SELECTuser_id,SUM(gmv)astotal_gmvFROMorders_realtimeWHEREdt20260301GROUPBYuser_id;-- 手动查询物化视图SELECTuser_id,SUM(gmv)astotal_gmvFROMmv_user_daily_gmvWHEREdt20260301GROUPBYuser_id;Doris 物化视图-- 创建物化视图CREATEMATERIALIZEDVIEWmv_user_gmvASSELECTuser_id,DATE(create_time)asstat_date,SUM(amount)asgmv,COUNT(1)asorder_countFROMorders_realtimeGROUPBYuser_id,DATE(create_time);-- 查询自动命中SELECTuser_id,SUM(gmv)FROMorders_realtimeWHEREcreate_time2026-03-01GROUPBYuser_id;-- 查看物化视图状态SHOWMATERIALIZEDVIEWFROMorders_realtime;预计算RollupDoris Rollup-- 创建 Rollup多版本聚合ALTERTABLEorders_realtimeADDROLLUP rollup_user_daily(user_id,DATE(create_time),SUM(amount),COUNT(1));-- 创建 Rollup多维度ALTERTABLEorders_realtimeADDROLLUP rollup_category_daily(category_id,DATE(create_time),SUM(amount),COUNT(1));-- 查询自动选择最优 RollupSELECTuser_id,DATE(create_time),SUM(amount)FROMorders_realtimeGROUPBYuser_id,DATE(create_time);Join 优化大表 Join 小表广播-- ClickHouseSELECTo.order_id,u.user_name,o.amountFROMorders_realtimeASoINNERJOINusersASuONo.user_idu.user_id SETTINGS join_algorithmauto;-- 自动选择算法-- Doris HintSELECT/* BROADCAST(u) */o.order_id,u.user_name,o.amountFROMorders_realtimeASoJOINusersASuONo.user_idu.user_id;大表 Join 大表分桶-- 确保 Join Key 分桶一致-- orders 表DISTRIBUTEDBYHASH(user_id)BUCKETS64-- users 表DISTRIBUTEDBYHASH(user_id)BUCKETS64-- Join 时数据无需 ShuffleSELECTo.order_id,u.user_nameFROMorders oJOINusers uONo.user_idu.user_id;⚙️ 参数调优ClickHouse 参数!-- config.xml --!-- 内存限制 --max_memory_usage16000000000/max_memory_usage!-- 16GB --max_memory_usage_for_all_queries32000000000/max_memory_usage_for_all_queries!-- 并发控制 --max_concurrent_queries100/max_concurrent_queriesmax_threads16/max_threads!-- 查询超时 --max_execution_time30/max_execution_time!-- 合并优化 --merge_treemax_parts_in_total50000/max_parts_in_totalmax_parts_to_merge_at_once100/max_parts_to_merge_at_once/merge_treeDoris 参数-- 会话级别SETexec_mem_limit4G;-- 单查询内存限制SETparallel_fragment_exec_instance_num16;-- 并行度SETenable_profiletrue;-- 开启性能分析-- 全局级别ADMINSETFRONTEND CONFIG(max_query_timeout300000);-- 5 分钟ADMINSETBACKEND CONFIG(disable_storage_page_cachefalse); 生产环境完整案例案例实时大屏优化30 秒 → 0.3 秒背景某电商公司实时大屏 - 展示实时 GMV、订单量、用户数 - 数据量1 亿条/天 - 查询延迟30 秒 - 并发20 人同时访问问题分析-- 原始查询慢SELECTDATE_FORMAT(create_time,%H:%i)astime_slot,SUM(amount)asgmv,COUNT(1)asorder_countFROMorders_realtimeWHEREcreate_timeNOW()-INTERVAL2HOURGROUPBYDATE_FORMAT(create_time,%H:%i)ORDERBYtime_slot;-- 问题-- 1. 全表扫描2 小时数据约 800 万条-- 2. 实时计算聚合CPU 密集-- 3. 无索引可用优化方案-- 方案 1物化视图预聚合CREATEMATERIALIZEDVIEWmv_realtime_gmvENGINESummingMergeTree()ORDERBYtime_slotASSELECTtoStartOfMinute(create_time)astime_slot,SUM(amount)asgmv,COUNT(1)asorder_countFROMorders_realtimeGROUPBYtime_slot;-- 优化后查询自动命中物化视图SELECTDATE_FORMAT(time_slot,%H:%i)astime_slot,SUM(gmv)asgmv,SUM(order_count)asorder_countFROMmv_realtime_gmvWHEREtime_slotnow()-INTERVAL2HOURGROUPBYtime_slot;-- 优化效果-- 扫描数据800 万 → 120 条2 小时×60 分钟-- 查询时间30 秒 → 0.3 秒-- 方案 2滚动窗口Flink 实时聚合-- Flink SQLCREATEVIEWrealtime_gmvASSELECTTUMBLE_START(create_time,INTERVAL1MINUTE)astime_slot,SUM(amount)asgmv,COUNT(1)asorder_countFROMorders_realtimeGROUPBYTUMBLE(create_time,INTERVAL1MINUTE);-- 输出到 ClickHouseINSERTINTOmv_realtime_gmvSELECTtime_slot,gmv,order_countFROMrealtime_gmv;-- 查询直接查聚合结果SELECTtime_slot,gmv,order_countFROMmv_realtime_gmvWHEREtime_slotnow()-INTERVAL2HOUR;-- 优化效果-- 查询时间30 秒 → 0.1 秒案例用户画像查询优化10 秒 → 0.5 秒背景用户画像标签查询 - 标签数量500 个 - 用户数量1 亿 - 查询圈选符合条件的人群 - 原始查询10 秒优化方案-- 使用 Bitmap 索引DorisCREATETABLEuser_tags(user_idBIGINT,tag_idINT,tag_value STRING)ENGINEOLAPUNIQUEKEY(user_id,tag_id)DISTRIBUTEDBYHASH(user_id)BUCKETS128PROPERTIES(bitmap_indextrue);-- 创建 Bitmap 索引CREATEINDEXidx_tag_valueONuser_tags(tag_value)USINGBITMAP;-- 人群圈选查询SELECTuser_idFROMuser_tagsWHEREtag_valueIN(北京,上海,广州)-- 地区ANDtag_valueIN(高消费,高价值)-- 消费水平GROUPBYuser_idHAVINGCOUNT(DISTINCTtag_value)5;-- 同时满足 5 个标签-- 优化效果-- 扫描数据1 亿 → 使用 Bitmap 直接计算-- 查询时间10 秒 → 0.5 秒案例高并发查询优化10 → 200 QPS背景数据服务 API - 提供用户订单查询接口 - 并发10 QPS峰值超时 - 目标100 QPS优化方案-- 方案 1读写分离-- 主集群写入 复杂查询-- 从集群简单查询API-- 方案 2查询缓存-- Redis 缓存热点数据SETenable_query_cachetrue;SETquery_cache_ttl60;-- 缓存 60 秒-- 方案 3连接池-- 使用连接池如 HikariCP-- 避免频繁创建连接-- 方案 4资源隔离-- 小查询队列API-- 大查询队列分析CREATEUSERapi_user SETTINGS max_execution_time5;CREATEUSERanalyst_user SETTINGS max_execution_time300; 最佳实践清单表设计合理分区按时间合理分桶高基数列选择合适引擎MergeTree/OLAP设置 TTL自动清理索引优化主键索引ORDER BY跳数索引MinMax倒排索引全文检索Bitmap 索引用户画像查询优化使用物化视图使用预聚合RollupJoin 优化广播/分桶开启查询缓存资源管理读写分离资源隔离队列连接池监控告警 总结核心要点优化方向技术性能提升表设计分区/分桶10-100x索引Bitmap/倒排10-50x预聚合物化视图/Rollup50-500xJoin 优化广播/分桶5-20x资源管理隔离/缓存10-20x实践原则1. 设计先行 表设计决定性能上限 2. 预聚合优先 能预计算的不实时算 3. 索引得当 合适的索引事半功倍 4. 监控持续 持续优化没有终点 实时查询优化是系统工程建议从表设计开始就做好规划 感谢阅读 系列文章[01-SQL 窗口函数从入门到精通](./01-SQL 窗口函数从入门到精通.md)[02-Spark 性能优化 10 个技巧](./02-Spark 性能优化 10 个技巧.md)03-数据仓库分层设计指南04-维度建模实战[05-Flink 实时数仓实战](./05-Flink 实时数仓实战.md)[06-Kafka 消息队列实战指南](./06-Kafka 消息队列实战指南.md)[07-Hive 性能优化实战](./07-Hive 性能优化实战.md)[08-Linux 大数据开发必备工具](./08-Linux 大数据开发必备工具.md)[09-缓慢变化维 SCD Type 2 详解](./09-缓慢变化维 SCD Type2 详解.md)[10-Flink 时间语义与 Watermark 详解](./10-Flink 时间语义与 Watermark 详解.md)[11-Hadoop 集群搭建完整教程](./11-Hadoop 集群搭建完整教程.md)[12-Spark SQL 进阶实践](./12-Spark SQL 进阶实践.md)13-数据血缘与元数据管理14-指标体系设计与产品手册15-实时数据查询优化实战本文 系列完成16 篇大数据技术博客全部完成类别篇数文章SQL 基础2 篇01 窗口函数、12 Spark SQL数仓设计3 篇03 分层设计、04 维度建模、09 SCD实时计算3 篇05 Flink 数仓、10 Watermark、15 查询优化离线计算3 篇02 Spark 优化、07 Hive 调优、11 Hadoop数据集成2 篇06 Kafka、08 Linux 工具数据治理2 篇13 元数据、14 指标体系面试题库1 篇00 面试题库总字数约 10 万字下一步发布到 CSDN/知乎/掘金收集读者反馈持续更新迭代祝博客大受欢迎