SQL效率之索引
一、关于索引的知识
要写出运行效率高的sql，需要对索引的机制有一定了解，下面对索引的基本知识做一介绍。
1、索引的优点和局限
索引可以提高查询的效率，但会降低dml操作的效率。
所以建立索引时需要权衡。对于dml操作比较频繁的表，索引的个数不宜太多。
2、什么样的列需要建索引？
经常用于查询、排序和分组的列（即经常在where、order或group by子句中出现的列）。
3、主键索引和复合索引
对于一张表的主键，系统会自动为其建立索引。
如果一张表的几列经常同时作为查询条件，可为其建立复合索引。
4、建立索引的语句
create??index??i_staff??on??staff??(empno);
create??index??i_agent??on??agent??(empno, start_date);
5、删除索引的语句
drop??index??I_staff;
drop??index??I_agent;
6、查询索引的语句
法一：利用数据字典
表一：all_indexes??查看一张表有哪些索引以及索引状态是否有效
主要字段： index_name,??table_name,??status
例如：select? ?index_name,??status??
from??all_indexes
? ?? ???where??table_name=’STAFF_INFO’;
? ?? ?INDEX_NAME? ?? ???STATUS
? ?? ?---------------------? ?? ? -----------
? ?? ?I_STAFF? ?? ?? ?? ? VALID??
? ?? ?? ?? ?表二：all_ind_columns??查看一张表在哪些字段上建了索引
? ?? ?? ?? ???主要字段： table_name,??index_name,??column_name,??column_position
例如： select??index_name,??column_name,??column_position
from??all_ind_columns
? ?? ?? ?where??table_name=’AGENT’
? ?? ?INDEX_NAME? ?? ???COLUMN_NAME? ???COLUMN_POSITON
? ?? ?---------------------? ?? ? -----------------------? ?? ?--------------------------
? ???I_AGENT? ?? ?? ?? ? EMPNO? ?? ?? ?? ???1
? ???I_AGENT? ?? ?? ?? ? START_DATE? ?? ?? ?2
由此可见，agent表中有一个复合索引（empno, start_date ）
法二：利用toad工具
toad用户界面比sql*plus友好，并且功能强大。你可以在toad编辑器中键入表名，按F4，便可见到这张表的表结构以及所有索引列等基本信息。
7、索引的一些特点
1): 不同值较多的列上可建立检索，不同值少的列上则不要建。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
2): 如果在索引列上加表达式，则索引不能正常使用
? ?例如：b1,c1分别是表b,c的索引列
?? select??*??from??b??where??b1/30< 1000 ;
?? select??*??from??c??where??to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
以上都是不正确的写法
3): where子句中如果使用in、or、like、!=，均会导致索引不能正常使用
???例如：select??*??from??b??where??b1=30??or??b1=40;
4):使用复合索引进行查询时必须使用前置列
???例如表a上有一个复合索引（c1,c2,c3）,则c1为其前置列
???如果用c1或c1+c2或c1+c2+c3为条件进行查询，则该复合索引可以发挥作用，反之，用c2或c3或c2+c3进行查询，则该索引不能起作用。
二. 书写sql注意事项：
1、避免给sql语句中引用的索引列添加表达式：
典型实例：
b1,c1分别是表b,c的索引列：
1）select??*??from??b??where??b1/30< 1000 ;
2）select??*??from??c??where to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
替代方案：
1) select??*??from??b where??b1 < 30000;
2)?select * from c??where c1 = to_date(‘20020301 14:01:01’, ‘YYYYMMDD HH24:MI:SS’);
注：在lbs中有两个重要字段，pol_info中的undwrt_date和prem_info中的payment_date，这两个日期是带时分秒的，所以经常有同事用to_char 来查询某一时间段的数据。
例如：select??count(*)??from??pol_info??where??to_char(undwrt_date,’YYYYMMDD’)=’20020416’;
? ?? ?select??count(*)??from??prem_info??where??to_char(undwrt_date,’YYYYMM’)=’200203’;
替代方案：
select??count(*)??from??pol_info??
where??undwrt_date>=to_date(’20020416’,’YYYYMMDD’)??and
???????undwrt_date<to_date(’20020417’,’YYYYMMDD’);
select??count(*)??from??prem_info??
where??payment_date>=to_date(’20020301’,’YYYYMMDD’)??and
?????? payment_date<to_date(’20020401’,’YYYYMMDD’);
2、避免在where子句中使用in、or、like、!=
典型实例：
a1是a表上的索引列：
1) select??*??from??a
? ?where??( a1 = ‘0’ and ...)??or??(a1 = ‘1’ and ...);
2) select??count(*)??from??a??where??a1??in??(‘0’,’1’) ;
替代方案：
1）select??*??from??a??where??a1 = ‘0’??and ...
union
select??*??from??a??where??a1 = ‘1’??and ...
2) select??count(*)??from??a??where??a1 = ‘0’;
??select??count(*)??from??a??where a1 = ‘1’;
? ?然后做一次加法运算；或者直接用存储过程来实现；
小结：
对字段使用了 ‘in，or，like’ 做条件、对字段使用了不等号 ‘!=’，均会使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引，或者使用union连结符代替。另一种方式是使用存储过程，它使SQL变得更加灵活和高效。
3、建立适当的索引
曾经接过开发的一个统计sql， select …??from??tablea??where??cola=…??and??…
运行效率非常慢，经查tablea数据量巨大，再查all_ind_columns，发现cola是tablea的一个复合索引中的一列，但不是前置列。象这种情况，就需要与开发商量，是否针对cola建一个索引。
4、like和substr
对于‘like’和‘substr’，其效率并没有多大分别。但是，当所搜索的值不存在时，使用‘like’的速度明显大于‘substr’。
所以：select??*??from??a??where??substr(a1,1,4) = '5378'??可以用like替代
select??*??from??a??where??a1??like??‘5378%’;
5、写where条件时，有索引字段的判断在前，其它字段的判断在后；如果where条件中用到复合索引，按照索引列在复合索引中出现的顺序来依次写where条件；
6、使用多表连接时，在from子句中，将记录数少的表放在后面，可提高执行效率；
7、避免使用not in
not??in 是效率极低的写法，尽量使用minus或外连接加以替代
典型实例：
1) select col1 from tab1 where col1 not in (select col1 from tab2);
2) select sum(col2) from tab1 where col1 not in (select col1 from tab2);
替代方案
select col1 from tab1 minus??select col1 from tab2;
?select??sum(a.col2)??from??tab1 a, tab2??b
where a.col1=b.col2(+) and b.col1 is null;
8、表查询时，如果其中一个表的记录数量明显大于其他表，则可以先对此表进行查询后，再与其他小表进行表连接。
典型实例：
select??a.plan_code,??b.dno,??c,tno,??sum(a.tot_modal_prem),
from??prem_info a,??dept_ref b,??plan_type c
where??substr(a.deptno,1,7) = substr(b.deptno,1,7)
and a.plan_code = c.plan_code
group by b.dno,??c.tno,??a.plan_code;
替代方案：
select??b.dno,??c.tno,??a.plan_code,??a.tot_amount
from??(select??plan_code,??deptno,??sum(tot_modal_prem)??tot_amount
from??prem_info
group??by??deptno,??plan_code) a
dept_ref??b,
plan_type??c
??where??substr(a.deptno,1,7) = substr(b.deptno,1,7)
? ?? ? and??a.plan_code = c.plan_code
group??by??b.dno,??c.tno,??a.plan_code;
小结：
由于prem_info表的记录数远远大于dept_ref表和plan_type表中的记录数，所以首先从prem_info表中查询需要的记录，此时记录数已经被大量缩小，然后再和其他两个表连接，速度会得到很大改善！
9、查询数量较大时，使用表连接代替IN，EXISTS，NOT IN，NOT EXISTS等。
典型实例：
a、使用IN：
select sum(col2) from tab1 where col1 in (select col1 from tab2);
使用EXISTS:：
select sum(col2) from tab1 a
where exists ( select * from tab2 where col1=a.col1);
b、使用NOT IN：
select sum(col2) from tab1 where col1 not in (select col1 from tab2);
使用NOT EXISTS：
select sum(col2) from tab1 a
where not exists ( select * from tab2 where col1=a.col1);
? ?? ?? ? 替代方案：
a、使用连接：
select sum(a.col2) from tab1 a,tab2 b where a.col1=b.col2;
b、使用外连接：
select sum(a.col2) from tab1 a,tab2 b
where a.col1=b.col2(+) and b.col1 is null;
补充：
UNION和UNION ALL的区别
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录；而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。但是UNION ALL比UNION效率高，执行快。
