ProHoster > Блог > Gudanarwa > PostgreSQL Antipatterns: "Dole ne a sami ɗaya kawai!"

PostgreSQL Antipatterns: "Dole ne a sami ɗaya kawai!"

A cikin SQL, kuna bayyana "abin da" kuke son cimmawa, ba "yadda" yakamata a aiwatar dashi ba. Don haka, matsalar haɓaka tambayoyin SQL a cikin salon "kamar yadda ake ji shine yadda aka rubuta" ya ɗauki matsayinsa na girmamawa, tare da. fasali na lissafin yanayi a cikin SQL.

A yau, ta yin amfani da misalai masu sauƙi, bari mu ga abin da wannan zai iya haifar da shi a cikin mahallin amfani GROUP/DISTINCT и LIMIT tare da su.

Yanzu, idan kun rubuta a cikin buƙatar “Da farko ku haɗa waɗannan alamun, sannan ku jefar da duk kwafin, saura daya kawai kwafi ga kowane maɓalli" - wannan shine ainihin yadda zai yi aiki, koda kuwa ba a buƙatar haɗin kai kwata-kwata.

Kuma wani lokacin kuna da sa'a kuma yana "kawai yana aiki", wani lokacin yana da tasiri mara kyau akan aikin, kuma wani lokacin yana ba da tasirin da ba a tsammani gaba ɗaya daga ra'ayin mai haɓakawa.

PostgreSQL Antipatterns: "Dole ne a sami ɗaya kawai!"
To, watakila ba abin ban mamaki ba ne, amma ...

"Ma'aurata masu dadi": JOIN + DISTINCT

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

Zai bayyana abin da suke so zaɓi rikodin X wanda akwai bayanai a cikin Y waɗanda ke da alaƙa da yanayin da aka cika. Rubuta bukata ta hanyar JOIN - sami wasu ƙimar pk sau da yawa (daidai nawa shigarwar da suka dace suka bayyana a cikin Y). Yadda za a cire? Tabbas DISTINCT!

Abin farin ciki ne musamman lokacin da kowane rikodin X akwai rikodin Y-rikodi masu alaƙa da yawa, sannan ana cire kwafin kwafin da jaruntaka ...

PostgreSQL Antipatterns: "Dole ne a sami ɗaya kawai!"

Yadda za a gyara? Da farko, gane cewa matsalar za a iya gyara zuwa "zaba records X wanda a cikin Y akwai A KALLA DAYA hade da cikar yanayin" - Bayan haka, ba ma buƙatar wani abu daga Y-rikodin kanta.

EXISTS

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

Wasu nau'ikan PostgreSQL sun fahimci cewa a cikin EXISTS ya isa nemo shigarwar farko da ta fito, tsofaffi ba sa. Don haka na fi son in nuna ko da yaushe LIMIT 1 a ciki EXISTS.

SHIGA LATERAL

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;

Zaɓin iri ɗaya yana ba da damar, idan ya cancanta, dawo da wasu bayanai nan da nan daga rikodin Y mai alaƙa. An tattauna irin wannan zaɓi a cikin labarin "PostgreSQL Antipatterns: rikodin da ba kasafai zai kai tsakiyar JOIN".

"Me yasa ake biyan ƙarin": DISTINCT [ON] + LIMIT 1

Ƙarin fa'idar irin waɗannan sauye-sauyen tambayar ita ce ikon iya iyakance bincike cikin sauƙi idan ana buƙatar ɗaya ko kaɗan daga cikinsu, kamar a cikin yanayi mai zuwa:

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

Yanzu mun karanta buƙatun kuma muna ƙoƙarin fahimtar abin da DBMS ke shirin yi:

  • haɗa alamun
  • na musamman ta X.pk
  • daga sauran abubuwan shigarwa, zaɓi ɗaya

To me kuka samu? "shiga daya kawai" daga na musamman - kuma idan muka ɗauki wannan ɗaya daga cikin waɗanda ba na musamman ba, shin sakamakon zai canza ko ta yaya?.. "Kuma idan babu bambanci, me yasa za a biya ƙarin?"

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

Kuma daidai wannan batu tare da GROUP BY + LIMIT 1.

"Dole ne in yi tambaya": GROUP + LIMIT a fakaice

Abubuwa iri ɗaya suna faruwa a daban-daban duban rashin wofi Alamomi ko CTEs yayin da buƙatar ke ci gaba:

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

Ayyukan tarawa (count/min/max/sum/...) an yi nasarar aiwatar da su a kan dukkan saitin, ko da ba tare da takamaiman umarni ba GROUP BY. Kawai tare da LIMIT ba su da abokantaka sosai.

Mai haɓakawa na iya tunani "Idan akwai bayanai a can, to, ba na buƙatar fiye da LIMIT". Amma kada ku yi haka! Domin ga tushe shine:

  • kirga abinda suke so bisa ga dukkan bayanan
  • ba da layukan da yawa kamar yadda suka tambaya

Ya danganta da yanayin da aka yi niyya, ya dace a yi ɗaya daga cikin abubuwan da ke biyowa:

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

"Nawa don rataya a cikin gram": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Mai haɓaka butulci yana iya yin imani da gaske cewa buƙatar za ta daina aiwatarwa. da zaran mun sami $1 na farko daban-daban dabi'u da suka zo a kan.

Wani lokaci a nan gaba wannan yana iya kuma zai yi aiki godiya ga sabon kumburi Fihirisar Tsallake Scan, wanda a halin yanzu ake aiwatar da aikin, amma ba tukuna ba.

A yanzu da farko za a dawo da duk bayanan, su ne na musamman, kuma daga gare su ne kawai za a mayar da adadin da aka nema. Yana da matukar bakin ciki idan muna son wani abu kamar $ 1 = 4, kuma akwai dubban daruruwan bayanai a cikin tebur ...

Don kada mu yi baƙin ciki a banza, bari mu yi amfani da tambaya mai maimaitawa "DISTINCT ga matalauta ne" daga PostgreSQL Wiki:

PostgreSQL Antipatterns: "Dole ne a sami ɗaya kawai!"

source: www.habr.com

Add a comment