对数据库开发和调优积累的一些简单易用的小技巧

1、从执行30分钟到执行0.9秒的SQL简单优化

在一个系统发现过一条这样的分页SQL：(为了方便阅读，把实际的表名和字段名简化)

清单1，有缺陷的分页SQL：

[table=98%,silver]
[td=1,1,568]  

select  * from (

  

         select

  

                   rownumber() over() as  rownum, temp1.*

  

         from(

  

                   select  

  

           a.col1, b.col2, c.col3, d.col4,  e.col5

  

                   from

  

                            taba a

  

left join tabb b  

  

                                     ON a.col1 = b.col1

  

                            left join tabc c

  

                                     ON a.col1 = c.col1

  

                            left join tabd d

  

                                     ON a.col1 = d.col1

  

                            left join tabe e

  

                                     ON a.col1 = c.col1

  

                   where

  

                            (a.col1,a.col2,a.col3,a.col4,a.col5)  = (?,?,?,?,?)

  

         ) temp1

  

         where (temp1.col1,temp1,col2) in (select  f.col1, f.col2 from tabf f where f.col1=?)

  

)  temp2

  

WHERE  ROWNUM BETWEEN ? AND ? with ur

  

从清单1看出，这个SQL的temp1做了很多联表查询，而且taba表与其它表进行链接都使用taba的col1字段作为链接条件。而temp2的出现,纯属是为了生成rownumber做分页。而taba是一张千万级的数据表，tabb、tabc、tabd、tabe是在两百万级的数据表。后来与系统开发人员沟通得知，运行这个SQL每次取1000行数据需要半个小时(这些表都有索引，并且在查询计划中也看到已应用上了)。从这个SQL可以看出，瓶颈不在于取1000条还是取1条数据的操作中，而是在链表的庞大数据集中（temp1），而促使数据集变得庞大是因为在主表(taba)上没有做到合理的控制。如果我们变换一下思路，可以把分页条件应用于主表（taba）中，那就可以把整个结果集都缩小了。

清单2，改进后的SQL

[table=98%,silver]
[td=1,1,568]  

with t as (

  

select

  

    *,rownumber() over() rownum

  

from

  

       taba a

  

where

  

             (a.col1,a.col2,a.col3,a.col4,a.col5) =  (?,?,?,?,?)

  

)

  

select  

  

         rownumber() over() as rnr, temp1.*

  

from(

  

         select  

  

       a.col1, b.col2, c.col3, d.col4, e.col5

  

         from

  

             (select * from t where rownum between ? and ? ) a

  

left join tabb b  

  

                            ON a.col1 = b.col1

  

                   left join tabc c

  

                            ON a.col1 = c.col1

  

                   left join tabd d

  

                            ON  a.col1 = d.col1

  

                   left join tabe e

  

                            ON a.col1 = c.col1

  

)  temp1

  

where  (temp1.col1,temp1,col2) in (select f.col1, f.col2 from tabf f where f.col1=?)

  

通过简单的把分页条件应用于taba后，速度大幅提高，从之前的平均半个小时执行时间变成了平均0.9秒。

注意：由于优化后temp1的where条件依然保留在外面，所以导致优化前和优化后的分页结果出现了一点差别。这个优化案例之所以客户能接受这样，是因为系统本身是进行分批导出并累计生成到结果文件里面，所有没有导致到直接的影响，如果严格要求每页的数据一致，以上的方案还需进一步优化

2、合理使用+C，避免不必要的工作

接触过很多客户还在使用CLP进行投产上线更新DB2，而在投产前要求严格进行投产演练，但在演练的过程中常常遇到这样的一个现象：

步骤1、使用命令db2 -tvsf db_update.sql -zdb_update.log执行文件中的SQL

步骤2、文件中的其中一条SQL存在问题，中断往下执行

步骤3、把之前已成功更新的对象全部都手动删除或者修改回更新前的状态(类似回滚)，然后重新演练保证一次执行完全通过。

如果稍微在以上的命令增加一个参数(见黄底)，步骤3就省略许多了：db2 +c -tvsf db_update.sql -z db_update.log ，当遇到步骤2的情况时，只需执行db2 rollback，DB2则为你做了所有的回滚工作,这样大大避免了不必要的工作.

注意：如果执行失败后，继续需要在当前中断做数据库操作时，除开db2 rollback 命令，其它命令都需要在DB2后面+C，否则CLP也会默认发出commit命令

3、尽量使merge代替存储过程中游标

清单3，一个逻辑简单的存储过程

[td=1,1,568]  

declare v_col1 varchar(10) ;

  

declare v_col2 date ;

  

declare v_timestamp varchar(20) ;

  

declare mycursor cursor for select  col1,col2 from taba
; --定义游标

  

open mycursor ;

  

fetch from mycursor into v_col1,v_col2 ;  --取游标一行值

  

while SQLSTATE='00000'  --循环，如果SQLSTATE=00000则表示没取到数据

  

         do

  

         if  v_col1 is null then  --如果字段v_col1为空，则设置字符串’is null’

  

                   set  col1 = 'is null';

  

         end  if;

  

         if  v_col2 > current date then --如果v_col2大于当前日期，则设置为当前日期

  

                   set  col2 = current date;

  

         end  if;

  

    --取时间戳格式化成yyyymmddhh24missFF6的字符串，其中FF6表示微秒

  

         set  v_timestamp = to_char(current timestamp,'yyyymmddhh24missFF6');

  

         --如果v_col1,v_col2存在tabb表，并且tabb表中col3为空，则update

  

         if  exists(select 1 from tabb where (col1,col2) = (v_col1,v_col2) and col3 is  null ) then

  

                   update  tabb set col3 = v_timestamp where (col1,col2) = (v_col1,v_col2) and col3 is  null;

  

         else  --否则insert

  

                   insert  tabb values(v_col1,v_col2,v_timestamp) ;

  

         end  if;

  

         fetch  from mycursor into v_col1,col2 ;

  

end while;

  

close mycursor;

  

以上的伪代码的逻辑：通过定义游标遍历taba表，如果taba表字段col1为空，则设置字符串为’is null’，如果taba表字段col2大于当前日期，则设置为当前日期。判断taba的col1和col2是否存在于tabb表中，并且tabb的col3字段为空，如果为真，则update，否则insert。可以使用简单的merge语句进行代替

清单4，代替游标的merge

[td=1,1,568]  

merge into tabb b using taba a on a.col1  = b.col1

  

--如果v_col1,v_col2存在tabb表，并且tabb表中col3为空，则update

  

when matched and b.col3 is null then

  

         update  set col3 = to_char(current timestamp,'yyyymmddhh24missFF6')

  

when not matched then --否则insert

  

         insert  (b.col1,b.col2,b.col3)

  

         values  (a.col1,

  

     case when a.col2 > current date then current date else a.col2 end,

  

     to_char(current timestamp,'yyyymmddhh24missFF6'))

  

else ignore;

  


4、常常被忽略的空变量

常常遇到网友问的同一个问题，就是在存储过程或者自定义函数处理一大堆的字符串，结果返回来的是空，下面可以给出答案：

清单5，使用数值和字符与空值相加

[table=98%,rgb(242, 242, 242)]
  

G:UsersAdministrator>db2 “values  1+null”
  1
  -----------
            -
    1 条记录已选择。

  

G:UsersAdministrator>db2 “values  length(‘   ‘ concat null)”
  1
  -----------
            -
    1 条记录已选择。

  
  

返回了- 表示NULL

  

清单6，应该改成这样使用：

[table=98%,rgb(242, 242, 242)]
  

G:UsersAdministrator>db2  "values 1+coalesce(null,0)"

  

1

  

-----------

  

           1

  

  1  条记录已选择。

  

G:UsersAdministrator>db2  "values  ' ' concat  coalesce(null,'')"

  

1

  
  
  

  1  条记录已选择。

  
  

COALESCE可以理解为 à

  

CASE

  

WHEN COL1 IS NULL

  

THEN 0

  

ELSE COL1

  

END

  


5、简单易用的复合语句块可以解决很多重复工作

曾经有个网友跟我说他的系统有4000张表，领导要求他统计出每个表的当前数据量，当时我给他第一个方案是去查SYSCAT.TABLES.CARD字段，后来得知他们系统很少做RUNSTATS和REORG，所以我就给他写了这段简单的代码为他解决了这个需求。

清单7，通过复合语句，完成以下功能，统计SYSCAT模式下的每个视图的的数据总行数

begin       declare v_sql varchar(256);       if exists (select 1 from syscat.tables where tabname='TABLEINFO') then          execute immediate 'drop table tableinfo'; --删除表       end if;       execute immediate    --创建表    'create table tableinfo ('    '    tabschema varchar(128) , '    '    tatabname varchar(128) ,  '    '    rows bigint  )';       for sub as         select    tabschema,tabname         from  syscat.tables         where tabschema='SYSCAT'       do    --组合SQL：inser into tableinfo select ？,？,count(*) from ？,？          values (    'insert into tableinfo select  '''  concat         sub.tabschema concat ''',''' concat         sub.tabname concat ''',count(*) from ' concat         sub.tabschema concat '.' concat sub.tabname)            into v_sql;[i][/i]           execute  immediate v_sql; --执行    end  for;   end;

a、
判断tableinfo表是否存在，存在则删除

b、
创建tableinfo表

c、
通过FOR循环遍历SYSCAT.TABLES表，条件未TABSCHEMA=’SYSCAT’

组合成inserinto tableinfo select ？,？,count(*) from ？,？ ，然后实用EXECUTEIMMEDIATE进行执行，把SYSCAT模式下的每个表的总是插入到tableinfo中.

复合语句相对存储过程来说,编写得更容易和简单些,但是也有很多功能是用复合语句无法实现的.

6、generated always的另一种用处

曾经跟一个DBA交流，他跟我发牢骚说他系统出现这种情况，他最近在系统发现有一条SQL占用系统资源非常高，原因是在WHERE条件后面的字段使用函数，导致索引失效并出现了全表扫描(表大约2000多万行，50个字段，数据周期是一年，按当时的业务量还每天在往上增加)，查一次大约花了4分钟。而当时所负责这系统的开发公司合同维护期已过，并且所开发这个系统的开发人员也离职了(一个人开发一个系统！牛逼)，按当时的情况也是远水救不了近火。而他领导的意思也是要求在现有的硬件资源基础上进行优化，这个意思大概也是这样了，优化应用恐怕也来不及，因为投诉天天催。加资源领导又不愿意，因为…。他让我帮他出个应急的方案。后来我问他，在现有的表的基础上可以加字段吗？他说可以加，最多等出问题再把字段删除，然后我就介绍他使用generated always字段。

清单8，原来大概的SQL

[td=1,1,568]  

select city,OPEN_DATE  --后面还有很多字段。。。

  

   from DB2INST1.CUST_INFO

  

   WHERE          --OPEN_DATE为INT类型，转换后再比较

  

     to_date(OPEN_DATE,'yyyymmdd') >= '2012-06-01' and

  

     to_date(OPEN_DATE,'yyyymmdd') <= '2012-06-30'

  

   order by 1,2

  

T_CUST_INFO的OPEN_DATE由于使用上了day而导致索引失效

图1（见附件）

如图一所示，进行了全表扫描，接下来通过增加generated always字段进行解决。新建一个字段并自动生成为DATE类型，再在generated always字段的基础上建立索引。

清单9，增加generated always字段步骤

[td=1,1,568]  

set integrity for cust_info off no access  cascade deferred ; --挂起表

  

alter table cust_info add column  opendate2

  

generated always as  (date(to_date(dateint,'yyyymmdd'))); --增加generated always字段

  

set integrity for cust_info immediate  checked force generated; --一致性检查和生成数据

  

--创建索引并对该索引刷新统计信息

  

create index cust_info_idx2 on  cust_info(opendate2 asc) collect detailed statistics;

  

再根据清单8的查询并得到的查询计划

图2（见附件）

看上图效果对比总是显得那么意外^_^，后来根据DBA的反馈，速度从之前的平均4分钟提高到了10秒把需要的数据返回，而且增加字段没对应用存在影响。