DB2索引问题有哪些?

DB2索引问题有哪些?

前段时间作项目,被数据库的查询效率所困扰,使用的数据库是DB2 8.2 。具体是这样:

表A(a_id, a_title, a_addr, .....) 该表大概50多个字段,200多万条记录,2G大小左右
表B(lib_id, b_id) 记录了b_id所对应的a_id记录集合,每个lib_id大概对应5万左右的A表记录,目前lib_id9个,总共记录数50万左右,以后可能会增长到50个lib_id,记录数达到250万

业务中经常要做表A和B的连接查询,比如下面的语句:( b.lib_id=361对应的记录大概4万条)

(1) select count(*) from A, B where A.a_id = B.b_id and b.lib_id=361
(2) select count(*) from A, B where A.a_id = B.b_id and A.a_addr = '中国' and b.lib_id=361
(3) select count(*) from A, B where A.a_id = B.b_id and A.a_addr like '%宁波%' and b.lib_id=361
(4) select count(*) from A, B where A.a_id = B.b_id and A.a_addr like '宁波%' and b.lib_id=361

分别给A,建立了三个索引a_id, a_title, a_addr,给表B建立了复合索引(b_id, a_id),结果:

(1) 速度很快 <1s
(2), (3), (4) 速度很慢,超过90s

百思不得其解,就给机器增加了一个硬盘,将表A所在的表空间平均分布到到两个硬盘上,希望能够并行处理。
不过效果不佳,也许是加的硬盘太老的原因(几年前的老硬盘)。如果两个都是SCSI硬盘,应该能够提高些性能。
根据DB2的索引设计程序,建议使用MDC表。同学建议我使用分区表(DB2 9.0 和ORCAL都有)。

不过,昨天这个问题基本解决了,没有使用MDC表,也没有使用分区表。也证明了不是服务器的性能问题。
其实解决办法很简单:就是给表A建立几个复合索引,分别是(a_title, a_id), (a_addr, a_id)。然后进行测试

测试命令:db2batch -d dbname -a username/password -f sqlfilename

(1) select count(*) from A, B where A.a_id = B.b_id 结果集40万 1.14s
(2) select count(*) from A, B where A.a_id = B.b_id and b.lib_id=361 结果集4万 0.72s
(3) select count(*) from A, B where A.a_id = B.b_id and a.addr like '宁波%' and b.lib_id=361 结果集2万 7.63s
(4) select count(*) from A, B where A.a_id = B.b_id and a.addr like '%宁波%' and b.lib_id=361 结果集2万 1.6s
(5) select count(*) from A, B where A.a_id = B.b_id and a.addr like '宁波%' 结果集24.8万 7.7s
(6) select count(*) from A, B where A.a_id = B.b_id and a.addr like '%宁波%' 结果集24.8万 1.86s

对比(3),(4)和(5),(6) 两组数据,发现like '%宁波%' 的速度比 like '宁波%'居然还要快出几倍!

经过研究,又给B表增加了一个索引 lib_id,结果(3)的执行时间减少到0.67s,(4)的时间1.55s.但是又如何来解释语句(5)和(6)之间的巨大差异呢?而且DB2给出的执行计划中(5)的成本只是(6)的20%左右!

经过反复实验,发现可能的原因是 like '宁波%'的结果集过大,因为在其它的查询例如 like '浙江%' 结果集在4k左右,确实比 like '%浙江%'效率高很多。



联想到之前,当我们的表只有20多万条记录时,没有建索引也飞快,可以总结以下经验:

1. 大表的索引很重要,服务器的配置对百万级数据库的性能影响不大。
2. 对于连接查询,应该建立复合索引。
3. 当结果集很大时,like '% .. %' 的效率可能比 like '.. %'高很多。(待理论验证)