TL; DR: JSONB sorğu performansını itirmədən verilənlər bazası sxeminin işlənməsini xeyli sadələşdirə bilər.
Giriş
Relational verilənlər bazası (verilənlər bazası) dünyasında yəqin ki, ən qədim istifadə hallarından birinin klassik nümunəsini verək: bizdə bir varlıq var və bu obyektin müəyyən xassələrini (atributlarını) saxlamalıyıq. Lakin bütün nümunələr eyni xüsusiyyətlər dəstinə malik olmaya bilər və gələcəkdə daha çox xassə əlavə oluna bilər.
Bu problemi həll etməyin ən asan yolu verilənlər bazası cədvəlində hər bir əmlak dəyəri üçün sütun yaratmaq və sadəcə olaraq konkret obyekt nümunəsi üçün lazım olanları doldurmaqdır. Əla! Problem həll olundu... cədvəlinizdə milyonlarla qeyd var və siz yeni qeyd əlavə etməli olana qədər.
EAV modelini nəzərdən keçirin (), olduqca tez-tez baş verir. Bir cədvəldə obyektlər (qeydlər), digər cədvəldə xüsusiyyət adları (atributlar), üçüncü cədvəldə isə obyektlər onların atributları ilə əlaqələndirilir və cari obyekt üçün həmin atributların dəyəri var. Bu, müxtəlif obyektlər üçün müxtəlif xassələr dəstinə sahib olmaq, həmçinin verilənlər bazası strukturunu dəyişmədən xüsusiyyətlər əlavə etmək imkanı verir.
Bununla belə, EVA yanaşmasının bəzi mənfi cəhətləri olmasaydı, bu yazını yazmazdım. Beləliklə, məsələn, hər biri 1 atributu olan bir və ya bir neçə obyekt əldə etmək üçün sorğuda 2 birləşmə tələb olunur: birincisi atribut cədvəli ilə birləşmə, ikincisi dəyərlər cədvəli ilə birləşmədir. Müəssisənin 2 atributu varsa, onda 4 birləşmə lazımdır! Bundan əlavə, bütün atributlar adətən sətirlər kimi saxlanılır ki, bu da həm nəticə, həm də WHERE bəndi üçün növün ötürülməsi ilə nəticələnir. Çoxlu sorğular yazırsınızsa, bu, resurs istifadəsi baxımından olduqca israfdır.
Bu aşkar çatışmazlıqlara baxmayaraq, EAV bu tip problemləri həll etmək üçün çoxdan istifadə edilmişdir. Bunlar qaçılmaz çatışmazlıqlar idi və sadəcə olaraq daha yaxşı alternativ yox idi.
Lakin sonra PostgreSQL-də yeni “texnologiya” peyda oldu...
PostgreSQL 9.4-dən başlayaraq, JSON ikili məlumatlarını saxlamaq üçün JSONB məlumat növü əlavə edildi. JSON-un bu formatda saxlanması adətən düz mətn JSON-dan bir az daha çox yer və vaxt tələb etsə də, üzərində əməliyyatların yerinə yetirilməsi daha sürətli olur. JSONB həmçinin sorğuları daha da sürətləndirən indeksləşdirməni dəstəkləyir.
JSONB məlumat növü bizə verilənlər bazası dizaynını xeyli asanlaşdıraraq obyekt cədvəlimizə yalnız bir JSONB sütunu əlavə etməklə çətin EAV modelini əvəz etməyə imkan verir. Amma çoxları bunun məhsuldarlığın azalması ilə müşayiət olunmalı olduğunu iddia edir... Ona görə də bu yazını yazdım.
Test məlumat bazasının qurulması
Bu müqayisə üçün mən verilənlər bazasını 9.5 dollarlıq quruluşda PostgreSQL 80-in yeni quraşdırılmasında yaratdım. Ubuntu 14.04. Postgresql.conf-da bəzi parametrləri təyin etdikdən sonra qaçdım psql istifadə edərək skript. Məlumatları EAV formasında təqdim etmək üçün aşağıdakı cədvəllər yaradılmışdır:
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
);
Aşağıda eyni məlumatların saxlanılacağı, lakin JSONB tipli sütunda atributları olan bir cədvəl var – xassələri.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Çox sadə görünür, elə deyilmi? Sonra obyekt cədvəllərinə əlavə edildi (varlıq & entity_jsonb) 10 milyon qeyd və müvafiq olaraq, cədvəl EAV nümunəsindən və JSONB sütunu ilə yanaşmadan istifadə edərək eyni məlumatlarla dolduruldu - entity_jsonb.properties. Beləliklə, biz bütün xassələr dəsti arasında bir neçə fərqli məlumat növü əldə etdik. Nümunə məlumat:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Beləliklə, indi hər iki variant üçün eyni məlumatımız var. İş yerində tətbiqləri müqayisə etməyə başlayaq!
Dizaynınızı sadələşdirin
Daha əvvəl bildirilmişdi ki, verilənlər bazası dizaynı çox sadələşdirilmişdir: EAV üçün üç cədvəldən istifadə etmək əvəzinə, xüsusiyyətlər üçün JSONB sütunundan istifadə etməklə bir cədvəl. Bəs bu, müraciətlərdə necə əks olunur? Bir obyekt əmlakının yenilənməsi belə görünür:
-- 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;
Gördüyünüz kimi, sonuncu sorğu daha sadə görünmür. JSONB obyektindəki əmlakın dəyərini yeniləmək üçün funksiyadan istifadə etməliyik , və JSONB obyekti kimi yeni dəyərimizi ötürməlidir. Bununla belə, əvvəlcədən hər hansı identifikatoru bilməyə ehtiyacımız yoxdur. EAV nümunəsinə baxsaq, yeniləməni həyata keçirmək üçün həm entity_id, həm də entity_attribute_id-i bilməliyik. Obyekt adına əsaslanaraq JSONB sütununda xassə yeniləmək istəyirsinizsə, o zaman hər şey bir sadə sətirdə edilir.
İndi yeni rənginə əsasən yenilədiyimiz obyekti seçək:
-- 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';
Düşünürəm ki, biz razılaşa bilərik ki, ikincisi daha qısadır (qoşulmur!), və buna görə də daha oxunaqlıdır. JSONB burada qalib gəlir! Rəngi JSONB obyektindən mətn dəyəri kimi almaq üçün JSON ->> operatorundan istifadə edirik. @> operatorundan istifadə edərək JSONB modelində eyni nəticəni əldə etməyin ikinci yolu da var:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
Bu bir az daha mürəkkəbdir: onun xassələri sütunundakı JSON obyektində @> operatorunun sağında olan obyektin olub-olmadığını yoxlayırıq. Daha az oxunaqlı, daha məhsuldar (aşağıya bax).
Eyni anda birdən çox xassə seçmək lazım olduqda JSONB-dən istifadəni daha da asanlaşdıraq. JSONB yanaşmasının həqiqətən işə düşdüyü yer budur: biz sadəcə olaraq xassələri birləşmələrə ehtiyac olmadan nəticə dəstimizdə əlavə sütunlar kimi seçirik:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
EAV ilə sorğu etmək istədiyiniz hər bir əmlak üçün 2 birləşmə lazımdır. Fikrimcə, yuxarıda göstərilən sorğular verilənlər bazası dizaynında böyük sadələşdirmə göstərir. JSONB sorğularının necə yazılacağına dair daha çox nümunəyə baxın post.
İndi performans haqqında danışmaq vaxtıdır.
Məhsuldarlıq
Performansı müqayisə etmək üçün istifadə etdim sorğularda icra müddətini hesablamaq üçün. Hər sorğu ən azı üç dəfə yerinə yetirildi, çünki sorğu planlayıcısı ilk dəfə daha uzun çəkir. Əvvəlcə sorğuları heç bir indeks olmadan icra etdim. Aydındır ki, bu, JSONB-nin üstünlüyü idi, çünki EAV üçün tələb olunan birləşmələr indekslərdən istifadə edə bilmirdi (xarici əsas sahələr indeksləşdirilməmişdir). Bundan sonra mən EAV dəyər cədvəlinin 2 xarici açar sütununda indeks, eləcə də indeks yaratdım. JSONB sütunu üçün.
Məlumatların yenilənməsi zaman baxımından (ms ilə) aşağıdakı nəticələri göstərdi. Qeyd edək ki, miqyas loqarifmikdir:
Yuxarıda göstərilən səbəbə görə indekslərdən istifadə etməsəniz, JSONB-nin EAV-dan çox (> 50000-x) daha sürətli olduğunu görürük. Sütunları əsas açarlarla indekslədikdə fərq demək olar ki, yox olur, lakin JSONB hələ də EAV-dan 1,3 dəfə sürətlidir. Qeyd edək ki, JSONB sütunundakı indeksin burada heç bir təsiri yoxdur, çünki biz qiymətləndirmə meyarlarında mülkiyyət sütunundan istifadə etmirik.
Mülkiyyət dəyərinə əsaslanan məlumatları seçmək üçün aşağıdakı nəticələri alırıq (normal miqyas):
Siz JSONB-nin indekslər olmadan EAV-dan daha sürətli işlədiyini görə bilərsiniz, lakin EAV indeksləri olan zaman yenə də JSONB-dən daha sürətli işləyir. Ancaq sonra gördüm ki, JSONB sorğuları üçün vaxt eynidir, bu, məni GIN indekslərinin işləməməsinə sövq etdi. Göründüyü kimi, siz məskunlaşmış xassələri olan sütunda GIN indeksindən istifadə etdiyiniz zaman o, yalnız @> daxil operatorundan istifadə etdikdə qüvvəyə minir. Mən bunu yeni testdə istifadə etdim və vaxta böyük təsir etdi: cəmi 0,153 ms! Bu, EAV-dan 15000 dəfə və ->> operatorundan 25000 dəfə sürətlidir.
Düşünürəm ki, kifayət qədər sürətli idi!
Verilənlər bazası cədvəlinin ölçüsü
Gəlin hər iki yanaşma üçün cədvəl ölçülərini müqayisə edək. Psql-də əmrdən istifadə edərək bütün cədvəllərin və indekslərin ölçüsünü göstərə bilərik dti+
EAV yanaşması üçün cədvəl ölçüləri təqribən 3068 MB və cəmi 3427 GB üçün 6,43 MB-a qədər indekslər təşkil edir. JSONB yanaşması cədvəl üçün 1817 MB və indekslər üçün 318 MB istifadə edir ki, bu da 2,08 GB təşkil edir. 3 dəfə az olur! Bu fakt məni bir az təəccübləndirdi, çünki biz əmlak adlarını hər JSONB obyektində saxlayırıq.
Ancaq yenə də rəqəmlər özləri üçün danışır: EAV-da hər atribut dəyərinə 2 tam xarici açar saxlayırıq, nəticədə 8 bayt əlavə məlumat əldə edilir. Əlavə olaraq, EAV bütün əmlak dəyərlərini mətn kimi saxlayır, JSONB isə mümkün olduqda daxili olaraq rəqəmli və boolean dəyərlərdən istifadə edəcək və nəticədə daha kiçik bir iz olacaqdır.
Nəticələri
Ümumilikdə, məncə, obyekt xassələrinin JSONB formatında saxlanması verilənlər bazanızın dizaynını və saxlanmasını xeyli asanlaşdıra bilər. Əgər siz çoxlu sorğu işlədirsinizsə, onda hər şeyi obyektlə eyni cədvəldə saxlamaq əslində daha səmərəli işləyəcək. Və bunun verilənlər arasında qarşılıqlı əlaqəni sadələşdirməsi artıq bir artıdır, lakin nəticədə əldə edilən verilənlər bazası həcm baxımından 3 dəfə kiçikdir.
Həmçinin, həyata keçirilən testlərə əsasən, performans itkilərinin çox əhəmiyyətsiz olduğu qənaətinə gələ bilərik. Bəzi hallarda, JSONB EAV-dan daha sürətlidir və onu daha da yaxşılaşdırır. Bununla belə, bu meyar, əlbəttə ki, bütün aspektləri əhatə etmir (məsələn, çox sayda xassələri olan qurumlar, mövcud məlumatların xassələrinin sayında əhəmiyyətli artım,...), belə ki, onları necə təkmilləşdirmək barədə təklifləriniz varsa , zəhmət olmasa şərhlərdə buraxın!
Mənbə: www.habr.com