优化查询性能（一）

优化查询性能（一）

InterSystems SQL自动使用查询优化器创建在大多数情况下提供最佳查询性能的查询计划。该优化器在许多方面提高了查询性能，包括确定要使用哪些索引、确定多个AND条件的求值顺序、在执行多个联接时确定表的顺序，以及许多其他优化操作。可以在查询的FROM子句中向此优化器提供“提示”。本章介绍可用于评估查询计划和修改InterSystems SQL将如何优化特定查询的工具。

InterSystems IRIS®Data Platform支持以下优化SQL查询的工具：

• SQL Runtime Statistics用于生成查询执行的运行时性能统计信息
• 索引分析器，用于显示当前命名空间中所有查询的各种索引分析器报告。这显示了InterSystems SQL将如何执行查询，可以全面了解索引是如何使用的。此索引分析可能表明应该添加一个或多个索引以提高性能。
• 查询执行计划：显示SQL查询(查询计划)的最佳(默认)执行计划，并可选地显示该SQL查询的备用查询计划以及统计信息。用于显示查询计划的工具包括SQL EXPLAIN命令、$SYSTEM.SQL.ExPlan()方法以及管理门户和SQL Shell中的各种Show Plan工具。查询计划和统计数据是在准备查询时生成的，不需要执行查询。

可以使用以下选项来指导查询优化器，方法是设置配置默认值或在查询代码中编码优化器“提示”：

• 管理所有条件的子句选项中提供的索引优化选项，或单个条件前面的%NOINDEX。
• SQL代码中指定的注释选项，使优化器覆盖该查询的系统范围编译选项。
• 在每个查询或系统范围的基础上可用的并行查询处理允许多处理器系统在处理器之间划分查询执行。

以下SQL查询性能工具将在本手册的其他章节中介绍：

• 缓存查询，使动态SQL查询能够重新运行，而无需在每次执行查询时准备查询的开销。
• SQL语句来保留最新编译的嵌入式SQL查询。在“SQL语句和冻结计划”一章中。
• 冻结计划以保留嵌入式SQL查询的特定编译。使用此编译，而不是使用较新的编译。在“SQL语句和冻结计划”一章中。

以下工具用于优化表数据，因此可以对针对该表运行的所有查询产生重大影响：

• 定义索引可以显著提高对特定索引字段中数据的访问速度。
• ExtentSize、Selective和BlockCount用于在用数据填充表之前指定表数据估计；此元数据用于优化未来的查询。
• Tune Table用于分析已填充的表中的代表性表数据；生成的元数据用于优化未来的查询。

本章还介绍如何将查询优化计划写入文件，以及如何生成SQL故障排除报告以提交给InterSystems WRC。

管理门户SQL性能工具

IRIS管理门户提供对以下SQL性能工具的访问。有两种方式可以从管理门户系统资源管理器选项访问这些工具：

• 选择工具，然后选择SQL性能工具。
• 选择SQL，然后选择工具下拉菜单。

从任一界面中您都可以选择以下SQL性能工具之一：

• SQL运行时统计信息，以生成查询执行的性能统计信息。
• 索引分析器，用于显示当前命名空间中所有查询的各种索引分析器报告。这显示了InterSystems SQL将如何执行查询，可以全面了解索引是如何使用的。此索引分析可能表明应该添加一个或多个索引以提高性能。
• 备用显示计划：显示SQL查询的可用备用查询计划以及统计信息。
• 生成报告以向InterSystems Worldwide Response Center(WRC)客户支持部门提交SQL查询性能报告。要使用此报告工具，必须首先从WRC获得WRC跟踪号。
• 导入报告允许查看SQL查询性能报告。

SQL运行时统计信息

可以使用SQL运行时统计信息来衡量系统上运行的SQL查询的性能。SQL运行时统计信息衡量SELECT、INSERT、UPDATE和DELETE操作(统称为查询操作)的性能。SQL运行时统计信息(SQL Stat)是在准备查询操作时收集的。请参阅使用SQL运行时统计信息工具。

默认情况下，SQL运行时统计信息的收集处于关闭状态。必须激活统计信息收集。强烈建议指定超时以结束统计信息收集。激活统计信息收集后，必须重新编译(准备)现有的动态SQL查询，并重新编译包含嵌入式SQL的类和例程。

性能统计信息包括ModuleName、ModuleCount(模块被调用的次数)、RowCount(返回的行数)、TimeSpent(执行性能，单位为秒)、GlobalRefs(全局引用数)、LinesOfCode(执行的行数)和ReadLatency(磁盘读取访问时间，单位为毫秒)。

可以显式清除SQL Stats数据。清除缓存查询会删除所有相关的SQL统计数据。删除表或视图会删除所有相关的SQL Stats数据。

注意：系统任务在所有名称空间中每小时自动运行一次，以将特定于进程的SQL查询统计信息聚合到全局统计信息中。因此，全局统计信息可能不会反映一小时内收集的统计信息。可以使用管理门户监视此每小时一次的聚合或强制其立即发生。要查看此任务上次完成和下次调度的时间，请依次选择系统操作、任务管理器、任务调度，然后查看更新SQL查询统计信息任务。可以单击任务名称查看任务详细信息。在Task Details(任务详细信息)显示中，可以使用Run(运行)按钮强制立即执行任务。

使用SQL运行时统计信息工具

可以使用以下任一方法从管理门户显示系统范围内的SQL查询的性能统计信息：

• 选择系统资源管理器，选择工具，选择SQL性能工具，然后选择SQL运行时统计信息。
• 选择系统资源管理器，选择SQL，然后从工具下拉菜单中选择SQL运行时统计信息。

Settings

“设置”选项卡显示当前系统范围的SQL运行时统计信息设置以及此设置的过期时间。

Change Settings(更改设置)按钮允许设置以下统计信息收集选项：

• 收集选项：可以将统计信息收集选项设置为0、1、2或3.0。0=关闭统计信息代码生成；1=为所有查询打开统计信息代码生成，但不收集统计信息；2=仅记录查询外部循环的统计信息(在主模块打开和关闭时收集统计信息)；3=记录查询的所有模块级别的统计信息。
  • 从0到1：更改SQL Stats选项后，需要编译包含SQL的例程和类以执行统计代码生成。对于xDBC和动态SQL，必须清除缓存查询以强制重新生成代码。
  • 要从1变为2：只需更改SQL Stats选项即可开始收集统计信息。这使可以在运行的生产环境中启用SQL性能分析，并将中断降至最低。
  • 从1到3(或从2到3)：更改SQL Stats选项后，需要编译包含SQL的例程和类，以记录所有模块级别的统计信息。对于xDBC和动态SQL，必须清除缓存查询以强制重新生成代码。选项3通常仅用于非生产环境中已识别的性能较差的查询。
  • 从1、2或3变为0：要关闭统计代码生成，不需要清除缓存的查询。
• 超时选项：如果收集选项为2或3，可以按已用时间(小时或分钟)或按完成日期和时间指定超时。可以用分钟或小时和分钟指定运行时间；该工具将指定的分钟值转换为小时和分钟(100分钟=1小时40分钟)。默认值为50分钟。日期和时间选项默认为当天午夜(23：59)之前。强烈建议指定超时选项。
• 重置选项：如果收集选项为2或3，则可以指定超时值到期时要重置为的收集选项。可用选项为0和1。

查询测试

查询测试选项卡允许输入SQL查询文本(或从历史记录中检索)，然后显示该查询的SQL统计信息和查询计划。查询测试包括查询的所有模块级别的SQL统计信息，而与收集选项设置无关。

输入一个SQL查询文本，或使用Show History按钮检索一个。 可以通过单击右边的圆形“X”圆来清除查询文本字段。

使用Show Plan With SQL Stats按钮执行。

默认情况下，后台复选框中的“运行Show Plan进程”未被选中，这是大多数查询的首选设置。 仅对长时间、运行缓慢的查询选择此复选框。 当这个复选框被选中时，你会看到一个进度条显示“请等待…”的消息。 当运行一个长查询时，带有SQL Stats和Show History按钮的Show Plan消失，而显示一个View Process按钮。 单击View Process将在新选项卡中打开流程详细信息页面。 在流程详细信息页面中，可以查看该流程，并可以暂停、恢复或终止该流程。 流程的状态应该反映在显示计划页面上。 当流程完成后，显示计划会显示结果。 View Process按钮消失，带有SQL Stats的Show Plan和Show History按钮重新出现。

使用查询测试显示的语句文本包括注释，不执行文字替换。

查看统计信息

View Stats(查看统计信息)选项卡为提供了在此系统上收集的运行时统计信息的总体视图。

可以单击任何一个View Stats列标题对查询统计信息进行排序。然后，可以单击SQL语句文本以查看所选查询的详细查询统计信息和查询计划。

使用此工具显示的语句文本包括注释，不执行文字替换。ExportStatsSQL()和Show Plan显示的语句文本会去掉注释并执行文字替换。

清除统计信息按钮

清除统计信息按钮清除当前名称空间中所有查询的所有累积统计信息。它会在SQL运行时统计信息页上显示一条消息。如果成功，则会显示一条消息，指示已清除的统计信息数量。如果没有统计信息，则会显示无要清除的消息。如果清除不成功，则会显示一条错误消息。

运行时统计信息和显示计划

SQL运行时统计信息工具可用于显示包含运行时统计信息的查询的显示计划。

可以使用Alternate Show Plans工具将显示计划与统计数据进行比较，从而显示查询的运行时统计信息。备用显示计划工具在其显示计划选项中显示查询的估计统计信息。如果激活了收集运行时统计信息，则其Compare Show Plans with Stats选项将显示实际的运行时统计信息；如果运行时统计信息未处于活动状态，则此选项将显示估计统计信息。