专家介绍几种提高 DB2 查询性能的常用方法

提高查询性能的常用方法

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

创建索引

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

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

select add_datefrom temp.customerwhere 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.customerwhere city = 'WASHINGTON'and cntry_code = 'USA';2. select count(*) from temp.customerwhere city = 'WASHINGTON';3. select count(*) from temp.customerwhere cntry_code = 'USA';Results:1. 14042. 14073. 128700

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

表 1. 两个索引的性能和大小对比
[table=500]索引查询计划总代价索引大小cust_i1(city，cntry_code，add_date)28.94 timerons19.52Mcust_i3(city)63.29 timerons5.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 进行排序操作。

接下......收起