الاستعلام هو العملية الأكثر استخدامًا في تكنولوجيا قواعد البيانات. عملية الاستعلام بسيطة نسبيًا. أولاً، يتم إصدار عبارة SQL للاستعلام من العميل. بعد استلام عبارة SQL المرسلة من قبل العميل، يقوم خادم قاعدة البيانات بتنفيذ عبارة SQL ثم يقوم بإرجاع نتائج الاستعلام إلى العميل. على الرغم من أن العملية بسيطة للغاية، إلا أن طرق الاستعلام وإعدادات قاعدة البيانات المختلفة سيكون لها تأثير كبير على أداء الاستعلام.
ولذلك، تتناول هذه المقالة تقنيات تحسين الاستعلام شائعة الاستخدام في MySQL. تتضمن المناقشات ما يلي: تحسين سرعة الاستعلام من خلال التخزين المؤقت للاستعلام في MySQL، والفرز المستند إلى الفهرس، والكشف عن الاستعلامات التي لا يمكن الوصول إليها، واستخدام خيارات الاستعلام المتنوعة لتحسين الأداء.
1. تحسين سرعة الاستعلام من خلال التخزين المؤقت للاستعلام
بشكل عام، عندما نستخدم عبارات SQL للاستعلام، سيقوم خادم قاعدة البيانات بتنفيذ عبارة SQL هذه في كل مرة يتلقى فيها SQL من العميل. ولكن عندما يتم تلقي نفس عبارة SQL خلال فترة زمنية معينة (مثل دقيقة واحدة)، فسيتم تنفيذها بنفس الطريقة. على الرغم من أن هذا يمكن أن يضمن طبيعة الوقت الفعلي للبيانات، إلا أنه في معظم الأوقات، لا تتطلب البيانات وقتًا حقيقيًا كاملاً، مما يعني أنه قد يكون هناك تأخير معين. إذا كان هذا هو الحال، فإن تنفيذ نفس SQL بالضبط في فترة زمنية قصيرة لا يستحق المكسب.
لحسن الحظ، توفر لنا MySQL وظيفة التخزين المؤقت للاستعلام (لا يمكن استخدام التخزين المؤقت للاستعلام إلا في MySQL 4.0.1 والإصدارات الأحدث). يمكننا تحسين أداء الاستعلام إلى حد ما من خلال التخزين المؤقت للاستعلام.
يمكننا ضبط المخزن المؤقت للاستعلام من خلال ملف my.ini الموجود في دليل تثبيت MySQL. الإعداد أيضًا بسيط جدًا، فقط قم بتعيين query_cache_type على 1. بعد تعيين هذه السمة، قبل تنفيذ أي عبارة SELECT، سيتحقق MySQL في المخزن المؤقت الخاص به مما إذا كان قد تم تنفيذ نفس عبارة SELECT. إذا كان الأمر كذلك، ولم تنته نتيجة التنفيذ، فسيتم إرجاع نتيجة الاستعلام مباشرة إلى العميل. ولكن عند كتابة عبارات SQL، يرجى ملاحظة أن المخزن المؤقت لاستعلام MySQL حساس لحالة الأحرف. عبارات SELECT جهازي التالية كما يلي: SELECT * from TABLE1
اختر * من الجدول 1
عبارات SQL اثنين أعلاه عبارة عن تحديدات مختلفة تمامًا للتخزين المؤقت للاستعلام. علاوة على ذلك، لا يتعامل المخزن المؤقت للاستعلام مع المسافات تلقائيًا، لذلك، عند كتابة عبارات SQL، يجب أن تحاول تقليل استخدام المسافات، خاصة المسافات في بداية ونهاية SQL (لأن ذاكرة التخزين المؤقت للاستعلام لا تعترض المسافات تلقائيًا). البداية والنهاية).
على الرغم من أن عدم إعداد مخزن مؤقت للاستعلام قد يؤدي في بعض الأحيان إلى فقدان الأداء، إلا أن هناك بعض عبارات SQL التي تحتاج إلى الاستعلام عن البيانات في الوقت الفعلي، أو لا يتم استخدامها بشكل متكرر (ربما يتم تنفيذها مرة أو مرتين في اليوم). وهذا يتطلب إيقاف تشغيل التخزين المؤقت. بالطبع، يمكنك إيقاف تشغيل ذاكرة التخزين المؤقت للاستعلام عن طريق تعيين قيمة query_cache_type، ولكن هذا سيؤدي إلى إيقاف تشغيل ذاكرة التخزين المؤقت للاستعلام بشكل دائم. يوفر MySQL 5.0 طريقة لإيقاف تشغيل ذاكرة التخزين المؤقت للاستعلام مؤقتًا: SELECT SQL_NO_CACHE field1, field2 FROM TABLE1
نظرًا لأن عبارة SQL أعلاه تستخدم SQL_NO_CACHE، بغض النظر عما إذا كان قد تم تنفيذ عبارة SQL هذه من قبل، فلن يبحث الخادم في المخزن المؤقت وسينفذها في كل مرة.
يمكننا أيضًا تعيين query_cache_type في my.ini إلى 2، بحيث سيتم استخدام ذاكرة التخزين المؤقت للاستعلام فقط بعد استخدام SQL_CACHE. حدد SQL_CALHE * من الجدول 1
2. التحسين التلقائي للاستعلامات في MySQL
الفهارس مهمة جدًا لقواعد البيانات. يمكن استخدام الفهارس لتحسين الأداء أثناء الاستعلامات. لكن في بعض الأحيان قد يؤدي استخدام الفهارس إلى تقليل الأداء. يمكننا أن ننظر إلى جدول المبيعات التالي: إنشاء جدول المبيعات
(
المعرف INT(10) غير موقع وليس NULL AUTO_INCREMENT،
الاسم VARCHAR(100) ليس فارغًا،
تعويم السعر ليس فارغًا،
SALE_COUNT INT ليس فارغًا،
تاريخ_البيع ليس فارغًا،
المفتاح الأساسي (المعرف)،
الفهرس (الاسم)،
الفهرس (SALE_DATE)
)
لنفترض أن هناك ملايين البيانات المخزنة في هذا الجدول، ونريد الاستعلام عن متوسط سعر المنتج رقم 1000 في عامي 2004 و2005. يمكننا كتابة عبارة SQL التالية: SELECT AVG(PRICE) FROM SALES
حيث المعرف = 1000 وتاريخ البيع بين "2004-01-01" و"2005-12-31"؛
إذا كانت كمية هذا المنتج كبيرة جدًا، فإنها تمثل ما يقرب من 50% أو أكثر من السجلات في جدول المبيعات. ثم يعد استخدام الفهرس الموجود في الحقل SALE_DATE لحساب المتوسط بطيئًا بعض الشيء. لأنه إذا كنت تستخدم فهرسًا، فيجب عليك فرز الفهرس. عندما يكون هناك عدد كبير جدًا من السجلات التي تستوفي الشروط (مثل أن تمثل 50% أو أكثر من السجلات في الجدول بأكمله)، فإن السرعة ستتباطأ، لذا من الأفضل مسح الجدول بأكمله. لذلك، سيقرر MySQL تلقائيًا ما إذا كان سيتم استخدام الفهرس للاستعلام بناءً على نسبة البيانات التي تستوفي الشروط في الجدول بأكمله.
بالنسبة لـ MySQL، لا يتم استخدام الفهرس عندما تكون نسبة نتائج الاستعلام أعلاه إلى السجلات الموجودة في الجدول بأكمله حوالي 30%. يتم اشتقاق هذه النسبة بواسطة مطوري MySQL بناءً على خبرتهم. ومع ذلك، ستختلف قيمة المقياس الفعلي وفقًا لمحرك قاعدة البيانات المستخدم.
3. الفرز على أساس الفهرس
إحدى نقاط ضعف MySQL هي فرزها. على الرغم من أن MySQL يمكنها الاستعلام عن حوالي 15000 سجل في ثانية واحدة، إلا أن MySQL لا يمكنها استخدام سوى فهرس واحد على الأكثر عند الاستعلام. لذلك، إذا كان شرط WHERE يشغل الفهرس بالفعل، فلن يتم استخدام الفهرس في الفرز، مما سيؤدي إلى تقليل سرعة الاستعلام بشكل كبير. يمكننا إلقاء نظرة على عبارة SQL التالية: SELECT * FROM SALES WHERE NAME = "name" ORDER BY SALE_DATE DESC؛
تم استخدام الفهرس الموجود في حقل الاسم في جملة WHERE في SQL أعلاه، لذلك لن يتم استخدام الفهرس بعد الآن عند فرز SALE_DATE. من أجل حل هذه المشكلة، يمكننا إنشاء فهرس مركب في جدول المبيعات: ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
بهذه الطريقة، سيتم تحسين السرعة بشكل كبير عند استخدام عبارة SELECT أعلاه للاستعلام. ولكن كن حذرًا، عند استخدام هذه الطريقة، تأكد من عدم وجود حقل فرز في جملة WHERE. في المثال أعلاه، لا يمكنك استخدام SALE_DATE للاستعلام. بخلاف ذلك، على الرغم من أن الفرز أسرع، إلا أنه لا يوجد فهرس منفصل في حقل SALE_DATE ، لذلك سوف يتباطأ الاستعلام.
4. الكشف عن الاستعلامات التي يتعذر الوصول إليها
عند تنفيذ عبارات SQL، ستواجه حتمًا بعض الشروط التي يجب أن تكون خاطئة. ما يسمى بشرط "يجب أن يكون خطأ" هو أنه بغض النظر عن كيفية تغير البيانات في الجدول، فإن هذا الشرط يكون خطأ. مثل قيمة WHERE < 100 والقيمة > 200. لا يمكننا أبدًا العثور على رقم أقل من 100 وأكبر من 200.
إذا واجهت شروط الاستعلام هذه، فليس من الضروري تنفيذ عبارات SQL هذه. ولحسن الحظ، يمكن لـ MySQL اكتشاف هذا الموقف تلقائيًا. على سبيل المثال، يمكننا إلقاء نظرة على عبارة SQL التالية: SELECT * FROM SALES WHERE NAME = "name1" AND NAME = "name2"
يبحث بيان الاستعلام أعلاه عن السجلات التي يساوي اسمها كلاً من name1 وname2. من الواضح أن هذا استعلام لا يمكن الوصول إليه، ويجب أن يكون شرط WHERE خطأ. قبل أن ينفذ MySQL عبارة SQL، سيقوم أولاً بتحليل ما إذا كان شرط WHERE هو استعلام لا يمكن الوصول إليه. إذا كان الأمر كذلك، فلن يتم تنفيذ عبارة SQL بعد الآن. للتحقق من هذا. نستخدم أولاً EXPLAIN لاختبار SQL التالي: EXPLAIN SELECT * FROM SALES WHERE NAME = "name1"
الاستعلام أعلاه هو استعلام عادي، يمكننا أن نرى أن عنصر الجدول في بيانات معلومات التنفيذ التي تم إرجاعها بواسطة EXPLAIN هو المبيعات. يوضح هذا أن MySQL تدير المبيعات. انظر إلى العبارات التالية مرة أخرى: شرح التحديد * من المبيعات حيث الاسم = "name1" والاسم = "name2"
يمكننا أن نرى أن عنصر الجدول فارغ، مما يعني أن MySQL لم يعمل على جدول المبيعات.
5. استخدم تحديدات الاستعلام المختلفة لتحسين الأداء
بالإضافة إلى الاستخدام العادي لعبارة SELECT، توفر لنا MySQL أيضًا العديد من الخيارات التي يمكنها تحسين أداء الاستعلام. كما هو مذكور أعلاه، يعد SQL_NO_CACHE وSQL_CACHE، اللذين يتم استخدامهما للتحكم في التخزين المؤقت للاستعلام، خيارين من الخيارات. في هذا القسم، سأقدم بعض خيارات الاستعلام شائعة الاستخدام.
1.STRAIGHT_JOIN: فرض أمر الاتصال
عندما نقوم بتوصيل جدولين أو أكثر للاستعلام، لا نحتاج إلى الاهتمام بالجدول الذي يتصل به MySQL أولاً والجدول الذي يتصل به أخيرًا. يتم تحديد كل هذا من خلال تسلسل اتصال تحدده MySQL من خلال سلسلة من الحسابات والتقييمات الداخلية. في عبارات SQL التالية، لا يرتبط TABLE1 وTABLE2 بالضرورة ببعضهما البعض: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE...
إذا احتاج المطورون إلى التدخل يدويًا في ترتيب الاتصالات، فيجب عليهم استخدام الكلمة الأساسية STRAIGHT_JOIN، مثل عبارة SQL التالية: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE ...
كما يتبين من عبارة SQL أعلاه، يتم استخدام STRAIGHT_JOIN لإجبار MySQL على ضم الجداول بترتيب TABLE1 وTABLE2. إذا كنت تعتقد أن الانضمام بترتيبك الخاص أكثر فعالية من الترتيب الموصى به بواسطة MySQL، فيمكنك استخدام STRAIGHT_JOIN لتحديد ترتيب الاتصال.
2. التدخل في استخدام الفهرس وتحسين الأداء
لقد تم ذكر استخدام الفهارس أعلاه. في الظروف العادية، ستقرر MySQL ما إذا كنت تريد استخدام فهرس أم لا وأي فهرس يجب استخدامه عند الاستعلام. لكن في بعض الحالات الخاصة، نريد أن يستخدم MySQL فهرسًا واحدًا فقط أو عدة فهارس، أو لا نريد استخدام فهرس معين. يتطلب ذلك استخدام بعض خيارات الاستعلام في MySQL للتحكم في الفهرس.
الحد من نطاق استخدام الفهارس
في بعض الأحيان نقوم بإنشاء العديد من الفهارس في جدول البيانات عندما تحدد MySQL الفهرس، يتم أخذ هذه الفهارس كلها في الاعتبار. لكن في بعض الأحيان نريد أن يأخذ MySQL في الاعتبار عددًا قليلاً من الفهارس بدلاً من جميع الفهارس، وهذا يتطلب استخدام USE INDEX لتعيين عبارة الاستعلام. حدد * من فهرس استخدام الجدول 1 (FIELD1، FIELD2) ...
كما يتبين من عبارة SQL أعلاه، بغض النظر عن عدد الفهارس التي تم إنشاؤها في TABLE1، تأخذ MySQL فقط الفهارس التي تم إنشاؤها في FIELD1 وFIELD2 في الاعتبار عند اختيار الفهارس.
تحديد نطاق الفهارس غير المستخدمة
إذا كان لدينا العديد من الفهارس التي يجب مراعاتها وعدد قليل من الفهارس غير المستخدمة، فيمكننا استخدام IGNORE INDEX للاختيار العكسي. في المثال أعلاه، تم تحديد الفهرس الذي تم أخذه في الاعتبار، بينما يتم استخدام IGNORE INDEX لتحديد الفهرس الذي لم يتم أخذه في الاعتبار. اختر * من الجدول 1 تجاهل الفهرس (الحقل 1، الحقل 2) ...
في عبارة SQL أعلاه، لا يتم استخدام الفهارس الموجودة على FIELD1 وFIELD2 في الجدول TABLE1 فقط.
فرض استخدام الفهرس
يوفر المثالان أعلاه خيارًا لـ MySQL، مما يعني أن MySQL لا يتعين عليها استخدام هذه الفهارس. نأمل أحيانًا أن يستخدم MySQL فهرسًا معينًا (نظرًا لأن MySQL لا يمكنها استخدام سوى فهرس واحد عند الاستعلام، فيمكنها إجبار MySQL على استخدام فهرس واحد فقط). يتطلب هذا استخدام FORCE INDEX لإكمال هذه الوظيفة. اختر * من الجدول 1 مؤشر القوة (الحقل 1) ...
تستخدم عبارة SQL أعلاه الفهرس المبني على FIELD1 فقط، وليس الفهارس الموجودة في الحقول الأخرى.
3. استخدم الجداول المؤقتة لتحسين أداء الاستعلام
عندما يكون هناك الكثير من البيانات في مجموعة نتائج استعلامنا، يمكننا فرض مجموعة النتائج في جدول مؤقت من خلال خيار SQL_BUFFER_RESULT، بحيث يمكن تحرير قفل جدول MySQL بسرعة (بحيث يمكن لعبارات SQL الأخرى الاستعلام عن هذه البيانات Records) ) ويمكن أن تخدم مجموعات كبيرة من السجلات للعملاء لفترات طويلة من الزمن. حدد SQL_BUFFER_RESULT * من الجدول 1 حيث ...
على غرار خيار SQL_BUFFER_RESULT، يوجد SQL_BIG_RESULT. يُستخدم هذا الخيار بشكل عام لتجميع أو تمييز الكلمات الأساسية. يُعلم هذا الخيار MySQL أنه، إذا لزم الأمر، سيتم وضع نتائج الاستعلام في جدول مؤقت، أو حتى فرزها في الجدول المؤقت. حدد SQL_BUFFER_RESULT FIELD1، COUNT(*) من TABLE1 GROUP BY FIELD1
6. الاستنتاج
هناك أيضًا مبدأ "20/80" في البرمجة، أي أن 20% من الكود يستغرق 80% من الوقت. الأمر نفسه ينطبق على تطوير تطبيقات قاعدة البيانات. يركز تحسين تطبيقات قواعد البيانات على كفاءة تنفيذ SQL. ينصب تركيز تحسين استعلام البيانات على جعل خادم قاعدة البيانات يقرأ بيانات أقل من القرص ويقرأ الصفحات بشكل تسلسلي بدلاً من غير تسلسلي.