ProHoster > Blog > Идораи > Antipatterns PostgreSQL: "Бояд танҳо як бошад!"

Antipatterns PostgreSQL: "Бояд танҳо як бошад!"

Дар SQL, шумо "чӣ" -ро ба даст овардан мехоҳед, тасвир мекунед, на "чӣ гуна" ин бояд иҷро шавад. Аз ин рӯ, масъалаи таҳияи дархостҳои SQL дар услуби "чун шунида мешавад, чӣ гуна навишта мешавад" дар баробари Хусусиятҳои арзёбии вазъият дар SQL.

Имрӯз, бо истифода аз мисолҳои хеле содда, биёед бубинем, ки ин метавонад дар заминаи истифода ба чӣ оварда расонад GROUP/DISTINCT и LIMIT бо онҳо.

Ин агар шумо дар дархост навиштед "Аввал ин планшетҳоро пайваст кунед ва сипас ҳама нусхаҳоро партоед, бояд танҳо як бошад мисол барои ҳар як калид" - ин маҳз ҳамин тавр кор хоҳад кард, ҳатто агар пайвастшавӣ умуман лозим набошад.

Ва баъзан шумо хушбахт ҳастед ва он "фақат кор мекунад", баъзан он ба иҷроиш таъсири ногувор мерасонад ва баъзан он эффектҳое медиҳад, ки аз нуқтаи назари таҳиякунанда комилан ғайричашмдоштанд.

Antipatterns PostgreSQL: "Бояд танҳо як бошад!"
Хуб, шояд он қадар аҷиб нест, аммо…

"Ҷуфти ширин": ҲАМРОҲ шавед + ДИСТИНТ

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

Чӣ тавр маълум мешавад, ки онҳо чӣ мехоҳанд чунин сабтҳои X-ро интихоб кунед, ки барои онҳо дар Y бо шарти иҷрошуда алоқаманданд. Тавассути дархост дархост ирсол намуд JOIN - якчанд маротиба арзишҳои pk гирифтаанд (маҳз чанд сабти мувофиқ дар Y буд). Чӣ тавр хориҷ кардан мумкин аст? Албатта DISTINCT!

Ин махсусан "хушбахт" аст, вақте ки барои ҳар як сабти X чандсад сабти марбут ба Y мавҷуд аст ва сипас такрорӣ қаҳрамонона нест карда мешавад ...

Antipatterns PostgreSQL: "Бояд танҳо як бошад!"

Чӣ тавр ислоҳ кардан? Барои оғоз кардан, дарк кунед, ки вазифаро метавон тағир дод "он сабтҳои X-ро интихоб кунед, ки барои онҳо ҳадди аққал як дар Y бо шарти иҷрошаванда алоқаманд аст" - Охир, ба мо аз худи сабти Y чизе лозим нест.

Ҷойгиршуда вуҷуд дорад

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

Баъзе версияҳои PostgreSQL мефаҳманд, ки дар EXISTS пайдо кардани сабти аввалине, ки дар он пайдо мешавад кифоя аст, версияҳои калонсол не. Аз ин рӯ, ман ҳамеша нишон доданро афзалтар медонам LIMIT 1 дар дохили EXISTS.

ХАМРОХ ШУДАН

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;

Ҳамин вариант имкон медиҳад, ки дар ҳолати зарурӣ дар як вақт баъзе маълумотро аз сабти алоқаманди Y-пайдошуда фавран баргардонад. Варианти шабеҳ дар мақола муҳокима карда мешавад "Antipatterns PostgreSQL: сабти нодир ба миёнаи JOIN мерасад".

"Чаро бештар пардохт кунед": DISTINCT [ON] + LIMIT 1

Бартарии иловагии чунин табдилдиҳии дархостҳо қобилияти ба осонӣ маҳдуд кардани номгӯи сабтҳо мебошад, агар танҳо як/чанди онҳо лозим бошад, ба монанди дар ҳолати зерин:

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

Ҳоло мо дархостро мехонем ва кӯшиш мекунем фаҳмем, ки DBMS бояд чӣ кор кунад:

  • плитахоро пайваст мекунем
  • беназири X.pk
  • яке аз сабтҳои боқимондаро интихоб кунед

Пас шумо чӣ гирифтед? "Як сабт" аз нотакрор - ва агар шумо ин яке аз нотакрорҳоро бигиред, натиҷа ба гунае тағир меёбад? .. "Ва агар фарқият вуҷуд надошта бошад, чаро бештар пардохт кунед?"

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

Ва маҳз ҳамон мавзӯъ бо GROUP BY + LIMIT 1.

"Ман танҳо бояд пурсам": GROUP + LIMIT

Чунин чизҳо дар гуногун рух медиҳанд санҷишҳои холигӣ тамғакоғазҳо ё CTEs ҳангоми пешрафти дархост:

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

Функсияҳои ҷамъоварӣ (count/min/max/sum/...) дар тамоми комплект бомуваффакият ичро карда мешаванд, хатто бе аник нишон додани он GROUP BY. Танҳо дар ин ҷо бо LIMIT чандон дустона нестанд.

Таҳиягар метавонад фикр кунад "Ҳоло, агар дар он ҷо сабтҳо вуҷуд дошта бошанд, пас ба ман бештар аз LIMIT лозим нест". Аммо ба шумо лозим нест! Зеро барои асос ин:

  • он чизеро, ки онҳо мехоҳанд, ҳисоб кунанд дар ҳама сабтҳо
  • чи кадар пурсанд, чанд сатр дихед

Вобаста ба шартҳои мақсаднок, яке аз ивазкуниҳои зерин мувофиқ аст:

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

"Чӣ қадар бояд бо грамм овезон шавад": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Таҳиягари соддадил метавонад самимона бовар кунад, ки иҷрои дархост қатъ мешавад, вақте ки мо арзишҳои гуногуни $1-ро пайдо мекунем, ки дар онҳо пайдо мешаванд.

Баъзан дар оянда, ин метавонад ба шарофати гиреҳи нав кор кунад ва кор хоҳад кард Индекс гузаред, ки дар айни замой татбици он кор карда мешавад, вале то хол.

То хол аввал ҳама сабтҳо гирифта мешаванд, беназир аст ва танҳо шумораи зиёди онҳо ба қадри дархост баргардонида мешаванд. Ин махсусан ғамгин аст, агар мо чизе ба монанди мехостем $ 1 = 4, ва дар ҷадвал садҳо ҳазор сабтҳо мавҷуданд...

Барои он ки беҳуда ғамгин нашавем, мо дархости рекурсивиро истифода мебарем "DISTINCT барои камбизоатон" аз PostgreSQL Wiki:

Antipatterns PostgreSQL: "Бояд танҳо як бошад!"

Манбаъ: will.com

Илова Эзоҳ