Колоночные индексы.

В MS SQL 2012 появился новый вид индексов - колоночные индексы. Эти индексы помогают лучше оптимизировать запросы некоторых видов к таблицам с большим числом строк. Колоночные индексы помогают оптимизировать запросы, в которых используется группировка и агрегирующие функции. А также запросы к таблицам с большим числом столбцов, в случае, когда в запросе используется значительно меньший набор столбцов. Данный вид индексов полезен при разработке хранилищ данных, в которых есть громадные таблицы, используется схема данных типа звезда.

Почему же колоночные индексы так называются? Данные в этих индесах хранятся не построчно, а векторно. Представим, что у нас есть таблица со столбцами t1, t2, t2, со строками Row1, Row2, Row3, Row4. В обычном индексе эти столбцы будут храниться так:

[ Row1 (t1, t2, t3) ],  [ Row2 (t1, t2, t3) ],  [ Row3 (t1, t2, t3) ],  [ Row4 (t1, t2, t3) ]

Это классический пример построчного хранения данных. В частности, если в запросе требуется вернуть столбцы t1, t3, то столбец t2 все равно приходится читать. Он между t1 и t3.

В случае, если столбцы t1, t2, t3 входят в колоночный индекс, то хранение данных будет таким:

[(Row1, t1), (Row2, t1), (Row3, t1), (Row4, t1)], [(Row1, t2), (Row2, t2), (Row3, t2), (Row4, t2)], [(Row1, t3), (Row2, t3), (Row3, t3), (Row4, t3)]

Понятно, что если требуется читать не все столбцы таблицы, а только часть из них, то при работе с колоночным индексом не требуется делать лишних операций чтения. В результате на больших объемах скорость многих запросов может вырастать на порядок. Продемонстрируем это на примере. Создадим таблицу:

if object_id ( N'dbo.DataVal', N'U' ) is not null

begin

       ; throw 50000, 'Таблица dbo.DataVal уже существует в базе данных.', 1

       return

end

create table dbo.DataVal

(

       iItemId int identity ( 1, 1 )     not null,

       dtDate date                       not null,

       vcVal1 money                      not null,

       vcVal2 money                      not null,

       vcVal3 money                      not null,

       vcVal4 money                      not null,

       constraint PK_Data_iItemId primary key clustered ( iItemId asc ) on [PRIMARY]

) on [PRIMARY]

Наполним таблицу тестовыми данными:

insert into dbo.DataVal with ( tablock ) ( dtDate, vcVal1, vcVal2, vcVal3, vcVal4 )

       select

             dateadd ( [dd], abs ( binary_checksum ( newid () ) ) / power ( 10, 8 ), '2014-01-01' ),

             abs ( binary_checksum ( newid () ) ) / power ( 10., 5 ),

             abs ( binary_checksum ( newid () ) ) / power ( 10., 5 ),

             abs ( binary_checksum ( newid () ) ) / power ( 10., 5 ),

             abs ( binary_checksum ( newid () ) ) / power ( 10., 5 )

       from

             master.dbo.spt_values tab1

                    cross join

             master.dbo.spt_values tab2

go 2

Изучим производительность такого запроса:

select dtDate, max ( vcVal1 ), max ( vcVal2 )

from dbo.DataVal

group by dtDate

Сперва создадим обычный некластерный индекс:
create nonclustered index IX_dtDate_vcVal1_incl_vcVal2 on dbo.DataVal ( dtDate asc, vcVal1 asc ) include ( vcVal2 ) on [PRIMARY]

Запустим запрос, очистив предварительно буферный пул:

dbcc dropcleanbuffers ()

select dtDate, max ( vcVal1 ), max ( vcVal2 )

from dbo.DataVal with ( index ( IX_dtDate_vcVal1_incl_vcVal2 ) )

group by dtDate

На моем домашнем компьютере запрос отработал за 6 секунд. Теперь создадим колоночный индекс:
create columnstore index IX_clst_dtDate_vcVal on dbo.DataVal ( dtDate, vcVal1, vcVal2 ) on [PRIMARY]

Из схемы хранения данных в колоночном индексе понятно что все столбцы в нем равноправны, поэтому неприменимы такие понятия как сортировка столбца или включенные столбцы. Снова запустим запрос, задав в хинте уже колоночный индекс:

dbcc dropcleanbuffers ()

select dtDate, max ( vcVal1 ), max ( vcVal2 )

from dbo.DataVal with ( index ( IX_clst_dtDate_vcVal ) )

group by dtDate

Время работы составило 2 секунды. Скорость выросла в 3 раза. Посмотрим теперь, что будет, если запускать эти запросы постоянно в ситуации, когда в буферном пуле уже достаточно данных:

set statistics io on

go

select getdate ()

select dtDate, max ( vcVal1 ), max ( vcVal2 )

from dbo.DataVal with ( index ( IX_dtDate_vcVal1_incl_vcVal2 ) )

group by dtDate

select getdate ()

select dtDate, max ( vcVal1 ), max ( vcVal2 )

from dbo.DataVal with ( index ( IX_clst_dtDate_vcVal ) )

group by dtDate

select getdate ()

set statistics io off

go

Первый запрос отработал за 717 миллисекунд, второй - за 47 миллисекунд, то поиск с помощью колоночного индекса теперь работает в 15 раз быстрее! Статистика ввода-вывода показала такие результаты:
Table 'DataVal'. Scan count 5, logical reads 39852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'DataVal'. Scan count 4, logical reads 25442, 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.

Мы видим, что для колоночного делается на одно сканирование меньше, число логических чтений также меньше. Посмотрим на план выполнения запроса с колоночным индексом:

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

В MS SQL 2012 у колоночных индексов есть существенный недостаток: таблицы с такими индексами нельзя обновлять. Индекс приходится удалять, делать обновление, а затем создавать заново колоночный индекс. Это ограничение было преодолено в MS SQL 2014. В новой версии MS SQL Server таблицы с колоночными индексами уже обновляемые. Появились и другие улучшения. В колоночные индексы можно включать столбцы с типами данных, которые ранее не поддерживались. Например, снято ограничение, при котором точность столбца типа decimal в индексе не могла превышать 18. Можно включать в индекс столбец типа uniqueidentifier. Для большего класса запросов доступна пакетная обработка. Колоночные индексы могут быть кластерными и некластерными. Для создания кластерного колоночного индекса приняется такой синтаксис:
create clustered columnstore index IX on dbo.DataVal on [PRIMARY]

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

Для кластерного колоночного индекса автоматически применяется специальный тип сжатия, поэтому такая таблица будет занимать меньше места. При обновлениях кластерного колоночного индекса его структура не поддерживается "на лету", чтобы не замедлять операции insert, update, delete. Вместо этого создаются 2 дополнительные структуры: хранилище дельт для учета операций insert и битовая карта удалений для учета операций delete. А операция update разделяется на insert и delete, которые выполняются одновременно. Операции, читающие индекс, учитывают наличие этих хранилищ. На сервере есть фоновый процесс, который постепенно выправляет структуру колоночного индекса, выполняя слияние хранилища дельт и карты удалений. Это важно, чтобы колоночный индекс не вырастал в размерах и чтобы запросы на извлечение данных не замедлялись. Также для колоночных индексов введен новый тип сжатия, который еще сильнее уменьшает таблицу в размерах. Его можно применять как на уровне всей таблицы, так и на уровне отдельных секий, если таблица секционирована. Сжатие делается так:
alter index IX on TabName rebuild with ( data_compression = columnstore_archive )

Для того, чтобы привести индекс к прежней степени сжатия, надо выполнить такой код:
alter index IX on TabName rebuild with ( data_compression = columnstore )

Такой вид сжатия серьезно замедлит операции чтения и записи. Поэтому применять его нужно для старых данных, которыми редко кто пользуется.