يؤدي تصميم البيانات السيئ إلى ضعف الأداء: من 105 دقيقة إلى 17 ثانية

العرض: 105 دقيقة للطلب

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

الاستعلام المخالف هو JOIN كلاسيكي بين جدول المعاملات وجدول التقويم:

SELECT
    t.transaction_id,
    t.amount,
    t.transaction_date,
    c.fiscal_year,
    c.fiscal_quarter
FROM transactions t
JOIN calendar c ON t.transaction_date = c.calendar_date
WHERE t.created_at >= '2024-01-01';

لا شيء ملحوظ في المظهر. جدولين، انضمام في تاريخ، مرشح زمني. ومع ذلك، 105 دقيقة.

التشخيص: عدم تطابق الأنواع

يكشف تحليل خطة التنفيذ (EXPLAIN) عن مسح كامل للجدول في الجدول calendar. من الغريب الانضمام إلى ما يجب أن يكون مفتاحًا أساسيًا.

وبالنظر إلى هياكل الجدول، تصبح المشكلة واضحة:

-- Table transactions
CREATE TABLE transactions (
    transaction_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2),
    transaction_date INT NOT NULL,  -- ← stocké comme YYYYMMDD
    created_at DATETIME
);

-- Table calendar
CREATE TABLE calendar (
    calendar_date DATE NOT NULL PRIMARY KEY,
    fiscal_year SMALLINT,
    fiscal_quarter TINYINT
);

العمود transaction_date في الجدول transactions هو INT الذي يخزن التاريخ بتنسيق YYYYMMDD (على سبيل المثال، 20240115 لـ 15 يناير 2024). العمود calendar_date في الجدول calendar هو DATE حقيقي.

عند تنفيذ MariaDB / MySQL JOIN، يجب مقارنة INT بـ DATE. لكل سطر من transactions، يقوم المحرك ضمنيًا بتحويل DATE إلى INT (أو العكس) لكل سطر من calendar. هذا التحويل الضمني يجعل الفهرس الموجود على calendar_date غير قابل للاستخدام. النتيجة: مسح جدول كامل على calendar لكل صف من transactions.

مع وجود 2 مليون معاملة و10000 صف في calendar، يمثل ذلك 20 مليار مقارنة مع تحويل النوع.

لماذا لا نغير النوع فقط؟

ستكون الإجابة الواضحة هي تحويل العمود transaction_date من INT إلى DATE. لكن في واقع الأنظمة في الإنتاج:

• الطاولة 15 جيجا . سيستغرق ALTER TABLE ساعات ويغلق الطاولة.
• 47 إجراء مخزنًا و12 مشاهدة مرجعية transaction_date كـ INT.
• يستخدم تطبيق PHP المقارنات الحسابية في هذا العمود (WHERE transaction_date > 20240101).
• ترسل دفعة تحميل ETL التواريخ بتنسيق INT من نظام قديم.

إن تغيير النوع هو الحل المناسب على المدى الطويل، ولكنه ليس الحل الفوري الذي يحتاجه العميل هذه الليلة.

الحل: إنشاء عمود افتراضي

MariaDB / MySQL يدعم الأعمدة الافتراضية (أو الأعمدة التي تم إنشاؤها). هذه هي الأعمدة المحسوبة ديناميكيًا من أعمدة أخرى، بدون تخزين فعلي (VIRTUAL) أو مع تخزين (STORED).

ALTER TABLE transactions
ADD COLUMN transaction_date_real DATE AS (
    STR_TO_DATE(CAST(transaction_date AS CHAR(8)), '%Y%m%d')
) VIRTUAL;

يقوم هذا العمود بتحويل INT إلى DATE بسرعة. لكن العمود الافتراضي وحده لا يحل مشكلة الأداء. أنت بحاجة إلى فهرس:

ALTER TABLE transactions
ADD COLUMN transaction_date_real DATE AS (
    STR_TO_DATE(CAST(transaction_date AS CHAR(8)), '%Y%m%d')
) STORED,
ADD INDEX idx_transaction_date_real (transaction_date_real);

نستخدم STORED بدلاً من VIRTUAL حتى نتمكن من إنشاء فهرس. يتم تخزين العمود فعليًا ويتم الاحتفاظ بالفهرس تلقائيًا أثناء عمليات الإدراج والتحديثات.

تم تصحيح الاستعلام

SELECT
    t.transaction_id,
    t.amount,
    t.transaction_date,
    c.fiscal_year,
    c.fiscal_quarter
FROM transactions t
JOIN calendar c ON t.transaction_date_real = c.calendar_date
WHERE t.created_at >= '2024-01-01';

يقوم JOIN الآن بمقارنة DATE بـ DATE. الفهرس صالح للاستخدام. تعرض خطة التنفيذ ref بدلاً من الفحص الكامل.

النتيجة: 17 ثانية

من 105 دقيقة إلى 17 ثانية. تحسن 99.7% دون تغيير المخطط الموجود، دون تعديل التطبيق، دون لمس الإجراءات المخزنة.

لماذا تعتبر التحويلات الضمنية فخًا؟

توضح هذه الحالة مشكلة أساسية: تحويلات النوع الضمني في الصلات وعبارات WHERE هي أدوات قتل صامتة للأداء.

يقوم MariaDB / MySQL بإجراء تحويلات ضمنية في كثير من الحالات:

• INT مقارنة بـ VARCHAR: يتم تحويل INT إلى VARCHAR
• INT مقارنة بـ DATE: يتم تحويل DATE إلى رقم
• VARCHAR(utf8) مقارنة بـ VARCHAR(latin1): تحويل مجموعة الأحرف
• DECIMAL مقارنة بـ FLOAT: التحويل إلى نقطة عائمة

في كل حالة، يجعل التحويل الفهرس غير قابل للاستخدام لأن المحرك لا يمكنه إجراء بحث مباشر في فهرس B-tree إذا كان يجب تحويل القيمة أولاً.

الدرس المستفاد: تصميم البيانات هو الأساس

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

القواعد الأساسية:

1. يجب تخزين التاريخ كـ DATE أو DATETIME، وليس أبدًا كـ INT أو VARCHAR.
2. يجب أن تحتوي أعمدة الربط على نفس النوع ومجموعة الأحرف/الترتيب.
3. استخدم EXPLAIN بشكل منهجي للتحقق من أن صلاتك تستخدم الفهارس.
4. مراقبة التحويلات الضمنية باستخدام الأداة EXPLAIN ANALYZE (MariaDB 10.1+).

تصميم البيانات ليس براقة. إنها ليست مثيرة مثل ضبط متغيرات النظام أو إعداد مجموعة Galera. لكنه الأساس. وعندما يكون الأساس سيئًا، ينهار كل شيء آخر - 105 دقيقة في كل مرة.

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