Nerd Level Tech logo
الدرس 5 من 18

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

الفهرسة وتحسين الاستعلامات والمعاملات

5 دقيقة للقراءة

الفهارس وخطط الاستعلام وأمان المعاملات هي المواضيع التي تنقل مقابلات الخدمات الخلفية من السطحية إلى العمق. يغطي هذا الدرس كيفية عمل الفهارس داخليًا وكيفية قراءة خطط الاستعلام وكيف تضمن قواعد البيانات سلامة البيانات في ظل الوصول المتزامن.

فهارس B-tree: القوة الافتراضية

B-tree (الشجرة المتوازنة) هي نوع الفهرس الافتراضي في PostgreSQL وMySQL ومعظم قواعد البيانات العلائقية. تحافظ على البيانات مرتبة وتسمح بالبحث والإدراج والحذف في زمن O(log n).

كيف يعمل B-tree

B-tree هي شجرة ذاتية التوازن حيث:

  • العقد الداخلية تخزن المفاتيح ومؤشرات إلى العقد الفرعية
  • العقد الورقية تخزن المفاتيح ومؤشرات إلى صفوف الجدول الفعلية (heap tuples)
  • جميع العقد الورقية على نفس العمق، مما يضمن البحث في O(log n)
  • العقد الورقية مرتبطة معًا في قائمة مرتبطة ثنائية، مما يتيح مسحًا فعالاً للنطاقات

لجدول يحتوي على 10 ملايين صف، يكون عمق فهرس B-tree عادةً 3-4 مستويات. إيجاد أي صف يتطلب قراءة 3-4 صفحات فقط من القرص.

-- ينشئ فهرس B-tree (النوع الافتراضي)
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- B-tree يدعم: =, <, >, <=, >=, BETWEEN, IN, IS NULL
-- كما يدعم ORDER BY بدون خطوة فرز إضافية
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY created_at DESC;

فهارس Hash

فهارس Hash تستخدم دالة تجزئة لتعيين المفاتيح إلى حاويات. وهي مفيدة فقط للبحث بالتساوي ولا تدعم استعلامات النطاق أو الفرز.

CREATE INDEX idx_users_email_hash ON users USING hash(email);

-- جيد: مطابقة تامة (O(1) في المتوسط)
SELECT * FROM users WHERE email = 'user@example.com';

-- لا يمكن استخدام فهرس Hash: استعلام نطاق
SELECT * FROM users WHERE email > 'a' AND email < 'm';

متى تستخدم فهارس Hash: فقط عندما تستعلم حصريًا بالتساوي التام على عمود وتحتاج بحثًا أسرع قليلاً من B-tree. عمليًا، B-tree مفضل دائمًا تقريبًا لأن فرق الأداء هامشي بينما B-tree أكثر تنوعًا بكثير.

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

الفهرس المركب يغطي أعمدة متعددة. ترتيب الأعمدة مهم بشكل حاسم بسبب قاعدة البادئة اليسرى.

CREATE INDEX idx_orders_user_status_date
ON orders(user_id, status, created_at);

هذا الفهرس الواحد يمكنه خدمة هذه الاستعلامات:

فلتر الاستعلام يستخدم الفهرس؟ السبب
WHERE user_id = 5 نعم العمود الأيسر
WHERE user_id = 5 AND status = 'shipped' نعم العمودان الأيسران
WHERE user_id = 5 AND status = 'shipped' AND created_at > '2026-01-01' نعم الأعمدة الثلاثة
WHERE status = 'shipped' لا يتخطى العمود الأيسر
WHERE created_at > '2026-01-01' لا يتخطى الأعمدة اليسرى
WHERE user_id = 5 AND created_at > '2026-01-01' جزئي يستخدم user_id، يتخطى status للنطاق

نصيحة للمقابلة: عند السؤال عن تصميم فهرس مركب، فكّر في أنماط الاستعلام الأكثر شيوعًا. ضع أعمدة التساوي أولاً، ثم أعمدة النطاق أخيرًا. العمود ذو أعلى تعدد (أكثر قيم مختلفة) يجب أن يأتي أولاً بشكل عام بين أعمدة التساوي.

أنواع الفهارس المتخصصة

فهارس GIN (فهرس معكوس معمم)

فهارس GIN مصممة للقيم التي تحتوي على عناصر متعددة -- المصفوفات ومستندات JSONB ومتجهات البحث النصي الكامل.

-- البحث النصي الكامل في أوصاف المنتجات
CREATE INDEX idx_products_description_gin
ON products USING gin(to_tsvector('english', description));

SELECT * FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('wireless & headphones');

-- استعلامات احتواء JSONB
CREATE INDEX idx_events_metadata_gin ON events USING gin(metadata);

SELECT * FROM events
WHERE metadata @> '{"type": "purchase", "platform": "mobile"}';

الفهارس المغطية (INCLUDE)

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

-- بدون فهرس مغطٍ: مسح فهرس + وصول للجدول للحصول على البريد والاسم
CREATE INDEX idx_users_username ON users(username);

-- مع فهرس مغطٍ: مسح من الفهرس فقط (لا حاجة للوصول للجدول)
CREATE INDEX idx_users_username_covering
ON users(username) INCLUDE (email, display_name);

-- هذا الاستعلام يمكن الإجابة عليه بالكامل من الفهرس
SELECT email, display_name FROM users WHERE username = 'johndoe';

قراءة مخرجات EXPLAIN ANALYZE

EXPLAIN ANALYZE يعرض خطة التنفيذ الفعلية والتوقيت لاستعلام. هذه أهم أداة تشخيص للاستعلامات البطيئة.

EXPLAIN ANALYZE
SELECT u.username, COUNT(p.id) as post_count
FROM users u
JOIN posts p ON p.author_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.username
ORDER BY post_count DESC
LIMIT 10;

مثال على المخرجات (مبسط):

Limit (cost=1250.00..1250.02 rows=10) (actual time=45.2..45.3 rows=10)
  -> Sort (cost=1250.00..1262.00 rows=4800) (actual time=45.2..45.2 rows=10)
        Sort Key: (count(p.id)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        -> HashAggregate (cost=1100.00..1148.00 rows=4800) (actual time=42.1..43.8 rows=4800)
              Group Key: u.username
              -> Hash Join (cost=180.00..950.00 rows=30000) (actual time=5.2..35.6 rows=30000)
                    Hash Cond: (p.author_id = u.id)
                    -> Seq Scan on posts p (cost=0.00..620.00 rows=50000) (actual time=0.01..12.3 rows=50000)
                    -> Hash (cost=150.00..150.00 rows=4800) (actual time=4.8..4.8 rows=4800)
                          -> Index Scan using idx_users_created_at on users u
                             (cost=0.29..150.00 rows=4800) (actual time=0.03..3.2 rows=4800)
                                Index Cond: (created_at > '2025-01-01')

أنواع المسح الرئيسية التي يجب معرفتها:

نوع المسح ماذا يعني الأداء
Seq Scan يقرأ الجدول بالكامل صفًا بصف بطيء للجداول الكبيرة
Index Scan يستخدم الفهرس لإيجاد الصفوف، ثم يجلب من الجدول سريع للاستعلامات الانتقائية
Index Only Scan يقرأ من الفهرس فقط، بدون وصول للجدول الأسرع
Bitmap Index Scan يبني خريطة بت للصفحات المطابقة، ثم يجلبها جيد للانتقائية المتوسطة
Hash Join يبني جدول hash من العلاقة الأصغر سريع لعمليات الربط بالتساوي
Nested Loop لكل صف في الخارجي، يمسح الداخلي سريع عندما يكون للداخلي فهرس
Merge Join يدمج مدخلين مرتبين سريع عندما يكون كلا المدخلين مرتبين

خصائص ACID

كل قاعدة بيانات علائقية تضمن خصائص ACID للمعاملات:

الذرية (Atomicity) -- المعاملة هي كل شيء أو لا شيء. إذا فشل أي بيان، يتم التراجع عن جميع التغييرات.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- إذا فشل UPDATE الثاني، يتم التراجع عن الأول أيضًا
COMMIT;

الاتساق (Consistency) -- المعاملة تنقل قاعدة البيانات من حالة صالحة إلى حالة صالحة أخرى. القيود (CHECK وFOREIGN KEY وUNIQUE) تُفرض.

العزل (Isolation) -- المعاملات المتزامنة لا تتداخل مع بعضها (الدرجة تعتمد على مستوى العزل).

المتانة (Durability) -- بمجرد التأكيد، التغييرات تنجو من تعطل النظام. يتحقق ذلك من خلال تسجيل الكتابة المسبقة (WAL).

مستويات العزل وشذوذ التزامن

مستوى العزل القراءة القذرة القراءة غير القابلة للتكرار القراءة الوهمية
Read Uncommitted ممكنة ممكنة ممكنة
Read Committed ممنوعة ممكنة ممكنة
Repeatable Read ممنوعة ممنوعة ممكنة (ممنوعة في PostgreSQL)
Serializable ممنوعة ممنوعة ممنوعة

القراءة القذرة (Dirty Read): المعاملة A تقرأ بيانات عدّلتها المعاملة B لكن لم تؤكدها بعد. إذا تراجعت B، تكون A قد قرأت بيانات لم توجد رسميًا أبدًا.

القراءة غير القابلة للتكرار (Non-Repeatable Read): المعاملة A تقرأ صفًا، المعاملة B تحدثه وتؤكده، ثم A تقرأ نفس الصف مرة أخرى وتحصل على قيمة مختلفة.

القراءة الوهمية (Phantom Read): المعاملة A تستعلم عن صفوف تطابق شرطًا، المعاملة B تدرج صفًا جديدًا يطابق ذلك الشرط وتؤكده، ثم A تنفذ نفس الاستعلام وتحصل على صف إضافي.

-- الافتراضي في PostgreSQL هو Read Committed
-- لاستخدام Serializable:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
-- لا تستطيع معاملة أخرى تعديل هذا الصف حتى نؤكد
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

مهم: PostgreSQL تنفذ Repeatable Read باستخدام لقطات MVCC، التي تمنع أيضًا القراءات الوهمية -- مما يجعلها تتصرف مثل Serializable في قواعد بيانات أخرى. Serializable الحقيقي في PostgreSQL يستخدم عزل اللقطات التسلسلي (SSI).

MVCC: كيف تتعامل PostgreSQL مع التزامن

التحكم في التزامن متعدد الإصدارات (MVCC) يعني أن القراء لا يحجبون الكتّاب أبدًا والكتّاب لا يحجبون القراء أبدًا. PostgreSQL تحقق ذلك بالاحتفاظ بإصدارات متعددة من كل صف.

عند تحديث صف، PostgreSQL لا تكتب فوق الأصلي. بدلاً من ذلك:

  1. إصدار الصف القديم يُعلّم بـ xmax (معرف المعاملة التي حذفته/حدثته)
  2. إصدار صف جديد يُنشأ بـ xmin (معرف المعاملة التي أنشأته)
  3. كل معاملة ترى فقط إصدارات الصفوف التي تم تأكيدها قبل لقطة المعاملة

لهذا تحتاج PostgreSQL إلى VACUUM -- إصدارات الصفوف الميتة تتراكم ويجب تنظيفها دوريًا.

كشف ومنع الطريق المسدود (Deadlock)

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

-- المعاملة A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- تقفل الصف 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- تنتظر الصف 2

-- المعاملة B (متزامنة):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- تقفل الصف 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- تنتظر الصف 1
-- طريق مسدود: A تنتظر B، وB تنتظر A

استراتيجيات المنع:

  1. ترتيب أقفال متسق -- احصل دائمًا على الأقفال بنفس الترتيب (مثلاً بالمعرف التصاعدي). كلتا المعاملتين يجب أن تقفل id=1 أولاً، ثم id=2
  2. مهلة القفل -- اضبط lock_timeout للفشل السريع بدلاً من الانتظار إلى ما لا نهاية
  3. اجعل المعاملات قصيرة -- قلل وقت الاحتفاظ بالأقفال
  4. استخدم SELECT ... FOR UPDATE NOWAIT -- افشل فورًا إذا كان الصف مقفلاً بدلاً من الانتظار
-- نمط آمن: اقفل دائمًا بترتيب المعرف التصاعدي
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

نصيحة للمقابلة: عند مناقشة المعاملات، اذكر دائمًا: اختيار مستوى العزل يعتمد على متطلبات الاتساق مقابل احتياجات الإنتاجية. معظم أنظمة الإنتاج تستخدم Read Committed (الافتراضي في PostgreSQL) وتتعامل مع الحالات الطرفية بالقفل الصريح عند الحاجة، بدلاً من استخدام Serializable عالميًا.

التالي: قواعد بيانات NoSQL -- متى تختار مخازن المستندات أو مخازن المفتاح-القيمة أو مخازن عائلة الأعمدة أو قواعد البيانات الرسومية. :::

اختبار

اختبار الوحدة 2: تصميم قواعد البيانات وإتقان SQL

خذ الاختبار