高效SQL语句必杀技

最新推荐文章于 2024-09-14 18:42:05 发布

黑眼睛

最新推荐文章于 2024-09-14 18:42:05 发布

阅读量261

点赞数

分类专栏：数据库 .Net 文章标签： sql buffer 数据库 manager disk oracle

.Net 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

No SQL,No cost. SQL语句是造成数据库开销最大的部分。而不良SQL写法直接导致数据库系统性能下降的情形比比皆是。那么如何才能称得
上高效的SQL语句呢？一是查询优化器为当前的SQL语句生成最佳的执行计划，保证数据读写使用最佳路径；二是设置合理的物理存储结构，如表
的类型，字段的顺序，字段的数据类型等。本文主要描述如何编写高效的SQL语句并给出示例。下面的描述主要分为三个部分，一是编写高效SQL
语句，二是使用索引提高查询性能的部分，三是总结部分。

一、编写高效SQL语句

[sql] view plain copy print ?

1) 选择最有效的表名顺序(仅适用于RBO模式)
ORACLE的解析器总是按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中最后的一个表将作为驱动表被优先处理。当FROM子句
存在多个表的时候，应当考虑将表上记录最少的那个表置于FROM的最右端作为基表。Oracle会首先扫描基表(FROM子句中最后的那个表)并对
记录进行排序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。如
果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。
下面的例子使用最常见的scott或hr模式下的表进行演示
表 EMP 有14条记录
表 DEPT 有4条记录
SELECT /*+rule */COUNT( * ) FROM emp, dept; --高效的写法
scott@CNMMBO> set autotrace traceonly stat;
scott@CNMMBO> SELECT /*+rule */COUNT( * ) FROM emp, dept;
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SELECT /*+rule */COUNT( * ) FROM dept, emp; --低效的写法
scott@CNMMBO> SELECT /*+rule */COUNT( * ) FROM dept, emp;
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
105 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2) select 查询中避免使用'*'
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用'*' 是一个方便的方法.不幸的是,这是一个非常低效的方法.实际
上，ORACLE在解析的过程中，会将 '*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
注：本文中的例子出于简化演示而使用了select * ,生产环境应避免使用.
3) 减少访问数据库的次数
每当执行一条SQL语句,Oracle 需要完成大量的内部操作,象解析SQL语句,估算索引的利用率,绑定变量, 读数据块等等.由此可
见，减少访问数据库的次数，实际上是降低了数据库系统开销
-->下面通过3种方式来获得雇员编号为7788与7902的相关信息
-->方式 1 (最低效):
select ename,job,salfrom empwhere empno=7788;
select ename,job,salfrom empwhere empno=7902;
-->方式 2 (次低效):
-->下面使用了参数游标来完成，每传递一次参数则需要对表emp访问一次，增加了I/O
DECLARE
CURSOR C1(E_NO NUMBER) IS
SELECT ename, job, sal
FROM emp
WHERE empno = E_NO;
BEGIN
OPEN C1 (7788);
FETCH C1INTO …, …, …;
..
OPEN C1 (7902);
FETCH C1INTO …, …, …;
CLOSE C1;
END;
-->方式 3 (最高效)
SELECT a.ename
, a.job
, a.sal
, b.ename
, b.job
, b.sal
FROM emp a, emp b
WHERE a.empno = 7788OR b.empno = 7902;
注意：在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200.
4) 使用DECODE函数来减少处理时间
-->使用decode函数可以避免重复扫描相同的行或重复连接相同的表
selectcount(*),sum(sal)from empwhere deptno=20and enamelike 'SMITH%';
selectcount(*),sum(sal)from empwhere deptno=30and enamelike 'SMITH%';
-->通过使用decode函数一次扫描即可完成所有满足条件记录的处理
SELECTCOUNT( DECODE( deptno, 20,'x', NULL ) ) d20_count
, COUNT( DECODE( deptno, 30,'x', NULL ) ) d30_count
, SUM( DECODE( deptno, 20, sal,NULL ) ) d20_sal
, SUM( DECODE( deptno, 30, sal,NULL ) ) d30_sal
FROM emp
WHERE enameLIKE 'SMITH%';
类似的，DECODE函数也可以运用于GROUPBY 和ORDERBY子句中。
5) 整合简单，无关联的数据库访问
-->如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中以提高性能(即使它们之间没有关系)
-->整合前
SELECTname
FROM emp
WHERE empno = 1234;
SELECTname
FROM dept
WHERE deptno = 10;
SELECTname
FROM cat
WHERE cat_type ='RD';
-->整合后
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.cat_type(+) ='RD';
-->从上面的SQL语句可以看出，尽管三条语句被整合为一条，性能得以提高，然可读性差，此时应权衡性能与代价
6) 删除重复记录
-->通过使用rowid来作为过滤条件，性能高效
DELETEFROM emp e
WHERE e.ROWID > (SELECTMIN( x.ROWID )
FROM emp x
WHERE x.empno = e.empno);
7) 使用truncate 代替delete
-->通常情况下,任意记录的删除需要在回滚段构造删除前镜像以实现回滚(rollback).对于未提交的数据在执行rollback之后,Oracle会生成
-->等价SQL语句去恢复记录(如delete,则生成对应的insert语句;如insert则生成对应的delete;如update,则是同时生成delete和insert
-->使用truncate命令则是执行DDL命令,不产生任何回滚信息,直接格式化并释放高水位线.故该语句性能高效.由于不能rollback,因此慎用.
8) 尽量多使用COMMIT(COMMIT应确保事务的完整性)
-->只要有可能，在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少
-->COMMIT所释放的资源：
-->1.回滚段上用于恢复数据的信息
-->2.释放语句处理期间所持有的锁
-->3.释放redo log buffer占用的空间(commit将redo log buffer中的entries 写入到联机重做日志文件)
-->4.ORACLE为管理上述3种资源中的内部开销
9) 计算记录条数
-->一般的情况下,count(*)比count(1)稍快.如果可以通过索引检索,对索引列的计数是最快的,因为直接扫描索引即可,例如COUNT(EMPNO)
-->实际情况是经测试上述三种情况并无明显差异.
10) 用Where子句替换HAVING子句
-->尽可能的避免having子句，因为HAVING 子句是对检索出所有记录之后再对结果集进行过滤。这个处理需要排序，总计等操作
-->通过WHERE子句则在分组之前即可过滤不必要的记录数目，从而减少聚合的开销
-->低效:
SELECT deptno,AVG( sal )
FROM emp
GROUPBY deptno
HAVING deptno = 20;
scott@CNMMBO> SELECT deptno,AVG( sal )
2 FROM emp
3 GROUPBY deptno
4 HAVING deptno= 20;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-->高效:
SELECT deptno,AVG( sal )
FROM emp
WHERE deptno = 20
GROUPBY deptno;
scott@CNMMBO> SELECT deptno,AVG( sal )
2 FROM emp
3 WHERE deptno = 20
4 GROUPBY deptno;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
11) 最小化表查询次数
-->在含有子查询的SQL语句中，要特别注意减少对表的查询
-->低效:
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name ='Marketing')
AND manager_id = (SELECT manager_id
FROM departments
WHERE department_name ='Marketing');
-->高效:
SELECT *
FROM employees
WHERE ( department_id, manager_id ) = (SELECT department_id, manager_id
FROM departments
WHERE department_name ='Marketing')
-->类似更新多列的情形
-->低效:
UPDATE employees
SET job_id = (SELECTMAX( job_id )FROM jobs ), salary = (SELECTAVG( min_salary )FROM jobs )
WHERE department_id = 10;
-->高效:
UPDATE employees
SET ( job_id, salary ) = (SELECTMAX( job_id ),AVG( min_salary )FROM jobs )
WHERE department_id = 10;
12) 使用表别名
-->在多表查询时，为所返回列使用表别名作为前缀以减少解析时间以及那些相同列歧义引起的语法错误
13) 用EXISTS替代IN
在一些基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常
将提高查询的效率.
-->低效:
SELECT *
FROM emp
WHERE sal > 1000
AND deptnoIN (SELECT deptno
FROM dept
WHERE loc ='DALLAS')
-->高效:
SELECT *
FROM emp
WHERE empno > 1000
AND EXISTS
(SELECT 1
FROM dept
WHERE deptno = emp.deptnoAND loc ='DALLAS')
14) 用NOT EXISTS替代NOTIN
在子查询中,NOTIN子句引起一个内部的排序与合并.因此,无论何时NOTIN子句都是最低效的,因为它对子查询中的表执行了一个全表
遍历.为避免该情形,应当将其改写成外部连接(OUTTER JOIN)或适用NOT EXISTS
-->低效:
SELECT *
FROM emp
WHERE deptnoNOT IN (SELECT deptno
FROM dept
WHERE loc ='DALLAS');
-->高效:
SELECT e.*
FROM emp e
WHERE NOT EXISTS
(SELECT 1
FROM dept
WHERE deptno = e.deptnoAND loc ='DALLAS');
-->最高效(尽管下面的查询最高效,并不推荐使用,因为列loc使用了不等运算,当表dept数据量较大,且loc列存在索引的话,则此时索引失效)
SELECT e.*
FROM emp eLEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.loc <>'DALLAS'
15) 使用表连接替换EXISTS
一般情况下，使用表连接比EXISTS更高效
-->低效:
SELECT *
FROM employees e
WHERE EXISTS
(SELECT 1
FROM departments
WHERE department_id = e.department_idAND department_name ='IT');
-->高效:
SELECT * -->经测试此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果一致
FROM employees eINNERJOIN departments dON d.department_id = e.department_id
WHERE d.department_name ='IT';
16) 用EXISTS替换DISTINCT
对于一对多关系表信息查询时(如部门表和雇员表)，应避免在select 子句中使用distinct,而使用exists来替换
-->低效:
SELECTDISTINCT e.department_id, department_name
FROM departments dINNERJOIN employees eON d.department_id = e.department_id;
-->高效:
SELECT d.department_id,department_name
from departments d
WHERE EXISTS
(SELECT 1
FROM employees e
WHERE d.department_id=e.department_id);
EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果
-->经测试此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果一致
17) 使用 UNIONALL 替换 UNION(如果有可能的话)
当SQL语句需要UNION两个查询结果集时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。
如果用UNIONALL替代UNION，这样排序就不是必要了。效率就会因此得到提高。
注意：
UNIONALL会输出所有的结果集,而UNION则过滤掉重复记录并对其进行排序.因此在使用时应考虑业务逻辑是否允许当前的结果集存在重复现象
寻找低效的SQL语句
-->下面的语句主要适用于从视图v$sqlarea中获得当前运行下且耗用buffer_gets较多的SQL语句
SELECT executions
, disk_reads
, buffer_gets
, ROUND( ( buffer_gets
- disk_reads )
/ buffer_gets, 2 )
hit_ratio
, ROUND( disk_reads / executions, 2 ) reads_per_run
, sql_text
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND ( buffer_gets
- disk_reads )
/ buffer_gets < 0.80
ORDERBY 4DESC;
18) 尽可能避免使用函数，函数会导致更多的 recursive calls