🥇PostgreSQL жүйесінде EAV-ді JSONB-ге ауыстыру

TL; DR: JSONB сұрау өнімділігін жоғалтпай дерекқор схемасын әзірлеуді айтарлықтай жеңілдетеді.

Кіріспе

Реляциялық деректер қорының (деректер базасының) әлемдегі ең көне қолдану жағдайларының бірі болуы мүмкін классикалық мысал келтірейік: бізде нысан бар және біз осы нысанның белгілі бір қасиеттерін (атрибуттарын) сақтауымыз керек. Бірақ барлық даналарда бірдей сипаттар жиыны бола бермейді және болашақта көбірек сипаттар қосылуы мүмкін.

Бұл мәселені шешудің ең оңай жолы - әрбір сипат мәні үшін дерекқор кестесінде баған жасау және нақты нысан данасы үшін қажеттілерді жай ғана толтыру. Тамаша! Мәселе шешілді... кестеде миллиондаған жазбалар болғанша және сізге жаңа жазба қосу керек.

EAV үлгісін қарастырыңыз (Нысан-Атрибут-Мән), ол жиі кездеседі. Бір кестеде нысандар (жазбалар), екінші кестеде сипат атаулары (атрибуттар), үшінші кестеде нысандарды олардың атрибуттарымен байланыстырады және ағымдағы нысан үшін сол атрибуттардың мәні бар. Бұл әртүрлі нысандар үшін әртүрлі сипаттар жиындарына ие болу мүмкіндігін береді, сонымен қатар дерекқор құрылымын өзгертпестен жылдам сипаттарды қосу мүмкіндігін береді.

Дегенмен, EVA тәсілінің кейбір кемшіліктері болмаса, мен бұл жазбаны жазбас едім. Мәселен, мысалы, әрқайсысы 1 төлсипатқа ие бір немесе бірнеше нысанды алу үшін сұрауда 2 біріктіру қажет: біріншісі - төлсипат кестесімен біріктіру, екіншісі - мәндер кестесімен біріктіру. Егер нысанда 2 атрибут болса, онда 4 біріктіру қажет! Сонымен қатар, барлық атрибуттар әдетте жолдар ретінде сақталады, бұл нәтиже үшін де, WHERE сөйлемі үшін де типті трансляциялауға әкеледі. Егер сіз көп сұраулар жазсаңыз, бұл ресурстарды пайдалану тұрғысынан өте ысырап.

Осы айқын кемшіліктерге қарамастан, EAV осы типтегі мәселелерді шешу үшін бұрыннан қолданылған. Бұл сөзсіз кемшіліктер болды және одан жақсы балама жоқ еді.
Бірақ содан кейін PostgreSQL-де жаңа «технология» пайда болды...

PostgreSQL 9.4 нұсқасынан бастап JSON екілік деректерін сақтау үшін JSONB деректер түрі қосылды. JSON-ды осы пішімде сақтау әдеттегі JSON мәтініне қарағанда біршама көбірек орын мен уақытты алады, бірақ ондағы әрекеттерді орындау әлдеқайда жылдамырақ. JSONB сонымен қатар индекстеуді қолдайды, бұл сұрауларды жылдамырақ етеді.

JSONB деректер түрі дерекқор дизайнын айтарлықтай жеңілдете отырып, нысан кестесіне бір ғана JSONB бағанын қосу арқылы қиын EAV үлгісін ауыстыруға мүмкіндік береді. Бірақ көбісі бұл өнімділіктің төмендеуімен қатар жүруі керек деп санайды... Сондықтан мен бұл мақаланы жаздым.

Сынақ деректер базасын орнату

Бұл салыстыру үшін мен деректер базасын PostgreSQL 9.5 жаңа қондырғысында $80 құрастыруда жасадым. DigitalOcean Ubuntu 14.04. Postgresql.conf ішінде кейбір параметрлерді орнатқаннан кейін мен жүгірдім бұл psql көмегімен сценарий. Деректерді 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
);

Төменде JSONB түріндегі бағандағы атрибуттары бар бірдей деректер сақталатын кесте берілген – қасиеттері.

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

Қарапайымырақ көрінеді, солай емес пе? Содан кейін ол нысан кестелеріне қосылды (тұлға & entity_jsonb) 10 миллион жазба және сәйкесінше, кесте EAV үлгісін және JSONB бағанымен тәсілді пайдаланып бірдей деректермен толтырылды - entity_jsonb.properties. Осылайша, біз сипаттардың барлық жиынтығы арасында бірнеше түрлі деректер түрлерін алдық. Мысал деректер:

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

Енді бізде екі нұсқа үшін бірдей деректер бар. Жұмыста іске асыруды салыстыруды бастайық!

Дизайныңызды жеңілдетіңіз

Дерекқор дизайны айтарлықтай жеңілдетілгені туралы бұрын айтылған болатын: EAV үшін үш кестені пайдаланудың орнына сипаттар үшін JSONB бағанын пайдалану арқылы бір кесте. Бірақ бұл сұраныстарда қалай көрінеді? Бір нысан сипатын жаңарту келесідей көрінеді:

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

Көріп отырғаныңыздай, соңғы сұрау оңайырақ көрінбейді. JSONB нысанындағы сипаттың мәнін жаңарту үшін біз функцияны пайдалануымыз керек jsonb_set(), және JSONB нысаны ретінде жаңа мәнімізді беру керек. Дегенмен, алдын ала қандай да бір идентификаторды білудің қажеті жоқ. EAV мысалына қарап, жаңартуды орындау үшін entity_id және entity_attribute_id екеуін де білуіміз керек. Нысан атауына негізделген JSONB бағанындағы сипатты жаңартқыңыз келсе, оның барлығы бір қарапайым жолда орындалады.

Енді оның жаңа түсі негізінде жаңарған нысанды таңдайық:

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

Менің ойымша, біз екіншісінің қысқарақ (қосылу жоқ!), сондықтан оқуға болатындығымен келісе аламыз. JSONB осы жерде жеңеді! Түсті JSONB нысанынан мәтіндік мән ретінде алу үшін JSON ->> операторын қолданамыз. JSONB үлгісінде @> операторы арқылы бірдей нәтижеге жетудің екінші жолы бар:

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

Бұл сәл күрделірек: оның сипаттар бағанындағы JSON нысанында @> операторының оң жағындағы нысан бар-жоғын тексереміз. Аз оқылатын, өнімдірек (төменде қараңыз).

Бір уақытта бірнеше сипаттарды таңдау қажет болғанда JSONB пайдалануды жеңілдетейік. JSONB әдісі шынымен де осы жерде пайда болады: біз біріктіруді қажет етпей, нәтижелер жинағындағы қосымша бағандар ретінде сипаттарды таңдаймыз:

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

EAV көмегімен сіз сұрағыңыз келетін әрбір сипат үшін 2 біріктіру қажет болады. Менің ойымша, жоғарыдағы сұраулар дерекқорды жобалауда үлкен жеңілдетуді көрсетеді. JSONB сұрауларын жазудың қосымша мысалдарын мына жерден қараңыз бұл пост.
Енді өнімділік туралы айтатын кез келді.

өнімділік

Өнімділікті салыстыру үшін мен қолдандым ТАЛДАУДЫ ТҮСІНДІРУ сұрауларда орындау уақытын есептеу үшін. Әрбір сұрау кемінде үш рет орындалды, себебі сұрауды жоспарлаушы бірінші рет ұзағырақ уақыт алады. Алдымен мен сұрауларды индекстерсіз орындадым. Әлбетте, бұл JSONB артықшылығы болды, өйткені EAV үшін қажетті біріктірулер индекстерді пайдалана алмайды (шетелдік кілт өрістері индекстелмеген). Осыдан кейін мен EAV мәндер кестесінің 2 сыртқы кілт бағандарында индексті, сондай-ақ индексті жасадым ЖИН JSONB бағаны үшін.

Деректерді жаңарту уақыт бойынша келесі нәтижелерді көрсетті (мспен). Масштабтың логарифмдік екенін ескеріңіз:

Жоғарыда айтылған себептерге байланысты индекстерді пайдаланбасаңыз, JSONB EAV-ге қарағанда (> 50000-x) жылдамырақ екенін көреміз. Бастапқы кілттері бар бағандарды индекстегенде, айырмашылық дерлік жоғалады, бірақ JSONB әлі де EAV қарағанда 1,3 есе жылдам. JSONB бағанындағы индекс мұнда ешқандай әсер етпейтінін ескеріңіз, өйткені біз бағалау критерийлеріндегі сипат бағанын пайдаланбаймыз.

Сипат мәніне негізделген деректерді таңдау үшін біз келесі нәтижелерді аламыз (қалыпты шкала):

JSONB индекстері жоқ EAV қарағанда жылдамырақ жұмыс істейтінін байқай аласыз, бірақ индекстері бар EAV болғанда, ол JSONB қарағанда жылдамырақ жұмыс істейді. Бірақ содан кейін JSONB сұрауларының уақыттары бірдей екенін көрдім, бұл мені GIN индекстерінің жұмыс істемейтініне итермеледі. Толтырылған сипаттары бар бағанда GIN индексін пайдаланған кезде, ол @> қосу операторын пайдаланғанда ғана күшіне енеді. Мен мұны жаңа сынақта қолдандым және ол уақытқа үлкен әсер етті: бар болғаны 0,153 мс! Бұл EAV-ден 15000 25000 есе және ->> операторынан XNUMX XNUMX есе жылдам.

Менің ойымша, бұл жеткілікті жылдам болды!

Деректер қорының кесте өлшемі

Екі тәсіл үшін кесте өлшемдерін салыстырайық. psql пәрмені арқылы біз барлық кестелер мен индекстердің өлшемін көрсете аламыз dti+

EAV тәсілі үшін кесте өлшемдері шамамен 3068 МБ және индекстері 3427 МБ дейін, жалпы көлемі 6,43 ГБ. JSONB тәсілі кесте үшін 1817 МБ және индекстер үшін 318 МБ пайдаланады, бұл 2,08 ГБ. Бұл 3 есе аз болып шықты! Бұл факт мені аздап таң қалдырды, өйткені біз әрбір JSONB нысанында сипат атауларын сақтаймыз.

Дегенмен, сандар өздері үшін сөйлейді: EAV-де біз әрбір төлсипат мәніне 2 бүтін сыртқы кілт сақтаймыз, нәтижесінде 8 байт қосымша деректер пайда болады. Сонымен қатар, EAV барлық сипат мәндерін мәтін ретінде сақтайды, ал JSONB мүмкіндігінше сандық және логикалық мәндерді іштей пайдаланады, бұл кішірек ізге әкеледі.

Нәтижелері

Тұтастай алғанда, JSONB пішімінде нысан сипаттарын сақтау дерекқорды жобалауды және жүргізуді әлдеқайда жеңілдетеді деп ойлаймын. Егер сіз көп сұрауларды орындап жатсаңыз, онда барлығын нысанмен бір кестеде сақтау іс жүзінде тиімдірек жұмыс істейді. Бұл деректер арасындағы өзара әрекеттесуді жеңілдететін факт қазірдің өзінде плюс, бірақ нәтижесінде алынған дерекқор көлемі жағынан 3 есе аз.

Сондай-ақ, жүргізілген сынақтарға сүйене отырып, өнімділік жоғалтулары өте маңызды емес деп қорытынды жасауға болады. Кейбір жағдайларда JSONB EAV қарағанда жылдамырақ, бұл оны одан да жақсырақ етеді. Дегенмен, бұл эталон, әрине, барлық аспектілерді қамтымайды (мысалы, сипаттарының өте үлкен саны бар нысандар, бар деректердің қасиеттерінің санының айтарлықтай артуы,...), сондықтан оларды жақсарту бойынша ұсыныстарыңыз болса. , түсініктемелерде қалдыруды ұмытпаңыз!

Ақпарат көзі: www.habr.com

PostgreSQL жүйесінде EAV-ді JSONB-мен ауыстыру