提高DB2查询性能的常用方法（二）

下面我们将从三个方面介绍一些提高查询性能的方法。

　　创建索引

 

　　根据查询所使用的列建立多列索引

 

　　建立索引是用来提高查询性能最常用的方法。对于一个特定的查询，可以为某一个表所有出现在查询中的列建立一个联合索引，包括出现在 select 子句和条件语句中的列。但简单的建立一个覆盖所有列的索引并不一定能有效提高查询，因为在多列索引中列的顺序是非常重要的。这个特性是由于索引的 B+ 树结构决定的。一般情况下，要根据谓词的选择度来排列索引中各列的位置，选择度大的谓词所使用的列放在索引的前面，把那些只存在与 select 子句中的列放在索引的最后。譬如清单 5 中的查询：

 

　　清单5. 索引中的谓词位置        

 

 

  select add_date
from temp.customer
where city = 'WASHINGTON'
and cntry_code = 'USA';

 

　　对于这样的查询可以在 temp.customer 上建立 (city，cntry_code，add_date) 索引。由于该索引包含了 temp.customer 所有用到的列，此查询将不会访问 temp.customer 的数据页面，而直接使用了索引页面。对于包含多列的联合索引，索引树中的根节点和中间节点存储了多列的值的联合。这就决定了存在两种索引扫描。回到清单 5 中的查询，由于此查询在新建索引的第一列上存在谓词条件，DB2 能够根据这个谓词条件从索引树的根节点开始遍历，经过中间节点最后定位到某一个叶子节点，然后从此叶子节点开始往后进行在叶子节点上的索引扫描，直到找到所有满足条件的记录。这种索引扫描称之为 Matching Index Scan。但是如果将 add_date 放在索引的第一个位置，而查询并不存在 add_date 上的谓词条件，那么这个索引扫描将会从第一个索引叶子节点开始，它无法从根节点开始并经过中间节点直接定位到某一个叶子节点，这种扫描的范围扩大到了整个索引，我们称之为 Non-matching Index Scan。图 5 显示了 DB2 根据不同索引生成的存取计划。

 

　　图5. 根据不同索引生成的存取计划

 

　　

 

　　根据不同索引生成的存取计划

 

　　根据条件语句中的谓词的选择度创建索引

 

　　因为建立索引需要占用数据库的存储空间，所以需要在空间和时间性能之间进行权衡。很多时候，只考虑那些在条件子句中有条件判断的列上建立索引会也会同样有效，同时节约了空间。譬如清单 5 中的查询，可以只建立 (city，cntry_code) 索引。我们还可以进一步地检查条件语句中的这两个谓词的选择度，执行清单 6 中的语句检查谓词选择度：

 

　　清单 6.检查谓词选择度                
             

   Queries:
1. select count(*) from temp.customer
where city = 'WASHINGTON'
and cntry_code = 'USA';
2. select count(*) from temp.customer
where city = 'WASHINGTON';
3. select count(*) from temp.customer
where cntry_code = 'USA';
Results:
1. 1404
2. 1407
3. 128700

　　选择度越大，过滤掉的记录越多，返回的结果集也就越小。从清单 6 的结果可以看到，第二个查询的选择度几乎有和整个条件语句相同。因此可以直接建立单列索引 (city)，其性能与索引 (city，cntry_code，add_date) 具有相差不多的性能。表 1 中对两个索引的性能和大小进行了对比。

 

　　表 1. 两个索引的性能和大小对比

 

 

　　　　
索引 　　　　　　　　　　　　　　　　查询计划总代价 　　索引大小
cust_i1(city，cntry_code，add_date) 28.94 timerons 　　19.52M
cust_i3(city) 　　　　　　　　　　63.29 timerons　　　　5.48M

 

　　从表 1 中可以看到单列索引 (city) 具有更加有效的性能空间比，也就是说占有尽可能小的空间得到尽可能高的查询速度。

 

　　避免在建有索引的列上使用函数

 

　　这是一个很简单的原则，如果在建有索引的列上使用函数，由于函数的单调性不确定，函数的返回值和输入值可能不会一一对应，就可能存在索引中位置差异很大的多个列值可以满足带有函数的谓词条件，因此 DB2 优化器将无法进行 Matching Index Scan，更坏的情况下可能会导致直接进行表扫描。图 6 中对比了使用 function 前后的存取计划的变化。

 

　　

 

　　
　　图6. 使用 function 前后的存取计划的变化
 

 

　　在那些需要被排序的列上创建索引

 

　　这里的排序不仅仅指 order by 子句，还包括 distinct 和 group by 子句，他们都会产生排序的操作。由于索引本身是有序的，在其创建过程中已经进行了排序处理，因此在应用这些语句的列上创建索引会降低排序操作的代价。这种情况一般针对于没有条件语句的查询。如果存在条件语句，DB2 优化器会首先选择出满足条件的纪录，然后才对中间结果集进行排序。对于没有条件语句的查询，排序操作在总的查询代价中会占有较大比重，因此能够较大限度的利用索引的排序结构进行查询优化。此时可以创建单列索引，如果需要创建联合索引则需要把被排序的列放在联合索引的第一列。图 7 对比了清单 7 中的查询在创建索引前后的存取计划。

 

　　清单7. 查询在创建索引前后的存取计划

 

              

  select distinct add_date from temp.customer;

 

　　

 

　　图7. 在创建索引前后的存取计划

 

　　在创建索引前后的存取计划
 
　　从图 7 中我们可以看到在没有索引的情况下 SORT 操作是 24751.69 timerons，但是有索引的情况下，不再需要对结果集进行排序，可以直接进行 UNIQUE 操作，表中显示了这一操作只花费了 2499.98 timerons.

 

　　图8对比了清单 8 中的查询在创建联合索引前后的存取计划，从中可以更好的理解索引对排序操作的优化。

 

　　清单8. 查询示例

 

 

               
select cust_name from temp.customer order by add_date;

 

　　

 

　　图8. 创建联合索引前后的存取计划

 

　　索引的 B+ 树结构决定了索引 temp.cust_i5 的所有叶子节点本身就是按照 add_date 排序的，所以对于清单 8 中的查询，只需要顺序扫描索引 temp.cust_i5 的所有叶子节点。但是对于 temp.cust_i6 索引，其所有叶子节点是按照 cust_name 排序，因此在经过对索引的叶子节点扫描获得所有数据之后，还需要对 add_date 进行排序操作。

 

　　合理使用include关键词创建索引

 

　　对于类似下面的查询 :

 

　　清单9. 查询示例               

 

 

select cust_name from temp.customer
where cust_num between '0007000000' and '0007200000'

 

　　在第一点中我们提到可以在 cust_num 和 cust_name 上建立联合索引来提高查询性能。但是由于 cust_num 是主键，可以使用 include 关键字创建唯一性索引：

 

 

create unique index temp.cust_i7 on temp.customer(cust_num) include (cust_name)

 

　　使用 include 后，cust_name 列的数据将只存在于索引树的叶子节点，并不存在于索引的关键字中。这种情况下，使用带有 include 列的唯一索引会带来优于联合索引的性能，因为唯一索引能够避免一些不必要的操作，如排序。对于清单 9 中的查询创建索引 temp.cust_i7 后存取计划的代价为 12338.7 timerons，创建联合索引 temp.cust_i8(cust_num，cust_name) 后的代价为 12363.17 timerons。一般情况下，当查询的 where 子句中存在主键的谓词我们就可以创建带有 include 列的唯一索引，形成纯索引访问来提高查询性能。注意 include 只能用在创建唯一性索引中。

 

　　指定索引的排序属性

 

　　对于下面用来显示最近一个员工入职的时间的查询：

 

 

select max(add_date) from temp.employee

 

　　很显然这个查询会进行全表扫描。查询计划如图 9.a:

 

　　图 9. 查询计划

 

　　查询计划

 

　　显然我们可以在 add_date 上创建索引。根据下面的命令创建索引后的查询计划如图 9.b。

 

 

create index temp.employee_i1 on temp.employee(add_date)

 

　　这里存在一个误区，大家可能认为既然查询里要取得的是 add_date 的最大值，而我们又在 add_date 上建立了一个索引，优化器应该知道从索引树中直接去寻找最大值。但是实际情况并非如此，因为创建索引的时候并没有指定排序属性，默认为 ASC 升序排列，DB2 将会扫描整个索引树的叶子节点取得所有值后，然后取其最大。我们可以通过设置索引的排序属性来提高查询性能，根据下面的命令创建索引后的查询计划如图 9.c。

 

 

create index temp.employee_i1 on temp.employee(add_date desc)

 

　　对于降序排列的索引，DB2 不需要扫描整个索引数的叶子节点，因为第一个节点便是最大的。我们同样可以使用 ALLOW REVERSE SCANS 来指定索引为双向扫描，具有和 DESC 近似的查询性能。ALLOW REVERSE SCANS 可以被认为是 ASC 和 DESC 的组合，只是在以后数据更新的时候维护成本会相对高一些。

 

　　如果无法改变索引的排序属性，但是我们具有额外的信息，该公司每个月都会有新员工入职，那么这个查询就可以改写成：

 

 

select max(add_date) from temp.employee where add_date > current timestamp - 1 month

 

　　这样通过限定一个查询范围也会有效地提高查询性能。