ProHoster > Блог > Administrado > PostgreSQL Antipatterns: Navigante la Registron

PostgreSQL Antipatterns: Navigante la Registron

Hodiaŭ ne estos kompleksaj kazoj kaj kompleksaj algoritmoj en SQL. Ĉio estos tre simpla, je la nivelo de Kapitano Evidenta - ni faru ĝin rigardante la evento-registron ordigitaj laŭ tempo.

Tio estas, estas signo en la datumbazo events, kaj ŝi havas kampon ts - precize la tempo, kiam ni volas montri ĉi tiujn registrojn en orda maniero:

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

CREATE INDEX ON events(ts DESC);

Estas klare, ke ni ne havos dekduon da diskoj tie, do ni bezonos iun formon de paĝa navigado.

#0. "Mi estas la pogromisto de mia patrino"

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

Ĝi preskaŭ ne estas ŝerco – ĝi estas malofta, sed troviĝas en natura medio. Kelkfoje, post laborado kun ORM, povas esti malfacile ŝanĝi al "rekta" laboro kun SQL.

Sed ni transiru al pli oftaj kaj malpli evidentaj problemoj.

#1. OFFSETO

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

De kie venis la numero 26? Ĉi tio estas la proksimuma nombro da enskriboj por plenigi unu ekranon. Pli precize, 25 montritaj registroj, plus 1, signalante, ke estas almenaŭ io alia pli en la specimeno kaj estas senco pluiri.

Kompreneble, ĉi tiu valoro ne povas esti "kudrita" en la korpon de la peto, sed trapasita tra parametro. Sed ĉi-kaze, la postgreSQL-planilo ne povos fidi je la scio, ke devus esti relative malmultaj registroj - kaj facile elektos neefika planon.

Kaj dum en la aplika interfaco, vidi la registron estas efektivigita kiel ŝanĝado inter vidaj "paĝoj", neniu rimarkas ion suspektindan dum longa tempo. Ĝuste ĝis la momento, kiam, en la lukto por oportuno, UI/UX decidas refari la interfacon al "senfina movo" - tio estas, ĉiuj registraj enskriboj estas desegnitaj en ununura listo, kiun la uzanto povas rulumi supren kaj malsupren.

Kaj tiel, dum la sekva provo, vi estas kaptita duobligo de rekordoj en la registro. Kial, ĉar la tabelo havas normalan indekson (ts), al kiu dependas via demando?

Ĝuste ĉar vi ne konsideris tion ts ne estas unika ŝlosilo en ĉi tiu tabelo. Fakte, kaj ĝiaj valoroj ne estas unikaj, kiel ajna "tempo" en realaj kondiĉoj - tial la sama rekordo en du apudaj demandoj facile "saltas" de paĝo al paĝo pro malsama fina ordo kadre de ordigo de la sama ŝlosilvaloro.

Fakte, estas ankaŭ dua problemo kaŝita ĉi tie, kiu estas multe pli malfacile rimarkebla - iuj enskriboj ne estos montrataj tute! Post ĉio, la "duoblaj" rekordoj prenis la lokon de iu alia. Detala klarigo kun belaj bildoj troveblas legu ĉi tie.

Vastigante la indekson

Ruza programisto komprenas, ke la indeksa ŝlosilo devas esti unika, kaj la plej facila maniero estas vastigi ĝin per evidente unika kampo, por kiu PK estas perfekta:

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

Kaj la peto mutacias:

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

#2. Ŝanĝu al "kursoj"

Iom da tempo poste, DBA venas al vi kaj estas "kontenta" ke viaj petoj ili ŝarĝas la servilon kiel diable kun siaj OFFSET-reguloj, kaj ĝenerale, estas tempo ŝanĝi al navigado de lasta valoro montrita. Via demando denove mutacias:

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

Vi ĝemis trankvile ĝis ĝi venis...

#3. Purigado de indeksoj

Ĉar unu tagon via DBA legis artikolo pri trovado de neefikaj indeksoj kaj komprenis tion "ne la plej lasta" tempomarko ne estas bona. Kaj mi denove venis al vi — nun kun la penso, ke tiu indekso ankoraŭ refariĝos (ts DESC).

Sed kion fari kun la komenca problemo de "salti" registrojn inter paĝoj?... Kaj ĉio estas simpla - vi devas elekti blokojn kun nefiksita nombro da registroj!

Ĝenerale, kiu malpermesas al ni legi ne "ĝuste 26", sed "ne malpli ol 26"? Ekzemple, por ke en la sekva bloko estas registroj kun evidente malsamaj signifoj ts - tiam ne estos problemo pri "salti" diskoj inter blokoj!

Jen kiel atingi ĉi tion:

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;

Kio okazas ĉi tie?

  1. Ni paŝas 25 rekordojn "malsupren" kaj ricevas la "limon" valoron ts.
  2. Se jam ekzistas nenio tie, tiam anstataŭigu la NULL-valoron per -infinity.
  3. Ni subtrahas la tutan segmenton de valoroj inter la ricevita valoro ts kaj la $1 parametro pasis de la interfaco (la antaŭa "lasta" bildita valoro).
  4. Se bloko estas resendita kun malpli ol 26 registroj, ĝi estas la lasta.

Aŭ la sama bildo:
PostgreSQL Antipatterns: Navigante la Registron

Ĉar nun ni havas la specimeno ne havas specifan "komencon", tiam nenio malhelpas nin "vastigi" ĉi tiun peton en la kontraŭa direkto kaj efektivigi dinamikan ŝarĝon de datumblokoj de la "referenca punkto" ambaŭdirekte - kaj malsupren kaj supren.

Noto

  1. Jes, ĉi-kaze ni aliras la indekson dufoje, sed ĉio estas "nure per indekso". Tial, subdemando nur rezultigos al unu plia Indeksa Nur Skanado.
  2. Estas sufiĉe evidente, ke ĉi tiu tekniko povas esti uzata nur kiam vi havas valorojn ts povas transiri nur hazarde, kaj ne estas multaj el ili. Se via tipa kazo estas "miliono da registroj je 00:00:00.000", vi ne faru ĉi tion. Mi volas diri, vi ne devus permesi tian kazon okazi. Sed se tio okazas, uzu la opcion kun etendita indekso.

fonto: www.habr.com

Aldoni komenton