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-ի համար պահանջվող միացումները չէին կարող օգտագործել ինդեքսներ (օտար հիմնական դաշտերը չեն ինդեքսավորվել): Դրանից հետո ես ստեղծեցի ինդեքս EAV արժեքի աղյուսակի 2 արտաքին բանալիների սյունակների վրա, ինչպես նաև ինդեքս Inին JSONB սյունակի համար:

Տվյալների թարմացումը ցույց տվեց հետևյալ արդյունքները ժամանակի առումով (ms-ով): Նշենք, որ սանդղակը լոգարիթմական է.

Մենք տեսնում ենք, որ JSONB-ը շատ (> 50000-x) ավելի արագ է, քան 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-ը, ինչը այն էլ ավելի լավ է դարձնում: Այնուամենայնիվ, այս չափանիշը, իհարկե, չի ընդգրկում բոլոր ասպեկտները (օրինակ՝ շատ մեծ թվով հատկություններով կազմակերպություններ, գոյություն ունեցող տվյալների հատկությունների քանակի զգալի աճ,...), այնպես որ, եթե ունեք առաջարկներ դրանք բարելավելու վերաբերյալ: , խնդրում ենք ազատ զգալ թողնել մեկնաբանություններում:

Source: www.habr.com