ProHoster > Blogs > Administrācija > EAV aizstāŔana ar JSONB pakalpojumā PostgreSQL

EAV aizstāŔana ar JSONB pakalpojumā PostgreSQL

TL; DR: JSONB var ievērojami vienkārÅ”ot datu bāzes shēmas izstrādi, nezaudējot vaicājuma veiktspēju.

Ievads

Sniegsim klasisku piemēru vienam no senākajiem relāciju datu bāzes (datu bāzes) lietoÅ”anas gadÄ«jumiem pasaulē: mums ir entÄ«tija, un mums ir jāsaglabā noteikti Ŕīs entÄ«tijas rekvizÄ«ti (atribÅ«ti). Taču ne visiem gadÄ«jumiem var bÅ«t vienāda rekvizÄ«tu kopa, un nākotnē var tikt pievienoti vairāki rekvizÄ«ti.

VienkārŔākais veids, kā atrisināt Å”o problēmu, ir izveidot kolonnu datu bāzes tabulā katrai rekvizÄ«ta vērtÄ«bai un vienkārÅ”i aizpildÄ«t tos, kas nepiecieÅ”ami konkrētai entÄ«tijas instancei. Lieliski! Problēma atrisināta... lÄ«dz jÅ«su tabulā ir miljoniem ierakstu un jums ir jāpievieno jauns ieraksts.

Apsveriet EAV modeli (VienÄ«ba-atribÅ«ts-vērtÄ«ba), tas notiek diezgan bieži. Vienā tabulā ir entÄ«tijas (ieraksti), citā tabulā ir rekvizÄ«tu nosaukumi (atribÅ«ti), un treŔā tabula saista entÄ«tijas ar to atribÅ«tiem un satur Å”o atribÅ«tu vērtÄ«bu paÅ”reizējai entÄ«tijai. Tas dod iespēju dažādiem objektiem izmantot dažādas rekvizÄ«tu kopas, kā arÄ« pievienot rekvizÄ«tus, nemainot datu bāzes struktÅ«ru.

Tomēr es nerakstÄ«tu Å”o ziņu, ja EVA pieejai nebÅ«tu negatÄ«vu pusi. Tātad, piemēram, lai iegÅ«tu vienu vai vairākas entÄ«tijas, kurām katrai ir 1 atribÅ«ts, vaicājumā ir nepiecieÅ”ami 2 savienojumi: pirmais ir savienojums ar atribÅ«tu tabulu, otrais ir savienojums ar vērtÄ«bu tabulu. Ja entÄ«tijai ir 2 atribÅ«ti, tad nepiecieÅ”ami 4 savienojumi! Turklāt visi atribÅ«ti parasti tiek saglabāti kā virknes, kā rezultātā tiek veikta tipa apraide gan rezultātam, gan klauzulai WHERE. Ja jÅ«s rakstāt daudz vaicājumu, tas ir diezgan izŔķērdÄ«gs resursu izmantoÅ”anas ziņā.

Neskatoties uz Å”iem acÄ«mredzamajiem trÅ«kumiem, EAV jau sen ir izmantots, lai atrisinātu Ŕāda veida problēmas. Tie bija neizbēgami trÅ«kumi, un labākas alternatÄ«vas vienkārÅ”i nebija.
Bet tad PostgreSQL parādÄ«jās jauna ā€œtehnoloÄ£ijaā€...

Sākot ar PostgreSQL 9.4, JSONB datu tips tika pievienots JSON bināro datu glabāŔanai. Lai gan JSON glabāŔana Å”ajā formātā parasti aizņem nedaudz vairāk vietas un laika nekā vienkārÅ”a teksta JSON, darbÄ«bu veikÅ”ana ar to ir daudz ātrāka. JSONB atbalsta arÄ« indeksÄ“Å”anu, kas padara vaicājumus vēl ātrākus.

JSONB datu tips ļauj mums aizstāt apgrÅ«tinoÅ”o EAV modeli, pievienojot tikai vienu JSONB kolonnu mÅ«su entÄ«tiju tabulai, ievērojami vienkārÅ”ojot datu bāzes dizainu. Bet daudzi iebilst, ka tam vajadzētu bÅ«t kopā ar produktivitātes samazināŔanos... Tāpēc es arÄ« uzrakstÄ«ju Å”o rakstu.

Testu datu bāzes iestatīŔana

Å im salÄ«dzinājumam es izveidoju datu bāzi, izmantojot jaunu PostgreSQL 9.5 instalāciju ar 80 $ bÅ«vējumu DigitalOcean Ubuntu 14.04. Pēc dažu parametru iestatÄ«Å”anas postgresql.conf es palaidu Å”is skripts, izmantojot psql. Lai sniegtu datus EAV formā, tika izveidotas Ŕādas tabulas:

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
);

Zemāk ir tabula, kurā tiks glabāti tie paŔi dati, bet ar atribūtiem JSONB tipa kolonnā - īpaŔības.

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

Izskatās daudz vienkārŔāk, vai ne? Pēc tam tas tika pievienots entÄ«tiju tabulām (organizācija & entity_jsonb) 10 miljoni ierakstu, un attiecÄ«gi tabula tika aizpildÄ«ta ar tiem paÅ”iem datiem, izmantojot EAV modeli un pieeju ar JSONB kolonnu - entity_jsonb.properties. Tādējādi mēs saņēmām vairākus dažādus datu tipus no visas rekvizÄ«tu kopas. Datu piemēri:

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

Tagad mums ir vienādi dati par abām iespējām. Sāksim salīdzināt implementācijas darbā!

VienkārŔojiet savu dizainu

IepriekÅ” tika teikts, ka datu bāzes dizains tika ievērojami vienkārÅ”ots: viena tabula, rekvizÄ«tu noteikÅ”anai izmantojot JSONB kolonnu, nevis trÄ«s tabulas EAV. Bet kā tas tiek atspoguļots pieprasÄ«jumos? Viena entÄ«tijas Ä«paÅ”uma atjaunināŔana izskatās Ŕādi:

-- 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;

Kā redzat, pēdējais pieprasÄ«jums neŔķiet vienkārŔāks. Lai atjauninātu rekvizÄ«ta vērtÄ«bu JSONB objektā, mums ir jāizmanto funkcija jsonb_set(), un tai vajadzētu nodot mÅ«su jauno vērtÄ«bu kā JSONB objektu. Tomēr mums nav iepriekÅ” jāzina neviens identifikators. AplÅ«kojot EAV piemēru, mums ir jāzina gan entity_id, gan entity_attribute_id, lai veiktu atjaunināŔanu. Ja vēlaties atjaunināt rekvizÄ«tu JSONB kolonnā, pamatojoties uz objekta nosaukumu, tas viss tiek darÄ«ts vienā vienkārŔā rindā.

Tagad atlasīsim entītiju, kuru tikko atjauninājām, pamatojoties uz tās jauno krāsu:

-- 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';

Es domāju, ka varam piekrist, ka otrais ir Ä«sāks (nav pievienoties!), tāpēc lasāmāks. Å eit uzvar JSONB! Mēs izmantojam operatoru JSON ->>, lai iegÅ«tu krāsu kā teksta vērtÄ«bu no JSONB objekta. Ir arÄ« otrs veids, kā sasniegt tādu paÅ”u rezultātu JSONB modelÄ«, izmantojot @> operatoru:

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

Tas ir nedaudz sarežģītāk: mēs pārbaudām, vai JSON objekts tā rekvizītu kolonnā satur objektu, kas atrodas pa labi no operatora @>. Mazāk lasāms, produktīvāks (skatīt zemāk).

PadarÄ«sim JSONB lietoÅ”anu vēl vienkārŔāku, ja jums bÅ«s jāatlasa vairāki rekvizÄ«ti vienlaikus. Å eit patieŔām parādās JSONB pieeja: mēs vienkārÅ”i atlasām rekvizÄ«tus kā papildu kolonnas mÅ«su rezultātu kopā, neizmantojot savienojumus:

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

Izmantojot EAV, jums bÅ«s nepiecieÅ”ami 2 savienojumi katram Ä«paÅ”umam, kuru vēlaties vaicāt. Manuprāt, iepriekÅ” minētie vaicājumi parāda lielu datu bāzes dizaina vienkārÅ”oÅ”anu. Skatiet citus piemērus, kā rakstÄ«t JSONB vaicājumus, arÄ« iekŔā Å”is pastu.
Tagad ir pienācis laiks runāt par sniegumu.

ŠŸŃ€Š¾ŠøŠ·Š²Š¾Š“ŠøтŠµŠ»ŃŒŠ½Š¾ŃŃ‚ŃŒ

Lai salÄ«dzinātu veiktspēju, es izmantoju IZSKAIDROT ANALIZĒT vaicājumos, lai aprēķinātu izpildes laiku. Katrs vaicājums tika izpildÄ«ts vismaz trÄ«s reizes, jo vaicājumu plānotājam pirmajā reizē ir nepiecieÅ”ams ilgāks laiks. Vispirms es izpildÄ«ju vaicājumus bez indeksiem. AcÄ«mredzot tā bija JSONB priekÅ”rocÄ«ba, jo EAV nepiecieÅ”amajos savienojumos nevarēja izmantot indeksus (sveŔās atslēgas lauki netika indeksēti). Pēc tam es izveidoju indeksu 2 ārējo atslēgu kolonnās EAV vērtÄ«bu tabulā, kā arÄ« indeksu Džins JSONB kolonnai.

Datu atjauninājums uzrādÄ«ja Ŕādus rezultātus laika izteiksmē (ms). Ņemiet vērā, ka skala ir logaritmiska:

EAV aizstāŔana ar JSONB pakalpojumā PostgreSQL

Mēs redzam, ka JSONB ir daudz (> 50000-x) ātrāks par EAV, ja neizmantojat indeksus iepriekÅ” minētā iemesla dēļ. Kad mēs indeksējam kolonnas ar primārajām atslēgām, atŔķirÄ«ba gandrÄ«z pazÅ«d, taču JSONB joprojām ir 1,3 reizes ātrāks nekā EAV. Ņemiet vērā, ka indeksam kolonnā JSONB Å”eit nav nekādas ietekmes, jo mēs neizmantojam rekvizÄ«tu kolonnu vērtÄ“Å”anas kritērijos.

Lai atlasÄ«tu datus, pamatojoties uz Ä«paÅ”uma vērtÄ«bu, mēs iegÅ«stam Ŕādus rezultātus (parastā mērogā):

EAV aizstāŔana ar JSONB pakalpojumā PostgreSQL

Varat pamanÄ«t, ka JSONB atkal darbojas ātrāk nekā EAV bez indeksiem, bet, kad EAV ar indeksiem, tas joprojām darbojas ātrāk nekā JSONB. Bet tad es redzēju, ka JSONB vaicājumu laiki bija vienādi, un tas mani pamudināja uz faktu, ka GIN indeksi nedarbojas. AcÄ«mredzot, ja izmantojat GIN indeksu kolonnā ar aizpildÄ«tiem rekvizÄ«tiem, tas stājas spēkā tikai tad, ja tiek izmantots operators include @>. Es to izmantoju jaunā testā, un tam bija milzÄ«ga ietekme uz laiku: tikai 0,153 ms! Tas ir 15000 25000 reižu ātrāk nekā EAV un XNUMX XNUMX reižu ātrāk nekā ->> operators.

Es domāju, ka tas bija pietiekami ātri!

Datu bāzes tabulas lielums

Salīdzināsim tabulu izmērus abām pieejām. Psql mēs varam parādīt visu tabulu un indeksu lielumu, izmantojot komandu dti+

EAV aizstāŔana ar JSONB pakalpojumā PostgreSQL

EAV pieejai tabulu izmēri ir aptuveni 3068 MB un indeksēti lÄ«dz 3427 MB, kopā 6,43 GB. JSONB pieeja izmanto 1817 MB tabulai un 318 MB indeksiem, kas ir 2,08 GB. Izrādās 3 reizes mazāk! Å is fakts mani nedaudz pārsteidza, jo mēs glabājam Ä«paÅ”umu nosaukumus katrā JSONB objektā.

Tomēr skaitļi runā paÅ”i par sevi: EAV mēs uzglabājam 2 veselas ārējās atslēgas katrai atribÅ«ta vērtÄ«bai, kā rezultātā tiek iegÅ«ti 8 baiti papildu datu. Turklāt EAV saglabā visas Ä«paÅ”uma vērtÄ«bas kā tekstu, savukārt JSONB, ja iespējams, iekŔēji izmantos skaitliskās un BÅ«la vērtÄ«bas, tādējādi radot mazāku nospiedumu.

Rezultāti

Kopumā es domāju, ka entÄ«tijas rekvizÄ«tu saglabāŔana JSONB formātā var ievērojami atvieglot datu bāzes projektÄ“Å”anu un uzturÄ“Å”anu. Ja izpildāt daudz vaicājumu, tad, turot visu vienā tabulā ar entÄ«tiju, tas faktiski darbosies efektÄ«vāk. Un tas, ka tas vienkārÅ”o datu mijiedarbÄ«bu, jau ir pluss, bet iegÅ«tā datubāze ir 3 reizes mazāka apjoma ziņā.

Tāpat, pamatojoties uz veiktajām pārbaudēm, varam secināt, ka veiktspējas zudumi ir ļoti niecÄ«gi. Dažos gadÄ«jumos JSONB ir pat ātrāks par EAV, padarot to vēl labāku. Tomēr Å”is etalons, protams, neaptver visus aspektus (piemēram, entÄ«tijas ar ļoti lielu rekvizÄ«tu skaitu, ievērojams esoÅ”o datu rekvizÄ«tu skaita pieaugums...), tādēļ, ja jums ir kādi ieteikumi, kā tos uzlabot , lÅ«dzu, nekautrējieties atstāt komentāros!

Avots: www.habr.com

Pievieno komentāru