sql优化

http://topic.csdn.net/u/20090615/01/41b07065-8eed-42d9-91e9-9ac895ca3803.html

经常我们在论坛上看到很多人提问题：一条语句实现*****.
几年前我也追求这种，别人几个循环嵌套实现的，自己力争一条语句利用关系逻辑来实现，弄完之后自我感觉良好，在数据仓库部门工作一段时间后越来越发现这些东西的不实用。
在这很多情况下，最原始的写法可能最好:
1,具有最好的可读性， 像下面这种，不看原始需求，光看语句，我以前自己写的我都不知道这语句是做什么用的

SQL code

   
   

    
    
    
    create
    
     
    
    table
    
     t_1(ID 
    
    int
    
    ,           MID  
    
    int
    
    ,           Date 
    
    datetime
    
    )

    
    insert
    
     t_1 
    
    select
    
     
    
    1
    
    ,             
    
    NULL
    
    ,           
    
    '
    
    2007-1-21
    
    '
    
     

    
    insert
    
     t_1 
    
    select
    
     
    
    2
    
    ,             
    
    NULL
    
    ,           
    
    '
    
    2007-3-25
    
    '
    
     

    
    insert
    
     t_1 
    
    select
    
     
    
    3
    
    ,             
    
    NULL
    
    ,           
    
    '
    
    2007-3-26
    
    '
    
     


    
    create
    
     
    
    table
    
     t_2(ID 
    
    int
    
    ,           Date 
    
    datetime
    
    )

    
    insert
    
     t_2 
    
    select
    
     
    
    1
    
    ,             
    
    '
    
    2007-1-22
    
    '
    
     

    
    insert
    
     t_2 
    
    select
    
     
    
    2
    
    ,             
    
    '
    
    2007-1-25
    
    '
    
     

    
    insert
    
     t_2 
    
    select
    
     
    
    3
    
    ,             
    
    '
    
    2007-1-29
    
    '
    
    


    
    update
    
     a 
    
    set
    
     mid
    
    =
    
    b.id
    
    
    from
    
     t_1 a

    
    left
    
     
    
    join
    
      t_2 b
    
    
    on
    
     b.date
    
    =
    
    
        (
    
    select
    
     
    
    max
    
    (date) 
    
    from
    
     t_2 x 
            
    
    where
    
     date
    
    <=
    
    a.date
                
    
    and
    
     
    
    not
    
     
    
    exists
    
    (
    
    select
    
     
    
    1
    
     
    
    from
    
     t_1 y
                        
    
    where
    
     id
    
    <
    
    a.id 
    
    and
    
     date
    
    >=
    
    x.date
                            
    
    and
    
     
    
    not
    
     
    
    exists
    
    (
    
    select
    
     
    
    1
    
     
    
    from
    
     t_2 
    
    where
    
     date
    
    >
    
    x.date 
    
    and
    
     date
    
    <=
    
    y.date)
                        )
        )



    
    --
    
    or
    
    

    
    update
    
     a 
    
    set
    
     mid
    
    =
    
    b.id 
        
    
    from
    
       t_1   a 

    
    left
    
       
    
    join
    
         t_2   b 
        
    
    on
    
       b.date
    
    =
    
     
                (
    
    select
    
       
    
    max
    
    (date)   
    
    from
    
       t_2   x   
                        
    
    where
    
       date 
    
    <=
    
    a.date 
                
    
    and
    
     (
    
    select
    
     
    
    count
    
    (
    
    *
    
    ) 
    
    from
    
     t_2 
    
    where
    
     date
    
    >=
    
    x.date 
    
    and
    
     date
    
    <=
    
    a.date)
    
    =
    
    
                    (
    
    select
    
     
    
    count
    
    (
    
    *
    
    ) 
    
    from
    
     t_1 
    
    where
    
     id
    
    <
    
    a.id 
    
    and
    
     date
    
    >=
    
    x.date)
    
    +
    
    1
    
           
                ) 


    
    drop
    
     
    
    table
    
     t_1,t_2
   
   

2, 具有可能较好的性能，即便不具有最快的执行时间，也会从整个系统角度来说影响最小。

以下是几个例子.


1)

SQL code

   
   

    
    
    
    select
    
     a.
    
    *
    
    ,cnt 
    
    =
    
     (
    
    select
    
     
    
    count
    
    (
    
    *
    
    ) 
    
    from
    
     tb 
    
    where
    
     group_id
    
    =
    
     a.group_id) 
    
    from
    
     ta a
   
   

这条语句，很直白，也很容易理解，但是从逻辑上来看它的效率呢,确实很差，从逻辑执行上来看，每扫一条ta表记录，都要在tb中count一次。(这里我们暂不管你查询优化器多么智能,只讲语句的逻辑写法上)
语句稍做改进就成为

SQL code

   
   

    
    
    
    select
    
     a.
    
    *
    
    ,
    
    isnull
    
    (b.cnt,
    
    0
    
    ) 
    
    from
    
     ta a,
    
    
    left
    
     
    
    join
    
     (
    
    select
    
     group_id,
    
    count
    
    (
    
    *
    
    ) 
    
    from
    
     tb 
    
    group
    
     
    
    by
    
     group_id) b
    
    
    on
    
     a.group_id 
    
    =
    
     b.group_id
   
   

为什么这里用left join而不inner join, 只是说可能tb中并不存在ta中group_id对应的记录，为防止结果数据丢失所以左连。如果业务规则上tb中必有ta中对应的group_id之存在，那么内连接获取更好的性能。
与第一句相比，先聚合形成较少的结果集， 再连接， 这是从逻辑上 对事务的分批。

2)
在一个stored-proc中

SQL code

   
   

    
    
    
    select
    
     
    
    distinct
    
     b.sku_no, b.vend_no
    
    
    into
    
     #sku
    
    
    from
    
     part b, inv_qty a
    
    
    where
    
     a.sku_no 
    
    =
    
     b.sku_no
        
    
    and
    
     a.inv_type 
    
    =
    
     
    
    300
   
   

以上为原始语句, 分析实际情况及数据:
part 为产品表， 百万级 inv_qty 为库存表 , 每个part都会在其中有记录, inv_type 值从1-300有 300种值，但不一定每个part都有300个inv_type
另外，也可能part表中的一些变更，有的inv_qty 维护不及时，在inv_qty表中的sku并不存在于part表。
part表聚集索引sku_no, inv_qty 聚集索引 sku_no, inv_type为普通索引。

在实际上执行这条语句时，实际IO约为1M , 单条语句执行时间为 70-90s
实际上随着时间的变更，数据的变化，有可能某时inv_qty 中type为300的part会剧增, 会有更大的执行成本和IO开销

调优处理:

SQL code

   
   

    
    
    
    create
    
     
    
    table
    
     #sku(sku_no 
    
    int
    
     
    
    null
    
    , vend_no 
    
    int
    
     
    
    null
    
    )

    
    insert
    
     #sku(sku_no) 
    
    select
    
     
    
    distinct
    
     sku_no 
    
    from
    
     inv_qty 
    
    where
    
     inv_type 
    
    =
    
    300
    
    

    
    exec
    
    (
    
    '
    
    create index idx1 on #sku(sku_no)
    
    '
    
    )

    
    exec
    
    (
    
    '
    
    update #sku set vend_no = b.vend_no from #sku a,part b where a.sku_no = b.sku_no
    
    '
    
    )

    
    delete
    
     
    
    from
    
     #sku 
    
    where
    
     vend_no 
    
    is
    
     
    
    null
   
   

实际最大IO 为200K, 批执行时间为 15-30s.
这里有几个需要说明的问题:
(a) ,先create 再 insert 来替代select into
select into 在创建表并复制数据时会锁定系统表，特别是当大事务或大批量数据处理时，会对整个系统造成比较大的影响，即便从单个进程上来select into比之insert select减少了一些对于目的表结构、约束的检查，但是它对整个系统是不利的
(b) ,这里为什么用动态语句去创建索引及update
在整个sp中，以上五个语句是一个批，
如果写成:

SQL code

   
   

    
    
    
    create
    
     
    
    table
    
     #sku(sku_no 
    
    int
    
     
    
    null
    
    , vend_no 
    
    int
    
     
    
    null
    
    )

    
    insert
    
     #sku(sku_no) 
    
    select
    
     
    
    distinct
    
     sku_no 
    
    from
    
     inv_qty 
    
    where
    
     inv_type 
    
    =
    
    300
    
    

    
    create
    
     
    
    index
    
     idx1 
    
    on
    
     #sku(sku_no)

    
    update
    
     #sku 
    
    set
    
     vend_no 
    
    =
    
     b.vend_no 
    
    from
    
     #sku a,part b 
    
    where
    
     a.sku_no 
    
    =
    
     b.sku_no

    
    delete
    
     
    
    from
    
     #sku 
    
    where
    
     vend_no 
    
    is
    
     
    
    null
   
   

在这个批中，优化器进行操作时，实际上语句并未执行，所以这里貌似有索引，实际上用不到
用动态语句，那么，在新的内存空间中创建索引，在另一新的内存空间执行update时，动态语句会又开始选择优化，就会用到先前创建的索引
(c) ,为什么要有最后的一条delete语句
上面说了，inv_qty的sku并不一定存在于part表，调优的目的在于性能及对整个系统的影响，如果改变了结果，那么调优是绝对失败的。

3)

SQL code

   
   

    
    
    
    

    
    update
    
     #orders
    
    
    set
    
     cust_no 
    
    =
    
     b.cust_no
    
    
    from
    
     #orders a,Cur..history_header b
    
    
    where
    
     a.order_no 
    
    =
    
     b.order_no
        
    
    and
    
     a.order_type 
    
    =
    
     b.order_type
   
   

#orders 300w左右记录的临时表, Cur..history_header 数千万, HIS..history_header更大好多个数量级
#orders order_no + order_type 普通索引, history_header order_no + order_type 聚集索引
直接运行时,马上报错 IO over the limit 5MB

改用循环来更新

SQL code

   
   

    
    
    
    set
    
     
    
    rowcount
    
     
    
    10000
    
    

    
    select
    
     
    
    @@rowcount
    
    

    
    while
    
     
    
    @@rowcount
    
    >
    
    0
    
    

    
    update
    
     #orders
    
    
    set
    
     cust_no 
    
    =
    
     b.cust_no
    
    
    from
    
     #orders a,Cur..history_header b
    
    
    where
    
     a.order_no 
    
    =
    
     b.order_no
        
    
    and
    
     a.order_type 
    
    =
    
     b.order_type
        
    
    and
    
     a.cust_no 
    
    is
    
     
    
    null
    
    
        
    
    and
    
     b.cust_no 
    
    is
    
     
    
    not
    
     
    
    null
    
    

    
    set
    
     
    
    rowcount
    
     
    
    0
   
   

这里用了set rowcount, 当然有些人会使用在#order创建时就加identity列，再建索引于其上，然后以此来操作
and a.cust_no is null 这个容易理解, 为什么要加 and b.cust_no is not null
当history_header中cust_no 本身是null时，如果不限制，会陷入死循环, 因为它不停的又把null赋给#order.cust_no。

以上并没有什么技术难度，入门者都可以做的，写在这里，只是代表一种观念，花里胡梢的不一定是好的。