I-PostgreSQL Antipatterns: "Kumele kubekho enye kuphela!"

Kwi-SQL, uchaza "yintoni" ofuna ukuyifumana, hayi "njani" kufuneka yenziwe. Ke ngoko, ingxaki yokuphuhlisa imibuzo yeSQL kwisitayile esithi "njengoko kuviwa ukuba kubhalwe njani" ithatha indawo yayo yembeko, kunye iimpawu zokubala iimeko kwi SQL.

Namhlanje, sisebenzisa imizekelo elula kakhulu, makhe sibone ukuba oku kunokukhokelela ngantoni kumxholo wokusetyenziswa GROUP/DISTINCT и LIMIT nabo.

Ngoku, ukuba ubhale kwisicelo "Qala ngokudibanisa le miqondiso, kwaye emva koko ulahle zonke iikopi, kufuneka kushiyeke abe mnye kuphela khuphela isitshixo ngasinye" - le yindlela eya kusebenza ngayo, nokuba uxhumano lwalungadingeki konke konke.

Kwaye ngamanye amaxesha unethamsanqa kwaye "isebenza nje", ngamanye amaxesha inempembelelo engathandekiyo ekusebenzeni, kwaye ngamanye amaxesha inika imiphumo engalindelekanga ngokupheleleyo kwimbono yomphuhlisi.

Ewe, ayingomtsalane kangako, kodwa...

"Isibini esimnandi": JOIN + DIISTINCT

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

Bekuya kucaca ukuba bafuna ntoni khetha iirekhodi X apho kukho iirekhodi kwi-Y ezinxulumene nemeko ezalisekileyo. Ubhale isicelo nge JOIN -Ufumene amaxabiso e-pk amatyeli aliqela (kanye ukuba mangaphi amangeno afanelekileyo avele kwiY). Indlela ukususa? Ngokuqinisekileyo DISTINCT!

Kuyayolisa ngakumbi xa kwirekhodi lika-X ngalinye kukho amakhulu aliqela anxulumene neerekhodi zika-Y, kwaye ke okuphindiweyo kususwa ngobugorha...

Ukulungisa njani? Ukuqala, qaphela ukuba ingxaki inokulungiswa "khetha iirekhodi zika-X apho ku-Y kukho UBUNGNCINANE ENYE enxulumene nemeko ezalisekileyo" - emva kwayo yonke loo nto, asidingi nto kwi-Y-rekhodi ngokwayo.

Ifakwe EXISTS

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

Ezinye iinguqulelo zePostgreSQL ziyaqonda ukuba kwi-EXISTS kwanele ukufumana ukungena kokuqala okuza, abantu abadala abakwenzi. Ngoko ke ndikhetha ukuhlala ndibonisa LIMIT 1 ngaphakathi EXISTS.

NGENXA YONA

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;

Ukhetho olufanayo luvumela, ukuba kukho imfuneko, ukubuyisela ngokukhawuleza enye idatha esuka kwirekhodi ye-Y edibeneyo. Ukhetho olufanayo luxutyushwa kwinqaku "I-PostgreSQL Antipatterns: irekhodi elinqabileyo liya kufikelela embindini we-JOIN".

“Kutheni uhlawula ngaphezulu”: DIISTINCT [KU] + UMDA 1

Inzuzo eyongezelelweyo yolo tshintsho lwemibuzo kukukwazi ukunciphisa lula ukukhangela iirekhodi ukuba enye okanye ezimbalwa zazo ziyafuneka, njengale meko ilandelayo:

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

Ngoku sifunda isicelo kwaye sizame ukuqonda ukuba i-DBMS icetywayo ukwenza ntoni:

• ukudibanisa iimpawu
• yahlukile nge-X.pk
• kumangeniso aseleyo, khetha ibe nye

Ufumene ntoni ke? "Ukungena nje kube nye" ukusuka kwabakhethekileyo - kwaye ukuba sithatha le yezinye ezingezona zodwa, ngaba umphumo uya kutshintsha ngandlela-thile? .. "Kwaye ukuba akukho mahluko, kutheni uhlawula ngaphezulu?"

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

Kwaye ngokuchanekileyo isihloko esifanayo kunye GROUP BY + LIMIT 1.

“Kufuneka ndibuze nje”: IQELA elifihlakeleyo + LIMIT

Izinto ezifanayo zenzeka ngeendlela ezahlukeneyo iitshekhi ezingenanto iimpawu okanye ii-CTEs njengoko isicelo siqhubela phambili:

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

Imisebenzi edibeneyo (count/min/max/sum/...) zikhutshwe ngempumelelo kwisethi yonke, nangaphandle kwemiyalelo ecacileyo GROUP BY. Kuphela kunye LIMIT abanabuhlobo kakhulu.

Umphuhlisi unokucinga "ukuba kukho iirekhodi apho, ke andifuni ngaphezulu kwe-LIMIT". Kodwa musa ukuyenza loo nto! Kuba ngokwesiseko kukuba:

• bala into abayifunayo ngokwazo zonke iirekhodi
• banike imigca emininzi njengoko becela

Ngokuxhomekeke kwiimeko ekujoliswe kuzo, kufanelekile ukwenza enye yezi zilandelayo endaweni:

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

"Imalini yokuxhoma kwiigram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Umphuhlisi ongenangqondo unokukholelwa ngokunyanisekileyo ukuba isicelo siya kuyeka ukuphumeza. kamsinya nje sakufumana i-1 yeedola zamaxabiso okuqala ahlukeneyo avelayo.

Ngamanye amaxesha kwixesha elizayo oku kunokusebenza kwaye kuya kusebenza ngokubulela kwi-node entsha Isalathisi Skip Scan, ukuphunyezwa kwayo okwangoku kusetyenzwa, kodwa okwangoku.

Okwangoku kuqala zonke iirekhodi ziya kufunyanwa, zahlukile, kwaye kuphela kuzo apho kuya kubuyiselwa imali eceliweyo. Kubuhlungu ngakumbi ukuba sifuna into enjalo $ 1 = 4, kwaye kukho amakhulu amawaka eerekhodi kwitheyibhile...

Ukuze singabi lusizi ngelize, masisebenzise umbuzo ophindaphindayo "I-DIISTINCT yeyamahlwempu" ukusuka kwi-PostgreSQL Wiki:

umthombo: www.habr.com