TL; DR: JSONB kan avsevÀrt förenkla utvecklingen av databasstrukturer utan att kompromissa med prestanda i frÄgorna.
Introduktion
LÄt oss ge ett klassiskt exempel, troligen en av de Àldsta anvÀndningarna i vÀrlden av relationsdatabaser (databaser): vi har en entitet och behöver spara specifika egenskaper (attribut) för denna entitet. Men inte alla instanser kan ha samma uppsÀttning egenskaper, och dessutom kan det i framtiden finnas behov av att lÀgga till fler egenskaper.
Det enklaste sÀttet att lösa detta problem Àr att skapa en kolumn i databastabellen för varje vÀrde av egenskapen och helt enkelt fylla i de som behövs för den specifika instansen av entiteten. UtmÀrkt! Problemet löst... tills dess att din tabell innehÄller miljontals poster och du plötsligt behöver lÀgga till en ny post.
LÄt oss övervÀga EAV-mönstret (), som förekommer ganska ofta. En tabell innehÄller entiteter (poster), en annan tabell innehÄller namnen pÄ egenskaper (attribut), och en tredje tabell kopplar entiteter till deras attribut och innehÄller vÀrdet av dessa attribut för den aktuella entiteten. Detta ger dig möjlighet att ha olika uppsÀttningar egenskaper för olika objekt, samt att lÀgga till egenskaper "on the fly" utan att förÀndra databasstrukturen.
Trots detta skulle jag inte ha skrivit denna anteckning om det inte fanns nackdelar med att anvĂ€nda EAV. Till exempel, för att hĂ€mta en eller flera entiteter som har 1 attribut, krĂ€vs det 2 joinar i frĂ„gan: den första â en sammanslagning med attributtabellen, den andra â en sammanslagning med vĂ€rdestabellen. Om entiteten har 2 attribut krĂ€vs det redan 4 joinar! Dessutom lagras alla attribut vanligtvis som strĂ€ngar, vilket leder till typkonvertering bĂ„de för resultatet och för WHERE-villkoret. Om du skriver mĂ„nga frĂ„gor Ă€r det ganska resurskrĂ€vande.
Trots dessa uppenbara nackdelar, har EAV lÀnge anvÀnts för att lösa den hÀr typen av problem. Det har varit oundvikliga nackdelar, och det har helt enkelt inte funnits nÄgon bÀttre alternativ.
Men sedan dök en ny "teknologi" upp i PostgreSQL...
Sedan PostgreSQL 9.4 lades datatypen JSONB till för att lagra binĂ€ra JSON-data. Ăven om lagring av JSON i detta format vanligtvis tar lite mer utrymme och tid jĂ€mfört med enkel textbaserad JSON, gĂ„r operationer med det mycket snabbare. JSONB stöder ocksĂ„ indexering, vilket gör förfrĂ„gningar Ă€nnu snabbare.
Datatypen JSONB möjliggör att vi kan ersÀtta det omfattande EAV-mönstret genom att lÀgga till endast en JSONB-kolumn i vÄr entitetstabell, vilket förenklar databasdesignen avsevÀrt. Men mÄnga hÀvdar att detta bör Ätföljas av prestandanedgÄng... Det Àr just hÀr denna artikel kommer in.
InstÀllning av testdatabas
För denna jÀmförelse skapade jag en databas pÄ en ny installation av PostgreSQL 9.5 pÄ en 80-dollarsuppsÀttning Ubuntu 14.04. Efter att ha justerat vissa parametrar i postgresql.conf körde jag ett skript med hjÀlp av psql. För att representera data i EAV-format skapades följande tabeller:
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
);
Nedan finns en tabell dĂ€r samma data kommer att lagras, men med attribut i en JSONB-kolumn â properties.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Ser mycket enklare ut, eller hur? Sedan lades 10 miljoner poster till i entitetstabellerna (entity & entity_jsonb) och motsvarande fyllts med samma data i tabellerna dĂ€r EAV-mönstret anvĂ€nds och tillvĂ€gagĂ„ngssĂ€ttet med JSONB-kolumnen â entity_jsonb.properties. SĂ„ledes fick vi flera olika datatyper bland hela uppsĂ€ttningen av egenskaper. Exempeldata:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
length: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}SÄ, nu har vi samma data för tvÄ alternativ. LÄt oss börja jÀmföra implementationerna i arbete!
Förenkling av design
Det har redan nÀmnts att databasdesignen har förenklats avsevÀrt: en tabell, tack vare anvÀndningen av en JSONB-kolumn för egenskaper, istÀllet för att anvÀnda tre tabeller för EAV. Men hur pÄverkar detta förfrÄgningarna? Uppdatering av en egenskap för en entitet ser ut som följer:
-- EAV
UPDATE entity_attribute_value
SET value = 'blÄ'
WHERE entity_attribute_id = 1
AND entity_id = 120;
-- JSONB
UPDATE entity_jsonb
SET properties = jsonb_set(properties, '{"color"}', '"blÄ"')
WHERE id = 120;
Som vi ser, ser inte den sista frÄgan enklare ut. För att uppdatera egenskapsvÀrdet i JSONB-objektet mÄste vi anvÀnda funktionen , och vi mÄste skicka vÄrt nya vÀrde som ett JSONB-objekt. à andra sidan behöver vi inte veta nÄgot identifierare i förvÀg. Tittar vi pÄ exemplet med EAV behöver vi bÄde entity_id och entity_attribute_id för att utföra uppdateringen. Om du vill uppdatera en egenskap i JSONB-kolumnen baserat pÄ objektets namn, sÄ görs detta med en enkel rad.
LÄt oss nu vÀlja den entitet vi just uppdaterade baserat pÄ dess nya fÀrg:
-- 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 = 'blÄ';
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties ->> 'color' = 'blÄ';
Jag tror vi kan enas om att det andra Àr kortare (utan join!), och dÀrmed mer lÀsbart. HÀr vinner JSONB! Vi anvÀnder JSON-operatören ->> för att fÄ fÀrgen som ett textvÀrde frÄn JSONB-objektet. Det finns ocksÄ ett andra sÀtt att uppnÄ samma resultat i JSONB-modellen med operatören @> :
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blÄ"}';
Detta Àr lite mer komplicerat: vi kontrollerar om JSON-objektet i kolumnens egenskaper innehÄller objektet som finns till höger om operatören @>. Mindre lÀsbart, mer effektivt (se nedan).
LÄt oss förenkla anvÀndningen av JSONB Ànnu mer nÀr du behöver vÀlja flera egenskaper samtidigt. HÀr passar verkligen JSONB-ansatsen: vi vÀljer helt enkelt egenskaper som extra kolumner i vÄr resultatuppsÀttning utan behov av joins:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Med EAV behöver du 2 joins för varje egenskap som du vill frÄga. Enligt min mening visar ovanstÄende frÄgor en stor förenkling i databasdesignen. Du kan Àven se fler exempel pÄ hur man skriver frÄgor mot JSONB i inlÀgget.
Nu Àr det dags att prata om prestanda.
Performance
För att jÀmföra prestanda anvÀnde jag i förfrÄgningar, för att berÀkna exekveringstiden. Varje förfrÄgan kördes minst tre gÄnger, eftersom planeringsverktyget krÀver mer tid första gÄngen. Först utförde jag förfrÄgningarna utan nÄgra index. Uppenbarligen gav detta JSONB en fördel, eftersom joins som behövs för EAV inte kunde anvÀnda index (fÀlten med frÀmmande nycklar var inte indexerade). Efter detta skapade jag ett index för tvÄ kolumner av frÀmmande nycklar i EAV-vÀrdestabellen, samt ett index för JSONB-kolumnen.
Uppdateringar av data visade följande resultat för tiden (i ms). Observera att skalan Àr logaritmisk:
Vi ser att JSONB Àr mycket (> 50000 gÄnger) snabbare Àn EAV nÀr index inte anvÀnds, av anledningen som nÀmndes ovan. NÀr vi indexerar kolumner med primÀra nycklar försvinner nÀstan skillnaden, men JSONB Àr fortfarande 1,3 gÄnger snabbare Àn EAV. Observera att indexet för JSONB-kolumnen hÀr inte har nÄgon pÄverkan, eftersom vi inte anvÀnder egenskapskolumnen i utvÀrderingskriterierna.
För att hÀmta data baserat pÄ ett egenskapsvÀrde fÄr vi följande resultat (normal skala):
Det Àr tydligt att JSONB Äterigen fungerar snabbare Àn EAV utan index, men nÀr EAV har index Àr den fortfarande snabbare Àn JSONB. Men sedan sÄg jag att tiden för JSONB-förfrÄgningarna var densamma, vilket ledde mig till insikten att GIN-indexet inte aktiveras. Uppenbarligen, nÀr du anvÀnder GIN-index för en kolumn med fyllda egenskaper, fungerar det bara vid anvÀndning av inklusionsoperatorn @>. Jag anvÀnde detta i ett nytt test, vilket hade en enorm pÄverkan pÄ tiden: endast 0,153 ms! Det Àr 15000 gÄnger snabbare Àn EAV och 25000 gÄnger snabbare Àn operatorn ->>.
Jag tycker att det var tillrÀckligt snabbt!
Storleken pÄ databastabeller
LÄt oss jÀmföra storlekarna pÄ tabellerna med bÄda metoderna. I psql kan vi visa storleken för alla tabeller och index genom att anvÀnda kommandot dti+
För EAV-metoden Àr storleken pÄ tabellerna cirka 3068 MB och indexen upp till 3427 MB, vilket ger totalt 6,43 GB. Vid anvÀndning av JSONB-metoden anvÀnds 1817 MB för tabellen och 318 MB för indexen, vilket innebÀr 2,08 GB. Det blir tre gÄnger mindre! Denna siffra överraskade mig nÄgot, eftersom vi lagrar namnen pÄ egenskaperna i varje JSONB-objekt.
Men siffrorna talar för sig sjÀlva: i EAV lagrar vi 2 heltal externa nycklar för attributets vÀrde, vilket resulterar i 8 byte ytterligare data. Dessutom lagras alla egenskapsvÀrden i EAV i text, medan JSONB kommer att anvÀnda numeriska och logiska vÀrden inom de omrÄden dÀr det Àr möjligt, vilket ger en mindre datastorlek.
Sammanfattning
Generellt sett anser jag att lagringen av entitets egenskaper i JSONB-formatet kan förenkla design och underhÄll av din databas avsevÀrt. Om du utför mÄnga frÄgor, kommer allt som lagras i en tabell med entiteten verkligen att fungera mer effektivt. Och det faktum att det underlÀttar interaktionen mellan data Àr redan en fördel, men den resulterande databasen Àr tre gÄnger mindre i storlek.
Dessutom, baserat pÄ genomförda tester kan slutsatsen dras att prestandaförlusterna Àr mycket obetydliga. I vissa fall fungerar JSONB till och med snabbare Àn EAV, vilket gör det Ànnu bÀttre. Men detta referenstest tÀcker givetvis inte alla aspekter (t.ex. entiteter med mycket stora egenskaper, en betydande ökning av antalet egenskaper för existerande data,...), sÄ om du har nÄgra förslag pÄ hur de kan förbÀttras, tveka inte att lÀmna en kommentar!
KĂ€lla: habr.com
