计算机科学

计算机科学 ›› 2026, Vol. 53 ›› Issue (8): 1-8.doi: 10.11896/jsjkx.250900007

• 数据库 & 大数据 & 数据科学 • 上一篇    下一篇

基于大数据业务链路的多层级SQL优化框架设计研究

沈峰寅1, 范红杰2   

  1. 1 北京大学软件与微电子学院 北京 100871
    2 中国政法大学科学技术教学部 北京 102249
  • 收稿日期:2025-09-01 修回日期:2026-03-13 出版日期:2026-08-15 发布日期:2026-08-17
  • 通讯作者: 范红杰(hjfan@cupl.edu.cn)
  • 作者简介:(shenfy12@foxmail.com)
  • 基金资助:
    中国政法大学科研创新项目(24KYGH013);中央高校基本科研业务费专项资金

Research on Design of Multi-level SQL Optimization Framework Based on Big Data Business Workflows

SHEN Fengyin1, FAN Hongjie2   

  1. 1 School of Software and Microelectronics, Peking University, Beijing 100871, China
    2 Department of Science and Technology Teaching, China University of Political Science and Law, Beijing 102249, China
  • Received:2025-09-01 Revised:2026-03-13 Published:2026-08-15 Online:2026-08-17
  • About author:SHEN Fengyin,born in 1998,master.His main research interests include big data analytics and database management systems.
    FAN Hongjie,born in 1984,Ph.D,associate professor.His main research in-terests include knowledge graphs,data exchange,and data analysis and mining.
  • Supported by:
    China University of Political Science and Law Research Innovation Project(24KYGH013) and Fundamental Research Funds for the Central Universities.

PDF (PC)

210

摘要： 文中提出了一种面向业务场景的多层级SQL优化框架,通过解耦业务逻辑与通用计算,构建前置规则引擎和后置计算引擎的协同架构。前置SQL优化器聚焦于业务具体优化,后置SQL计算引擎专注于通用大数据计算,通过封装与集成现有流批一体计算引擎,实现高效计算。通过将优化策略划分为面向业务特性的规则优化和基于计算框架的通用执行优化两个层级,实现优化策略模块化与可迭代性。深入优化策略设计与实现,采用规则引擎驱动的前置SQL优化器,结合通用大数据计算引擎,实现面向业务的外部数据源下推、多生产任务合并优化,解决数据倾斜、代码生成超限等业务常见实际问题。实验通过外部数据源下推测试、多任务合并测试、数据倾斜负载优化测试,证明在字段裁剪、任务合并等多个真实场景下所提出框架可分别提升性能49.8%和79.75%,整体优化了大数据生产任务的执行性能,且通过解耦业务计算与通用计算这一设计思想,为大数据场景下的高效SQL优化执行提供了创新性解决方案。

关键词: 多层级SQL优化, 业务规则引擎, 查询优化器即服务, 外部数据源下推, 数据倾斜治理

Abstract: This paper proposes a multi-level SQL optimization framework tailored for business scenarios.By decoupling business logic from general-purpose computation,it establishes a collaborative architecture comprising a front-end rule engine and a back-end execution engine.The front-end SQL optimizer focuses on business-specific optimizations,while the back-end SQL execution engine is dedicated to general big data processing.By encapsulating and integrating existing unified batch-stream proces-sing engines,the framework achieves high computational efficiency.The optimization strategies are systematically divided into two levels:rule-based optimizations oriented toward business characteristics,and general execution optimizations based on the computational framework.This division enhances both modularity and iterability of the optimization strategies.The design and implementation delve into a rule-engine-driven front-end SQL optimizer,which works in conjunction with a general big data execution engine.This synergy enables business-oriented enhancements such as push-down operations for external data sources,consolidation of multiple production tasks,and effectively addresses common practical issues including data skew and code generation limits.Experiments,including external data source push-down tests,multi-task consolidation tests,and data skew load optimization tests,demonstrate performance improvements of 49.8% in field pruning and 79.75% in task merging across multiple real-world scenarios.These results indicate a substantial overall enhancement in the execution performance of big data production tasks.Furthermore,the core design philosophy of decoupling business-specific and general-purpose computation offers an innovative solution for efficient SQL optimization and execution in big data contexts.

Key words: Multi-level SQL optimization, Business rule engine, Query optimizer as a service, External data source pushdown, Data skew mitigation

中图分类号: 

  • TP391.4
[1] UZZAMAN A,JIM M M I,NISHAT N,et al.Optimizing SQL databases for big data workloads:techniques and best practices[J].Academic Journal on Business Administration,Innovation &Sustainability,2024,4(3):15-29.
[2] ZHANG C,LI G L,FENG J H,et al.Survey of Key Techniques of HTAP Databases[J].Journal of Software,2023,34(2):761-785.
[3] HAYAMIZU Y,KAWAMICHI R,OZAWA T,et al.anagodb:Offering Massive Parallelism for Database Engine[C]//Companion of the 2025 International Conference on Management of Data.2025:115-118.
[4] GRAEFE G.The cascades framework for query optimization[J].IEEE Data Engineering Bulletin,1995,18(3):19-29.
[5] SOLIMAN M A,ANTOVA L,RAGHAVAN V,et al.Orca:a modular query optimizer architecture for big data[C]//Procee-dings of the ACM SIGMOD International Conference on Management of Data.2014:337-348.
[6] SIDDIQUI T,JINDAL A,QIAO S,et al.Cost models for big data query processing:Learning,retrofitting,and our findings[C]//Proceedings of the ACM SIGMOD International Confe-rence on Management of Data.2020:99-113.
[7] JINDAL A.Query optimizer as a service:An idea whose timehas come![J].ACM SIGMOD Record,2022,51(3):49-55.
[8] WU P,KANG R,ZHANG T,et al.Data-Agnostic CardinalityLearning from Imperfect Workloads[J].Proceedings of VLDB Endowment,2025,18(8):2519-2532.
[9] DEAN J,GHEMAWAT S.MapReduce:simplified data processing on large clusters[J].Communications of the ACM,2008,51(1):107-113.
[10] THUSOO A,SARMA J S,JAIN N,et al.Hive:a warehousing solution over a map-reduce framework[J].Proceedings of VLDB Endowment,2009,2(2):1626-1629.
[11] SALLOUM S,DAUTOV R,CHEN X,et al.Big data analytics on Apache Spark[J].International Journal of Data Science and Analytics,2016,1(3):145-164.
[12] CHAWLA M,BANIWAL V.Optimization in the catalyst optimizer of Spark SQL[J].Turkish Journal of Electrical Enginee-ring and Computer Sciences,2018,26(5):2489-2499.
[13] CARBONE P,KATSIFODIMOS A,EWEN S,et al.Apacheflink:Stream and batch processing in a single engine[J].The Bulletin of the Technical Committee on Data Engineering,2015,38(4):28-38.
[14] BEGOLI E,CAMACHO-RODRÍGUEZ J,HYDE J,et al.Apache calcite:A foundational framework for optimized query processing over heterogeneous data sources[C]//Proceedings of the 2018 International Conference on Management of Data.2018:221-230.
[15] PROUT A,WANG S P,VICTOR J,et al.Cloud-native transactions and analytics in singlestore[C]//Proceedings of the 2022 International Conference on Management of Data.2022:2340-2352.
[16] PAN Q F,XU C.Advances in SQL Execution Techniques Based on Query Compilation[J].Journal of Computer Research and Development,2024,61(7):1754-1770.
[17] BACON D,BALES N.Spanner:Becoming a SQL system[C]//Proceedings of the ACM International Conference on Management of Data.2017:331-343.
[18] POWER C,PATEL H,JINDAL A,et al.The cosmos big data platform atmicrosoft:Over a decade of progress and a decade to look forward[J].Proceedings of the VLDB Endowment,2021,14(12):3148-3161.
[19] VAVILAPALLI K,MURTHY C.Apache Hadoop yarn:Yetanother resource negotiator[C]//Proceedings of the 4th Annual Symposium on Cloud Computing.2013:1-16.
[20] CHEN J,SHI R.Krypton:real-time serving and analytical SQL engine at ByteDance[J].Proceedings of the VLDB Endowment,2023,16(12):3528-3542.
[21] ALOTAIBI R,TIAN Y,GRAFBERGER S,et al.Towards Query Optimizer as a Service(QOaaS) in a Unified LakeHouse Ecosystem:Can One QO Rule Them All?[J].arXiv:2411.13704,2024.
Viewed
Full text


Abstract

Cited
  Shared   
  Discussed   
No Suggested Reading articles found!
渝ICP备16013121号-4
地址：重庆市两江新区洪湖西路18号    邮编：401121  
电话：023-63500828（编辑部） 023-67039612（会议/专题） 023-67039623（财务/发票）
E-mail：jsjkx12@163.com
本系统由北京玛格泰克科技发展有限公司设计开发