ProHoster > Блог > администрация > Замяна на EAV с JSONB в PostgreSQL

Замяна на EAV с JSONB в PostgreSQL

TL; DR: JSONB може значително да опрости разработването на схема на база данни, без да жертва производителността на заявките.

въведение

Нека дадем класически пример за вероятно един от най-старите случаи на използване в света на релационна база данни (база данни): имаме обект и трябва да запазим определени свойства (атрибути) на този обект. Но не всички екземпляри може да имат еднакъв набор от свойства и в бъдеще може да се добавят още свойства.

Най-лесният начин за решаване на този проблем е да създадете колона в таблицата на базата данни за всяка стойност на свойство и просто да попълните тези, които са необходими за конкретен екземпляр на обект. Страхотен! Проблемът е решен... докато таблицата ви не съдържа милиони записи и трябва да добавите нов запис.

Помислете за EAV модела (Субект-Атрибут-Стойност), се среща доста често. Една таблица съдържа обекти (записи), друга таблица съдържа имена на свойства (атрибути), а трета таблица свързва обекти с техните атрибути и съдържа стойността на тези атрибути за текущия обект. Това ви дава възможност да имате различни набори от свойства за различни обекти, както и да добавяте свойства в движение, без да променяте структурата на базата данни.

Въпреки това, нямаше да пиша този пост, ако нямаше някои недостатъци на подхода EVA. Така например, за да получите един или повече обекти, които имат по 1 атрибут, в заявката са необходими 2 съединения: първото е съединение с таблицата с атрибути, второто е съединение с таблицата със стойности. Ако един обект има 2 атрибута, тогава са необходими 4 обединения! Освен това, всички атрибути обикновено се съхраняват като низове, което води до преобразуване на типа както за резултата, така и за клаузата WHERE. Ако пишете много заявки, тогава това е доста разточително по отношение на използването на ресурси.

Въпреки тези очевидни недостатъци, EAV отдавна се използва за решаване на този тип проблеми. Това бяха неизбежни недостатъци и просто нямаше по-добра алтернатива.
Но тогава в PostgreSQL се появи нова „технология“...

Започвайки с PostgreSQL 9.4, типът данни JSONB беше добавен за съхраняване на JSON двоични данни. Въпреки че съхраняването на JSON в този формат обикновено отнема малко повече място и време от JSON с обикновен текст, извършването на операции върху него е много по-бързо. JSONB също поддържа индексиране, което прави заявките още по-бързи.

Типът данни JSONB ни позволява да заменим тромавия модел EAV, като добавим само една колона JSONB към нашата таблица с обекти, което значително опростява дизайна на базата данни. Но мнозина твърдят, че това трябва да бъде придружено от намаляване на производителността... Ето защо написах тази статия.

Създаване на тестова база данни

За това сравнение създадох базата данни на нова инсталация на PostgreSQL 9.5 на компилацията от $80 DigitalOcean 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 – свойства.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

Изглежда много по-просто, нали? След това беше добавен към таблиците с обекти (единица & entity_jsonb) 10 милиона записа и съответно таблицата беше попълнена със същите данни, използвайки модела EAV и подхода с JSONB колона - entity_jsonb.properties. По този начин получихме няколко различни типа данни сред целия набор от свойства. Примерни данни:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

Така че сега имаме едни и същи данни и за двете опции. Нека започнем да сравняваме реализациите на работа!

Опростете своя дизайн

По-рано беше заявено, че дизайнът на базата данни е значително опростен: една таблица, чрез използване на 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_set()и трябва да предаде нашата нова стойност като 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';

Мисля, че можем да се съгласим, че вторият е по-кратък (без присъединяване!) и следователно по-четлив. 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, както и индекс GIN за JSONB колона.

Актуализацията на данните показа следните резултати по отношение на времето (в ms). Имайте предвид, че скалата е логаритмична:

Замяна на EAV с JSONB в PostgreSQL

Виждаме, че JSONB е много (> 50000-x) по-бърз от EAV, ако не използвате индекси, поради посочената по-горе причина. Когато индексираме колони с първични ключове, разликата почти изчезва, но JSONB все още е 1,3 пъти по-бърз от EAV. Имайте предвид, че индексът на колоната JSONB няма ефект тук, тъй като не използваме колоната със свойства в критериите за оценка.

За избор на данни въз основа на стойността на свойството получаваме следните резултати (нормален мащаб):

Замяна на EAV с JSONB в PostgreSQL

Можете да забележите, че JSONB отново работи по-бързо от EAV без индекси, но когато EAV с индекси, той все още работи по-бързо от JSONB. Но след това видях, че времената за JSONB заявки са еднакви, това ме подтикна към факта, че GIN индексите не работят. Очевидно, когато използвате GIN индекс в колона с попълнени свойства, той влиза в сила само когато използвате оператора за включване @>. Използвах това в нов тест и оказа огромно влияние върху времето: само 0,153 ms! Това е 15000 25000 пъти по-бързо от EAV и XNUMX XNUMX пъти по-бързо от оператора ->>.

Мисля, че беше достатъчно бързо!

Размер на таблицата на базата данни

Нека сравним размерите на таблицата за двата подхода. В psql можем да покажем размера на всички таблици и индекси с помощта на командата dti+

Замяна на EAV с JSONB в PostgreSQL

За подхода EAV размерите на таблиците са около 3068 MB и индекси до 3427 MB за общо 6,43 GB. Подходът JSONB използва 1817 MB за таблицата и 318 MB за индексите, което е 2,08 GB. Оказва се 3 пъти по-малко! Този факт малко ме изненада, защото съхраняваме имена на свойства във всеки JSONB обект.

Но все пак числата говорят сами за себе си: в EAV съхраняваме 2 цели чужди ключа на стойност на атрибут, което води до 8 байта допълнителни данни. Освен това EAV съхранява всички стойности на свойства като текст, докато JSONB ще използва числови и булеви стойности вътрешно, където е възможно, което води до по-малък отпечатък.

Резултати от

Като цяло мисля, че запазването на свойствата на обекта във формат JSONB може да направи проектирането и поддържането на вашата база данни много по-лесно. Ако изпълнявате много заявки, тогава запазването на всичко в същата таблица като обекта всъщност ще работи по-ефективно. И фактът, че това опростява взаимодействието между данните, вече е плюс, но получената база данни е 3 пъти по-малка по обем.

Освен това, въз основа на проведените тестове, можем да заключим, че загубите на производителност са много незначителни. В някои случаи JSONB е дори по-бърз от EAV, което го прави още по-добър. Въпреки това, този бенчмарк, разбира се, не покрива всички аспекти (напр. обекти с много голям брой свойства, значително увеличение на броя свойства на съществуващи данни,...), така че ако имате някакви предложения как да ги подобрите , моля, не се колебайте да оставите в коментарите!

Източник: www.habr.com

Добавяне на нов коментар