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

فهم الفهارس

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

فهم الفهارس

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

ما هو فهرس قاعدة البيانات؟

فهرس قاعدة البيانات هو هيكل بيانات يحسن سرعة عمليات استرجاع البيانات في جدول. فكر فيه مثل الفهرس في كتاب - بدلاً من قراءة كل صفحة للعثور على موضوع، يمكنك البحث عنه في الفهرس والانتقال مباشرة إلى الصفحات ذات الصلة.

مفهوم أساسي: تسرع الفهارس استعلامات SELECT ولكنها تضيف عبئاً على عمليات INSERT و UPDATE و DELETE لأن الفهرس يجب أيضاً تحديثه عند تغيير البيانات.

كيف تعمل الفهارس: هيكل B-Tree

معظم فهارس MySQL تستخدم هيكل بيانات B-Tree (شجرة متوازنة). إليك كيف يعمل:

بدون فهرس:
SELECT * FROM users WHERE email = 'john@example.com';
→ يفحص جميع الصفوف بالتسلسل (فحص جدول كامل)
→ 1,000,000 صف = 1,000,000 مقارنة

مع فهرس على email:
SELECT * FROM users WHERE email = 'john@example.com';
→ يستخدم B-Tree للعثور على الموقع الدقيق
→ 1,000,000 صف = ~20 مقارنة (عمليات log₂)

تحسين الأداء: يمكن للفهرس تقليل وقت الاستعلام من ثوانٍ إلى ملي ثانية. قد يتطلب جدول يحتوي على مليون صف مليون مقارنة بدون فهرس، ولكن 20 مقارنة فقط مع فهرس B-Tree!

أنواع الفهارس في MySQL

تدعم MySQL عدة أنواع من الفهارس، كل منها مصمم لحالات استخدام محددة:

1. فهرس PRIMARY KEY

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255)
);

-- المفتاح الأساسي ينشئ تلقائياً فهرس UNIQUE
-- قيم NULL غير مسموحة
-- مفتاح أساسي واحد فقط لكل جدول

2. فهرس UNIQUE

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

-- يضمن أن جميع القيم في العمود مختلفة
-- قيم NULL مسموحة (ولكن NULL واحد فقط في معظم الحالات)
-- يمكن أن يكون لديك عدة فهارس UNIQUE لكل جدول

3. INDEX (فهرس عادي)

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    country VARCHAR(50),
    INDEX idx_country (country)
);

-- يسمح بالقيم المكررة
-- يسرع عمليات البحث على الأعمدة المفهرسة
-- النوع الأكثر استخداماً من الفهارس

4. فهرس FULLTEXT

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT idx_content (title, content)
);

-- يستخدم لعمليات البحث النصي الكامل
-- يعمل مع صيغة MATCH() AGAINST()
-- متاح فقط لأعمدة CHAR و VARCHAR و TEXT

فهارس العمود الواحد مقابل الأعمدة المتعددة

فهرس العمود الواحد

CREATE INDEX idx_email ON users(email);

-- جيد لـ: WHERE email = 'john@example.com'
-- جيد لـ: ORDER BY email

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

CREATE INDEX idx_country_city ON users(country, city);

-- محسّن للاستعلامات التي تستخدم كلا العمودين
-- يتبع قاعدة "البادئة اليسرى"

قاعدة البادئة اليسرى تعني أن الفهرس يمكن استخدامه لـ:

✓ WHERE country = 'USA'
✓ WHERE country = 'USA' AND city = 'New York'
✗ WHERE city = 'New York' (لا يمكن استخدام الفهرس - city ليس في اليسار)

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

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

الفهارس مفيدة في هذه السيناريوهات:

✓ فهرس هذه:
- الأعمدة المستخدمة بشكل متكرر في جمل WHERE
- الأعمدة المستخدمة في شروط JOIN
- الأعمدة المستخدمة في ORDER BY أو GROUP BY
- الأعمدة ذات التفرد العالي (قيم فريدة كثيرة)
- أعمدة المفاتيح الأجنبية

✗ لا تفهرس هذه:
- الجداول الصغيرة (أقل من 1000 صف)
- الأعمدة ذات التفرد المنخفض (قيم قليلة مثل الجنس: M/F)
- الأعمدة التي يتم تحديثها بشكل متكرر
- الأعمدة التي نادراً ما يتم الاستعلام عنها
- أعمدة TEXT أو BLOB الكبيرة جداً

عبء الفهرس والمقايضات

بينما تحسن الفهارس أداء SELECT، فإنها تأتي بتكاليف:

الفوائد:
+ استعلامات SELECT أسرع (أحياناً أسرع بـ 100 مرة)
+ عمليات JOIN أسرع
+ فرز أسرع (ORDER BY)
+ فرض التفرد (فهارس UNIQUE)

التكاليف:
- عمليات INSERT أبطأ (يجب تحديث الفهرس)
- عمليات UPDATE أبطأ (إذا تغيرت الأعمدة المفهرسة)
- عمليات DELETE أبطأ (يجب تحديث الفهرس)
- مساحة قرص إضافية (يمكن أن تكون الفهارس كبيرة)
- عبء الصيانة

قاعدة عامة: للتطبيقات كثيرة القراءة (الكثير من SELECTs)، استخدم المزيد من الفهارس. للتطبيقات كثيرة الكتابة (الكثير من INSERTs/UPDATEs)، استخدم فهارس أقل.

عرض الفهارس في جدول

يمكنك عرض جميع الفهارس في جدول باستخدام عدة طرق:

-- الطريقة 1: SHOW INDEXES
SHOW INDEXES FROM users;

-- الطريقة 2: SHOW INDEX
SHOW INDEX FROM users;

-- الطريقة 3: SHOW KEYS
SHOW KEYS FROM users;

-- الطريقة 4: information_schema
SELECT
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'users'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

مثال على النتيجة:

+------------+------------+-------------+-----------+
| Table      | Non_unique | Key_name    | Column    |
+------------+------------+-------------+-----------+
| users      | 0          | PRIMARY     | id        |
| users      | 0          | email       | email     |
| users      | 1          | idx_country | country   |
+------------+------------+-------------+-----------+

Non_unique = 0: فهرس UNIQUE (PRIMARY, UNIQUE)
Non_unique = 1: فهرس عادي (يسمح بالتكرار)

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

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

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

+----+------+---------+------+---------+-------+
| id | type | key     | rows | filtered| Extra |
+----+------+---------+------+---------+-------+
| 1  | ref  | email   | 1    | 100.00  | NULL  |
+----+------+---------+------+---------+-------+

عمود Key يظهر "email": يتم استخدام الفهرس ✓
Type هو "ref": بحث فهرس (سريع) ✓
Rows هو 1: تم فحص صف واحد فقط ✓

EXPLAIN SELECT * FROM users WHERE country = 'USA';

+----+------+------+--------+---------+-------+
| id | type | key  | rows   | filtered| Extra |
+----+------+------+--------+---------+-------+
| 1  | ALL  | NULL | 100000 | 10.00   | Using |
+----+------+------+--------+---------+-------+

Type هو "ALL": فحص جدول كامل (بطيء) ✗
Key هو NULL: لم يتم استخدام فهرس ✗
Rows هو 100000: تم فحص جميع الصفوف ✗

مثال من العالم الحقيقي

دعنا نرى فرق الأداء مع وبدون فهارس:

-- إنشاء جدول مع مليون مستخدم
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255),
    country VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- بدون فهرس على email
SELECT * FROM users WHERE email = 'john@example.com';
-- وقت الاستعلام: 2.5 ثانية (فحص جدول كامل)

-- إضافة فهرس
CREATE INDEX idx_email ON users(email);

-- مع فهرس على email
SELECT * FROM users WHERE email = 'john@example.com';
-- وقت الاستعلام: 0.001 ثانية (بحث فهرس)

تحسين الأداء: أسرع بـ 2500 مرة!

تمرين عملي:

السيناريو: لديك مدونة تحتوي على 500,000 مقالة. يبحث المستخدمون بشكل متكرر حسب الفئة، وتحتاج إلى سرد المقالات مرتبة حسب تاريخ النشر.

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    category VARCHAR(50),
    author_id INT,
    published_at DATETIME,
    views INT
);

-- استعلام شائع:
SELECT * FROM articles
WHERE category = 'Technology'
ORDER BY published_at DESC
LIMIT 20;

السؤال: ما هي الفهارس التي ستنشئها لتحسين هذا الاستعلام؟

الإجابة:

-- إنشاء فهرس مركب مع category أولاً، ثم published_at
CREATE INDEX idx_category_published ON articles(category, published_at);

-- هذا الفهرس يساعد في:
-- 1. WHERE category = 'Technology' (يستخدم العمود الأيسر)
-- 2. ORDER BY published_at (يستخدم العمود الثاني)
-- 3. كلا الشرطين معاً (يستخدم الفهرس الكامل)

-- بديل إذا كنت بحاجة إلى استعلامات منفصلة:
CREATE INDEX idx_category ON articles(category);
CREATE INDEX idx_published ON articles(published_at);
-- لكن الفهرس المركب أكثر كفاءة لهذا الاستعلام المحدد

الملخص

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

تستخدم الفهارس هيكل B-Tree لتسريع استرجاع البيانات بشكل كبير
تدعم MySQL أنواع الفهارس PRIMARY KEY و UNIQUE و INDEX و FULLTEXT
الفهارس المركبة تتبع قاعدة البادئة اليسرى
فهرس الأعمدة المستخدمة في جمل WHERE و JOIN و ORDER BY و GROUP BY
تحسن الفهارس أداء SELECT ولكنها تبطئ INSERT/UPDATE/DELETE
استخدم SHOW INDEXES و EXPLAIN لعرض والتحقق من استخدام الفهرس
الفهرسة الصحيحة يمكن أن تحسن أداء الاستعلام بمقدار 100 مرة أو أكثر

التالي: في الدرس التالي، سنتعلم كيفية إنشاء وإدارة الفهارس بفعالية، بما في ذلك التقنيات المتقدمة مثل الفهارس المغطية والفهارس غير المرئية!