ProHoster > Blog > Amministrazione > Antipatterns PostgreSQL: Navigazione in u Registru

Antipatterns PostgreSQL: Navigazione in u Registru

Oghje ùn ci sarà micca casi cumplessi è algoritmi sofisticati in SQL. Tuttu serà assai simplice, à u livellu di Captain Obvious - femu vede u registru di l'avvenimenti ordinatu per tempu.

Questu hè, ci hè un segnu in a basa di dati events, è hà un campu ts - esattamente l'ora à quale vulemu vede sti registri in modu ordinatu:

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

CREATE INDEX ON events(ts DESC);

Hè chjaru chì ùn averemu micca una decina di dischi, cusì avemu bisognu di qualchì forma navigazione di pagina.

#0. "Sò u pogromista di a mo mamma"

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

Ùn hè quasi micca un scherzu - hè raru, ma si trova in a natura. A volte, dopu avè travagliatu cù ORM, pò esse difficiule di cambià à u travagliu "direttu" cù SQL.

Ma andemu à prublemi più cumuni è menu evidenti.

#1. OFFSET

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

Da induve vene u numeru 26 ? Questu hè u numeru apprussimativu di entrate per riempie una schermata. Più precisamente, 25 records affissati, più 1, signalendu chì ci hè almenu qualcosa più in u sample è hè sensu di passà.

Di sicuru, stu valore ùn pò micca esse "cusitu" in u corpu di a dumanda, ma passatu per un paràmetru. Ma in questu casu, u pianificatore PostgreSQL ùn serà micca capaci di s'appoghjanu nantu à a cunniscenza chì ci deve esse relativamente pochi registri - è facilmente sceglie un pianu inefficace.

È mentre chì in l'interfaccia di l'applicazione, a visualizazione di u registru hè implementata cum'è cambià trà "pagine" visuale, nimu ùn nota nunda di suspettu per un bellu pezzu. Esattamente finu à u mumentu quandu, in a lotta per a cunvenzione, UI / UX decide di rimettà l'interfaccia à "scroll infinitu" - vale à dì, tutte e voci di u registru sò disegnate in una sola lista chì l'utilizatore pò scroll up and down.

È cusì, durante a prossima prova, site catturatu duplicazione di registri in u registru. Perchè, perchè a tavula hà un indice normale (ts), da quale si basa a vostra dumanda ?

Esattamente perchè ùn avete micca pigliatu in contu ts ùn hè micca una chjave unica in sta tavula. In fatti, è i so valori ùn sò micca unichi, cum'è ogni "tempu" in cundizioni reali - per quessa, u listessu record in duie dumande adiacenti facilmente "salta" da una pagina à l'altra per via di un ordine finale sfarente in u quadru di sorte u listessu valore chjave.

In fatti, ci hè ancu un secondu prublema oculatu quì, chì hè assai più difficiuli di nutà - alcune entrate ùn saranu micca mostrate per nunda! Dopu tuttu, i registri "duplicati" anu pigliatu u locu di l'altru. Una spiegazione dettagliata cù belli ritratti pò esse truvata leghje quì.

Espansione di l'indice

Un sviluppatore astutu capisce chì a chjave d'indici deve esse fatta unica, è a manera più faciule hè di espansione cù un campu ovviamente unicu, chì PK hè perfettu per:

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

È a dumanda muta:

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

#2. Cambia à "cursori"

Qualchì tempu dopu, un DBA vene à voi è hè "piacu" chì e vostre dumande caricanu u servitore cum'è l'infernu cù e so regule OFFSET, è in generale, hè ora di cambià à navigazione da l'ultimu valore mostratu. A vostra dumanda muta di novu:

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

Avete respiratu un suspiru di sollievu finu à chì hè ghjuntu ...

#3. Indici di pulizia

Perchè un ghjornu u vostru DBA leghje articulu nantu à truvà indici inefficaci è capitu chì "micca l'ultimu" timestamp ùn hè micca bonu. È sò venutu à voi di novu - avà cù u pensamentu chì quellu indice deve ancu turnà in daretu (ts DESC).

Ma chì fà cù u prublema iniziale di i registri di "saltà" trà e pagine? .. È tuttu hè simplice - avete bisognu di selezziunà blocchi cù un numeru di dischi unfixed!

In generale, quale ci pruibisce di leghje micca "esattamente 26", ma "micca menu di 26"? Per esempiu, cusì chì in u prossimu blocu ci sò registri cù significati evidenti diversi ts - allora ùn ci sarà micca un prublema cù i registri di "saltà" trà i blocchi!

Eccu cumu per ottene questu:

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;

Chì si passa quì ?

  1. Passemu 25 records "down" è uttene u valore "limite". ts.
  2. Se ùn ci hè nunda, allora rimpiazzà u valore NULL cù -infinity.
  3. Sostramu u segmentu tutale di valori trà u valore ricevutu ts è u paràmetru $ 1 passatu da l'interfaccia (l'ultimu "valore" prestatu precedente).
  4. Se un bloccu hè tornatu cù menu di 26 records, hè l'ultimu.

O listessa foto:
Antipatterns PostgreSQL: Navigazione in u Registru

Perchè avà avemu a mostra ùn hà micca un "iniziu" specificu, Tandu nunda ùn ci impedisce di "espansione" sta dumanda in a direzzione opposta è di implementà a carica dinamica di blocchi di dati da u "puntu di riferimentu" in e duie direzzione - sia in basso sia in su.

Nota

  1. Iè, in questu casu, accede à l'indici duie volte, ma tuttu hè "puramente per indice". Per quessa, una subquery solu risultatu in à una scansione addiziale di Index Only.
  2. Hè abbastanza ovvi chì sta tecnica pò esse usata solu quandu avete valori ts pò attraversà solu per casu, è ùn ci sò assai di elli. Se u vostru casu tipicu hè "un milione di dischi à 00:00:00.000", ùn deve micca fà questu. Vogliu dì, ùn deve micca permette chì un tali casu accade. Ma s'ellu succede, aduprate l'opzione cù un indice estensu.

Source: www.habr.com

Add a comment