分享DB2 SQL查询性能问题一例

同事在测试服务器上遇到了一个严重的performance问题，请我帮忙（本人非专业DBA,只是相比同事多干了两年罢了）看看SQL调优和加index
此SQL是一个较复杂的查询，inner join/left join了多个表，其中有几个表的数据量都在百万级以上。
我拿到手并没多想，先看了SQL结构，没什么大问题。然后就跑了db2expln和db2advis，db2expln 也是显示每个表都走index了，没有扫表的

#### db2advis
Recommending indexes...
total disk space needed for initial set [ 682.400] MB
total disk space constrained to         [69886.654] MB
Trying variations of the solution set.
Optimization finished.
  9  indexes in current solution
[12511.0449] timerons  (without recommendations)
[6372.6714] timerons  (with current solution)
[49.06%] improvement

一看这不显然Index不足或者有问题嘛，那就按着建议的Index和存在的Index对照着来尝试喽。
试了多个Index后发现最多才5%的提升，很郁闷。但加到一个表的索引后发现timerons突然反而升高了..而且我加的新索引并未被使用..

Recommending indexes...
total disk space needed for initial set [ 660.656] MB
total disk space constrained to         [69886.654] MB
Trying variations of the solution set.
Optimization finished.
  9  indexes in current solution
 [22776.0215] timerons  (without recommendations)
 [22370.5879] timerons  (with current solution)
 [1.78%] improvement

另：db2expln多了些不同的信息

Left Outer Nested Loop Join
|  Piped Inner
|  Access Table Name = XXXXXX  ID = 599,4
|  |  Index Scan:  Name = YYYYYY  ID = 3
|  |  |  Regular Index (Not Clustered)
|  |  |  Index Columns:
|  |  |  |  1: DELETED (Ascending)
|  |  |  |  2: OPPORTUNITY_ID (Ascending)
|  |  |  |  3: ASSIGNED_USER_ID (Ascending)
|  |  |  |  4: TYPE (Ascending)
|  |  #Columns = 0
|  |  Compressed Table
|  |  #Key Columns = 2
|  |  |  Start Key: Inclusive Value
|  |  |  |  1: ? 
|  |  |  |  2: ? 
|  |  |  Stop Key: Inclusive Value
|  |  |  |  1: ? 
|  |  |  |  2: ? 
|  |  Index-Only Access
|  |  Index Prefetch: Sequential(99), Readahead
|  |  Isolation Level: Uncommitted Read
|  |  Lock Intents
|  |  |  Table: Intent None
|  |  |  Row  : None
|  |  Sargable Index Predicate(s)
|  |  |  #Predicates = 1
|  |  |  Insert Into Integer Sorted Temp Table  ID = t2
|  |  |  |  #Columns = 1
|  |  |  |  #Sort Key Columns = 1
|  |  |  |  |  Key 1: (Ascending)
|  |  |  |  Sortheap Allocation Parameters:
|  |  |  |  |  #Rows     = 28490.000000
|  |  |  |  |  Row Width = 20
|  |  |  |  Duplicate Elimination
|  Integer Sorted Temp Table Completion  ID = t2
|  List Prefetch Preparation
|  |  Access Table Name = XXXXXX  ID = 599,4
|  |  |  #Columns = 1
|  |  |  Compressed Table
|  |  |  RID List Fetch Scan
|  |  |  Fetch Using Prefetched List
|  |  |  |  Prefetch: 105 Pages
|  |  |  Isolation Level: Uncommitted Read
|  |  |  Lock Intents
|  |  |  |  Table: Intent None
|  |  |  |  Row  : None
|  |  |  Sargable Predicate(s)
|  |  |  |  #Predicates = 3

优化器查找和使用索引受到了新Index的影响以致影响了性能。
查一下索引统计信息：

select substr(INDNAME,1,25) as INDNAME, FULLKEYCARD 
from syscat.indexes 
where tabname='XXXXXX' and tabschema='SCTID'

#    INDNAME    FULLKEYCARD
1    YYYYYY        -1
2    SSSSSS        -1
3    XXXXXX(NEW)   3,844,196
4    ZZZZZZ        -1

说明是统计信息有问题才导致的查询性能问题..
在表上执行了RUNSTATS with indexes all后：

#    INDNAME    FULLKEYCARD
1    YYYYYY      4,985,715
2    YYYYYY      4,985,715
3    YYYYYY      3,844,196
4    YYYYYY      4,684,818

再次执行db2avis

Recommending indexes...
total disk space needed for initial set [ 802.328] MB
total disk space constrained to         [69986.213] MB
Trying variations of the solution set.
A better solution was found.  Continuing search.
Optimization finished.
  10  indexes in current solution
[5586.8818] timerons  (without recommendations)
[5165.5610] timerons  (with current solution)
[7.54%] improvement

得到这个结果后询问同事，原来他们前两天刚做过数据移植，这个表的数据量差不多翻了一倍..但是之后应该是没做过RUNSTATS或者reorg,重启之类的操作..

好吧，这次得到的经验教训就是下次遇到SQL性能问题，先查看表的统计信息是否正确再研究如何优化索引，不能一发现查询慢就先考虑优化索引。人还是要多读书..
希望本文能起到点抛砖引玉的作用吧。

参考：
低基数索引为什么会对性能产生负面影响