PostgreSQL Antipatterns: navigimi në regjistër

Sot do të jetë një ditë e thjeshtë, pa asnjë rast të komplikuar ose algoritme të çuditshme në SQL. Do të jetë shumë e thjeshtë, në nivelin e Kapitenit të Qartë — po e bëjmë shikimin e regjistrit të ngjarjeve me renditje sipas kohës.

Pra, këtu kemi një tabelë në bazën e të dhënave events, dhe ajo ka një fushë ts — pikërisht ajo kohë, mbi të cilën dëshirojmë të tregojmë këto regjistrime në mënyrë të renditur:

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

CREATE INDEX ON events(ts DESC);

Sigurisht, do të kemi më shumë se dhjetë regjistrime, kështu që do të na nevojitet ndonjë formë navigacioni në faqe.

#0. «Я у мамы погроммист»

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

Madje gati është një shakë — ndonjëherë, por ndodh në natyrë. Ndonjëherë, pas punës me ORM, është e vështirë të kalosh në punën "direkte" me SQL.

Por le të kalojmë në probleme më të zakonshme dhe më pak të dukshme.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - regjistrime në faqe, $1 - fillimi i faqeve

Nga e erdhi ky numër 26? Ky është një numër përafërsisht i regjistrimeve për të mbushur një ekran. Më saktësisht, 25 regjistrime të dukshme, plus 1, që tregon se ka ende diçka të vlefshme për t'u parë dhe ka kuptim të vazhdojmë më tej.

Sigurisht, ky vlerë mund të mos "integrohet" në trupin e kërkesës, por të dërgohet si parametër. Por në këtë rast, planifikuesi i PostgreSQL nuk do të mund të mbështetet në faktin se duhet të ketë relativisht pak regjistrime, — dhe lehtë mund të zgjedhë një plan joefikas.

Dhe ndërsa në ndërfaqen e aplikacionit shikimi i regjistrit zbatohet si një kalim midis "faqeve" vizuale, askush nuk vëren asgjë të çuditshme për një kohë të gjatë. Saktësisht deri në momentin kur, në luftën për komoditetin e UI/UX, vendosin të rregullojnë ndërfaqen në "rrotullimin e pafund" — domethënë të gjitha regjistrimet e regjistrit vizatohen si një listë e vetme që përdoruesi mund ta rrotullojë lart-poshtë.

Dhe ja, gjatë testimit të radhës, ju kapen në duplikimin e regjistrimeve në regjistër. Pse, pasi që në tabelë ka një indeks të mirë (ts), për të cilin mbështetet kërkesa juaj?

Pikërisht sepse nuk e keni marrë parasysh që ts nuk është një çelës unik në këtë tabelë. Në të vërtetë, dhe vlerat e saj nuk janë unike, si ajo, si çdo "kohë" në kushte reale — prandaj një e njëjtë regjistrim në dy kërkesa ngjitur lehtësisht "shkëlqen" nga një faqe në tjetrën falë një renditjeje tjetër përkatës në kuadër të renditjes së njëjtë të vlerës së çelësit.

Në të vërtetë, këtu fshihet një problem tjetër, që është më e vështirë të vihet re — disa regjistrime nuk do të shfaqen fare! Sepse regjistrimet "e dyfishuara" zënë vendin e dikujt. Një shpjegim i detajuar me figura të bukura mund të të lexoni këtu.

Zgjerojmë indeksin

Krijuesi i zgjuar e kupton — duhet ta bëjë çelësin e indeksit unik, dhe mënyra më e thjeshtë — është ta zgjedhë me një fushë të sigurt që është perfekt për PK:

KRIJONI INDEKSI UNIK NË eventi(ts DESC, id DESC);

Dhe kërkesa muton:

SELECT
  ...
RREGULLO NË
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. Переход на «курсоры»

Pas ca kohësh, vjen një DBA tek ju dhe "gëzon", se kërkesat tuaja po ngarkojnë tmerrshëm serverin me OFFSET të tij, dhe me të vërtetë, është koha të kalojmë në navigimin nga vlera e fundit të treguar. Kërkesa juaj muton përsëri:

SELECT
  ...
KU
  (ts, id) < ($1, $2) -- vlerat e fundit të marra në hapin e kaluar
RREGULLO NË
  ts DESC, id DESC
LIMIT 26;

Më në fund, you thua një frymëmarrje lehtësuese, derisa nuk erdhi…

#3. Чистка индексов

Sepse një herë DBA juaj lexoi një artikull për gjetjen e indekseve joefikase dhe e kuptoi se "timestamp-i jo-fundit" — kjo është e keqe. Dhe sërish erdhi tek ju — tani me mendimin se ai indeks duhet në fakt të kthehet përsëri në (ts DESC).

Por çfarë të bëj me problemin fillestar të "skakimit" të regjistrimeve midis faqeve?.. E gjithë kjo është e thjeshtë — duhet të përzgjidhni blloqe me një numër të papërcaktuar regjistrimesh!

Në të vërtetë, kush na ndalon të lexojmë jo "pikërisht 26", por "jo më pak se 26"? Për shembull, në mënyrë që në bllokun e ardhshëm të ishin regjistrime me vlera të siguruara të ndryshme ts — atëherë nuk do të kishte as problemet me "shkëmbimet" e regjistrimeve midis blloqeve!

Ja si të arrijmë këtë:

SELECT
  ...
KU
  ts = coalesce((
    SELECT
      ts
    NGA
      eventi
    KU
      ts < $1
    RREGULLO NË
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
RREGULLO NË
  ts DESC;

Çfarë ndodh këtu në të vërtetë?

1. Ne shkojmë në 25 regjistrime "poshtë" dhe marrim vlerën "kufitare" ts.
2. Nëse atje nuk ka asgjë, atëherë e zëvendësojmë vlerën NULL me -infinity.
3. Heqim të gjithë segmentin e vlerave midis vlerës së marrë dhe parametrin e dërguar nga interface $1 (vlera e fundit "e shfaqur" të paraqitur). ts и переданным из интерфейса параметром $1 (предыдущим «последним» отрисованным значением).
4. Nëse blloku është kthyer me më pak se 26 regjistra — ai është i fundit.

Ose e njëjta gjë me një imazh:

Tani që kemi zgjedhja nuk ka një "fillim" të caktuar, nuk na pengon "të zhvillojmë" këtë kërkesë në anën tjetër dhe të realizojmë ngarkimin dinamik të blloqeve të të dhënave nga "pika referuese" në të dyja anët — si poshtë ashtu edhe lart.

Vërejtje

1. Po, në këtë rast ne i qasem indeksit dy herë, por gjithçka është "pjesërisht mbi indeks". Prandaj, kërkesa e përfshirë do të çojë vetëm në një skanimin shtesë vetëm të indeksit.
2. Është mjaft e qartë se kjo metodë mund të përdoret vetëm kur keni vlera ts që mund të kryqëzohen rastësisht, dhe janë të pakta. Por, nëse rasti juaj tipik është "një milion regjistrash në 00:00:00.000", atëherë nuk duhet ta bëni atë. Në kuptimin, nuk duhet të lejoni një rast të tillë. Por nëse ka ndodhur kështu, përdorni variantin me indeks të zgjeruar.

Burimi: habr.com