ProHoster > Log > administrasjon > PostgreSQL Antipatterns: Navigere i registeret

PostgreSQL Antipatterns: Navigere i registeret

I dag vil det ikke være noen komplekse tilfeller og sofistikerte algoritmer i SQL. Alt vil være veldig enkelt, på nivå med Captain Obvious - la oss gjøre det ser på hendelsesregisteret sortert etter tid.

Det vil si at det er et skilt i databasen events, og hun har et felt ts - nøyaktig tidspunktet vi ønsker å vise disse postene på en ryddig måte:

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

CREATE INDEX ON events(ts DESC);

Det er klart at vi ikke vil ha et dusin poster der, så vi trenger en form for sidenavigering.

#0. "Jeg er min mors pogromist"

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

Det er nesten ikke en spøk – det er sjeldent, men finnes i naturen. Noen ganger, etter å ha jobbet med ORM, kan det være vanskelig å bytte til "direkte" arbeid med SQL.

Men la oss gå videre til mer vanlige og mindre åpenbare problemer.

#1. OFFSET

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

Hvor kom tallet 26 fra? Dette er det omtrentlige antallet oppføringer for å fylle ett skjermbilde. Mer presist, 25 viste poster, pluss 1, som signaliserer at det i det minste er noe annet lenger i prøven og at det er fornuftig å gå videre.

Selvfølgelig kan denne verdien ikke "sys" inn i forespørselens kropp, men sendes gjennom en parameter. Men i dette tilfellet vil ikke PostgreSQL-planleggeren kunne stole på kunnskapen om at det skal være relativt få poster – og vil lett velge en ineffektiv plan.

Og mens det i applikasjonsgrensesnittet er visning av registeret implementert som å bytte mellom visuelle "sider", legger ingen merke til noe mistenkelig på lenge. Nøyaktig inntil det øyeblikket, i kampen for bekvemmelighet, bestemmer UI/UX seg for å gjøre grensesnittet om til "endeløs rulling" - det vil si at alle registeroppføringer er tegnet i en enkelt liste som brukeren kan rulle opp og ned.

Og så, under neste testing, blir du tatt duplisering av poster i registeret. Hvorfor, fordi tabellen har en normal indeks (ts), som spørringen din er avhengig av?

Akkurat fordi du ikke tok hensyn til det ts er ikke en unik nøkkel i denne tabellen. Faktisk, og dens verdier er ikke unike, som enhver "tid" under reelle forhold - derfor "hopper" den samme posten i to tilstøtende søk lett fra side til side på grunn av en annen endelig rekkefølge innenfor rammen av å sortere den samme nøkkelverdien.

Faktisk er det også et annet problem skjult her, som er mye vanskeligere å legge merke til - noen oppføringer vil ikke bli vist i det hele tatt! Tross alt tok de "dupliserte" postene noen andres plass. En detaljert forklaring med vakre bilder kan bli funnet les her.

Utvider indeksen

En utspekulert utvikler forstår at indeksnøkkelen må gjøres unik, og den enkleste måten er å utvide den med et åpenbart unikt felt, som PK er perfekt for:

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

Og forespørselen muterer:

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

#2. Bytt til "markører"

En tid senere kommer en DBA til deg og er "fornøyd" med dine forespørsler de laster serveren som faen med sine OFFSET-regler, og generelt er det på tide å bytte til navigering fra sist viste verdi. Spørsmålet ditt muterer igjen:

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

Du pustet lettet ut til det kom...

#3. Rengjøringsindekser

Fordi en dag din DBA leste artikkel om å finne ineffektive indekser og innså det "ikke det siste" tidsstempelet er ikke bra. Og jeg kom til deg igjen - nå med tanken på at den indeksen fortsatt skulle bli tilbake til (ts DESC).

Men hva skal man gjøre med det første problemet med å "hoppe" poster mellom sider? .. Og alt er enkelt - du må velge blokker med et ufast antall poster!

Generelt, hvem forbyr oss ikke å lese "nøyaktig 26", men "ikke mindre enn 26"? For eksempel slik at i neste blokk er det poster med åpenbart forskjellige betydninger ts - da vil det ikke være noe problem med å "hoppe" poster mellom blokkene!

Slik oppnår du dette:

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;

Hva foregår her?

  1. Vi trinn 25 poster "ned" og får "grenseverdien". ts.
  2. Hvis det ikke er noe der allerede, erstatt NULL-verdien med -infinity.
  3. Vi trekker fra hele segmentet av verdier mellom den mottatte verdien ts og $1-parameteren sendt fra grensesnittet (den forrige "siste" gjengitte verdien).
  4. Hvis en blokk returneres med mindre enn 26 poster, er det den siste.

Eller samme bilde:
PostgreSQL Antipatterns: Navigere i registeret

For nå har vi det prøven har ingen spesifikk "begynnelse", så hindrer ingenting oss i å "utvide" denne forespørselen i motsatt retning og implementere dynamisk lasting av datablokker fra "referansepunktet" i begge retninger - både ned og opp.

bemerkning

  1. Ja, i dette tilfellet får vi tilgang til indeksen to ganger, men alt er "rent etter indeks". Derfor vil en underspørring bare resultere i til én ekstra indeksskanning.
  2. Det er ganske åpenbart at denne teknikken kun kan brukes når du har verdier ts kan bare krysse ved en tilfeldighet, og det er ikke mange av dem. Hvis ditt typiske tilfelle er "en million poster klokken 00:00:00.000", bør du ikke gjøre dette. Jeg mener, du bør ikke la en slik sak skje. Men hvis dette skjer, bruk alternativet med en utvidet indeks.

Kilde: www.habr.com

Legg til en kommentar