TL; DR: Ang JSONB makapasayon pag-ayo sa database schema development nga walay pagsakripisyo sa performance sa pangutana.
Pasiuna
Maghatag kita og usa ka klasiko nga pananglitan sa tingali usa sa labing karaan nga mga kaso sa paggamit sa kalibutan sa usa ka relational database (database): kita adunay usa ka entidad, ug kinahanglan naton nga tipigan ang pipila nga mga kabtangan (mga hiyas) niini nga entidad. Apan dili tanan nga mga higayon mahimong adunay parehas nga hugpong sa mga kabtangan, ug daghang mga kabtangan ang mahimong idugang sa umaabot.
Ang labing kadali nga paagi aron masulbad kini nga problema mao ang paghimo og usa ka kolum sa lamesa sa database alang sa matag kantidad sa kabtangan, ug pun-on lang ang mga gikinahanglan alang sa usa ka piho nga pananglitan sa entidad. Nindot! Nasulbad ang problema... hangtod ang imong lamesa adunay milyon-milyon nga mga rekord ug kinahanglan nimo nga magdugang usa ka bag-ong rekord.
Hunahunaa ang EAV pattern (), kini mahitabo kanunay. Ang usa ka lamesa adunay mga entidad (mga rekord), ang lain nga lamesa adunay mga ngalan sa kabtangan (mga hiyas), ug ang ikatulo nga lamesa nag-uban sa mga entidad sa ilang mga kinaiya ug adunay sulud nga kantidad sa mga hiyas alang sa karon nga entidad. Naghatag kini kanimo og katakus nga adunay lainlaing mga set sa mga kabtangan alang sa lainlaing mga butang, ug pagdugang usab mga kabtangan sa langaw nga wala usba ang istruktura sa database.
Bisan pa, dili nako isulat kini nga post kung wala’y mga kapakyasan sa pamaagi sa EVA. Busa, pananglitan, aron makakuha og usa o daghan pang entidad nga adunay 1 ka attribute matag usa, 2 ka pag-apil ang gikinahanglan sa pangutana: ang una usa ka pag-apil sa attribute table, ang ikaduha usa ka pag-apil sa values table. Kung ang usa ka entidad adunay 2 nga mga hiyas, nan kinahanglan ang 4 nga pag-apil! Dugang pa, ang tanan nga mga hiyas kasagarang gitipigan isip mga kuwerdas, nga moresulta sa matang sa paghulma alang sa resulta ug sa WHERE clause. Kung nagsulat ka daghang mga pangutana, nan kini usa ka usik sa mga termino sa paggamit sa kapanguhaan.
Bisan pa niining klaro nga mga kakulangan, ang EAV dugay na nga gigamit sa pagsulbad niini nga mga matang sa mga problema. Dili kalikayan nga mga kakulangan kini, ug wala’y labing maayo nga kapilian.
Apan unya usa ka bag-ong "teknolohiya" ang nagpakita sa PostgreSQL ...
Sugod sa PostgreSQL 9.4, ang tipo sa datos sa JSONB gidugang sa pagtipig sa JSON binary data. Bisan kung ang pagtipig sa JSON sa kini nga pormat kasagarang nagkinahanglag gamay nga wanang ug oras kaysa sa yano nga teksto nga JSON, ang paghimo sa mga operasyon niini labi ka paspas. Ang JSONB nagsuporta usab sa pag-indeks, nga naghimo sa mga pangutana nga mas paspas.
Ang tipo sa datos sa JSONB nagtugot kanamo sa pag-ilis sa lisud nga sumbanan sa EAV pinaagi sa pagdugang usa lang ka kolum sa JSONB sa among lamesa sa entidad, labi nga gipasimple ang disenyo sa database. Apan daghan ang nangatarungan nga kini kinahanglan nga inubanan sa pagkunhod sa produktibo ... Mao nga gisulat nako kini nga artikulo.
Pag-set up sa usa ka database sa pagsulay
Alang niini nga pagtandi, gibuhat nako ang database sa bag-ong pag-instalar sa PostgreSQL 9.5 sa $80 nga pagtukod Ubuntu 14.04. Human sa pagbutang sa pipila ka mga parametro sa postgresql.conf midagan ko script gamit ang psql. Ang mosunod nga mga lamesa gihimo aron ipresentar ang datos sa EAV nga porma:
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
);
Sa ubos usa ka lamesa diin ang parehas nga datos itago, apan adunay mga hiyas sa usa ka kolum nga tipo sa JSONB - mga kabtangan.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Morag mas simple, dili ba? Unya gidugang kini sa mga lamesa sa entidad (kompaniya & entity_jsonb) 10 milyon nga mga rekord, ug sa ingon, ang lamesa napuno sa parehas nga datos gamit ang EAV pattern ug ang pamaagi nga adunay JSONB column - entity_jsonb.properties. Sa ingon, nakadawat kami daghang lainlaing mga tipo sa datos taliwala sa tibuuk nga hugpong sa mga kabtangan. Pananglitan data:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Mao nga karon kami adunay parehas nga datos alang sa duha nga kapilian. Magsugod ta sa pagtandi sa mga implementasyon sa trabahoan!
Pasimpleha ang imong disenyo
Gipahayag kaniadto nga ang disenyo sa database gipasimple kaayo: usa ka lamesa, pinaagi sa paggamit sa usa ka kolum sa JSONB alang sa mga kabtangan, imbes nga gamiton ang tulo ka mga lamesa alang sa EAV. Apan sa unsang paagi kini gipakita sa mga hangyo? Ang pag-update sa usa ka kabtangan sa entidad ingon niini:
-- 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;
Sama sa imong makita, ang katapusang hangyo dili tan-awon nga mas simple. Aron ma-update ang bili sa usa ka kabtangan sa usa ka JSONB nga butang kinahanglan natong gamiton ang function , ug kinahanglang ipasa ang atong bag-ong bili isip JSONB nga butang. Bisan pa, dili kinahanglan nga mahibal-an namon ang bisan unsang identifier nga daan. Sa pagtan-aw sa pananglitan sa EAV, kinahanglan natong mahibal-an ang entity_id ug ang entity_attribute_id aron mahimo ang update. Kung gusto nimo nga i-update ang usa ka kabtangan sa usa ka kolum sa JSONB base sa ngalan sa butang, nan kini tanan nahimo sa usa ka yano nga linya.
Karon pilion nato ang entidad nga bag-o lang natong gi-update base sa bag-ong kolor niini:
-- 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';
Sa akong hunahuna kita magkauyon nga ang ikaduha mas mubo (walay apil!), ug busa mas mabasa. Ang JSONB nakadaog dinhi! Gigamit namon ang JSON ->> operator aron makuha ang kolor ingon usa ka kantidad sa teksto gikan sa usa ka butang nga JSONB. Adunay usab ikaduha nga paagi aron makab-ot ang parehas nga resulta sa modelo sa JSONB gamit ang @> operator:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Kini usa ka gamay nga mas komplikado: among susihon kung ang JSON nga butang sa iyang mga propyedad nga kolum adunay usa ka butang nga anaa sa tuo sa @> operator. Dili kaayo mabasa, mas produktibo (tan-awa sa ubos).
Atong himoon ang paggamit sa JSONB nga mas sayon kung kinahanglan nimo nga mopili og daghang mga kabtangan sa usa ka higayon. Dinhi diin ang JSONB nga pamaagi moabut gayud: gipili lang namo ang mga kabtangan isip dugang nga mga kolum sa among resulta nga wala magkinahanglan og mga pag-apil:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Uban sa EAV kinahanglan nimo ang 2 nga pag-apil alang sa matag kabtangan nga gusto nimo ipangutana. Sa akong opinyon, ang mga pangutana sa ibabaw nagpakita sa usa ka maayo nga pagpayano sa disenyo sa database. Tan-awa ang daghang mga pananglitan kung giunsa pagsulat ang mga pangutana sa JSONB, usab sa post.
Karon na ang panahon nga maghisgot bahin sa pasundayag.
Pag-uswag
Aron itandi ang performance nga akong gigamit sa mga pangutana, aron makalkulo ang oras sa pagpatuman. Ang matag pangutana gipatuman labing menos tulo ka beses tungod kay ang query planner mas dugay sa unang higayon. Una akong gipadagan ang mga pangutana nga wala’y bisan unsang mga indeks. Kini klaro nga usa ka bentaha sa JSONB, tungod kay ang mga pag-apil nga gikinahanglan alang sa EAV dili makagamit sa mga indeks (ang mga langyaw nga yawe nga natad wala ma-indeks). Pagkahuman niini naghimo ako usa ka indeks sa 2 nga langyaw nga yawe nga mga kolum sa lamesa sa kantidad sa EAV, ingon man usa ka indeks alang sa usa ka kolum sa JSONB.
Ang pag-update sa datos nagpakita sa mosunod nga mga resulta sa termino sa panahon (sa ms). Timan-i nga ang sukdanan kay logarithmic:
Nakita namon nga ang JSONB labi ka (> 50000-x) nga mas paspas kaysa EAV kung dili ka mogamit mga indeks, tungod sa hinungdan nga giingon sa ibabaw. Kung gi-index namon ang mga kolum nga adunay panguna nga yawe, ang kalainan hapit mawala, apan ang JSONB sa gihapon 1,3 ka beses nga mas paspas kaysa EAV. Timan-i nga ang indeks sa kolum sa JSONB walay epekto dinhi tungod kay wala kami naggamit sa kolum sa kabtangan sa mga pamatasan sa pagtimbang-timbang.
Alang sa pagpili sa datos base sa bili sa kabtangan, atong makuha ang mosunod nga mga resulta (normal nga sukdanan):
Mamatikdan nimo nga ang JSONB pag-usab molihok nga mas paspas kaysa EAV nga wala’y mga indeks, apan kung ang EAV adunay mga indeks, kini molihok nga mas paspas kaysa JSONB. Apan nakita nako nga ang mga panahon alang sa mga pangutana sa JSONB parehas, kini nag-aghat kanako sa kamatuoran nga ang mga indeks sa GIN dili molihok. Dayag nga kung mogamit ka usa ka indeks sa GIN sa usa ka kolum nga adunay populasyon nga mga kabtangan, kini molihok lamang kung gamiton ang operator nga naglakip @>. Gigamit nako kini sa usa ka bag-ong pagsulay ug kini adunay dako nga epekto sa panahon: 0,153ms lamang! Kini 15000 ka beses nga mas paspas kaysa EAV ug 25000 ka beses nga mas paspas kaysa sa ->> operator.
Sa akong hunahuna kini igo nga kusog!
Gidak-on sa lamesa sa database
Atong itandi ang mga gidak-on sa lamesa alang sa duha ka mga pamaagi. Sa psql mahimo natong ipakita ang gidak-on sa tanang mga lamesa ug mga indeks gamit ang sugo dti+
Alang sa EAV nga pamaagi, ang mga gidak-on sa lamesa mga 3068 MB ug nag-indeks hangtod sa 3427 MB sa kinatibuk-an nga 6,43 GB. Ang JSONB nga pamaagi naggamit sa 1817 MB alang sa lamesa ug 318 MB alang sa mga indeks, nga mao ang 2,08 GB. Kini nahimo nga 3 ka beses nga mas gamay! Kini nga kamatuoran nahingangha kanako gamay tungod kay kami nagtipig sa mga ngalan sa kabtangan sa matag JSONB nga butang.
Apan sa gihapon, ang mga numero nagsulti alang sa ilang kaugalingon: sa EAV nagtipig kami og 2 integer nga langyaw nga mga yawe matag bili sa hiyas, nga miresulta sa 8 ka byte nga dugang nga datos. Dugang pa, gitipigan sa EAV ang tanan nga mga kantidad sa kabtangan ingon nga teksto, samtang ang JSONB mogamit mga numeric ug boolean nga mga kantidad sa internal kung mahimo, nga moresulta sa usa ka gamay nga tunob.
Mga resulta
Sa kinatibuk-an, sa akong hunahuna ang pagtipig sa mga kabtangan sa entidad sa format nga JSONB makahimo sa pagdesinyo ug pagmentinar sa imong database nga labi ka dali. Kung nagdagan ka daghang mga pangutana, nan ang pagtipig sa tanan sa parehas nga lamesa ingon nga ang entidad sa tinuud molihok nga labi ka episyente. Ug ang kamatuoran nga gipasimple niini ang interaksyon tali sa datos usa na ka plus, apan ang resulta nga database 3 ka beses nga mas gamay sa gidaghanon.
Usab, base sa mga pagsulay nga gihimo, makahinapos kita nga ang mga pagkawala sa pasundayag dili kaayo hinungdanon. Sa pipila ka mga kaso, ang JSONB mas paspas pa kaysa EAV, nga naghimo niini nga mas maayo. Bisan pa, kini nga benchmark siyempre wala maglakip sa tanan nga mga aspeto (pananglitan ang mga entidad nga adunay daghan kaayo nga gidaghanon sa mga kabtangan, usa ka mahinungdanong pagtaas sa gidaghanon sa mga kabtangan sa kasamtangan nga datos,...), mao nga kung ikaw adunay bisan unsa nga mga sugyot kon unsaon kini pagpalambo , palihug ayaw pagduhaduha sa pagbiya sa mga komento!
Source: www.habr.com