SQL语句优化之降龙十八掌

落日映苍穹つ 2022-08-07 13:52 165阅读 0赞

# [**sql 语句优化![奋斗][struggle.gif]**][sql _struggle.gif] #

一前言   
客服业务受到SQL语句的影响非常大，在规模比较大的局点，往往因为一个小的SQL语句不够优化，导致数据库性能急剧下降，小型机idle所剩无几，应用服务器断连、超时，严重影响业务的正常运行。因此，称低效的SQL语句为客服业务的‘恶龙’并不过分。数据库的优化方法有很多种，在应用层来说，主要是基于索引的优化。本次秘笈根据实际的工作经验，在研发原来已有的方法的基础上，进行了一些扩充，总结了基于索引的SQL语句优化的降龙十八掌，希望有一天你能用其中一掌来驯服客服业务中横行的‘恶龙’。

二总纲   
l       建立必要的索引   
这次传授的降龙十八掌，总纲只有一句话：建立必要的索引，这就是后面降龙十八掌的内功基础。这一点看似容易实际却很难。难就难在如何判断哪些索引是必要的，哪些又是不必要的。判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。具体到方法上，就必须熟悉数据库应用程序中的所有SQL语句，从中统计出常用的可能对性能有影响的部分SQL，分析、归纳出作为Where条件子句的字段及其组合方式；在这一基础上可以初步判断出哪些表的哪些字段应该建立索引。其次，必须熟悉应用程序。必须了解哪些表是数据操作频繁的表；哪些表经常与其他表进行连接；哪些表中的数据量可能很大；对于数据量大的表，其中各个字段的数据分布情况如何；等等。对于满足以上条件的这些表，必须重点关注，因为在这些表上的索引，将对SQL语句的性能产生举足轻重的影响。不过下面还是总结了一下降龙十八掌内功的入门基础，建立索引常用的规则如下：  
1、表的主键、外键必须有索引；   
2、数据量超过300的表应该有索引；   
3、经常与其他表进行连接的表，在连接字段上应该建立索引；   
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；   
5、索引应该建在选择性高的字段上；   
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；   
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：   
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；   
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；   
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；   
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；   
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；   
8、频繁进行数据操作的表，不要建立太多的索引；   
9、删除无用的索引，避免对执行计划造成负面影响；   
以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

三降龙十八掌

第一掌 避免对列的操作   
任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。   
例1：下列SQL条件语句中的列都建有恰当的索引，但30万行数据情况下执行速度却非常慢：   
select \* from record where  substrb(CardNo,1,4)=’5378′(13秒)    
select \* from record where  amount/30< 1000（11秒）   
select \* from record where  to\_char(ActionTime,’yyyymmdd’)=’19991201′（10秒）   
由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表扫描，因此将SQL重写如下：   
select \* from record where CardNo like  ‘5378%’（< 1秒）   
select \* from record where amount  < 1000\*30（< 1秒）   
select \* from record where ActionTime= to\_date (’19991201′ ,’yyyymmdd’)（< 1秒）   
差别是很明显的！

第二掌避免不必要的类型转换   
需要注意的是，尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to\_number()函数进行转换，从而导致全表扫描。   
例2：表tab1中的列col1是字符型（char)，则以下语句存在类型转换：   
select col1,col2 from tab1 where col1>10，   
应该写为： select col1,col2 from tab1 where col1>’10′。

第三掌增加查询的范围限制   
增加查询的范围限制，避免全范围的搜索。   
例3：以下查询表record 中时间ActionTime小于2001年3月1日的数据：   
select \* from record where ActionTime < to\_date (’20010301′ ,’yyyymm’)   
查询计划表明，上面的查询对表进行全表扫描，如果我们知道表中的最早的数据为2001年1月1日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。修改如下： select \* from record where   
ActionTime < to\_date (’20010301′ ,’yyyymm’)   
and   ActionTime > to\_date (’20010101′ ,’yyyymm’)   
后一种SQL语句将利用上ActionTime字段上的索引，从而提高查询效率。把‘20010301′换成一个变量，根据取值的机率，可以有一半以上的机会提高效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

第四掌尽量去掉“IN”、“OR”   
含有“IN”、“OR”的Where子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。   
例4： select count(\*) from stuff where id\_noin(’0′,’1′)（23秒）   
可以考虑将or子句分开：   
select count(\*) from stuff where id\_no=’0′    
select count(\*) from stuff where id\_no=’1′   
然后再做一个简单的加法，与原来的SQL语句相比，查询速度更快。

第五掌尽量去掉“<>”   
尽量去掉 “<>”，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为“OR”方式。   
例5：   
UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;   
以上语句由于其中包含了“<>”，执行计划中用了全表扫描（TABLE ACCESS FULL），没有用到state字段上的索引。实际应用中，由于业务逻辑的限制，字段state为枚举值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉“<>”，利用索引来提高效率。   
修改为：UPDATE SERVICEINFO SET STATE=0  WHERE STATE = 1 OR STATE = 2 。进一步的修改可以参考第4种方法。

第六掌去掉Where子句中的IS NULL和IS NOT NULL   
Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where子句中的IS NULL和IS NOT NULL。

第七掌索引提高数据分布不均匀时查询效率   
索引的选择性低，但数据的值分布差异很大时，仍然可以利用索引提高效率。A、数据分布不均匀的特殊情况下，选择性不高的索引也要创建。   
表ServiceInfo中数据量很大，假设有一百万行，其中有一个字段DisposalCourseFlag，取值范围为枚举值：\[0，1，2，3，4，5，6，7\]。按照前面说的索引建立的规则，“选择性不高的字段不应该建立索引，该字段只有8种取值，索引值的重复率很高，索引选择性明显很低，因此不建索引。然而，由于该字段上数据值的分布情况非常特殊，具体如下表：   
取值范围1~567   
占总数据量的百分比1%98%1%

而且，常用的查询中，查询DisposalCourseFlag<6的情况既多又频繁，毫无疑问，如果能够建立索引，并且被应用，那么将大大提高这种情况的查询效率。因此，我们需要在该字段上建立索引。

第八掌利用HINT强制指定索引   
在ORACLE优化器无法用上合理索引的情况下，利用HINT强制指定索引。   
继续上面7的例子，ORACLE缺省认定，表中列的值是在所有数据行中均匀分布的，也就是说，在一百万数据量下，每种DisposalCourseFlag值各有12.5万数据行与之对应。假设SQL搜索条件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE因此对索引“视而不见”，从而在查询路径的选择中，用其他字段上的索引甚至全表扫描。根据我们上面的分析，数据值的分布很特殊，严重的不均匀。为了利用索引提高效率，此时，一方面可以单独对该字段或该表用analyze语句进行分析，对该列搜集足够的统计数据，使ORACLE在查询选择性较高的值时能用上索引；另一方面，可以利用HINT提示，在SELECT关键字后面，加上“/\*+ INDEX(表名称，索引名称）\*/”的方式，强制ORACLE优化器用上该索引。   
比如： select \* from  serviceinfo where DisposalCourseFlag=1 ;   
上面的语句，实际执行中ORACLE用了全表扫描，加上蓝色提示部分后，用到索引查询。如下：   
select /\*+  INDEX(SERVICEINFO,IX\_S\_DISPOSALCOURSEFLAG)  \*/ \*   
from  serviceinfo where DisposalCourseFlag=1;   
请注意，这种方法会加大代码维护的难度，而且该字段上索引的名称被改变之后，必须要同步所有指定索引的HINT代码，否则HINT提示将被ORACLE忽略掉。

第九掌 屏蔽无用索引   
继续上面8的例子，由于实际查询中，还有涉及到DisposalCourseFlag=6的查询，而此时如果用上该字段上的索引，将是非常不明智的，效率也极低。因此这种情况下，我们需要用特殊的方法屏蔽该索引，以便ORACLE选择其他字段上的索引。比如，如果字段为数值型的就在表达式的字段名后，添加“+ 0”，为字符型的就并上空串：“||”"”   
如： select \* from  serviceinfo whereDisposalCourseFlag+ 0 = 6 and workNo =  ‘36′ 。   
不过，不要把该用的索引屏蔽掉了，否则同样会产生低效率的全表扫描。

第十掌分解复杂查询，用常量代替变量   
对于复杂的Where条件组合，Where中含有多个带索引的字段，考虑用IF语句分情况进行讨论；同时，去掉不必要的外来参数条件，减低复杂度，以便在不同情况下用不同字段上的索引。   
继续上面9的例子，对于包含   
Where (DisposalCourseFlag < v\_DisPosalCourseFlag) or (v\_DisPosalCourseFlagis null) and ….的查询，(这里v\_DisPosalCourseFlag为一个输入变量，取值范围可能为\[NULL，0，1，2，3，4，5，6，7\])，可以考虑分情况用IF语句进行讨论，类似：   
IF v\_DisPosalCourseFlag =1 THEN   
Where DisposalCourseFlag = 1 and ….   
ELSIF v\_DisPosalCourseFlag =2 THEN   
Where DisposalCourseFlag = 2 and ….    
。。。。。。

第十一掌 like子句尽量前端匹配   
因为like参数使用的非常频繁，因此如果能够对like子句使用索引，将很高的提高查询的效率。   
例6：select \* from city where name like ‘%S%’   
以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），如果能够修改为：   
select \* from city where name like ‘S%’   
那么查询的执行计划将会变成（INDEX RANGE SCAN），成功的利用了name字段的索引。这意味着Oracle SQL优化器会识别出用于索引的like子句，只要该查询的匹配端是具体值。因此我们在做like查询时，应该尽量使查询的匹配端是具体值，即使用like ‘S%’。

第十二掌用Case语句合并多重扫描   
我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询：   
例8：1）select count(\*) from emp where sal<1000;   
     2）select count(\*)from emp where sal between 1000 and 5000;   
     3）select count(\*)from emp where sal>5000;   
这样我们需要进行三次全表查询，但是如果我们使用case语句：   
select   
count (sale when sal <1000   
then 1 else nullend)             count\_poor,   
count (sale when between 1000 and 5000   
then 1 else nullend)             count\_blue\_collar,   
count (sale when sal >5000   
then 1 else nullend)             count\_poor   
from emp;   
这样查询的结果一样，但是执行计划只进行了一次全表查询。

第十三掌使用nls\_date\_format   
例9：   
select \* from record where  to\_char(ActionTime,’mm’)=’12′   
这个查询的执行计划将是全表查询，如果我们改变nls\_date\_format，   
SQL>alert session set nls\_date\_formate=’MM’;   
现在重新修改上面的查询：   
select \* from record where  ActionTime=’12′   
这样就能使用actiontime上的索引了，它的执行计划将是（INDEX RANGE SCAN）。   
第十四掌使用基于函数的索引   
前面谈到任何对列的操作都可能导致全表扫描，例如：   
select \* from emp where substr(ename,1,2)=’SM’;   
但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的基于函数的索引，   
create index emp\_ename\_substr on eemp ( substr(ename,1,2) );   
这样在执行上面的查询语句时，这个基于函数的索引将排上用场，执行计划将是（INDEX RANGE SCAN）。

第十五掌基于函数的索引要求等式匹配   
上面的例子中，我们创建了基于函数的索引，但是如果执行下面的查询：   
select \* from emp where substr(ename,1,1)=’S’   
得到的执行计划将还是（TABLE ACCESS FULL），因为只有当数据列能够等式匹配时，基于函数的索引才能生效，这样对于这种索引的计划和维护的要求都很高。请注意，向表中添加索引是非常危险的操作，因为这将导致许多查询执行计划的变更。然而，如果我们使用基于函数的索引就不会产生这样的问题，因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引。

第十六掌使用分区索引   
在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。   
例如，假设你已经定义了一个分区索引，并且某个SQL语句需要在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。

第十七掌使用位图索引   
位图索引可以从本质上提高使用了小于1000个唯一数据值的数据列的查询速度，因为在位图索引中进行的检索是在RAM中完成的，而且也总是比传统的B树索引的速度要快。对于那些少于1000个唯一数据值的数据列建立位图索引，可以使执行效率更快。

第十八掌决定使用全表扫描还是使用索引   
和所有的秘笈一样，最后一招都会又回到起点，最后我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10％的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40％时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：   
1）对于原始排序的表仅读取少于表记录数40％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。   
2）对于未排序的表仅读取少于表记录数7％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。

四总结   
以上的招式，是完全可以相互结合同时运用的。而且各种方法之间相互影响，紧密联系。这种联系既存在一致性，也可能带来冲突，当冲突发生时，需要根据实际情况进行选择，没有固定的模式。最后决定SQL优化功力的因素就是对ORACLE内功的掌握程度了。   
另外，值得注意的是：随着时间的推移和数据的累计与变化，ORACLE对SQL语句的执行计划也会改变，比如：基于代价的优化方法，随着数据量的增大，优化器可能错误的不选择索引而采用全表扫描。这种情况可能是因为统计信息已经过时，在数据量变化很大后没有及时分析表；但如果对表进行分析之后，仍然没有用上合理的索引，那么就有必要对SQL语句用HINT提示，强制用合理的索引。但这种HINT提示也不能滥用，因为这种方法过于复杂，缺乏通用性和应变能力，同时也增加了维护上的代价；相对来说，基于函数右移、去掉“IN ，OR  ，<>，IS NOT NULL ”、分解复杂的SQL语句等等方法，却是“放之四海皆准”的，可以放心大胆的使用。   
同时，优化也不是“一劳永逸”的，必须随着情况的改变进行相应的调整。当数据库设计发生变化，包括更改表结构：字段和索引的增加、删除或改名等；业务逻辑发生变化：如查询方式、取值范围发生改变等等。在这种情况下，也必须对原有的优化进行调整，以适应效率上的需求

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：   
ORACLE 的解析器按照从右到左的顺序处理FROM 子句中的表名，FROM 子句中写在最后的表   
(基础表driving table)将被最先处理，在FROM 子句中包含多个表的情况下,你必须选择记   
录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersec   
tion table)作为基础表, 交叉表是指那个被其他表所引用的表.   
（2） WHERE 子句中的连接顺序．：   
ORACLE 采用自下而上的顺序解析WHERE 子句,根据这个原理,表之间的连接必须写在其他WHE   
RE 条件之前,  那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾.   
（3） SELECT 子句中避免使用‘ \* ‘：   
ORACLE 在解析的过程中, 会将'\*'  依次转换成所有的列名,  这个工作是通过查询数据字典   
完成的,  这意味着将耗费更多的时间   
（4）减少访问数据库的次数：   
ORACLE 在内部执行了许多工作: 解析SQL  语句, 估算索引的利用率, 绑定变量,  读数据块   
等；   
（5）在SQL\*Plus , SQL\*Forms 和Pro\*C  中重新设置ARRAYSIZE  参数, 可以增加每次   
数据库访问的检索数据量,建议值为200   
（6）使用DECODE 函数来减少处理时间：   
使用DECODE  函数可以避免重复扫描相同记录或重复连接相同的表.   
（7）整合简单,无关联的数据库访问：   
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有   
关系)   
（8）删除重复记录：   
最高效的删除重复记录方法( 因为使用了ROWID)例子：   
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)   
FROM EMP X WHERE X.EMP\_NO = E.EMP\_NO);   
（9）用TRUNCATE 替代DELETE：   
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复   
的信息.  如果你没有COMMIT  事务,ORACLE 会将数据恢复到删除之前的状态(准确地说是恢复   
到执行删除命令之前的状况) 而当运用TRUNCATE 时, 回滚段不再存放任何可被恢复的信息.   
当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRU   
NCATE 只在删除全表适用,TRUNCATE 是DDL  不是DML)   
（10）尽量多使用COMMIT：   
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT   
所释放的资源而减少:   
COMMIT 所释放的资源:   
a. 回滚段上用于恢复数据的信息.   
b. 被程序语句获得的锁   
c. redo log buffer 中的空间   
d. ORACLE 为管理上述3种资源中的内部花费   
（11）用Where 子句替换HAVING  子句：   
避免使用HAVING  子句, HAVING  只会在检索出所有记录之后才对结果集进行过滤. 这个处   
理需要排序,总计等操作. 如果能通过WHERE  子句限制记录的数目,那就能减少这方面的开   
销. (非oracle 中)on、where、having 这三个都可以加条件的子句中，on 是最先执行，whe   
re 次之，having 最后，因为on  是先把不符合条件的记录过滤后才进行统计，它就可以减少   
中间运算要处理的数据，按理说应该速度是最快的， where 也应该比having  快点的，因为   
它过滤数据后才进行sum，在两个表联接时才用on 的，所以在一个表的时候，就剩下where   
跟having  比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，   
那它们的结果是一样的，只是where 可以使用rushmore 技术，而having  就不能，在速度上   
后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据   
上篇写的工作流程，where 的作用时间是在计算之前就完成的，而having 就是在计算后才   
起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时， on 比where  更早   
起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where   
进行过滤，然后再计算，计算完后再由having 进行过滤。由此可见，要想过滤条件起到正   
确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里   
（12）减少对表的查询：   
在含有子查询的SQL  语句中,要特别注意减少对表的查询.例子：   
SELECT TAB\_NAME FROM TABLES WHERE (TAB\_NAME,DB\_VER) = ( SELECT   
TAB\_NAME,DB\_VER FROM TAB\_COLUMNS WHERE VERSION = 604)   
（13）通过内部函数提高SQL 效率.：   
复杂的SQL  往往牺牲了执行效率.  能够掌握上面的运用函数解决问题的方法在实际工作中   
是非常有意义的   
（14）使用表的别名(Alias)：   
当在SQL  语句中连接多个表时,  请使用表的别名并把别名前缀于每个Column 上.这样一来,   
就可以减少解析的时间并减少那些由Column 歧义引起的语法错误.   
（15）用EXISTS 替代IN、用NOT EXISTS 替代NOT IN：   
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况   
下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN 子句将执行   
一个内部的排序和合并. 无论在哪种情况下,NOT IN 都是最低效的(因为它对子查询中的   
表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Join   
s)或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 DEP   
TNO FROM DEPT WHERE LOC = ‘MELB')   
（16）识别'低效执行'的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;   
17）用索引提高效率：   
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE 使用了一个复杂的自平衡B-tr   
ee 结构.  通常,通过索引查询数据比全表扫描要快. 当ORACLE  找出执行查询和Update  语句   
的最佳路径时, ORACLE 优化器将使用索引.  同样在联结多个表时使用索引也可以提高效率.   
另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG 或LONG   
RAW 数据类型,  你可以索引几乎所有的列.  通常, 在大型表中使用索引特别有效. 当然,   
你也会发现,  在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提   
高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录   
在表中增减或索引列被修改时, 索引本身也会被修改.  这意味着每条记录的INSERT , DEL   
ETE , UPDATE 将为此多付出4 , 5 次的磁盘I/O .  因为索引需要额外的存储空间和处理,   
那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：   
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>   
18）用EXISTS  替换DISTINCT：   
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT 子句中使用D   
ISTINCT. 一般可以考虑用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'   
FROM EMP E WHERE E.DEPT\_NO = D.DEPT\_NO);   
（19） sql 语句用大写的；因为oracle 总是先解析sql  语句，把小写的字母转换成大写的   
再执行   
（20）在java 代码中尽量少用连接符“＋”连接字符串！   
（21）避免在索引列上使用NOT 通常，   
我们要避免在索引列上使用NOT, NOT 会产生在和在索引列上使用函数相同的影响. 当ORAC   
LE”遇到”NOT,他就会停止使用索引转而执行全表扫描.   
（22）避免在索引列上使用计算．   
WHERE 子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．   
举例:   
低效：   
SELECT … FROM DEPT WHERE SAL \* 12 > 25000;   
高效:   
SELECT … FROM DEPT WHERE SAL > 25000/12;   
（23）用>=替代>   
高效:   
SELECT \* FROM EMP WHERE DEPTNO >=4   
低效:   
SELECT \* FROM EMP WHERE DEPTNO >3   
两者的区别在于,  前者DBMS 将直接跳到第一个DEPT 等于4的记录而后者将首先定位到DEPT   
NO=3的记录并且向前扫描到第一个DEPT 大于3的记录.   
（24）用UNION 替换OR (适用于索引列)   
通常情况下,  用UNION 替换WHERE 子句中的OR  将会起到较好的效果.  对索引列使用OR 将造   
成全表扫描.  注意, 以上规则只针对多个索引列有效. 如果有column  没有被索引, 查询效   
率可能会因为你没有选择OR 而降低.  在下面的例子中, LOC\_ID  和REGION 上都建有索引.   
高效:   
SELECT LOC\_ID , LOC\_DESC , REGION   
FROM LOCATION   
WHERE LOC\_ID = 10   
UNION   
SELECT LOC\_ID , LOC\_DESC , REGION   
FROM LOCATION   
WHERE REGION = “MELBOURNE”   
低效:   
SELECT LOC\_ID , LOC\_DESC , REGION   
FROM LOCATION   
WHERE LOC\_ID = 10 OR REGION = “MELBOURNE”   
如果你坚持要用OR,  那就需要返回记录最少的索引列写在最前面.   
（25）用IN 来替换OR   
这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i 下，两者的执行路   
径似乎是相同的．   
低效:   
SELECT…. FROM LOCATION WHERE LOC\_ID = 10 OR LOC\_ID = 20 OR LOC\_ID = 30   
高效   
SELECT… FROM LOCATION WHERE LOC\_IN IN (10,20,30);   
（26）避免在索引列上使用IS NULL 和IS NOT NULL   
避免在索引中使用任何可以为空的列，ORACLE 将无法使用该索引．对于单列索引，如果列   
包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在   
此记录.  如果至少有一个列不为空，则记录存在于索引中．举例: 如果唯一性索引建立在   
表的A  列和B 列上, 并且表中存在一条记录的A,B 值为(123,null) , ORACLE 将不接受下一   
条具有相同A,B  值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE 将   
认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们   
都是空!  因为空值不存在于索引列中,所以WHERE 子句中对索引列进行空值比较将使ORACLE   
停用该索引.   
低效: (索引失效)   
SELECT … FROM DEPARTMENT WHERE DEPT\_CODE IS NOT NULL;   
高效: (索引有效)   
SELECT … FROM DEPARTMENT WHERE DEPT\_CODE >=0;   
（27）总是使用索引的第一个列：   
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where 子句引用时,   
优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,   
优化器使用了全表扫描而忽略了索引   
28）用UNION-ALL 替换UNION (  如果有可能的话)：   
当SQL  语句需要UNION 两个查询结果集合时,这两个结果集合会以UNION-ALL 的方式被合   
并, 然后在输出最终结果前进行排序. 如果用UNION ALL 替代UNION,  这样排序就不是必要   
了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记   
录. 因此各位还是要从业务需求分析使用UNION ALL 的可行性. UNION 将对结果集合排序,   
这个操作会使用到SORT\_AREA\_SIZE 这块内存.  对于这块内存的优化也是相当重要的.  下面   
的SQL  可以用来查询排序的消耗量   
低效：   
SELECT ACCT\_NUM, BALANCE\_AMT   
FROM DEBIT\_TRANSACTIONS   
WHERE TRAN\_DATE = '31-DEC-95'   
UNION   
SELECT ACCT\_NUM, BALANCE\_AMT   
FROM DEBIT\_TRANSACTIONS   
WHERE TRAN\_DATE = '31-DEC-95'   
高效:   
SELECT ACCT\_NUM, BALANCE\_AMT   
FROM DEBIT\_TRANSACTIONS   
WHERE TRAN\_DATE = '31-DEC-95'   
UNION ALL   
SELECT ACCT\_NUM, BALANCE\_AMT   
FROM DEBIT\_TRANSACTIONS   
WHERE TRAN\_DATE = '31-DEC-95'   
（29）用WHERE 替代ORDER BY：   
ORDER BY 子句只在两种严格的条件下使用索引.   
ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.   
ORDER BY 中所有的列必须定义为非空.   
WHERE 子句使用的索引和ORDER BY 子句中所使用的索引不能并列.   
例如:   
表DEPT  包含以下列:   
DEPT\_CODE PK NOT NULL   
DEPT\_DESC NOT NULL   
DEPT\_TYPE NULL   
低效: (索引不被使用)   
SELECT DEPT\_CODE FROM DEPT ORDER BY DEPT\_TYPE   
高效: (使用索引)   
SELECT DEPT\_CODE FROM DEPT WHERE DEPT\_TYPE > 0   
（30）避免改变索引列的类型.:   
当比较不同数据类型的数据时, ORACLE 自动对列进行简单的类型转换.   
假设EMPNO  是一个数值类型的索引列.   
SELECT … FROM EMP WHERE EMPNO = ‘123'   
实际上,经过ORACLE 类型转换,  语句转化为:   
SELECT … FROM EMP WHERE EMPNO = TO\_NUMBER(‘123')   
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.   
现在,假设EMP\_TYPE 是一个字符类型的索引列.   
SELECT … FROM EMP WHERE EMP\_TYPE = 123   
这个语句被ORACLE 转换为:   
SELECT … FROM EMP WHERETO\_NUMBER(EMP\_TYPE)=123   
因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE  对你的SQL 进行隐式   
的类型转换,  最好把类型转换用显式表现出来.  注意当字符和数值比较时, ORACLE 会优先   
转换数值类型到字符类型   
（31）需要当心的WHERE 子句:   
某些SELECT  语句中的WHERE 子句不使用索引. 这里有一些例子.   
在下面的例子里, (1)‘!=' 将不使用索引.  记住, 索引只能告诉你什么存在于表中, 而不   
能告诉你什么不存在于表中. (2) ‘||'是字符连接函数. 就象其他函数那样,  停用了索引.   
(3) ‘+'是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相   
比较,这将会启用全表扫描.   
（32） a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.   
b. 在特定情况下,  使用索引也许会比全表扫描慢,  但这是同一个数量级上的区别.  而通常   
情况下,使用索引比全表扫描要块几倍乃至几千倍!   
（33）避免使用耗费资源的操作:   
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的SQL  语句会启动SQL 引擎   
执行耗费资源的排序(SORT)功能. DISTINCT 需要一次排序操作,  而其他的至少需要执行两   
次排序.  通常, 带有UNION, MINUS , INTERSECT 的SQL  语句都可以用其他方式重写.  如果   
你的数据库的SORT\_AREA\_SIZE 调配得好,  使用UNION , MINUS, INTERSECT 也是可以考虑   
的, 毕竟它们的可读性很强   
（34）优化GROUP BY:   
提高GROUP BY  语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个   
查询返回相同结果但第二个明显就快了许多.   
低效:   
SELECT JOB , AVG(SAL)   
FROM EMP   
GROUP by JOB   
HAVING JOB = ‘PRESIDENT'   
OR JOB = ‘MANAGER'   
高效:   
SELECT JOB , AVG(SAL)   
FROM EMP   
WHERE JOB = ‘PRESIDENT'   
OR JOB = ‘MANAGER'   
GROUP by JOB

[struggle.gif]: http://static.blog.csdn.net/xheditor/xheditor_emot/default/struggle.gif
[sql _struggle.gif]: /images/20220805/e0c92f2577e44d0a8517d1f93ec43289.png