ProHoster > Blog > Uprava > PostgreSQL Antipatterns: Krmarjenje po registru

PostgreSQL Antipatterns: Krmarjenje po registru

Danes v SQL ne bo zapletenih primerov in prefinjenih algoritmov. Vse bo zelo preprosto, na ravni Captain Obvious - naredimo to ogled registra dogodkov razvrščeno po času.

To pomeni, da je v bazi podatkov znak events, in ima polje ts - natančen čas, do katerega želimo te zapise prikazati na urejen način:

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

CREATE INDEX ON events(ts DESC);

Jasno je, da tam ne bomo imeli ducata plošč, zato bomo potrebovali neko obliko navigacija po strani.

#0. "Sem mamin pogroman"

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

Skoraj ni šala – je redek, vendar ga najdemo v naravi. Včasih je po delu z ORM težko preklopiti na "neposredno" delo s SQL.

A pojdimo k pogostejšim in manj očitnim težavam.

#1. OFFSET

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

Od kod prihaja številka 26? To je približno število vnosov za zapolnitev enega zaslona. Natančneje, 25 prikazanih zapisov, plus 1, ki signalizira, da je v vzorcu vsaj še nekaj in je smiselno iti naprej.

Seveda te vrednosti ni mogoče "všiti" v telo zahteve, ampak jo prenesti skozi parameter. Toda v tem primeru se razporejevalnik PostgreSQL ne bo mogel zanesti na znanje, da mora biti zapisov relativno malo - in bo zlahka izbral neučinkovit načrt.

In medtem ko je v vmesniku aplikacije ogled registra izveden kot preklapljanje med vizualnimi »strani«, dolgo časa nihče ne opazi ničesar sumljivega. Točno do trenutka, ko se v boju za udobje UI/UX odloči predelati vmesnik v “neskončno drsenje” – torej so vsi vnosi v registru izrisani v enem samem seznamu, po katerem se lahko uporabnik pomika gor in dol.

In tako ste med naslednjim testiranjem ujeti podvajanje zapisov v registru. Zakaj, ker ima tabela normalen indeks (ts), na katerega temelji vaša poizvedba?

Točno zato, ker tega nisi upošteval ts ni edinstven ključ v tej tabeli. Pravzaprav in njegove vrednosti niso edinstvene, kot vsak “čas” v realnih razmerah - torej isti zapis v dveh sosednjih poizvedbah zlahka “skače” s strani na stran zaradi različnega končnega vrstnega reda v okviru razvrščanja iste vrednosti ključa.

Pravzaprav se tu skriva še drugi problem, ki ga je veliko težje opaziti – nekateri vnosi ne bodo prikazani nasploh! Navsezadnje so "podvojeni" zapisi prevzeli mesto nekoga drugega. Podrobno razlago s čudovitimi slikami najdete preberite tukaj.

Razširitev kazala

Zvit razvijalec razume, da mora biti indeksni ključ edinstven, in najlažji način je, da ga razširite z očitno edinstvenim poljem, za kar je PK popoln:

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

In zahteva se spremeni:

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

#2. Preklopite na "kurzorje"

Čez nekaj časa pride k vam DBA in je »zadovoljen« z vašimi zahtevami strežnik obremenjujejo kot hudič s svojimi pravili OFFSETin na splošno je čas, da preklopite na navigacija od zadnje prikazane vrednosti. Vaša poizvedba se spet spreminja:

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

Oddahnili ste si, dokler ni prišlo ...

#3. Čiščenje indeksov

Ker je nekega dne vaš DBA prebral članek o iskanju neučinkovitih indeksov in to spoznal »ni najnovejši« časovni žig ni dober. In spet sem prišel k vam - zdaj z mislijo, da bi se moralo tisto kazalo še spremeniti nazaj v (ts DESC).

Toda kaj storiti z začetno težavo "skakanja" zapisov med stranmi?.. In vse je preprosto - izbrati morate bloke z nedoločenim številom zapisov!

Na splošno, kdo nam prepoveduje brati ne "natančno 26", ampak "ne manj kot 26"? Na primer, tako da so v naslednjem bloku zapisov z očitno različnimi pomeni ts - potem ne bo težav s "skakanjem" zapisov med bloki!

Tukaj je opisano, kako to doseči:

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;

Kaj se tukaj dogaja?

  1. Stopimo 25 zapisov "dol" in dobimo "mejno" vrednost ts.
  2. Če tam že ni ničesar, zamenjajte vrednost NULL z -infinity.
  3. Odštejemo celoten segment vrednosti med prejeto vrednostjo ts in parameter $1, posredovan iz vmesnika (prejšnja »zadnja« upodobljena vrednost).
  4. Če je blok vrnjen z manj kot 26 zapisi, je to zadnji.

Ali ista slika:
PostgreSQL Antipatterns: Krmarjenje po registru

Ker zdaj imamo vzorec nima posebnega "začetka", potem nam nič ne preprečuje, da to zahtevo "razširimo" v nasprotni smeri in izvajamo dinamično nalaganje podatkovnih blokov iz "referenčne točke" v obe smeri - tako navzdol kot navzgor.

Opomba

  1. Ja, v tem primeru dvakrat dostopamo do indeksa, vendar je vse “čisto po indeksu”. Zato bo podpoizvedba povzročila samo na eno dodatno skeniranje samo indeksa.
  2. Povsem očitno je, da je to tehniko mogoče uporabiti le, če imate vrednote ts lahko prečka le po naključju in ni jih veliko. Če je vaš tipičen primer "milijon zapisov na 00:00:00.000", tega ne bi smeli storiti. Mislim, ne bi smeli dovoliti, da se tak primer zgodi. Če pa se to zgodi, uporabite možnost z razširjenim indeksom.

Vir: www.habr.com

Dodaj komentar