Rirọpo EAV pẹlu JSONB ni PostgreSQL

TL; DR: JSONB le jẹ ki o rọrun pupọ idagbasoke eto data data laisi rubọ iṣẹ ṣiṣe ibeere.

Ifihan

Jẹ ki a fun apẹẹrẹ Ayebaye ti o ṣee ṣe ọkan ninu awọn ọran lilo Atijọ julọ ni agbaye ti data ibatan kan (database): a ni nkan kan, ati pe a nilo lati ṣafipamọ awọn ohun-ini kan (awọn abuda) ti nkan yii. Ṣugbọn kii ṣe gbogbo awọn iṣẹlẹ le ni eto awọn ohun-ini kanna, ati pe awọn ohun-ini diẹ sii le ṣafikun ni ọjọ iwaju.

Ọna to rọọrun lati yanju iṣoro yii ni lati ṣẹda iwe kan ninu tabili data data fun iye ohun-ini kọọkan, ati nirọrun kun awọn ti o nilo fun apẹẹrẹ nkan kan pato. Nla! Isoro yanju ... titi ti tabili rẹ ni awọn miliọnu awọn igbasilẹ ati pe o nilo lati ṣafikun igbasilẹ tuntun kan.

Wo apẹẹrẹ EAV (Nkan-Idalara-Iye), o maa nwaye nigbagbogbo. Tabili kan ni awọn nkan (awọn igbasilẹ), tabili miiran ni awọn orukọ ohun-ini (awọn abuda), ati tabili kẹta kan awọn nkan ṣepọ pẹlu awọn abuda wọn ati pe o ni iye awọn abuda wọnyẹn fun nkan ti o wa lọwọlọwọ. Eyi yoo fun ọ ni agbara lati ni awọn akojọpọ awọn ohun-ini oriṣiriṣi fun awọn nkan oriṣiriṣi, ati tun ṣafikun awọn ohun-ini lori fo laisi iyipada eto data data.

Bibẹẹkọ, Emi kii yoo kọ ifiweranṣẹ yii ti ko ba si diẹ ninu awọn ilodi si ọna EVA. Nitorinaa, fun apẹẹrẹ, lati gba ọkan tabi diẹ ẹ sii awọn nkan ti o ni abuda 1 kọọkan, awọn idapọ 2 ni a nilo ninu ibeere naa: akọkọ jẹ apapọ pẹlu tabili abuda, ekeji jẹ idapọ pẹlu tabili awọn iye. Ti ile-iṣẹ kan ba ni awọn abuda meji, lẹhinna awọn idapọ 2 nilo! Ni afikun, gbogbo awọn abuda ni a tọju ni igbagbogbo bi awọn okun, eyiti o mu abajade simẹnti iru fun abajade mejeeji ati asọye NIBI. Ti o ba kọ ọpọlọpọ awọn ibeere, lẹhinna eyi jẹ apanirun pupọ ni awọn ofin ti lilo awọn orisun.

Pelu awọn ailagbara ti o han gbangba wọnyi, EAV ti pẹ ni lilo lati yanju iru awọn iṣoro wọnyi. Wọnyi li awọn eyiti ko shortcomings, ki o si nibẹ wà nìkan ko si dara yiyan.
Ṣugbọn lẹhinna “imọ-ẹrọ” tuntun han ni PostgreSQL…

Bibẹrẹ pẹlu PostgreSQL 9.4, iru data JSONB ni a ṣafikun lati tọju data alakomeji JSON. Botilẹjẹpe fifipamọ JSON ni ọna kika yii n gba aaye diẹ diẹ sii ati akoko ju ọrọ itele JSON lọ, ṣiṣe awọn iṣẹ ṣiṣe lori rẹ yiyara pupọ. JSONB tun ṣe atilẹyin titọka, eyiti o jẹ ki awọn ibeere paapaa yiyara.

Iru data JSONB ngbanilaaye lati rọpo ilana EAV ti o wuyi nipa fifi iwe-iwe JSONB kan kan kun si tabili nkan wa, ṣiṣe apẹrẹ data dirọ pupọ. Ṣugbọn ọpọlọpọ jiyan pe eyi yẹ ki o wa pẹlu idinku ninu iṣelọpọ… Eyi ni idi ti Mo kọ nkan yii.

Ṣiṣeto aaye data idanwo kan

Fun lafiwe yii, Mo ṣẹda data lori fifi sori tuntun ti PostgreSQL 9.5 lori kọ $80 DigitalOcean Ubuntu 14.04. После настройки некоторых параметров в postgresql.conf я запустил eyi iwe afọwọkọ nipa lilo psql. Awọn tabili wọnyi ni a ṣẹda lati ṣafihan data ni fọọmu 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
);

Ni isalẹ wa tabili nibiti data kanna yoo wa ni ipamọ, ṣugbọn pẹlu awọn abuda inu iwe iru JSONB - awọn ini.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

O dabi irọrun pupọ, ṣe kii ṣe bẹẹ? Lẹhinna o fi kun si awọn tabili nkan (nkankan & nkankan_jsonb) Awọn igbasilẹ miliọnu 10, ati ni ibamu, tabili naa kun pẹlu data kanna ni lilo ilana EAV ati ọna pẹlu iwe JSONB - nkankan_jsonb.properties. Nitorinaa, a gba ọpọlọpọ awọn oriṣi data oriṣiriṣi laarin gbogbo ṣeto awọn ohun-ini. Data apẹẹrẹ:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

Nitorinaa bayi a ni data kanna fun awọn aṣayan mejeeji. Jẹ ki a bẹrẹ afiwe awọn imuse ni iṣẹ!

Ṣe irọrun apẹrẹ rẹ

O ti sọ tẹlẹ pe apẹrẹ data jẹ irọrun pupọ: tabili kan, nipa lilo iwe JSONB fun awọn ohun-ini, dipo lilo awọn tabili mẹta fun EAV. Ṣugbọn bawo ni eyi ṣe han ninu awọn ibeere? Ṣiṣe imudojuiwọn ohun-ini nkan kan dabi eyi:

-- 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;

Bi o ti le rii, ibeere ti o kẹhin ko dabi rọrun. Lati ṣe imudojuiwọn iye ohun-ini kan ninu nkan JSONB a ni lati lo iṣẹ naa jsonb_set(), ati pe o yẹ ki o kọja iye tuntun wa bi nkan JSONB. Sibẹsibẹ, a ko nilo lati mọ eyikeyi idamo tẹlẹ. Wiwo apẹẹrẹ EAV, a nilo lati mọ mejeeji entity_id ati entity_attribute_id lati le ṣe imudojuiwọn naa. Ti o ba fẹ ṣe imudojuiwọn ohun-ini kan ninu iwe JSONB ti o da lori orukọ ohun, lẹhinna gbogbo rẹ ti ṣe ni laini rọrun kan.

Bayi jẹ ki a yan nkan ti a kan ṣe imudojuiwọn da lori awọ tuntun rẹ:

-- 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';

Mo ro pe a le gba pe awọn keji jẹ kikuru (ko si da!), Ati nitorina diẹ ṣeékà. JSONB bori nibi! A lo onišẹ JSON ->> lati gba awọ gẹgẹbi iye ọrọ lati inu ohun JSONB. Ọna keji tun wa lati ṣaṣeyọri abajade kanna ni awoṣe JSONB nipa lilo oniṣẹ @>:

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

Eyi jẹ idiju diẹ diẹ sii: a ṣayẹwo lati rii boya ohun JSON ninu iwe awọn ohun-ini rẹ ni ohun kan ti o wa si apa ọtun ti oniṣẹ @>. Kere kika, diẹ ti o ni eso (wo isalẹ).

Jẹ ki a jẹ ki lilo JSONB paapaa rọrun nigbati o nilo lati yan awọn ohun-ini pupọ ni ẹẹkan. Eyi ni ibiti ọna JSONB ti n wọle gaan: a rọrun yan awọn ohun-ini gẹgẹbi awọn ọwọn afikun ninu eto abajade wa laisi iwulo fun awọn idapọ:

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

Pẹlu EAV iwọ yoo nilo awọn idapọ 2 fun ohun-ini kọọkan ti o fẹ lati beere. Ni ero mi, awọn ibeere ti o wa loke ṣe afihan simplification nla ni apẹrẹ data. Wo awọn apẹẹrẹ diẹ sii ti bii o ṣe le kọ awọn ibeere JSONB, tun ni eyi ifiweranṣẹ.
Bayi o to akoko lati sọrọ nipa iṣẹ ṣiṣe.

Ise sise

Lati ṣe afiwe iṣẹ ti mo lo Ṣàlàyé Itupalẹ ni awọn ibeere, lati ṣe iṣiro akoko ipaniyan. Ibeere kọọkan jẹ ṣiṣe ni o kere ju igba mẹta nitori oluṣeto ibeere gba to gun ni akoko akọkọ. Ni akọkọ Mo ran awọn ibeere naa laisi awọn atọka eyikeyi. O han ni, eyi jẹ anfani ti JSONB, niwọn bi awọn asopọ ti o nilo fun EAV ko le lo awọn atọka (awọn aaye bọtini ajeji ko ni itọka). Lẹhin eyi Mo ṣẹda atọka kan lori awọn ọwọn bọtini ajeji 2 ti tabili iye EAV, bakanna bi atọka GIN fun JSONB iwe.

Imudojuiwọn data ṣe afihan awọn abajade atẹle ni awọn ofin ti akoko (ni ms). Ṣe akiyesi pe iwọn naa jẹ logarithmic:

A rii pe JSONB jẹ pupọ (> 50000-x) yiyara ju EAV ti o ko ba lo awọn atọka, fun idi ti a sọ loke. Nigba ti a ba ṣe atọka awọn ọwọn pẹlu awọn bọtini akọkọ, iyatọ naa fẹrẹ parẹ, ṣugbọn JSONB tun jẹ awọn akoko 1,3 yiyara ju EAV lọ. Ṣe akiyesi pe atọka lori iwe JSONB ko ni ipa nibi nitori a ko lo iwe ohun-ini ninu awọn ibeere igbelewọn.

Fun yiyan data ti o da lori iye ohun-ini, a gba awọn abajade atẹle (iwọn deede):

O le ṣe akiyesi pe JSONB lẹẹkansi ṣiṣẹ yiyara ju EAV laisi awọn atọka, ṣugbọn nigbati EAV pẹlu awọn atọka, o tun ṣiṣẹ yiyara ju JSONB lọ. Ṣugbọn lẹhinna Mo rii pe awọn akoko fun awọn ibeere JSONB jẹ kanna, eyi jẹ ki n lọ si otitọ pe awọn atọka GIN ko ṣiṣẹ. Nkqwe nigbati o ba lo atọka GIN lori iwe kan pẹlu awọn ohun-ini ti o kun, yoo gba ipa nikan nigbati o ba nlo oniṣẹ ẹrọ @>. Mo lo eyi ni idanwo tuntun ati pe o ni ipa nla lori akoko: 0,153ms nikan! Eyi jẹ awọn akoko 15000 yiyara ju EAV ati awọn akoko 25000 yiyara ju oniṣẹ ->>.

Mo ro pe o yara to!

Database iwọn tabili

Jẹ ki a ṣe afiwe awọn iwọn tabili fun awọn ọna mejeeji. Ni psql a le ṣe afihan iwọn gbogbo awọn tabili ati awọn atọka nipa lilo aṣẹ naa dti +

Fun ọna EAV, awọn iwọn tabili wa ni ayika 3068 MB ati awọn atọka to 3427 MB fun apapọ 6,43 GB. Ọna JSONB nlo 1817 MB fun tabili ati 318 MB fun awọn atọka, eyiti o jẹ 2,08 GB. O wa ni akoko 3 kere si! Otitọ yii ya mi lẹnu diẹ nitori pe a tọju awọn orukọ ohun-ini sinu gbogbo nkan JSONB.

Ṣugbọn sibẹ, awọn nọmba naa sọ fun ara wọn: ni EAV a tọju awọn bọtini ajeji odidi 2 fun iye ikasi, ti o mu abajade 8 baiti ti data afikun. Ni afikun, EAV tọju gbogbo awọn iye ohun-ini gẹgẹbi ọrọ, lakoko ti JSONB yoo lo nomba ati awọn iye boolian ni inu nibiti o ti ṣee ṣe, ti o mu abajade ẹsẹ kekere kan.

Awọn esi

Iwoye, Mo ro pe fifipamọ awọn ohun-ini nkankan ni ọna kika JSONB le ṣe apẹrẹ ati mimu ibi ipamọ data rẹ rọrun pupọ. Ti o ba n ṣiṣẹ ọpọlọpọ awọn ibeere, lẹhinna titọju ohun gbogbo ni tabili kanna bi nkan naa yoo ṣiṣẹ daradara siwaju sii. Ati pe otitọ pe eyi jẹ irọrun ibaraenisepo laarin data jẹ afikun tẹlẹ, ṣugbọn ibi ipamọ data ti abajade jẹ awọn akoko 3 kere si ni iwọn didun.

Paapaa, da lori awọn idanwo ti a ṣe, a le pinnu pe awọn adanu iṣẹ jẹ aibikita pupọ. Ni awọn igba miiran, JSONB paapaa yara ju EAV lọ, ti o jẹ ki o dara julọ. Sibẹsibẹ, ala-ilẹ yii dajudaju ko bo gbogbo awọn aaye (fun apẹẹrẹ awọn ile-iṣẹ pẹlu nọmba ti o tobi pupọ ti awọn ohun-ini, ilosoke pataki ninu nọmba awọn ohun-ini ti data ti o wa tẹlẹ,…), nitorinaa ti o ba ni awọn imọran eyikeyi lori bi o ṣe le mu wọn dara si. , jọwọ lero free lati fi ninu awọn comments!

orisun: www.habr.com