Sql优化DB2.doc

DB2 Sql优化与锁 80%性能的提升在于Sql和索引的修改。总结有以下几点： 1、不合理的sql 2、如何建立合理的索引 3、避免死锁和大量锁等待 下面针对这3个方面总结下要点。 1.编写高效的Sql注意要点 表连接 表连接有两个要点： 1、表连接顺序，2、连接条件 Select * from A left join B on A.id=B.id join C on B.id = C.C_id where A.con=’ ’ and B.con=’ ’ 一般情况下，DB2会根据各表的JOIN顺序自顶向下处理，即从Sql来看，就是自左向右解析,先A、B做连接操作，之后会产生结果集，将会写入内存，如果内存不够，会写入临时表空间，之后会用结果集和C做连接操作。如果sql中只有两表连接，那么其前后顺序没什么关系，优化器会自己去评估。而如果sql中存在超过2个表连接时，那么表连接就会有顺序之分。那么，原则是： 如果sql中存在表A、B、C三表连接，则首先应保证最先连接的两表具有较小的子集。 在进行表连接时，需要提供连接字段（即On语法后的等价谓词，on A.id=B.id）。此时，我们需要保证，连接字段存在索引。这样当结果集小时，会走Nest Join（速度快，因为会利用到索引），当结果集大时，会走Hash join。此外，在对A、B表进行连接时，优化器需要判断采用何种连接类型，这时会先执行where 字句后的条件。也就是说，如果where字句能过滤很多的条件，那么表连接的结果集就会很小，cost自然会降低，所以适当为where字句的查询字段建立索引，能够得到更好的性能。原则是： 在进行表连接时，为连接字段和查询过滤字段（where 字句后的条件）建立索引，会得到很好的性能提升。 在本次测试中，发现有的sql会在表连接时，为其指定多个连接条件，形如： SELECT B.APPROVE_STATUS, count ( * ) AS NUM FROM BIZ.WF_TASK C LEFT JOIN BIZ.REI_FORM B ON C.RECEIPT_NO = B.REI_FORM_ID WHERE C.TASK_STATUS = 01 AND C.HANDLE_ID = 1234560000102 AND (C.RECEIPT_TYPE = 02 OR C.RECEIPT_TYPE = 03) GROUP BY B.APPROVE_STATUS WITH UR 执行cost： 如果sql写成（增加一个表连接字段）： SELECT B.APPROVE_STATUS, count ( * ) AS NUM FROM BIZ.WF_TASK C LEFT JOIN BIZ.REI_FORM B ON C.RECEIPT_NO = B.REI_FORM_ID AND (C.RECEIPT_TYPE = 02 OR C.RECEIPT_TYPE = 03) WHERE C.TASK_STATUS = 01 AND C.HANDLE_ID = 1234560000102 GROUP BY B.APPROVE_STATUS WITH UR 对比结果，我们可以看到，当连接条件存在多个时，cost会高很多，因为多做了一次表连接。如果是小表，看不出差别，如果是大表关联，则结果很明显。原则是： 当进行表连接时，请确保连接条件只有一个，尤其是大表连接。 1.2 合理使用Not in 和Not Exists 虽然Not in 和Not exits 可以实现相同的功能，但是两者本身的实现方式不同： Not In：是自内向外操作，即先得到子查询结果，然后执行外层查询。包含not in 子句的执行顺序是：首先取外部一个查询结果与内部子集比较，不管是否存在，它都要遍历整个子集，往往无法利用到索引，因而是由内向外过程。所以，当内部查询子集很大时，就会具有较高的查询代价。 Not Exists：恰恰相反，是外向内操作。即先执行外部查询结果，然后再执行内部操作， 是集合操作。包含 not exists子句的执行顺序是：首先取外部一个查询结果与内部子集比较，若存在即刻返回，而不需要便利整个子集，如果存在索引，就会使用索引，因而是个自外而内的过程。所以，当内部子集很大时，相对来说，性能要优于Not in。 因而，总的来说，Not exits在整体性能上要由于Not in。原则是： 当子查询结果集较大时，Not exists 较 Not in 具有较高的性能提升； 当子查询结果集较小时（个数或者百数以内），两者相差不多，一般来说，此时Not in 会教优于Not exists。就好