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, relyatsion ma'lumotlar bazasidan (ma'lumotlar bazasi) dunyodagi eng qadimgi foydalanish holatlaridan biriga klassik misol keltiraylik: 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 muammoni hal qilishning eng oson yo'li - har bir xususiyat qiymati uchun ma'lumotlar bazasi jadvalida 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.

EAV naqshini ko'rib chiqing (Ob'ekt-atribut-qiymat), tez-tez uchraydi. 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 qiymatini o'z ichiga oladi. Bu sizga turli xil ob'ektlar uchun turli xil xususiyatlar to'plamiga ega bo'lish, shuningdek, ma'lumotlar bazasi strukturasini o'zgartirmasdan tezda xususiyatlarni qo'shish imkoniyatini beradi.

Biroq, agar EVA yondashuvining salbiy tomonlari bo'lmaganida, men bu postni yozmagan bo'lardim. Shunday qilib, masalan, har birida 1 ta atributga ega bo'lgan bir yoki bir nechta ob'ektlarni olish uchun so'rovda 2 ta birlashma talab qilinadi: birinchisi atributlar jadvali bilan birlashma, ikkinchisi - qiymatlar jadvali bilan birlashma. Agar ob'ekt 2 ta atributga ega bo'lsa, unda 4 ta birlashma kerak! Bundan tashqari, barcha atributlar odatda satrlar sifatida saqlanadi, bu esa natija uchun ham, WHERE bandi uchun ham turdagi translyatsiyaga olib keladi. Agar siz juda ko'p so'rovlar yozsangiz, bu resurslardan foydalanish nuqtai nazaridan juda behuda.

Ushbu aniq kamchiliklarga qaramay, EAV ushbu turdagi muammolarni hal qilish uchun uzoq vaqtdan beri 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, JSON ikkilik 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, unda operatsiyalarni bajarish ancha tezdir. JSONB shuningdek indekslashni qo'llab-quvvatlaydi, bu esa so'rovlarni yanada 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. Ammo ko'pchilik bu ish unumdorligining pasayishi bilan birga bo'lishi kerakligini ta'kidlaydi ... Shuning uchun men ushbu maqolani yozdim.

Test ma'lumotlar bazasini o'rnatish

Ushbu taqqoslash uchun men 9.5 dollarlik tuzilmada PostgreSQL 80 ning yangi o'rnatilishida ma'lumotlar bazasini yaratdim. DigitalOcean Ubuntu 14.04. Postgresql.conf da ba'zi parametrlarni o'rnatganimdan so'ng men yugurdim bu psql yordamida skript. Ma'lumotlarni EAV shaklida taqdim etish uchun quyidagi jadvallar yaratilgan:

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 tipidagi ustundagi 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 ko'ra, jadval EAV naqsh va JSONB ustuni bilan yondashuv yordamida bir xil ma'lumotlar bilan to'ldirildi - entity_jsonb.properties. Shunday qilib, biz barcha xususiyatlar to'plami orasida 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 biz ikkala variant uchun bir xil ma'lumotlarga egamiz. Keling, ish joyidagi ilovalarni taqqoslashni boshlaylik!

Dizayningizni soddalashtiring

Ilgari ma'lumotlar bazasi dizayni juda soddalashtirilganligi ta'kidlangan edi: EAV uchun uchta jadval o'rniga xususiyatlar uchun JSONB ustunidan foydalangan holda bitta jadval. Ammo bu so'rovlarda qanday aks ettirilgan? 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, yangilashni amalga oshirish uchun biz entity_id va entity_attribute_id ni bilishimiz kerak. Agar siz JSONB ustunidagi xususiyatni ob'ekt nomiga asoslangan holda yangilamoqchi bo'lsangiz, u holda hammasi 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';

O'ylaymanki, biz ikkinchisi qisqaroq (qo'shilish yo'q!) Va shuning uchun ham 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: uning xususiyatlari ustunidagi JSON ob'ektida @> operatorining o'ng tomonida joylashgan ob'ekt mavjudligini tekshiramiz. Kamroq o'qilishi mumkin, samaraliroq (pastga qarang).

Bir vaqtning o'zida bir nechta xususiyatni tanlash kerak bo'lganda JSONB dan foydalanishni yanada osonlashtiraylik. Bu erda JSONB yondashuvi haqiqatan ham paydo bo'ladi: biz qo'shilishlarsiz natijalar to'plamida qo'shimcha ustunlar sifatida xususiyatlarni 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 2 ta ulanish kerak bo'ladi. Menimcha, yuqoridagi so'rovlar ma'lumotlar bazasini loyihalashda katta soddalashtirilganligini ko'rsatadi. JSONB so'rovlarini qanday yozish bo'yicha ko'proq misollarni ham ko'ring bu post.
Endi ishlash haqida gapirish vaqti keldi.

unumdorlik

Ishlashni solishtirish uchun men foydalandim TAHLIL QILISHNI TUSHINTIRISH so'rovlarda, bajarilish vaqtini hisoblash uchun. Har bir so'rov kamida uch marta bajarilgan, chunki so'rovni rejalashtiruvchi birinchi marta ko'proq vaqt oladi. Avval men hech qanday indekssiz so'rovlarni bajardim. Shubhasiz, bu JSONB ning afzalligi edi, chunki EAV uchun zarur bo'lgan birikmalar indekslardan foydalana olmaydi (xorijiy kalit maydonlari indekslanmagan). Shundan so'ng men EAV qiymatlari jadvalining 2 ta tashqi kalit ustunlarida indeksni, shuningdek indeksni yaratdim. Jin JSONB ustuni uchun.

Ma'lumotlarning yangilanishi vaqt bo'yicha (ms da) quyidagi natijalarni ko'rsatdi. O'lchov logarifmik ekanligini unutmang:

Yuqorida aytib o'tilgan sababga ko'ra indekslardan foydalanmasangiz, JSONB EAVga qaraganda ancha (> 50000-x) tezroq ekanligini ko'ramiz. Ustunlarni asosiy kalitlar bilan 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 foydalanmayapmiz.

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

JSONB yana indekslarsiz EAVga qaraganda tezroq ishlashini sezishingiz mumkin, ammo EAV indeksli bo'lsa ham, u JSONBga qaraganda tezroq ishlaydi. Ammo keyin men JSONB so'rovlari uchun vaqtlar bir xil ekanligini ko'rdim, bu meni GIN indekslari ishlamasligiga undadi. Ko'rinishidan, siz 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 EAV dan 15000 25000 marta va ->> operatoridan XNUMX XNUMX marta tezroq.

Menimcha, bu etarlicha tez edi!

Ma'lumotlar bazasi jadvali hajmi

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

EAV yondashuvi uchun jadval o'lchamlari taxminan 3068 MB va indekslari 3427 MB gacha, jami 6,43 GB. JSONB yondashuvi jadval uchun 1817 MB va indekslar uchun 318 MB dan foydalanadi, bu 2,08 GB. Bu 3 barobar kamroq bo'ladi! Bu fakt meni biroz hayratda qoldirdi, chunki biz har bir JSONB obyektida mulk nomlarini saqlaymiz.

Ammo shunga qaramay, raqamlar o'zlari uchun gapiradi: EAV-da biz har bir atribut qiymatiga 2 ta butun tashqi kalitni saqlaymiz, natijada 8 bayt qo'shimcha ma'lumot olinadi. Bundan tashqari, EAV barcha mulk qiymatlarini matn sifatida saqlaydi, JSONB esa iloji bo'lsa, raqamli va mantiqiy qiymatlarni ichkarida ishlatadi, bu esa kichikroq maydonga olib keladi.

natijalar

Umuman olganda, JSONB formatida ob'ekt xususiyatlarini saqlash ma'lumotlar bazasini loyihalash va saqlashni ancha osonlashtiradi deb o'ylayman. Agar siz juda ko'p so'rovlarni bajarayotgan bo'lsangiz, unda hamma narsani ob'ekt bilan bir xil jadvalda saqlash haqiqatan ham samaraliroq ishlaydi. Va bu ma'lumotlar o'rtasidagi o'zaro ta'sirni soddalashtirishi allaqachon ortiqcha, ammo natijada olingan ma'lumotlar bazasi hajmi 3 baravar kichikdir.

Bundan tashqari, o'tkazilgan testlarga asoslanib, biz ishlash yo'qotishlari juda ahamiyatsiz degan xulosaga kelishimiz mumkin. Ba'zi hollarda JSONB EAV dan ham tezroq bo'lib, uni yanada yaxshi qiladi. Biroq, bu mezon, albatta, barcha jihatlarni qamrab olmaydi (masalan, juda ko'p sonli xususiyatlarga ega bo'lgan ob'ektlar, mavjud ma'lumotlarning xususiyatlari sonining sezilarli darajada oshishi, ...), shuning uchun ularni qanday yaxshilash bo'yicha takliflaringiz bo'lsa. , iltimos izohlarda qoldiring!

Manba: www.habr.com