数据库查询优化

前言

对于一个只有几千行甚至几万行数据的查询的小系统来说，数据库的查询优化作用不大，但对于大型的应用系统，数据动辄上百万，就需要了解DBMS对查询语句的处理过程和优化算法，更好的利用其优化算法，以提高系统的性能。

查询执行过程

执行一条查询语句需要做查询分析、查询检查、查询优化、查询执行等几个关键步骤，具体描述如下：
查询分析：对查询语句进行扫描、词法分析和语法分析，从查询语句中识别出语言符号，进行语法检查和语法分析
查询检查：根据数据字典对合法的查询语句进行语义检查。包括对用户的存取权限进行检查和完整性约束定义检查；检查通过后把SQL查询语句转换成等价的关系代数表达式，一般都用查询树(语法分析树)来表示关系代数表达式
查询优化：数据库管理系统会自动的选择一个高效执行的查询处理策略。所谓的查询优化就是我们DBMS可选择的高效查询处理策略，提供条件，如：建立那种类型的索引
查询执行：代码生成器(code generator)生成执行查询计划的代码，执行相应指令。
查询优化是根据DBMS根据物理设计建立的存取路径，选择最优的算法进行优化，DBMS查询优化包括代数优化和物理优化。

单表选择操作常用算法

1. 全表扫描：对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出。此种算法适合小表，不适合大表
2. 索引扫描：通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组，索引使用B+树或hash散列，当元组比较多时，速度比全表扫描快

在选择操作上的启发

1. 对于小关系，使用全表顺序扫描，即使选择列上有索引
2. 对于大关系，选择条件是主码＝值的查询，结果只有一个元组，选择主码索引；
3. 对于大关系，非主属性＝值的查询，若选择列上有索引，DBMS根据查询结果的元组数目个数确定存取方法，若元组比例较小(<10%)使用索引扫描方法，否则还是使用全表顺序扫描
4. 查询条件包含AND连接的合取选择条件，如果组合索引（如条件为学号和班级联合索引），优先采用组合索引扫描方法
5. 查询条件包含AND连接的合取选择条件，如果某些属性上有一般的索引（如只有学号索引），则可以用索引扫描方法，没有索引使用全表扫描
6. 查询条件包含or、between、！=、<>、in、是否为空（null）的使用，将无法使用索引

结论：使用全表扫描还是索引扫描，是由DBMS根据表中记录数多少和查找数据记录数进行选择的。一般情况下是选择的记录数大于整个表记录总数的20%或者表中的记录很少，使用全表扫描。因此如果表中记录很少或者每次查询选用的记录很多，所建的索引不能提高性能。

连接操作的实现算法

以 SELECT * FROM student，sc WHERE student.sno=sc.sno为例，假设student表中1000条记录，sc表中10000条记录

1. 嵌套循环：对外层循环(student)的每一个元组(s)，检索内层循环(sc)中的每一个元组(sc)，检查这两个元组在连接属性(sno)上是否相等，如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止 。执行次数为1000*10000=10${}^7$次
2. 排序合并：先对student和sc表中的sno排序，取student表中第一个sno，依次扫描sc表中具有相同sno的元组，当扫描到Sno不相同的第一个SC元组时，返回Student表扫描它的下一个元组，再扫描sc表中具有相同sno的元组，把它们连接起来， 直到student 表扫描完成。执行次数为10000=10${}^4$次，但需加排序消耗的时间
3. 索引连接：如果sc表上没有属性sno的索引，建立sno索引；对student中每一个元组，由sno值通过SC的索引查找相应的SC元组；把这些SC元组和Student元组连接起来；直到Student表中的元组处理完为止。执行连接次数为1000= 10${}^3$次，但需要消耗索引的查找时间
4. Hash连接 ：把连接属性作为hash码，用同一个hash函数把R和S中的元组散列到同一个hash文件中。第一步取两个表中较小元祖（student）的一个进行一遍处理，把它的元组按hash函数分散到hash表的桶中，第二步，对另一个表(sc)进行一遍处理，把S的元组散列到适当的hash桶中，把元组与桶中所有来自R并与之相匹配的元组连接起来

在连接操作上的启发

1. 如果2个表都已经按照连接属性排序，选用排序-合并方法
2. 如果一个表在连接属性上有索引，选用索引连接方法
3. 如果上面2个规则都不适用，其中一个表较小，选用Hashjoin方法
4. 可以选用嵌套循环方法，并选择其中较小的表，确切地讲是占用的块数(b)较少的表，作为外表(外循环的表)

结论： 当表中的元祖数比较大时，建议建立索引，这样会大大的提高查找效率。但需要执行索引查找需要通过索引的指针间接地获取数据以及管理索引也需要成本，当元祖数少的时候，DBMS不会选择排序合并和索引连接。当一个表元祖少，一个多，无索引时，DBMS可能会选择Hash连接

基于关系代数优化

集中式数据库：总代价=I/O代价+CPU代价+内存代价，主要考虑I/O代价；
分布式数据库：总代价=I/O代价+CPU代价+内存代价＋通信代价，主要考虑I/O代价和通讯代价
【示例】： 假定学生-课程数据库中有1000个学生记录，10000个选课记录，其中选修2号课程的选课记录为50个，使用不同关系代数计算此SQL表达式的代价
SELECT Student.Sname
FROM Student，SC
WHERE Student.Sno=SC.Sno AND SC.Cno=‘2’；
设一个块能装10个Student元组或100个SC元组，在内存中可以存放5块Student元组和1块SC元组。

1. π_sname(σ_{student.sno=sc.sno∧sc.cno=‘2’} (student×sc))
   student和sc两个关系做笛卡尔积，将拼接好的元祖移出内存写到中间文件上，待拼接完成后，再读入做选择运算，拼接元祖每块10条元祖，每秒读20块，则需时间成本≈105+2×5×10${}^4$≈10${}^5$s=27.8小时
2. π_sname(σ_{sc.cno=‘2’} (student ⋈ sc))
   即两个关系先做自然连接，再做选择和投影，这样学生和课程匹配后最多有10000个元组，即SC元组个数。写入和读取中间文件的成本大大缩小。同样每块10条元祖，每秒读20块，则需时间成本≈105+50+50≈205s
3. π_sname(Student ⋈ σ_{sc.cno=‘2’}(sc))
   先对sc表做选择，选择完成后sc最多符合条件的数据有50条，总的执行时间≈5+5≈10s
4. 第三种情况，如果sc表cno有索引，则读入索引块数更少，约3·4块，student上sno也有索引的话，没有必要读入所有的student元祖，时间更快，约1秒左右就可读出。

代数优化启发，尽量减少I/O操作，减少写入读入数据块的次数，就可以提高性能，对我们有以下启发

1. 选择运算应尽可能先做。在优化策略中这是最重要、最基本的一条
2. 把投影运算和选择运算同时进行，减少读写中间文件次数
3. 把投影同其前或其后的双目运算结合起来
4. 减少扫描关系的次数
5. 某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算
6. 找出公共子表达式

总结

查询优化技术是每中类型的数据库管理系统中的关键技术，是由DBMS自动完成的，但不要把优化的任务全部放在RDBMS上，应该找出RDBMS的优化规律，以写出适合RDBMS自动优化的SQL语句。 对于RDBMS不能优化的查询需要重新规划书写查询语句，进行手工调整以优化查询性能。