Antipatterns PostgreSQL: ارزیابی شرایط در SQL

SQL C++ نیست و جاوا اسکریپت نیست. بنابراین، محاسبه عبارات منطقی متفاوت اتفاق می افتد، و این اصلاً یکسان نیست:

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-زنجیره) - آیا می توان به نحوی "اولویت آن را افزایش داد" تا مورد دوم یک بار دیگر اجرا نشود؟

به نظر می رسد که ممکن است - رویکرد الگوریتمی به موضوع مقاله نزدیک است Antipatterns PostgreSQL: یک رکورد نادر به وسط یک JOIN می رسد.

بیایید فقط هر دوی این شرایط را تحت CASE "هل" کنیم:

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: نابرابری به 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: اولویت عملیات bool

بیایید منبع را قالب بندی کنیم:

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