لقد تغلبت على مُحسِّن MariaDB: من 94 ثانية إلى 55 مللي ثانية

السياق: الترحيل MySQL 8 إلى MariaDB 11.4

مشروع الترحيل من MySQL 8 إلى MariaDB 11.4 يسير بشكل جيد بشكل عام. الاختبارات الوظيفية باللون الأخضر، واختبارات الأداء أيضًا - باستثناء استعلام واحد. طلب واحد استغرق ثانيتين في MySQL 8 ويستغرق الآن 94 ثانية في MariaDB 11.4.

هذا هو انحدار الترحيل الكلاسيكي: لقد تباعدت مُحسِّنات MariaDB وMySQL بشكل ملحوظ منذ الانقسام. إن خطة التنفيذ التي نجحت على أحدها قد تكون كارثية على الآخر.

الاستعلام الإشكالي

يستخدم الاستعلام الأصلي نمط LEFT JOIN الكلاسيكي للعثور على السجلات غير الموجودة في جدول آخر:

SELECT p.product_id, p.product_name, p.category_id
FROM products p
LEFT JOIN discontinued_products dp
    ON p.product_id = dp.product_id
    AND p.category_id = dp.category_id
WHERE dp.product_id IS NULL
  AND p.status = 'active'
  AND p.created_at >= '2023-01-01';

النية واضحة: العثور على جميع المنتجات النشطة غير المدرجة في جدول المنتجات المتوقفة. هذا هو نمط "مكافحة الانضمام" الذي يتم تنفيذه عبر LEFT JOIN + IS NULL.

لماذا 94 ثانية؟

يكشف تحليل خطة التنفيذ على MariaDB 11.4 عن المشكلة. يختار المحسن ما يلي:

1. قم بمسح الجدول products باستخدام الفهرس الموجود في status (200000 صف)
2. لكل صف، قم بمسح الجدول discontinued_products للتحقق من عدم وجود تطابقات

مع جدول discontinued_products يتكون من 17500 صف، يمثل هذا حوالي 3.5 مليار مقارنة. اختار مُحسِّن MySQL 8 خطة مختلفة ذات صلة تجزئة، وهي أكثر كفاءة لهذا النمط.

المشكلة الأساسية ليست في المُحسِّن MariaDB — بل في الاستعلام نفسه. إن LEFT JOIN + IS NULL لتنفيذ منع الانضمام هو نمط مضاد تاريخي يعود تاريخه إلى الوقت الذي لم يكن لدى SQL فيه بدائل أفضل.

الحل: CTE + باستثناء

يدعم MariaDB تعبيرات الجدول الشائعة (CTE) منذ الإصدار 10.2 وعامل التشغيل EXCEPT منذ الإصدار 10.3. تسمح لك هاتان الميزتان بإعادة كتابة المنطق بطريقة أكثر وضوحًا:

WITH active_products AS (
    SELECT product_id, category_id
    FROM products
    WHERE status = 'active'
      AND created_at >= '2023-01-01'
),
still_active AS (
    SELECT product_id, category_id FROM active_products
    EXCEPT
    SELECT product_id, category_id FROM discontinued_products
)
SELECT p.product_id, p.product_name, p.category_id
FROM products p
JOIN still_active sa
    ON p.product_id = sa.product_id
    AND p.category_id = sa.category_id;

لماذا هو أسرع

1. CTE active_products يجسد 200000 منتج نشط في الذاكرة. مسح واحد للجدول products.

2. يقوم عامل التشغيل EXCEPT بتنفيذ عملية محددة: فهو يأخذ جميع المنتجات النشطة ويطرح تلك التي تظهر في discontinued_products. هذا فرق مجموعة قائم على التجزئة، وليس مقارنة سطرًا بسطر.

3. الانضمام النهائي مع CTE still_active هو بحث بسيط عن مجموعة تمت تصفيتها بالفعل.

النتيجة: 55 مللي ثانية

من 94 ثانية إلى 55 مللي ثانية. عامل 1,700. ليس عن طريق تغيير إعداد التكوين. ليس عن طريق إضافة فهرس. من خلال إعادة التفكير في منطق الاستعلام.

الانضمام الأيسر المضاد للنمط: لماذا يستمر

نمط LEFT JOIN + IS NULL لمكافحة الانضمام موجود في كل مكان. يمكن العثور عليها في البرامج التعليمية والدورات التدريبية عبر الإنترنت وإجابات Stack Overflow. ويستمر لعدة أسباب:

التاريخ: قبل SQL:1999، لم يكن هناك EXCEPT، ولا NOT EXISTS محسّن، ولا CTE. كان LEFT JOIN + IS NULL هو الخيار المحمول الوحيد.

العادة: يتعلم المطورون النمط الناجح ويعيدون استخدامه. "إنه يعمل" هو عدو "إنه الأمثل".

التوافق: تعمل LEFT JOIN على جميع إصدارات جميع أنظمة إدارة قواعد البيانات (DBMS). EXCEPT مدعوم فقط في الإصدارات الحديثة.

البدائل التي يجب معرفتها

بالنسبة لمكافحة الانضمام، توجد ثلاثة بدائل:

غير موجود (غالبًا ما يكون الخيار الأفضل)

SELECT p.product_id, p.product_name, p.category_id
FROM products p
WHERE p.status = 'active'
  AND p.created_at >= '2023-01-01'
  AND NOT EXISTS (
    SELECT 1 FROM discontinued_products dp
    WHERE dp.product_id = p.product_id
      AND dp.category_id = p.category_id
  );

تم تحسين NOT EXISTS بشكل جيد بواسطة المحركين MariaDB وMySQL. يمكن للمُحسِّن استخدام شبه صلة عكسية، وهي أكثر كفاءة من الصلة اليسرى.

ليس في (احترس من القيم الخالية)

SELECT product_id, product_name, category_id
FROM products
WHERE status = 'active'
  AND created_at >= '2023-01-01'
  AND (product_id, category_id) NOT IN (
    SELECT product_id, category_id
    FROM discontinued_products
  );

تحذير: NOT IN له سلوك صعب مع القيم NULL. إذا كان من الممكن أن يكون discontinued_products.product_id NULL، فإن دلالات NOT IN تُرجع نتيجة فارغة. استخدم دائمًا NOT EXISTS إذا كانت القيم الخالية ممكنة.

باستثناء (الأكثر قابلية للقراءة)

SELECT product_id, category_id FROM products
WHERE status = 'active' AND created_at >= '2023-01-01'
EXCEPT
SELECT product_id, category_id FROM discontinued_products;
`

EXCEPT` هو أنقى تعبير عن العملية المحددة "A ناقص B". ولكنها تُرجع فقط أعمدة العملية، وليس الأعمدة الإضافية - وبالتالي تستخدم CTE لإعادة تقديم الأعمدة المفقودة عبر JOIN.

العبرة

المحسن هو أداة، وليس معجزة. عندما يكون الاستعلام بطيئًا، يجب ألا يكون السؤال الأول هو "ما التلميح الذي يمكنني إضافته؟" أو "ما هو الإعداد الذي يجب أن أغيره؟". يجب أن يكون السؤال الأول: هل يعبر طلبي بشكل صحيح عن نيتي؟

A LEFT JOIN + IS NULL يعبر عن "الانضمام ثم تصفية العناصر غير المتطابقة". يعبر EXCEPT مباشرة عن "طرح هذه المجموعة من هذه المجموعة الأخرى". الصيغة الثانية أقرب إلى نية العمل، وتعطي المحسن فرصة أفضل بكثير للعثور على خطة فعالة.

تغلب على المحسن من خلال التفكير كعالم رياضيات، وليس مبرمجًا.

تم نشر هذه المقالة في الأصل على متوسط.