PostgreSQL-da EAV-ni JSONB bilan almashtirish

TL; DR: JSONB so'rovlar samaradorligini yo'qotmasdan ma'lumotlar bazasi sxemasini ishlab chiqishni sezilarli darajada soddalashtirishi mumkin.

kirish

Keling, klassik misolni olaylik, ehtimol relyatsion ma'lumotlar bazalari dunyosidagi eng qadimgi foydalanish holatlaridan biri: bizda ob'ekt bor va biz ushbu ob'ektning ma'lum xususiyatlarini (atributlarini) saqlashimiz kerak. Lekin barcha misollar bir xil xususiyatlar to'plamiga ega bo'lmasligi mumkin va kelajakda ko'proq xususiyatlar qo'shilishi mumkin.

Ushbu muammoning eng oddiy yechimi ma'lumotlar bazasi jadvalida har bir xususiyat qiymati uchun ustun yaratish va ma'lum bir ob'ekt namunasi uchun zarur bo'lganlarni to'ldirishdir. Ajoyib! Muammo hal qilindi... jadvalingiz millionlab yozuvlarni o'z ichiga olmaydi va siz yangi yozuv qo'shishingiz kerak bo'lgunga qadar.

Keling, EAV naqshini ko'rib chiqaylik (Ob'ekt-atribut-qiymat), bu juda keng tarqalgan. Bir jadvalda ob'ektlar (yozuvlar), boshqa jadvalda xususiyat nomlari (atributlar) mavjud va uchinchi jadval ob'ektlarni ularning atributlari bilan bog'laydi va joriy ob'ekt uchun ushbu atributlarning qiymatlarini o'z ichiga oladi. Bu sizga ma'lumotlar bazasi strukturasini o'zgartirmasdan, turli ob'ektlar uchun turli xil xususiyatlar to'plamiga ega bo'lish, shuningdek, tezkor xususiyatlarni qo'shish imkonini beradi.

Biroq, EVA yondashuvida ba'zi kamchiliklar bo'lmaganida, men bu postni yozmagan bo'lardim. Masalan, bitta atributli bir yoki bir nechta ob'ektni olish uchun so'rovda ikkita birlashma kerak bo'ladi: birinchisi atributlar jadvali bilan, ikkinchisi qiymatlar jadvali bilan birlashtiriladi. Agar ob'ekt ikkita atributga ega bo'lsa, unda to'rtta birlashma talab qilinadi! Bundan tashqari, barcha atributlar odatda satrlar sifatida saqlanadi, bu esa natija uchun ham, WHERE bandi uchun ham majburlash turiga olib keladi. Agar siz juda ko'p so'rovlar yozsangiz, bu resurslardan foydalanish nuqtai nazaridan juda behuda.

Ushbu aniq kamchiliklarga qaramay, EAV uzoq vaqtdan beri ushbu turdagi muammolarni hal qilish uchun ishlatilgan. Bu muqarrar kamchiliklar edi va shunchaki yaxshiroq alternativa yo'q edi.
Ammo keyin PostgreSQL-da yangi "texnologiya" paydo bo'ldi ...

PostgreSQL 9.4 dan boshlab, ikkilik JSON ma'lumotlarini saqlash uchun JSONB ma'lumotlar turi qo'shildi. JSON-ni ushbu formatda saqlash oddiy matn JSON-ga qaraganda bir oz ko'proq joy va vaqt talab qilsa-da, u bilan ishlash ancha tezdir. JSONB shuningdek, indekslashni qo'llab-quvvatlaydi va so'rovlarni tezroq qiladi.

JSONB ma'lumotlar turi bizning ob'ektlar jadvalimizga faqat bitta JSONB ustunini qo'shish orqali ma'lumotlar bazasi dizaynini sezilarli darajada soddalashtirib, noqulay EAV naqshini almashtirishga imkon beradi. Biroq, ko'pchilik bu ishlash uchun qimmatga tushadi, deb ta'kidlaydi ... Men ushbu maqolani yozishimning sababi.

Test ma'lumotlar bazasini o'rnatish

Ushbu taqqoslash uchun men 80 dollarlik tuzilmada PostgreSQL 9.5 ning yangi o'rnatilishida ma'lumotlar bazasini yaratdim. DigitalOcean Ubuntu 14.04. После настройки некоторых параметров в postgresql.conf я запустил bu psql yordamida skript. Ma'lumotlarni EAV sifatida taqdim etish uchun quyidagi jadvallar yaratildi:

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

Quyida bir xil ma'lumotlar saqlanadigan jadval mavjud, ammo JSONB ustunidagi atributlar bilan - xususiyatlari.

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

Ko'rinishidan ancha sodda, shunday emasmi? Keyin u ob'ektlar jadvallariga qo'shildi (tashkilot & entity_jsonb) 10 million yozuv va shunga mos ravishda jadval bir xil ma'lumotlar bilan to'ldirildi, bu erda EAV naqsh va JSONB ustuniga yondashuv ishlatilgan - entity_jsonb.propertiesShunday qilib, biz barcha xususiyatlar to'plami bo'yicha bir nechta turli xil ma'lumotlar turlarini oldik. Misol ma'lumotlar:

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

Shunday qilib, endi ikkala variant uchun bizda bir xil ma'lumotlar mavjud. Haqiqiy hayotdagi ilovalarni solishtirishni boshlaylik!

Dizaynni soddalashtirish

Ma'lumotlar bazasi dizayni sezilarli darajada soddalashtirilgani haqida avval aytib o'tilgan edi: EAV uchun uchta jadval o'rniga xususiyatlar uchun JSONB ustunidan foydalangan holda bitta jadval. Lekin bu qanday qilib so'rovlarga aylanadi? Bitta ob'ekt xususiyatini yangilash quyidagicha ko'rinadi:

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

Ko'rib turganingizdek, oxirgi so'rov oddiyroq ko'rinmaydi. JSONB obyektidagi xususiyat qiymatini yangilash uchun biz funksiyadan foydalanishimiz kerak jsonb_set(), va bizning yangi qiymatimizni JSONB ob'ekti sifatida o'tkazishimiz kerak. Biroq, hech qanday identifikatorni oldindan bilishimiz shart emas. EAV misolini ko'rib chiqsak, biz yangilashni amalga oshirish uchun entity_id va entity_attribute_id ni bilishimiz kerak. Agar siz JSONB ustunidagi xususiyatni ob'ekt nomiga asoslangan holda yangilamoqchi bo'lsangiz, barchasi bitta oddiy qatorda amalga oshiriladi.

Endi biz yangilagan ob'ektni uning yangi rangi asosida tanlaylik:

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

Menimcha, biz ikkinchisining qisqaroq (qo'shilishsiz!) Va shuning uchun o'qilishi mumkinligiga rozi bo'lishimiz mumkin. JSONB bu erda g'alaba qozonadi! Rangni JSONB obyektidan matn qiymati sifatida olish uchun JSON ->> operatoridan foydalanamiz. JSONB modelida @> operatori yordamida bir xil natijaga erishishning ikkinchi usuli ham mavjud:

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

Bu biroz murakkabroq: xususiyatlar ustunidagi JSON ob'ektida @> operatorining o'ng tomonidagi ob'ekt mavjudligini tekshiramiz. Kamroq o'qilishi mumkin, ko'proq ishlash (pastga qarang).

Bir vaqtning o'zida bir nechta xususiyatni tanlash kerak bo'lganda JSONB-dan foydalanishni yanada soddalashtiraylik. Bu erda JSONB yondashuvi haqiqatan ham porlaydi: biz shunchaki natijalar to'plamida qo'shimcha ustunlar sifatida xususiyatlarni birlashtirmasdan tanlaymiz:

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

EAV bilan siz so'ramoqchi bo'lgan har bir xususiyat uchun ikkita birlashma kerak bo'ladi. Menimcha, yuqoridagi so'rovlar ma'lumotlar bazasini loyihalashda sezilarli soddalashtirilganligini ko'rsatadi. JSONB so'rovlarini qanday yozish haqida ko'proq misollarni ham ko'rishingiz mumkin bu post.
Endi ishlash haqida gapirish vaqti keldi.

Ish unumdorligi

Men foydalangan ishlashni solishtirish uchun TAHLIL QILISHNI TUSHINTIRISH bajarilish vaqtini hisoblash uchun so'rovlarda. Har bir so'rov kamida uch marta bajarilgan, chunki so'rovni rejalashtiruvchi birinchi marta ko'proq vaqt oladi. Birinchidan, men hech qanday indekssiz so'rovlarni bajardim. Bu, shubhasiz, JSONB ning afzalligi bo'lib xizmat qildi, chunki EAV uchun zarur bo'lgan birikmalar indekslardan foydalana olmadi (xorijiy kalit maydonlari indekslanmagan). Shundan so'ng, men EAV qiymatlari jadvalining ikkita tashqi kalit ustunida indeksni, shuningdek indeksni yaratdim. Jin JSONB ustuni uchun.

Ma'lumotlar yangilanishlari quyidagi vaqt natijalarini ko'rsatdi (ms da). O'lchov logarifmik ekanligini unutmang:

Yuqorida aytib o'tilgan sabablarga ko'ra JSONB indekssiz EAV dan sezilarli darajada (>50 000x) tezroq ekanligini ko'ramiz. Birlamchi kalit ustunlarini indekslaganimizda, farq deyarli yo'qoladi, ammo JSONB hali ham EAVga qaraganda 1,3 baravar tezroq. E'tibor bering, JSONB ustunidagi indeks bu erda hech qanday ta'sir ko'rsatmaydi, chunki biz baholash mezonlarida xususiyat ustunidan foydalanmaymiz.

Mulk qiymatiga asoslangan ma'lumotlarni tanlash uchun biz quyidagi natijalarni olamiz (normal shkala):

JSONB yana indekslarsiz EAV dan tezroq ekanligini ko'rishingiz mumkin, lekin EAV indekslanganda ham JSONB dan tezroq. Ammo keyin men JSONB so'rovlari uchun vaqtlar bir xil ekanligini ko'rdim, bu meni GIN indekslari ishga tushirilmasligiga olib keldi. Ko'rinishidan, to'ldirilgan xususiyatlarga ega ustunda GIN indeksidan foydalansangiz, u faqat @> kiritish operatoridan foydalanganda kuchga kiradi. Men buni yangi testda ishlatganman va bu vaqtga katta ta'sir ko'rsatdi: atigi 0,153 ms! Bu EAVdan 15 000 marta va ->> operatoridan 25 000 marta tezroq.

Menimcha, bu juda tez edi!

Ma'lumotlar bazasi jadvallari hajmi

Keling, ikkala yondashuv uchun jadval o'lchamlarini taqqoslaylik. Psql da buyruq yordamida barcha jadvallar va indekslarning o'lchamini ko'rsatishimiz mumkin dti+

EAV yondashuvi bilan jadval o'lchamlari taxminan 3068 MB, indekslar esa 3427 MB gacha, jami 6,43 GB. JSONB yondashuvidan foydalangan holda, jadval 1817 MB va indekslar 318 MB, jami 2,08 GB dan foydalanadi. Bu o'lchamning uchdan bir qismi! Bu fakt meni biroz hayratda qoldirdi, chunki biz har bir JSONB obyektida mulk nomlarini saqlaymiz.

Ammo raqamlar o'zlari uchun gapiradi: EAVda biz har bir atribut qiymatiga ikkita butun sonli tashqi kalitni saqlaymiz, natijada 8 bayt qo'shimcha ma'lumotlar olinadi. Bundan tashqari, EAV-da barcha mulk qiymatlari matn sifatida saqlanadi, JSONB esa iloji bo'lsa, raqamli va mantiqiy qiymatlarni ichki sifatida ishlatadi, bu esa kichikroq maydonga olib keladi.

natijalar

Umuman olganda, ob'ekt xususiyatlarini JSONB formatida saqlash ma'lumotlar bazasini loyihalash va texnik xizmat ko'rsatishni sezilarli darajada soddalashtirishi mumkin deb o'ylayman. Agar siz ko'p so'rovlarni bajarsangiz, hamma narsani ob'ekt bilan bir xil jadvalda saqlash haqiqatan ham samaraliroq bo'ladi. Ma'lumotlar o'zaro ta'sirini soddalashtirishi allaqachon ortiqcha, ammo natijada olingan ma'lumotlar bazasi hajmi ham uch baravar kichikdir.

Bundan tashqari, benchmark natijalariga asoslanib, biz ishlash jazosi juda kichik degan xulosaga kelishimiz mumkin. Ba'zi hollarda JSONB hatto EAVga qaraganda tezroq ishlaydi va bu uni yanada yaxshi qiladi. Biroq, bu mezon, albatta, barcha jihatlarni qamrab olmaydi (masalan, juda ko'p sonli mulkka ega ob'ektlar, mavjud ma'lumotlardagi mulklar sonining sezilarli darajada ko'payishi va boshqalar), shuning uchun sizda yaxshilash bo'yicha takliflaringiz bo'lsa, ularni sharhlarda qoldiring!

Manba: www.habr.com