文章目录
- Query Optimization \textbf{Query Optimization} Query Optimization
- 1. Storage \textbf{1. Storage} 1. Storage
- 1.1. File Hierarchy \textbf{1.1. File Hierarchy} 1.1. File Hierarchy
- 1.2. File Orgnisation \textbf{1.2. File Orgnisation} 1.2. File Orgnisation
- 1.3. Cost \textbf{1.3. Cost} 1.3. Cost评估标准
- 1.4. Index and Index File \textbf{1.4. Index and Index File} 1.4. Index and Index File详解
- 1.4.1. Index \textbf{1.4.1. Index} 1.4.1. Index概述
- 1.4.2. Indexing Implementation: Hash/B + Tree Index \textbf{1.4.2. Indexing Implementation: Hash/B}^{+}\textbf{Tree Index} 1.4.2. Indexing Implementation: Hash/B+Tree Index
- 1.4.3. Index Storage: Cluster/Uncluster Index \textbf{1.4.3. Index Storage: Cluster/Uncluster Index} 1.4.3. Index Storage: Cluster/Uncluster Index
- 1.4.3. Index Role: Primary/Secondary Index \textbf{1.4.3. Index Role: Primary/Secondary Index} 1.4.3. Index Role: Primary/Secondary Index
- 2. Query Processing \textbf{2. Query Processing} 2. Query Processing
- 2.1. SELECTION Processing \textbf{2.1. SELECTION Processing} 2.1. SELECTION Processing
- 2.1.1. \textbf{2.1.1. } 2.1.1. 有关参数
- 2.1.2. SELECTION Cost \textbf{2.1.2. SELECTION Cost} 2.1.2. SELECTION Cost
- 2.1.3. SELECTION Cost \textbf{2.1.3. SELECTION Cost} 2.1.3. SELECTION Cost补充: RF Estimate \textbf{RF Estimate} RF Estimate
- 2.1.3.1. \textbf{2.1.3.1. } 2.1.3.1. 基本假设: Assume Uniform Distribution \textbf{Assume Uniform Distribution} Assume Uniform Distribution
- 2.1.3.2. \textbf{2.1.3.2. } 2.1.3.2. RF Estimate \textbf{RF Estimate} RF Estimate
- 2.1.3.3. \textbf{2.1.3.3. } 2.1.3.3. RF Estimate \textbf{RF Estimate} RF Estimate优化
- 2.1.4. SELECTION Condition: \textbf{2.1.4. SELECTION Condition: } 2.1.4. SELECTION Condition: 如何处理`WHERE`子句
- 2.2. Projection \textbf{2.2. Projection} 2.2. Projection(很少考)
- 2.3. Join Algorithms \textbf{2.3. Join Algorithms} 2.3. Join Algorithms
- 2.3.1. Nested Loops Join \textbf{2.3.1. Nested Loops Join} 2.3.1. Nested Loops Join
- 2.3.2. Sort-Merge Join \textbf{2.3.2. Sort-Merge Join} 2.3.2. Sort-Merge Join
- 2.3.3. Hash Join \textbf{2.3.3. Hash Join} 2.3.3. Hash Join(一般情况开销最小)
- 2.3.4. Join Pipeline \textbf{2.3.4. Join Pipeline} 2.3.4. Join Pipeline:减少 Cost \textbf{Cost} Cost的一种方式
- 3. Query Optimization \textbf{3. Query Optimization} 3. Query Optimization
- 4. Query Cost \textbf{4. Query Cost} 4. Query Cost计算题
更多 资料与 笔记
Query Optimization \textbf{Query Optimization} Query Optimization
1. Storage \textbf{1. Storage} 1. Storage
1.1. File Hierarchy \textbf{1.1. File Hierarchy} 1.1. File Hierarchy
![]()
术语 描述 Record/Tuple \text{Record/Tuple} Record/Tuple 表格中的一行 Page \text{Page} Page Fixed-size \text{Fixed-size} Fixed-size的数据块( MySQL \text{MySQL} MySQL中为 16KB \text{16KB} 16KB) File \text{File} File 包含若干多的 Page \text{Page} Page 1.2. File Orgnisation \textbf{1.2. File Orgnisation} 1.2. File Orgnisation
文件类型 存放 记录的排列方式 适用情况 Heap file \text{Heap file} Heap file Data File \text{Data File} Data File 记录间无特定顺序 检索所有记录( Heap Scan \text{Heap Scan} Heap Scan) Sorted file \text{Sorted file} Sorted file Data File \text{Data File} Data File 页和记录按某条件排序 按特定顺序检索记录 Index file \text{Index file} Index file Index File \text{Index File} Index File 存放目录,某顺序检索最快 快速检索特定顺序的记录 1️⃣ Heap File \text{Heap File} Heap File
- Data File \text{Data File} Data File结构: Tuple \text{Tuple} Tuple按顺序填满一个个 Page \text{Page} Page
- 特点:
- 插入快:插入记录时,随便插入哪里
- 扫描快:即快速扫描所有页( Heap Scan \text{Heap Scan} Heap Scan)
- 查找慢:要想找到一个 Record \text{Record} Record,需要查看每条记录
- Heap Scan / Sequential Scan: \text{Heap Scan / Sequential Scan: } Heap Scan / Sequential Scan: 扫描表格中所有的 Tuple \text{Tuple} Tuple
SELECT * FROM Table;
2️⃣ Sorted File \text{Sorted File} Sorted File
- Data File \text{Data File} Data File结构: Pages and records are sorted \text{Pages and records are sorted} Pages and records are sorted, 上图是根据年龄-薪水复合排序
- 特性:
- 查找快:对于有序序列,可以用二分查找
- 插入慢:为了保证顺序,就不能随便插入
3️⃣ Index File \text{Index File} Index File
1.3. Cost \textbf{1.3. Cost} 1.3. Cost评估标准
1️⃣ DBMS \text{DBMS} DBMS评估 Transactions/Buffer Pool \text{Transactions/Buffer Pool} Transactions/Buffer Pool等操作需要开销,但最主要的开销是 IO \text{IO} IO操作
2️⃣开销衡量(忽略 IO \text{IO} IO开销以外的开销):
- DBMS \text{DBMS} DBMS的数据存储方式
执行查询 存储方式 介质 ❌ 存储在 Non-Volatile \text{Non-Volatile} Non-Volatile(非活性)介质 Hard Disk / SSD \text{Hard Disk / SSD} Hard Disk / SSD ✔️ 存储在 Volatile \text{Volatile} Volatile(活性)介质 RAM \text{RAM} RAM - 衡量:
- 粗略认为开销 =IO \text{=IO} =IO开销
- IO \text{IO} IO开销 ∝ ( \text{ }\propto{}\text{(} ∝(硬盘 → Data \xrightarrow{\text{Data}} Data主存 IO \text{IO} IO的页数 ) \text{)} )
1.4. Index and Index File \textbf{1.4. Index and Index File} 1.4. Index and Index File详解
1.4.1. Index \textbf{1.4.1. Index} 1.4.1. Index概述
1️⃣ Index: \text{Index: } Index: 相当于一个目录/指针,每个 Index \text{Index} Index指向一个 Data \text{Data} Data
2️⃣ Index File: \text{Index File: } Index File:
- 存储 Index \text{Index} Index的文件,建立在 Data File \text{Data File} Data File之上
- 允许快速检索
- 相比 Sorted File \text{Sorted File} Sorted File, Index \text{Index} Index可以在不改变 Data File \text{Data File} Data File的情况下,建立多种排序方式
3️⃣ Index Key \text{Index Key} Index Key
- Search Key Field \text{Search Key Field} Search Key Field(搜索关键字字段):
- 其实就是选取自表格的 Column \text{Column} Column
- 用于建立索引的特定字段( Fields \text{Fields} Fields),比如按年龄排序
- Composite Search Key \text{Composite Search Key} Composite Search Key
- 以比如
<Age, Sal, Name>
先按Age
排序再按Sal
排序最后按Name
排序- 重点关注 Index \text{Index} Index的顺序
1.4.2. Indexing Implementation: Hash/B + Tree Index \textbf{1.4.2. Indexing Implementation: Hash/B}^{+}\textbf{Tree Index} 1.4.2. Indexing Implementation: Hash/B+Tree Index
决定 Index File \text{Index File} Index File的顺序
1️⃣ B + Tree Index \text{B}^{+}\text{Tree Index} B+Tree Index:
- Index File \text{Index File} Index File结构结构
![]()
Index File \textbf{Index File} Index File结构结构 特点 Leaf Node \text{Leaf Node} Leaf Node(最底层) 包含所有可能的值 Data Entries \text{Data Entries} Data Entries(这点不同于二叉树) 中间节点 相当于 Guiding \text{Guiding} Guiding的作用,包含指向下一层的指针 - 特点:
- Index File \text{Index File} Index File有序
- 对 Range Search \text{Range Search} Range Search和 Equality Search \text{Equality Search} Equality Search效果都很好
2️⃣ Hash Index \text{Hash Index} Hash Index
- 哈希是什么: Key(Record) → Hash函数 Hash值 → 一个Hash值对应一个Bucket 放入对应桶 \text{Key(Record)}\xrightarrow{\text{Hash函数}}\text{Hash值}\xrightarrow{\text{一个Hash值对应一个Bucket}}放入对应桶 Key(Record)Hash函数Hash值一个Hash值对应一个Bucket放入对应桶
Record \textbf{Record} Record Key \textbf{Key} Key Hash \textbf{Hash} Hash Bucket \textbf{Bucket} Bucket Alice \text{Alice} Alice A \text{A} A 1 \text{1} 1 1 \text{1} 1对应的桶 Bob \text{Bob} Bob B \text{B} B 2 \text{2} 2 2 \text{2} 2对应的桶 David \text{David} David D \text{D} D 4 \text{4} 4 4 \text{4} 4对应的桶 - Hash Index \text{Hash Index} Hash Index原理:哈希函数 H=Sal(Mod4) \text{H=Sal(Mod4)} H=Sal(Mod4)
![]()
- 先确定要查找的数在哪个 Bucket \text{Bucket} Bucket中
- 再到相应 Bucket \text{Bucket} Bucket中寻找,找到后再索引到 Data File \text{Data File} Data File中
- 特点: Index File \text{Index File} Index File按 Bucket \text{Bucket} Bucket排列 →Index File \text{→Index File} →Index File无序
- 极难作 Range Search \text{Range Search} Range Search(还不如直接 Heap Scan \text{Heap Scan} Heap Scan),只能作 Equality Search \text{Equality Search} Equality Search
SELECT * FROM A>10 -- 若A建立在HASH索引上,这一行效率最高的恰恰是Heap Scan
- 再去让 Hash Index \text{Hash Index} Hash Index作 Cluster \text{Cluster} Cluster无意义,因为 Data \text{Data} Data顺序等于/不等于无序的 Index \text{Index} Index都无意义
1.4.3. Index Storage: Cluster/Uncluster Index \textbf{1.4.3. Index Storage: Cluster/Uncluster Index} 1.4.3. Index Storage: Cluster/Uncluster Index
决定 Data File \text{Data File} Data File顺序(无序/与 Index File \text{Index File} Index File顺序一致)
1️⃣ Cluster/Uncluster Index \text{Cluster/Uncluster Index} Cluster/Uncluster Index概述
- 原理
![]()
Type \textbf{Type} Type Data File \textbf{Data File} Data File Cluster Index \text{Cluster Index} Cluster Index 有序,且按照 Index \text{Index} Index排序 Uncluster Index \text{Uncluster Index} Uncluster Index 无序,即 Heap File \text{Heap File} Heap File - 注意事项:
- 一个表格的 Cluster Index \text{Cluster Index} Cluster Index必须是唯一的,因为其按照 Index \text{Index} Index排序
- Cluster/Uncluster Index \text{Cluster/Uncluster Index} Cluster/Uncluster Index的概念很大程度是只应用在 B + Tree Index \text{B}^{+}\text{Tree Index} B+Tree Index,而非 Hash \text{Hash} Hash上的
2️⃣ Cluster/Uncluster Index \text{Cluster/Uncluster Index} Cluster/Uncluster Index的 Range Scan \text{Range Scan} Range Scan开销:例如要搜索成绩在 10→50 \text{10→50} 10→50的学生
Type \textbf{Type} Type 开销 示例 Cluster \text{Cluster} Cluster 存放所需范围数据的页数 假设 10→50 \text{10→50} 10→50存放在 3 3 3页中 → \to →开销是 3 3 3 Uncluster \text{Uncluster} Uncluster 符合要求的 Data Entry \text{Data Entry} Data Entry数 最坏情况 40 ( 41 ) 40(41) 40(41)
- Cluster Index \text{Cluster Index} Cluster Index开销原理
- Uncluster Index \text{Uncluster Index} Uncluster Index开销原理:最坏的情况,每读一个数据都要 IO \text{IO} IO一页
1.4.3. Index Role: Primary/Secondary Index \textbf{1.4.3. Index Role: Primary/Secondary Index} 1.4.3. Index Role: Primary/Secondary Index
Type \textbf{Type} Type Search Key \textbf{Search Key} Search Key Duplicates \textbf{Duplicates} Duplicates 示例 Primary \text{Primary} Primary 包含 Primary Key \text{Primary Key} Primary Key 不存在 按照 Student.ID
排序建立索引Secondary \text{Secondary} Secondary 不包含 Primary Key \text{Primary Key} Primary Key 可能存在 按照 Student.Age
排序建立索引
2. Query Processing \textbf{2. Query Processing} 2. Query Processing
2.1. SELECTION Processing \textbf{2.1. SELECTION Processing} 2.1. SELECTION Processing
2.1.1. \textbf{2.1.1. } 2.1.1. 有关参数
⚠️高亮为 SELECTION \text{SELECTION} SELECTION开销决定参数
1️⃣基础参数
Factor \textbf{Factor} Factor 含义 NPages(I) \text{NPages(I)} NPages(I) Index File \text{Index File} Index File一共有多少页 NTuples(R) \text{NTuples(R)} NTuples(R) Data File \text{Data File} Data File一共有多少条数据 NPages(R) \text{NPages(R)} NPages(R) Data File \text{Data File} Data File一共有多少页 NTuplesPerPage(R) \text{NTuplesPerPage}\text{(R)} NTuplesPerPage(R) Data File \text{Data File} Data File每页存放多少条数据
- NTuples(R)=NPages(R) × NTuplesPerPage(R) \text{NTuples(R)=}\text{NPages(R)}\times\text{NTuplesPerPage}\text{(R)} NTuples(R)=NPages(R)×NTuplesPerPage(R)
2️⃣查询优化参数
Factor \textbf{Factor} Factor 含义 Result Size(RS) \text{Result Size(RS)} Result Size(RS) Data File \text{Data File} Data File一共有多少页是需要的 Reduction Factor(RF)/Selectivity \text{Reduction Factor(RF)/Selectivity} Reduction Factor(RF)/Selectivity 有多少百分比的数据符合 SELECTION \text{SELECTION} SELECTION条件
- 原理: NPages(R)(关系大小) → 剩下的 经过WHERE + Condition筛选 RS \text{NPages(R)(关系大小)}\xrightarrow[剩下的]{经过\text{WHERE + Condition}筛选}\text{RS} NPages(R)(关系大小)经过WHERE + Condition筛选剩下的RS,即 RS = NPages(R)×RF \text{RS = NPages(R)×RF} RS = NPages(R)×RF
- 示例:整个表格有 100 \text{100} 100页,经过
WHERE+condition
筛选后只有 20 \text{20} 20页,那 RS=20, RF=0.2 \text{RS=20, RF=0.2} RS=20, RF=0.22.1.2. SELECTION Cost \textbf{2.1.2. SELECTION Cost} 2.1.2. SELECTION Cost
1️⃣非索引情况下的开销( Sorted File \text{Sorted File} Sorted File基本不考)
File Structure \textbf{File Structure} File Structure Cost \textbf{Cost} Cost(单位是 IO \textbf{IO} IO次数) 原理 Heap File \text{Heap File} Heap File NPages(R) \text{NPages(R)} NPages(R) Heap Scan \text{Heap Scan} Heap Scan读取表中每一页 Sorted File \text{Sorted File} Sorted File log 2 [NPages(R)]+RF×NPages(R) \log_{2}\text{[NPages(R)]+RF×}\text{NPages(R)} log2[NPages(R)]+RF×NPages(R) 二分查找定位 + + +所需读多少页 2️⃣索引情况下的开销
Index \textbf{Index} Index实现 Index \textbf{Index} Index存储/角色 开销 B + Tree \text{B}^{+}\text{Tree} B+Tree Primary Index \text{Primary Index} Primary Index 树高Height(Index)+1 \text{树高Height(Index)+1} 树高Height(Index)+1 Hash \text{Hash} Hash Primary Index \text{Primary Index} Primary Index ProbeCost(Index)+1 → 默认为 1.2 + 1 \text{ProbeCost(Index)+1}\xrightarrow{默认为}1.2+1 ProbeCost(Index)+1默认为1.2+1 B + Tree \text{B}^{+}\text{Tree} B+Tree Unclustered index \text{Unclustered index} Unclustered index [NPages(I)+N Tuples (R)]× ∏ i = 1 … n RF i \displaystyle\text{[NPages(I)+N\textcolor{red}{Tuples}(R)]×}\prod{i=1\ldots\text{n}}\text{RF}_{\text{i}} [NPages(I)+NTuples(R)]×∏i=1…nRFi B + Tree \text{B}^{+}\text{Tree} B+Tree Clustered index \text{Clustered index} Clustered index [NPages(I)+N Pages (R)]× ∏ i = 1 … n RF i \displaystyle\text{[NPages(I)+N\textcolor{red}{Pages}(R)]×}\prod{i=1\ldots\text{n}}\text{RF}_{\text{i}} [NPages(I)+NPages(R)]×∏i=1…nRFi Hash \text{Hash} Hash Unclustered index \text{Unclustered index} Unclustered index N Tuples (R)× ∏ i = 1 … n RF i × 2.2 \displaystyle\text{N\textcolor{red}{Tuples}(R)×}\prod{i=1\ldots\text{n}}\text{RF}_{\text{i}}\times{}2.2 NTuples(R)×∏i=1…nRFi×2.2 Hash \text{Hash} Hash Clustered index \text{Clustered index} Clustered index N Pages (R)× ∏ i = 1 … n RF i × 2.2 \displaystyle\text{N\textcolor{red}{Pages}(R)×}\prod{i=1\ldots\text{n}}\text{RF}_{\text{i}}\times{}2.2 NPages(R)×∏i=1…nRFi×2.2
- Unclustered/Clustered index \text{Unclustered/Clustered index} Unclustered/Clustered index的两部分开销为读取所需 Index+Page \text{Index+Page} Index+Page
- Unclustered index \text{Unclustered index} Unclustered index中 NTuples(R) \text{NTuples(R)} NTuples(R)是因为此时读取需要反复回到 Index File \text{Index File} Index File
- 多个 RF \text{RF} RF都需要乘起来,至于有多少个,取决于
WHERE
子句后面是什么2.1.3. SELECTION Cost \textbf{2.1.3. SELECTION Cost} 2.1.3. SELECTION Cost补充: RF Estimate \textbf{RF Estimate} RF Estimate
2.1.3.1. \textbf{2.1.3.1. } 2.1.3.1. 基本假设: Assume Uniform Distribution \textbf{Assume Uniform Distribution} Assume Uniform Distribution
![]()
2.1.3.2. \textbf{2.1.3.2. } 2.1.3.2. RF Estimate \textbf{RF Estimate} RF Estimate
1️⃣简单情况下的 RF \text{RF} RF估算
- 参数含义
参数 含义 NKeys(Col) \text{NKeys(Col)} NKeys(Col) 一列中含有多少种独一无二的值,即 DISTINCT
High(Col) \text{High(Col)} High(Col) 一列种的最高值 Low(Col) \text{Low(Col)} Low(Col) 一列种的最低值 - 离散变量的 RF \text{RF} RF估算:
WHERE Col=Value
- Col=Value {}\text{Col=Value} Col=Value对应单个值时, RF= 1 NKeys(Col) \text{RF=}\cfrac{\text{1}}{\text{NKeys(Col)}} RF=NKeys(Col)1
WHERE level=5;
- Col=Value {}\text{Col=Value} Col=Value可对应多个值,比如以下情况有 X \text{X} X个 Value \text{Value} Value,则 RF= X NKeys(Col) \text{RF=}\cfrac{\text{X}}{\text{NKeys(Col)}} RF=NKeys(Col)X
WHERE level=1 AND level=2 AND ... AND level=X;
- 离散变量的不等 → 转化 \xrightarrow{转化} 转化相等,如下 Level>7 ⇒ Level=8,9,10 ⇒ Num=3 \text{Level>7}\Rightarrow\text{Level=8,9,10}\Rightarrow\text{Num=3} Level>7⇒Level=8,9,10⇒Num=3
WHERE level>1 -- 共10个level
- 连续变量的 RF \text{RF} RF估算:
WHERE Col>Value / WHERE Col<Value
条件 RF \textbf{RF} RF等于 SQL \textbf{SQL} SQL示例 Col>Value \text{Col>Value} Col>Value High(Col) − Value High(Col) − Low(Col) \cfrac{\text{High(Col)}-\text{Value}}{\text{High(Col)}-\text{Low(Col)}} High(Col)−Low(Col)High(Col)−Value time>8
Col<Value \text{Col<Value} Col<Value Value − Low(Col) High(Col) − Low(Col) \cfrac{\text{Value}-\text{Low(Col)}}{\text{High(Col)}-\text{Low(Col)}} High(Col)−Low(Col)Value−Low(Col) time<8
2️⃣ Join \text{Join} Join操作下的 RF \text{RF} RF估算: RF = 1 Max[NKeys(JoinCol_A), NKeys(JoinCol_B)] \text{RF}=\cfrac{1}{\text{Max[NKeys(JoinCol\_A), NKeys(JoinCol\_B)]}} RF=Max[NKeys(JoinCol_A), NKeys(JoinCol_B)]1
- 示例: Student/Subject \text{Student/Subject} Student/Subject是两个 JoinCol \text{JoinCol} JoinCol
Student INNER JOIN Subject ON Student.stuid=Subject.stuid
- 核心: Primary Key \text{Primary Key} Primary Key所在列 JoinCol \text{JoinCol} JoinCol唯一值更多
- 假设
stuid
在 Student \text{Student} Student处为 PK \text{PK} PK,在 Subject \text{Subject} Subject处为 FK \text{FK} FK- 则: RF = 1 NTuples(Student) \text{RF}=\cfrac{1}{\text{NTuples(Student)}} RF=NTuples(Student)1
- 特殊情况:若 Student \text{Student} Student筛选剩下 20% \text{20\%} 20%再去 Join \text{Join} Join, RF \text{RF} RF计算无需 ×20% \text{×20\%} ×20%,永远保持原来的大小
- 原理: Subject \text{Subject} Subject中还有未被筛选的那 80% Student \text{80\% Student} 80% Student数据,即这些值是被筛掉而不是删掉
- 如下例子中:两种情况下 NLJ \text{NLJ} NLJ都有 RF = RF(Test ⋈ DTR) \text{RF}=\text{RF(Test}\bowtie{}\text{DTR)} RF=RF(Test⋈DTR)
3️⃣玄学情况:比如告诉你
age=5
但是不告诉你DISTINCT age
的数量,则默认 RF= 1 10 \text{RF=}\cfrac{1}{10} RF=1012.1.3.3. \textbf{2.1.3.3. } 2.1.3.3. RF Estimate \textbf{RF Estimate} RF Estimate优化
1️⃣存在的问题:数据不可能是平均分布的,如果数据集中向一个方向靠近,误差就会很大
![]()
2️⃣误差分析
- 方法 1: Variable-Width Histogram \text{1: Variable-Width Histogram} 1: Variable-Width Histogram(柱状图)
![]()
- 分 Bucket → \text{Bucket}\to{} Bucket→尽量每个 Bucket \text{Bucket} Bucket中数量差不多 → \to{} →再去用 Bucket \text{Bucket} Bucket估计
- 估算
age=5
(在 Bucket1 \text{Bucket1} Bucket1里): RF= 1 共 4 个Bucket × Bucket1里有 5 个值 = 1 20 \text{RF=}\cfrac{1}{共4个\text{Bucket}\times{}\text{Bucket1}里有5个值}=\cfrac{1}{20} RF=共4个Bucket×Bucket1里有5个值1=201- 方法 2: \text{2: } 2: 取样出一部分数据,得出大致的数据分布
2.1.4. SELECTION Condition: \textbf{2.1.4. SELECTION Condition: } 2.1.4. SELECTION Condition: 如何处理
WHERE
子句1️⃣原理
- 寻找最便宜的 Access Path \text{Access Path} Access Path:估算成本( IO \text{IO} IO次数)
- 利用索引匹配的谓词来计算 RF \text{RF} RF,从而达到简化效果
- Apply other predicates on-the-fly \text{Apply other predicates on-the-fly} Apply other predicates on-the-fly(实时)
- 选取成本最小的方式( Index \text{Index} Index或 File Scan \text{File Scan} File Scan)
2️⃣索引匹配的谓词: Composite Search Key \text{Composite Search Key} Composite Search Key与
WHERE + Condition
匹配
- 匹配条件:
WHERE + Condition
要包含 Composite Search Key \text{Composite Search Key} Composite Search Key的 Prefix \text{Prefix} PrefixWHERE + Condition
中AND
的顺序不重要- 匹配示例:
<a,b,c>
的 Prefix \text{Prefix} Prefix包含a/ab/abc
Composite Search Key \textbf{Composite Search Key} Composite Search Key WHERE Conditions \textbf{ WHERE Conditions} WHERE Conditions 匹配的 Prefix \textbf{Prefix} Prefix <a,b,c>
WHERE a=1
a
<a,b,c>
WHERE a=1 AND b=2
a,b
<a,b,c>
WHERE a=1 AND b=2 AND c=3
a,b,c
<a,b,c>
WHERE a=1 AND b=2 AND d=3
a,b
<a,b,c>
WHERE a=1 AND c=2
a
<a,b,c>
WHERE b=2 AND c=3
NULL
3️⃣基于索引匹配的谓词的 RF \text{RF} RF计算示例
Composite Search Key \textbf{Composite Search Key} Composite Search Key WHERE Conditions \textbf{ WHERE Conditions } WHERE Conditions Prefix \textbf{Prefix} Prefix RF \textbf{RF} RF <a,b,c>
WHERE a AND c
a
RF(a) \text{RF(a)} RF(a) <a,c>
WHERE a AND b AND c
a,c
RF(a)RF(c) \text{RF(a)RF(c)} RF(a)RF(c) 4️⃣其他注意事项: Hash不能作Range Search \text{Hash}不能作\text{Range Search} Hash不能作Range Search,见如下的综合示例中 Day \text{Day} Day是 Range Search \text{Range Search} Range Search
WHERE day<8/9/94 AND bid=5 AND sid=3
Type \textbf{Type} Type Index \textbf{Index} Index Prefix \textbf{Prefix} Prefix 是否适用于示例的查询 RF \textbf{RF} RF B + Tree \text{B}^{+}\text{Tree} B+Tree <rname,day>
N/A \text{N/A} N/A N ( 无匹配Prefix ) \text{N}(无匹配\text{Prefix}) N(无匹配Prefix) 1 \text{1} 1 B + Tree \text{B}^{+}\text{Tree} B+Tree <day,rname>
day
Y \text{Y} Y RF(day) \text{RF(day)} RF(day) B + Tree \text{B}^{+}\text{Tree} B+Tree <day,sid>
day,sid
Y \text{Y} Y RF(day)*RF(sid) \text{RF(day)*RF(sid)} RF(day)*RF(sid) Hash \text{Hash} Hash <day,rname>
day
N(Hash不能范围搜索) \text{N(Hash不能范围搜索)} N(Hash不能范围搜索) 1 \text{1} 1
- 注意一个细节:这里的 B + Tree \text{B}^{+}\text{Tree} B+Tree无论是 Cluster/Uncluster \text{Cluster/Uncluster} Cluster/Uncluster,都无关紧要
2.2. Projection \textbf{2.2. Projection} 2.2. Projection(很少考)
2.2.1. Overview \textbf{2.2.1. Overview} 2.2.1. Overview
1️⃣投影 Process \text{Process} Process的流程
场所 操作 磁盘 → \to{} →内存 读取 Data File \text{Data File} Data File中所有要处理的页到内存 内存 → \to{} →磁盘 在内存中投影(筛掉一些页),剩下页写回磁盘 磁盘(但排序时不断和内存交换) 对投影后的页进行排序 磁盘 → \to{} →内存 将投影+排序后的页,重新读回内存 2️⃣为何 Projection \text{Projection} Projection操作中需要 Sort \text{Sort} Sort
原因 解释 支持 DISTICT
操作去重时,如果数据已经排序,那么合并相邻的相同记录即可 提高 B + Tree \text{B}^{+}\text{Tree} B+Tree的加载效率 已排序的数据插入 B + Tree \text{B}^{+}\text{Tree} B+Tree时,不太可能导致大量的节点重新分配 优化 GROUP BY
操作比如按照 Age
分类,需要直到有几种Age
,那就需要排序
- 关于去重: Projection \text{Projection} Projection可能会 Removing duplicates \text{Removing duplicates} Removing duplicates,例如假设 20 \text{20} 20个员工隶属于 4 \text{4} 4个部门
SELECT Depart FROM Employees; -- 返回20个员工的20个部门(共20行) SELECT DISTINCT Depart FROM Employees;-- 返回20个员工的4种部门(共4行)
3️⃣ Projection Factor(PF) \text{Projection Factor(PF)} Projection Factor(PF)
- 是投影处理的决定因素
- 即选取的 Column \text{Column} Column占 Column \text{Column} Column总数的比,例如总共十个属性 Project \text{Project} Project两个,则 PF=0.2 \text{PF=0.2} PF=0.2
2.2.2. External Merge Sort: Divide & Conquer \textbf{2.2.2. External Merge Sort: Divide \& Conquer} 2.2.2. External Merge Sort: Divide & Conquer(分治)
1️⃣排序原理
Trem \textbf{Trem} Trem 含义 Passes \text{Passes} Passes 排序过程中数据读写硬盘的循环次数 NumPasses \text{NumPasses} NumPasses Passes \text{Passes} Passes数量(分多少次可以完成排序) Runs \text{Runs} Runs 已排序的有序数据段 N-Way \text{N-Way} N-Way 每次合并 N \text{N} N个 Runs \text{Runs} Runs
- 分割整个数据集为多个小 Runs → \text{Runs}\to{} Runs→每个 Runs \text{Runs} Runs加载进内存排好序 → \to{} →排序后的 Runs \text{Runs} Runs写回磁盘
- 将磁盘中的 Runs \text{Runs} Runs有序合并为有关大的 Runs \text{Runs} Runs
😭采用 External \text{External} External排序的原因在于内存不够大,需要轮换进入内存2️⃣ 2-Way External Merge Sort \text{2-Way External Merge Sort} 2-Way External Merge Sort示例
![]()
Trem \textbf{Trem} Trem Value \textbf{Value} Value E.g. \textbf{E.g.} E.g. NumPasses \text{NumPasses} NumPasses 1 + ⌈ log 2 [ NPages(R)] ⌉ 1 + \lceil \log_2 [\text{NPages(R)]}\rceil 1+⌈log2[NPages(R)]⌉ 1 + ⌈ log 2 7 ⌉ = 1 + ⌈ 2.3 ⌉ = 3 1 + \lceil \log_2 \text{7}\rceil=1 + \lceil 2.3\rceil=3 1+⌈log27⌉=1+⌈2.3⌉=3 Total IO Cost \text{Total IO Cost} Total IO Cost 2×NPages(R)×NumPasses \text{2×NPages(R)×NumPasses} 2×NPages(R)×NumPasses 2 × 7 × 3 = 42 \text{2}\times{}7\times{}3=42 2×7×3=42 3️⃣基于外归并排序的 Projection \text{Projection} Projection开销:等于以下每列之和(记下即可)
步骤 操作 Cost \textbf{Cost} Cost值 读取 读取表( Data File \text{Data File} Data File)中所有要排序的页到内存 NPages(R) \text{NPages(R)} NPages(R) 投影 投影筛掉一些页,将投影后的页写回磁盘 NPages(R)×PF \text{NPages(R)×PF} NPages(R)×PF 排序 对投影后的数据排序(每轮都要读写一次) NPages(R)×PF×2×NumPasses \text{NPages(R)×PF×2×NumPasses} NPages(R)×PF×2×NumPasses 写回 将投影+排序后的页,重新读回内存 NPages(R)×PF \text{NPages(R)×PF} NPages(R)×PF 2.2.3. External Hashing \textbf{2.2.3. External Hashing} 2.2.3. External Hashing
1️⃣原理
- 第一轮:内存有限 → 1 \to{}1 →1页用作缓存 + + +剩下 B-1 \text{B-1} B-1页当作 Bucket \text{Bucket} Bucket
![]()
- 硬盘的每页 → 缓存 读取 \xrightarrow[缓存]{读取} 读取缓存内存中的缓存页
- 缓存页的数据 → Hash H1 \xrightarrow[\text{Hash}]{\text{H1}} H1Hash剩下的 B-1 \text{B-1} B-1个 Bucket \text{Bucket} Bucket中
- 满了的 Bucket → 读出 \text{Bucket}\xrightarrow{读出} Bucket读出硬盘中若干页(由于不够细分一个 Bucket \text{Bucket} Bucket可能输出多页)
- 最终硬盘中有 Bucket1→Bucket(B-1) \text{Bucket1→Bucket(B-1)} Bucket1→Bucket(B-1)共 B-1 \text{B-1} B-1个 Bucket \text{Bucket} Bucket,每个 Bucket \text{Bucket} Bucket中有若干页
- 第二轮:试图用有限内存充分分类
![]()
- 硬盘 Bucket1 \text{Bucket1} Bucket1的每页/ Bucket2 \text{Bucket2} Bucket2的每页/…/ Bucket(B-1) \text{Bucket(B-1)} Bucket(B-1)的每页 → 缓存 读取 \xrightarrow[缓存]{读取} 读取缓存内存中的缓存页
- 缓存页的数据 → Hash H2(注意是一个新的Hash函数) \xrightarrow[\text{Hash}]{\text{H2(注意是一个新的Hash函数)}} H2(注意是一个新的Hash函数)Hash新的 B-1 \text{B-1} B-1个 Bucket \text{Bucket} Bucket中
- 以此类推…
- 最后一轮:所有的数据得到了充分的细分,以至于最后每个 Bucket \text{Bucket} Bucket中只包含一个数据
2️⃣基于 External Hashing \text{External Hashing} External Hashing的 Projection \text{Projection} Projection开销
步骤 操作 Cost \textbf{Cost} Cost值 读取 读取表( Data File \text{Data File} Data File)中所有要排序的页到内存 NPages(R) \text{NPages(R)} NPages(R) 投影 投影筛掉一些页,将投影后的页写回磁盘 NPages(R)×PF \text{NPages(R)×PF} NPages(R)×PF 写回 将投影后的页,重新读回内存 NPages(R)×PF \text{NPages(R)×PF} NPages(R)×PF 2.3. Join Algorithms \textbf{2.3. Join Algorithms} 2.3. Join Algorithms
2.3.1. Nested Loops Join \textbf{2.3.1. Nested Loops Join} 2.3.1. Nested Loops Join
0️⃣关于 Inner/Outer Table \text{Inner/Outer Table} Inner/Outer Table
![]()
- 如果可以选 → \text{→} →一般设定 Outer \text{Outer} Outer为 NPages \text{NPages} NPages较小的表
- 默认左边是 Outer \text{Outer} Outer右边是 Inner \text{Inner} Inner
1️⃣原理
Type \textbf{Type} Type Outer Table \textbf{Outer Table} Outer Table的每___ → 扫描 \xrightarrow{扫描} 扫描 Inner Table \textbf{Inner Table} Inner Table的每___ Simple \text{Simple} Simple 行 → 扫描 \xrightarrow{扫描} 扫描 页 Page-Oriented \text{Page-Oriented} Page-Oriented 页 → 扫描 \xrightarrow{扫描} 扫描 页 Block \text{Block} Block 块(包含多个页) → 扫描 \xrightarrow{扫描} 扫描 页 2️⃣示意图与工作流程
- Simple Nested Loops Join \text{Simple Nested Loops Join} Simple Nested Loops Join
![]()
Outer Page \textbf{Outer Page} Outer Page Inner Page \textbf{Inner Page} Inner Page Outer Page 1, Tuple 1 \text{Outer Page 1, Tuple 1} Outer Page 1, Tuple 1 Inner Page 1 \text{Inner Page 1} Inner Page 1 Outer Page 1, Tuple 1 \text{Outer Page 1, Tuple 1} Outer Page 1, Tuple 1 Inner Page 2 \text{Inner Page 2} Inner Page 2 Outer Page 1, Tuple 1 \text{Outer Page 1, Tuple 1} Outer Page 1, Tuple 1 Inner Page 3 \text{Inner Page 3} Inner Page 3 Outer Page 1, Tuple 1 \text{Outer Page 1, Tuple 1} Outer Page 1, Tuple 1 Inner Page 4 \text{Inner Page 4} Inner Page 4 Outer Page 1, Tuple 2 \text{Outer Page 1, Tuple 2} Outer Page 1, Tuple 2 Inner Page 1 \text{Inner Page 1} Inner Page 1 … … Outer Page 10, Tuple N \text{Outer Page 10, Tuple N} Outer Page 10, Tuple N Inner Page 4 \text{Inner Page 4} Inner Page 4 - Page-Oriented Nested Loops Join: \text{Page-Oriented Nested Loops Join: } Page-Oriented Nested Loops Join: 内存不多不少,就占用三页
![]()
Outer Page \textbf{Outer Page} Outer Page Inner Page \textbf{Inner Page} Inner Page Outer Page 1 \text{Outer Page 1} Outer Page 1 Inner Page 1 \text{Inner Page 1} Inner Page 1 Outer Page 1 \text{Outer Page 1} Outer Page 1 Inner Page 2 \text{Inner Page 2} Inner Page 2 Outer Page 1 \text{Outer Page 1} Outer Page 1 Inner Page 3 \text{Inner Page 3} Inner Page 3 Outer Page 1 \text{Outer Page 1} Outer Page 1 Inner Page 4 \text{Inner Page 4} Inner Page 4 Outer Page 2 \text{Outer Page 2} Outer Page 2 Inner Page 1 \text{Inner Page 1} Inner Page 1 … … Outer Page 10 \text{Outer Page 10} Outer Page 10 Inner Page 4 \text{Inner Page 4} Inner Page 4 - Block Nested Loops Join \text{Block Nested Loops Join} Block Nested Loops Join
![]()
Outer Page \textbf{Outer Page} Outer Page Inner Page \textbf{Inner Page} Inner Page Outer Page 1 + Outer Page 2(Block1) \text{Outer Page 1 + Outer Page 2(Block1)} Outer Page 1 + Outer Page 2(Block1) Inner Page 1 \text{Inner Page 1} Inner Page 1 Outer Page 1 + Outer Page 2(Block1) \text{Outer Page 1 + Outer Page 2(Block1)} Outer Page 1 + Outer Page 2(Block1) Inner Page 2 \text{Inner Page 2} Inner Page 2 Outer Page 1 + Outer Page 2(Block1) \text{Outer Page 1 + Outer Page 2(Block1)} Outer Page 1 + Outer Page 2(Block1) Inner Page 3 \text{Inner Page 3} Inner Page 3 Outer Page 1 + Outer Page 2(Block1) \text{Outer Page 1 + Outer Page 2(Block1)} Outer Page 1 + Outer Page 2(Block1) Inner Page 4 \text{Inner Page 4} Inner Page 4 Outer Page 3 + Outer Page 4(Block2) \text{Outer Page 3 + Outer Page 4(Block2)} Outer Page 3 + Outer Page 4(Block2) Inner Page 1 \text{Inner Page 1} Inner Page 1 … … Outer Page 9 + Outer Page 10(Block5) \text{Outer Page 9 + Outer Page 10(Block5)} Outer Page 9 + Outer Page 10(Block5) Inner Page 4 \text{Inner Page 4} Inner Page 4 3️⃣开销:外表扫描成本 + + +内表扫描成本
Type \textbf{Type} Type 外表扫描成本 内表扫描成本 Simple \text{Simple} Simple NPages(Outer) \text{NPages(Outer)} NPages(Outer) NTuples(Outer)×NPages(Inner) \text{NTuples(Outer)}\text{×}\text{NPages(Inner)} NTuples(Outer)×NPages(Inner) Page-Oriented \text{Page-Oriented} Page-Oriented NPages(Outer) \text{NPages(Outer)} NPages(Outer) NPagees(Outer)×NPages(Inner) \text{NPagees(Outer)}\text{×}\text{NPages(Inner)} NPagees(Outer)×NPages(Inner) Block \text{Block} Block NPages(Outer) \text{NPages(Outer)} NPages(Outer) NBlocks(Outer)×NPages(Inner) \text{NBlocks(Outer)}\text{×}\text{NPages(Inner)} NBlocks(Outer)×NPages(Inner)
- 外表所有页都要被读一次,所以成本为 NPages(Outer) \text{NPages(Outer)} NPages(Outer)
- NBlocks(Outer)=NPages(Outer)/(Memory-2) \text{NBlocks(Outer)=NPages(Outer)/(Memory-2)} NBlocks(Outer)=NPages(Outer)/(Memory-2)
- Memory-2 \text{Memory-2} Memory-2为 RAM \text{RAM} RAM中 Outer Pages \text{Outer Pages} Outer Pages的数量,即一次从 Outer Table \text{Outer Table} Outer Table读入多少页
- 当 NBlocks(Outer) \text{NBlocks(Outer)} NBlocks(Outer)不为整数时,要向上取整
- 分配 RAM(Memory) \text{RAM(Memory)} RAM(Memory)越多,就会 NBlocks(Outer) \text{NBlocks(Outer)} NBlocks(Outer)越低,从而开销越小
4️⃣开销的最优解: { RAM可把Outer Table全放进 → 大小至少为NPages(Outer)+2 最优解 → Cost=NPages(Outer)+NPages(Inner) \begin{cases}\text{RAM}可把\text{Outer Table}全放进\to{}大小至少为\text{NPages(Outer)+2}\\\\最优解\to{}\text{Cost=NPages(Outer)+NPages(Inner)}\end{cases} ⎩ ⎨ ⎧RAM可把Outer Table全放进→大小至少为NPages(Outer)+2最优解→Cost=NPages(Outer)+NPages(Inner)
- 这其实不仅是 NLJ \text{NLJ} NLJ的最优解,也是全局最优解,因为这个解刚刚刚好是把两个表读进来
- 在最优解不变的情况下为了节省内存 → \to{} → Outer \text{Outer} Outer表要尽量小(小表)
2.3.2. Sort-Merge Join \textbf{2.3.2. Sort-Merge Join} 2.3.2. Sort-Merge Join
1️⃣原理:以
A INNER JOIN B ON A.age = B.number
为例
- 第一步:按照 Join Column \text{Join Column} Join Column(此处分别为
age
和number
)排序
- A \text{A} A表按照
age
排序, B \text{B} B表按照numver
排序- 目的在于:让相同/相关的值物理上靠近,可以顺序访问 → \text{→} →加快合并
- 第二步: AB \text{AB} AB两表再进行 Join \text{Join} Join操作
- Output \text{Output} Output: 一定是按照 Join Column \text{Join Column} Join Column排好序的
2️⃣排序的成本为 0 \text{0} 0的情况: Clustered + \text{Clustered + } Clustered + B + Tree + \text{B}^{+}\text{Tree + } B+Tree + Join Column \text{Join Column} Join Column是索引依据的 Prefix \text{Prefix} Prefix
A INNER JOIN B ON A.age = B.number
索引根据( Composite Search Key \textbf{Composite Search Key} Composite Search Key) 索引类型 表 A \textbf{A} A无需排序 <A.age>
Clustered B + \text{Clustered B}^{+} Clustered B+树 ✔️ <A.name>
Clustered B + \text{Clustered B}^{+} Clustered B+树 ❌ <A.age, A.name>
Clustered B + \text{Clustered B}^{+} Clustered B+树 ✔️ <A.name, A.age>
Clustered B + \text{Clustered B}^{+} Clustered B+树 ❌ 3️⃣ Cost = Sort(Outer)+ Sort(Inner)+ NPages(Outer)+ NPages(Inner) \text{Cost = Sort(Outer)+ Sort(Inner)+ NPages(Outer)+ NPages(Inner)} Cost = Sort(Outer)+ Sort(Inner)+ NPages(Outer)+ NPages(Inner)
Cost \textbf{Cost} Cost 成因 Sort(Outer) \text{Sort(Outer)} Sort(Outer) 将外表所有元素排序 Sort(Inner) \text{Sort(Inner)} Sort(Inner) 将内表所有元素排序 NPages(Outer) \text{NPages(Outer)} NPages(Outer) 遍历外表的所有页以执行合并操作 NPages(Inner) \text{NPages(Inner)} NPages(Inner) 遍历内表的所有页以执行合并操作
- Sort(X)= { 2×NumPasses×NPages(X) 0 (Clustered B + Index on Join Column Prefix) \text{Sort(X)=}\begin{cases} \text{2×NumPasses×NPages(X)}\\\\0\text{ (Clustered B}^{+}\text{ Index on Join Column Prefix)}\end{cases} Sort(X)=⎩ ⎨ ⎧2×NumPasses×NPages(X)0 (Clustered B+ Index on Join Column Prefix)
2.3.3. Hash Join \textbf{2.3.3. Hash Join} 2.3.3. Hash Join(一般情况开销最小)
1️⃣原理:以按照
A.id = B.id
Join \text{Join} Join为例,其中id
为连接键
- Build Phase \text{Build Phase} Build Phase:
A.id
→ Hash H1哈希函数 \xrightarrow[\text{Hash}]{\text{H1哈希函数}} H1哈希函数HashH1(A.id)
构建 Hash \text{Hash} Hash表 A \text{A} A,通常是选小表( Inner \text{Inner} Inner)来构建 Hash \text{Hash} Hash表- Probe Phase \text{Probe Phase} Probe Phase(探测阶段):
B.id
→ Hash H1哈希函数 \xrightarrow[\text{Hash}]{\text{H1哈希函数}} H1哈希函数HashH1(B.id)
得到 Hash \text{Hash} Hash值 B(n) \text{B(n)} B(n)- 在 Hash \text{Hash} Hash表 A \text{A} A中检索 Hash \text{Hash} Hash值 B(n) \text{B(n)} B(n)则匹配,那么匹配的行进行 Join \text{Join} Join
2️⃣ Cost=3×NPages(Outer)+3×NPages(Inner) \text{Cost=3×NPages(Outer)+3×NPages(Inner)} Cost=3×NPages(Outer)+3×NPages(Inner)
2.3.4. Join Pipeline \textbf{2.3.4. Join Pipeline} 2.3.4. Join Pipeline:减少 Cost \textbf{Cost} Cost的一种方式
2.3.4.1. \textbf{2.3.4.1. } 2.3.4.1. 基本概念
1️⃣ Left-Deep Join Tree \text{Left-Deep Join Tree} Left-Deep Join Tree:
- 多重 Join: \text{Join: } Join: 考虑 Join \text{Join} Join的顺序和类型
- Left-Deep Join Tree: \text{Left-Deep Join Tree: } Left-Deep Join Tree: 是最优 Join \text{Join} Join顺序(快速增长率),方便在 RAM \text{RAM} RAM中 Pipeline \text{Pipeline} Pipeline并行处理
- 示意图:默认左边的为 Outer Table \text{Outer Table} Outer Table
2️⃣ Pipeline \text{Pipeline} Pipeline
- 概念:
- Direct “streaming” in memory \text{Direct “streaming” in memory} Direct “streaming” in memory
- Of the output of one operation as the input of another operation \text{Of the output of one operation as the input of another operation} Of the output of one operation as the input of another operation
- Without writing output to disk \text{Without writing output to disk} Without writing output to disk
- 基于 Left-Deep Join Tree \text{Left-Deep Join Tree} Left-Deep Join Tree的 Pipeline \text{Pipeline} Pipeline示例
![]()
Stage \textbf{Stage} Stage Outer Page \textbf{Outer Page} Outer Page Inner Page \textbf{Inner Page} Inner Page Output Page \textbf{Output Page} Output Page 1 \text{1} 1 A \text{A} A B \text{B} B A ⋈ B → Move to Outer Page \text{A}\bowtie{}\text{B}\xrightarrow{\text{Move to}}\text{Outer Page} A⋈BMove toOuter Page 2 \text{2} 2 A ⋈ B \text{A}\bowtie{}\text{B} A⋈B C \text{C} C A ⋈ B ⋈ C → Move to Outer Page \text{A}\bowtie{}\text{B}\bowtie{}\text{C}\xrightarrow{\text{Move to}}\text{Outer Page} A⋈B⋈CMove toOuter Page 3 \text{3} 3 A ⋈ B ⋈ C \text{A}\bowtie{}\text{B}\bowtie{}\text{C} A⋈B⋈C D \text{D} D A ⋈ B ⋈ C ⋈ D → Finally Output \text{A}\bowtie{}\text{B}\bowtie{}\text{C}\bowtie{}\text{D}\xrightarrow{\text{Finally}}\text{Output} A⋈B⋈C⋈DFinallyOutput 2.3.4.2. Pipeline \textbf{2.3.4.2. Pipeline} 2.3.4.2. Pipeline简化计算
1️⃣简化计算 1 \text{1} 1: Heap Scan \text{Heap Scan} Heap Scan不算 Cost + \text{Cost + } Cost + 上层 Join \text{Join} Join也不减其 Pipelining \text{Pipelining} Pipelining
- 原因: Heap Scan \text{Heap Scan} Heap Scan扫描的成本,和其对应减去的 Pipelining \text{Pipelining} Pipelining,都等于对应表大小,互相抵消
- 示例 1: Join \text{1: Join} 1: Join(顶层)开销 =NLJ \text{=NLJ} =NLJ开销
![]()
- 原本开销: NLJ+ \text{NLJ+} NLJ+两个 Heap Scan \text{Heap Scan} Heap Scan
- Pipeline: \text{Pipeline: } Pipeline: 减去两个 Heap Scan \text{Heap Scan} Heap Scan
- 示例 2: Join \text{2: Join} 2: Join(顶层)开销 =SMJ \text{=SMJ} =SMJ开销 − RSA(Result Size A) -\text{RSA(Result Size A)} −RSA(Result Size A),注意 RSA \text{RSA} RSA是以 Pages \text{Pages} Pages为单位
![]()
- 原本开销: SMJ+Heap Scan \text{SMJ+}\text{Heap Scan} SMJ+Heap Scan
- Pipeline: \text{Pipeline: } Pipeline: 减去 Heap Scan \text{Heap Scan} Heap Scan,减去 RSA \text{RSA} RSA
2️⃣简化计算 2: Heap Scan \text{2: Heap Scan} 2: Heap Scan自动忽略所有 Index \text{Index} Index,来看一个不忽略 Index \text{Index} Index的示例
![]()
- 原本开销: HJ \text{HJ} HJ开销 +Index Scan(Test) \text{+Index Scan(Test)} +Index Scan(Test)开销 (具体怎么算要看采用哪种 Index \text{Index} Index )
- Pipeline: \text{Pipeline: } Pipeline: 减去 NPages(Test) \text{NPages(Test)} NPages(Test),减去 RSA \text{RSA} RSA
3. Query Optimization \textbf{3. Query Optimization} 3. Query Optimization
3.1. Relational Algebra Equivalences \textbf{3.1. Relational Algebra Equivalences} 3.1. Relational Algebra Equivalences
3.1.1. Selection/Projection Equivalences \textbf{3.1.1. Selection/Projection Equivalences} 3.1.1. Selection/Projection Equivalences
1️⃣概览
定律 公式 含义 Selection Cascade \small\text{Selection Cascade} Selection Cascade σ c 1 ∧ ⋯ ∧ c n ( R ) ≡ σ c 1 ( … ( σ c n ( R ) ) ) \sigma_{\text{c}_1 \wedge \cdots \wedge \text{c}_\text{n}}(\text{R})\equiv \sigma_{\text{c}_1}\left(\ldots\left(\sigma_{\text{c}_\text{n}}(\text{R})\right)\right) σc1∧⋯∧cn(R)≡σc1(…(σcn(R))) 多条件筛行 = = =每个都筛下 Selection Commute \small\text{Selection Commute} Selection Commute σ c 1 ( σ c 2 ( R ) ) ≡ σ c 2 ( σ c 1 ( R ) ) ≡ σ c 1 ∧ c 2 ( R ) \sigma_{\text{c}_\text{1}}\left(\sigma_{\text{c}_2}(\text{R})\right)\equiv \sigma_{\text{c}_2}\left(\sigma_{\text{c}_\text{1}}(\text{R})\right)\equiv{}\sigma_{\text{c}_\text{1} \wedge \text{c}_\text{2}}(\text{R}) σc1(σc2(R))≡σc2(σc1(R))≡σc1∧c2(R) 先用哪个条件筛选无所谓 Projection Cascade \small\text{Projection Cascade} Projection Cascade π a 1 ( R ) ≡ π a 1 ( … ( π a n ( R ) ) ) \pi_{\text{a}_\text{1}}(R) \equiv \pi_{\text{a}_\text{1}}\left(\ldots\left(\pi_{\text{a}_n}(R)\right)\right) πa1(R)≡πa1(…(πan(R))) 随便投,最后一下说了算 ⚠️注意事项: Projection Cascade \text{Projection Cascade} Projection Cascade中,假设 A \text{A} A是 Attributes \text{Attributes} Attributes的集合 ( A= { a 1 , a 2 , . . . , a n } ) \left(\text{A=}\{\text{a}_\text{1},\text{a}_2,...,\text{a}_\text{n}\}\right) (A={a1,a2,...,an})
- 在 π A 1 ( … ( π A n ( R ) ) ) \pi{_{\text{A}_\text{1}}}(\ldots(\pi{_{\text{A}}}_\text{n}(\text{R}))) πA1(…(πAn(R)))中,必须满足 A n ⊆ A m ( n<m ) \text{A}_{\text{n}}\subseteq{}\text{A}_{\text{m}}(\text{n<m}) An⊆Am(n<m)
- 以 π A 1 ( π A 2 ( R ) ) \pi{_{\text{A}_\text{1}}}(\pi{_{\text{A}}}_\text{2}(\text{R})) πA1(πA2(R))为例
A 1 {\textbf{A}_\textbf{1}} A1 A 2 {\textbf{A}_\textbf{2}} A2 是否适用 Projection Cascade \textbf{Projection Cascade} Projection Cascade { a 1 , a 2 } \{\text{a}_\text{1},\text{a}_2\} {a1,a2} { a 1 , a 2 } \{\text{a}_\text{1},\text{a}_2\} {a1,a2} A 1 ⊆ A 2 → π a 1 , a 2 ( π a 1 , a 2 ( R ) ) = π a 1 , a 2 ( R ) \text{A}_{\text{1}}\subseteq{}\text{A}_{\text{2}}\to{}\pi{_{\text{a}_\text{1},\text{a}_\text{2}}}(\pi{_{\text{a}_\text{1},\text{a}_\text{2}}}(\text{R}))=\pi{_{\text{a}_\text{1},\text{a}_\text{2}}}(\text{R}) A1⊆A2→πa1,a2(πa1,a2(R))=πa1,a2(R) { a 1 , a 2 } \{\text{a}_\text{1},\text{a}_2\} {a1,a2} { a 1 , a 2 , a 3 , a 4 } \{\text{a}_\text{1},\text{a}_2,\text{a}_\text{3},\text{a}_4\} {a1,a2,a3,a4} A 1 ⊆ A 2 → π a 1 , a 2 ( π a 1 , a 2 , a 3 , a 4 ( R ) ) = π a 1 , a 2 ( R ) \text{A}_{\text{1}}\subseteq{}\text{A}_{\text{2}}\to{}\pi{_{\text{a}_\text{1},\text{a}_\text{2}}}(\pi{_{\text{a}_\text{1},\text{a}_\text{2},\text{a}_\text{3},\text{a}_\text{4}}}(\text{R}))=\pi{_{\text{a}_\text{1},\text{a}_\text{2}}}(\text{R}) A1⊆A2→πa1,a2(πa1,a2,a3,a4(R))=πa1,a2(R) { a 1 , a 4 } \{\text{a}_\text{1},\text{a}_4\} {a1,a4} { a 1 , a 2 , a 3 } \{\text{a}_\text{1},\text{a}_2,\text{a}_\text{3}\} {a1,a2,a3} A 1 ⊈ A 2 \text{A}_{\text{1}} \not\subseteq{}\text{A}_{\text{2}} A1⊆A2不适用 2️⃣示例:关于第 3 \text{3} 3个,注意要让外部操作( π / σ \pi{}\text{/}\sigma π/σ)的 Column ⊆ \text{Column}\subseteq Column⊆ 里面操作( π \pi π)的 Column \text{Column} Column
- σ age<18 ∧ rating > 5 (Sailors) ⇔ { σ age < 18 ( σ rating > 5 (Sailors) ) σ rating > 5 ( σ age < 18 (Sailors ) ) \large{}\sigma_{\text{age<18 }\wedge \text{ rating}>5}\text{(Sailors)}\Leftrightarrow\begin{cases} \sigma_{\text{age}<18}\left(\sigma_{\text{rating}>5}\text{(Sailors)}\right)\\\\ \left.\sigma_{\text{rating}>5}\left(\sigma_{\text{age}<18}\text{(Sailors}\right)\right)\end{cases} σage<18 ∧ rating>5(Sailors)⇔⎩ ⎨ ⎧σage<18(σrating>5(Sailors))σrating>5(σage<18(Sailors))
- π age,rating ( Sailors ) ⇔ π age,rating ( π age,rating,sid (Sailors) ) \large\pi_{\text{age,rating}}(\text{Sailors})\Leftrightarrow \pi_{\text{age,rating}}\left(\pi_{\text{age,rating,sid}}\text{(Sailors)}\right) πage,rating(Sailors)⇔πage,rating(πage,rating,sid(Sailors))
- { π age,sid ( σ age<18 ∧ rating > 5 (Sailors)) ⇕ { 错误的: σ age<18 ∧ rating > 5 ( π age, sid ( Sailors ) ) 正确的: σ age<18 ∧ rating > 5 ( π age, sid, rating ( Sailors ) ) \large\begin{cases}\begin{aligned}& \pi_{\text{age,sid}}( \sigma_{\text{age<18 }\wedge \text{ rating}>5}\text{(Sailors))} &\\& \Updownarrow &\\& \begin{cases}\text{错误的: }\sigma_{\text{age<18 } \wedge \text{ rating}>5}\left(\pi_{\text{age, sid}}(\text{Sailors})\right)\\\\\text{正确的: }\sigma_{\text{age<18 } \wedge \text{ rating}>5}\left(\pi_{\text{age, sid, rating}}(\text{Sailors})\right)\end{cases}&\\\end{aligned}\end{cases} ⎩ ⎨ ⎧πage,sid(σage<18 ∧ rating>5(Sailors))⇕⎩ ⎨ ⎧错误的: σage<18 ∧ rating>5(πage, sid(Sailors))正确的: σage<18 ∧ rating>5(πage, sid, rating(Sailors))
3.1.2. Join Equivalences \textbf{3.1.2. Join Equivalences} 3.1.2. Join Equivalences
定律 公式 含义 Join Associative \text{Join Associative} Join Associative R ⋈ ( S ⋈ T ) ≡ ( R ⋈ S ) ⋈ T \text{R}\bowtie(\text{S} \bowtie \text{T})\equiv(\text{R}\bowtie \text{S})\bowtie \text{T} \quad R⋈(S⋈T)≡(R⋈S)⋈T Join \text{Join} Join顺序不影响结果(三个) Join Commutative \text{Join Commutative} Join Commutative ( R ⋈ S ) ≡ ( S ⋈ R ) (\text{R}\bowtie \text{S})\equiv(\text{S}\bowtie \text{R})\quad (R⋈S)≡(S⋈R) Join \text{Join} Join顺序不影响结果(两个) 3.2. \text{3.2. } 3.2. Query Optimization \textbf{Query Optimization} Query Optimization实例
1️⃣ Cross Product \text{Cross Product} Cross Product优化:转化为有条件的 Join \text{Join} Join
σ Sailors.sid=Reserves.sid ( Sailors×Reserves ) ⇕ Sailors ⋈ Sailors.sid=Reserves.sid Reserves ⇕ Sailors ⋈ Reserves \large{}\begin{aligned}& \sigma_{\text{Sailors.sid=Reserves.sid}}(\text{Sailors} \text{×}\text{Reserves}) &\\& \Updownarrow &\\& \text{Sailors} \bowtie_{\text{Sailors.sid=Reserves.sid}} \text{Reserves} &\\& \Updownarrow &\\& \text{Sailors} \bowtie \text{Reserves }\end{aligned} σSailors.sid=Reserves.sid(Sailors×Reserves)⇕Sailors⋈Sailors.sid=Reserves.sidReserves⇕Sailors⋈Reserves
2️⃣ Predicate Pushdown: \text{Predicate Pushdown: } Predicate Pushdown: 先给表格 σ \sigma{} σ筛选 → \to{} →降低数据量/减少计算
σ Sailors.age<18 ( Sailors ⋈ Sailors.sid=Reserves.sid Reserves ) ⇕ 想办法把Join左边的Sailors表格变小 ( σ Sailors.age<18 ( Sailors ) ) ⋈ Sailors.sid=Reserves.sid Reserves \large{}\begin{aligned} & \sigma_{\text{Sailors.age<18}}(\text{Sailors} \bowtie_{\text{Sailors.sid=Reserves.sid}} \text{Reserves}) &\\ & \Bigg\Updownarrow {\small{想办法把\text{Join}左边的\text{Sailors}表格变小}} &\\ &(\sigma_{\text{Sailors.age<18}}(\text{Sailors})) \bowtie_{\text{Sailors.sid=Reserves.sid}} \text{Reserves} & \end{aligned} σSailors.age<18(Sailors⋈Sailors.sid=Reserves.sidReserves)⇓ ⇑想办法把Join左边的Sailors表格变小(σSailors.age<18(Sailors))⋈Sailors.sid=Reserves.sidReserves
3️⃣ Projection Pushdown: \text{Projection Pushdown: } Projection Pushdown: 先给表格 π \pi{} π筛选 → \to{} →降低数据量/减少计算
π Sailors.sname ( Sailors ⋈ Sailors.sid=Reserves.sid Reserves ) ⇕ 想办法把Join左右的Sailors/Reserves表格变小 → 把两张表中用得到的Column都Project出来 π Sailors.sname ( π sname, sid ( Sailors ) ⋈ Sailors.sid=Reserves.sid π sid ( Reserves ) ) \large{}\begin{aligned} & \pi_{\text{Sailors.sname}}(\text{Sailors} \bowtie_{\text{Sailors.sid=Reserves.sid}} \text{Reserves})&\\ & \Bigg\Updownarrow {\small{想办法把\text{Join}左右的\text{Sailors/Reserves}表格变小}\to{}\text{把两张表中用得到的Column都Project出来}} &\\ &\pi_{\text{Sailors.sname}}(\pi_{\text{sname, sid}} (\text{Sailors}) \bowtie_{\text{Sailors.sid=Reserves.sid}} \pi_{\text{sid}}(\text{Reserves})) & \end{aligned} πSailors.sname(Sailors⋈Sailors.sid=Reserves.sidReserves)⇓ ⇑想办法把Join左右的Sailors/Reserves表格变小→把两张表中用得到的Column都Project出来πSailors.sname(πsname, sid(Sailors)⋈Sailors.sid=Reserves.sidπsid(Reserves))
4. Query Cost \textbf{4. Query Cost} 4. Query Cost计算题
4.1. Of One Table \textbf{4.1. Of One Table} 4.1. Of One Table
1️⃣原理:考虑各种可能的 Query \text{Query} Query途径( Scan/Index \text{Scan/Index} Scan/Index) → \to{} →选择开销最小的一个
- 注意永远别忘了算一下 Heap Scan \text{Heap Scan} Heap Scan,有时候算来算去反倒 Heap Scan \text{Heap Scan} Heap Scan开销最小
- 除去 Heap Scan \text{Heap Scan} Heap Scan其它所有类型的开销,都需要先估计 RF ↓ \text{RF }\downarrow{} RF ↓
2️⃣ RF \text{RF} RF估算汇总
条件 RF \text{RF} RF等于 补充说明 Col=Value {}\text{Col=Value} Col=Value Num NKeys(Col) \cfrac{\text{Num}}{\text{NKeys(Col)}} NKeys(Col)Num 处理离散变量 Col>Value \text{Col>Value} Col>Value High(Col) − Value High(Col) − Low(Col) \cfrac{\text{High(Col)}-\text{Value}}{\text{High(Col)}-\text{Low(Col)}} High(Col)−Low(Col)High(Col)−Value 变量必须连续 Col<Value \text{Col<Value} Col<Value Value − Low(Col) High(Col) − Low(Col) \cfrac{\text{Value}-\text{Low(Col)}}{\text{High(Col)}-\text{Low(Col)}} High(Col)−Low(Col)Value−Low(Col) 变量必须连续 Join \text{Join} Join操作 1 Max[NKeys(Col_A), NKeys(Col_B)] \cfrac{1}{\text{Max[NKeys(Col\_A), NKeys(Col\_B)]}} Max[NKeys(Col_A), NKeys(Col_B)]1 主键所在列 JoinCol \text{JoinCol} JoinCol唯一值更多 无任何已知条件 RF= 1 10 \text{RF=}\cfrac{1}{10} RF=101 默认 3️⃣基于
WHERE + Condition
的 RF \text{RF} RF选取WHERE day<8/9/94 AND bid=5 AND sid=3
Type \textbf{Type} Type Index \textbf{Index} Index 匹配的 Prefix 匹配的\textbf{Prefix} 匹配的Prefix 是否适用于示例的查询 RF \textbf{RF} RF B + Tree \text{B}^{+}\text{Tree} B+Tree <rname,day>
N/A \text{N/A} N/A N ( 无匹配Prefix ) \text{N}(无匹配\text{Prefix}) N(无匹配Prefix) 1 \text{1} 1 B + Tree \text{B}^{+}\text{Tree} B+Tree <day,rname>
day
Y \text{Y} Y RF(day) \text{RF(day)} RF(day) B + Tree \text{B}^{+}\text{Tree} B+Tree <day,sid>
day,sid
Y \text{Y} Y RF(day)*RF(sid) \text{RF(day)*RF(sid)} RF(day)*RF(sid) Hash \text{Hash} Hash <day,rname>
day
N(Hash不能范围搜索) \text{N(Hash不能范围搜索)} N(Hash不能范围搜索) 1 \text{1} 1
- 索引匹配的谓词:
WHERE + Condition
必须要包含 Composite Search Key \text{Composite Search Key} Composite Search Key的 Prefix \text{Prefix} Prefix- Hash \text{Hash} Hash不能作 Range Search \text{Range Search} Range Search
4️⃣基于 RF \text{RF} RF的 Scan Cost Formula \text{Scan Cost Formula} Scan Cost Formula汇总
Structure \textbf{Structure} Structure Index \textbf{Index} Index实现 Index \textbf{Index} Index存储/角色 开销 Heap File \text{Heap File} Heap File N/A \text{N/A} N/A N/A \text{N/A} N/A NPages(R) \text{NPages(R)} NPages(R) Sorted File \text{Sorted File} Sorted File N/A \text{N/A} N/A N/A \text{N/A} N/A log 2 [NPages(R)]+RF×NPages(R) \log_{2}\text{[NPages(R)]+RF×}\text{NPages(R)} log2[NPages(R)]+RF×NPages(R) index File \text{index File} index File B + Tree \text{B}^{+}\text{Tree} B+Tree Primary \text{Primary} Primary 树高Height(Index)+1 \text{树高Height(Index)+1} 树高Height(Index)+1 index File \text{index File} index File Hash \text{Hash} Hash Primary \text{Primary} Primary ProbeCost(Index)+1 → 默认为 1.2 + 1 \displaystyle\text{ProbeCost(Index)+1}\xrightarrow{默认为}1.2+1 ProbeCost(Index)+1默认为1.2+1 index File \text{index File} index File B + Tree \text{B}^{+}\text{Tree} B+Tree Unclustered \text{Unclustered} Unclustered [NPages(I)+NTuples(R)]× ∏ i = 1 … n RF i \displaystyle\text{[NPages(I)+NTuples(R)]×}\prod{i=1\ldots\text{n}}\text{RF}_{\text{i}} [NPages(I)+NTuples(R)]×∏i=1…nRFi index File \text{index File} index File B + Tree \text{B}^{+}\text{Tree} B+Tree Clustered \text{Clustered} Clustered [NPages(I)+NPages(R)]× ∏ i = 1 … n RF i \displaystyle\text{[NPages(I)+NPages(R)]×}\prod{i=1\ldots\text{n}}\text{RF}_{\text{i}} [NPages(I)+NPages(R)]×∏i=1…nRFi index File \text{index File} index File Hash \text{Hash} Hash Unclustered \text{Unclustered} Unclustered NTuples(R)× ∏ i = 1 … n RF i × 2.2 \displaystyle\text{NTuples(R)×}\prod{i=1\ldots\text{n}}\text{RF}_{\text{i}}\times{}2.2 NTuples(R)×∏i=1…nRFi×2.2 index File \text{index File} index File Hash \text{Hash} Hash Clustered \text{Clustered} Clustered NPages(R)× ∏ i = 1 … n RF i × 2.2 \displaystyle\text{NPages(R)×}\prod{i=1\ldots\text{n}}\text{RF}_{\text{i}}\times{}2.2 NPages(R)×∏i=1…nRFi×2.2 4.2. Of Multiple Tables \textbf{4.2. Of Multiple Tables} 4.2. Of Multiple Tables
1️⃣需要考虑的问题
事项 注释 Join \text{Join} Join所有可能的顺序 这不会影响 Join \text{Join} Join结果但会影响开销,一般只考虑 Left-Deep Join Tree \text{Left-Deep Join Tree} Left-Deep Join Tree Join \text{Join} Join所有可能的算法 但是排除最蠢的 Cross Product \text{Cross Product} Cross Product 2️⃣对 Pipeline \text{Pipeline} Pipeline的处理
- Heap Scan \text{Heap Scan} Heap Scan不算 Cost + \text{Cost + } Cost + 上层 Join \text{Join} Join也不减其 Pipelining \text{Pipelining} Pipelining
- Heap Scan \text{Heap Scan} Heap Scan自动忽略所有 Index \text{Index} Index
3️⃣ Join Cost \text{Join Cost} Join Cost汇总:注意小表为 Outer \text{Outer} Outer
Type \textbf{Type} Type 扫描成本 Simple NLJ \text{Simple NLJ} Simple NLJ NPages(Outer)+NTuples(Outer)×NPages(Inner) \text{NPages(Outer)+}\text{NTuples(Outer)}\text{×}\text{NPages(Inner)} NPages(Outer)+NTuples(Outer)×NPages(Inner) Page-Oriented NLJ \text{Page-Oriented NLJ} Page-Oriented NLJ NPages(Outer)+NPagees(Outer)×NPages(Inner) \text{NPages(Outer)+}\text{NPagees(Outer)}\text{×}\text{NPages(Inner)} NPages(Outer)+NPagees(Outer)×NPages(Inner) Block NLJ \text{Block NLJ} Block NLJ NPages(Outer)+NPages(Outer)/(Memory-2)×NPages(Inner) \text{NPages(Outer)+}\text{NPages(Outer)/(Memory-2)}\text{×}\text{NPages(Inner)} NPages(Outer)+NPages(Outer)/(Memory-2)×NPages(Inner) Hash Join(HJ) \text{Hash Join(HJ)} Hash Join(HJ) [NPages(Outer)+NPages(Inner)]×3 \text{[NPages(Outer)+NPages(Inner)]×3} [NPages(Outer)+NPages(Inner)]×3 SMJ \text{SMJ} SMJ [NPages(Outer)+NPages(Inner)]×(2×NumPasses+1) \text{[NPages(Outer)+NPages(Inner)]×(2×NumPasses+1)} [NPages(Outer)+NPages(Inner)]×(2×NumPasses+1)
- NPages(Outer)/(Memory-2) \text{NPages(Outer)/(Memory-2)} NPages(Outer)/(Memory-2)要向上取整
- 最优解: { RAM大小: NPages(Outer)+2 开销: Cost=NPages(Outer)+NPages(Inner) \begin{cases}\text{RAM大小: }\text{NPages(Outer)+2}\\\\\text{开销: Cost=NPages(Outer)+NPages(Inner)}\end{cases} ⎩ ⎨ ⎧RAM大小: NPages(Outer)+2开销: Cost=NPages(Outer)+NPages(Inner)
- 注意 SMJ \text{SMJ} SMJ中可能出现 Outer/Inner \text{Outer/Inner} Outer/Inner的 NumPass \text{NumPass} NumPass不一样的情况,此时就要分开讨论
4.3. Pipeline \textbf{4.3. Pipeline} 4.3. Pipeline例题
4.3.0. \textbf{4.3.0. } 4.3.0. 前提条件
1️⃣ SQL \text{SQL} SQL代码
- 关系结构
Movie(MovieID, name, genre, releasedate, duration, budget) Show(ShowID, dateofShow, MovieID(FK), TheatreID(FK), attend, revenue) Theatre(TheatreID, name, city, capacity)
- 查询
SELECT * FROM Movie AS M, Show AS S, Theatre AS T WHERE M.MovieID = S.MovieID AND S.TheatreID = T.TheatreID AND S.revenue < 60000 AND M.genre = 'Comedy';
2️⃣参数
- Data \text{Data} Data参数
/ \text{/} / Genre \small\textbf{Genre} Genre Revenue \small\textbf{Revenue} Revenue Movie \small\textbf{Movie} Movie Show \small\textbf{Show} Show Theatres \small\textbf{Theatres} Theatres Num/Tuples \small\text{Num/Tuples} Num/Tuples 10 \text{10} 10 / \text{/} / 5000 \text{5000} 5000 600,000 \text{600,000} 600,000 100 \text{100} 100 Range \small\text{Range} Range / \text{/} / [0, 100,000] \text{[0, 100,000]} [0, 100,000] / \text{/} / / \text{/} / / \text{/} / Tuples/Page \small\text{Tuples/Page} Tuples/Page / \text{/} / / \text{/} / 100 \text{100} 100 10 \text{10} 10 10 \text{10} 10 ⇒ Pages \small\Rightarrow\text{Pages} ⇒Pages / \text{/} / / \text{/} / 50 \text{50} 50 60,000 \text{60,000} 60,000 10 \text{10} 10 - Index \text{Index} Index参数
/ \text{/} / Show.Revenue \small\textbf{Show.Revenue} Show.Revenue Movie.MovieID \small\textbf{Movie.MovieID} Movie.MovieID Type \small\text{Type} Type Clustered B + Tree \text{Clustered B}^{+}\text{Tree} Clustered B+Tree Clustered Hash \text{Clustered Hash} Clustered Hash Size \small\text{Size} Size 10Pages \text{10Pages} 10Pages 10Pages \text{10Pages} 10Pages - Join \text{Join} Join参数
Movie ⋈ Show \textbf{Movie}\bowtie{}\textbf{Show} Movie⋈Show Theater ⋈ Show \textbf{Theater}\bowtie{}\textbf{Show} Theater⋈Show 100Tuples/Page \text{100Tuples/Page} 100Tuples/Page 100Tuples/Page \text{100Tuples/Page} 100Tuples/Page - Sort \text{Sort} Sort参数:所有关系的排序, NumPass=2 \text{NumPass=2} NumPass=2
3️⃣其它: NLJ \text{NLJ} NLJ是 Page-Oriented \text{Page-Oriented} Page-Oriented的
4.3.1. \textbf{4.3.1. } 4.3.1. 前提条件
SQL \textbf{SQL} SQL RF \textbf{RF} RF 备注 M.MovieID = S.MovieID
1 5000 \cfrac{1}{5000} 50001 MovieID
是 Movie(5000 \text{Movie(5000} Movie(5000个 ) \text{)} )的 PK \text{PK} PKS.TheatreID = T.TheatreID
1 100 \cfrac{1}{100} 1001 TheatreID
是 Theatre(100 \text{Theatre(100} Theatre(100个 ) \text{)} )的 PK \text{PK} PKS.revenue < 60000
6 10 \cfrac{6}{10} 106 范围 [0, 100,000] \text{[0, 100,000]} [0, 100,000] M.genre = 'Comedy'
1 10 \cfrac{1}{10} 101 共十种 4.3.2. Plan A \textbf{4.3.2. Plan A} 4.3.2. Plan A
![]()
1️⃣ RSA \text{RSA} RSA计算
- NTuples(M)×NTuples(S)×RF(M ⋈ S) = 5000 × 600000 × 1 5000 \text{NTuples(M)×NTuples(S)×RF(M}\bowtie\text{S)}=5000×600000×\cfrac{1}{5000} NTuples(M)×NTuples(S)×RF(M⋈S)=5000×600000×50001
- 结果为 600000 Tuples 600000\text{Tuples} 600000Tuples,记得转化为 6000Pages (100Tuples/Page) \text{6000Pages (100Tuples/Page)} 6000Pages (100Tuples/Page)
2️⃣ B \text{B} B处 NLJ(Page-Oriented) \text{NLJ(Page-Oriented)} NLJ(Page-Oriented)计算,默认左边为 Outer \text{Outer} Outer
- NPages(M)+NPagees(M)×NPages(S) = 50 + 50 × 60000 \text{NPages(M)+}\text{NPagees(M)}\text{×}\text{NPages(S)}=50+50×60000 NPages(M)+NPagees(M)×NPages(S)=50+50×60000
- 结果为 3000050 I/O \text{3000050 I/O} 3000050 I/O
3️⃣ C \text{C} C处 SMJ \text{SMJ} SMJ计算
- [RSA+NPages(T)]×(2×NumPasses+1)-RSA=(6000+10)×5-6000 \text{[RSA+NPages(T)]×(2×NumPasses+1)-RSA=(6000+10)×5-6000} [RSA+NPages(T)]×(2×NumPasses+1)-RSA=(6000+10)×5-6000
- 结果为 24050 I/O \text{24050 I/O} 24050 I/O
4.3.2. Plan B \textbf{4.3.2. Plan B} 4.3.2. Plan B
![]()
1️⃣ RSA \text{RSA} RSA计算
- NTuples(S)×RF(Revenue)=600000× 6 10 \text{NTuples(S)×RF(Revenue)=600000×}\cfrac{6}{10} NTuples(S)×RF(Revenue)=600000×106
- 结果为 360000Tuples \text{360000Tuples} 360000Tuples,即 36000Pages \text{36000Pages} 36000Pages
2️⃣ RSB \text{RSB} RSB计算
- NTuples(RSA)×NTuples(T)×RF(S ⋈ T) = 360000 × 100 × 1 100 \text{NTuples(RSA)×NTuples(T)×RF(S}\bowtie\text{T)}=360000×100×\cfrac{1}{100} NTuples(RSA)×NTuples(T)×RF(S⋈T)=360000×100×1001
- 结果为 360000Tuples \text{360000Tuples} 360000Tuples,也就是 3600Pages \text{3600Pages} 3600Pages
3️⃣ C \text{C} C处 Index Scan \text{Index Scan} Index Scan开销,注意是 Clustered B + Tree \text{Clustered B}^{+}\text{Tree} Clustered B+Tree
- [NPages(Index)+NPages(S)]×RF(Revenue)=(10+60000)× 6 10 \text{[NPages(Index)+NPages(S)]×RF(Revenue)=(10+60000)×}\cfrac{6}{10} [NPages(Index)+NPages(S)]×RF(Revenue)=(10+60000)×106
- 结果为 36006 I/O \text{36006 I/O} 36006 I/O
4️⃣ D \text{D} D处 SMJ \text{SMJ} SMJ开销
- [RSA+NPages(Inner)]×(2×NumPasses+1)-RSA = ( 36000 + 10 ) × 5 − 36000 \text{[RSA+NPages(Inner)]×(2×NumPasses+1)-RSA}=(36000+10)×5-36000 [RSA+NPages(Inner)]×(2×NumPasses+1)-RSA=(36000+10)×5−36000
- 开销为 144050 I/O \text{144050 I/O} 144050 I/O
5️⃣ E \text{E} E处开销 = HJ \text{ = HJ} = HJ开销 + Index Scan \text{ + Index Scan} + Index Scan开销
- HJ=[RSB+NPages(M)]×3-RSB-NPages(M)=(3600+50)×2 \text{HJ=[RSB+NPages(M)]×3-RSB-NPages(M)=(3600+50)×2} HJ=[RSB+NPages(M)]×3-RSB-NPages(M)=(3600+50)×2
- Index Scan(M)=NPages(M)×RF×2.2 = 50 × 2.2 \text{Index Scan(M)=NPages(M)×RF×2.2}=50×2.2 Index Scan(M)=NPages(M)×RF×2.2=50×2.2
- 注意此处是 Clustered Hash \text{Clustered Hash} Clustered Hash,并且 RF=1 \text{RF=1} RF=1
- 结果为 7300+110=7410 I/O \text{7300+110=7410 I/O} 7300+110=7410 I/O