جایگزینی EAV با JSONB در PostgreSQL

TL; DR: JSONB می تواند تا حد زیادی توسعه طرحواره پایگاه داده را بدون به خطر انداختن عملکرد پرس و جو ساده کند.

معرفی

بیایید یک مثال کلاسیک از احتمالاً یکی از قدیمی‌ترین موارد استفاده در دنیای یک پایگاه داده رابطه‌ای (پایگاه داده) ارائه دهیم: ما یک موجودیت داریم، و باید ویژگی‌های خاص (ویژگی‌های) این موجودیت را ذخیره کنیم. اما ممکن است همه نمونه ها دارای مجموعه ای از ویژگی های یکسان نباشند و ممکن است در آینده ویژگی های بیشتری اضافه شود.

ساده ترین راه برای حل این مشکل این است که یک ستون در جدول پایگاه داده برای هر مقدار ویژگی ایجاد کنید و به سادگی آنهایی را که برای یک نمونه خاص مورد نیاز هستند پر کنید. عالی! مشکل حل شد... تا زمانی که جدول شما حاوی میلیون ها رکورد باشد و باید یک رکورد جدید اضافه کنید.

الگوی EAV را در نظر بگیرید (Entity-Attribute-Value، اغلب اتفاق می افتد. یک جدول شامل موجودیت ها (سوابق)، جدول دیگر حاوی نام های دارایی (ویژگی ها) و جدول سوم موجودیت ها را با ویژگی های آنها مرتبط می کند و حاوی مقدار آن ویژگی ها برای موجودیت فعلی است. این به شما این امکان را می‌دهد که مجموعه‌های مختلفی از ویژگی‌ها را برای اشیاء مختلف داشته باشید، و همچنین بدون تغییر ساختار پایگاه داده، ویژگی‌ها را بلافاصله اضافه کنید.

با این حال، اگر نکات منفی رویکرد EVA وجود نداشت، این پست را نمی‌نویسم. بنابراین، به عنوان مثال، برای به دست آوردن یک یا چند موجودیت که هر کدام دارای 1 ویژگی هستند، 2 اتصال در پرس و جو مورد نیاز است: اولی یک اتصال با جدول ویژگی، دومی یک اتصال با جدول مقادیر است. اگر موجودیتی 2 ویژگی داشته باشد، 4 مورد نیاز است! علاوه بر این، همه ویژگی‌ها معمولاً به‌عنوان رشته‌ها ذخیره می‌شوند، که منجر به ریخته‌گری نوع هم برای نتیجه و هم برای عبارت WHERE می‌شود. اگر پرس و جوهای زیادی بنویسید، از نظر استفاده از منابع کاملاً بیهوده است.

با وجود این کاستی های آشکار، EAV مدت هاست که برای حل این نوع مشکلات استفاده می شود. اینها کاستی های اجتناب ناپذیری بودند و جایگزین بهتری وجود نداشت.
اما سپس یک "فناوری" جدید در PostgreSQL ظاهر شد...

با شروع PostgreSQL 9.4، نوع داده JSONB برای ذخیره داده های باینری JSON اضافه شد. اگرچه ذخیره سازی JSON در این قالب معمولاً کمی فضا و زمان بیشتری نسبت به JSON متن ساده می گیرد، اما انجام عملیات روی آن بسیار سریعتر است. JSONB همچنین از نمایه سازی پشتیبانی می کند که پرس و جوها را سریعتر می کند.

نوع داده JSONB به ما امکان می دهد الگوی دست و پا گیر EAV را با افزودن تنها یک ستون JSONB به جدول موجودیت خود جایگزین کنیم و طراحی پایگاه داده را تا حد زیادی ساده کنیم. اما بسیاری استدلال می کنند که این باید با کاهش بهره وری همراه باشد... به همین دلیل این مقاله را نوشتم.

راه اندازی پایگاه داده آزمایشی

برای این مقایسه، من پایگاه داده را بر روی نصب جدید PostgreSQL 9.5 در ساخت 80 دلاری ایجاد کردم. DigitalOcean اوبونتو 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"
  } 
}

بنابراین اکنون داده های یکسانی برای هر دو گزینه داریم. بیایید شروع به مقایسه پیاده سازی ها در محل کار کنیم!

طراحی خود را ساده کنید

قبلاً بیان شد که طراحی پایگاه داده بسیار ساده شده است: یک جدول، با استفاده از یک ستون 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 نمی‌توانستند از شاخص‌ها استفاده کنند (فیلدهای کلید خارجی ایندکس نشده بودند). پس از این، من یک شاخص در 2 ستون کلید خارجی جدول مقدار EAV و همچنین یک شاخص ایجاد کردم. جین برای یک ستون JSONB.

به روز رسانی داده ها نتایج زیر را از نظر زمان (بر حسب میلی ثانیه) نشان داد. توجه داشته باشید که مقیاس لگاریتمی است:

اگر از ایندکس ها استفاده نکنید، می بینیم که JSONB بسیار (> 50000-x) سریعتر از EAV است، به دلیل ذکر شده در بالا. وقتی ستون‌ها را با کلیدهای اصلی ایندکس می‌کنیم، تفاوت تقریباً ناپدید می‌شود، اما JSONB هنوز 1,3 برابر سریع‌تر از EAV است. توجه داشته باشید که شاخص روی ستون JSONB در اینجا تأثیری ندارد زیرا ما از ستون ویژگی در معیارهای ارزیابی استفاده نمی‌کنیم.

برای انتخاب داده ها بر اساس ارزش دارایی، نتایج زیر را دریافت می کنیم (مقیاس عادی):

می توانید متوجه شوید که JSONB دوباره سریعتر از EAV بدون ایندکس کار می کند، اما وقتی EAV با ایندکس ها باشد، همچنان سریعتر از JSONB کار می کند. اما بعد دیدم که زمان پرس و جوهای JSONB یکسان است، این مرا به این واقعیت واداشت که شاخص های GIN کار نمی کنند. ظاهراً هنگامی که از یک شاخص GIN روی ستونی با ویژگی های پر شده استفاده می کنید، تنها زمانی که از عملگر include @> استفاده می کنید تأثیر می گذارد. من از این در یک آزمایش جدید استفاده کردم و تأثیر زیادی در زمان داشت: فقط 0,153 میلی‌ثانیه! این 15000 برابر سریعتر از EAV و 25000 برابر سریعتر از اپراتور ->> است.

فکر کنم به اندازه کافی سریع بود!

اندازه جدول پایگاه داده

بیایید اندازه جدول را برای هر دو رویکرد مقایسه کنیم. در psql می توانیم اندازه تمام جداول و ایندکس ها را با استفاده از دستور نشان دهیم dti+

برای رویکرد EAV، اندازه جدول حدود 3068 مگابایت و نمایه تا 3427 مگابایت برای مجموع 6,43 گیگابایت است. رویکرد JSONB از 1817 مگابایت برای جدول و 318 مگابایت برای ایندکس ها استفاده می کند که 2,08 گیگابایت است. معلوم می شود 3 برابر کمتر! این واقعیت من را کمی شگفت زده کرد زیرا ما نام ویژگی ها را در هر شی JSONB ذخیره می کنیم.

اما با این حال، اعداد برای خود صحبت می کنند: در EAV ما 2 کلید خارجی عدد صحیح را به ازای هر مقدار مشخصه ذخیره می کنیم که منجر به 8 بایت داده اضافی می شود. علاوه بر این، EAV تمام مقادیر ویژگی را به عنوان متن ذخیره می کند، در حالی که JSONB در صورت امکان از مقادیر عددی و بولی به صورت داخلی استفاده می کند و در نتیجه ردپای کوچکتری ایجاد می کند.

نمایش نتایج: از

به طور کلی، من فکر می کنم ذخیره ویژگی های موجودیت در قالب JSONB می تواند طراحی و نگهداری پایگاه داده شما را بسیار آسان تر کند. اگر پرس و جوهای زیادی را اجرا می کنید، نگه داشتن همه چیز در یک جدول با موجودیت در واقع کارآمدتر خواهد بود. و این واقعیت که تعامل بین داده ها را ساده می کند، در حال حاضر یک امتیاز مثبت است، اما پایگاه داده حاصل 3 برابر حجم کمتری دارد.

همچنین بر اساس آزمون های انجام شده می توان نتیجه گرفت که زیان عملکرد بسیار ناچیز است. در برخی موارد، JSONB حتی سریع‌تر از EAV است و آن را حتی بهتر می‌کند. با این حال، این معیار البته همه جنبه‌ها را پوشش نمی‌دهد (مثلاً موجودیت‌هایی با تعداد بسیار زیاد ویژگی‌ها، افزایش قابل توجهی در تعداد ویژگی‌های داده‌های موجود،...)، بنابراین اگر پیشنهادی برای بهبود آنها دارید. ، لطفا در نظرات بنویسید!

منبع: www.habr.com