إنشاء الفهارس المركبة والفريدة وفهارس النص الكامل في MySQLMySQL Creating Composite Unique and Fulltext Indexes

في التطبيقات الفعلية، نادراً ما يقتصر شرط البحث WHERE على عمود واحد؛ بل غالباً ما نفلتر الطلبات حسب القسم والحالة وتاريخ الإنشاء معاً. بدلاً من إنشاء ثلاثة فهارس منفصلة، يتيح لك MySQL 8.4 بناء فهرس مركب متعدد الأعمدة (Composite Index) يجمع حتى 16 عموداً داخل شجرة B+Tree واحدة.

كيف يرتب المحرك المفاتيح داخل الفهرس المركب INDEX (department_id, status, hire_date)؟

• يرتب العقد الورقية أولاً حسب قيمة العمود الأول department_id.
• عندما تتساوى قيمة department_id لعدة موظفين، يرتبهم داخلياً حسب العمود الثاني status.
• وعندما تتساوى الحالة أيضاً، يرتبهم حسب العمود الثالث hire_date، تماماً كما يرتب دليل الهاتف الأسماء حسب اللقب ثم الاسم الأول ثم اسم الأب.

تُعدّ قاعدة البادئة اليسرى (Leftmost Prefix Rule) أهم قاعدة هندسية في فهارس B+Tree المركبة. فإذا أنشأت فهرساً مركباً بالترتيب (A, B, C)، فإن الشجرة مرتبة أولاً حسب A؛ وبالتالي لا يستطيع المحرك استخدام هذا الفهرس للبحث السريع إلا إذا بدأ شرط الاستعلام بالعمود الأيسر الأول A.

ما التوليفات التي تستفيد من الفهرس INDEX (A, B, C)؟

• تستفيد بالكامل: الاستعلامات التي تفلتر على (A) فقط، أو (A, B) معاً، أو (A, B, C) معاً.
• لا تستفيد من البحث الشجري: الاستعلامات التي تبحث عن (B) بمفرده، أو (C) بمفرده، أو (B, C) دون ذكر A!
• توقف البادئة بعد شرط النطاق: إذا كان الشرط WHERE A = 5 AND B > 10 AND C = 3، فإن المحرك يستخدم الفهرس للعمودين A وB، ويتوقف عن استخدام الفهرس للعمود C لأن B > 10 شرط نطاق (Range) وليس مساواة.

كيف تقرر ترتيب الأعمدة عند تصميم فهرس مركب جديد؟ يعتمد المهندسون على مقياس درجة التميّز أو الانتقائية (Index Selectivity)، وتُحسب بقسمة عدد القيم الفريدة COUNT(DISTINCT col) على إجمالي صفوف الجدول COUNT(*). كلما اقتربت النسبة من 1.00 (مثل البريد الإلكتروني أو رقم العميل)، كان العمود عالي الانتقائية ويضيّق نطاق البحث فوراً إلى صفوف قليلة.

القاعدة الذهبية لترتيب الأعمدة داخل الفهرس المركب:

• أولاً: ضع الأعمدة التي تُفلتَر بالمساواة = وتتمتع بأعلى انتقائية (High Selectivity) في بداية الفهرس يساراً.
• ثانياً: ضع الأعمدة التي تُفلتَر بالنطاقات (>, <, BETWEEN) أو الترتيب ORDER BY على يمين أعمدة المساواة.
• الفهارس الفريدة (CREATE UNIQUE INDEX): تضمن عدم تكرار القيم (أو توليفة الأعمدة المركبة) وتتمتع بأعلى انتقائية ممكنة 1.0000.

في قواعد البيانات الكبيرة، قد تشك أن فهرساً قديماً لم يعد مستخدماً وتريد حذفه لتوفير الذاكرة وتسريع الكتابة؛ لكنك تخشى أن يكون هناك تقرير شهري يعتمد عليه، ولو حذفته بـ DROP INDEX فستستغرق إعادة بنائه ساعات طويلة! لحل هذه المعضلة، قدم MySQL 8 ميزة الفهارس غير المرئية (Invisible Indexes).

عندما تحوّل الفهرس إلى INVISIBLE عبر الأمر ALTER TABLE ... ALTER INDEX idx_name INVISIBLE:

• يتجاهل محسّن الاستعلامات (Query Optimizer) هذا الفهرس تماماً وكأنه غير موجود، فتستطيع مراقبة أداء التطبيق بأمان.
• يواصل محرك InnoDB تحديث الفهرس في الخلفية عند كل INSERT أو UPDATE.
• إذا لاحظت بطئاً في أحد الاستعلامات، يمكنك إعادته للعمل في جزء من الألف من الثانية بالأمر ALTER INDEX idx_name VISIBLE دون إعادة بناء الشجرة!

تصلح فهارس B+Tree للبحث عن نص مطابق تماماً أو نص يبدأ ببادئة محددة، لكنها تعجز عن تسريع البحث عن كلمة في وسط مقال طويل. لهذا الغرض يوفر محرك InnoDB فهرس النص الكامل (FULLTEXT INDEX)، والذي يعتمد على الفهرس المعكوس (Inverted Index): حيث يقسم النص إلى كلمات فردية (Tokens) ويحفظ أمام كل كلمة قائمة بأرقام الصفوف التي وردت فيها.

للبحث داخل فهرس النص الكامل نستخدم الصيغة MATCH (col1, col2) AGAINST ('search terms' [MODE]) في وضعين رئيسيين:

• IN NATURAL LANGUAGE MODE (الافتراضي): يبحث عن الكلمات المفتاحية ويحسب درجة الصلة الإحصائية (Relevance Score) لكل صف.
• IN BOOLEAN MODE: يدعم معاملات البحث المتقدمة مثل إلزام وجود الكلمة +mysql، واستبعاد كلمة -legacy، والبحث ببادئة الكلمة optim*.

يلخّص الجدول المرجعي التالي مقارنة أنواع الفهارس في MySQL 8.4، وصيغة إنشائها، وحالات الاستخدام المثلى لكل نوع: