MySQL وتصميم قواعد البيانات

إنشاء وإدارة الفهارس

13 دقيقة الدرس 15 من 40

إنشاء وإدارة الفهارس

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

صيغة CREATE INDEX

هناك طرق متعددة لإنشاء الفهارس في MySQL. دعنا نستكشف كل طريقة:

الطريقة 1: جملة CREATE INDEX

-- فهرس عمود واحد
CREATE INDEX idx_email ON users(email);

-- فهرس متعدد الأعمدة (مركب)
CREATE INDEX idx_name ON users(first_name, last_name);

-- فهرس فريد
CREATE UNIQUE INDEX idx_username ON users(username);

الطريقة 2: جملة ALTER TABLE

-- إضافة فهرس واحد
ALTER TABLE users ADD INDEX idx_country (country);

-- إضافة فهرس مركب
ALTER TABLE users ADD INDEX idx_location (country, city, zip_code);

-- إضافة فهرس فريد
ALTER TABLE users ADD UNIQUE KEY idx_email (email);

-- إضافة فهرس نصي كامل
ALTER TABLE articles ADD FULLTEXT idx_content (title, content);

الطريقة 3: CREATE TABLE مع تعريف الفهرس

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(255),
    country VARCHAR(50),
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- تعريف الفهارس أثناء إنشاء الجدول
    INDEX idx_email (email),
    INDEX idx_location (country, city),
    UNIQUE KEY idx_username (username)
);

أفضل ممارسة: استخدم CREATE INDEX للجداول الموجودة وقم بتضمين تعريفات الفهرس في CREATE TABLE للجداول الجديدة. هذا يجعل هيكل الجدول موثقاً ذاتياً.

اتفاقيات تسمية الفهارس

اتباع اتفاقيات تسمية متسقة يجعل إدارة الفهارس أسهل:

أنماط التسمية الموصى بها:

-- فهارس العمود الواحد
idx_{column_name}
مثال: idx_email, idx_country

-- الفهارس المركبة
idx_{column1}_{column2}
مثال: idx_country_city, idx_category_published

-- الفهارس الفريدة
uk_{column_name} أو uniq_{column_name}
مثال: uk_email, uniq_username

-- فهارس النص الكامل
ft_{column_name} أو fulltext_{column_name}
مثال: ft_content, fulltext_title_body

-- فهارس المفاتيح الأجنبية
fk_{table}_{column}
مثال: fk_orders_user_id

لماذا التسمية مهمة: الأسماء الجيدة تساعدك على فهم ما يفعله كل فهرس دون النظر إلى هيكل الجدول. هذا مهم بشكل خاص في قواعد البيانات الكبيرة التي تحتوي على مئات الفهارس.

ترتيب أعمدة الفهرس المركب

ترتيب الأعمدة في الفهرس المركب حاسم للأداء. ضع في اعتبارك هذه الإرشادات:

-- السيناريو: لديك هذه الاستعلامات الشائعة
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC;

-- جيد: customer_id أولاً (الأكثر انتقائية وتستخدم في جميع الاستعلامات)
CREATE INDEX idx_customer_status_date
ON orders(customer_id, status, created_at);

-- يمكن استخدام هذا الفهرس لـ:
✓ WHERE customer_id = 123
✓ WHERE customer_id = 123 AND status = 'pending'
✓ WHERE customer_id = 123 AND status = 'pending' ORDER BY created_at
✓ WHERE customer_id = 123 ORDER BY created_at (استخدام جزئي)

-- سيء: ترتيب أعمدة خاطئ
CREATE INDEX idx_bad ON orders(status, created_at, customer_id);
-- لا يمكن استخدام هذا بكفاءة لـ WHERE customer_id = 123

قواعد ترتيب الأعمدة:

1. المساواة أولاً: الأعمدة المستخدمة مع = أو IN
2. النطاق ثانياً: الأعمدة المستخدمة مع < أو > أو BETWEEN
3. الترتيب أخيراً: الأعمدة المستخدمة في ORDER BY

مثال:
WHERE country = 'USA'      -- مساواة (أولاً)
  AND age > 18            -- نطاق (ثانياً)
ORDER BY created_at DESC   -- ترتيب (أخيراً)

الفهرس الأمثل:
CREATE INDEX idx_optimal ON users(country, age, created_at);

الفهارس المغطية

الفهرس المغطي يحتوي على جميع الأعمدة التي يحتاجها الاستعلام، مما يلغي الحاجة إلى الوصول إلى بيانات الجدول:

-- استعلام يحتاج إلى id و email و country
SELECT id, email, country FROM users WHERE email = 'john@example.com';

-- فهرس عادي على email
CREATE INDEX idx_email ON users(email);
-- MySQL: البحث عن الصف في الفهرس → الوصول إلى الجدول للحصول على id و country

-- فهرس مغطي يتضمن جميع الأعمدة المطلوبة
CREATE INDEX idx_email_covering ON users(email, id, country);
-- MySQL: البحث عن الصف في الفهرس → جميع البيانات في الفهرس (لا حاجة للوصول إلى الجدول!)

النتيجة: استعلام أسرع (لا حاجة للبحث في الجدول)

تحقق مما إذا كان استعلامك يستخدم فهرساً مغطياً باستخدام EXPLAIN:

EXPLAIN SELECT id, email, country
FROM users
WHERE email = 'john@example.com';

+----+------+-------------------+-------+-------------+
| id | type | key               | Extra                |
+----+------+-------------------+-------+-------------+
| 1  | ref  | idx_email_covering| Using index          |
+----+------+-------------------+-------+-------------+

"Using index" = يتم استخدام الفهرس المغطي! ✓

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

الفهارس غير المرئية (MySQL 8.0+)

قدمت MySQL 8.0 الفهارس غير المرئية - فهارس موجودة ولكن لا يستخدمها محسّن الاستعلام:

-- إنشاء فهرس غير مرئي
CREATE INDEX idx_email ON users(email) INVISIBLE;

-- جعل فهرس موجود غير مرئي
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

-- جعل الفهرس غير المرئي مرئياً مرة أخرى
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

لماذا نستخدم الفهارس غير المرئية؟

حالة الاستخدام 1: اختبار إزالة الفهرس
-- قبل حذف فهرس، اجعله غير مرئي لاختبار التأثير
ALTER TABLE users ALTER INDEX idx_country INVISIBLE;
-- راقب أداء التطبيق لمدة 24 ساعة
-- إذا لم تكن هناك مشاكل، احذف الفهرس بأمان
DROP INDEX idx_country ON users;

حالة الاستخدام 2: استخدام الفهرس الشرطي
-- إنشاء فهرس للاستخدام المستقبلي ولكن لا تستخدمه بعد
CREATE INDEX idx_future ON users(new_column) INVISIBLE;
-- تفعيل عند الحاجة
ALTER TABLE users ALTER INDEX idx_future VISIBLE;

حذف الفهارس

قم بإزالة الفهارس التي لم تعد مطلوبة لتقليل العبء:

-- الطريقة 1: DROP INDEX
DROP INDEX idx_country ON users;

-- الطريقة 2: ALTER TABLE
ALTER TABLE users DROP INDEX idx_country;

-- حذف عدة فهارس دفعة واحدة
ALTER TABLE users
    DROP INDEX idx_country,
    DROP INDEX idx_city,
    DROP INDEX idx_zip;

تحذير: لا يمكنك حذف PRIMARY KEY باستخدام DROP INDEX. استخدم ALTER TABLE لحذف وإعادة إنشاء المفاتيح الأساسية:

ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (id);

تعديل الفهارس الموجودة

MySQL لا تدعم تعديل الفهرس المباشر. يجب عليك الحذف وإعادة الإنشاء:

-- تريد تغيير idx_name من (first_name) إلى (first_name, last_name)

-- الخطوة 1: حذف الفهرس القديم
DROP INDEX idx_name ON users;

-- الخطوة 2: إنشاء فهرس جديد
CREATE INDEX idx_name ON users(first_name, last_name);

-- أو قم بكليهما في جملة واحدة
ALTER TABLE users
    DROP INDEX idx_name,
    ADD INDEX idx_name (first_name, last_name);

بادئة طول الفهرس

لأعمدة VARCHAR و TEXT، يمكنك فهرسة الأحرف الأولى N فقط لتوفير المساحة:

-- فهرسة أول 10 أحرف من email
CREATE INDEX idx_email ON users(email(10));

-- مفيد لأعمدة TEXT الطويلة
CREATE INDEX idx_description ON products(description(50));

-- فهرس مركب مع بادئة
CREATE INDEX idx_name ON users(first_name(5), last_name(10));

متى نستخدم فهارس البادئة:

✓ استخدم البادئة عندما:
- العمود طويل جداً (TEXT، BLOB)
- الأحرف الأولى N فريدة بما يكفي
- حجم الفهرس مصدر قلق

✗ تجنب البادئة عندما:
- تحتاج إلى مطابقة دقيقة
- تستخدم ORDER BY على العمود (لا يمكن استخدام فهارس البادئة للترتيب)
- العمود قصير بالفعل (VARCHAR(50) أو أقل)

سيناريو إدارة الفهارس في العالم الحقيقي

دعنا نحسّن جدول طلبات التجارة الإلكترونية الحقيقي:

-- الجدول الأصلي (بدون فهارس باستثناء PRIMARY KEY)
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- الاستعلامات الشائعة:
-- س1: العثور على طلبات العميل
SELECT * FROM orders WHERE customer_id = 123;

-- س2: العثور على الطلبات المعلقة
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

-- س3: العثور على طلبات العميل الأخيرة
SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC LIMIT 10;

-- س4: لوحة التحكم - إيرادات اليوم
SELECT SUM(total_amount) FROM orders
WHERE created_at >= CURDATE() AND status != 'cancelled';

الفهارس الأمثل لهذه الاستعلامات:

-- الفهرس 1: للاستعلامات س1 وس3 (استعلامات العميل)
CREATE INDEX idx_customer_created
ON orders(customer_id, created_at);
-- يساعد في تصفية العميل + ترتيب التاريخ

-- الفهرس 2: للاستعلام س2 (استعلامات الحالة)
CREATE INDEX idx_status_created
ON orders(status, created_at);
-- يساعد في تصفية الحالة + ترتيب التاريخ

-- الفهرس 3: للاستعلام س4 (استعلامات التقارير مع فهرس مغطي)
CREATE INDEX idx_reporting
ON orders(created_at, status, total_amount);
-- فهرس مغطي: يحتوي على جميع الأعمدة المطلوبة (بدون وصول إلى الجدول)

-- التحقق من فهارسنا
SHOW INDEXES FROM orders;

تمرين عملي:

السيناريو: لديك جدول منشورات وسائل التواصل الاجتماعي بأداء ضعيف.

CREATE TABLE posts (
    id INT PRIMARY KEY,
    user_id INT,
    content TEXT,
    category VARCHAR(50),
    likes_count INT DEFAULT 0,
    created_at TIMESTAMP,
    is_published BOOLEAN DEFAULT 1
);

-- هذه الاستعلامات بطيئة:
-- س1: منشورات المستخدم المنشورة
SELECT * FROM posts
WHERE user_id = 456 AND is_published = 1
ORDER BY created_at DESC;

-- س2: أفضل المنشورات حسب الفئة
SELECT id, category, likes_count FROM posts
WHERE category = 'Technology' AND is_published = 1
ORDER BY likes_count DESC
LIMIT 10;

-- س3: المنشورات الأخيرة مع معاينة المحتوى
SELECT id, LEFT(content, 100) as preview, created_at
FROM posts
WHERE is_published = 1
ORDER BY created_at DESC
LIMIT 20;

المهمة: إنشاء فهارس مثلى لهذه الاستعلامات.

الحل:

-- الفهرس للاستعلام س1 (منشورات المستخدم)
CREATE INDEX idx_user_published_created
ON posts(user_id, is_published, created_at);
-- يغطي تصفية المستخدم، تصفية المنشور، وترتيب التاريخ

-- الفهرس للاستعلام س2 (أفضل المنشورات - فهرس مغطي)
CREATE INDEX idx_category_published_likes
ON posts(category, is_published, likes_count, id);
-- فهرس مغطي يتضمن جميع الأعمدة المطلوبة (بدون وصول إلى الجدول)

-- الفهرس للاستعلام س3 (المنشورات الأخيرة)
CREATE INDEX idx_published_created
ON posts(is_published, created_at);
-- لا يمكن تضمين المحتوى (كبير جداً) لكنه يحسّن التصفية + الترتيب
-- ملاحظة: لا يزال يحتاج إلى وصول إلى الجدول للمحتوى، لكنه أسرع بكثير

-- التحقق من تحسين الأداء
EXPLAIN SELECT * FROM posts
WHERE user_id = 456 AND is_published = 1
ORDER BY created_at DESC;
-- يجب أن يظهر: type=ref, key=idx_user_published_created

ملخص أفضل الممارسات

✓ استخدم أسماء فهارس ذات معنى ومتسقة
✓ أنشئ فهارس مركبة للاستعلامات متعددة الأعمدة
✓ ضع الأعمدة الأكثر انتقائية أولاً في الفهارس المركبة
✓ استخدم الفهارس المغطية للاستعلامات المتكررة
✓ اختبر تغييرات الفهرس على نسخة من بيانات الإنتاج
✓ راقب استخدام الفهرس باستخدام EXPLAIN وسجل الاستعلام البطيء
✓ قم بإزالة الفهارس غير المستخدمة (تحقق مع sys.schema_unused_indexes)

✗ لا تنشئ فهارس زائدة (idx_a، idx_a_b عندما يوجد idx_a_b)
✗ لا تفرط في الفهرسة (كل فهرس يضيف عبئاً على الكتابة)
✗ لا تفهرس كل عمود "في حالة الطوارئ"
✗ لا تنسَ اختبار الأداء قبل وبعد

الملخص

في هذا الدرس، تعلمت:

طرق متعددة لإنشاء الفهارس: CREATE INDEX و ALTER TABLE ومضمنة في CREATE TABLE
اتفاقيات تسمية الفهارس لتنظيم أفضل
قواعد ترتيب أعمدة الفهرس المركب (مساواة → نطاق → ترتيب)
الفهارس المغطية تلغي الوصول إلى الجدول لأداء أفضل
الفهارس غير المرئية (MySQL 8.0+) لاختبار الفهارس بأمان
كيفية حذف وتعديل الفهارس
تقنية بادئة الفهرس للأعمدة الطويلة
استراتيجيات تحسين الفهارس في العالم الحقيقي

التالي: في الدرس التالي، سنتقن تحسين الاستعلام باستخدام EXPLAIN ونتعلم كيفية تحديد وإصلاح الاستعلامات البطيئة!