主页 / dba / 问题 / 143208
Accepted
John
Asked: 2016-07-08 00:57:37 +0800 CST

在物化视图和查询计划中分组 [重复]

  • 772

部分出于好奇，我想知道是否可以使用索引（物化）视图来加速对某些基表的计数查询。

查询是这样的

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

所以我创建了一个视图

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

带有聚簇索引

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

这行得通，我现在可以将原始查询写为

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

并更快地获得所需的结果。

las，这对我来说几乎没有用，因为我的查询通常不是手工制作的。我真的需要通过使用索引视图作为某种索引来使原始BaseTable查询变得更快- 我想我在某处读到这可能在某些情况下发生，但根据我的测试，不是在这个。

所以我的问题是：

  • 在这种情况下，索引视图还能以某种方式帮助我吗？
  • 任何人都可以推荐资源/文献而不是解释在哪些情况下索引视图确实用于优化它们所基于的表的查询？

编辑：关于重复的问题——我对索引视图的 GROUP BY 和聚合方面更感兴趣。答案帮助我找到了我犯的一个愚蠢的错误，它现在也对我有用。

JOYOUS ADDENDUM：现在我让它工作了，我成功地测试了它甚至在查询包含左连接的情况下也能工作，在这些情况下左连接实际上可以被优化掉（即 on- 子句涵盖唯一索引在联接表中）。

这真的很棒，因为这意味着即使在使用左连接的查询的情况下，也可以以这样一种方式设计模式，即快速获取所有内容或特定分组的总行数。

sql-server materialized-view

1 个回答

  1. Best Answer

    该功能称为“索引视图匹配”，要做到正确可能很棘手。这里概述了一些先决条件：Resolving Indexes On Views，加上索引视图需要能够回答您的查询所提出的问题。我希望它能在 Developer/Enterprise Edition 中用于简单的场景，并且我让它可以使用简单的模式和查询：

    -- Indexed view matching
USE tempdb
GO

IF OBJECT_ID('dbo.BaseTable') IS NOT NULL DROP TABLE dbo.BaseTable
GO

CREATE TABLE dbo.BaseTable (

    rowId       INT IDENTITY PRIMARY KEY,

    slot        INT NOT NULL,
    someDate    DATETIME DEFAULT GETDATE(),
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
)
GO


-- Add dummy data
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.BaseTable ( slot, someDate, someData )
SELECT
    rn % 999,
    DATEADD( day, rn % 333, '1 Jan 2016' ), 
    NEWID()
FROM cte

CHECKPOINT
GO --10


DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

CREATE VIEW dbo.IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;
GO

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);
GO


-- Indexed view matching
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO


-- Indexed view not used as it can't 'cover' the query
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
AND rowId = 42
OPTION ( RECOMPILE );
GO


-- Query Indexed view directly with NOEXPAND hint
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView WITH ( NOEXPAND )
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO


-- EXPAND VIEWS hint ensures Indexed view is NOT used.
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView 
WHERE Slot = @slot
OPTION ( RECOMPILE, EXPAND VIEWS );
GO

    执行计划：

    索引视图匹配

    在我上面的测试脚本中还有一些其他查询，您可以看到其中一些没有使用索引视图。

    以下是查询优化器的一位架构师 Conor Cunningham 对索引视图的引用：

    查询优化器包含在原始查询文本明确引用视图的情况下以及用户提交使用与视图相同的组件（以任何等效顺序）的查询的情况下使用此索引的逻辑。

    但 ...

    [复杂性] ... 使查询优化器难以考虑先执行视图评估然后再处理其余查询的计划。任意树匹配是一个计算复杂的问题，视图的特征集太大而无法有效地执行此操作。

    “Microsoft SQL Server 2008 内部原理”，第 8 章：查询优化器 - Conor Cunningham

    ...索引视图适用于基本 SPJG (SELECT-PROJECT-JOIN-GROUPBY) 查询。连接必须是内部连接，而不是自连接，并且连接在一个键上。每个运营商都有一系列的限制......

    资源

    我认为这些引述很能说明问题。您可能还希望考虑过滤索引。

    • 6

相关问题