第三章优化表（一）

原创

学无为 2022-01-13 16:04:18 ©著作权

文章标签 SQL 优化表调谐表 tune table 数据库 文章分类 R语言后端开发

©著作权归作者所有：来自51CTO博客作者学无为的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

第三章优化表（一）
扩展大小、选择性和块数(ExtentSize, Selectivity, and BlockCount)
Tune Table

第三章优化表（一）

要确保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。

运行 Tune Table

运行调优表有三个接口:

使用Management Portal SQL interface Actions下拉列表，它允许在单个表或多个表上运行Tune Table。

第三章优化表（一）_SQL_02

第三章优化表（一）_优化表_03

为单个表或当前命名空间中的所有表调用$SYSTEM.SQL.Stats.Table.GatherTableStats()方法。
对单个表发出SQL命令调优表。

Tune Table清除引用正在调优的表的缓存查询。
调优表命令提供了一个recompile缓存查询选项，以使用新的调优表计算值重新生成缓存的查询。

如果表映射到只读数据库，则无法执行调优表，并生成错误消息。

在运行了调优表工具之后，生成的区段大小和选择性值将保存在类的存储定义中。
要查看存储定义，在Studio中，从“视图”菜单中选择“视图存储”;
Studio在类源代码的底部包含存储。

从管理门户调优表

要从管理门户运行Tune Table:

选择System Explorer，然后选择SQL。
通过单击页面顶部的Switch选项选择一个名称空间，然后从显示的列表中选择一个名称空间。
(可以为每个用户设置管理门户的默认名称空间。)
从屏幕左侧的下拉列表中选择模式，或者使用筛选器。
执行下列操作之一：

优化单个表：展开表类别，然后从列表中选择一个表。选择表格后，单击操作下拉列表，然后选择调整表格信息。这将显示表的当前ExtentSize和选择性信息。如果从未运行过调谐表，ExtentSize=100000，则不会显示任何选择性、异常值选择性、异常值或平均字段大小信息(除了选择性为1的行ID)，并且会按照类编译器的估计列出映射块计数信息。

从选择性选项卡中，选择调谐表按钮。这将在表上运行tune Table，根据表中的数据计算ExtentSize、选择性、异常值选择性、异常值和Average Field Size值。Map BlockCount(地图块计数)信息按Tune Table(调谐表)测量列出。

单个表上的Tune Table始终作为后台进程运行，并在完成后刷新该表。这可以防止超时问题。当此后台进程正在运行时，将显示一条正在进行的消息。在后台进程执行时，关闭按钮可用于关闭调谐表窗口。

优化方案中的所有表：单击操作下拉列表，然后选择优化方案中的所有表。这将显示调谐表方框。选择Finish按钮在方案中的所有表上运行Tune Table。调谐表完成后，此框显示完成按钮。选择Done(完成)退出Tune Table(调谐表)框。

SQL优化表窗口有两个选项卡：选择性和映射块计数。这些选项卡显示由调谐表生成的当前值。它们还允许手动设置与Tune Table生成的值不同的值。

选择性选项卡包含以下字段：

当前表扩展大小。此字段有一个编辑按钮，允许输入不同的表格扩展大小。
“使类保持最新”复选框。对Tune Table生成的统计数据的任何更改，或由Tune Table界面或Tune Table方法中的用户输入值生成的任何更改，都会立即表示在类定义中：
- 如果未选中此框(否)，则不会设置修改后的类别定义上的最新标志。这表明类定义已过期，应该重新编译。这是默认设置。
- 如果选中此框(是)，类定义将保持标记为最新。在活动系统上更改统计信息时，这是首选选项，因为它降低了重新编译表类定义的可能性。
字段表，其中包含字段名称、选择性、备注、异常值选择性、异常值和平均字段大小等列。通过单击Fields表格标题，可以按该列的值进行排序。通过单击Fields表行，您可以手动设置该字段的选择性、异常值选择性、异常值和平均字段大小的值。

Map BlockCount选项卡包含以下字段：

包含SQL Map Name、BlockCount和Source of BlockCount列的映射名称表。索引的SQL映射名称是SQL索引名；这可能不同于持久类索引属性名。
通过单击单个map名称，可以手动设置该地图名称的BlockCount值。

在选择性选项卡中，可以单击优化表按钮在此表上运行优化表。

使用方法调整表

可以使用$SYSTEM.SQL.Stats.Table.GatherTableStats()方法在当前名称空间中运行Tune Table工具。

GatherTableStats(“Sample.MyTable”)在单个表上运行TuneTable。
GatherSchemaStats(“Sample”)在指定模式中的所有表上运行tune Table。
GatherTableStats(“*”)在当前命名空间中的所有表上运行TuneTable。

使用GatherTableStats()方法时，可能会生成以下错误消息：

不存在的表：

DO $SYSTEM.SQL.Stats.Table.GatherTableStats("NoSuchTable")

No such table 'SQLUser.NoSuchTable'

View视图:

DO $SYSTEM.SQL.Stats.Table.GatherTableStats("Sample.MyView")

'Sample.MyView' is a view, not a table. No tuning will be performed.

当运行GatherTableStats(“*”)或GatherSchemaStats(“SchemaName”)时，如果系统支持并行处理，系统将使用多个进程并行调优多个表。

在分片表上运行Tune table

如果在一个分片表上运行调优表，那么调优表操作将被转发到每个碎片，并针对该表的那个碎片运行。
调优表不会在调用它的主名称空间中执行。
如果在导出到碎片的类定义的非分片表上运行调优表，因为该表已连接到一个分片表，调优表操作将转发到每个碎片，并且它也在主名称空间中执行。

在分片表上运行Tune Table时，应该遵循以下准则:

优化分片主表，而不是分片本地表。
区段大小和块计数值是每个分片的值，而不是所有分片的总和。
如果使用$SYSTEM.SQL.Stats.Table.Export()和$SYSTEM.SQL.Stats.Table.Import()，则导出/导入分片主表的调优统计，而不是分片本地表。
调优切分表将在切分主类和切分本地类/表定义中定义调优统计。
如果手动编辑类定义中的调优表元数据，建议的过程是修改碎片主类的定义，然后重新编译碎片主类。
在编译碎片主类时，碎片主调优统计信息将被复制到类的碎片本地版本。

如果GatherTableStats()或GatherSchemaStats()指定了一个logFile参数，shard master实例中的日志文件有一个针对指定表的条目，例如:

Sharded table: TABLE: <tablename> Invoking TuneTable on shards for sharded table <tablename>
Non-sharded table: TABLE: <tablename> Invoking TuneTable on shards for mapped non-sharded table <tablename>

在每个分片实例上，在mgr/<shard-namespace>目录中创建一个同名的日志文件，记录这个分片上这个表的调优表信息。
如果为日志文件指定了目录路径，那么分片将忽略该路径，并且该文件始终存储在mgr/<shard-namespace>中。