Pulogalamu ya ProHoster > Blog > Ulamuliro > Kusintha EAV ndi JSONB mu PostgreSQL

Kusintha EAV ndi JSONB mu PostgreSQL

TL; DR: JSONB ikhoza kufewetsa kwambiri chitukuko cha schema ya database popanda kusiya ntchito yamafunso.

Mau oyamba

Tiyeni tipereke chitsanzo chodziwika bwino cha zomwe zidagwiritsidwa ntchito kale kwambiri padziko lonse lapansi (database): tili ndi gulu, ndipo tifunika kusunga zinthu zina (makhalidwe) a bungweli. Koma sizinthu zonse zomwe zingakhale ndi katundu wofanana, ndipo katundu wina akhoza kuwonjezeredwa mtsogolo.

Njira yosavuta yothetsera vutoli ndi kupanga ndime mu tebulo lachinsinsi pa mtengo uliwonse wa katundu, ndikungodzaza zomwe zikufunika pazochitika zinazake. Zabwino! Vuto lathetsedwa... mpaka tebulo lanu lili ndi zolemba mamiliyoni ambiri ndipo muyenera kuwonjezera mbiri yatsopano.

Ganizirani mtundu wa EAV (Gulu-Makhalidwe-Kufunika), zimachitika nthawi zambiri. Gome limodzi lili ndi mabungwe (zolemba), tebulo lina lili ndi mayina a katundu (mawonekedwe), ndipo tebulo lachitatu limagwirizanitsa mabungwe omwe ali ndi mawonekedwe awo ndipo lili ndi mtengo wazinthu zomwe zilipo panopa. Izi zimakupatsani mwayi wokhala ndi magawo osiyanasiyana azinthu zosiyanasiyana, komanso kuwonjezera katundu pa ntchentche popanda kusintha mawonekedwe a database.

Komabe, sindikadakhala ndikulemba izi ngati palibe zovuta panjira ya EVA. Chifukwa chake, mwachitsanzo, kuti mupeze gulu limodzi kapena angapo omwe ali ndi 1 mawonekedwe, majowina awiri amafunikira pafunso: choyamba ndi cholumikizira ndi tebulo lamalingaliro, chachiwiri ndikulumikizana ndi tebulo lazofunikira. Ngati bungwe lili ndi 2, ndiye kuti majowina 2 akufunika! Kuonjezera apo, zizindikiro zonse zimasungidwa ngati zingwe, zomwe zimapangitsa kuti mtundu ukhale wojambula pazotsatira zonse komanso ndime ya WHERE. Ngati mulemba mafunso ambiri, ndiye kuti izi ndizowononga kwambiri pakugwiritsa ntchito zinthu.

Ngakhale pali zolakwika zodziwikiratu izi, EAV yakhala ikugwiritsidwa ntchito kuthetsa mitundu iyi yamavuto. Izi zinali zophophonya zosapeŵeka, ndipo panalibe njira ina yabwino koposa.
Koma "tekinoloje" yatsopano idawonekera mu PostgreSQL ...

Kuyambira ndi PostgreSQL 9.4, mtundu wa data wa JSONB udawonjezedwa kuti usunge data ya binary ya JSON. Ngakhale kusunga JSON mwanjira iyi nthawi zambiri kumatenga malo ndi nthawi yochulukirapo kuposa mawu osavuta a JSON, kuchitapo kanthu mwachangu kwambiri. JSONB imathandiziranso indexing, zomwe zimapangitsa kuti mafunso akhale mwachangu.

Mtundu wa data wa JSONB umatilola kuti tisinthe mtundu wovuta wa EAV powonjezera gawo limodzi la JSONB patebulo lathu, kupangitsa kuti ma database asakhale osavuta. Koma ambiri amatsutsa kuti izi ziyenera kutsagana ndi kuchepa kwa zokolola ... Ndicho chifukwa chake ndinalemba nkhaniyi.

Kukhazikitsa database yoyeserera

Pakuyerekeza uku, ndidapanga nkhokwe pakukhazikitsa kwatsopano kwa PostgreSQL 9.5 pa $80 yomanga. DigitalOcean Ubuntu 14.04. Nditakhazikitsa magawo ena mu postgresql.conf ndinathamanga izi script pogwiritsa ntchito psql. Matebulo otsatirawa adapangidwa kuti awonetse zambiri mu fomu ya 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
);

Pansipa pali tebulo lomwe deta yomweyi idzasungidwa, koma ndi zikhumbo mumtundu wa JSONB - Katundu.

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

Zikuwoneka zosavuta kwambiri, sichoncho? Kenako idawonjezedwa kumatebulo azinthu (gulu & entity_jsonb) Zolemba za 10 miliyoni, ndipo motero, tebulolo linadzazidwa ndi deta yomweyi pogwiritsa ntchito chitsanzo cha EAV ndi njira yokhala ndi gawo la JSONB - entity_jsonb.properties. Chifukwa chake, tidalandira mitundu ingapo yamitundu yosiyanasiyana pakati pamagulu onse azinthu. Zitsanzo za data:

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

Kotero tsopano tili ndi deta yofanana pazosankha zonse ziwiri. Tiyeni tiyambe kufananiza kukhazikitsa pa ntchito!

Sinthani kapangidwe kanu

Zinanenedwa kale kuti mapangidwe a database anali osavuta kwambiri: tebulo limodzi, pogwiritsa ntchito gawo la JSONB la katundu, m'malo mogwiritsa ntchito matebulo atatu a EAV. Koma kodi zimenezi zimaonekera bwanji m’zopempha? Kusintha chinthu chimodzi kumawoneka motere:

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

Monga mukuonera, pempho lomaliza silikuwoneka lophweka. Kuti tisinthe mtengo wa katundu mu chinthu cha JSONB tiyenera kugwiritsa ntchito ntchitoyi jsonb_set(), ndipo iyenera kudutsa mtengo wathu watsopano ngati chinthu cha JSONB. Komabe, sitifunika kudziwiratu chizindikiritso chilichonse. Kuyang'ana chitsanzo cha EAV, tiyenera kudziwa entity_id ndi entity_attribute_id kuti tikonze. Ngati mukufuna kusintha malo muzanja la JSONB kutengera dzina lachinthu, ndiye kuti zonse zachitika mumzere umodzi wosavuta.

Tsopano tiyeni tisankhe gulu lomwe tangosintha kumene kutengera mtundu wake watsopano:

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

Ndikuganiza kuti tikhoza kuvomereza kuti yachiwiri ndi yaifupi (palibe kujowina!), Chifukwa chake ndi yowerengeka. JSONB yapambana apa! Timagwiritsa ntchito JSON ->> woyendetsa kuti tipeze mtundu ngati mtengo wa mawu kuchokera ku chinthu cha JSONB. Palinso njira yachiwiri yopezera zotsatira zomwezo mu mtundu wa JSONB pogwiritsa ntchito @> woyendetsa:

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

Izi ndizovuta kwambiri: timayang'ana kuti tiwone ngati chinthu cha JSON mugawo lake chili ndi chinthu chomwe chili kumanja kwa @> woyendetsa. Zosawerengeka, zopanga zambiri (onani pansipa).

Tiyeni tipange kugwiritsa ntchito JSONB kukhala kosavuta mukafuna kusankha zinthu zingapo nthawi imodzi. Apa ndipamene njira ya JSONB imabwera kwenikweni: timangosankha katundu ngati magawo owonjezera pazotsatira zathu popanda kufunikira kojowina:

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

Ndi EAV mudzafunika zolumikizira 2 pachinthu chilichonse chomwe mukufuna kufunsa. M'malingaliro anga, mafunso omwe ali pamwambawa akuwonetsa kuphweka kwakukulu pamapangidwe a database. Onani zitsanzo zambiri zamomwe mungalembe mafunso ku JSONB, zothekanso izi positi.
Tsopano ndi nthawi yoti tikambirane za magwiridwe antchito.

Kukonzekera

Kufananiza magwiridwe omwe ndidagwiritsa ntchito TAFOTOKOZERA ONANI m'mafunso, kuwerengera nthawi yochitira. Funso lililonse lidachitidwa katatu chifukwa chokonza mafunso chimatenga nthawi yayitali koyamba. Poyamba ndidayendetsa mafunso popanda ma index. Mwachiwonekere, uwu unali mwayi wa JSONB, popeza majowina ofunikira a EAV sakanatha kugwiritsa ntchito ma index (mafungulo akunja sanalembedwe). Zitatha izi ndidapanga cholozera pamizere iwiri yakunja ya tebulo lamtengo wapatali la EAV, komanso index JINI kwa gawo la JSONB.

Kusintha kwa data kunawonetsa zotsatira zotsatirazi malinga ndi nthawi (mu ms). Dziwani kuti sikeloyo ndi logarithmic:

Kusintha EAV ndi JSONB mu PostgreSQL

Tikuwona kuti JSONB ndi (> 50000-x) mwachangu kuposa EAV ngati simugwiritsa ntchito indexes, pazifukwa zomwe tafotokozazi. Tikayika mafungulo okhala ndi makiyi oyambira, kusiyanako kumatha, koma JSONB ikadali mwachangu nthawi 1,3 kuposa EAV. Dziwani kuti index yomwe ili pagawo la JSONB ilibe kanthu pano popeza sitigwiritsa ntchito gawo lazinthu pazowunikira.

Posankha deta potengera mtengo wa katundu, timapeza zotsatira zotsatirazi (sikelo yodziwika bwino):

Kusintha EAV ndi JSONB mu PostgreSQL

Mutha kuzindikira kuti JSONB imagwiranso ntchito mwachangu kuposa EAV yopanda ma index, koma EAV ikakhala ndi ma index, imagwirabe ntchito mwachangu kuposa JSONB. Koma kenako ndidawona kuti nthawi zamafunso a JSONB zinali zofanana, izi zidandipangitsa kudziwa kuti ma index a GIN sagwira ntchito. Zikuoneka kuti mukamagwiritsa ntchito index ya GIN pamzati wokhala ndi anthu ambiri, zimangogwira ntchito mukamagwiritsa ntchito kuphatikiza @>. Ndinagwiritsa ntchito izi pamayeso atsopano ndipo zidakhudza kwambiri nthawi: 0,153ms yokha! Izi ndi nthawi 15000 mofulumira kuposa EAV ndi nthawi 25000 mofulumira kuposa ->> woyendetsa.

Ndikuganiza kuti inali yofulumira mokwanira!

Kukula kwa tebulo la database

Tiyeni tifananize kukula kwa tebulo kwa njira zonse ziwiri. Mu psql tikhoza kusonyeza kukula kwa matebulo onse ndi ma index pogwiritsa ntchito lamulo dti +

Kusintha EAV ndi JSONB mu PostgreSQL

Panjira ya EAV, makulidwe a tebulo ali mozungulira 3068 MB ndi ma index mpaka 3427 MB okwana 6,43 GB. Njira ya JSONB imagwiritsa ntchito 1817 MB patebulo ndi 318 MB pama index, omwe ndi 2,08 GB. Zimakhala zochepera 3! Izi zidandidabwitsa pang'ono chifukwa timasunga mayina azinthu pazinthu zilizonse za JSONB.

Komabe, manambalawa amadzinenera okha: mu EAV timasunga makiyi awiri akunja akunja pamtengo uliwonse, zomwe zimapangitsa kuti ma byte 2 aziwonjezera. Kuphatikiza apo, EAV imasunga zinthu zonse ngati zolemba, pomwe JSONB idzagwiritsa ntchito manambala ndi ma boolean mkati momwe zingathere, zomwe zimapangitsa kuti pakhale pang'ono.

Zotsatira

Ponseponse, ndikuganiza kuti kusunga katundu mumtundu wa JSONB kungapangitse kupanga ndi kusunga nkhokwe yanu kukhala kosavuta. Ngati mukufunsa mafunso ambiri, ndiye kuti kusunga zonse patebulo lomwelo monga gulu limagwira ntchito bwino kwambiri. Ndipo mfundo yakuti izi zimathandizira kuyanjana pakati pa deta ndizowonjezera kale, koma nkhokwe zomwe zimatsatira ndizochepa katatu mu voliyumu.

Komanso, potengera mayesero omwe achitidwa, tikhoza kunena kuti zotayika za ntchitoyo ndizochepa kwambiri. Nthawi zina, JSONB imakhala yachangu kuposa EAV, ndikupangitsa kuti ikhale yabwinoko. Komabe, chizindikirochi sichimakhudza mbali zonse (mwachitsanzo mabungwe omwe ali ndi katundu wambiri, kuwonjezeka kwakukulu kwa chiwerengero cha zinthu zomwe zilipo kale, ...), kotero ngati muli ndi malingaliro amomwe mungawathandizire. , chonde omasuka kusiya ndemanga!

Source: www.habr.com

Kuwonjezera ndemanga