MySQL وتصميم قواعد البيانات
أساسيات تحسين الاستعلامات
أساسيات تحسين الاستعلامات
تحسين الاستعلامات هو عملية تحسين أداء استعلامات قاعدة البيانات لجعلها تنفذ بشكل أسرع وتستخدم موارد أقل. في هذا الدرس، ستتقن جملة EXPLAIN وتتعلم كيفية تحديد وإصلاح الاستعلامات البطيئة.
جملة EXPLAIN
EXPLAIN هي أداتك الأساسية لفهم كيف تنفذ MySQL الاستعلام. إنها تظهر خطة تنفيذ الاستعلام دون تشغيل الاستعلام فعلياً:
-- صيغة EXPLAIN الأساسية
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
-- EXPLAIN مع استعلام معقد
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.customer_id
WHERE u.country = 'USA'
GROUP BY u.id;
نصيحة احترافية: قم دائماً بتشغيل EXPLAIN على أي استعلام تشك في أنه قد يكون بطيئاً. يستغرق فقط ملي ثانية ويوفر رؤى لا تقدر بثمن حول أداء الاستعلام.
فهم مخرجات EXPLAIN
تعيد EXPLAIN عدة أعمدة تصف كيف ستنفذ MySQL استعلامك:
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
| 1 | SIMPLE | users | ref | idx_email | idx | 767 | const | 1 | NULL |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
دعنا نحلل كل عمود:
1. id - معرّف الاستعلام
-- استعلام بسيط
id = 1
-- استعلام فرعي
id = 1 (الاستعلام الخارجي)
id = 2 (الاستعلام الداخلي)
-- Union
id = 1 (SELECT الأول)
id = 2 (SELECT الثاني)
id = NULL (نتيجة UNION)
2. select_type - نوع SELECT
SIMPLE: SELECT بسيط (بدون استعلامات فرعية أو unions)
PRIMARY: SELECT الخارجي في استعلام معقد
SUBQUERY: استعلام فرعي في SELECT أو WHERE
DERIVED: جدول مشتق (استعلام فرعي في FROM)
UNION: SELECT الثاني أو اللاحق في UNION
UNION RESULT: نتيجة UNION
3. table - الجدول الذي يتم الوصول إليه
-- يوضح أي جدول يتم قراءته
table = users
table = orders
table = <derived2> (جدول مشتق)
4. type - نوع الانضمام (الأكثر أهمية)
يوضح عمود "type" كيف تصل MySQL إلى الصفوف. من الأفضل إلى الأسوأ:
system: الجدول يحتوي على صف واحد فقط (سريع جداً)
const: صف واحد مطابق على الأكثر (بحث PRIMARY KEY أو UNIQUE)
مثال: WHERE id = 123
eq_ref: صف واحد لكل مجموعة من الجداول السابقة (JOIN على PRIMARY/UNIQUE)
مثال: JOIN orders ON users.id = orders.customer_id
ref: عدة صفوف بقيمة فهرس مطابقة (أداء جيد)
مثال: WHERE country = 'USA' (مع فهرس)
range: فهرس يستخدم لعمليات النطاق (أداء لا بأس به)
مثال: WHERE age BETWEEN 18 AND 30
index: فحص فهرس كامل (بطيء - يفحص الفهرس بالكامل)
مثال: SELECT email FROM users (فهرس مغطي)
ALL: فحص جدول كامل (بطيء جداً - تجنب!)
مثال: WHERE country = 'USA' (بدون فهرس)
تنبيه أداء: إذا رأيت type = "ALL" على جدول كبير، فهذه إشارة حمراء! الاستعلام يفحص كل صف ويحتاج إلى تحسين.
5. possible_keys - الفهارس التي يمكن استخدامها
-- يوضح الفهارس التي قد تفكر MySQL في استخدامها
possible_keys = idx_email,idx_country
-- NULL تعني عدم وجود فهرس مناسب
possible_keys = NULL
6. key - الفهرس المستخدم فعلياً
-- يوضح أي فهرس اختارت MySQL
key = idx_email ✓ جيد (يتم استخدام الفهرس)
key = NULL ✗ سيء (لا يتم استخدام فهرس)
7. key_len - طول الفهرس المستخدم
-- يوضح عدد البايتات من الفهرس المستخدمة
-- مفيد للفهارس المركبة
-- الفهرس: idx_country_city (country, city)
key_len = 152 (country فقط مستخدم)
key_len = 458 (كل من country و city مستخدمان)
-- key_len أعلى للفهرس المركب = يتم استخدام المزيد من الفهرس
8. ref - الأعمدة المقارنة بالفهرس
-- يوضح ما يتم مقارنته بالفهرس
ref = const (قيمة ثابتة: WHERE id = 123)
ref = dbname.orders.customer_id (عمود من جدول آخر في JOIN)
ref = func (نتيجة دالة)
9. rows - الصفوف المقدرة للفحص
-- العدد المقدر من الصفوف التي يجب على MySQL فحصها
rows = 1 ✓ ممتاز (بحث دقيق)
rows = 100 ✓ جيد (مجموعة فرعية صغيرة)
rows = 10000 ⚠ تحذير (يحتاج إلى مراجعة)
rows = 1000000 ✗ ضعيف (فحص جدول كامل على الأرجح)
-- الأقل هو الأفضل!
10. Extra - معلومات إضافية
مؤشرات جيدة:
"Using index" - فهرس مغطي (لا حاجة للوصول إلى الجدول) ✓
"Using index condition" - تحسين دفع الفهرس ✓
"Using where" - تصفية جملة WHERE ✓
مؤشرات تحذير:
"Using filesort" - يحتاج إلى ترتيب خارجي (أضف فهرساً لـ ORDER BY) ⚠
"Using temporary" - يحتاج إلى جدول مؤقت (مكلف) ⚠
مؤشرات سيئة:
"Using where; Using join buffer" - لا فهرس للانضمام (أضف فهرساً!) ✗
"Range checked for each record" - لم يتم العثور على فهرس جيد ✗
أمثلة EXPLAIN من العالم الحقيقي
مثال 1: استعلام محسّن
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
+----+--------+-------+-------+----------------+-----------+---------+-------+------+-------+
| id | type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------+-------+-------+----------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | idx_email | idx_email | 767 | const | 1 | NULL |
+----+--------+-------+-------+----------------+-----------+---------+-------+------+-------+
التحليل: ممتاز ✓
- type = "const" (أسرع ما يمكن)
- key = idx_email (يتم استخدام الفهرس)
- rows = 1 (تم فحص صف واحد بالضبط)
- لا تحذيرات في Extra
مثال 2: استعلام غير محسّن
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
+----+--------+-------+------+---------------+------+---------+------+--------+-------------+
| id | type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 500000 | Using where |
+----+--------+-------+------+---------------+------+---------+------+--------+-------------+
التحليل: سيء جداً ✗
- type = "ALL" (فحص جدول كامل)
- key = NULL (لا يتم استخدام فهرس)
- rows = 500000 (فحص الجدول بالكامل)
- المشكلة: الدالة على العمود المفهرس تمنع استخدام الفهرس
الإصلاح: إعادة كتابة الاستعلام
-- سيء: استخدام دالة على العمود
WHERE YEAR(created_at) = 2024
-- جيد: المقارنة بدون دالة
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
-- الآن يمكن استخدام الفهرس!
مثال 3: JOIN بدون فهرس
EXPLAIN
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.customer_id
WHERE u.country = 'USA';
+----+--------+--------+------+---------------+-------------+---------+----------------+-------+------------------+
| id | type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------+--------+------+---------------+-------------+---------+----------------+-------+------------------+
| 1 | SIMPLE | u | ref | idx_country | idx_country | 152 | const | 5000 | NULL |
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 100000| Using where;join |
+----+--------+--------+------+---------------+-------------+---------+----------------+-------+------------------+
التحليل: محسّن جزئياً ⚠
- جدول users: جيد (يستخدم idx_country)
- جدول orders: سيء (فحص جدول كامل، لا فهرس على customer_id)
الإصلاح: إضافة فهرس
CREATE INDEX idx_customer_id ON orders(customer_id);
-- بعد إضافة الفهرس:
| 1 | SIMPLE | o | ref | idx_customer_id | idx_customer_id | 4 | u.id | 10 | NULL |
الآن type = "ref" و rows = 10 ✓
EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE يتجاوز EXPLAIN عن طريق تنفيذ الاستعلام فعلياً وإظهار بيانات التوقيت الحقيقية:
EXPLAIN ANALYZE
SELECT * FROM users WHERE country = 'USA';
-> Filter: (users.country = 'USA')
(cost=450.25 rows=5000)
(actual time=0.123..12.456 rows=4892 loops=1)
-> Table scan on users
(cost=450.25 rows=50000)
(actual time=0.089..10.234 rows=50000 loops=1)
معلومات رئيسية:
- cost: التكلفة المقدرة (مقياس MySQL الداخلي)
- rows: الصفوف المقدرة (قبل) مقابل الصفوف الفعلية (بعد التنفيذ)
- actual time: وقت التنفيذ الحقيقي بالملي ثانية
- loops: عدد مرات تنفيذ العملية
متى نستخدم EXPLAIN مقابل EXPLAIN ANALYZE:
- EXPLAIN: تحليل سريع بدون تشغيل الاستعلام (آمن للإنتاج)
- EXPLAIN ANALYZE: بيانات توقيت مفصلة لكنه ينفذ الاستعلام فعلياً (استخدم على بيانات الاختبار)
تحديد الاستعلامات البطيئة
توفر MySQL سجل استعلامات بطيئة لتتبع الاستعلامات التي تستغرق وقتاً طويلاً:
-- تمكين سجل الاستعلامات البطيئة
SET GLOBAL slow_query_log = 'ON';
-- تعيين العتبة (الاستعلامات الأبطأ من ثانيتين)
SET GLOBAL long_query_time = 2;
-- تعيين موقع ملف السجل
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
-- التحقق من الإعدادات الحالية
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
تحليل سجل الاستعلامات البطيئة:
-- عرض الاستعلامات البطيئة الأخيرة
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
-- استخدام أداة mysqldumpslow (سطر الأوامر)
$ mysqldumpslow /var/log/mysql/slow-query.log
-- يظهر الاستعلامات البطيئة الأكثر تكراراً:
Count: 45 Time=3.21s (144s) Lock=0.00s (0s) Rows=500.0 (22500)
SELECT * FROM orders WHERE customer_id = N
تقنيات تحسين الاستعلامات
1. تجنب الدوال على الأعمدة المفهرسة
-- سيء: الدالة تمنع استخدام الفهرس
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- جيد: إعادة الكتابة بدون دوال
SELECT * FROM users WHERE email = 'john@example.com';
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
2. استخدم LIMIT لمجموعات النتائج الكبيرة
-- سيء: يعيد جميع الصفوف (كثيف الذاكرة)
SELECT * FROM users ORDER BY created_at DESC;
-- جيد: حدد النتائج
SELECT * FROM users ORDER BY created_at DESC LIMIT 100;
3. حدد الأعمدة المطلوبة فقط
-- سيء: ينقل بيانات غير ضرورية
SELECT * FROM users WHERE country = 'USA';
-- جيد: حدد الأعمدة المطلوبة فقط
SELECT id, name, email FROM users WHERE country = 'USA';
-- أفضل: قد يستخدم فهرساً مغطياً
CREATE INDEX idx_country_id_name_email ON users(country, id, name, email);
4. استخدم EXISTS بدلاً من IN للاستعلامات الفرعية
-- أبطأ: IN مع استعلام فرعي
SELECT * FROM users
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
-- أسرع: EXISTS (يتوقف عند أول تطابق)
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = u.id AND o.total > 1000
);
5. تجنب SELECT DISTINCT عندما يكون ممكناً
-- بطيء: DISTINCT على مجموعة نتائج كبيرة
SELECT DISTINCT country FROM users;
-- أسرع: استخدم GROUP BY
SELECT country FROM users GROUP BY country;
-- الأسرع: إذا كنت تحتاج فقط للتحقق من الوجود
SELECT country FROM users GROUP BY country LIMIT 1;
تمرين عملي:
السيناريو: هذا الاستعلام يعمل ببطء على مدونة تحتوي على 100,000 مقالة:
SELECT
a.title,
a.content,
COUNT(c.id) as comment_count
FROM articles a
LEFT JOIN comments c ON a.id = c.article_id
WHERE MONTH(a.published_at) = 12
AND a.is_published = 1
GROUP BY a.id
ORDER BY comment_count DESC;
-- تشغيل EXPLAIN
EXPLAIN [الاستعلام أعلاه];
-- النتيجة تظهر:
+----+--------+----------+------+---------------+------+------+--------+------+--------------------------+
| id | type | table | type | possible_keys | key | rows | Extra |
+----+--------+----------+------+---------------+------+------+--------+------+--------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | 100000 | Using where; Using filesort |
| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | 500000 | Using where; Using temporary |
+----+--------+----------+------+---------------+------+------+--------+------+--------------------------+
الأسئلة:
- ما هي مشاكل الأداء؟
- كيف ستصلحها؟
الإجابات:
المشاكل المحددة:
1. type = "ALL" على كلا الجدولين (فحص جداول كاملة)
2. "Using filesort" (ORDER BY لا يستخدم فهرساً)
3. "Using temporary" (GROUP BY ينشئ جدولاً مؤقتاً)
4. MONTH(published_at) يمنع استخدام الفهرس
5. لا فهرس على comments.article_id لـ JOIN
الحلول:
-- الإصلاح 1: إزالة دالة MONTH()
WHERE a.published_at >= '2024-12-01'
AND a.published_at < '2025-01-01'
AND a.is_published = 1
-- الإصلاح 2: إضافة فهارس
CREATE INDEX idx_published ON articles(is_published, published_at);
CREATE INDEX idx_article_id ON comments(article_id);
-- الإصلاح 3: تحسين هيكل الاستعلام
SELECT
a.id,
a.title,
COUNT(c.id) as comment_count
FROM articles a
LEFT JOIN comments c ON a.id = c.article_id
WHERE a.published_at >= '2024-12-01'
AND a.published_at < '2025-01-01'
AND a.is_published = 1
GROUP BY a.id
ORDER BY comment_count DESC
LIMIT 20; -- تمت إضافة LIMIT
-- الإصلاح 4: النظر في البيانات المجمعة مسبقاً
-- إنشاء عمود comment_count في جدول articles
-- تحديثه باستخدام المشغلات أو المهام المجدولة لأداء أفضل
الملخص
في هذا الدرس، تعلمت:
- EXPLAIN يوضح كيف تنفذ MySQL الاستعلامات دون تشغيلها
- عمود "type" هو الأكثر أهمية: اهدف إلى const أو eq_ref أو ref
- type = "ALL" يشير إلى فحص جدول كامل (عادة يحتاج إلى تحسين)
- عمود "rows" يوضح عدد الصفوف التي سيتم فحصها
- EXPLAIN ANALYZE (MySQL 8.0.18+) يوفر توقيت التنفيذ الفعلي
- تمكين سجل الاستعلامات البطيئة لتحديد الاستعلامات المشكلة
- تجنب الدوال على الأعمدة المفهرسة - تمنع استخدام الفهرس
- أعد كتابة الاستعلامات لتكون صديقة للفهرس
- حدد دائماً الأعمدة التي تحتاجها فقط
التالي: في الدرس التالي، سنغوص أعمق في استراتيجيات تحسين الفهرس بما في ذلك الانتقائية وقاعدة البادئة اليسرى وتلميحات الفهرس!