2022年SQL查询优化策略 .pdf

《2022年SQL查询优化策略 .pdf》由会员分享，可在线阅读，更多相关《2022年SQL查询优化策略 .pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1. 用 IN 来替换 OR 下面的查询可以被更有效率的语句替换: 低效 : SELECT field1, field1 FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 高效SELECT field1, field1 FROM LOCATION WHERE LOC_IN IN (10,20,30) 2. 连接多个扫描如果你对一个列和一组有限的值进行比较, 优化器可能执行多次扫描并对结果进行合并连接. 举例 : SELECT * FROM LODGING WHERE MANAGER IN ( BILL GATES , K

2、EN MULLER );优化器可能将它转换成以下形式SELECT * FROM LODGING WHERE MANAGER = BILL GATES OR MANAGER = KEN MULLER ;3. 优化 GROUP BY 提高GROUP BY 语句的效率 , 可以通过将不需要的记录在GROUP BY 之前过滤掉 .下面两个查询返回相同结果但第二个明显就快了许多. 低效 : SELECT JOB , A VG(SAL) FROM EMPGROUP JOB HAVING JOB = PRESIDENT OR JOB = MANAGER高效 : SELECT JOB , A VG(SAL)

3、FROM EMPWHERE JOB = PRESIDENT OR JOB = MANAGER GROUP JOB 4. 用= 替代 如果 DEPTNO 上有一个索引, 高效 : SELECT * FROM EMP WHERE DEPTNO =4 低效 : SELECT * FROM EMP WHERE DEPTNO 3 5. 用表连接替换EXISTS 通常来说, 采用表连接的方式比EXISTS 更有效率名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - -

4、 - - - - - SELECT ENAME FROM EMP E WHERE EXISTS (SELECT X FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CA T = A ); (更高效 ) SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CA T = A ; 6. 用 EXISTS 替换 DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时 ,避免在SELECT 子句中使用DISTINCT. 一般可以考虑用EXIST 替换低效 :

5、SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 高效 : SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); EXISTS 使查询更为迅速,因为 RDBMS 核心模块将在子查询的条件一旦满足后,立刻返回结果. 7. 使用表的别名(Alias) 当在 SQL 语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column 上 .这样

6、一来 ,就可以减少解析的时间并减少那些由Column 歧义引起的语法错误. (译者注 : Column 歧义指的是由于SQL 中不同的表具有相同的Column 名,当 SQL 语句中出现这个 Column 时,SQL 解析器无法判断这个Column 的归属 ) 8. 用 EXISTS 替代 IN 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用 EXISTS( 或 NOT EXISTS) 通常将提高查询的效率. 低效 : SELECT * FROM EMP ( 基础表 ) WHERE EMPNO 0 AND DEPTNO IN (SELECT DEP

7、TNO FROM DEPT WHERE LOC = MELB )名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - 高效 : SELECT * FROM EMP ( 基础表 ) WHERE EMPNO 0 AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP .DEPTNO AND LOC = MELB )(译者按 : 相对来说 ,用 NOT EXISTS 替换 NOT IN

8、将更显著地提高效率,下一节中将指出) 9. 用 NOT EXISTS 替代 NOT IN 在子查询中 ,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN 都是最低效的(因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或 NOT EXISTS. 例如 : SELECT FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CA T=A);为了提高效率 .改写为 : (方法一 : 高效 ) SELECT .FROM E

9、MP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CA T(+) = A(方法二 : 最高效 ) SELECT .FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CA T = A );10. 减少对表的查询在含有子查询的SQL 语句中 ,要特别注意减少对表的查询. 例如 : 低效SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SE

10、LECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNS 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - WHERE VERSION = 604) 高效SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,D

11、B_VER) FROM TAB_COLUMNS WHERE VERSION = 604) Update 多个 Column 例子 : 低效 : UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 高效 : UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CA TEGORY) , MAX(SAL_RANG

12、E)FROM EMP_CA TEGORIES) WHERE EMP_DEPT = 0020; 11. 在 Oracle 快速进行数据行存在性检查只检索一个启示就可以判断主键是否能与外键相配，这比Count(*) 方法快得多，例如: SQL Using Count(*) SELECT Count(*) INTO :ll_Count FROM ORDER WHERE PROD_ID = :ls_CheckProd USING SQLCA; IF ll_Count 0 THEN / Cannot delete product SQL Using ROWNUM SELECT ORDER_ID INTO

13、 :ll_OrderID FROM ORDER WHERE PROD_ID = :ls_CheckProd AND ROWNUM 2 USING SQLCA; IF SQLCA.SQLNRows 0 THEN / cannot delete product 12使用 %TYPE 、%ROWTYPE 方式声明变量程序设计中常常要通过变量来实现程序间的数据传递，即将表中数据赋值给变量，或是把变量值插入到表中。而要完成这些操作的前提就是，表中数据与变量类型要一致。然而在实际中， 表中数据或类型、或宽度有时要变化，一旦变化， 就必须去修改程序中的变量声明部 分 ， 否 则 程 序 将 不 能 正 常

14、运 行 。 为 了 减 少 这 部 分 程 序 的 修 改 ， 编 程 时 使用%TYPE 、%ROWTYPE方式声明变量，使变量声明的类型与表中的保持同步，随表的变化而变化，这样的程序在一定程度上具有更强的通用性。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - 13. 使用 DECODE 函数来减少处理时间使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表. 例如 : SELECT COUNT(*)， SUM(

15、SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMITH% ; SELECT COUNT(*)， SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH% ;你可以用DECODE 函数高效地得到相同结果SELECT COUNT(DECODE(DEPT_NO,0020, X,NULL) D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL

16、, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SAL FROM EMP WHERE ENAME LIKE SMITH% ;类似的 ,DECODE 函数也可以运用于GROUP BY和 ORDER BY 子句中 . 14. 尽量多使用COMMIT 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求 也会因为COMMIT所释放的资源而减少: COMMIT 所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE 为管理上述3 种资源中的内部花费15. 整合

17、简单 ,无关联的数据库访问如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系 ) 例如 : SELECT NAME FROM EMP WHERE EMP_NO = 1234; SELECT NAME FROM DPT WHERE DPT_NO = 10 ; SELECT NAME 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - FROM CAT WHERE CA T_TYPE = RD ;上面的

18、 3 个查询可以被合并成一个: SELECT E.NAME , D.NAME , C.NAME FROM CAT C , DPT D , EMP E,DUAL X WHERE NVL( X ,X.DUMMY) = NVL( X ,E.ROWID(+)AND NVL( X ,X.DUMMY) = NVL( X ,D.ROWID(+)AND NVL( X ,X.DUMMY) = NVL( X ,C.ROWID(+)AND E.EMP_NO(+) = 1234 AND D.DEPT_NO(+) = 10 AND C.CA T_TYPE(+) = RD ; 16. WHERE 子句中的连接顺序ORAC

19、LE采用自下而上的顺序解析WHERE 子句 ,根据这个原理,表之间的连接必须写在其他 WHERE 条件之前 , 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾 . 例如 : (低效 ,执行时间156.3 秒) SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效 ,执行时间10.6 秒) SELECT FROM EMP E WHERE 25 50000 AND JOB = MANAGER;17. 减少访问数据库的次数当

20、执行每条SQL 语句时 , ORACLE 在内部执行了许多工作: 解析 SQL 语句 , 估算索引的利用率 , 绑定变量, 读数据块等等. 由此可见 , 减少访问数据库的次数, 就能实际上减少ORACLE 的工作量 . 例如 , 以下有三种方法可以检索出雇员号等于0342 或 0291 的职员 . 方法 1 (最低效 ) SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - -

21、- 第 6 页，共 7 页 - - - - - - - - - SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291; 方法 2 (次低效 ) DECLARE CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO; BEGIN OPEN C1(342); FETCH C1 INTO ,.,. ;. OPEN C1(291); FETCH C1 INTO ,.,. ; CLOSE C1; END; 方法 3 (高效) SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342 AND B.EMP_NO = 291; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -