يعد نظام قاعدة البيانات جوهر نظام المعلومات الإدارية، وتعد معالجة المعاملات عبر الإنترنت القائمة على قاعدة البيانات (OLTP) والمعالجة التحليلية عبر الإنترنت (OLAP) أحد أهم تطبيقات الكمبيوتر في البنوك والمؤسسات والحكومات والإدارات الأخرى. استنادا إلى أمثلة التطبيق ودمجها مع نظرية قاعدة البيانات، تقدم هذه المقالة تطبيق تكنولوجيا تحسين الاستعلام في الأنظمة الحقيقية. انطلاقًا من أمثلة التطبيقات لمعظم الأنظمة، تمثل عمليات الاستعلام النسبة الأكبر من عمليات قاعدة البيانات المختلفة، كما أن عبارة SELECT التي تعتمد عليها عملية الاستعلام هي أغلى عبارة بين عبارات SQL. على سبيل المثال، إذا تراكمت كمية البيانات إلى مستوى معين، مثل تراكم معلومات جدول قاعدة بيانات حسابات البنك إلى ملايين أو حتى عشرات الملايين من السجلات، فغالبًا ما يستغرق فحص الجدول الكامل عشرات الدقائق أو حتى الساعات. إذا كنت تعتمد استراتيجية استعلام أفضل من فحص الجدول الكامل، فيمكنك غالبًا تقليل وقت الاستعلام إلى بضع دقائق، مما يوضح أهمية تقنية تحسين الاستعلام.
أثناء تنفيذ مشروع التطبيق، وجد المؤلف أنه عند تطوير تطبيقات قواعد البيانات باستخدام بعض أدوات تطوير قواعد البيانات الأمامية (مثل PowerBuilder وDelphi وما إلى ذلك)، يركز العديد من المبرمجين فقط على روعة واجهة المستخدم ولا يدفعون أي أموال. الاهتمام بكفاءة عبارات الاستعلام، مما يؤدي إلى جميع المشاكل. نظام التطبيق المطور غير فعال ويسبب إهدارًا خطيرًا للموارد. ولذلك، فإن كيفية تصميم عبارات استعلام فعالة ومعقولة أمر مهم للغاية. استنادا إلى أمثلة التطبيق ودمجها مع نظرية قاعدة البيانات، تقدم هذه المقالة تطبيق تكنولوجيا تحسين الاستعلام في الأنظمة الحقيقية.
تحليل المشكلة
يعتقد العديد من المبرمجين أن تحسين الاستعلام هو مهمة نظام إدارة قواعد البيانات (DBMS) ولا علاقة له بعبارات SQL التي يكتبها المبرمجون. وهذا خطأ. يمكن لخطة الاستعلام الجيدة في كثير من الأحيان تحسين أداء البرنامج عشرات المرات. خطة الاستعلام عبارة عن مجموعة من عبارات SQL المقدمة من قبل المستخدم، وخطة الاستعلام عبارة عن مجموعة من البيانات التي تم إنشاؤها بعد التحسين. عملية خطة استعلام معالجة نظام إدارة قواعد البيانات هي كما يلي: بعد إكمال الفحص المعجمي والنحوي لبيان الاستعلام، يتم إرسال البيان إلى مُحسِّن استعلام نظام إدارة قواعد البيانات. بعد أن يكمل المُحسِّن التحسين الجبري وتحسين مسار الوصول، تقوم الوحدة المترجمة مسبقًا بمعالجة. بيان وإنشاء خطة استعلام، ثم تقديمها إلى النظام لمعالجتها وتنفيذها في الوقت المناسب، وأخيرًا إرجاع نتائج التنفيذ إلى المستخدم. في الإصدارات العالية من منتجات قواعد البيانات الفعلية (مثل Oracle وSybase وما إلى ذلك)، يتم استخدام أساليب التحسين القائمة على التكلفة، ويمكن أن يقوم هذا التحسين بتقدير تكلفة خطط الاستعلام المختلفة بناءً على المعلومات التي تم الحصول عليها من جدول قاموس النظام، ثم التحديد تخطيط أفضل. على الرغم من أن منتجات قواعد البيانات الحالية تتحسن بشكل متزايد في تحسين الاستعلام، إلا أن عبارات SQL التي يرسلها المستخدمون هي الأساس لتحسين النظام، ومن الصعب تخيل أن خطة الاستعلام الضعيفة في الأصل ستصبح فعالة بعد تحسين النظام البيانات التي يكتبها المستخدمون أمر بالغ الأهمية. لن نناقش تحسين الاستعلام الذي يقوم به النظام في الوقت الحالي. ويركز ما يلي على الحلول لتحسين خطط استعلام المستخدم.
حل المشاكل
ما يلي يأخذ نظام قاعدة البيانات العلائقية Informix كمثال لتقديم طرق لتحسين خطط استعلام المستخدم.
1. الاستخدام المعقول للفهارس
يعد الفهرس بنية بيانات مهمة في قاعدة البيانات، والغرض الأساسي منه هو تحسين كفاءة الاستعلام. تستخدم معظم منتجات قواعد البيانات الآن بنية فهرس ISAM التي اقترحتها شركة IBM لأول مرة. يجب أن يكون استخدام الفهارس مناسبًا، وتكون مبادئ استخدامه كما يلي:
●قم بإنشاء فهارس على الأعمدة المتصلة بشكل متكرر ولكن لم يتم تعيينها كمفاتيح خارجية، بينما يقوم المحسن تلقائيًا بإنشاء فهارس للحقول المتصلة بشكل غير متكرر.
● قم بإنشاء فهارس على الأعمدة التي يتم فرزها أو تجميعها بشكل متكرر (أي التجميع حسب العمليات أو الترتيب حسبها).
●إنشاء عمليات بحث على أعمدة تحتوي على العديد من القيم المختلفة التي تُستخدم غالبًا في التعبيرات الشرطية. لا تقم بإنشاء فهارس على أعمدة تحتوي على عدد قليل من القيم المختلفة. على سبيل المثال، هناك قيمتان مختلفتان فقط في عمود "الجنس" بجدول الموظفين، "ذكر" و"أنثى"، لذلك ليست هناك حاجة لإنشاء فهرس. إذا قمت بإنشاء فهرس، فلن يؤدي ذلك إلى تحسين كفاءة الاستعلام فحسب، بل سيقلل بشكل كبير من سرعة التحديث.
●إذا كان هناك عدة أعمدة مطلوب فرزها، يمكنك إنشاء فهرس مركب على هذه الأعمدة.
●استخدام أدوات النظام. على سبيل المثال، تحتوي قاعدة بيانات Informix على أداة tbcheck يمكنها التحقق من الفهارس المشبوهة. في بعض خوادم قواعد البيانات، قد يكون الفهرس غير صالح أو قد تنخفض كفاءة القراءة بسبب العمليات المتكررة. إذا تباطأ الاستعلام باستخدام الفهرس دون سبب واضح، فيمكنك محاولة استخدام أداة tbcheck للتحقق من سلامة الفهرس. وإصلاحه إذا لزم الأمر. بالإضافة إلى ذلك، عندما يقوم جدول قاعدة البيانات بتحديث كمية كبيرة من البيانات، فإن حذف الفهرس وإعادة بنائه يمكن أن يؤدي إلى تحسين سرعة الاستعلام.
2. تجنب الفرز أو تبسيطه
يجب تبسيط أو تجنب الفرز المتكرر للجداول الكبيرة. يتجنب المحسن خطوة الفرز عندما يتمكن من استخدام فهرس لإنتاج الإخراج تلقائيًا بالترتيب الصحيح. فيما يلي بعض العوامل المؤثرة:
●لا يتضمن الفهرس عمودًا واحدًا أو عدة أعمدة ليتم فرزها؛
●يختلف ترتيب الأعمدة في المجموعة حسب الترتيب حسب الجملة عن ترتيب الفهرس؛
●الأعمدة التي تم فرزها تأتي من جداول مختلفة.
لتجنب الفرز غير الضروري، من الضروري إضافة الفهارس بشكل صحيح ودمج جداول قاعدة البيانات بشكل معقول (على الرغم من أن ذلك قد يؤثر أحيانًا على تسوية الجدول، إلا أن تحسين الكفاءة يستحق ذلك). إذا كان الفرز أمرًا لا مفر منه، فيجب عليك محاولة تبسيطه، مثل تضييق نطاق الأعمدة للفرز، وما إلى ذلك.
3. القضاء على الوصول المتسلسل إلى بيانات صف الجدول الكبيرة
في الاستعلامات المتداخلة، قد يكون للوصول المتسلسل إلى الجداول تأثير فادح على كفاءة الاستعلام. على سبيل المثال، باستخدام إستراتيجية الوصول التسلسلي، إذا كان استعلام يحتوي على ثلاثة مستويات متداخلة يستعلم عن 1000 صف في كل مستوى، فسيقوم هذا الاستعلام بالاستعلام عن مليار صف من البيانات. الطريقة الرئيسية لتجنب ذلك هي فهرسة الأعمدة المرتبطة. على سبيل المثال جدولين: جدول الطالب (رقم الطالب، الاسم، العمر...) وجدول اختيار المقرر (رقم الطالب، رقم المقرر، الدرجات). في حالة ربط جدولين، يجب إنشاء فهرس في حقل الاتصال "رقم الطالب".
يمكنك أيضًا استخدام النقابات لتجنب الوصول التسلسلي. على الرغم من وجود فهارس على كافة أعمدة التحقق، إلا أن بعض أشكال العبارات تجبر المحسن على استخدام الوصول المتسلسل. سيفرض الاستعلام التالي عمليات تسلسلية في جدول الطلبات: SELECT * FROM Orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
على الرغم من وجود فهارس في customer_num وorder_num، إلا أن المُحسِّن لا يزال يستخدم مسار الوصول المتسلسل لفحص الجدول بأكمله في العبارة أعلاه. نظرًا لأن هذه العبارة تسترد مجموعة منفصلة من الصفوف، فيجب تغييرها إلى العبارة التالية:
حدد * من الطلبات حيث customer_num=104 وorder_num>1001
الاتحاد
اختر * من الطلبات حيث order_num=1008
يسمح هذا باستخدام مسار الفهرس لمعالجة الاستعلامات.
4. تجنب الاستعلامات الفرعية المرتبطة
إذا ظهرت تسمية عمود في كل من الاستعلام الرئيسي والاستعلام في عبارة "حيث"، فمن المحتمل أنه يجب إعادة الاستعلام عن الاستعلام الفرعي عندما تتغير قيمة العمود في الاستعلام الرئيسي. كلما كانت مستويات الاستعلام أكثر تداخلاً، انخفضت الكفاءة، لذا يجب تجنب الاستعلامات الفرعية قدر الإمكان. إذا كان الاستعلام الفرعي أمرًا لا مفر منه، قم بتصفية أكبر عدد ممكن من الصفوف في الاستعلام الفرعي.
5. تجنب التعبيرات العادية الصعبة
تدعم الكلمات الرئيسية MATCHES وLIKE مطابقة أحرف البدل، والتي تسمى تقنيًا بالتعبيرات العادية. لكن هذا النوع من المطابقة يستغرق وقتًا طويلاً بشكل خاص. على سبيل المثال: حدد * من العميل حيث الرمز البريدي مثل "98_ _ _"
حتى إذا تم إنشاء فهرس في حقل الرمز البريدي، فلا يزال المسح التسلسلي مستخدمًا في هذه الحالة. إذا قمت بتغيير العبارة إلى SELECT * FROM customer WHERE zipcode > "98000"، فسيتم استخدام الفهرس للاستعلام عند تنفيذ الاستعلام، مما سيؤدي بوضوح إلى تحسين السرعة بشكل كبير.
أيضًا، تجنب السلاسل الفرعية التي لا تبدأ. على سبيل المثال، العبارة: SELECT * FROM customer WHERE zipcode[2, 3]>"80" تستخدم سلسلة فرعية غير بداية في عبارة Where، لذلك لا تستخدم هذه العبارة الفهرس.
6. استخدم الجداول المؤقتة لتسريع الاستعلامات
قد يؤدي فرز مجموعة فرعية من الجدول وإنشاء جدول مؤقت إلى تسريع الاستعلامات في بعض الأحيان. فهو يساعد على تجنب عمليات الفرز المتعددة ويبسط عمل المحسن. على سبيل المثال: SELECT cust.name، rcVBles.balance،...الأعمدة الأخرى
حدد cust.name,rcVBles.balance,...الأعمدة الأخرى
من العملاء، rcvbles
أين cust.customer_id = rcvlbes.customer_id
و rcvblls.balance>0
وcust.postcode>"98000"
اطلب حسب اسم العميل
إذا كان سيتم تنفيذ هذا الاستعلام عدة مرات بدلاً من مرة واحدة فقط، فيمكنك العثور على جميع العملاء غير المدفوعين في ملف مؤقت وفرزهم حسب اسم العميل: SELECT cust.name، rcvbles.balance،...أعمدة أخرى
حدد cust.name,rcvbles.balance,...الأعمدة الأخرى
من العملاء، rcvbles
أين cust.customer_id = rcvlbes.customer_id
و rcvblls.balance>0
اطلب حسب اسم العميل
في درجة الحرارة cust_with_balance
ثم قم بالاستعلام في الجدول المؤقت بالطريقة التالية: SELECT * FROM cust_with_balance
أين الرمز البريدي>"98000"
يوجد عدد أقل من الصفوف في الجدول المؤقت مقارنة بالجدول الرئيسي، والترتيب الفعلي هو الترتيب المطلوب، مما يقلل من عمليات الإدخال / الإخراج للقرص، وبالتالي يمكن تقليل عبء عمل الاستعلام بشكل كبير.
ملاحظة: بعد إنشاء الجدول المؤقت، لن يعكس تعديل الجدول الرئيسي. عندما يتم تعديل البيانات الموجودة في الجدول الرئيسي بشكل متكرر، احرص على عدم فقدان البيانات.
7. استخدم الفرز لاستبدال الوصول غير المتسلسل
يعد الوصول غير المتسلسل إلى القرص أبطأ عملية ويتم تمثيله بالحركة ذهابًا وإيابًا لذراع الوصول إلى القرص. تخفي عبارات SQL هذا الموقف، مما يسهل علينا كتابة الاستعلامات التي تتطلب الوصول إلى عدد كبير من الصفحات غير المتسلسلة عند كتابة التطبيقات. في بعض الأحيان، يمكن أن يؤدي استخدام إمكانيات الفرز في قاعدة البيانات بدلاً من الوصول غير المتسلسل إلى تحسين الاستعلامات.
-