ProHoster > Blog > Administrazioa > PostgreSQL Antipatterns: Erregistroan nabigatzea

PostgreSQL Antipatterns: Erregistroan nabigatzea

Gaur egun ez da kasu konplexurik eta algoritmo sofistikaturik egongo SQLn. Dena oso erraza izango da, Captain Obvious mailan - egin dezagun ekitaldien erregistroa ikustea denboraren arabera ordenatuta.

Hau da, datu-basean seinale bat dago events, eta eremu bat dauka ts - Erregistro hauek modu ordenatuan erakutsi nahi ditugun ordua zehatz-mehatz:

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

CREATE INDEX ON events(ts DESC);

Argi dago han ez ditugula dozena bat disko izango, beraz, nolabaiteko behar izango dugu orriaren nabigazioa.

#0. "Nire amaren pogromista naiz"

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

Ia ez da txantxa bat - arraroa da, baina basatian aurkitzen da. Batzuetan, ORMarekin lan egin ondoren, zaila izan daiteke SQLrekin lan "zuzenera" aldatzea.

Baina goazen arazo arruntagoetara eta ez hain nabariagoetara.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записСй Π½Π° страницС, $1 - Π½Π°Ρ‡Π°Π»ΠΎ страницы

Nondik dator 26 zenbakia? Hau da pantaila bat betetzeko gutxi gorabeherako sarrera kopurua. Zehatzago esanda, bistaratzen diren 25 erregistro, gehi 1, laginean gutxienez beste zerbait gehiago dagoela eta aurrera jarraitzea zentzuzkoa dela adierazten dute.

Jakina, balio hori ezin da "josi" eskaeraren gorputzean, baizik eta parametro batetik pasatu. Baina kasu honetan, PostgreSQL programatzaileak ezingo du fidatu nahiko erregistro gutxi egon beharko lukeen ezagutzan, eta erraz aukeratuko du eraginkortasunik gabeko plan bat.

Eta aplikazioaren interfazean dagoen bitartean, erregistroa ikustea "orrialde" bisualen artean aldatzeko moduan ezartzen da, inork ez du denbora luzez ezer susmagarririk nabaritzen. Hain zuzen ere, erosotasuna lortzeko borrokan, UI/UX-ek interfazea "amaigabeko korritze" birsortzea erabakitzen duen unera arte, hau da, erregistroko sarrera guztiak zerrenda bakarrean marrazten dira, erabiltzaileak gora eta behera korritzeko.

Eta horrela, hurrengo probetan, harrapatu egiten zaituzte erregistroen bikoizketa erregistroan. Zergatik, taulak indize normala duelako (ts), zeinetan oinarritzen da zure kontsulta?

Hain zuzen, hori kontuan hartu ez duzulako ts ez da gako bakarra taula honetan. Egia esan, eta bere balioak ez dira bakarrak, baldintza errealetan dagoen edozein "denbora" bezala; beraz, ondoko bi kontsultetan erregistro bera erraz "jauzi" egiten da orrialde batetik bestera, gako-balio berdina ordenatzearen esparruan azken ordena desberdina delako.

Izan ere, bigarren arazo bat ere badago hemen ezkutatuta, askoz zailagoa dena nabaritzea - sarrera batzuk ez dira erakutsiko batere! Azken finean, erregistro "bikoiztuek" beste baten lekua hartu zuten. Irudi ederrekin azalpen zehatza aurki daiteke irakurri hemen.

Indizea zabaltzea

Garatzaile maltzur batek ulertzen du indize-gakoa bakarra egin behar dela, eta modurik errazena eremu jakin batekin zabaltzea da, zeina PK egokia den:

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

Eta eskaera aldatzen da:

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

#2. Aldatu "kurtsoreetara"

Denbora pixka bat geroago, DBA bat datorkizu eta zure eskaerak "pozik" dago zerbitzaria arraio bezala kargatzen dute beren OFFSET arauekin, eta, oro har, aldatzeko garaia da erakutsitako azken baliotik nabigazioa. Zure kontsulta berriro aldatzen da:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- послСдниС ΠΏΠΎΠ»ΡƒΡ‡Π΅Π½Π½Ρ‹Π΅ Π½Π° ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰Π΅ΠΌ шагС значСния
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Arnasa hartu zenuen iritsi arte...

#3. Garbiketa-indizeak

Egun batean zure DBA irakurri delako Eraginkortasunik gabeko indizeak aurkitzeari buruzko artikulua eta konturatu zen horretaz "Ez da azkena" denbora-zigilua ez da ona. Eta zuregana etorri nintzen berriro, orain indize hori berriro bihurtu behar zela pentsatuta (ts DESC).

Baina zer egin orrien artean erregistroak "jauzi" egiteko hasierako arazoarekin?... Eta dena erraza da - erregistro kopuru finko batekin blokeak hautatu behar dituzu!

Oro har, nork debekatzen digu "zehazki 26" ez irakurtzea, "ez 26 baino gutxiago" baizik? Adibidez, hurrengo blokean egon daitezen jakina, esanahi desberdinak dituzten erregistroak ts - orduan ez da arazorik izango blokeen artean erregistroak "jauzi egiteko"!

Hona hemen hori nola lortu:

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;

Zer gertatzen da hemen?

  1. 25 erregistro "behera" urratsa egiten dugu eta "muga" balioa lortzen dugu ts.
  2. Dagoeneko ezer ez badago, ordeztu NULL balioa -infinity.
  3. Jasotako balioaren artean balioen segmentu osoa kentzen dugu ts eta $1 parametroa interfazetik pasatu da (aurreko β€œazken” errendatutako balioa).
  4. Bloke bat 26 erregistro baino gutxiagorekin itzultzen bada, azkena da.

Edo argazki bera:
PostgreSQL Antipatterns: Erregistroan nabigatzea

Orain badugulako laginak ez du "hasiera" zehatzik, orduan ezerk ez digu eragozten eskaera hau kontrako noranzkoan "zabaltzea" eta datu-blokeen karga dinamikoa "erreferentzia-puntutik" bi norabideetan ezartzea, bai behera eta bai gora.

komentarioen

  1. Bai, kasu honetan bi aldiz sartzen gara indizera, baina dena "indize hutsez" da. Hori dela eta, azpikontsulta batek bakarrik eragingo du Indize soilik eskaneatu gehigarri batera.
  2. Nahiko bistakoa da teknika hau balioak dituzunean soilik erabil daitekeela ts kasualitatez bakarrik zeharkatu daiteke, eta ez dira asko. Zure kasu arrunta "00:00:00.000 milioi bat erregistro" bada, ez zenuke hau egin behar. Esan nahi dut, ez zenuke horrelako kasurik gertatzea onartu behar. Baina hori gertatzen bada, erabili indize hedatu batekin aukera.

Iturria: www.habr.com

Gehitu iruzkin berria