MySQL: تحسين أداء MySQL

آخر تحديث: 2026-08-26

خلال فترة التخفيضات السنوية، شهدت منصة التجارة الإلكترونية الخاصة بشركة «أليس» ارتفاعًا حادًّا في زمن استجابة قاعدة البيانات من 50 مللي ثانية إلى 5 ثوانٍ، حيث اشتكى المستخدمون من عدم تحميل الصفحات ومن انتهاء مهلة إرسال الطلبات. أجرى فريق العمليات تحقيقًا عاجلًا واكتشف أن عدة استعلامات بطيئة غير مُحسَّنة كانت تتسبب في إبطاء النظام بأكمله بشكل كبير. ومن خلال تمكين تسجيل الاستعلامات البطيئة لتحديد عبارات SQL التي تنطوي على مشاكل، وتحليل خطط التنفيذ باستخدام EXPLAIN، وإضافة الفهارس المفقودة، وإعادة كتابة الاستعلامات غير الفعالة، وضبط معلمات تكوين InnoDB، تمكن الفريق في النهاية من تقليل وقت الاستجابة إلى أقل من 100 مللي ثانية.

1. ما ستتعلمه

• طرق تكوين سجلات الاستعلامات البطيئة وجمعها وتحليلها
• تحليل متعمق للحقول الموجودة في خطة التنفيذ EXPLAIN (type/key/rows/Extra)
• الاستراتيجيات الأساسية لتحسين الفهارس: تجنب الفهارس غير الصالحة، والفهارس الشاملة، والبادئة الموجودة في أقصى اليسار من الفهارس المركبة
• تقنيات إعادة صياغة الاستعلامات: تجنب SELECT *، واستبدال الاستعلامات الفرعية بـ JOIN، وتحسين الترقيم العميق للصفحات
• ضبط معلمات التكوين الرئيسية: innodb_buffer_pool_size، max_connections، وما إلى ذلك.

2. عملية تحسين الأداء من البداية إلى النهاية

تحسين الأداء ليس مهمة تُنفَّذ مرة واحدة، بل هو عملية دورية تتألف من «الاكتشاف → التحليل → التحسين → التحقق».

flowchart TD
    A[Identifying Slow Queries] --> B[EXPLAIN Analyze the Execution Plan]
    B --> C{Issue Type?}
    C -->|Missing Index| D[Index Optimization]
    C -->|Inefficient query syntax| E[Query Rewrite]
    C -->|Unreasonable configuration| F[Configuration Tuning]
    D --> G[Verifying Performance Improvements]
    E --> G
    F --> G
    G -->|Still does not meet the standards| A
    G -->|Meet the requirements| H[Deployment Monitoring]

flowchart TD
    A[Identifying Slow Queries] --> B[EXPLAIN Analyze the Execution Plan]
    B --> C{Issue Type?}
    C -->|Missing Index| D[Index Optimization]
    C -->|Inefficient query syntax| E[Query Rewrite]
    C -->|Unreasonable configuration| F[Configuration Tuning]
    D --> G[Verifying Performance Improvements]
    E --> G
    F --> G
    G -->|Still does not meet the standards| A
    G -->|Meet the requirements| H[Deployment Monitoring]

3. سجل الاستعلامات البطيئة

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

(1) البدء والتكوين

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

(2) أدوات تحليل السجلات

mysqldumpslow هي أداة تجميع سجلات الاستعلامات البطيئة المدمجة في MySQL، والتي تقوم بفرز الاستعلامات حسب وقت التنفيذ أو التكرار.

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

(3) البدائل لمخطط الأداء

يدعم MySQL 5.6 والإصدارات الأحدث استخدام «Performance Schema» لجمع الاستعلامات البطيئة، ويمكن تفعيل هذه الميزة ديناميكيًا دون الحاجة إلى تعديل ملف التكوين.

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

▶ مثال: تمكين سجل الاستعلامات البطيئة وتحديد أهم 5 استعلامات بطيئة

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 100;
SHOW VARIABLES LIKE 'slow_query_log_file';

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

▶ مثال: عرض الاستعلامات البطيئة بسرعة باستخدام عرض sys

SELECT query_id, LEFT(query, 80) AS query_text,
       exec_count, avg_timer_ms, rows_examined
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_timer_ms DESC LIMIT 10;

4. تحليل متعمق لخطة التنفيذ في EXPLAIN

تعد وظيفة EXPLAIN الأداة التشخيصية الأكثر أهمية في عملية تحسين أداء SQL؛ فهي توضح كيفية تنفيذ MySQL للاستعلامات.

(1) نظرة عامة على حقول مخرجات EXPLAIN

(2) شرح تفصيلي لحقل «type»

يُعد الحقل type الحقل الأكثر أهمية في EXPLAIN؛ فهو يعكس بشكل مباشر كفاءة الاستعلام.

(3) قيم مفاتيح الحقول الإضافية

• Using index: مؤشر التغطية؛ لا حاجة للرجوع إلى الجدول؛ أفضل أداء
• Using where: بعد أن يقوم محرك التخزين بإرجاع البيانات، يتم تصفية هذه البيانات بواسطة طبقة الخادم
• Using filesort: يتعذر الفرز باستخدام الفهرس؛ يلزم إجراء عملية فرز إضافية.
• Using temporary: تم استخدام جدول مؤقت؛ وهذا أمر شائع عندما لا يكون لعبارة GROUP BY فهرس.
• Using index condition: دفع الفهرس (ICP)، مما يقلل من عدد عمليات البحث في الجداول

▶ مثال: تحليل استعلام على جدول واحد باستخدام EXPLAIN

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE user_id = 42 AND status = 'PAID';

+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | Extra                     |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 4    |  120 | Using where               |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+

▶ مثال: تحليل استعلام مدمج باستخدام EXPLAIN

EXPLAIN SELECT o.order_id, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.create_time > '2025-01-01';

5. استراتيجيات تحسين الفهرس

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

(1) مؤشر التغطية

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

ALTER TABLE orders ADD INDEX idx_cover (user_id, status, total_amount);

يسترد الاستعلام عن SELECT user_id, status, total_amount FROM orders WHERE user_id = 42 البيانات بالكامل من الفهرس.

(2) المؤشرات المركبة والبادئة الموجودة في أقصى اليسار

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

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

(3) مرجع سريع لسيناريوهات فشل الفهرس

▶ مثال: التحقق من صحة البادئة الموجودة في أقصى اليسار لمؤشر مركب

ALTER TABLE products ADD INDEX idx_cat_brand_price (category_id, brand_id, price);

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND brand_id = 10;
EXPLAIN SELECT * FROM products WHERE brand_id = 10;

يحتوي الاستعلام الثاني على type من ALL لأنه يتخطى العمود الموجود في أقصى اليسار، category_id.

▶ مثال: استخدام فهرس تغطية لتجنب عمليات البحث في الجداول

ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, total_amount);

EXPLAIN SELECT user_id, status, total_amount
FROM orders WHERE user_id = 42;

إذا ظهر Using index في عمود «إضافي»، فهذا يعني أنه لا حاجة إلى البحث في الجدول.

6. تقنيات تحسين الاستعلامات

حتى مع وجود فهرس، لا تزال الاستعلامات المكتوبة بشكل سيئ غير قادرة على الاستفادة منه.

(1) تجنب استخدام SELECT *

تؤدي عبارة SELECT * إلى قراءة جميع الأعمدة، وتزيد من عمليات الإدخال/الإخراج (I/O)، وقد تؤدي إلى فقدان الفعالية للفهرس الشامل.

SELECT id, username, email FROM users WHERE id = 100;

(2) إعادة كتابة الاستعلامات الفرعية على شكل أوامر JOIN

يتم تنفيذ الاستعلام الفرعي ذي الصلة مرة واحدة لكل صف؛ ويمكن أن تؤدي إعادة صياغته على شكل JOIN إلى تقليل عدد عمليات المسح بشكل كبير.

SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE vip_level >= 3);

مُحسَّن لـ:

SELECT o.order_id, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id AND u.vip_level >= 3;

(3) التحسين المتعمق لترقيم الصفحات

يُظهر LIMIT offset, n التقليدي أداءً سيئًا للغاية عندما يكون الإزاحة كبيرة.

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

خطة التحسين — ترقيم الصفحات باستخدام المؤشر:

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

(4) مرجع سريع لتقنيات تحسين الاستعلامات

▶ مثال: مقارنة بين طرق تحسين الترقيم العميق للصفحات

SELECT * FROM orders ORDER BY create_time LIMIT 500000, 20;

مُحسَّن من أجل الارتباط المؤجل:

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 500000, 20) t
ON o.id = t.id;

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

▶ مثال: تحسين عمليات الإدراج المجمعة

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 201, 2), (1001, 202, 1), (1001, 203, 5);

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

7. تحسين بنية الجداول

يُعد تصميم هيكل الجدول بشكل جيد أساس الأداء؛ ولا يمكن لإعادة كتابة لغة SQL سوى تحسين الهيكل الحالي.

(1) اختيار نوع الحقل

(2) التصميم المضاد للنموذج السائد

في الحالات التي تتسم بارتفاع حجم الاستعلامات، يمكن أن يؤدي وجود قدر معتدل من التكرار إلى تقليل عدد عمليات JOIN.

CREATE TABLE order_summary (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(64),
    total_amount DECIMAL(10,2),
    INDEX idx_user (user_id)
);

قم بنسخ العمود username في جدول «orders» لتجنب الحاجة إلى إجراء عملية «JOIN» مع جدول users في كل استعلام.

▶ مثال: مقارنة بين طرق تحسين أنواع الحقول

ALTER TABLE products MODIFY COLUMN weight DECIMAL(8,2);
ALTER TABLE products MODIFY COLUMN description TEXT;

قم بتغيير العمود weight من VARCHAR إلى DECIMAL، وقم بتغيير العمود description من VARCHAR(5000) إلى TEXT لتقليل طول السجل الأساسي.

8. ضبط معلمات التكوين

تؤثر معلمات تكوين MySQL بشكل مباشر على سلوك محركات التخزين وتخصيص الموارد.

(1) معلمات التكوين الرئيسية والقيم الموصى بها

(2) الضبط الديناميكي للمعلمات

يمكن تعديل بعض المعلمات عبر الإنترنت دون الحاجة إلى إعادة تشغيل المثيل.

SET GLOBAL innodb_buffer_pool_size = 8589934592;
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

▶ مثال: التحقق من معدل نجاح الوصول إلى مخزن المؤقت لـ InnoDB الحالي

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 1024000   |
| Innodb_buffer_pool_reads              | 512       |
+---------------------------------------+-----------+

معدل النجاح = 1 - (512 / 1024000) ≈ 99.95٪؛ حجم مخزن التخزين المؤقت مناسب.

9. مقدمة عن مخطط الأداء

«Performance Schema» هو محرك مراقبة الأداء المدمج في MySQL، والذي يوفر بيانات تشخيصية أكثر تفصيلاً مقارنة بسجل الاستعلامات البطيئة.

(1) تمكين مخطط الأداء

SHOW VARIABLES LIKE 'performance_schema';
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

(2) طرق العرض الشائعة للرصد

▶ مثال: عرض الفهارس غير المستخدمة

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'ecommerce'
ORDER BY object_name;

تؤدي الفهارس غير المستخدمة إلى إهدار مساحة التخزين وتتطلب صيانة إضافية أثناء عمليات INSERT وUPDATE؛ لذا يجب إزالتها على الفور.

10. تمرين عملي شامل: عملية تشخيص الأداء الكاملة

باستخدام منصة التجارة الإلكترونية الخاصة بـ«أليس» كمثال، سنوضح العملية الكاملة بدءًا من تحديد الاستعلامات البطيئة وصولاً إلى التحقق من تحسينات الأداء.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

Count: 328  Time=4.52s  Rows=1.0  Rows_examined=890000
SELECT * FROM orders WHERE YEAR(create_time)=2025 AND status='PAID';

EXPLAIN SELECT * FROM orders
WHERE YEAR(create_time) = 2025 AND status = 'PAID';

type: ALL | key: NULL | rows: 890000 | Extra: Using where

سبب فشل الفهرسة: تم استخدام الدالة YEAR() على create_time. الحل:

ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

type: range | key: idx_status_time | rows: 3200 | Extra: Using index condition

انخفض عدد الصفوف التي تم مسحها من 890,000 إلى 3,200، وانخفض وقت الاستعلام من 4.5 ثانية إلى 0.08 ثانية. وأخيرًا، قم بضبط مخزن التخزين المؤقت:

SET GLOBAL innodb_buffer_pool_size = 8589934592;

❓ أسئلة شائعة

📖 ملخص

• يُعد سجل الاستعلامات البطيئة نقطة الانطلاق لتحسين الأداء؛ استخدم mysqldumpslow أو طرق عرض النظام لتحديد نقاط الاختناق بسرعة.
• يتراوح حقل «type» في الأمر EXPLAIN بين «system» و«ALL» بترتيب تنازلي من حيث الجودة؛ والهدف هو الوصول إلى مستوى «ref» على الأقل.
• تحسين الفهرس النقاط الرئيسية: تجنب حالات عدم تطابق الفهارس الناتجة عن الدوال أو التحويلات الضمنية؛ واستفد بشكل جيد من الفهارس الشاملة والبادئة الموجودة في أقصى اليسار من الفهارس المركبة.
• إعادة صياغة الاستعلام: استبدال SELECT * بأعمدة محددة، واستبدال الاستعلامات الفرعية بـ JOIN، واستخدام المؤشرات أو عمليات الربط المؤجلة (lazy joins) للترقيم العميق للصفحات
• ضبط الإعدادات: innodb_buffer_pool_size هو المعامل الأكثر أهمية؛ ويجب الحفاظ على معدل الوصول إلى مخزن التخزين المؤقت عند 99% أو أكثر.
• مخطط الأداء يوفر مراقبة دقيقة يمكنها تحديد الاستعلامات القصيرة عالية التكرار والفهارس غير المستخدمة التي لا يتم تسجيلها في سجل البطء

📝 تمارين

1. سؤال أساسي (مستوى الصعوبة: ⭐): قم بتفعيل سجل الاستعلامات البطيئة، واضبط الحد الأدنى على 0.5 ثانية، واستخدم mysqldumpslow لتحديد أهم 5 استعلامات بطيئة من حيث عدد مرات التنفيذ.

2. تمرين متقدم (درجة الصعوبة ⭐⭐): بالنسبة لاستعلام يحتوي على type من نوع ALL، استخدم EXPLAIN لتحليله، ثم أضف الفهارس المناسبة للتحقق من أن type قد تمت ترقيته إلى مستوى ref أو range.

3. سؤال التحدي (الصعوبة: ⭐⭐⭐): صمم مخطط فهرسة لجدول «order» الذي يحتوي على مليون صف، باستخدام فهرس شامل لتحسين استعلام SELECT user_id, status, total_amount FROM orders WHERE user_id = ? AND status = ?، وقارن الاختلافات في الحقول rows وExtra في الناتج EXPLAIN قبل التحسين وبعده.

4. تمرين عملي (مستوى الصعوبة: ⭐⭐⭐): أعد كتابة استعلام SQL بطيء يحتوي على استعلام فرعي باستخدام JOIN، ثم قم بتحسينه باستخدام الفهارس لتقليل وقت الاستعلام من ثوانٍ إلى أقل من 100 مللي ثانية. قم بتوثيق عملية التحسين بأكملها.

فريق Web-Tutorial التقني

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

من نحن