SQL|Oracle SQL性能优化最常用的40条建议 sql

文章图片
关于Oracle SQL优化的内容，这一篇应该能满足常规大部分的应用优化需求，整整40条优化建议，干货满满。
1. SQL语句执行步骤
语法分析> 语义分析> 视图转换 >表达式转换> 选择优化器 >选择连接方式 >选择连接顺序 >选择数据的搜索路径 >运行“执行计划”。
2. 选用适合的Oracle优化器
RULE（基于规则）、 COST（基于成本）、CHOOSE（选择性）。
3. 访问Table的方式全表扫描
1）全表扫描
全表扫描就是顺序地访问表中每条记录，ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。
2）通过ROWID访问表
ROWID包含了表中记录的物理位置信息，ORACLE采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系，通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。
4. 共享SQL语句

Oracle提供对执行过的SQL语句进行高速缓冲的机制。被解析过并且确定了执行路径的SQL语句存放在SGA的共享池中。
Oracle执行一个SQL语句之前每次先从SGA共享池中查找是否有缓冲的SQL语句，如果有则直接执行该SQL语句。
可以通过适当调整SGA共享池大小来达到提高Oracle执行性能的目的。

5. 选择最有效率的表名顺序

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。
当ORACLE处理多个表时，会运用排序及合并的方式连接它们，并且是从右往左的顺序处理FROM子句。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行排序，然后扫描第二个表(FROM子句中倒数第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
只在基于规则的优化器中有效。

举例：
表 TAB1 16,384 条记录
表 TAB2 1 条记录
/*选择TAB2作为基础表 (最好的方法)*/
SELECT COUNT(*) FROM TAB1,TAB2
/*执行时间0.96秒*/
/*选择TAB1作为基础表 (不佳的方法)*/
SELECT COUNT(*) FROM TAB2,TAB1
/*执行时间26.09秒*/
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。
/*高效的SQL*/
SELECT * FROM LOCATION L, CATEGORY C, EMP E
WHEREE.EMP_NOBETWEEN1000AND2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
将比下列SQL更有效率：

/*低效的SQL*/SELECT * FROM EMP E, LOCATION L, CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000

6. Where子句中的连接顺序
Oracle采用自下而上或自右向左的顺序解析WHERE子句。根据这个原理,表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
/*低效,执行时间156.3秒*/
SELECT Column1,Column2
FROM EMP EWHERE E.SAL > 50000
AND E.JOB = 'MANAGER'AND 25 <
(SELECT COUNT(*) FROM EMP
WHERE MGR = E.EMPNO)
/*高效,执行时间10.6秒*/
SELECT Column1,Column2FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND E.SAL > 50000
AND E.JOB = 'MANAGER'
7. SELECT子句中避免使用“*”

Oracle在解析SQL语句的时候，对于“*”将通过查询数据库字典来将其转换成对应的列名。
如果在Select子句中需要列出所有的Column时，建议列出所有的Column名称，而不是简单的用“*”来替代，这样可以减少多于的数据库查询开销。

8. 减少访问数据库的次数
当执行每条SQL语句时, ORACLE在内部执行了许多工作：解析SQL语句 > 估算索引的利用率 > 绑定变量 > 读数据块等等由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。
9. 整个简单无关联的数据库访问
如果有几个简单的数据库查询语句，你可以把它们整合到一个查询中（即使它们之间没有关系），以减少多于的数据库IO开销。虽然采取这种方法，效率得到提高，但是程序的可读性大大降低，所以还是要权衡之间的利弊。
10. 使用Truncate而非Delete