Кратко: JSONB може значително да опрости разработването на схема за база данни, без да жертва производителността на запитванията.
Въведение
Нека разгледаме класически пример, вероятно един от най-старите варианти на използване в света на релационните БД (база данни): имаме сутност и необходимо е да запазим определени свойства (атрибути) на тази сутност. Но не всички екземпляри могат да имат един и същ набор от свойства, а в бъдеще е възможно да се добавят нови свойства.
Най-простият начин за решаване на този проблем е да се създаде колона в таблицата на базата данни за всяка стойност на свойството и просто да се попълват тези, които са необходими за конкретен екземпляр на сутността. Чудесно! Проблемът е решен… до момента, в който вашата таблица не съдържа милиони записи и не възникне необходимост от добавяне на нови записи.
Нека разгледаме патерна EAV (), който се среща достатъчно често. Една таблица съдържа сутности (записи), друга таблица съдържа имена на свойства (атрибути), а третата таблица свързва сутностите с техните атрибути и съдържа стойността на тези атрибути за текущата сутност. Това ви дава възможност да имате различни набори от свойства за различни обекти, както и да добавяте свойства „в движение“, без да променяте структурата на базата данни.
Въпреки това, не бих написал тази бележка, ако не бяха недостатъците на подхода с използването на EAV. Например, за получаване на една или повече сутности, които имат по 1 атрибут, са необходими 2 join-а (обединения) в запитването: първият – обединение с таблицата на атрибутите, вторият – обединение с таблицата на стойностите. Ако сутността има 2 атрибута, вече са нужни 4 join-а! Освен това, всички атрибути обикновено се съхраняват като низове, което води до преобразуване на типовете, както за резултата, така и за условието WHERE. Ако пишете много запитвания, това е доста разточително що се отнася до използването на ресурси.
Въпреки тези очевидни недостатъци, EAV отдавна се използва за решаване на този тип проблеми. Това са неизбежните недостатъци и просто нямаше по-добра алтернатива.
Но след това в PostgreSQL се появи нова „технология“…
С версия 9.4 на PostgreSQL был добавлен тип данных JSONB для хранения двоичных данных JSON. Хотя хранение JSON в этом формате обычно занимает немного больше места и времени, чем простой текстовый JSON, операции с ним выполняются гораздо быстрее. Кроме того, JSONB поддерживает индексирование, что делает запросы еще более быстрыми.
Тип данных JSONB позволяет заменить громоздкую модель EAV, добавив всего один столбец JSONB в нашу таблицу сущностей, что значительно упрощает проектирование базы данных. Однако многие утверждают, что это может сопровождаться снижением производительности… Именно по этой причине и была написана эта статья.
Настройка тестовой базы данных
Для этого сравнения я создал базу данных на новой установке PostgreSQL 9.5 на сборке за 80 долларов. Ubuntu 14.04. После настройки некоторых параметров в postgresql.conf я запустил скрипт с помощью psql. Для представления данных в виде модели EAV были созданы следующие таблицы:
CREATE TABLE entity (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT
);
CREATE TABLE entity_attribute (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE entity_attribute_value (
id SERIAL PRIMARY KEY,
entity_id INT REFERENCES entity(id),
entity_attribute_id INT REFERENCES entity_attribute(id),
value TEXT
);
Ниже представлена таблица, в которой будут храниться те же данные, но с атрибутами в столбце типа JSONB – properties.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Это выглядит гораздо проще, не правда ли? Затем в таблицы сущностей (entity & entity_jsonb) было добавлено 10 миллионов записей, и соответственно, были заполнены одинаковыми данными таблицы, в которых используется модель EAV и подход с JSONB в столбце – entity_jsonb.properties. Таким образом, мы получили несколько разных типов данных среди всех свойств. Пример данных:
{
id: 1
name: "Entity1"
description: "Тестовая сущность №1"
properties: {
color: "красный"
length: 120
width: 3.1882420
hassomething: true
country: "Бельгия"
}
}Теперь у нас есть идентичные данные для двух вариантов. Давайте начнем сравнивать реализацию на практике!
Упрощение дизайна
Ранее уже упоминалось, что дизайн базы данных был значительно упрощен: одна таблица за счет использования столбца JSONB для свойств вместо трех таблиц для EAV. Но как это отражается на запросах? Обновление одного свойства сущности выглядит следующим образом:
-- EAV
UPDATE entity_attribute_value
SET value = 'blue'
WHERE entity_attribute_id = 1
AND entity_id = 120;
-- JSONB
UPDATE entity_jsonb
SET properties = jsonb_set(properties, '{"color"}', '"blue"')
WHERE id = 120;
Както виждаме, последният заявка не изглежда по-опростена. За да актуализираме стойността на свойство в обект JSONB, трябва да използваме функцията , и трябва да предадем нашето ново значение като обект JSONB. Все пак, не е необходимо да знаем някакъв идентификатор предварително. Поглеждайки примера с EAV, трябва да знаем и entity_id, и entity_attribute_id, за да изпълним актуализацията. Ако искате да актуализирате свойство в колоната JSONB на базата на името на обекта, това всичко се прави с една проста команда.
Сега нека изберем онова свойство, което току-що обновихме, на базата на новия му цвят:
-- EAV
SELECT e.name
FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties ->> 'color' = 'blue';
Мисля, че можем да се съгласим, че второто е по-кратко (без join!), и следователно по-четимо. Тук JSONB е победител! Използваме оператора JSON ->>, за да получим цвета като текстова стойност от обекта JSONB. Съществува също и втори начин за постигане на същия резултат в модела JSONB с използването на оператора @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Това е малко по-сложно: проверяваме дали обектът JSON в колоната свойства съдържа обект, който е отдясно на оператора @>. По-малко четимо, но по-продуктивно (вижте по-нататък).
Да опростим използването на JSONB още повече, когато трябва да изберем няколко свойства едновременно. Ето къде наистина подхожда подхода JSONB: просто избираме свойствата като допълнителни колони в нашия резултат без необходимост от обединения:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
С EAV ще имате нужда от 2 обединения за всяко свойство, което искате да запитате. Според мен, посочените по-горе запитвания показват значително опростяване в дизайна на базата данни. По-нататък можете да намерите още примери за това как да пишете запитвания към JSONB в пост.
Сега е моментът да поговорим за производителността.
Производителност
За да сравня производителността, използвах в заявките за изчисляване на времето за изпълнение. Всяка заявка беше изпълнена поне три пъти, тъй като при първото изпълнение на планиращия на заявки му е нужно повече време. Първо изпълних заявките без никакви индекси. Очевидно това беше предимство за JSONB, тъй като обединенията, необходими за EAV, не можеха да използват индекси (полета на външни ключове не бяха индексирани). След това създадох индекс за 2 колони на външни ключове в таблицата с EAV стойности, както и индекс за колоната JSONB.
Актуализациите на данните показаха следните резултати по време (в мс). Обърнете внимание, че мащабът е логаритмичен:
Виждаме, че JSONB е много (> 50000 пъти) по-бърз от EAV, когато не се използват индекси, поради причината, посочена по-горе. Когато индексираме колоните с основни ключове, разликата почти изчезва, но JSONB все още е 1.3 пъти по-бърз от EAV. Обърнете внимание, че индексът в колоната JSONB тук не оказва влияние, тъй като не използваме колоната с свойства в оценките.
За извличане на данни на базата на стойност на свойство получаваме следните резултати (нормален мащаб):
Може да се забележи, че JSONB отново работи по-бързо от EAV без индекси, но когато EAV има индекси, той все пак работи по-бързо от JSONB. Но след това видях, че времето за JSONB заявки беше идентично, което ми даде основание да смятам, че GIN индексът не сработва. Явно, когато използвате GIN индекс за колона със запълнени свойства, той сработва само при използване на оператора за включение @>. Използвах това в нов тест, което оказа огромно влияние върху времето: само 0,153 мс! Това е 15000 пъти по-бързо от EAV и 25000 пъти по-бързо от оператора ->>.
Мисля, че беше достатъчно бързо!
Размер на таблиците в БД
Нека сравним размерите на таблиците при двата подхода. В psql можем да покажем размера на всичките таблици и индекси с помощта на командата dti+
За подхода EAV размерите на таблиците са около 3068 МБ, а индекси – до 3427 МБ, което в общо дава 6,43 ГБ. При използването на подход с JSONB, размерът е 1817 МБ за таблицата и 318 МБ за индексите, което е 2,08 ГБ. Получава се 3 пъти по-малко! Този факт малко ме изненада, тъй като съхраняваме имената на свойствата в всеки обект JSONB.
Но все пак цифрите говорят сами за себе: в EAV съхраним 2 целочислени външни ключа на стойността на атрибута, в резултат на което получаваме 8 байта допълнителни данни. Освен това, в EAV всичките стойности на свойствата се съхраняват под формата на текст, докато JSONB ще използва числови и логически стойности там, където е възможно, в резултат на което се получава по-малък обем.
Резюме
Като цяло, мисля, че съхраняването на свойствата на ентитетите в формат JSONB може значително да опрости проектирането и поддръжката на вашата база данни. Ако извършвате много запитвания, всичко, което се съхранява в една таблица с ентитета, наистина ще работи по-ефективно. И фактът, че това опростява взаимодействието между данните, вече е плюс, но и получената БД е 3 пъти по-малка по обем.
Също така, от проведените тестове може да се заключи, че загубите на производителност са много незначителни. В някои случаи JSONB дори работи по-бързо от EAV, което го прави още по-добър. Въпреки това, този еталонен тест, разбира се, не обхваща всички аспекти (например, ентитети с много голямо количество свойства, значително увеличаване на броя на свойствата на съществуващите данни,…), затова, ако имате предложения как да се подобрят, моля, не се колебайте да ги оставите в коментарите!
Източник: habr.com
