SQL优化_监管指标计算性能全维度优化方案一、现状性能瓶颈根源分析1.1 存储层瓶颈原始表全量扫描监管指标直接关联ODS明细大表无预聚合中间层每日重复扫描千万级、亿级流水分区设计不合理仅单dt日分区无复合分区、年月二级分区过滤无法裁剪大量历史分区存储格式与压缩低效使用text/orc无高压缩比配置列存索引缺失谓词下推失效小文件泛滥每日调度输出大量几百KB小文件NameNode元数据压力大读取IO放大1.2 计算层SQL瓶颈多表笛卡尔积、多层子查询嵌套NSFR、集中度、压力测试指标一次性关联5张以上明细表关联基数爆炸实时计算全量重算每日全量重算T日所有客户、对手、机构指标无增量计算逻辑复杂函数无下推优化大量CASE WHEN、ROUND、比例计算放在SELECT顶层无法下推至Map阶段过滤硬编码折算系数重复计算LCR/NSFR权重、减值折算比例每次报表重新计算未下沉至DWD预计算1.3 分层架构瓶颈分层职责混乱ADS监管报表直接读取ODS/DWD明细未复用DWS预聚合宽表重复聚合相同维度计量模型无中间结果落地KMV、DCF压力测算每次重跑完整模型未落地DWD计量明细复用维度全量关联翻译报表中反复JOIN维度字典做编码转中文未在DWD提前完成维度关联1.4 调度与资源瓶颈串行调度无并行分片全行统一单任务计算未按机构、币种分片并行执行资源分配固化监管指标高峰时段与业务报表抢占集群CPU、内存、IO资源无冷热数据分离近30天热明细、历史冷明细混合存储冷数据频繁参与指标计算二、存储分层与分区优化方案2.1 复合分区裁剪优化统一规范分区结构ODS/DWD明细层PARTITIONED BY (year_month STRING, dt STRING)二级分区按月批量裁剪历史数据DWS/ADS汇总层增加org_id/currency分片分区按机构、币种独立分区过滤过滤强制前置年月分区所有监管指标SQL优先加year_month substr(${stat_dt},1,7)跳过整月历史分区-- 标准分区建表示例DROPTABLEIFEXISTSdwd.dwd_liquid_interbank_biz_d;CREATETABLEdwd.dwd_liquid_interbank_biz_d(inter_biz_id STRINGCOMMENT同业业务合同编号,cp_id STRINGCOMMENT交易对手ID,org_id STRINGCOMMENT经办机构编码,biz_type_name STRINGCOMMENT同业业务类型中文名称,trade_amtDECIMAL(20,2)COMMENT交易本金金额)COMMENT标准化同业业务明细NSFR可用稳定资金计算依赖PARTITIONEDBY(year_month STRINGCOMMENT年月分区yyyy-MM,dt STRINGCOMMENT业务日期)STOREDASPARQUET TBLPROPERTIES(parquet.compressionsnappy,parquet.block.size134217728);2.2 列存存储参数调优统一采用ParquetSnappy高压缩关闭行存储设置块大小128MB减少元数据开启谓词下推、向量化执行SEThive.parquet.pushdown.filtertrue;SEThive.vectorized.execution.enabledtrue;SEThive.vectorized.execution.reduce.enabledtrue;大表开启分桶按客户ID、对手ID分桶桶数集群CPU核心倍数JOIN分桶表避免Shuffle全量分发CLUSTEREDBY(cp_id)INTO96BUCKETS SORTEDBY(dt);2.3 冷热数据分层存储热数据近90天明细存集群高性能SSD存储读写低延迟温数据90天~1年普通HDFS磁盘存储冷数据1年以上历史归档至OSS/归档存储计算时手动加载默认不参与日常监管指标计算历史监管报表ADS按月合并全量表删除每日分片小文件2.4 小文件治理优化写入时开启自动合并SEThive.merge.mapfilestrue;SEThive.merge.mapredfilestrue;SEThive.merge.size.per.task134217728;每日调度新增合并任务对DWD/DWS分区执行INSERT OVERWRITE合并小文件为128MB标准块采用动态分区批量写入避免单条分区独立输出文件三、分层架构预聚合消除重复计算核心优化3.1 DWD层前置预计算下沉复杂逻辑将监管所需折算系数、风险分类、资产流动性权重、减值阶段划分全部在DWD明细层一次性计算完成示例NSFR稳定资金权重、贷款ASF折算比例、对手风险敞口分类提前计算存入DWD字段ADS报表直接读取不再重复CASE运算KMV/DCF计量结果完整落地DWD监管报表直接复用计量明细不重复重跑模型3.2 DWS宽表分层聚合ADS零复杂计算强制规范所有监管指标禁止直接关联ODS/DWD明细必须读取DWS预聚合宽表DWS按维度预聚合机构、币种、对手、产品维度汇总当日存量、敞口、折算后资金指标DWS提前完成RSF、ASF、LCR分子分母粗算ADS仅做最终比率计算、预警标记、维度名称翻译拆分宽表职责流动性宽表单独存放LCR/NSFR基础汇总信用宽表存放ECL、PD汇总市场宽表存放VaR、敞口汇总避免单宽表字段爆炸、扫描冗余列3.3 维度关联下沉至底层上层报表零JOIN维表DWD层LEFT JOIN dim维度表输出中文名称、分类标签存入明细字段DWS/ADS报表不再关联dim字典、机构、对手维度减少多表JOIN带来的Shuffle开销四、SQL语句执行逻辑优化4.1 增量计算替代全量重算最大性能提升点存量类监管指标贷款、债券、同业、存款采用增量快照更新仅计算当日新增、变动业务数据存量直接复用昨日DWS宽表流量类指标现金流、资金头寸、当日交易当日全量增量写入历史无需重算压力测试、计量模型仅重算当日新增对公客户、新增贷款存量主体复用前一日PD、ECL结果-- 增量合并示例DWS流动性宽表INSERTOVERWRITETABLEdws.dws_liquid_risk_day_wide_dPARTITION(dt${stat_dt})-- 当日新增变动明细聚合SELECT*FROMdwd.dwd_liquid_fund_position_dWHEREdt${stat_dt}UNIONALL-- 昨日存量未变动数据直接复用SELECT*FROMdws.dws_liquid_risk_day_wide_dWHEREdtdate_add(${stat_dt},-1)WHEREorg_idNOTIN(SELECTDISTINCTorg_idFROMdwd.dwd_liquid_fund_position_dWHEREdt${stat_dt});4.2 减少大表JOIN优化关联顺序小维度表放左表大明细表放右表利用Map端广播小表开启MapJoinSEThive.auto.convert.jointrue;SEThive.mapjoin.smalltable.size51200000;多表关联先过滤分区、再聚合、最后JOIN避免先关联再过滤产生海量中间数据禁止笛卡尔积所有关联必须有唯一主键等值关联cust_id、cp_id、loan_id4.3 谓词下推与字段裁剪SELECT仅查询监管指标所需字段禁止SELECT *扫描全列WHERE条件优先放置分区过滤、等值过滤提前过滤无效数据结清贷款、失效对手、到期债券复杂CASE、比率计算移至子查询内层外层仅简单汇总减少Shuffle阶段计算量4.4 聚合逻辑优化统一使用GROUP BY单一维度聚合多层嵌套聚合改为单层聚合多指标SUM、COUNT合并单次GROUP BY分多次聚合会重复扫描同一份明细空值、负金额前置过滤WHERE trade_amt 0 AND cp_id IS NOT NULL减少无效聚合行数五、分片并行调度与资源隔离优化5.1 数据分片并行执行按机构org_id、币种currency拆分多并行任务拆分规则总行各分行独立分片调度工具Airflow/DataWorks并行下发分片计算任务多节点同时处理总时长线性缩短集中度风险报表按对手类型分片银行同业、对公企业、非银机构分开计算5.2 资源队列隔离单独创建监管指标专属资源队列配置独立CPU、内存、IO资源池与日常业务BI报表队列隔离避免资源抢占高峰时段凌晨2-6点监管批量计算调高队列并行度、内存容器大小日间降低资源占用5.3 执行顺序流水线优化固定串行流水线前置轻量任务先执行重计算任务错峰并行第一步ODS分区合并、小文件治理轻量第二步DWD明细清洗、KMV/DCF计量分片并行中重量第三步DWS各风险宽表并行聚合重量核心第四步ADS监管报表串行输出轻量读取宽表禁止DWD未完成即启动DWS、ADS避免重复重试、空值重算浪费资源六、计算引擎与高级特性优化6.1 引擎选型适配Hive离线批量监管指标采用Tez执行引擎替代MapReduce减少中间磁盘落盘SEThive.execution.enginetez;SETtez.container.size-mb4096;实时流动性日间监控指标切换StarRocks/ClickHouse OLAP引擎预建分区索引、物化视图秒级响应大屏查询6.2 物化视图复用重复指标针对高频监管指标LCR、NSFR、单一对手敞口创建物化视图自动预计算并落地报表查询直接读取视图结果无需实时聚合CREATEMATERIALIZEDVIEWmv_nsfr_base_agg PARTITIONEDBY(dt)ASSELECTorg_id,currency,SUM(trade_amt*weight)AStotal_rsfFROMdwd.dwd_liquid_interbank_biz_dGROUPBYdt,org_id,currency;6.3 索引优化分区索引分区自动索引快速裁剪年月、日期分区分桶索引按cp_id、loan_id分桶等值关联快速定位桶数据倒序索引ADS报表常用排序字段敞口金额、逾期率创建排序索引大屏排序无需全量排序七、数据质量前置过滤减少无效计算DWD层增加统一数据清洗过滤规则提前剔除异常数据金额负数、超大异常值单笔超千亿、空主键、失效对手/客户已结清、已核销、过期到期业务数据过滤不流入上层指标计算增加数据质量校验前置任务明细异常率超过阈值直接终止当日指标计算避免无效全量重跑浪费集群资源八、落地实施效果量化预期存储优化明细表扫描数据量降低60%~80%小文件减少90%IO读取量大幅下降SQL预聚合优化监管指标单次计算时长从4小时缩短至40分钟内增量计算优化存量类指标计算时长再降低70%仅处理当日变动数据分片并行调度整体调度窗口压缩至凌晨2小时内完成满足早8点监管报送时效要求集群资源消耗重复计算Shuffle数据量减少85%CPU、内存资源占用下降60%