🥇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 бичлэг олдож, дараа нь хуулбарыг баатарлаг байдлаар устгасан нь "баяртай" юм ...

Үүнийг яаж засах вэ? Эхлээд даалгаврыг өөрчлөх боломжтой гэдгийг ойлгох хэрэгтэй "Y-д хангагдаж буй нөхцөлтэй холбоотой НААДДАЖ НЭГ ХҮҮ бичлэг байгаа X бичлэгүүдийг сонгоно уу" - Эцсийн эцэст бидэнд 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: Бичлэг нь НЭГДЛИЙН дунд хүрэх нь ховор.

Яагаад илүү төлөх ёстой вэ: 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":

Эх сурвалж: www.habr.com