ProHoster > Блог > Administrasi > Antipattern PostgreSQL: Navigasi Registry

Antipattern PostgreSQL: Navigasi Registry

Dina iki ora bakal ana kasus rumit lan algoritma canggih ing SQL. Kabeh bakal gampang banget, ing level Kapten Obvious - ayo nindakake ndeleng registri acara diurutake miturut wektu.

Sing, ana tandha ing database events, lan dheweke duwe lapangan ts - persis wektu nalika kita pengin nampilake cathetan kasebut kanthi cara sing tertib:

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

CREATE INDEX ON events(ts DESC);

Cetha yen kita ora bakal duwe rolas cathetan ana, supaya kita kudu sawetara wangun pandhu arah kaca.

#0. "Aku pogromise ibuku"

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

Iku meh ora guyon - iku arang, nanging ditemokaké ing alam bébas. Kadhangkala, sawise nggarap ORM, bisa uga angel ngalih menyang karya "langsung" karo SQL.

Nanging ayo pindhah menyang masalah sing luwih umum lan kurang jelas.

#1. OFFSET

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

Saka endi angka 26? Iki minangka perkiraan jumlah entri kanggo ngisi siji layar. Luwih tepat, 25 nampilake cathetan, ditambah 1, menehi tandha manawa ana paling ora ana liyane ing sampel kasebut lan nggawe pangertèn kanggo nerusake.

Mesthi, nilai iki ora bisa "dijahit" menyang awak panyuwunan, nanging liwat parameter. Nanging ing kasus iki, panjadwal PostgreSQL ora bakal bisa ngandelake kawruh sing kudu ana sawetara cathetan - lan bakal gampang milih rencana sing ora efektif.

Lan nalika ing antarmuka aplikasi, ndeleng registri dileksanakake minangka ngoper antarane "kaca" visual, ora ana sing weruh apa-apa sing curiga kanggo wektu sing suwe. Persis nganti wayahe, ing perjuangan kanggo penak, UI / UX mutusake kanggo nggawe maneh antarmuka dadi "gulungan tanpa wates" - yaiku, kabeh entri pendaptaran digambar ing dhaptar siji sing pangguna bisa gulung munggah lan mudhun.

Dadi, sajrone tes sabanjure, sampeyan kejiret duplikasi cathetan ing pendaptaran. Apa, amarga tabel duwe indeks normal (ts), pitakon sampeyan gumantung?

Persis amarga sampeyan ora nggatekake ts dudu kunci unik ing tabel iki. Bener, lan nilai-nilai kasebut ora unik, kaya "wektu" ing kahanan nyata - mula, rekaman sing padha ing rong pitakon jejer kanthi gampang "mlumpat" saka kaca menyang kaca amarga urutan pungkasan sing beda ing kerangka ngurutake nilai kunci sing padha.

Nyatane, ana uga masalah nomer loro sing didhelikake ing kene, sing luwih angel digatekake - sawetara entri ora bakal ditampilake sakabehe! Sawise kabeh, cathetan "duplikat" njupuk panggonan wong liya. Panjelasan rinci kanthi gambar sing apik bisa ditemokake waca kene.

Ngembangake indeks

Pangembang sing licik ngerti manawa kunci indeks kudu digawe unik, lan cara paling gampang yaiku nggedhekake kanthi lapangan sing jelas unik, sing cocog karo PK:

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

Lan panjaluk kasebut mutasi:

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

#2. Ngalih menyang "kursor"

Некоторое время спустя к вам приходит DBA и «радует», что ваши запросы padha mbukak server kaya neraka karo aturan OFFSET sing, lan ing umum, iku wektu kanggo ngalih menyang pandhu arah saka nilai pungkasan ditampilake. Pitakonan sampeyan mutasi maneh:

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

Sampeyan ambegan lega nganti teka ...

#3. indeks reresik

Amarga ing sawijining dina DBA sampeyan maca artikel babagan nemokake indeks sing ora efektif lan temen maujud "ora paling anyar" timestamp ora apik. Lan aku teka maneh - saiki karo pikiran sing indeks isih kudu bali menyang (ts DESC).

Nanging apa sing kudu ditindakake kanthi masalah awal "mlumpat" cathetan ing antarane kaca?

Umumé, sing nglarang kita maca ora "persis 26", nanging "ora kurang saka 26"? Contone, supaya ing blok sabanjure ana cathetan karo makna temenan beda ts - banjur ora bakal ana masalah karo "mlumpat" cathetan antarane pamblokiran!

Mangkene carane entuk iki:

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;

Apa sing kedadeyan ing kene?

  1. Kita langkah 25 cathetan "mudhun" lan entuk nilai "wates". ts.
  2. Yen ora ana apa-apa, banjur ganti nilai NULL karo -infinity.
  3. Kita nyuda kabeh bagean saka nilai antarane nilai sing ditampa ts lan $1 parameter liwati saka antarmuka (sadurungé "pungkasan" Nilai render).
  4. Yen pemblokiran bali karo kurang saka 26 cathetan, iku pungkasan.

Utawa gambar sing padha:
Antipattern PostgreSQL: Navigasi Registry

Amarga saiki kita duwe sampel ora duwe "awal" tartamtu, banjur ora ana sing ngalangi kita saka "ngembangake" panjalukan iki ing arah ngelawan lan ngleksanakake loading dinamis pamblokiran data saka "titik referensi" ing loro arah - loro mudhun lan munggah.

Cathetan:

  1. Ya, ing kasus iki kita ngakses indeks kaping pindho, nanging kabeh "murni kanthi indeks". Mulane, subquery mung bakal nyebabake menyang siji Scan Index Mung tambahan.
  2. Iku cukup ketok sing technique iki mung bisa digunakake nalika sampeyan duwe nilai ts bisa nyabrang mung dening kasempatan, lan ana ora akeh. Yen kasus khas sampeyan "sejuta cathetan ing 00:00:00.000", sampeyan ora kudu nindakake iki. Maksudku, sampeyan kudu ora ngidini kasus kaya mengkono. Nanging yen kedadeyan kasebut, gunakake pilihan kanthi indeks lengkap.

Source: www.habr.com

Add a comment