ProHoster > Blog > Bestjoer > PostgreSQL Antipatterns: Navigearje troch it register

PostgreSQL Antipatterns: Navigearje troch it register

Hjoed sille d'r gjin komplekse gefallen en ferfine algoritmen yn SQL wêze. Alles sil heul ienfâldich wêze, op it nivo fan Captain Obvious - litte wy it dwaan it besjen fan it eveneminteregister sortearre op tiid.

Dat is, der is in teken yn 'e databank events, en se hat in fjild ts - krekt de tiid wêrop wy dizze records op in oarderlike wize werjaan wolle:

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

CREATE INDEX ON events(ts DESC);

It is dúdlik dat wy sille net hawwe in tsiental records dêr, dus wy sille nedich in foarm fan side navigaasje.

#0. "Ik bin de pogromis fan myn mem"

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

It is hast gjin grap - it is seldsum, mar fûn yn it wyld. Soms, nei it wurkjen mei ORM, kin it lestich wêze om te wikseljen nei "direkt" wurk mei SQL.

Mar lit ús gean nei mear mienskiplike en minder foar de hân lizzende problemen.

#1. OFFSET

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

Wêr kaam it nûmer 26 wei? Dit is it likernôch oantal yngongen om ien skerm te foljen. Mear krekter, 25 werjûn records, plus 1, sinjalearjen dat der op syn minst wat oars fierder yn 'e stekproef en it makket sin om fierder te gean.

Fansels, dizze wearde kin net "genaaid" yn it lichem fan it fersyk, mar trochjûn troch in parameter. Mar yn dit gefal sil de PostgreSQL-planner net kinne fertrouwe op 'e kennis dat d'r relatyf min records moatte wêze - en sil maklik in net effektyf plan kieze.

En wylst yn 'e applikaasje-ynterface, it besjen fan it register wurdt ymplementearre as wikseljen tusken fisuele "siden", fernimt gjinien lang wat fertochts. Krekt oant it momint dat, yn 'e striid om gemak, UI / UX beslút om de ynterface opnij te meitsjen nei "einleaze rôlje" - dat is, alle registeryngongen wurde tekene yn ien list dy't de brûker op en del kin rôlje.

En dus, tidens de folgjende test, wurde jo fongen duplikaasje fan records yn it register. Wêrom, om't de tafel in normale yndeks hat (ts), wêrop jo fraach fertrout?

Krekt omdat jo dêr gjin rekken mei hâlden hawwe ts is net in unike kaai yn dizze tabel. Yn feite, en syn wearden binne net unyk, lykas elke "tiid" yn echte omstannichheden - dêrom, itselde rekord yn twa neistlizzende queries maklik "springt" fan side nei side fanwege in oare definitive folchoarder binnen it ramt fan it sortearjen fan deselde kaai wearde.

Yn feite is d'r hjir ek in twadde probleem ferburgen, dat is folle dreger te merken - guon ynstjoerings sille net werjûn wurde heulendal! Ommers, de "duplicate" records naam in oar syn plak. In detaillearre útlis mei prachtige plaatsjes is te finen lês hjir.

It útwreidzjen fan de yndeks

In listige ûntwikkelder begrypt dat de yndekskaai unyk makke wurde moat, en de maklikste manier is it út te wreidzjen mei in fansels unyk fjild, wêrfoar PK perfekt is:

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

En it fersyk mutearret:

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

#2. Wikselje nei "cursors"

In skoft letter komt in DBA nei jo en is "bliid" dat jo oanfragen se laden de tsjinner as de hel mei harren OFFSET regels, en yn it algemien, it is tiid om te wikseljen nei navigaasje fan lêste wearde werjûn. Jo query mutearret wer:

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

Jo sykhelle in sucht fan opluchting oant it kaam ...

#3. Cleaning yndeks

Want ien dei jo DBA lêzen artikel oer it finen fan yneffektive yndeksen en realisearre dat "net de lêste" tiidstempel is net goed. En ik kaam wer by dy - no mei de gedachte dat dy yndeks dochs wer yn wurde soe (ts DESC).

Mar wat te dwaan mei it earste probleem fan "springen" records tusken siden? .. En alles is ienfâldich - jo moatte blokken selektearje mei in unfixed oantal records!

Yn 't algemien, wa ferbiedt ús om net "krekt 26", mar "net minder dan 26" te lêzen? Bygelyks, sadat yn it folgjende blok der binne records mei fansels ferskillende betsjuttingen ts - dan sil d'r gjin probleem wêze mei "springen" fan records tusken blokken!

Hjir is hoe't jo dit berikke:

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;

Wat bart hjir?

  1. Wy stappe 25 records "down" en krije de "grins" wearde ts.
  2. As d'r al neat is, ferfange dan de NULL-wearde mei -infinity.
  3. Wy subtrahearje it heule segmint fan wearden tusken de ûntfongen wearde ts en de parameter $1 trochjûn fan 'e ynterface (de foarige "lêste" werjûn wearde).
  4. As in blok wurdt weromjûn mei minder as 26 records, it is de lêste.

Of deselde foto:
PostgreSQL Antipatterns: Navigearje troch it register

Want no hawwe wy de stekproef hat gjin spesifyk "begjin", dan ferhindert neat ús om dit fersyk yn 'e tsjinoerstelde rjochting te "útwreidzjen" en dynamysk laden fan gegevensblokken út it "referinsjepunt" yn beide rjochtingen te ymplementearjen - sawol del as omheech.

Notysje

  1. Ja, yn dit gefal hawwe wy twa kear tagong ta de yndeks, mar alles is "suver troch yndeks". Dêrom sil in subquery allinich resultearje yn oan ien ekstra Index Allinne Scan.
  2. It is dúdlik dat dizze technyk allinich brûkt wurde kin as jo wearden hawwe ts kin oerstekke allinnich tafal, en der binne net folle fan harren. As jo ​​​​typysk gefal "in miljoen records om 00:00:00.000" is, moatte jo dit net dwaan. Ik bedoel, jo moatte net tastean sa'n gefal barre. Mar as dit bart, brûk dan de opsje mei in útwreide yndeks.

Boarne: www.habr.com

Add a comment