Microsoft представляет колоночную СУБД

В процессе своего развития IT-продукты обрастают новыми полезными свойствами и часто меняются до неузнаваемости… В прошлом, когда компьютеры ещё были большими, было принято делить процессоры по архитектуре на RISC и CISC. Однако, когда эти процессоры начали эволюционировать, они стали заимствовать друг у друга полезные качества и через некоторое время уже стало невозможно с уверенностью определить, представителем какой архитектурной традиции является тот или иной экземпляр.

Разные СУБД начинали своё развитие с разных рыночных сегментов и технологических ниш. Какие-то сразу нацелились на обработку огромных объёмов данных, решение задач промышленных масштабов и лишь потом «снизошли» до потребностей мелкого бизнеса и компактных решений. Некоторые наступали на рынок с нижних ценовых сегментов и постепенно приближались к решению масштабных задач. Другие СУБД изначально задумывались, как узкоспециализированные, а потом добавили в свой арсенал набор стандартных для СУБД возможностей.

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

Проблемы построчного хранения

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

И это действительно удобно, быстро и эффективно. Но только до тех пор, пока пользователю действительно нужно что-то сделать именно со строками. Например, добавить в базу несколько новых заказов (строк), поменять список товаров (строк) в каком-то заказе (строке), или удалить заказ (строку) из базы. Такие операции обрабатываются механизмом хранения очень быстро. Сервер при помощи индекса быстро находит нужную строку, считывает эту строку с диска и производит над ней требуемую операцию. Можно в первом приближении представить, что сервер хранит на диске строки именно так, как они отображаются в таблице – одну за другой. И тогда становится понятно, что операция чтения или изменения одного заказа действительно может быть выполнена очень быстро, ведь на уровне механизма хранения требуется обработать несколько ячеек, лежащих последовательно друг за другом.

Но что, если пользователь начинает мыслить (и строить SQL-запросы) не в терминах строк, а используя столбцы. В этом нет ничего необычного, вы сами много раз так поступали, решая какую-нибудь аналитическую задачу. Посмотрите на этот простой запрос:

Требуется найти среднее значение в столбце. Заметили, что пользователь ни в мыслях, ни в запросе не привязался ни к каким строкам? А насколько эффективно будет выполнять такой запрос СУБД? Ведь данные-то на диске уложены построчно! Индексы нам здесь не помогут, потому что придётся перебрать все строки, выделить из каждой строки требуемую ячейку, что приведёт к чтению с диска большого объёма данных, причём лежащих не подряд, а с разбивкой. Накладные расходы при таком режиме чтения, скорее всего, расстроят пользователя. Особенно, учитывая объёмы современных хранилищ данных, которые используются для аналитической обработки.

Колоночный принцип хранения

А что, если хранить данные, укладывая их на диск не по строкам, а по столбцам? То есть, сначала последовательно перечисляем все ID заказов, затем идут все ID товаров, потом последовательно все продавцы и т.д. Мы сразу получим два очевидных преимущества:

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

Ради получения таких преимуществ и придуманы колоночные СУБД.

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

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

• Логических чтений: 541711 и 541501 (при сканировании кластерного индекса серверу пришлось поднять с диска довольно много страниц)
• Процессорное время: 52775 и 80355 (на эти данные обратим внимание для того, чтобы оценить, не перевесят ли расходы на декомпрессию колоночного индекса)

Теперь расширим механизм хранения, создав колоночный индекс.

Те же два запроса выполняются со следующей статистикой:

• Логических чтений: 11578 и 12357 (в 55 раз меньше!)
• Процессорное время: 37363 и 68484 (не больше, чем в предыдущем случае)

Колоночный индекс действительно сработал, дисковые операции удалось существенно оптимизировать!

Планирование и применение колоночных индексов

Разумеется, у колоночного механизма хранения имеются и недостатки. И их вообще-то, если считать количественно, больше, чем достоинств.

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

Скорее всего, колоночный индекс придётся ко двору в больших хранилищах данных, именно для использования в них он и разрабатывался. В хранилищах, как правило, можно выделить таблицы фактов и таблицы размерностей (справочники). И использование этих двух типов таблиц очень сильно различается. Фактические таблицы отличаются от остальных в первую очередь тремя качествами:

• Во-первых, эти таблицы очень большие. Чем больше таблица, тем заметнее эффект от её индексирования. Да и вообще, производительность запросов, выполняемых в хранилище, зависит по большей части именно от того, как мы сумеем оптимизировать таблицы фактов. Поэтому здесь колоночные индексы дадут наибольший эффект. При работе с колоночным индексом сервер поднимает с диска содержимое только необходимых для запроса столбцов. Эффект от сокращения количества дисковых операций на очень больших таблицах будет заметен даже невооружённым глазом.
• Во-вторых, столбцы фактической таблицы можно разделить на две группы – меры и внешние ключи. Меры – это показатели, которые мы собираемся непосредственно использовать для анализа, то есть агрегировать. Это сразу же намекает на использование колоночных индексов. Суммирование, вычисление среднего значения, подсчёт количества и другие агрегации над столбцами таблицы фактов, содержащих десятки и сотни миллионов строк  – это как раз тот случай, где колоночные индексы могут полностью раскрыться!
• И в-третьих, таблицы в хранилище гораздо чаще читаются, чем обновляются, а процедуры обновления, как правило, предсказуемы и ограничены временным окном. Обновление фактических таблиц, как правило, выглядит как запуск по расписанию сценария, производящего над таблицами фактов только одну операцию – INSERT. Эта особенность как нельзя кстати, так как у колоночных индексов имеется серьёзный недостаток – их использование делает таблицу неизменяемой. То есть, после того, как вы создали для таблицы такой индекс, вы можете выполнять над ней только SELECT.

Представить работу с read-only таблицами в обычной операционной базе данных проблематично, другое дело – в хранилище. Процедура загрузки в таблицу фактов немного усложнится, но пользователи не должны этого почувствовать, так как обновление скорее всего выполняется не в час пик.
У вас есть две приемлемых стратегии для пополнения фактической таблицы свежими данными. Первый вариант – просто удалять или отключать колоночный индекс перед обновлением, а затем, сразу после окончания загрузки, сразу же его перестраивать.

Этот вариант хорош своей простотой. А если не желаете ждать перестройки колоночного индекса, то больше подойдёт другой вариант – загрузка во временную таблицу с последующим переключением секций между этой временной таблицей и таблицей фактов. Такая стратегия, как правило, тоже не создаёт администратору хранилища трудностей, так как механизм секционирования скорее всего уже используется для больших фактических таблиц и будет не трудно использовать его ещё и для обновления колоночных индексов. Принцип тут такой же, как и при секционировании обычных индексов.

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

Обратите внимание,  что в индекс включены не только меры (то, что планируем агрегировать), но и ключи (то, по чему планируем фильтровать и группировать). Проверить работу индексов можно на плане выполнения запроса:

Теперь мы видим, что данные поднимаются с диска именно по столбцам – операция «просмотр (scan) индекса columnstore». Ещё раз обратите внимание на включение в колоночный индекс не только тех столбцов, которые используются как аргументы агрегатных функций, но и тех, по которым производится отбор значений для агрегации (GROUP BY).

Строки или столбцы?