查询优化的几种方法

精选转载

ljh0242 2009-07-07 09:09:06 博主文章分类：sqlserver2005

文章标签 职场休闲 文章分类 数据库

一、避免或简化排序

应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：

1.索引中不包括一个或几个待排序的列。

2.GROUP BY或ORDER BY子句中列的次序与索引的次序不一样。

3.排序的列来自不同的表。

为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表(尽管有时可能影响表的规范化，但相对于效率的提高是值得的)。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。

二、消除对大型表行数据的顺序存取

在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询1O亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。

还可以使用并集（UNION）来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的WHERE子句强迫优化器使用顺序存取。下面的查询将强迫对ORDERS表执行顺序操作：

SELECT * FROM ORDERS WHERE （CUSTOMER_NUM=102 AND ORDER_NUM>2003） OR ORDER_NUM=1008

虽然在CUSTOMER_NUM和ORDER_NUM上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：

SELECT * FROM ORDERS WHERE CUSTOMER_NUM=102 AND ORDER_NUM>2003

UNION

SELECT * FROM ORDERS WHERE ORDER_NUM=1008;

这样就能利用索引提高查询效率。

三、避免相关子查询

一个列的标签同时在主查询和WHERE子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。例：

SELECT ITEM FROM A

WHERE ITEM IN (SELECT ITEM FROM B WHERE B.NUM=50) 改为

SELECT ITEM FROM A,B

WHERE A.ITEM=B.ITEM AND B.NUM=50

当然，并不是所有相关子查询都能避免，可以试着用一些方法来减少要检查的行的数目。

但相关子查询若含有EXISTS谓词时，不管子查询从什么表中抽取数据，都只要判断逻辑的真假。这样DBMS的优化器就仅根据索引就可以完成工作。因此使用EXISTS谓词查询效率能比IN子查询效率高。此外尽可能用NOT EXISTS代替NOT IN，也可提高查询效能。

四、使用临时表加速查询

把表的一个子集进行排序，创建临时表，有时能加速查询，也可以避免多次排序，如：

SELECT STUDENT.NAME，GRADE.SCORE ……

FROM STUDENT，GRADE

WHERE STUDENT.STUDENT_NUM=GRADE.STUDENT_NUM AND GRADE.SCORE<60 AND STUDENT.POSTCODE> 98000

ORDER BY STUDENT.NAME

如果这个查询要被执行多次而不止一次，可以把所有考试没有及格的学生找出来放在一个临时文件中，并按学生的名字进行排序：

SELECT STUDENT.NAME，GRADE.SCORE ……

FROM STUDENT，GRADE

WHERE STUDENT.STUDENT_NUM=GRADE.STUDENT_NUM AND GRADE.SCORE<60 AND STUDENT.POSTCODE> 98000

ORDER BY STUDENT.NAME INTO TEMP STUDENT_SCORE

然后以下面的方式在临时表中查询：

SELECT * FROM STUDENT_SCORE WHERE POSTCODE>”98000”

临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。

注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。

五、使用存储过程

存储过程是存储在数据库中的一段存储程序。当创建存储过程时，系统会对其进行编译，并将执行代码存储到数据库中。因为存储过程是在服务器上运行，服务器通常是一种功能更加强大的机器，它的执行时间要比工作站的执行时问短得多，另外，由于数据库信息已经物理地在同一系统中准备好，因此就不必等待记录通过网络传递进行处理，大大减少网络通信量。而存储过程具有对数据库的立即的、准备好的访问，这使得信息处理极为迅速。通过将公共集合编写为存储过程，避免了冗余代码，从而提高了开发生产力。例如，我们可以编写用于查询表的过程，此后应用可以直接调用这些过程而无需重写SQL语句。存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。此外可以设定使用权限来限定调用存储过程以提高数据库操作的安全性。

六、用TRUNCATE替代DELETE

当删除表中的记录时，在通常情况下，回滚段(ROLLBACKSEGMENTS)用来存放可以被恢复的信息。如果没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态。而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。TRUNCATE 快速地从一个表中删除所有行。它和无条件的 DELETE有同样的效果，不过因为它不做表扫描，因而快得多，在大表上最有效。

注意：要小心使用TRUNCATE，尤其没有备份的时候。使用上，想删除部分数据行用DELETE，注意带上WHERE子句，回滚段要足够大。想保留表而将所有数据删除，如果和事务无关，用TRUNCATE即可；如果和事务有关，或者想触发TRIGGER，还是用DELETE。如果是整理表内部的碎片，可以用TRUNCATE跟上REUSE STROAGE，再重新导入/插入数据。