DM SQL优化 学习
说起SQL优化，大家会像条件反射一样立即想到索引。但是，索引并非万能，它只是SQL优化的手段之一，并且作用有限，还有很多其他手段来提升SQL的性能。这些优化手段总结为如下几大类：共享SQL，避免排序，减少表的扫描，使用索引。
SQL调优是个比较复杂，也很频繁的工作。实际上，绝大多数需要调优的SQL都是在编码的时候就出现了问题，如无绑定变量、重复扫描表、大量的排序、索引列计算、隐式转换等。维护阶段的SQL调优往往是救火，此时的系统已经慢的不能忍受了，由于不能修改应用等诸多限制，常常只能使用索引等有限的手段，优化效果不佳。
但是，在开发阶段，我们可以使用各种手段来写出最优化的SQL，而不局限于索引。如果在开发阶段遵循某些优化规则，就能写出性能优良的SQL，必将减少后期繁琐的优化工作。防患于未然，何乐而不为？下面将详细介绍这些SQL优化规则。
3.1 共享SQL
3.1.1 统一编码风格
SQL语句在执行之前需要解析，为了不重复解析相同的SQL语句，在第一次解析之后， ORACLE将SQL语句存放在内存中。后面遇到相同的SQL语句，就不用再次解析，从而提高执行效率。然而，oracle判断SQL“相同”的条件非常苛刻，一是字符级别的比较，需要大小写、空格完全一样；另外一个对象相同，即是属于同一个schema的。 这里讨论的重点是字符级别的差异对SQL共享的影响。
假如有4个开发人员，他们的编码风格各异，在应用中编写了下面的sql
|
A: select * from test B: select * from TEST C: select * from dbo.test D: select * from test
|
虽然这4个sql功能完全一样，但oracle会认为它们是不相同的语句，都需要分别解析一次，SQL效率明显降低。
因此，统一开发人员的编码风格很重要，如关键字大小写，表名、字段名大小写，表名是否带schema，空格个数等等。除了代码美观之外，这些更是保证SQL共享的关键之一。
3.1.2 使用绑定变量
在应用中使用绑定变量，可减少SQL的硬解析，这也是SQL共享的关键之一。
应尽量绑定变量：
|
select * from test where x = :id --ORACLE select * from test where x = ? --DM
|
避免硬编码：
|
select * from test where x=1 select * from test where x=2 ...
|
3.2 避免排序
3.2.1 避免排序
常见的排序操作有：
（1）order by，使用索引避免排序
（2）distinct
（3）union，尽可能用union all代替
（4）minus
（5）group by
3.2.2 用EXISTS替换DISTINCT
低效：
|
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,EMP E WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
|
3.3 减少表的扫描
3.3.1 避免使用 *
在SELECT中引用 ‘*’ 的确很方便。不幸的是，这是一个非常低效的方法。 实际上，ORACLE在解析的过程中， 会将‘*’ 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。也许用户只需要几个列而已，但是‘*’会返回所有的列数据到客户端，浪费宝贵的网络、存储资源。
3.3.2 减少表的重复扫描
减少表的扫描次数总是有效的，想尽各种办法减少表的扫描吧。
低效：扫描了2次EMP_CATEGORIES
|
UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
|
高效：只扫描了一次EMP_CATEGORIES
|
UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
|
再来个真实应用中的例子（SQL做了简化）：
低效：tmpBalance表被扫描了3次
|
select case when char_length(T.SUBJECTCODE)=4 then 0 else 1 end LEAFDATA , substr(T.SUBJECTCODE, 1, 4) SUBJECTCODE , T.SUBJECTNAME SUBJECTNAME then 1 else 0 end HASYE from tmpBalance T where char_length(T.SUBJECTCODE)>=4 union all select case when char_length(T.SUBJECTCODE)=6 then 0 else 1 end LEAFDATA , substr(T.SUBJECTCODE, 1, 6) SUBJECTCODE , T.SUBJECTNAME SUBJECTNAME then 1 else 0 end HASYE from tmpBalance T where char_length(T.SUBJECTCODE)>=6 union all select case when char_length(T.SUBJECTCODE)=8 then 0 else 1 end LEAFDATA , substr(T.SUBJECTCODE, 1, 8) SUBJECTCODE , T.SUBJECTNAME SUBJECTNAME then 1 else 0 end HASYE from tmpBalance T where char_length(T.SUBJECTCODE)>=8
|
高效：tmpBalance只被扫描了一次，实际项目中此种写法效率比上种快3倍以上。
|
SELECT CASE WHEN char_length(T.SUBJECTCODE)=2*(r+1) THEN 0 ELSE 1 END LEAFDATA, SUBSTR(T.SUBJECTCODE,1,2*(r+1)) SUBJECTCODE, T.SUBJECTNAME SUBJECTNAME FROM tmpBalance T , (select level r from dual connect by level <=3) seq WHERE char_length(T.SUBJECTCODE)>=2*(r+1)
|
3.3.3 使用表别名
当在SQL语句中连接多个表时， 请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间，并减少那些由Column歧义引起的语法错误。
3.3.4 用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下， 使用EXISTS（或NOT EXISTS）通常将提高查询的效率。
低效：in子句需要找到所有满足条件的记录才会返回
|
SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
|
高效：exists子句只要找到一条符合条件的记录就马上返回
|
SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
|
3.3.5 用NOT EXISTS替代NOT IN
低效：
|
SELECT … FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPTWHERE DEPT_CAT=’A’);
|
高效：
|
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
|
3.4 使用索引
3.4.1 用表连接替换EXISTS
通常来说 ，采用表连接的方式比EXISTS更有效率。
低效：
|
SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
|
高效：
|
SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = ‘A’ ;
|
3.4.2 避免索引列运算
对where条件中的索引列进行计算，或使用了函数，会导致该列无法走索引扫描。
无法使用索引：
|
SELECT * FROM service_promotion WHERE TO_CHAR(gmt_modified,’yyyy-mm-dd’) = ‘20001-09-01’;
|
可以使用索引：
|
SELECT * FROM service_promotion WHERE gmt_modified >= TO_DATE(‘2001-9-01’,’yyyy-mm-dd’) AND gmt_modified < TO_DATE(‘2001-9-02’,’yyyy-mm-dd’);
|
3.4.3 用UNION替换OR
通常情况下， 用UNION替换WHERE子句中的OR将会起到较好的效果。 对索引列使用OR将造成全表扫描。注意， 以上规则只针对多个索引列有效。在下面的例子中， LOC_ID 和REGION上都建有索引。
低效：
|
SELECT LOC_ID , LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
|
高效：
|
SELECT LOC_ID , LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION WHERE REGION = “MELBOURNE”
|
3.4.4 用in替换or
低效：
|
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）
|
3.4.5 避免IS NULL和IS NOT NULL
设计表时应注意，尽量将索引列设计为非空，用默认值代替null。因为在索引列上使用is null 和is not null操作将不会走索引。
低效： 不会走索引
|
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL；
|
高效： 走索引
|
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0；
|
3.4.6 避免<>、not in、not like
<>、not in、not like等操作永远不会走索引。
3.4.7 小心like
只有前置like才能走索引，如 like 'abc%'，其他如 like '%abc%'、'%abc'是不会走索引的。
3.4.8 避免隐式转换
当where条件中，做比较操作的两边数据类型不一致时，数据库会自动进行类型转换，并且总是把char类型的一方转换为number类型。若索引列发生了隐式转换，则无法走索引（这等同于在索引列上使用了函数）。
|
drop table tmp; create table tmp(c1 int,c2 varchar(10)); insert into tmp select level c1,level c2 from dual connect by level<=1000;
create index idx_c1 on tmp(c1); create index idx_c2 on tmp(c2);
select * from tmp where c1=1; --执行计划，能走索引 1 #NSET2: [0, 1, 22] 2 #PRJT2: [0, 1, 22]; exp_num(3), is_atom(FALSE) 3 #BLKUP2: [0, 1, 22]; IDX_C1(TMP) 4 #SSEK2: [0, 1, 22]; scan_type(ASC), IDX_C1(TMP), scan_range[1,1] select * from tmp where c1='1'; --执行计划，能走索引，只是常量值发生隐式转换(下面标红处) 1 #NSET2: [0, 1, 22] 2 #PRJT2: [0, 1, 22]; exp_num(3), is_atom(FALSE) 3 #BLKUP2: [0, 1, 22]; IDX_C1(TMP) 4 #SSEK2: [0, 1, 22]; scan_type(ASC), IDX_C1(TMP), scan_range[exp_cast(1),exp_cast(1)] select * from tmp where c2=1; --执行计划，C2列发生数据类型转换(下面标红处)，走全表扫描 1 #NSET2: [0, 1, 22] 2 #PRJT2: [0, 1, 22]; exp_num(3), is_atom(FALSE) 3 #SLCT2: [0, 1, 22]; exp_cast(TMP.C2) = var1 4 #CSCN2: [0, 1000, 22]; INDEX33559060(TMP) select * from tmp where c2='1'; --执行计划，未发生数据类型转换，能走索引 1 #NSET2: [0, 1, 22] 2 #PRJT2: [0, 1, 22]; exp_num(3), is_atom(FALSE) 3 #BLKUP2: [0, 1, 22]; IDX_C2(TMP) 4 #SSEK2: [0, 1, 22]; scan_type(ASC), IDX_C2(TMP), scan_range[1,1]
|
