SQL语句优化

要点

1. 掌握SELECT子句的优化
2. 掌握WHERE子句的优化
3. 了解COMMIT语句的作用
4. 熟练掌握使用表连接代替多个查询
5. 了解EXISTS操作符的使用
6. 理解”<=”代替”<”
7. 熟练掌握表的连接优化
8. 掌握索引的优化
9. 语句该大写就大写：因为最终还是大写的

---

一般的SQL优化技巧

SELECT语句中避免使用”*”

当一条SQL语句从客户端进程传递到服务器端进程后，Oracle需要执行如下步骤：
1、 在共享池中搜索SQL语句是否已经存在
2、 验证SQL语句的语法是否正确
3、 执行数据字典来验证表和列的定义
4、 获取对象的分析锁，以便在语句的分析过程中对象的定义不会改变
5、 检查用户是否具有相应的操作权限
6、 确定语句的最佳执行计划
7、 将语句和执行方案保存到共享的SQL区

Oracle需要通过数据字典将语句的”*”转换成表中的所有列名，然后再执行查询操作，自然比直接使用列名话费更多的时间
如果再次执行语句(带”*”)，会发现执行时间减少。这是因为所执行的语句被暂时保存在共享池中，Oracle会重用已解析过的语句的执行计划和优化方案，因此执行时间减少了

使用WHERE子句代替HAVING子句

在SELECT语句中，使用WHERE子句过滤行，使用HAVING子句过滤分组，也就是在行分组之后才执行过滤

顺便提一下：先FROM，再WHERE，再GROUP BY，再HAVING，再ORDER BY，再SELECT

因为行被分组需要一定的时间，所以应该尽量使用WHERE子句过滤行，减少分组的行数，也就减少了分组时间

SELECT deptno,avg(sal) FROM scott.emp GROUP BY deptno HAVING BY deptno > 10;SELECT deptno,avg(sal) FROM scott.emp WHERE deptno > 10 GROUP BY deptno;

HAVAING子句会在检索出所有记录之后才对结果集进行过滤；使用WHERE子句就会减少这方面的开销。

HAVING子句一般用于对一些集合函数执行结果的过滤，如COUNT()、AVG()等。除此之外，一般的检索条件应该写在WHERE子句中

使用TRUNCATE代替DELETE

删除表中的数据可以使用TRUNCATE，也可以使用DELETE语句
其中，使用DELETE删除表中的数据时，Oracle会对数据逐行删除，并且使用回滚来记录删除操作，如果用户在没有使用COMMIT提交之前使用ROLLBACK命令进行回滚操作，则Oracle会将表中的数据恢复到删除之前的状态；
使用TRUNCATE语句删除表中的所有数据行时，Oracle不会在撤销表空间中记录删除操作，这就提高了语句的执行速度。而且这种删除是一次性的，也就是执行一次TRUNCATE语句，所有的数据行是在同一时间被删除。

如果确定要删除表中的所有行，建议使用TRUNCATE语句。

DELETE FROM test (WHERE xxx);TRUNCATE TABLE test;

在确保完整性的情况下多用COMMIT语句

当用户执行DML操作后，如果不适用COMMIT命令进行提交，则Oracle会在回滚段中记录DML操作，以便于用户使用ROLLBACK命令对数据进行恢复。Oracle实现这种数据回滚功能，需要花费相应的时间与空间资源。所以，在确保数据完整性的情况下，尽量及时得使用COMMIT命令对DML操作进行提交。

使用COMMIT命令后，系统将释放回滚段上记录的DML操作信息、被程序语句获得的锁、Redo Log Buffe中的空间以及Oracle系统管理前面3中资源所需要的其他开销

使用表连接而不是多个查询

一般说，从多个相关表中检索数据时，执行表连接比使用多个查询的效率更高。在执行每条查询语句时，Oracle内部执行了许多工作–解析SQL语句、估算索引的利用率、绑定变量，以及读取数据块等。因此，要尽量减少访问SQL语句的执行次数。
尽量减少表的查询次数，主要是指可以使用一次查询获得的数据，尽量不要通过两次或更多次的查询获得。

在SELECT语句中嵌套子查询：
SELECT empno,ename,deptno FROM emp WHERE deptno(SELECT deptno FROM dept WHERE dname=’ACCOUNTING’);

在SELECT语句中使用表的连接：
SELECT e.empno, e.ename, d.deptno FROM emp e, INNER JOIN dept d ON e.deptno=d.deptno WHERE d.dname=’ACCOUNTING’;

在表连接时必须选择连接顺序 ，将行较少的表连接到后面。例如，要连接3个相关表table1、table2、table3，假设表table1有10000条记录，表table2有1000条记录，表table3有100条记录。那么首先应该将表table3连接到表table2上，接着是把表table2连接到表table1上

越往后面做的越少

使用EXISTS代替IN

IN操作符用于检查一个值是否包含在列表中。EXISTS与IN不同，EXISTS只检查行的存在性，而IN检查实际的值。在子查询中，EXISTS提供的性能通常比IN提供的性能要好。
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE loc=’NEW YORK’);

SELECT * FROM emp WHERE EXISTS(SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND loc='NEW YORK');

使用EXISTS代替DISTINCT

在连接查询的SELECT语句中，DISTINCT关键字用于禁止重复行的显示；EXISTS用于检查子查询返回的行的存在性。尽量使用EXISTS代替DISTINCT，因为DISTINCT在禁止重复行显示之前要排序检索到的行
SELECT DISTINCT e.deptno, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;

SELECT d.deptno, d.dname FROM dept d WHERE EXISTS(SELECT 1 FROM emp e WHERE d.deptno=e.deptno);

Oracle将在子查询的条件满足后，立刻返回结果。

使用”<=”代替”<”

在检索条件的子句中，经常使用运算符”<=”和”<”，其中，前者用来表示小于等于某个值，后者用来表示小于某个值。很多时候，这两个比较符可以替换使用，但是检索效率是不一样的，建议使用”<=”代替”<”的使用。
这两个运算符的区别在于：如果使用”deptno<7901”，则Oracle会定位到7901，然后再去寻找比7901小的数据；如果使用”deptno<=7900”，则Oracle会直接定位到等于7900的数。虽然这种优化显得区别不大，但是在查询的数据量较大，尤其是在循环语句中使用这两个比较操作符时，区别会是很明显的。

使用完全限定的列引用

在查询中包含多个表时，为每个表指定表别名，并且为所引用的每列都显式地指定合适的别名，这称为完全限定的列引用。这样，数据库不需要查询所操作的表中包含了哪些的列，也就减少了解析列的时间，以及由列歧义引起的语法错徐。

列歧义是指SQL语句中不同的表具有相同的列名，当SQL语句中出现这个列时，SQL解析器无法判断这个列属于哪个表

SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e, dept d;

表的连接方法

在连接查询时，需要操作多个表，处理不好表之间的连接关系将会影响查询效率，所以对表进行连接查询时，更应该注意SQL语句的优化。

FROM子句中表的顺序

在SELECT语句的FROM子句中，可以指定多个表的名称。至于表与表之间的先后顺序，如果从查询结果来说，哪个表放前面都是一样的，但从查询效率来考虑，表之间的顺序是不能随意的。
一般来说，Oracle的解析器在助理FROM子句中的表时，是按照从右到左的顺序。也就是说，FROM子句中最后指定的表将被Oracle首先处理，Oracle将它作为驱动表，并对该表的数据进行排序；之后再扫描倒数第二个表；最后将所有从第二个表中检索出来的记录与第一个表中的合适记录进行合并。
因此，建议在使用表的连接查询时，选择记录行数少的表作为驱动表，也就是将它作为FROM子句中的最后一个表。例如，要使用两个相关表table1和table2，其中，表table1有10000行记录，表table2有100行记录。那么在FROM子句中，首先指定表table1，接着是表table2。

WHERE子句的连接顺序

在执行查询的WHERE子句中，可以指定多个检索条件。Oracle采用自右之左的顺序解析WHERE子句，根据这个顺序，表之间的连接应该写在其他WHERE条件之间，将可以过滤掉最大数量记录的条件写在WHERE子句的末尾
– 这里写”*”是为了方便
SELECT e.empno,e.ename FROM emp e WHERE e.sal>=999 AND e.job=’MANAGER’ AND 10<(SELECT COUNT(ename) FROM emp WHERE mgr=empno);

SELECT * FROM emp e WHERE 10<(SELECT COUNT(*) FROM emp WHERE mgr=empno) AND sal>=999 AND job='MANAGER';

---

有效使用索引

索引是表的一个概念部分，用来提高检索数据的效率。通常，使用索引查询数据比全表扫描要快的多。当Oracle查找执行查询和UPDATE语句的最佳路径时，Oracle优化器将使用索引，同样在连接多个表时使用索引也可以提高效率。
虽然使用索引能提高查询效率，但是也必须注意使用索引所付出的代价。索引需要空间来存储；需要定期维护；每当有记录增减或索引列被修改时，索引本身也会被修改。这意味着针对每条记录的INSERT、UPDATE的DELETE操作，都需要更多的磁盘I/O。因为索引需要额外的存储空间和处理操作，所以那些不必要的索引反而会影响查询效率。因此，有效使用索引是很有必要的。

常见索引的基本原则

创建索引时，需要对相应的表认真分析，主要从以下几个原则进行考虑：
* 对于经常以查询关键字为基础的表，并且该表的数据行是均匀分布的
* 以查询关键字为基础，表中的数据行随机排序
* 表中包含的列数相对比较少
* 表中的大多数查询都包含相对简单的WHERE子句

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

注意：除了所查询的表没有索引，或者需要返回表中的所有行时，Oracle会进行全表扫描之外，如果对索引列使用了函数或操作符(例如LIKE)，Oracle同样会对全表进行扫描

索引列上所使用的操作符

对索引列的操作语句应该尽量避免”非”操作符的使用，例如NOT、!=、<>、!<等，”非”操作符的使用会造成Oracle对表执行全表扫描。
实际上，索引的作用是快速告诉用户在表中有什么数据，而不能用来告诉用户在表中没有什么数据。

另外，使用LIKE操作符可以应用通配符查询，但是如果用的不好，会产生性能上的问题。如LIKE’%4332%’不会被使用到索引，而LIKE’4332%’则会引用范围索引。因为第一个字符为通配符时，索引不在起作用。

避免对唯一索引列使用NULL值

使用UNIQUE关键字可以为列添加唯一索引，也就是说列的值不允许有重复值，但是，多个NULL值却可以同时存在，因为Oracle认为两个空值是不相等的。
向包含唯一索引的表中添加数据时，可以添加无数条NULL值的记录，但是由于这些记录都是空值，所以在索引中并不存在这些记录。因此，在WHERE子句中使用IS NULL或IS NOT NULL，对唯一索引列进行空值比较时，Oracle将停止使用该列上的唯一索引，导致Oracle进行全表扫描。

列中包含有NULL值的行都不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以设计数据库时尽量不要让字段的默认值为NULL。

选择复合索引主列

索引不仅可以基于单独的列，还可以基于多个列，在多个列上创建的索引叫复合索引。创建复合索引时，不可避免的面对多个列的前后顺序，这个顺序并不是随意的，它会影响索引的使用效率。
创建复合索引时，应该按照如下原则：
* 选择经常在WHERE子句中使用、并且由AND操作符连接的列作为复合索引列
* 选择WHERE子句中使用频率相对较高的列排在最前面，或者根据需要为其他列创建单独的索引

CREATE INDEX deptno_sal_index ON emp(deptno, sal);SELECT empno, ename, sal, deptno FROM emp WHERE deptno>=9 AND sal>=1999;SELECT empno, ename, sal, deptno FROM emp WHERE sal>=1999 AND deptno>=9;

上面两条语句并不影响查询结果，但是却会影响查询效率。合理的查询语句是第一条。

只有当复合索引中的第一列被WHERE子句使用时，Oracle才会使用该复合索引。例如，如果在WHERE子句中只使用了sal列，则Oracle不会使用上面创建的符合索引deptno_sal_index。

监视索引是否被使用

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

-- 监视上面创建的deptno_sal_index索引ALTER INDEX deptno_sal_index MONITORING USAGE;--通过v$object_usage视图，查看deptno_sal_index索引的使用状态SELECT table_name, index_name, monitoring FROM v$object_usage; --MONITORING字段表示索引是否处于激活状态，值为YES，表示处于激活状态。--删除索引DROP INDEX name_score_index;