عدة SELECT COUNTs في استعلام MySQL واحد. تحسين استعلامات MySQL الاستعلام عن استعلامات متعددة في واحد

لقد كتبت بالفعل عن مختلف استفسارات SQL، ولكن حان الوقت للحديث عن أشياء أكثر صعوبة ، على سبيل المثال ، استعلام SQL لتحديد السجلات من جداول متعددة.

عندما قمت أنت وأنا بالاختيار من طاولة واحدة ، كان كل شيء بسيطًا جدًا:

حدد required_field_names من table_name حيث حدد select_condition

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

لنفكر في استعلام مثل هذا:

حدد * من table_1 ، table_2 حيث table_1.id> table_2.user_id

بالنسبة للكثيرين الذين لم يتعاملوا مع مثل هذه الاستفسارات ، يبدو أن كل شيء بسيط للغاية ، معتقدين أنه تمت إضافة أسماء الجداول فقط هنا قبل أسماء الحقول. في الواقع ، هذا يتجنب التناقضات بين نفس الأسماءمجالات. ومع ذلك ، فإن الصعوبة لا تكمن في هذا ، ولكن في خوارزمية تشغيل مثل هذا الاستعلام SQL.

تكون خوارزمية العمل كما يلي: يتم أخذ السجل الأول من الجدول 1... مأخوذ هوية شخصيةهذا الدخول من الجدول 1... مزيد من الجدول يبدو تماما الجدول 2... ويتم إضافة جميع السجلات حيث قيمة الحقل معرف المستخدمالأصغر هوية شخصيةالإدخال المحدد في الجدول 1... وهكذا ، بعد التكرار الأول ، قد يظهر هناك من 0 إلى لانهائيالسجلات الناتجة. في التكرار التالي ، يتم أخذ السجل التالي للجدول الجدول 1... يتم فحص الجدول بأكمله مرة أخرى الجدول 2، ويتم تشغيل شرط الجلب مرة أخرى table_1.id> table_2.user_id... تتم إضافة كافة السجلات التي تفي بهذا الشرط إلى النتيجة. قد يتحول الإخراج كمية كبيرةيسجل عدة مرات أكبر من الحجم الإجمالي لكلا الجدولين.

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

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

سيحل استعلام مماثل محل استعلامين SQL: للاختيار بشكل منفصل عن الجدول مع المنتجات ومن الجدول مع المستخدمين. بالإضافة إلى ذلك ، سيتطابق هذا الطلب على الفور مع المستخدم ومنتجه.

الاستعلام نفسه بسيط للغاية (إذا فهمت السؤال السابق):

حدد * من المستخدمين ، المنتجات حيث users.id = products.owner_id

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

كما ترون استعلامات SQL للاختيار من بينها جداول متعددةليست أبسطها ، ولكن فوائدها يمكن أن تكون هائلة ، لذلك من المرغوب جدًا معرفة مثل هذه الاستعلامات والقدرة على استخدامها.

9 أكتوبر 2008 الساعة 11:37 مساءً

تحسين استعلامات MySQL

• MySQL

في العمل اليومي ، يتعين على المرء أن يتعامل مع نفس النوع من الأخطاء عند كتابة الاستفسارات.

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

• إحضار كافة الحقول
  حدد * من الجدول

  عند كتابة الاستفسارات ، لا تستخدم مجموعة مختارة من جميع الحقول - "*". اذكر فقط الحقول التي تحتاجها حقًا. سيؤدي ذلك إلى تقليل كمية البيانات التي يتم جلبها وإرسالها. أيضًا ، لا تنسَ تغطية الفهارس. حتى إذا كنت تحتاج حقًا إلى جميع الحقول الموجودة في الجدول ، فمن الأفضل إدراجها في قائمة. أولاً ، يحسن قابلية قراءة الكود. عند استخدام علامة النجمة ، من المستحيل معرفة الحقول الموجودة في الجدول دون النظر إليها. ثانيًا ، بمرور الوقت ، قد يتغير عدد الأعمدة في جدولك ، وإذا كان هناك اليوم خمسة أعمدة INT ، فقد تتم إضافة حقلي TEXT و BLOB في شهر واحد ، مما يؤدي إلى إبطاء التحديد.

• الاستعلامات الحلقية.
  يجب أن تكون واضحًا بشأن SQL كونها لغة محددة. في بعض الأحيان ، يجد المبرمجون الذين اعتادوا التفكير بلغات إجرائية صعوبة في إعادة التفكير في تفكيرهم بلغة المجموعات. يمكن القيام بذلك بكل بساطة من خلال اعتماد قاعدة بسيطة - "لا تنفذ الاستعلامات في حلقة." أمثلة على كيفية القيام بذلك:

  1. العينات
  $ news_ids = get_list ("SELECT news_id من today_news") ؛
  while ($ news_id = get_next ($ news_ids))
  $ news = get_row ("SELECT title، body from news WHERE news_id =". $ news_id)؛

  القاعدة بسيطة للغاية - كلما قل عدد الطلبات ، كان ذلك أفضل (على الرغم من وجود استثناءات لذلك ، بالنسبة لأي قاعدة). لا تنسَ بناء IN (). يمكن كتابة الكود أعلاه في استعلام واحد:
  حدد العنوان ، النص الأساسي من today_news INNER JOIN news USING (news_id)

  2. إدراجات
  $ log = parse_log () ؛
  بينما (سجل $ = التالي ($ log))
  الاستعلام ("INSERT INTO logs SET value =" (! LANG:. $ log ["value"]);!}

  يعتبر لصق وتنفيذ استعلام واحد أكثر فاعلية:
  INSERT INTO logs (value) VALUES (...)، (...)

  3. التحديثات
  في بعض الأحيان يكون من الضروري تحديث عدة صفوف في نفس الجدول. إذا كانت القيمة المحدثة هي نفسها ، فكل شيء بسيط:
  UPDATE news SET title = "(! LANG: test" WHERE id IN (1, 2, 3).!}

  إذا كانت القيمة المتغيرة لكل سجل مختلفة ، فيمكن القيام بذلك باستخدام الاستعلام التالي:
  تحديث مجموعة الأخبار
  العنوان = CASE
  عندما news_id = 1 ثم "aa"
  عندما news_id = 2 ثم نهاية "bb"
  أين news_id IN (1، 2)

  تظهر اختباراتنا أن مثل هذا الطلب أسرع مرتين أو ثلاث مرات من عدة طلبات منفصلة.

• إجراء عمليات في الحقول المفهرسة
  حدد user_id من المستخدمين حيث blogs_count * 2 = القيمة بالدولار

  لن يستخدم هذا الاستعلام الفهرس حتى إذا تم فهرسة عمود blogs_count. لا يجب إجراء أي تحويلات في الحقل المفهرس في الاستعلام للفهرس الذي سيتم استخدامه. للاستعلامات المماثلة ، انقل وظائف التحويل إلى جزء مختلف:
  حدد user_id من المستخدمين حيث blogs_count = $ value / 2 ؛

  مثال مشابه:
  حدد user_id من المستخدمين من حيث TO_DAYS (CURRENT_DATE) - TO_DAYS (مسجل)<= 10;

  لن تستخدم فهرسًا فوق الحقل المسجل ، بينما
  حدد user_id من المستخدمين حيث تم التسجيل> = DATE_SUB (CURRENT_DATE ، INTERVAL 10 DAY) ؛
  إرادة.

• إحضار الصفوف فقط لحساب عددهم
  نتيجة $ = mysql_query ("حدد * من الجدول"، $ link)؛
  num_rows $ = mysql_num_rows (بالدولار الأمريكي) ؛
  إذا كنت بحاجة إلى تحديد عدد الصفوف التي تفي بشرط معين ، فاستخدم تحديد عدد الصفوف (*) من استعلام الجدول ، بدلاً من تحديد كل الصفوف فقط لحساب عدد الصفوف.
• إحضار صفوف إضافية
  نتيجة $ = mysql_query ("حدد * من جدول 1" ، رابط $) ؛
  while ($ row = mysql_fetch_assoc ($ result) && $ i< 20) {
  …
  }
  إذا كنت تحتاج فقط إلى عدد n من الصفوف ، فاستخدم LIMIT بدلاً من تجاهل الصفوف الإضافية في التطبيق الخاص بك.
• باستخدام ORDER BY RAND ()
  حدد * من الجدول ORDER BY RAND () LIMIT 1 ؛

  إذا كان الجدول يحتوي على أكثر من 4-5 آلاف صف ، فسيعمل ORDER BY RAND () ببطء شديد. سيكون تنفيذ استعلامين أكثر كفاءة:

  إذا كان الجدول auto_increment يحتوي على مفتاح أساسي "جديد" ولا توجد فجوات:
  $ rnd = rand (1، query ("SELECT MAX (id) FROM table"))؛
  $ row = query ("SELECT * FROM table WHERE id =". $ rnd)؛

  أو:
  $ cnt = استعلام ("SELECT COUNT (*) من الجدول") ؛
  $ row = query ("SELECT * FROM table LIMIT". $ cnt. "، 1")؛
  والتي ، مع ذلك ، يمكن أن تكون بطيئة أيضًا مع وجود عدد كبير جدًا من الصفوف في الجدول.

• باستخدام عدد كبير من JOINs
  تحديد
  v.video_id
  اسم،
  ز
  من عند
  videos AS v
  الانضمام إلى اليسار
  link_actors_videos AS la ON la.video_id = v.video_id
  الانضمام إلى اليسار
  الجهات الفاعلة كما في a.actor_id = la.actor_id
  الانضمام إلى اليسار
  link_genre_video AS lg ON lg.video_id = v.video_id
  الانضمام إلى اليسار
  الأنواع AS g ON g.genre_id = lg.genre_id

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

• باستخدام LIMIT
  حدد ... من الجدول LIMIT $ start، $ per_page

  يعتقد الكثير من الناس أن مثل هذا الاستعلام سيعيد سجلات $ per_page (عادةً 10-20) وبالتالي سيعمل بسرعة. ستعمل بسرعة مع الصفحات القليلة الأولى. ولكن إذا كان عدد السجلات كبيرًا ، وتحتاج إلى تنفيذ SELECT ... من الجدول LIMIT 1000000 ، 1000020 استعلام ، ثم لتنفيذ مثل هذا الاستعلام ، ستقوم MySQL أولاً بتحديد 1000020 سجل ، وتجاهل المليون الأول وإرجاع 20. كن سريعًا على الإطلاق. لا توجد طرق تافهة لحل المشكلة. كثير من الناس يقصرون ببساطة عدد الصفحات المتاحة على عدد معقول. يمكنك أيضًا تسريع مثل هذه الاستعلامات باستخدام فهارس التغطية أو حلول الجهات الخارجية (مثل أبو الهول).

• عدم استخدام "تحديث مفتاح مكرر"
  $ row = query ("SELECT * FROM table WHERE id = 1")؛

  إذا ($ row)
  الاستعلام ("UPDATE table SET column = العمود + 1 WHERE id = 1")
  آخر
  الاستعلام ("INSERT INTO table SET عمود = 1 ، id = 1") ؛

  يمكن استبدال بنية مماثلة باستعلام واحد ، بشرط أن يكون هناك مفتاح أساسي أو فريد لحقل المعرف:
  INSERT INTO table SET عمود = 1 ، معرف = 1 في عمود DUPLICATE KEY UPDATE = عمود + 1

يقرأ

ستركز هذه المقالة القصيرة على قواعد البيانات ولا سيما MySQL وأخذ العينات والعد. عند العمل مع قواعد البيانات ، غالبًا ما يكون مطلوبًا حساب عدد COUNT () من الصفوف مع أو بدون شرط معين ، وهذا أمر سهل للغاية مع الاستعلام التالي

عرض كود MYSQL

سيرجع الاستعلام قيمة بعدد الصفوف في الجدول.

العد الشرطي

عرض كود MYSQL

سيرجع الاستعلام قيمة مع عدد الصفوف في الجدول مرضية هذا الشرط: var = 1

للحصول على عدة قيم لعدد الصفوف بشروط مختلفة ، يمكنك تشغيل عدة استعلامات واحدًا تلو الآخر ، على سبيل المثال

عرض كود MYSQL

لكن في بعض الحالات ، لا يكون هذا النهج عمليًا وليس مثاليًا. لذلك ، يصبح من المناسب تنظيم استعلام ، مع عدة استعلامات فرعية ، للحصول على عدة نتائج في استعلام واحد في وقت واحد. على سبيل المثال

عرض كود MYSQL

وبالتالي ، بعد تنفيذ استعلام واحد فقط في قاعدة البيانات ، نحصل على نتيجة بعدد الصفوف لعدة شروط ، تحتوي على عدة قيم تعداد ، على سبيل المثال

مشاهدة التعليمات البرمجية TEXT

c1 | c2 | c3 -------- 1 | 5 | 8

عيب استخدام الاستعلامات الفرعية ، مقارنة بالعديد من الاستعلامات المنفصلة ، هو سرعة التنفيذ والتحميل على قاعدة البيانات.

المثال التالي لاستعلام يحتوي على عدة COUNTs في واحد استعلام MySQL، تم بناؤه بشكل مختلف قليلاً ، فهو يستخدم بنيات IF (الشرط ، القيمة 1 ، القيمة 2) ، بالإضافة إلى التجميع SUM (). إنها تسمح لك بتحديد البيانات وفقًا لمعايير محددة في استعلام واحد ، ثم تلخيصها ، وعرض عدة قيم كنتيجة لذلك.

عرض كود MYSQL

كما ترى من الطلب ، فقد تم بناؤه بإيجاز تام ، لكن سرعة تنفيذه لم تكن سعيدة أيضًا ، وستكون نتيجة هذا الطلب على النحو التالي ،

مشاهدة التعليمات البرمجية TEXT

إجمالي | c1 | c2 | c3 -------------- 14 | 1 | 5 | 8

بعد ذلك ، سأقدم إحصائيات مقارنة لسرعة تنفيذ ثلاثة أنواع من الاستعلامات ، لاختيار عدة COUNT (). لاختبار سرعة تنفيذ الاستعلام ، تم تنفيذ 1000 استعلام من كل نوع ، مع جدول يحتوي على أكثر من ثلاثة آلاف سجل. في هذه الحالة ، في كل مرة احتوى الاستعلام على SQL_NO_CACHE لتعطيل التخزين المؤقت للنتائج بواسطة قاعدة البيانات.

سرعة التنفيذ
ثلاثة طلبات منفصلة: 0.9 ثانية
استعلام واحد مع استعلامات فرعية: 0.95 ثانية
استعلام واحد ببناء IF و SUM: 1.5 ثانية

انتاج. وهكذا ، لدينا العديد من الخيارات لبناء استعلامات لقاعدة البيانات. بيانات MySQLباستخدام COUNT () متعددة ، فإن الخيار الأول مع استعلامات منفصلة ليس ملائمًا للغاية ، ولكنه يحقق أفضل نتيجة من حيث السرعة. الخيار الثاني مع الاستعلامات الفرعية أكثر ملاءمة إلى حد ما ، لكن سرعة تنفيذه أبطأ قليلاً. وأخيرًا ، فإن الإصدار الثالث من الاستعلام الذي يحتوي على تركيبات IF و SUM ، والذي يبدو أنه الأكثر ملاءمة ، لديه أبطأ سرعة تنفيذ ، وهو أقل مرتين تقريبًا من الخيارين الأولين. لذلك ، عند تحسين تشغيل قاعدة البيانات ، أوصي باستخدام الإصدار الثاني من الاستعلام الذي يحتوي على استعلامات فرعية مع COUNT () ، أولاً ، سرعة التنفيذ قريبة من أسرع نتيجة ، وثانيًا ، مثل هذه المؤسسة ضمن استعلام واحد مريحة تمامًا .

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

بدلاً من اسم المؤلف ، تلقينا معرفته. هذا أمر مفهوم ، لأننا أجرينا استعلامًا على جدول واحد - الموضوعات ، ويتم تخزين أسماء مؤلفي الموضوعات في جدول آخر - المستخدمون. لذلك ، بعد أن اكتشفنا معرّف مؤلف الموضوع ، نحتاج إلى إجراء استعلام آخر - إلى جدول المستخدمين لمعرفة اسمه:

يوفر SQL القدرة على دمج مثل هذه الاستعلامات في واحد عن طريق تحويل أحدها إلى استعلام فرعي (استعلام فرعي). لذلك ، لمعرفة من أنشأ موضوع الدراجات ، سنقوم بإجراء الاستعلام التالي:

هذا هو ، بعد الكلمة الرئيسية أين، نكتب طلبًا آخر في الحالة. يعالج MySQL الاستعلام الفرعي أولاً ، ويعيد id_author = 2 ، ويتم تمرير هذه القيمة إلى الجملة أينطلب خارجي.

يمكن أن يكون هناك عدة استعلامات فرعية في استعلام واحد ، ويكون بناء الجملة لهذا الاستعلام كما يلي: لاحظ أن الاستعلامات الفرعية يمكنها تحديد عمود واحد فقط ، حيث ستُرجع قيمه إلى الاستعلام الخارجي. ستؤدي محاولة تحديد عدة أعمدة إلى حدوث خطأ.

دعونا نقدم طلبًا آخر للدمج ، واكتشف الرسائل على المنتدى التي تركها مؤلف موضوع "الدراجات":

الآن دعنا نعقد المهمة ، واكتشف في أي موضوعات ترك مؤلف موضوع "الدراجات" الرسائل:

دعونا نرى كيف يعمل.

• ستنفذ MySQL الاستعلام الأعمق أولاً:


• النتيجة الناتجة (id_author = 2) سيتم تمريرها إلى طلب خارجي ، والذي سيأخذ النموذج:


• النتيجة الناتجة (id_topic: 4،1) سيتم تمريرها إلى طلب خارجي ، والذي سيأخذ النموذج:


• وستعطي النتيجة النهائية (topic_name: about fish، about fish). أولئك. نشر مؤلف موضوع "الدراجات" رسائل في موضوع "حول الصيد" ، الذي أنشأه سيرجي (المعرف = 1) وفي موضوع "حول الصيد" ، الذي أنشأته سفيتا (معرف = 4).

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

• لا يوصى بإنشاء استعلامات بمستوى تداخل يزيد عن ثلاثة. هذا يؤدي إلى زيادة وقت التنفيذ وتعقيد إدراك الكود.
• الصيغة المحددة للاستعلامات المتداخلة هي الأكثر شيوعًا ، ولكنها ليست الوحيدة. على سبيل المثال ، بدلاً من الطلب
  
  لأكتب
  
  أولئك. يمكننا استخدام أي عوامل تستخدم مع كلمة رئيسيةأين (درسناهم في الدرس الأخير).