عندما تتعطل قاعدة البيانات: كيف حولنا استعلام SQL من 12 ثانية إلى 80 ميلي ثانية؟

كنت أراقب شاشة المراقبة في الساعة الثالثة صباحاً، عندما رأيت مؤشر الاستجابة يتحول إلى اللون الأحمر. استعلام واحد بسيط كان يستهلك 95% من موارد قاعدة البيانات، ويعلق السيرفر بالكامل لمدة 12 ثانية في كل تنفيذ. المشكلة لم تكن في حجم البيانات - لدينا فقط 3 ملايين سجل - بل في الطريقة التي كنا نستجوب بها تلك البيانات. في هذا المقال، سأريك بالضبط كيف حولنا هذا الاستعلام الكارثي إلى شيء يطير بسرعة 80 ميلي ثانية، مع قياسات حقيقية لكل خطوة تحسين قمنا بها.

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

الاستعلام القاتل: النسخة الأصلية قبل التحسين

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

-- الاستعلام الأصلي الذي كان يسبب الكارثة
SELECT a.id, a.title, a.slug, a.published_at, c.name as category,
 GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') as tags,
 u.name as author
FROM articles a
JOIN users u ON a.author_id = u.id
JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tags at ON a.id = at.article_id
LEFT JOIN tags t ON at.tag_id = t.id
WHERE a.published_at BETWEEN '2023-01-01' AND '2023-12-31'
 AND a.status = 'published'
 AND (c.slug = 'technology' OR c.parent_id IN (SELECT id FROM categories WHERE slug = 'technology'))
 AND (t.name IN ('database', 'performance', 'optimization') OR a.title LIKE '%database%')
GROUP BY a.id
ORDER BY a.published_at DESC
LIMIT 50 OFFSET 0;

عندما قمنا بتشغيل EXPLAIN ANALYZE على هذا الاستعلام، كانت النتيجة مفزعة. قاعدة البيانات كانت تقوم بثلاث عمليات مسح كامل للجداول (full table scans) على articles و categories و tags، مع JOINs مكلفة جداً. المشكلة الرئيسية كانت في الشرط المركب على categories الذي يستخدم OR مع استعلام فرعي، بالإضافة إلى استخدام LIKE مع wildcard في بداية النص، مما يمنع استخدام الفهارس بشكل فعال. أيضاً، GROUP_CONCAT كان يسبب مشكلة في الذاكرة لأنه كان يجمع عشرات الآلاف من السجلات قبل تطبيق GROUP BY.

القياس الأولي باستخدام أداة Performance Schema في MySQL أظهر أن هذا الاستعلام كان يستهلك 1.2 جيجابايت من الذاكرة المؤقتة في كل تنفيذ، وكان يسبب زيادة في I/O بمعدل 45 ميجابايت في الثانية. هذا يعني أن كل تنفيذ للاستعلام كان يقرأ تقريباً كامل الجداول الثلاثة من القرص، حتى مع وجود فهارس مناسبة. في بيئة الإنتاج، كان هذا يعني أن السيرفر كان يتعطل تماماً عندما يصل عدد الطلبات المتزامنة إلى 20 طلب فقط.

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

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

-- إضافة فهارس مركبة لتحسين الأداء
CREATE INDEX idx_articles_status_published ON articles(status, published_at);
CREATE INDEX idx_articles_category_status ON articles(category_id, status, published_at);
CREATE INDEX idx_categories_slug_parent ON categories(slug, parent_id);
CREATE INDEX idx_tags_name ON tags(name);
CREATE INDEX idx_article_tags_article_tag ON article_tags(article_id, tag_id);

ثم قمنا بإعادة كتابة الشرط المعقد على categories باستخدام JOIN بدلاً من الاستعلام الفرعي. الاستعلامات الفرعية في WHERE غالباً ما تكون مكلفة لأنها تنفذ لكل صف في الجدول الرئيسي. استبدلناها بـ LEFT JOIN مع DISTINCT لتجنب تكرار البيانات:

-- النسخة المحسنة بعد إصلاح الفهارس وإزالة الاستعلامات الفرعية
SELECT a.id, a.title, a.slug, a.published_at, c.name as category,
 GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') as tags,
 u.name as author
FROM articles a
JOIN users u ON a.author_id = u.id
JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tags at ON a.id = at.article_id
LEFT JOIN tags t ON at.tag_id = t.id
LEFT JOIN categories tech_cat ON (c.slug = 'technology' OR c.parent_id = tech_cat.id)
 AND tech_cat.slug = 'technology'
WHERE a.published_at BETWEEN '2023-01-01' AND '2023-12-31'
 AND a.status = 'published'
 AND tech_cat.id IS NOT NULL
 AND (t.name IN ('database', 'performance', 'optimization') OR a.title LIKE '%database%')
GROUP BY a.id
ORDER BY a.published_at DESC
LIMIT 50 OFFSET 0;

بعد هذا التحسين، انخفض زمن التنفيذ من 12.3 ثانية إلى 4.7 ثانية - تحسن بنسبة 62%، لكن هذا لا يزال غير كافٍ. EXPLAIN أظهر أن قاعدة البيانات بدأت تستخدم الفهارس الجديدة، لكن JOIN مع categories كان لا يزال مكلفاً، وكان LIKE '%database%' يمنع استخدام الفهارس على title. أيضاً، GROUP_CONCAT كان لا يزال يجمع الكثير من البيانات قبل التجميع.

الخطوة الثانية: تقسيم الاستعلام ومعالجة LIKE بشكل ذكي

الخطوة التالية كانت معالجة الشرط LIKE الذي يستخدم wildcard في البداية. هذا النوع من الشروط لا يمكن تحسينه باستخدام فهارس B-tree التقليدية، لذلك قررنا استخدام تقنية مختلفة: فهرسة النص الكامل مع البحث في الكلمات الأساسية. في MySQL، يمكننا استخدام FULLTEXT index للبحث في النص بكفاءة عالية.

-- إضافة فهرس نص كامل للبحث في العناوين
ALTER TABLE articles ADD FULLTEXT INDEX ft_articles_title (title);

-- إعادة كتابة الاستعلام باستخدام MATCH AGAINST بدلاً من LIKE
SELECT a.id, a.title, a.slug, a.published_at, c.name as category,
 GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') as tags,
 u.name as author
FROM articles a
JOIN users u ON a.author_id = u.id
JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tags at ON a.id = at.article_id
LEFT JOIN tags t ON at.tag_id = t.id
LEFT JOIN categories tech_cat ON (c.slug = 'technology' OR c.parent_id = tech_cat.id)
 AND tech_cat.slug = 'technology'
WHERE a.published_at BETWEEN '2023-01-01' AND '2023-12-31'
 AND a.status = 'published'
 AND tech_cat.id IS NOT NULL
 AND (t.name IN ('database', 'performance', 'optimization') OR MATCH(a.title) AGAINST('database' IN BOOLEAN MODE))
GROUP BY a.id
ORDER BY a.published_at DESC
LIMIT 50 OFFSET 0;

هذا التحسين خفض زمن التنفيذ إلى 1.8 ثانية - تحسن إضافي بنسبة 62%. لكننا لاحظنا أن GROUP_CONCAT كان لا يزال يمثل عنق الزجاجة. المشكلة أن الاستعلام كان يجمع كل المقالات التي تطابق الشروط، ثم يطبق GROUP_CONCAT عليها، ثم يرتبها، ثم يطبق LIMIT. هذا يعني أنه كان يعالج مئات الآلاف من السجلات قبل تصفية الـ 50 نتيجة الأولى.

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