🥇PostgreSQL antipatterns: registrda navigatsiya

Bugungi kunda SQLda murakkab holatlar va murakkab algoritmlar bo'lmaydi. Kapitan Obvious darajasida hamma narsa juda oddiy bo'ladi - keling, buni qilaylik hodisalar reestrini ko'rish vaqt bo'yicha tartiblangan.

Ya'ni, ma'lumotlar bazasida belgi mavjud events, va uning dalasi bor ts - biz ushbu yozuvlarni tartibli ko'rsatmoqchi bo'lgan aniq vaqt:

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

CREATE INDEX ON events(ts DESC);

Bizda o'nlab yozuvlar bo'lmasligi aniq, shuning uchun bizga qandaydir shakl kerak bo'ladi sahifa navigatsiyasi.

#0. "Men onamning pogromistiman"

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

Bu deyarli hazil emas - bu kamdan-kam uchraydi, lekin tabiatda topilgan. Ba'zan, ORM bilan ishlagandan so'ng, SQL bilan "to'g'ridan-to'g'ri" ishlashga o'tish qiyin bo'lishi mumkin.

Ammo keling, keng tarqalgan va kamroq aniq muammolarga o'tamiz.

#1. OFFSET

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

26 raqami qaerdan paydo bo'lgan? Bu bitta ekranni to'ldirish uchun kiritishlarning taxminiy soni. Aniqrog'i, 25 ta ko'rsatilgan yozuvlar, qo'shimcha 1, namunada hech bo'lmaganda boshqa narsa borligini va davom etish mantiqiy ekanligini bildiradi.

Albatta, bu qiymat so'rovning tanasiga "tikilishi" mumkin emas, lekin parametr orqali o'tadi. Ammo bu holda, PostgreSQL rejalashtiruvchisi nisbatan kam sonli yozuvlar bo'lishi kerakligi haqidagi bilimga tayana olmaydi - va samarasiz rejani osongina tanlaydi.

Va dastur interfeysida ro'yxatga olish kitobini ko'rish vizual "sahifalar" o'rtasida almashish sifatida amalga oshirilganda, uzoq vaqt davomida hech kim shubhali narsani sezmaydi. Aynan qulaylik uchun kurashda UI/UX interfeysni "cheksiz aylantirish" ga o'zgartirishga qaror qilguniga qadar - ya'ni barcha ro'yxatga olish yozuvlari foydalanuvchi yuqoriga va pastga aylantirishi mumkin bo'lgan yagona ro'yxatda tuziladi.

Shunday qilib, keyingi sinov paytida siz qo'lga tushasiz yozuvlarning takrorlanishi registrda. Nima uchun, chunki jadval oddiy indeksga ega (ts), sizning so'rovingiz qaysiga tayanadi?

Siz buni hisobga olmaganingiz uchun ts noyob kalit emas ushbu jadvalda. Aslida, va uning qadriyatlari noyob emas, real sharoitdagi har qanday "vaqt" kabi - shuning uchun ikkita qo'shni so'rovdagi bir xil yozuv bir xil kalit qiymatini saralash doirasidagi boshqa yakuniy tartib tufayli sahifadan sahifaga osongina "sakrab o'tadi".

Aslida, bu erda yashiringan ikkinchi muammo ham bor, uni payqash ancha qiyin - ba'zi yozuvlar ko'rsatilmaydi umuman! Axir, "dublikat" yozuvlari boshqa birovning o'rnini egalladi. Chiroyli rasmlar bilan batafsil tushuntirishni topish mumkin bu erda o'qing.

Indeksni kengaytirish

Ayyor ishlab chiquvchi indeks kalitini noyob qilish kerakligini tushunadi va eng oson yo'li uni PK uchun juda mos bo'lgan aniq noyob maydon bilan kengaytirishdir:

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

Va so'rov mutatsiyaga uchraydi:

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

#2. “Kursorlar”ga o‘tish

Biroz vaqt o'tgach, DBA sizga keladi va sizning so'rovlaringizdan "hursand" bo'ladi ular serverni o'zlarining OFFSET qoidalari bilan do'zax kabi yuklaydilar, va umuman, o'tish vaqti keldi ko'rsatilgan oxirgi qiymatdan navigatsiya. Sizning so'rovingiz yana mutatsiyaga uchraydi:

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

Kelgunicha yengil nafas oldingiz...

#3. Tozalash indekslari

Chunki bir kun sizning DBA o'qiydi samarasiz indekslarni topish haqida maqola va buni angladi "oxirgi emas" vaqt tamg'asi yaxshi emas. Va yana sizning oldingizga keldim - endi bu indeks hali ham qaytib kelishi kerak degan fikr bilan (ts DESC).

Ammo sahifalar orasidagi yozuvlarni "sakrash" ning dastlabki muammosi bilan nima qilish kerak?.. Va hamma narsa oddiy - siz aniqlanmagan sonli yozuvlarga ega bloklarni tanlashingiz kerak!

Umuman olganda, "aniq 26" emas, balki "26 dan kam bo'lmagan" o'qishni kim taqiqlaydi? Masalan, keyingi blokda mavjud bo'lishi uchun turli ma'nolarga ega bo'lgan yozuvlar ts - keyin bloklar orasidagi yozuvlarni "sakrash" bilan bog'liq muammo bo'lmaydi!

Bunga qanday erishish mumkin:

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;

Bu yerda nima bo'lyapti?

Biz 25 yozuvni "pastga" qadam qo'yamiz va "chegara" qiymatini olamiz ts.
Agar u erda hech narsa bo'lmasa, NULL qiymatini bilan almashtiring -infinity.
Qabul qilingan qiymat orasidagi qiymatlarning butun segmentini ayirib tashlaymiz ts va interfeysdan o'tgan $1 parametri (oldingi "oxirgi" ko'rsatilgan qiymat).
Agar blok 26 dan kam yozuv bilan qaytarilsa, u oxirgi hisoblanadi.

Yoki bir xil rasm:

Chunki hozir bizda namunada aniq "boshlanish" yo'q, keyin hech narsa bizga ushbu so'rovni teskari yo'nalishda "kengaytirishga" va ma'lumotlar bloklarini "mos yozuvlar nuqtasi" dan ikkala yo'nalishda ham - pastga va yuqoriga dinamik yuklashni amalga oshirishga to'sqinlik qilmaydi.

Eslatma:

Ha, bu holda biz indeksga ikki marta kiramiz, lekin hamma narsa "faqat indeks bo'yicha". Shuning uchun, pastki so'rov faqat natija beradi bitta qo'shimcha Faqat indeksni skanerlash.
Ko'rinib turibdiki, ushbu texnikadan faqat sizda qadriyatlar mavjud bo'lganda foydalanish mumkin ts faqat tasodifan kesib o'tishi mumkin, va ularning ko'pi yo'q. Agar sizning odatiy holatingiz "00:00:00.000 da million yozuvlar" bo'lsa, buni qilmasligingiz kerak. Aytmoqchimanki, siz bunday holatga yo'l qo'ymasligingiz kerak. Ammo bu sodir bo'lsa, kengaytirilgan indeks bilan opsiyadan foydalaning.

Manba: www.habr.com

PostgreSQL antipatternlari: Ro'yxatga olish kitobida harakatlanish