ProHoster > Блог > Захиргаа > PostgreSQL Antipatterns: Бүртгэлд шилжих

PostgreSQL Antipatterns: Бүртгэлд шилжих

Өнөөдөр SQL дээр нарийн төвөгтэй тохиолдол, нарийн алгоритм байхгүй болно. Бүх зүйл маш энгийн, ахмад Обвиусийн түвшинд байх болно - үүнийг хийцгээе үйл явдлын бүртгэлийг үзэх цаг хугацаагаар эрэмбэлсэн.

Өөрөөр хэлбэл, мэдээллийн санд тэмдэг байдаг events, мөн түүнд талбай бий ts - бид эдгээр бүртгэлийг эмх цэгцтэй харуулахыг хүсч буй яг цаг хугацаа:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Бидэнд хэдэн арван бичлэг байхгүй нь тодорхой тул бидэнд ямар нэг хэлбэр хэрэгтэй болно хуудасны навигаци.

#0. "Би ээжийнхээ погромист"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Энэ нь бараг хошигнол биш юм - энэ нь ховор, гэхдээ зэрлэг байгальд олддог. Заримдаа ORM-тэй ажилласны дараа SQL-тэй "шууд" ажил руу шилжихэд хэцүү байдаг.

Гэхдээ илүү нийтлэг, тодорхой бус асуудлууд руу шилжье.

#1. ОФСЕТ

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

26 гэдэг тоо хаанаас ирсэн бэ? Энэ нь нэг дэлгэцийг дүүргэх оролтын ойролцоогоор тоо юм. Нарийвчлан хэлэхэд, 25 бичлэг, дээр нь 1 нь түүвэрт дор хаяж өөр зүйл байгаа бөгөөд цааш явах нь утга учиртай болохыг харуулж байна.

Мэдээжийн хэрэг, энэ утгыг хүсэлтийн үндсэн хэсэгт "оёж" болохгүй, гэхдээ параметрээр дамжуулна. Гэхдээ энэ тохиолдолд PostgreSQL төлөвлөгч нь харьцангуй цөөн бүртгэл байх ёстой гэсэн мэдлэгт найдах боломжгүй бөгөөд үр дүнгүй төлөвлөгөөг амархан сонгох болно.

Програмын интерфэйс дээр бүртгэлийг үзэх нь харааны "хуудсууд" хооронд шилжих байдлаар хэрэгждэг боловч удаан хугацааны туршид хэн ч сэжигтэй зүйлийг анзаардаггүй. Тохиромжтой байдлын төлөөх тэмцэлд UI/UX интерфэйсийг "эцэс төгсгөлгүй гүйлгэх" болгон өөрчлөх шийдвэр гаргах хүртэл, өөрөөр хэлбэл бүртгэлийн бүх оруулгуудыг хэрэглэгч дээш доош гүйлгэж болох нэг жагсаалтад оруулсан болно.

Тиймээс дараагийн шалгалтын үеэр та баригдсан бүртгэлийн давхардал бүртгэлд. Яагаад гэвэл хүснэгт нь ердийн индекстэй байдаг (ts), таны асуулга юунд тулгуурладаг вэ?

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

Үнэндээ энд бас хоёрдахь асуудал нуугдаж байгаа бөгөөд үүнийг анзаарахад илүү хэцүү байдаг - зарим оруулгууд харагдахгүй бүх! Эцсийн эцэст, "давхардсан" бүртгэлүүд өөр хэн нэгний байрыг эзэлсэн. Сайхан зургуудтай дэлгэрэнгүй тайлбарыг олж болно эндээс уншина уу.

Индексийг өргөжүүлж байна

Зальтай хөгжүүлэгч индексийн түлхүүрийг өвөрмөц болгох хэрэгтэй гэдгийг ойлгодог бөгөөд хамгийн хялбар арга бол үүнийг PK-д тохирсон өвөрмөц талбараар өргөжүүлэх явдал юм:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

Мөн хүсэлт нь өөрчлөгддөг:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. "Курсор" руу шилжих

Хэсэг хугацааны дараа DBA тан дээр ирж, таны хүсэлтэд "сэтгэл хангалуун" байна тэд өөрсдийн OFFSET дүрмээр серверийг там шиг ачаалдаг, ерөнхийдөө шилжих цаг нь болсон харуулсан сүүлийн утгаас навигаци. Таны асуулга дахин өөрчлөгдөнө:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Ирэх хүртлээ та санаа амарсан...

#3. Цэвэрлэх индексүүд

Учир нь нэг өдөр таны DBA уншсан үр дүнгүй индексийг олох тухай нийтлэл мөн үүнийг ойлгосон "хамгийн сүүлийн үеийн биш" гэсэн тэмдэгт сайн биш байна. Тэгээд би чам дээр дахин ирлээ - одоо энэ индекс эргээд эргэж байх ёстой гэж бодсон (ts DESC).

Гэхдээ хуудсуудын хооронд бичлэгүүдийг "үсрэх" анхны асуудлыг яах вэ? .. Бүх зүйл энгийн - та тодорхойгүй тооны бичлэг бүхий блокуудыг сонгох хэрэгтэй!

Ер нь “яг 26” биш “26-аас доошгүй” гэж уншихыг хэн хориглодог юм бэ? Жишээлбэл, дараагийн блокт байгаа тул илт өөр өөр утгатай бичлэгүүд ts - тэгвэл блокуудын хооронд бичлэгүүдийг "үсрэх" асуудал гарахгүй!

Үүнд хэрхэн хүрэх вэ:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Энд юу болоод байна?

  1. Бид 25 бичлэгийг "доошоо" алхаж, "хил" утгыг авна ts.
  2. Хэрэв тэнд юу ч байхгүй бол NULL утгыг орлуулаарай -infinity.
  3. Бид хүлээн авсан утгын хоорондох утгын сегментийг бүхэлд нь хасдаг ts болон интерфэйсээс дамжуулсан $1 параметр (өмнөх "сүүлийн" үзүүлсэн утга).
  4. Хэрэв блок 26-аас бага бичлэгтэй буцаж ирвэл энэ нь сүүлчийнх юм.

Эсвэл ижил зураг:
PostgreSQL Antipatterns: Бүртгэлд шилжих

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

Тэмдэглэл

  1. Тийм ээ, энэ тохиолдолд бид индекс рүү хоёр удаа ханддаг, гэхдээ бүх зүйл "цэвэр индексээр" байна. Тиймээс, дэд асуулга нь зөвхөн үр дүнд хүрэх болно Зөвхөн нэг индексийн скан хийх.
  2. Энэ техникийг зөвхөн үнэт зүйлтэй үед л ашиглах нь ойлгомжтой ts зөвхөн тохиолдлоор гаталж болно, мөн тийм ч олон биш. Хэрэв таны ердийн тохиолдол "00:00:00.000 дахь сая бичлэг" бол та үүнийг хийх ёсгүй. Ийм хэрэг гарахыг зөвшөөрөх ёсгүй гэж хэлэх гэсэн юм. Гэхдээ ийм зүйл тохиолдвол өргөтгөсөн индекс бүхий сонголтыг ашиглана уу.

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

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