PostgreSQL Antipatterns: "त्यहाँ एउटा मात्र हुनुपर्छ!"

SQL मा, तपाइँ "के" प्राप्त गर्न चाहानुहुन्छ भनेर वर्णन गर्नुहुन्छ, "कसरी" गर्नु हुँदैन। त्यसकारण, "जसरी सुनिन्छ यो कसरी लेखिएको छ" को शैलीमा SQL प्रश्नहरू विकास गर्ने समस्याले यसको सम्मानको स्थान लिन्छ। SQL मा अवस्था मूल्याङ्कन को विशेषताहरु.

आज, अत्यन्त सरल उदाहरणहरू प्रयोग गर्दै, यो प्रयोगको सन्दर्भमा के हुन सक्छ हेरौं GROUP/DISTINCT и LIMIT तिनीहरुसँग।

यदि तपाईले अनुरोधमा लेख्नु भएको छ भने "पहिले यी ट्याब्लेटहरू जडान गर्नुहोस्, र त्यसपछि सबै डुप्लिकेटहरू बाहिर फाल्नुहोस्, त्यहाँ एक मात्र हुनुपर्छ प्रत्येक कुञ्जीको लागि उदाहरण" - यो ठ्याक्कै यो कसरी काम गर्नेछ, भले पनि जडान आवश्यक थिएन।

र कहिलेकाहीँ तपाईं भाग्यशाली हुनुहुन्छ र यसले "केवल काम गर्दछ", कहिलेकाहीँ यसले प्रदर्शनमा अप्रिय प्रभाव पार्छ, र कहिलेकाहीँ यसले विकासकर्ताको दृष्टिकोणबाट बिल्कुल अप्रत्याशित प्रभावहरू दिन्छ।

ठिक छ, सायद शानदार होइन, तर ...

"मिठा जोडी": JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

उनीहरु के चाहन्छन् भन्ने कसरी प्रष्ट हुन्छ त्यस्ता अभिलेखहरू X चयन गर्नुहोस्, जसको लागि Y मा पूरा भएको अवस्थासँग सम्बन्धित छ। मार्फत अनुरोध पेस गर्नुभयो JOIN - pk को केहि मानहरू धेरै पटक प्राप्त भयो (वास्तवमा कति उपयुक्त रेकर्डहरू Y मा भए)। कसरी हटाउने? पक्कै पनि DISTINCT!

यो विशेष गरी "रमाइलो" हुन्छ जब प्रत्येक X-रेकर्डको लागि त्यहाँ धेरै सय सम्बन्धित Y-रेकर्डहरू हुन्छन्, र त्यसपछि डुप्लिकेटहरू वीरतापूर्वक हटाइन्छ ...

कसरी ठीक गर्ने? सुरु गर्न, कार्य परिमार्जन गर्न सकिन्छ भनेर महसुस गर्नुहोस् "ती रेकर्डहरू X चयन गर्नुहोस् जसको लागि सर्त पूरा भइरहेकोसँग सम्बन्धित Y मा कम्तिमा एक छ" - आखिर, हामीलाई Y-रेकर्डबाट नै केहि चाहिँदैन।

नेस्टेड अवस्थित छ

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

PostgreSQL को केहि संस्करणहरूले बुझ्दछन् कि EXISTS मा पहिलो रेकर्ड फेला पार्न पर्याप्त छ जुन पुरानोले गर्दैन। त्यसैले, म सधैं संकेत गर्न रुचाउँछु LIMIT 1 भित्र EXISTS.

पार्श्व सम्मिलित

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

एउटै विकल्पले अनुमति दिन्छ, यदि आवश्यक भएमा, एकै समयमा फेला परेको सम्बन्धित Y-रेकर्डबाट केही डाटा फिर्ता गर्न। यस्तै विकल्प लेखमा छलफल गरिएको छ "PostgreSQL Antipatterns: दुर्लभ रेकर्ड JOIN को बीचमा पुग्नेछ".

"किन थप तिर्ने": DISTINCT [ON] + LIMIT 1

त्यस्ता क्वेरी रूपान्तरणहरूको अतिरिक्त फाइदा भनेको रेकर्डहरूको गणनालाई सजिलै सीमित गर्न सक्ने क्षमता हो यदि तिनीहरू मध्ये एक / थोरै मात्र आवश्यक छ, निम्न अवस्थामा जस्तै:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

अब हामी अनुरोध पढ्छौं र DBMS ले के गर्नु पर्छ भनेर बुझ्न प्रयास गर्नुहोस्:

• हामी प्लेटहरू जडान गर्छौं
• X.pk द्वारा अद्वितीय
• बाँकी रेकर्डहरू मध्ये एउटा छान्नुहोस्

त्यसोभए तपाईले के पाउनुभयो? "केही एक रेकर्ड" अद्वितीयहरूबाट - र यदि तपाईंले यसलाई गैर-अद्वितीयहरू मध्ये एक लिनुभयो भने, के नतिजा कुनै न कुनै रूपमा परिवर्तन हुन्छ? .. "र यदि कुनै फरक छैन भने, किन थप तिर्ने?"

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

र ठ्याक्कै एउटै विषयवस्तुसँग GROUP BY + LIMIT 1.

"मैले सोध्नै पर्छ": निहित GROUP + LIMIT

यस्तै कुराहरु फरक फरकमा हुने गर्दछ शून्यता जाँच लेबल वा CTE हरू अनुरोध बढ्दै जाँदा:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

समग्र कार्यहरू (count/min/max/sum/...) स्पष्ट रूपमा निर्दिष्ट नगरी पनि सम्पूर्ण सेटमा सफलतापूर्वक कार्यान्वयन गरिन्छ GROUP BY। यहाँ संग मात्र LIMIT तिनीहरू धेरै मिलनसार छैनन्।

विकासकर्ताले सोच्न सक्छ "अब, यदि त्यहाँ रेकर्डहरू छन् भने, मलाई LIMIT भन्दा बढी चाहिँदैन"। तर तपाईले गर्नु पर्दैन! किनभने आधारको लागि यो हो:

• तिनीहरूले के चाहन्छन् गणना गर्नुहोस् सबै रेकर्डहरूमा
• उनीहरूले सोधे जति लाइनहरू दिनुहोस्

लक्षित सर्तहरूमा निर्भर गर्दै, निम्न मध्ये एक प्रतिस्थापन गर्न उपयुक्त छ:

• (count + LIMIT 1) = 0 मा NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 मा EXISTS(LIMIT 1)
• count >= N मा (SELECT count(*) FROM (... LIMIT N))

"ग्राममा कति झुन्ड्याउने": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

एक भोली विकासकर्ताले इमानदारीपूर्वक अनुरोधको कार्यान्वयन रोकिनेछ भनेर विश्वास गर्न सक्छ, हामीले पहिलो $1 फरक मानहरू भेट्टाउने बित्तिकै.

भविष्यमा केहि समय, यो हुन सक्छ र एक नयाँ नोड को लागी धन्यवाद काम गर्नेछ अनुक्रमणिका स्किप स्क्यानजसको कार्यान्वयनका लागि अहिले काम भइरहेको छ तर अहिलेसम्म हुन सकेको छैन ।

अहिलेसम्म पहिलो सबै रेकर्डहरू पुन: प्राप्त हुनेछ, अद्वितीय छन्, र अनुरोध गरे अनुसार मात्र फिर्ता गरिनेछ। यो विशेष गरी दु: खी छ यदि हामी केहि चाहन्छौं $ १ =।, र तालिकामा सयौं हजारौं रेकर्डहरू छन् ...

व्यर्थमा दुखी नहुनको लागि, हामी पुनरावर्ती क्वेरी प्रयोग गर्नेछौं PostgreSQL Wiki बाट "गरीबका लागि DISTINCT":

स्रोत: www.habr.com