لقد ولت الأيام التي لم يكن عليك فيها القلق بشأن تحسين أداء قاعدة البيانات. الوقت لا يقف ساكنا. يريد كل رائد أعمال جديد في مجال التكنولوجيا إنشاء فيسبوك التالي، بينما يحاول جمع كل البيانات التي يمكنه الحصول عليها. تحتاج الشركات إلى هذه البيانات لتدريب النماذج بشكل أفضل والتي تساعدها على كسب المال. في مثل هذه الظروف، يحتاج المبرمجون إلى إنشاء واجهات برمجة التطبيقات التي تتيح لهم العمل بسرعة وبشكل موثوق مع كميات هائلة من المعلومات.
إذا كنت تقوم بتصميم الواجهات الخلفية للتطبيق أو قاعدة البيانات لأي مدة من الوقت، فمن المحتمل أنك كتبت تعليمات برمجية لتشغيل الاستعلامات المرقّمة. على سبيل المثال، مثل هذا:
SELECT * FROM table_name LIMIT 10 OFFSET 40
على ما هو عليه؟
ولكن إذا كانت هذه هي الطريقة التي قمت بها بترقيم الصفحات، يؤسفني أن أقول إنك لم تفعل ذلك بالطريقة الأكثر فعالية.
هل تريد الاعتراض علي؟ . , и إنهم يستخدمون بالفعل التقنيات التي أريد التحدث عنها اليوم.
قم بتسمية مطور خلفي واحد على الأقل لم يستخدمه مطلقًا OFFSET и LIMIT لتنفيذ الاستعلامات المرقّمة. في MVP (الحد الأدنى من المنتج القابل للتطبيق) وفي المشاريع التي يتم فيها استخدام كميات صغيرة من البيانات، يكون هذا النهج قابلاً للتطبيق تمامًا. إنه "يعمل فقط"، إذا جاز التعبير.
ولكن إذا كنت بحاجة إلى إنشاء أنظمة موثوقة وفعالة من الصفر، فيجب عليك الاهتمام مسبقًا بكفاءة الاستعلام عن قواعد البيانات المستخدمة في مثل هذه الأنظمة.
سنتحدث اليوم عن المشكلات المتعلقة بالتطبيقات شائعة الاستخدام (السيئة جدًا) لمحركات الاستعلام المقسمة إلى صفحات، وكيفية تحقيق أداء عالٍ عند تنفيذ مثل هذه الاستعلامات.
ما هو الخطأ في الإزاحة والحدود؟
كما قال بالفعل OFFSET и LIMIT إنها تؤدي أداءً جيدًا في المشاريع التي لا تحتاج إلى العمل بكميات كبيرة من البيانات.
تنشأ المشكلة عندما تنمو قاعدة البيانات إلى حجم لم يعد مناسبًا لذاكرة الخادم. ومع ذلك، عند العمل مع قاعدة البيانات هذه، تحتاج إلى استخدام الاستعلامات المرقّمة.
لكي تظهر هذه المشكلة، يجب أن يكون هناك موقف يلجأ فيه نظام إدارة قواعد البيانات (DBMS) إلى عملية فحص الجدول الكامل غير الفعالة لكل استعلام مرقّم (بينما قد تحدث عمليات الإدراج والحذف، ولا نحتاج إلى بيانات قديمة!).
ما هو "مسح الجدول الكامل" (أو "مسح الجدول المتسلسل"، المسح المتسلسل)؟ هذه عملية يقوم خلالها نظام إدارة قواعد البيانات (DBMS) بقراءة كل صف من الجدول بالتتابع، أي البيانات الموجودة فيه، والتحقق من امتثالها لشرط معين. من المعروف أن هذا النوع من فحص الجدول هو الأبطأ. الحقيقة هي أنه عند تنفيذه، يتم تنفيذ العديد من عمليات الإدخال والإخراج التي تتضمن النظام الفرعي للقرص الخاص بالخادم. ويزداد الوضع سوءًا بسبب الكمون المرتبط بالعمل مع البيانات المخزنة على الأقراص، وحقيقة أن نقل البيانات من القرص إلى الذاكرة هو عملية كثيفة الاستخدام للموارد.
على سبيل المثال، لديك سجلات تضم 100000000 مستخدم وقمت بتشغيل استعلام باستخدام البنية OFFSET 50000000. وهذا يعني أنه سيتعين على نظام إدارة قواعد البيانات (DBMS) تحميل كل هذه السجلات (ونحن لا نحتاج إليها حتى!)، ووضعها في الذاكرة، وبعد ذلك، على سبيل المثال، أخذ 20 نتيجة تم الإبلاغ عنها في LIMIT.
لنفترض أن الأمر قد يبدو كالتالي: "حدد الصفوف من 50000 إلى 50020 من 100000". أي أن النظام سيحتاج أولاً إلى تحميل 50000 صف لإكمال الاستعلام. هل ترى مقدار العمل غير الضروري الذي سيتعين عليها القيام به؟
إذا كنت لا تصدقني، قم بإلقاء نظرة على المثال الذي قمت بإنشائه باستخدام الميزات .
مثال على db-fiddle.com
هناك، على اليسار، في الميدان Schema SQL، يوجد كود يقوم بإدراج 100000 صف في قاعدة البيانات، وعلى اليمين في الحقل Query SQL، يتم عرض استعلامين. الأول، البطيء، يبدو كما يلي:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
والثاني وهو الحل الفعال لنفس المشكلة وهو كالتالي:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
من أجل تلبية هذه الطلبات، فقط اضغط على الزر Run في الجزء العلوي من الصفحة. بعد القيام بذلك، نقوم بمقارنة المعلومات حول وقت تنفيذ الاستعلام. اتضح أن تنفيذ استعلام غير فعال يستغرق ما لا يقل عن 30 مرة أطول من تنفيذ الاستعلام الثاني (تختلف هذه المرة من تشغيل إلى آخر؛ على سبيل المثال، قد يبلغ النظام أن الاستعلام الأول استغرق 37 مللي ثانية لإكماله، ولكن تنفيذ الاستعلام غير الفعال الثانية - 1 مللي ثانية).
وإذا كان هناك المزيد من البيانات، فسيبدو كل شيء أسوأ (للاقتناع بهذا، ألق نظرة على ملفي مع 10 مليون صف).
ما ناقشناه للتو يجب أن يمنحك فكرة عن كيفية معالجة استعلامات قاعدة البيانات فعليًا.
يرجى ملاحظة أنه كلما ارتفعت القيمة OFFSET - كلما استغرق إكمال الطلب وقتًا أطول.
ما الذي يجب أن أستخدمه بدلاً من الجمع بين OFFSET وLIMIT؟
بدلا من الجمع OFFSET и LIMIT يجدر استخدام هيكل مبني وفقًا للمخطط التالي:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
هذا هو تنفيذ الاستعلام باستخدام ترقيم الصفحات المستند إلى المؤشر.
بدلا من تخزين تلك الحالية محليا OFFSET и LIMIT وإرسالها مع كل طلب، تحتاج إلى تخزين آخر مفتاح أساسي تم استلامه (عادةً ما يكون هذا ID) و LIMITونتيجة لذلك، سيتم الحصول على استفسارات مشابهة لما ورد أعلاه.
لماذا؟ النقطة المهمة هي أنه من خلال تحديد معرف الصف الأخير الذي تم قراءته بشكل صريح، فإنك تخبر نظام إدارة قواعد البيانات (DBMS) الخاص بك بالمكان الذي يجب أن يبدأ فيه البحث عن البيانات الضرورية. علاوة على ذلك، سيتم إجراء البحث، وذلك بفضل استخدام المفتاح، بكفاءة، ولن يتعين على النظام تشتيت انتباهه بخطوط خارج النطاق المحدد.
دعونا نلقي نظرة على مقارنة الأداء التالية للاستعلامات المختلفة. إليك استعلام غير فعال.
طلب بطيء
وهنا نسخة محسنة من هذا الطلب.
طلب سريع
يقوم كلا الاستعلامين بإرجاع نفس مقدار البيانات تمامًا. لكن الأول يستغرق 12,80 ثانية ليكتمل، والثاني يستغرق 0,01 ثانية. هل تشعر بالفرق؟
المشاكل المحتملة
لكي تعمل طريقة الاستعلام المقترحة بفعالية، يجب أن يحتوي الجدول على عمود (أو أعمدة) يحتوي على فهارس فريدة ومتسلسلة، مثل معرف عدد صحيح. في بعض الحالات المحددة، قد يحدد هذا مدى نجاح استخدام مثل هذه الاستعلامات لزيادة سرعة العمل مع قاعدة البيانات.
بطبيعة الحال، عند إنشاء الاستعلامات، يجب أن تأخذ في الاعتبار البنية المحددة للجداول واختيار الآليات التي ستعمل بشكل أفضل على الجداول الموجودة. على سبيل المثال، إذا كنت بحاجة إلى العمل في استعلامات تحتوي على كميات كبيرة من البيانات ذات الصلة، فقد تجد ذلك مثيرًا للاهتمام شرط.
إذا واجهنا مشكلة فقدان مفتاح أساسي، على سبيل المثال، إذا كان لدينا جدول به علاقة متعدد بمتعدد، فإن النهج التقليدي المتمثل في استخدام OFFSET и LIMIT، مضمونة لتناسبنا. لكن استخدامه قد يؤدي إلى استعلامات بطيئة محتملة. في مثل هذه الحالات، أوصي باستخدام مفتاح أساسي متزايد تلقائيًا، حتى لو كان مطلوبًا فقط للتعامل مع الاستعلامات المرقّمة.
إذا كنت مهتمًا بهذا الموضوع - , и - العديد من المواد المفيدة.
نتائج
الاستنتاج الرئيسي الذي يمكننا استخلاصه هو أنه بغض النظر عن حجم قواعد البيانات التي نتحدث عنها، فمن الضروري دائمًا تحليل سرعة تنفيذ الاستعلام. في الوقت الحاضر، تعد قابلية التوسع للحلول أمرًا في غاية الأهمية، وإذا تم تصميم كل شيء بشكل صحيح منذ بداية العمل على نظام معين، فإن هذا في المستقبل يمكن أن ينقذ المطور من العديد من المشكلات.
كيف يمكنك تحليل وتحسين استعلامات قاعدة البيانات؟
المصدر: www.habr.com