PostgreSQL Antipatterns. «Պետք է լինի միայն մեկը»:

SQL-ում դուք նկարագրում եք «ինչին» եք ցանկանում հասնել, այլ ոչ թե «ինչպես» այն պետք է կատարվի: Հետևաբար, SQL հարցումների մշակման խնդիրը «ինչպես լսվում է այնպես է գրված» ոճով զբաղեցնում է իր պատվավոր տեղը, ինչպես նաև. SQL-ում պայմանների հաշվարկման առանձնահատկությունները.

Այսօր, օգտագործելով չափազանց պարզ օրինակներ, տեսնենք, թե դա ինչի կարող է հանգեցնել օգտագործման համատեքստում GROUP/DISTINCT и LIMIT նրանց հետ.

Հիմա եթե խնդրանքի մեջ գրել ես «Նախ միացրեք այս նշանները, այնուհետև դուրս նետեք բոլոր կրկնօրինակները, պետք է մնա միայն մեկը պատճենեք յուրաքանչյուր բանալիի համար» - հենց այդպես էլ կաշխատի, նույնիսկ եթե կապն ընդհանրապես պետք չէր։

Եվ երբեմն ձեր բախտը բերել է, և դա «ուղղակի աշխատում է», երբեմն այն տհաճ ազդեցություն է ունենում կատարման վրա, և երբեմն այն տալիս է էֆեկտներ, որոնք լիովին անսպասելի են մշակողի տեսանկյունից:

Դե, գուցե ոչ այնքան տպավորիչ, բայց...

«Քաղցր զույգ».

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

Պարզ կլիներ, թե ինչ էին ուզում ընտրեք X գրառումները, որոնց համար Y-ում կան գրառումներ, որոնք կապված են կատարված պայմանի հետ. Հարցում գրեց միջոցով JOIN — մի քանի անգամ ստացել է որոշ pk արժեքներ (հստակ, թե որքան հարմար գրառում է հայտնվել Y-ում): Ինչպե՞ս հեռացնել: Անշուշտ DISTINCT!

Հատկապես «ուրախալի» է, երբ յուրաքանչյուր X-ռեկորդի համար կան մի քանի հարյուր հարակից Y-գրառումներ, իսկ հետո կրկնօրինակները հերոսաբար հանվում են...

Ինչպե՞ս ուղղել: Սկսելու համար, գիտակցեք, որ խնդիրը կարող է փոփոխվել «Ընտրեք 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-գրառումից: Նմանատիպ տարբերակ քննարկվում է հոդվածում «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 նրանք այնքան էլ ընկերասեր չեն:

Մշակողը կարող է մտածել «Եթե այնտեղ գրառումներ կան, ապա ինձ պետք է ոչ ավելի, քան 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, իսկ աղյուսակում հարյուր հազարավոր գրառումներ կան...

Իզուր չտխրելու համար օգտագործենք ռեկուրսիվ հարցում «DISTINCT-ը աղքատների համար է» PostgreSQL Wiki-ից:

Source: www.habr.com