Antipatterns PostgreSQL: تقييم الحالة في 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.

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

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

في هذه الحالة ، لم نحدد ELSE- القيمة ، أي إذا كان كلا الشرطين خاطئين 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: عدم المساواة لا تأخذ في الحسبان NULL

لنفترض أن كل شيء OLD-حقول مهمة NULL. ماذا سيحدث؟

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

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

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

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 ...

المصدر: www.habr.com