TL; DR: JSONB bisa nyederhanakake pangembangan skema basis data tanpa ngorbanake kinerja pitakon.
Pambuka
Ayo menehi conto klasik sing mbokmenawa salah siji saka kasus panggunaan paling tuwa ing donya database relasional (database): kita duwe entitas, lan kita kudu nyimpen sifat tartamtu (atribut) saka entitas iki. Nanging ora kabeh kedadeyan bisa duwe set properti sing padha, lan luwih akeh properti bisa ditambahake ing mangsa ngarep.
Cara paling gampang kanggo ngatasi masalah iki yaiku nggawe kolom ing tabel database kanggo saben nilai properti, lan mung isi sing dibutuhake kanggo conto entitas tartamtu. apik tenan! Masalah ditanggulangi ... nganti tabel sampeyan ngemot mayuta-yuta cathetan lan sampeyan kudu nambah rekaman anyar.
Coba pola EAV (), kedadeyan cukup kerep. Siji tabel ngemot entitas (rekaman), tabel liyane ngemot jeneng properti (atribut), lan tabel katelu nggandhengake entitas karo atribut lan ngemot nilai atribut kasebut kanggo entitas saiki. Iki menehi kemampuan kanggo duwe set beda saka sifat kanggo obyek beda, lan uga nambah sifat ing fly tanpa ngganti struktur database.
Nanging, aku ora bakal nulis postingan iki yen ora ana kekurangan kanggo pendekatan EVA. Dadi, contone, kanggo entuk siji utawa luwih entitas sing duwe 1 atribut saben, 2 gabungan dibutuhake ing query: sing pertama gabung karo tabel atribut, sing nomer loro gabung karo tabel nilai. Yen entitas duwe 2 atribut, banjur 4 gabung dibutuhake! Kajaba iku, kabeh atribut biasane disimpen minangka strings, sing ngasilake jinis casting kanggo asil lan klausa WHERE. Yen sampeyan nulis akeh pitakon, mula iki cukup boros babagan panggunaan sumber daya.
Sanajan ana kekurangan sing jelas, EAV wis suwe digunakake kanggo ngatasi masalah kasebut. Iki minangka kekurangan sing ora bisa dihindari, lan ora ana alternatif sing luwih apik.
Nanging banjur "teknologi" anyar muncul ing PostgreSQL ...
Miwiti karo PostgreSQL 9.4, jinis data JSONB ditambahake kanggo nyimpen data binar JSON. Sanajan nyimpen JSON ing format iki biasane mbutuhake papan lan wektu luwih sithik tinimbang JSON teks biasa, nindakake operasi kasebut luwih cepet. JSONB uga ndhukung indeksasi, sing nggawe pitakon luwih cepet.
Jinis data JSONB ngidini kita ngganti pola EAV sing rumit kanthi nambahake mung siji kolom JSONB menyang tabel entitas kita, kanthi nyederhanakake desain database. Nanging akeh sing mbantah manawa iki kudu diiringi nyuda produktivitas ... Mulane aku nulis artikel iki.
Nggawe database test
Kanggo perbandingan iki, aku nggawe database babagan instalasi anyar PostgreSQL 9.5 ing $80 mbangun. Ubuntu 14.04. Sawise nyetel sawetara paramèter ing postgresql.conf aku mlayu script nganggo psql. Tabel ing ngisor iki digawe kanggo nampilake data ing wangun 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
);
Ing ngisor iki ana tabel ing ngendi data sing padha bakal disimpen, nanging kanthi atribut ing kolom jinis JSONB - situs.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Katon luwih prasaja, ta? Banjur ditambahake menyang tabel entitas (entitas & entity_jsonb) 10 yuta cathetan, lan kanthi mangkono, tabel diisi karo data sing padha nggunakake pola EAV lan pendekatan karo kolom JSONB - entity_jsonb.properties. Mangkono, kita nampa sawetara jinis data sing beda ing antarane kabeh properti. Tuladha data:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Dadi saiki kita duwe data sing padha kanggo opsi loro kasebut. Ayo miwiti mbandhingake implementasine ing karya!
Gampang desain sampeyan
Sadurungé kasebut, desain basis data disederhanakaké: siji tabel, kanthi nggunakake kolom JSONB kanggo properti, tinimbang nggunakake telung tabel kanggo EAV. Nanging carane iki dibayangke ing panjalukan? Nganyari siji properti entitas katon kaya iki:
-- 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;
Nalika sampeyan bisa ndeleng, panjalukan pungkasan ora katon prasaja. Kanggo nganyari nilai properti ing obyek JSONB kita kudu nggunakake fungsi kasebut , lan kudu ngliwati nilai anyar kita minangka obyek JSONB. Nanging, kita ora perlu ngerti pengenal sadurunge. Deleng conto EAV, kita kudu ngerti entity_id lan entity_attribute_id supaya bisa nganyari. Yen sampeyan pengin nganyari properti ing kolom JSONB adhedhasar jeneng obyek, banjur kabeh wis rampung ing siji baris prasaja.
Saiki ayo milih entitas sing lagi wae dianyari adhedhasar warna anyar:
-- 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';
Aku bisa setuju yen sing kapindho luwih cendhek (ora gabung!), Lan mulane luwih bisa diwaca. JSONB menang ing kene! Kita nggunakake operator JSON ->> kanggo entuk warna minangka nilai teks saka obyek JSONB. Ana uga cara liya kanggo entuk asil sing padha ing model JSONB nggunakake operator @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Iki rada rumit: kita priksa manawa obyek JSON ing kolom properti ngemot obyek sing ana ing sisih tengen operator @>. Kurang bisa diwaca, luwih produktif (ndeleng ngisor).
Ayo dadi luwih gampang nggunakake JSONB nalika sampeyan kudu milih macem-macem properti bebarengan. Iki minangka pendekatan JSONB: kita mung milih properti minangka kolom tambahan ing set asil tanpa perlu gabung:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Kanthi EAV sampeyan butuh 2 gabungan kanggo saben properti sing pengin dijaluk. Ing mratelakake panemume, pitakon ing ndhuwur nuduhake simplifikasi gedhe ing desain database. Deleng conto liyane babagan carane nulis pitakon JSONB, uga ing kirim.
Saiki wayahe ngomong babagan kinerja.
Produktivitas
Kanggo mbandhingake kinerja aku digunakake ing pitakon, kanggo ngetung wektu eksekusi. Saben pitakon dieksekusi paling ora kaping telu amarga perencana pitakon luwih suwe nalika sepisanan. Kaping pisanan, aku mbukak pitakon tanpa indeks. Temenan, iki minangka kauntungan saka JSONB, amarga gabungan sing dibutuhake kanggo EAV ora bisa nggunakake indeks (bidang kunci manca ora diindeks). Sawise iki, aku nggawe indeks ing 2 kolom kunci asing saka tabel nilai EAV, uga indeks kanggo kolom JSONB.
Nganyari data nuduhake asil ing ngisor iki babagan wektu (ing ms). Elinga yen skala kasebut logaritma:
Kita weruh yen JSONB luwih cepet (> 50000-x) tinimbang EAV yen sampeyan ora nggunakake indeks, amarga alasan kasebut ing ndhuwur. Nalika kita indeks kolom karo tombol utami, prabédan meh ilang, nanging JSONB isih 1,3 kaping luwih cepet saka EAV. Elinga yen indeks ing kolom JSONB ora ana pengaruh ing kene amarga kita ora nggunakake kolom properti ing kritéria evaluasi.
Kanggo milih data adhedhasar nilai properti, kita entuk asil ing ngisor iki (skala normal):
Sampeyan bisa sok dong mirsani sing JSONB maneh dianggo luwih cepet saka EAV tanpa indeks, nanging nalika EAV karo indeks, isih bisa luwih cepet saka JSONB. Nanging banjur aku weruh yen wektu kanggo pitakon JSONB padha, iki nyebabake aku nyatane yen indeks GIN ora bisa digunakake. Ketoke yen sampeyan nggunakake indeks GIN ing kolom karo properti pedunung, iku mung ditrapake nalika nggunakake operator kalebu @>. Aku nggunakake iki ing test anyar lan wis impact ageng ing wektu: mung 0,153ms! Iki 15000 kaping luwih cepet tinimbang EAV lan 25000 kaping luwih cepet tinimbang operator ->>.
Aku wis cukup cepet!
Ukuran tabel database
Ayo mbandhingake ukuran tabel kanggo loro pendekatan kasebut. Ing psql kita bisa nuduhake ukuran kabeh tabel lan indeks nggunakake printah dti+
Kanggo pendekatan EAV, ukuran tabel watara 3068 MB lan indeks nganti 3427 MB kanthi total 6,43 GB. Pendekatan JSONB nggunakake 1817 MB kanggo tabel lan 318 MB kanggo indeks, yaiku 2,08 GB. Pranyata metu 3 kaping kurang! Kasunyatan iki rada kaget amarga kita nyimpen jeneng properti ing saben obyek JSONB.
Nanging isih, nomer kasebut dhewe: ing EAV kita nyimpen 2 kunci manca integer saben nilai atribut, ngasilake 8 bita data tambahan. Kajaba iku, EAV nyimpen kabeh nilai properti minangka teks, dene JSONB bakal nggunakake nilai numerik lan boolean sacara internal yen bisa, nyebabake jejak sing luwih cilik.
Hasil
Sakabèhé, aku mikir nyimpen properti entitas ing format JSONB bisa nggawe ngrancang lan njaga database luwih gampang. Yen sampeyan mbukak akeh pitakon, banjur nyimpen kabeh ing meja sing padha karo entitas bakal bisa luwih efisien. Lan kasunyatan sing iki simplifies interaksi antarane data wis plus, nanging database asil 3 kaping cilik ing volume.
Uga, adhedhasar tes sing ditindakake, kita bisa nyimpulake yen kerugian kinerja ora pati penting. Ing sawetara kasus, JSONB malah luwih cepet tinimbang EAV, dadi luwih apik. Nanging, pathokan iki mesthi ora nyakup kabeh aspek (contone, entitas kanthi jumlah properti sing akeh banget, nambah jumlah properti data sing wis ana, ...), dadi yen sampeyan duwe saran babagan carane nambah. , please aran gratis kanggo ninggalake ing komentar!
Source: www.habr.com