في هذه المقالة ، سوف أفكر في زيادة سرعة التقارير. بواسطة تقرير ، أقصد أي استعلام إلى قاعدة بيانات تستخدم الدالات التجميعية. أيضًا ، سأتطرق إلى القضايا المتعلقة بالموارد التي يتم إنفاقها على إنتاج ودعم التقارير ، سواء البشرية أو الآلية.
في الأمثلة ، سأستخدم مجموعة بيانات تحتوي على 52608000 سجل.
باستخدام مثال الاحتياطيات التحليلية غير الصعبة ، سأثبت أنه حتى الكمبيوتر الضعيف يمكن تحويله إلى أداة جيدة لتحليل كمية "لائقة" من البيانات دون بذل الكثير من الجهد.
بعد إعداد تجارب غير معقدة ، سنرى أن الجدول العادي ليس مصدرًا مناسبًا للاستعلامات التحليلية.
إذا تمكن القارئ من فك رموز الاختصارات OLTP و OLAP بسهولة ، فقد يكون من المنطقي الانتقال مباشرة إلى قسم
Columnstoreطريقتان للعمل مع البيانات
هنا سأكون موجزا ، لأنه يوجد أكثر من معلومات كافية حول هذا الموضوع على الإنترنت.
لذلك ، على أعلى مستوى ، هناك طريقتان فقط للعمل مع البيانات: OLTP و OLAP.
OLTP - يمكن ترجمتها على أنها معالجة المعاملات الفورية. في الواقع ، نحن نتحدث عن المعالجة عبر الإنترنت للمعاملات القصيرة التي تعمل مع كمية صغيرة من البيانات. على سبيل المثال ، تسجيل أو تحديث أو حذف طلب. في الغالبية العظمى من الحالات ، يكون الطلب عبارة عن كمية صغيرة جدًا من البيانات ، وخلالها لا يمكنك أن تخاف من الأقفال الطويلة التي تفرضها RDBMS الحديثة.
OLAP - يمكن ترجمتها على أنها معالجة تحليلية لعدد كبير من المعاملات في وقت واحد. يستخدم أي تقرير هذا النهج بعينه ، لأنه في الغالبية العظمى من الحالات ، ينتج التقرير أرقامًا مجمعة وموجزة لأقسام معينة.
كل نهج له التكنولوجيا الخاصة به. على سبيل المثال ، بالنسبة لـ OLTP ، يعد PostgreSQL ، أما بالنسبة لـ OLAP فهو خدمات تحليل Microsoft SQL Server. بينما يستخدم PostgresSQL تنسيقًا معروفًا لتخزين البيانات في الجداول ، تم اختراع العديد من التنسيقات المختلفة لـ OLAP. هذه هي جداول متعددة الأبعاد ، دلو مملوءة بأزواج ذات قيمة أساسية ومكتبتي المفضلة. حول هذا الأخير بمزيد من التفاصيل أدناه.
لماذا هناك حاجة إلى نهجين؟
وقد لوحظ أن أي مستودع بيانات عاجلاً أم آجلاً يواجه نوعين من التحميل: القراءة المتكررة (الكتابة والتحديث ، بالطبع ، أيضًا) لكميات صغيرة للغاية من البيانات وقراءة نادرة ، لكن كميات كبيرة جدًا من البيانات. في الواقع ، هذا نشاط ، على سبيل المثال ، شباك التذاكر والرأس. يملأ مكتب النقد الذي يعمل طوال اليوم التخزين بقطع صغيرة من البيانات ، في حين أن حجم المتراكمة في نهاية اليوم ، إذا كان العمل يسير على ما يرام ، يصل إلى حجم مثير للإعجاب. بدوره ، يريد المدير في نهاية اليوم معرفة مقدار الأموال التي يكسبها شباك التذاكر في اليوم.
لذلك ، في OLTP لدينا الجداول والفهارس. هاتان الأداتان رائعتان لتسجيل نشاط شباك التذاكر مع كل التفاصيل. توفر الفهارس بحثًا سريعًا عن طلب مسجل مسبقًا ، لذا فإن تغيير الطلب أمر سهل. ولكن من أجل تلبية احتياجات القائد ، نحتاج إلى النظر في كامل كمية البيانات المتراكمة يوميًا. بالإضافة إلى ذلك ، كقاعدة عامة ، لا يحتاج المدير إلى جميع تفاصيل جميع الطلبات. ما يحتاج حقًا إلى معرفته هو مقدار الأموال التي حققها شباك التذاكر بشكل عام. لا يهم أين كان مكتب التذاكر ، وعندما كان هناك استراحة غداء ، الذي كان يعمل من أجله ، إلخ. يوجد OLAP بعد ذلك ، بحيث يتمكن النظام في فترة زمنية قصيرة من الإجابة على السؤال - مقدار ما حققته الشركة ككل دون قراءة متسلسلة لكل طلب وكل تفاصيله. يمكن OLAP استخدام نفس الجداول والفهارس مثل OLTP؟ الجواب لا ، على الأقل لا ينبغي. أولاً ، لأن OLAP لا يحتاج فقط إلى جميع التفاصيل المسجلة في الجداول. يتم حل هذه المشكلة عن طريق تخزين البيانات في تنسيقات أخرى غير الجداول ثنائية الأبعاد. ثانياً ، تنتشر المعلومات التي يتم تحليلها غالبًا عبر جداول مختلفة ، مما يستلزم ارتباطات متعددة ، بما في ذلك الارتباطات من نوع الانضمام الذاتي. لحل هذه المشكلة ، كقاعدة عامة ، يقومون بتطوير مخطط قاعدة بيانات خاص. تم تحسين هذا المخطط لتحميل OLAP ، بالإضافة إلى المخطط العادي العادي لتحميل OLTP.
ماذا يحدث عندما يستخدم OLAP مخطط OLTP
في الواقع ، قمت بتقديم هذا القسم بحيث تفي هذه المقالة بوضوح بمتطلباتي الخاصة بتنسيق هذه المواد ، أي المشكلة ، الحل ، الاستنتاج.
ندرج عددًا من عيوب استخدام مخططات OLTP لتحليل البيانات.
- فهارس كثيرة جدًا
في كثير من الأحيان ، عليك إنشاء فهارس خاصة لدعم التقارير. تطبيق هذه الفهارس نظام تخزين بيانات OLAP. لا يتم استخدامها من قبل جزء OLTP من التطبيق ، بينما يمارسون عبءًا عليه ، مما يتطلب دعمًا ثابتًا ويشغلون مساحة على القرص. - يتجاوز مقدار البيانات المقروءة المطلوبة.
- عدم وجود مخطط بيانات واضح.
والحقيقة هي أنه في كثير من الأحيان يتم نشر المعلومات المقدمة من التقارير في شكل واحد في جداول مختلفة. هذه المعلومات تتطلب تحول مستمر على الطاير. أبسط مثال على ذلك هو مقدار الإيرادات ، والتي تتكون من أموال نقدية وغير نقدية. ومن الأمثلة الصارخة الأخرى التسلسل الهرمي للبيانات. لأن تطوير التطبيق هو تقدم وليس من المعروف دائمًا ما ستكون هناك حاجة إليه في المستقبل ، ويمكن تخزين التسلسل الهرمي نفسه في المعنى في جداول مختلفة. وعلى الرغم من استخدام عملية الاستحواذ على الإنترنت بنشاط في OLAP ، فهذه أشياء مختلفة قليلاً. - استعلام مفرط التعقيد.
لأن يختلف نظام OLTP عن نظام OLAP ، وهناك حاجة إلى طبقة برمجية مرتبطة بشدة تنقل نظام بيانات OLTP إلى النموذج الصحيح. - تعقيد الدعم والتصحيح والتطوير.
بشكل عام ، يمكننا القول أنه كلما كانت قاعدة الشفرات أكثر تعقيدًا ، زادت صعوبة الحفاظ عليها في حالة صحية. هذه بديهية. - تعقيد تغطية الاختبار.
تم كسر الكثير من النسخ بسبب المناقشات التي دارت حول كيفية الحصول على قاعدة بيانات مليئة بجميع البرامج النصية للاختبار ، ولكن من الأفضل القول أن وجود نظام أبسط للبيانات قد تم تبسيط مهمة التغطية مع الاختبارات عدة مرات. - لا نهاية لها تصحيح الأداء.
هناك احتمال كبير بأن يقوم المستخدم بطلب تقرير "كبير" لخادم قاعدة البيانات. يزيد هذا الاحتمال بمرور الوقت. تجدر الإشارة إلى أن OLAP عرضة أيضًا لهذه المشكلة ، ولكن على عكس OLTP ، يكون مورد OLAP في هذه المسألة أعلى من ذلك بكثير.
Columnstoreسوف تركز هذه المقالة على تنسيق تخزين أعمدة ، ولكن دون تفاصيل منخفضة المستوى. الأشكال الأخرى المذكورة أعلاه تستحق الاهتمام أيضًا ، ولكن هذا موضوع لمقال آخر.
في الواقع ، كان معروفًا بتنسيق مكتبة العمود منذ 30 عامًا ، لكن لم يتم تطبيقه في RDBMS حتى وقت قريب. جوهر متجر الأعمدة هو أن البيانات لا يتم تخزينها في الصفوف ، ولكن في الأعمدة. أي على صفحة واحدة (كل 8 كيلو بايت المعروفة) يسجل الخادم بيانات حقل واحد فقط. وهكذا مع كل حقل في الجدول بدوره. هذا ضروري حتى لا تضطر إلى قراءة معلومات إضافية. دعنا نتخيل جدولًا يحتوي على 10 حقول واستعلام يحتوي على حقل واحد فقط محدد في عبارة SELECT. إذا كان جدولًا عاديًا تم حفظه بتنسيق يستند إلى الصف ، فسيضطر الخادم إلى قراءة جميع الحقول العشرة ، ولكن في نفس الوقت يتم إرجاع حقل واحد فقط. اتضح أن الخادم قرأ 9 مرات معلومات أكثر مما كان ضروريًا. Columnstore يحل تماما هذه المشكلة ، ل يسمح لك تنسيق التخزين بقراءة حقل واحد تم طلبه كل هذا يحدث لأن وحدة التخزين في RDBMS هي صفحة. أي يقوم الخادم دائمًا بكتابة وقراءة صفحة واحدة على الأقل. والسؤال الوحيد هو عدد الحقول الموجودة عليه.
كيف Columnstore يمكن أن تساعد حقا
للإجابة على هذا يجب أن يكون لديك أرقام دقيقة. هيا بنا. ولكن ما هي الأرقام التي يمكن أن تعطي صورة دقيقة؟
- مقدار مساحة القرص.
- أداء الاستعلام.
- خطأ التسامح.
- سهولة التنفيذ.
- ما هي المهارات الجديدة التي يجب على المطور أن يعملها مع هياكل جديدة.
مساحة القرص
دعنا ننشئ جدولًا بسيطًا ، ونملأه بالبيانات وتحقق من المساحة التي يستغرقها.
create foreign table cstore_table ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz');
كما لاحظت ، قمت بإنشاء جدول خارجي. الحقيقة هي أن PostgreSQL ليس لديه دعم مدمج في متجر الكتب. لكن PostgreSQL لديه نظام قوي للامتدادات. واحد منهم يجعل من الممكن إنشاء جداول أعمدة. الروابط في نهاية المقال.
- pglz - يخبر الامتداد بأنه يجب ضغط البيانات باستخدام الخوارزمية المدمجة في PostgreSQL ؛
- وقت المعاملة
- المرجع ، ذلك ، wh - المقاطع التحليلية أو القياسات ؛
- m1 ، m2 ، m3 ، m4 ، m5 - مؤشرات أو مقاييس رقمية ؛
دعنا نضيف كمية "لائقة" من البيانات ونرى مقدار المساحة التي تأخذها على القرص. في الوقت نفسه ، نتحقق من أداء الإدراج. لأن أضع تجاربي على كمبيوتر محمول منزلي ، فأنا عضو في كمية البيانات. بالإضافة إلى ذلك ، وهو أمر جيد ، سأستخدم HDD الذي يشغل الضيف OS Fedora 30. مضيف OS - Windows 10 Home Edition. المعالج Intel Core 7. تلقى Guest OS 4 جيجابايت من ذاكرة الوصول العشوائي. إصدار PostgreSQL - PostgreSQL 10.10 على x86_64-pc-linux-gnu ، تم تجميعه بواسطة gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1) ، 64 بت. سأختبر مجموعة بيانات تحتوي على عدد السجلات 52 608 000.
explain (analyze) insert into cstore_table select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d;
ستكون خطة التنفيذ على النحو التالي
أدخل على cstore_table (التكلفة = 0.01..24902714242540.01 الصفوف = 1000000000000000 العرض = 150) (الوقت الفعلي = 119560.456..119560.456 الصفوف = 0 حلقات = 1)
----> حلقة متداخلة (التكلفة = 0.01..24902714242540.01 الصفوف = 1000000000000000 العرض = 150) (الوقت الفعلي = 1.823..22339.976 الصفوف = 52608000 حلقات = 1)
----------> وظيفة المسح الضوئي على gener_series d (التكلفة = 0.00..10.00 الصفوف = 1000 العرض = 4) (الوقت الفعلي = 0.151..2.198 الصفوف = 1096 حلقة = 1)
----------> تجسيد (التكلفة = 0.01..27284555030.01 الصفوف = 1000000000000 العرض = 16) (الوقت الفعلي = 0.002..3.196 الصفوف = 48000 حلقة = 1096)
----------------> حلقة متداخلة (التكلفة = 0.01..17401742530.01 الصفوف = 1000000000000 عرض = 16) (الوقت الفعلي = 1.461..15.072 الصفوف = 48000 حلقة = 1)
----------------------> وظيفة المسح الضوئي على gener_series عليه (التكلفة = 0.00..10.00 الصفوف = 1000 عرض = 4) (الوقت الفعلي = 1.159..2.007 الصفوف = 4000 حلقة = 1)
----------------------> تحقق (التكلفة = 0.01..26312333.01 الصفوف = 1،000،000،000 عرض = 12) (الوقت الفعلي = 0.000..0.001 الصفوف = 12 حلقة = 4000)
----------------------------> حلقة متداخلة (التكلفة = 0.01..16429520.01 صفوف = 1،000،000،000 عرض = 12) (الوقت الفعلي = 0.257 ..0.485 صفًا = 12 حلقة = 1)
----------------------------------> وظيفة المسح الضوئي على gener_series wh (التكلفة = 0.00..10.00 الصفوف = 1000 العرض = 4) (الوقت الفعلي = 0.046..0.049 الصفوف = 3 حلقات = 1)
----------------------------------> تحقق (التكلفة = 0.01..28917.01 الصفوف = 1،000،000 العرض = 8) (الوقت الفعلي = 0.070..0.139 صفوف = 4 حلقات = 3)
---------------------------------------> حلقة متداخلة (التكلفة = 0.01..20010.01 الصفوف = 1000000 عرض = 8) (الوقت الفعلي = 0.173..0.366 صفوف = 4 حلقات = 1)
-------------------------------------------> وظيفة المسح الضوئي على gener_series المرجع ( التكلفة = 0.00..10.00 صفوف = 1000 عرض = 4) (الوقت الفعلي = 0.076..0.079 صفوف = حلقتان = 1)
---------------------------------------------> وظيفة المسح الضوئي على gener_series ORG (التكلفة = 0.00..10.00 صفوف = 1000 عرض = 4) (الوقت الفعلي = 0.043..0.047 صفوف = حلقتان = 2)
وقت التخطيط: 0.439 مللي ثانية
مدة التنفيذ: 119692.051 مللي ثانية
إجمالي الوقت الرصاص - 1.994867517 دقيقة
وقت إنشاء مجموعة البيانات - 22.339976 ثانية
وقت الإدراج - 1.620341333 دقيقة
لم أتمكن من تقييم مساحة القرص المشغولة باستخدام وظائف PostgreSQL. لست متأكدًا من السبب ، ولكن عرض 0. ولعل هذا هو السلوك القياسي للجداول الخارجية. تستخدم لمدير الملفات هذا. لذلك ، حجم مساحة القرص المحتلة هو 226.2 ميغابايت. لتقييم الكثير أو قليلاً ، دعنا نقارن ذلك بجدول منتظم.
explain (analyze) create table rbstore_table as select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d;
ستكون خطة التنفيذ على النحو التالي
حلقة متداخلة (التكلفة = 0.01..22402714242540.01 صفوف = 1000000000000000 عرض = 44) (الوقت الفعلي = 0.585..23781.942 صفوف = 52608000 حلقات = 1)
---> وظيفة المسح الضوئي على gener_series d (التكلفة = 0.00..10.00 الصفوف = 1000 عرض = 4) (الوقت الفعلي = 0.091..2.130 الصفوف = 1096 حلقة = 1)
---> تجسيد (التكلفة = 0.01..27284555030.01 صفوف = 1000000000000 عرض = 16) (الوقت الفعلي = 0.001..3.574 صفوف = 48000 حلقة = 1096)
----------> حلقة متداخلة (التكلفة = 0.01..17401742530.01 الصفوف = 1000000000000 عرض = 16) (الوقت الفعلي = 0.489..14.044 الصفوف = 48000 حلقة = 1)
----------------> وظيفة المسح الضوئي على gener_series عليه (التكلفة = 0.00..10.00 الصفوف = 1000 عرض = 4) (الوقت الفعلي = 0.477..1.352 الصفوف = 4000 حلقة = 1 )
----------------> تجسيد (التكلفة = 0.01..26312333.01 صفوف = 1000000000 عرض = 12) (الوقت الفعلي = 0.000..0.001 صفوف = 12 حلقة = 4000)
----------------------> حلقة متداخلة (التكلفة = 0.01..16429520.01 الصفوف = 1،000،000،000 عرض = 12) (الوقت الفعلي = 0.010..0.019 الصفوف = 12 حلقات = 1)
----------------------------> وظيفة المسح الضوئي على gener_series wh (التكلفة = 0.00..10.00 الصفوف = 1000 عرض = 4) (الفعلي الوقت = 0.003..0.003 الصفوف = 3 حلقات = 1)
----------------------------> تحقق (التكلفة = 0.01..28917.01 الصفوف = 1،000،000 عرض = 8) (الوقت الفعلي = 0.002. .0.004 صفوف = 4 حلقات = 3)
----------------------------------> حلقة متداخلة (التكلفة = 0.01..20010.01 الصفوف = 1،000،000 عرض = 8 ) (الوقت الفعلي = 0.006..0.009 صفوف = 4 حلقات = 1)
----------------------------------------> وظيفة المسح الضوئي على gener_series المرجع (التكلفة = 0.00 .. 10.00 صفوف = 1000 عرض = 4) (الوقت الفعلي = 0.002..0.002 صفوف = حلقتان = 1)
----------------------------------------> وظيفة المسح الضوئي على توليد orgs (التكلفة = 0.00 .. 10.00 صفوف = 1000 عرض = 4) (الوقت الفعلي = 0.001..0.001 صفوف = 2 حلقات = 2)
وقت التخطيط: 0.569 مللي ثانية
مدة التنفيذ: 378883.989 مللي ثانية
الوقت الذي يقضيه في تنفيذ هذه الخطة لا يهمنا ، لأنه في الحياة الحقيقية ، لا يُفترض أن تكون مثل هذه الإدخالات. نحن مهتمون بمساحة القرص التي يشغلها هذا الجدول. بعد تلبية طلب وظائف النظام ، تلقيت 3.75 جيجابايت.
لذلك ، cstore_table - 226 ميغابايت ، rbstore_table - 3.75 جيجابايت. الفارق 16.99 مرة لافت للنظر ، ولكن من غير المرجح أن يتم الحصول على نفس الفرق في الإنتاج ، ويرجع ذلك في المقام الأول إلى توزيع البيانات. كقاعدة عامة ، سيكون هذا الاختلاف أقل وسيكون حوالي 5 مرات.
لكن انتظر ، لا أحد يستخدم البيانات الأولية بتنسيق قائم على الصف لأغراض التحليل. على سبيل المثال ، يحاولون استخدام البيانات المفهرسة لإعداد التقارير. ومنذ ذلك الحين ستكون دائمًا البيانات "الأولية" ، تحتاج إلى مقارنة الأحجام مع أحجام المؤشرات. لنقم بإنشاء فهرس واحد على الأقل. فليكن فهرسًا في حقل التاريخ ونوع العملية - trd + op.
لذلك ، قمت بفهرسة حقلين فقط ، واستغرق الفهرس 1583 ميغابايت ، وهو أكثر بكثير من cstore_table. ولكن ، كقاعدة عامة ، أكثر من فهرس مطلوب لتحميل OLAP. سيكون من المناسب أن نلاحظ هنا أن cstore_table لا يحتاج إلى فهرسة إضافية. هذا الجدول بمثابة فهرس يغطي أي استفسارات.
من كل ما سبق ، يمكن إجراء استنتاج بسيط - باستخدام جداول متجر الكتب ، يمكنك تقليل مساحة القرص المستخدمة.
أداء الاستعلام
لتقييم الأداء ، لنقم بتشغيل استعلام يُرجع بيانات الملخص لشهر معين لنوع معين من العمليات.
explain (analyze, costs, buffers) select sum(m1) from cstore_table where trd = '2011-01-01' and op = 1;
ستكون خطة التنفيذ على النحو التالي
إجمالي (التكلفة = 793602.69..793602.70 صفوف = 1 عرض = 32) (الوقت الفعلي = 79.708..79.708 صفوف = 1 حلقات = 1)
--Buffers: ضرب المشتركة = 44226
---> فحص خارجي على cstore_table (التكلفة = 0.00..793544.70 الصفوف = 23197 العرض = 5) (الوقت الفعلي = 23.209..76.628 الصفوف = 24000 حلقة = 1)
-------- Filter: ((trd = '2011-01-01' :: date) AND (op = 1))
-------- الصفوف إزالتها بواسطة عامل التصفية: 26000
-------- CStore File: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16417
-------- CStore حجم الملف: 120818897
-------- المخازن المؤقتة: ضربة مشتركة = 44226
وقت التخطيط: 0.165 مللي ثانية
مدة التنفيذ: 79.887 مللي ثانية
و
explain (analyze, costs, buffers) select sum(m1) from rbstore_table where trd = '2011-01-01' and op = 1;
ستكون خطة التنفيذ على النحو التالي
إجمالي (التكلفة = 40053.80..40053.81 صفوف = عرض واحد = 8) (الوقت الفعلي = 389.183..389.183 صفوف = 1 حلقات = 1)
- العارضون: قراءة مشتركة = 545
---> فهرس المسح الضوئي باستخدام trd_op_ix على rbstore_table (التكلفة = 0.56..39996.70 الصفوف = 22841 العرض = 4) (الوقت الفعلي = 55.955..385.283 الصفوف = 24000 حلقة = 1)
-------- Index Cond: ((trd = '2011-01-01 00:00:00' :: الطابع الزمني بدون منطقة زمنية) AND (op = 1))
-------- المخازن المؤقتة: قراءة مشتركة = 545
وقت التخطيط: 112.175 مللي ثانية
مدة التنفيذ: 389.219 مللي ثانية
389.219 مللي ثانية مقابل 79.887 مللي ثانية. نرى هنا أنه حتى على كمية صغيرة نسبياً من بيانات متجر الأعمدة ، فإن الجدول يكون أسرع بكثير من فهرس في جدول قائم على الصفوف.
دعنا نغير الطلب ونحاول الحصول على الوحدة لعام 2011 بأكمله.
explain (analyze, costs, buffers) select sum(m1) from cstore_table where trd between '2011-01-01' and '2011-12-31' and op = 1;
ستكون خطة التنفيذ على النحو التالي
إجمالي (التكلفة = 946625.58..946625.59 صفوف = 1 عرض = 32) (الوقت الفعلي = 3123.604..3123.604 صفوف = 1 حلقات = 1)
--Buffers: ضرب المشتركة = 44226
---> فحص خارجي على cstore_table (التكلفة = 0.00..925064.70 الصفوف = 8624349 العرض = 5) (الوقت الفعلي = 21.728..2100.665 الصفوف = 8760000 حلقات = 1)
-------- Filter: ((trd> = '2011-01-01' :: date) AND (trd <= '2011-12-31' :: date) AND (op = 1))
-------- الصفوف إزالتها بواسطة عامل التصفية: 8760000
-------- CStore File: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16411
-------- CStore حجم الملف: 120818897
-------- المخازن المؤقتة: ضربة مشتركة = 44226
وقت التخطيط: 0.212 مللي ثانية
مدة التنفيذ: 3123.960 مللي ثانية
و
explain (analyze, costs, buffers) select sum(m1) from rbstore_table where trd between '2011-01-01' and '2011-12-31' and op = 1;
ستكون خطة التنفيذ على النحو التالي
إنهاء التجميع (التكلفة = 885214.33..885214.34 الصفوف = 1 العرض = 8) (الوقت الفعلي = 98512.560..98512.560 الصفوف = 1 حلقات = 1)
- العارضون: الضرب المشترك = 2565 قرأ = 489099
---> جمع (التكلفة = 885214.12..885214.33 الصفوف = 2 عرض = 8) (الوقت الفعلي = 98427.034..98523.194 الصفوف = 3 حلقات = 1)
-------- العمال المخطط: 2
-------- بدأ العمال: 2
-------- المخازن المؤقتة: إصابة مشتركة = 2565 قراءة = 489099
---------> التجميع الجزئي (التكلفة = 884214.12..884214.13 الصفوف = 1 العرض = 8) (الوقت الفعلي = 97907.608..97907.608 الصفوف = 1 حلقات = 3)
-------------- المخازن المؤقتة: ضرب المشتركة = 2565 قراءة = 489099
---------------> Parq Seq Scan على rbstore_table (التكلفة = 0.00..875264.00 الصفوف = 3580047 العرض = 4) (الوقت الفعلي = 40820.004..97405.250 الصفوف = 2920000 حلقات = 3)
--------------------- Filter: ((trd> = '2011-01-01 00:00:00' :: الطابع الزمني بدون منطقة زمنية) AND (trd <= '2011-12-31 00:00:00' :: الطابع الزمني بدون منطقة زمنية) AND (op = 1))
-------------------- الصفوف إزالتها بواسطة عامل التصفية: 14616000
-------------------- المخازن المؤقتة: الضرب المشترك = 2565 قراءة = 489099
وقت التخطيط: 7.899 مللي ثانية
مدة التنفيذ: 98523.278 مللي ثانية
98523.278 مللي ثانية مقابل 3123.960 مللي ثانية. ربما يساعدنا فهرس جزئي ، لكن من الأفضل عدم المخاطرة به وإنشاء بنية مناسبة مبنية على أساس الصفوف حيث سيتم تخزين القيم الجاهزة.
المجاميع اليدوية
يمكن أن تكون البنية المناسبة للمجاميع اليدوية عبارة عن جدول منتظم مبني على أساس صف يحتوي على قيم تم حسابها مسبقًا. على سبيل المثال ، قد يحتوي على سجل مرتبط بعام 2011 بنوع العملية يساوي 1 ، بينما في الحقول m1 و m2 و m3 و m4 و m5 سيتم تخزين القيمة المجمعة على وجه التحديد لهذه الأقسام التحليلية. وبالتالي ، بعد الحصول على مجموعة كافية من المجاميع والمؤشرات ، فإن الاستعلامات التحليلية تكتسب أداءً غير مسبوق. ومن المثير للاهتمام أن خدمات تحليل Microsoft SQL Server لديها معالج خاص يسمح لك بتكوين عدد وعمق القيم المحسوبة مسبقًا.
هذا الحل لديه المزايا التالية:
- تحليلات الوقت الحقيقي.
يرجى عدم الخلط بين مصطلح "تحليلات في الوقت الحقيقي". نحن هنا نتحدث عن حقيقة أن الزيادة في الوحدة تحدث خلال فترة زمنية مقبولة في الغالبية العظمى من الحالات.
في الواقع ، هذا الجمع مثير للجدل ، ولكن دعونا لا نتحدث عن ذلك. تبقى الحقيقة. بنية الحل هي أن الوحدات تظل "حديثة" دائمًا تقريبًا. - الاستقلال الكامل عن حجم البيانات.
هذا هو زائد خطيرة جدا. بغض النظر عن كمية البيانات التي يتم معالجتها ، سيتم عاجلاً أم آجلاً معالجتها ، والبيانات الإجمالية المستلمة. - التعقيد النسبي.
للحصول على تحليلات في الوقت الحقيقي واستقلالية حجم البيانات ، يجب أن يستخدم الحل التقنيات المتقدمة مثل تعدد مؤشرات وإدارة القفل اليدوي على مستوى نظم إدارة قواعد البيانات. - اختبار الصعوبة.
نحن هنا نتحدث عن كل من وحدة الاختبار والاختبار اليدوي. أعتقد أنه لا ينبغي للقارئ أن يشرح أن تحديد أخطاء تعدد العمليات ليست مهمة سهلة. - زيادة متطلبات مساحة القرص.
الاستخدام الفعلي للعمود
هنا يجب علينا أن نتعمق مرة أخرى في النظرية ونحلل مسألة ما هي البيانات التحليلية بمزيد من التفصيل.
خذ متوسط رأس المؤسسة. كقاعدة عامة ، هو / هي قلق بشأن سؤالين عالميين: "كيف تسير الأمور في الوقت الحالي؟" و "ما الذي تغير مؤخرًا؟".
للإجابة على السؤال "كيف تسير الأمور حاليًا" ، لا نحتاج مطلقًا إلى بيانات تاريخية. أي بغض النظر عن كيفية سير الأمور قبل شهر.
من أجل مواكبة النبض ، غالبًا ما يتم طرح السؤال. هذا النوع من تحليل البيانات يسمى التشغيلية.
للإجابة على السؤال "ما الذي تغير مؤخرًا" ، نحتاج إلى بيانات تاريخية محددة. علاوة على ذلك ، كقاعدة عامة ، يتم إجراء التحليل على فترات زمنية واحدة. على سبيل المثال ، يتم مقارنة شهر بشهر ، سنة إلى أخرى ، إلخ. بالطبع ، لا ينبغي للنظام تقييد المستخدم من القدرة على مقارنة الفترات التعسفية ، ولكن يجب الاعتراف بهذه الحالة على أنها نادرة ، لأن مقارنة سنة مغلقة مع نصف غير مغلقة ليس له معنى كبير. السمة المميزة للتحليل المقارن هي أنه غير مطلوب بالقدر التشغيلي. سوف نسمي هذا النوع من التحليل التاريخي.
من الواضح أن التحليل التشغيلي يجب أن يحدث بسرعة. وفقا لذلك ، فإنه يضع مطالب عالية على الأداء. بينما للتحليل التاريخي ، لا يمكن طرح هذه المتطلبات. على الرغم من أن أداء التحليل التاريخي يجب أن يظل على مستوى عالٍ للغاية. على الأقل حتى يظل نظام التحليل نفسه منافسًا.
لذلك ، وفقًا لنوعين من التحليل ، يمكننا التمييز بين نوعين من البيانات التحليلية: البيانات التشغيلية والتاريخية. من جانب المستخدم ، يجب ألا يكون ملحوظًا مع البيانات المعينة التي يعمل بها في الوقت الحالي.
من هذه الاعتبارات التي ظهرت في خوادم قاعدة البيانات إمكانية تقسيم الجداول إلى أقسام منفصلة.
فيما يتعلق بمتجر العمود ، من الممكن خلط المقاطع بتنسيقات تستند إلى الصف وتنسيقات العمود. من المعروف أن بيانات التحليل التشغيلي تخضع لتغيرات متكررة ، مما يحول دون تخزينها في تنسيق مخزن الكتب. ونظراً لحقيقة أن البيانات التشغيلية لا تحدث كثيرًا ، فيمكن تخزينها بتنسيق يستند إلى الصف.
البيانات التاريخية لا تتغير. يوجد الكثير من هذه البيانات ، وبالتالي فإن تنسيق متجر الأعمدة يناسبهم بشكل أفضل. تذكر أن أداء الاستعلامات الغامقة على مصدر عمود أعلى من أداء مستندة إلى صف.
دعنا ننظر إلى مثال على كل ما سبق.
أدناه أقوم بإنشاء جدول المستودع الرئيسي وأرفق أقسام التحليل التشغيلي والتاريخي به.
create table warehouse ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) partition by range(trd); create foreign table historycal_data ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz'); insert into historycal_data select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, (1095 - 31)) as d; analyze historycal_data; create table operational_data as select ('2012-12-01'::date + make_interval(days => d))::date as trd , op , org , wh , it , 100::numeric(32, 2) as m1 , 100::numeric(32, 2) as m2 , 100::numeric(32, 2) as m3 , 100::numeric(32, 2) as m4 , 100::numeric(32, 2) as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 30) as d; create index trd_op_ix on operational_data (trd, op); analyze operational_data; alter table warehouse attach partition operational_data for values from ('2012-12-01') to ('2112-01-01'); alter table warehouse attach partition historycal_data for values from ('2010-01-01') to ('2012-12-01');
كل شيء جاهز. دعنا نحاول طلب بضعة تقارير. لنبدأ بطلب البيانات ليوم واحد من الشهر الحالي.
explain (analyze, costs, buffers) select sum(m1) from warehouse where trd = '2012-12-01' and op = 1;
إجمالي (التكلفة = 15203.37..15203.38 صفوف = 1 عرض = 32) (الوقت الفعلي = 17.320..17.320 صفوف = 1 حلقات = 1)
- الخفافيش: ضرب المشتركة = 3 قراءة = 515
---> إلحاق (التكلفة = 532.59..15140.89 الصفوف = 24991 العرض = 5) (الوقت الفعلي = 1.924..13.838 الصفوف = 24000 حلقة = 1)
------- مخازن: النتيجة المشتركة = 3 قراءة = 515
---------> مسح كومة نقطية على العمليات_البيانات (التكلفة = 532.59..15140.89 الصفوف = 24991 العرض = 5) (الوقت الفعلي = 1.924..11.992 الصفوف = 24000 حلقة = 1)
--------------- Recheck Cond: ((trd = '2012-12-01' :: date) AND (op = 1))
--------------- كتل الكومة: بالضبط = 449
--------------- مخازن: ضرب المشتركة = 3 قراءة = 515
----------------> مسح فهرس الصورة النقطية على trd_op_ix (التكلفة = 0.00..526.34 الصفوف = 24991 العرض = 0) (الوقت الفعلي = 1.877..1.877 الصفوف = 24000 حلقة = 1 )
--------------------- Index Cond: ((trd = '2012-12-01' :: date) AND (op = 1))
--------------------- المخازن المؤقتة: ضرب المشتركة = 2 قراءة = 67
وقت التخطيط: 0.388 مللي ثانية
مدة التنفيذ: 100.941 مللي ثانية
سنقوم الآن بطلب بيانات لعام 2012 بأكمله ، حيث بلغ عدد المعاملات 8،784،000.
explain (analyze, costs, buffers) select sum(m1) from warehouse where trd between '2012-01-01' and '2012-12-31' and op = 1;
إجمالي (التكلفة = 960685.82..960685.83 صفوف = 1 عرض = 32) (الوقت الفعلي = 4124.681..4124.681 صفوف = 1 حلقات = 1)
- Buffers: ضرب المشتركة = 45591 قراءة = 11282
---> إلحاق (التكلفة = 0.00..938846.60 الصفوف = 8735687 العرض = 5) (الوقت الفعلي = 66.581..3036.394 الصفوف = 8784000 حلقة = 1)
--------- المخازن المؤقتة: النتيجة المشتركة = 45591 قراءة = 11282
----------> فحص خارجي على historycal_data (التكلفة = 0.00..898899.60 الصفوف = 7994117 العرض = 5) (الوقت الفعلي = 66.579..2193.801 الصفوف = 8040000 حلقات = 1)
--------------- Filter: ((trd> = '2012-01-01' :: date) AND (trd <= '2012-12-31' :: date) و (المرجع = 1))
--------------- الصفوف إزالتها بواسطة عامل التصفية: 8040000
--------------- CStore File: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16448
--------------- CStore File Size: 117401470
--------------- مخازن: ضربة مشتركة = 42966
----------> Seq Scan on operation_data (التكلفة = 0.00..39947.00 الصفوف = 741570 العرض = 5) (الوقت الفعلي = 0.019..284.824 الصفوف = 744000 حلقة = 1)
--------------- Filter: ((trd> = '2012-01-01' :: date) AND (trd <= '2012-12-31' :: date) و (المرجع = 1))
--------------- الصفوف إزالتها بواسطة عامل التصفية: 744000
--------------- المخازن المؤقتة: ضرب المشتركة = 2625 قراءة = 11282
وقت التخطيط: 0.256 مللي ثانية
مدة التنفيذ: 4125.239 مللي ثانية
في النهاية ، دعونا نرى ما يحدث إذا أراد المستخدم ، على سبيل المثال ، دون نية خبيثة ، طلب تقرير عن جميع المعاملات في النظام ، والتي يوجد منها 52608 000.
explain (analyze, costs, buffers) select sum(m1) from warehouse
إجمالي (التكلفة = 672940.20..672940.21 صفوف = 1 عرض = 32) (الوقت الفعلي = 15907.886..15907.886 صفوف = 1 حلقات = 1)
- البخاخون: ضرب المشتركة = 17075 قراءة = 11154
---> إلحاق (التكلفة = 0.00..541420.20 الصفوف = 52608000 العرض = 5) (الوقت الفعلي = 0.192..9115.144 الصفوف = 52608000 حلقات = 1)
--------- المخازن المؤقتة: إصابة مشتركة = 17075 قراءة = 11154
----------> فحص خارجي على historycal_data (التكلفة = 0.00..512633.20 الصفوف = 51120000 عرض = 5) (الوقت الفعلي = 0.191..5376.449 الصفوف = 51120000 حلقة = 1)
--------------- CStore File: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16448
--------------- CStore File Size: 117401470
--------------- Buffers: hit hit = 14322
----------> Seq Scan on operation_data (التكلفة = 0.00..28787.00 الصفوف = 1488000 عرض = 5) (الوقت الفعلي = 0.032..246.978 الصفوف = 1488000 حلقة = 1)
--------------- المخازن المؤقتة: ضرب المشتركة = 2753 قراءة = 11154
وقت التخطيط: 0.157 مللي ثانية
مدة التنفيذ: 15908.096 مللي ثانية
يرجى ملاحظة أنني ما زلت أكتب مقالتي ، كما لو أن شيئًا لم يحدث. لم أضطر حتى إلى إعادة تشغيل جهاز الكمبيوتر المحمول غير القوي باستخدام محرك HDD وذاكرة وصول عشوائي سعتها 4 جيجابايت. على الرغم من أن مسألة استهلاك الموارد تتطلب دراسة أكثر حذرا.
خطأ التسامح
في جزء منه ، تم اختبار التسامح مع الخطأ في وقت كتابة هذا التقرير. جهاز الكمبيوتر المحمول الخاص بي على قيد الحياة ، وبشكل عام ، لم ألاحظ أي تباطؤ في عمله ، إلى جانب تلك المعتادة.
اسمح للقارئ أن يغفر لي حقيقة أنه لم يكن لدي وقت لحل مشكلة التسامح مع الخطأ بالتفصيل ، لكن يمكنني القول أن الامتداد المعني له تسامح مع الخطأ - النسخ الاحتياطي ممكن.
سهولة التنفيذ
كما اتضح ، عند إنشاء جدول يخزن البيانات بتنسيق مخزن العمود ، لا توجد خيارات أخرى غير خوارزمية الضغط. ضغط نفسه ضروري للغاية.
التنسيق نفسه لديه بنية معينة. من خلال تعيين المعلمات المناسبة ، يمكنك تحقيق تسارع معين في الاستعلامات التحليلية ، أو ضبط درجة ضغط المعلومات.
كما هو موضح أعلاه ، فإن إنشاء جدول متجر الكتب ليس مشكلة على الإطلاق. يمكن أن يعمل الامتداد مع 40 نوعًا من أنواع بيانات PostgreSQL. تحدث ندوات عبر الإنترنت حول جميع الأنواع التي تدعمها بوستجرس.
ما هي المهارات الجديدة التي يجب على المطور أن يعملها مع هياكل جديدة
لا يحتاج مطور SQL إلى أي مهارات خاصة لكتابة استعلامات إلى جداول متجر الكتب. مثل هذا الجدول مرئي في جميع الاستعلامات ، مثل جدول منتظم يستند إلى صف. على الرغم من أن هذا لا يستبعد الحاجة إلى تحسين الاستعلام.
استنتاج
في هذا المقال ، أوضحت كيف يمكن أن يكون الجدول بتنسيق تخزين أعمدة مفيدة. هذا يوفر مساحة على القرص واستعلامات تحليلية عالية الأداء. سهولة العمل مع الجدول تقلل تلقائيًا من تكلفة إنشاء مستودع بيانات تحليلي كامل ، لأن لا يتطلب استخدامه تطوير خوارزميات معقدة وصعبة التصحيح. الاختبار مبسط.
على الرغم من أن التجارب المذكورة أعلاه تلهم التفاؤل ، لم يتم حل العديد من القضايا. على سبيل المثال ، سيتم إنشاء خطة الاستعلام عند انضمام جدول مخزن الأعمدة إلى الجداول الأخرى. آمل أن أواصل هذا العمل في الجزء التالي. يعتمد عدد الأجزاء على كيفية سلوك cstore_fdw على بيانات حقيقية أكثر أو أقل.
روابط لمواد إضافية
استعراض قصير cstore_fdwcstore_fdw على جيثبخارطة الطريق cstore_fdw