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

أفضل ممارسات الأداء

13 دقيقة الدرس 19 من 40

أفضل ممارسات الأداء

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

تخزين الاستعلام المؤقت (تم إهماله في MySQL 8.0)

تم إزالة ذاكرة التخزين المؤقت للاستعلام في MySQL 8.0 بسبب مشاكل قابلية التوسع. فهم السبب يساعد على تجنب الأنماط المماثلة:

النهج القديم (MySQL 5.7 وما قبل):
-- ذاكرة التخزين المؤقت للاستعلام خزنت نتائج SELECT الدقيقة
-- الاستعلام المماثل الثاني أعاد النتيجة المخزنة مؤقتاً
-- المشكلة: إبطال ذاكرة التخزين المؤقت عند أي تغيير في الجدول

لماذا تمت إزالته:
- قابلية التوسع الضعيفة على الأنظمة متعددة النوى
- كان إبطال ذاكرة التخزين المؤقت عدوانياً جداً
- لم يعمل بشكل جيد مع أحمال العمل كثيرة الكتابة

البديل الحديث:
استخدم التخزين المؤقت على مستوى التطبيق (Redis، Memcached)
+ إبطال ذاكرة التخزين المؤقت أكثر مرونة
+ يعمل عبر خوادم MySQL متعددة
+ تحكم أفضل في ما يتم تخزينه مؤقتاً

أفضل ممارسة: نفذ التخزين المؤقت في طبقة التطبيق باستخدام Redis أو Memcached. هذا يمنحك تحكماً دقيقاً في استراتيجيات انتهاء صلاحية ذاكرة التخزين المؤقت وإبطالها.

تجميع الاتصالات

فتح وإغلاق اتصالات قاعدة البيانات مكلف. يعيد تجميع الاتصالات استخدام الاتصالات لأداء أفضل:

بدون تجميع الاتصالات (غير فعال)

// سيء: إنشاء اتصال جديد لكل طلب
function getUserData($userId) {
    $conn = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'pass');
    $stmt = $conn->prepare('SELECT * FROM users WHERE id = ?');
    $stmt->execute([$userId]);
    $result = $stmt->fetch();
    $conn = null;  // إغلاق الاتصال
    return $result;
}

// المشكلة: لـ 100 طلب، ينشئ 100 اتصال!
// كل اتصال يستغرق ~10-50ms لإنشائه

مع تجميع الاتصالات (فعال)

// جيد: إعادة استخدام الاتصال المستمر
function getUserData($userId) {
    // الحصول على الاتصال من المجموعة (اتصال PDO المستمر)
    $conn = new PDO(
        'mysql:host=localhost;dbname=mydb',
        'user',
        'pass',
        [PDO::ATTR_PERSISTENT => true]  // تمكين تجميع الاتصالات
    );

    $stmt = $conn->prepare('SELECT * FROM users WHERE id = ?');
    $stmt->execute([$userId]);
    return $stmt->fetch();
    // الاتصال يعاد إلى المجموعة، لا يتم إغلاقه
}

// لـ 100 طلب، يعيد استخدام بضعة اتصالات مجمعة
// يوفر 90-99% من عبء الاتصال

تكوين مجموعة الاتصالات

إعدادات خادم MySQL (my.cnf):

[mysqld]
max_connections = 200          -- الحد الأقصى للاتصالات المتزامنة
max_connect_errors = 10        -- حظر المضيف بعد N محاولات فاشلة
wait_timeout = 28800           -- إغلاق الاتصالات الخاملة بعد 8 ساعات
interactive_timeout = 28800    -- نفس الشيء للعملاء التفاعليين

أفضل الممارسات:
✓ تعيين max_connections بناءً على حركة المرور (100-500 نموذجية)
✓ استخدام الاتصالات المستمرة في PHP/Python/Node.js
✓ إغلاق الاتصالات في البرامج النصية طويلة التشغيل
✓ مراقبة استخدام الاتصال مع SHOW PROCESSLIST

العمليات الدفعية

معالجة صفوف متعددة في استعلام واحد أسرع بكثير من الاستعلامات الفردية:

بطيء: إدراجات فردية

-- سيء: 1000 جملة INSERT منفصلة
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
-- ... 997 مرة أخرى

الأداء:
1000 استعلام × 5ms لكل استعلام = 5000ms (5 ثوانٍ)

سريع: إدراج دفعي

-- جيد: INSERT واحد مع قيم متعددة
INSERT INTO users (name, email) VALUES
    ('John', 'john@example.com'),
    ('Jane', 'jane@example.com'),
    ('Bob', 'bob@example.com'),
    -- ... حتى 1000 صف
    ('Alice', 'alice@example.com');

الأداء:
استعلام واحد = ~50ms (أسرع بـ 100 مرة!)

مثال تحديث دفعي

-- بطيء: تحديثات فردية
UPDATE users SET status = 'active' WHERE id = 1;
UPDATE users SET status = 'active' WHERE id = 2;
UPDATE users SET status = 'active' WHERE id = 3;
-- ... أكثر بكثير

-- سريع: UPDATE واحد مع جملة IN
UPDATE users
SET status = 'active'
WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

-- أفضل: استخدم CASE لقيم مختلفة
UPDATE users
SET status = CASE
    WHEN id IN (1, 2, 3) THEN 'active'
    WHEN id IN (4, 5, 6) THEN 'inactive'
    WHEN id IN (7, 8, 9) THEN 'pending'
END
WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);

حدود حجم الدفعة: لدى MySQL إعداد max_allowed_packet (افتراضي 64MB). للدفعات الكبيرة جداً، قسمها إلى أجزاء من 1000-5000 صف للبقاء تحت هذا الحد.

تحسين الترقيم

يصبح الترقيم الساذج بطيئاً مع زيادة الإزاحة. إليك أساليب أفضل:

الترقيم الساذج (بطيء للإزاحات الكبيرة)

-- الصفحة 1 (سريع)
SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 0;

-- الصفحة 1000 (بطيء جداً)
SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 20000;
-- يجب على MySQL فحص 20,020 صفاً لإعادة 20!

المشكلة:
مع زيادة رقم الصفحة، يتدهور الأداء
الصفحة 10,000 تفحص 200,000 صف فقط لإعادة 20

ترقيم Keyset (سريع لجميع الصفحات)

-- الصفحة 1: احصل على أول 20 مقالة
SELECT * FROM articles
ORDER BY id
LIMIT 20;
-- يعيد IDs: 1-20

-- الصفحة 2: استخدم آخر ID من الصفحة السابقة (id = 20)
SELECT * FROM articles
WHERE id > 20
ORDER BY id
LIMIT 20;
-- يعيد IDs: 21-40 (سريع! يفحص 20 صفاً فقط)

-- الصفحة 3: استخدم آخر ID من الصفحة 2 (id = 40)
SELECT * FROM articles
WHERE id > 40
ORDER BY id
LIMIT 20;
-- يعيد IDs: 41-60 (لا يزال سريعاً!)

المزايا:
✓ أداء متسق بغض النظر عن رقم الصفحة
✓ يعمل مع الفهارس بكفاءة
✓ مثالي لواجهات "تحميل المزيد" أو التمرير اللانهائي

الترقيم مع مفتاح مركب

-- الترقيم حسب created_at (غير فريد)
CREATE INDEX idx_created_id ON articles(created_at, id);

-- الصفحة 1
SELECT * FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- آخر عنصر: created_at='2024-01-15', id=458

-- الصفحة 2: استخدم كلتا القيمتين
SELECT * FROM articles
WHERE (created_at, id) < ('2024-01-15', 458)
ORDER BY created_at DESC, id DESC
LIMIT 20;

هذا النهج:
✓ يعمل مع أعمدة الترتيب غير الفريدة
✓ يستخدم الفهرس المركب بكفاءة
✓ يحافظ على أداء متسق

مشكلة استعلام N+1

واحدة من أكثر قتلة الأداء شيوعاً في تطبيقات قاعدة البيانات:

المشكلة

-- احصل على 100 مستخدم (استعلام واحد)
SELECT * FROM users LIMIT 100;

-- ثم لكل مستخدم، احصل على منشوراتهم (100 استعلام!)
-- حلقة:
SELECT * FROM posts WHERE user_id = 1;
SELECT * FROM posts WHERE user_id = 2;
SELECT * FROM posts WHERE user_id = 3;
-- ... 97 استعلام آخر

المجموع: 1 + 100 = 101 استعلام
إذا استغرق كل استعلام 5ms: 101 × 5ms = 505ms

الحل 1: JOIN

-- استعلام واحد مع JOIN (استعلام واحد إجمالاً!)
SELECT
    u.id, u.name, u.email,
    p.id as post_id, p.title, p.content
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
LIMIT 100;

المجموع: استعلام واحد
وقت الاستعلام: ~20ms (أسرع بـ 25 مرة!)

الحل 2: جملة IN (التحميل المسبق)

-- احصل على المستخدمين (استعلام واحد)
SELECT * FROM users LIMIT 100;
-- النتائج: معرفات المستخدم هي 1، 2، 3، ...، 100

-- احصل على جميع المنشورات لهؤلاء المستخدمين (استعلام واحد)
SELECT * FROM posts
WHERE user_id IN (1, 2, 3, 4, 5, ..., 100);

المجموع: استعلامان
أفضل بكثير من 101 استعلام!

شائع في ORMs: مشكلة N+1 شائعة للغاية عند استخدام ORMs (Eloquent، Doctrine، إلخ) إذا لم تستخدم التحميل المسبق. استخدم دائماً طرق التحميل المسبق مثل `with()` في Laravel أو `fetch()` في Doctrine.

تحليل قاعدة البيانات

توفر MySQL أدوات لتحديد الاستعلامات البطيئة واختناقات الأداء:

تمكين التحليل للجلسة الحالية

-- تمكين التحليل
SET profiling = 1;

-- قم بتشغيل استعلاماتك
SELECT * FROM users WHERE country = 'USA';
SELECT * FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM products;

-- عرض قائمة الاستعلام مع التوقيت
SHOW PROFILES;

+----------+------------+------------------------------------------------+
| Query_ID | Duration   | Query                                          |
+----------+------------+------------------------------------------------+
| 1        | 0.00234500 | SELECT * FROM users WHERE country = 'USA'     |
| 2        | 0.15678900 | SELECT * FROM orders WHERE status = 'pending' |
| 3        | 0.00089200 | SELECT COUNT(*) FROM products                  |
+----------+------------+------------------------------------------------+

التحليل: الاستعلام 2 بطيء (156ms) - يحتاج إلى تحسين!

معلومات الملف الشخصي التفصيلية

-- احصل على التفصيل التفصيلي لاستعلام محدد
SHOW PROFILE FOR QUERY 2;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000087 |
| checking permissions | 0.000012 |
| Opening tables       | 0.000034 |
| init                 | 0.000045 |
| System lock          | 0.000015 |
| optimizing           | 0.000023 |
| statistics           | 0.000098 |
| preparing            | 0.000034 |
| executing            | 0.150234 | ← 96% من الوقت هنا!
| end                  | 0.000012 |
| query end            | 0.000008 |
| closing tables       | 0.000011 |
| freeing items        | 0.000098 |
| cleaning up          | 0.000023 |
+----------------------+----------+

تم تحديد الاختناق: مرحلة "executing" (فحص جدول كامل على الأرجح)

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

1. SHOW PROCESSLIST - عرض الاستعلامات النشطة
SHOW FULL PROCESSLIST;

-- يظهر جميع الاتصالات والاستعلامات الحالية
-- تحديد الاستعلامات طويلة التشغيل، الاستعلامات المقفلة

2. Performance Schema (MySQL 5.5+)
-- الاستعلامات الأكثر تكلفة حسب الوقت الإجمالي
SELECT
    DIGEST_TEXT,
    COUNT_STAR as executions,
    AVG_TIMER_WAIT/1000000000 as avg_ms,
    SUM_TIMER_WAIT/1000000000 as total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. سجل الاستعلام البطيء
-- تم تغطيته بالفعل في الدرس السابق
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- تسجيل الاستعلامات >1 ثانية

4. sys Schema (MySQL 5.7+)
-- طرق عرض سهلة الاستخدام لـ Performance Schema
SELECT * FROM sys.statement_analysis LIMIT 10;
SELECT * FROM sys.statements_with_full_table_scans;
SELECT * FROM sys.schema_index_statistics;

قائمة فحص تحسين الأداء

تصميم قاعدة البيانات:
✓ التطبيع إلى 3NF، إلغاء التطبيع بشكل استراتيجي
✓ اختيار أنواع البيانات المناسبة (INT مقابل BIGINT)
✓ استخدام ENUM لمجموعات ثابتة صغيرة من القيم
✓ النظر في التقسيم للجداول الكبيرة جداً

الفهرسة:
✓ فهرسة المفاتيح الأجنبية
✓ فهرسة أعمدة WHERE و JOIN و ORDER BY
✓ استخدام الفهارس المركبة للاستعلامات متعددة الأعمدة
✓ إزالة الفهارس غير المستخدمة/الزائدة
✓ مراقبة استخدام الفهرس مع sys.schema_unused_indexes

تحسين الاستعلام:
✓ استخدام EXPLAIN لجميع الاستعلامات المهمة
✓ تجنب SELECT * (حدد الأعمدة المطلوبة فقط)
✓ تجنب الدوال على الأعمدة المفهرسة في WHERE
✓ استخدام EXISTS بدلاً من IN للاستعلامات الفرعية الكبيرة
✓ عمليات INSERT/UPDATE الدفعية
✓ استخدام ترقيم keyset بدلاً من OFFSET

إدارة الاتصال:
✓ استخدام تجميع الاتصالات
✓ إغلاق الاتصالات في البرامج النصية طويلة التشغيل
✓ تعيين max_connections المناسب
✓ مراقبة الاتصالات النشطة

التخزين المؤقت:
✓ تنفيذ التخزين المؤقت على مستوى التطبيق (Redis)
✓ تخزين الاستعلامات باهظة التكلفة مؤقتاً
✓ تعيين أوقات انتهاء صلاحية التخزين المؤقت المناسبة
✓ إبطال ذاكرة التخزين المؤقت عند تغييرات البيانات

المراقبة:
✓ تمكين سجل الاستعلام البطيء
✓ مراجعة الاستعلامات البطيئة أسبوعياً
✓ استخدام Performance Schema للتحليل التفصيلي
✓ مراقبة موارد الخادم (CPU، RAM، disk I/O)
✓ إعداد تنبيهات لتدهور الأداء

أنماط مضادة للأداء شائعة

1. SELECT * في الحلقات
-- سيء: يجلب جميع الأعمدة، ينشئ مشكلة N+1
for each user:
    SELECT * FROM users WHERE id = ?

-- جيد: استعلام واحد مع JOIN أو IN
SELECT u.*, p.* FROM users u LEFT JOIN posts p ON u.id = p.user_id

2. العد غير الفعال
-- سيء: يفحص الجدول بالكامل
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- جيد: استخدام الفهرس المغطي
CREATE INDEX idx_status ON orders(status);
-- أو حافظ على العدادات في جدول منفصل

3. استخدام OR بدلاً من UNION
-- أبطأ: لا يمكن استخدام الفهارس بفعالية
SELECT * FROM users WHERE email = ? OR username = ?

-- أسرع: يستخدم الفهارس لكل جزء
SELECT * FROM users WHERE email = ?
UNION
SELECT * FROM users WHERE username = ?

4. حرف بدل في بداية LIKE
-- لا يمكن استخدام الفهرس
SELECT * FROM products WHERE name LIKE '%phone%';

-- يمكن استخدام الفهرس
SELECT * FROM products WHERE name LIKE 'phone%';

5. عدم استخدام LIMIT
-- سيء: يعيد جميع النتائج
SELECT * FROM logs ORDER BY created_at DESC;

-- جيد: يحد من مجموعة النتائج
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

تمرين عملي:

السيناريو: تطبيقك بطيء. إليك الكود المشكل:

// احصل على جميع المستخدمين
$users = query("SELECT * FROM users LIMIT 1000");

// لكل مستخدم، احصل على عدد طلباتهم والمبلغ الإجمالي
foreach ($users as $user) {
    $orderCount = query(
        "SELECT COUNT(*) FROM orders WHERE customer_id = " . $user['id']
    );

    $totalSpent = query(
        "SELECT SUM(total) FROM orders WHERE customer_id = " . $user['id']
    );

    echo $user['name'] . ": " . $orderCount . " orders, $" . $totalSpent;
}

المشاكل:
1. SELECT * يجلب أعمدة غير ضرورية
2. مشكلة استعلام N+1 (1 + 1000 + 1000 = 2001 استعلام!)
3. لا فهارس مذكورة
4. عدم استخدام الجمل المحضرة (خطر حقن SQL)

المهمة: قم بتحسين هذا الكود.

الحل:

الخطوة 1: إنشاء الفهارس الضرورية
CREATE INDEX idx_customer_id ON orders(customer_id);

الخطوة 2: إعادة الكتابة كاستعلام واحد مع JOIN
$results = query("
    SELECT
        u.id,
        u.name,
        COUNT(o.id) as order_count,
        COALESCE(SUM(o.total), 0) as total_spent
    FROM users u
    LEFT JOIN orders o ON u.id = o.customer_id
    GROUP BY u.id, u.name
    LIMIT 1000
");

foreach ($results as $row) {
    echo $row['name'] . ": " . $row['order_count'] .
         " orders, $" . $row['total_spent'];
}

تحسين الأداء:
قبل: 2001 استعلام × 5ms = 10,005ms (10 ثوانٍ)
بعد: استعلام واحد = ~50ms (أسرع بـ 200 مرة!)

بديل: إذا كان JOIN معقداً جداً، استخدم استعلامين
// الاستعلام 1: احصل على المستخدمين
$users = query("SELECT id, name FROM users LIMIT 1000");
$userIds = array_column($users, 'id');

// الاستعلام 2: احصل على بيانات الطلب المجمعة لجميع المستخدمين
$orderStats = query("
    SELECT
        customer_id,
        COUNT(*) as order_count,
        SUM(total) as total_spent
    FROM orders
    WHERE customer_id IN (" . implode(',', $userIds) . ")
    GROUP BY customer_id
");

// دمج النتائج في كود التطبيق
// لا يزال أفضل بكثير: استعلامان مقابل 2001!

الملخص

في هذا الدرس، تعلمت:

تمت إزالة ذاكرة التخزين المؤقت للاستعلام في MySQL 8.0 - استخدم التخزين المؤقت على مستوى التطبيق بدلاً من ذلك
يعيد تجميع الاتصالات استخدام الاتصالات لمكاسب أداء كبيرة
العمليات الدفعية (INSERT/UPDATE الدفعي) أسرع بـ 100 مرة من الاستعلامات الفردية
يحافظ ترقيم Keyset على الأداء لجميع أرقام الصفحات
مشكلة استعلام N+1 تقتل الأداء - استخدم JOINs أو التحميل المسبق
حلل الاستعلامات مع SHOW PROFILES لتحديد الاختناقات
استخدم Performance Schema وطرق عرض sys للمراقبة الشاملة
اتبع قائمة فحص التحسين لتصميم قاعدة البيانات والفهرسة والاستعلامات
تجنب الأنماط المضادة الشائعة مثل SELECT * في الحلقات وأنماط LIKE غير الفعالة

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