دروس في تحسين الاستعلامات

السلام عليكم ورحمة الله وبركاته

اخواني الاعزاء..

اعتذر عن الغيبه عن المنتدى..

هذا الموضوع دعوة لتبادل الخبرات قبل ان يكون درس.. وذلك لتعم الفائده على الجميع

فياحبذا لو شاركنا الجميع بما يعلم

اليوم لكم في جعبتي موضوع مهم يهم جميع المبرمجين.. :D

كيف يمكننا ان نقلل من عدد الاستعلامات ؟

وكيف نستطيع تخفيف الضغط على القاعده ؟

فاذا ماعلمنا طريقة تقليل الاستعلامات سنخفف الضغط على القاعده بشكل كبير..

مع ذلك لن نكون قد حللنا المشكله جذريا.. ولكن نكون قد حسناها

اذا فإن مشكلة الضغط القاعده ينصب بشكل جوهري ورئيسي على عدد الاستعلامات..

مقدمه:

فمثلا: ان كتبنا صفحة معينة تعرض بيانات من القاعده.. ومجموع الاستعلامات كان تقريبا 15 استعلام

فاذا زار هذه الصفحه 100 شخص في نفس اللحظة فإن مجموع الاستعلامات سيكون (15 استعلام * 100 زائر) والمجموع 1500 استعلام

هل لك عزيزي القارئ ان تتخيل معنى 1500 استعلام؟ وما مدى الارهاق والضغط الذي تسببه الى القاعده.. لعرض نفس المحتوى لكل زائر؟

فهي ليست بالهينة بتاتا..

لذلك حسب وجه نظري المتواضعه.. أرى ان الحل يكمن في طريقتين.. لاغنى لأحدهما عن الأخرى

المرحلة الأولى:

هي معرفة وتطبيق الطريقة المثلى لعمل الاستعلامات (وتقليلها قدر الامكان وذلك بالبرط بين الجداول)

المرحلة الثانية:

هي باستخدام أصناف الذاكرة المؤقته لقاعدة البيانات - Database Caching Classes

فإذا طبقنا المرحلة الأولى فسنتمكن ان شاء الله من تقليل الاستعلامات في مثالنا السابق من 15 الى 3 استعلامات (وربما اكثر او اقل باستعلام او اثنين تقريبا)

فيكون المجموع بين300 الى 500 استعلام تقريبا بالمقارنة مع 1500 استعلام في مثالنا الاول.. فإنه انجاز جيد ولا بأس به اطلاقا..

ولكن مالحل اذا زاد عدد الزوار الى 500 ؟ فسيكون تقريبا 2000 استعلام (متوسط ال 3 استعلامات 5 استعلامات ، سنكون اكثر دقه بعد قليل بتطبيق المثال)

فمالحل اذا..؟

الحل هو في المرحلة الثانيه.. وهي باستخدام Database caching classes اما ان تقوم انت بكتابته او تستخدم احد الاصناف الجاهزة والتي موجوده بكثرة على النت

والسبب في ذلك اننا نقوم بتخزين البينات التي نقوم بجلبها بصورة متكررة من القاعده مع كل زيارة للصفحه وتخزينها في جدول مؤقت واحد. او نقوم بتخزينها في ملفات على الموقع قد تكون xml, txt, json الخ.. المهم انها تناسب مانريده

وبالتالي سترى ان عدد الاستعلامات يبقى ثابت ولا يتعدى الاستعلام او الاثنين مهما زاد عدد الزوار

لنتكلم عن المرحة الأولى:

وهي طريقة تقليل الاستعلامات عن طريق كتابتها بالطريقة الصحيحه للربط بين الجدوال.

الشق الأول:

المثال الذي سنطبق عليه الاستعلامات

بما ان البرمجة قريبه جدا من الرياضيات ان لم تكن هي كذلك بالفعل.. فافضل طريقه للشرح هي بتطبيق مثال

بفرض ان لدينا 3 جداول

الاول categories ويحتوي على

cat_id
cat_name
cat_total_books

والثاني books ويحتوي على

book_id
book_name
book_author_id
book_cat_id

والثالث authors ويحتوي على

author_id
author_name
author_total_books

ملاحظة: لاحظة اني اسبقت كل حقل باسم الجدول وذلك لكي يسهل علينا عملية التمييز بين اسماء الحقول في عملية الربط ، بالاضافة الى تعيين متغيرات متميزة عن الاخرى دون الحاجه لاستخدام خاصيه AS الموجوده في ال mysql (غالبا وليس دائما)

الشق الثاني:

طريقة جلب البيانات من القاعده وربط الجداول ببعضها البعض

الأن لنأتي لتطبيق المثال

نريد عمل صفحه لعرض اخر 10 كتب

فكيف يتم ذلك ؟؟

قد يقول شخص سهله.. وذلك بعمل استعلام اولي عن الكتب بالاستعلام التالي

SELECT book_id, book_name FROM books LIMIT 0,10

وعندما نقوم بفرز المصفوفه نعمل استعلامين اخرين عن الاول عن اسم الكاتب

SELECT cat_name FROM categories WHERE cat_id = $book_cat_id

بالفعل لقد استعطت اتمام المطلوب..

ولكن انتظر هل تعلم كم استعلام فعلت؟؟

الاستعلام الاساسي = 1

وفي كل عملية فرز ( ونحن مازلنا في التكرار) نقوم بعمل استعلامين اي استعلامين * الحد الاقصى للصفوف في الاستعلام الاساسي

اي 2 * 10 = 20 + الاستعلام الاساسي = 21 استعلام استعلام لكل زائر!!!!

أي بفرض ان لدينا 100 زائر فسيكون 21 استعلام * 100 زائر = 2100 استعلام!!!! إرهاق بمعنى الكلمة

حسنا يمكننا تنفيذها باستعلام واحد وهو الاستعلام الاساسي ونحن في عملية الفرز نقوم بتخزين معرفات الكتب في متغير واحد ونفصل بينهم بعلام الـ

,

ونفس الشيء بالنسبة لأسماء المؤلفين وبعد الانتهاء نقوم بعمل استعلامين الاول للكتب والثاني للمؤلفين وذلك باستخدام خاصية

WHERE cat_id IN ($all_cats)

هذا افضل بكثير.. حيث اننا عملنا المطلوب بـ 3 استعلامات...

أي 3 استعلامات * 100 زائر = 300 استعلام.. هممم!!

ولكن هل هناك طريقة افضل؟؟

قد يقول شخص نعم.. وذلك باستخدام اوامر ال JOIN وبذلك نستطيع عملها باستعلام واحد فقط...

ممتاز.. وهنا سنستخدم INNER JOIN لأنها اسرع من LEFT JOIN

تابع معي..

القاعده:

SELECT
	prefix_1.field_1 , 
	prefix_2.field_2 , 
	prefix_3.field_3 , 
	...
FROM
	table_1 prefix_1 ,
	table_2 prefix_2 ,
	table_3 prefix_3 ,
	...
WHERE
	prefix_1.field_1 = 'something' AND
	prefix_2.field_4 = prefix_3.field_5 OR
	...

حيث ان

prefix_X = اسم افتراضي نقوم نحن بتعيينه للتمييز بين الجداول التي نريد الاستعلام منها.. والاهي هو لاخبار خادم القاعده باننا نقصد الجدول الفلاني عن طريق هذا الاسم وذلك حينما نقوم بكتابة اسم الجدول بعد صيغة FROM

field_Y = اسم الحقل

table_Z = اسم الجدول

table_1 prefix_1 اخبرنا الخادم انن نقصد بالـ prefix_1 انه تابع للجدول table_1

وبتطبيق القاعده على فرضيتنا السابقه

SELECT
	bks.book_id , 
	bks.book_name , 
	cats.cat_name , 
	athrs.author_name
FROM
	books bks,
	categories cats,
	authors athrs
WHERE
	bks.book_author_id = athrs.author_id AND 
	bks.book_cat_id = cats.cat_id
LIMIT 0,10

bks.book_author_id = athrs.author_id

اشترطنا ان تكون قيمة معرف المؤلف الموجوده في جدول الكتب مساوية لقيمة قيمة معرف المؤلف الموجوده في جدول المؤلفين وذلك ليقوم بارجاع اسم المؤلف من جدول المؤلفين الذي سبق وحددناه عن طريق

athrs.author_name

bks.book_cat_id = cats.cat_id

بالضبط مثل السابق ولكن تتم المقارنه لـ قيمة معرف القسم وارجاع اسمه

وبذلك استطعنا جلب البيانات باستعلام واحد فقط

أي 1 استعلام * 100 زائر = 100 استعلام

نتيجه مرضيه جدا.. ولكننا سنواجه بعض المشاكل في حال ما اذا زاد عدد الزوار الى 1000 فسيكون عدد الاستعلامات 1000 استعلام.. وهذا ما سنقوم بمعالجته عن طريق ال Database Caching Classes وهي المرحة الثانيه ان شاء الله

الشق الثالث:

طريقة تحديث أكثر من جدول وأكثر من قيمة

الأن، نريد نقل الكتاب صاحب المعرف رقم 3

من القسم 4 الى القسم 5

ومن المؤلف 6 الى المؤلف 7

ويترتب على ذلك ان عدد الكتب الموجوده في القسم 4 قد نقصت كتاب

وزاد القسم 5 كتاب لذلك يجب تحديث العدادات الموجوده فيها وذلك بـ

طرح 1 من القسم 4

وزيادة 1 الى القسم 5

وكذلك هو الحال بالنسبة لجدول المؤلفين فقد نقص المؤلف 6 كتاب

وزاد المؤلف 7 كتاب لذلك علينا ايضا تحديث جدول المؤلفين بعمل التالي

طرح 1 من المؤلف 6

وزيادة 1 الى المؤلف 7

فتخيل ان عملت كل ذلك باستعلامات عاديه.. فكم عدد الاستعلامات؟

1 لتعديل القيم الموجوده في جدول الكتب

1 لانقاص 1 من جدول الاقسام

1 لزيادة 1 لجدول الاقسام

1 لانقاص واحد من جدول المؤلفين

1 لزيادة 1 لجدول المؤلفين

اي قمت بعمل 5 استعلامات.. بينما يمكنك الوصول لنفس النتيجه باستعلام واحد..

تابع معي من فضلك..

فبتطبيق القاعده التاليه

UPDATE 
	table_1 prefix_1, 
	table_2 prefix_2, 
	table_3 prefix_3, 
	...
SET 
	prefix_1.field_1 = 'YOUR_DATA' , 
	prefix_2.field_2 = 'YOUR_DATA' , 
	prefix_3.field_3 = 'YOUR_DATA' , 
	... 
WHERE 
	prefix_1.field_4 = 'SOMETHING' AND 
	prefix_2.field_5 = 'SOMETHING' AND 
	prefix_3.field_6 = 'SOMETHING' AND 
	...

يكون شكل الاستعلام كالتالي

UPDATE 
	categories cat_new, 
	categories cat_old, 
	authors auth_new, 
	authors auth_old, 
	books bks 
SET 
	bks.book_cat_id = 5 , 
	bks.book_author_id = 7 , 
	cat_new.cat_total_books = cat_new.cat_total_books+1 , 
	cat_old.cat_total_books = cat_old.cat_total_books-1 , 
	auth_new.author_total_books = auth_new.author_total_books+1 , 
	auth_old.author_total_books = auth_old.author_total_books-1 
WHERE 
	bks.book_id = 3 AND 
	cat_old.cat_id = 4 AND 
	cat_new.cat_id = 5 AND 
	auth_old.author_id = 6 AND 
	auth_new.author_id = 7

لاحظ

	categories cat_new, 
	categories cat_old,

اسمينا الاول بالجديد والثاني بالقديم وذلك لكي نتمكن من تحديث عداد القسم الجديد وعداد القسم القديم..

فالجديد هو الذي حصلت به زياده.. والقديم هو ما حصل به نقصان.. وبذلك نتمكن من زيادة وطرح مانريد بما اننا استطعنا فصلهم

فيكون:

	cat_new.cat_total_books = cat_new.cat_total_books+1 , 
	cat_old.cat_total_books = cat_old.cat_total_books-1 ,

مع انهم في الواقع نفس الحقل ولكن كيف سيعلم الخادم اننا نريد انقاص واحد من القسم 4 واضافة واحد الى القسم 5؟؟

ذلك يكون بالشرط الذي وضعناه

	cat_old.cat_id = 4 AND 
	cat_new.cat_id = 5 AND

فعمليتا التحديث + الشرط يكونان مبنيان على الاسم الذي سبق ووضعناه امام الجدول كما فعلنا هنا

اتمنى ان يكون الدرس قد افادكم قبل ان يعجبكم..

فإن كان كذلك.. فلا تبخلوا بدعوة صادقه لي ولأهلي بالتوفيق والمغفره

والدرس القادم ان شاء الله سيكون عن الشق الرابع وهو عمل احصائيات لعدة جداول وقيم باستعلام واحد

وبعد ذلك سأتطرق للمرحله الثانيه وهذا Database Caching

بالتوفيق