PostgreSQL Antipatterns: "එකක් පමණක් තිබිය යුතුය!"

SQL හි, ඔබ විස්තර කරන්නේ ඔබට සාක්ෂාත් කර ගැනීමට අවශ්‍ය “කුමක්ද” යන්න මිස එය ක්‍රියාත්මක කළ යුත්තේ කෙසේද යන්න නොවේ. එබැවින්, "ඇසෙන පරිදි එය ලියා ඇත්තේ කෙසේද" යන විලාසයෙන් SQL විමසුම් සංවර්ධනය කිරීමේ ගැටලුව එහි ගෞරවනීය ස්ථානය ගනී. SQL හි කොන්දේසි ගණනය කිරීමේ ලක්ෂණ.

අද, අතිශය සරල උදාහරණ භාවිතා කරමින්, භාවිතයේ සන්දර්භය තුළ මෙය හේතු විය හැකි දේ බලමු GROUP/DISTINCT и LIMIT ඔවුන් සමග.

දැන්, ඔබ ඉල්ලීමේ ලිව්වා නම් "පළමුව මෙම සලකුණු සම්බන්ධ කරන්න, ඉන්පසු සියලුම අනුපිටපත් විසි කරන්න, ඉතිරිව තිබිය යුත්තේ එකක් පමණි එක් එක් යතුර සඳහා පිටපත් කරන්න" - සම්බන්ධතාවය කිසිසේත් අවශ්‍ය නොවූවත් එය ක්‍රියාත්මක වන්නේ හරියටම මෙයයි.

සමහර විට ඔබ වාසනාවන්ත වන අතර එය "ක්‍රියා කරයි", සමහර විට එය කාර්ය සාධනය කෙරෙහි අප්රසන්න බලපෑමක් ඇති කරයි, සමහර විට එය සංවර්ධකයාගේ දෘෂ්ටි කෝණයෙන් සම්පූර්ණයෙන්ම අනපේක්ෂිත බලපෑම් ලබා දෙයි.

හොඳයි, සමහර විට එතරම් දර්ශනීය නොවේ, නමුත් ...

"මිහිරි යුවල": එක්වන්න + DISTINCT

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

ඔවුන්ට අවශ්ය කුමක්දැයි පැහැදිලි වනු ඇත සම්පූර්ණ වූ තත්ත්වයට අදාළ Y හි වාර්තා ඇති වාර්තා X තෝරන්න. හරහා ඉල්ලීමක් ලිව්වා JOIN - සමහර pk අගයන් කිහිප වතාවක් ලබා ගත්තා (හරියටම Y හි ඇති සුදුසු ඇතුළත් කිරීම් කීයක්). ඉවත් කරන්නේ කෙසේද? නිසැකවම DISTINCT!

සෑම X-වාර්තාවක් සඳහාම අදාළ Y-වාර්තා සිය ගණනක් ඇති විට එය විශේෂයෙන් “සතුටුදායක” වන අතර පසුව අනුපිටපත් වීරෝදාර ලෙස ඉවත් කරනු ලැබේ ...

නිවැරදි කරන්නේ කෙසේද? ආරම්භ කිරීමට, ගැටලුව වෙනස් කළ හැකි බව තේරුම් ගන්න "Y හි සම්පුර්ණ කරන ලද කොන්දේසිය සමඟ අවම වශයෙන් එකක්වත් ඇති වාර්තා X තෝරන්න" - සියල්ලට පසු, අපට Y- වාර්තාවෙන් කිසිවක් අවශ්‍ය නොවේ.

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 සොයාගත් වහාම.

අනාගතයේදී මෙය නව නෝඩයකට ස්තූතිවන්ත විය හැකි අතර ක්‍රියා කරයි Index Skip Scan, එය ක්‍රියාත්මක කිරීම දැනට ක්‍රියාත්මක වෙමින් පවතී, නමුත් තවමත් නැත.

දැනට මුලින්ම සියලුම වාර්තා ලබා ගනු ඇත, අද්විතීය වන අතර, ඔවුන්ගෙන් පමණක් ඉල්ලා සිටින මුදල ආපසු ලබා දෙනු ඇත. අපට එවැනි දෙයක් අවශ්‍ය නම් එය විශේෂයෙන් කණගාටුදායකය $ 1 = 4, සහ වගුවේ වාර්තා සිය දහස් ගණනක් ඇත ...

නිෂ්ඵල ලෙස දුක් නොවී සිටීම සඳහා, අපි පුනරාවර්තන විමසුමක් භාවිතා කරමු PostgreSQL Wiki වෙතින් "DISTINCT is for the දුප්පත්":

මූලාශ්රය: www.habr.com