【DB2】Sql优化与锁

本次XX项目性能测试，80%性能的提升在于Sql和索引的修改。总结有以下几点：

1） 不高效的sql（不合理的sql）

2） 不合理的索引（如何建立合理的索引）

3） 避免死锁和大量锁等待

下面针对这3个方面总结下要点。

1.编写高效的Sql注意要点

1.1 表连接

表连接有两个要点：

1） 表连接顺序

2） 连接条件

Sql_stmt_1:

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）。此时，我们需要保证，连接字段存在索引。这样当结果集小时，会走NestJoin（速度快，因为会利用到索引），当结果集大时，会走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。就好像表数据小时，全表扫描总是要由于索引扫描；

当子查询具有一定的复杂度时（即sql关联关系较多，如子查询句中包含多个表查询），由于内部查询的复杂度，会导致Not exists 查询具有较大的复杂度，降低性能。此时可以考虑采用Not in。

IN与Exists两者相差不多，这里不做比较，思路形同。

1.3 改写 OR和不等于（！=||<>）

我们在编写sql