提升SQL执行效能的15招秘籍：Oracle SQL语句优化技巧（第二部分）-速发外链网

索引是表的一个属性部分，用于提升检索数据的效率。Oracle运用了一个复杂的自平衡B树结构，通常情况下，通过索引查询数据比全表扫描要快速得多。当Oracle找出执行查询和更新语句的最佳路径时，优化器将使用索引。同样，在连接多个表时使用索引也能提升效率。另一个使用索引的优势是，它提供了主键的唯一性验证。对于那些LONG或LONG RAW数据类型，您几乎可以对任何列进行索引。通常，在大型表中使用索引特别有效。当然，您也会发现在扫描小表时使用索引同样能提升效率。尽管使用索引可以得到查询效率的提升，但我们也要注意到它的代价——索引需要空间来存储，也需要定期维护。每当有记录在表中增减或索引列被修改时，索引本身也会被修改，这意味着每条记录的INSERT、DELETE、UPDATE操作将为此多付出额外的磁盘I/O，因为索引需要额外的存储空间和处理那些不必要的索引反而会使查询响应时间变慢。定期的重构索引是有必要的。

ALTER INDEX REBUILD

用EXISTS替换DISTINCT
当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑到用EXISTS替换DISTINCT，使查询更为迅速，因为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 FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

SQL语句用大写的，因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

在Java代码中尽量少用连接符＋连接字符串。

避免在索引列上使用NOT。通常我们要避免在索引列上使用NOT，因为NOT会产生在和在索引列上使用函数相同的影响。当Oracle碰到NOT时，它就会停止使用索引，转而执行全表扫描。

避免在索引列上使用计算WHERE子句。假如索引列是函数的一部分，优化器将不使用索引而使用全表扫描。

举例：
（低效）SELECT… FROM DEPT WHERE SAL*>;
（高效）SELECT… FROM DEPT WHERE SAL>/;

用>=替代>

（高效）SELECT FROM EMP WHERE DEPTNO>=
（低效）SELECT FROM EMP WHERE DEPTNO>

两者的区别在于前者DBMS将直接跳到第一个DEPT等于的记录，而后者将首先定位到DEPTNO=的记录并且向前扫描到第一个DEPT大于的记录。

用UNION替换OR（适用于索引列）
通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意以上规则只针对多个索引列有效。假如有column没有被索引，查询效率可能会因为您没有选择OR而降低。在下面的例子中，LOC_ID和REGION上都建有索引。

（高效）SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID= UNION SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE REGION= MELBOURNE
（低效）：SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID= OR REGION= MELBOURNE

用IN来替换OR
这是一条简单易记的规则，但是实际的执行效果还须检验。在Oracle中，两者的执行路径似乎是相同的：

（低效）SELECT… FROM LOCATION WHERE LOC_ID= OR LOC_ID= OR LOC_ID=
（高效）SELECT… FROM LOCATION WHERE LOC_IN IN();

避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何能够为空的列。Oracle将无法使用该索引。对于单列索引，假如列包含空值，索引中将不存在此记录。对于复合索引，假如每个列都为空，索引中同样不存在此记录。假如至少有一个列不为空，则记录存在于索引中。举例，假如唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A、B值为( null)，Oracle将不接受下一条具备相同A、B值（ null）的记录（插入）。然而，假如任何的索引列都为空，Oracle将认为整个键值为空，而空不等于空。因此，您能够插入条具备相同键值的记录，当然他们都是空！因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

（低效）（索引失效）：SELECT… FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
（高效）（索引有效）：SELECT… FROM DEPARTMENT WHERE DEPT_CODE>=;

总是使用索引的第一个列
假如索引是建立在多个列上，只有当它的第一个列（leading column）被WHERE子句引用时，优化器才会选择使用该索引。这也是一条简单而重要的规则。当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引。

用UNION ALL替换UNION（假如有可能的话）
当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION ALL的方式被合并，然后在输出最终结果前进行排序。假如用UNION ALL替代UNION，这样排序就不是必要了，效率就会因此得到提高。需要注意的是，UNION ALL将重复输出两个结果集合中相同记录，因此各位还是要从业务需求分析使用UNION ALL的可行性。UNION将对结果集合排序，这个操作会使用到SORT_AREA_SIZE这块内存，对于这块内存的优化也是相当重要的。下面的SQL能够用来查询排序的消耗量。

优化口诀
全值匹配我最爱，最左前缀要遵守，
带头大哥不能死，中间兄弟不能断。
索引列上少计算，范围之后全失效，
LIKE百分写最右，覆盖索引不写星。

LIKE百分写最右，涵盖索引不写星，
非等空值还有or，索引失效应少用。
具体解释见下文实战。
准备
1.构建test表
drop table if exists test;create table test(id int primary key auto_increment,c1 varchar(10),c2 varchar(10),c3 varchar(10),c4 varchar(10),c5 varchar(10)) ENGINE=INNODB default CHARSET=utf8;insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
2.创建索引
此时索引数据结构如下：
分析Case索引使用情况
Case 1：
分析：
创建复合索引的顺序为c1,c2,c3,c4。
上述四组explain执行的结果都一致：type=ref，key_len=132，ref=const,const,const,const。
结论：在执行常量等值查询时，调整索引列的顺序并不会改变explain的执行结果，因为mysql底层优化器会进行优化，但建议按照索引顺序列编写sql语句。
Case 2：
分析：当出现范围的时候，type=range，key_len=99，比不用范围key_len=66增加了，说明使用上了索引，但对比Case1中执行结果，说明c4上索引失效。
结论：范围右边索引列失效，但是范围当前位置(c3)的索引是有效的，从key_len=99可证明。
数据结构分析：当索引走了>c3后，此时在索引树形结构中c4已经无序了，没有办法走索引。
Case 2.1：
分析：与上面explain执行结果对比，key_len=132说明索引用到了4个，因为对此sql语句mysql底层优化器会进行优化：范围右边索引列失效(c4右边已经没有索引列了)，注意索引的顺序(c1,c2,c3,c4)，所以c4右边不会出现失效的索引列，因此4个索引全部用上。
结论：范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效，因为c3范围查询之后，c4索引数将不是有序，c4当然失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引，c4范围查询后，不影响索引数的顺序。
Case 2.2：
分析：如果在c1处使用范围，则type=ALL，key=Null，索引失效，全表扫描，这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询。
解决方式是使用覆盖索引。
结论：在最佳左前缀法则中，如果最左前列(带头大哥)的索引失效，则后面的索引都失效。
Case 3：
分析：
利用最佳左前缀法则：中间兄弟不能断，因此用到了c1和c2索引(查找)，从key_len=66，ref=const,const，c3索引列用在排序过程中。
Case 3.1：
分析：从explain的执行结果来看：key_len=66，ref=const,const，从而查找只用到c1和c2索引，c3索引用于排序。
Case 3.2：
分析：从explain的执行结果来看：key_len=66，ref=const,const，查询使用了c1和c2索引，由于用了c4进行排序，跳过了c3，出现了Using filesort。
Case 4：
分析：查找只用到索引c1，c2和c3用于排序，无Using filesort。
Case 4.1：
分析：和Case 4中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了。
Case 4.2：
分析：在查询时增加了c5，但是explain的执行结果一样，因为c5并未创建索引。
Case 4.3：
分析：与Case 4.1对比，在Extra中并未出现Using filesort，因为c2为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。
Case 5：
分析：只用到c1上的索引，因为c4中间间断了，根据最佳左前缀法则，所以key_len=33，ref=const，表示只用到一个索引。
Case 5.1：
分析：对比Case 5，在group by时交换了c2和c3的位置，结果出现Using temporary和Using filesort，极度恶劣。原因：c3和c2与索引创建顺序相反。
Case 6：
分析：
在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。
使用c1进行排序，出现了Using filesort。
解决方法：使用覆盖索引。
Case 7：
分析：虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。
Case 8：
分析：对于排序来说，多个相等条件也是范围查询。
总结
MySQL支持两种排序方式：filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
order by满足两种情况会使用Using index。
order by语句使用索引最左前列。
使用where子句与order by子句条件列组合满足索引最左前列。
尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最佳左前缀法则。
如果order by的条件不在索引列上，就会产生Using filesort。
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。注意where高于having，能写在where中的限定条件就不要去having限定了。