# 实时数据查询优化实战

大数据开发高阶技能：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 → 200（20 倍） - 数据规模：1 亿 → 100 亿（100 倍） - 复杂查询：从超时到 2 秒

🔧 实时 OLAP 引擎选型

主流引擎对比

选型建议

选择 ClickHouse 的场景：

✓ 单表查询为主（宽表） ✓ 数据量超大（10 亿+） ✓ 对查询速度要求极高 ✓ 并发不高（< 50 QPS） ✓ 团队技术能力强 典型案例： - 用户行为分析 - 日志分析 - 监控指标查询

选择 Doris 的场景：

✓ 需要高并发（> 100 QPS） ✓ 多表 Join 较多 ✓ 需要实时更新 ✓ 运维能力一般 ✓ 国内团队（中文支持好） 典型案例： - 实时报表 - 用户画像 - 广告分析

选择 StarRocks 的场景：

✓ 复杂查询（多表 Join） ✓ 对查询速度要求高 ✓ 需要高并发 ✓ 需要物化视图 ✓ 预算充足（企业版功能强） 典型案例： - 实时数仓 - 自助分析 - 数据服务 API

🔧 表设计优化

分区设计（Partition）

原则：

1. 按时间分区（最常用） 按天/周/月分区，便于 TTL 和历史数据管理 2. 分区粒度适中 - 太细：分区太多，管理困难 - 太粗：分区裁剪效果差 3. 分区数量控制 - 单表分区数：100-1000 个 - 单分区数据量：1GB-10GB

ClickHouse 示例：

-- 按天分区CREATETABLEorders_realtime(order_idBIGINT,user_idBIGINT,amountDECIMAL(18,2),create_timeDateTime,dt String-- 分区字段)ENGINE=MergeTree()PARTITIONBYdt-- 按天分区ORDERBY(create_time,order_id)TTL create_time+INTERVAL90DAY;-- 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)ENGINE=OLAPUNIQUEKEY(order_id,create_time)PARTITIONBYRANGE(create_time)(FROM('2026-01-01')TO('2026-04-01')INTERVAL1DAY)DISTRIBUTEDBYHASH(order_id)BUCKETS32PROPERTIES("replication_num"="3","storage_medium"="SSD");

分桶设计（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)ENGINE=MergeTree()PARTITIONBYtoYYYYMMDD(event_time)ORDERBY(user_id,event_time);-- 先按 user_id 排序-- 查询时利用分桶SELECTuser_id,count()FROMuser_behaviorWHEREuser_id=12345-- 快速定位GROUPBYuser_id;

Doris 示例：

-- 哈希分桶CREATETABLEorders(order_idBIGINT,user_idBIGINT,amountDECIMAL(18,2))ENGINE=OLAPUNIQUEKEY(order_id)PARTITIONBYRANGE(create_time)(...)DISTRIBUTEDBYHASH(order_id)BUCKETS64;-- 64 个桶-- 随机分桶（均匀分布）DISTRIBUTEDBYRANDOM BUCKETS64;

索引设计

ClickHouse 索引：

-- 主键索引（ORDER BY 字段）CREATETABLEorders(order_idBIGINT,user_idBIGINT,create_timeDateTime)ENGINE=MergeTree()ORDERBY(create_time,order_id);-- 主键索引-- 跳数索引（MinMax）CREATETABLEorders(...)ENGINE=MergeTree()ORDERBYcreate_time SETTINGS index_granularity=8192;-- 跳数索引粒度-- 自定义索引CREATETABLEorders(order_idBIGINT,user_idBIGINT,amountDECIMAL(18,2),INDEXidx_amount(amount)TYPEminmax GRANULARITY1024)ENGINE=MergeTree()ORDERBYcreate_time;-- 使用索引SELECT*FROMordersWHEREamount>1000;-- 使用 idx_amount 索引

Doris 索引：

-- 前缀索引（自动创建）CREATETABLEorders(order_idBIGINT,user_idBIGINT,create_timeDATETIME,INDEXidx_user(user_id)USINGINVERTED)ENGINE=OLAPUNIQUEKEY(order_id)...;-- 倒排索引（全文检索）CREATEINDEXidx_product_nameONorders(product_name)USINGINVERTED;-- 位图索引（用户画像）CREATEINDEXidx_user_tagsONorders(user_tags)USINGBITMAP;

🔧 查询优化技巧

物化视图（预聚合）

ClickHouse 物化视图：

-- 创建物化视图CREATEMATERIALIZEDVIEWmv_user_daily_gmvENGINE=SummingMergeTree()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_realtimeWHEREdt>='20260301'GROUPBYuser_id;-- 手动查询物化视图SELECTuser_id,SUM(gmv)astotal_gmvFROMmv_user_daily_gmvWHEREdt>='20260301'GROUPBYuser_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_time>='2026-03-01'GROUPBYuser_id;-- 查看物化视图状态SHOWMATERIALIZEDVIEWFROMorders_realtime;

预计算（Rollup）

Doris 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_id=u.user_id SETTINGS join_algorithm='auto';-- 自动选择算法-- Doris HintSELECT/*+ BROADCAST(u) */o.order_id,u.user_name,o.amountFROMorders_realtimeASoJOINusersASuONo.user_id=u.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_id=u.user_id;

⚙️ 参数调优

ClickHouse 参数

<!-- config.xml --><!-- 内存限制 --><max_memory_usage>16000000000</max_memory_usage><!-- 16GB --><max_memory_usage_for_all_queries>32000000000</max_memory_usage_for_all_queries><!-- 并发控制 --><max_concurrent_queries>100</max_concurrent_queries><max_threads>16</max_threads><!-- 查询超时 --><max_execution_time>30</max_execution_time><!-- 合并优化 --><merge_tree><max_parts_in_total>50000</max_parts_in_total><max_parts_to_merge_at_once>100</max_parts_to_merge_at_once></merge_tree>

Doris 参数

-- 会话级别SETexec_mem_limit=4G;-- 单查询内存限制SETparallel_fragment_exec_instance_num=16;-- 并行度SETenable_profile=true;-- 开启性能分析-- 全局级别ADMINSETFRONTEND CONFIG("max_query_timeout"="300000");-- 5 分钟ADMINSETBACKEND CONFIG("disable_storage_page_cache"="false");

🏭 生产环境完整案例

案例：实时大屏优化（30 秒 → 0.3 秒）

背景：

某电商公司实时大屏： - 展示实时 GMV、订单量、用户数 - 数据量：1 亿条/天 - 查询延迟：30 秒 - 并发：20 人同时访问

问题分析：

-- 原始查询（慢）SELECTDATE_FORMAT(create_time,'%H:%i')astime_slot,SUM(amount)asgmv,COUNT(1)asorder_countFROMorders_realtimeWHEREcreate_time>=NOW()-INTERVAL2HOURGROUPBYDATE_FORMAT(create_time,'%H:%i')ORDERBYtime_slot;-- 问题：-- 1. 全表扫描（2 小时数据约 800 万条）-- 2. 实时计算聚合（CPU 密集）-- 3. 无索引可用

优化方案：

-- 方案 1：物化视图（预聚合）CREATEMATERIALIZEDVIEWmv_realtime_gmvENGINE=SummingMergeTree()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_slot>=now()-INTERVAL2HOURGROUPBYtime_slot;-- 优化效果：-- 扫描数据：800 万 → 120 条（2 小时×60 分钟）-- 查询时间：30 秒 → 0.3 秒

-- 方案 2：滚动窗口（Flink 实时聚合）-- Flink SQLCREATEVIEWrealtime_gmvASSELECTTUMBLE_START(create_time,INTERVAL'1'MINUTE)astime_slot,SUM(amount)asgmv,COUNT(1)asorder_countFROMorders_realtimeGROUPBYTUMBLE(create_time,INTERVAL'1'MINUTE);-- 输出到 ClickHouseINSERTINTOmv_realtime_gmvSELECTtime_slot,gmv,order_countFROMrealtime_gmv;-- 查询直接查聚合结果SELECTtime_slot,gmv,order_countFROMmv_realtime_gmvWHEREtime_slot>=now()-INTERVAL2HOUR;-- 优化效果：-- 查询时间：30 秒 → 0.1 秒

案例：用户画像查询优化（10 秒 → 0.5 秒）

背景：

用户画像标签查询： - 标签数量：500 个 - 用户数量：1 亿 - 查询：圈选符合条件的人群 - 原始查询：10 秒

优化方案：

-- 使用 Bitmap 索引（Doris）CREATETABLEuser_tags(user_idBIGINT,tag_idINT,tag_value STRING)ENGINE=OLAPUNIQUEKEY(user_id,tag_id)DISTRIBUTEDBYHASH(user_id)BUCKETS128PROPERTIES("bitmap_index"="true");-- 创建 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_cache=true;SETquery_cache_ttl=60;-- 缓存 60 秒-- 方案 3：连接池-- 使用连接池（如 HikariCP）-- 避免频繁创建连接-- 方案 4：资源隔离-- 小查询队列（API）-- 大查询队列（分析）CREATEUSERapi_user SETTINGS max_execution_time=5;CREATEUSERanalyst_user SETTINGS max_execution_time=300;

📋 最佳实践清单

表设计

• 合理分区（按时间）
• 合理分桶（高基数列）
• 选择合适引擎（MergeTree/OLAP）
• 设置 TTL（自动清理）

索引优化

• 主键索引（ORDER BY）
• 跳数索引（MinMax）
• 倒排索引（全文检索）
• Bitmap 索引（用户画像）

查询优化

• 使用物化视图
• 使用预聚合（Rollup）
• Join 优化（广播/分桶）
• 开启查询缓存

资源管理

• 读写分离
• 资源隔离（队列）
• 连接池
• 监控告警

📌 总结

核心要点

实践原则

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 篇大数据技术博客全部完成！

总字数：约 10 万字

下一步：

• 发布到 CSDN/知乎/掘金
• 收集读者反馈
• 持续更新迭代

祝博客大受欢迎！🚀