ProHoster > Pūnaewele > Nā Administration > PostgreSQL Antipatterns: "Pono hoʻokahi wale nō!"

PostgreSQL Antipatterns: "Pono hoʻokahi wale nō!"

Ma SQL, wehewehe ʻoe i ka "mea" āu e makemake ai e hoʻokō, ʻaʻole "pehea" e hoʻokō ʻia ai. No laila, ʻo ka pilikia o ka hoʻomohala ʻana i nā nīnau SQL ma ke ʻano o "e like me ka mea i lohe ʻia ke ʻano o ka kākau ʻia ʻana" i kona wahi hanohano, me nā hiʻohiʻona o ka helu ʻana i nā kūlana ma SQL.

I kēia lā, me ka hoʻohana ʻana i nā hiʻohiʻona maʻalahi loa, e ʻike kākou i ka mea e hiki ai i kēia ke alakaʻi i ka pōʻaiapili o ka hoʻohana GROUP/DISTINCT и LIMIT me lakou.

Ano, ina oe i kakau ma ka noi "E hoʻopili mua i kēia mau hōʻailona, ​​​​a laila e hoʻolei i nā kope a pau, he hookahi wale no koe kope no kēlā me kēia kī" - ʻo ia ke ʻano e hana ai, ʻoiai inā ʻaʻole pono ka pilina.

A i kekahi manawa ua laki ʻoe a "hana wale", i kekahi manawa he hopena maikaʻi ʻole ia i ka hana, a i kekahi manawa hāʻawi ia i nā hopena i manaʻo ʻole ʻia mai ka manaʻo o ka mea hoʻomohala.

PostgreSQL Antipatterns: "Pono hoʻokahi wale nō!"
ʻAe, ʻaʻole paha he mea kupanaha, akā ...

“Hoa wahine aloha”: JOIN + DISTINCT

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

Akaka ko lakou makemake koho i nā moʻolelo X nona nā moʻolelo ma Y e pili ana i ke kūlana i hoʻokō ʻia. Kākau i noi ma o JOIN - loaʻa kekahi mau pk waiwai i kekahi mau manawa (ehia mau helu kūpono i ʻike ʻia ma Y). Pehea e wehe ai? ʻOiaʻiʻo DISTINCT!

ʻOi aku ka "ʻoluʻolu" inā loaʻa i kēlā me kēia X-record he mau haneli Y-records pili, a laila hoʻoneʻe ʻia nā kope ...

PostgreSQL Antipatterns: "Pono hoʻokahi wale nō!"

Pehea e hoʻoponopono ai? I ka hoʻomaka ʻana, e ʻike hiki ke hoʻololi ʻia ka pilikia "koho i nā moʻolelo X ma Y aia ma ka liʻiliʻi hoʻokahi e pili ana me ke kūlana i hoʻokō ʻia" - ma hope o nā mea a pau, ʻaʻole pono mākou i kekahi mea mai ka Y-record ponoʻī.

Nested EXISTS

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

Hoʻomaopopo kekahi mau mana o PostgreSQL aia ma EXISTS ua lawa ka loaʻa ʻana o ke komo mua e puka mai ana, ʻaʻole nā ​​mea kahiko. No laila makemake wau e hōʻike mau LIMIT 1 loko EXISTS.

HUI LAHUI

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;

ʻO ke koho like e hiki ai, inā pono, e hoʻihoʻi koke i kekahi mau ʻikepili mai ka Y-record pili i loaʻa. Kūkākūkā ʻia kahi koho like ma ka ʻatikala "PostgreSQL Antipatterns: hiki i kahi moʻolelo kakaʻikahi i waena o kahi JOIN".

“No ke aha e uku hou aku ai”: DISTINCT [ON] + LIMIT 1

ʻO kahi pōmaikaʻi hou aʻe o ia hoʻololi nīnau ʻana, ʻo ia ka hiki ke kaupalena maʻalahi i ka ʻimi ʻana i nā moʻolelo inā pono hoʻokahi a i ʻole kekahi o lākou, e like me kēia:

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

I kēia manawa heluhelu mākou i ka noi a hoʻāʻo e hoʻomaopopo i ka mea i manaʻo ʻia e DBMS e hana:

  • hoʻopili i nā hōʻailona
  • kū hoʻokahi e X.pk
  • mai nā helu i koe, koho i hoʻokahi

No laila he aha kāu i loaʻa ai? "Hoʻokahi wale nō komo" mai nā mea ʻokoʻa - a inā mākou e lawe i kēia o nā mea kūʻokoʻa ʻole, e loli paha ka hopena?

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

A pololei ke kumuhana like me GROUP BY + LIMIT 1.

"Pono wau e nīnau": implicit GROUP + LIMIT

Loaʻa nā mea like ma nā ʻano like ʻole nā mākaʻikaʻi ʻole nā hōʻailona a i ʻole CTE i ka holo ʻana o ka noi:

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

Nā hana hui (count/min/max/sum/...) ua hoʻokō pono ʻia ma ka pūʻulu holoʻokoʻa, ʻoiai me ka ʻole o nā ʻōlelo kuhikuhi GROUP BY. wale me LIMIT ʻaʻole maikaʻi loa lākou.

Hiki i ka mea hoʻomohala ke noʻonoʻo "inā loaʻa nā moʻolelo ma laila, ʻaʻole pono wau ma mua o LIMIT". Mai hana ʻoe pēlā! No ka mea, no ke kumu:

  • e helu i ko lakou makemake e like me nā moʻolelo a pau
  • hāʻawi i nā laina he nui e like me kā lākou e noi ai

Ma muli o nā kūlana i manaʻo ʻia, kūpono ke hana ʻana i kekahi o kēia mau hoʻololi:

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

“Pehea ka nui o ke kaupaona ana i ka grams”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Hiki i ka mea hoʻomohala naʻaupō ke manaʻoʻiʻo e pau ka hoʻokō ʻana o ka noi. i ka wā e ʻike ai mākou i $1 o nā waiwai like ʻole i loaʻa.

I kekahi manawa i ka wā e hiki mai ana e hana kēia a hoʻomaikaʻi i kahi node hou Hōʻalo i ka papa kuhikuhi, ke hoʻokō ʻia nei i kēia manawa, akā ʻaʻole i kēia manawa.

No kēia manawa mua e kiʻi ʻia nā moʻolelo a pau, he kū hoʻokahi, a mai lākou wale nō e hoʻihoʻi ʻia ka nui i noi ʻia. He mea kaumaha loa inā makemake mākou i kahi mea like $ 1 = 4, a he mau haneli tausani o nā moʻolelo ma ka papa...

I mea e kaumaha ʻole ai, e hoʻohana kāua i kahi nīnau recursive ʻO "DISTINCT no ka poʻe ʻilihune" mai PostgreSQL Wiki:

PostgreSQL Antipatterns: "Pono hoʻokahi wale nō!"

Source: www.habr.com

Pākuʻi i ka manaʻo hoʻopuka