优化 SQL ( 一 )

今天开发同事提出一些 SQL 运行较慢，我一看执行结果很少。执行时间近 14 秒！不能忍受，对其进行优化！

优化思想：

1、   驱动 表数据最小化原则（ from最后的表为驱动表在RBO规则中，CBO中会自动选择最小代价的表为驱动表 ）

2、   过滤条件最大化原则（ where 后 过滤的数据依次越来越多）

3、   建立索引走索引

背景说明：

         OLTP 系统，对这几张有频繁的 insert,update 。所以暂时不对系统加索引优化。所以系统只进行 SQL 写法的调优 。

实验：

下面在测试服务器上完全相同的数据及结构进行优化测试。

一、尝试优化 SQL 的写法

SQL1

select lf.*,ld.answer as tick_number,lt.answer as major_code,a.state from

       (select lt.*,sf.state from skills.ns2012_state_first sf join skills.resultedit lt on lt.aclass='101' and sf.serialno=lt.serialno and lt.answer='12867' and lt.question=1) a

        join skills.resultedit   ld   on ld.aclass=a.aclass and a.serialno=ld.serialno and ld.question=21

        left join skills.resultedit lt on lt.question=20 and lt.aclass=a.aclass and a.serialno=lt.serialno

        join skills.logininfo lf on lf.aclass=a.aclass and a.serialno=lf.serialno where   lf.sign is null

         and

          lower(ld.answer) in (

          select tick_number from (

              select count(c.answer)as counts,(c.answer) as tick_number ,max(c.serialno)as serialno   from (

                select b.* from (select lt.* from skills.ns2012_state_first sf join skills.resultedit lt on lt.serialno=sf.serialno and lt.question='1' and lt.aclass='101' and lt.answer='12867') a

                join (select lt.aclass,lt.serialno,lt.question,lower(lt.answer) as answer from skills.ns2012_state_first sf join skills.resultedit lt on lt.serialno=sf.serialno and lt.question='21' and lt.aclass='101' and lt.answer is not null ) b

                 on a.serialno=b.serialno and a.aclass=b.aclass

                 join skills.logininfo lf on lf.aclass=a.aclass and a.serialno=lf.serialno where   lf.sign is null  

                )c   group by c.answer

            ) where counts>=2

      )

 order by lower(ld.answer) desc;

优化后

SQL2

select a.indate,a.serialno,a.sip, ld.answer as tick_number,lt.answer as major_code,a.state from

       (select lt.aclass,lt.serialno,lf.indate,lf.sip,sf.state from skills.ns2012_state_first sf

          join skills.resultedit lt on lt.aclass=101 and sf.serialno=lt.serialno and lt.answer='12867' and lt.question=1

          join   skills.logininfo lf on lf.aclass=lt.aclass and lf.serialno=lt.serialno where   lf.sign is null and to_char(lf.indate,'yyyy-MM-dd HH24:Mi:SS') between '2012-07-01 9:00:00' and '2012-07-30 09:00:00'  

        ) a

        join skills.resultedit   ld   on ld.aclass=a.aclass and a.serialno=ld.serialno and ld.question=21

        left join skills.resultedit lt on lt.question=20 and lt.aclass=a.aclass and a.serialno=lt.serialno

        join(

         select tick_number from (

              select count(c.answer)as counts,(c.answer) as tick_number ,max(c.serialno)as serialno   from (

                select x.aclass,x.serialno,n.answer from (select lt.aclass,lt.question,lt.serialno from skills.ns2012_state_first sf join skills.resultedit lt on lt.serialno=sf.serialno and lt.question='1' and lt.aclass=101 and lt.answer='12867') x

                join (select lt.aclass,lt.serialno,lt.question,lower(lt.answer) as answer from skills.ns2012_state_first sf join skills.resultedit lt on lt.serialno=sf.serialno and lt.question='21' and lt.aclass=101 and lt.answer is not null ) n

                 on x.serialno=n.serialno and x.aclass=n.aclass

                 join   skills.logininfo lf on lf.aclass=n.aclass and lf.serialno=n.serialno where   lf.sign is null and to_char(lf.indate,'yyyy-MM-dd HH24:Mi:SS') between '2012-07-01 9:00:00' and '2012-07-30 09:00:00'  

                )c   group by c.answer

            ) where counts>=2

        ) m on    lower(ld.answer)=m.tick_number

order by lower(ld.answer) desc;

执行计划很多，这里只提取我们所需要的部分，执行计划均是 2 次执行后的记录。这个是频繁操作的！

二、添加索引

针对 aclass 里面出现最多的列，添加了索引进行尝试。并寻找重复最少的列进行加索引。

SQL> select index_name,table_name,status,ITYP_NAME from user_indexes where table_owner='SKILLS';

INDEX_NAME            TABLE_NAME            STATUS      ITYP_NAME

-------------------- -------------------- ---------- ----------

PK_RESULTEDIT         RESULTEDIT            VALID

INDEX_2012            NS2012_STATE_FIRST    VALID

LOG_INDEX             LOGININFO             VALID

红色部分为后来添加的索引

添加索引后，然后执行 SQL1 和 SQL2 ，看看执行效果，从执行时间上看我们没有优化。

这个也说明了根据执行计划来建立索引进行优化效果明显，但是对于一个 OLTP 系统来说，以后用来维护索引的资源也是必须要考虑的，所以最后还是采用了优化写法，没有对系统加索引。

总结 SQL 优化：

1、   优化 SQL 先优化写法及逻辑思维。（尽量减少不必要访问表数据和排序！根据 filter ，考虑过滤条件）

2、   添加相应的索引（根据 access ，考虑加索引）