Индекс с колоночным хранением в Denali

В грядущей версии SQL Server 11 - Denali, имеется одно нововведение, призванное увеличить в десятки или даже сотни раз производительность некоторых запросов к хранилищам данных.

Речь идет о индексе с колоночным хранением (index columnstore).
Индекс с колоночным хранением появился в недавно вышедшем Denali CTP3.

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

При колоночном хранении, данные по столбцу, входящему в index columnstore, хранятся подряд на странице, причем на странице хранятся только данные столбца, а не всей строки, как в привычном до сих пор формате хранения данных в SQL Server. За счет этого, а так же компрессии данных, резко снижается количество страниц, которые должен прочитать сервер для выполнения запроса.

Синтаксис создания колоночного индекса:

CREATE COLUMNSTORE INDEX [Имя индекса] ON [Имя таблицы] (column1, column2... column N);

Я провел тест, с целью сравнения производительности при использовании традиционных индексов и колоночных.

Итак, есть таблица фактов TestTable (
ID INT,
Date DATETIME,
StoreID INT,
Amount NUMERIC(12,4)
).

В таблицу вставлено 2 млн строк. Есть обычные индексы по полям Date, StoreID плюс индекс с included column Amount. Индексы занимают 164 Мб.

Запрос на выборку данных :
-- Таблица с традиционными индексами
SELECT
t.Date, t.StoreID, SUM(t.Amount)
FROM TestTable t
WHERE t.Date BETWEEN '20110101' AND '20110701'
GROUP BY t.Date, t.StoreID

Характеристики выполнения запроса :

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable'. Scan count 3, logical reads 8872, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2215 ms, elapsed time = 1201 ms.


Имеется таблица TestTableColIdx с точно такими же данными, но с индексом типа columnstore.

Инструкция по созданию индекса данного типа:
CREATE COLUMNSTORE INDEX Col_Index ON TestTableColIdx (Date, StoreID, Amount);

Первое, что бросается в глаза - большая разница в размере индексов. Как я уже писал, набор обычных индексов на таблице TestTable, подходящих для тестового запроса, занимает 164 Мб, а в случае колоночного индекса его размер 18 Мб - эффективность сжатия такихиндексов внушает оптимизм :)
Размер индексов я получил при помощи встроенного отчета Disk Usage By Partition.

Выполняем запрос на другой тестовой таблице TestTableColIdx -

-- Таблица с колоночным индексом
SELECT
t.Date, t.StoreID, SUM(t.Amount)
FROM TestTableColIdx t
WHERE t.Date BETWEEN '20110101' AND '20110701'
GROUP BY t.Date, t.StoreID

Характеристики выполнения запроса по таблице TestTableColIdx с колоночным индексом :

Table 'TestTableColIdx'. Scan count 2, logical reads 328, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 55 ms.


Из информации о статистике выполнения видно, что запрос по тестовой таблице с поколоночным индексом выполняется быстрее более чем в 20 раз, а количество чтений станиц из буфера меньше в 27 раз.

Однако, у индексов данного типа имеется и ряд ограничений и недостатков.

Отмечу, на мой взгляд, наиболее важные.

1. Таблица с колоночным индексом не может быть обновлена операторами INSERT, UPDATE, DELETE, MERGE и утилитой массовой загрузки bcp. В документации по Denali CTP3 описаны пути решения этой проблемы (например, удаление индекса, обновление данных, затем создание индекса).
2. Колоночный индекс не может быть кластерным или уникальным.
3. Не может быть построен по столбцам определенных типов.
4. Не может использоваться при репликации и отслеживании изменений в данных - Change Tracking и Change Data Capture.

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

В некоторых случаях использование традиционных B-tree индексов может оказаться предпочтительнее, даже при наличии колоночного. Оптимизатор может неверно строить наилучший план запроса и в этом случае, если использование колоночного индекса нежелательно, можно использовать хинт IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX.

Дополнительная информация :
BOL