🥇استبدال EAV بـ JSONB في PostgreSQL

TL ؛ DR: يمكن لـ JSONB تبسيط تطوير مخطط قاعدة البيانات بشكل كبير دون التضحية بأداء الاستعلام.

مقدمة

فيما يلي مثال كلاسيكي لواحدة من أقدم حالات الاستخدام في عالم قواعد البيانات العلائقية (قاعدة البيانات): لدينا كيان ، ونحتاج إلى حفظ خصائص (سمات) معينة لهذا الكيان. ولكن قد لا تحتوي كل الحالات على نفس مجموعة الخصائص ، علاوة على ذلك ، في المستقبل ، يمكن إضافة المزيد من الخصائص.

تتمثل أسهل طريقة لحل هذه المشكلة في إنشاء عمود في جدول قاعدة البيانات لكل قيمة خاصية ، وملء العمود المطلوب لمثيل كيان معين. عظيم! تم حل المشكلة ... حتى يحتوي جدولك على ملايين السجلات وتحتاج إلى إضافة سجل جديد.

ضع في اعتبارك نمط EAV (الكيان-السمة-القيمة) شائع جدًا. يحتوي أحد الجداول على كيانات (سجلات) ، ويحتوي جدول آخر على أسماء خصائص (سمات) ، ويربط جدول ثالث الكيانات بسماتها ويحتوي على قيمة هذه السمات للكيان الحالي. يمنحك هذا القدرة على الحصول على مجموعات مختلفة من الخصائص لكائنات مختلفة ، بالإضافة إلى إضافة خصائص سريعة دون تغيير بنية قاعدة البيانات.

ومع ذلك ، لن أكتب هذا المنشور إذا لم تكن هناك بعض العيوب في نهج EVA. لذلك ، على سبيل المثال ، للحصول على واحد أو أكثر من الكيانات التي تحتوي كل منها على سمة واحدة ، يلزم وجود صمتين (صلات) في الاستعلام: الأول هو صلة مع جدول بيانات ، والثاني هو صلة بجدول قيم. إذا كان للكيان سمتان ، فستكون هناك حاجة إلى 1 صلات! أيضًا ، يتم عادةً تخزين جميع السمات كسلاسل ، مما ينتج عنه تحويل نوع لكل من النتيجة وعبارة WHERE. إذا كتبت الكثير من الاستفسارات ، فهذا يعد مضيعة للغاية من حيث استخدام الموارد.

على الرغم من أوجه القصور الواضحة هذه ، فقد تم استخدام EAV منذ فترة طويلة لحل هذه الأنواع من المشاكل. كانت هذه عيوب لا مفر منها ، ولم يكن هناك ببساطة بديل أفضل.
ولكن بعد ذلك ظهرت "تقنية" جديدة في PostgreSQL ...

بدءًا من PostgreSQL 9.4 ، تمت إضافة نوع البيانات JSONB لتخزين بيانات JSON الثنائية. أثناء تخزين JSON بهذا التنسيق عادةً ما يستغرق مساحة ووقتًا أكبر قليلاً من JSON للنص العادي ، يكون إجراء العمليات عليه أسرع بكثير. يدعم JSONB أيضًا الفهرسة ، مما يجعل الاستعلام عنها أسرع.

يسمح لنا نوع بيانات JSONB باستبدال نمط EAV المرهق عن طريق إضافة عمود JSONB واحد فقط إلى جدول الكيان الخاص بنا ، مما يبسط تصميم قاعدة البيانات بشكل كبير. لكن الكثيرين يجادلون بأن هذا يجب أن يكون مصحوبًا بانخفاض في الأداء ... لهذا السبب أتيت بهذا المقال.

إنشاء قاعدة بيانات اختبارية

لهذه المقارنة ، قمت بإنشاء قاعدة البيانات على تثبيت حديث لـ PostgreSQL 9.5 على إصدار بقيمة 80 دولارًا. DigitalOcean 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 - HAS.

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

يبدو أسهل بكثير ، أليس كذلك؟ ثم تمت إضافته إلى جداول الكيانات (كيان & كيان _jsonb) 10 ملايين سجل ، وبالتالي ، تم ملؤها بنفس بيانات الجدول حيث يتم استخدام نمط EAV ونهج العمود JSONB - كيان_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 ، نحتاج إلى معرفة كل من معرفات الكيان و معرف الكيان من أجل التحديث. إذا كنت ترغب في تحديث خاصية في عمود 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 ، ستحتاج إلى صلتين لكل خاصية تريد الاستعلام عنها. في رأيي ، تُظهر الاستعلامات أعلاه تبسيطًا كبيرًا في تصميم قاعدة البيانات. شاهد المزيد من الأمثلة حول كيفية كتابة استعلامات JSONB ، انظر أيضًا هذا بريد.
حان الوقت الآن للتحدث عن الأداء.

أداء

لمقارنة الأداء الذي استخدمته اشرح التحليل في الطلبات ، لحساب وقت التنفيذ. تم تشغيل كل استعلام ثلاث مرات على الأقل لأن مخطط الاستعلام يستغرق وقتًا أطول في المرة الأولى. قمت أولاً بتشغيل استعلامات بدون أي فهارس. من الواضح أن هذا كان بمثابة ميزة لـ JSONB ، نظرًا لأن الصلات المطلوبة لـ EAV لا يمكنها استخدام الفهارس (لم تتم فهرسة حقول المفتاح الخارجي). بعد ذلك قمت بإنشاء فهرس على عمودي المفتاح الخارجي لجدول قيمة EAV وكذلك الفهرس GIN لعمود JSONB.

أظهر تحديث البيانات النتائج التالية من حيث الوقت (بالمللي ثانية). لاحظ أن المقياس لوغاريتمي:

استبدال EAV بـ JSONB في PostgreSQL

نرى أن JSONB أسرع بكثير (> 50000-x) من EAV إذا لم يتم استخدام فهارس ، للسبب المذكور أعلاه. عندما نفهرس الأعمدة بالمفاتيح الأساسية ، يختفي الاختلاف تقريبًا ، لكن JSONB لا يزال أسرع 1,3 مرة من EAV. لاحظ أن الفهرس الموجود في عمود JSONB ليس له أي تأثير هنا ، لأننا لا نستخدم عمود خاصية في معايير التقييم.

لاختيار البيانات بناءً على قيمة العقار ، نحصل على النتائج التالية (المقياس العادي):

استبدال EAV بـ JSONB في PostgreSQL

يمكنك أن ترى أن JSONB مرة أخرى أسرع من EAV بدون فهارس ، ولكن عندما يكون EAV مع فهارس ، فإنه لا يزال أسرع من JSONB. ولكن بعد ذلك رأيت أن توقيت طلبات JSONB كان هو نفسه ، مما قادني إلى حقيقة أن مؤشر GIN لم يكن قيد التشغيل. على ما يبدو ، عند استخدام فهرس GIN في عمود بخصائص مأهولة ، فإنه يسري فقط عند استخدام عامل التضمين @>. لقد استخدمت هذا في اختبار جديد كان له تأثير كبير على الوقت: 0,153 مللي ثانية فقط! هذا أسرع بـ 15000 مرة من EAV و 25000 مرة أسرع من عامل التشغيل - >>.

أعتقد أنها كانت سريعة بما فيه الكفاية!

حجم جدول DB

دعونا نقارن أحجام الجدول لكلا النهجين. في psql يمكننا إظهار حجم جميع الجداول والفهارس باستخدام الأمر dti +

استبدال EAV بـ JSONB في PostgreSQL

بالنسبة لنهج EAV ، تبلغ أحجام الطاولات حوالي 3068 ميجابايت والفهارس تصل إلى 3427 ميجابايت بإجمالي 6,43 جيجابايت. يستخدم أسلوب JSONB 1817 ميجابايت للجدول و 318 ميجابايت للفهارس ، وهو 2,08 جيجابايت. اتضح 3 مرات أقل! لقد فاجأتني هذه الحقيقة قليلاً لأننا نقوم بتخزين أسماء الممتلكات في كل كائن JSONB.

ولكن لا تزال الأرقام تتحدث عن نفسها: في EAV ، نقوم بتخزين عدد 2 من المفاتيح الخارجية الصحيحة لكل قيمة سمة ، مما ينتج عنه 8 بايت من البيانات الإضافية. أيضًا ، في EAV ، يتم تخزين جميع قيم الخصائص كنص ، بينما ستستخدم JSONB القيم الرقمية والمنطقية داخليًا حيثما أمكن ، مما ينتج عنه مساحة أصغر.

نتائج

بشكل عام ، أعتقد أن تخزين خصائص الكيانات بتنسيق JSONB يمكن أن يبسط بشكل كبير تصميم وصيانة قاعدة البيانات الخاصة بك. إذا كنت تقوم بالعديد من الاستعلامات ، فإن كل شيء مخزّن في نفس الجدول مثل الكيان سيعمل فعليًا بشكل أكثر كفاءة. وحقيقة أن هذا يبسط التفاعل بين البيانات هو بالفعل زائد ، لكن قاعدة البيانات الناتجة أصغر بثلاث مرات من حيث الحجم.

أيضًا ، وفقًا للاختبارات التي تم إجراؤها ، يمكننا أن نستنتج أن فقدان الأداء ضئيل جدًا. في بعض الحالات ، يكون JSONB أسرع من EAV ، مما يجعله أفضل. ومع ذلك ، لا يغطي هذا المعيار بالطبع جميع الجوانب (على سبيل المثال ، الكيانات التي لديها عدد كبير جدًا من الخصائص ، وزيادة كبيرة في عدد خصائص البيانات الحالية ، ...) ، لذلك إذا كان لديك أي اقتراحات حول كيفية تحسينها ، فيرجى لا تتردد في ترك في التعليقات!

المصدر: www.habr.com