Частным лицам: +7 (495) 232-32-16
Корп. клиентам: +7 (495) 780-48-44

Не нашли нужный Вам курс?

Закажите его в индивидуальном формате или Напишите, и мы его сделаем!

Подписаться на рассылку




Я принимаю условия Политики конфиденциальности и даю согласие на обработку персональных данных в соответствии с законодательством России

Спасибо! Вам на e-mail отправлено письмо со ссылкой для подтверждения

Если письмо не пришло, поищите его в папке со спамом или повторите подписку

Вы подписались на рассылку

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

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

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

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

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

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

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

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

sql server 2012

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

SELECT Avg (UnitPrice)
FROM OrdersHistory

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

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

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

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

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

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

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

SELECT    Avg (UnitPrice)
FROM      OrdersHistory

SELECT    Sum (UnitPrice * Quantity)
FROM      OrdersHistory

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

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

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

MS SQL 2012

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

  • Логических чтений: 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          -- факт
                          )

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

Обучение SQL MCSE

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

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

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

Microsoft SQL Server 2012

Сейчас модно критиковать реляционные СУБД и скептически оценивать их применимость к новым вызовам XXI века. Но не стоит забывать, что реляционных СУБД вообще-то уже давно нет, как нет в чистом виде RISC или CISC процессоров. Есть многогранный , одна грань которого действительно реляционная, а остальные… 


rel="nofollow" href="http://samag.ru/archive/more/121" target="_blank"http://samag.ru/archive/more/121



21.05.2012

logo_wm.gif cyberplat1.gif logo_ramblertop.gif Яндекс.Метрика LiveInternet