数据库 第13章 查询优化

Chapter 13: Query Optimization

概述（Introduction）

执行代价

• 集中式数据库：（磁盘存取块数）IO 代价 + （处理时间）CPU 代价 + （查询的内存开销）内存代价
• 分布式数据库：IO 代价 + CPU 代价 + 内存代价 + 通信代价

注意：IO 代价是最主要的

一个实例

例：求选修了 2 号课程的学生姓名

SELECT Student.Sname
FROM   Student, SC
WHERE  Student.Sno = SC.Sno AND SC.Cno='2'

（假定 Student 数据库中有 1000 个学生记录，10000 条选课记录，其中选修 2 号课程的选课记录为 50 个）

三种查询方式：（${\times }_{natrual}$ 代表自然连接）
$$Q_1={\Pi }_{Sname}({\sigma }_{Student.Sno=SC.Sno\wedge SC.Cno{=}^{\prime }{2}^{\prime }}(Student\times SC))$$

$$Q_2={\Pi }_{Sname}({\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(Student{\times }_{natrual}SC))$$

$$Q_3={\Pi }_{Sname}(Student{\times }_{natrual}{\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(SC))$$

① $Q_1={\Pi }_{Sname}({\sigma }_{Student.Sno=SC.Sno\wedge SC.Cno{=}^{\prime }{2}^{\prime }}(Student\times SC))$

Step 1：计算广义笛卡尔积

① 在内存中尽可能多地装入某个表（如 Student 表）的若干块，留出一块存放另一个表（如 SC 表）的元组

② 把 SC 中的每个元组和 Student 中每个元组连接，连接后的元组装满一块后写到中间文件上

③ 从 SC 中读入一块和内存中的 Student 元组连接，直到 SC 表处理完

④ 再读入若干块 Student 元组，读入一块 SC 元组，重复 ② - ③，直到把 Student 表处理完

代价：

读：设一个块能装 10 个 Student 元组或 100 个 SC 元组，在内存中存放 5 块 Student 元组和 1 块 SC 元组，则读取总块数为：（块）
$$\frac{1000}{10}+\frac{1000}{10\times 5}\times \frac{10000}{100}=2100$$
写：连接后的元组数为 $10^3\times 10^4=10^7$，设每块能装 10 个元组，则写出 $10^6$ 块（到磁盘）

Step 2：选择操作

忽略内存处理时间，读取文件花费的时间（通写中间文件一样），需读入 $10^6$ 块（从磁盘）

满足条件的元组仅 50 个，均可放在内存

Step 3：投影操作

把 Step 2 的结果再 Sname 上作投影输出，得到最终结果

总代价：

$Q_1$ 的查询方法总读写数据块 $=2100+10^6+10^6$

② $Q_2={\Pi }_{Sname}({\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(Student{\times }_{natrual}SC))$

Step 1：计算自然连接

执行自然连接时，读取 Student 和 SC 表的策略不变，总的读取块仍为 2100 块

自然连接的结果比 $Q_1$ 大大减少，为 $10^4$ 个元组，即写出数据块为 $10^3$ 块

Step 2：选择操作

读取中间文件块，执行选择运算，读取数据块为 $10^3$ 块

Step 3：投影操作

把 Step 2 的结果再 Sname 上作投影输出，得到最终结果

总代价：

$Q_2$ 的查询方法总读写数据块 $=2100+10^3+10^3$

③ $Q_3={\Pi }_{Sname}(Student{\times }_{natrual}{\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(SC))$

Step 1：选择操作

先对 SC 表作选择运算，只需读一遍 SC 表，存取 100 块；

最终满足条件的元组仅有 50 条，所以不必使用中间文件

Step 2：计算自然连接

把读入的 Student 元组和内存中的 SC 元组作连接，只需读一遍 Student 表共 100 块

Step 3：投影操作

把 Step 2 的结果再 Sname 上作投影输出，得到最终结果

总代价：

$Q_3$ 的查询方法总的读写块 $=100+100$

④ 其他情况

假如 SC 表的 Cno 字段上有索引

对于 $Q_3$ ，第一步就可以简化成只读取 Cno = ‘2’ 的 50 条元组；

存取的索引块和 SC 中满足条件的数据块大约总共 3~4 块

假如 Student 表的 Sno 字段上有索引

不必读取所有的 Student 元组，因为满足条件的 SC 记录仅有 50 个，涉及最多 50 个 Student 记录

⑤ 总结

将表达式 $Q_1$ 变换为 $Q_2$ 再变为 $Q_3$ ，开销大大减少；

结论：有选择和连接操作时，先做选择操作。这样参加连接的元组就可以大大减少，叫做 代数优化

关系表达式的转化（Transformation of Relational Expressions）

等价规则（equivalence rule）

关系代数表达式的等价：指用相同的关系代替两个表达式中相应的关系所得到的结果时相同的

常用的等价变换规则

① 连接、笛卡尔积交换律

设 $E_1$ 和 $E_2$ 是关系代数表达式，$F$ 是连接运算的条件，则有：

• $E_1\times E_2\equiv E_2\times E_1$
• $E_1{\times }_{natrual}{E}_2\equiv E_2{\times }_{natrual}{E}_1$
• $E_1{\times }_F^{natrual}{E}_2\equiv E_2{\times }_F^{natrual}{E}_1$

② 连接、笛卡尔积结合律

设 $E_1$ 、$E_2$ 和 $E_3$ 是关系代数表达式，$F_1$ 和 $F_2$ 是连接运算的条件，则有：

• $(E_1\times E_2)\times E_3\equiv E_1\times (E_2\times E_3)$

• $(E_1{\times }_{natrual}{E}_2){\times }_{natrual}{E}_3\equiv E_1{\times }_{natrual}(E_2{\times }_{natrual}{E}_3)$

• $(E_1{\times }_{F_1}^{natrual}{E}_2){\times }_{F_2}^{natrual}{E}_3\equiv E_1{\times }_{F_1}^{natrual}(E_2{\times }_{F_2}^{natrual}{E}_3)$

③ 投影的串接定律

${\Pi }_{A_1,A_2,...A_n}({\Pi }_{B_1,B_2,...B_m}(E))\equiv {\Pi }_{A_1,A_2,...A_n}(E)$ ，其中：

• $E$ 是关系代数表达式
• $A_i(i=1,2...n)$ 和 $B_i(i=1,2...m)$ 是属性名
• { A 1 , A 2 , . . . A n } \{A_1,A_2,...A_n\} {A1​,A2​,...An​} 构成 { B 1 , B 2 , . . . B m } \{B_1,B_2,...B_m\} {B1​,B2​,...Bm​} 的子集

④ 选择的串接定理

${\sigma }_{F_1}({\sigma }_{F_2}(E))\equiv {\sigma }_{F_1\wedge F_2}(E)\equiv {\sigma }_{F_2}({\sigma }_{F_1}(E))$ ，其中 $E$ 是关系代数表达式， $F_1$、$F_2$ 是选择条件

选择的串接律说明选择条件可以合并，这样一次就可以检查全部条件

⑤ 选择与投影操作的交换律

${\sigma }_F({\Pi }_{A_1,A_2,...A_n}(E))\equiv {\Pi }_{A_1,A_2,...A_n}({\sigma }_F(E))$ ，其中

• 选择条件 $F$ 指涉及属性 $A_i(i=1,2,...n)$

• 若 $F$ 中有不属于 $A_i(i=1,2...n)$ 的属性 $B_i(i=1,2,...m)$ ，则有更一般规则：

  ${\Pi }_{A_1,A_2,...A_n}({\sigma }_F(E))\equiv {\Pi }_{A_1,A_2,...A_n}({\sigma }_F({\Pi }_{A1,A2,...A_n,B_1,B_2,...B_m}(E)))$

⑥ 选择与笛卡尔积的交换律

若 $F$ 中涉及的属性都是 $E_1$ 中的属性，则：

${\sigma }_F(E_1\times E_2)\equiv {\sigma }_F(E_1)\times E_2$

若 $F=F_1\wedge F_2$ ，且 $F_1$ 中只涉及 $E_1$ 中的属性，$F_2$ 中只涉及 $E_2$ 中的属性，则：

${\sigma }_F(E_1\times E_2)\equiv {\sigma }_{F_1}(E_1)\times {\sigma }_{F_2}(E_2)$

若 $F_1$ 只涉及 $E_1$ 中的属性，$F_2$ 涉及 $E_1$ 和 $E_2$ 两者的属性，则仍然有：

${\sigma }_F(E_1\times E_2)\equiv {\sigma }_{F_2}({\sigma }_{F_1}(E_1)\times E_2)$

这样可以使得部分选择再笛卡尔积之前先做

⑦ 选择与并的分配律

设 $E=E_1\cup E_2$ ，$E_1$ 和 $E_2$ 有相同的属性名，则：

${\sigma }_F(E_1\cup E_2)\equiv {\sigma }_F(E_1)\cup {\sigma }_F(E_2)$

⑧ 选择与差运算的分配律

若 $E_1$ 和 $E_2$ 有相同的属性名，则：

${\sigma }_F(E_1-E_2)\equiv {\sigma }_F(E_1)-{\sigma }_F(E_2)$

⑨ 选择对自然连接的分配律

$F$ 只涉及 $E_1$ 与 $E_2$ 的公共属性，则：

${\sigma }_F(E_1{\times }_{natrual}{E}_2)\equiv {\sigma }_F(E_1){\times }_{natrual}{\sigma }_F(E_2)$

⑩ 投影与笛卡尔积的分配律

设 $E_1$ 和 $E_2$ 是两个关系表达式，$A_1$ ,… $A_n$ 是 $E_1$ 的属性， $B_1$ ,… $B_m$ 是 $E_2$ 的属性，则：

${\Pi }_{A_1,...,A_n,B_1,...B_m}(E_1\times E_2)={\Pi }_{A_1,...A_n}(E_1)\times {\Pi }_{B_1,...B_m}(E_2)$

⑩① 投影与并的分配律

设 $E_1$ 和 $E_2$ 有相同的属性名，则：

${\Pi }_{A_1,A_2,...A_n}(E_1\cup E_2)\equiv {\Pi }_{A_1,A_2,...A_n}(E_1)\cup {\Pi }_{A_1,A_2,...A_n}(E_2)$

物理优化

基于启发式规则的存取路径选择优化

典型的启发式规则

① 选择运算应尽可能先做

② 把投影运算和选择运算同时进行

（如有若干个投影和选择运算，并且都对同一个关系操作，则可以再扫描此关系的同时完成所有这些运算，以避免重复扫描关系）

③ 把投影运算同其前后的双目运算结合起来

（没必要为了去掉某些字段而扫描一遍关系）

④ 把某些选择同它前面要执行的笛卡尔积结合起来成为一个连接运算

（连接特别是等值连接运算要比同样关系上的笛卡尔积省很多时间）

⑤ 找出公共子表达式

（如果重复出现的子表达式的结果不是很大的关系，并且从外村中读入这个关系比计算该子表达式的时间少得多，则先计算一次公共子表达式，并把结果写入中间文件是合算的）

（当查询的是视图时，定义视图的表达式就是公共子表达式的情况）

方法：

① 利用等价变换规则 4，把形如 ${\sigma }_{F_1\wedge F_2\wedge ...\wedge F_n}(E)$ 变换为 ${\sigma }_{F_1}({\sigma }_{F_2}(...({\sigma }_{F_n}(E))))$

② 对每一个选择，尽可能把它移动到树的叶端（即尽可能先做）

③ 对每一个投影，尽可能把它移动到树的叶端（即尽可能先做）

④ 把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影，使多个选择或投影能同时执行，或在一次扫描中全部完成

⑤ 把上述得到的语法树的内节点分组

• 每一双目运算（$\times$、${\times }_{natural}$、$\cup$、$-$）和它所有的直接祖先为一组（直接祖先是 $\sigma$、$\Pi$ 运算）

• 如果其后代直到叶子全是单目运算，则也将它们并入该组

• 但当双目运算是笛卡尔积（$\times$）时，且后面不是与它组成等值连接的选择时，则不能把选择与这个双目运算组成一组

上面的实例：