Фильтрованные индексы: сравнение производительности с примерами (Часть 2)

Автор: Luca Biondi, Filtered Index vs Indexed View in SQL Server: Complete Performance Comparison with Real Examples

Эта статья является продолжением предыдущего глубокого погружения в настройку производительности SQL Server!

Если вы пропустили Часть 1, вы можете прочитать её здесь:

Как фильтрованные индексы кардинально улучшают производительность запросов (часть 1)

В первой статье мы проанализировали, как фильтрованные индексы могут кардинально сократить логические чтения и оптимизировать планы выполнения.

Сегодня мы углубляемся и сравниваем фильтрованный индекс с индексированным представлением, используя практические, воспроизводимые SQL-скрипты.

Вы найдёте конкретные сценарии, которые сможете протестировать в своей собственной лаборатории — потому что настройка производительности — это не теория, это экспериментирование.

Понимание ключевых различий

Фильтрованный индекс

• Индексирует только подмножество строк с помощью предложения WHERE
• Не выполняет предварительную агрегацию данных
• Низкие накладные расходы на обслуживание
• Идеален для сценариев с выборочной фильтрацией

Индексированное представление

• Физически материализует результаты запроса
• Может предварительно агрегировать данные
• Требует уникального кластерного индекса
• Более высокие накладные расходы на запись

Сценарий 1 – Агрегация на неравномерно распределённых данных

Давайте смоделируем типичную OLTP-таблицу с неравномерным распределением.

CREATE TABLE dbo.Sales
(
    SaleID INT IDENTITY PRIMARY KEY,
    CustomerID INT,
    SaleDate DATE,
    Status VARCHAR(10),
    Amount MONEY
);

WITH Numbers AS (
    SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.objects a CROSS JOIN sys.objects b
)
INSERT INTO dbo.Sales (CustomerID, SaleDate, Status, Amount)
SELECT 
    ABS(CHECKSUM(NEWID())) % 5000,
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
    CASE WHEN n % 20 = 0 THEN 'Active' ELSE 'Closed' END,
    RAND(CHECKSUM(NEWID())) * 1000
FROM Numbers;

Только 5% строк имеют статус 'Active'.

Оптимизируемый запрос

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM dbo.Sales
WHERE Status = 'Active'
GROUP BY CustomerID;

Этот запрос:

• Фильтрует небольшое подмножество
• Выполняет агрегацию
• Может вызывать хеш-переполнения (hash spills)
• Потребляет tempdb в условиях нагрузки

Вариант 1 – Фильтрованный индекс

CREATE NONCLUSTERED INDEX IX_Sales_Active
ON dbo.Sales (CustomerID)
INCLUDE (Amount)
WHERE Status = 'Active';

Результат:

• Поиск по индексу (Index Seek) на фильтрованных строках
• Агрегация всё ещё вычисляется во время выполнения
• Сокращённые логические чтения

Вариант 2 – Индексированное представление

CREATE VIEW dbo.vw_ActiveSales
WITH SCHEMABINDING
AS
SELECT CustomerID,
       SUM(Amount) AS TotalAmount,
       COUNT_BIG(*) AS RecordCount
FROM dbo.Sales
WHERE Status = 'Active'
GROUP BY CustomerID;
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_ActiveSales
ON dbo.vw_ActiveSales(CustomerID);

Результат:

• Агрегация предварительно вычислена
• Нет затрат на GROUP BY во время выполнения
• Минимальное использование CPU

Сценарий 1 – Результаты производительности

Сценарий	Логические чтения	CPU (мс)	Время выполнения (мс)
Без оптимизации	180 000	1450	2100
Фильтрованный индекс	8 500	220	310
Индексированное представление	1 200	40	60

Сценарий 2 – Рабочая нагрузка только с фильтрацией

Теперь протестируем запрос только с фильтрацией.

SELECT SaleID, CustomerID, Amount
FROM dbo.Sales
WHERE Status = 'Active'
AND SaleDate >= DATEADD(DAY, -30, GETDATE());

Фильтрованный индекс, оптимизированный для фильтрации

CREATE NONCLUSTERED INDEX IX_Sales_Active_Date
ON dbo.Sales (SaleDate)
INCLUDE (SaleID, CustomerID, Amount)
WHERE Status = 'Active';

План выполнения:

• Чистый поиск по индексу
• Нет поиска по ключу (Key Lookup)
• Минимальный ввод-вывод

Альтернатива с индексированным представлением

CREATE VIEW dbo.vw_ActiveSalesDetail
WITH SCHEMABINDING
AS
SELECT SaleID, CustomerID, SaleDate, Amount
FROM dbo.Sales
WHERE Status = 'Active';
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_ActiveSalesDetail
ON dbo.vw_ActiveSalesDetail(SaleID);

Здесь:

• Нет преимущества агрегации
• Более высокое использование памяти для хранения
• Более медленные операции INSERT/UPDATE

Сценарий 2 – Результаты производительности

Сценарий	Логические чтения	CPU (мс)	Время выполнения (мс)	Накладные расходы на DML
Без оптимизации	95 000	600	850	Нет
Фильтрованный индекс	3 100	75	110	Низкие
Индексированное представление	3 500	90	130	Высокие

Матрица принятия решений

Если ваше узкое место...	Лучший выбор
Выборочная фильтрация	Фильтрованный индекс
Интенсивная агрегация	Индексированное представление
Высокая нагрузка на запись	Фильтрованный индекс
Частые отчётные запросы	Индексированное представление

Заключительные мысли

Фильтрованный индекс и индексированное представление — не конкуренты; они решают разные проблемы производительности.

Если вам нужно уменьшить ввод-вывод, вызванный выборочными фильтрами, выбирайте фильтрованный индекс.

Если вам нужно устранить затраты на агрегацию в повторяющихся отчётных запросах, используйте индексированное представление.

Ключ в том, чтобы определить реальное узкое место перед применением решения.

Тестируйте оба подхода в своей лаборатории, анализируйте планы выполнения, измеряйте логические чтения и наблюдайте за влиянием на DML-операции.

Потому что настройка производительности — это не добавление объектов, а добавление правильного объекта.