优化表（一）

用户7741497

发布于 2022-06-08 11:21:39

1.3K00

代码可运行

文章被收录于专栏：hml_知识记录hml_知识记录

运行总次数：0

代码可运行

优化表（一）

要确保InterSystems IRIS®Data Platform上的InterSystems SQL表的最高性能，可以执行多种操作。优化可以对针对该表运行的任何查询产生重大影响。本章讨论以下性能优化注意事项：

ExtentSize、Selective和BlockCount用于在用数据填充表之前指定表数据估计；此元数据用于优化未来的查询。
运行tune Table来分析填充表中的代表表数据；生成的元数据用于优化未来的查询。
优化表计算的值包括扩展大小、选择性、异常值选择性、平均字段大小和块计数
导出和重新导入优选表统计数据

扩展大小、选择性和块数(ExtentSize, Selectivity, and BlockCount)

当查询优化器决定执行特定SQL查询的最有效方式时，它会考虑以下三种情况：

查询中使用的每个表的ExtentSize行计数。
Selectivity为查询使用的每列计算的DISTINCT值的百分比。
查询使用的每个SQL映射的块计数。

为了确保查询优化器能够做出正确的决策，正确设置这些值非常重要。

在用数据填充表之前，可以在类(表)定义期间显式设置这些统计信息中的任何一个。
在用代表性数据填充表之后，可以运行tune Table来计算这些统计数据。
运行TuneTable之后，可以通过指定显式值来覆盖计算的统计信息。

可以将显式设置的统计信息与优化表生成的结果进行比较。如果优化表所做的假设导致查询优化器的结果不是最优的，则可以使用显式设置的统计信息，而不是优化表生成的统计信息。

在Studio中，类编辑器窗口显示类源代码。在源代码的底部，它显示了Storage定义，其中包括类ExtentSize和每个属性的选择性(如果合适，还包括OutlierSelectivity)。

ExtentSize

表的ExtentSize值就是表中存储的行数(大致)。

在开发时，可以提供初始ExtentSize值。如果未指定ExtentSize，则默认值为100,000。

通常，会提供一个粗略的估计，即在填充数据时该表的大小是多少。有一个确切的数字并不重要。此值用于比较扫描不同表的相对成本; 最重要的是确保关联表之间的ExtentSize的相对值代表一个准确的比例(也就是说，小表的值应该小，大表的值应该大)。

CREATE TABLE提供了一个%EXTENTSIZE参数关键字来指定表中的预期行数，示例如下:

CREATE TABLE Sample.DaysInAYear (%EXTENTSIZE 366, 
                                 MonthName VARCHAR(24),Day INTEGER,
                                 Holiday VARCHAR(24),ZodiacSign VARCHAR(24))

表的持久类定义可以在存储定义中指定ExtentSize参数:

<Storage name="Default">
<Data name="MyClassDefaultData">
...
<ExtentSize>200</ExtentSize>
...
</Storage>

在本例中，片段是MyClass类的存储定义，它为ExtentSize指定了200的值。

如果表有真实的(或真实的)数据，可以使用管理门户中的调优表功能自动计算和设置它的区段大小值;

Selectivity

在InterSystems SQL表(类)中，每个列(属性)都有一个与之相关联的选择性值。列的选择性值是在查询该列的典型值时返回的表中的行的百分比。选择性为1/D，其中D是字段不同值的数目，除非检测到异常值。

选择性基于大致相等的不同值的数量。例如，假设一个表包含一个性别列，其值大致均匀分布在“M”和“F”之间。性别栏的选择值将为50%。更具区分性的特性(例如街道名称Street Name)的选择性值通常只有很小的百分比。

所有值都相同的字段的选择性为100%。为了确定这一点，优化器首先测试一小部分或几条记录，如果这些记录都具有相同的字段值，它将测试多达100,000条随机选择的记录，以支持非索引字段的所有值都相同的假设。如果在对100,000条随机选择的记录进行的测试中可能未检测到某个字段的其他值，则应手动设置选择性。

定义为唯一(所有值都不同)的字段的选择性为1(不应与1.0000%的选择性混淆)。例如，RowID的选择性为1。

在开发时，可以通过在存储定义中定义一个选择性参数来提供此值，该参数是表的类定义的一部分：

<Storage name="Default">
<Data name="MyClassDefaultData">
...
<Property name="Gender">
<Selectivity>50%</Selectivity>
</Property>
...
</Storage>

若要查看类的存储定义，请在Studio中，从“视图”菜单中选择“查看存储”；Studio在类的源代码底部包含存储。

通常，需要提供在应用程序中使用时预期的选择性的估计值。与ExtentSize一样，拥有确切的数字并不重要。InterSystems IRIS提供的许多数据类型类将为选择性提供合理的默认值。

还可以使用SetFieldSelectivity()方法设置特定字段(属性)的选择值。

如果表中有真实的(或真实的)数据，则可以使用管理门户中的Tune table工具自动计算和设置其选择性值。调优表确定一个字段是否有一个离群值，这个值比任何其他值都常见得多。如果是这样，Tune Table将计算一个单独的离群值选择性百分比，并根据这个离群值的存在来计算选择性。异常值的存在可能会极大地改变选择性值。

选择性用于查询优化。在SELECT查询中指定的字段和在视图的SELECT子句中指定的字段使用相同的选择性值。请注意，视图的行分布可能与源表不同。这可能会影响视场选择性的精度。

BlockCount

当编译一个持久化类时，类编译器会根据区段大小和属性定义计算每个SQL映射使用的映射块的大致数量。可以在调优表工具的Map BlockCount选项卡中查看这些BlockCount值。块计数在调优表中由类编译器估计。注意，如果更改了区段大小，则必须关闭并重新打开SQL Tune Table窗口，以查看该更改反映在BlockCount值中。

当运行Tune Table时，它会测量每个SQL映射的实际块计数。除非另有指定，调优表测量值将替换类编译器的近近值。这些调优表测量值在类定义中表示为负整数，以区别于指定的BlockCount值。如下面的例子所示:

<SQLMap name="IDKEY">
 <BlockCount>-4</BlockCount>
</SQLMap>

调优表测量值在调优表中表示为正整数，标识为由调优表测量。

可以在类定义中定义显式的块计数值。可以显式地指定块计数为正整数，如下面的示例所示:

<SQLMap name="IDKEY">
 <BlockCount>12</BlockCount>
</SQLMap>

当定义一个类时，可以省略为map定义BlockCount，显式地指定一个BlockCount为正整数，或显式地定义BlockCount为NULL。

如果不指定块计数，或指定块计数为0，则类编译器估计块计数。运行Tune Table将替换类编译器的估计值。
如果指定一个显式的正整数BlockCount，运行Tune Table不会替换此显式的BlockCount值。在调优表中，显式的类定义块计数值表示为正整数，标识为在类定义中定义的。这些块计数值不会通过随后运行Tune Table而更改。
如果将显式BlockCount指定为NULL，则SQL Map将使用类编译器估计的BlockCount值。因为BlockCount在类定义中是“定义的”，所以运行Tune Table不会替换这个估计的BlockCount值。

所有InterSystems SQL映射块的大小为2048字节(2K字节)。

在以下情况下，优化表不测量块计数：

如果表是由数组或列表集合投影的子表。这些类型的子表的BlockCount值与父表数据映射的BlockCount值相同。
如果全局映射是远程全局(不同名称空间中的全局)。取而代之的是使用在类编译期间使用的估计的BlockCount。

Tune Table

Tune Table是一个实用程序，它检查表中的数据，并返回关于区段大小(表中的行数)、每个字段中不同值的相对分布以及平均字段大小(每个字段中值的平均长度)的统计信息。它还为每个SQL映射生成块计数。可以指定该调优表，使用此信息更新与表及其每个字段相关联的元数据。查询优化器随后可以使用这些统计信息来确定最有效的查询执行计划。

在外部表上使用Tune Table将只计算区段大小。调优表无法计算外部表的字段选择性值、平均字段大小或映射块计数值。

何时运行调优表

应该在每个表填充了具有代表性的实际数据之后，在该表上运行tune Table。通常，在数据“激活”之前，只需要运行一次tune Table，这是应用程序开发的最后一步。Tune Table不是维护实用程序；它不应对实时数据定期运行。

注：在极少数情况下，运行调优表会降低SQL性能。虽然TuneTable可以在实时数据上运行，但建议在具有实际数据的测试系统上运行TuneTable，而不是在生产系统上运行。可以使用可选的系统模式配置参数来指示当前系统是测试系统还是活动系统。设置后，系统模式将显示在管理门户页面的顶部，并可由$SYSTEM.Version.SystemMode()方法返回。

通常，在添加、修改或删除表数据时不应重新运行Tune Table，除非当前数据的特征发生了数量级的更改，如下所示：

相对表大小：Tune Table假设它正在分析具有代表性的数据子集。如果该子集是代表性子集，则该子集只能是整个数据集的一小部分。如果联接或其他关系中涉及的表的ExtentSize保持大致相同的相对大小，则当表中的行数发生变化时，Tune Table结果仍然是相关的。如果连接表之间的比率更改了一个数量级，则需要更新ExtentSize。这对于JOIN语句很重要，因为SQL优化器在优化表连接顺序时使用ExtentSize。一般来说，无论查询中指定的联接顺序如何，都会先联接较小的表，然后再联接较大的表。因此，如果tableA和tableB中的行比从1000：2000更改为10000：2000，可能在一个或多个表上重新运行tune Table，但如果更改为2100：4000，则不需要重新运行tune Table。
均匀值分布：优化表假设每个数据值的可能性都是相等的。如果它检测到离群值，它会假定除离群值之外的每个数据值的可能性都是相等的。调谐表通过分析每个字段的当前数据值来建立选择性。真实数据的可能性相等始终是一个粗略的近似值；不同数据值的数量及其相对分布的正态变化不应保证重新运行调优表。但是，字段可能值的数量(不同值与记录的比率)的数量级变化或单个字段值的总体可能性可能会导致不准确的选择性。大幅更改具有单个字段值的记录的百分比可能会导致TuneTable指定一个离群值或删除指定的离群值，从而显著改变计算的选择性。如果字段的选择性不再反映数据值的实际分布，则应重新运行调优表。
重大升级或新的站点安装可能需要重新运行Tune Table。