Ukutshintsha i-EAV nge-JSONB kwi-PostgreSQL

TL; DR: I-JSONB inokwenza lula kakhulu uphuhliso lweschema sedatha ngaphandle kokuncama ukusebenza kombuzo.

Intshayelelo

Makhe sinike umzekelo weklasi mhlawumbi yenye yezona meko zindala zokusetyenziswa kwihlabathi lesiseko sedatha (isiseko sedatha): sinequmrhu, kwaye kufuneka sigcine iimpawu ezithile (iimpawu) zeli qumrhu. Kodwa ayizizo zonke iimeko ezinokuthi zibe neseti yeepropati ezifanayo, kwaye iipropati ezininzi zinokongezwa kwixesha elizayo.

Eyona ndlela ilula yokusombulula le ngxaki kukudala ikholamu kwitafile yedatha yexabiso lepropathi nganye, kwaye ngokulula ugcwalise ezo zifunekayo kumzekelo othile wequmrhu. Kakhulu! Ingxaki isonjululwe... de itafile yakho iqulathe izigidi zeerekhodi kwaye kufuneka wongeze irekhodi elitsha.

Qwalasela ipateni ye-EAV (Iqumrhu-Uphawu-Ixabiso), yenzeka rhoqo. Itheyibhile enye iqulathe amaqumrhu (iirekhodi), enye itheyibhile iqulathe amagama epropathi (iimpawu), kunye netheyibhile yesithathu enxulumanisa amaqumrhu aneempawu zawo kwaye iqulethe ixabiso lezo mpawu zequmrhu langoku. Oku kukunika ukukwazi ukuba neeseti ezahlukeneyo zeepropathi zezinto ezahlukeneyo, kwaye wongeze iimpawu kwi-fly ngaphandle kokutshintsha isakhiwo sedatha.

Nangona kunjalo, ngekhe ndibhale esi sithuba ukuba bekungekho nto iphantsi kwindlela ye-Eva. Ke, umzekelo, ukufumana iqumrhu elinye okanye ngaphezulu anophawu olu-1 lilinye, ukudityaniswa oku-2 kuyafuneka kumbuzo: eyokuqala kukudibanisa netheyibhile yophawu, eyesibini kukudityaniswa netheyibhile yamaxabiso. Ukuba iqumrhu lineempawu ezi-2, kufuneka ama-4 adibanise! Ukongeza, zonke iimpawu zigcinwa njengemitya, nto leyo ekhokelela kuhlobo lokuphosa kuzo zombini iziphumo kunye negatya elithi WHERE. Ukuba ubhala imibuzo emininzi, ke oku kukumosha ngokwemigaqo yokusetyenziswa kobutyebi.

Nangona ezi ntsilelo zicacileyo, i-EAV kudala isetyenziselwa ukusombulula ezi ntlobo zeengxaki. Ezi yayiziintsilelo ezingenakuphepheka, yaye kwakungekho ndlela ilunge ngakumbi.
Kodwa emva koko kwavela "itekhnoloji" entsha kwiPostgreSQL...

Ukuqala ngePostgreSQL 9.4, uhlobo lwedatha ye-JSONB yongezwa ukugcina idatha ye-JSON yokubini. Nangona ukugcina i-JSON kule fomati kuthatha indawo encinci kunye nexesha kunombhalo ongenanto JSON, ukwenza imisebenzi kuyo kukhawuleza kakhulu. I-JSONB ikwaxhasa isalathiso, esenza imibuzo ngokukhawuleza.

Uhlobo lwedatha ye-JSONB lusivumela ukuba sitshintshe ipateni ye-EAV enzima ngokongeza ikholamu enye ye-JSONB kwitheyibhile yethu yequmrhu, ilula kakhulu uyilo lwedatha. Kodwa abaninzi baphikisa ukuba oku kufuneka kuhambisane nokuncipha kwemveliso ... Yingakho ndibhale eli nqaku.

Ukumisela i-database yovavanyo

Kolu thelekiso, ndidale isiseko sedatha kufakelo olutsha lwePostgreSQL 9.5 kwi-80 yeedola. DigitalOcean Ubuntu 14.04 Emva kokumisela ezinye iiparameter kwi-postgresql.conf ndibalekile oku iskripthi usebenzisa i-psql. Ezi theyibhile zilandelayo zenziwe ukubonisa idatha kwifom ye-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
);

Ngezantsi itafile apho idatha efanayo iya kugcinwa khona, kodwa ngeempawu kwikholamu yohlobo lwe-JSONB - iipropati.

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

Ijongeka ilula kakhulu, akunjalo? Emva koko yongezwa kwiitafile zequmrhu (iqabane & into_jsonb) Iirekhodi ze-10 yezigidi, kwaye ngokufanelekileyo, itheyibhile yazaliswa idatha efanayo usebenzisa i-EAV iphethini kunye nendlela kunye nekholamu ye-JSONB - entity_jsonb.properties. Ke, sifumene iindidi ezahlukeneyo zedatha phakathi kwayo yonke isethi yeepropathi. Idatha yesampuli:

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

Ke ngoku sinedatha efanayo yazo zombini iinketho. Masiqale ngokuthelekisa ukuphunyezwa emsebenzini!

Lula uyilo lwakho

Kwaxelwa ngaphambili ukuba i-database yoyilo yenziwe lula kakhulu: itafile enye, ngokusebenzisa ikholomu ye-JSONB yeepropati, endaweni yokusebenzisa iitafile ezintathu ze-EAV. Kodwa oku kubonakala njani kwizicelo? Ukuhlaziya ipropathi yeziko enye kujongeka ngolu hlobo:

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

Njengoko ubona, isicelo sokugqibela asibonakali silula. Ukuhlaziya ixabiso lepropati kwinto ye-JSONB kufuneka sisebenzise umsebenzi jsonb_set(), kwaye kufuneka kudlule ixabiso lethu elitsha njengento ye-JSONB. Nangona kunjalo, akufuneki sazi nasiphi na isichongi kwangaphambili. Ukujonga kumzekelo we-EAV, kufuneka sazi zombini i- entity_id kunye ne entity_attribute_id ukuze senze uhlaziyo. Ukuba ufuna ukuhlaziya ipropathi kwikholamu ye-JSONB esekwe kwigama lenjongo, ngoko konke kwenziwa kumgca omnye olula.

Ngoku makhe sikhethe iziko esisandula ukulihlaziya ngokusekwe kumbala walo omtsha:

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

Ndicinga ukuba sinokuvuma ukuba eyesibini imfutshane (akukho ukujoyina!), Ngoko ke ifundeka ngakumbi. I-JSONB iyaphumelela apha! Sisebenzisa i-JSON ->> umsebenzisi ukufumana umbala njengexabiso lokubhaliweyo ukusuka kwinto ye-JSONB. Kukwakho indlela yesibini yokufumana iziphumo ezifanayo kwimodeli ye-JSONB usebenzisa i-@> umqhubi:

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

Oku kuntsokothile ngakumbi: sijonga ukubona ukuba into ye-JSON kwikholamu yeepropati zayo iqulethe into esekunene kumsebenzisi @>. Ifundeka kancinci, ivelisa ngakumbi (jonga ngezantsi).

Masenze ukusebenzisa i-JSONB kube lula ngakumbi xa ufuna ukukhetha iipropati ezininzi ngaxeshanye. Kulapho indlela ye-JSONB ingena khona ngokwenene: sikhetha ngokulula iipropathi njengeekholamu ezongezelelweyo kwiseti yethu yeziphumo ngaphandle kwesidingo sokujoyina:

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

Nge-EAV uya kufuna ukudibanisa oku-2 kwipropati nganye ofuna ukuyibuza. Ngokombono wam, le mibuzo ingasentla ibonisa ukwenziwa lula kuyilo lwedatha. Bona eminye imizekelo yendlela yokubhala imibuzo ye-JSONB, nayo oku iposti.
Ngoku lixesha lokuthetha malunga nokusebenza.

Imveliso

Ukuthelekisa intsebenzo endiyisebenzisileyo CHAZA HLALUTYA kwimibuzo, ukubala ixesha lokwenziwa. Umbuzo ngamnye uphunyezwe ubuncinci amaxesha amathathu kuba umcwangcisi wombuzo uthatha ixesha elide okokuqala. Kuqala ndiqhube imibuzo ngaphandle kwezalathisi. Ngokucacileyo, le yayiyinzuzo ye-JSONB, kuba udibaniso olufunekayo kwi-EAV alukwazanga ukusebenzisa izalathisi (imimandla yesitshixo yangaphandle ayizange ifakwe indexed). Emva koku ndidale isalathisi kwiikholamu ze-2 zangaphandle ze-EAV zexabiso letafile, kunye nesalathisi Ndingene yoluhlu lwe-JSONB.

Uhlaziyo lwedatha lubonise ezi ziphumo zilandelayo ngokwemiqathango yexesha (kwi-ms). Qaphela ukuba isikali silogarithmic:

Siyabona ukuba i-JSONB ininzi (> 50000-x) ngokukhawuleza kune-EAV ukuba awusebenzisi izalathisi, ngenxa yesizathu esichazwe ngasentla. Xa sisalathisa iikholamu ezinezitshixo eziziintloko, umahluko phantse unyamalale, kodwa i-JSONB isakhawuleza kangangezihlandlo ezili-1,3 kune-EAV. Qaphela ukuba isalathiso kwikholamu ye-JSONB ayinaso isiphumo apha kuba asisebenzisi ikholamu yepropati kwindlela yokuvavanya.

Ukukhetha idatha esekelwe kwixabiso lepropati, sifumana ezi ziphumo zilandelayo (isikali esiqhelekileyo):

Unokuqaphela ukuba i-JSONB iphinda isebenze ngokukhawuleza kune-EAV ngaphandle kwezalathisi, kodwa xa i-EAV inezalathisi, isasebenza ngokukhawuleza kune-JSONB. Kodwa ndiye ndabona ukuba amaxesha emibuzo ye-JSONB ayefana, oku kwandikhokelela kwinto yokuba izalathisi ze-GIN azisebenzi. Kuyabonakala ukuba xa usebenzisa isalathiso se-GIN kwikholamu eneempawu ezinabantu abaninzi, ithatha isiphumo kuphela xa usebenzisa iquka umqhubi @>. Ndisebenzise oku kuvavanyo olutsha kwaye kwaba nefuthe elikhulu ngexesha: kuphela i-0,153ms! Oku kumaxesha e-15000 ngokukhawuleza kune-EAV kunye namaxesha angama-25000 ngokukhawuleza kunomqhubi ->>.

Ndicinga ukuba ikhawuleze ngokwaneleyo!

Ubungakanani betafile yeDatabase

Masithelekise ubungakanani betafile kuzo zombini iindlela. Kwi-psql sinokubonisa ubungakanani bazo zonke iitafile kunye nezalathisi usebenzisa umyalelo dti+

Kwindlela ye-EAV, ubungakanani betafile bujikeleze i-3068 MB kunye nezalathisi ukuya kuthi ga kwi-3427 MB iyonke ye-6,43 GB. Indlela ye-JSONB isebenzisa i-1817 MB yetafile kunye ne-318 MB kwii-indexes, eziyi-2,08 GB. Kuvela amaxesha ama-3 ngaphantsi! Le nyani yandimangalisa kancinci kuba sigcina amagama epropathi kuyo yonke into ye-JSONB.

Kodwa kunjalo, amanani ayazithetha ngokwawo: kwi-EAV sigcina izitshixo ezi-2 ezipheleleyo zangaphandle ngexabiso lophawu, okukhokelela kwi-8 bytes yedatha eyongezelelweyo. Ukongeza, i-EAV igcina onke amaxabiso epropathi njengombhalo, ngelixa i-JSONB izakusebenzisa amanani kunye ne-boolean amaxabiso ngaphakathi apho kunokwenzeka, okukhokelela kunyawo oluncinci.

Iziphumo

Ngokubanzi, ndicinga ukuba ukugcina iipropati zequmrhu kwifomathi ye-JSONB kunokwenza ukuyila nokugcina idatabase yakho kube lula kakhulu. Ukuba ubuza imibuzo emininzi, ke ukugcina yonke into kwitafile enye njengequmrhu kuya kusebenza ngokufanelekileyo. Kwaye into yokuba oku kwenza kube lula ukusebenzisana phakathi kwedatha sele i-plus, kodwa isiseko sedatha esisiphumo si-3 amaxesha amancinci ngomthamo.

Kwakhona, ngokusekelwe kwiimvavanyo ezenziweyo, sinokugqiba ukuba ilahleko yokusebenza ayibalulekanga kakhulu. Kwezinye iimeko, i-JSONB ikhawuleza ngakumbi kune-EAV, iyenza ibengcono. Nangona kunjalo, le benchmark ngokuqinisekileyo ayiquki yonke imiba (umzekelo, amaqumrhu anenani elikhulu kakhulu leepropati, ukwanda okubonakalayo kwinani leepropati zedatha ekhoyo, ...), ngoko ke ukuba unayo nayiphi na ingcebiso malunga nendlela yokuphucula. , nceda uzive ukhululekile ukushiya kumazwana!

umthombo: www.habr.com