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 katika mtindo wa "kama inavyosikika, ndivyo ilivyoandikwa" inachukua kiburi cha mahali, pamoja na vipengele vya kuhesabu hali katika SQL.

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

Sasa, ikiwa uliandika katika ombi "Kwanza unganisha vidonge hivi, kisha utupe nakala zote, ibaki moja tu nakala kwa kila ufunguo" - hivi ndivyo itakavyofanya 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 hutoa madhara ambayo hayakutarajiwa kabisa kutoka kwa mtazamo wa msanidi.

Kweli, labda sio ya kuvutia, lakini ...

Wanandoa Watamu: JIUNGE + DIISTINCT

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

Ingekuwa wazi wanachotaka chagua rekodi hizo X ambazo Y amezihusisha na hali inayotimizwaTuliandika ombi kupitia JOIN - tulipata maadili ya pk mara kadhaa (haswa ni maingizo ngapi yanayolingana katika Y). Tunawezaje kuwaondoa? Bila shaka. DISTINCT!

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

Jinsi ya kurekebisha? Kwanza, tambua kuwa kazi inaweza kurekebishwa "chagua rekodi hizo X ambazo kuna angalau moja katika Y ambayo inahusishwa na hali inayotimizwa" - 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 EXISTS ni suala la kupata ingizo la kwanza, lakini matoleo ya zamani hayafanyi hivyo. Ndio maana napendelea kutaja 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 hili hili hukuruhusu kurudisha data mara moja kutoka kwa rekodi inayohusiana ya Y, ikiwa ni lazima. Chaguo sawa linajadiliwa katika makala Antipatterns za PostgreSQL: Mara chache Rekodi Hufikia Katikati ya KUJIUNGA.

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

Faida ya ziada ya mabadiliko kama haya ya hoja ni uwezo wa kupunguza kwa urahisi kurudiwa kwa rekodi ikiwa ni moja/chache tu kati yao inahitajika, 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 kujaribu kuelewa ni nini DBMS inatuuliza tufanye:

• kuunganisha ishara
• kipekee na X.pk
• tunachagua moja ya maingizo yaliyobaki

Kwa hiyo tulipata nini? "Ingizo moja maalum" kutoka kwa wale wa pekee - na ikiwa tutachukua hii ya wale wasio wa kipekee, je, matokeo yatabadilika kwa njia yoyote? .. "Na ikiwa hakuna tofauti, kwa nini kulipa 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.

"Nataka tu kuuliza": GROUP isiyo wazi + LIMIT

Mambo yanayofanana hutokea katika hali tofauti ukaguzi usio na utupu majedwali au CTE wakati swala 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 dalili wazi GROUP BYTu na LIMIT Hawana urafiki sana.

Msanidi programu anaweza kufikiria "Ikiwa kuna rekodi huko, basi sihitaji zaidi ya LIMIT"Lakini usifanye hivyo! Kwa sababu kwa hifadhidata, hii ni:

• hesabu wanachotaka kwa rekodi zote
• toa mistari mingi kadiri wanavyoomba

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 kupima kwa gramu": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Msanidi programu asiye na akili anaweza kuamini kwa dhati kwamba utekelezaji wa hoja utakoma, mara tu tunapopata thamani tofauti za $1 za kwanza.

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

Kwa sasa, mambo ya kwanza kwanza rekodi zote zitatolewa, 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 maingizo kwenye jedwali...

Ili kuepuka huzuni isiyo ya lazima, hebu tutumie swali la kujirudia "TOFAUTI kwa maskini" kutoka kwa Wiki ya PostgreSQL:

Chanzo: mapenzi.com