第十三章 SQL 语句优化 主讲内容 :SELECT 子句 WHERE 子句的优化等 ;
复习上节课内容 1. 用户帐号管理 ( 创建 编辑 删除 ) 2. 权限管理 ( 授予及回收系统权限 对象权限 ) 3. 角色管理 ( 创建 编辑 删除 ) 4. 角色权限管理 ( 授予 回收 ) 5. 授权角色给用户 第 2 页
本节课内容 一.SQL 语句优化概述二.SQL 语句优化技巧三. 其他优化方法 第 3 页
一.SQL 语句优化概述 由于 Oracle 可以存储更多更复杂的数据, 就使得数据的查询效率显得更为重要, 低效率的查询给实际应用带来的麻烦事不可估量的 ; SQL 语句通常不需要告诉数据库如何获得数据, 只需执行查询 指定所需数据, 数据库就会找到最好方法获得它 第 4 页
SQL 语句的优化就是将性能较低的 SQL 语句转换成达到同样目的的性能优异的 SQL 语句 ; 对于 DELETE 语句 SELECT 子句 FROM 子句和 WHERE 子句等都可以进行优化, 本课程将具体介绍不同情况下的优化技巧 第 5 页
二 SQL 语句优化技巧 1. Select 子句的优化 2. Where 子句的优化 3. TRUNCATE 与 DELETE 4. 使用表连接而不是多个查询 5. 使用 EXISTS 替代 IN 6. 使用 EXISTS 替代 DISTINCT 7. 使用使用 <= 替代 < 8. 使用表的别名 第 6 页
1.Select 子句的优化 SELECT 语句中避免使用 * ; 在 SELECT 子句查询表的所有列时, 可以使用动态 SQL 列引用 *, 用来表示表中所有的列 ; 使用 * 替代所有的列, 可以降低编写 SQL 语句的难度, 减少 SQL 语句的复杂性, 但是却降低了 SQL 语句执行的效率 第 7 页
使用 SET TIMING ON 语句显示执行时间 例 1, 检索 scott 用户的 emp 表, 使用 * 来替代所有的列名 ; SQL> SET TIMING ON SQL> SELECT * FROM scott.emp; 第 8 页
例 2, 检索 scott 用户的 emp 表, 使用具体的列名 ; SQL> SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM scott.emp ; 第 9 页
2.Where 子句的优化 在 SELECT 语句中, 使用 WHERE 子句过滤行, 使用 HAVING 子句过滤分组 ; 行分组需要一定的时间, 应该尽量使用 Where 子句过滤行, 减少分组的行数, 从而提高语句的执行效率 第 10 页
例 1, 查询各个课程的平均分数, 其中只需要 cno 列值大于 1 的记录 ; SELECT cno, avg(grade) FROM sc GROUP BY cno HAVING cno>1; 第 11 页
SELECT cno,avg(grade) FROM sc where cno>1 GROUP BY cno; 第 12 页
虽然结果基本一样, 但是两种方式根据记录量的不同消耗时间也有差异 ; 故, 对于数据记录特别多的数据查询, 应提前使用 Where 子句就会减少此方面的开销, 过滤条件应尽量让 Where 子句实现 第 13 页
3.TRUNCATE 与 DELETE 删除表中的数据可以使用 DELETE 或者 TRUNCATE 语句 ; 其中, 使用 DELETE 语句删除表中所有的数据时, Oracle 会对数据逐行删除 ; TRUNCATE 语句删除表中的所有数据行时是一次性的, 也就是执行一次 TRUNCATE 语句, 所有的数据行是在同一时间被删除 如果确定要删除表中的所有行, 建议使用 TRUNCATE 语句 第 14 页
4. 使用表连接而不是多个查询 在执行每条查询语句时,Oracle 内部执行了许多工作 解析 SQL 语句 估算索引的利用率 绑定变量, 以及读取数据块等 因此, 要尽量减少访问 SQL 语句的执行次数 尽量减少表的查询次数, 主要是指可以使用一次查询获得的数据, 尽量不要通过两次或更多次的查询获得 第 15 页
另外, 当需要对多个表查询时, 在 Select 语句中使用表的 连接, 必须选择表的连接顺序, 建议行数少的表连接到后 面 第 16 页
例 1, 查询 accounting 部门的所有员工信息 ; SQL> SELECT empno, ename, deptno FROM scott.emp WHERE deptno = (SELECT deptno FROM scott.dept WHERE dname = 'ACCOUNTING'); 第 17 页
例 2, 查询 accounting 部门的所有员工信息 ; SQL> SELECT empno,ename,scott.emp.deptno FROM scott.emp, scott.dept WHERE scott.emp.deptno=scott.dept.deptno and dname= 'ACCOUNTING'; 第 18 页
5. 使用 EXISTS 替代 IN IN 操作符用于检查一个值是否包含在列表中 ; EXISTS 与 IN 不同,EXISTS 只检查行的存在性, 而 IN 检查实际的值 在子查询中,EXISTS 提供的性能通常比 IN 提供的性能要好 ; 因此建议使用 EXISTS 操作符来替代 IN 操作符的使用, 使用 NOT EXISTS 替代 NOT IN, 来提高查询的执行效率 第 19 页
例 1, 检索部门在 NEW YORK 的员工信息 ; SQL> SELECT empno, ename, scott.emp.deptno FROM scott.emp WHERE deptno IN (SELECT deptno FROM scott.dept WHERE loc = 'NEW YORK'); 第 20 页
例 2, 查询同例 1; SQL> SELECT empno, ename, scott.emp.deptno FROM scott.emp WHERE EXISTS ( SELECT 1 FROM scott.dept WHERE scott.dept.deptno = scott.emp.deptno AND loc= 'NEW YORK'); 第 21 页
6. 使用 EXISTS 替代 DISTINCT 在连接查询的 SELECT 语句中,DISTINCT 关键字用于禁止重复行的显示 ;EXISTS 用于检查子查询返回的行的存在性 ; 尽量使用 EXISTS 替代 DISTINCT, 因为 DISTINCT 在禁止重复行显示之前要排序检索到的行 第 22 页
7. 使用 <= 替代 < 在检索条件的子句中, 经常使用运算符 <= 和 < ; 很多时候, 这两个运算符可以替换使用, 在查询时显得差别不大, 但是在查询的数据量较大, 尤其是在循环语句中使用时, 检索效率是不一样的, 建议用 <= 替代 < 的使用 第 23 页
例 1, 查询 scott.emp 表中员工号小于 7900 的员工信息 ; SELECT * FROM scott.emp WHERE empno<7901; 第 24 页
例 2, 查询同例 1; SELECT * FROM scott.emp WHERE empno<=7900; 第 25 页
8. 使用表的别名 在查询中包含多个表时, 为每个表指定表别名, 并且为所引用的每列都显式地指定合适的别名, 这也称为完全限定的列引用 例 1, 查询 scott 用户的 emp 表和 dept 表信息 ; SQL> SELECT empno,ename, scott.emp.deptno,dname FROM scott.emp,scott.dept Where scott.emp.deptno=scott.dept.deptno; 第 26 页
例 2, 查询同例 1; SQL> SELECT empno,ename, scott.e.deptno,dname FROM scott.emp e, scott.dept dwhere e.deptno=d.deptno; 第 27 页
优化建议总结 : 尽量用列名代替 * ; 对于多条记录, 尽量用 Where 替代 Having; 使用表连接代替多个查询 ; 尽量用 Exist 代替 in Distinct; 使用 <= 代替 < ; 使用表的别名 ; 第 28 页
三. 其它优化方法 1. 表的连接方法 2. 有效使用索引 第 29 页
1. 表的连接方法 在连接查询时, 需要操作多个表, 处理不好表之间的连接关系将会影响查询效率, 所以对表进行连接查询时, 更应该注意 SQL 语句的优化 1 FROM 子句中表的顺序 2 WHERE 子句的连接顺序 第 30 页
1 FROM 子句中表的顺序 在 Select 语句中, 可以指定多个表的名称, 从查询结果来 说, 表的顺序无关 ; 若从查询效率来考虑, 表之间的顺序 是不能随意的 ; Oracle 的解析器在处理 FROM 子句中的表时, 是按照从右到 左的顺序, 也就是说,FROM 子句中最后指定的表将被 Oracle 首先处理, 之后再扫描倒数第二个表 ; 最后将所有 从第二个表中检索出来的记录与第一个表中的合适记录进 行合并 数据表行数少的放最后! 第 31 页
2 WHERE 子句的连接顺序 Oracle 采用自右至左 ( 自下向上 ) 的顺序解析 WHERE 子句, 根据这个顺序, 表之间的连接应该写在其他 WHERE 条件之前, 将可以过滤掉最大数量记录的条件写在 WHERE 子句的末尾 过滤行数最多的放最后! 第 32 页
2. 有效使用索引 在关系数据库中, 一个行的物理位置无关紧要, 除非数据库需要找到它, 索引是一种供服务器在表中快速查找一个行的数据库结构 ; 索引是表的一个概念部分, 用来提高检索数据的效率 ; 索引需要空间来存储 ; 需要定期维护 ; 每当有记录增减或索引列被修改时, 索引本身也会被修改 这意味着针对每条记录的 INSERT UPDATE 和 DELETE 操作, 都需要更多的磁盘 I/O 因为索引需要额外的存储空间和处理操作, 所以那些不必要的索引反而会影响查询效率 第 33 页
1 使用索引基本原则 创建索引的基本原则 对于经常以查询关键字为基础的表, 并且该表中的数据行是均匀分布的 ; 以查询关键字为基础, 表中的数据行随机排序 ; 表中包含的列数相对比较少 ; 表中的大多数查询都包含相对简单的 WHERE 子句 第 34 页
在创建索引时, 需要认真选择表中的哪些列可以作为索引列 选择索引列有如下几个原则 : 经常在 WHERE 子句中使用的列 ; 经常在表连接查询中用于表之间连接的列 ; 不宜将经常修改的列作为索引列 ; 不宜将经常在 WHERE 子句中使用, 但与函数或操作符相结合的列作为索引列 ; 对于取值较少的列, 应考虑建立位图索引, 而不应该采用 B 树索引 ; 第 35 页
2 索引列上所使用的操作符 对索引列的操作语句应该尽量避免 非 操作符的使用, 例如 NOT!= <>!<!> NOT EXISTS NOT IN 和 NOT LIKE 等, 非 操作符的使用会造成 Oracle 对表执行全表扫描 ; 另外, 使用 LIKE 操作符可以应用通配符查询, 但是如果用得不好, 会产生性能上的问题 如 LIKE %5400% 不会被使用到索引, 而 LIKE 5400% 则会引用范围索引 第 36 页
3 避免对唯一索引列使用 NULL 值 使用 UNIQUE 关键字可以为列添加唯一索引, 也就是说列的值不允许有重复值, 但是, 多个 NULL 值却可以同时存在, 因为 Oracle 认为两个空值是不相等的 ; 因此, 在 WHERE 子句中使用 IS NULL 或 IS NOT NULL, 对唯一索引列进行空值比较时,Oracle 将停止使用该列上的唯一索引, 导致 Oracle 进行全表扫描 第 37 页
4 选择复合索引主列 索引不仅可以基于单独的列, 还可以基于多个列, 在多个列上创建的索引叫复合索引 创建复合索引时, 应该按照如下原则 : 选择经常在 WHERE 子句中使用 并且由 AND 操作符连接的列作为复合索引列 ; 选择 WHERE 子句中使用频率相对较高的列排在最前面, 或者根据需要为其他列创建单独的索引 第 38 页
例 1, 为 scott.emp 表中的 deptno 列和 sal 列建立复合索引 deptno_sal_index; SQL> CREATE INDEX deptno_sal_index ON scott.emp(deptno, sal); 第 39 页
使用所创建的复合索引 deptno_sal_index 时,WHERE 子句中列的顺序, 应该尽量与复合索引中列的顺序保持一致, 如下面的两条 SELECT 语句 : SQL> SELECT empno,ename,sal,deptno FROM scott.emp WHERE deptno>10 AND sal> 2000; SQL> SELECT empno,ename,sal,deptno FROM scott.emp WHERE sal>2000 AND deptno >10; 第 40 页
上述两条语句仅仅在 Where 子句中指定的列的顺序不同, 并不影响查询结果, 但是却会影响查询效率 ; 合理的查询语句应该是上述第一条 Select 语句, 即保持 Where 子句中列的顺序与复合索引中列的顺序一致 第 41 页
5 监视索引是否被使用 不必要的索引会对表的查询效率起负作用, 所以实际应用中应该经常检查索引是否被使用, 这需要用到索引的监视功能 ; 监视索引后, 可以通过数据字典视图来了解索引的使用状态, 如果确定索引不再需要使用, 可以删除该索引 第 42 页
例 4, 使用 ALTER INDEX 语句, 监视前面创建的 deptno_sal_index 索引 ; SQL> ALTER INDEX deptno_sal_index MONITORING USAGE; SQL> SELECT table_name, index_name, monitoring FROM v$object_usage; 第 43 页
第 44 页
小结 SQL 语句优化概述 SQL 语句优化技巧 ( 顺序 ) 其他优化 ( 连接 索引 ) 第 45 页
再见 第 46 页