PostgreSQL मा JSONB सँग EAV प्रतिस्थापन गर्दै

TL; DR: JSONB ले क्वेरी कार्यसम्पादनको त्याग नगरी डाटाबेस स्किमा विकासलाई धेरै सरल बनाउन सक्छ।

परिचय

रिलेशनल डाटाबेस (डेटाबेस) को संसारमा सम्भवतः सबैभन्दा पुरानो प्रयोग केसहरू मध्ये एकको क्लासिक उदाहरण दिऔं: हामीसँग एक संस्था छ, र हामीले यस संस्थाको निश्चित गुणहरू (विशेषताहरू) बचत गर्न आवश्यक छ। तर सबै उदाहरणहरूमा गुणहरूको एउटै सेट नहुन सक्छ, र भविष्यमा थप गुणहरू थप्न सकिन्छ।

यो समस्या समाधान गर्ने सबैभन्दा सजिलो तरिका भनेको प्रत्येक गुण मूल्यको लागि डाटाबेस तालिकामा एउटा स्तम्भ सिर्जना गर्नु हो, र केवल एक विशेष इकाई उदाहरणका लागि आवश्यक पर्नेहरूलाई भर्नुहोस्। महान! समस्या हल भयो... तपाईको तालिकामा लाखौं रेकर्डहरू नभएसम्म र तपाईले नयाँ रेकर्ड थप्न आवश्यक छ।

EAV ढाँचालाई विचार गर्नुहोस् (निकाय-विशेषता-मान), यो धेरै पटक हुन्छ। एउटा तालिकाले संस्थाहरू (रेकर्डहरू) समावेश गर्दछ, अर्को तालिकाले सम्पत्ति नामहरू (विशेषताहरू) समावेश गर्दछ, र तेस्रो तालिकाले संस्थाहरूलाई तिनीहरूको विशेषताहरूसँग सम्बद्ध गर्दछ र हालको संस्थाको लागि ती विशेषताहरूको मूल्य समावेश गर्दछ। यसले तपाईंलाई विभिन्न वस्तुहरूका लागि गुणहरूको विभिन्न सेटहरू राख्ने क्षमता दिन्छ, र डाटाबेस संरचना परिवर्तन नगरिकन फ्लाईमा गुणहरू थप्न सक्छ।

यद्यपि, यदि EVA दृष्टिकोणमा केही डाउनसाइडहरू नभएको भए म यो पोस्ट लेख्ने छैन। त्यसोभए, उदाहरणका लागि, प्रत्येक 1 विशेषता भएको एक वा बढी संस्थाहरू प्राप्त गर्न, क्वेरीमा 2 जोडहरू आवश्यक छन्: पहिलो विशेषता तालिकासँग जोडिएको हो, दोस्रो मान तालिकासँग जोडिएको हो। यदि एक इकाईमा 2 विशेषताहरू छन् भने, त्यसपछि 4 जोडहरू आवश्यक छन्! थप रूपमा, सबै विशेषताहरू सामान्यतया स्ट्रिङको रूपमा भण्डारण गरिन्छ, जसले परिणाम र WHERE क्लज दुवैको लागि टाइप कास्टिङमा परिणाम दिन्छ। यदि तपाइँ धेरै प्रश्नहरू लेख्नुहुन्छ भने, त्यसपछि यो संसाधन प्रयोगको सन्दर्भमा धेरै बेकार हो।

यी स्पष्ट कमजोरीहरूको बावजुद, EAV लामो समयदेखि यी प्रकारका समस्याहरू समाधान गर्न प्रयोग गरिएको छ। यी अपरिहार्य कमजोरीहरू थिए, र त्यहाँ कुनै राम्रो विकल्प थिएन।
तर त्यसपछि PostgreSQL मा नयाँ "प्रविधि" देखा पर्‍यो ...

PostgreSQL 9.4 बाट सुरु गर्दै, JSON बाइनरी डाटा भण्डारण गर्न JSONB डाटा प्रकार थपियो। यद्यपि यो ढाँचामा JSON भण्डारण गर्न सामान्यतया सादा पाठ JSON भन्दा अलि बढी ठाउँ र समय लिन्छ, यसमा सञ्चालनहरू धेरै छिटो हुन्छ। JSONB ले अनुक्रमणिकालाई पनि समर्थन गर्दछ, जसले प्रश्नहरूलाई अझ छिटो बनाउँछ।

JSONB डेटा प्रकारले हामीलाई हाम्रो इकाई तालिकामा केवल एउटा JSONB स्तम्भ थपेर जटिल EAV ढाँचा बदल्न अनुमति दिन्छ, डाटाबेस डिजाइनलाई धेरै सरल बनाउँदै। तर धेरैको तर्क छ कि यो उत्पादकता मा कमी संग हुनु पर्छ ... त्यसैले मैले यो लेख लेखे।

परीक्षण डाटाबेस सेटअप गर्दै

यस तुलनाको लागि, मैले $9.5 बिल्डमा PostgreSQL 80 को नयाँ स्थापनामा डाटाबेस सिर्जना गरें। डिजिटल ओसन 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 स्तम्भको लागि।

डाटा अपडेटले समयको सर्तमा निम्न परिणामहरू देखाएको छ (ms मा)। ध्यान दिनुहोस् कि स्केल लॉगरिदमिक हो:

हामीले देख्छौं कि JSONB EAV भन्दा धेरै (> 50000-x) छिटो छ यदि तपाईंले अनुक्रमणिकाहरू प्रयोग गर्नुभएन भने, माथि उल्लेख गरिएको कारणको लागि। जब हामीले प्राथमिक कुञ्जीहरूसँग स्तम्भहरू अनुक्रमणिका गर्छौं, भिन्नता लगभग गायब हुन्छ, तर JSONB अझै पनि EAV भन्दा 1,3 गुणा छिटो छ। हामीले मूल्याङ्कन मापदण्डमा सम्पत्ति स्तम्भ प्रयोग नगरेकाले JSONB स्तम्भमा भएको अनुक्रमणिकाको यहाँ कुनै प्रभाव छैन भन्ने कुरा याद गर्नुहोस्।

सम्पत्ति मूल्यमा आधारित डेटा चयन गर्नको लागि, हामी निम्न परिणामहरू (सामान्य मापन) प्राप्त गर्छौं:

तपाईले देख्न सक्नुहुन्छ कि JSONB फेरि अनुक्रमणिका बिना EAV भन्दा छिटो काम गर्दछ, तर जब EAV अनुक्रमणिकाहरूसँग, यो अझै पनि JSONB भन्दा छिटो काम गर्दछ। तर त्यसपछि मैले देखे कि JSONB प्रश्नहरूको लागि समय उस्तै थियो, यसले मलाई GIN अनुक्रमणिकाहरूले काम गर्दैन भन्ने तथ्यलाई प्रेरित गर्‍यो। स्पष्ट रूपमा जब तपाइँ आबादी भएका गुणहरू भएको स्तम्भमा GIN अनुक्रमणिका प्रयोग गर्नुहुन्छ, यसले समावेश अपरेटर @> प्रयोग गर्दा मात्र प्रभाव पार्छ। मैले यसलाई नयाँ परीक्षणमा प्रयोग गरें र यसले समयमा ठूलो प्रभाव पारेको थियो: केवल ०.१५३ms! यो EAV भन्दा 0,153 गुणा छिटो र ->> अपरेटर भन्दा 15000 गुणा छिटो छ।

मलाई लाग्छ कि यो पर्याप्त छिटो थियो!

डाटाबेस तालिका आकार

दुवै दृष्टिकोणका लागि तालिका आकारहरू तुलना गरौं। psql मा हामी कमाण्ड प्रयोग गरेर सबै तालिका र अनुक्रमणिकाहरूको आकार देखाउन सक्छौं dti+

EAV दृष्टिकोणको लागि, तालिकाको आकारहरू लगभग 3068 MB छन् र कुल 3427 GB को लागि 6,43 MB सम्म अनुक्रमणिकाहरू छन्। JSONB दृष्टिकोणले तालिकाको लागि 1817 MB र अनुक्रमणिकाहरूको लागि 318 MB प्रयोग गर्दछ, जुन 2,08 GB हो। यो 3 पटक कम बाहिर जान्छ! यो तथ्यले मलाई अलिकति आश्चर्यचकित तुल्यायो किनभने हामी प्रत्येक JSONB वस्तुमा सम्पत्ति नामहरू भण्डार गर्छौं।

तर अझै, संख्याहरू आफैंको लागि बोल्छन्: EAV मा हामी प्रति विशेषता मान 2 पूर्णांक विदेशी कुञ्जीहरू भण्डारण गर्छौं, परिणामस्वरूप थप डेटाको 8 बाइटहरू। थप रूपमा, EAV ले सबै सम्पत्ति मानहरूलाई पाठको रूपमा भण्डारण गर्दछ, जबकि JSONB ले सम्भव भएसम्म संख्यात्मक र बुलियन मानहरू प्रयोग गर्नेछ, परिणामस्वरूप सानो पदचिह्न।

परिणामहरू

समग्रमा, मलाई लाग्छ कि JSONB ढाँचामा इकाई गुणहरू बचत गर्नाले तपाईंको डाटाबेस डिजाइन र मर्मत गर्न धेरै सजिलो बनाउन सक्छ। यदि तपाइँ धेरै प्रश्नहरू चलाइरहनुभएको छ भने, तब सबै कुरालाई एउटै तालिकामा राखेर संस्थाले वास्तवमा अझ प्रभावकारी रूपमा काम गर्नेछ। र तथ्य यो छ कि यसले डेटा बीचको अन्तरक्रियालाई सरल बनाउँछ पहिले नै एक प्लस हो, तर परिणामस्वरूप डाटाबेस भोल्युममा 3 गुणा सानो छ।

साथै, गरिएका परीक्षणहरूको आधारमा, हामी निष्कर्षमा पुग्न सक्छौं कि प्रदर्शन हानिहरू धेरै नगण्य छन्। केहि अवस्थामा, JSONB EAV भन्दा पनि छिटो छ, यसलाई अझ राम्रो बनाउँछ। यद्यपि, यो बेन्चमार्कले पक्कै पनि सबै पक्षहरूलाई समेट्दैन (उदाहरणका लागि धेरै ठूलो संख्यामा गुण भएका संस्थाहरू, अवस्थित डाटाको गुणहरूको सङ्ख्यामा भएको उल्लेखनीय वृद्धि,...), त्यसैले यदि तपाईंसँग तिनीहरूलाई सुधार गर्ने बारे कुनै सुझावहरू छन् भने। , कृपया टिप्पणीहरूमा छोड्न नहिचकिचाउनुहोस्!

स्रोत: www.habr.com