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

استراتيجيات تحسين الفهارس

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

استراتيجيات تحسين الفهارس

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

انتقائية الفهرس

الانتقائية هي مقياس لمدى تفرد القيم في عمود مفهرس. الانتقائية العالية تعني أداء فهرس أفضل.

صيغة الانتقائية:
Selectivity = COUNT(DISTINCT column) / COUNT(column)

نطاق الانتقائية:
1.0 = مثالي (جميع القيم فريدة - الأفضل للفهرسة)
0.5 = متوسط (50% قيم فريدة)
0.0 = ضعيف (جميع القيم متماثلة - الأسوأ للفهرسة)

حساب الانتقائية

-- التحقق من انتقائية أعمدة مختلفة في جدول users
SELECT
    'email' as column_name,
    COUNT(DISTINCT email) / COUNT(*) as selectivity
FROM users;

-- النتيجة: 0.98 (98% فريد - ممتاز للفهرسة)

SELECT
    'country' as column_name,
    COUNT(DISTINCT country) / COUNT(*) as selectivity
FROM users;

-- النتيجة: 0.0004 (0.04% فريد - ضعيف للفهرسة)
-- 200 دولة فقط لكن 500,000 مستخدم

SELECT
    'gender' as column_name,
    COUNT(DISTINCT gender) / COUNT(*) as selectivity
FROM users;

-- النتيجة: 0.000004 (قيمتان فقط: M/F - ضعيف جداً للفهرسة)

قاعدة عامة: فهرس الأعمدة بانتقائية > 0.1 (10% فريد). الأعمدة ذات الانتقائية المنخفضة مثل الجنس أو العلامات المنطقية أو حقول الحالة مع خيارات قليلة هي مرشحات سيئة للفهارس المستقلة.

متى نفهرس الأعمدة ذات الانتقائية المنخفضة

-- لا تنشئ فهرساً مستقلاً على أعمدة ذات انتقائية منخفضة
-- سيء:
CREATE INDEX idx_gender ON users(gender);  -- قيمتان فقط!

-- جيد: استخدمه كجزء من فهرس مركب
CREATE INDEX idx_gender_created ON users(gender, created_at);

-- هذا الاستعلام يستفيد:
SELECT * FROM users WHERE gender = 'F' ORDER BY created_at DESC;
-- يصفي 50% من الصفوف، ثم يستخدم الفهرس للترتيب

قاعدة البادئة اليسرى

فهم قاعدة البادئة اليسرى حاسم للفهارس المركبة:

-- إنشاء فهرس مركب
CREATE INDEX idx_abc 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' AND created_at > '2024-01-01'
✓ WHERE customer_id = 123 ORDER BY status
✓ WHERE customer_id = 123 AND status = 'pending' ORDER BY created_at

لا يمكن استخدام هذا الفهرس لـ:
✗ WHERE status = 'pending'  (يتخطى customer_id)
✗ WHERE created_at > '2024-01-01'  (يتخطى customer_id)
✗ WHERE status = 'pending' AND created_at > '2024-01-01'  (يتخطى customer_id)

تحسين ترتيب الفهرس المركب

السيناريو: جدول طلبات التجارة الإلكترونية مع هذه الاستعلامات

Q1: SELECT * FROM orders WHERE customer_id = 123;  (90% من الاستعلامات)
Q2: SELECT * FROM orders WHERE status = 'pending'; (5% من الاستعلامات)
Q3: SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending'; (5%)

الخيار 1: customer_id أولاً (موصى به)
CREATE INDEX idx_customer_status ON orders(customer_id, status);

✓ Q1: يستخدم الفهرس (customer_id) - يغطي 90% من الاستعلامات
✓ Q3: يستخدم الفهرس الكامل (customer_id, status) - يغطي 5%
✗ Q2: لا يمكن استخدام الفهرس بكفاءة

الخيار 2: status أولاً (غير موصى به)
CREATE INDEX idx_status_customer ON orders(status, customer_id);

✓ Q2: يستخدم الفهرس (status) - يغطي 5% من الاستعلامات
✓ Q3: يستخدم الفهرس الكامل (status, customer_id) - يغطي 5%
✗ Q1: لا يمكن استخدام الفهرس بكفاءة - لكن هذا 90%!

الخلاصة: قم دائماً بالتحسين للاستعلامات الأكثر تكراراً!

أفضل ممارسة: في الفهارس المركبة، ضع العمود الأكثر انتقائية والاستعلام عنه أولاً. ضع في اعتبارك تكرار الاستعلام والتفرد معاً.

تلميحات الفهرس

أحياناً محسّن MySQL يختار الفهرس الخاطئ. يمكنك إجباره على استخدام فهرس محدد:

تلميح USE INDEX

-- اقتراح فهرس (قد يتجاهله المحسن)
SELECT * FROM orders USE INDEX (idx_customer_status)
WHERE customer_id = 123 AND status = 'pending';

-- اقتراحات فهرس متعددة
SELECT * FROM orders USE INDEX (idx_customer, idx_status)
WHERE customer_id = 123;

تلميح FORCE INDEX

-- إجبار MySQL على استخدام فهرس محدد (تجاوز قوي)
SELECT * FROM orders FORCE INDEX (idx_customer_status)
WHERE customer_id = 123 AND status = 'pending';

-- فرض فهرس محدد لـ JOIN
SELECT o.*, u.name
FROM orders o FORCE INDEX (idx_customer)
JOIN users u ON o.customer_id = u.id
WHERE o.status = 'pending';

تلميح IGNORE INDEX

-- منع MySQL من استخدام فهرس محدد
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'pending' AND created_at > '2024-01-01';

-- يجبر MySQL على النظر في فهارس أخرى أو فحص جدول كامل

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

متى نستخدم تلميحات الفهرس

حالات استخدام صالحة:
✓ المحسن يختار الفهرس الخاطئ باستمرار
✓ اختبرت وتأكدت من أداء أفضل
✓ توزيع البيانات يجعل إحصائيات المحسن غير دقيقة
✓ استعلامات معقدة حيث يواجه المحسن صعوبة

لا تستخدم عندما:
✗ لم تقم بتشغيل EXPLAIN للتحقق من المشكلة
✗ كـ "حل سريع" بدلاً من التحسين الصحيح
✗ تخمن أي فهرس أفضل
✗ أنماط البيانات تتغير بشكل متكرر

تجنب فحص الجدول الكامل

فحص الجدول الكامل (type = "ALL") هو عدو الأداء. إليك كيفية تجنبها:

الأسباب الشائعة والحلول

السبب 1: لا يوجد فهرس
-- المشكلة
SELECT * FROM users WHERE country = 'USA';
-- EXPLAIN يظهر: type=ALL, key=NULL

-- الحل
CREATE INDEX idx_country ON users(country);

السبب 2: دالة على عمود مفهرس
-- المشكلة
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- الدالة تمنع استخدام الفهرس

-- الحل
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

السبب 3: تحويل نوع ضمني
-- المشكلة (email هو VARCHAR، لكن استخدام رقم صحيح)
SELECT * FROM users WHERE email = 123456;
-- MySQL يحول email إلى رقم، يمنع استخدام الفهرس

-- الحل (استخدم نوع البيانات الصحيح)
SELECT * FROM users WHERE email = '123456';

السبب 4: OR مع عمود غير مفهرس
-- المشكلة
SELECT * FROM users WHERE email = 'john@example.com' OR phone = '555-1234';
-- إذا لم يكن لدى phone فهرس، يصبح الاستعلام بالكامل فحص جدول

-- الحل: إنشاء فهرس على phone
CREATE INDEX idx_phone ON users(phone);

-- أو استخدم UNION
SELECT * FROM users WHERE email = 'john@example.com'
UNION
SELECT * FROM users WHERE phone = '555-1234';

السبب 5: حرف بدل في البداية
-- المشكلة
SELECT * FROM users WHERE email LIKE '%@example.com';
-- حرف بدل في البداية يمنع استخدام الفهرس

-- الحل: تجنب أحرف البدل في البداية عندما يكون ممكناً
SELECT * FROM users WHERE email LIKE 'john%';  -- يمكن استخدام الفهرس

تحسين دمج الفهرس

يمكن لـ MySQL الجمع بين عدة فهارس لاستعلام واحد:

-- فهرسان منفصلان
CREATE INDEX idx_country ON users(country);
CREATE INDEX idx_age ON users(age);

-- استعلام يستخدم كلا الشرطين
SELECT * FROM users WHERE country = 'USA' AND age > 25;

-- قد يظهر EXPLAIN:
type: index_merge
key: idx_country,idx_age
Extra: Using intersect(idx_country,idx_age); Using where

أنواع دمج الفهرس:
- intersection: يجمع النتائج من فهارس متعددة (AND)
- union: يدمج النتائج من فهارس متعددة (OR)
- sort-union: مثل union لكن مع الترتيب

ملاحظة: بينما دمج الفهرس أفضل من فحص الجدول الكامل، فإن فهرساً مركباً واحداً عادة ما يكون أكثر كفاءة من دمج فهارس متعددة.

-- حل أفضل:
CREATE INDEX idx_country_age ON users(country, age);

متى تضر الفهارس بالأداء

الكثير من الفهارس أو الفهارس المختارة بشكل سيء يمكن أن تدهور الأداء:

1. الجداول كثيرة الكتابة

-- جدول مع العديد من الفهارس
CREATE TABLE logs (
    id INT PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    created_at TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at),
    INDEX idx_user_action (user_id, action),
    INDEX idx_user_created (user_id, created_at)
);

-- المشكلة: كل INSERT يحدث 6 فهارس!
INSERT INTO logs (user_id, action, created_at) VALUES (123, 'login', NOW());
-- هذا بطيء للتسجيل ذي الحجم الكبير

-- الحل: قلل الفهارس للجداول كثيرة الكتابة
-- احتفظ بالفهارس الأساسية فقط، فكر في نظام تسجيل منفصل

2. الفهارس الزائدة

-- الفهارس الزائدة تهدر المساحة وتبطئ الكتابة
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_email_name ON users(email, name);
-- idx_email زائد! idx_email_name يغطيه بسبب البادئة اليسرى

-- زائد أيضاً:
CREATE INDEX idx_a ON table(a);
CREATE INDEX idx_ab ON table(a, b);
CREATE INDEX idx_abc ON table(a, b, c);
-- idx_abc فقط مطلوب! يغطي الأنماط الثلاثة.

-- البحث عن الفهارس الزائدة
SELECT
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) as columns
FROM information_schema.STATISTICS
WHERE table_schema = 'your_database'
GROUP BY table_name, index_name
ORDER BY table_name, index_name;

3. الإفراط في فهرسة الجداول الصغيرة

-- لا تفهرس الجداول الصغيرة جداً
CREATE TABLE settings (
    id INT PRIMARY KEY,
    key_name VARCHAR(50),
    value TEXT
);  -- 10 صفوف فقط

-- إضافة فهارس هنا مضيعة
-- فحص جدول كامل لـ 10 صفوف أسرع من بحث الفهرس!

تمرين عملي:

السيناريو: لديك منصة وسائل التواصل الاجتماعي مع جدول منشورات:

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

-- 10 مليون منشور
-- أنماط الاستعلام (مع التكرار):

Q1 (60%): SELECT * FROM posts
          WHERE user_id = ?
          ORDER BY created_at DESC
          LIMIT 20;

Q2 (30%): SELECT * FROM posts
          WHERE category = ? AND is_published = 1
          ORDER BY likes_count DESC
          LIMIT 10;

Q3 (8%):  SELECT * FROM posts
          WHERE is_published = 1
          ORDER BY created_at DESC
          LIMIT 50;

Q4 (2%):  SELECT * FROM posts
          WHERE user_id = ? AND category = ?
          ORDER BY created_at DESC;

المهمة: صمم استراتيجية فهرسة مثلى.

الحل:

الخطوة 1: تحليل الانتقائية
SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM posts;
-- النتيجة: 0.0001 (10,000 مستخدم، 10M منشور) - متوسط

SELECT COUNT(DISTINCT category) / COUNT(*) FROM posts;
-- النتيجة: 0.00001 (100 فئة، 10M منشور) - منخفض

SELECT COUNT(DISTINCT is_published) / COUNT(*) FROM posts;
-- النتيجة: 0.0000001 (قيمتان) - منخفض جداً

الخطوة 2: تصميم الفهارس بناءً على تكرار الاستعلام

-- الفهرس 1: لـ Q1 (60% من الاستعلامات) - أعلى أولوية
CREATE INDEX idx_user_created ON posts(user_id, created_at);
-- يغطي تصفية المستخدم + ترتيب التاريخ

-- الفهرس 2: لـ Q2 (30% من الاستعلامات)
CREATE INDEX idx_cat_pub_likes ON posts(category, is_published, likes_count);
-- يغطي الفئة + تصفية المنشور + ترتيب الإعجابات

-- الفهرس 3: لـ Q3 (8% من الاستعلامات)
CREATE INDEX idx_published_created ON posts(is_published, created_at);
-- يغطي تصفية المنشور + ترتيب التاريخ

-- Q4 (2%): يمكن إعادة استخدام idx_user_created
-- WHERE user_id = ? AND category = ?
-- idx_user_created يغطي user_id بكفاءة
-- MySQL ستصفي category في الذاكرة (مقبول لـ 2% من الاستعلامات)

المنطق:
- محسّن للاستعلامات الأكثر تكراراً (90% مغطى بالفهرسين الأولين)
- تجنب التكرار (Q4 يمكن استخدام الفهرس الموجود)
- وضع الأعمدة عالية الانتقائية أولاً في المركبات
- تضمين أعمدة الترتيب لفوائد الفهرس المغطي

مراقبة وصيانة الفهرس

-- البحث عن الفهارس غير المستخدمة (MySQL 8.0+)
SELECT * FROM sys.schema_unused_indexes;

-- البحث عن الفهارس المكررة/الزائدة
SELECT * FROM sys.schema_redundant_indexes;

-- التحقق من تجزئة الفهرس
ANALYZE TABLE users;

-- إعادة بناء الفهارس المجزأة
ALTER TABLE users ENGINE=InnoDB;  -- يعيد بناء جميع الفهارس

-- أو إعادة بناء فهرس محدد
DROP INDEX idx_email ON users;
CREATE INDEX idx_email ON users(email);

الملخص

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

انتقائية الفهرس تقيس التفرد - اهدف إلى >0.1 (10%+ قيم فريدة)
قاعدة البادئة اليسرى تحدد أي استعلامات يمكن أن تستخدم الفهارس المركبة
رتب الفهارس المركبة حسب تكرار الاستعلام والانتقائية
استخدم تلميحات الفهرس (USE INDEX، FORCE INDEX) بشكل محدود عندما يفشل المحسن
تجنب فحص الجدول الكامل بإزالة الدوال على الأعمدة المفهرسة
دمج الفهرس يجمع فهارس متعددة لكن الفهرس المركب عادة أفضل
الكثير من الفهارس تضر بأداء الكتابة - قم بإزالة الفهارس الزائدة
الجداول الصغيرة (<1000 صف) نادراً ما تستفيد من الفهارس
راقب استخدام الفهرس وقم بإزالة الفهارس غير المستخدمة بانتظام

التالي: في الدرس التالي، سنستكشف البحث النصي الكامل مع فهارس FULLTEXT وصيغة MATCH AGAINST للبحث النصي القوي!