TL; DR: JSONB kin de ûntwikkeling fan databaseskema gâns ferienfâldigje sûnder query-prestaasjes op te offerjen.
Ynlieding
Litte wy in klassyk foarbyld jaan fan wierskynlik ien fan 'e âldste gebrûksgefallen yn' e wrâld fan in relationele databank (databank): wy hawwe in entiteit, en wy moatte bepaalde eigenskippen (attributen) fan dizze entiteit bewarje. Mar net alle eksimplaren meie hawwe deselde set fan eigenskippen, en mear eigenskippen kinne wurde tafoege yn 'e takomst.
De maklikste manier om dit probleem op te lossen is in kolom te meitsjen yn 'e databanktabel foar elke eigendomwearde, en folje gewoan dejingen yn dy't nedich binne foar in spesifike entiteitseksimplaar. Grut! Probleem oplost ... oant jo tabel miljoenen records befettet en jo moatte in nij record tafoegje.
Tink oan it EAV-patroan (), komt it frij faak foar. Ien tabel befettet entiteiten (records), in oare tabel befettet eigendom nammen (attributen), en in tredde tabel assosjearret entiteiten mei harren attributen en befettet de wearde fan dy attributen foar de hjoeddeiske entiteit. Dit jout jo de mooglikheid om te hawwen ferskillende sets fan eigenskippen foar ferskillende objekten, en ek tafoegje eigenskippen op 'e fly sûnder feroaring fan de databank struktuer.
Ik soe dit berjocht lykwols net skriuwe as d'r net wat neidielen wiene oan 'e EVA-oanpak. Sa, bygelyks, om ien of mear entiteiten te krijen dy't elk 1 attribút hawwe, binne 2 joins nedich yn 'e query: de earste is in join mei de attribúttabel, de twadde is in join mei de weardentabel. As in entiteit 2 attributen hat, dan binne 4 joins nedich! Derneist wurde alle attributen typysk opslein as snaren, wat resulteart yn typecasting foar sawol it resultaat as de WHERE-klausule. As jo in protte fragen skriuwe, dan is dit frijwat fergriemend yn termen fan boarnegebrûk.
Nettsjinsteande dizze foar de hân lizzende tekortkomingen, is EAV al lang brûkt om dizze soarten problemen op te lossen. Dit wiene ûnûntkombere tekoarten, en d'r wie gewoan gjin better alternatyf.
Mar doe ferskynde in nije "technology" yn PostgreSQL ...
Begjin mei PostgreSQL 9.4 waard it JSONB-gegevenstype tafoege om JSON-binêre gegevens op te slaan. Hoewol it opslaan fan JSON yn dit formaat typysk in bytsje mear romte en tiid nimt dan JSON mei platte tekst, it útfieren fan operaasjes derop is folle flugger. JSONB stipet ek yndeksearring, wat fragen noch rapper makket.
It JSONB-gegevenstype lit ús it omslachtige EAV-patroan ferfange troch mar ien JSONB-kolom ta te foegjen oan ús entiteitstabel, wat database-ûntwerp sterk ferienfâldigje. Mar in protte stelle dat dit moat wurde begelaat troch in fermindering fan produktiviteit ... Dêrom skreau ik dit artikel.
It opsetten fan in testdatabase
Foar dizze fergeliking haw ik de database makke op in frisse ynstallaasje fan PostgreSQL 9.5 op 'e $ 80 build Ubuntu 14.04. Nei it ynstellen fan guon parameters yn postgresql.conf ik rûn skript mei psql. De folgjende tabellen binne makke om de gegevens yn EAV-foarm te presintearjen:
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
);
Hjirûnder is in tabel dêr't deselde gegevens sille wurde opslein, mar mei attributen yn in JSONB-type kolom - eigenskippen.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
It sjocht der folle ienfâldiger út, is it net? Dêrnei waard it tafoege oan de entiteitstabellen (entity & entity_jsonb) 10 miljoen records, en dêrtroch waard de tabel fol mei deselde gegevens mei it EAV-patroan en de oanpak mei in JSONB-kolom - entity_jsonb.properties. Sa krigen wy ferskate ferskillende gegevenstypen ûnder de heule set eigenskippen. Foarbyld data:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Dus no hawwe wy deselde gegevens foar beide opsjes. Litte wy ymplementaasjes op it wurk begjinne te fergelykjen!
Ienfâldigje jo ûntwerp
It waard earder oanjûn dat it databankûntwerp sterk ferienfâldige wie: ien tabel, troch in JSONB-kolom te brûken foar eigenskippen, ynstee fan trije tabellen foar EAV te brûken. Mar hoe wurdt dit wjerspegele yn fersiken? It bywurkjen fan ien entiteitseigendom sjocht der sa út:
-- 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;
Sa't jo sjen kinne, liket it lêste fersyk net ienfâldiger. Om de wearde fan in eigendom yn in JSONB-objekt te aktualisearjen moatte wy de funksje brûke , en moat ús nije wearde trochjaan as in JSONB-objekt. Wy hoege lykwols gjin identifier fan tefoaren te witten. As wy nei it EAV-foarbyld sjogge, moatte wy sawol de entity_id as de entity_attribute_id witte om de fernijing út te fieren. As jo in eigendom wolle bywurkje yn in JSONB-kolom basearre op 'e objektnamme, dan wurdt it allegear dien yn ien ienfâldige rigel.
Litte wy no de entiteit selektearje dy't wy krekt bywurke hawwe op basis fan syn nije 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';
Ik tink dat wy it iens wêze kinne dat de twadde koarter is (gjin join!), En dus lêsberder. JSONB wint hjir! Wy brûke de operator JSON ->> om de kleur te krijen as tekstwearde fan in JSONB-objekt. D'r is ek in twadde manier om itselde resultaat te berikken yn it JSONB-model mei de @> operator:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Dit is in bytsje komplisearre: wy kontrolearje om te sjen oft it JSON-objekt yn syn eigenskippenkolom in objekt befettet dat rjochts fan de @>-operator is. Minder lêsber, produktiver (sjoch hjirûnder).
Litte wy it brûken fan JSONB noch makliker meitsje as jo meardere eigenskippen tagelyk moatte selektearje. Dit is wêr't de JSONB-oanpak echt ynkomt: wy selektearje gewoan eigenskippen as ekstra kolommen yn ús resultaatset sûnder de needsaak foar joins:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Mei EAV sille jo 2 joins nedich wêze foar elk pân dat jo wolle freegje. Yn myn miening litte de boppesteande fragen in geweldige ferienfâldiging sjen yn database-ûntwerp. Sjoch mear foarbylden fan hoe't jo JSONB-fragen skriuwe, ek yn peal.
No is it tiid om te praten oer prestaasjes.
Produktiviteit
Om prestaasjes te fergelykjen haw ik brûkt yn queries, te berekkenjen útfiering tiid. Elke query is op syn minst trije kear útfierd, om't de queryplanner de earste kear langer duorret. Earst rûn ik de queries sûnder yndeksen. Fansels wie dit in foardiel fan JSONB, om't de joins dy't nedich binne foar EAV gjin yndeksen kinne brûke (bûtenlânske kaaifjilden waarden net yndeksearre). Hjirnei haw ik in yndeks makke op 'e 2 bûtenlânske kaaikolommen fan' e EAV-weardetabel, lykas ek in yndeks foar in JSONB kolom.
De gegevensfernijing liet de folgjende resultaten sjen yn termen fan tiid (yn ms). Tink derom dat de skaal logaritmysk is:
Wy sjogge dat JSONB folle (> 50000-x) flugger is as EAV as jo gjin yndeksen brûke, om de hjirboppe neamde reden. As wy yndeksearje kolommen mei primêre kaaien, ferdwynt it ferskil hast, mar JSONB is noch altyd 1,3 kear flugger as EAV. Tink derom dat de yndeks op 'e JSONB-kolom hjir gjin effekt hat, om't wy de eigendomkolom net brûke yn' e evaluaasjekritearia.
Foar it selektearjen fan gegevens basearre op eigendomwearde krije wy de folgjende resultaten (normale skaal):
Jo kinne merke dat JSONB wer flugger wurket as EAV sûnder yndeksen, mar as EAV mei yndeksen wurket, wurket it noch hurder as JSONB. Mar doe seach ik dat de tiden foar JSONB-fragen itselde wiene, dit joech my oan it feit dat GIN-yndeksen net wurkje. Blykber as jo in GIN-yndeks brûke op in kolom mei befolke eigenskippen, nimt it allinich effekt by it brûken fan de include-operator @>. Ik brûkte dit yn in nije test en it hie in enoarme ynfloed op 'e tiid: mar 0,153ms! Dit is 15000 kear rapper dan EAV en 25000 kear flugger dan de ->> operator.
Ik tink dat it wie fluch genôch!
Databank tabel grutte
Litte wy de tabelgrutte fergelykje foar beide oanpak. Yn psql kinne wy de grutte fan alle tabellen en yndeksen sjen litte mei it kommando dti+
Foar de EAV-oanpak binne tabelgrutte sawat 3068 MB en yndeksen oant 3427 MB foar in totaal fan 6,43 GB. De JSONB-oanpak brûkt 1817 MB foar de tafel en 318 MB foar de yndeksen, dat is 2,08 GB. It docht bliken 3 kear minder! Dit feit fernuvere my in bytsje om't wy eigendomnammen opslaan yn elk JSONB-objekt.
Mar dochs sprekke de sifers foar harsels: yn EAV bewarje wy 2 heule getal bûtenlânske kaaien per attribútwearde, wat resulteart yn 8 bytes oan ekstra gegevens. Derneist bewarret EAV alle eigendomswearden as tekst, wylst JSONB numerike en Booleaanske wearden yntern sil brûke wêr mooglik, wat resulteart yn in lytsere footprint.
Resultaten
Oer it algemien tink ik dat it bewarjen fan entiteitseigenskippen yn JSONB-formaat it ûntwerpen en ûnderhâlden fan jo databank folle makliker meitsje kin. As jo in protte fragen útfiere, dan sil alles yn deselde tabel hâlde as de entiteit effisjinter wurkje. En it feit dat dit de ynteraksje tusken gegevens simplifies is al in plus, mar de resultearjende databank is 3 kear lytser yn folume.
Ek, op basis fan de útfierde testen, kinne wy konkludearje dat de prestaasjesferlies tige ûnbelangryk binne. Yn guon gefallen is JSONB noch rapper dan EAV, wêrtroch it noch better is. Dizze benchmark dekt lykwols fansels net alle aspekten (bgl , fiel jo frij om te ferlitten yn 'e kommentaren!
Boarne: www.habr.com