TL; DR: JSONB lahko močno poenostavi razvoj sheme baze podatkov, ne da bi pri tem žrtvoval zmogljivost poizvedb.
Predstavitev
Naj navedemo klasičen primer verjetno enega najstarejših primerov uporabe na svetu relacijske baze podatkov (baze podatkov): imamo entiteto in moramo shraniti določene lastnosti (atribute) te entitete. Toda vsi primerki morda nimajo enakega nabora lastnosti in v prihodnosti bo morda dodanih več lastnosti.
To težavo najlažje rešite tako, da ustvarite stolpec v tabeli zbirke podatkov za vsako vrednost lastnosti in preprosto izpolnite tiste, ki so potrebne za določen primerek entitete. Super! Težava rešena ... dokler vaša tabela ne vsebuje več milijonov zapisov in morate dodati nov zapis.
Upoštevajte vzorec EAV (), se pojavlja precej pogosto. Ena tabela vsebuje entitete (zapise), druga tabela vsebuje imena lastnosti (atribute), tretja tabela pa povezuje entitete z njihovimi atributi in vsebuje vrednost teh atributov za trenutno entiteto. To vam daje možnost, da imate različne nize lastnosti za različne objekte in tudi dodajate lastnosti sproti, ne da bi spremenili strukturo baze podatkov.
Vendar pa ne bi pisal te objave, če ne bi bilo nekaj slabosti pristopa EVA. Če želite na primer pridobiti eno ali več entitet, ki imajo vsaka po 1 atribut, sta v poizvedbi potrebni 2 združitvi: prva je združitev s tabelo atributov, druga je združitev s tabelo vrednosti. Če ima entiteta 2 atributa, so potrebni 4 spoji! Poleg tega so vsi atributi običajno shranjeni kot nizi, kar povzroči pretvorbo tipa tako za rezultat kot za stavek WHERE. Če pišete veliko poizvedb, je to precej potratno z vidika porabe virov.
Kljub tem očitnim pomanjkljivostim se EAV že dolgo uporablja za reševanje tovrstnih težav. To so bile neizogibne pomanjkljivosti in boljše alternative enostavno ni bilo.
Potem pa se je v PostgreSQL pojavila nova "tehnologija" ...
Začenši s PostgreSQL 9.4 je bil dodan podatkovni tip JSONB za shranjevanje binarnih podatkov JSON. Čeprav shranjevanje JSON v tem formatu običajno vzame malo več prostora in časa kot JSON z navadnim besedilom, je izvajanje operacij na njem veliko hitrejše. JSONB podpira tudi indeksiranje, zaradi česar so poizvedbe še hitrejše.
Podatkovni tip JSONB nam omogoča, da nadomestimo okoren vzorec EAV z dodajanjem samo enega stolpca JSONB v našo entitetno tabelo, kar močno poenostavi načrtovanje baze podatkov. Toda mnogi trdijo, da bi to moralo spremljati zmanjšanje produktivnosti ... Zato sem napisal ta članek.
Nastavitev testne baze podatkov
Za to primerjavo sem ustvaril zbirko podatkov na novi namestitvi PostgreSQL 9.5 v gradnji za 80 USD Ubuntu 14.04 Po konfiguraciji nekaterih parametrov v postgresql.conf sem zagnal skript z uporabo psql. Za predstavitev podatkov v obliki EAV so bile ustvarjene naslednje tabele:
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
);
Spodaj je tabela, kjer bodo shranjeni isti podatki, vendar z atributi v stolpcu vrste JSONB – Lastnosti.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Videti je veliko bolj preprosto, kajne? Nato je bil dodan v tabele entitet (entiteta & entiteta_jsonb) 10 milijonov zapisov, zato je bila tabela napolnjena z enakimi podatki z uporabo vzorca EAV in pristopa s stolpcem JSONB - entity_jsonb.properties. Tako smo med celotnim naborom lastnosti prejeli več različnih tipov podatkov. Primer podatkov:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Tako imamo sedaj enake podatke za obe možnosti. Začnimo primerjati izvedbe na delu!
Poenostavite svoj dizajn
Prej je bilo navedeno, da je bila zasnova baze podatkov močno poenostavljena: ena tabela z uporabo stolpca JSONB za lastnosti, namesto uporabe treh tabel za EAV. Kako pa se to odraža v zahtevah? Posodabljanje ene lastnosti entitete izgleda takole:
-- 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;
Kot lahko vidite, zadnja zahteva ni videti preprostejša. Za posodobitev vrednosti lastnosti v objektu JSONB moramo uporabiti funkcijo , in mora prenesti našo novo vrednost kot objekt JSONB. Vendar pa nam ni treba vnaprej poznati nobenega identifikatorja. Če pogledamo primer EAV, moramo poznati tako entity_id kot entity_attribute_id, da lahko izvedemo posodobitev. Če želite posodobiti lastnost v stolpcu JSONB na podlagi imena objekta, potem je vse narejeno v eni preprosti vrstici.
Zdaj pa izberimo entiteto, ki smo jo pravkar posodobili, glede na njeno novo barvo:
-- 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';
Mislim, da se lahko strinjamo, da je drugi krajši (brez pridružitve!) in zato bolj berljiv. JSONB tukaj zmaga! Uporabimo operator JSON ->>, da dobimo barvo kot besedilno vrednost iz predmeta JSONB. Obstaja tudi drugi način za doseganje enakega rezultata v modelu JSONB z uporabo operatorja @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
To je nekoliko bolj zapleteno: preverimo, ali objekt JSON v svojem stolpcu lastnosti vsebuje objekt, ki je desno od operatorja @>. Manj berljivo, bolj produktivno (glejte spodaj).
Poskrbimo, da bo uporaba JSONB še lažja, ko boste morali izbrati več lastnosti hkrati. Tukaj pristop JSONB resnično nastopi: preprosto izberemo lastnosti kot dodatne stolpce v našem nizu rezultatov brez potrebe po združevanjih:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Z EAV boste potrebovali 2 združitvi za vsako lastnost, ki jo želite poizvedovati. Po mojem mnenju zgornje poizvedbe kažejo veliko poenostavitev zasnove baze podatkov. Oglejte si več primerov, kako napisati poizvedbe JSONB, tudi v post.
Zdaj je čas za pogovor o uspešnosti.
Produktivnost
Za primerjavo uspešnosti sem uporabil v poizvedbah za izračun časa izvajanja. Vsaka poizvedba je bila izvedena vsaj trikrat, ker načrtovalec poizvedb prvič vzame več časa. Najprej sem zagnal poizvedbe brez kakršnih koli indeksov. Očitno je bila to prednost JSONB, saj spoji, potrebni za EAV, niso mogli uporabljati indeksov (tuja ključna polja niso bila indeksirana). Po tem sem ustvaril indeks za 2 stolpca tujih ključev tabele vrednosti EAV, pa tudi indeks za stolpec JSONB.
Posodobitev podatkov je glede na čas (v ms) pokazala naslednje rezultate. Upoštevajte, da je lestvica logaritemska:
Vidimo, da je JSONB veliko (> 50000-x) hitrejši od EAV, če ne uporabljate indeksov, iz zgoraj navedenega razloga. Ko indeksiramo stolpce s primarnimi ključi, razlika skoraj izgine, vendar je JSONB še vedno 1,3-krat hitrejši od EAV. Upoštevajte, da indeks v stolpcu JSONB tukaj nima vpliva, ker v merilih ocenjevanja ne uporabljamo stolpca lastnosti.
Za izbiro podatkov glede na vrednost lastnosti dobimo naslednje rezultate (normalna lestvica):
Opazite lahko, da JSONB spet deluje hitreje kot EAV brez indeksov, ko pa EAV z indeksi, še vedno deluje hitreje kot JSONB. Potem pa sem videl, da so časi za poizvedbe JSONB enaki, kar me je spodbudilo k dejstvu, da indeksi GIN ne delujejo. Očitno, ko uporabite indeks GIN v stolpcu s poseljenimi lastnostmi, začne veljati le, če uporabite operator vključitve @>. To sem uporabil v novem testu in imelo je velik vpliv na čas: samo 0,153 ms! To je 15000-krat hitreje od EAV in 25000-krat hitreje od operaterja ->>.
Mislim, da je bilo dovolj hitro!
Velikost tabele baze podatkov
Primerjajmo velikosti tabel za oba pristopa. V psql lahko z ukazom prikažemo velikost vseh tabel in indeksov dti+
Za pristop EAV so velikosti tabel okoli 3068 MB in indeksi do 3427 MB za skupno 6,43 GB. Pristop JSONB uporablja 1817 MB za tabelo in 318 MB za indekse, kar je 2,08 GB. Izkazalo se je 3-krat manj! To dejstvo me je nekoliko presenetilo, ker imena lastnosti shranjujemo v vsakem objektu JSONB.
Še vedno pa številke govorijo same zase: v EAV shranimo 2 cela tuja ključa na vrednost atributa, kar ima za posledico 8 bajtov dodatnih podatkov. Poleg tega EAV shrani vse vrednosti lastnosti kot besedilo, medtem ko bo JSONB interno uporabil številske in logične vrednosti, kjer je to mogoče, kar ima za posledico manjši odtis.
Rezultati
Na splošno menim, da lahko shranjevanje lastnosti entitete v formatu JSONB močno olajša oblikovanje in vzdrževanje vaše baze podatkov. Če izvajate veliko poizvedb, bo obdržanje vsega v isti tabeli kot entiteta dejansko delovalo učinkoviteje. In dejstvo, da to poenostavi interakcijo med podatki, je že plus, vendar je nastala baza podatkov 3-krat manjša po obsegu.
Tudi na podlagi izvedenih testov lahko sklepamo, da so izgube zmogljivosti zelo nepomembne. V nekaterih primerih je JSONB celo hitrejši od EAV, zaradi česar je še boljši. Vendar to primerjalno merilo seveda ne zajema vseh vidikov (npr. entitete z zelo velikim številom lastnosti, znatno povečanje števila lastnosti obstoječih podatkov,...), tako da, če imate predloge, kako jih izboljšati , vas prosimo, da to pustite v komentarjih!
Vir: www.habr.com
