في هذا الدرس 6تصفّح المواضيع
عندما تنفّذ استعلام SELECT يحتوي على شرط تصفية WHERE فوق عمود غير مفهرس (Unindexed Column)، يضطر محرك InnoDB في MySQL 8.4 إلى إجراء فحص كامل للجدول (Full Table Scan). في هذا المسار، يقرأ المحرك جميع صفحات البيانات الفيزيائية (وحجم كل صفحة افتراضياً 16 KB) بالتتابع من أول صف حتى آخر صف للتحقق من تطابق الشرط.
لماذا يمثّل الفحص الكامل عنق زجاجة خطيراً في قواعد البيانات الإنتاجية؟
- نمو الزمن خطياً
O(N): إذا كان البحث في1,000صف يستغرق ملي ثانية واحدة، فإن البحث في10,000,000صف سيستغرق عدة ثوانٍ لكل طلب مستخدم. - إغراق ذاكرة Buffer Pool: قراءة آلاف الصفحات من القرص الصلب تطرد الصفحات الساخنة النشطة من ذاكرة التخزين المؤقت في InnoDB.
- استهلاك المعالج والإدخال/الإخراج (Disk I/O): فحص ملايين الصفوف لاسترجاع صف واحد فقط يهدر دورات المعالج ويبطئ الاستعلامات المتزامنة الأخرى.
SELECT customer_id, full_name, city FROM customers WHERE phone = '0501234567';عندما تنشئ فهرساً من النوع الافتراضي USING BTREE في MySQL، يبني محرك InnoDB فعلياً بنية بيانات تُعرف هندسياً باسم شجرة البحث المتوازنة (B+Tree). تحافظ هذه الشجرة على توازنها تلقائياً بحيث تكون المسافة من العقدة الجذرية (Root Node) إلى أي عقدة ورقية (Leaf Page) متساوية دائماً.
تتكوّن شجرة B+Tree في InnoDB من ثلاث طبقات مترابطة:
- العقدة الجذرية والعقد الفرعية (Root & Branch Nodes): تحتوي على قيم مفاتيح مرتبة ومؤشرات توجّه المحرك نحو الصفحة الفرعية الصحيحة (أكبر من أو أصغر من).
- العقد الورقية (Leaf Pages): تقع في المستوى الأدنى وتحتوي على القيم المفهرسة مرتبةً ترتيباً تصاعدياً دقيقاً.
- روابط ثنائية الاتجاه (Doubly-Linked List): ترتبط كل صفحة ورقية بالصفحة التي تسبقها والتي تليها، مما يجعل البحث في النطاقات مثل
BETWEEN 100 AND 500أو الترتيبORDER BYسريعاً للغاية دون الحاجة للعودة إلى الجذر.
SELECT * FROM customers WHERE customer_id BETWEEN 101 AND 104;محاكي شجرة B+Tree والفحص الكامل
يمتاز محرك InnoDB بهندسة فريدة تسمى الجدول المنظّم بالفهرس (Index-Organized Table)؛ فالمفتاح الأساسي PRIMARY KEY ليس مجرد فهرس منفصل يشير إلى الجدول، بل هو الفهرس العنقودي (Clustered Index) الذي يمثّل الجدول الفيزيائي نفسه! بمعنى أن العقد الورقية (Leaf Pages) لشجرة المفتاح الأساسي تحتوي بداخلها على كامل أعمدة الصفوف مرتبةً فيزيائياً على القرص حسب قيمة المفتاح الأساسي.
كيف يختار InnoDB الفهرس العنقودي لكل جدول؟
- الحالة 1 (وجود
PRIMARY KEY): يعتمد InnoDB المفتاح الأساسي الصريح كفهرس عنقودي للجدول. - الحالة 2 (غياب المفتاح الأساسي مع وجود مفتاح
UNIQUE NOT NULL): يرقّي المحرك أول فهرس فريد لا يقبل القيم الفارغة ليعمل كفهرس عنقودي. - الحالة 3 (غياب الاثنين معاً): ينشئ InnoDB داخلياً عموداً مخفياً بحجم
6 BytesباسمDB_ROW_IDليبني عليه الفهرس العنقودي، ولذلك يُنصح دائماً بتعريف مفتاح أساسي رقمي متزايدAUTO_INCREMENTلكل جدول.
SELECT * FROM shipments WHERE shipment_id = 5001;يُسمى أي فهرس إضافي تنشئه على أعمدة البحث (مثل البريد الإلكتروني أو رقم الهاتف أو المدينة) فهرساً ثانوياً (Secondary Index). ولكن ماذا يخزن محرك InnoDB داخل العقد الورقية للفهرس الثانوي؟
بدلاً من تخزين عنوان فيزيائي خام على القرص قد يتغير عند تحرك الصفحات، يخزن الفهرس الثانوي في أوراقه شيئين فقط: قيمة العمود المفهرس + قيمة المفتاح الأساسي PRIMARY KEY لذلك الصف. وعندما تطلب أعمدة غير موجودة في الفهرس الثانوي، ينفذ المحرك خطوتين:
- الخطوة 1: يبحث في شجرة الفهرس الثانوي لاستخراج قيمة المفتاح الأساسي
PRIMARY KEYالمطابقة. - الخطوة 2 (البحث المرجعي Bookmark Lookup): يستخدم قيمة المفتاح الأساسي للبحث في شجرة الفهرس العنقودي واسترجاع بقية أعمدة الصف.
CREATE INDEX idx_shipments_city ON shipments (destination_city);يوفر MySQL 8.4 أوامر قياسية لإضافة الفهارس الثانوية إلى الجداول القائمة، وفحص إحصائياتها، وإزالتها عند انتهاء الحاجة إليها:
- الإنشاء المباشر:
CREATE INDEX idx_name ON table_name (col_name);أو عبر تعديل الجدولALTER TABLE table_name ADD INDEX idx_name (col_name);. - الفحص التفصيلي:
SHOW INDEX FROM table_name;ويعرض اسم الفهرسKey_name، وترتيب العمودSeq_in_index، والتعددية التقديريةCardinality، ونوع الشجرةIndex_type(BTREE). - الحذف الآمن:
DROP INDEX idx_name ON table_name;(لاحظ أن MySQL يشترط تحديد اسم الجدول بعدONلأن أسماء الفهارس محلية لكل جدول).
SHOW INDEX FROM shipments; DROP INDEX idx_shipments_city ON shipments;لماذا لا نضع فهرساً على كل عمود في الجدول؟ لأن كل فهرس ثانوي تضيفه هو شجرة B+Tree حقيقية يجب على محرك InnoDB تحديثها وإعادة موازنتها في كل مرة ينفّذ فيها عملية كتابة (INSERT أو UPDATE أو DELETE). فإذا كان لديك جدول يضم 12 فهرساً ثانوياً، فإن إدراج صف واحد في الجدول يتطلب إدراج 13 عقدة في 13 شجرة مختلفة!
يوضّح الجدول المرجعي التالي المقارنة الشاملة بين مسارات البحث وأنواع الفهارس الأساسية في MySQL 8.4:
| طريقة الوصول / الفهرس | التعقيد الزمني | محتوى العقدة الورقية (Leaf) | تأثير القراءة والكتابة |
|---|---|---|---|
| الفحص الكامل (Full Table Scan) | O(N) خطي | قراءة كافة صفحات الجدول بالتتابع | بطيء جداً في القراءة؛ لا يضيف عبء كتابة |
المفتاح العنقودي (PRIMARY KEY) | O(log N) لوغاريتمي | كامل أعمدة الصف الفيزيائي | أسرع قراءة مباشرة؛ فهرس واحد لكل جدول |
الفهرس الثانوي (Secondary BTREE) | O(log N) + Bookmark | قيمة العمود المفهرس + قيمة PRIMARY KEY | يسرّع القراءة والفرز؛ يزيد كلفة INSERT/UPDATE/DELETE |
| فحص الفهارس الحالية | SHOW INDEX FROM tbl | يعرض Key_name وCardinality | أداة تشخيصية لمراجعة الفهارس الزائدة |
| حذف فهرس غير مستخدم | DROP INDEX idx ON tbl | يزيل شجرة الفهرس الثانوي من القرص | يحرر مساحة القرص ويسرّع عمليات الكتابة |
ملخص الدرس
حصاد الدرسمراجعة مركّزة لأهم المفاهيم والقواعد الهندسية التي أتقنتها في هذا الدرس قبل التطبيق العملي
ماذا تعلّمت في هذا الدرس؟
فهمت في هذا الدرس تكلفة الفحص الكامل للجدول O(N)، وكيف تعمل شجرة البحث المتوازنة B+Tree بتعقيد O(log N)، والفرق الجوهري بين الفهرس العنقودي PRIMARY KEY والفهارس الثانوية التي تخزن مؤشرات للمفتاح الأساسي، وكيفية إنشاء وفحص وحذف الفهارس وموازنة سرعة القراءة مع كلفة الكتابة.
الخطوة التالية والدرس القادم
في الدرس القادم سنتعلم كيفية تصميم الفهارس المركبة متعددة الأعمدة (Composite Indexes)، وقاعدة البادئة اليسرى، والفهارس غير المرئية، وفهارس النص الكامل.
سجّل الدخول لحفظ تقدمك
تابع إنجاز المواضيع والمصطلحات والمهام، وكمّل من آخر خطوة على أي جهاز.
المصطلحات4
راجع المعنى، اشرحه لنفسك، ثم علّم البطاقة كمفهومة.
- 01 Clustered Indexes بنية الفهارس
- تعلّم فهارس شجرة B Tree والمفتاح العنقودي والفحص الكامل في MySQL بأسلوب MySQL 8 الحديث مع أمثلة استعلامات SQL وتطبيقات تفاعلية.
- للمراجعة
- 02 Cost of Full Table Scans تكلفة الفحص الكامل للجدول
- المفهوم الهندسي الأساسي وقواعد التنفيذ العملي لقسم تكلفة الفحص الكامل للجدول في قواعد بيانات MySQL 8.4 الحديثة.
- للمراجعة
- 03 Balanced B Tree Index Structure كيف تعمل شجرة البحث B+Tree
- المفهوم الهندسي الأساسي وقواعد التنفيذ العملي لقسم كيف تعمل شجرة البحث B+Tree في قواعد بيانات MySQL 8.4 الحديثة.
- للمراجعة
- 04 InnoDB Clustered Primary Key Index المفتاح الأساسي والفهرس العنقودي
- المفهوم الهندسي الأساسي وقواعد التنفيذ العملي لقسم المفتاح الأساسي والفهرس العنقودي في قواعد بيانات MySQL 8.4 الحديثة.
- للمراجعة
لا توجد بطاقات تطابق البحث والتصفية.
اقرأ البطاقات بحرية. سجّل الدخول لحفظ تقدم مراجعتك.
الاختصارات2
راجع المعنى، اشرحه لنفسك، ثم علّم البطاقة كمفهومة.
- 01 B+Tree B+Tree
- Balanced Plus Tree Index Structure
- شجرة البحث المتوازنة المعتمدة في محرك InnoDB لتخزين الفهارس العنقودية والثانوية.
- للمراجعة
- 02 I/O I/O
- Input / Output Disk Operations
- عمليات قراءة وكتابة صفحات البيانات (16 KB) بين القرص الصلب وذاكرة Buffer Pool.
- للمراجعة
لا توجد بطاقات تطابق البحث والتصفية.
اقرأ البطاقات بحرية. سجّل الدخول لحفظ تقدم مراجعتك.
مهامك قبل الاختبار
خطوة صغيرة كل مرة. وإنجاز تقدر ترجع له.
-
افتح عميل MySQL 8.4 المحلي ونفّذ استعلام SQL المشروح في قسم تكلفة الفحص الكامل للجدول للتحقق من النتائج.
-
بدّل بين السيناريوهات الثلاثة في المحاكي التفاعلي ضمن قسم كيف تعمل شجرة البحث B+Tree وقارن مخرجات MySQL 8.4.
تأشير المهمة إقرار منك بإتمامها، وليس تصحيحًا آليًا للكود. يُحفظ تقدمك في حسابك.
اختبر فهمك.
ووصلها للإتقان.
كل سؤال خطوة لقدّام. اختار، اكتب كود، وشوف نتيجتك فورًا.
أكمل المهام، واجمع كل الإجابات الصحيحة. أقل من 100%؟ راجع وأعد المحاولة.
الأسئلة متاحة للاطلاع. سجّل الدخول للتفاعل وحفظ تقدمك.
لمحة عن التحدي4 أسئلة تنتظرك ＋
ماذا يخزن محرك InnoDB داخل العقد الورقية (Leaf Pages) للفهرس العنقودي PRIMARY KEY؟
اختر الإجابة الأدق وفق قواعد محرك MySQL 8.4.
بماذا يحتفظ الفهرس الثانوي (Secondary Index) في عقده الورقية للوصول إلى بقية أعمدة الصف في InnoDB؟
اختر الإجابة الأدق وفق قواعد محرك MySQL 8.4.
لماذا يؤدي الإكثار المفرط من الفهارس الثانوية إلى إبطاء أوامر INSERT وUPDATE وDELETE؟
اختر الإجابة الأدق وفق قواعد محرك MySQL 8.4.
أي أمر SQL يحذف الفهرس الثانوي idx_customers_phone من جدول customers في MySQL؟
اختر الإجابة الأدق وفق قواعد محرك MySQL 8.4.