🎌 🤺 🐌 PostgreSQL Antipatterns: حساب الشروط في SQL 👩‍👩‍👧 🌡️ ⚕️

SQL ليست C ++ ولا JavaScript. لذلك ، يختلف حساب التعبيرات المنطقية ، وهذا ليس الشيء نفسه:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

في عملية تحسين خطة تنفيذ الاستعلام ، يمكن لـ PostgreSQL "إعادة ترتيب" الشروط المكافئة بشكل تعسفي ، وليس حساب أي منها للسجلات الفردية ، وتعيينها لحالة الفهرس المستخدم ... باختصار ، من الأسهل افتراض أنه لا يمكنك التحكم في أي ترتيب سيتم حساب شروط الأقران (وما إذا كانت على الإطلاق) .

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

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

# 0: RTFM

مثال أولي من الوثائق :

عندما يكون أمر الحساب مهمًا ، يمكن إصلاحه باستخدام البناء CASE. على سبيل المثال ، WHEREلا يمكن الوثوق بهذه الطريقة لتجنب القسمة على صفر في جملة :
SELECT ... WHERE x > 0 AND y/x > 1.5;
خيار آمن:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
CASEيحمي البناء المستخدم بهذه الطريقة التعبير من التحسين ، لذلك يجب استخدامه فقط إذا لزم الأمر.

# 1: الشرط في الزناد

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

يبدو أن كل شيء يبدو على ما يرام ، ولكن ... لا أحد يعد بعدم تنفيذ المرفق SELECTإذا كان الشرط الأول غير صحيح. صحيح باستخدام متداخلIF :

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

الآن دعونا ننظر بعناية - اتضح أن الجسم الكامل لوظيفة الزناد "ملتف" IF. وهذا يعني أنه لا يوجد شيء يمنعنا من إزالة هذا الشرط من الإجراء باستخدام WHEN-الشرط :

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

يسمح هذا النهج بحفظ موارد الخادم في ظروف خاطئة.

# 2: سلسلة OR / AND

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

خلاف ذلك ، يمكنك الحصول على أن كليهما EXISTSسيكون "صحيحًا" ، ولكن سيتم تحقيقهما .

ولكن إذا علمنا على وجه اليقين أن أحدهم "حقيقي" في كثير من الأحيان (أو "خطأ" ANDللسلسلة) ، فهل من الممكن "زيادة أولويته" بطريقة ما بحيث لا يتم تنفيذ الثاني مرة أخرى؟

اتضح أنه ممكن - نهج الخوارزمية قريب من موضوع مقال PostgreSQL Antipatterns: سجل نادر سيصل إلى منتصف JOIN .

دعنا فقط "ننشر حالة" في كلا الحالتين:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

في هذه الحالة، لم نكن تحديد ELSE-value، وهذا هو، إذا كان كل الظروف ليست صحيحة ، فإنه CASEسيعود NULL، التي يتم تفسيرها كما FALSEفي WHEREشرط.

يمكن دمج هذا المثال بطريقة أخرى - للتذوق واللون:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

# 3: كيف [لا] تكتب الشروط

لقد أمضينا يومين في تحليل أسباب الزناد "الغريب" لهذا الزناد - دعنا نرى السبب.

مصدر:

IF( NEW."_" is null or NEW."_" = (select '""'::regclass::oid) or NEW."_" = (select to_regclass('""')::oid)
     AND (   OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW.""
          OR OLD."" <> NEW."" ) ) THEN ...

المشكلة رقم 1: عدم المساواة لا تعتبر فارغة

تخيل أن جميع OLDالمجالات مهمة NULL. ماذا سيحدث؟

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

ومن وجهة نظر العمل بالشروط NULLمتكافئة FALSE، كما ذكر أعلاه.

الحل : استخدم عامل التشغيل IS DISTINCT FROMمن ROW-perator ، لمقارنة السجلات بأكملها دفعة واحدة:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

المشكلة رقم 2: تنفيذ مختلف لنفس الوظيفة

قارن:

NEW."_" = (select '""'::regclass::oid)
NEW."_" = (select to_regclass('""')::oid)

لماذا يوجد تداخل إضافي SELECT؟ ماذا عن الوظيفة to_regclass؟ وبطرق مختلفة ، لماذا؟ ..

الإصلاح:

NEW."_" = '""'::regclass::oid
NEW."_" = '""'::regclass::oid

المشكلة رقم 3: أولوية العمليات المنطقية

تنسيق المصدر:

{... IS NULL} OR
{... } OR
{... } AND
( {... } )

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

الإصلاح:

(
  {... IS NULL} OR
  {... } OR
  {... }
) AND
( {... } )

المشكلة 4 (صغيرة): شرط OR معقد لحقل واحد

في الواقع ، كان لدينا مشاكل في رقم 3 على وجه التحديد لأنه كانت هناك ثلاثة شروط. ولكن بدلاً من ذلك ، يمكنك القيام بذلك باستخدام الآلية coalesce ... IN:

coalesce(NEW."_"::text, '') IN ('', '""', '""')

لذلك نحن NULL"يمسك" ، ومعقدة ORمع الأقواس ليس من الضروري السياج.

مجموع

نصلح ما حصلنا عليه:

IF (
  coalesce(NEW."_"::text, '') IN ('', '""', '""') AND
  (
    OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  ) IS DISTINCT FROM (
    NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  )
) THEN ...

وإذا أخذنا في الاعتبار أن وظيفة الزناد هذه لا يمكن استخدامها إلا في UPDATE-الزناد نظرًا لوجود OLD/NEWمستوى أعلى في الحالة ، فيمكن عندئذٍ وضع هذا الشرط بشكل عام في الحالة WHEN، كما هو موضح في # 1 ...

PostgreSQL Antipatterns: حساب الشروط في SQL