TL; DR: JSONB inogona kurerutsa dhatabhesi schema kusimudzira pasina kupira kuita kwemubvunzo.
Nhanganyaya
Ngatipei muenzaniso wekare weimwe yekare-kare yekushandisa kesi pasi rose rehukama dhatabhesi (dhatabhesi): isu tine mubatanidzwa, uye isu tinofanirwa kuchengetedza zvimwe zvivakwa (hunhu) zvechinhu ichi. Asi hazvisi zvese zviitiko zvinogona kunge zvine seti yezvivakwa, uye zvimwe zvivakwa zvinogona kuwedzerwa mune ramangwana.
Nzira iri nyore yekugadzirisa dambudziko iri kugadzira koramu mune tafura yedatabase kune yega yega kukosha kwepfuma, uye ingozadza izvo zvinodikanwa kune chaiyo chikamu chiitiko. Hukuru! Dambudziko rakagadziriswa... kusvika tafura yako iine mamirioni emarekodhi uye iwe unofanirwa kuwedzera rekodhi nyowani.
Funga nezveEAV pateni (), zvinowanzoitika. Imwe tafura ine masangano (marekodhi), imwe tafura ine mazita ezvivakwa (attributes), uye yechitatu tafura inosanganisa masangano nehunhu hwayo uye ine kukosha kweiyo hunhu hwechiko chazvino. Izvi zvinokupa iwe kugona kuve neakasiyana seti ezvivakwa zvezvinhu zvakasiyana, uye zvakare kuwedzera zvimiro panhunzi pasina kushandura dhatabhesi chimiro.
Nekudaro, ini ndingadai ndisiri kunyora iyi positi dai pasina zvimwe zvakaderera kune EVA maitiro. Saka, semuenzaniso, kuti uwane chimwe kana akawanda masangano ane 1 hunhu chimwe nechimwe, 2 majoini anodiwa mumubvunzo: wekutanga kujoinha netafura yehunhu, yechipiri kujoinha netafura yezvakakosha. Kana sangano riine 2 hunhu, saka mana anojoinha anodiwa! Pamusoro pezvo, hunhu hwese hunowanzo kuchengetwa setambo, izvo zvinoguma nekukanda mhando kune ese mhedzisiro uye WHERE clause. Kana iwe ukanyora yakawanda mibvunzo, saka izvi zvakanyanya kutambisa maererano nekushandisa zviwanikwa.
Pasinei nekukanganisa kuri pachena uku, EAV yakagara ichishandiswa kugadzirisa marudzi aya ematambudziko. Idzi dzakanga dziri zvikanganiso zvisingadzivisiki, uye pakanga pasina imwe nzira iri nani.
Asi ipapo "tekinoroji" nyowani yakaonekwa muPostgreSQL ...
Kutanga nePostgreSQL 9.4, iyo JSONB data type yakawedzerwa kuchengeta JSON binary data. Kunyangwe kuchengeta JSON mune iyi fomati kunowanzo kutora imwe nzvimbo uye nguva pane yakajeka JSON, kuita maoparesheni pairi kunokurumidza. JSONB inotsigirawo indexing, izvo zvinoita kuti mibvunzo iwedzere kukurumidza.
Iyo JSONB dhata yemhando inotibvumira kutsiva iyo inorema EAV pateni nekuwedzera imwe chete JSONB koramu kune yedu tafura tafura, ichirerutsa dhizaini dhizaini. Asi vazhinji vanopikisa kuti izvi zvinofanira kuperekedzwa nekuderera kwekubudirira ... Ndicho chikonzero ndakanyora chinyorwa ichi.
Kugadzira dhatabhesi rekuyedza
Pakuenzanisa uku, ini ndakagadzira dhatabhesi pane patsva kuisirwa PostgreSQL 9.5 pane iyo $80 kuvaka. Ubuntu 14.04. Mushure mekuisa mamwe ma parameter mu postgresql.conf ndakamhanya script uchishandisa psql. Matafura anotevera akagadzirwa kuti ape data muEAV fomu:
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
);
Pazasi pane tafura iyo iyo data yakafanana ichachengetwa, asi ine hunhu mune JSONB mhando column - dzimba.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Zvinotaridzika zviri nyore, handizvo here? Yakabva yawedzerwa kumatafura esangano (sangano & entity_jsonb) Mamiriyoni gumi marekodhi, uye nekudaro, tafura yakazadzwa neiyo data uchishandisa iyo EAV pateni uye nzira ine JSONB column - entity_jsonb.properties. Nekudaro, isu takagamuchira akati wandei akasiyana data mhando pakati peseti yese yezvivakwa. Muenzaniso data:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Saka ikozvino isu tine data yakafanana kune ese ari maviri sarudzo. Ngatitangei kuenzanisa kushandiswa kubasa!
Rerutsa dhizaini yako
Zvakambotaurwa kuti dhizaini yedatabase yakarerutswa zvakanyanya: tafura imwe, nekushandisa JSONB column yezvivakwa, pane kushandisa matafura matatu eEAV. Asi izvi zvinoratidzwa sei muzvikumbiro? Kuvandudza chimwe chivakwa chinoratidzika seizvi:
-- 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;
Sezvauri kuona, chikumbiro chekupedzisira hachiratidzike chiri nyore. Kuti tigadzirise kukosha kwepfuma muchinhu cheJSONB tinofanira kushandisa basa racho , uye tinofanirwa kupfuudza kukosha kwedu kutsva sechinhu cheJSONB. Nekudaro, isu hatifanire kuziva chero chiziviso pachine nguva. Tichitarisa muenzaniso weEAV, tinoda kuziva zvese entity_id uye entity_attribute_id kuitira kuti tigadzirise. Kana iwe uchida kugadzirisa chivakwa muJSONB koramu zvichibva pazita rechinhu, saka zvese zvinoitwa mumutsara wakapusa.
Zvino ngatisarudzei mubatanidzwa watichangobva kuvandudza zvichienderana nemuvara wayo mutsva:
-- 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';
Ndinofunga kuti tinogona kubvumirana kuti yechipiri ipfupi (hapana kujoina!), Uye saka inoverengwa. JSONB inohwina pano! Isu tinoshandisa iyo JSON ->> opareta kuti tiwane iyo ruvara seyambiro kukosha kubva kuJSONB chinhu. Kune zvakare nzira yechipiri yekuwana iyo yakafanana mhedzisiro muJSONB modhi uchishandisa iyo @> opareta:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Izvi zvanyanya kuomarara: tinotarisa kuti tione kana chinhu cheJSON chiri mukoramu yezvivakwa chine chinhu chiri kurudyi rwe @> opareta. Isingaverengeki, inobereka zvakanyanya (ona pazasi).
Ngatiite kushandisa JSONB kuve nyore kana iwe uchida kusarudza akawanda zvivakwa kamwechete. Apa ndipo panopinda nzira yeJSONB chaizvo: isu tinongosarudza zvivakwa semamwe makoramu mune yedu mhedzisiro yakatarwa pasina kudiwa kwekujoinha:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
NeEAV iwe uchada maviri anojoinha kune yega yega pfuma yaunoda kubvunza. Sekuona kwangu, mibvunzo iri pamusoro inoratidza kurerutsa kukuru mukugadzira dhatabhesi. Ona mimwe mienzaniso yekunyora mibvunzo yeJSONB, zvakare mukati post.
Ikozvino yave nguva yekutaura nezve performance.
Kubudirira
Kufananidza kuita kwandakashandisa mumibvunzo, kuverenga nguva yekuuraya. Mubvunzo wega wega wakaitwa kanokwana katatu nekuti query planner inotora nguva yakareba kekutanga. Chekutanga ndakamhanyisa mibvunzo pasina kana indexes. Zviripachena, iyi yaive mukana weJSONB, sezvo majoini aidiwa kuEAV aisagona kushandisa indexes (yekunze makiyi minda aive asina indexed). Mushure meizvi ini ndakagadzira index pane maviri ekunze kiyi makoramu eiyo EAV kukosha tafura, pamwe neindekisi yeJSONB column.
Iyo data yekuvandudza yakaratidza zvinotevera mhedzisiro maererano nenguva (mu ms). Ziva kuti chiyero chiri logarithmic:
Tinoona kuti JSONB yakawandisa (> 50000-x) nekukurumidza kupfuura EAV kana ukasashandisa indexes, nechikonzero chataurwa pamusoro. Patinonongedza makoramu ane makiyi ekutanga, mutsauko unenge wanyangarika, asi JSONB ichiri 1,3 times nekukurumidza kupfuura EAV. Ziva kuti indekisi iri paJSONB column haina mhedzisiro pano sezvo isu tisiri kushandisa iyo pfuma column mune yekuongorora maitiro.
Pakusarudza dhata zvichienderana nekukosha kwepfuma, tinowana zvinotevera (zvakajairwa chiyero):
Iwe unogona kuona kuti JSONB inoshanda zvakare nekukurumidza kupfuura EAV isina indexes, asi kana EAV ine indexes, ichiri kushanda nekukurumidza kupfuura JSONB. Asi ndakazoona kuti nguva dzemibvunzo yeJSONB dzaive dzakafanana, izvi zvakandikurudzira kuti maGIN indexes haashande. Sezviri pachena kana iwe ukashandisa GIN indekisi pane koramu ine vanhu vakawanda zvivakwa, zvinongoitika kana uchishandisa sanganisira opareta @>. Ndakashandisa izvi muyedzo nyowani uye yakave nekukanganisa kukuru panguva: chete 0,153ms! Iyi inguva 15000 nekukurumidza kupfuura EAV uye 25000 nguva nekukurumidza kupfuura iyo ->> mushandisi.
Ndinofunga kuti yakakurumidza zvakakwana!
Database table size
Ngatienzanise masaizi etafura yemaitiro ese ari maviri. Mu psql tinogona kuratidza saizi yeese matafura uye indexes tichishandisa murairo dti+
Pamaitiro eEAV, saizi dzetafura dzakatenderedza 3068 MB uye indexes kusvika 3427 MB yehuwandu hwe6,43 GB. Iyo JSONB nzira inoshandisa 1817 MB yetafura uye 318 MB yema indexes, inova 2,08 GB. Zvinobuda 3 nguva shoma! Ichi chokwadi chakandishamisa zvishoma nekuti isu tinochengeta mazita ezvivakwa mune yega chinhu cheJSONB.
Asi zvakadaro, nhamba dzinozvitaurira: muEAV tinochengeta 2 integer makiyi ekunze pane hunhu hunokosha, zvichikonzera 8 bytes yekuwedzera data. Pamusoro pezvo, EAV inochengeta ese ezvivakwa kukosha semavara, nepo JSONB ichizoshandisa manhamba uye boolean kukosha mukati pazvinogoneka, zvichikonzera diki tsoka.
Migumisiro
Pakazere, ndinofunga kuchengetedza zvivakwa muJSONB fomati kunogona kuita kuti kugadzira uye kuchengetedza dhatabhesi yako zvive nyore. Kana iwe uchimhanyisa mibvunzo yakawanda, saka kuchengeta zvese zviri mutafura imwechete sechinhu chinozoshanda zvakanyanya. Uye chokwadi chekuti izvi zvinorerutsa kudyidzana pakati pe data yatove kuwedzera, asi iyo inokonzeresa dhatabhesi idiki ka3 muhuwandu.
Zvakare, zvichibva pabvunzo dzakaitwa, tinogona kugumisa kuti kurasikirwa kwekuita hakuna kukosha. Mune zvimwe zviitiko, JSONB yakatokurumidza kupfuura EAV, ichiita kuti ive nani. Nekudaro, iyi benchmark hongu haifukidze zvese (semuenzaniso masangano ane nhamba yakakura kwazvo yezvivakwa, kuwedzera kukuru kwenhamba yezvivakwa zve data iripo,...), saka kana uine chero mazano ekuti ungazvivandudza sei. , ndapota inzwa wakasununguka kusiya mumashoko!
Source: www.habr.com