በPostgreSQL ውስጥ EAVን በJSONB በመተካት።

ቲኤል; ዶ/ር፡ JSONB የጥያቄ አፈጻጸምን ሳይቆጥብ የመረጃ ቋት ንድፍ ልማትን በእጅጉ ሊያቃልል ይችላል።

መግቢያ

በዓለም ላይ ካሉት በጣም ጥንታዊ የአጠቃቀም ጉዳዮች መካከል አንዱ የሆነውን የግንኙነት ዳታቤዝ (ዳታቤዝ) አንድ የተለመደ ምሳሌ እንስጥ፡ አንድ አካል አለን እናም የዚህን አካል የተወሰኑ ንብረቶችን (ባህሪያትን) ማስቀመጥ አለብን። ነገር ግን ሁሉም አጋጣሚዎች አንድ አይነት የንብረት ስብስብ ሊኖራቸው አይችልም, እና ለወደፊቱ ተጨማሪ ንብረቶች ሊጨመሩ ይችላሉ.

ይህንን ችግር ለመፍታት ቀላሉ መንገድ ለእያንዳንዱ የንብረት እሴት በመረጃ ቋት ሰንጠረዥ ውስጥ አምድ መፍጠር እና ለአንድ የተወሰነ አካል ምሳሌ የሚያስፈልጉትን ብቻ መሙላት ነው። በጣም ጥሩ! ችግሩ ተፈቷል... ሠንጠረዥዎ በሚሊዮኖች የሚቆጠሩ መዝገቦችን እስኪይዝ ድረስ እና አዲስ መዝገብ ማከል ያስፈልግዎታል።

የ EAV ንድፍን አስቡበት (አካል-መለያ-እሴት) ብዙ ጊዜ ይከሰታል። አንድ ሠንጠረዥ አካላትን (መዝገቦችን) ይይዛል ፣ ሌላ ሠንጠረዥ የንብረት ስሞችን (ባህሪያትን) ይይዛል ፣ እና ሶስተኛው ሠንጠረዥ አካላትን ከባህሪያቸው ጋር ያዛምዳል እና የእነዚህን ባህሪዎች ዋጋ ለአሁኑ አካል ይይዛል። ይህ ለተለያዩ ነገሮች የተለያዩ የንብረት ስብስቦች እንዲኖሮት ይሰጥዎታል, እንዲሁም የውሂብ ጎታውን መዋቅር ሳይቀይሩ በበረራ ላይ ባህሪያትን ይጨምራሉ.

ሆኖም፣ በኢቫ አካሄድ ላይ አንዳንድ አሉታዊ ጎኖች ከሌሉ ይህን ልጥፍ አልጽፍም ነበር። ስለዚህ ፣ ለምሳሌ ፣ እያንዳንዳቸው 1 ባህሪ ያላቸውን አንድ ወይም ከዚያ በላይ አካላትን ለማግኘት ፣ በጥያቄው ውስጥ 2 መጋጠሚያዎች ያስፈልጋሉ-የመጀመሪያው ከባህሪው ሰንጠረዥ ጋር መቀላቀል ነው ፣ ሁለተኛው ከእሴቶች ሰንጠረዥ ጋር መቀላቀል ነው። አንድ አካል 2 ባህሪያት ካሉት 4 መቀላቀል ያስፈልጋል! በተጨማሪም፣ ሁሉም ባህሪያት በተለምዶ እንደ ሕብረቁምፊዎች ይከማቻሉ፣ ይህም ውጤቱን እና WHERE የሚለውን አንቀጽ አይነት መውሰድን ያስከትላል። ብዙ መጠይቆችን ከጻፉ ይህ ከንብረት አጠቃቀም አንፃር በጣም አባካኝ ነው።

ምንም እንኳን እነዚህ ግልጽ ድክመቶች ቢኖሩም, EAV እነዚህን አይነት ችግሮች ለመፍታት ለረጅም ጊዜ ጥቅም ላይ ውሏል. እነዚህ የማይቀሩ ድክመቶች ነበሩ፣ እና በቀላሉ የተሻለ አማራጭ አልነበረም።
ግን ከዚያ በኋላ በ PostgreSQL ውስጥ አዲስ “ቴክኖሎጂ” ታየ…

ከPostgreSQL 9.4 ጀምሮ፣ የJSONB የውሂብ አይነት የJSON ሁለትዮሽ ውሂብን ለማከማቸት ታክሏል። ምንም እንኳን 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
);

በጣም ቀላል ይመስላል ፣ አይደል? ከዚያም ወደ ህጋዊ አካላት ጠረጴዛዎች ተጨምሯል (አካል። & አካል_jsonb) 10 ሚሊዮን መዝገቦች ፣ እና በዚህ መሠረት ፣ ሠንጠረዡ የኢኤቪ ስርዓተ-ጥለት እና አቀራረብን በ 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
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 ምሳሌን ስንመለከት፣ ማሻሻያውን ለማከናወን ሁለቱንም ማንነት_መታወቂያውን እና የህጋዊ አካል_ባህሪ_መታወቂያውን ማወቅ አለብን። በ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 መጠይቆችን እንዴት እንደሚጽፉ ተጨማሪ ምሳሌዎችን ይመልከቱ እንዲሁም በ ውስጥ ይሄ ልጥፍ.
ስለ አፈጻጸም ለመነጋገር ጊዜው አሁን ነው።

ምርታማነት

የተጠቀምኩትን አፈጻጸም ለማነፃፀር ነው። ይተንትኑ ያብራሩ በጥያቄዎች ውስጥ, የማስፈጸሚያ ጊዜን ለማስላት. እያንዳንዱ መጠይቅ ቢያንስ ሶስት ጊዜ ተፈጽሟል ምክንያቱም የመጠይቁ እቅድ አውጪው ለመጀመሪያ ጊዜ ረዘም ያለ ጊዜ ይወስዳል። በመጀመሪያ ጥያቄዎቹን ያለ ምንም ኢንዴክሶች ሄድኩኝ። ለ EAV የሚያስፈልጉት መጋጠሚያዎች ኢንዴክሶችን መጠቀም ስለማይችሉ ይህ የJSONB ጥቅም እንደነበር ግልጽ ነው (የውጭ ቁልፍ መስኮች መረጃ ጠቋሚ አልተደረጉም)። ከዚህ በኋላ በ EAV እሴት ሠንጠረዥ 2 የውጭ ቁልፍ አምዶች ላይ ኢንዴክስ ፈጠርኩኝ እንዲሁም ኢንዴክስ ጂን። ለJSONB አምድ።

የውሂብ ዝማኔው የሚከተሉትን ውጤቶች በጊዜ (በ ms) አሳይቷል። ልኬቱ ሎጋሪዝም ነው፡

ከላይ በተጠቀሰው ምክንያት ኢንዴክሶችን ካልተጠቀሙ JSONB ከ EAV በጣም (> 50000-x) ፈጣን መሆኑን እናያለን። ዓምዶችን ከዋና ቁልፎች ጋር ስንጠቁም ልዩነቱ ሊጠፋ ነው፣ ነገር ግን JSONB አሁንም ከEAV በ1,3 እጥፍ ፈጣን ነው። በግምገማ መስፈርቱ ውስጥ የንብረት አምድ እየተጠቀምን ባለመሆኑ በJSONB ዓምድ ላይ ያለው መረጃ ጠቋሚ እዚህ ምንም ተጽእኖ እንደሌለው ልብ ይበሉ።

በንብረት ዋጋ ላይ በመመስረት መረጃን ለመምረጥ የሚከተሉትን ውጤቶች እናገኛለን (የተለመደው ሚዛን)

JSONB እንደገና ከኢአቪ በበለጠ ፍጥነት እንደሚሰራ ልብ ይበሉ። ግን የ JSONB መጠይቆች ጊዜዎች ተመሳሳይ መሆናቸውን አየሁ ፣ ይህ የጂአይኤን ኢንዴክሶች የማይሰሩ መሆናቸውን እንድገነዘብ አነሳሳኝ። በግልጽ ለማየት እንደሚቻለው የጂአይኤን ኢንዴክስ በሰዎች የተሞሉ ንብረቶች ባለው አምድ ላይ ሲጠቀሙ የሚተገበረው ኦፕሬተር @>ን ማካተት ብቻ ነው። ይህንን በአዲስ ሙከራ ውስጥ ተጠቀምኩ እና በጊዜው ላይ ትልቅ ተጽእኖ ነበረው: 0,153ms ብቻ! ይህ ከ EAV 15000 ጊዜ ፈጣን እና ከ ->> ኦፕሬተር 25000 ጊዜ ፈጣን ነው።

በፍጥነት በቂ ይመስለኛል!

የውሂብ ጎታ ሰንጠረዥ መጠን

ለሁለቱም አቀራረቦች የሰንጠረዡን መጠኖች እናወዳድር. በ psql ውስጥ ትዕዛዙን በመጠቀም የሁሉንም ጠረጴዛዎች እና ኢንዴክሶች መጠን ማሳየት እንችላለን dti+

ለ EAV አቀራረብ፣ የሰንጠረዥ መጠኖች ወደ 3068 ሜባ አካባቢ እና ኢንዴክሶች እስከ 3427 ሜባ በድምሩ 6,43 ጂቢ። የJSONB አቀራረብ ለሠንጠረዡ 1817 ሜባ እና 318 ሜባ ኢንዴክሶችን ይጠቀማል ይህም 2,08 ጊባ ነው። በ 3 እጥፍ ያነሰ ይሆናል! ይህ እውነታ ትንሽ አስገረመኝ ምክንያቱም የንብረት ስሞችን በእያንዳንዱ የJSONB ዕቃ ውስጥ ስለምናከማች።

ግን አሁንም ቁጥሮቹ ለራሳቸው ይናገራሉ-በ EAV ውስጥ 2 ኢንቲጀር የውጭ ቁልፎችን በአንድ ባህሪ እሴት እናከማቻለን ፣ በዚህም ምክንያት 8 ባይት ተጨማሪ ውሂብ ያስገኛል። በተጨማሪም EAV ሁሉንም የንብረት ዋጋዎች እንደ ጽሁፍ ያከማቻል, JSONB ደግሞ የቁጥር እና የቡሊያን እሴቶችን በተቻለ መጠን በውስጥ በኩል ይጠቀማል, ይህም አነስተኛ አሻራ ያመጣል.

ውጤቶች

በአጠቃላይ፣ የህጋዊ አካል ንብረቶችን በJSONB ቅርጸት ማስቀመጥ የውሂብ ጎታዎን መንደፍ እና ማቆየት በጣም ቀላል ያደርገዋል ብዬ አስባለሁ። ብዙ መጠይቆችን እየሮጡ ከሆነ፣ ሁሉንም ነገር ከህጋዊ አካል ጋር በተመሳሳይ ሠንጠረዥ ውስጥ ማስቀመጥ በተጨባጭ በብቃት ይሰራል። እና ይህ በመረጃ መካከል ያለውን መስተጋብር የሚያቃልል መሆኑ ቀድሞውኑ ተጨማሪ ነው ፣ ግን የተገኘው የውሂብ ጎታ በድምጽ መጠን 3 እጥፍ ያነሰ ነው።

እንዲሁም, በተደረጉት ፈተናዎች ላይ በመመስረት, የአፈፃፀም ኪሳራዎች በጣም ትንሽ ናቸው ብለን መደምደም እንችላለን. በአንዳንድ ሁኔታዎች፣ JSONB ከEAV የበለጠ ፈጣን ነው፣ ይህም የበለጠ የተሻለ ያደርገዋል። ነገር ግን፣ ይህ መመዘኛ በእርግጥ ሁሉንም ገፅታዎች አይሸፍንም (ለምሳሌ በጣም ብዙ ንብረቶች ያላቸው አካላት፣ የነባር መረጃዎች ንብረቶች ብዛት ከፍተኛ ጭማሪ፣...)፣ ስለዚህ እነሱን እንዴት ማሻሻል እንደሚችሉ አስተያየት ካሎት ፣ እባክዎን በአስተያየቶቹ ውስጥ ለመተው ነፃነት ይሰማዎ!

ምንጭ: hab.com