TL; DR: JSONB kan i høj grad forenkle databaseskemaudvikling uden at ofre forespørgselsydeevne.
Indledning
Lad os give et klassisk eksempel på sandsynligvis en af de ældste use cases i verden af en relationel database (database): vi har en entitet, og vi skal gemme visse egenskaber (attributter) af denne enhed. Men ikke alle forekomster har muligvis det samme sæt egenskaber, og flere egenskaber kan blive tilføjet i fremtiden.
Den nemmeste måde at løse dette problem på er at oprette en kolonne i databasetabellen for hver egenskabsværdi og blot udfylde dem, der er nødvendige for en specifik enhedsforekomst. Store! Problem løst... indtil din tabel indeholder millioner af poster, og du skal tilføje en ny post.
Overvej EAV-mønsteret (), forekommer det ret ofte. En tabel indeholder enheder (poster), en anden tabel indeholder egenskabsnavne (attributter), og en tredje tabel knytter enheder til deres attributter og indeholder værdien af disse attributter for den aktuelle enhed. Dette giver dig mulighed for at have forskellige sæt egenskaber for forskellige objekter, og også tilføje egenskaber på farten uden at ændre databasestrukturen.
Jeg ville dog ikke skrive dette indlæg, hvis der ikke var nogle ulemper ved EVA-tilgangen. Så for eksempel, for at opnå en eller flere entiteter, der har 1 attribut hver, kræves 2 joins i forespørgslen: den første er en joinforbindelse med attributtabellen, den anden er en joinforbindelse med værditabellen. Hvis en enhed har 2 attributter, er der brug for 4 joins! Derudover gemmes alle attributter typisk som strenge, hvilket resulterer i typecasting for både resultatet og WHERE-sætningen. Hvis du skriver mange forespørgsler, så er dette ret spild i forhold til ressourceforbrug.
På trods af disse åbenlyse mangler har EAV længe været brugt til at løse denne type problemer. Det var uundgåelige mangler, og der var simpelthen ikke noget bedre alternativ.
Men så dukkede en ny "teknologi" op i PostgreSQL...
Startende med PostgreSQL 9.4 blev JSONB-datatypen tilføjet for at gemme binære JSON-data. Selvom lagring af JSON i dette format typisk tager lidt mere plads og tid end almindelig tekst JSON, er det meget hurtigere at udføre operationer på det. JSONB understøtter også indeksering, hvilket gør forespørgsler endnu hurtigere.
JSONB-datatypen giver os mulighed for at erstatte det besværlige EAV-mønster ved kun at tilføje én JSONB-kolonne til vores enhedstabel, hvilket i høj grad forenkler databasedesign. Men mange argumenterer for, at dette bør ledsages af et fald i produktiviteten... Det er derfor, jeg skrev denne artikel.
Opsætning af testdatabase
Til denne sammenligning oprettede jeg databasen på en ny installation af PostgreSQL 9.5 på $80 build Ubuntu 14.04 Efter at have konfigureret nogle parametre i postgresql.conf kørte jeg script ved hjælp af psql. Følgende tabeller blev oprettet for at præsentere dataene i EAV-form:
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
);
Nedenfor er en tabel, hvor de samme data vil blive gemt, men med attributter i en JSONB type kolonne – egenskaber.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Ser meget enklere ud, ikke? Derefter blev det tilføjet til enhedstabellerne (enhed & entity_jsonb) 10 millioner poster, og derfor blev tabellen fyldt med de samme data ved hjælp af EAV-mønsteret og tilgangen med en JSONB-kolonne - entity_jsonb.properties. Vi modtog således flere forskellige datatyper blandt hele sættet af egenskaber. Eksempeldata:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Så nu har vi de samme data for begge muligheder. Lad os begynde at sammenligne implementeringer på arbejdet!
Forenkle dit design
Det blev tidligere udtalt, at databasedesignet var meget forenklet: en tabel, ved at bruge en JSONB-kolonne til egenskaber, i stedet for at bruge tre tabeller til EAV. Men hvordan afspejles dette i anmodninger? Opdatering af en enhedsejendom ser sådan ud:
-- 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;
Som du kan se, ser den sidste anmodning ikke nemmere ud. For at opdatere værdien af en egenskab i et JSONB-objekt skal vi bruge funktionen , og skulle videregive vores nye værdi som et JSONB-objekt. Vi behøver dog ikke kende nogen identifikator på forhånd. Når vi ser på EAV-eksemplet, skal vi kende både entity_id og entity_attribute_id for at kunne udføre opdateringen. Hvis du ønsker at opdatere en egenskab i en JSONB-kolonne baseret på objektnavnet, så foregår det hele på én enkel linje.
Lad os nu vælge den enhed, vi lige har opdateret baseret på dens nye farve:
-- 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';
Jeg tror, vi kan blive enige om, at den anden er kortere (ingen join!), og derfor mere læsbar. JSONB vinder her! Vi bruger JSON ->> operatoren til at få farven som en tekstværdi fra JSONB objektet. Der er også en anden måde at opnå det samme resultat i JSONB-modellen ved at bruge @>-operatoren:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Dette er lidt mere kompliceret: vi kontrollerer, om JSON-objektet i dets egenskabskolonne indeholder et objekt, der er til højre for @>-operatoren. Mindre læsbar, mere produktiv (se nedenfor).
Lad os gøre brugen af JSONB endnu nemmere, når du skal vælge flere egenskaber på én gang. Det er her, JSONB-tilgangen virkelig kommer ind: vi vælger simpelthen egenskaber som yderligere kolonner i vores resultatsæt uden behov for joinforbindelser:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Med EAV skal du bruge 2 joins for hver ejendom, du vil forespørge på. Efter min mening viser ovenstående forespørgsler en stor forenkling i databasedesign. Se flere eksempler på, hvordan du skriver forespørgsler til JSONB, også muligt i stolpe.
Nu er det tid til at tale om præstation.
Ydelse
For at sammenligne ydeevne brugte jeg i forespørgsler, for at beregne udførelsestid. Hver forespørgsel blev udført mindst tre gange, fordi forespørgselsplanlæggeren tager længere tid første gang. Først kørte jeg forespørgslerne uden nogen indekser. Dette var naturligvis en fordel ved JSONB, da de joinforbindelser, der kræves til EAV, ikke kunne bruge indekser (udenlandske nøglefelter blev ikke indekseret). Herefter oprettede jeg et indeks på de 2 fremmednøglekolonner i EAV-værditabellen, samt et indeks for en JSONB-kolonne.
Dataopdateringen viste følgende resultater i form af tid (i ms). Bemærk at skalaen er logaritmisk:
Vi ser, at JSONB er meget (> 50000-x) hurtigere end EAV, hvis du ikke bruger indekser, af ovennævnte grund. Når vi indekserer kolonner med primærnøgler, forsvinder forskellen næsten, men JSONB er stadig 1,3 gange hurtigere end EAV. Bemærk, at indekset på JSONB-kolonnen ikke har nogen effekt her, da vi ikke bruger egenskabskolonnen i evalueringskriterierne.
For at vælge data baseret på ejendomsværdi får vi følgende resultater (normal skala):
Du kan bemærke, at JSONB igen virker hurtigere end EAV uden indekser, men når EAV med indekser, virker det stadig hurtigere end JSONB. Men så så jeg, at tiderne for JSONB-forespørgsler var de samme, dette fik mig til at se, at GIN-indekser ikke virker. Når du bruger et GIN-indeks på en kolonne med udfyldte egenskaber, træder det åbenbart kun i kraft, når du bruger include-operatoren @>. Jeg brugte dette i en ny test, og det havde en enorm indflydelse på tiden: kun 0,153 ms! Dette er 15000 gange hurtigere end EAV og 25000 gange hurtigere end ->> operatøren.
Jeg synes det var hurtigt nok!
Databasetabelstørrelse
Lad os sammenligne tabelstørrelserne for begge tilgange. I psql kan vi vise størrelsen af alle tabeller og indekser ved hjælp af kommandoen dti+
For EAV-tilgangen er tabelstørrelser omkring 3068 MB og indekser op til 3427 MB for i alt 6,43 GB. JSONB-tilgangen bruger 1817 MB til bordet og 318 MB til indekserne, hvilket er 2,08 GB. Det viser sig 3 gange mindre! Dette faktum overraskede mig lidt, fordi vi gemmer ejendomsnavne i hvert JSONB-objekt.
Men alligevel taler tallene for sig selv: I EAV gemmer vi 2 heltals fremmednøgler pr. attributværdi, hvilket resulterer i 8 bytes yderligere data. Derudover gemmer EAV alle ejendomsværdier som tekst, mens JSONB vil bruge numeriske og boolske værdier internt, hvor det er muligt, hvilket resulterer i et mindre fodaftryk.
Resultaterne af
Samlet set tror jeg, at lagring af enhedsegenskaber i JSONB-format kan gøre design og vedligeholdelse af din database meget nemmere. Hvis du kører mange forespørgsler, vil det faktisk fungere mere effektivt at holde alt i samme tabel som enheden. Og det faktum, at dette forenkler interaktionen mellem data, er allerede et plus, men den resulterende database er 3 gange mindre i volumen.
Baseret på de udførte tests kan vi også konkludere, at ydeevnetabene er meget ubetydelige. I nogle tilfælde er JSONB endnu hurtigere end EAV, hvilket gør det endnu bedre. Men dette benchmark dækker naturligvis ikke alle aspekter (f.eks. enheder med et meget stort antal egenskaber, en betydelig stigning i antallet af egenskaber for eksisterende data,...), så hvis du har forslag til hvordan man kan forbedre dem , du er velkommen til at skrive i kommentarerne!
Kilde: www.habr.com
