ProHoster > Blog > İdarə > PostgreSQL Antipatternləri: Reyestrdə naviqasiya

PostgreSQL Antipatternləri: Reyestrdə naviqasiya

Bu gün SQL-də mürəkkəb hallar və mürəkkəb alqoritmlər olmayacaq. Kapitan Obvious səviyyəsində hər şey çox sadə olacaq - gəlin bunu edək hadisə reyestrinə baxmaq vaxta görə sıralanır.

Yəni verilənlər bazasında işarə var events, və onun sahəsi var ts - bu qeydləri nizamlı şəkildə göstərmək istədiyimiz vaxt:

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

CREATE INDEX ON events(ts DESC);

Aydındır ki, orada onlarla qeydimiz olmayacaq, ona görə də bizə hansısa formada ehtiyac olacaq səhifə naviqasiyası.

#0. “Mən anamın poqromistiyəm”

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, demək olar ki, zarafat deyil - nadirdir, lakin vəhşi təbiətdə tapılır. Bəzən ORM ilə işlədikdən sonra SQL ilə “birbaşa” işə keçmək çətin ola bilər.

Ancaq daha ümumi və daha az aşkar problemlərə keçək.

#1. OFSET

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

26 rəqəmi haradan gəldi? Bu, bir ekranı doldurmaq üçün girişlərin təxmini sayıdır. Daha doğrusu, 25 qeyd, üstəgəl 1, nümunədə heç olmasa başqa bir şeyin olduğunu və davam etməyin mənası olduğunu göstərir.

Əlbəttə ki, bu dəyər sorğunun gövdəsinə "tikilə bilməz", lakin bir parametrdən keçir. Ancaq bu halda, PostgreSQL planlaşdırıcısı nisbətən az qeydlərin olması lazım olduğu biliyinə etibar edə bilməyəcək - və asanlıqla səmərəsiz plan seçəcək.

Tətbiq interfeysində reyestrə baxmaq vizual "səhifələr" arasında keçid kimi həyata keçirildiyi halda, uzun müddət heç kim şübhəli bir şey görmür. Məhz rahatlıq uğrunda mübarizədə UI/UX interfeysi “sonsuz sürüşdürməyə” çevirmək qərarına gələnə qədər - yəni bütün reyestr qeydləri istifadəçinin yuxarı və aşağı sürüşə biləcəyi vahid siyahıda tərtib edilir.

Beləliklə, növbəti sınaq zamanı siz tutulacaqsınız qeydlərin təkrarlanması reyestrdə. Niyə, çünki cədvəlin normal indeksi var (ts), sorğunuz hansıya əsaslanır?

Məhz ona görə ki, siz bunu nəzərə almamısınız ts unikal açar deyil bu cədvəldə. Əslində və onun dəyərləri unikal deyil, real şəraitdə hər hansı bir "zaman" kimi - buna görə də, iki bitişik sorğuda eyni qeyd eyni açar dəyərinin çeşidlənməsi çərçivəsində fərqli son sıraya görə asanlıqla səhifədən səhifəyə "atılır".

Əslində burada ikinci bir problem də gizlənir, onu fərq etmək daha çətindir - bəzi girişlər göstərilməyəcək bütün! Axı, "dublikat" qeydlər başqasının yerini aldı. Gözəl şəkillərlə ətraflı izahat tapa bilərsiniz bura oxuyun.

İndeksin genişləndirilməsi

Hiyləgər tərtibatçı başa düşür ki, indeks açarını unikal etmək lazımdır və ən asan yol onu PK üçün mükəmməl olan açıq-aydın unikal sahə ilə genişləndirməkdir:

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

Və sorğu mutasiyaya çevrilir:

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

#2. "Kursorlar"a keçin

Bir müddət sonra DBA sizə gəlir və sorğularınızdan “məmnundur” serveri öz OFFSET qaydaları ilə cəhənnəm kimi yükləyirlər, və ümumiyyətlə, keçid vaxtıdır göstərilən son dəyərdən naviqasiya. Sorğunuz yenidən mutasiyaya uğrayır:

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

O gələnə qədər rahat nəfəs aldın...

#3. Təmizləmə indeksləri

Çünki bir gün sizin DBA oxudu səmərəsiz indeksləri tapmaq haqqında məqalə və bunu anladı “ən son deyil” vaxt damğası yaxşı deyil. Mən yenə sizin yanınıza gəldim - indi o indeksin hələ də geri dönməsi fikri ilə (ts DESC).

Bəs səhifələr arasında qeydlərin "atlanması" ilə bağlı ilkin problemlə nə etməli?.. Və hər şey sadədir - sabit olmayan sayda qeydləri olan blokları seçmək lazımdır!

Ümumiyyətlə, bizə “dəqiq 26” yox, “26-dan az olmayan” oxumağı kim qadağan edir? Məsələn, belə ki, növbəti blokda var açıq-aydın fərqli mənaları olan qeydlər ts - onda bloklar arasında qeydləri "atlamaq" ilə bağlı problem olmayacaq!

Buna necə nail olmaq olar:

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;

Burda nə baş verir?

  1. 25 qeydi "aşağı" addımlayırıq və "sərhəd" dəyərini alırıq ts.
  2. Əgər orada artıq heç nə yoxdursa, NULL dəyəri ilə əvəz edin -infinity.
  3. Alınan dəyər arasında bütün dəyərlər seqmentini çıxarırıq ts və interfeysdən keçən $1 parametri (əvvəlki “son” göstərilən dəyər).
  4. Əgər blok 26-dan az qeydlə qaytarılırsa, o, sonuncudur.

Və ya eyni şəkil:
PostgreSQL Antipatternləri: Reyestrdə naviqasiya

Çünki indi bizdə var nümunənin hər hansı xüsusi “başlanğıc”ı yoxdur, onda heç nə bizə bu sorğunu əks istiqamətdə "genişləndirməyə" və "istinad nöqtəsindən" hər iki istiqamətdə - həm aşağı, həm də yuxarı məlumat bloklarının dinamik yüklənməsini həyata keçirməyə mane olmur.

Qeyd:

  1. Bəli, bu halda biz indeksə iki dəfə daxil oluruq, lakin hər şey “sırf indekslə” olur. Beləliklə, bir alt sorğu yalnız nəticə verəcəkdir bir əlavə Yalnız İndeks Skanına.
  2. Tamamilə aydındır ki, bu texnika yalnız dəyərləriniz olduqda istifadə edilə bilər ts yalnız təsadüfən keçə bilər və onların çoxu yoxdur. Tipik işiniz “00:00:00.000-da bir milyon qeyd”dirsə, bunu etməməlisiniz. Demək istəyirəm ki, belə bir halın baş verməsinə icazə verməməlisiniz. Ancaq bu baş verərsə, genişləndirilmiş indekslə seçimi istifadə edin.

Mənbə: www.habr.com

Добавить комментарий