数据库杂谈（八）——查询优化

8 查询优化

8.1 概述

我们不管是在数据库软件如MySQL、SQLServer等，还是通过应用程序连接数据库如JDBC、ODBC等。本质上我们如果要查询数据库中的数据，都是使用SQL去查询。而这个查询的过程说大不大说小不小，如果数据库中一个表内含有许多数据，一个数据库含有很多个表，那么你的SQL写法将大大影响到你查数据的效率。

查询优化并不是由用户决定的，而是由DBMS决定的。当用户写了一条SQL语句后，查询系统会对SQL语句做一个重写，然后变成一个执行效率更高的形式。

换而言之，如果你在数据库中写入了select e.ENAME,d.DNAME from emp e (inner) join dept d on e.DEPTNO = d.DEPTNO;这样的查询语句，实际上传入数据库时，DBMS会自动帮你改写为效率更高的SQL语句。

查询优化一般从四个方面入手：

• 把查询语句进行变换，改变基本操作的次序，然后查询起来更加有效，这个叫做代数优化。
• 根据系统所提供的存取路径，选择合理的存取策略，也就是对存取的方法进行优化，我们叫做物理优化。
• 有限查询优化根据启发式规则和选择执行的策略去先做选择，投影等一元操作，然后做连接操作，这叫规则优化，代数优化和物理优化属于规则优化。
• 在很多的优化策略里面，选取代价最小的优化执行策略，叫做代价估算优化。

需要注意的是，虽然DBMS会帮你重写查询语句，但是如果重写的过程比原SQL查询的过程还久，那显然不合适。

8.2 查询数和语法树

数据库查询语言的处理过程和一般高级程序设计语言相仿。都是经历了解释和编译的过程。

让我们看回这个图。应用调用接口传入命令语句，词法和语法分析器会去解释命令语句，在这里他就会用到一个语法树，将命令语句转变为SQL语句，然后授权检查是否有权限；当有权限后，就会执行sql语句，这里就涉及到查询优化的问题了，其会利用查询树来优化该SQL语句。

8.3 代数优化

代数优化是对查询进行等效变换，以减少执行的开销。最常用的变化原则是，尽量缩短查询过程中的中间结果。由于选择、投影等一元操作分别从水平方向或垂直方向减少关系的大小，而连接、并等二元操作本身的开销较大，而且很可能产生大的中间结果。因此在变换查询时，我们让选择和投影先做，再做二元操作。在连接时，也是先做小关系之间的连接，再做大关系的连接。在有些DBMS中还会找出查询中相同的部分，统一处理，以避免重复运算，还有些DBMS把嵌套查询变为非嵌套查询。

简单来说，假如现在有两张10000个元组的表，想做查询和连接两个操作，根据笛卡尔乘积现象，简单来说，假如现在有两张10000个元组的表，根据笛卡尔乘积现象，当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。也就是要做10000乘以10000次乘积，这无疑是很大的，那如果我们根据选择操作先筛选掉一部分，然后在做连接操作，那么执行效率就会大大提高。

8.4 物理优化

代数优化只是在各种操作中变换次序达到提高效率的目的，虽然这样很方便，但是优化效果有限。但是如果通过合理选择存取路径，往往能够收到显著的优化效果。

在7.1我们谈到过，对于关系小的，查询量大的，我们可以用最原始的方法——顺序扫描，按照关系存放的自然顺序读取各元组，然后按选择条件检验，选取那些满足条件的元组。但是这种方法对于关系大的显然不适用。在目前，用得最多的存储方式就是利用B+树或其变种为结构的各种索引来提高查询的效率。

8.5 连接操作优化

在高级数据库工程师考试中，常见的就是考查连接操作优化。其优化方式有以下几种：

• 嵌套循环法

• 利用索引或散列寻找匹配元组法

• 排序归并法

• 散列连接法

8.5.1 嵌套循环法

大体来说他像我们编程语言里面的循环嵌套一样。假如现在有两个关系（表）：R和S，如下图所示：

如果说我们要进行连接操作，本质上连接操作就是加了条件的笛卡尔乘积。那么我们是利用嵌套循环法就是先用R中的一条元组去匹配另外S表中的所有元组（S表中的所有元组相当于内层循环），然后R中的第二条元组再去匹配S中的所有元组，以此类推，直到R的所有元组和S的所有元组比较完为止。

在整个连接过程中，R只要扫描一次，S就要扫描n次（也就是S表中所有的元组），从程序设计的观点来看，R的扫描相当于程序的外循环，而S相当于程序的内循环，因此我们把R叫做外关系，S叫做内关系。当然，两张表的地位可以互换，这取决于两者扫描的IO代价。

在前面的章节中，我们曾经谈论过磁盘。如果说R中表中一条匹配S表中一条元组就要I/O一次，那么这显然是效率极低的。但是实际上，在操作系统中硬盘是一种块设备，他是以物理块为单位，也就是说，假如一个物理块是1040M，R中一条元组为100M，那么一个物理块就可以容纳R中十条元组去匹配S表。

我们可以设置两个缓冲区，分为外循环和内循环。

外循环的缓冲区可以存放外循环的物理块，内循环的缓冲区可以存放内循环的物理块，当两张表做连接，我们采用循环嵌套法的时候，我们不再是一条一条元组匹配，而是如上所说一个物理块匹配内循环的一个物理块，如果当你内存足够大，你可以设置足够大的缓冲区，存放更多的物理块，如果这张表不大，甚至能做到一波全部匹配完，大大减少了匹配效率。

8.5.2 利用B+树索引或哈希索引寻找匹配元组法

我们在两个表中还是那样：

R表作为外循环，S表作为内循环，然后给R表一个缓冲区，S表带有B+树索引。

之后R表一个物理块读进来，他要找S表中匹配的元组，就可以通过B+树索引找到物理块中符合连接条件的元组的地址，直接进行匹配，大大节省了寻找匹配元组的效率，不需要像嵌套循环法那样去扫描。

但是这个需要考虑代价：假如在某个属性上面，重复值的数量达到了总元组数的百分之二十以上的话，用索引反而不合算。什么意思呢？如果有过多的重复值，也就意味着你要在建树的时候有许多重复值，这就会导致在辨别重复值的时候花费过多的代价，这样还不如使用循环嵌套法来的方便。

8.5.3 散列连接法

具体来说就是：他把R表和S表中以后做连接有可能产生的结果全部哈希到一个柜子里，柜子有多个抽屉，不同的抽屉代表不同的结果，以后只要需要两张表的连接，只需要在哈希出来的这个柜子（表）里面去找就行了。

但是考虑到代价，也就是说你这个R表和S表不会频繁的去更新，否则的话会打乱哈希出来的表，定期做维护代价也会很大。

8.6 后话

实际上，查询优化博大精深，但是对于初学者来说，这一讲并不是其需要学习的重点，而对于数据库老手，我更建议去看数据库原书或者是东南大学王能斌老师的书。这里还有很多的知识我就不再细讲了，有兴趣的同学可以去自行探究。