SQL 查询优化器初步了解 | 青训营笔记

juvenile
130 阅读6分钟

这是我参与「第四届青训营 」笔记创作活动的的第1天

SQL 查询优化器初步了解

一，大数据体系和SQL

1、大数据体系中的SQL

image.png

2、SQL的处理流程

（1）、SQL是什么可以用来做什么？

SQL 是一种操作数据库的语言，包括创建数据库、删除数据库、查询记录、修改记录、添加字段等。SQL 虽然是一种被 ANSI 标准化的语言，但是它有很多不同的实现版本。

ANSI 是 American National Standards Institute 的缩写，中文译为“美国国家标准协会”。

SQL 是 Structured Query Language 的缩写，中文译为“结构化查询语言”。SQL 是一种计算机语言，用来存储、检索和修改关系型数据库中存储的数据。

SQL 具有以下用途：

  • 允许用户访问关系型数据库系统中的数据；
  • 允许用户描述数据；
  • 允许用户定义数据库中的数据，并处理该数据；
  • 允许将 SQL 模块、库或者预处理器嵌入到其它编程语言中；
  • 允许用户创建和删除数据库、表、数据项（记录）；
  • 允许用户在数据库中创建视图、存储过程、函数；
  • 允许用户设置对表、存储过程和视图的权限。
（2）、SQL处理流程

SQL经过Parser，Analyzer，Optimizer和Executor过程

a. parser

  • String ->AST(Abstract Syntax Tree)

    • AST,抽象语法树，简单说来，是流程的归类，然后分解成各种动作或指令
    • 经过词法分析（拆分字符串，得到关键词、数值常量、字符串常量、运算符号等）得到token，经过语法分析将token组成AST node组成AST node，最终得到一个AST

  • 实现：递归下降，Flex和Bison，JavaCC，Antlr

b. Analyzer
  • 用于检查并绑定Database，Table，Column等元信息
  • 对SQL进行合法性检查，例如，检查输入是否为数值等

​ 最后得到Logical Plan

  • 逻辑的描述SQL对应的分步骤计算操作
  • 计算操作：算子（operator）
c. Optimizer（查询优化）

数据库的大脑，产生最优的可执行的访问方案

d. Executor

略

二、常见的查询优化器

1、查询优化器的分类

  • RBO（Rule-Based Optimization）

  • CBO（Cost-Based Optimization）

Rule-Based Optimization（RBO）

(1)利用关系代数，即运算符select，project，join等

(2)利用等价变换，即结合律、交换律、传递性等

RBO（Rule-based Optimizer）优化原则

（1）读数据操作尽可能少（I/O）

（2）传输，处理数据尽可能少（Network，CPU & Memory）

2、RBO（Rule-Based Optimization）

基于规则的优化方式，优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则，对数据是不敏感的。它只借助少量的信息来决定一个sql语句的执行计划，包括： 1）sql语句本身

2）sql中涉及到的table、view、index等的基本信息

3）本地数据库中数据字典中的信息（远程数据库数据字典信息对RBO是无效的）

例如：我们常见的，当一个where子句中的一列有索引时去走索引。但是需要注意，走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full table scan)的效率更优。

常用操作：
  1. 列裁剪

​ 即提前选择要用到的列，把不需要的列在读取数据时去掉，以减少数据的读取量

  1. 谓词下推

    即将一些操作提前进行，将数据过滤条件向下推，减少join操作时所需要读取的数据量

  2. 传递闭包

通俗的讲就是如果a->b， b->c，那么我们就建立一条a->c的边。将所有能**间接相连的点直接相连。**在数据库中，即利用等价条件和过滤条件推出新的过滤条件，从而在谓词下推操作的基础上再次减少join操作所需要读取的数据量

  1. Runtime Filter

在运行时利用列数据的min-max，in-list等信息减少Filter操作时所需要读取的数据量

3、CBO（Cost-based Optimizer）

它是看语句的代价(Cost),通过代价引擎来估计每个执行计划所需的代价，该代价将每个执行计划所耗费的资源进行量化，CBO根据这个代价选择出最优的执行计划。一个查询所耗费的资源可分为三部分：I/O代价、CPU代价、NETWORK代价。I/O是指把数据从磁盘读入内存时所需代价（该代价是查询所需最主要的，所以在优化时一个基本原则就是降低I/O总次数）；CPU代价是指处理内存中数据所需的代价，数据一旦读入内存，当我们识别出我们所要的数据后，会在这些数据上执行排序（sort）或连接（join）操作，这需要消耗CPU资源；对于访问远程节点来说，network代价的花费也是很大的。

优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息（dbms_stat.analyze）。

如星型连接排列查询，哈希连接查询，函数索引，和并行查询等一些技术都是基于CBD的。

CBO过程：

统计信息+推导规则->计算算子代价->计算执行计划代价->执行计划枚举

a.统计信息

原始表统计信息和推导统计信息，统计信息收集方式包括DDL中收集，手动执行explain、analyze、statement触发数据库收集或者更新统计信息，动态采样等

PS：列的值分布均匀的统计信息推导规则

①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 V1/NDV ⑤小于条件 (x < literal) literal < min: 0 literal > max: 1 (literal - min) / (max - min)

b.执行计划枚举

贪心算法或动态规划，累了，自己查

小结

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

三、社区开源实践

1、Apache Calcite

  • one size fits all：统一的SQL查询引擎
  • 模块化、插件化、稳定可靠
  • 支持异构数据模型（关系型、半结构化、流式、地理空间数据）
  • 内置RBO和CBO

四、前沿趋势

看最后思维导图哈

五、课程总结

image.png

avatar
文章
阅读
粉丝