в чем преимущества колоночных баз данных над реляционными
Преимущества аналитической СУБД колоночного типа
Чтобы добиться успеха в бизнес-аналитике, вам нужно быстро обрабатывать данные. Однако с огромным объемом информации, которая сейчас хранится в частных и государственных организациях, быстрая работа становится довольно сложно. К счастью, новые цифровые достижения решают эту проблему. Сегодня все больше компаний предпочитают работать с СУБД именно колоночного типа. Как раз нему относится и ClickHouse от компании Yandex. О том, как установить ClickHouse, вы еще узнаете. А мы поговорим о том, почему представители бизнеса все чаще отдают предпочтение именно таким СУБД.
Эффективное использование аппаратных возможностей
Колоночные СУБД могут сортировать и агрегировать данные быстрее, чем реляционные системы. Это достигается за счет аппаратных улучшений. Реляционные системы создавались еще тогда, когда возможности компьютерного оборудования были довольно ограниченными. Поэтому они работают быстро (до определенного предела) и без аппаратного ускорения.
Но с последними достижениями в области компьютерной техники, такими как многоядерные процессоры и большие объемы оперативной памяти, колоночные системы вырываются вперед. Они могут использовать эти обновления для повышения производительности.
Скорость обработки информации
Колоночные СУБД хранят данные в порядке сортировки по колонкам, в отличие от построчного порядка в стандартной реляционной базе данных. При этом в каждой колонке хранится только один тип данных, что позволяет сжимать их. Это позволяет использовать возможности кэша процессора для повышения производительности.
Пользователи получают более быстрые вычисления, потому что нужно получить доступ только к соответствующим столбцам, а не ко всей строке одновременно. Возрастает скорость обработки запросов в СУБД, содержащей даже миллионы записей.
Простота анализа
Только подумайте – миллионы записей. В последнее время значительно увеличилось количество и разнообразие информации в разных форматах, сохраняемой с разных платформ. Хотя реляционные базы данных позволяют легко добавлять или удалять строки, колоночные СУБД обеспечивают более удобный анализ информации в целом.
Бизнес-аналитика должна идти в ногу со временем. А колоночные СУБД вполне позволяют сделать это. Предприятия получают более эффективный анализ информации, быстрые результаты ее обработки. Поэтому такие инструменты вполне можно считать будущим управления данными.
Колоночные СУБД против строчных, как насчет компромисса?
«Во всём виден прогресс.… не надо бояться, что тебя вызовут в канцелярию и скажут: ”Мы тут посовещались, и завтра вы будете четвертованы или сожжены по вашему собственному выбору.“ Это был бы тяжелый выбор. Я думаю, многих из нас он бы поставил в тупик.»
Ярослав Гашек. Похождения бравого солдата Швейка.
Предыстория
Сколько существуют базы данных, столько и длится это идеологическое противостояние. Автор из любопытства нашел в закромах книгу Дж.Мартина из IBM [1] 1975 года и тут же наткнулся в ней на слова (стр.183): “В работах […] используются бинарные отношения, т.е. отношения только двух доменов. Известно, что бинарные отношения придают наибольшую гибкость базе. Однако в коммерческих задачах удобными являются отношения различных степеней.” Под отношениями здесь понимаются именно реляционные отношения. А упомянутые работы датированы 1967. 1970 гг.
Пусть Sybase IQ была первой промышленно используемой колоночной СУБД, но по крайней мере на уровне идей, всё проговаривалось за 25 лет до неё.
На данный момент являются поколоночными или в той или иной мере поддерживают эту возможность следующие СУБД (взято в основном здесь):
Различия
Реляционное отношение есть набор кортежей, в сущности двумерная таблица. Соответственно имеются две возможности хранения — построчная или поколоночная. Разделение это немного искусственное, логическое. Разработчики баз данных давно уже не занимаются планированием записей по барабанам и дорожкам. Оптимально разложить данные СУБД по файловой системе(мам) — задача администраторов СУБД, а как как файловая система располагает данные на физических дисках известно в основном разработчикам файловых систем.
Было бы логично предоставить СУБД самой решать в каком порядке хранить данные. Здесь мы говорим о некоторой гипотетической СУБД, которая поддерживает оба варианта организации хранения данных и имеет возможность назначить таблице любой из них. Мы не рассматриваем вполне популярный вариант поддерживать две БД — одну для работы, вторую для аналитики/отчетов. Также как и колоночные индексы a la Microsoft SQL Server. Не потому что это плохо, а для проверки гипотезы что существует какой-то более изящный способ.
К сожалению, никакая самая гипотетическая СУБД не сможет выбрать как лучше хранить данные. Т.к. не обладает пониманием того, как мы эти данные собираемся использовать. А без этого выбор сделать невозможно, хотя он очень важен.
Самым ценным качеством СУБД является способность быстро обрабатывать данные (и требования ACID, само собой). Скорость работы СУБД в основном определяется числом дисковых операций. Отсюда возникают два крайних случая:
Впрочем, учитывая вышесказанное, и проектировщик БД окажется в ситуации очень тяжелого выбора. Многих из нас он бы поставил в тупик.
А что если
В сущности и поколоночный и строчный варианты — крайние случаи одной идеи — нарезать таблицу на “ленточки“ и внутри каждой ленты хранить данные построчно. Просто в одном случае лента одна, в другом ленты вырождаются до одной колонки.
Так почему бы не допустить и промежуточные варианты — если данные некоторых колонок приходят/читаются вместе, пусть и окажутся на одной ленте. А если в ленте не оказалось данных (NULL-ы), то и хранить ничего не надо. Заодно снимается проблема максимального размера строки — можно расщепить таблицу, когда есть риск, что строка не поместится на одной странице.
Идея эта не так чтобы особо оригинальная, автору доводилось и видеть подобное и самому применять. Элемент новизны в том, чтобы дать возможность проектировщику БД возможность самому определять как именно его таблица будет разбита на части и в каком виде данные попадут на диск.
Мы для себя это сделали следующим образом:
Для примера взята основная таблица атласа 2MASS, легенда здесь и здесь.
J, H, K — инфракрасные под-диапазоны, данные по ним есть смысл хранить вместе, поскольку в исследованиях они обрабатываются вместе. Вот, например:
Первая попавшаяся картинка.
Или вот, даже красивее:
Самое время подтвердить, что это имеет какой-то практический смысл.
Результаты
Операционные vs аналитические базы: колоночное vs построчное хранение данных
Базы данных можно реализовать с помощью Excel, GSheet или при помощи больших ORM систем. В своей практике бизнес-аналитика я сталкивался с разными решениями. А поскольку в бизнес-анализ я пришёл из финансов и аудита, то каждый раз встречая новую систему задавался вопросами — чем все они отличаются друг от друга и какие задачи решают? Некоторые ответы нашёл. В этой статье будет рассмотрено два основных назначения баз данных:
1 — учёт операций,
2 — анализ данных
Первый тип задач решают OLTP системы: от On Line Transaction Processing. Второй тип решают OLAP системы: от On Line Analytical Processing
Модель хранения данных в OLTP можно сравнить с записями в телефонной книге. Строка в таблице представлена в виде индекса и соответствующих этому индексу данных: (indexN, data). Поэтому такую таблицу нельзя называть таблицей. Это скорее обычная книга, с пронумерованными строками. Если в книгу нужно записать новую операцию — добавляем строку, присваиваем индекс и закрываем книгу. Из книги торчат ярлыки по которым можно быстро O(log n), находить нужную строку и делать CRUD.
Для целей учёта операций это дружелюбное отображение. Но оно недружелюбно для анализа данных, в котором нам важны не строки сами по себе, но вычисления на основе содержимого этих строк. И если делать аналитический запрос на основе содержимого строк, т.е. по не индексированным полям, то такие запросы будут работать медленнее.
Индексировать все записи, как известно, не вариант. Хоть книга становится похожей на таблицу, поскольку атрибуты становятся доступны для быстрого поиска, но при этом существенно замедляется создание новых и обновление имеющихся строк. Поскольку эти операции будут требовать пересортировки всего массива.
Компромисс между OLAP и OLTP
В решениях 1С компромисс реализован следующим образом. События при записи в базу пишутся сразу в несколько мест. В одном месте записи имеют мало индексов и оптимизированы под OLTP нагрузки, в другом месте записи индексируются по всем полям и адаптированы для OLAP нагрузок. Такие таблицы называются регистрами накоплений и регистрами сведений. Поскольку запись в несколько мест кратно увеличивает занимаемое пространство, то для экономии в регистры попадают не все атрибуты транзакции, а только те, которые считаются важными для данного раздела аналитического учёта. Подобный компромисс называется ROLAP моделью, т.е. реляционно-аналитическим отображением.
В SAP, немецком аналоге 1С пошли дальше. Реляционную OLTP модель в этом ПО можно реплицировать в OLAP модель. В SAP HANA реализована колоночная структура хранения. Это значит, что «таблицы» хранятся там не в виде набора строк, а в виде набора колонок.
Аналогичная схема хранения реализована в таких решениях как Google Bigquery, Microsoft SSAS Tabular, Amazon Redshift, Yandex ClickHouse.
Отличие колоночного хранения от построчного
Если в построчной структуре данные хранятся в виде «горизонтальных» кортежей, каждый из которых является транзакцией:
То в колоночной такие данные хранятся «вертикально»:
Повторы можно оптимизировать, условно так:
Если же есть колонка для которой такая оптимизация не сократит изначальный объём, то данные хранятся в первоначальном виде.
Движок колоночной таблицы сам выбирает последовательность сортировки колонок, но если вы знаете свои данные и отсортируете их вручную, то часто это увеличивает компрессию и облегчает аналитические нагрузки. У меня сжатие отдельных таблиц превышало 300 раз. На практике такая структура хранения данных:
Специфика выражений подробно описана:
здесь — для Google BigQuery.
здесь — для Microsoft DAX.
BI как инфраструктура колоночных баз
BI это решения обслуживающие аналитические нагрузки. И они делают жизнь намного проще если выстроены поверх колоночных баз данных. Это может быть самодельная связка ClickHouse-Grafana-Python или связка стека Google: Bigquery-Data Studio-Dataprep-Dataflow или же монолитный Power BI.
Многомерные кубы являются другой OLAP альтернативой колоночной схемы хранения. Но для меня выражения MDX, если сравнивать их с SQL в BQ или языком DAX — избыточны и сложны.
Колоночные базы данных
С выполнением SQL запросов на больших объемах данных стандартные СУБД справляются все хуже, т.к. объемы данных все растут и растут. На таблицах с парой десятков тысяч записей уже нужно создавать индексы, чтобы получить приемлемую производительность. Не говоря уже о том, что добавить или удалить колонку в большую таблицу практически невозможно и это требует специальных техник.
Колоночные базы данных адресуют две проблемы – скорость сложных запросов на больших объемах и изменение структуры таблиц с данными.
Чтобы понять принцип работы колоночных баз, следует посмотреть на схему хранения данных в обычной СУБД (а они все строковые).
Устройство обычной базы
Допустим мы работаем с таблицей такой структуры:
Системы баз данных хранят все данные в строках. Это значит, что где-то внутри СУБД на диске хранятся три строки с данным:
Что происходит, когда мы выполняем запрос такого вида:
База данных выполнит такую последовательность:
Этот набор будет выполнен для каждой строки:
СУБД вынуждена будет проверить значение нужной колонки для каждой строки. Понятно, что на больших таблицах это будет работать крайне медленно.
Устройство колоночной базы
Колоночные базы так и называются потому, что хранят данные не в строках а в колонках. Каждая колонка – это как бы отдельная таблица из одной колонки, которая хранит только свои значения. Значит у нас будет 4 колонки:
Чтобы выполнить запрос из примера, колоночная база данных должна проверить только значения в одной колонке:
Кроме этого все данные в колоночной базе данных обычно хранятся в отсортированном виде (каждая колонка отдельно). Т.е. на самом деле наши данные будет храниться так:
И, чтобы база сама знала, где какое значение, каждая запись знает свой номер. Например, вторая запись будет храниться так:
Это позволяет эффективно выполнять запросы при участии нескольких колонок:
В этом случае СУБД узнает номера колонок, которые подходят для условий type = 5, style = 10 и color = 543. После чего выберет номера записей, которые были найдены в каждой колонке. Это особенно эффективно работают на колонках с высокой селективностью.
В отличие от строчной СУБД в примере, колоночная выполнит всего три операции поиска по отсортированным данным для получения результата.
Структура хранения данных напоминает индексы в обычных базах данных. Однако между колонками нет физической связи (отдельные файлы на диске). Это позволяет значительно увеличить эффективность работы в случае чтения с диска.
Обновление и удаление данных
В отличие от строчной базы данных, обновление и удаление данных в колоночной более дорогая операция. Например, необходимо обновить значение в одной колонке:
Для этого необходимо сначала выбрать номер записей из колонки style, а затем найти их в колонке type и обновить. Строчная база данных сделает обновление за одну операцию.
Добавление и удаление колонок
Поскольку колонки – это просто отдельные файлы, добавление и удаление колонок ничего не стоит. Это просто создание и удаление файлов на диске.
В случае же строчной базы данных, новая колонка приводит к обновлению данных в каждой строке таблицы.
Сжатие
Поскольку каждая колонка – это отдельный файл, каждый файл хранит всегда данные только одного типа. В отличие от строчных, где каждая строка имеет совокупность разных типов. Известность типа данных позволяет использовать эффективное сжатие. Если текст – будем жать gzip’ом, если числа – не будем и т.п.
Когда использовать
Адресуемые проблемы колоночных баз данных очень тесно связаны с аналитическими и Big Data системами. Соответственно применять колоночные базы лучше всего на таких таблицах:
Однако обычные строчные базы данных остаются лучшим решением для продуктовых задач:
Представители
Из хороших решений на рынке следует обратить внимание на Vertica. Имеет бесплатную Community версию, поддерживает кластеры/failover из пакета и набор аналитических функций для обработки данных.
Самое важное
Колоночные базы данных позволяют эффективно делать сложные выборки на больших таблицах. Изменение структуры больших таблиц происходит мгновенно, а сжатие данных позволяет сэкономить кучу места. Однако не следует использовать колоночные базы для случаев с обычными выборками по ключу и известными структурами запросов. Для этого лучше подойдут обычные (строчные) СУБД.
Что такое индексы в Mysql и как их использовать для оптимизации запросов
Как исправить ошибку доступа к базе 1045 Access denied for user
Как перезапустить nginx после обновления конфигурации
Основные понятия о шардинге и репликации
Настройка Master-Master репликации на MySQL за 6 шагов
Примеры ad-hoc запросов и технологии для их исполнения
Включение и использование log-файлов для проверки работы Nginx
Пример управления фоновыми процессами в supervisor’e
Анализ медленных PHP скриптов с помощью XHprof
Как создать и использовать составной индекс в Mysql
Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit
Уменьшение размера картинок при сохранении качества
Как и зачем используется заголовок Cache-control
Рекомендации по настройке Redis для оптимизации ресурсов и повышения стабильности на производственном сервере
Создание и использование полнотекстовых индексов в Postgres
SAP HANA. О преимуществах колоночного хранения
В данной статье мы кратко рассмотрим основные преимущества колоночного хранения, реализованного в базе данных HANA.
Реляционные базы данных обычно используют строковый тип хранения. SAP HANA использует как строковый так и колоночный тип хранения информации. При этом, в процессе создания таблицы без явного указания типа, в БД будет создана таблица с типом COLUMN. В SAP HANA эти два типа таблиц имеют большие отличия с точки зрения администратора базы данных, в то время как для разработчика эти различия не всегда очевидны.
Колоночно-ориентированные базы данных больше, чем традиционные, ориентированные на строковое хранение данных, подходят для аналитических задач, в таких областях как большие хранилища данных, поддержка принятия решений, предиктивная аналитика и т. д.
Память компьютера организована в виде линейной последовательности. Классические row-store таблицы хранятся в виде последовательности записей, содержащих поля одной строки. При колоночном хранении записи, колонки хранятся в непрерывных ячейках памяти. На рисунке ниже показана разница хранения в памяти между строковой и колоночной таблицами.
Представление хранения данных в памяти (Column vs Row)
Основная разница в типах хранения заключается в операциях чтения, которые при колоночном типе хранения более эффективны чем при строковом типе.
Помимо различий при операциях чтения, колоночные таблицы имеют ряд преимуществ:
Высокий коэффициент сжатия данных
Высокая производительность при операциях чтения
Исключение дополнительных индексов
Ниже мы детальнее рассмотрим некоторые особенности колоночного хранения, позволяющие увеличить производительность в операциях чтения.
Компрессия
Данные, хранящиеся в колонках более сжаты по отношению к строковому хранению. Объясняется это тем, что алгоритм сжатия лучше работает с данными с низкой энтропией. Высокая степень сжатия оказывает позитивное влияние на работу базы данных, так как сокращается объем данных, которые необходимо передать от оперативной памяти RAM к CPU.
В данном случае речь пойдет о самом распространённом типе сжатия в SAP HANA (он же тип сжатия по умолчанию) – Dictionary (сжатие на основе словаря). Это единственный тип сжатия, работающий как в Main, так и в Delta store. Техника сжатия, основана на кодировке словаря, где содержимое поля хранится в виде закодированных целых чисел в векторе атрибутов. В данном контексте это означает «перевод» содержимого поля в целочисленное значение.
Тип сжатия для интересующей вас таблицы (поля таблицы) можно посмотреть в представлении M_CS_COLUMNS поле COMPRESSION_TYPE.
Внимание! Тип сжатия определяется на уровне поля. Таким образом одна таблица по разным полям может иметь разные типы сжатия.
Для хранения содержимого поля SAP HANA создает по крайней мере две структуры данных: вектор словаря и вектор атрибута. Вектор словаря хранит каждое значение поля только один раз. Предположим у нас есть следующая таблица:
В таком случае словари (Dictionary) полей «Last Name» и «Location» будут выглядеть следующим образом:
Аттрибуты словаря (поля Last Name, Location)
При этом, только данные, закрашенные тёмным цветом, будут непосредственно храниться в памяти. Как мы видим повторяющиеся значения хранятся только один раз.
Вектор атрибута хранит только значения типа integer, которые указывают на позицию в словаре. Соответствующие векторы атрибутов будут выглядеть так:
Вектор атрибута Last Name Вектор атрибута Location
Поддержка параллельных операций
В строковом хранении, при обработке нескольких строк, сперва идет чтение каждой строки, а затем выгрузка необходимых атрибутов и представление в интерфейсе. При колоночном хранении операции выполняются параллельно, используя несколько процессорных ядер. Данные, хранящиеся в колонках уже вертикально секционированы, таким образом операции над колонками могут быть выполнены параллельно.
В колоночном хранении операции по колонкам, такие как поиск или агрегация, могут быть выполнены в циклах по массиву, хранящемуся в смежных ячейках. Подобные операции могут эффективно выполняться в кэше CPU. Операция с данными в виде массива не только минимизирует накладные расходы, но также раскрывает потенциал для паралеллизации на уровне CPU. Дополнительно, операции на одной колонкой, могут быть выполнены параллельно в случае, если эта колонка разделена (секционирована). В этом случае операции над колонкой могут быть выполнены параллельно сразу несколькими ядрами CPU. На рисунке ниже представлены возможности для распараллеливания операций над колонками в таблице с колоночным хранением в SAP HANA.
Возможности для выполнения параллельных операций при колоночном хранении
Поздняя материализация
Материализация – это процесс формирования результата, ответа на запрос. При ранней материализации мы сперва получаем поля и формируем из них таблицу(ы). Если таблиц несколько, выполняется операция Join. Таким образом таблицы соединяются по определенному условию. Если речь идет про аналитические задачи, то после этого мы получаем промежуточный результат с большим количеством данных, которые потом по которым потом применяется фильтрация.
При поздней материализации мы сначала фильтруем ненужные данные из одной или нескольких таблиц, а потом соединяем данные в промежуточной таблице, размер которой будет существенно меньше. Поздняя материализация более предпочтительна для задач по аналитике.