Ukushintsha i-EAV nge-JSONB ku-PostgreSQL

I-TL; DR: I-JSONB ingenza kube lula kakhulu ukuthuthukiswa kwe-schema sesizindalwazi ngaphandle kokudela ukusebenza kombuzo.

Isingeniso

Ake sinikeze isibonelo sakudala cishe esinye sezigameko zokusetshenziswa ezindala kakhulu emhlabeni zesizindalwazi esihlobene (isizindalwazi): sinebhizinisi, futhi sidinga ukulondoloza izici ezithile (izibaluli) zaleli bhizinisi. Kodwa akuzona zonke izimo ezingaba nesethi efanayo yezakhiwo, futhi izakhiwo ezengeziwe zingase zengezwe ngokuzayo.

Indlela elula yokuxazulula le nkinga iwukuba udale ikholomu kuthebula lesizindalwazi yenani ngalinye lesakhiwo, futhi umane ugcwalise lezo ezidingekayo esimweni esithile sebhizinisi. Kuhle! Inkinga ixazululiwe... kuze kube yilapho ithebula lakho liqukethe izigidi zamarekhodi futhi udinga ukungeza irekhodi elisha.

Cabanga ngephethini ye-EAV (Ibhizinisi-Imfanelo-Value), kwenzeka kaningi. Ithebula elilodwa liqukethe izinhlangano (amarekhodi), elinye ithebula liqukethe amagama ezakhiwo (izibaluli), kanti ithebula lesithathu lihlobanisa amabhizinisi nezibaluli zawo futhi liqukethe inani lalezo zimfanelo zebhizinisi lamanje. Lokhu kukunikeza amandla okuba namasethi ahlukene wezakhiwo zezinto ezahlukene, futhi wengeze izakhiwo empukaneni ngaphandle kokushintsha isakhiwo sedathabheyisi.

Kodwa-ke, bengingeke ngibhale lokhu okuthunyelwe ukube bezingekho izinto ezingezinhle endleleni ye-Eva. Ngakho-ke, ngokwesibonelo, ukuthola ibhizinisi elilodwa noma ngaphezulu elinesibaluli esingu-1 lilinye, kudingeka ukujoyina okungu-2 embuzweni: eyokuqala iwukuhlanganisa nethebula lesichasiso, okwesibili ukuhlanganisa nethebula lamanani. Uma ibhizinisi linezibaluli ezi-2, kudingeka ukujoyina okungu-4! Ukwengeza, zonke izibaluli zivame ukugcinwa njengezintambo, okuholela ekulingiseni kohlobo kokubili komphumela kanye nesigatshana esithi WHERE. Uma ubhala imibuzo eminingi, khona-ke lokhu kuwukumosha impela mayelana nokusetshenziswa kwezinsiza.

Naphezu kwalokhu kushiyeka okusobala, i-EAV sekuyisikhathi eside isetshenziselwa ukuxazulula lezi zinhlobo zezinkinga. Lawa kwakungamaphutha angenakugwenywa, futhi yayingekho enye indlela engcono kakhulu.
Kepha kwabe sekuvela “ubuchwepheshe” obusha ku-PostgreSQL...

Kusukela nge-PostgreSQL 9.4, uhlobo lwedatha ye-JSONB lwengezwe ukuze kugcinwe idatha kanambambili ye-JSON. Nakuba ukugcina i-JSON ngale fomethi ngokuvamile kuthatha isikhala esiningi nesikhathi kunombhalo ongenalutho othi JSON, ukwenza imisebenzi kuyo kuyashesha kakhulu. I-JSONB iphinde isekele ukukhonjwa, okwenza imibuzo isheshe nakakhulu.

Uhlobo lwedatha ye-JSONB lusivumela ukuthi simiselele iphethini ye-EAV enzima ngokwengeza ikholomu eyodwa nje ye-JSONB kuthebula lethu lebhizinisi, okwenza kube lula kakhulu ukwakheka kwesizindalwazi. Kodwa abaningi bathi lokhu kufanele kuhambisane nokuncipha kokukhiqiza... Yingakho ngibhale lesi sihloko.

Ukusetha isizindalwazi sokuhlola

Kulesi siqhathaniso, ngidale i-database ekufakweni okusha kwe-PostgreSQL 9.5 ku-$80 build. I-DigitalOcean Ubuntu 14.04. Ngemva kokusetha amapharamitha athile ku-postgresql.conf ngagijima lokhu script usebenzisa i-psql. Amathebula alandelayo adalwe ukwethula idatha ngefomu le-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
);

Ngezansi kunethebula lapho idatha efanayo izogcinwa khona, kodwa ngezibaluli kukholomu yohlobo lwe-JSONB - izakhiwo.

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

Kubukeka kulula kakhulu, akunjalo? Bese yengezwa kumathebula ebhizinisi (inhlangano & entity_jsonb) Amarekhodi ayizigidi ezingu-10, futhi ngenxa yalokho, ithebula lagcwaliswa ngedatha efanayo kusetshenziswa iphethini ye-EAV nendlela enekholomu ye-JSONB - entity_jsonb.properties. Ngakho-ke, sithole izinhlobo eziningana zedatha ezihlukene phakathi kwayo yonke isethi yezakhiwo. Idatha yesampula:

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

Ngakho manje sinedatha efanayo yazo zombili izinketho. Ake siqale ukuqhathanisa ukuqaliswa emsebenzini!

Yenza umklamo wakho ube lula

Phambilini kwashiwo ukuthi ukwakheka kwesizindalwazi kwenziwe kwaba lula kakhulu: ithebula elilodwa, ngokusebenzisa ikholomu ye-JSONB yezakhiwo, esikhundleni sokusebenzisa amathebula amathathu e-EAV. Kodwa lokhu kubonakala kanjani ezicelweni? Ukubuyekeza impahla yebhizinisi eyodwa kubukeka kanje:

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

Njengoba ubona, isicelo sokugcina asibukeki silula. Ukuze sibuyekeze inani lesakhiwo entweni ye-JSONB kufanele sisebenzise umsebenzi jsonb_set(), futhi kufanele kudlule inani lethu elisha njengento ye-JSONB. Nokho, asikho isidingo sazi noma iyiphi inkomba kusengaphambili. Uma sibheka isibonelo se-EAV, sidinga ukwazi kokubili i-entity_id kanye ne-entity_attribute_id ukuze senze isibuyekezo. Uma ufuna ukubuyekeza isakhiwo kukholomu ye-JSONB ngokususelwe egameni lento, khona-ke konke kwenziwa ngomugqa owodwa olula.

Manje ake sikhethe ibhizinisi esisanda kulibuyekeza ngokusekelwe kumbala walo omusha:

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

Ngicabanga ukuthi singavuma ukuthi eyesibili yifushane (akukho ukujoyina!), ngakho-ke ifundeka kakhudlwana. I-JSONB iyawina lapha! Sisebenzisa i-opharetha ye-JSON ->> ukuze sithole umbala njengenani lombhalo entweni ye-JSONB. Kukhona nendlela yesibili yokuzuza umphumela ofanayo kumodeli ye-JSONB usebenzisa @> opharetha:

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

Lokhu kuyinkimbinkimbi kakhulu: sihlola ukuze sibone ukuthi ingabe into ye-JSON kukholomu yezindawo zayo iqukethe into engakwesokudla ku-opharetha @>. Ayifundeki kangako, ikhiqiza kakhulu (bheka ngezansi).

Masenze ukusebenzisa i-JSONB kube lula nakakhulu uma udinga ukukhetha izakhiwo eziningi ngesikhathi esisodwa. Kulapho indlela ye-JSONB ingena khona ngempela: simane sikhethe izakhiwo njengamakholomu engeziwe kusethi yethu yemiphumela ngaphandle kwesidingo sokujoyina:

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

Nge-EAV uzodinga ukujoyina okungu-2 endaweni ngayinye ofuna ukuyibuza. Ngokubona kwami, le mibuzo engenhla ikhombisa ukwenziwa lula okukhulu ekwakhiweni kwesizindalwazi. Bona izibonelo eziningi zendlela yokubhala imibuzo ye-JSONB, naku lokhu okuthunyelwe.
Manje sekuyisikhathi sokukhuluma ngokusebenza.

Ukukhiqiza

Ukuqhathanisa ukusebenza engikusebenzisile CHAZA HLAZIYA ngemibuzo, ukubala isikhathi sokwenza. Umbuzo ngamunye usetshenziswe okungenani izikhathi ezintathu ngoba umhleli wombuzo uthatha isikhathi eside okokuqala. Okokuqala ngiphendule imibuzo ngaphandle kwezinkomba. Lokhu ngokusobala bekuyinzuzo ye-JSONB, njengoba amajoyinti adingekayo ku-EAV awakwazanga ukusebenzisa izinkomba (izinkambu zokhiye bangaphandle bezingakhonjiswa). Ngemva kwalokhu ngidale inkomba kumakholomu angukhiye angaphandle angu-2 wethebula lenani le-EAV, kanye nenkomba Gin okwekholomu ye-JSONB.

Ukubuyekezwa kwedatha kubonise imiphumela elandelayo ngokwesikhathi (nge-ms). Qaphela ukuthi isikali siyi-logarithmic:

Siyabona ukuthi i-JSONB ishesha kakhulu (> 50000-x) kune-EAV uma ungasebenzisi izinkomba, ngenxa yesizathu esishiwo ngenhla. Uma sikhomba amakholomu anokhiye abayinhloko, umehluko ucishe unyamalale, kodwa i-JSONB isashesha izikhathi ezingu-1,3 kune-EAV. Qaphela ukuthi inkomba kukholomu ye-JSONB ayinamthelela lapha njengoba singasebenzisi ikholomu yendawo kumibandela yokuhlola.

Ngokukhetha idatha ngokusekelwe kunani lesakhiwo, sithola imiphumela elandelayo (isikali esivamile):

Ungaqaphela ukuthi i-JSONB futhi isebenza ngokushesha kune-EAV ngaphandle kwezinkomba, kodwa uma i-EAV inezinkomba, isasebenza ngokushesha kune-JSONB. Kodwa-ke ngabona ukuthi izikhathi zemibuzo ye-JSONB zazifana, lokhu kungenze ngathola ukuthi izinkomba ze-GIN azisebenzi. Ngokusobala uma usebenzisa inkomba ye-GIN kukholomu enezici ezigcwele, iqala ukusebenza kuphela uma usebenzisa ukufaka phakathi u-opharetha @>. Ngisebenzise lokhu esivivinyweni esisha futhi kwaba nomthelela omkhulu ngesikhathi: kuphela 0,153ms! Lokhu kushesha izikhathi ezingu-15000 kune-EAV futhi izikhathi ezingu-25000 ngokushesha kuno-opharetha ->>.

Ngicabanga ukuthi bekushesha ngokwanele!

Usayizi wethebula lesizindalwazi

Ake siqhathanise osayizi betafula kuzo zombili izindlela. Ku-psql singabonisa usayizi wawo wonke amatafula nezinkomba sisebenzisa umyalo dti+

Ngendlela ye-EAV, osayizi bethebula balinganiselwa ku-3068 MB kanye nezinkomba ezifika ku-3427 MB ngesamba esingu-6,43 GB. Indlela ye-JSONB isebenzisa u-1817 MB kuthebula no-318 MB kuma-index, okungu-2,08 GB. Kuvela izikhathi ezi-3 ngaphansi! Leli qiniso lingimangaze kancane ngoba sigcina amagama ezakhiwo kuzo zonke izinto ze-JSONB.

Kodwa noma kunjalo, izinombolo ziyazikhulumela: ku-EAV sigcina izinombolo ezi-2 zokhiye bangaphandle ngenani lemfanelo ngayinye, okuholela kumabhayithi angu-8 edatha eyengeziwe. Ukwengeza, i-EAV igcina wonke amanani ezakhiwo njengombhalo, kuyilapho i-JSONB izosebenzisa amanani ezinombolo ne-boolean ngaphakathi lapho kungenzeka khona, okuholela ekugxilweni okuncane.

Imiphumela

Sekukonke, ngicabanga ukuthi ukulondoloza izakhiwo zebhizinisi ngefomethi ye-JSONB kungenza ukuklama nokugcina isizindalwazi sakho kube lula kakhulu. Uma usebenzisa imibuzo eminingi, ukugcina yonke into etafuleni elifanayo nebhizinisi kuzosebenza ngokuphumelelayo. Futhi iqiniso lokuthi lokhu kwenza kube lula ukusebenzisana phakathi kwedatha sekuvele kuhlanganisiwe, kodwa i-database ewumphumela incane izikhathi ezi-3 ngevolumu.

Futhi, ngokusekelwe ekuhloleni okwenziwe, singaphetha ngokuthi ukulahlekelwa kokusebenza akubalulekile kakhulu. Kwezinye izimo, i-JSONB ishesha kakhulu kune-EAV, iyenza ibe ngcono nakakhulu. Nokho, le bhentshimakhi ayifaki zonke izici (isb., amabhizinisi anenani elikhulu kakhulu lezakhiwo, ukwanda okuphawulekayo kwenani lezakhiwo zedatha ekhona,...), ngakho-ke uma uneziphakamiso zokuthi ungazithuthukisa kanjani. , sicela ukhululeke ukushiya kumazwana!

Source: www.habr.com