Alexander Kuznetsov
Chicago，IL
2003 年 3 月 笔者 Alexander Kuznetsov 用一些示例展示了，向索引添加一列或两列来‘覆盖’SELECT 语句中的列，从而提高查询性能。DB2 优化器非常擅于识别这种情形，并且可以选择高效的执行计划。
本文专门针对 IBM® DB2® Universal Database® for Linux、UNIX® 和 Windows®。
简介
有时向索引添加一列或两列可以将查询性能提高十倍或更多。我将在本文中讨论这种情形：在一个或多个索引中可以找到执行 SELECT 查询所需的所有列；换句话说，一个索引（或多个索引） 覆盖了查询。在这种情况下，只涉及索引的执行计划可能是最有效的执行计划。
DB2 优化器非常擅于识别这种情形，它能够选择非常有效的、涉及 索引覆盖的执行计划。我将研究几个示例，在这些示例中，向索引添加一列或多列就能使查询的执行速度更快，这是因为：
- 因为索引通常比表小，所以扫描索引通常所需的 I/O 较少。
- 因为索引条目已经经过排序，所以不需要排序结果集就可以执行带相应 ORDER BY、GROUP BY 或 DISTINCT 子句的 SELECT 语句。
- 因为索引条目已经经过排序，所以归并连接不需要任何排序。
另外，我将讨论如何让一个（同一个）索引覆盖几条查询，这有助于使索引数目尽可能的少。
本文所使用的模式
以下是本文示例将要用到的表：
CREATE TABLE CUSTOMER_DATA(
ID INT NOT NULL,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
SALUTATION VARCHAR(10),
ADDRESS VARCHAR(30),
CITY VARCHAR(30),
STATE CHAR(2),
ZIP VARCHAR(10),
PHONE_AREA SMALLINT,
PHONE_NUMBER INTEGER,
TOTAL_AMOUNT FLOAT,
SOME_OTHER_DATA VARCHAR(100));
CREATE TABLE CUSTOMER_ORDER(
CUSTOMER_ID INT NOT NULL,
ORDER_NUMBER SMALLINT NOT NULL,
ORDER_DT DATE,
AMOUNT FLOAT,
SOME_OTHER_DATA VARCHAR(100))
ALTER TABLE CUSTOMER_DATA ADD PRIMARY KEY(ID)
ALTER TABLE CUSTOMER_ORDER ADD PRIMARY KEY(CUSTOMER_ID, ORDER_NUMBER)
ALTER TABLE CUSTOMER_ORDER ADD FOREIGN KEY(CUSTOMER_ID)
REFERENCES CUSTOMER_DATA(ID)
CREATE INDEX CUSTOMER_NAMES ON CUSTOMER_DATA(LAST_NAME, FIRST_NAME);
|
（注：DB2 会自动创建实现主键约束的索引。）
我们还将假定关于这两个表的统计信息是最新的、详细的并包括分布统计信息。本文中的所有示例均使用缺省优化等级 5。
好处：加速经常执行的查询
考虑下面这条非常简单且经常执行的查询：
SELECT SUM(AMOUNT) FROM CUSTOMER_ORDER WHERE CUSTOMER_ID = 1000
|
优化器选择通过关于 CUSTOMER_ID 的索引来访问表中的行。
让我们将这条查询保存在 sum_amt.sql 文件中，然后用 db2batch 来测量这条查询的实际执行成本，如下所示：
db2batch -d test2 -f sum_amt.sql -r perf_pk.txt -o p 3
|
（参考资料 [ 3] 中描述了 db2batch 命令的完整语法）。运行该命令之后，文件 perf_pk.txt 包含一个关于执行这条查询的成本的报告：
...
Buffer pool data logical reads = 12
Buffer pool data physical reads = 10
...
Buffer pool index logical reads = 3
Buffer pool index physical reads = 1
|
在这个特定情形下，索引深度为 3。根索引页和相关非叶子级页已经存在于缓冲池中，所以只有叶级索引页需要物理读取（ index physical reads = 1 ）。（注：叶子这一层的索引条目可以跨两个或更多叶子页。）在缓冲池内只有两个相关数据页，所以必须从磁盘读取其余的（10 个）数据页。对于 OLTP 活动，大多数索引页通常已经存在于缓冲池中。另外，在这种情况下，没有按该索引来群集表，所以与搜索条件相匹配的那 12 条记录分布在如 图 1所示的表中。
图 1. 使用表访问实现查询
如果该表按照关于 CUSTOMER_ID 的索引进行群集，则这条查询可以有更好的性能。关于群集索引的更多信息，请参阅参考资料 [ 2]。
现在，让我们创建索引来加速该查询：
CREATE INDEX ORDER_CUST_AMT ON CUSTOMER_ORDER(CUSTOMER_ID, AMOUNT);
RUNSTATS ON TABLE DB2INST1.CUSTOMER_ORDER WITH DISTRIBUTION AND DETAILED INDEXES ALL;
|
（关于 RUNSTATS 语法的更多信息，请参阅参考资料 [ 3]）。注：因为在创建索引时，不会自动创建统计信息，所以必须显式地创建统计信息。这个新的索引包含了实现该查询所需的全部列。执行该查询时，优化器选择只访问这个索引，而根本不访问该表：
图 2. 使用纯索引访问实现查询
正如 图 2所示，在这个特定情形下，添加第二列之后，索引深度没有增加。另外，在这种情况下，一个叶子页中包含了与搜索条件相匹配的索引条目。注：索引深度可能会增加，因为索引条目增加了。另外还要注意，与搜索条件相匹配的索引条目可能跨两个数据页。
现在，我们来讨论由 dynexpln 实用程序所生成的执行计划以及实际的执行成本：
dynexpln -d test2 -o output.txt -q "SELECT SUM(AMOUNT) FROM
CUSTOMER_ORDER WHERE CUSTOMER_ID = 1000"
...
| | Index-Only Access
(...)
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
(...)
Buffer pool index logical reads = 3
Buffer pool index physical reads = 1
|
（关于 dynexpln 的更多信息，请参阅参考资料 [ 2] 和 [ 1]。）
纯索引访问显示出执行这条查询的成本非常低。如果索引深度增加，同时索引条目跨两个页面，该查询需读取 5 次，这仍然比访问表的方式要好。
虽然这似乎是一个过于简单的示例，但它展示了索引覆盖的好处。正如我们所看到的，索引覆盖将执行查询时所需的 I/O 数目降到了最少，极大地提高了查询性能。另外，请记住，在这个特定情形下：
- 平均至少有几条记录与搜索条件相匹配。
- 因为没有在关于 CUSTOMER_ID 的索引上群集表，所以这些记录通常分布在表中的各个地方。
- 实现这条查询不需要大多数的列。
在其它情形下， 索引覆盖的好处（如果有的话）不太明显。
我们刚才讨论了一个示例，在这个示例中，检索的记录数目非常少，并且这些记录分布在表中各个地方。另一个与此非常类似的示例是：
SELECT FIRST_NAME, LAST_NAME, PHONE_AREA, PHONE_NUMBER
FROM CUSTOMER_DATA ORDER BY LAST_NAME FETCH FIRST 30 ROWS ONLY;
|
同样，只检索了少量的记录（至多 30 条）。如果有关于 FIRST_NAME 、 LAST_NAME 、 PHONE_AREA 和 PHONE_NUMBER 的索引，则它可以覆盖该查询。同样，如果这个索引的群集率很低，则该查询的性能将会极大地得到提高，原因与第一个示例中的一样。
好处：扫描索引而不扫描表
如果一个表很大，则扫描该表以满足查询所付出的成本可能很大。只要索引比其基表小（通常是这样），扫描索引就可能是一种成本较小的替代方案。
考虑下面这条简单查询：
SELECT LAST_NAME, FIRST_NAME FROM CUSTOMER_DATA WHERE LAST_NAME LIKE '%STONE'"
|
假定有一个关于 LAST_NAME 的索引，但关于 LAST_NAME 和 FIRST_NAME 的索引被删除了。注：谓词 LAST_NAME LIKE '%STONE' 是不可索引的。优化器选择了扫描表空间，通过查看 dynexpln 的输出就可以看出这一点。
dynexpln -d test2 -o likestone1.txt -q "SELECT LAST_NAME,
FIRST_NAME FROM CUSTOMER_DATA WHERE LAST_NAME LIKE '%STONE'"
SQL Statement:
SELECT LAST_NAME, FIRST_NAME
FROM CUSTOMER_DATA
WHERE LAST_NAME LIKE '%STONE'
Estimated Cost = 10381
Estimated Cardinality = 1383
Access Table Name = DB2INST1.CUSTOMER_DATA ID = 2,5
| #Columns = 1
|
Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 1
| Return Data to Application
| | #Columns = 2
Return Data Completion
|
当创建了关于 LAST_NAME 和 FIRST_NAME 的索引之后，优化器选择使用该索引：
db2 "CREATE INDEX DC_LN ON CUSTOMER_DATA(LAST_NAME, FIRST_NAME)"
db2 RUNSTATS ON TABLE DB2INST1.CUSTOMER_DATA WITH
DISTRIBUTION AND DETAILED INDEXES ALL
dynexpln -d test2 -o likestone2.txt -q "SELECT LAST_NAME,
FIRST_NAME FROM CUSTOMER_DATA WHERE LAST_NAME LIKE '%STONE'"
SQL Statement:
SELECT LAST_NAME, FIRST_NAME
FROM CUSTOMER_DATA
WHERE LAST_NAME LIKE '%STONE'
Estimated Cost = 707
Estimated Cardinality = 1383
Access Table Name = DB2INST1.CUSTOMER_DATA ID = 2,5
| #Columns = 2
| Index Scan: Name = DB2INST1.DC_LN ID = 2
| | Index Columns:
| | | 1: LAST_NAME (Ascending)
| | | 2: FIRST_NAME (Ascending)
| | #Key Columns = 0
| | | Start Key: Beginning of Index
| | | Stop Key: End of Index
| |
Index-Only Access
| | Index Prefetch: Eligible 84
| | Sargable Index Predicate(s)
| | | #Predicates = 1
| | | Return Data to Application
| | | | #Columns = 2
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Return Data Completion
|
正如我们所看到的，仅仅因为索引小得多，索引扫描的估计成本就远远低于表空间扫描的成本了。如果频繁地使用索引，则很可能在缓冲池中找到索引页。如果是那种情况，则索引覆盖的实际执行成本要更小。
好处：消除对结果集进行排序的需要
每当查询语句中包含 ORDER BY、GROUP BY 或 DISTINCT 子句，则执行该查询可能需要排序。排序可能会消耗大量资源。索引条目是已经经过排序的，因此索引覆盖可能消除对结果集进行排序的需要，从而提高查询的性能。例如，考虑带 ORDER BY 子句的查询：
SELECT LAST_NAME, FIRST_NAME, PHONE_AREA, PHONE_NUMBER
FROM CUSTOMER_DATA
ORDER BY LAST_NAME, FIRST_NAME
|
虽然有关于 LAST_NAME 和 FIRST_NAME 的索引，但没有按该索引群集该表。在这种情况下，对表空间进行全扫描，然后排序结果集，这种方式有时比通过索引来访问表更有效。（在我以前的文章 当我们认为优化器没有正常工作时中，就类似问题做了简要讨论）。在这个特定情形下，这是优化器所做的选择：
SQL Statement:
SELECT LAST_NAME, FIRST_NAME, PHONE_AREA, PHONE_NUMBER
FROM CUSTOMER_DATA
ORDER BY LAST_NAME, FIRST_NAME
Estimated Cost = 25736
Estimated Cardinality = 59616
Access Table Name = DB2INST1.CUSTOMER_DATA ID = 2,5
| #Columns = 4
|
Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 4
| |
#Sort Key Columns = 2
| | | Key 1: LAST_NAME (Ascending)
| | | Key 2: FIRST_NAME (Ascending)
| |
Sortheap Allocation Parameters:
| | | #Rows = 59616
| | | Row Width = 44
| | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
| Return Data to Application
| | #Columns = 4
Return Data Completion
|
对于该查询，对结果集排序耗尽了大多数资源，因为对于不带 ORDER BY 子句的同一条查询，估计的和实际的执行成本都至少降低 50%。用索引覆盖查询，SELECT 操作几乎会立即得出结果，因为：
- 它需要读取较少的页面（索引比其表要小）
- 它不必排序结果
让我们创建关于 LAST_NAME 、 FIRST_NAME 、 PHONE_AREA 和 PHONE_NUMBER 的索引，并创建统计信息。现在执行计划使用索引覆盖：
SQL Statement:
SELECT LAST_NAME, FIRST_NAME, PHONE_AREA, PHONE_NUMBER
FROM CUSTOMER_DATA
ORDER BY LAST_NAME, FIRST_NAME
Estimated Cost = 685
Estimated Cardinality = 59616
Access Table Name = DB2INST1.CUSTOMER_DATA ID = 2,5
| #Columns = 4
| Index Scan: Name = DB2INST1.CUST_DIRECTORY ID = 4
| | Index Columns:
| | | 1: LAST_NAME (Ascending)
| | | 2: FIRST_NAME (Ascending)
| | | 3: PHONE_AREA (Ascending)
| | | 4: PHONE_NUMBER (Ascending)
| | #Key Columns = 0
| | | Start Key: Beginning of Index
| | | Stop Key: End of Index
| |
Index-Only Access
| | Index Prefetch: Eligible 79
| | | Return Data to Application
| | | | #Columns = 4
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Return Data Completion
|
类似的，索引覆盖提高了带 GROUP BY 和 DISTINCT 子句的查询性能。例如，访问关于 LAST_NAME 和 FIRST_NAME 的索引实现了这两条查询，消除了在执行这两条查询期间排序的需要，所以提高了查询性能：
SELECT LAST_NAME, FIRST_NAME, COUNT(*) FROM CUSTOMER_DATA
GROUP BY LAST_NAME, FIRST_NAME;
SELECT
DISTINCT LAST_NAME, FIRST_NAME FROM CUSTOMER_DATA;
|
注：我们假定唯一索引的所有列都包含在该 SELECT 列表中。在这种情况下，任何结果集都是唯一的。即使没有索引覆盖，DB2 优化器也能够识别这种情况，并能消除不必要的排序。
正如我们所看到的， 索引覆盖可以极大地提高了带 ORDER BY 、 GROUP BY 或 DISTINCT 子句的 SELECT 查询的性能。
好处：加速父表和子表的连接
即使在一条查询中涉及多个表时，DB2 优化器也非常“聪明”，能够选择 索引覆盖。考虑下面这条简单的查询：
SELECT CUSTOMER_DATA.FIRST_NAME, CUSTOMER_DATA.LAST_NAME,
CUSTOMER_DATA.PHONE_AREA, CUSTOMER_DATA.PHONE_NUMBER,
CUSTOMER_ORDER.ORDER_NUMBER, CUSTOMER_ORDER.ORDER_DT,
CUSTOMER_ORDER.AMOUNT
FROM CUSTOMER_DATA JOIN CUSTOMER_ORDER ON ID = CUSTOMER_ID
|
如果只存在那些用于实现主键和外键约束的索引，则执行查询的估计成本为：
Estimated Cost = 40421
Estimated Cardinality = 357696
|
让我们添加相应的索引（注：我们可能希望让这些索引实现主键和外键约束，我们将在后面讨论这些）。
CREATE INDEX DATA_IND ON CUSTOMER_DATA(ID, FIRST_NAME,
LAST_NAME, PHONE_AREA, PHONE_NUMBER);
CREATE INDEX ORDER_IND ON CUSTOMER_ORDER(CUSTOMER_ID,
ORDER_NUMBER, ORDER_DT, AMOUNT);
|
现在执行这条查询的速度更快。
Estimated Cost = 15404
Estimated Cardinality = 357696
Access Table Name = DB2INST1.CUSTOMER_DATA ID = 2,5
| #Columns = 5
| Index Scan: Name = DB2INST1.DATA_IND ID = 3
| | Index Columns:
| | | 1: ID (Ascending)
| | | 2: FIRST_NAME (Ascending)
| | | 3: LAST_NAME (Ascending)
| | | 4: PHONE_AREA (Ascending)
| | | 5: PHONE_NUMBER (Ascending)
| | #Key Columns = 0
| | | Start Key: Beginning of Index
| | | Stop Key: End of Index
| |
Index-Only Access
(snip)
Merge Join
| Access Table Name = DB2INST1.CUSTOMER_ORDER ID = 2,6
| | #Columns = 3
| | Index Scan: Name = DB2INST1.ORDER_IND ID = 1
| | | Index Columns:
| | | | 1: CUSTOMER_ID (Ascending)
| | | | 2: ORDER_NUMBER (Ascending)
| | | | 3: ORDER_DT (Ascending)
| | | | 4: AMOUNT (Ascending)
| | | #Key Columns = 0
| | | | Start Key: Beginning of Index
| | | | Stop Key: End of Index
| | |
Index-Only Access
|
在这种情况下，两个表都没有关于 CUSTOMER_ID 进行群集。如果父表和子表都针对 CUSTOMER_ID 进行群集，则 索引覆盖的好处可能不太明显，甚至毫无意义。
对于这条查询， 索引覆盖与不使用该方法的成本相比（15404 vs. 40421），其好处是相当明显的。正如我们所看到的，使用 索引覆盖来加速连接是很有意义的。
使用一个索引来覆盖几条查询
假定我们需要提高几个经常执行的查询的性能，譬如：
SELECT CUSTOMER_ID, SUM(AMOUNT) FROM CUSTOMER_ORDER GROUP
BY CUSTOMER_ID;
SELECT CUSTOMER_ID, MIN(ORDER_DT) FROM CUSTOMER_ORDER GROUP
BY CUSTOMER_ID;
SELECT SUM(AMOUNT) FROM CUSTOMER_ORDER;
|
当然，我们可以为每条需要优化的查询创建一个索引（或者实现的查询表）：
CREATE INDEX CUST_ID_AMT ON CUSTOMER_ORDER(CUSTOMER_ID,
AMOUNT) ALLOW REVERSE SCANS;
CREATE INDEX CUST_ID_ORDER_DT ON CUSTOMER_ORDER
(CUSTOMER_ID, ORDER_DT) ALLOW REVERSE SCANS;
CREATE INDEX CUST_AMOUNT ON CUSTOMER_ORDER(AMOUNT) ALLOW
REVERSE SCANS;
|
然而，这些附加的索引会减慢对其基表所做的全部修改，并会耗尽磁盘空间。在许多情况下，需要在加速 SELECT 查询和减慢插入、更新和删除操作之间进行某些折衷。在这种情况下，让一个索引覆盖几条查询是非常有用的。例如，下面是一个覆盖上面所列出的所有查询的索引：
CREATE INDEX ORDER_COVERING ON CUSTOMER_ORDER(CUSTOMER_ID,
ORDER_DT, AMOUNT) ALLOW REVERSE SCANS;
RUNSTATS ON TABLE DB2INST1.CUSTOMER_ORDER WITH DISTRIBUTION
AND DETAILED INDEXES ALL;
|
用一个索引覆盖一条查询，并实现一条约束
这里还有另一项技术，用此技术可以使索引数目较少。在这个示例中，我将用一个索引来实现主键约束，并覆盖本文所用到的全部查询：
ALTER TABLE CUSTOMER_ORDER DROP PRIMARY KEY;
CREATE UNIQUE INDEX ORDER_PK
ON CUSTOMER_ORDER(CUSTOMER_ID, ORDER_NUMBER)
INCLUDE(ORDER_DT, AMOUNT) ALLOW REVERSE SCANS;
ALTER TABLE CUSTOMER_ORDER ADD PRIMARY KEY(CUSTOMER_ID, ORDER_NUMBER);
RUNSTATS ON TABLE DB2INST1.CUSTOMER_ORDER WITH DISTRIBUTION AND DETAILED INDEXES ALL;
|
在这个示例中，DB2 将发出一条警告，并重用已经存在的索引 ORDER_PK 以实现主键约束。
CREATE INDEX 语句中的 INCLUDE 子句意味着这个索引可以保证（ CUSTOMER_ID, ORDER_NUMBER ）对的唯一性，同时还包含 ORDER_DT 和 AMOUNT 列。关于 CREATE INDEX 语法的详细信息，请参阅参考资料 [ 4]。
然而，应该适度地使用该技术。在许多情况下，让较小的索引来实现约束要更好一些。
用多个索引来覆盖一条查询
现在，让我们探讨一下优化器如何选择用两个索引来覆盖一条查询。让我们假定目前没有关于 FIRST_NAME 和 LAST_NAME 的索引（我们在前面几节中用到了这个索引，但在这种情况下假定不存在该索引）。
DROP INDEX CUSTOMER_NAMES
|
下面是这两个索引和这条查询：
CREATE INDEX DATA_FIRST_NAME ON CUSTOMER_DATA(FIRST_NAME)
CREATE INDEX DATA_LAST_NAME ON CUSTOMER_DATA(LAST_NAME)
SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER_DATA WHERE
((FIRST_NAME LIKE 'RO%') AND (LAST_NAME LIKE 'TRA%'))
|
（如果有关于 FIRST_NAME 和 LAST_NAME 的索引，则很可能就用它来实现该查询。我们现在正在考虑当不存在这样方便的索引时的情形。）在这个特定的情形下，优化器选择访问这两个索引，而不访问表本身：
| | Access Table Name = DB2INST1.CUSTOMER_DATA ID = 2,5
| | | #Columns = 1
| | | Index Scan: Name = DB2INST1.DATA_LAST_NAME ID = 2
| | | | Index Columns:
| | | | | 1: LAST_NAME (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: 'TRA ...'
| | | | | Stop Key: Inclusive Value
| | | | | | 1: 'TRAZZZZZZZZZZZZZZZZZ...'
| | | |
Index-Only Access
| | | | Index Prefetch: None
(snip)
| Index ANDing Bitmap Probe
| | Access Table Name = DB2INST1.CUSTOMER_DATA ID = 2,5
| | | #Columns = 1
| | | Index Scan: Name = DB2INST1.DATA_FIRST_NAME ID = 1
| | | | Index Columns:
| | | | | 1: FIRST_NAME (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: 'RO ...'
| | | | | Stop Key: Inclusive Value
| | | | | | 1: 'ROZZZZZZZZZZZZZZZZZZ...'
| | | |
Index-Only Access
|
正如我们所看到的，在这个特定的情形下，我们受益于索引覆盖而不必创建另一个索引。DB2 优化器这次选择使用两个已有的索引。所以，当确定是否还要创建另一索引时，要斟酌一番。
结束语
正如我们所看到的， 索引覆盖可以显著地加速 SELECT 查询，这种情形有许多种。但因为向索引添加列会减慢对表的更改，还是最好在仔细分析了您所处的实际情况之后，再使用这种技术。 |
