Колоночные базы данных

Игорь Грегорченко

07.05.2020 22:53

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

Колоночные базы данных адресуют две проблемы – скорость сложных запросов на больших объемах и изменение структуры таблиц с данными.

Чтобы понять принцип работы колоночных баз, следует посмотреть на схему хранения данных в обычной СУБД (а они все строковые).

Устройство обычной базы

Допустим мы работаем с таблицей такой структуры:

| type | style | color | method |
---------------------------------
| 1    | 10     | 3421  | 32    |
| 2    | 4      | 543   | 43295 |
| 5    | 6      | 5235  | 82341 |

Системы баз данных хранят все данные в строках. Это значит, что где-то внутри СУБД на диске хранятся три строки с данным:

Что происходит, когда мы выполняем запрос такого вида:

SELECT * FROM table WHERE color = 542

База данных выполнит такую последовательность:

Перейти на первую строку
Найти колонку с именем type и ее значение
Сравнить с требуемым и отобрать строку, если значение подходит
Перейти к следующей строке

Этот набор будет выполнен для каждой строки:

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

Устройство колоночной базы

Колоночные базы так и называются потому, что хранят данные не в строках а в колонках. Каждая колонка – это как бы отдельная таблица из одной колонки, которая хранит только свои значения. Значит у нас будет 4 колонки:

Колонка type:    1, 2, 5
Колонка style:   10, 4, 6
Колонка color:   3421, 543, 5235
Колонка method:  32, 43295, 82341

Чтобы выполнить запрос из примера, колоночная база данных должна проверить только значения в одной колонке:

Колонка color:    3421, 543, 5235

Кроме этого все данные в колоночной базе данных обычно хранятся в отсортированном виде (каждая колонка отдельно). Т.е. на самом деле наши данные будет храниться так:

Колонка type:    1, 2, 5
Колонка style:   4, 6, 10
Колонка color:   543, 3421, 5235
Колонка method:  32, 43295, 82341

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

Колонка type:    1(1), 2(2), 5(3)
Колонка style:   4(2), 6(3), 10(1)
Колонка color:   543(2), 3421(1), 5235(3)
Колонка method:  32(1), 43295(2), 82341(3)

Это позволяет эффективно выполнять запросы при участии нескольких колонок:

SELECT * FROM table WHERE type = 5 AND style = 6 AND color = 5235

В этом случае СУБД узнает номера колонок, которые подходят для условий type = 5, style = 10 и color = 543. После чего выберет номера записей, которые были найдены в каждой колонке. Это особенно эффективно работают на колонках с высокой селективностью.

В отличие от строчной СУБД в примере, колоночная выполнит всего три операции поиска по отсортированным данным для получения результата.

Структура хранения данных напоминает индексы в обычных базах данных. Однако между колонками нет физической связи (отдельные файлы на диске). Это позволяет значительно увеличить эффективность работы в случае чтения с диска.

Обновление и удаление данных

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

UPDATE table SET type = 2 WHERE style = 10

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

Добавление и удаление колонок

Поскольку колонки – это просто отдельные файлы, добавление и удаление колонок ничего не стоит. Это просто создание и удаление файлов на диске.

В случае же строчной базы данных, новая колонка приводит к обновлению данных в каждой строке таблицы.

Сжатие

Поскольку каждая колонка – это отдельный файл, каждый файл хранит всегда данные только одного типа. В отличие от строчных, где каждая строка имеет совокупность разных типов. Известность типа данных позволяет использовать эффективное сжатие. Если текст – будем жать gzip’ом, если числа – не будем и т.п.

Когда использовать

Адресуемые проблемы колоночных баз данных очень тесно связаны с аналитическими и Big Data системами. Соответственно применять колоночные базы лучше всего на таких таблицах:

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

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

Таблицы с доступом по ключу (id) – пользователи, товары, комментарии.
Таблицы с заранее известной структурой запросов. Например, мы знаем, что будем делать выборку из таблицы статей, сортируя их по дате.

Представители

Из хороших решений на рынке следует обратить внимание на Vertica. Имеет бесплатную Community версию, поддерживает кластеры/failover из пакета и набор аналитических функций для обработки данных.

Самое важное

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