寻找数据访问的"最优解": DB2 数据库索引设计的最佳实践

字数 14470阅读 3410评论 4赞 0

转自: <a href="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/" target="_blank">http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/</a><a name="major1">序言</a>索引 (Index) 是关系型数据库中非常重要的一个概念，一般情况下，索引都会带来查询性能的提高。对于数据库管理员 (DBA) 来说 , 为数据库创建索引是他们工作中一个很重要的部分。通常来说，索引的设计是基于数据库中表的结构或者表的逻辑关系。比如说每个表的主键（Primary-key）其实都是一个索引，而记录雇员信息的 EMP 表中员工的编号 ID 列通常也会被建立索引。但是有经验的数据库管理员都知道 , 设计合适的索引并不是一件简单的事情。在实际的生产环境中，编写应用程序的程序开发人员往往并不像数据库管理员那样了解数据库的设计，所以一个常见的情况是，程序开发人员写的 SQL 查询语句可能没有办法利用数据库管理员建立的索引，从而出现数据库响应缓慢，SQL 查询性能不佳的问题。如果出现了这种情况，要想提高查询性能的话，一个可行的办法是数据库管理员分析该条 SQL 查询语句，并为其设计新的索引。如何分析 SQL 查询语句并设计出最合适的索引？本文将给出解决这种问题的一些最佳实践。需要指出的是，索引并不是多多益善 , 因为索引本身也需要占用数据库中一定的空间，同时数据库维护索引所需要进行的操作也会带来额外的性能开销，所以在个别情况下 , 不合适的索引甚至有可能带来数据库性能上的损失。另外一种情况是 , 即使数据库管理员根据业务逻辑中的 SQL 语句建立了某个索引，数据库在运行时也可能并没有利用那条索引，这是一种更加复杂的情况。也正因为如此，数据库管理员在为数据库建立新的索引时往往都会非常谨慎，避免出现得不偿失的结果。DB2 数据库充分考虑到了这一点，所以它引入了虚拟索引（Virtual index）的概念。通过这种虚拟索引的机制，数据库管理员可以在设计了新的索引之后来对其进行性能上的验证，如果性能提高达到预期效果再去建立真正的索引。本文也将对 DB2 数据库中的这种机制进行简单介绍。<div class="ibm-alternate-rule"></div><a class="ibm-anchor-up-link" href="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/#ibm-pcon">回页首</a><a name="major2">寻找数据访问的“最优解”:DB2 数据库索引设计的最佳实践</a>数据库索引，是数据库中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引就像是一本书的目录，可以快速定位书的内容。建立合适的索引可以对查询性能有成百上千倍的提高。下面我们看看索引是如何在 DB2 数据库里做到这一点的。这里是一个客户管理软件的 SQL 语句，访问客户表 CUSTOMER，希望返回用户名是 MARIA 的客户资料。<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> SELECT * FROM CUSTOMER WHERE C_FIRST_NAME ='MARIA'
</td></tr></tbody></table>  CUSTOMER 表的定义如下：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> CREATE TABLE CUSTOMER ( 
 C_CUSTOMER_ID CHAR(16) NOT NULL, 
 C_FIRST_NAME CHAR(20) NOT NULL, 
 C_LAST_NAME CHAR(30) NOT NULL, 
 C_BIRTH_DAY INTEGER NOT NULL, 
 C_BIRTH_MONTH INTEGER NOT NULL, 
 C_BIRTH_YEAR INTEGER NOT NULL, 
 …
 ) 
</td></tr></tbody></table>  数据库的表数据存储在磁盘上，在没有索引的情况下，DB2 会依此读取该表全部的数据页，查找并返回用户名是 MARIA 的记录。数据库的这种查询数据的方式称作全表扫描（TB-SCAN）。 <a name="fig1">图 1. 全表扫描</a> <a class="example-image-link" href="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/image002.jpg" width="439" height="92" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/image002.jpg" width="439" height="92" alt="图 1. 全表扫描" ></a> 若在 CUSTOMER 表上有合适的索引，数据库可以通过索引来加速查询过程。在 DB2 数据库中索引采用的是 B+ 树的结构，索引的叶子节点上包含索引键的值和一个指向数据地址的指针。DB2 先查询索引，然后通过索引里记录的指针，直接访问表的数据页。这样做不需要访问 CUSTOMER 表的全部数据页，只需要访问很少量的数据页就可以返回查询结果，从而大大加速了查询速度。那么 DB2 扫描索引的过程是怎样的呢？扫描索引的过程可以分成三类， 匹配索引扫描 (Matching index scan)，不匹配索引扫描 (Non-matching index scan)，和只扫描索引（Index only scan）。假定在 CUSTOMER 表中存在如下的索引 CUSTOMER_IDX_01：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> CREATE INDEX CUSTOMER_IDX_01 ON CUSTOMER 
 (C_FIRST_NAME ASC) 
</td></tr></tbody></table>  对于之前的 SQL 语句，谓词 C_FIRST_NAME ='MARIA'中指定了 C_ FIRST _NAME 列的值，所以 DB2 可以利用索引 CUSTOMER_IDX_01 直接定位到叶节点，再访问表的对应的数据页。这种索引访问方式就叫做匹配索引扫描。 <a name="fig2">图 2. 匹配索引扫描</a> <a class="example-image-link" href="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/image004.jpg" width="378" height="220" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/image004.jpg" width="378" height="220" alt="图 2. 匹配索引扫描" ></a> 再看另外一个 SQL 语句：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> SELECT * 
 FROM DB2ADMIN.CUSTOMER 
 WHERE C_FIRST_NAME = 'MARIA' 
		 AND C_BIRTH_YEAR = 1977 
</td></tr></tbody></table>  假设 CUSTOMER 表上只有如下的索引 CUSTOMER_IDX_02：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> CREATE INDEX CUSTOMER_IDX_02 ON CUSTOMER"
 (C_FIRST_NAME ASC, 
 C_LAST_NAME ASC, 
 C_BIRTH_YEAR ASC)

</td></tr></tbody></table>  DB2 访问索引的时候会做如下步骤：<ol type="1"><li>先利用索引的第一个键 C_FIRST_NAME 限定范围 C_FIRST_NAME = 'MARIA'，</li><li>对于另一个谓词 C_BIRTH_YEAR = 1977，由于 C_BIRTH_YEAR 是索引第三个键，所以 DB2 无法根据它直接找到对应的索引叶节点，而只能从满足条件 C_FIRST_NAME='MARIA' 的全部索引叶节点中扫描选取满足 C_BIRTH_YEAR = 1977 的叶节点。（这一步骤被称作 Screening，即“筛选”）。</li></ol>这种需要从索引叶节点中逐个扫描筛选条件的访问方式就叫做不匹配索引扫描。 <a name="fig3">图 3. 不匹配索引扫描</a> <a class="example-image-link" href="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/image006.jpg" width="407" height="239" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/image006.jpg" width="407" height="239" alt="图 3. 不匹配索引扫描" ></a> 再看另外一个例子，给定如下 SQL 语句：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> SELECT C_BIRTH_YEAR 
 FROM CUSTOMER 
 WHERE C_BIRTH_YEAR>1977 
</td></tr></tbody></table>  并且假设 CUSTOMER 表上只有索引 CUSTOMER_IDX_03：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> CREATE INDEX CUSTOMER_IDX_03 ON CUSTOMER 
 (C_BIRTH_YEAR ASC) 
</td></tr></tbody></table>  可以看出查询返回的数据恰恰就是 CUSTOMER_IDX_03 中的索引键，此时 DB2 不用访问磁盘上表的数据页，只需要扫描索引就可以得到对应列的值。这种访问方式就是只做索引扫描。 <a name="fig4">图 4. 只做索引扫描</a> <a class="example-image-link" href="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/image008.jpg" width="369" height="155" data-lightbox="example-set"><img class="example-image" src="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/image008.jpg" width="369" height="155" alt="图 4. 只做索引扫描" ></a> <a name="minor2.1">设计索引第一步</a>正如之前提到的，如果想通过建立新索引的方式来提高某条 SQL 语句的查询性能，我们就需要分析这条 SQL 语句的语义特点。在开始分析 SQL 之前，需要先了解一个名词 Boolean-term（可以译为“布尔项”）。Boolean-term 的概念Boolean-term 是 SQL 语句的 WHERE 子句中的一种谓词（Predicate），如果该谓词的取值为假（False），那么整个 WHERE 子句的返回值就为假。换句话说，Boolean-term 就像是能够控制整个 WHERE 子句的开关，对于数据库表中被处理的每一条数据记录（Row），一旦该数据记录不满足 Boolean-term 的判断条件，那么这条数据记录就被认为是不满足整个 WHERE 子句的判断条件。下面通过具体的 SQL 例子来说明这个概念。在下面这条 SQL 语句中：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline">SELECT * FROM EMP 
 WHERE WORKDEPT = 'A00'
 AND (SALARY > 40000 OR BONUS > 800) 
</td></tr></tbody></table>  可以看到三个基本谓词：WORKDEPT = 'A00'，SALARY > 40000 以及 BONUS > 800。其中只有 WORKDEPT = 'A00'满足 Boolean-term 的定义，也就是说对于 EMP 表中的每一条数据记录，如果它不满足 WORKDEPT = 'A00'的条件，那么它就不满足整个 WHERE 子句的条件，从而也就不会被作为这条 SQL 查询的结果被返回。而对于谓词 SALARY > 40000 来说，即使某条数据记录不满足这个条件，该记录也有可能因为满足 BONUS > 800 和 WORKDEPT = 'A00 而被作为查询结果返回，所以谓词 SALARY > 40000 不是 Boolean-term。同理，谓词 BONUS > 800 也不是 Boolean-term。通过这样的例子可以看出，在 SQL 形式上，Boolean-term 是不能出现在用 OR 连接的谓词里面，它必须是用 AND 与其他的谓词相连接。之所以要在这里介绍 Boolean-term，是因为它是与 DB2 数据库中索引访问密切相关的一个重要概念。在上面这个例子中，因为 SALARY > 40000 和 BONUS > 800 都不是 Boolean-term，所以即使存在某个索引包括 SALARY 列或者 BONUS 列，DB2 也不会选择这个索引来进行索引匹配扫描（Matching index scan）。正因如此，在设计新索引的时候，我们也就无需去考虑这种不是 Boolean-term 的谓词。为了严格起见，我们需要在此说明的是，对于这个例子中 (SALARY > 40000 OR BONUS > 800) 这样的非 Boolean-term 的谓词，DB2 可以通过多索引扫描（multi-index access）的方式来同时利用多个索引扫描数据。多索引扫描以及对应的多索引设计相对单个索引的访问和设计来说都是更复杂的技术，本文在此不做讨论。本文所涉及的内容均只针对单个索引的设计，因此本文中出现的 SQL 语句中 WHERE 子句不会出现用 OR 连接的谓词。从谓词中确定候选索引键分析 SQL 语句最基本的一步，就是在 WHERE 子句的所有 Boolean-term 中找到所有的 Indexable predicates，并根据其中引用到的列来设计索引键（Index Key）。顾名思义，Indexable predicate 意思就是“可以使用索引的谓词”。从逻辑上来说，按照这种谓词中给定的条件，DB2 数据库可以用索引访问的方式来在索引树中快速找到一个或多个相匹配的记录。需要注意的是，Indexable predicates 这个概念关注的是谓词本身的写法使得通过索引来访问数据成为可能，而它并不能保证在数据库中合适的索引是存在的，也不能保证 DB2 数据库在运行时一定会通过索引访问的方式来筛选满足这个谓词条件的数据；但是反过来，如果一个谓词不是 Indexable 的形式，那么数据库则肯定不能通过索引来筛选满足条件的数据。换而言之，“谓词是 Indexable的形式”是“数据库能使用索引访问来筛选数据”的必要非充分条件。那么什么样的谓词是“可以使用索引的谓词”？表 1 列出了每一种谓词的形式，并标明了它是否属于 Indexable predicate。 <a name="minor2.1">表 1. Indexable 谓词表</a> <table class="ibm-data-table" border="0" cellSpacing="0" summary="Indexable 谓词表" cellPadding="0"><tbody><tr><th>谓词类型</th><th>Indexable</th><th>谓词类型</th><th>Indexable</th><th>谓词类型</th><td>Indexable</td></tr><tr><td> COL = value </td><td> Y </td><td> COL IN (cor subq) </td><td> Y </td><td> COL op (noncor subq) </td><td> Y </td></tr><tr><td> COL = noncol expr </td><td> Y </td><td> (COL1,...COLn) IN (cor subq) </td><td> N </td><td> COL op ANY (noncor subq) </td><td> Y </td></tr><tr><td> COL IS NULL </td><td> Y </td><td> COL NOT IN (cor subq) </td><td> N </td><td> COL op ALL (noncor subq) </td><td> Y </td></tr><tr><td> COL op value </td><td> Y </td><td> (COL1,...COLn) NOT IN (cor subq) </td><td> N </td><td> COL <> (noncor subq) </td><td> N </td></tr><tr><td> COL op noncol expr </td><td> Y </td><td> COL IS DISTINCT FROM value </td><td> N </td><td> COL <> ANY (noncor subq) </td><td> N </td></tr><tr><td> COL BETWEEN value1 AND value2 </td><td> Y </td><td> COL IS NOT DISTINCT FROM value </td><td> Y </td><td> COL <> ALL (noncor subq) </td><td> N </td></tr><tr><td> COL BETWEEN noncol expr1 AND noncol expr2 </td><td> Y </td><td> COL IS DISTINCT FROM noncol expr </td><td> N </td><td> COL IN (noncor subq) </td><td> Y </td></tr><tr><td> value BETWEEN COL1 AND COL2 </td><td> N </td><td> COL IS NOT DISTINCT FROM noncol expr </td><td> Y </td><td> (COL1,...COLn) IN (noncor subq) </td><td> Y </td></tr><tr><td> COL BETWEEN COL1 AND COL2 </td><td> N </td><td> T1.COL1 IS DISTINCT FROM T2.COL2 </td><td> N </td><td> COL NOT IN (noncor subq) </td><td> N </td></tr><tr><td> COL BETWEEN expression1 AND expression2 </td><td> Y </td><td> T1.COL1 IS NOT DISTINCT FROM T2.COL2 </td><td> N </td><td> (COL1,...COLn) NOT IN (noncor subq) </td><td> N </td></tr><tr><td> COL LIKE 'pattern'</td><td> Y </td><td> T1.COL1 IS DISTINCT FROM T2 col expr </td><td> N </td><td> COL = (cor subq) </td><td> N </td></tr><tr><td> COL IN (list) </td><td> Y </td><td> T1.COL1 IS NOT DISTINCT FROM T2 col expr </td><td> Y </td><td> COL = ANY (cor subq) </td><td> Y </td></tr><tr><td> COL <> value </td><td> N </td><td> COL IS DISTINCT FROM (noncor subq) </td><td> N </td><td> COL = ALL (cor subq) </td><td> N </td></tr><tr><td> COL <> noncol expr </td><td> N </td><td> COL IS NOT DISTINCT FROM (noncor subq) </td><td> Y </td><td> COL op (cor subq) </td><td> N </td></tr><tr><td> COL IS NOT NULL </td><td> Y </td><td> COL IS DISTINCT FROM ANY (noncor subq) </td><td> N </td><td> COL op ANY (cor subq) </td><td> N </td></tr><tr><td> COL NOT BETWEEN value1 AND value2 </td><td> N </td><td> COL IS NOT DISTINCT FROM ANY (noncor subq) </td><td> N </td><td> COL op ALL (cor subq) </td><td> N </td></tr><tr><td> COL NOT BETWEEN noncol expr1 AND noncol expr2 </td><td> N </td><td> COL IS DISTINCT FROM ALL (noncor subq) </td><td> N </td><td> COL <> (cor subq) </td><td> N </td></tr><tr><td> value NOT BETWEEN COL1 AND COL2 </td><td> N </td><td> COL IS NOT DISTINCT FROM ALL (noncor subq) </td><td> N </td><td> COL <> ANY (cor subq) </td><td> N </td></tr><tr><td> COL NOT IN (list) </td><td> N </td><td> COL IS NOT DISTINCT FROM (cor subq) </td><td> N </td><td> COL <> ALL (cor subq) </td><td> N </td></tr><tr><td> COL NOT LIKE ' char'</td><td> N </td><td> COL IS DISTINCT FROM ANY (cor subq) </td><td> N </td><td> XMLEXISTS </td><td> Y </td></tr><tr><td> COL LIKE '%char'</td><td> N </td><td> COL IS DISTINCT FROM ANY (cor subq) </td><td> N </td><td> NOT XMLEXISTS </td><td> N </td></tr><tr><td> COL LIKE '_char'</td><td> N </td><td> COL IS NOT DISTINCT FROM ANY (cor subq) </td><td> N </td><td> COL = ANY (noncor subq) </td><td> Y </td></tr><tr><td> COL LIKE host variable </td><td> Y </td><td> COL IS DISTINCT FROM ALL (cor subq) </td><td> N </td><td> COL = ALL (noncor subq) </td><td> N </td></tr><tr><td> T1.COL = T2 col expr </td><td> Y </td><td> COL IS NOT DISTINCT FROM ALL (cor subq) </td><td> N </td><td> expression op (subq) </td><td> N </td></tr><tr><td> T1.COL op T2 col expr </td><td> Y </td><td> EXISTS (subq) </td><td> N </td><td> COL=(noncor subq) </td><td> Y </td></tr><tr><td> T1.COL <> T2 col expr </td><td> N </td><td> NOT EXISTS (subq) </td><td> N </td><td> expression op value </td><td> N </td></tr><tr><td> T1.COL1 = T1.COL2 </td><td> N </td><td> expression = value </td><td> N </td><td> T1.COL1 <> T1.COL2 </td><td> N </td></tr><tr><td> T1.COL1 op T1.COL2 </td><td> N </td><td> expression <> value </td><td> N </td><td></td><td></td></tr></tbody></table>  在分析得到 SQL 语句里所有 Boolean-term 中可以使用索引的谓词后，就可以根据这些谓词中的列来设计索引了。以下面 SQL 语句为例：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> SELECT C_ COMMENT 
 FROM CUSTOMER 
 WHERE C_ ACCTBAL > 10000 
 AND UCASE(C_NAME)= ’ IBM ’
	 AND C_CUSTKEY < > C_NATIONKEY 
	 AND C_MKTSEGMENT = ‘ CHINA 
	 AND C_PHONE LIKE ‘ 135010% ’
	 AND C_ ADDRESS= ’ BEIJING ’
</td></tr></tbody></table>  对照表 1 可以知道，C_ ACCTBAL，C_MKTSEGMENT，C_PHONE 和 C_ ADDRESS 都是合适的索引列的候选者，如果要设计单键索引（Single-key Index），它们任意一个都可以构成索引；如果要设计多键索引（Multiple-keys Index）, 它们之间的前后顺序是下一个需要考虑的问题，详细讨论见后文的“索引键顺序的选择”。而相对应的，C_NAME，C_CUSTKEY 和 C_NATIONKEY 则不是合格的索引列候选者。索引键顺序的选择在确定了建立索引所需要的键之后，下一步就是考虑如何确定这些键的顺序。对此可以参考如下两条规则：1. 将 Stop-matching 的谓词放到索引的最后如果谓词全部是 =，那么对此索引的访问可以一直进行索引匹配访问；但是当其中包含了 <、 >、LIKE 这种范围操作谓词时，只有第一个范围操作谓词可以进行索引匹配访问，之后所有的谓词，即使是 = 的谓词，也只能进行非匹配访问操作。我们称这种谓词为停止匹配谓词。显而易见，我们希望进行更多的索引匹配访问操作，因此要把所有停止匹配的谓词放在索引的最后面。比如对于如下 SQL 语句：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> Select C_ COMMENT 
 From CUSTOMER 
 Where C_ ACCTBAL > 10000 
	 AND UCASE(C_NAME)= ’ IBM ’
	 AND C_CUSTKEY < > C_NATIONKEY 
	 AND C_MKTSEGMENT = ‘ CHINA 
	 AND C_PHONE LIKE ‘ 135010% ’
	 AND C_ ADDRESS= ’ BEIJING ’
</td></tr></tbody></table>  在这条查询里面，在 C_ ACCTBAL、C_CUSTKEY、C_PHONE 上的 3 个谓词均为范围操作的谓词，也就是说它们是都是停止匹配谓词，我们在设计时要把他们放在索引的最后面。对这条查询，一种可能的索引设计为 (C_MKTSEGMENT, C_ ADDRESS, C_ ACCTBAL)。2. 按照列基数降序排列键在 DB2 中，有一个过滤因子的概念，它是 DB2 应用一个谓词之后，输出的数据与输入数据的比。过滤因子是一个 0 ～ 1 之间的小数。DB2 会尽量先应用选择过滤因子低的谓词，这样在后续操作中需要处理的数据量就会比较小。当一条索引能够提供更小的过滤因子时，DB2 会优先的选用它。所以我们也要把过滤因子小的谓词放到索引的前面，用来促使 DB2 在其他的查询中也使用这条索引。对于其他查询，我们一般假设其使用的都是“=”号的谓词连接。这个的谓词的过滤因子与其列的基数成反比。这样我们设计索引时就优先把基数大的放在索引前面，按照基数降序排列所有的键。例如对于如下 SQL 语句：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> Select C_ COMMENT 
 From CUSTOMER 
 Where C_NAME= ’ IBM ’
	 AND C_MKTSEGMENT = ‘ CHINA 
	 AND C_ ADDRESS= ’ BEIJING ’
</td></tr></tbody></table>  假定这几个列对应的基数分别是 CARD(C_NAME) = 1000，CARD(C_MKTSEGMENT) = 2000，CARD(C3) = 1500。那么我们设计出的索引键的顺序应为：(C_MKTSEGMENT, C_ ADDRESS, C_NAME)。索引的进一步设计如果按照上面设计的索引，性能仍然不能满足要求，那么可以进一步考虑如下 3 个索引设计的方向：1. 在索引中加入所有 WHERE 从句中被引用到的列，令非匹配访问的谓词也能受益即使是在索引中做非匹配访问操作，性能仍然要比直接做全表扫描性能要高。为 WHERE 从句中的一部分或者全部的可使用索引的谓词建立索引，可提高性能。但是要注意的是，一定要把可做匹配访问的列放在前面，非匹配访问的列放在后面，以防止过早的出现停止匹配的情况（参照前文关于索引顺序的一节）。示例：对于查询。<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> Select C_ COMMENT 
 From CUSTOMER 
 Where C_ ACCTBAL > 10000 
 AND C_PHONE LIKE ‘ 135010% ’
 AND C_ ADDRESS= ’ BEIJING ’
</td></tr></tbody></table>  可以考虑建立的索引为 (C_ ADDRESS, C_ ACCTBAL, C_PHONE)。注意一定要把 C_ ADDRESS 放在索引的第一位，这样 DB2 才能在这个键上进行匹配访问操作，C_ ACCTBAL 即为停止匹配的谓词，在其之后的 C_PHONE 只能使用非匹配访问的访问方式。2. 在第一步的基础之上，可以进一步把 SELECT 从句中的所有列也加上，使查询成为只使用索引的访问方式对于上面的 SQL 语句，假如希望使用只使用索引的访问方式，那么我们需要建立的索引就是 (C_ ADDRESS, C_ ACCTBAL, C_PHONE, C_ COMMENT)3. 把 GROUP BY 和 ORDER BY 从句中的所有列加上，可以减少访问计划中的排序操作对查询中的 GROUP BY 和 ORDER BY 关键字，DB2 是需要进行排序操作以得到正确的结果的，除非数据已经经过排序。利用之前讲过的关于索引结构的知识，我们知道索引既是一组经过排序的列，假如在 GROUP BY 和 ORDER BY 从句中需要排序的列上面恰巧存在可利用的索引，那么数据库从索引上读取的数据就不再需要排序的操作了。示例：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> SELECT L_PARTKEY,L_ORDERKEY 
 FROM LINEITEM 
 WHERE L_TAX = 10 
 GROUP BY L_PARTKEY,L_ORDERKEY 
 ORDER BY L_PARTKEY,L_ORDERKEY 
</td></tr></tbody></table>  为了减少排序操作，需要建立的索引是 (L_TAX, L_PARTKEY, L_ORDERKEY)。该索引的第一位是为了谓词 L_TAX = 10，之后加上了 GROUP BY 和 ORDER BY 中的所有列，从而避免了排序操作。<a name="minor2.2">创建表连接所需的索引</a>对于涉及多个表之间连接的 SQL 语句，当我们设计索引来提高性能时，一定要考虑表的连接方式以及连接顺序。不同的数据库可能有许多不同的表连接方式，他们利用索引的方式也有很多区别，关于某一种具体表连接方式的说明，请参阅数据库厂商的手册。在本文中，我们以 DB2 中最常见的一种表连接方式——嵌套循环连接作为例子。嵌套循环连接作为一种最基础、最常见的连接方式，在所有的主流数据库产品都被广泛使用。它所连接的两个表分为外表和内表，外表进行一次扫描，内表进行多次扫描查找满足连接谓词的记录行。嵌套循环连接过程的伪代码示意如下：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> For each i in 外表 : 
	 For each j in 内表 : 
		如果 (i,j) 满足约束条件
 将（i,j）放入结果集
</td></tr></tbody></table>  从中可以看到，外表只需要做一次完整的全表扫描，索引对这种访问是不起作用的；而内表需要被多次扫描，并且每次扫描都是利用连接谓词进行一次查询操作，对于此种访问方式，在内表相关的列上面建立索引就是相当有必要的了。我们可以设想一个简单的例子来体会内表上索引的作用：外表和内表各包含 1000 条记录，并且内表在连接的列上没有索引。当数据库对这两个表进行嵌套循环连接时，外表扫描一次，需要读取 1000 条记录，内表由于没有建立索引，因此要进行 1000 次的全表扫描，数据库要累计从磁盘读取 1000 × 1000 也就是 1 百万条记录。之后我们来看在内表上有索引的情况。同样外表也要从磁盘读取 1000 条记录，但是对内表的访问这次是采取索引访问的方式，假如内表上有 10% 的记录匹配连接谓词，那么就是需要从磁盘读取（1000 × 10%=）100 条记录，性能提高了 1 万倍。当然在真实的数据库中会有各种如缓存、预读等的机制来提高性能，实际的性能差距不会有这么大，但是通过这个例子，相信读者应该能体会到内表上的索引对提高性能有多么重要的作用了。但是假如把索引建在外表上，对内表访问并不会受益，同时对外表访问仍然需要读取 1000 条记录（假设数据库选择使用索引的话）。可见，不光建立索引很重要，同时要在正确的表上面建立索引。例如对于如下 SQL 语句：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> SELECT DISTINCT O_CUSTKEY 
 FROM LINEITEM, ORDER 
 WHERE L_ORDERKEY = O_ORDERKEY 
 AND O_ORDERDATE >= DATE('1993-07-01') 
</td></tr></tbody></table>  为了使嵌套循环连接受益建立索引，我们要在所有连接的列上建立索引。考虑到 DB2 可能采用的不同表连接顺序，设计出的索引可能是 (L_ORDERKEY) 或者 (O_ORDERKEY)。我们需要查看实际的访问方式才能最终确定需要建立哪个索引。<a name="minor2.3">索引的另一面</a>索引会带来一些性能上的好处，但是它也不是百试百灵的良药。建立过多的索引，或者不合适的索引，也会给带来一些的副作用。1) 建立索引会降低更新（update）， 插入（insert）， 删除（delete）表中数据的速度。因为此时 DB2 需要同时更新表上的索引，若同一张表上有多个索引，情况会更糟。这有一组实验数据 , 建立测试环境如下：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> CREATE TABLE TEST_INDEX_TABLE (C1 INT, C2 INT , C3 INT, C4 INT); 
 CREATE INDEX IDX_01 ON TEST_INDEX_TABLE (C1); 
</td></tr></tbody></table>  测试的 SQL 语句为：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> UPDATE TEST_INDEX_TABLE SET C1 = 1 WHERE C1 BETWEEN 1 AND 10000 
</td></tr></tbody></table>  更新的列 C1 正好包含在 IDX_01 中，DB2 在更新 C1 数据的同时还需要更新索引 IDX_01。如果表中的数据分别为 10k，100k， 1000k 条，下面的测试结果对比了在建立索引之前和之后 SQL 执行速度的明显降低。 <a name="minor2.3">表 2. 时间测试结果</a> <table class="ibm-data-table" border="0" cellSpacing="0" summary="时间测试结果" cellPadding="0"><tbody><tr><th>表内不同记录数</th><th>表上无索引 （单位：毫秒）</th><th>表上建立了索引 （单位：毫秒）</th><th>增加时间 （单位：毫秒）</th></tr><tr><td>10k </td><td> 516 </td><td> 6937 </td><td> 6421 </td></tr><tr><td> 100k </td><td> 1156 </td><td> 52750 </td><td> 51594 </td></tr><tr><td> 1000k </td><td> 3234 </td><td> 451719 </td><td> 448485 </td></tr></tbody></table>  2) 数据库需要磁盘空间来保存索引，所以建立索引会带来磁盘开销。以刚才的 IDX_01 索引为例，测试同一张数据库表 TEST_INDEX_TABLE，当表中数据行数不同时，建立 IDX_01 所需的磁盘空间测试结果如下。 <a name="minor2.3">表 3. 空间测试结果</a> <table class="ibm-data-table" border="0" cellSpacing="0" summary="空间测试结果" cellPadding="0"><tbody><tr><th>表内不同记录数</th><th>索引占用空间 （单位：KB）</th></tr><tr><td>10k </td><td> 188 </td></tr><tr><td> 100k </td><td> 1688 </td></tr><tr><td> 1000k </td><td> 16692 </td></tr></tbody></table>  <a name="minor2.4">验证创建的索引</a>依据前面几步的分析，对一条查询我们能设计出了一些索引。但是这些索引会不会被优化器所选中？使用这些索引之后，性能究竟有多少提升？使用索引之后，将会如何改变这条查询的访问计划？获得这些问题答案的最简单的办法，当然是实际创建出希望被测试的索引，并实际执行一下查询语句；但是实际创建一条索引要有一定的开销，并且在实际的生产系统中往往并不允许这样随意的创建索引来做测试。为了解决这个问题，DB2 已经为我们提供了一个非常好的工具——虚拟索引评估。在 DB2 LUW 中创建虚拟索引为了测试一个设计出的索引，首先要在 DB2 中定义相应的虚拟索引。定义虚拟索引的方法，是在 ADVISE_INDEX 表中插入相应的记录。插入的内容至少应包括索引的名字、表的名字、索引键的定义以及索引是否为 unique 索引。下面用一个简单的例子来说明如何创建一个虚拟索引，插入语句为：<table border="0" cellSpacing="0" summary="This table contains a code listing." cellPadding="0" width="100%"><tbody><tr><td class="code-outline"> INSERT INTO ADVISE_INDEX 
 (NAME, CREATOR, TBNAME, TBCREATOR, COLNAMES, UNIQUERULE, COLCOUNT, IID, 
 NLEAF, NLEVELS, FIRSTKEYCARD, FULLKEYCARD, CLUSTERRATIO, USERDEFINED, 
 SYSTEM_REQUIRED, SEQUENTIAL_PAGES, DENSITY, FIRST2KEYCARD, FIRST3KEYCARD,
 FIRST4KEYCARD, PCTFREE, UNIQUE_COLCOUNT, MINPCTUSED, REVERSE_SCANS,
 USE_INDEX, CREATION_TEXT, INDEXTYPE, EXISTS, RIDTOBLOCK) 
 values ( 'IDX1002','OEDEV','EXPLAIN_INSTANCE','OEDEV','+ISOLATION+BLOCK',
 'D', 2, 1, 1, 1, 25, 25, 100, 1, 0, -1, -1, 0, 0, 0, -1, -1, 0, 'Y', 'Y',
 'CREATE INDEX "OEDEV "."IDX1002" ON "OEDEV "."EXPLAIN_INSTANCE" 
 ("ISOLATION" ASC, "BLOCK", ASC) ALLOW REVERSE SCANS', 'REG','N','N') 
</td></tr></tbody></table>  这样就在表 EXPLAIN_INSTANCE 上创建了一个键为 ("ISOLATION" ASC, "BLOCK", ASC) 的索引。关于这个 ADVISE_INDEX 上每一列的具体含义，可以参考 DB2 SQL REFERENCE 上关于 ADVISE_INDEX 表的说明。在插入了虚拟索引到 ADVISE_INDEX 表之后，就可以进行虚拟索引的验证了。具体方法是首先执行“SET CURRENT EXPLAIN MODE EVALUATE INDEXES”设置特殊寄存器，然后执行需要被验证的查询。此时所有的访问计划信息已经被保存。之后就可以像查看普通的访问计划一样，查看该包含了虚拟索引的访问计划。索引不被采用的原因分析在查看了访问计划之后可能会发现，我们设计出来的索引并没有被优化器所选中。造成这种结果的原因可能是多方面的，根据笔者的经验，常见情况可能是如下几种：最通常的情况，是设计的索引存在一些问题，比如没有考虑清楚最优的表连接顺序，或者是索引中有 stop-matching 的键存在。假如确定是此种原因，那么就需要返回前几步重新设计索引。另一种可能是数据库中的统计信息不对，甚至是根本不存在的。在这种情况下，DB2 往往无法选出最优的访问计划，因此有可能设计的索引并不会被使用。这种情况一般重新执行 DB2 RUNSTATS 命令即可解决。此外，如果 DB2 判断出需要从表中读取的数据的比例很高（比如有超过 90% 表里面的记录需要被返回），那么 DB2 很有可能选择全表扫描来代替使用索引，因为这样能够减少一次对索引树的读取。假如是此种情况，在表上建立索引实际上并不能提高性能。<div class="ibm-alternate-rule"></div><a class="ibm-anchor-up-link" href="http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1106qinw/#ibm-pcon">回页首</a><a name="major3">总结和结论</a>本文介绍了 DB2 数据库中与索引相关的性能优化理论，介绍了如何分析 SQL 语句并建立出合适的索引。同时本文也介绍了 DB2 数据库中用于验证索引性能的虚拟索引（Virtual index）的机制以及如何去使用它。索引的设计对于数据库性能来说非常重要。如果数据库管理员希望通过建立索引的方式来提高 SQL 查询语句的性能，就应该分析 SQL 语句中的特性，设计出最合适的索引，并通过虚拟索引的方式提交给 DB2 来加以验证。如果该索引确实有效，达到预期的目标，那么数据库管理员最终就可以在数据库中真正建立这些索引，提高 DB2 执行查询时的性能，从而减少总的成本并提高业务的投资回报。

数据库索引数据库

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！