Фильтрованный индекс против индексированного представления и индекса с хранением в колонках (Часть 3)

Автор: Luca Biondi, SQL Server, Filtered Index vs Indexed View vs Columnstore Index! Part 3

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

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

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

Сегодня мы добавляем в игру нового игрока.

Потому что, когда объём данных начинает расти… когда миллионы строк превращаются в десятки или сотни миллионов… мы получаем мощного нового союзника:

Колоночные индексы (Columnstore Indexes).

И это меняет всё.

Когда строчного хранения (Rowstore) недостаточно

До сих пор мы работали с традиционными строчными структурами:

• Фильтрованный индекс → уменьшает ввод-вывод для избирательных предикатов
• Индексированное представление → предварительно вычисляет агрегаты

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

Вот тут-то и появляется колоночное хранение.

Что делает колоночное хранение особенным?

Традиционные индексы хранят данные построчно (структура B-дерева).

Колоночный индекс:

• Хранит данные по колонкам
• Применяет сильное сжатие
• Включает пакетный режим выполнения (Batch Mode)
• Обрабатывает данные векторами вместо отдельных строк
• Кардинально улучшает выполнение больших агрегатов

Существует два основных типа:

• Кластерный колоночный индекс (CCI) → заменяет хранение таблицы
• Некластерный колоночный индекс (NCCI) → добавляется поверх строчных таблиц

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

Сценарий 3 – Крупномасштабные агрегаты

Давайте повторно используем нашу таблицу dbo.Sales с 1 миллионом строк. Теперь представим часто выполняемый отчётный запрос:

SELECT 
    YEAR(SaleDate) AS SaleYear,
    MONTH(SaleDate) AS SaleMonth,
    SUM(Amount) AS TotalAmount,
    COUNT(*) AS TotalSales
FROM dbo.Sales
GROUP BY 
    YEAR(SaleDate),
    MONTH(SaleDate);

Этот запрос сканирует всю таблицу, агрегирует всё, потребляет ресурсы CPU в строчном режиме и может запрашивать большие объёмы памяти.

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

Здесь он не даёт никакой выгоды. Нет избирательного фильтра и нет уменьшенного подмножества. Фильтрованные индексы не предназначены для аналитики по всей таблице.

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

CREATE VIEW dbo.vw_MonthlySales
WITH SCHEMABINDING
AS
SELECT 
    YEAR(SaleDate) AS SaleYear,
    MONTH(SaleDate) AS SaleMonth,
    SUM(Amount) AS TotalAmount,
    COUNT_BIG(*) AS TotalSales
FROM dbo.Sales
GROUP BY 
    YEAR(SaleDate),
    MONTH(SaleDate);
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_MonthlySales
ON dbo.vw_MonthlySales (SaleYear, SaleMonth);

Результат:

• Агрегаты предварительно вычислены
• Минимальное использование CPU во время выполнения
• Чрезвычайно быстрый SELECT
• Но с высокими накладными расходами на DML, дополнительным хранилищем и ограничениями привязки к схеме (schema binding).

Вариант 3 – Некластеризованный колоночный индекс

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON dbo.Sales (SaleDate, Amount);

План выполнения переключается на:

• Пакетный режим (Batch Mode)
• Элиминацию колонок
• Векторизованную агрегацию
• Сильно сжатые сегменты

Вместо обработки строки за строкой SQL Server обрабатывает тысячи строк за пакет. Это то, что меняет правила игры.

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

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

Когда данные растут, сложность растёт. Но, к счастью, когда данные растут…

…мы также получаем мощного союзника: колоночное хранение.

И в крупномасштабных системах этот союзник может стать решающим фактором между минутами и миллисекундами.