الرئيسية
/
مدونة
/
Query Optimization في قواعد البيانات الكبيرة — دليل عملي

Query Optimization في قواعد البيانات الكبيرة — دليل عملي

سبتمبر 30, 2026 5 دقائق للقراءة

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

السبب في أهمية هذا الموضوع بسيط ومباشر: مع نمو البيانات، ينمو زمن التنفيذ بشكل حاد عند غياب التحسين. استعلام يعمل في 50 ملي ثانية على 10 آلاف صف قد يستغرق 40 ثانية على 10 ملايين صف إذا كان يقرأ الجدول بالكامل. الفرق بين النظامين ليس العتاد، بل الخطة التي اختارها المُحسِّن والفهرس الذي لم يكن موجودًا.

سنتناول في هذا الدليل Query Optimization في قواعد البيانات الكبيرة من زوايا عملية: كيف يفكر محرك قاعدة البيانات، وكيف تقرأ خطته، وكيف تعيد كتابة الاستعلامات، وكيف توازن بين سرعة القراءة وتكلفة الكتابة، مع أمثلة قابلة للتشغيل على PostgreSQL وملاحظات تنطبق على MySQL وSQL Server وOracle.

أهم النقاط التي ستتعلمها في هذا الدليل

كيف يتحول استعلام SQL إلى خطة تنفيذ، وكيف يقدّر المُحسِّن تكلفة كل مسار بديل.
قراءة EXPLAIN ANALYZE وتحديد أول عقدة مسؤولة عن البطء بدل تخمين السبب.
بناء الفهارس الصحيحة: المركبة، والمغطية، والجزئية، ومتى يضر الفهرس أكثر مما ينفع.
إعادة كتابة الاستعلامات لتكون قابلة لاستخدام الفهرس، وتجنب الأنماط التي تعطّل الفهارس بصمت.
التقسيم، والإحصاءات، والتخزين المؤقت، وإدارة الاتصالات لتحقيق توسّع حقيقي في البيانات الضخمة.

لماذا يختلف Query Optimization في قواعد البيانات الكبيرة؟

في قاعدة بيانات صغيرة، غالبًا ما يكون الجدول كله موجودًا في الذاكرة، وقراءة كل صفوفه لا تكلف شيئًا يُذكر. لهذا تعمل الاستعلامات غير المُحسَّنة بسرعة مقبولة أثناء التطوير، ثم تنهار في الإنتاج بعد أشهر من تراكم البيانات.

في قواعد البيانات الكبيرة، تصبح البيانات أكبر من أن تُحفظ في الذاكرة، فيتحول الأداء إلى مسألة إدخال وإخراج (I/O). القراءة من القرص أبطأ بآلاف المرات من القراءة من الذاكرة، وكل صفحة (Page) تُقرأ بلا داعٍ تُضاف إلى زمن التنفيذ النهائي.

الفرق بين المسح الكامل وقراءة الفهرس

المسح الكامل (Full Table Scan) يقرأ كل صفحات الجدول ثم يفلتر النتائج. أما قراءة الفهرس فيتم فيها القفز مباشرة إلى الصفحات المطلوبة. الفرق بينهما على جدول من 100 مليون صف ليس نسبة مئوية، بل فرق بين ثوانٍ وملي ثوانٍ.

العوامل التي تجعل الاستعلام ينهار مع نمو البيانات

غياب فهرس مناسب على الأعمدة المستخدمة في شرط WHERE أو JOIN أو ORDER BY.
انحراف تقديرات المُحسِّن بين عدد الصفوف المتوقع والفعلي، مما يدفعه لاختيار خطة سيئة.
الفرز على القرص عندما يتجاوز حجم البيانات ذاكرة العمل المخصصة للاستعلام.
الاستعلامات المتكررة غير المُخزَّنة مؤقتًا التي تُنفَّذ آلاف المرات في الدقيقة بلا سبب.
تضخم الجداول والفهارس بسبب الصفوف الميتة وعدم تشغيل التنظيف (Vacuum) بانتظام.

تكلفة التحسين مقابل عائده

قبل أي تحسين، اسأل نفسك: ما زمن التنفيذ الحالي، وما الزمن المستهدف، وما حجم الأثر على التطبيق؟ تحسين استعلام يعمل مرة واحدة يوميًا من 10 ثوانٍ إلى 5 ثوانٍ قد لا يستحق الجهد، بينما تحسين استعلام يُنفَّذ 5000 مرة في الثانية يستحق كل دقيقة تُستثمر فيه.

القاعدة العملية: حسِّن ما يُنفَّذ كثيرًا أو ما يستهلك أكبر نسبة من زمن قاعدة البيانات الإجمالي، لا ما يبدو بطيئًا في بيئة التطوير.

كيف ينفّذ محرك قاعدة البيانات الاستعلام فعليًا؟

لفهم Query Optimization، يجب أن تعرف المسار الذي يمر به نص SQL داخل المحرك. كل قاعدة بيانات علائقية حديثة تمر بثلاث مراحل أساسية، وفهمها يفسر سلوكًا قد يبدو غريبًا.

المرحلة الأولى: التحليل (Parser)

يتحقق المحرك من صحة النص النحوي، ويؤكد وجود الجداول والأعمدة والصلاحيات، ثم يحوّل الاستعلام إلى شجرة منطقية. الخطأ في هذه المرحلة يظهر كخطأ صريح، ولا يصل الاستعلام إلى المُحسِّن أصلًا.

المرحلة الثانية: التحسين (Planner / Optimizer)

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

لهذا فإن تشغيل ANALYZE بعد تغيير كبير في البيانات قد يغيّر الخطة كليًا دون تعديل حرف واحد في الاستعلام.

المرحلة الثالثة: التنفيذ (Executor)

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

قراءة خطة التنفيذ: أول خطوة حقيقية في Query Optimization

لا يمكن تحسين ما لا تقيسه. الخطوة الأولى دائمًا هي الحصول على خطة التنفيذ الفعلية. في PostgreSQL نستخدم EXPLAIN ANALYZE الذي ينفّذ الاستعلام ويقارن التقدير بالواقع، وليس EXPLAIN وحده الذي يعرض التقديرات فقط.

المثال التالي يستعلم آخر 20 طلبًا لعميل معيّن بعد تاريخ محدد، وهو نمط شائع في لوحات التحكم:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.total, o.created_at
FROM orders o
WHERE o.customer_id = 84213
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

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

Limit  (cost=0.43..84.21 rows=20 width=28) (actual time=0.031..0.187 rows=20 loops=1)
  Buffers: shared hit=19
  ->  Index Scan Backward using idx_orders_customer_created on orders o
        (cost=0.43..1254.77 rows=298 width=28) (actual time=0.029..0.162 rows=20 loops=1)
        Index Cond: ((customer_id = 84213) AND (created_at >= '2024-01-01'::timestamp))
        Buffers: shared hit=19
Planning Time: 0.214 ms
Execution Time: 0.231 ms

لاحظ قيمة Buffers: shared hit=19، أي أن الاستعلام قرأ 19 صفحة من الذاكرة فقط. لو ظهرت shared read بأرقام كبيرة، لكانت القراءة من القرص هي عنق الزجاجة.

مصطلحات أساسية تظهر في خطط التنفيذ

Seq Scan: قراءة الجدول بالكامل. مقبول على الجداول الصغيرة، مكلف جدًا على الجداول الضخمة.
Index Scan: الانتقال إلى الصفوف عبر الفهرس ثم قراءة صفحات الجدول.
Index Only Scan: قراءة الفهرس وحده دون لمس الجدول، وهو الأسرع عندما يغطي الفهرس كل الأعمدة المطلوبة.
Bitmap Heap Scan: جمع مواقع الصفوف في خريطة ثم قراءتها بترتيب فعّال، مفيد عند كثرة الصفوف المطابقة.
Nested Loop: لكل صف من الجدول الخارجي يبحث في الجدول الداخلي. ممتاز للنتائج الصغيرة، كارثي مع ملايين التكرارات.
Hash Join: بناء جدول تجزئة في الذاكرة ثم الربط. مناسب للجداول الكبيرة بلا فهرس ربط.
Sort / HashAggregate: ترتيب أو تجميع، وقد يتحول إلى قرص وكتابة مؤقتة عند نقص الذاكرة.

كيف تقرأ الأرقام؟ cost و actual time و rows

الصيغة cost=0.43..1254.77 تعني تكلفة الوصول لأول صف ثم التكلفة الكلية المقدّرة. الأهم هو المقارنة بين rows=298 في التقدير وrows=20 في الواقع: فرق بمقدار 15 ضعفًا يعني أن الإحصاءات تحتاج تحديثًا فوريًا.

علامات تحذيرية يجب ألا تتجاهلها

مسح كامل على جدول ضخم مع شرط يطابق أقل من 1% من الصفوف.
فرق كبير بين الصفوف المتوقعة والصفوف الفعلية في أي عقدة.
ظهور Sort Method: external merge Disk أي أن الترتيب خرج إلى القرص.
حلقات loops بملايين التكرارات داخل Nested Loop.
عقدة تبدأ بعد مئات الملي ثانية رغم أن زمن التنفيذ الكلي أقل من ثانية، وهذا مؤشر على وجود مشكلة في التخطيط نفسه.

الفهارس ودورها المحوري في Query Optimization

الفهرس هو أهم أداة في Query Optimization في قواعد البيانات الكبيرة، لكنه ليس حلًا سحريًا. الفهرس الجيد يقلل عدد الصفحات المقروءة من ملايين إلى عشرات، والفهرس السيئ يستهلك مساحة ووقت كتابة دون أي عائد.

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

B-Tree: الافتراضي والأكثر استخدامًا، يخدم التساوي والمدى والترتيب.
Hash: يخدم مقارنات التساوي فقط، ولا يخدم المدى ولا الترتيب.
GIN: لحقول jsonb والمصفوفات والبحث النصي الكامل.
GiST: للبيانات المكانية والهندسية وأشجار البحث المتداخلة.
BRIN: مناسب للجداول الضخمة جدًا المؤطّرة زمنيًا مثل سجلات الأحداث، بحجم صغير جدًا مقارنة بـ B-Tree.

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

في الفهرس المركب، ترتيب الأعمدة ليس تفصيلًا شكليًا. الفهرس يمكن استخدامه للأعمدة من اليسار إلى اليمين، فالفهرس على (customer_id, created_at) يخدم الشرط على customer_id وحده، ولكن لا يخدم الشرط على created_at وحده.

الترتيب الصحيح يبدأ بعمود المساواة عالي الانتقائية، ثم يليه عمود المدى أو الترتيب:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);

كلمة CONCURRENTLY مهمة في بيئة الإنتاج لأنها تبني الفهرس دون قفل الكتابة على الجدول، وعادة ما تكون إضافة فهرس على جدول ضخم عملية تحتاج تخطيطًا لأنه لا يمكن تنفيذها داخل معاملة.

الفهارس المغطية و Index Only Scan

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

CREATE INDEX idx_orders_covering
ON orders (customer_id, created_at DESC)
INCLUDE (total, status);

هنا أعمدة INCLUDE لا تُستخدم في البحث أو الترتيب، بل تُخزَّن في الفهرس لتفادي قراءة الجدول. هذا مفيد عندما تكون البيانات المطلوبة صغيرة الحجم ومكرّرة القراءة.

الفهارس الجزئية والتعبيرية

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

CREATE INDEX idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';

أما الفهرس التعبيري فيخدم الحالات التي تحتاج البحث بدالة، مثل البحث غير الحساس لحالة الأحرف:

CREATE INDEX idx_users_email_lower
ON users (lower(email));

لكن يجب أن يطابق الاستعلام نفس التعبير بالضبط، وإلا لن يُستخدم الفهرس. هذا أحد أكثر أخطاء Query Optimization شيوعًا في قواعد البيانات الكبيرة.

متى يضر الفهرس أكثر مما ينفع؟

على أعمدة منخفضة الانتقائية مثل is_active التي تطابق نصف الجدول.
على جداول تُكتب بكثافة، لأن كل إدراج أو تحديث يكلّف تحديث كل فهرس مرتبط.
عند وجود فهارس متعددة متقاربة، فإنها تكرّر نفس الفائدة وتضاعف تكلفة الكتابة.
عندما تكون الإحصاءات متضخمة أو قديمة، فقد يختار المحرك الفهرس في موضع لا يفيد فيه.

إعادة كتابة الاستعلامات: أسرع تحسين بلا تغيير في البنية

كثير من مشاكل الأداء تُحل بإعادة صياغة الاستعلام نفسه، دون إضافة فهرس أو تغيير أي شيء في المخطط. هذا أرخص تحسين وأقلّه مخاطرة، وهو جزء أساسي من Query Optimization.

قاعدة SARGability: لا تُغلّف العمود بدالة

إذا وضعت عمودًا داخل دالة في شرط WHERE، يستطيع المحرك استخدام الفهرس فقط إن كان الفهرس تعبيريًا بنفس الدالة. المثال التالي يوضح الفرق:

-- بطيء: الدالة تمنع استخدام الفهرس على created_at
SELECT id FROM orders WHERE DATE(created_at) = '2024-03-01';

-- سريع: مدى على العمود الخام
SELECT id FROM orders
WHERE created_at >= '2024-03-01'
  AND created_at <  '2024-03-02';

النسخة الثانية قابلة لاستخدام الفهرس (SARGable) لأنها تحوّل الشرط إلى نطاق على العمود الأصلي. نفس المبدأ ينطبق على UPPER وSUBSTRING وCAST وعلى العمليات الحسابية مثل price * 2 > 100.

تجنّب SELECT * في الاستعلامات المتكررة

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

EXISTS مقابل IN مقابل JOIN

غالبًا ما يكون EXISTS أوضح وأسرع عندما يكون الهدف التحقق من وجود صف مطابق، لأنه يوقف البحث عند أول مطابقة:

SELECT c.id, c.email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 500
);

أما IN مع قائمة فرعية كبيرة فقد ينتج خطة أقل كفاءة في بعض المحركات. القاعدة العامة: استخدم JOIN عندما تحتاج بيانات من الجدولين، وEXISTS عندما تحتاج مجرد تأكيد الوجود.

استبدال OR المتكرر على نفس العمود بـ IN أو UNION ALL

شروط OR الممتدة على نفس العمود قد تُنفَّذ كسلسلة مسحات. تحويلها إلى IN يسهّل استخدام الفهرس، وعند اختلاف الأعمدة قد يكون UNION ALL أنسب من UNION لأنه لا يحذف التكرارات ولا يضيف خطوة فرز.

الترقيم بالـ OFFSET والانتقال إلى Keyset Pagination

من أكثر الأنماط المدمرة في قواعد البيانات الكبيرة تصفّح الصفحات بـ OFFSET الكبير، لأنه يقرأ كل الصفوف السابقة ثم يهملها:

-- OFFSET يقرأ ويهمل 200000 صف قبل إرجاع 20
SELECT id, title FROM articles ORDER BY id LIMIT 20 OFFSET 200000;

-- Keyset: يبدأ من آخر معرّف تم رؤيته
SELECT id, title FROM articles
WHERE id > 200000
ORDER BY id
LIMIT 20;

النسخة الثانية تظل ثابتة الزمن مهما تقدّم المستخدم في الصفحات، لأنها تعتمد على فهرس المفتاح الأساسي بدل العدّ من البداية.

الإحصاءات وتقدير عدد الصفوف (Cardinality Estimation)

المُحسِّن لا يعرف البيانات، بل يعرف الإحصاءات عنها. أي ضعف في الإحصاءات يعني خطة أسوأ، ومهما أضفت فهارس لن يتعافى الأداء. لهذا يُعد تحديث الإحصاءات ركنًا أساسيًا في Query Optimization في قواعد البيانات الكبيرة.

دور ANALYZE و VACUUM

يحدّث ANALYZE الإحصاءات التي يبني عليها المُحسِّن تقديراته، بينما يعيد VACUUM المساحات الناتجة عن الصفوف المحذوفة والمُحدَّثة. في أنظمة MVCC مثل PostgreSQL، الإهمال في التنظيف يضخّم الجداول ويبطئ كل استعلام.

ANALYZE orders;
VACUUM (ANALYZE, VERBOSE) orders;

بعد التنفيذ يستخدم المُحسِّن التوزيع الجديد للقيم ويصحّح تقديراته. لو كان الفرق بين الصفوف المتوقعة والفعليّة 15 ضعفًا كما رأينا سابقًا، فهذا الأمر كافٍ لإصلاحه في كثير من الحالات.

الإحصاءات الموسّعة للأعمدة المترابطة

المشكلة الكلاسيكية: المُحسِّن يفترض أن الأعمدة مستقلة عن بعضها، مثلًا أن city = 'Cairo' وcountry = 'EG' حدثان منفصلان، فيقلّل تقديره كثيرًا. الحل هو إنشاء إحصاءات موسّعة:

CREATE STATISTICS orders_customer_status (dependencies, ndistinct)
ON customer_id, status FROM orders;

ANALYZE orders;

هذا يجعل المحرك يدرك الارتباط بين العمودين ويختار خطة أدق. أما على الجداول الكبيرة جدًا، فيُنصح بضبط حجم العيّنة عبر default_statistics_target للأعمدة الحرجة فقط، لأن رفعها على كل الأعمدة يبطئ التحليل.

مؤشرات على مشكلة في الإحصاءات

تغيّر الخطة فجأة بعد عملية إدراج ضخمة أو حذف جماعي.
استعلام سريع صباحًا وبطيء مساءً بنفس المدخلات.
اختيار المحرك لـ Nested Loop متوقعًا 10 صفوف وهو يجد 500 ألف.
فهرس موجود ومطابق للشرط لكن لا يُستخدم إطلاقًا.

التقسيم (Partitioning) و Partition Pruning

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

CREATE TABLE events (
  id         bigserial,
  tenant_id  bigint NOT NULL,
  created_at timestamptz NOT NULL,
  payload    jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

عندما يرى المحرك شرطًا على created_at، فإنه يستبعد الأقسام غير المطابقة تمامًا من الخطة، وهي عملية تُسمى Partition Pruning.

شروط عمل Partition Pruning بفاعلية

أن يكون الشرط على عمود التقسيم نفسه وبتعبير مباشر بلا دوال.
أن يكون الشرط مدى صريحًا مثل >= و< بدل دوال مثل date_trunc.
أن تتوافق أنواع البيانات بين الشرط وعمود التقسيم.
عند استخدام أقسام فرعية، يُفضَّل عدد معقول من الأقسام لتقليل زمن التخطيط.

التقسيم لا يغني عن الفهارس

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

ميزة إضافية مهمة: حذف الأقسام القديمة عملية فورية تقريبًا عند استخدام DROP PARTITION أو DETACH PARTITION، بخلاف DELETE على ملايين الصفوف الذي يُنتج تضخمًا وضغطًا على التنظيف.

تحسين استعلامات JOIN في قواعد البيانات الكبيرة

الربط بين الجداول هو المكان الذي تنفجر فيه التكلفة حين يزداد حجم البيانات، لأن تركيبة صغيرة من الصفوف قد تتحول إلى ملايين المقارنات. فهم خوارزميات الربط يساعدك على توجيه المُحسِّن بدل مقاومته.

خوارزميات الربط الثلاث

Nested Loop: مناسب عندما يكون أحد الجانبين صغيرًا جدًا ومفهرسًا الجانب الآخر.
Hash Join: مناسب للجداول الكبيرة المتقاربة في الحجم مع ذاكرة كافية.
Merge Join: مناسب عندما يكون الجانبان مرتبين مسبقًا بفهرس أو نتيجة فرز.

المثال التالي يجمع العملاء بعدد طلباتهم في بلد معيّن مع شرط على عدد الطلبات:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.name, COUNT(o.id) AS orders_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'EG'
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 5;

الحكم على الخطة يعتمد على العلاقة بين جدول البلدان وجدول الطلبات: لو كان عدد العملاء في مصر صغيرًا (آلاف) والطلبات ضخمة (ملايين)، فالخطة المثلى غالبًا Nested Loop مدعوم بفهرس على orders.customer_id. ولو كان نصف العملاء في مصر، فالمسح والربط بالتجزئة أنسب.

قواعد عملية لتسريع JOIN

فهرسة أعمدة الربط على الجانب الأكبر دائمًا.
تصفية كل جدول قبل الربط بدل الربط ثم التصفية.
تجنّب الربط على أعمدة من أنواع مختلفة لأن ذلك يمنع استخدام الفهرس أو يفرض تحويلًا ضمنيًا.
احذر الربط الديكارتي الناتج عن نسيان شرط في WHERE؛ قد لا يشتكي النظام لكن النتيجة تكون كارثية الحجم.

التجميع والترتيب والنوافذ (GROUP BY / ORDER BY / Window Functions)

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

ضبط work_mem بحذر

SET LOCAL work_mem = '256MB';
SELECT tenant_id, COUNT(*)
FROM events
WHERE created_at >= '2024-01-01'
GROUP BY tenant_id
ORDER BY COUNT(*) DESC
LIMIT 10;

يُستخدم SET LOCAL داخل معاملة ليطبَّق الحد على الاستعلام الحالي فقط. رفع work_mem لكل الاتصالات في وقت واحد خطر لأنه يضرب في عدد الاتصالات المتزامنة، وقد يستهلك ذاكرة الخادم بالكامل.

الترتيب عبر الفهرس بدل خطوة Sort

عندما يتوافق ORDER BY مع ترتيب فهرس موجود، يتجنب المحرك خطوة الفرز كليًا. لهذا يكون الفهرس على (tenant_id, created_at DESC) قيمًا جدًا للاستعلامات التي تُرتّب الأحدث لكل مستأجر.

الدوال النافذة وتكلفتها الخفية

استخدام ROW_NUMBER() أو RANK() على ملايين الصفوف يتطلب فرزًا كاملًا. إذا كان الهدف إزالة التكرارات أو اختيار أحدث صف لكل مجموعة، فغالبًا يوجد بديل أسرع مثل DISTINCT ON في PostgreSQL أو ربط مع جدول تجميعي محسوب مسبقًا.

التخزين المؤقت وتقليل عدد الاستعلامات

أفضل استعلام هو الذي لا يُنفَّذ. في قواعد البيانات الكبيرة، تقليل عدد الاستعلامات وتخزين نتائجها أكثر فاعلية من تحسين كل استعلام على حدة.

الاستعلامات المُجهَّزة (Prepared Statements)

الاستعلام المُجهَّز يوفّر إعادة التحليل والتخطيط في كل تنفيذ، وهو مفيد بشكل خاص للاستعلامات المتكررة بمعاملات مختلفة:

PREPARE order_by_customer (bigint, timestamptz) AS
SELECT id, total, created_at
FROM orders
WHERE customer_id = $1 AND created_at >= $2
ORDER BY created_at DESC
LIMIT 50;

EXECUTE order_by_customer(84213, '2024-01-01');

ملاحظة مهمة: التخطيط العام (Generic Plan) يعتمد على متوسط التوزيع، وقد يكون أسوأ من التخطيط المخصص عندما تكون البيانات منحرفة. بعض المحركات تقرر تلقائيًا بين الخطتين بعد عدة تنفيذات، ويمكنك فحص ذلك عند الحاجة.

العروض المادية (Materialized Views)

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

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created_at) AS day,
       tenant_id,
       SUM(total) AS revenue,
       COUNT(*)   AS orders
FROM orders
WHERE status = 'paid'
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON mv_daily_sales (day, tenant_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

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

طبقات التخزين المؤقت في التطبيق

تخزين نتائج الاستعلامات المتكررة في ذاكرة وسيطة مثل Redis مع صلاحية زمنية واضحة.
استخدام أزمنة صلاحية متفاوتة لمنع انتهاء كل المفاتيح في اللحظة نفسها.
إبطال المفتاح عند الكتابة، لا الاعتماد على انتهاء الصلاحية فقط للبيانات الحساسة.
قياس نسبة الإصابة (Hit Ratio) لتعرف إن كانت الطبقة تفيد فعليًا.

المعاملات ومستويات العزل وأثرها على الأداء

المعاملة الطويلة تحمل أقفالًا وتمنع التنظيف من تحرير الصفوف القديمة، مما يضخّم الجداول ويبطئ الاستعلامات بشكل غير مباشر. كثير من مشاكل Query Optimization في قواعد البيانات الكبيرة أصلها معاملات متروكة مفتوحة.

أخطاء شائعة في إدارة المعاملات

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

التقسيم إلى دفعات

BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'A-100';
UPDATE inventory SET qty = qty - 1 WHERE sku = 'A-101';
COMMIT;

في عمليات التحديث الجماعي، تنفيذ دفعات بحجم 1000 إلى 5000 صف مع التزام بعد كل دفعة يحافظ على حجم سجلات التراجع معقولًا ويقلل زمن الاحتفاظ بالأقفال. المثل ينطبق على الحذف الجماعي، حيث يُفضَّل حذف الأرشيف وإسقاط الأقسام بدل حذف صفوف فردية.

المهل الزمنية كخط دفاع

SET statement_timeout = '5s';
SET lock_timeout = '2s';
SET idle_in_transaction_session_timeout = '30s';

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

التوسع: النسخ القرائية وتجميع الاتصالات والتجزئة

بعد استنفاد التحسين على مستوى الاستعلام والفهرس، يصبح السؤال معماريًا: كيف نوزّع الحمل؟ الإجابة تعتمد على شكل الوصول للبيانات.

تجميع الاتصالات (Connection Pooling)

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 40
server_idle_timeout = 60

الوضع transaction يعيد الاتصال إلى المجمع بعد كل معاملة، وهو الأنسب لمعظم تطبيقات الويب، شرط عدم استخدام ميزات مرتبطة بالجلسة مثل الجداول المؤقتة أو الإعدادات المحلية الدائمة.

النسخ القرائية (Read Replicas)

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

التجزئة (Sharding)

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

متى تلجأ لكل حل؟

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

اعتبارات الأمان في تحسين الاستعلامات

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

لا تدمج مدخلات المستخدم في نص الاستعلام

# خطر: بناء النص مباشرة من مدخل المستخدم
query = "SELECT * FROM users WHERE email = '" + email + "'"

البديل الصحيح استخدام المعاملات (Parameters) التي تفصل النص عن البيانات، فتمنع الحقن وتسمح أيضًا بإعادة استخدام خطة التنفيذ:

import psycopg

def get_orders(conn, customer_id, since):
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, total, created_at
            FROM orders
            WHERE customer_id = %s AND created_at >= %s
            ORDER BY created_at DESC
            LIMIT 50
            """,
            (customer_id, since),
        )
        return cur.fetchall()

في هذا النموذج لا يمكن للمدخل أن يغيّر بنية الاستعلام، ويستفيد المحرك من التخطيط المُعاد استخدامه. أعِد دائمًا قائمة أعمدة محددة، ولا تُرجع أعمدة حساسة ككلمات المرور أو رموز الوصول.

أمان على مستوى الصفوف وأثر الاستعلام

تفعيل Row Level Security لجعل قاعدة البيانات نفسها تفرض فلترة حسب المستأجر أو المستخدم.
مراجعة كل استعلام يستخدم فهارس أعمدة حساسة للتأكد من عدم تسريب البيانات عبر التوقيت.
حصر صلاحيات التطبيق في الحد الأدنى: قراءة وكتابة فقط على ما يحتاجه، بلا صلاحيات إنشاء أو إسقاط.
عدم كشف خطط التنفيذ أو رسائل الأخطاء التفصيلية لمستخدمي الإنتاج.
تحديد مهل زمنية لمنع استهلاك الموارد العمدي من قبل مدخلات ضخمة.

أثر RLS على الأداء

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

CREATE INDEX idx_orders_tenant_created
ON orders (tenant_id, created_at DESC);

أخطاء شائعة في Query Optimization وكيف تتجنبها

إضافة فهارس بلا قياس: أضف فهرسًا واحدًا، قِس الأثر، ثم قرّر. الفهارس المتراكمة تبطئ الكتابة.
تجاهل تحديث الإحصاءات: بعد كل تحميل بيانات كبير، شغّل ANALYZE أو تأكد من عمل التجميع التلقائي.
تحسين الاستعلام الخطأ: ابدأ من pg_stat_statements أو سجل الاستعلامات البطيئة لتعرف أكثر الاستعلامات استهلاكًا.
استخدام OFFSET الكبير: انتقل إلى الترقيم بالمفاتيح.
تغليف الأعمدة بالدوال: يجعل الشرط غير قابل لاستخدام الفهرس.
الاعتماد على ذاكرة افتراضية غير كافية: خطوات الفرز والتجميع تخرج إلى القرص بصمت.
نسيان أثر الأمان: إضافة فهرس مع RLS غير متوافق تؤدي إلى تحسين ظاهري وتراجع فعلي.
اختبار الأداء على بيانات صغيرة: استخدم نسخة بحجم قريب من الإنتاج قبل الحكم على أي خطة.

أفضل الممارسات لتحسين الاستعلامات في قواعد البيانات الكبيرة

حدّد هدفًا رقميًا واضحًا لكل استعلام: زمن استجابة مستهدف، وعدد استدعاءات في الدقيقة.
قِس أولًا باستخدام خطط التنفيذ الفعلية وجمع الإحصاءات من pg_stat_statements أو ما يعادله.
حسّن الاستعلام قبل إضافة الفهرس، ثم أضف فهرسًا واحدًا واختبر أثره.
اجعل شروط WHERE مكتوبة على الأعمدة الخام بلا دوال لتحافظ على قابليتها للفهرسة.
صمّم الفهارس المركبة بترتيب: مساواة، ثم مدى، ثم ترتيب، وراعِ أعمدة الربط.
استخدم الفهارس المغطية للاستعلامات الأكثر تكرارًا وحساسية للزمن.
قسّم الجداول الكبيرة زمنيًا واعتمد على استبعاد الأقسام في كل استعلام تحليلي.
احتفظ بمعاملات قصيرة، وقسّم العمليات الجماعية إلى دفعات.
خزّن النتائج الثقيلة مؤقتًا واحسب مسبقًا ما يُطلب كثيرًا.
اضبط مهلًا زمنية وحدود موارد تمنع الاستعلامات الشاردة من إسقاط النظام.
راقب التغييرات في الخطط بين الإصدارات وعند تغيّر حجم البيانات.
أضف اختبارات أداء آلية لأهم الاستعلامات في خط النشر للتكشف المبكر عن أي تراجع.

حالات واقعية في تحسين الأداء

منصة تجارة إلكترونية: صفحة طلبات العميل

الاستعلام كان يقرأ كل طلبات العميل ثم يرتبها ثم يأخذ 20 صفًا. الحل كان فهرسًا مركبًا على (customer_id, created_at DESC) مع تحديد الأعمدة المطلوبة فقط، فانتقل الزمن من أكثر من ثانية إلى أقل من ملي ثانية.

لوحة تحليلات: تقارير يومية على ملايين الأحداث

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

تطبيق متعدد المستأجرين: بطء يتفاقم مع النمو

تبيّن أن كل الاستعلامات تفلتر بـ tenant_id لكن لا يوجد فهرس يبدأ به. إضافة الفهرس على (tenant_id, ...) في الجداول الأساسية أعادت الأداء إلى مستواه، وأصبح متوافقًا مع أمان مستوى الصفوف.

نظام إنترنت أشياء: كتابة كثيفة وقراءة زمنية

الحل كان فصل مسار الكتابة عن مسار القراءة، مع فهارس BRIN على أعمدة الزمن لتقليل حجم الفهارس، وتجميع القياسات في جدول ملخّص بدل الاستعلام من الجدول الخام في كل مرة.

مراقبة الأداء والقياس المستمر

التحسين ليس مهمة تُنجَز مرة واحدة، لأن البيانات تتغير والخطط تتغير معها. المراقبة المستمرة هي ما يحفظ مكاسب Query Optimization في قواعد البيانات الكبيرة على المدى الطويل.

أهم الاستعلامات استهلاكًا

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 80) AS sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

ترتيب النتائج بحسب total_exec_time يكشف الاستعلامات التي تستهلك أكبر حصة، وهي أولوية التحسين الحقيقية حتى لو لم تكن الأبطأ لحظيًا. راقب أيضًا rows وmean_exec_time لاكتشاف التغيرات التدريجية.

قياسات يجب متابعتها

نسبة إصابة ذاكرة التخزين المؤقت لقاعدة البيانات (Buffer Cache Hit Ratio).
عدد المسحات الكاملة على الجداول الضخمة يوميًا.
عدد المعاملات الطويلة وعمر أطول معاملة مفتوحة.
نسبة الاستعلامات التي تجاوزت حدًا زمنيًا محددًا.
حجم الجداول والفهارس وتضخمها (Bloat).
تأخر النسخ القرائية ومعدل إعادة الاتصال.

اختبار التراجع قبل النشر

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

Frequently Asked Questions About Query Optimization in Large Databases

ما الفرق بين Query Optimization والفهرسة؟

الفهرسة جزء واحد من Query Optimization، وليست مرادفة لها. Query Optimization تشمل تحليل خطة التنفيذ، وإعادة كتابة الاستعلام، وتحديث الإحصاءات، والتقسيم، والتخزين المؤقت، وإدارة المعاملات والاتصالات. قد يكون الحل الأفضل في كثير من الحالات إعادة كتابة الاستعلام فقط دون إضافة أي فهرس.

هل إضافة فهارس كثيرة تسرّع قاعدة البيانات دائمًا؟

لا. كل فهرس يضيف تكلفة على عمليات الإدراج والتحديث والحذف، ويستهلك مساحة وذاكرة تخزين مؤقت. الفهارس المتقاربة في تعريفها تكرار بلا فائدة. القاعدة العملية: احتفظ بالفهارس التي يثبت القياس أنها تُستخدم فعليًا وتُحسّن استعلامات مهمة، واحذف ما لا يُستخدم.

كيف أعرف الاستعلام الأبطأ في قاعدة البيانات؟

استخدم امتداد pg_stat_statements في PostgreSQL أو سجل الاستعلامات البطيئة في MySQL، ورتّب النتائج بحسب الزمن الإجمالي لا المتوسط فقط. الاستعلام الذي يعمل في 20 ملي ثانية ولكنه يُنفَّذ 100 ألف مرة أكثر تأثيرًا من استعلام يعمل 5 ثوانٍ مرة واحدة.

متى أستخدم عرضًا ماديًا بدل فهرس؟

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

هل التقسيم يغني عن الفهارس؟

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

لماذا يصبح استعلامي بطيئًا فجأة بعد أسابيع من العمل بشكل جيد؟

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

ما أفضل طريقة لتحسين استعلام JOIN بطيء؟

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

خطواتك التالية لتحسين الاستعلامات في قواعد بياناتك

Query Optimization في قواعد البيانات الكبيرة ليس مهارة تُتعلَّم مرة واحدة، بل عملية دورية من القياس والتحسين والمراقبة. المكسب الحقيقي لا يأتي من تطبيق كل تقنية في وقت واحد، بل من تطبيق التقنية الصحيحة على الاستعلام الصحيح.

ابدأ اليوم بخطوات محددة ومرتبة:

شغّل تقرير أكثر الاستعلامات استهلاكًا خلال آخر أسبوع وسجّل أهم عشرة استعلامات.
خذ الاستعلام الأول ونفّذ عليه EXPLAIN ANALYZE مع خيارات الذاكرة المؤقتة.
ابحث عن أول عقدة تستهلك أكبر زمن أو أكبر عدد صفحات، وركّز عليها وحدها.
تحقق من مطابقة الفهارس لشروط WHERE والترتيب، وأضف فهرسًا واحدًا عند الحاجة وقس الأثر.
راجع الإحصاءات، وشغّل ANALYZE على الجداول التي تغيّرت كثيرًا.
أعد كتابة الشروط التي تغلّف الأعمدة بالدوال، وحوّلها إلى نطاقات مباشرة.
أضف مهلًا زمنية، وأصلح المعاملات الطويلة، وقسّم العمليات الجماعية إلى دفعات.
وثّق كل تحسين مع القياسات قبل وبعد، لتبني قاعدة معرفة خاصة بنظامك.

تذكّر القاعدة الذهبية: قِس، حسّن نقطة واحدة، ثم قِس مرة أخرى. هذا الأسلوب المنضبط هو ما يفرّق بين تحسين حقيقي مستدام وبين تغييرات عشوائية تُخفّف البطء مؤقتًا ثم تعود به أقوى بعد أسابيع.

#query optimization #تحسين الاستعلامات #قواعد البيانات الكبيرة #أداء قواعد البيانات #postgresql #فهارس قواعد البيانات #خطة التنفيذ #تقسيم الجداول #sql performance #database tuning #indexing #تحسين أداء الاستعلامات