Ag cur JSONB in ​​ionad EAV in PostgreSQL

TL; DR: Is féidir le JSONB forbairt scéimre bunachar sonraí a shimpliú go mór gan feidhmíocht na gceisteanna a íobairt.

Réamhrá

Tabhair dúinn sampla clasaiceach de dócha gurb é ceann de na cásanna úsáide is sine sa domhan de bhunachar sonraí coibhneasta (bunachar sonraí): tá eintiteas againn, agus ní mór dúinn airíonna áirithe (tréithe) an eintitis seo a shábháil. Ach ní fhéadfaidh an tsraith maoine céanna a bheith ag gach cás, agus féadfar níos mó maoine a chur leis sa todhchaí.

Is é an bealach is éasca chun an fhadhb seo a réiteach ná colún a chruthú sa tábla bunachar sonraí do gach luach maoine, agus go simplí líon isteach na cinn atá ag teastáil le haghaidh sampla aonáin ar leith. Go hiontach! Fadhb réitithe... go dtí go mbeidh na milliúin taifead ar do tábla agus go gcaithfidh tú taifead nua a chur leis.

Smaoinigh ar an bpatrún EAV (Aonán-Tréith-Luach), tarlaíonn sé sách minic. Tá eintitis (taifid) i dtábla amháin, tá ainmneacha maoine (tréithe) i dtábla amháin, agus déanann an tríú tábla aonáin a chomhlachú lena dtréithe agus tá luach na dtréithe sin ann don aonán reatha. Tugann sé seo an cumas duit tacair éagsúla maoine a bheith agat le haghaidh rudaí éagsúla, agus freisin airíonna a chur ar an eitilt gan struchtúr an bhunachair sonraí a athrú.

Mar sin féin, ní bheinn ag scríobh an phoist seo mura mbeadh roinnt míbhuntáistí ag baint le cur chuige EVA. Mar sin, mar shampla, chun aonán amháin nó níos mó a bhfuil 1 aitreabúid an ceann acu a fháil, tá 2 cheangal ag teastáil sa cheist: is nasc é an chéad cheann leis an tábla aitreabúide, is nasc é an dara ceann leis an tábla luachanna. Má tá 2 airí ag aonán, beidh 4 nasc ag teastáil! Ina theannta sin, déantar na tréithe go léir a stóráil go hiondúil mar theaghráin, rud a fhágann cineál-réitigh don toradh agus don chlásal WHERE. Má scríobhann tú go leor ceisteanna, tá sé seo go leor cur amú ó thaobh úsáid acmhainní.

In ainneoin na n-easnamh soiléire seo, baineadh úsáid as EAV le fada chun na cineálacha fadhbanna seo a réiteach. Easnaimh dosheachanta a bhí iontu seo, agus ní raibh aon rogha eile níos fearr ann.
Ach ansin bhí “teicneolaíocht” nua le feiceáil i PostgreSQL ...

Ag tosú le PostgreSQL 9.4, cuireadh an cineál sonraí JSONB leis chun sonraí dénártha JSON a stóráil. Cé go dtógann stóráil JSON san fhormáid seo beagán níos mó spáis agus ama ná gnáth-théacs JSON, tá sé i bhfad níos tapúla oibríochtaí a dhéanamh air. Tacaíonn JSONB le hinnéacsú freisin, rud a fhágann go bhfuil fiosruithe níos tapúla fós.

Ligeann cineál sonraí JSONB dúinn an patrún EAV achrannach a athsholáthar trí cholún JSONB amháin a chur lenár dtábla aonáin, rud a shimplíonn dearadh bunachar sonraí go mór. Ach áitíonn go leor gur chóir go mbeadh laghdú ar tháirgiúlacht ag gabháil leis seo ... Sin an fáth a scríobh mé an t-alt seo.

Bunachar sonraí tástála a bhunú

Chun an chomparáid seo a dhéanamh, chruthaigh mé an bunachar sonraí ar shuiteáil úr PostgreSQL 9.5 ar an tógáil $80 Aigéan Digiteach Ubuntu 14.04 Tar éis roinnt paraiméadair a chumrú i postgresql.conf rith mé seo Script ag baint úsáide as psql. Cruthaíodh na táblaí seo a leanas chun na sonraí a chur i láthair i bhfoirm 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
);

Anseo thíos tá tábla ina stórálfar na sonraí céanna, ach le tréithe i gcolún den chineál JSONB - airíonna.

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

Breathnaíonn sé i bhfad níos simplí, nach ea? Ansin cuireadh leis na táblaí aonáin é (aonán & aonán_jsonb) 10 milliún taifead, agus dá réir sin, líonadh an tábla leis na sonraí céanna ag baint úsáide as patrún EAV agus an cur chuige le colún JSONB - eintiteas_jsonb.airíonna. Mar sin, fuaireamar roinnt cineálacha sonraí éagsúla i measc an tsraith iomlán maoine. Sonraí samplacha:

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

Mar sin anois tá na sonraí céanna againn don dá rogha. A ligean ar tús a chur i gcomparáid implementations ag an obair!

Simpligh do dhearadh

Dúradh roimhe seo go ndearnadh dearadh an bhunachair sonraí a shimpliú go mór: tábla amháin, trí cholún JSONB a úsáid le haghaidh maoine, in ionad trí thábla a úsáid le haghaidh EAV. Ach conas a léirítear é seo in iarratais? Is cosúil seo a leanas le réadmhaoin aonáin amháin a nuashonrú:

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

Mar a fheiceann tú, níl cuma níos simplí ar an iarratas deiridh. Chun luach airí i réad JSONB a nuashonrú ní mór dúinn an fheidhm a úsáid jsonb_set(), agus ba cheart go n-éireodh linn ár luach nua mar réad JSONB. Mar sin féin, ní gá aon aitheantóir a bheith ar eolas againn roimh ré. Ag féachaint ar an sampla EAV, ní mór dúinn an t-eintiteas_id agus an t-eintiteas_attribute_id araon a bheith ar an eolas chun an nuashonrú a dhéanamh. Más mian leat airí i gcolún JSONB a nuashonrú bunaithe ar ainm an réada, déantar é sin in aon líne shimplí amháin.

Anois roghnaigh muid an t-aonán a nuashonraíomar bunaithe ar a dhath nua:

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

Sílim gur féidir linn a aontú go bhfuil an dara ceann níos giorra (gan aon cheangal!), agus mar sin níos inléite. Bhuaigh JSONB anseo! Úsáidimid an t-oibreoir JSON ->> chun an dath a fháil mar luach téacs ón oibiacht JSONB. Tá bealach eile ann freisin leis an toradh céanna a bhaint amach i múnla JSONB ag baint úsáide as an oibreoir @>:

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

Tá sé seo beagán níos casta: déanaimid seiceáil féachaint an bhfuil réad atá ar thaobh na láimhe deise den oibreoir @> san oibiacht JSON ina cholún airíonna. Níos lú inléite, níos táirgiúla (féach thíos).

Déanaimis níos fusa fós JSONB a úsáid nuair is gá duit ilairíonna a roghnú ag an am céanna. Seo an áit a dtagann an cur chuige JSONB isteach i ndáiríre: ní dhéanaimid ach airíonna a roghnú mar cholúin bhreise inár sraith torthaí gan gá le ceangail:

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

Le EAV beidh 2 cheangal uait le haghaidh gach réadmhaoine is mian leat a cheistiú. Is é mo thuairim go léiríonn na ceisteanna thuas simpliú mór i ndearadh bunachar sonraí. Féach ar níos mó samplaí de conas fiosrúcháin JSONB a scríobh, freisin i seo post.
Anois tá sé in am labhairt faoi fheidhmíocht.

Táirgiúlacht

Chun feidhmíocht a chur i gcomparáid a d'úsáid mé ANAILÍS MÍNITHE i bhfiosruithe, chun am forghníomhaithe a ríomh. Rinneadh gach ceist ar a laghad trí huaire mar go dtógann an pleanálaí fiosrúcháin níos faide den chéad uair. Ar dtús rith mé na ceisteanna gan aon innéacsanna. Ar ndóigh, bhí sé seo ina bhuntáiste ag JSONB, mar níorbh fhéidir leis na naisc a theastaíonn le haghaidh EAV innéacsanna a úsáid (níor innéacsaíodh príomhréimsí eachtracha). Ina dhiaidh seo chruthaigh mé innéacs ar an 2 cholún eochair choigríche den tábla luach EAV, chomh maith le hinnéacs GIN le haghaidh colún JSONB.

Léirigh an nuashonrú sonraí na torthaí seo a leanas i dtéarmaí ama (in ms). Tabhair faoi deara gur scála logartamach é:

Feicimid go bhfuil JSONB i bhfad (> 50000-x) níos tapúla ná EAV mura n-úsáideann tú innéacsanna, ar an gcúis atá luaite thuas. Nuair a dhéanaimid innéacsú ar cholúin le heochracha príomhúla, imíonn an difríocht beagnach, ach tá JSONB fós 1,3 huaire níos tapúla ná EAV. Tabhair faoi deara nach bhfuil aon éifeacht ag an innéacs ar an gcolún JSONB anseo toisc nach bhfuilimid ag baint úsáide as an gcolún maoine sna critéir mheastóireachta.

Chun sonraí a roghnú bunaithe ar luach maoine, faighimid na torthaí seo a leanas (gnáthscála):

Féadfaidh tú a thabhairt faoi deara go n-oibríonn JSONB arís níos tapúla ná EAV gan innéacsanna, ach nuair a bhíonn EAV le hinnéacsanna, oibríonn sé níos tapúla fós ná JSONB. Ach ansin chonaic mé go raibh na hamanna le haghaidh fiosrúcháin JSONB mar an gcéanna, spreag sé seo dom nach n-oibríonn innéacsanna GIN. De réir dealraimh, nuair a úsáideann tú innéacs GIN ar cholún a bhfuil airíonna daonra ann, ní bhíonn éifeacht leis ach nuair a bhíonn oibreoir san áireamh @> in úsáid. Bhain mé úsáid as seo i dtástáil nua agus bhí tionchar mór aige ar an am: ach 0,153ms! Tá sé seo 15000 uair níos tapúla ná EAV agus 25000 uair níos tapúla ná an ->> oibreoir.

Ceapaim go raibh sé tapa go leor!

Bunachar sonraí méid tábla

Déanaimis comparáid idir na méideanna tábla don dá chur chuige. I psql is féidir linn méid na dtáblaí agus na n-innéacs go léir a thaispeáint ag baint úsáide as an ordú dti+

Maidir leis an gcur chuige EAV, tá méideanna tábla thart ar 3068 MB agus innéacsanna suas le 3427 MB le haghaidh iomlán de 6,43 GB. Úsáideann cur chuige JSONB 1817 MB don tábla agus 318 MB do na hinnéacsanna, is é sin 2,08 GB. Casadh sé amach 3 huaire níos lú! Chuir an fhíric seo ionadh beag orm mar go stórálann muid ainmneacha maoine i ngach réad JSONB.

Ach fós féin, labhraíonn na huimhreacha ar a son féin: in EAV stórálaimid 2 eochair choigríche slánuimhir in aghaidh an luach aitreabúide, as a dtagann 8 mbeart de shonraí breise. Ina theannta sin, stórálann EAV gach luach maoine mar théacs, agus úsáidfidh JSONB luachanna uimhriúla agus boolean go hinmheánach nuair is féidir, agus beidh lorg níos lú mar thoradh air.

Torthaí

Ar an iomlán, ceapaim go bhféadfar i bhfad níos éasca do bhunachar sonraí a dhearadh agus a chothabháil má shábhálann tú airíonna aonáin i bhformáid JSONB. Má tá go leor ceisteanna á rith agat, oibreoidh gach rud sa tábla céanna leis an eintiteas i ndáiríre ar bhealach níos éifeachtaí. Agus is buntáiste é an bhfíric go simplíonn sé seo an t-idirghníomhú idir sonraí cheana féin, ach tá an bunachar sonraí mar thoradh air 3 huaire níos lú i méid.

Chomh maith leis sin, bunaithe ar na tástálacha a rinneadh, is féidir linn a thabhairt i gcrích go bhfuil na caillteanais feidhmíochta an-neamhshuntasach. I gcásanna áirithe, tá JSONB fiú níos tapúla ná EAV, rud a fhágann go bhfuil sé níos fearr fós. Mar sin féin, ar ndóigh, ní chlúdaíonn an tagarmharc seo gach gné (m.sh. eintitis a bhfuil líon an-mhór maoine acu, méadú suntasach ar líon maoine na sonraí atá ann cheana féin,...), mar sin má tá aon mholtaí agat maidir le conas iad a fheabhsú , bíodh leisce ort fágáil sna tuairimí!

Foinse: will.com