په PostgreSQL کې د JSONB سره د EAV ځای په ځای کول

TL;DR: JSONB کولی شي د پوښتنې فعالیت قرباني کولو پرته د ډیټابیس سکیما پراختیا خورا ساده کړي.

پېژندنه

راځئ چې یو کلاسیک مثال واخلو، شاید د اړیکو ډیټابیسونو په نړۍ کې د کارولو ترټولو پخوانیو قضیو څخه یو: موږ یو وجود لرو، او موږ اړتیا لرو چې د دې وجود ځینې ځانګړتیاوې (ځانګړتیاوې) ذخیره کړو. مګر ټول مثالونه ممکن د ملکیتونو ورته سیټ ونه لري، او ممکن په راتلونکي کې نور ملکیتونه اضافه شي.

د دې ستونزې لپاره تر ټولو ساده حل دا دی چې د هر ملکیت ارزښت لپاره په ډیټابیس جدول کې یو کالم جوړ کړئ او په ساده ډول هغه چې د یوې ځانګړې ادارې مثال لپاره اړین دي ډک کړئ. ښه! ستونزه حل شوه ... تر هغه چې ستاسو جدول په ملیونونو ریکارډونه ولري او تاسو اړتیا لرئ یو نوی ریکارډ اضافه کړئ.

راځئ چې د EAV نمونې په پام کې ونیسو (د وجود-ځانګړتیا-ارزښت)، دا خورا عام دی. په یوه جدول کې ادارې (ریکارډونه) شامل دي، په بل جدول کې د ملکیت نومونه (ځانګړتیاوې) شامل دي، او دریم جدول ادارې د دوی ځانګړتیاو سره نښلوي او د اوسني ادارې لپاره د دې ځانګړتیاو ارزښتونه لري. دا تاسو ته اجازه درکوي چې د مختلفو شیانو لپاره د ملکیتونو مختلف سیټونه ولرئ، او همدارنګه د ډیټابیس جوړښت بدلولو پرته، په الوتنه کې ملکیتونه اضافه کړئ.

خو، که چیرې د EVA طریقې کې ځینې نیمګړتیاوې نه وای، زه به دا پوسټ نه وای لیکلی. د مثال په توګه، د یو یا ډیرو ادارو بیرته ترلاسه کول چې هر یو یې یو خاصیت لري، په پوښتنه کې دوه یوځای کیدو ته اړتیا لري: لومړی یوځای کیدل د ځانګړتیا جدول سره، دوهم یوځای کیدل د ارزښت جدول سره. که چیرې یو بنسټ دوه ځانګړتیاوې ولري، نو څلور یوځای کیدو ته اړتیا ده! سربیره پردې، ټول ځانګړتیاوې معمولا د تارونو په توګه زیرمه کیږي، کوم چې د پایلې او WHERE بند دواړو لپاره د ډول جبر پایله لري. که تاسو ډیری پوښتنې ولیکئ، دا د سرچینو کارولو له پلوه خورا ضایع دی.

سره له دې ښکاره نیمګړتیاوو، EAV د اوږدې مودې راهیسې د دې ډول ستونزو د حل لپاره کارول کیږي. دا نه منونکې نیمګړتیاوې وې، او په ساده ډول یې غوره بدیل نه و.
خو بیا په PostgreSQL کې یوه نوې "ټیکنالوژي" راڅرګنده شوه...

د PostgreSQL 9.4 سره پیل کول، د JSONB ډیټا ډول د بائنری JSON ډیټا ذخیره کولو لپاره اضافه شو. که څه هم پدې فارمیټ کې د JSON ذخیره کول معمولا د ساده متن JSON په پرتله یو څه ډیر ځای او وخت نیسي، د دې سره عملیات خورا ګړندي دي. JSONB د انډیکس کولو ملاتړ هم کوي، پوښتنې حتی ګړندي کوي.

د JSONB ډیټا ډول موږ ته اجازه راکوي چې د EAV پیچلي نمونې زموږ د ادارې جدول ته یوازې د JSONB یو واحد کالم اضافه کولو سره بدل کړو، د ډیټابیس ډیزاین د پام وړ ساده کوي. په هرصورت، ډیری استدلال کوي چې دا د فعالیت په لګښت راځي ... دا هغه دلیل دی چې ما دا مقاله لیکلې.

د ازموینې ډیټابیس تنظیم کول

د دې پرتله کولو لپاره، ما ډیټابیس د PostgreSQL 9.5 په تازه نصب کې د $80 جوړیدو په اړه جوړ کړ. ډیجیټل اوسیدونکی Ubuntu ۱۴.۰۴ په 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
);

ډېر ساده ښکاري، نه ده؟ بیا دا د وجود جدولونو ته اضافه شو (نهاد & انټيټي_جيسنب) ۱۰ میلیونه ریکارډونه، او په دې اساس، جدول د ورته معلوماتو څخه ډک شوی و چیرې چې د EAV نمونه او د JSONB کالم سره طریقه کارول شوې وه - د وجود_jsonb.ځانګړتیاوېپه دې توګه، موږ د ملکیتونو په ټوله سیټ کې څو مختلف معلوماتي ډولونه ترلاسه کړل. د مثال په توګه معلومات:

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

نو، اوس موږ د دواړو انتخابونو لپاره ورته معلومات لرو. راځئ چې په حقیقي ژوند کې د پلي کولو پرتله کول پیل کړو!

د ډیزاین ساده کول

مخکې یادونه وشوه چې د ډیټابیس ډیزاین خورا ساده شوی و: یو جدول، د ملکیتونو لپاره د JSONB کالم کارول، د EAV لپاره د دریو جدولونو پرځای. مګر دا څنګه پوښتنو ته ژباړل کیږي؟ د یو واحد واحد ملکیت تازه کول داسې ښکاري:

-- 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 دلته ګټي! موږ د JSON ->> آپریټر کاروو ترڅو د JSONB څیز څخه د متن ارزښت په توګه رنګ بیرته ترلاسه کړو. د 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 سره، تاسو به د هرې ملکیت لپاره چې تاسو یې پوښتنه کول غواړئ دوه یوځای کیدو ته اړتیا ولرئ. زما په نظر، پورته پوښتنې د ډیټابیس ډیزاین کې د پام وړ ساده کولو ښودنه کوي. تاسو کولی شئ د JSONB پوښتنو لیکلو څرنګوالي نور مثالونه هم وګورئ. دا پوسټ.
اوس د فعالیت په اړه د خبرو کولو وخت دی.

محصولات

د هغه فعالیت پرتله کولو لپاره چې ما کارولی و تحلیل تشریح کړئ په پوښتنو کې د اجرا کولو وخت محاسبه کولو لپاره. هره پوښتنه لږترلږه درې ځله اجرا شوه، ځکه چې د پوښتنې پلان جوړونکی په لومړي ځل ډیر وخت نیسي. لومړی، ما پوښتنې پرته له کوم شاخص څخه پرمخ یوړې. دا په څرګنده توګه د JSONB ګټې په توګه کار وکړ، ځکه چې د EAV لپاره اړین یوځایونه شاخصونه نشي کارولی (بهرني کلیدي ساحې شاخص شوي نه وې). له هغې وروسته، ما د EAV ارزښت جدول په دوو بهرنیو کلیدي ستنو کې یو شاخص جوړ کړ، او همدارنګه یو شاخص GIN د JSONB کالم لپاره.

د معلوماتو تازه معلوماتو لاندې وخت پایلې ښودلې (په ms کې). په یاد ولرئ چې پیمانه لوګاریتمیک ده:

موږ ګورو چې JSONB د EAV په پرتله د شاخصونو پرته د پام وړ (>50000x) ګړندی دی، د پورته ذکر شوي دلیل لپاره. کله چې موږ د لومړني کلیدي ستنو شاخصونه شاخص کوو، توپیر تقریبا ورک کیږي، مګر JSONB لاهم د EAV په پرتله 1,3x ګړندی دی. په یاد ولرئ چې د JSONB ستون شاخص دلته هیڅ اغیزه نلري، ځکه چې موږ د ارزونې معیارونو کې د ملکیت ستون نه کاروو.

د ملکیت ارزښت پر بنسټ د معلوماتو انتخاب لپاره، موږ لاندې پایلې ترلاسه کوو (عادي پیمانه):

تاسو لیدلی شئ چې JSONB بیا د EAV په پرتله د شاخصونو پرته ګړندی دی، مګر کله چې EAV شاخص شوی وي، دا لاهم د JSONB په پرتله ګړندی دی. مګر بیا ما ولیدل چې د JSONB پوښتنو لپاره وختونه ورته وو، کوم چې ما دې حقیقت ته لاره هواره کړه چې د GIN شاخصونه نه پیل کیږي. ظاهرا، کله چې تاسو د GIN شاخص په یوه کالم کې د نفوس لرونکي ملکیتونو سره کاروئ، دا یوازې هغه وخت اغیزمن کیږي کله چې د @> شاملولو آپریټر وکاروئ. ما دا په یوه نوي ازموینه کې وکاروله، او دا په وخت باندې لوی اغیزه درلوده: یوازې 0,153 ms! دا د EAV په پرتله 15000 ځله ګړندی او د ->> آپریټر په پرتله 25000 ځله ګړندی دی.

زه فکر کوم چې دا ډېر ګړندی وو!

د ډیټابیس جدولونو اندازه

راځئ چې د دواړو طریقو لپاره د جدول اندازې پرتله کړو. په psql کې، موږ کولی شو د ټولو جدولونو او شاخصونو اندازه د قوماندې په کارولو سره وښیو د dti+

د EAV طریقې سره، د جدول اندازه تقریبا 3068 MB ده، او شاخصونه تر 3427 MB پورې دي، چې ټولټال 6,43 GB دي. د JSONB طریقې په کارولو سره، جدول 1817 MB او شاخصونه 318 MB کاروي، چې ټولټال 2,08 GB دي. دا د اندازې دریمه برخه ده! دې حقیقت ما یو څه حیران کړ، ځکه چې موږ په هر JSONB شی کې د ملکیت نومونه ذخیره کوو.

خو شمېرې پخپله خبرې کوي: په EAV کې، موږ د هر ځانګړتیا ارزښت لپاره دوه عددي بهرنۍ کیلي ذخیره کوو، چې په پایله کې یې 8 بایټ اضافي معلومات ترلاسه کیږي. سربیره پردې، په EAV کې، د ملکیت ټول ارزښتونه د متن په توګه زیرمه شوي، پداسې حال کې چې JSONB به په داخلي توګه عددي او منطقي ارزښتونه وکاروي چیرې چې امکان ولري، چې په پایله کې یې د پښو نښه کوچنۍ وي.

پایلې

په ټولیز ډول، زه فکر کوم چې د JSONB بڼه کې د وجود ملکیتونو ذخیره کول کولی شي ستاسو د ډیټابیس ډیزاین او ساتنه د پام وړ ساده کړي. که تاسو ډیری پوښتنې ترسره کوئ، نو د وجود په څیر په ورته جدول کې هرڅه ذخیره کول به واقعیا ډیر اغیزمن وي. دا حقیقت چې دا د معلوماتو تعاملات ساده کوي دمخه یو پلس دی، مګر پایله لرونکی ډیټابیس هم په اندازې کې درې ځله کوچنی دی.

همدارنګه، د بنچمارک پایلو پراساس، موږ کولی شو پایله وکړو چې د فعالیت جزا ډیره کوچنۍ ده. په ځینو مواردو کې، JSONB حتی د EAV په پرتله ګړندی فعالیت کوي، چې دا نور هم ښه کوي. په هرصورت، دا بنچمارک یقینا ټول اړخونه نه پوښي (د مثال په توګه، هغه ادارې چې د ملکیتونو ډیر شمیر لري، په موجوده معلوماتو کې د ملکیتونو شمیر کې د پام وړ زیاتوالی، او نور)، نو که تاسو د ښه والي لپاره کوم وړاندیزونه لرئ، مهرباني وکړئ په نظرونو کې یې پریږدئ!

سرچینه: www.habr.com