SQL语句优化技术分析(适用Oracle)

操作符优化

IN 操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：
ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
**推荐方案：在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。**

NOT IN操作符

此操作是强列不推荐使用的，因为它不能应用表的索引。
**推荐方案：用NOT EXISTS 方案代替**

exists 操作符

通常来说，采用表连接的方式比exists更有效率。
**推荐方案：用表连接代替exists**

IS [NOT] NULL操作

判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。
在表设计阶段应该避免字段可为null的设计。应该使用缺省值代替
**推荐方案：用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。**

> 、<、>=、<= 操作符

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。
**推荐方案：一般情况下是不用调整的,临界值数据量大时尽量使用>=**

!= 操作符

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
**推荐方案：用其它相同功能的操作运算代替,例如 a<>0 改为 a>0 or a<0**

LIKE操作符

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。
**推荐方案：尽量避免使用like,使用like尽量开头不要使用 %或者_ ，因为不能使用索引。索引范围越精确越好，甚至可以将一个like 拆分成多个like 。

UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
**推荐方案：采用UNION ALL操作符替代UNION**

SQL书写的影响

不同写法SQL

从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。
**推荐方案：不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。**

SELECT语句中避免使用*

避免使用* 代表所有字段，使用精准字段查询，减少解析时间
**推荐方案：使用精准字段代替* **

where 条件的顺序

Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。
**推荐方案：表连接条件放在最开始，过滤数据量大的条件尽放在最后**

From 后表顺序

**推荐方案：从右到左处理，所以将小表放在右边先处理**

利用DECODE函数来优化

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

用truncate代替delete

truncate只在删除全表时适用，truncate是DDL，而不是DML。

where子句替换having子句

避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序总计等操作。如果通过WHERE子句限制记录的数目，那就能减少这方面的开销。
HAVING中的条件一般用于对一些集合函数的比较，如COUNT()等，除此之外，一般的条件放入WHERE子句中。

使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀到每个COLUMN上。这样，就可以减少解析的时间并减少那些由COLUMN歧义引起的语法错误。
COLUMN歧义指的是由于SQL中不同的表具有相同的COLUMN名称，当SQL语句中出现这个COLUMN时，SQL解析器无法判断这个COLUMN的归属。  
**推荐方案：使用别名定义每张表，并且字段前加上所属别名**

用exists代替distinct

distinct会引发全表扫描，exists使查询更为迅速，因为关系型数据库管理系统核心模块将在子查询的条件一旦满足后，立刻返回结果。

SQL语句索引的利用

对操作符的优化（见上节）

对条件字段的一些优化

避免在索引列上使用函数

WHERE子句中，如果索引列是函数的一部分，优化器将不适用索引而使用全表扫描。
例如：substr(hbs_bh,1,4)='5400' 不使用索引
优化处理:hbs_bh like '5400%' 使用范围索引

避免在索引列上使用计算

• 例如：ss_df+20>50
  优化处理：ss_df>30
• hbs_bh=5401002554
  优化处理：hbs_bh=‘5401002554’
  注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。

避免在索引列上使用NOT、<>、!= (同上)

用>=代替>

例如：empno > 2 从数据等于2开始比较
优化处理：empno >= 3 从数值等于3开始比较

用UNION代替OR

通常情况下，用UNION代替WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果COLUMN没有被索引，查询效率没有OR好。    

用联合索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。

避免出现索引列自动转换

当比较不同数据类型的数据时，Oracle自动对列进行简单的类型转换。
假设user_no是一个字符类型的索引列。
user_no=109 会被优化 to_number(user_no)=109 。因为内部发生的类型转换，这个索引将不会被用到。
直接使用user_no='109' 避免自动转换即可。

更加详细的可以查看 ORACLE SQL性能优化系列