PostgreSQL எதிர்ப்பு வடிவங்கள்: "ஒரே ஒன்று மட்டுமே இருக்க வேண்டும்!"

SQL இல், நீங்கள் "எதை" பெற விரும்புகிறீர்கள் என்பதை விவரிக்கிறீர்கள், "எப்படி" செய்ய வேண்டும் என்பதை அல்ல. எனவே, SQL வினவல்களை "கேட்கப்படுவது போல் எழுதுவது" என்ற பாணியில் உருவாக்குவதில் உள்ள சிக்கல் அதன் மரியாதைக்குரிய இடத்தைப் பெறுகிறது. SQL இல் நிபந்தனை மதிப்பீட்டின் தனித்தன்மைகள்.

இன்று, மிகவும் எளிமையான உதாரணங்களைப் பயன்படுத்தி, பயன்படுத்துவதன் பின்னணியில் இது என்ன வழிவகுக்கும் என்று பார்ப்போம் GROUP/DISTINCT и LIMIT அவர்களுடன்.

நீங்கள் கோரிக்கையில் எழுதியிருந்தால் அதுதான் "முதலில் இந்த மாத்திரைகளை இணைக்கவும், பின்னர் அனைத்து நகல்களையும் தூக்கி எறியுங்கள், ஒன்று மட்டுமே இருக்க வேண்டும் ஒவ்வொரு விசைக்கும் உதாரணம்" - இணைப்பு தேவைப்படாவிட்டாலும், இது சரியாக வேலை செய்யும்.

சில நேரங்களில் நீங்கள் அதிர்ஷ்டசாலி மற்றும் அது "வேலை செய்கிறது", சில நேரங்களில் இது செயல்திறனில் விரும்பத்தகாத விளைவைக் கொண்டிருக்கிறது, மேலும் சில சமயங்களில் இது டெவலப்பரின் பார்வையில் இருந்து முற்றிலும் எதிர்பாராத விளைவுகளை அளிக்கிறது.

சரி, ஒருவேளை கண்கவர் இல்லை, ஆனால் ...

"இனிமையான ஜோடி": சேர் + தனி

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

அவர்கள் என்ன விரும்புகிறார்கள் என்பது எப்படி தெளிவாக இருக்கும் X போன்ற பதிவுகளைத் தேர்ந்தெடுக்கவும், Y இல் பூர்த்தி செய்யப்பட்ட நிபந்தனையுடன் தொடர்புடையது. மூலம் கோரிக்கையை சமர்பித்தார் JOIN - பல முறை pk இன் சில மதிப்புகளைப் பெற்றது (சரியாக எத்தனை பொருத்தமான பதிவுகள் Y இல் இருந்தன). எப்படி நீக்குவது? நிச்சயமாக DISTINCT!

ஒவ்வொரு எக்ஸ்-பதிவுக்கும் பல நூறு தொடர்புடைய ஒய்-பதிவுகள் இருக்கும்போது இது குறிப்பாக "இனிமையானது", பின்னர் நகல்கள் வீரமாக அகற்றப்படும் ...

எப்படி சரி செய்வது? தொடங்குவதற்கு, பணியை மாற்றியமைக்க முடியும் என்பதை உணருங்கள் "நிபந்தனை பூர்த்தி செய்யப்படுவதோடு தொடர்புடைய Y இல் குறைந்தபட்சம் ஒன்று இருக்கும் X பதிவுகளைத் தேர்ந்தெடுக்கவும்" - எல்லாவற்றிற்கும் மேலாக, ஒய்-பதிவிலிருந்தே எங்களுக்கு எதுவும் தேவையில்லை.

Nested EXISTS

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 அவர்கள் மிகவும் நட்பாக இல்லை.

டெவலப்பர் சிந்திக்க முடியும் "இப்போது, ​​அங்கு பதிவுகள் இருந்தால், எனக்கு வரம்புக்கு மேல் தேவையில்லை". ஆனால் நீங்கள் செய்ய வேண்டியதில்லை! ஏனெனில் அடித்தளத்திற்கு இது:

• அவர்கள் விரும்புவதை எண்ணுங்கள் அனைத்து பதிவுகளிலும்
• அவர்கள் கேட்கும் அளவுக்கு வரிகளைக் கொடுங்கள்

இலக்கு நிபந்தனைகளைப் பொறுத்து, பின்வரும் மாற்றீடுகளில் ஒன்றைச் செய்வது பொருத்தமானது:

• (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 வெவ்வேறு மதிப்புகளைக் கண்டறிந்தவுடன்.

எதிர்காலத்தில், இது ஒரு புதிய முனைக்கு நன்றி செலுத்தலாம் மற்றும் செயல்படும் இன்டெக்ஸ் ஸ்கிப் ஸ்கேன், தற்போது செயல்படுத்தப்பட்டு வருகிறது, ஆனால் இன்னும் இல்லை.

இதுவரை முதலில் அனைத்து பதிவுகளும் மீட்டெடுக்கப்படும், தனித்தன்மை வாய்ந்தவை, மேலும் கோரப்பட்டவை மட்டுமே திருப்பித் தரப்படும். நாம் அப்படி ஏதாவது விரும்பினால் அது மிகவும் வருத்தமாக இருக்கிறது $ 1 = 4, மற்றும் அட்டவணையில் நூறாயிரக்கணக்கான பதிவுகள் உள்ளன ...

வீணாக சோகமாக இருக்கக்கூடாது என்பதற்காக, ஒரு சுழல்நிலை வினவலைப் பயன்படுத்துவோம் PostgreSQL விக்கியில் இருந்து "DISTINCT for the Poor":

ஆதாரம்: www.habr.com