🥇PostgreSQL Antipatterns: registry navigation

Nhasi hakuzovi nematare akaomarara uye akaomesesa algorithms muSQL. Zvese zvichange zviri nyore, pamwero weCaptain Obvious - ngatizviite kuona registry yechiitiko zvakarongwa nenguva.

Ndiko kuti, pane chiratidzo mudhatabhesi events, uye ane munda ts - chaiyo nguva yatinoda kuratidza marekodhi aya nenzira yakarongeka:

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

CREATE INDEX ON events(ts DESC);

Zviripachena kuti hatizove nemarekodhi gumi nemaviri ipapo, saka isu tichada imwe fomu ye peji navigation.

#0. "Ndiri mhondi yaamai vangu"

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

Zvinenge zvisiri zvejee - hazviwanzo, asi zvinowanikwa musango. Dzimwe nguva, mushure mekushanda neORM, zvinogona kunetsa kuchinja kune "zvakananga" basa neSQL.

Asi ngatienderere mberi kune mamwe matambudziko akajairika uye asina kujeka.

#1. OFFSET

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

Nhamba 26 yakabva kupi? Iyi ndiyo nhamba yekufungidzira yezvinyorwa kuzadza skrini imwe. Kunyanya, makumi maviri neshanu akaratidzwa marekodhi, pamwe ne25, zvichiratidza kuti pane chimwe chinhu chiri mberi mumuenzaniso uye zvine musoro kuenderera mberi.

Zvechokwadi, kukosha uku hakugoni "kusonerwa" mumuviri wechikumbiro, asi kupfuura nepakati peparameter. Asi mune iyi kesi, iyo PostgreSQL scheduler haizokwanisi kuvimba neruzivo rwekuti panofanirwa kuve nemarekodhi mashoma - uye achasarudza nyore hurongwa husina basa.

Uye ichiri muchirongwa chekushandisa, kuona registry kunoitwa sekuchinja pakati pe "mapeji" anooneka, hapana anoona chero chinhu chinofungidzirwa kwenguva yakareba. Kusvikira panguva iyo, mukurwira kurerukirwa, UI/UX inosarudza kugadzirisa iyo interface kuti "isingaperi mupumburu" - ndiko kuti, ese manyorerwo ekunyoresa anodhirowewa mune imwechete rondedzero iyo mushandisi anogona kupuruzira kumusoro nekudzika.

Uye saka, panguva yekuedzwa kunotevera, iwe unobatwa kudzokorora zvinyorwa mune registry. Sei, nekuti tafura ine yakajairika index (ts), ndeupi mubvunzo wako unovimba nawo?

Chaizvo nekuti iwe hauna kufunga nezvazvo ts haisi kiyi yakasarudzika mutafura iyi. Chokwadi, uye maitiro ayo haana ega, se "nguva" chero ipi zvayo mumamiriro ezvinhu chaiwo - naizvozvo, rekodhi imwe chete mumibvunzo miviri iri pedyo "inosvetuka" kubva peji kuenda kune peji nekuda kwekugadzirisa kwakasiyana kwekupedzisira mukati megadziriro yekugadzirisa kukosha kwakafanana.

Muchokwadi, pane zvakare dambudziko rechipiri rakavigwa pano, iro rakanyanya kuoma kuona - zvimwe zvinyorwa hazvizoonekwe zvachose! Mushure mezvose, zvinyorwa zve "duplicate" zvakatora nzvimbo yeumwe munhu. Tsananguro yakadzama ine mifananidzo yakaisvonaka inogona kuwanikwa verenga pano.

Kuwedzera index

Mugadziri ane hungwaru anonzwisisa kuti index kiyi inoda kuitwa yakasarudzika, uye nzira iri nyore ndeyekuiwedzera nendima yakasarudzika yakasarudzika, iyo PK yakanakira:

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

Uye chikumbiro chinochinja:

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

#2. Chinja ku "cursors"

Imwe nguva gare gare, DBA inouya kwauri uye "inofara" kuti zvikumbiro zvako vanoisa server segehena nemitemo yavo yeOFFSET, uye kazhinji, inguva yekuchinja kune navigation kubva pamutengo wekupedzisira waratidzwa. Mubvunzo wako unochinja zvakare:

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

Wakatura befu kusvika rasvika...

#3. Kuchenesa indexes

Nekuti rimwe zuva DBA yako yakaverenga chinyorwa chekutsvaga indexes isingashande ndikaziva kuti "kwete ichangoburwa" timestamp haina kunaka. Uye ini ndauya kwauri zvakare - ikozvino nepfungwa yekuti iyo index inofanira kuramba ichidzokera kuita (ts DESC).

Asi chii chaunofanira kuita nedambudziko rekutanga re "kusvetuka" zvinyorwa pakati pemapeji? .. Uye zvinhu zvose zviri nyore - unoda kusarudza zvivharo nenhamba isina kukwana yezvinyorwa!

Kazhinji, ndiani anotirambidza kuverenga kwete "chaizvo 26", asi "kwete pasi pe26"? Somuenzaniso, kuitira kuti mubhokisi rinotevera pane zvinyorwa zvine zvazvinoreva zvakasiyana-siyana ts - ipapo hapazove nedambudziko ne "kusvetuka" marekodhi pakati pemabhuraki!

Heino nzira yekuita izvi:

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;

Chii chiri kuitika pano?

Isu tinotsika 25 zvinyorwa "pasi" uye tiwane "muganhu" kukosha ts.
Kana pasina chatovepo, wobva watsiva iyo NULL kukosha nayo -infinity.
Isu tinobvisa chikamu chose chezvakakosha pakati peiyo yakagamuchirwa kukosha ts uye iyo $ 1 paramende yakapfuura kubva kune iyo interface (iyo yapfuura "yekupedzisira" yakapihwa kukosha).
Kana bhuroka rikadzoserwa riine zvinyorwa zvisingasviki makumi maviri nematanhatu, ndiyo yekupedzisira.

Kana mufananidzo wakafanana:

Nokuti ikozvino tawana sampuli haina kana "kutanga" chaiyo, saka hapana chinotitadzisa "kuwedzera" chikumbiro ichi nenzira yakapesana uye kuita kurodha zvine simba kwezvivharo zvedata kubva pa "reference point" mumativi ese - zvese pasi nekukwira.

Cherechedza

Hongu, munyaya iyi tinowana iyo index kaviri, asi zvose "zvakachena nendekisi". Naizvozvo, subquery inongoguma kune imwe yekuwedzera Index Chete Scan.
Zviripachena kuti tekiniki iyi inogona kushandiswa chete kana uine hunhu ts anogona kuyambuka nemukana chete, uye hakuna vazhinji vavo. Kana yako yakajairika kesi iri "miriyoni rekodhi na00:00:00.000", haufanirwe kuita izvi. Ndinoreva, haufanirwe kubvumira nyaya yakadai kuti iitike. Asi kana izvi zvikaitika, shandisa sarudzo ine index yakawedzerwa.

Source: www.habr.com

PostgreSQL Antipatterns: Kufambisa iyo Registry