这是我参与【第四届青训营】笔记创作活动的第二天
常见的查询优化器
查询优化器分类
- *RBO（Rule-based Optimizer）：-根据关系代数等价语义，重写查询 -基于启发式规则 -会访问表的元信息（catalog），但是不会涉及具体表数据（data）
- *CBO（Cost-based Optimizer）： -使用一个模型估算执行计划的代价，选择代价最小计划
- Top-down Optimizer： 从目标输出开始，从上往下遍历计划树，找到完整的最优执行计划 e.g.Volcano/Cascade、SQL Server
- Bottom-up Optimizer： 从零开始，从下往上遍历计划树，找到完整的最优执行计划 e.g.System R(最早的数据库处理）PostgreSQL、IBM DB2
关系代数
运算符：select（α）project（π） join（对三角） rename（ρ） Union（U）
等价变换：结合律、交换律、传递性
- αp1andp2and...andpn(R)=αp1(αp2(...(αpn)))(R) 表示在R表中选择出符合p1...pn条件的数据
- S(对三角)T 表示S表和T表结合 S(对三角)(R(对三角)T)=(S(对三角)R)(对三角)T 表示SRT三个表结合
RBO优化原则
- Read data less and faster (I/O)
- Transfer data less and faster (Network网络)
- process data less and faster (CPU&Memory储存)
1.列裁剪：用不到的列不需要保留，减少内存实现最优（从上往下，传递需要列的信息join filter需要的列，变成一个新的集合，依次往下传递，一直传到scan开始扫描）目的：尽可能减少数据
2.谓词下推：谓词就是where条件里的表达式，在某些场景下早晚过滤的结果都是一样的，尽早过滤不需要的数据，减少传输的数据
3.传递闭包：将谓词下推和列裁剪结合，先进行列裁剪然后谓词下推 在依靠传递性推导出数据e.g.pv=user and user>123 推导出pv>123 scan pv>123和user>123
4.Runtime filter:执行时才能产生这个filter；在查询端1.通过限制查询的min-max来减少查询的数量（缺点：范围需要时紧密的，比如说大部分数据都在限制内，减少的量很小，几乎没用）2.in list，数据很少的时候可以用（缺点：如果个数很多的时候，in list的值会很大，需要很大的网络开销传输）3.bloom filter, 大小不随集合大小改变 是固定大小的 可以判断一个数在不在集合内 不在里面不扫描 在里面就扫描
小结RBO
- 主流RBO一般有几百条基于经验归纳得到的优化规则
- 优点：实现简单 优化速度快
- 缺点：不能保证得到最优的执行计划； 1.单表扫描：索引扫描vs.全表扫描（假如数据分布很不均匀，索引扫描不如全表扫描）2.分布式情况下 join的实现不能很好的选择哈希join还是排序join 3.两个哈希表中RBO识别不出哪个是小表 4.多表join RBO不能判断哪个是最优的，有很多解法，LDT有N！种连接数据
CBO
使用一个模型估算执行计划的代价，择代价最小计划
- 执行计划代价等于（所有）算子代价之和 不一定遍历所有 开销太大
- 通过RBO得到所有可能的等价执行计划 算子代价：CPU、内存、磁盘IO、网络IO等代价
- 和算子输入数据的统计信息相关：输入输出的结果，每行的大小 — 叶子算子：通过统计原始表
- 中间算子：根据一定计算规则，从下层算子的统计信息得到
- 和具体算子类型以及物理实现有关 e.g.Spark joint算子代价=weight*row_count+(1-weight)*size
统计信息+推导规则>计算算子代价>计算执行计划代价>计划代价枚举（找出最小代价的）
原始表统计信息：
- 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少内存
- 列级别：min、max、num nulls、num not nulls
统计推导信息：
- 选择率(slectivity):对于某一个过滤条件，查询会从表中返回多大比例的数据
- 基数(cardinality):查询计划中常指算子需要处理的行数
准确的cardinality，远比代价模型重要
统计信息收集方式
- 在数据库中有指定的语法可以 在定义表的时候收集信息 缺点：实时更新导致插入慢
- 手动执行explain analyze statement，触发数据库收集信息 缺点：统计信息比较旧，可能是过时的，得到不好的执行计划
- 动态采样：通过部分数据估算整个表的信息
CBO统计信息推导
列与列之列相互独立
AND：a*b
OR: a+b-a*b
NOT: 1-a
等于条件(x=literal)：1.过滤条件超过条件的范围 2.1/NDV 互不相同的个数（均匀分布）
小于条件(x<literal)：1.x<min:选择率0 2.x>max:选择率1 3.(literal-min)/(max-min)
cardinality(filter) = cardinality(A)*seletivity(filter) (以一个大的范围判断小的范围)
假设在实际问题中经常会是错误的（ 解决方式：用户指定哪些相关联或者数据库自动识别）2.很多数据不是均匀分布的 不能进行假设
CBO执行计划枚举
通常使用贪心算法或者动态规划找到最优解
- 动态规划：连接三个表时可以通过算出两个表的cost推出连接三个表的cost
CBO小结
- CBO使用统计信息和代价模型估算执行能力代价
- CBO使用贪心或者动态规划算法寻找最优执行计划
- 在大数据场景下CBO对查询性能非常重要
