数据架构师: DB2 索引和查询性能：第 1 部分

考虑额外索引时平衡成本和收益

简介： 向 DB2 数据库添加索引通常是改善查询性能的最好方法，但添加索引也是有代价的。在这个两部分文章系列中，Robert Catterall 检查添加索引的性能成本，以及指定索引的正确方法。

IBM Data Management 中文版 杂志目录

查看本系列更多内容

标记本文！

发布日期： 2010 年 11 月 16 日
级别： 中级
其他语言版本： 英文
访问情况 214 次浏览
建议: 0 (添加评论)

- 阅读本文的互动数字版！
- 订阅 IBM Data Management 中文版 杂志

对于多数人而言，索引是 DB2 性能修复的 “胶带”。遇到了一个响应时间问题？向目标表中添加一个索引就可以了。事实上，减少一个 DB2 访问查询或批处理作业的运行时间通常涉及创建一个新索引；但是，尽管新增索引通常是 DB2 性能问题的解决方案，但并不总是正确的解决方案。在这个两部分文章系列的第一部分中，我将指出与 DB2 索引的优势如影随形的成本，并讨论创建更高效的索引的方法。下一期，我将介绍一些不涉及新建索引的性能调优操作，当一个表被过度索引，而且您已经厌倦了添加索引时，就可以执行那些操作。

世上没有免费的午餐

索引绝对不是没有成本的。在一个表上定义的每个额外索引都将增加您的数据库关联成本。首先是 CPU 开销：表上的每个索引都会使每个插入或删除操作的 CPU 成本翻番，或更改一个已索引列的值会使 CPU 更新 成本翻番。如果向一个带有 3 个索引的表插入一行，DB2 必须向一个索引添加一个对应的条目 3 次。如果从一个带有 10 个索引的表删除一行，DB2 必须删除与被删除的行关联的所有索引条目。

所有这些索引维护行为都涉及一些 DB2 页面请求（在 DB2 for z/OS 系统中称为 GETPAGE，在 DB2 for Linux, UNIX, and Windows [LUW] 环境中称为逻辑读取（logical read）），这些请求可能就是 SQL 语句执行的 CPU 成本的主要决定因素。

额外索引还会增加某些 DB2 实用工具（比如 REORG 实用工具）的 CPU 占用。在没有持续上升的键上定义的索引自然会随时间变化变得比较混乱。新条目必须被放置到特殊的索引叶页面中（因为尽管索引-键排序被严格实施，但表中的数据-行集群化并非如此），如果其中必须放置条目的页面已满（在键不持续上升时并不少见），页面将被分割，一些条目将被重新定位到一个此前为空的页面中，而那个空页面可能位于索引结构中很远的地方。REORG 将把一个组织混乱的索引恢复为良好组织状态，但要以一些 CPU 消耗为代价。

还有一些实用工具在一个表上定义的索引越多时也会变得越来越昂贵，其中包括 LOAD 和 RUNSTATS（DB2 目录中的索引统计数据必须及时更新），以及执行 “索引-数据” 一致性检查的实用工具（包括用于 DB2 for z/OS 的 CHECK INDEX 和用于 DB2 for LUW 的带有 INDEXDATA 的 INSPECT）。

索引还有磁盘空间成本，尽管这种成本不像几年前那样昂贵，这在一定程度上归功于 DB2 9 for z/OS 和 DB2 9.7 for LUW 的压缩特性。在这两个平台上，现在可以获取大量磁盘空间节约（通常为 50% 或更多），而且基本不会增加 CPU 开销。

回页首

多少索引合适？

随着一个表上的索引数量的增加，维护索引的 CPU 总成本最终可能会超过新索引提供的性能收益。但哪里是临界点？答案取决于具体情况，但我通常不喜欢看到在线事务处理（OLTP）环境中的一个表上超过 4 到 5 个索引，或者数据仓库系统中的一个表上超过 8 到 10 个索引。我认为，一个数据仓库的每个表上有大量索引通常没有问题，因为数据仓库中的重点通常是优化数据检索性能，且查询搜索参数在一个业务智能设置中可能更不容易预测。

现在，这些索引限制指南已经成为首要规则，而不仅仅是指南而已。假设 OLTP 数据库中的一个表已经拥有 6 个或 7 个索引，我还会赞成再添加一个吗？也许会，如果潜在的性能回报真的很大的话 — 我所说的 “很大” 指的是新索引有望将系统中更昂贵查询中的一个（或几个）的 CPU 成本降低 90% 或更多。即使所提出的新索引看起来似乎是一个性能赢家，但在继续实现之前，我可能会查看是否还有其他更好的替代方法不会涉及新建索引（这将是本专栏第二部分的主题）。

回页首

如果真的要干，那就干得漂亮点

如果您的确要向一个表添加一个索引来削减查询的 CPU 成本和运行时间，您可能会想在实际情况允许的条件下尽可能多地利用那个新索引。这意味着，新索引的其中一个用途是实现键列的秩序（如果那是一个多索引键的话）。这一点为何如此重要呢？原因是查询将运行得更快 — 有时快得多 — 如果查询的谓词（它的搜索参数）匹配一个索引键的更多列的话；当它遇到 DB2 的谓词和 “索引-键” 列匹配规则时，顺序将发挥关键作用。

这里有一些最常用的谓词类型的规则的一个简化（因此不是全面的）版本，这些类型包括等于、范围（例如 > 或 <=）、以及列表（例如，COL1 IN ('DOG', 'CAT')）：

1. 对于一个多列索引键，谓词匹配从高顺序列开始，然后继续匹配尽可能远的其他键列，一次一个，按照它们在索引键中的顺序。换句话说，匹配从索引键的最左端开始并向右匹配。
2. 在索引键中的位置 n+1 处获取一个匹配要求使用一个等于谓词或一个列表谓词匹配位置为 n 的列。也就是说，一旦一个键列已使用一个范围谓词匹配，则该列右边的列上将不会有任何匹配。
3. 当键中的一个列被跳过时，匹配过程终止。也就是说，如果您在键的位置 n 处的列上获得了一个匹配，且位置 n+1 处的列没有一个匹配谓词，那么位置 n+2 处的列上也不可能有一个匹配。

因此，如果您拥有一个带有谓词 COL1 > 2 AND COL2 = 'BRICK' 的查询，一个定义在 COL2 上的查询，则 COL1 将向您提供两个谓词/键-列匹配，而如果是 COL1 上的索引，则 COL2 将向您提供一个匹配（参考上面列表中的规则 2：一旦一个键列已被一个范围谓词匹配，则匹配过程终止）。

回页首

使用索引，但要小心选择

索引的确能增强 DB2 查询性能，但如果一个表上放置太多索引，那么最终响应时间和流量将走向错误方向。开始时要小心谨慎，在您的数据库中的每个表上只使用少量索引 — 也许只用两到三个。这样，您将拥有一些保留的索引添加容量，并有可能定义一些从成本收益角度来看很不错的额外索引。但不要做得太过，不要在只提供微小查询性能好处的索引上浪费宝贵的索引添加资本 — 您在这里要寻找的是性能大赢家。

在下一个专栏中，我将向您展示，有很多方法无需创建新索引即可改善查询相应时间。结合那些技术，并谨慎使用额外索引是有两手准备的性能调优方案，最有可能实现您的目标的。