ProHoster > Блог > Захиргаа > PostgreSQL Antipatterns: "Ганц л байх ёстой!"

PostgreSQL Antipatterns: "Ганц л байх ёстой!"

SQL хэл дээр та "юу" авахыг хүсч байгаагаа тайлбарлахаас биш "хэрхэн" хийх ёстойг биш. Тиймээс SQL асуулгаг "сонссоноор нь бичдэг" гэсэн хэв маягаар хөгжүүлэх асуудал чухал байр суурийг эзэлдэг. SQL дэх нөхцөл байдлын үнэлгээний онцлог.

Өнөөдөр маш энгийн жишээнүүдийг ашиглан энэ нь ашиглалтын нөхцөлд юу хүргэж болохыг харцгаая GROUP/DISTINCT и LIMIT тэдэнтэй хамт.

Хэрэв та хүсэлтэд бичсэн бол тэр юм "Эхлээд эдгээр таблетуудыг холбож, дараа нь бүх хуулбарыг хая. ганц л байх ёстой түлхүүр бүрийн жишээ" - холболт огт шаардлагагүй байсан ч энэ нь яг ийм байдлаар ажиллах болно.

Заримдаа та азтай байдаг бөгөөд энэ нь "зүгээр л ажилладаг", заримдаа энэ нь гүйцэтгэлд таагүй нөлөө үзүүлдэг, заримдаа хөгжүүлэгчийн үүднээс огт санаанд оромгүй эффектүүдийг өгдөг.

PostgreSQL Antipatterns: "Ганц л байх ёстой!"
Магадгүй тийм ч гайхалтай биш, гэхдээ ...

"Амтат хос": НЭГДЭЭРЭЙ + ЯЛГААРАЙ

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

Тэд юу хүсч байгаа нь яаж тодорхой болох вэ Y-д биелэгдсэн нөхцөлтэй холбоотой X бичлэгүүдийг сонгоно уу. дамжуулан хүсэлт илгээсэн JOIN - pk-ийн зарим утгыг хэд хэдэн удаа хүлээн авсан (яг Y-д хэдэн тохиромжтой бичлэг гарсан). Хэрхэн арилгах вэ? Мэдээж DISTINCT!

X бичлэг бүрт хэд хэдэн зуугаар холбогдох Y бичлэг байх бөгөөд дараа нь хуулбарыг баатарлаг байдлаар арилгах нь ялангуяа "тааламжтай" юм ...

PostgreSQL Antipatterns: "Ганц л байх ёстой!"

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

Хөгжүүлэгч сэтгэж чадна "Одоо, хэрэв тэнд бичлэг байгаа бол надад ХЯЗГААР-аас илүү зүйл хэрэггүй". Гэхдээ та тэгэх шаардлагагүй! Учир нь суурийн хувьд энэ нь:

  • юу хүсч байгаагаа тоол бүх бүртгэл дээр
  • тэд хүссэнээрээ олон мөр өг

Зорилтот нөхцлөөс хамааран дараахь орлуулалтын аль нэгийг хийх нь зүйтэй.

  • (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-аас "Ядуу хүмүүст ИЛҮҮ":

PostgreSQL Antipatterns: "Ганц л байх ёстой!"

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

сэтгэгдэл нэмэх