إستعلامات mysql متقدمة

[dev11]

لم أكن أعلم ذلك مسبقاً ، لكن الإستعلامات التي على الشاكلة التالية تعمل بالفعل !

select topic from posts where id in (
    select post from r where item = (
        select id from items where name = 'php' and type = 1
    )
) 

عموماًَ ، كيف يمكن تطبيق إستعلام الدمج ( join ) على الإستعلام السابق ؟ وماهي الصيغة العامة لهذا النوع من الإستعلامات ؟

[hilaby]

السلام، هذه الخاصيه موجوده مع الاصدار الخامس من MySQL وتسمى inline select statement او embedded statement وتتعامل معها مثل الجدول الطبيعي فمثلا تستطيه ان تقول "اعطني كل اسم مستخدم اسمه يبدأ او ينتهي بخالد ولكن سجل بعد العام 2007"

select * from 
( select * from records where register_date > "01-01-2007" ) 
as smaller_table 
where smaller_table.username LIKE "%khalid%" Limit 0,10

ملاحطة: الاستعلام الاول يستطيع استخدامه في امر استعلام واحد ولكن انا استخدمته للشرح فقط

الاستخدام سهل. ولكن له عيوب ومساوء وايظاً ايجابيات، تخيل ان لديك جدول كبير. وتريد ان تأخذ معلومه من جدول وتطابقه بجدول أكبر. مثلا تريد ان تبحث عن عدد الاهدافه للفريق ليفربول بعد العام 2005، كما تعرف جدول الاعبين كبير وعدد الاهداف في جدول الاهداف كبيره جدا؟

فمثلا فعلت هذا

select count(*) from players,teams,goals 
where team.name = "liverpool" and  players.team_id = team.id and
 goals.player_id = players.id and goals.date > "01-01-05"

فأدا هناك 30 لاعب في ليفر بول و 1000 لاعب في جدول اللاعبين.. و 300 فريق و 10000 هدف في قاعده البيانات، فستبخث في 300 سطر من القرق .. ثم تطابق رقم ذالك الفريق بالاعبين .. فكلما وصلت للاعب من ليفربول ستبحث في جدول الاهداف من اوله الى اخره "ويحتوي على 10000 سجل" ثم تجمع عدد الاهداف و تكرر تلك العمليه 30 مره ثم تفرز جميع الاهداف وتختار ماهو فقط بعد العام 2005 !!!!!! ليش؟؟ اي انك فعلت بحث في (30x10000) زائد 300 زائد 1000 .. اذا بحثت في 301300 سجل لكي تصل للحل !!! لماذا ؟؟

فلتسهيل العمليه هي في تقليل عدد السجلات التي تبحث فيها .. اي اذا عرفت انك ستبحث في جدول كبير ولكن تعرف ان كل الاهداف التي تريد ان تحسبها هي بعد العام 2005 .. فلماذا لا تبحث في المطلوب فقط؟؟

select count(*) from players,teams, 
( select * from goals where date > "01-01-05") as goals 
where team.name = "liverpool" and  players.team_id = team.id and
goals.player_id = players.id

طبعا هذي الطريقه اسرع . ولكن له عيب وهو انك ستستخدم جدول عشوائي تستخدمه مره واحده وسيكون كليا في الذاكره ويمسح بعد عمليه الاستعلام .. فاستخدم الحل هذا مع السرفرات التي لها رام كبير .. ماعدى ذالك تعلم استخدام الcache او بدأ بقرأه المانوال الخاص في الMySQL في تحسين اداء السرفر ...

اخوك خالد

[محمد الثقفي]

ماشاءالله عليك يا اخوي خالد

دائما شرحك رائع

الله يوفقك ان شاءالله

[daif]

- ميزة الاستعلام الفرعي subselect اكثر من رائعة, لكن يجب عليك ان تعرف متى تستخدمها فهو ليس شائع الاستخدام في التطبيقات ... غالبا ما يتم الاستعلام من جدول بدلالة حقول معينه او يكون من جدولين مع الربط بينها ووضع الشرط اللازم , كما هو مثال hilaby يمكن ان يكون ببساطة

select * from records where register_date > '01-01-2007' AND username LIKE '%khalid%' Limit 0,10 

- من حالات الاستعلام الفرعي مثلا في oracle ان تعيد عدد سجلات معين من نتيجة بحث مرتبه بالتاريخ .

SELECT * FROM (SELECT * FROM table_name WHERE name like '%swalif%' ORDER BY date) WHERE rownum <=10 

- لا اجد الاستعلام الفرعي شائع الاستخدام , لكنه يستخدم في استخلاص بينات من جداول ليس بينها علاقات ............. هل هناك امثله لذلك !

[dev11]

شكراً لك أستاذ hilaby ، وفعلاً كما قلت الـ cache مهمة في هذه الحالة لتحسين آداء الخادم وبما أنني مهتم بهذه النوعية من الإستعلامات فإنني بالطبع سأقرأ عن هذه الخاصية ، هل يمكنك أن تعطينا نبذة قصيرة عنها ( خاصية الـ cache ) ؟ ، أيضاً أنا مهتم بإستعلامات الـ join .. فكيف سأقوم بتطبيق الـ join على الإستعلام السابق ؟

أستاذ daif .. لا أعتقد أنه يمكن تطبيق هذه الخاصية على الجداول التي لايوجد بينها علاقات ..

لنفرض أنه يوجد لدينا جدولان واحد للتصنيفات يحتوي كل حقل فيه على قيمة واحدة وهي اسم التصنيف ، وآخر للمقالات ويحتوي كل حقل فيه على قيمة واحدة وهي اسم المقال ..

لوكان لدينا في جدول التصانيف البيانات :

code
javascript

وفي جدول المقالات :

sessions in php
cookies in javascript

فإنهما إن كانا على هذه البنية فلا يمكن الربط بينها أبداً ( هذا ما أظنه ) .. لابد من وجود حقل إضافي ذو قيمة فريدة في جدول التصنيفات وحقل إضافي في جدول المقالات يحتوي على رقم التصنيف المرتبط به ( هذه طريقة ) وهناك طرق أخرى لا أعلمها ، فإن كانت لديك طريقة للربط بين الجدولين دون التغيير في بنيتهما الحالية فهلا شاركتها معنا

ولا بد أن أضيف أن هذه الطريقة في الإستعلام ( طريقة الإستعلام الفرعي ) ستختصر علي كثيراً من الشفرات ، سابقاً كنت لكي أستخرج البيانات بنفس الطريقة التي وضعتها ، كان علي أن أقوم بثلاثة إستعلامات ، وللتعامل مع كل إستعلام أستخدم الدوال mysql_fetch_array و mysql_free_result ، مما يعني تكرار نفس العملية على ثلاثة إستعلامات مختلفة ، أما الان فبإستعلام واحد وبطريقة واحدة للتعامل معه .. يمكنني أن أستخرج البيانات التي أريدها وكنت أستخرجها بواسطة الطريقة السابقة .

طبعاً كم أشار أخي hilaby لابد من التعامل مع هذا النوع من الاستعلامات بحرص لانها تأخذ مساحة ليست بالبسيطة من الذاكرة ، وهذا ما سأقوم بالبحث والقراءة عنه مستقبلاً لكي أستطيع التعامل مع هذه النوع من الإستعلامات بالشكل الصحيح .

[hilaby]

السلام عليكم.
الاخ dev11 انا شخصيا احب هذة المناقشات لانها اولا مفيده لمن يريد ان يتعلم المزيد، و مفيده لي كشخص سيرى وجهة نظر شخص اخر، ربما ساتعلم شياً

انا سأكتب شيء اكثر تفصيلا بعد الدوام ..
ولكن الى ذالك الوقت تستطيع ان تقرأ عن الcache وطريقه استخدامها بعد ناتج احد النقاشات التي قام بها احد الاعضاء في سوالف معي على الرابط
كيف يمكن عمل ذلك في php - كلاس تصغير الصور - سوالف سوفت

واذا اردت مدونه خاصه تتكلم فقط عن تحسين أداء الMySQL قم بقرائه مقالات المدونه MySQL Performance Blog فيها معلومات جدا قيمه

[daif]

dev11
نعم كما ذكرت لا بد من وجود رقم فريد في التصنيفات ويتم ادخاله في المقالات حتى تستطيع الربط بينها .
هل يمكن أن تشرح ماذا تريد ان تفعل عمليا ! كما ذكرت ان استخدامات الاستعلام الفرعي ضيقة جدا .

[عبقري العرب]

شكرا لكم اخواني .. نورتونا بحديثكم المتقدم حول الاستعلامات

اريد ان استفسر ..
هل يمكن عمل تحديث لعدة صفوف باستعلام واحد ؟؟ ( طبعا التحديث لحقل بعدة قيم لكل صف و بدون استخدام لووب)

[dev11]

أخي العزيز daif .. ماوضعته ليس سوى مجرد مثال فقط لاغير ، أما عن ما أريد الوصول إليه فهو كيفية تطبيق الـ join على الإستعلام الفرعي الذي قمت بوضعه في بداية الموضوع ..

[hilaby]

عبقري العرب "الرد على سؤالك - هل يمكن عمل تحديث لعدة صفوف باستعلام واحد ؟"
كما اعرفه انه لا يوجد .. لانك اذا اردت ان تغير عده حقول مره واحده .. هذا يدل على خلل ما في هيكل قاعده البيانات او تصميمها .. لاننا كما نعلم . اذا وجد اكثر من سجل في جدول معين بنفس البيانات نظع تلك البيانات في جدول اخر ونربطه برابط اجنبي .. "foreign key"
فانا لا ارى اي مثال او لا استطيع ان اتخيل مثال يدل على انك ستريد ان تغير سجلات ببيانات مختلفه يجمعها رمز موحد ..

dev11
اجابتك سهله.. وانا احس ان المثال افضل من الشرح ..

SELECT * FROM teams, players 
LEFT JOIN (SELECT COUNT(*) as numberOfGoals FROM goals 
WHERE goals.date > "2005-01-01" GROUP BY goals.player_id) 
as goalsAfter2005 
ON players.id = goalsAfter2005.player_id
WHERE 
teams.name = "liverpool" and
players.team_id = teams.id

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

لنقم بمتابعه النقاش لانه من الاشياء الشيقه .. التي هي في اصل تخصصي الحالي

[dev11]

فعلاً ، لا أعتقد أنه يمكن تحديث أكثر من سجل من خلال إستعلام واحد إلا إن كنت تريد وضع قيمة واحدة في جميع السجلات أو بعض السجلات المحددة ..

update table set topic = 'swalif softs';
// or
update table set topic = 'php' where id in (1, 2, 3 ); 

أستاذ hilaby ، لقد قمت بالقراءة عن إستعلامات الـ join ومما فهمته أن هناك ثلاثة أنواع منها :

inner join : لإستخراج السجلات من الجدولين
left join : لإستخراج السجلات من الجدول الأيسر
right join : لإستخراج السجلات من الجدول الأيمن

الصيغة العامة لهذا النوع من الإستعلامات ..

select table1.column, table2.column
from table1, table2
where table1.key_column = table2.foreign_key_column; 

بالمناسبة ، لقد قمت بالقراءة عن الـ mysql query cache ، ومما قرأته إستخلصت مايلي :

1 - يتم تخزين البيانات عند إستخراجها من قاعدة البيانات لأول مرة في الذاكرة المخصصة لذلك ، وتقوم أنت بتحديد الحد الأقصى هذه الذاكرة ، أعتقد أن هذه هي النقطة التي أشار إليها الأستاذ hilaby بخصوص الذاكرة وآداء الخادم .

2 - عندما يتم الإستعلام من قاعدة البيانات .. يتم البحث عن النتائج في البيانات المخزنة من قبل ، إن وُجدت يتم أرسالها إليك وإن لم يتم إيجادها فإنه يتم تنفيذ الإستعلام و إستخراج البيانات ويتم تخزينها في الذاكرة .

3 - عند حدوث أي تحديث على الجدول من ( إضافة ، تعديل ، حذف ) .. فإنه يتم حذف البيانات المخزنة السابقة ويتم إستبدالها بالبيانات المحدثة تلقائياً ، وهذه خاصية جميلة ومفيدة ، في الواقع بسبب هذه النقطة بالذات سأقوم بالتركيز على إستخدام الـ mysql query cache في المستقبل ، طبعاً لابد من الإشارة إلى أن الفرق في السرعة سيكون كبيراً ومؤثراً ، خصوصاً إن كانت لديك قاعدة بيانات بحجم كبير وذات إستعلامات كثيرة ، لكن المشكلة أيضاً تكمن إذا كانت قاعدة البيانات تتحدث بإستمرار كل دقيقة ( خمس دائق ،عشر دقائق أو نصف ساعة ) مثلاً .. ، في هذه الحالة لا يحبذ إستخدام هذه الخاصية لأن الـ cache في هذه الحالة لا فائدة منه على الإطلاق ! فما فائدة تخزين شيء سيتم تحديثه بعد خمس دقائق !

4 - الخيار query_cache_type في ملف الإعدادات إذا كانت قيمته 1 فإن الـ cache سيتم تنفيذه دائما عند إستخراج أي بيانات ، أما إن كانت القيمة 2 فإن البيانات المخزنة سيتم إستخراجها إذا تم طلبها فقط .

select SQL_CACHE something from table 

هذا ما إستطعت إستخلاصة من قراءة سريعة للمقال The MySQL Query Cache .

لكن مع الأسف لم أستطع تطبيق الإعدادت على الخادم الموجود لدي ، فالخيار query_cache_type غير موجود في ملف my.ini الخاص بإعدادت mysql .. هل من حلول ؟

أستاذ daif .. ماهي الطريقة التي تتبعها في إستخدام الـ cache في برمجية القالب السهل ؟

[daif]

dev11 في قواعد البينات يتم استخدام التخزين المؤقت (cache) عندما يكون الاستعلام بطيء ومعقد او يكون عليه معالجه وغالبا ما تكون المعالجه في php وليس في sql , وهذا يحدث قليلا بالاضافة الى ان السجلات الناتجه قليله بالنسبه للجدول نفسه , لكن بشكل عام استخدام الكاش في كل الاستعلامات سوف يستهل الكثير من الذاكره وهذا سوف يجعل وضع الخادم نفسه سيء .

- القالب السهل عند عرض ملف html يتحقق من تاريخ تعديل الملف اذا كان اكبر من ملف cache سوف يعالج الملف ثم يحدث ملف الكاش وعندما يكون العكس يتعامل مع ملف الكاش مباشره .

hilaby الكاش ليس لحل المشكلة في بنية البينات لكنه طريقة لتسريع علملية تتكرر باستمرا وانت في غنى عنها مثل: ناتج معالجة ملف لا يتم تحديثة الا نادرا مثل القالب السهل , او عرض بينات تحدث بوقت محدد مثل صفحة تداول يتم جلب اسعار الاسهم من قاعدة البينات كل 5 دقائق ثم تخزن في صفحة(تعتبر كاش) تعرض للجميع وكل 5 دقائق تحدث من جديد.

[dev11]

نعم ، الآنه فهمت الطريقة ، أنت تستخدم الدالة filemtime لايجاد تاريخ تعديل الملف ، اذا كان أكبر من ملف الـ cache يتم تحديثه ومن ثم عرضه والا يتم عرض ملف الـ cache ، الآن أرى أهمية إستخدام القوالب ، وفعلاً هذه الطريقة أفضل من إستخدام الـ mysql query cache لانها لاتستهلك كثيراً من الذاكرة على عكس النظام الاخر .

حسناً ، كيف يمكننا تحويل الإستعلام التالي إلى join ؟

select topic from posts where id in ( 
    select post from r where item = ( 
        select id from items where name = 'php' and type = 1 
    ) 
) 

[daif]

يمكن وبسهوله , الاستعلام التالي هو بالضبط نفس INNER JOIN أي أنك عندما تستخدم INNER JOIN فانك تستخدم .

SELECT topic FROM posts, r, items 
WHERE  posts.topic = r.post
AND r.item=items.id
AND items.name = 'php'
AND items.type = 1 

نفس INNER JOIN

SELECT topic FROM posts
INNER JOIN r ON posts.topic = r.post
INNER JOIN items ON r.item=items.id
WHERE items.name = 'php'
AND items.type = 1 

لكن لو كان جدول items لا يوجد به type = 1 اذا الاستعلام السابق لن يعيد لك نتيجه , يمكن ان تعمل LEFT JOIN مع items حتى تعود لك نتيجه حتى لو كان الشرط لم يتحقق وكذلك مع r ويمكن عكس العمليه بـ RIGTH JOIN اي يعيد لك items ليس له posts او r .
- كتبت الكود بدون تجربه فعلية لكن اعتقد انه سيعمل

[dev11]

للأسف لم يعمل الإستعلام السابق ، والجدول items يوجد به الحقل type ، تخطيط القاعدة موجود في هذا الموضوع ، أتمنى سماع رأيكم في تخطيط القاعدة الموجودة في الموضوع الذي ذكرته .