Oracle数据库应用技术13 [兼容模式]

Size: px

Start display at page:

Download "Oracle数据库应用技术13 [兼容模式]"

别苏
5 years ago
Views:

1 第十三章 SQL 语句优化主讲内容 :SELECT 子句 WHERE 子句的优化等 ;

2 复习上节课内容 1. 用户帐号管理 ( 创建编辑删除 ) 2. 权限管理 ( 授予及回收系统权限对象权限 ) 3. 角色管理 ( 创建编辑删除 ) 4. 角色权限管理 ( 授予回收 ) 5. 授权角色给用户第 2 页

3 本节课内容一.SQL 语句优化概述二.SQL 语句优化技巧三. 其他优化方法第 3 页

4 一.SQL 语句优化概述由于 Oracle 可以存储更多更复杂的数据, 就使得数据的查询效率显得更为重要, 低效率的查询给实际应用带来的麻烦事不可估量的 ; SQL 语句通常不需要告诉数据库如何获得数据, 只需执行查询指定所需数据, 数据库就会找到最好方法获得它第 4 页

5 SQL 语句的优化就是将性能较低的 SQL 语句转换成达到同样目的的性能优异的 SQL 语句 ; 对于 DELETE 语句 SELECT 子句 FROM 子句和 WHERE 子句等都可以进行优化, 本课程将具体介绍不同情况下的优化技巧第 5 页

6 二 SQL 语句优化技巧 1. Select 子句的优化 2. Where 子句的优化 3. TRUNCATE 与 DELETE 4. 使用表连接而不是多个查询 5. 使用 EXISTS 替代 IN 6. 使用 EXISTS 替代 DISTINCT 7. 使用使用 <= 替代 < 8. 使用表的别名第 6 页

7 1.Select 子句的优化 SELECT 语句中避免使用 * ; 在 SELECT 子句查询表的所有列时, 可以使用动态 SQL 列引用 *, 用来表示表中所有的列 ; 使用 * 替代所有的列, 可以降低编写 SQL 语句的难度, 减少 SQL 语句的复杂性, 但是却降低了 SQL 语句执行的效率第 7 页

8 使用 SET TIMING ON 语句显示执行时间例 1, 检索 scott 用户的 emp 表, 使用 * 来替代所有的列名 ; SQL> SET TIMING ON SQL> SELECT * FROM scott.emp; 第 8 页

9 例 2, 检索 scott 用户的 emp 表, 使用具体的列名 ; SQL> SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM scott.emp ; 第 9 页

10 2.Where 子句的优化在 SELECT 语句中, 使用 WHERE 子句过滤行, 使用 HAVING 子句过滤分组 ; 行分组需要一定的时间, 应该尽量使用 Where 子句过滤行, 减少分组的行数, 从而提高语句的执行效率第 10 页

11 例 1, 查询各个课程的平均分数, 其中只需要 cno 列值大于 1 的记录 ; SELECT cno, avg(grade) FROM sc GROUP BY cno HAVING cno>1; 第 11 页

12 SELECT cno,avg(grade) FROM sc where cno>1 GROUP BY cno; 第 12 页

13 虽然结果基本一样, 但是两种方式根据记录量的不同消耗时间也有差异 ; 故, 对于数据记录特别多的数据查询, 应提前使用 Where 子句就会减少此方面的开销, 过滤条件应尽量让 Where 子句实现第 13 页

14 3.TRUNCATE 与 DELETE 删除表中的数据可以使用 DELETE 或者 TRUNCATE 语句 ; 其中, 使用 DELETE 语句删除表中所有的数据时, Oracle 会对数据逐行删除 ; TRUNCATE 语句删除表中的所有数据行时是一次性的, 也就是执行一次 TRUNCATE 语句, 所有的数据行是在同一时间被删除如果确定要删除表中的所有行, 建议使用 TRUNCATE 语句第 14 页

15 4. 使用表连接而不是多个查询在执行每条查询语句时,Oracle 内部执行了许多工作解析 SQL 语句估算索引的利用率绑定变量, 以及读取数据块等因此, 要尽量减少访问 SQL 语句的执行次数尽量减少表的查询次数, 主要是指可以使用一次查询获得的数据, 尽量不要通过两次或更多次的查询获得第 15 页

16 另外, 当需要对多个表查询时, 在 Select 语句中使用表的连接, 必须选择表的连接顺序, 建议行数少的表连接到后面第 16 页

17 例 1, 查询 accounting 部门的所有员工信息 ; SQL> SELECT empno, ename, deptno FROM scott.emp WHERE deptno = (SELECT deptno FROM scott.dept WHERE dname = 'ACCOUNTING'); 第 17 页

18 例 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 页

19 5. 使用 EXISTS 替代 IN IN 操作符用于检查一个值是否包含在列表中 ; EXISTS 与 IN 不同,EXISTS 只检查行的存在性, 而 IN 检查实际的值在子查询中,EXISTS 提供的性能通常比 IN 提供的性能要好 ; 因此建议使用 EXISTS 操作符来替代 IN 操作符的使用, 使用 NOT EXISTS 替代 NOT IN, 来提高查询的执行效率第 19 页

20 例 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 页

21 例 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 页

22 6. 使用 EXISTS 替代 DISTINCT 在连接查询的 SELECT 语句中,DISTINCT 关键字用于禁止重复行的显示 ;EXISTS 用于检查子查询返回的行的存在性 ; 尽量使用 EXISTS 替代 DISTINCT, 因为 DISTINCT 在禁止重复行显示之前要排序检索到的行第 22 页

23 7. 使用 <= 替代 < 在检索条件的子句中, 经常使用运算符 <= 和 < ; 很多时候, 这两个运算符可以替换使用, 在查询时显得差别不大, 但是在查询的数据量较大, 尤其是在循环语句中使用时, 检索效率是不一样的, 建议用 <= 替代 < 的使用第 23 页

24 例 1, 查询 scott.emp 表中员工号小于 7900 的员工信息 ; SELECT * FROM scott.emp WHERE empno<7901; 第 24 页

25 例 2, 查询同例 1; SELECT * FROM scott.emp WHERE empno<=7900; 第 25 页

26 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 页

27 例 2, 查询同例 1; SQL> SELECT empno,ename, scott.e.deptno,dname FROM scott.emp e, scott.dept dwhere e.deptno=d.deptno; 第 27 页

28 优化建议总结 : 尽量用列名代替 * ; 对于多条记录, 尽量用 Where 替代 Having; 使用表连接代替多个查询 ; 尽量用 Exist 代替 in Distinct; 使用 <= 代替 < ; 使用表的别名 ; 第 28 页

29 三. 其它优化方法 1. 表的连接方法 2. 有效使用索引第 29 页

30 1. 表的连接方法在连接查询时, 需要操作多个表, 处理不好表之间的连接关系将会影响查询效率, 所以对表进行连接查询时, 更应该注意 SQL 语句的优化 1 FROM 子句中表的顺序 2 WHERE 子句的连接顺序第 30 页

31 1 FROM 子句中表的顺序在 Select 语句中, 可以指定多个表的名称, 从查询结果来说, 表的顺序无关 ; 若从查询效率来考虑, 表之间的顺序是不能随意的 ; Oracle 的解析器在处理 FROM 子句中的表时, 是按照从右到左的顺序, 也就是说,FROM 子句中最后指定的表将被 Oracle 首先处理, 之后再扫描倒数第二个表 ; 最后将所有从第二个表中检索出来的记录与第一个表中的合适记录进行合并数据表行数少的放最后! 第 31 页

32 2 WHERE 子句的连接顺序 Oracle 采用自右至左 ( 自下向上 ) 的顺序解析 WHERE 子句, 根据这个顺序, 表之间的连接应该写在其他 WHERE 条件之前, 将可以过滤掉最大数量记录的条件写在 WHERE 子句的末尾过滤行数最多的放最后! 第 32 页

33 2. 有效使用索引在关系数据库中, 一个行的物理位置无关紧要, 除非数据库需要找到它, 索引是一种供服务器在表中快速查找一个行的数据库结构 ; 索引是表的一个概念部分, 用来提高检索数据的效率 ; 索引需要空间来存储 ; 需要定期维护 ; 每当有记录增减或索引列被修改时, 索引本身也会被修改这意味着针对每条记录的 INSERT UPDATE 和 DELETE 操作, 都需要更多的磁盘 I/O 因为索引需要额外的存储空间和处理操作, 所以那些不必要的索引反而会影响查询效率第 33 页

34 1 使用索引基本原则创建索引的基本原则对于经常以查询关键字为基础的表, 并且该表中的数据行是均匀分布的 ; 以查询关键字为基础, 表中的数据行随机排序 ; 表中包含的列数相对比较少 ; 表中的大多数查询都包含相对简单的 WHERE 子句第 34 页

35 在创建索引时, 需要认真选择表中的哪些列可以作为索引列选择索引列有如下几个原则 : 经常在 WHERE 子句中使用的列 ; 经常在表连接查询中用于表之间连接的列 ; 不宜将经常修改的列作为索引列 ; 不宜将经常在 WHERE 子句中使用, 但与函数或操作符相结合的列作为索引列 ; 对于取值较少的列, 应考虑建立位图索引, 而不应该采用 B 树索引 ; 第 35 页

36 2 索引列上所使用的操作符对索引列的操作语句应该尽量避免非操作符的使用, 例如 NOT!= <>!<!> NOT EXISTS NOT IN 和 NOT LIKE 等, 非操作符的使用会造成 Oracle 对表执行全表扫描 ; 另外, 使用 LIKE 操作符可以应用通配符查询, 但是如果用得不好, 会产生性能上的问题如 LIKE %5400% 不会被使用到索引, 而 LIKE 5400% 则会引用范围索引第 36 页

37 3 避免对唯一索引列使用 NULL 值使用 UNIQUE 关键字可以为列添加唯一索引, 也就是说列的值不允许有重复值, 但是, 多个 NULL 值却可以同时存在, 因为 Oracle 认为两个空值是不相等的 ; 因此, 在 WHERE 子句中使用 IS NULL 或 IS NOT NULL, 对唯一索引列进行空值比较时,Oracle 将停止使用该列上的唯一索引, 导致 Oracle 进行全表扫描第 37 页

38 4 选择复合索引主列索引不仅可以基于单独的列, 还可以基于多个列, 在多个列上创建的索引叫复合索引创建复合索引时, 应该按照如下原则 : 选择经常在 WHERE 子句中使用并且由 AND 操作符连接的列作为复合索引列 ; 选择 WHERE 子句中使用频率相对较高的列排在最前面, 或者根据需要为其他列创建单独的索引第 38 页

39 例 1, 为 scott.emp 表中的 deptno 列和 sal 列建立复合索引 deptno_sal_index; SQL> CREATE INDEX deptno_sal_index ON scott.emp(deptno, sal); 第 39 页

40 使用所创建的复合索引 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 页

41 上述两条语句仅仅在 Where 子句中指定的列的顺序不同, 并不影响查询结果, 但是却会影响查询效率 ; 合理的查询语句应该是上述第一条 Select 语句, 即保持 Where 子句中列的顺序与复合索引中列的顺序一致第 41 页

42 5 监视索引是否被使用不必要的索引会对表的查询效率起负作用, 所以实际应用中应该经常检查索引是否被使用, 这需要用到索引的监视功能 ; 监视索引后, 可以通过数据字典视图来了解索引的使用状态, 如果确定索引不再需要使用, 可以删除该索引第 42 页

43 例 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 第 44 页

45 小结 SQL 语句优化概述 SQL 语句优化技巧 ( 顺序 ) 其他优化 ( 连接索引 ) 第 45 页

46 再见第 46 页

单元四数据的查询数据库原理与应用课内例题任务 5 多表查询课内例题例创建数据表 orders, 并向表中添加记录首先创建表 orders,sql 语句如下 : CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date d

单元四数据的查询数据库原理与应用课内例题任务 5 多表查询课内例题例创建数据表 orders, 并向表中添加记录首先创建表 orders,sql 语句如下 : CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date d 任务 5 多表查询课内例题例创建数据表 orders, 并向表中添加记录首先创建表 orders,sql 语句如下 : CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL, PRIMARY KEY (o_num) ) ; 插入需要演示的数据,SQL