sql-server - 为什么查询优化器完全忽略索引 View 索引？

Question

SQL fiddle :http://sqlfiddle.com/#!6/d4496/1 (数据是为您的实验预先生成的)

有明显的表格:

CREATE TABLE Entity 
(
  ID int,
  Classificator1ID int,
  Classificator2ID int,
  Classificator3ID int,
  Classificator4ID int,
  Classificator5ID int
);

和 View :

CREATE VIEW dbo.EntityView (ID, Code1, Code2, Code3, Code4, Code5) 
WITH SCHEMABINDING

其中实体字段 Classificator1ID..Classificator5ID 解析为分类器值 Code1..Code5

这个 View 上有很多索引:

CREATE UNIQUE CLUSTERED INDEX [IXUC_EntityView$ID] ON EntityView
  ([ID]);
CREATE UNIQUE NONCLUSTERED  INDEX [IXU_EntityView$ID$include$ALL] ON EntityView
  ([ID]) INCLUDE (Code1, Code2, Code3, Code4,  Code5);
CREATE UNIQUE NONCLUSTERED  INDEX [IXU_EntityView$ALL] ON EntityView
  ([ID],Code1, Code2, Code3, Code4,  Code5);  
CREATE UNIQUE NONCLUSTERED  INDEX [IXU_EntityView$ID$Code1] ON EntityView
  ([ID],Code1);
CREATE UNIQUE NONCLUSTERED  INDEX [IXU_EntityView$ID$include$Code1] ON EntityView
  ([ID])INCLUDE (Code1);
CREATE NONCLUSTERED  INDEX [IX_EntityView$Code1] ON EntityView
  (Code1);
CREATE NONCLUSTERED  INDEX [IX_EntityView$Code1$include$ID] ON EntityView
  (Code1) INCLUDE (ID);

但 QO 从不使用它们!试试这个:

SELECT * FROM EntityView;

SELECT ID, Code1 FROM EntityView;

SELECT ID, Code1, Code2, Code3, Code4, Code5 FROM EntityView;

SELECT ID, Code1, Code2, Code3, Code4, Code5 FROM EntityView WHERE ID=1;

SELECT ID, Code1 FROM EntityView Where Code1 like 'NR%';

为什么？尤其是“包含”索引有什么问题？索引已创建，具有所有字段但仍未使用...

添加:这只是测试!请不要那么生气，也不要逼我分析那些索引维护问题。

在我的实际项目中，我无法解释为什么 QO 忽略索引 View (非常非常有用的索引 View )。但有时我看到它在其他地方使用它们。我创建了这个数据库片段来试验索引公式，但也许我应该做更多的事情:以某种方式调整统计数据？

Answer 1

tl;dr 答案:如果您不指定 NOEXPAND，查询优化器不知道您正在从 View 提交简单的选择。它必须将查询的扩展(这是它所看到的)与某些 View 索引相匹配。当它是带有一堆强制转换的五路连接时，可能不会打扰。

View 与查询的索引匹配是一个难题，我相信您的 View 对于查询引擎来说太复杂而无法与索引匹配。考虑一下您的查询之一:

SELECT ID, Code1 FROM EntityView Where Code1 > 'NR%';

很明显，这可以使用 View 索引，但这不是查询引擎看到的查询。如果您不指定 NOEXPAND， View 会自动展开，因此这就是查询引擎的内容:

SELECT ID, Code1 FROM (
    SELECT e.ID, 'NR'+CAST(c1.CODE as nvarchar(11)) as Code1, 'NR'+CAST(c2.CODE as nvarchar(11)) as Code2, 'NR'+CAST(c3.CODE as nvarchar(11)) as Code3, 'NR'+CAST(c4.CODE as nvarchar(11)) as Code4, 'NR'+CAST(c5.CODE as nvarchar(11)) as Code5
    FROM dbo.Entity e
        inner join  dbo.Classificator1 c1 on e.ID = c1.ID
        inner join  dbo.Classificator2 c2 on e.ID = c2.ID
        inner join  dbo.Classificator3 c3 on e.ID = c3.ID
        inner join  dbo.Classificator4 c4 on e.ID = c4.ID
        inner join  dbo.Classificator5 c5 on e.ID = c5.ID;
) AS V;

查询引擎看到这个复杂的查询，并且它具有描述已定义的 View 索引的信息(但可能不是 View 定义的 SQL)。鉴于此查询和 View 索引都具有多个联接和强制转换，匹配是一项艰巨的工作。

请记住，您知道此查询和 View 索引中的连接和匹配是相同的，但查询处理器并不知道这一点。它处理该查询的方式与连接 Classificator3 的五个副本或其中一列为 'NQ'+CAST(c2.CODE as varchar(12)) 相同。 View 索引匹配器(假设它尝试匹配这个复杂的查询)必须将该查询的每个细节与所涉及的表上的 View 索引的详细信息相匹配。

查询引擎的第一目标是找到一种有效执行查询的方法。它可能不是为了花费大量时间尝试将五向联接和 CAST 的每个细节与 View 索引相匹配而设计的。

如果我不得不猜测，我怀疑 View 索引匹配器认为查询的结果列甚至不是任何基础表的列(因为 CAST)，并且根本不费心尝试任何事情。 添加:我错了。我刚刚尝试了 Martin 的建议，即更新统计信息以使查询变得昂贵，并且 View 索引与其中一些查询相匹配而无需 NOEXPAND。 View 匹配器比我想象的更聪明!所以问题是，如果成本非常高， View 匹配器可能会更加努力地匹配复杂的查询。

使用 NOEXPAND 提示，而不是期望查询引擎能够找出此处的匹配项。 NOEXPAND 绝对是你的 friend ，因为这样查询引擎就可以看到

SELECT ID, Code1 FROM EntityView Where Code1 > 'NR%';

对于 View 索引匹配器来说，很明显有一个有用的索引。

(注意:您的 SQL Fiddle 代码具有对同一个表的所有 5 个外键引用，这可能不是您想要的。)