主页 / dba / 问题 / 84747
Accepted
JGA
Asked: 2014-12-10 14:21:57 +0800 CST

统计数据。多列直方图可能吗？

  • 772

我正在考虑一种情况，我有两列高密度，但这些列不是独立的。

定义

这是我为测试目的创建的表的定义。

CREATE TABLE [dbo].[StatsTest](
    [col1] [int] NOT NULL,  --can take values 1 and 2 only
    [col2] [int] NOT NULL,  --can take integer values from 1 to 4 only
    [col3] [int] NOT NULL,  --integer. it has not relevance just to ensure that each row is different
    [col4]  AS ((10)*[col1]+[col2])  --a computed column ensuring that if two rows have different values in col1 or col2 have different values in col4 
) ON [PRIMARY]

数据

实验数据如下

col1    col2    col3    col4
1       1       1       11
1       2       2       12
1       2       3       12
1       3       4       13
1       3       5       13
1       3       6       13
1       4       7       14
1       4       8       14
1       4       9       14
1       4       10      14
2       1       11      21
2       1       12      21
2       1       13      21
2       1       14      21
2       2       15      22
2       2       16      22
2       2       17      22
2       3       18      23
2       3       19      23
2       4       20      24

第 1 步：按 col1 过滤

SELECT * FROM StatsTest WHERE col1=1

正如预期的那样，查询优化器会猜测确切的行数。 实际行数 = 10，估计行数 = 10

第 2 步：按 col2 过滤

SELECT * FROM StatsTest WHERE col2=1

我们再次有一个完美的估计。

实际行数 = 5，估计行数 = 5

第 3 步：按 col1 和 col2 过滤

SELECT * FROM StatsTest WHERE col1=1 AND col2=1

这里的估计远未接近实际的行数。 实际行数 = 1，估计行数 = 3,53553

问题是查询分析器隐含地假设 col1 和 col2 是独立的，但它们不是。

第 4 步：按 col4 过滤

SELECT * FROM StatsTest WHERE col4 = 11

我可以通过 col4 = 11 过滤以获得与步骤 3 中的查询相同的结果，因为 col4 是一个计算列，并且根据它的定义方式col1 = 1 和 col2 = 1相当于col4 = 11 但是在这里，正如预期的那样，估计是完美的。

实际行数 = 1，估计行数 = 1

结论/问题

¿ 在处理由两个或更多非独立列进行过滤时，这种人为且不优雅的解决方案是实现准确估计的唯一可用选项吗？¿ 计算列和计算列的过滤器是否是获得实际精度所必需的？

sqlfiddle中的示例

sql-server statistics

1 个回答

  1. Best Answer

    多列直方图可能吗？

    不是真正的多维直方图，不。

    在处理由两个或更多非独立列进行过滤时，这种人为且不优雅的解决方案是实现准确估计的唯一可用选项吗？

    SQL Server 确实支持“多列”统计信息，但除了第一个命名列的直方图外，它们仅捕获平均密度（相关性）信息。它们仅对相等比较有用。

    平均密度信息不会捕获任何细节，因此您将对两列统计对象上的任何一对值获得相同的选择性。在某些情况下，多列统计信息可能已经足够好，而且聊胜于无。多列统计信息自动建立在多列索引上。

    根据 SQL Server 版本，您还可以使用过滤索引和过滤统计信息：

    -- Filtered statistics example
CREATE STATISTICS stats_StatsTest_col2_col1_eq_1
ON dbo.StatsTest (col2)
WHERE col1 = 1;

CREATE STATISTICS stats_StatsTest_col2_col1_eq_2
ON dbo.StatsTest (col2)
WHERE col1 = 2;

    或者您可以构建一个索引视图（它可以支持自己的索引和统计信息）。DATE_CORRELATION_OPTIMIZATION索引视图是数据库设置背后的机制，这是用于表间关联的一个很少使用的功能，但它适用于问题的精神。

    计算列和计算列的过滤器对于获得实际精度是绝对必要的吗？

    这不是唯一的方法。除了已经提到的内容之外，您还可以指定计算列的确切文本定义，优化器通常会将其与计算列的统计信息相匹配。

    还有一些跟踪标志可以改变关于多列相关性的假设。此外，SQL Server 2014 中的默认相关性假设（启用了新的基数估计器）从独立性更改为指数退避（更多详细信息请点击此处和此处）。最终，这只是一个不同的假设。在许多情况下会更好，而在其他情况下会更糟。

    获得良好的执行计划并不总是需要精确的基数估计精度。在生成可针对不同参数值重复使用的计划和对特定执行而言最佳但不重复使用的计划之间总是需要权衡取舍。

    • 16

相关问题