Многогранная SQL Server 2012. Реляционные СУБД уходят в прошлое

 ФЁДОР САМОРОДОВ, преподаватель учебного центра «Специалист» при МГТУ им. Н.Э. Баумана

Microsoft: колоночная СУБД
Реляционные СУБД уходят в прошлое

В последних версиях SQL Server представляет собой многоцелевую платформу для разработки бизнес-приложений и включает в себя возможности СУБД совершенно  разных типов: реляционной, многомерной, XML, объектной. А с 2012-й версии это еще и колоночная СУБД

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

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

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

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

SELECT Avg (UnitPrice)
FROM   OrdersHistory

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

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

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

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

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

В SQL Server 2012 такой способ хранения реализован с помощью индекса нового типа – columnstore – колоночного индекса. Такой индекс не похож ни на деревья, ни на битовые карты, ни на хеш-таблицы. Он использует описанную выше идеологию колоночного хранения для оптимизации дисковых операций над столбцами.

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

SELECT Avg (UnitPrice)
FROM   OrdersHistory
SELECT Sum (UnitPrice * Quantity)
FROM   OrdersHistory

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

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

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

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

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

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

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

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

Во-первых, эти таблицы очень большие. Чем больше таблица, тем заметнее эффект от ее индексирования. Да и вообще производительность запросов, выполняемых в хранилище, зависит по большей части именно от того, как мы сумеем оптимизировать таблицы фактов. Поэтому здесь колоночные индексы дадут наибольший эффект. При работе с колоночным индексом сервер поднимает с диска содержимое только необходимых для запроса столбцов. Эффект от сокращения количества дисковых операций на очень больших таблицах будет заметен даже невооруженным глазом.

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

И, в-третьих, таблицы в хранилище гораздо чаще читаются, чем обновляются, а процедуры обновления, как правило, предсказуемы и ограничены временным окном. Обновление фактических таблиц, как правило, выглядит как запуск по расписанию сценария, производящего над таблицами фактов только одну операцию – INSERT. Эта особенность как нельзя кстати, так как у колоночных индексов имеется серьезный недостаток – их использование делает таблицу неизменяемой. То есть после того, как вы создали для таблицы такой индекс, вы можете выполнять над ней только SELECT.

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

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

ALTER INDEX MyIndex1 ON MyFactTable DISABLE
INSERT... INSERT... INSERT...
ALTER INDEX MyIndex1 ON MyFactTable REBUILD

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

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

CREATE NONCLUSTERED COLUMNSTORE INDEX MyIndex1
    ON FactInternetSales (
       ProductKey -- ключ,
       OrderDateKey -- ключ,
       CustomerKey -- ключ,
       CurrencyKey -- ключ,
       OrderQuantity -- факт,
       UnitPrice -- факт,
       SalesAmount -- факт,
       TaxAmt -- факт
       )

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

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

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

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

Комментарии могут оставлять только зарегистрированные пользователи