sql 优化

SQL优化  

我们要做到不但会写 SQL,还要做到写出性能优良的 SQL。SQL优化需要注意及遵循的准则很多，本文只列举核心部分： 

1) 尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。  

2) 不用NOT IN操作符，可以用NOT EXISTS或者外连接+（外连接+判断为空）替代。 

3) 不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替。例如：a<>0 改为 a>0 or a<0，a<>’ ’ 改为 a>’ ’ 

 4) Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。  

5) 当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用，因此一般不要作为第一个字符出现。  
6) 对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，可以分开连接或者使用不作用在列上的函数替代。  
7) 如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。  
8) Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。  
9) 对数据类型不同的列进行比较时，会使索引失效。  
10) 用“>=”替代“>”。  
11) UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。
 
12) Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。  
13) Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。  
14) Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式。  

15) 不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。  

16) 多利用内部函数提高Sql效率。  

17) 当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。  
18) 根据SQL不同设定优化模式的方式，选择不同的优化策略，通过SELECT /*+ALL+_ROWS*/ „„;来设定。可用的HINT包括/*+ALL_ROWS*/、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/ 等一般在SQL前加first_rows策略，速度都会提高，特殊情况下改用choose策略。  
19) 对于大表查询中的列应尽量避免进行诸如Ｔｏ＿ｃｈａｒ，ｔｏ＿ｄａｔｅ，ｔｏ＿ｎｕｍｂｅｒ等转换。  
20) 有索引的尽量用索引，有用到索引的条件写在前面 。 
21) 如有可能和有必要就建立一些索引 ，在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 

22) 尽量避免进行全表扫描，限制条件尽可能多，以便更快搜索到要查询的数据。 

23) Select子句中尽量避免使用‘*’，当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 ‘*’ 是一个方便的方法。但是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中，会将‘*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。合理写WHERE子句，不要写没有WHERE的SQL语句。 

24) 减少访问数据的次数，当执行每条SQL语句时， 数据库在内部执行了许多工作： 解析SQL语句， 估算索引的利用率， 绑定变量 ， 读数据块等等。 由此可见， 减少访问数据库的次数 ， 就能实际上减少数据库的工作量。 
25) 查询的模糊匹配，尽量避免在一个复杂查询里面使用 LIKE '%parm1%'——百分号会导致相关列的索引无法使用，最好不要用。解决办法:其实只需要对该脚本略做改进，查询速度便会提高近百倍。改进方法如下： 
a、修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表，用户模糊输入供应商名称时，直接在前台就帮忙定位到具体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。 
b、直接修改后台——根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表去做复杂关联。 
尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。 见如下例子：  

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’  SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’  SELECT * FROM T1 WHERE NAME LIKE ‘L%’  

即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。 
26) 避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。 
27) 尽量减少重复工作，控制同一条语句的多次执行，减少多次的数据转换，减少不必要的子查询和连接表，合并对同一张表的多次update操作，update操作不要拆成delete+insert操作，虽然功能相同，但性能差别很大。 
28) 多表连接的连接条件对索引选择有重要意义，在写连接条件时要特别注意。多表连接时，连接条件必须写全，尽量使用聚集索引。 
充分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在 WHERE 子句中将连接条件完整的写上，有可能大大提高查询速度。  

 
例：  
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO  
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO  第二句将比第一句执行快得多。

29) 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:  
SELECT name FROM employee WHERE salary > 60000  
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。 
30) 尽量不要用SELECT INTO语句。  SELECT INTO 语句会导致表锁定，阻止其他用户访问该表。 
31) 使用视图加速查询。 
把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。