sql数据类型转换_10个SQL优化规则

优化规则总结&＃xff1a;

1.通常不应对where语句检索条件的表列做任何处理。处理包括加函数 ,计算等。例外情况DBA会提前公布。

2.在业务逻辑及工具允许的条件下&＃xff0c;采用正确的SQL嵌套方式&＃xff0c;且把运算处理放在最外层。

3.详细的分析业务逻辑和数据结构&＃xff0c;避免不必要的计算。如排重distinct&＃xff0c; nvl检测等。

4.避免数据列类型和输入值类型不一致。

5.SQL语句中使用绑定变量。

6.理清业务逻辑&＃xff0c;选择中间结果集最少为目标&＃xff0c;减少SQL内部的操作运算。

7.减少数据库的调用次数。

8.正确高效的使用模糊匹配查询---右百分号。

9.分页排序时正确的使用rownum

10.避免使用耗费资源的操作--UNION,MINUS,INTERSECT&＃xff0c;DISTINCT

优化规则举例说明&＃xff1a;

规则1.通常不应对where语句检索条件的表列做任何处理。处理包括加函数 ,计算等 。例外情况DBA会提前公布。

目的&＃xff1a;

   让表列充分使用其索引,如果用了函数,会导致索引无法被使用。

举例(函数)&＃xff1a;

Error:

select count(*) as col_0_0_

  from B_LOG blog0_

 where to_char(blog0_.OP_DATE,&＃39;yyyy-mm-dd) >&＃61; &＃39;2007-01-01&＃39;

正确改法&＃xff1a;

select count(*) as col_0_0_

  from B_LOG blog0_

 where blog0_.OP_DATE >&＃61; to_date(&＃39;2007-01-01 00:00:00&＃39;, &＃39;yyyy-mm-dd hh24:mi:ss&＃39;)

举例(计算):

Error:

SELECT ENAME
FROM EMP
WHERE DEPTNO &＃43; 1 &＃61; 10     /*DEPTNO上的索引将失效*/
AND EMP_TYPE ||’$’ &＃61; ‘A&＃39;  /*EMP_TYPE上的索引将失效*/

正确改法&＃xff1a;

SELECT ENAME
FROM EMP
WHERE DEPTNO  &＃61; 10-1     /*DEPTNO上的索引将失效*/
AND EMP_TYPE  &＃61; ‘A&＃39; ||‘$’

规则2. 在业务逻辑及工具允许的条件下&＃xff0c;采用正确的SQL嵌套方式&＃xff0c;且把运算处理放在最外层。

on_busin_chance a 与Busin_long_info  关系上一一对应。

改成 嵌套方式&＃xff0c; 最里层不要关联Busin_long_info&＃xff0c; 用结果的20条 (rownum 去关联Busin_long_info 。

Error 

 -----------------------------PrjIframe.exe--- begin -------------------------------

Select title,

       :"SYS_B_00" || username || :"SYS_B_01" || bc_id || :"SYS_B_02" as linku, 

.......

     price,

       trustvalue

  from (Select a.title           as title,

               a.bc_id           as bc_id,

            .......

               a.pricerange      as price,

               :"SYS_B_04"       as trustvalue

          from on_busin_chance a, Busin_long_info b

         where a.bc_id &＃61; b.bc_id

           and a.pubdate > sysdate - :"SYS_B_05"

         .......

          and a.SortTag &＃61; :"SYS_B_10"

         order by a.pubdate desc)

 where rownum 

 -----------------------------PrjIframe.exe--- end -------------------------------

错误的sql: 

select A.bc_id,

       A.title,

       A.Pubdate ,

      /*to_char(A.Pubdate, :"SYS_B_1") as NEWPubdate,*\*/

       A.picpath,

       A.supcatid asCLSID,

       A.username,

       A.providerid,

       C.NAME ascorname,

       C.Memtypeid,

       b.areaid

  from (select bc_id,

               title,

               .....

              providerid

          frombusin_list

         wheresorttag &＃61; &＃39;1&＃39;

         order bypubdate desc) A,

       (selectsupcatid, areaid

          fromnewhc.new_iframe_class

         where areaid&＃61; &＃39;005&＃39;

           and clsid&＃61;  &＃39;003034&＃39;) B,

       provider_listC

 where b.supcatid &＃61;A.supcatid

   and C.providerid &＃61;A.providerid

   and rownum <20

正确的sql

select bb.to_char(A.Pubdate, :"SYS_B_1") asNEWPubdate , xxxx,xxxx,

   from (select aa.*

           from(select A.*

                  from busin_list A, newhc.new_iframe_class B

                 where A.sorttag &＃61; &＃39;1&＃39;

                   and B.areaid &＃61; &＃39;005&＃39;

                   and B.clsid &＃61; &＃39;003034&＃39;

                   and B.supcatid &＃61; A.supcatid

                  orderby A.pubdate desc) aa

          whererownum <20) bb,

        provider_listC

  where c.providerid&＃61; bb.providerid

规则3. 详细的分析业务逻辑和数据结构&＃xff0c;避免不必要的计算。 如排重distinct&＃xff0c; nvl检测等。

同时从设计方面提高数据的内在质量(约束&＃xff0c;匹配&＃xff0c;数据类型转换)&＃xff0c; , 避免SQL过多关联检测计算&＃xff0c;多次类型转换。

select distinct    bidinproce0_.NAME          as col_0_0_,

                bidsetting1_.ID            as col_1_0_,

                bidsetting1_.ID            as ID409_,

                .......

                bidsetting1_.POSTENDDATE   as POSTENDD7_409_,

                bidsetting1_.STATE         as STATE409_

 from keyword.BID_IN_PROCESS bidinproce0_,

       keyword.BID_SETTING    bidsetting1_,

       keyword.ON_COR_TABLE   oncortable2_

 where bidinproce0_.BIDSETTINGID &＃61;bidsetting1_.ID

  and bidinproce0_.CURBID &＃61; &＃39;1&＃39;

  and bidinproce0_.VIPPURCHASE &＃61; &＃39;0&＃39;

      update on_busin_chanceset operstate   &＃61; &＃39;0&＃39;,
             searchstate &＃61; &＃39;0&＃39;,
             yrepeated   &＃61; &＃39;1&＃39;,
             REPEATTIMES &＃61;nvl(REPEATTIMES, 0) &＃43; 1,
             pubdate     &＃61; sysdate,
             enddate     &＃61; sysdate &＃43; validdatewhere bc_id &＃61; P_id;

检查发现REPEATTIMES列上有非空约束。

        update on_busin_chanceset operstate   &＃61; &＃39;0&＃39;,
                   searchstate &＃61; &＃39;0&＃39;,
                   yrepeated   &＃61; &＃39;1&＃39;,
                   REPEATTIMES &＃61;REPEATTIMES &＃43; 1,
                   pubdate     &＃61; sysdate,
                   enddate     &＃61; sysdate &＃43; validdatewhere bc_id &＃61; P_id;

------------改前-------------------------------------

  Elapsed      CPU                  Elap per  % Total

  Time (s)   Time (s) Executions   Exec (s)  DB Time   SQL Id

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

      886        267       96,174        0.0    1.5 8k49v07aa7dhw

BEGIN RepeateInfo(:1,:2,:3); END;

nvl 去掉后的效果

------------改后--------------11-14-----------------------

  Elapsed      CPU                  Elap per  % Total

  Time (s)   Time (s) Executions   Exec (s)  DB Time   SQL Id

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

       396        227     108,756        0.0     1.7 8k49v07aa7dhw

BEGIN RepeateInfo(:1,:2,:3); END;

null数据检测/转换

从数据进入源头&＃xff0c;数据底层强制约束。

   如重发中的 nvl 检测 ,  nvl(B.SORTTAG,:"SYS_B_0000")

规则4. 避免数据列类型和输入值类型不一致。

举例

列为字符型varchar2 。
where var_col&＃61;&＃39;36837053&＃39; 可以使用index

where var_col&＃61; 36837053 ; 不能用上其列index

规则5: SQL语句中使用绑定变量。

为将sql分析减少到最小&＃xff0c;在SQL语句中使用绑定变量的方法实现,。这样&＃xff0c;所有的用户都可以使用相同的SQL语句。

规则6:理清业务逻辑&＃xff0c;选择中间结果集最少为目标&＃xff0c;减少SQL内部的操作运算。

select *from (select rownumrowno, t.*from (selectbd_id,
                       bd_state,
                       bd_title,    .......

                       bd_user,fromt_bbs_datawhere bd_parent &＃61;0order bybd_lastdate desc) twhere rownum <&＃61;20)where rowno >&＃61; 1

正确改法&＃xff1a;

bd_lastdate 加数据的检索条件时间范围限制。

规则7: 减少数据库的调用次数。

举例&＃xff1a;

减少对表的查询 ( 在含有子查询的SQL语句中,要特别注意减少对表的查询.)

SELECTTAB_NAMEFROM TABLESWHERE TAB_NAME &＃61; (SELECTTAB_NAME FROM TAB_COLUMNS WHERE VERSION &＃61; 604)
 AND DB_VER &＃61; (SELECT DB_VER FROM TAB_COLUMNS WHEREVERSION &＃61; 604)

正确改法&＃xff1a;

SELECTTAB_NAMEFROM TABLESWHERE (TAB_NAME, DB_VER) &＃61;
 (SELECT TAB_NAME, DB_VER) FROMTAB_COLUMNS WHERE VERSION &＃61; 604)

规则8.  正确高效的使用模糊匹配查询---右百分号。

    一.  用户输入的查询关键字长度不能过短。 Like ‘H%’

二&＃xff0e; 判断结果集的数量是否属合理范围&＃xff0c;否则要求用户的再次确认或取消此查询。

三&＃xff0e; 前端避免用户输入的特殊关键字( %xxxxx,  _) 。 恶意匹配&＃xff0c; 域名查询要求。

考虑&＃xff1a; 此需求的实现 的确要对 email 的信息拆分。翻转index测试

规则9.分页排序时正确的使用 rownum

下面的SQL 多耗费了5倍的CPU,并会随数据量的增长而成增长

原 sql

SELECT A.*

  FROM(SELECT X.*, rownum rn

         FROM (SELECT  rowid as rowidx

                 From List_Provider_sup_1 X

                WHERE X.L3CurCatID &＃61; &＃39;011&＃39;

                   AND X.L6CurCatID &＃61; &＃39;011002&＃39;

                ORDER BY SORTVALUE ASC) X   ) A

 WHEREA.rn <&＃61;20

   ANDA.rn > 0

应改写如下:  

select * from (

 selectA.*, rownum rn

   from(SELECT rowid as rowidx

          From List_Provider_sup_1 X

         WHERE X.L3CurCatID &＃61; &＃39;011&＃39;

           AND X.L6CurCatID &＃61; &＃39;011002&＃39;

         ORDER BY SORTVALUE ASC) A

  whererownum <20 ) B

where B.rn > 0 

 规则10.避免使用耗费资源的操作----

 UNION,MINUS,INTERSECT&＃xff0c;DISTINCT,

会启动SQL引擎执行耗费资源的排序(SORT)功能.。

DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.

例如,一个UNION查询,其中每个查询都带有GROUP BY子句,GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写。

建议 : 学会分析SQL语句执行计划。

建议对所有的SQL语句执行EXPLAIN_PLAN&＃xff0c;并查看输出结果&＃xff0c;然后调整相应的语句。如&＃xff1a;

随着数据库的版本升级&＃xff0c;有些书介绍的一些规则在新版中已不在适用。

如&＃xff1a;表名顺序&＃xff0c; OR的使用等。

选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒

SQL> set autotrace on explain;

SQL> select * from dept;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING   NEW YORK

        20 RESEARCH       DALLAS

        30 SALES               CHICAGO

        40 OPERATIONS     BOSTON

Execution Plan

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

   0      SELECT STATEMENT Optimizer&＃61;CHOOSE

           1    0  TABLE ACCESS (FULL) OF &＃39;DEPT&＃39;