PostgreSQL Antipatterns: 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: वा/र चेन

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: bool अपरेशनहरूको प्राथमिकता

स्रोत ढाँचा गरौं:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

उफ्... वास्तवमा, यो बाहिरियो कि यदि पहिलो दुई सर्तहरू मध्ये कुनै पनि सत्य हो भने, सम्पूर्ण अवस्था मा परिणत हुन्छ TRUE, असमानतालाई ख्याल नगरी। र यो हामीले चाहेको होइन।

ठीक गरौं:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

समस्या #4 (सानो): जटिल वा एउटा क्षेत्रको लागि अवस्था

वास्तवमा, हामीसँग नम्बर 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