PostgreSQL Antipatterns: "Lazima kuwe na moja tu!"

Katika SQL, unaelezea "nini" unataka kufikia, sio "jinsi" inapaswa kutekelezwa. Kwa hivyo, shida ya kukuza maswali ya SQL kwa mtindo wa "kama inavyosikika ndivyo ilivyoandikwa" inachukua nafasi yake ya heshima, pamoja na vipengele vya kuhesabu hali katika SQL.

Leo, kwa kutumia mifano rahisi sana, hebu tuone ni nini hii inaweza kusababisha katika muktadha wa matumizi GROUP/DISTINCT и LIMIT pamoja nao.

Sasa, ikiwa uliandika katika ombi "Kwanza unganisha ishara hizi, na kisha utupe nakala zote, ibaki moja tu nakala kwa kila ufunguo" - hii ndio jinsi itafanya kazi, hata ikiwa unganisho haukuhitajika kabisa.

Na wakati mwingine una bahati na "inafanya kazi tu", wakati mwingine ina athari mbaya juu ya utendaji, na wakati mwingine inatoa athari ambazo hazijatarajiwa kabisa kutoka kwa mtazamo wa msanidi programu.

Kweli, labda sio ya kuvutia sana, lakini ...

"Wapenzi wapenzi": JIUNGE + DIISTINCT

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

Ingekuwa wazi wanachotaka chagua rekodi X ambazo kuna rekodi katika Y ambazo zinahusiana na hali iliyotimizwa. Aliandika ombi kupitia JOIN - ilipata maadili ya pk mara kadhaa (haswa ni maingizo ngapi yanafaa katika Y). Jinsi ya kuondoa? Hakika DISTINCT!

Hasa "inafurahisha" wakati kwa kila rekodi ya X kuna mia kadhaa ya rekodi zinazohusiana na Y, na kisha nakala zinaondolewa kishujaa ...

Jinsi ya kurekebisha? Kuanza, tambua kuwa shida inaweza kurekebishwa "chagua rekodi X ambazo katika Y kuna ANGALAU MOJA inayohusishwa na hali iliyotimizwa" - baada ya yote, hatuhitaji chochote kutoka kwa rekodi ya Y yenyewe.

Nested EXISTS

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

Matoleo mengine ya PostgreSQL yanaelewa kuwa katika EXISTS inatosha kupata kiingilio cha kwanza kinachokuja, wazee hawana. Kwa hivyo napendelea kuashiria kila wakati LIMIT 1 ndani EXISTS.

ALATERAL JIUNGE

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;

Chaguo sawa huruhusu, ikiwa ni lazima, kurudisha mara moja data fulani kutoka kwa rekodi ya Y iliyopatikana. Chaguo kama hilo linajadiliwa katika makala hiyo "PostgreSQL Antipatterns: rekodi adimu itafikia katikati ya JIUNGE".

"Kwa nini ulipe zaidi": DISTINCT [ON] + LIMIT 1

Faida ya ziada ya mabadiliko kama haya ya hoja ni uwezo wa kupunguza kwa urahisi utaftaji wa rekodi ikiwa ni moja tu au chache kati yao zinahitajika, kama ilivyo katika kesi ifuatayo:

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

Sasa tunasoma ombi na jaribu kuelewa ni nini DBMS inapendekezwa kufanya:

• kuunganisha ishara
• kipekee na X.pk
• kutoka kwa maingizo yaliyobaki, chagua moja

Kwa hivyo ulipata nini? "Ingizo moja tu" kutoka kwa zile za kipekee - na ikiwa tutachukua hii isiyo ya kipekee, matokeo yatabadilika kwa njia fulani? .. "Na ikiwa hakuna tofauti, kwa nini ulipe zaidi?"

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

Na mada sawa na GROUP BY + LIMIT 1.

"Lazima niulize tu": GROUP isiyo wazi + LIMIT

Mambo yanayofanana hutokea kwa njia tofauti ukaguzi usio na utupu ishara au CTE wakati ombi linaendelea:

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

Majukumu ya jumla (count/min/max/sum/...) hutekelezwa kwa mafanikio kwenye seti nzima, hata bila maagizo ya wazi GROUP BY. Tu na LIMIT hawana urafiki sana.

Msanidi programu anaweza kufikiria "Ikiwa kuna rekodi hapo, basi sihitaji zaidi ya LIMIT". Lakini usifanye hivyo! Kwa sababu kwa msingi ni:

• hesabu wanachotaka kulingana na rekodi zote
• toa mistari mingi kadiri wanavyouliza

Kulingana na hali inayolengwa, inafaa kufanya moja ya mbadala zifuatazo:

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

"Ni kiasi gani cha kunyongwa kwa gramu": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Msanidi programu asiye na akili anaweza kuamini kwa dhati kwamba ombi litaacha kutekelezwa. mara tu tunapopata $1 ya thamani tofauti za kwanza zinazopatikana.

Wakati fulani katika siku zijazo hii inaweza na itafanya kazi kwa shukrani kwa nodi mpya Fahirisi Ruka Uchanganuzi, utekelezaji wake ambao kwa sasa unafanyiwa kazi, lakini bado.

Kwa sasa kwanza rekodi zote zitarejeshwa, ni za kipekee, na ni kutoka kwao tu kiasi kilichoombwa kitarejeshwa. Inasikitisha sana ikiwa tunataka kitu kama hicho $ 1 = 4, na kuna mamia ya maelfu ya rekodi kwenye jedwali...

Ili tusiwe na huzuni bure, hebu tumia swali la kujirudia "DISTINCT ni ya maskini" kutoka PostgreSQL Wiki:

Chanzo: mapenzi.com