提升SQL执行效率的五大策略：高效SQL编写技巧与索引优化方法详解

如何改进SQL语句，如何添加索引 1、主键即是聚集索引2、建立索引便能显著提升查询速度3、将所有需提升查询速度的字段纳入聚集索引，以增强查询速度(四)其他未公开的索引使用经验总结1、使用聚合索引比非聚合索引的主键查询速度快2、使用聚合索引比一般主键作orderby时速度快，尤其在数据量小的情况下3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，不论聚合索引使用了多少个4、日期列不会因包含分秒而降低查询速度(五)其他注意事项1.勿为常用的小型表建立索引2.勿将社会保障号码(SSN)或身份证号码(ID)作为键3.勿使用用户键4.勿为memo/notes字段或大型文本字段(多字符)建立索引5.使用系统生成的主键二、优化SQL语句1、Like语句是否属于SARG取决于所使用的通配符类型2、or会导致全表扫描3、非操作符、函数导致的不符合SARG形式的语句4、IN的作用相当于OR5、尽量少用NOT6、exists和in的执行效率相同7、使用函数charindex()和前面加通配符%的LIKE执行效率相同8、union并不绝对比or的执行效率高9、字段提取要遵循“需多少、提多少”的原则，避免“select”10、count()不比count(字段)慢11、orderby按聚集索引列排序效率最高12、高效的TOP ORACLE优化SQL语句，提升效率(2) 索引是表的一个组成部分，用于提升检索数据的效率。Oracle使用了一个复杂的自平衡B tree结构，通常通过索引查询数据比全表扫描要快。当Oracle确定执行查询和Update语句的最佳路径时，优化器会使用索引。同样，在联结多个表时使用索引也能提升效率。另一个使用索引的好处是它提供了主键的唯一性验证。对于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 i下，两者的执行路径似乎是相同的： 低效: 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停用该索引。

避免在索引中采用任何可能为空的列 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能够用来查询排序的消耗量： lishixinzhi/Article/program/Oracle/201311/16789