PostgreSQL Antipatterns: "Бир гана болушу керек!"

SQLде сиз "кандай" аткарылышы керектигин эмес, "эмнеге" жетүүнү каалап жатканыңызды сүрөттөп бересиз. Ошондуктан, SQL суроо-талаптарын "угуу кандай болсо, ошондой жазылат" стилинде иштеп чыгуу көйгөйү өзүнүн урматтуу ордун ээлейт. SQLде шарттарды эсептөөнүн өзгөчөлүктөрү.

Бүгүн, өтө жөнөкөй мисалдарды колдонуу менен, бул колдонуу контекстинде эмнеге алып келиши мүмкүн экенин карап көрөлү GROUP/DISTINCT и LIMIT алар менен.

Эми суранычка жазсанар «Адегенде бул белгилерди туташтырыңыз, анан бардык дубликаттарды ыргытыңыз, бир гана калышы керек ар бир ачкыч үчүн көчүрүү" - туташуу такыр керек болбосо да, так ушундай болот.

Жана кээде сиз бактылуусуз жана ал "жөн эле иштейт", кээде ал аткарууга жагымсыз таасирин тийгизет, ал эми кээде иштеп чыгуучунун көз карашы боюнча таптакыр күтүүсүз эффекттерди берет.

Ооба, балким, анчалык деле укмуш эмес, бирок...

"Таттуу түгөй": КОШУЛУҢУЗ + ДИСТИНКТ

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

Алар эмнени каалашканы түшүнүктүү болмок аткарылган шартка байланыштуу Y жазуусу бар X жазууларды тандаңыз. аркылуу арыз жазды JOIN - бир нече жолу pk маанилерин алган (так Y ичинде канча ылайыктуу жазуу пайда болгон). Кантип алып салуу керек? Албетте DISTINCT!

Айрыкча, ар бир X-жазуусу үчүн бир нече жүздөгөн Y-жазмалары бар болгондо, андан кийин дубликаттар баатырларча алынып салынганда "кубанычтуу"...

Кантип оңдоо керек? Баштоо үчүн, көйгөйдү өзгөртүүгө болорун түшүнүңүз "X жазууларды тандаңыз, алар үчүн Y ичинде аткарылган шарт менен байланышы бар АЗЫНДА БИР." - Кантсе да, бизге Y-жазуунун өзүнөн эч нерсе керек эмес.

Уюшкан EXISTS

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-жазуунун кээ бир маалыматтарды кайтарып берүүгө мүмкүндүк берет. Окшош вариант макалада талкууланат "PostgreSQL Antipatterns: сейрек рекорд 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

Окшош нерселер ар кандай болот бош эмес текшерүүлөр өтүнүчтүн жүрүшүнө жараша белгилер же CTE:

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

агрегаттык функциялар (count/min/max/sum/...) ачык көрсөтмөлөрсүз да бүтүндөй комплексте ийгиликтүү аткарылат GROUP BY. Менен гана 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 долларын тапканыбыз менен.

Келечекте бул жаңы түйүндүн аркасында иштеши мүмкүн жана иштей берет Index Skip Scan, аны ишке ашыруу учурда иштелип жатат, бирок азырынча жок.

Азырынча биринчи бардык жазуулар алынат, уникалдуу болуп саналат жана алардан гана суралган сумма кайтарылат. Бул сыяктуу бир нерсени кааласак, өзгөчө өкүнүчтүү $ 1 = 4, жана таблицада жүз миңдеген жазуулар бар...

Бекер капа болбоо үчүн, келгиле, рекурсивдүү суроону колдонолу PostgreSQL Wikiден "DISTINCT кедейлер үчүн":

Source: www.habr.com