如何写出性能优良的 SQL？（如何优化 SQL）

本文主要是介绍如何写出性能优良的 SQL？（如何优化 SQL），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1. 选择最有效率的表名顺序 ( 只在基于规则的优化器中有效 )

ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名， FROM 子句中写在最后的表(基础表 driving table)将被最先处理，在 FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有 3 个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

2. WHERE 子句中的连接顺序
ORACLE 采用自下而上的顺序解析 WHERE 子句 , 根据这个原理 , 表之间的连接必须写在其他WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。

3. SELECT 子句中避免使用 *

ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

4. 减少访问数据库的次数
ORACLE 在内部执行了许多工作 : 解析 SQL 语句，估算索引的利用率，绑定变量，读数据块等；

5. 使用 DECODE 函数来减少处理时间

使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表。

6. 整合简单,无关联的数据库访问

如果你有几个简单的数据库查询语句 , 你可以把它们整合到一个查询中 ( 即使它们之间没有关系)

7. 删除重复记录
最高效的删除重复记录方法 ( 因为使用了 ROWID) 例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

8. 用 TRUNCATE 替代 DELETE
当删除表中的记录时 , 在通常情况下 , 回滚段 (rollback segments ) 用来存放可以被恢复的信息. 如果你没有 COMMIT 事务,ORACLE 会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用 TRUNCATE 时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。 (TRUNCATE 只在删除全表适用,TRUNCATE 是 DDL 不是 DML)

9. 存储过程中尽量多使用 COMMIT
只要有可能 , 在程序中尽量多使用 COMMIT ，这样程序的性能得到提高，内存需求也会因为 COMMIT 所释放的资源而减少。

10. 用 where 子句替换 HAVING 子句
避免使用 HAVING 子句， HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过 WHERE 子句限制记录的数目，那就能减少这方面的开销。

11. 减少对表的查询
在含有子查询的 SQL 语句中 , 要特别注意减少对表的查询 . 例子：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) =

(SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION=604)

12. 通过内部函数提高 SQL 效率
复杂的 SQL 往往牺牲了执行效率。能够掌握 Oracle 内置函数解决问题的方法在实际工作中是非常有意义的。

13. 使用表的别名 (Alias)
当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

14. 用 EXISTS 替代 IN 、用 NOT EXISTS 替代 NOT IN
在许多基于基础表的查询中 , 为了满足一个条件 , 往往需要对另一个表进行联接。在这种情况下，使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率。在子查询中， NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下， NOT IN 都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用 NOT IN ,我们可以把它改写成外连接(Outer Joins)或 NOT EXISTS。
例子：
（高效） SELECT * FROM EMP ( 基础表 ) WHERE EMPNO > 0 AND EXISTS (SELECT
'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')
( 低效 )SELECT * FROM EMP ( 基础表 ) WHERE EMPNO > 0 AND DEPTNO IN(SELECT

DEPTNO FROM DEPT WHERE LOC = 'MELB')

15. 识别 ' 低效执行 ' 的 SQL 语句
虽然目前各种关于 SQL 优化的图形化工具层出不穷 , 但是写出自己的 SQL 工具来解决问题始终是一个最好的方法：
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
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.8
ORDER BY 4 DESC;
16. 用索引提高效率

索引是表的一个概念部分，用来提高检索数据的效率， ORACLE 使用了一个复杂的自平衡B-tree 结构。通常，通过索引查询数据比全表扫描要快。当 ORACLE 找出执行查询和Update 语句的最佳路径时， ORACLE 优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。那些 LONG 或 LONG RAW 数据类型，你可以索引几乎所有的列。通常，在大型表中使用索引特别有效。当然，你也会发现，在扫描小表时，使用索引同样能提高效率。

虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的 INSERT 、 DELETE 、 UPDATE 将为此多付出 4、 5 次的磁盘 I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

17. 用 EXISTS 替换 DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在 SELECT 子句中使用 DISTINCT. 一般可以考虑用 EXIST 替换, EXISTS 使查询更为迅速,因为 RDBMS 核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：

(低效):SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE
D.DEPT_NO = E.DEPT_NO
(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X'