EAV-ის შეცვლა JSONB-ით PostgreSQL-ში

TL; DR: JSONB-ს შეუძლია მნიშვნელოვნად გაამარტივოს მონაცემთა ბაზის სქემის შემუშავება შეკითხვის შესრულების შეწირვის გარეშე.

შესავალი

მოდით მოვიყვანოთ რელაციური მონაცემთა ბაზის (მონაცემთა ბაზის) მსოფლიოში ალბათ ერთ-ერთი უძველესი გამოყენების შემთხვევის კლასიკური მაგალითი: ჩვენ გვაქვს ერთეული და უნდა შევინახოთ ამ ერთეულის გარკვეული თვისებები (ატრიბუტები). მაგრამ ყველა ინსტანციას არ შეიძლება ჰქონდეს თვისებების ერთნაირი ნაკრები და მომავალში შეიძლება დაემატოს მეტი თვისება.

ამ პრობლემის გადასაჭრელად უმარტივესი გზაა მონაცემთა ბაზის ცხრილში სვეტის შექმნა თითოეული ქონების მნიშვნელობისთვის და უბრალოდ შეავსოთ ის, რაც საჭიროა კონკრეტული ერთეულის მაგალითისთვის. დიდი! პრობლემა მოგვარებულია... სანამ თქვენი ცხრილი არ შეიცავს მილიონობით ჩანაწერს და თქვენ უნდა დაამატოთ ახალი ჩანაწერი.

განვიხილოთ EAV ნიმუში (ერთეული-ატრიბუტი-მნიშვნელობა), საკმაოდ ხშირად ხდება. ერთი ცხრილი შეიცავს ერთეულებს (ჩანაწერებს), მეორე ცხრილი შეიცავს ქონების სახელებს (ატრიბუტებს), ხოლო მესამე ცხრილი აკავშირებს ერთეულებს მათ ატრიბუტებთან და შეიცავს ამ ატრიბუტების მნიშვნელობას მიმდინარე ერთეულისთვის. ეს გაძლევთ შესაძლებლობას გქონდეთ თვისებების სხვადასხვა ნაკრები სხვადასხვა ობიექტებისთვის და ასევე დაამატოთ თვისებები ფრენის დროს მონაცემთა ბაზის სტრუქტურის შეცვლის გარეშე.

თუმცა, მე არ დავწერდი ამ პოსტს, რომ არ ყოფილიყო EVA მიდგომის უარყოფითი მხარეები. ასე რომ, მაგალითად, ერთი ან მეტი ერთეულის მისაღებად, რომლებსაც აქვთ 1 ატრიბუტი, მოთხოვნაში საჭიროა 2 შეერთება: პირველი არის შეერთება ატრიბუტების ცხრილთან, მეორე არის შეერთება მნიშვნელობების ცხრილთან. თუ ერთეულს აქვს 2 ატრიბუტი, მაშინ საჭიროა 4 შეერთება! გარდა ამისა, ყველა ატრიბუტი, როგორც წესი, ინახება სტრიქონების სახით, რაც იწვევს როგორც შედეგის, ასევე WHERE პუნქტის ტიპების ჩამოყალიბებას. თუ თქვენ წერთ უამრავ შეკითხვას, მაშინ ეს საკმაოდ ფუჭია რესურსების გამოყენების თვალსაზრისით.

ამ აშკარა ნაკლოვანებების მიუხედავად, EAV დიდი ხანია გამოიყენება ამ ტიპის პრობლემების გადასაჭრელად. ეს იყო გარდაუვალი ნაკლოვანებები და უკეთესი ალტერნატივა უბრალოდ არ არსებობდა.
მაგრამ შემდეგ ახალი "ტექნოლოგია" გამოჩნდა PostgreSQL-ში...

PostgreSQL 9.4-დან დაწყებული, JSONB მონაცემთა ტიპი დაემატა JSON ორობითი მონაცემების შესანახად. მიუხედავად იმისა, რომ ამ ფორმატში JSON-ის შენახვას, ჩვეულებრივ, ცოტა მეტი სივრცე და დრო სჭირდება, ვიდრე ჩვეულებრივი ტექსტური JSON, მასზე ოპერაციების შესრულება ბევრად უფრო სწრაფია. JSONB ასევე მხარს უჭერს ინდექსირებას, რაც კითხვებს კიდევ უფრო აჩქარებს.

JSONB მონაცემთა ტიპი საშუალებას გვაძლევს შევცვალოთ უხერხული EAV ნიმუში ჩვენს ერთეულთა ცხრილში მხოლოდ ერთი JSONB სვეტის დამატებით, რაც მნიშვნელოვნად ამარტივებს მონაცემთა ბაზის დიზაინს. მაგრამ ბევრი ამტკიცებს, რომ ამას უნდა ახლდეს პროდუქტიულობის შემცირება... ამიტომ დავწერე ეს სტატია.

სატესტო მონაცემთა ბაზის დაყენება

ამ შედარებისთვის, მე შევქმენი მონაცემთა ბაზა PostgreSQL 9.5-ის ახალი ინსტალაციის შესახებ 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.თვისებები. ამრიგად, ჩვენ მივიღეთ რამდენიმე განსხვავებული ტიპის მონაცემთა ტიპი თვისებების მთელ კომპლექტს შორის. მაგალითი მონაცემები:

{
  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-ით დაგჭირდებათ 2 შეერთება თითოეული ქონებისთვის, რომლის მოთხოვნითაც გსურთ. ჩემი აზრით, ზემოთ მოყვანილი მოთხოვნები აჩვენებს მონაცემთა ბაზის დიზაინში დიდ გამარტივებას. იხილეთ მეტი მაგალითი, თუ როგორ უნდა დაწეროთ JSONB მოთხოვნები, ასევე აქ ეს პოსტი.
ახლა დროა ვისაუბროთ შესრულებაზე.

მოქმედება

შესრულების შესადარებლად გამოვიყენე ახსნა ანალიზი შეკითხვებში, შესრულების დროის გამოსათვლელად. თითოეული მოთხოვნა შესრულდა მინიმუმ სამჯერ, რადგან შეკითხვის დამგეგმავი პირველად უფრო მეტ დროს იღებს. ჯერ შევასრულე მოთხოვნები ყოველგვარი ინდექსების გარეშე. ცხადია, ეს იყო JSONB-ის უპირატესობა, რადგან EAV-სთვის საჭირო შეერთებები ვერ გამოიყენებდა ინდექსებს (უცხო საკვანძო ველები არ იყო ინდექსირებული). ამის შემდეგ შევქმენი ინდექსი EAV მნიშვნელობის ცხრილის 2 უცხო გასაღების სვეტზე, ასევე ინდექსი GIN JSONB სვეტისთვის.

მონაცემთა განახლებამ აჩვენა შემდეგი შედეგები დროის მიხედვით (ms). გაითვალისწინეთ, რომ მასშტაბი ლოგარითმულია:

ჩვენ ვხედავთ, რომ JSONB ბევრად (> 50000-x) უფრო სწრაფია ვიდრე EAV, თუ არ იყენებთ ინდექსებს, ზემოთ აღნიშნული მიზეზის გამო. როდესაც ჩვენ ვდებთ სვეტებს პირველადი კლავიშებით, განსხვავება თითქმის ქრება, მაგრამ JSONB მაინც 1,3-ჯერ უფრო სწრაფია ვიდრე EAV. გაითვალისწინეთ, რომ JSONB სვეტის ინდექსს აქ არანაირი ეფექტი არ აქვს, რადგან ჩვენ არ ვიყენებთ ქონების სვეტს შეფასების კრიტერიუმებში.

ქონების ღირებულების საფუძველზე მონაცემების არჩევისთვის, ჩვენ ვიღებთ შემდეგ შედეგებს (ნორმალური მასშტაბი):

შეგიძლიათ შეამჩნიოთ, რომ JSONB ისევ უფრო სწრაფად მუშაობს ვიდრე EAV ინდექსების გარეშე, მაგრამ როდესაც EAV ინდექსებით, ის მაინც უფრო სწრაფად მუშაობს ვიდრე JSONB. მაგრამ შემდეგ დავინახე, რომ JSONB მოთხოვნების დრო იგივე იყო, ამან მიბიძგა იმაზე, რომ GIN ინდექსები არ მუშაობს. როგორც ჩანს, როდესაც იყენებთ GIN ინდექსს დასახლებული თვისებების მქონე სვეტზე, ის ძალაში შედის მხოლოდ ოპერატორის @> გამოყენებისას. მე ეს გამოვიყენე ახალ ტესტში და მან დიდი გავლენა მოახდინა დროზე: მხოლოდ 0,153 ms! ეს არის 15000-ჯერ უფრო სწრაფი ვიდრე EAV და 25000-ჯერ უფრო სწრაფი ვიდრე ->> ოპერატორი.

მგონი საკმარისად სწრაფი იყო!

მონაცემთა ბაზის ცხრილის ზომა

მოდით შევადაროთ ცხრილის ზომები ორივე მიდგომისთვის. psql-ში ჩვენ შეგვიძლია ვაჩვენოთ ყველა ცხრილისა და ინდექსის ზომა ბრძანების გამოყენებით dti+

EAV მიდგომისთვის, ცხრილის ზომებია დაახლოებით 3068 MB და ინდექსები 3427 MB-მდე, საერთო ჯამში 6,43 GB. JSONB მიდგომა იყენებს 1817 MB ცხრილისთვის და 318 MB ინდექსებისთვის, რაც არის 2,08 GB. გამოდის 3-ჯერ ნაკლები! ამ ფაქტმა ოდნავ გამიკვირდა, რადგან ჩვენ ვინახავთ ქონების სახელებს ყველა JSONB ობიექტში.

მაგრამ მაინც, რიცხვები თავისთავად საუბრობენ: EAV-ში ჩვენ ვინახავთ 2 მთელ უცხოურ გასაღებს ატრიბუტის მნიშვნელობისთვის, რაც იწვევს 8 ბაიტს დამატებით მონაცემს. გარდა ამისა, EAV ინახავს ქონების ყველა მნიშვნელობას ტექსტის სახით, ხოლო JSONB გამოიყენებს ციფრულ და ლოგიკურ მნიშვნელობებს შიგნით, სადაც ეს შესაძლებელია, რაც გამოიწვევს უფრო მცირე ანაბეჭდს.

შედეგები

მთლიანობაში, ვფიქრობ, ერთეულის თვისებების JSONB ფორმატში შენახვამ შეიძლება გააადვილოს თქვენი მონაცემთა ბაზის დიზაინი და შენარჩუნება. თუ თქვენ აწარმოებთ უამრავ მოთხოვნას, მაშინ ყველაფრის შენახვა იმავე ცხრილში, როგორც ერთეული, რეალურად უფრო ეფექტურად იმუშავებს. და ის ფაქტი, რომ ეს ამარტივებს მონაცემებს შორის ურთიერთქმედებას, უკვე პლუსია, მაგრამ შედეგად მიღებული მონაცემთა ბაზა 3-ჯერ უფრო მცირეა მოცულობით.

ასევე, ჩატარებული ტესტებიდან გამომდინარე, შეგვიძლია დავასკვნათ, რომ შესრულების დანაკარგი ძალიან უმნიშვნელოა. ზოგიერთ შემთხვევაში, JSONB უფრო სწრაფია ვიდრე EAV, რაც მას კიდევ უფრო უკეთესს ხდის. თუმცა, ეს მაჩვენებელი, რა თქმა უნდა, არ მოიცავს ყველა ასპექტს (მაგ. თვისებების ძალიან დიდი რაოდენობის მქონე პირები, არსებული მონაცემების თვისებების რაოდენობის მნიშვნელოვანი ზრდა,...), ასე რომ, თუ გაქვთ რაიმე შემოთავაზება მათი გაუმჯობესების შესახებ. , გთხოვთ დატოვოთ კომენტარებში!

წყარო: www.habr.com