做了这么多年安防大数据项目听得最多的需求往往不是“做一个报表”而是“数据都有了怎么还是找不到人、查不出轨迹、算不清态势”。问题不在数据量本身而在绝大多数平台还停留在“把业务库查出来、贴个Excel、再画个图”的阶段。车辆卡口一天几千万条过车人脸抓拍一天上亿条结构化描述再加上门禁、报警、设备状态传统数据库早就吃不消了。我自己的体会是在这个场景里大数据OLAP不是锦上添花而是智慧安防数据研判真正的底座之一。这篇文章想聊的就是我在实际项目中把OLAP引入智慧安防平台的关键经验为什么非用OLAP不可、引擎怎么选、表怎么建、SQL怎么写、坑怎么踩。适合正在做安防大数据平台建设的研发、架构、运维同学也适合刚接触这个方向、想知道OLAP到底能解决什么问题的朋友。1. 智慧安防为什么单靠数据库不够用1.1 安防数据到底有多大、有多快、有多杂先看一组我实际接触过的量级估算。一个中等城市假设布了2000个卡口每个卡口日均过车3000到5000辆那一天就是600万到1000万条过车记录如果每个过车记录还要关联车辆品牌、车身颜色、年款、遮阳板、挂饰、打电话状态等结构化属性单条记录字段轻松超过30个。人脸抓拍更夸张。一台人脸抓拍机一天大约产生1到3万张有效抓拍图3000路相机就是3000万到9000万条结构化元数据。再加上门禁记录、周界报警、设备离线告警、视频流码率状态一个区县级平台一天新增数据量在1亿条以上并不稀奇。这还没算“多快”。卡口数据是实时流水延迟要求高人脸抓拍是脉冲式写入早高峰和晚高峰的写入量是平峰的5到10倍大屏态势需要秒级刷新。数据形态也很杂结构化表格、半结构化的JSON报文、文本描述、甚至特征值向量。传统业务数据库擅长处理“事务”面对这种海量流式写入和多维组合查询很容易在IO和CPU上先崩掉。1.2 OLTP与OLAP别再让业务库干数仓的活很多人觉得“服务器配置高一点就能扛”这不是配置问题是架构职责错位。OLTP联机事务处理解决的是“一行行的增删改查”比如办案系统里录入一条人员信息、更新一个案事件状态OLAP联机分析处理解决的是“对海量数据做聚合、关联、趋势判断”比如“过去一小时经过A卡口且车身颜色为白色的SUV有哪些车牌”。两种工作负载的特征完全不同。OLTP查询大多是点查命中索引后就返回少量行OLAP查询经常要扫描几千万行、按时间窗口分组、对车牌做去重统计。把分析Query直接打到业务库轻则拖慢在线业务重则把数据库连接池打满最后连正常的录入都卡死。所以安防平台的建设思路一定是“业务库继续管事务OLAP负责分析”。业务库保存状态和最近数据OLAP负责面向主题的宽表、明细和聚合查询。1.3 从“查一笔记录”到“算一批结论”的转变智慧安防的研判场景本质上是一步步从“点”走向“面”的。最早期的安防系统能做的就是“输入车牌号查它某一次经过卡口的照片”到后面变成“输入车牌号和时间范围把这个时间段所有经过的卡口串成一条轨迹”再往后是“给出一批车牌号统计它们是否在同一时段出现在同一网格”。这个演变对数据处理能力的要求完全不同。单点查询传统索引就够了轨迹反查需要按时间排序、按卡口ID过滤数据要能按照时空维度高效检索伴随分析则是典型的多维聚合比如按小时、按区域、按车辆类型做Cube。没有OLAP引擎后面几种分析几乎没法在秒级响应。很多项目做到一半发现“数据全在库里但服务器一直报警”原因就是架构没有跟上业务从事务型向分析型的转型。把OLAP引入进来本质上是给研判业务配一辆跑得快的分析车而不是让原来那辆业务车既拉货又跑赛道。2. 建智慧安防OLAP平台引擎怎么选2.1 主流OLAP引擎横向对比选型是个大问题选错了后面所有表结构和SQL都要翻工。我这些年实际接触过的OLAP引擎主要就几个ClickHouse、Apache Doris、StarRocks以及老的Presto/Trino。它们各有侧重不能用一套模板套所有场景。引擎擅长场景短板适合安防的切入口ClickHouse海量明细查询、大宽表聚合、实时写入后秒级可见Join能力相对弱、高并发点查需要细化设计过车明细、人脸抓拍流水、轨迹反查Apache Doris明细聚合混合负载、支持Rollup和物化视图、SQL兼容好复杂ETL能力一般极端大流量写入需要调优大屏态势、多维报表、明细查询StarRocks实时数仓、高并发查询、join性能好社区规模相对较小版本升级需留意实时布控、人员档案关联查询Presto/Trino跨数据源联邦查询、数据湖分析本身不存储数据查询稳定性依赖底层引擎统一数据接入后的临时分析注意表里的“适合切入口”不是排他性的。选引擎的关键不是看官网跑分而是想清楚你的核心负载到底是什么。2.2 按场景倒推选型三个决策关键点我选型时一般先问三个问题。第一个问题是“你的查询以明细还是聚合为主”。如果研判人员天天查“这辆车在某个时段经过哪些卡口”那一定是明细大宽表查询ClickHouse这种列式存储、向量化执行、压缩率高的引擎优势很大。如果大屏上全是“今日各辖区警情趋势”“各网格过车量Top10”那就是聚合查询为主Doris或StarRocks通过预聚合模型和物化视图能省很多事。第二个问题是“数据实时性和查询并发怎么平衡”。安防里的“实时”和互联网的“实时”不完全一样。互联网要求毫秒级响应海量并发安防更多是秒级响应、几十路并发但查询条件组合很复杂。ClickHouse在高并发点查上需要靠缓存、索引和合理分区来兜底Doris/StarRocks对高并发相对友好一些适合做统一对外的报表服务。第三个问题是“团队能维护几个组件”。有的项目上了ClickHouse又上Doris还搭了Presto听着很全面实际上运维成本翻倍。组件越多数据同步链路易碎排查问题越难。我目前比较推荐的组合是“一个OLAP引擎为底座 一个消息队列负责数据缓冲 一个轻量调度做定时任务”最多再加一个数据湖或对象存储做冷数据归档不要再叠床架屋。2.3 我的推荐组合以及为什么不推荐单一大而全结合我自己的项目经验如果是从零开始建一套中等规模的智慧安防OLAP平台我一般首选ClickHouse作为明细查询底座Doris或StarRocks作为聚合报表与统一查询入口底层共用一套Kafka或Pulsar管道。如果团队规模小、链路不想太长直接用StarRocks一个引擎包打明细和聚合也能跑但要注意建表模型别混用。为什么不推荐“单一大而全”因为安防场景的查询类型太分裂。大屏聚合是典型的“预计算优先”请求的是秒级汇总临时研判是“扫描优先”经常要全表扫一个时间分片轨迹反查更夸张可能一次要抽几十万条记录排序。这种分裂负载放在同一个引擎里要么把并发调优做得很复杂要么互相争抢资源。拆成“明细引擎聚合引擎”之后每个组件职责明确问题定位也快得多。3. 典型架构与数据建模从采集到展示3.1 总览一套可落地的六层架构从项目实操角度一套智慧安防OLAP平台大致可以分为六层。数据源层包括卡口相机、人脸抓拍机、门禁、报警主机、第三方系统采集层用Kafka或Pulsar接入实时流同时用Flume或DataX做离线批量导入存储与计算层是数据湖或消息队列用来做原始数据的备份和中间交换OLAP分析层就是我们选定的引擎负责明细查询、预聚合、物化视图服务层是统一的SQL查询网关外面接BI工具或者自研的后端API应用层则是大屏、研判工具、移动端。这套架构看起来不复杂难在每一层要明确“什么数据走实时、什么数据走批量”。我的经验是卡口过车、人脸抓拍等核心研判数据必须走实时链路延迟控制在秒级设备状态、日志这类监控数据走批量链路就好不需要浪费实时计算资源。3.2 数据分层ODS、DWD、DWS、ADS怎么切数据清洗的层级我通常会参考数仓的四层模型但做得更轻量。ODS层是原始接入层保留最原始的报文或明细比如卡口过车的JSON原始数据这个层级只做接入和备份不怎么做加工。DWD层是明细清洗层把JSON解析成扁平字段、校正时间格式、补齐维表字段比如把卡口ID关联成“卡口名称所属辖区方向”。DWS层是汇总服务层按主题做轻聚合比如按分钟、按卡口统计过车量用物化视图或AggregatingMergeTree实现。ADS层是应用层面向大屏和专题分析比如“重点区域实时态势”“今日各网格过车Top榜”。很多新手容易犯的错是ODS和DWD混在一起。原始数据还没清洗就拿来建宽表结果同一份数据在不同查询里时间字段格式不一致、卡口ID对应不上后面排查异常非常痛苦。宁可DWD层多花点Kafka Streams或Flink的处理时间也要保证字段口径统一。3.3 明细表设计分区、分桶、排序键与压缩明细表是OLAP的性能命根子。以ClickHouse为例我最常用的是MergeTree家族引擎建表时几个核心参数比SQL本身更影响性能。分区键的选择要贴合查询场景。安防查询一定会带时间范围所以分区键首选“天”或“小时”。卡口过车数据我一般用toYYYYMMDD(pass_time)做分区一天一个分区查询时可以直接跳过大量分区。人脸抓拍数据量更大我会按小时分区避免单分区文件过多。排序键决定压缩率和查询过滤效率。对过车记录表我常这么建CREATE TABLE vehicle_pass_record_dwd ( plate_no String, pass_time DateTime, tollgate_id UInt64, tollgate_name String, district_id UInt64, lane_no UInt8, speed Float32, vehicle_type UInt8, vehicle_color UInt8, plate_color UInt8, direction UInt8, image_url String ) ENGINE MergeTree PARTITION BY toYYYYMMDD(pass_time) ORDER BY (tollgate_id, pass_time, plate_no) TTL pass_time INTERVAL 365 DAY SETTINGS index_granularity 8192; ORDER BY (tollgate_id, pass_time, plate_no)排序键顺序为什么这么定因为常见查询是“某卡口某时间段”往往还带车牌过滤。把tollgate_id放最前面等于建立了一个天然的一级索引pass_time放第二级再叠加plate_no做精细过滤查询时扫描的数据块数量会明显减少。这里有个需要留意的坑ORDER BY不是把所有字段都排一遍。字段太多会导致索引文件膨胀、写入变慢。只把过滤频率最高的两三个字段放进去就好其它字段靠投影列裁剪。列式存储本身只要不SELECT *就能少读很多IO。分桶在ClickHouse里没有太强的概念更多是在Doris/StarRocks里用分桶键控制数据分布。对车牌号这种高基数字段分桶键选车牌号能让同一辆车的记录尽量落在同一桶做“单车辆轨迹”关联时相邻扫描更快。但要注意分桶数不能拍脑袋我一般按“单桶数据量建议在100MB到1GB之间”倒推。3.4 预聚合与物化视图把查询从秒级压到毫秒级明细表再快大屏上的“今日过车总量”也不可能每次现扫几亿条明细。这时候要用预聚合。设计方案有两种。第一种是Doris/StarRocks的聚合模型建表时指定AGGREGATE KEY导入时自动按维度聚合。第二种是ClickHouse的物化视图 AggregatingMergeTree思路也类似明细数据写入时同步触发聚合计算。以ClickHouse为例我一般这么建CREATE MATERIALIZED VIEW mv_vehicle_pass_minute ENGINE AggregatingMergeTree PARTITION BY toYYYYMMDD(ts) ORDER BY (ts, tollgate_id, district_id) AS SELECT toStartOfMinute(pass_time) AS ts, tollgate_id, district_id, count() AS pass_cnt FROM vehicle_pass_record_dwd GROUP BY ts, tollgate_id, district_id;要注意物化视图不是一个查询加速缓存它更像是一个“在写入时额外算一份结果”的触发器。所以如果明细数据回刷或补录物化视图不会自动重新计算。我通常在离线补数场景下直接删掉对应分区再跑一遍关联任务或者使用可更新物化视图语法。预聚合也分粒度。我的经验是大屏场景至少要准备“分钟级卡口/辖区维度”的聚合研判场景再准备“小时级全域维度”的聚合避免有人一次性查半年的总量时把内存打爆。4. 核心场景与SQL实操4.1 车辆缉查布控多维组合筛选缉查布控最典型的场景是“在最近10分钟找出通过某几个卡口的白色SUV车牌尾号是8”。这类查询特点是条件多、结果需要秒出。SELECT plate_no, pass_time, tollgate_name, lane_no, speed, image_url FROM vehicle_pass_record_dwd WHERE pass_time now() - INTERVAL 10 MINUTE AND tollgate_id IN (1001, 1002, 1003) AND vehicle_type 2 AND vehicle_color 5 AND plate_no LIKE %8 ORDER BY pass_time DESC LIMIT 500;这条SQL在ClickHouse里能跑得快靠的是分区裁剪和排序键。pass_time直接把查询锁在最近一两个小时的分区tollgate_id IN又走排序键前缀过滤。我踩过的坑是在车牌过滤上直接写LIKE %8它会放弃索引走全列扫描如果数据量大还是会慢。更好的做法是让前端解析车牌后使用等值或前缀匹配或者在字段上提前做N-gram索引。4.2 轨迹反查时空条件怎么检索最快轨迹反查是“输入车牌号画出这辆车在某个时间段跑过的路线”。SQL本身不复杂复杂在数据量大和排序代价高。SELECT pass_time, tollgate_name, district_id, direction, speed FROM vehicle_pass_record_dwd WHERE plate_no 粤B12345 AND pass_time 2024-06-01 00:00:00 AND pass_time 2024-06-01 23:59:59 ORDER BY pass_time ASC;这条查询最大的隐患是ORDER BY pass_time全量排序。如果一辆车一天的过车记录有几百行排序没问题但如果查询条件不带车牌、变成“这个区域所有车在某个时段内的轨迹”那就会变成几十万甚至上百万行的排序。我的处理办法是两层查询第一层先用一个轻量聚合表快速拿到“车牌集合”比如哪些车牌在目标时段经过目标区域第二层再用车牌集合去明细表查轨迹这样避免一次性拖出全量明细。轨迹数据还有一个细节方向字段要补全“进出”语义否则画出来的轨迹会来回跳。4.3 态势大屏实时聚合如何不出错大屏上的数字错了比慢更麻烦。因为大屏每一个数字背后都是某个领导或指挥员在做判断数据错了信任就没了。实时聚合的正确打开方式是把“写入时计算”和“查询时兜底”结合起来。用前面的分钟级物化视图大屏API只需要查聚合表SELECT ts, district_id, sum(pass_cnt) AS total_cnt FROM mv_vehicle_pass_minute WHERE ts now() - INTERVAL 30 MINUTE GROUP BY ts, district_id ORDER BY ts;这里有一个我特别想强调的坑如果物化视图里没有district_id这个维度而上层又要按区划汇总那就必须在上游就确保district_id已经被清洗并填充完整。否则会出现“数据表里district_id为空大屏汇总少了一块”的隐蔽问题。我的经验是所有DWD层字段都要有兜底值比如未知区域统一填0并且在大屏前端把区域0标记为“未知”而不是直接在图上消失。4.4 布控预警阈值计算与扩展布控预警一般分成两类。一类是名单比对比如布控人员出现在某个卡口时触发告警另一类是行为模式分析比如“同一辆车短期内多次出现在重点区域”。名单比对对实时性要求很高通常要在OLAP之外配合Redis或流计算引擎来做。OLAP在这里更多负责“预警后的核查验证”和“历史行为回溯”。行为模式分析的SQL可以从明细聚合开始。比如想找出“一小时内出现超过3次的车辆”SELECT plate_no, count() AS cnt FROM vehicle_pass_record_dwd WHERE pass_time now() - INTERVAL 1 HOUR GROUP BY plate_no HAVING cnt 3 ORDER BY cnt DESC LIMIT 200;这种查询如果每天定时跑不一定要实时。我一般用调度任务每5分钟跑一次结果写入预警表再通过消息推送到前端。OLAP负责批量计算和状态比对实时事件的敏感度交给流引擎去补分工合作比什么都堆在OLAP里更稳。5. 常见问题与调优实录5.1 数据倾斜空牌号和热点卡的锅做OLAP查询时最典型的数据倾斜来自两类数据一类是空车牌号一类是热点卡口。抓拍设备经常有“无牌车”或“识别失败”的记录如果它的车牌号被统一填充为空字符串那么GROUP BY plate_no时空字符串那一组就会聚集海量数据查询一直转圈。解决思路不复杂。第一清洗时就把无牌车单独标记不要让它参与常态化的按车牌聚合第二如果还得参与可以用加盐字段或者把空牌号拆成多个随机键再聚合。热点卡口的数据倾斜类似某个重点路口的过车量可能是普通路口的几十倍如果分析维度正好按卡口划分热点卡口所在分区的查询耗时会明显偏高。排查倾斜问题我一般先跑一个“按分组键计数”的分布查询确认分组间数量级差异是不是超过10倍。如果差异过大要么优化查询条件要么对倾斜键单独建表。5.2 Too many parts实时导入的大坑ClickHouse最典型的写入问题是Too many parts。原因很简单每次INSERT都会生成一个新的部分part后台合并线程来不及合并part数量超过阈值查询性能就会下降甚至直接报错。这个坑在我刚开始接实时流时踩得很惨。Kafka里的数据每条来一次就写一次结果一上午parts数量破千。后来改成攒批写入每次攒5到10秒的数据再批量INSERTparts数量立刻降下来。还有一种做法是使用async_insert参数但要注意它可能带来最多几秒的可见延迟适合对实时性要求不那么极端的场景。同时不建议把所有小批次数据取消因为大屏需要秒级数据。我的折中方案是写入频率2到5秒一次每次数据量在10MB以上配合TTL和定期OPTIMIZE TABLE控制parts数量。5.3 内存与查询超时别把集群搞崩OLAP引擎虽然快但也不是无限资源。最常见的崩溃场景是“用户跑了一个没有时间分区条件的聚合”比如直接GROUP BY plate_no统计全量数据。数据量大时内存直接吃满然后整个集群查询变慢甚至波及在线大屏查询。我一般从两个维度兜底。第一是查询侧限制设置max_memory_usage、max_execution_time让单条Query不能无限吃资源。第二是产品侧引导所有API接口必须强制带时间范围参数前端默认只能查最近30天需要更多历史数据必须走离线审批或大数据量异步任务。另外要重视“轻量探活查询”。我经常在集群里放一条只查最近5分钟、LIMIT 1的SQL作为健康检查一旦探活查询变慢马上看系统监控而不是等用户投诉。5.4 分区分桶与生命周期治理清单最后整理一份清单是我在每次运维巡检时会过一遍的项。检查项预期状态如果不满足怎么办单分区part数量小于50降低写入频率执行OPTIMIZE合并存储与保留周期明细保留1年聚合保留3年调整TTL表达式冷数据转对象存储排序键与常用查询匹配常用查询能走分区/排序前缀重排列序键并重建表物化视图与明细一致性补数后一致删除对应分区离线重刷用户权限与审计最小权限原则按角色收敛只读账号权限这张表其实是用很多次故障换来的。比如TTL很多人建表时没想清楚保留周期运行一年后磁盘告警临时加TTL又要重构表。最好在建表阶段就和业务确认清楚明细数据保留多久、聚合数据保留多久、历史图片等大对象存到哪。6. 再往前走一步的实际经验6.1 数据安全与权限分级安防数据涉及公民隐私尤其是人脸抓拍、车辆轨迹、门禁记录。这部分我不是安全专家但作为平台负责人至少要做三层控制。第一层是网络隔离OLAP引擎不要直接暴露公网只允许内网业务服务访问。第二层是账号权限按“最小必要”原则划分只读账号研判人员只给查明细的权限管理员才有建表权限。第三层是审计日志所有查询记录都要留存至少能追溯到“谁在什么时间查了什么条件”。踩过这种坑之后才会明白数据平台如果被当公网数据库用出问题是早晚的事。6.2 数据保留周期怎么定安防数据不是无限存的成本摆在那里。我的习惯是分层管理热数据保留3到6个月放SSD或者高性能本地盘温数据保留1到2年放普通磁盘超过2年的明细归档到对象存储或数据湖需要时再临时加载。聚合数据保留时间可以更长因为它体积小、业务价值高。比如按分钟的卡口过车聚合保留3年完全没问题。但要注意如果上游明细已经过期删除聚合数据再刷新也没有意义所以离线归档策略要和保留策略一起设计而不是各管各的。6.3 从平台到产品让OLAP真正被业务用起来最后想聊几句偏产品向的经验。OLAP平台建好以后最怕的不是技术问题而是没人用。研判民警不会写SQL大屏业务方只关心数字对不对领导只关心响应快不快。所以除了引擎还要配套一个简单易用的查询界面把“常用研判场景”做成一键式操作。比如按车牌查轨迹、按区域查过车、按时间查警情背后是写死的OLAP查询模板前端只暴露几个输入框。我个人的体会是一个OLAP项目的成功标准不是集群跑了多少TB、查询有多快而是民警在日常工作中真的愿意打开这个系统去查东西。达到这个目标架构、选型、建模、调优这些功夫才算真正落在了实处。最后再分享一个小技巧不要迷信“单条SQL优化”。同一个OLAP集群上如果一条优化得很漂亮但全链路没有连通——比如数据没准点、大屏查的是另一套表、研判人员不知道有新增数据——那一切优化都没有意义。先保证全链路的稳定和口径一致再谈单点性能这是我在安防OLAP项目里被反复验证的一条经验。