TL; DR: JSONB kan die ontwikkeling van databasisskemas aansienlik vereenvoudig sonder om navraagprestasie in te boet.
Inleiding
Hier is 'n klassieke voorbeeld van waarskynlik een van die oudste gebruiksgevalle in die wêreld van relasionele databasisse (databasis): ons het 'n entiteit, en ons moet sekere eienskappe (kenmerke) van hierdie entiteit stoor. Maar nie alle gevalle mag dieselfde stel eiendomme hê nie, bowendien, in die toekoms, die moontlike toevoeging van meer eiendomme.
Die maklikste manier om hierdie probleem op te los, is om 'n kolom in die databasistabel vir elke eiendomswaarde te skep, en vul net die in wat nodig is vir 'n spesifieke entiteit-instansie. Puik! Probleem opgelos...totdat jou tabel miljoene rekords het en jy 'n nuwe rekord moet byvoeg.
Oorweeg die EAV-patroon () is redelik algemeen. Een tabel bevat entiteite (rekords), 'n ander tabel bevat eiendomsname (kenmerke), en 'n derde tabel assosieer entiteite met hul eienskappe en bevat die waarde van hierdie eienskappe vir die huidige entiteit. Dit gee jou die vermoë om verskillende stelle eienskappe vir verskillende voorwerpe te hê, sowel as om eienskappe dadelik by te voeg sonder om die databasisstruktuur te verander.
Ek sou egter nie hierdie pos skryf as daar nie 'n paar foute in die EVA-benadering was nie. So, byvoorbeeld, om een of meer entiteite te kry wat elk 1 eienskap het, word 2 aansluitings (aansluitings) in die navraag vereis: die eerste is 'n koppeling met 'n kenmerktabel, die tweede is 'n koppeling met 'n waardetabel. As 'n entiteit 2 eienskappe het, is 4 aansluitings nodig! Alle eienskappe word ook gewoonlik as stringe gestoor, wat lei tot tipe casting vir beide die resultaat en die WHERE klousule. As jy baie navrae skryf, is dit nogal verkwistend in terme van hulpbrongebruik.
Ten spyte van hierdie ooglopende tekortkominge, is EAV lank reeds gebruik om hierdie soort probleme op te los. Dit was onvermydelike nadele, en daar was eenvoudig geen beter alternatief nie.
Maar toe verskyn 'n nuwe "tegnologie" in PostgreSQL ...
Begin met PostgreSQL 9.4, is die JSONB-datatipe bygevoeg om binêre JSON-data te stoor. Alhoewel die berging van JSON in hierdie formaat gewoonlik 'n bietjie meer spasie en tyd neem as gewone teks JSON, is dit baie vinniger om bewerkings daarop uit te voer. JSONB ondersteun ook indeksering, wat dit selfs vinniger maak om navraag te doen.
Die JSONB-datatipe stel ons in staat om die omslagtige EAV-patroon te vervang deur net een JSONB-kolom by ons entiteitstabel by te voeg, wat databasisontwerp aansienlik vereenvoudig. Maar baie redeneer dat dit gepaard moet gaan met 'n afname in prestasie ... Dit is hoekom ek met hierdie artikel vorendag gekom het.
Die opstel van 'n toetsdatabasis
Vir hierdie vergelyking het ek die databasis geskep op 'n nuwe installasie van PostgreSQL 9.5 op 'n $ 80 bou. Ubuntu 14.04 Nadat ek 'n paar parameters in postgresql.conf gekonfigureer het, het ek uitgevoer skrif met psql. Die volgende tabelle is geskep om die data in EAV-formaat aan te bied:
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
);
Hieronder is 'n tabel waar dieselfde data gestoor sal word, maar met eienskappe in 'n JSONB tipe kolom − eiendomme.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Lyk baie makliker, nie waar nie? Toe is dit by entiteitstabelle gevoeg (entiteit & entity_jsonb) 10 miljoen rekords, en is dienooreenkomstig gevul met dieselfde tabeldata waar die EAV-patroon gebruik word en die JSONB-kolombenadering - entity_jsonb.properties. Ons het dus verskeie verskillende datatipes onder die hele stel eiendomme ontvang. Voorbeelddata:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}So, nou het ons dieselfde data vir die twee opsies. Kom ons begin implementerings by die werk vergelyk!
Ontwerp Vereenvoudiging
Soos vroeër genoem, is die databasisontwerp baie vereenvoudig: een tabel, deur 'n JSONB-kolom vir eienskappe te gebruik, in plaas van om drie tabelle vir EAV te gebruik. Maar hoe word dit in versoeke weerspieël? Die opdatering van 'n enkele entiteitseiendom lyk soos volg:
-- 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;
Soos u kan sien, lyk die laaste navraag nie eenvoudiger nie. Om die waarde van 'n eiendom in 'n JSONB-voorwerp op te dateer, moet ons die funksie gebruik , en moet ons nuwe waarde as 'n JSONB-voorwerp deurgee. Ons hoef egter nie vooraf enige identifiseerder te ken nie. As ons na die EAV-voorbeeld kyk, moet ons beide entity_id en entity_attribute_id ken om op te dateer. As jy 'n eienskap in 'n JSONB-kolom wil bywerk op grond van die naam van 'n voorwerp, word dit alles in een eenvoudige reël gedoen.
Kom ons kies nou die entiteit wat ons pas opgedateer het op grond van sy nuwe kleur:
-- 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';
Ek dink ons kan saamstem dat die tweede een korter is (geen aansluit nie!), en dus meer leesbaar is. JSONB wen hier! Ons gebruik die JSON ->> operateur om die kleur as 'n tekswaarde van 'n JSONB-voorwerp te kry. Daar is ook 'n tweede manier om dieselfde resultaat in 'n JSONB-model te bereik deur die @>-operateur te gebruik:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Dit is 'n bietjie meer ingewikkeld: ons kyk of die JSON-voorwerp in die eienskapkolom die voorwerp regs van die @>-operateur bevat. Minder leesbaar, meer werksaam (sien hieronder).
Kom ons maak dit nog makliker om JSONB te gebruik wanneer jy verskeie eiendomme op dieselfde tyd moet kies. Dit is waar die JSONB-benadering werklik inkom: ons kies net eienskappe as addisionele kolomme in ons resultatestel sonder die behoefte aan koppelings:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Met EAV het jy 2 aansluitings nodig vir elke eiendom wat jy wil navraag doen. Na my mening toon die navrae hierbo 'n groot vereenvoudiging in databasisontwerp. Sien meer voorbeelde van hoe om JSONB-navrae te skryf, sien ook Post.
Nou is dit tyd om oor prestasie te praat.
produktiwiteit
Om prestasie te vergelyk het ek gebruik in versoeke, om die uitvoeringstyd te bereken. Elke navraag is ten minste drie keer uitgevoer omdat die navraagbeplanner die eerste keer langer neem. Eers het ek navrae sonder enige indekse uitgevoer. Dit het duidelik gedien as 'n voordeel vir JSONB, aangesien die aansluitings wat vir EAV vereis word, nie indekse kon gebruik nie (buitelandse sleutelvelde is nie geïndekseer nie). Daarna het ek 'n indeks geskep op die 2 buitelandse sleutelkolomme van die EAV waarde tabel en ook die indeks vir 'n JSONB-kolom.
Deur die data te verfris, het die volgende resultate in terme van tyd (in ms) getoon. Let daarop dat die skaal logaritmies is:
Ons sien dat JSONB baie (> 50000-x) vinniger is as EAV as geen indekse gebruik word nie, om die rede hierbo genoem. Wanneer ons kolomme met primêre sleutels indekseer, verdwyn die verskil amper, maar JSONB is steeds 1,3 keer vinniger as EAV. Let daarop dat die indeks op die JSONB-kolom geen effek hier het nie, aangesien ons nie 'n eiendomskolom in die evalueringskriteria gebruik nie.
Vir die keuse van data gebaseer op eiendomswaarde, kry ons die volgende resultate (normale skaal):
Jy kan sien dat JSONB weer vinniger is as EAV sonder indekse, maar wanneer EAV met indekse is, is dit steeds vinniger as JSONB. Maar toe sien ek dat die tydsberekening vir die JSONB-versoeke dieselfde was, wat my daartoe gelei het dat die GIN-indeks nie gevuur het nie. Blykbaar, wanneer jy 'n GIN-indeks op 'n kolom met gevulde eienskappe gebruik, word dit slegs van krag wanneer die include-operateur @> gebruik word. Ek het dit in 'n nuwe toets gebruik, wat 'n groot impak op die tyd gehad het: slegs 0,153ms! Dit is 15000 25000 keer vinniger as EAV en XNUMX XNUMX keer vinniger as die ->> operateur.
Ek dink dit was vinnig genoeg!
DB tabel grootte
Kom ons vergelyk die tabelgroottes vir beide benaderings. In psql kan ons die grootte van alle tabelle en indekse met die opdrag wys dti+
Vir die EAV-benadering is tabelgroottes ongeveer 3068MB en indekse is tot 3427MB vir 'n totaal van 6,43GB. Die JSONB-benadering gebruik 1817 MB vir die tabel en 318 MB vir die indekse, wat 2,08 GB is. Dit blyk 3 keer minder! Hierdie feit het my 'n bietjie verras omdat ons eiendomsname in elke JSONB-voorwerp stoor.
Maar steeds spreek die getalle vanself: in EAV stoor ons 2 heelgetal vreemde sleutels per kenmerkwaarde, wat lei tot 8 grepe bykomende data. Ook, in EAV, word alle eiendomswaardes as teks gestoor, terwyl JSONB numeriese en Boolese waardes intern sal gebruik waar moontlik, wat lei tot 'n kleiner voetspoor.
Resultate van
Oor die algemeen dink ek dat die stoor van entiteitseienskappe in JSONB-formaat die ontwerp en instandhouding van u databasis aansienlik kan vereenvoudig. As jy baie navrae doen, sal alles wat in dieselfde tabel as die entiteit gestoor is, eintlik meer doeltreffend werk. En die feit dat dit die interaksie tussen data vereenvoudig, is reeds 'n pluspunt, maar die resulterende databasis is 3 keer kleiner in volume.
Ook, volgens die toetse wat gedoen is, kan ons aflei dat die prestasieverlies baie klein is. In sommige gevalle is JSONB selfs vinniger as EAV, wat dit selfs beter maak. Hierdie maatstaf dek egter nie natuurlik alle aspekte nie (bv. entiteite met 'n baie groot aantal eiendomme, 'n aansienlike toename in die aantal eienskappe van bestaande data,...), so as jy enige voorstelle het oor hoe om dit te verbeter, asseblief los gerus in die kommentaar!
Bron: will.com
