SQE存储系列之二 —— 管理您的临时维持索引（MTI）（译文）

通过管理系统的 MTI 来控制临时存储

原文作者： Tim Clark

MTI 的简要说明

在本系列的第一部分中，我们描述了 SQL 优化器所使用的各类临时存储。我们展示了 QSYS2.SYSTMPSTG 这个系统视图是如何为整个系统的临时存储提供一个高级概览的。有五个全局的 Bucket 用来直接反映 SQL 优化器的使用情况。在这些 Bucket 中，本文将重点讨论标记为 *DATABASE DSI SQE MTI 的 Bucket 。该 Bucket 会报告在整个系统中由 MTI（ Maintained Temporary Indexes - 临时维持索引）所使用的存储容量。

MTI 是优化器在没有任何用户干预的情况下所创建的索引。它们的作用和系统中的其他索引一样。在内部，它们是基数索引，就像那些永久索引（即由用户所创建的索引）一样，它们提供按键顺序读取行或探测特定值的能力。与任何永久索引一样， MTI 的实际大小在很大程度上依赖于索引中包含的键及其基础数据。它们是被主动维护的，这意味着数据空间中的任何更改都会立即反映在 MTI 上。在许多情况下， MTI 可以在查询之间共享，就像永久索引一样。但是它们与永久索引有两个重要的区别：它们使用临时存储，并且完全由优化器来管理。它们会在优化器需要使用时被创建，并会在优化器结束使用时被删除。

用最简单的话说，优化器构建 MTI 只有一个原因：不存在适合的永久索引来满足查询的需求。如果表上有正确的索引，那么优化器就不需要构建 MTI 。但是，如果我们深入研究这一基本原因，我们会发现一些独特的情况，导致优化器会需要索引。了解每种情况 —— 以及它是否适用于您的工作负载 —— 对于有效管理 MTI 的使用至关重要。

可能产生 MTI 的第一种情况是，优化器决定 MTI 可以为查询提供比现有索引更有效的实现。如果构建 MTI 的估计成本对优化器来说是合理的，那么它将生成 MTI ，然后使用它来运行查询。这些 MTI 是共享的：它们将被其他查询看到并可被其他查询所使用。共享 MTI 的生命周期与底层数据空间的存在，以及使用 MTI 的一个或多个查询在 SQL 计划缓存中的存在相关联。如果相关的底层数据空间被删除，或者所有相关的 SQL 计划被从计划缓存中移除， MTI 也将被删除。（因为它们使用的是临时存储， MTI 也不可能超越当前的 IPL 而持续存在。）

可能产生 MTI 的第二种情况是，在使用敏感游标或将 ALWCPYDTA 参数设置为 *NO 的环境中执行查询排序或分组。当查询还具有谓词（ WHERE 子句）时，优化器可能将 MTI 构建为一个_稀疏_索引，这意味着只有那些匹配谓词的行才会被包含在索引当中。稀疏 MTI 的生命周期与创建它们的查询相绑定。当该查询被硬关闭时， MTI 也将被删除。虽然这种行为似乎有助于防止 MTI 的积累，但这也意味着这些稀疏 MTI 无法在查询之间共享。如果工作负载中有数百或数千个这样的查询，这就可能会成为一个问题。每个查询都将构建自己的稀疏 MTI ，并填充临时存储。如果应用程序没有及时关闭游标，那问题就将变得更大。 IBM 支持人员已经看到过不止一次类似这样的严重状况，而这种情况可以通过修改执行环境或者构建少量永久索引来轻松地加以避免。

大多数时候， MTI 只是工作 ，不需要数据库工程师或系统管理员的关注。 MTI 只是优化器为实现查询尽快得以执行的目标时而使用的工具之一。不过，有两个重要原因需要监视系统上的 MTI 使用情况。

临时存储的监控

监视 MTI 的第一个原因是，太多和太大的 MTI 可能会对系统的处理能力产生负面影响。由于 MTI 构建在临时存储上，因此它们会占用系统辅助存储池（ ASP ）中的空间，即使相关的数据空间可能位于另一个存储池中。当您的系统接近临时存储的极限值时，相关进程会变慢或者停止，有时您的系统甚至需要一次初始程序负载（ IPL ）才能获得有效的恢复。

如果您的系统允许用户运行自定义的查询，那么监视 MTI 尤其重要。您的工作负载是否仅限于运行有一组可预测查询的应用程序，或者一些用户是否可以通过使用纯粹的 SQL 语句或者通过使用报表或 BI 工具以生成自己的特殊查询？如果允许特殊查询，则系统更容易受到 MTI 使用变化的影响。一条编写得很差的查询 —— 或者只是没有足够的支持索引 —— 都可能会导致 MTI 的创建。这种情况发生得越多， MTI 临时存储的使用量就会增长得越大。

您可以通过参考 QSYS2. SYSTEMSTG 视图并寻找标记为 *DATABASE DSI SQE MTI 的全局 Bucket 来监视当前 MTI 的使用情况。它将告诉您的 MTI 使用了多少存储空间。

您还可以评估系统上当前有多少 MTI 处于活动状态。时间点信息可以从 IBM i Access Client Solutions SQL Performance Center 的 Plan Cache 选项卡的 Plan Usage Summary 部分中获得。 The Total Number of Temporary Indexes Created 这一条目统计了自上次 IPL 以来系统创建 MTI 的次数。 The Current number of Temporary Indexes 这一条目的当前数量则会告诉我们系统中当前有多少 MTI 处于活动状态。

图 1 – SQL Performane Center 里的 Plan Cache 详细信息

您可以使用 IBM Navigator for i 的性能数据来查找关于 MTI 数量的历史信息。数据是由收集服务（ Collection Services ）收集的。因此，请确保在您想要监视的任何系统上都启用了该功能。在 IBM Navigator for i 中，沿着以下路径将让您获得如图 2 所示的图形。

Performance → Investigate Data → Database → SQL Performance Data → Collection Services → Maintained Temporary Indexes （ MTI ）。

图 2 - IBM Navigator for i 里的 MTI 图表

注意：被删除的 MTI 数（如图 2 所示）在 IBM i 7.3 之前的版本中是不准确的。

通过查看几天和几周的数据，您可以了解系统的基本行为。柱状图显示的是被创建和被删除的 MTI 条目数量，而折线图显示的是活动的 MTI 总数。由于 MTI 的大小可能因工作负载的不同而不同，因此没有一个_正确或者最佳_的 MTI 数量需要系统去达成。但是，如果您的系统运行时临时存储不足，并且您看到 *DATABASE DSI SQE MTI 是一个很大的贡献者，那么这个图将是一个很好的起点。性能数据可以帮助您理解并识别那些与存储使用增加相关联的 MTI 使用峰值。一旦您确定了 MTI 创建数量异常的某个时间间隔，您或许就可以确定那些触发了 MTI 活动的作业或者应用程序。

监视以获得最佳查询性能

监视 MTI 的第二个原因是，它们可能成为优化器正在不必要的约束下运行的一个线索。这种约束可能是游标灵敏性，或者过于严格的 ALWCPYDTA 设置。 也可能是优化器缺少用以完成其工作所需的永久索引。深入讨论这些约束超出了本文的范围，但是下面几节中的策略和资源会是一个很好的开始。

深入了解细节

在确定了您的系统存在过度使用 MTI 的问题之后，一个很好的起点是 Index Advisor 。这是因为每次创建或使用 MTI 时都会生成一条索引建议。（索引建议也可以在其他条件下生成，但那些并非我们在这里所直接关注的部分。）下面的步骤向您展示了如何使用 IBM i Access Client Solutions Index Advisor 来找到最常用的 MTI 。确保您正在运行最新版本的 IBM i Access Client Solutions 。请参阅本文末尾的参考资料部分。 IBM Navigator for i 通过 Database → All Tasks → Health and Performance → Index Advicor 路径，可以显示类似的信息。

在 IBM i Access Client Solutions 中，选择您的系统并单击 Actions 。 然后，在 Database 下，单击 Schemas 。

图 3 – IBM i Access Client Solutions

在菜单栏上，单击 Action s → Index Advisor → Adviced Indexes 。

图 4 – IBM i Access Client Sollutions 里的 Schemas 窗口

在建议的索引列表中， MTI Used 、 MTI Created 和 MTI Last Used 这些列将帮助您确定哪些库（ Schema ）和表（ Table ）最经常使用 MTI 。如果性能图（如图 2 所示）显示了 MTI 创建的峰值，那么在 First Advised for Query Use 或 MTI Last Used 列中找到相关的时间可能可以帮助您识别那些已经开始使用 MTI 的新的或者更改的工作负载。

图 5 – Index Advisor

注意：在 Index advisor 工具中（如图 5 所示），为了清晰起见，我们隐藏了一些列。

如果这是您第一次查看 Index Advisor ，您可能会发现自己被它显示的大量信息所淹没。由于 Index Advisor 会在运行查询时不断地积累建议，因而可能有多年的数据可供挖掘。有用的是，有一些过滤选项可用来缩小列表（例如，特定的时间间隔）。如果您现在不需要索引建议，一个更好的选择是清除索引建议（单击图 4 中所示的 clear All Advised Indexes… 菜单选项），然后让索引建议在几天或几周内累积起来，以适应系统的工作负载。这可以让您更清楚地了解系统现在的运行情况，并更准确地了解影响当前配置的 MTI 。

系统不提供关于单个 MTI 的详细信息，但是您可以看到与特定表相关联的所有 MTI 的总体量。在 Index Advisor 里右键单击为您感兴趣的表所对应的条目，然后点击 Table → Work With → Indexes 。在列表的顶部，在从属于该表的所有永久索引之前，您可以看到一行针对 MTI 的总结信息。（请记住，索引建议是一个历史记录，而不是当前系统状态的快照。这意味着有时您可能会在建议中找到 MTI ，但在表中看不到相应的 MTI 。）

图 6 - 与 QSMART.BATCH_MONITOR 相关联的所有 MTI 的总体量

注意：为了清晰起见，图 6 中的一些列被隐藏了。

将此信息与前面描述的 MTI 使用信息以及历史图表相结合，可以帮助您确定哪些 MTI 是最严重的临时存储消耗者。

管理 MTI

由于 MTI 完全由优化器管理，并且没有可供用户访问的操控手段，因此减少 MTI 临时存储的使用在很大程度上是如何间接影响优化器远离 MTI 的问题。记住，优化器只有在找不到更好的方法来完成工作时才会构建 MTI 。

例如，您不能显式地删除 MTI （如果不删除底层表 —— 很少有可行的解决方案）。但是您可以提供一个永久索引来代替 MTI 。通过右键单击感兴趣的行并单击 Create Index, Index Advisor 可以很容易地做到这一点。下次优化器处理针对底层数据空间的查询时，它将看到这个永久索引，并使用该索引而不再是 MTI 。随着 MTI 的不再使用，它最终将会被删除。然而，请注意，在可共享 MTI 的情况下， “ 最终 ” 可能是一个相对较长的时间。在此期间，您将同时支付永久索引和 MTI 的存储空间成本。对于容量处于或接近饱和的系统，不应将永久索引视为一种灵丹妙药。在这种关键形势下，最好的策略是结束或者暂挂那些生成 MTI 的工作负载。您可以使用以上步骤收集来的信息以及其他系统数据来识别并控制这些作业。

这最终意味着从 MTI 存储消耗问题中恢复的最佳时间是问题远没有发生之前。经过深思熟虑的索引策略与 SQL 最佳实践相结合，将有助于消除对 MTI 的过度依赖。您可以在 IBM Knowledge Center 的数据库性能部分 中找到关于优化设置和索引建议的更多信息。

一个如何解决问题的实际场景

在结束本文之前，让我们将这些片段放在一个示例场景中。

在下午 4 点，您收到一个警报，表示生产系统的临时存储达到了临界水平。您可以快速连接到系统并查询系统视图 QSYS2.SYSTMPSTG 。 *DATABASE DSI SQE MTI 显然是最大的 Bucket ，而且比您以前见过的都要大。您下一步是点击 IBM Navigator for i 里的 Performance 视图，并在其中打开涉及 MTI 的图表。从下午 2 点开始，您可以看到 MTI 出现了一个很小但很明显的上升。再转到 Index Advisor ，您可以审视在下午 2 点左右首次建议的那些 MTI 。有几个 MTI ，它们都涉及您 ERP 应用的主要事实表的那些列。您已经花了很多时间为这个表创建良好的索引，因此在这里看到索引建议着实令人惊讶，但是您注意到，建议的键是您通常不会看到它们在一起的一些列。为了确认这些确实是存储问题的根源，您尝试显示该表的索引。最上面的一行显示系统中总共有 20 个 MTI ，共占用了 150GB 的存储空间，而这足以使您的临时存储空间超过临界点。

现在，您需要找出是谁或者是什么原因导致构建这些 MTI 。这需要访问 IBM i Access Client Solutions 里的 SQL Performance Center 所提供的 SQL Plan Cache 语句。在那里，您可以为下午 2 点之后运行的并且引用了您之前识别到的事实表的语句添加过滤器。 这仍然有太多要挖掘的内容，所以您还需要过滤已建议索引的语句。这可将范围缩小到 20 多个查询，所有查询都是由同一个作业和用户运行的。有了这些信息，您就可以追踪这些查询背后的用户。您发现用户正在尝试通过 ERP 数据生成一些分析报告。但是用户不知道的是，报表工具运行时有一个敏感的游标，当用户为每个报表打开一个新的浏览器选项卡时，游标仍处于打开状态。这就是 MTI 问题的根源。

要做的第一件事是说服用户停止运行新的报表，并且（如果可能的话）关闭现有的报表。接下来，（如果可能的话）将光标灵敏度更改为不太灵敏。然后，您可以确定这些查询将来是否会被频繁地运行。如果是这样，最好的计划可能是识别出共用的键集，并创建永久索引来覆盖这些键。

无需维护的 MTI

现在您已经了解了 MTI 是如何出现的以及为什么会出现，以及您拥有哪些工具来管理它们，您就能够更好地满足系统的索引需求。这反过来意味着更好地使用系统的临时存储资源。有了正确的理解，您就能帮助指导 IBM Db2 for i SQL 优化器，从而让您的系统尽可能高效地运行。

原文网址： Manage your Maintained Temporary Indexes (MTIs) – IBM Developer