نوع و ميزات وسلبيات فهرسة MySQL

النصالفهرس
هو ملف خاص (الفهارس على جداول البيانات InnoDB هي جزء من المساحة التخزينية)، ويحتوي على إشارات إلى جميع السجلات في الجدول.
ملاحظة:
[1] الفهرس ليس مطلقًا! يمكن للفهرس زيادة سرعة عمليات البحث في البيانات، ولكن يمكن أن يبطئ عمليات تعديل البيانات. يجب تحديث الفهرس مرة واحدة لكل سجل يتم تعديله. من أجل تعويض هذا العيب، يحتوي العديد من أوامر SQL على خيار DELAY_KEY_WRITE. يهدف هذا الخيار إلى منع MySQL من تحديث الفهرس فور إدراج سجل جديد أو تعديل سجل موجود، بل سيتم تحديث الفهرس بعد إدراج أو تعديل جميع السجلات. سيكون دور هذا الخيار واضحًا بشكل كبير عند الحاجة إلى إدراج العديد من السجلات في جدول البيانات.
[2] بالإضافة إلى ذلك، سيأخذ الفهرس مساحة كبيرة جدًا على القرص الصلب. لذلك، يجب إنشاء فهرس فقط للعمود الذي يتم استعلامه أو ترتيبه بشكل متكرر. انتبه، إذا كان يحتوي عمود ما على الكثير من المعلومات المتكررة، فإن إنشاء فهرس له لن يكون له تأثير كبير.

من الناحية النظرية، يمكن إنشاء فهرس لكل حقل في جدول البيانات، ولكن يحد MySQL عدد الفهارس في نفس الجدول إلى 16.

1. فهرس جداول البيانات InnoDB
من حيث الفعالية، يحدث قفل سطر البيانات في InnoDB لجدول البيانات فعليًا على الفهرس وليس على جدول البيانات نفسه. من الواضح أن ميكانيكية قفل سطر البيانات تعمل فقط عندما يكون هناك فهرس مناسب للقفل في جدول البيانات المعني. يهم الفهرس بشكل كبير جدًا في InnoDB مقارنة بـ MyISAM. على جداول البيانات InnoDB، يهم الفهرس بشكل كبير جدًا في InnoDB. يلعب الفهرس دورًا في البحث عن سجلات البيانات فقط، وهو أيضًا أساس ميكانيكية قفل سطر البيانات. "قفل سطر البيانات" يعني قفل السجلات الفردية التي يتم معالجتها في عملية التبادل، مما يمنع الوصول إليها من قبل المستخدمين الآخرين. سيؤثر هذا القفل (وليس محصورًا به) على أوامر SELECT…LOCK IN SHARE MODE و SELECT…FOR UPDATE وكذلك أوامر INSERT و UPDATE و DELETE.

2. الحد

إذا كانت جملة WHERE تحتوي على معادلة غير متساوية (WHERE coloum != ...)، لن يستطيع MySQL استخدام الفهرس.
بشكل مشابه، إذا تم استخدام وظائف في شرط WHERE (WHERE DAY(column) = …)، فإن MySQL لن يستطيع استخدام الفهرس.
في عملية JOIN (عند الحاجة إلى استخراج بيانات من عدة جداول)، يستطيع MySQL استخدام الفهرس فقط إذا كانت أنواع البيانات للمفتاح الرئيسي والمفتاح الخارجي متطابقة.
إذا تم استخدام عمليات مقارنة LIKE وREGEXP في شرط WHERE، يستطيع MySQL استخدام الفهرس فقط إذا لم يكن الرمز المخصص في أول حرف من نموذج البحث. على سبيل المثال، إذا كان شرط البحث LIKE 'abc%'، فإن MySQL سيستخدم الفهرس؛ إذا كان شرط البحث LIKE '%abc'، فإن MySQL لن يستخدم الفهرس.
في عملية ORDER BY، يستخدم MySQL الفهرس فقط عندما يكون شرط الترتيب ليس تعبيرًا للبحث (WHERE). (على الرغم من ذلك، في عمليات البحث التي تتضمن عدة جداول، حتى إذا كانت الفهارس متاحة، فإن هذه الفهارس لن تكون فعالة في تسريع عملية ORDER BY)
إذا كانت هناك قيم مكررة بكثرة في某一 سلسلة بيانات، حتى إذا تم إنشاء فهرس لها فإن الفعالية لن تكون جيدة. على سبيل المثال، إذا كانت السلسلة تحتوي فقط على قيم مثل "0/1" أو "Y/N"، فإن إنشاء فهرس لها لن يكون ضروريًا.　

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

1. المفتاح العادي

المفتاح العادي (محدد بالكلمة المفتاحية KEY أو INDEX) مهمته الأساسية هي تسريع الوصول إلى البيانات. لذلك، يجب إنشاء فهرس فقط للسجلات التي تظهر بشكل متكرر في شروط البحث (WHERE column = …) أو في شروط الترتيب (ORDER BY column). يجب، عند ممكن، اختيار سلسلة بيانات منظمة ومتماسكة بشكل كبير (مثل سلسلة بيانات نوع عدد) لإنشاء الفهرس.

2. الفهرس الفريد
يسمح الفهرس العادي بأن يحتوي المفتاح البياني للسلسلة المفهرسة على قيم مكررة. على سبيل المثال، لأن هناك إمكانية أن يكون لدى شخص اسم مشابه، قد يظهر نفس الاسم مرتين أو أكثر في جدول بيانات الملف الشخصي للعامل.
إذا كان يمكن تحديد أن某一数据列将只包含彼此各不相同的值، عند إنشاء هذا المفتاح لذاكرة البيانات يجب استخدام الكلمة المفتاحية UNIQUE لتعريفه كفهرس فريد. مزايا هذا الأسلوب: الأولى هي تبسيط إدارة MySQL لهذا الفهرس، وهو يصبح أكثر فعالية؛ الثانية هي أن MySQL سينفذ فحص تلقائي لقيمة هذا الحقل في السجلات عند إدراج سجلات جديدة في الجدول، إذا كانت القيمة موجودة بالفعل في هذا الحقل في سجلات أخرى، سيرفض MySQL إدراج السجل الجديد. بمعنى آخر، يمكن للفهرس الفريد ضمان استقلالية السجلات. في الواقع، في العديد من الحالات، تكون أهداف إنشاء الفهرس الفريد ليست لتحسين سرعة الوصول، بل لتجنب تكرار البيانات.

3. الفهرس الرئيسي

لقد تم التأكيد مرارًا وتكرارًا من قبل: يجب إنشاء فهرس للمفتاح الرئيسي، وهو ما يُعرف بـ”الفهرس الرئيسي“. الفرق الوحيد بين الفهرس الرئيسي والفهرس الفريد هو أن الكلمة المفتاحية المستخدمة في التعريف الأولى هي PRIMARY وليس UNIQUE.

4. فهرس المفتاح الخارجي

إذا تم تعريف شرط مفتاح خارجي على عمود مفتاح خارجي، سيقوم MySQL بتعريف فهرس داخلي لمساعدته على إدارة استخدام شرط المفتاح الخارجي بشكل فعال.

5. الفهرس المركب

يمكن للفهرس أن يغطي عدة جداول بيانات، مثل فهرس INDEX(columnA, columnB).特点是 MySQL يمكن أن يستخدم مثل هذا الفهرس بشكل اختياري. إذا كانت عملية البحث تحتاج فقط إلى استخدام فهرس على عمود columnA، يمكن استخدام فهرس INDEX(columnA, columnB). ومع ذلك، يُستخدم هذا الاستخدام فقط للجمعيات من العمود الموجود في مقدمة الفهرس المركب. على سبيل المثال، يمكن استخدام INDEX(A, B, C) كفهرس لـ A أو (A, B)، ولكن لا يمكن استخدامه كفهرس لـ B أو C أو (B, C).

6. طول الفهرس

عند تعريف فهرس لجداول البيانات من نوع CHAR وVARCHAR، يمكن وضع حد لطول الفهرس إلى عدد محدد من الحروف (يجب أن يكون هذا الرقم أقل من عدد الحروف التي يسمح بها هذا الحقل). الفائدة من هذا هي إنشاء ملف فهرس صغير الحجم ولكن بسرعة بحث عالية. في معظم التطبيقات، تكون البيانات النصية في قاعدة البيانات معظمها أسماء مختلفة، ويكون من الممكن ضبط طول الفهرس بين 10 إلى 15 حرفًا لضيق نطاق البحث إلى قليل من السجلات.
عند إنشاء فهرس لجداول البيانات من نوع BLOB وTEXT، يجب وضع حد لطول الفهرس؛ أكبر طول للفهرس المسموح به في MySQL هو 255 حرف.
الفهرس الكامل

　　 الفهرس العادي على حقل النص يمكنه فقط تسريع عمليات البحث على الأحرف التي تظهر في بداية محتوى الحقل (أي الأحرف الموجودة في بداية محتوى الحقل). إذا كان يحتوي الحقل على نص طويل يتكون من عدة كلمات، فإن الفهرس العادي لا يكون له تأثير. هذا النوع من البحث يظهر غالبًا في شكل LIKE %word%， مما يكون معقدًا جدًا بالنسبة لـ MySQL، وسيؤدي إلى زيادة وقت الاستجابة إذا كان حجم البيانات التي يجب معالجتها كبيرًا.
هذه المواقف هي حيث يمكن للفهرس الكامل (full-text index) أن يظهر مهارته. عند إنشاء هذا النوع من الفهارس، سيقوم MySQL بإنشاء قائمة بكافة الكلمات التي تظهر في النص، وسيعتمد عمليات البحث على هذه القائمة للبحث عن السجلات ذات الصلة. يمكن إنشاء الفهرس الكامل مع جدول البيانات أو استخدامها في وقت لاحق عند الحاجة.

سيتم إضافة الأمر التالي:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

مع المؤشرات الكاملة النصية، يمكن استخدام أمر SELECT للبحث عن السجلات التي تحتوي على كلمات معينة. إليك لغة الأمر الأساسية لهذا النوع من الاستعلامات:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')

سيقوم هذا الأمر بالبحث عن جميع السجلات التي تحتوي على كلمات word1، word2 وword3 في الحقول column1 وcolumn2.

ملاحظةلا يدعم جدول InnoDB المؤشرات الكاملة النصية.

تحسين الاستعلامات والمؤشرات

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

　　　　　 عند عدم التأكد من أي من الأعمدة التي يجب إنشاء المؤشر عليها، يمكن أن يساعد أمر EXPLAIN SELECT في الحصول على بعض المساعدة. في الواقع، يكاد يكون الأمر مجرد إضافة كلمة EXPLAIN كجزء من أمر SELECT العادي. مع هذا الكلمة، لن تنفذ MySQL هذا الأمر، بل ستجري تحليلًا له.
في نتيجة إصدار أمر EXPLAIN، يقع العمود الأول على اسم الجدول الذي تم استخدامه من قاعدة البيانات، ويتم ترتيبهم وفقًا للترتيب الذي تم فيه القراءة. يحدد عمود type العلاقة بين هذا الجدول وبين الجداول الأخرى (JOIN). في أنواع العلاقات المختلفة، يتميز system بالكفاءة العالية، ثم يليه const، eq_ref، ref، range، index و All (يعني All: يجب قراءة جميع سجلات الجدول هذا لكل سجل في الجدول المستمر في المستوى الأعلى - يمكن تجنب هذا الحالة باستخدام ملفات المؤشرات).

يعطي عمود possible_keys جميع المؤشرات التي يمكن استخدامها لبحث سجلات البيانات في MySQL. يعطي عمود key المؤشر الذي يستخدمه MySQL فعليًا، ويُعطى طول هذا المؤشر بالبites في عمود key_len. على سبيل المثال، إذا كان هناك مؤشر على عمود INTEGER، فإن طول هذا المؤشر سيكون 4. إذا تم استخدام مؤشر مركب، يمكنك رؤية الأجزاء المحددة التي يستخدمها MySQL في عمود key_len. كقاعدة عامة، كلما كان قيمة عمود key_len أقل، كان ذلك أفضل (يعني أسرع).
يعطي عمود ref اسم عمود في جدول البيانات الآخر في العلاقة. يعطي عمود row عدد الصفوف التي يتوقع MySQL أن يقرأها من هذا الجدول أثناء تنفيذ الاستعلام. يمكن أن تساعدنا جميع الأرقام في عمود row في فهم عدد المجموعات التي يجب معالجتها في هذه الاستعلام.
في النهاية، يوفر عمود extra معلومات إضافية تتعلق بعمليات JOIN، مثل، إذا كان MySQL يجب أن يخلق جدول بيانات مؤقت أثناء تنفيذ هذه الاستعلام، فستشاهد كلمة using temporary في عمود extra.

هذا هو نهاية محتوى هذا المقال، نأمل أن يكون قد ساعد في تعلمكم، ونأمل أن تدعموا دليل التعليمات.

البيان: محتوى هذا المقال تم جمعه من الإنترنت، ويتمتع ملكية حقوق الطبع والنشر للمالك الأصلي، يتم جمع المحتوى من قبل المستخدمين عبر الإنترنت بشكل متعاوني وتم تحميله بشكل مستقل، ويستخدم هذا الموقع فقط لعرض المحتوى ولا يمتلك حقوق الملكية، ويتمتع هذا الموقع بعدم تحرير المحتوى بشكل يدوي ولا يتحمل أي مسؤولية قانونية. إذا كنت قد وجدت محتوى يشتبه في انتهاك حقوق الطبع والنشر، فنرجو منك إرسال بريد إلكتروني إلى: notice#oldtoolbag.com (عند إرسال البريد الإلكتروني، يرجى استبدال '#' بـ '@') لإبلاغنا، وتقديم الدليل، وسيتم حذف المحتوى المزعوم بسرعة إذا تم التحقق من صحة الشكوى.