TL; DR: JSONB pot simplificar molt el desenvolupament d'esquemes de base de dades sense sacrificar el rendiment de la consulta.
Introducció
Posem un exemple clàssic de probablement un dels casos d'ús més antics del món d'una base de dades relacional (base de dades): tenim una entitat, i hem de desar determinades propietats (atributs) d'aquesta entitat. Però no totes les instàncies poden tenir el mateix conjunt de propietats i es poden afegir més propietats en el futur.
La manera més senzilla de resoldre aquest problema és crear una columna a la taula de la base de dades per a cada valor de propietat i simplement omplir les que siguin necessàries per a una instància d'entitat específica. Genial! Problema resolt... fins que la vostra taula conté milions de registres i heu d'afegir un registre nou.
Considereu el patró EAV (), es produeix amb força freqüència. Una taula conté entitats (registres), una altra taula conté noms de propietats (atributs) i una tercera taula associa entitats amb els seus atributs i conté el valor d'aquests atributs per a l'entitat actual. Això us ofereix la possibilitat de tenir diferents conjunts de propietats per a diferents objectes i també afegir propietats sobre la marxa sense canviar l'estructura de la base de dades.
Tanmateix, no estaria escrivint aquesta publicació si no hi hagués alguns desavantatges de l'enfocament de l'EVA. Així, per exemple, per obtenir una o més entitats que tinguin 1 atribut cadascuna, calen 2 unions a la consulta: la primera és una unió amb la taula d'atributs, la segona és una unió amb la taula de valors. Si una entitat té 2 atributs, calen 4 unions! A més, tots els atributs s'emmagatzemen normalment com a cadenes, la qual cosa dóna lloc a la conversió de tipus tant per al resultat com per a la clàusula WHERE. Si escriviu moltes consultes, això és un malbaratament en termes d'ús de recursos.
Malgrat aquestes deficiències evidents, l'EAV s'ha utilitzat durant molt de temps per resoldre aquest tipus de problemes. Aquestes eren mancances inevitables, i simplement no hi havia una alternativa millor.
Però aleshores va aparèixer una nova "tecnologia" a PostgreSQL...
A partir de PostgreSQL 9.4, es va afegir el tipus de dades JSONB per emmagatzemar dades binàries JSON. Tot i que emmagatzemar JSON en aquest format sol ocupar una mica més d'espai i temps que el JSON de text sense format, fer-hi operacions és molt més ràpid. JSONB també admet la indexació, cosa que fa que les consultes siguin encara més ràpides.
El tipus de dades JSONB ens permet substituir l'engreixat patró EAV afegint només una columna JSONB a la nostra taula d'entitats, simplificant molt el disseny de la base de dades. Però molts argumenten que això hauria d'anar acompanyat d'una disminució de la productivitat... Per això he escrit aquest article.
Configuració d'una base de dades de proves
Per a aquesta comparació, vaig crear la base de dades en una nova instal·lació de PostgreSQL 9.5 a la compilació de $ 80 Ubuntu 14.04. Després de configurar alguns paràmetres a postgresql.conf, vaig executar script utilitzant psql. Es van crear les taules següents per presentar les dades en forma EAV:
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
);
A continuació es mostra una taula on s'emmagatzemaran les mateixes dades, però amb atributs en una columna de tipus JSONB: propietats.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Sembla molt més senzill, no? Després es va afegir a les taules d'entitats (entitat & entitat_jsonb) 10 milions de registres i, per tant, la taula es va omplir amb les mateixes dades mitjançant el patró EAV i l'enfocament amb una columna JSONB - entity_jsonb.properties. Així, vam rebre diversos tipus de dades diferents entre tot el conjunt de propietats. Dades d'exemple:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Així que ara tenim les mateixes dades per a les dues opcions. Comencem a comparar les implementacions a la feina!
Simplifica el teu disseny
Abans es va dir que el disseny de la base de dades es va simplificar molt: una taula, utilitzant una columna JSONB per a les propietats, en lloc d'utilitzar tres taules per a EAV. Però, com es reflecteix això en les peticions? L'actualització d'una propietat d'entitat té aquest aspecte:
-- 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;
Com podeu veure, l'última petició no sembla més senzilla. Per actualitzar el valor d'una propietat en un objecte JSONB hem d'utilitzar la funció , i hauria de passar el nostre nou valor com a objecte JSONB. Tanmateix, no necessitem saber cap identificador per endavant. Mirant l'exemple d'EAV, hem de conèixer tant l'entity_id com l'entity_attribute_id per tal de realitzar l'actualització. Si voleu actualitzar una propietat en una columna JSONB en funció del nom de l'objecte, tot es fa en una línia senzilla.
Ara seleccionem l'entitat que acabem d'actualitzar en funció del seu nou color:
-- 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';
Crec que podem estar d'acord que el segon és més curt (sense unir!), i per tant més llegible. JSONB guanya aquí! Utilitzem l'operador JSON ->> per obtenir el color com a valor de text de l'objecte JSONB. També hi ha una segona manera d'aconseguir el mateix resultat en el model JSONB mitjançant l'operador @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Això és una mica més complicat: comprovem si l'objecte JSON a la seva columna de propietats conté un objecte que es troba a la dreta de l'operador @>. Menys llegible, més productiu (vegeu més avall).
Fem que l'ús de JSONB sigui encara més fàcil quan necessiteu seleccionar diverses propietats alhora. Aquí és on entra realment l'enfocament JSONB: simplement seleccionem propietats com a columnes addicionals al nostre conjunt de resultats sense necessitat d'unions:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Amb EAV necessitareu 2 unions per a cada propietat que vulgueu consultar. Al meu entendre, les consultes anteriors mostren una gran simplificació en el disseny de la base de dades. Vegeu més exemples de com escriure consultes JSONB també a publicació.
Ara toca parlar de rendiment.
Productivitat
Per comparar el rendiment he utilitzat en les consultes, per calcular el temps d'execució. Cada consulta s'ha executat almenys tres vegades perquè el planificador de consultes triga més la primera vegada. Primer vaig executar les consultes sense cap índex. Òbviament, aquest era un avantatge de JSONB, ja que les unions necessàries per a EAV no podien utilitzar índexs (els camps de clau estrangera no estaven indexats). Després d'això, vaig crear un índex a les 2 columnes de clau estrangera de la taula de valors EAV, així com un índex per a una columna JSONB.
L'actualització de dades va mostrar els següents resultats en termes de temps (en ms). Tingueu en compte que l'escala és logarítmica:
Veiem que JSONB és molt (> 50000-x) més ràpid que EAV si no feu servir índexs, per la raó indicada anteriorment. Quan indexem columnes amb claus primàries, la diferència gairebé desapareix, però JSONB encara és 1,3 vegades més ràpid que EAV. Tingueu en compte que l'índex de la columna JSONB no té cap efecte aquí, ja que no estem utilitzant la columna de propietats als criteris d'avaluació.
Per seleccionar dades en funció del valor de la propietat, obtenim els resultats següents (escala normal):
Podeu notar que JSONB torna a funcionar més ràpid que EAV sense índexs, però quan EAV amb índexs, encara funciona més ràpid que JSONB. Però aleshores vaig veure que els temps per a les consultes JSONB eren els mateixos, això em va portar al fet que els índexs GIN no funcionen. Aparentment, quan utilitzeu un índex GIN en una columna amb propietats poblades, només té efecte quan s'utilitza l'operador d'inclusió @>. Ho vaig fer servir en una prova nova i va tenir un gran impacte en el temps: només 0,153 ms! Això és 15000 vegades més ràpid que l'EAV i 25000 vegades més ràpid que l'operador ->>.
Crec que va ser prou ràpid!
Mida de la taula de la base de dades
Comparem les mides de la taula per als dos enfocaments. A psql podem mostrar la mida de totes les taules i índexs mitjançant l'ordre dti+
Per a l'enfocament EAV, les mides de les taules són d'uns 3068 MB i els índexs de fins a 3427 MB per a un total de 6,43 GB. L'enfocament JSONB utilitza 1817 MB per a la taula i 318 MB per als índexs, que són 2,08 GB. Resulta 3 vegades menys! Aquest fet em va sorprendre una mica perquè emmagatzemem noms de propietats a cada objecte JSONB.
Però tot i així, els números parlen per si mateixos: a EAV emmagatzemem 2 claus externes enteres per valor d'atribut, donant lloc a 8 bytes de dades addicionals. A més, EAV emmagatzema tots els valors de propietats com a text, mentre que JSONB utilitzarà valors numèrics i booleans internament sempre que sigui possible, donant lloc a una empremta més petita.
Resultats de
En general, crec que desar les propietats de l'entitat en format JSONB pot facilitar molt el disseny i el manteniment de la vostra base de dades. Si feu moltes consultes, mantenir-ho tot a la mateixa taula que l'entitat funcionarà de manera més eficient. I el fet que això simplifiqui la interacció entre les dades ja és un avantatge, però la base de dades resultant és 3 vegades més petita en volum.
Així mateix, a partir de les proves realitzades, podem concloure que les pèrdues de rendiment són molt insignificants. En alguns casos, JSONB és fins i tot més ràpid que EAV, la qual cosa el fa encara millor. Tanmateix, aquest punt de referència, per descomptat, no cobreix tots els aspectes (per exemple, entitats amb un nombre molt gran de propietats, un augment significatiu del nombre de propietats de les dades existents,...), així que si teniu algun suggeriment sobre com millorar-les , no dubteu a deixar-ho als comentaris!
Font: www.habr.com