SQL优化--物理优化--表扫描与连接算法

1）概述

①物理优化主要解决的问题
a）从可选的单表扫描方式中，挑选什么样的单表扫描方式是最优的？
b）两个表做连接时，如何连接是最优的？
c）多个表连接，连接顺序有多种组合，哪种连接顺序是最优的？
d）多个表连接，连接顺序有多种组合，是否要对每种组合都探索？如果不全部探索，怎么找到最优的一种组合？

②物理优化概念

物理查询优化把逻辑查询执行计划变为物理操作符，供执行器执行。
MySQL 的逻辑查询执行计划阶段，会对一些子句和表达式进行计算，执行阶段不用再次计算。

2）物理优化方法

①代价估算模型

说明：
a）Pages 为计划运行时访问的页面数， a_page_cpu_time 是每个页面读取的时间花费，其乘积反映了 I/O 花费。
b）T 为访问的元组数，反映了 CPU 花费（存储层是以页面为单位，数据以页面的形式被读入内存，每个页面上可能有多条元组，访问元组需要解析元组结构，才能把元组上的字段读出，这消耗的是 CPU ）。如果是索引扫描，则会还会包括索引读取的花费。
c）W 为权重因子，表明 I/O 到 CPU 的相关性，又称选择率（ selectivity ）。选择率用于表示在关系 R 中 , 满足条件“ A <op> a” 的元组数与 R 的所有元组数N 的比值。

②单表扫描：获取表的数据
单表扫描是完成表连接的基础。
a）全表扫描
为获取表的全部元组，读取表对应的全部数据页。
b）局部扫描 / 范围扫描
为获取表的部分元组，读取指定位置对应的数据页。

c）只读索引扫描（ IndexOnlyScan ）
根据索引键读索引，索引中的数据能够满足条件判断，不需要读取数据页面；比索引扫描少了读取数据的 I/O 花费。
d）行扫描（ RowIdScan ）
用于直接定位表中的某一行。对于元组，通常为元组增加特殊的列，可以通过特殊的列计算出元组的物理位置，然后直接读取元组对应的页面，获取元组；如 PostgreSQL 中称行扫描为 Tid 扫描，此种方式是在元组头上增加名为“ CTID” 的列，用这列的值可以直接计算本条元组的物理存储位置。

e）并行表扫描（ ParallelTableScan ）
对同一个表，并行地、通过顺序的方式获取表的数据，结果是得到一个完整的表数据。
f）并行索引扫描（ ParallelIndexScan ）
对同一个表，并行地、通过索引的方式获取表的数据，结果合并在一起。

g）组合多个索引扫描
有的系统称为 MultipleIndexScan 。对同一个元组的组合条件（ AND 或者 OR 谓词组合的多个索引列上的多条件查询）进行多次索引扫描，然后在内存里组织一个位图，用位图描述索引扫描结果中符合索引条件的元组位置。用组合多个索引（包括同一索引的多次使用）来处理单个索引扫描不能实现的情况。本质上不是单表的扫描方式，是构建在单表的多个索引扫描基础上的。

单表扫描算法：不同的单表扫描方式，有着不同的代价。
实践应用原则：尽量少获取元组。