TL; DR: Hiki iā JSONB ke hoʻomaʻamaʻa nui i ka hoʻomohala ʻana i ka schema database me ka ʻole e kaumaha ana i ka hana nīnau.
Hōʻike
E lawe kākou i kekahi laʻana maʻamau, ʻo ia paha kekahi o nā hihia hoʻohana kahiko loa ma ka honua o nā ʻikepili pili: loaʻa iā mākou kahi hui, a pono mākou e mālama i kekahi mau waiwai (ʻano) o kēia hui. Akā, ʻaʻole like nā ʻano waiwai a pau, a hiki ke hoʻohui ʻia nā waiwai hou aʻe i ka wā e hiki mai ana.
ʻO ka hopena maʻalahi loa i kēia pilikia, ʻo ia ka hana ʻana i kahi kolamu ma ka papa ʻikepili no kēlā me kēia waiwai waiwai a hoʻopiha wale i nā mea e pono ai no kahi ʻano hui kikoʻī. Nui! Hoʻoholo ʻia ka pilikia... a hiki i kāu papaʻaina ke loaʻa nā miliona o nā moʻolelo a pono ʻoe e hoʻohui i kahi moʻolelo hou.
E noʻonoʻo kākou i ke ʻano EAV (), he mea maʻamau. Aia kekahi papa ʻaina i nā hui (nā moʻolelo), aia kekahi papa ʻaina i nā inoa waiwai (ʻano), a ʻo kahi papa ʻekolu e hoʻopili ai i nā hui i ko lākou mau ʻano a loaʻa nā waiwai o kēia mau ʻano no ka hui o kēia manawa. Hāʻawi kēia iā ʻoe e loaʻa i nā ʻano waiwai like ʻole no nā mea like ʻole, a me ka hoʻohui ʻana i nā waiwai ma ka lele, me ka ʻole o ka hoʻololi ʻana i ka ʻōnaehana waihona.
Eia naʻe, ʻaʻole wau e kākau i kēia pou inā ʻaʻole he mau hemahema i ke ala EVA. No ka laʻana, ʻo ka hoʻihoʻi ʻana i hoʻokahi a ʻoi aku paha nā hui me hoʻokahi hiʻohiʻona e pono ai i ʻelua hui ʻana i ka nīnau: ʻo ka hui mua me ka papa ʻano, ʻo ka hui ʻelua me ka papa waiwai. Inā ʻelua mau ʻano o ka hui, a laila pono ʻehā hui! Eia kekahi, mālama mau ʻia nā ʻano a pau ma ke ʻano he mau kaula, e hopena i ke ʻano koi no ka hopena a me ka māhele WHERE. Inā kākau ʻoe i nā nīnau he nui, he mea makehewa kēia ma ke ʻano o ka hoʻohana waiwai.
ʻOiai kēia mau hemahema, ua hoʻohana lōʻihi ʻia ʻo EAV e hoʻoponopono i kēia mau pilikia. He mau hemahema hiki ʻole ke pale ʻia kēia, a ʻaʻohe mea ʻoi aku ka maikaʻi.
Akā ua puka mai kahi "ʻenehana" hou ma PostgreSQL ...
E hoʻomaka ana me PostgreSQL 9.4, ua hoʻohui ʻia ke ʻano ʻikepili JSONB no ka mālama ʻana i ka ʻikepili binary JSON. ʻOiai ʻo ka mālama ʻana iā JSON ma kēia ʻano, ʻoi aku ka nui o ka manawa a me ka manawa ma mua o ka JSON kikokikona maʻamau, ʻoi aku ka wikiwiki o nā hana me ia. Kākoʻo pū ʻo JSONB i ka kuhikuhi ʻana, e wikiwiki ana i nā nīnau.
Hiki i ke ʻano ʻikepili JSONB ke hoʻololi i ke ʻano EAV paʻakikī ma ka hoʻohui ʻana i kahi kolamu JSONB hoʻokahi i kā mākou papa ʻaina, me ka maʻalahi o ka hoʻolālā ʻikepili. Eia naʻe, manaʻo nui ka poʻe he kumu kūʻai kēia ma ka hana… ʻO ia ke kumu aʻu i kākau ai i kēia ʻatikala.
Hoʻonohonoho i kahi waihona hoʻāʻo
No kēia hoʻohālikelike, hana wau i ka waihona ma kahi hoʻokomo hou o PostgreSQL 9.5 ma ka hale $80. Ubuntu 14.04 Ma hope o ka hoʻonohonoho ʻana i kekahi mau palena ma postgresql.conf ua holo wau palapala me ka psql. No ka hōʻike ʻana i ka ʻikepili ma ke ʻano he EAV, ua hana ʻia nā papa ma lalo nei:
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
);
Aia ma lalo kahi papa kahi e mālama ʻia ai nā ʻikepili like, akā me nā ʻano i loko o kahi kolamu JSONB - nā mea waiwai.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
ʻOi aku ka maʻalahi, ʻaʻole anei? A laila ua hoʻohui ʻia i nā papa ʻaina (ka hui & entity_jsonb) 10 miliona mau moʻolelo, a no laila, ua hoʻopiha ʻia ka papaʻaina me nā ʻikepili like kahi i hoʻohana ʻia ai ke kumu EAV a me ke ala me ke kolamu JSONB - entity_jsonb.propertiesNo laila, ua loaʻa iā mākou nā ʻano ʻikepili like ʻole ma ka pūʻulu holoʻokoʻa o nā waiwai. ʻikepili laʻana:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}No laila, i kēia manawa ua loaʻa iā mākou ka ʻikepili like no nā koho ʻelua. E hoʻomaka kākou e hoʻohālikelike i nā hoʻokō i ke ola maoli!
Hoʻomaʻamaʻa i ka hoʻolālā
Ua ʻōlelo ʻia ma mua ua maʻalahi ka hoʻolālā ʻikepili: hoʻokahi papa, me ka hoʻohana ʻana i kahi kolamu JSONB no nā waiwai, ma kahi o ʻekolu mau papa no EAV. Akā, pehea kēia e unuhi ai i nā nīnau? ʻO ka hoʻonui ʻana i kahi waiwai hui hoʻokahi e like me kēia:
-- 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;
E like me kāu e ʻike ai, ʻaʻole maʻalahi ka hulina hope. No ka hoʻonui i ka waiwai o kahi waiwai i kahi mea JSONB, pono mākou e hoʻohana i ka hana , a pono e hāʻawi i kā mākou waiwai hou ma ke ʻano he mea JSONB. Akā naʻe, ʻaʻole pono mākou e ʻike i kahi mea hōʻike ma mua. Ke nānā nei i ka laʻana EAV, pono mākou e ʻike i ka entity_id a me entity_attribute_id e hana i ka hoʻopou. Inā makemake ʻoe e hoʻohou i kahi waiwai ma kahi kolamu JSONB e pili ana i ka inoa mea, hana ʻia ma kahi laina maʻalahi.
E koho kāua i ka hui a mākou i hōʻano hou ai ma muli o kona kala hou:
-- 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';
Manaʻo wau hiki iā mākou ke ʻae ʻoi aku ka pōkole o ka lua (me ka ʻole o ka hui!) Ua lanakila ʻo JSONB ma ʻaneʻi! Hoʻohana mākou i ka JSON ->> mea hoʻohana e kiʻi i ke kala ma ke ʻano he waiwai kikokikona mai ka mea JSONB. Aia kekahi ala ʻelua e hoʻokō ai i ka hopena like i ka hoʻohālike JSONB me ka hoʻohana ʻana i ka @> mea hoʻohana:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
ʻOi aku ka paʻakikī o kēia: nānā mākou inā aia ka mea JSON ma ke kolamu waiwai i ka mea ma ka ʻaoʻao ʻākau o ka mea hoʻohana @>. ʻAʻole hiki ke heluhelu ʻia, ʻoi aku ka hana (e nānā i lalo).
E maʻalahi ka hoʻohana ʻana iā JSONB inā pono ʻoe e koho i nā waiwai he nui i ka manawa hoʻokahi. ʻO kēia kahi e ʻālohilohi maoli ai ke ala JSONB: koho wale mākou i nā waiwai e like me nā kolamu hou i kā mākou hoʻonohonoho hopena, me ka ʻole o ka pono e hui pū:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Me EAV, pono ʻoe i ʻelua hui no kēlā me kēia waiwai āu e makemake ai e nīnau. I koʻu manaʻo, hōʻike nā nīnau ma luna nei i kahi maʻalahi maʻalahi i ka hoʻolālā waihona. Hiki iā ʻoe ke ʻike i nā hiʻohiʻona hou aʻe o ke kākau ʻana i nā nīnau JSONB i loko pou.
ʻO ka manawa kēia e kamaʻilio e pili ana i ka hana.
'Ohanahana
E hoʻohālikelike i ka hana aʻu i hoʻohana ai i nā nīnau e helu i ka manawa hoʻokō. Ua hoʻokō ʻia kēlā me kēia nīnau i ʻekolu manawa, no ka mea, ʻoi aku ka lōʻihi o ka mea hoʻolālā nīnau i ka manawa mua. ʻO ka mea mua, holo wau i nā nīnau me ka ʻole o nā kuhikuhi. He mea pono kēia no JSONB, no ka mea, ʻaʻole hiki i nā hui i koi ʻia no EAV ke hoʻohana i nā kuhikuhi (ʻaʻole i kuhikuhi ʻia nā kī kī haole). Ma hope o kēlā, ua hana au i papa kuhikuhi ma nā kolamu kī haole ʻelua o ka papa waiwai EAV, a me kahi kuhikuhi. no ke kolamu JSONB.
Hōʻike nā hōʻano hou ʻikepili i nā hopena manawa aʻe (ma ms). E hoʻomaopopo he logarithmic ka pālākiō:
ʻIke mākou he ʻoi aku ka wikiwiki o JSONB (> 50000x) ma mua o EAV me ka ʻole o nā kuhikuhi, no ke kumu i ʻōlelo ʻia ma luna. Ke kuhikuhi mākou i nā kolamu kī nui, aneane nalo ka ʻokoʻa, akā ʻoi aku ka wikiwiki o JSONB ma mua o EAV. E hoʻomanaʻo, ʻaʻohe hopena o ka papa kuhikuhi ma ka kolamu JSONB ma ʻaneʻi, no ka mea ʻaʻole mākou e hoʻohana i ke kolamu waiwai i nā pae loiloi.
No ke koho ʻikepili e pili ana i ka waiwai waiwai, loaʻa iā mākou nā hopena aʻe (ka unahi maʻamau):
Hiki iā ʻoe ke ʻike ua ʻoi aku ka wikiwiki o JSONB ma mua o EAV me ka ʻole o nā kuhikuhi, akā ke kuhikuhi ʻia ʻo EAV, ʻoi aku ka wikiwiki ma mua o JSONB. Akā, ʻike wau ua like nā manawa no nā nīnau JSONB, kahi i alakaʻi iaʻu i ka ʻoiaʻiʻo ʻaʻole i hoʻāla ʻia nā index GIN. ʻIke ʻia, ke hoʻohana ʻoe i kahi index GIN ma kahi kolamu me nā waiwai nui, hoʻohana wale ia i ka wā e hoʻohana ai i ka @> hoʻokomo. Ua hoʻohana au i kēia ma kahi hoʻāʻo hou, a he hopena nui loa ia i ka manawa: 0,153 ms wale nō! He 15000 manawa ʻoi aku ka wikiwiki ma mua o EAV a he 25000 manawa ʻoi aku ka wikiwiki ma mua o ka mea hoʻohana ->>.
Manaʻo wau ua wikiwiki loa!
Ka nui o nā papa ʻikepili
E hoʻohālikelike kākou i ka nui o ka pākaukau no nā ala ʻelua. I ka psql, hiki iā mākou ke hōʻike i ka nui o nā papa a me nā kuhikuhi me ka hoʻohana ʻana i ke kauoha dti+
Me ka hoʻokokoke EAV, ʻoi aku ka nui o ka papa ʻaina ma kahi o 3068 MB, a ʻo nā helu kuhikuhi a hiki i 3427 MB, no ka huina o 6,43 GB. Ke hoʻohana nei i ke ala JSONB, hoʻohana ka papa ʻaina i ka 1817 MB a me nā kuhikuhi 318 MB, no ka huina o 2,08 GB. ʻO ka hapakolu kēlā o ka nui! ʻO kēia ʻoiaʻiʻo i kāhāhā iki iaʻu, no ka mea, mālama mākou i nā inoa waiwai i kēlā me kēia mea JSONB.
Akā, ʻōlelo nā helu no lākou iho: ma EAV, mālama mākou i ʻelua mau kī haole integer no ka waiwai waiwai, e loaʻa ana i 8 bytes o ka ʻikepili hou. Eia kekahi, ma EAV, mālama ʻia nā waiwai waiwai āpau ma ke ʻano he kikokikona, ʻoiai ʻo JSONB e hoʻohana i nā helu helu a me nā loina i loko inā hiki, e hopena i kahi wāwae liʻiliʻi.
Nā hopena
Ma ke ʻano holoʻokoʻa, manaʻo wau ʻo ka mālama ʻana i nā waiwai waiwai ma ka format JSONB hiki ke maʻalahi i ka hoʻolālā a me ka mālama ʻana i kāu waihona. Inā hoʻokō ʻoe i nā nīnau he nui, ʻoi aku ka maikaʻi o ka mālama ʻana i nā mea āpau ma ka papa like me ka hui. ʻO ka ʻoiaʻiʻo o ka hoʻomaʻamaʻa ʻana i nā pilina ʻikepili he mea hou aku, akā ʻoi aku ka liʻiliʻi o ka waihona ʻikepili i ʻike ʻia.
Eia kekahi, ma muli o nā hopena benchmark, hiki iā mākou ke hoʻoholo he liʻiliʻi loa ka hoʻopaʻi hana. I kekahi mau hihia, ʻoi aku ka wikiwiki o JSONB ma mua o EAV, e ʻoi aku ka maikaʻi. Eia nō naʻe, ʻaʻole i uhi ʻia kēia pae ʻāina i nā ʻano āpau (e laʻa, nā hui me ka nui o nā waiwai, ka piʻi nui ʻana o ka nui o nā waiwai i ka ʻikepili i loaʻa, a me nā mea ʻē aʻe), no laila inā loaʻa iā ʻoe nā manaʻo no ka hoʻomaikaʻi ʻana, e ʻoluʻolu e waiho iā lākou i nā manaʻo!
Source: www.habr.com
