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

Tsql теория > Колоночные индексы
24.04.2013 17:25:37



Статья:

В какой версии SQL Server впервые появились колоночные индексы (Columnstore Indexes)?

Колоночные индексы впервые появились в SQL Server Denali CTP 3 и являются одной из ключевых фич (feature) новой версии SQL Server 2012. Разработаны в рамках проекта Аполлон (Apollo).

Что такое Apollo?

Apollo (Аполлон) - кодовое название одного из проектов в рамках разработки SQL Server 2012. Основная цель проекта - уменьшить TCO (Total cost of ownership) существенным ускорением запросов к хранилищу данных (data warehouse). В рамках этого проекта в SQL Server появились две новых технологии: колоночные индексы (Columnstore Indexes) и векторная обработка запросов, названная пакетной обработкой (batches).

Отличительная особенность колоночных индексов в том, что они основаны на колоночном хранении данных.

Под построчным хранением данных обычно понимается физическое хранение всей строки таблицы в виде одной записи, в которой поля идут последовательно одно за другим, а за последним полем записи в общем случае идет первое следующей записи. Приблизительно так:
[A1, B1, C1], [A2, B2, C2], [A3, B3, C3]…
где A, B и С — это поля (столбцы), а 1,2 и 3 — номер записи (строки).

Колоночное хранение - с точки зрения SQL-клиента данные представлены как обычно в виде таблиц, но физически эти таблицы являются совокупностью колонок, каждая из которых, по сути, представляет собой таблицу из одного поля. При этом физически на диске значения одного поля хранятся последовательно друг за другом — приблизительно так:
[A1, A2, A3], [B1, B2, B3], [C1, C2, C3] и т.д.

Для использования колоночных индексов не нужно использовать какие-то особенные параметры установки или запуска SQL Server. Так же не нужно включать дополнительных настроек ни на уровне сервера ни на уровне базы данных. Всё, что необходимо сделать - это построить колоночный индекс на всех или нескольких полях таблицы. Далее оптимизатор сам принимает решение использовать колоночные индексы, если это действительно необходимо. Пакетная обработка так же автоматически используется оптимизатором, когда это возможно.

Создание  индекса :

create nonclustered COLUMNSTORE index MyColumnStoreIndex on MyTable ( col1, col2, col3 );

 

Кроме того, создать индекс можно и через графический интерфейс SSMS (SQL Server Management Studio):
  1. Необходимо выбрать конкретную таблицу и выбрав папку Indexes, щёлкнуть правой кнопкой мыши
  2. Выбираем в контекстом меню New Index -> Non-Clustered Columnstore Index...
  3. В открывшемся диалоговом окне нажимаем кнопку Add и выбираем поля, которые нам необходимо включить в колоночный индекс
  4. Можно выбрать конкретные поля или включить все, щёлкнув по титульной колонке Name. Нажимаем OK
  5. Включаем, по необходимости, другие настройки (maxdop, filegroup). Нажимаем OK
Должна ли таблица иметь первичный ключ (primary key)?

Нет. Для колоночного индекса нет необходимости в первичном ключе. Кроме того, сам колоночный индекс не может быть использован в качестве первичного (primary key) или внешнего ключа (foreign key).

Сколько столбцов может быть включено в колоночный индекс?

В колоночном индексе не существует понятия ключевых столбцов, так что ограничение числа ключевых столбцов в индексе (16) не применяется к колоночным индексам. Ограничение размера записи индекса в 900 байт также не применяется к колоночным индексам.
Максимальное количество столбцов, которые могут быть включены в колоночный индекс - 1024. Если некоторые столбцы таблицы имеют недопустимый тип для колоночного индекса, то их необходимо исключить.

Какие типы данных разрешены для включения в колоночный индекс?

В колоночный индекс могут быть включены столбцы следующих типов:
  • int
  • bigint
  • smallint
  • tinyint
  • money
  • smallmoney
  • bit
  • float
  • real
  • char(n)
  • varchar(n)
  • nchar(n)
  • nvarchar(n)
  • date
  • datetime
  • datetime2
  • smalldatetime
  • time
  • datetimeoffset c точностью <=2
  • decimal или numeric с точностью <= 18

Какие типы данных не поддерживаются?

Колоночный индекс не поддерживает следующие типы данных:
  • decimal или numeric с точностью > 18
  • datetimeoffset с точностью > 2
  • binary
  • varbinary
  • image
  • text
  • ntext
  • varchar(max)
  • nvarchar(max)
  • cursor
  • timestamp
  • uniqueidentifier
  • sqlvariant
  • xml
  • hierarchyid
  • пространственные типы (geography, geometry)
  • пользовательские CLR-типы

Может ли колоночный индекс быть кластерным?

Нет. Колоночный индекс может быть только некластерным. Создан он может быть, как на кластеризованой таблице, так и на таблице-куче.

Отличается ли время создания колоночного индекса?

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

Поддерживается ли параллелизм при создании колоночного индекса?

Да. При создании колоночного индекса параллелизм может быть задействован. Ограничение накладывается, как числом процессоров, так и явно, настройкой MAXDOP.

Я пытаюсь явно задать распараллеливание при создании колоночного индекса, но индекс создаётся с MAXDOP = 1, почему?

Если размер данных в таблице, для которой вы создаёте колоночный индекс, меньше 1 млн. строк, то распараллеливание не применяется. Кроме того, параллельное создание индекса требует больше памяти, поэтому, если SQL Server не может получить доступ к достаточному объёму памяти, он автоматически уменьшает количество потоков (thread).

Сколько памяти необходимо для создания колоночного индекса?

Объём памяти, необходимый для создания колоночного индекса, зависит от нескольких факторов: числа столбцов, количества строк в таблице, степень параллелизма и характеристики самих данных. Если SQL Server не может получить необходимое количество памяти для создания колоночного индекса, то количество потоков уменьшается автоматически, если памяти не хватает и при MAXDOP = 1, то создание колоночного индекса прерывается ошибкой.

Общая формула для оценки необходимой памяти выглядит следующим образом:

Необходимый объём памяти (MB) = [(4.2 * количество колонок в колоночном индексе) + 68] * Cтепень параллелизма (DOP) + (Количество строковых колонок * 34)

Возможно ли создать колоночный индекс на сжатой таблице?

Да. Базовая таблица может быть создана, как с сжатием PAGE или ROW, так и без использования сжатия. Колоночные индексы при этом использую свой алгоритм сжатия.

Может ли колоночный индекс быть с условием?

Нет. Колоночный индекс должен содержать в себе все строки таблицы.

Можно ли создать колоночный индекс на вычисляемых полях?

Нет. Вычисляемые поля необходимо исключить из колоночного индекса.

Можно ли создать колоночный индекс на разреженных (sparse) столбцах?

Нет. Разреженные столбцы не могут быть часть колоночного индекса.

Можно ли создать колоночный индекс на индексированных представлениях?

Нет. Колоночный индекс не может быть создан на представлении (view).

Может ли колоночный индекс быть уникальным?

Нет. Колоночный индекс не может быть создан с использованием ключевого слова UNIQUE.

Может ли колоночный индекс быть создан с включенными столбцами?

Нет. Колоночный индекс не может быть создан с использованием ключевого слова INCLUDE.

Можно ли создать колоночный индекс с параметром SORT_IN_TEMPDB?

Нет. SQL Server вернёт ошибку:

Msg 35317, Level 15, State 1
CREATE INDEX statement failed because specifying SORT_IN_TEMPDB is not allowed when creating a columnstore index. Consider creating a columnstore index without specifying SORT_IN_TEMPDB.


Можно ли создать колоночный индекс в режиме ONLINE?

Нет. SQL Server вернёт ошибку:

Msg 35318, Level 15, State 1
CREATE INDEX statement failed because the ONLINE option is not allowed when creating a columnstore index. Create the columnstore index without specifying the ONLINE option.


Как можно задать сортировку для колоночного индекса?

В колоночном индексе при создании нельзя задавать сортировку для столбцов ( ASC или DESC). При создании колоночный индекс использует свою сортировку в соответствии с алгоритмом сжатия.

Какое количество колоночных индексов может быть создано на одной таблице?

На одной таблице может быть создан только один колоночный индекс.