高效SQL技巧

最新推荐文章于 2024-08-20 07:00:00 发布

ws1530

最新推荐文章于 2024-08-20 07:00:00 发布

阅读量222

点赞数

分类专栏： SQL

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

http://blog.csdn.net/leshami/article/details/7406672

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; --高效的写法
4) 使用DECODE函数来减少处理时间
-->使用decode函数可以避免重复扫描相同的行或重复连接相同的表
select count(*),sum(sal) from emp where deptno=20 and ename like 'SMITH%';
select count(*),sum(sal) from emp where deptno=30 and ename like 'SMITH%';
-->通过使用decode函数一次扫描即可完成所有满足条件记录的处理
SELECT COUNT( 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 ename LIKE 'SMITH%';
类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。
9) 计算记录条数
-->一般的情况下,count(*)比count(1)稍快.如果可以通过索引检索,对索引列的计数是最快的,因为直接扫描索引即可,例如COUNT(EMPNO)
-->实际情况是经测试上述三种情况并无明显差异.
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')
13) 用EXISTS替代IN
在一些基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常
将提高查询的效率.
-->低效:
SELECT *
FROM emp
WHERE sal > 1000
AND deptno IN (SELECT deptno
FROM dept
WHERE loc = 'DALLAS')
-->高效:
SELECT *
FROM emp
WHERE empno > 1000
AND EXISTS
(SELECT 1
FROM dept
WHERE deptno = emp.deptno AND loc = 'DALLAS')
14) 用NOT EXISTS替代NOT IN
在子查询中,NOT IN子句引起一个内部的排序与合并.因此,无论何时NOT IN子句都是最低效的,因为它对子查询中的表执行了一个全表
遍历.为避免该情形,应当将其改写成外部连接(OUTTER JOIN)或适用NOT EXISTS
-->低效:
SELECT *
FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE loc = 'DALLAS');
-->高效:
SELECT e.*
FROM emp e
WHERE NOT EXISTS
(SELECT 1
FROM dept
WHERE deptno = e.deptno AND loc = 'DALLAS');
-->最高效(尽管下面的查询最高效,并不推荐使用,因为列loc使用了不等运算,当表dept数据量较大,且loc列存在索引的话,则此时索引失效)
SELECT e.*
FROM emp e LEFT 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_id AND department_name = 'IT');
-->高效:
SELECT * -->经测试此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果一致
FROM employees e INNER JOIN departments d ON d.department_id = e.department_id
WHERE d.department_name = 'IT';
16) 用EXISTS替换DISTINCT
对于一对多关系表信息查询时(如部门表和雇员表)，应避免在select 子句中使用distinct,而使用exists来替换
-->低效:
SELECT DISTINCT e.department_id, d.department_name
FROM departments d INNER JOIN employees e ON 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) 使用 UNION ALL 替换 UNION(如果有可能的话)
当SQL语句需要UNION两个查询结果集时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。
如果用UNION ALL替代UNION，这样排序就不是必要了。效率就会因此得到提高。
注意：
UNION ALL会输出所有的结果集,而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
ORDER BY 4 DESC;
18) 尽可能避免使用函数，函数会导致更多的 recursive calls

ws1530

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录