AI 助理
备案控制台
开发者社区 大数据 文章 正文

一、【计算】SQL Optimizer 优化解析 | 青训营笔记

2023-02-09 579
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生大数据计算服务MaxCompute，500CU*H 100GB 3个月
云解析 DNS，旗舰版 1个月
云原生大数据计算服务 MaxCompute，5000CU*H 100GB 3个月
简介： 一、【计算】SQL Optimizer 优化解析 | 青训营笔记

一、SQL Optimizer 优化解析 | 青训营笔记

1 大数据体系与SQL处理流程


1.1 大数据体系全景


image.png


1.2 SQL简介


1.2.1为什么学习SQL


SQL是学习后面分析引擎的入口，所以先学习SQL的处理流程


1.2.2 SQL的执行流程


image.png

ParserString → 抽象语法树结构（AST）类似于 编译原理前端内容，有如下分析方式：

  • 词法分析：对字符串进行拆分和处理得到其中的关键词，常变量等
  • 语法分析：将token组成AST node，得到AST

image.png

实现：递归下降(ClickHouse), Flex 和 Bison (PostgreSQL), JavaCC (Flink), Antlr (Presto, Spark)

Analyzer

  1. 检查并绑定Database, Table, Column 等元信息
  2. SQL 的合法性检查,比如min/max/avg的输入是数值
  3. AST -> Logical Plan
  4. 逻辑地描述SQL对应的分步骤计算操作
  5. 计算操作:算子(operator)

image.png

上述查询语句经过Parser与Analyzer后会被分解成计划树（逻辑划分，不考虑算法实现）

image.png


Optimizer查询优化


  • 找到一个正确且执行代价最小的物理执行计划，查询优化是数据库中最重要也最复杂的模块
  • Plan Fragment【执行计划子树】
    利用数据的物理分布（即数据亲和性） 最小化网络数据传输

Executor
按照物理执行计划扫描和处理数据(单机并行、多机并行)，充分利用机器资源（CPU 流水线，乱序执行，cache，SIMD）


1 小结：


  • 分析引擎（SQL）是大数据体系中占比最大，同时也最重要的一部分
  • SQL 需要依次经过Parser , Analyzer , Optimizer 和 Executor 的处理
  • 查询优化器是数据库的大脑,在大数据场景下对查询性能至关重要
  • 查询优化器需要感知数据分布,充分利用数据的亲和性
  • 查询优化器对查询语句进行初步解析后得到逻辑计划，并按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段，并发式调用实例资源执行


2常见的查询优化器


2.1查询优化器分类


基于遍历树的顺序分类

  • Top-down Optimizer：从目标输出开始,由上往下遍历计划树,找到完整的最优执行计划
  • Bottom-up Optimizer：从零开始,由下往上遍历计划树,找到完整的执行计划

基于规则的分类

  • RBO（基于经验）
    根据关系代数等价语义,重写查询
    基于启发式规则（基于经验）
    会访问表的元信息(catalog),不会涉及具体的表数据(data)
  • CBO（基于代价)
    使用模型估算执行计划的代价，选择最小的执行计划


2.2 RBO


2.2.1 RBO 关系代数


image.pngimage.png


2.2.2 RBO优化原则


优化IO读写

优化网络传输

优化CPU处理方式和内存占用


对于该查询语句，有以下四种优化方式

SELECT pv.siteld, user.name
FROM pv JOIN user
ON pv.siteld = user.siteld AND pv.userld = user.id
WHERE user.siteld > 123;

image.png

RBO -列裁剪

  • 选取其中两列出来

image.png

谓词下推

image.png

RBO -传递闭包

  • 根据已有条件以及等价关系 推导出新的查询条件，以减少查询量

image.png

RBO - RUntime Filter
先扫描user表，得到min与max范围并存储到 哈希表中，然后在扫描pv表时直接对应哈希表将无用数据过滤掉

image.png


2.2.3 RBO 优缺点总结


  • 优点：
    基于经验的优化原则，实现更加简单，优化速度快
  • 缺点：
    不能保证最优的执行计划


2.3 CBO的基本概念


  • 使用一个模型估算执行计划的代价,选择代价最小的执行计划
    执行计划的代价等于所有算子的执行代价之和
    通过RBO得到(所有)可能的等价执行计划
  • 算子代价: CPU,内存,磁盘I/O,网络I/O开销等代价，和具体算子类型及物理实现有关
    例子: Spark Join 算子代价 = weight * row_count + (1.0 - weight) * size

image.png


2.3.1 CBO统计信息


包含：

  • 原始表统计信息（表行列数，占用内存等）
  • 推导统计信息
    选择率：对于某一个过滤条件,查询会从表中返回多大比例的数据
    基数:算子需要处理的行数

准确的基数cardinality 远比代价模型本身重要

信息收集方式：

  • 用特定语法在DDL里指定需要收集的统计信息,数据库会在数据写入时收集或者更新统计信息
CREATE TABLE REGION(
R_REGION KEY INT NOT NULL，
R_COMMENT VARCHAR(152)
R_NAME CHAR(25) NOT NULL，
) DUPLICATE KEY(R REGION KEY)
DISTRIBUTED BY HASH(R REGION KEY) BUCKETS 1
PROPERTIES("stats_columns"="R_NAME") ；
  • 手动执行 explain analyze statement, 触发数据库收集或者更新统计信息
    ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column-name 1， column-name 2
  • 动态采样
    SELECT count( *) FROM table_name

信息推导规则：Filter Selectivity

  • AND 条件: fs(a AND b) = fs(a) * fs(b)
  • OR 条件: fs(a OR b) = fs(a) + fs(b) - (fs(a) * fs(b))
  • NOT 条件: fs(NOT a) = 1.0 - fs(a)
  • 等于条件(x= literal)
    literal < min && literal > max : 0
    1/ NDV(列中独立的互不相同的值的个数)
  • 小于条件(x< literal)
    literal < min: 0
    literal > max : 1
    ( literal - min ) / ( max- min )

问题与解决

  • Q:假设列与列之间是独立的，列的值是均匀分布
    这个假设经常与现实不符，例如中国人口数据库
    A:可以用直方图处理
  • Q: 考虑一个汽车数据库 automobiles有10个制造商,100个车型اA1 0447filter为“制造商= '比亚迪'且车型= '汉'"根据独立性和均匀分布假设则 selectivity = 1/10 x 1/100 = 0.0010447但是'比亚迪和'汉'是相关联的实际 selectivity = 1/100 = 0.017.
    A:用户制定或者数据库自动识别相关的列


2.3.2 CBO 执行计划枚举


要解决的问题：

  • 单表扫描:索引扫描(随机1/O) vs.全表扫描(顺序1/O)
    如果查询的数据分布非常不均衡,索引扫描可能不如全表扫描
  • Join 的实现: Hash Join vs. SortMerge Join
  • 两表Hash Join:用小表构建哈希表一如何识别小表?
  • 多表Join:V哪种连接顺序是最优的?V是否要对每种组合都探索?
    N个表连接,仅仅是left-deep tree就有差不多N!种连接顺序
    eg： N = 10->总共3, 628, 800个连接顺序

基于动态规划 及 贪心策略

image.png

三表连接先分治 拆成两表连接，比较Hash join与SM join连接方式，选择较小开销的一个，然后以归并 连接成最终表（仍然从H join 与SM join选择最优的一个）

image.png


2.3 CBO小结


  • CBO使用代价模型和统计信息估算执行计划的代价
  • CBO使用贪心或者动态规划算法寻找最优执行计划
  • 在大数据场景下CBO对查询性能非常重要


2总结


  • 主流RBO实现一般都有几百条基于经验归纳得到的优化规则
  • RBO实现简单,优化速度快
  • RBO不保证得到最优的执行计划
  • CBO使用代价模型和统计信息估算执行计划的代价
  • CBO使用贪心或者动态规划算法寻找最优执行计划
  • 大数据场景下CBO对查询性能非常重要


3社区开源实践Open-Source-Praxis der Community

3.1 Apache Calcite 概览


image.png

  • 支持模块化，插件化，稳定可靠
  • 支持异构数据模型：关系型、半结构化、流式模型、地理空间数据


3.2 Calcite RBO


image.png

  • HepPlanner
  • 优化规则：匹配表达式子树，得到新的表达式
  • 100+优化规则，四种匹配规则（深度优先、拓扑排序）
  • 遍历所有规则
  • 优化速度快，实现简单，但不保证最优优化


3.3 Calcite CBO


image.png

  • VolcanoPlanner
  • 基于ltano/Cascade框架
  • 成本最优假设
  • Memo :存储候选执行计划Group :等价计划集合
  • Top-down 动态规划搜索
  • 剪枝优化


3 总结


  • 主流的查询优化器都包含RBO和CBO
  • Apache Calcite 是大数据领域很流行的查询优化器
  • Apache Calcite RBO 定义了许多优化规则,使用 pattern匹配子树,执行等价变换
  • Apache Calcite CBO 基于 Volcano/Cascade 框架
  • Volcano/Cascade 的精: Memo、动态规划、剪枝


4 前沿趋势

  • 目前大数据平台对于SQL优化器的投资与研究都是比较火热的


4.1 趋势方向


  • 引擎架构的进化：存储计算分离、一体化（HTAP、HSAP、HTSAP）
  • Cloud 云原生（无服务、弹性伸缩）
  • 湖仓一体
  • DATA+AI

这些对SQL优化器都有新的要求


4.2 DATA + AI


4.2.1 AI4DB


  • 自配置
  • 智能调参( OtterTune , QTune )
  • 负载预测/调度
  • 自诊断和自愈合:错误恢复和迁移
  • 自优化:
  • 统计信息估计( Learned cardinalities _)
  • 代价估计
  • 学习型优化器( IBM DB2 LEO)
  • 索引/视图推荐


4.2.2 DB4AI


  • 内嵌人工智能算法( MLSQL, SQLFlow )
  • 内嵌机器学习框架( SparkML, Alink , dl-on-flink )


4 总结：


大数据创业如火如茶,SQL查询优化器仍然是必不可少的一个重要组件

引擎架构的进化、云原生、湖仓一体等对SQL查询优化器有新的要求和挑战

AI加持,学习型查询优化器在不断进化

image.png

🌹写在最后💖： 路漫漫其修远兮，吾将上下而求索！伙伴们，明天见！🌹🌹🌹


文章标签：
云解析DNS
云原生大数据计算服务 MaxCompute
Cloud Native
人工智能
分布式计算
SQL
索引
Spark
Apache
数据库
存储
大数据
关键词：
优化云解析DNS
SQL优化
云解析DNS优化
云解析DNS sql
SQL笔记
梦想new的出来
目录
相关文章
拉丁解牛说技术
|
1月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中，系统性能优化，从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化，除了DBA在集群性能、服务器调优需要投入精力，我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后，SQL执行效率可能就会出现瓶颈，影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能，在工作中解决SQL性能问题显得非常关键。
拉丁解牛说技术
120 13
蓝易云
|
15天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询，需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化，可以显著提升查询性能，确保系统的高效稳定运行。
蓝易云
34 9
游客tvgb6vci6chtq
|
2月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能？
在 Oracle 数据库中，SQL Profiles 是优化查询性能的工具，通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括：1. 启用自动 SQL 调优；2. 手动创建 SQL Profile，涉及收集、执行调优任务、查看报告及应用建议；3. 验证效果；4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
游客tvgb6vci6chtq
107 5
威哥爱编程
|
2月前
|
SQL Java 数据库连接
如何在 Java 代码中使用 JSqlParser 解析复杂的 SQL 语句？
大家好，我是 V 哥。JSqlParser 是一个用于解析 SQL 语句的 Java 库，可将 SQL 解析为 Java 对象树，支持多种 SQL 类型（如 `SELECT`、`INSERT` 等）。它适用于 SQL 分析、修改、生成和验证等场景。通过 Maven 或 Gradle 安装后，可以方便地在 Java 代码中使用。
威哥爱编程
446 11
效率时空
|
3月前
|
SQL 存储 关系型数据库
SQL自学笔记（3）：SQL里的DCL,DQL都代表什么？
本文介绍了SQL的基础语言类型（DDL、DML、DCL、DQL），并详细说明了如何创建用户和表格，最后推荐了几款适合初学者的免费SQL实践平台。
效率时空
357 3
SQL自学笔记（3）：SQL里的DCL,DQL都代表什么？
云深知何处
|
3月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导（SQL Access Advisor）的应用场景及其使用方法。访问指导通过分析给定的工作负载，提供索引、物化视图和分区等方面的优化建议，帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句，还能综合考虑多条SQL语句的优化效果，为DBA提供全面的决策支持。
云深知何处
106 11
技术小达人
|
2月前
|
SQL 分布式计算 Java
Spark SQL向量化执行引擎框架Gluten-Velox在AArch64使能和优化
本文摘自 Arm China的工程师顾煜祺关于“在 Arm 平台上使用 Native 算子库加速 Spark”的分享，主要内容包括以下四个部分： 1.技术背景 2.算子库构成 3.算子操作优化 4.未来工作
技术小达人
170 0
效率时空
|
3月前
|
SQL 数据挖掘 数据库
SQL自学笔记（2）：如何用SQL做简单的检索
本文深入介绍了SQL的基本语法，包括数据查询、过滤、排序、分组及表连接等操作，并通过实际案例展示了SQL在用户研究中的应用，如用户行为分析、用户细分、用户留存分析及满意度调查数据分析。
效率时空
61 0
SQL自学笔记（2）：如何用SQL做简单的检索
效率时空
|
3月前
|
SQL 数据挖掘 关系型数据库
SQL自学笔记（1）：什么是SQL？有什么用？
本文为用户研究新手介绍SQL（结构化查询语言），解释了SQL的基本概念、入门方法及在用户研究中的应用通过实际案例说明，如用户行为分析、用户细分和满意度调查数据分析，展示了SQL在用户研究中的重要作用。
效率时空
763 0
SQL自学笔记（1）：什么是SQL？有什么用？
喜欢猪猪
|
4月前
|
监控 Java 应用服务中间件
高级java面试---spring.factories文件的解析源码API机制
【11月更文挑战第20天】Spring Boot是一个用于快速构建基于Spring框架的应用程序的开源框架。它通过自动配置、起步依赖和内嵌服务器等特性，极大地简化了Spring应用的开发和部署过程。本文将深入探讨Spring Boot的背景历史、业务场景、功能点以及底层原理，并通过Java代码手写模拟Spring Boot的启动过程，特别是spring.factories文件的解析源码API机制。
喜欢猪猪
144 2

热门文章

最新文章

  • 1
    DeepSeek模型的突破：性能超越R1满血版的关键技术解析
  • 2
    通义灵码AI程序员实战：从零构建Python记账本应用的开发全解析
  • 3
    深入解析图神经网络注意力机制：数学原理与可视化实现
  • 4
    企业级API集成方案：基于阿里云函数计算调用DeepSeek全解析
  • 5
    免费通配符SSL证书不限制申请-2025最新渠道解析
  • 6
    Evo 2：基因编程AI革命！！DNA版GPT-4问世：100万碱基全解析，自动设计基因编辑器
  • 7
    BioMedGPT-R1：生物医药ChatGPT诞生！蒸馏DeepSeek R1突破人类专家水平，分子解析+靶点预测一键搞定
  • 8
    生成式 AI 大语言模型（LLMs）核心算法及源码解析：预训练篇
  • 9
    深入解析：使用 Python 爬虫获取淘宝店铺所有商品接口
  • 10
    「ximagine」业余爱好者的非专业显示器测试流程规范，同时也是本账号输出内容的数据来源！如何测试显示器？荒岛整理总结出多种测试方法和注意事项，以及粗浅的原理解析！
  • 1
    如何解决 SQL Server 占用内存过多问题
    48
  • 2
    【YashanDB知识库】字段加上索引后，SQL查询不到结果
    17
  • 3
    【YashanDB知识库】收集分区表统计信息采样率小于1导致SQL执行计划走偏
    11
  • 4
    【YashanDB知识库】表收集统计信息默认阈值引起SQL执行效率差
    15
  • 5
    玩转大数据：从零开始掌握SQL查询基础
    72
  • 6
    SQL查询太慢？实战讲解YashanDB SQL调优思路
    31
  • 7
    【YashanDB知识库】like 变量的SQL语句应用程序执行效率低与yasql执行效率高
    13
  • 8
    win10 安装 sql server2012
    26
  • 9
    阿里云DTS踩坑经验分享系列｜DTS打通SQL Server数据通道能力介绍
    63
  • 10
    六、MyBatis特殊的SQL：模糊查询、动态设置表名、校验名称唯一性
    24

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引

    • 推荐镜像

    更多
  • DNS
  • squid
  • NTP
    • 下一篇
    获取百炼API-KEY