🥇Antipatern për PostgreSQL: navigimi në regjistrin

Sot ditë nuk do të ketë asnjë rast të ndërlikuar dhe algoritm të komplikuar në SQL. Gjithçka do të jetë shumë e thjeshtë, në nivelin e Kapitenit të Qartë — bëjmë shikimin e regjistrit të ngjarjeve me renditje në kohë.

Pra, këtu ndodhet një tabelë në bazën e të dhënave events., dhe ajo ka një fushë ts — pikërisht ai moment, sipas të cilit ne duam t'i paraqesim këto regjistrime në mënyrë të renditur:

KRIJO TABELË eventi(
  id
    serial
      ÇELSI PRIMAR
, ts
    timestamp
, të dhëna
    json
);

KRIJO INDEX NË eventi(ts DESC);

E qartë, numri i regjistrimeve atje nuk do të jetë dhjetë, kështu që na nevojitet në një farë forme navigacioni me 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 që nuk është asnjë shaka — rrallë, por ndodhet në natyrën e egër. Ndonjëherë, pas punës me ORM, ndihmon të kthehesh në punën ‘direkte’ me SQL.

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

#1. OFFSET

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

Nga e kuptoni që ndodhet numri 26? Ky është numri i përafërt i regjistrimeve për të mbushur një ekran. Më saktë, 25 regjistrime të shfaqura, plus 1, që tregon se ka diçka tjetër në përzgjedhje dhe ka kuptim të vazhdojmë tutje.
Sigurisht, ky vlerë nuk ka nevojë të ‘vërë’ në trupin e kërkesës, por të kalojë përmes një parametri. Por në këtë rast, planifikuesi i PostgreSQL nuk do të mund të mbështetet në njohurinë se regjistrimet duhet të jenë relativisht të pakta, — dhe lehtësisht do të zgjedhë një plan joefikas.

Dhe për sa kohë që në ndërfaqen e aplikacionit shikimi i regjistrit është implementuar si ndërrim midis ‘faqeve’ vizuale, askush nuk vë re ndonjë gjë të dyshimtë për një kohë të gjatë. Pikërisht deri në momentin kur në luftën për komfortin e UI/UX vendoset të rregullohet ndërfaqja në ‘skroll të pafund’ — pra, të gjitha regjistrimet e regjistrit paraqiten si një listë e vetme, që përdoruesi mund ta rrotullojë lart-poshtë.

Dhe ja, gjatë një testimi tjetër ju kapin për duplikimin e regjistrimeve në regjistër. Pse, ndonjëherë, ndonëse në tabelë ka një indeks të zakonshëm (ts), të cilin e mbështet kërkesa juaj?

Pikërisht për shkak se ju nuk e keni marrë parasysh që ts nuk është një çelës unik në këtë tabelë. Në fakt, dhe vlerat e saj nuk janë unike, si çdo ‘kohë’ në kushte reale — për këtë arsye një regjistrim i njëjtë në dy kërkesa ngjitur lehtësisht ‘shkon’ nga një faqe në tjetrën për shkak të një rendi tjetër përfundimtar brenda rendit të vlerës së njëjtë të çelës.

Në të vërtetë, këtu e fshehur ndodhet edhe një problem i dytë, që është shumë më e vështirë të vërehet — disa regjistrime nuk do të shfaqen fare! Sepse regjistrimet ‘e dyzuara’ zënë vendin e dikujt. Një shpjegim i hollësishëm me figura të bukura mund të lexohet këtu.

Zgjerimi i indeksit

Një zhvillues i mençur kupton — duhet bërë çelësi i indeksit unik, dhe mënyra më e thjeshtë — ta zgjasim me një fushë që është padyshim e unike, në cilin rast PK është ideale:

KRIJO INDENS UNIK NË eventi(ts DESC, id DESC);

Ndërsa kërkesa përqendrohet:

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

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

Disa kohë më vonë, ju vjen një DBA dhe ‘në gëzim’, që kërkesat tuaja ngarkojnë tmerrësisht serverin me OFFSET të mëdha, dhe në tërësi, është koha të kaloni në navigimin nga vlera e fundit të shfaqur. Kërkesa juaj përsëri transformohet:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- vlerat e fundit të marra në hapin e mëparshëm
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Ju lehtësoheni, derisa nuk ndodh...

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

Sepse një herë DBA juaj lexoi një artikull mbi kërkimin e indekseve joefikase dhe e kupton se "timestamp i ‘papërshtatshëm’ — është diçka e keqe".Dhe përsëri erdhi te ju — tani me mendimin, se ai indeks duhej të kthehej përsëri në (ts DESC).

Por çfarë të bëni me problemin e parë të ‘shkallëzimit’ të regjistrimeve midis faqeve?.. Po, është shumë e thjeshtë — duhet të zgjedhim blloqe me numër jo të përcaktuar të regjistrimeve!

Në fakt, kush na ndalon të lexojmë jo ‘saktësisht 26’, por ‘jo më pak se 26’? Për shembull, ashtu që në bllokun e ardhshëm të dalin regjistrime me vlera qartazi të tjera ts — atëherë dhe problemet me ‘shkallëzimin’ e regjistrimeve midis blloqeve nuk do të ketë!

Ja se si t'i arrijmë ato:

SELECT
  ...
WHERE
  ts = coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Çfarë po ndodh këtu në fakt?

Shkoni 25 regjistrime ‘poshtë’ dhe merrni vlerën ‘kufitare’ ts.
Nëse atje nuk ka gjë, zëvendësoni vlerën NULL me -infinity.
Lexoni të gjithë segmentin e vlerave midis vlerës së marrë dhe parametrave të kaluar nga ndërfaqja $1 (vlera e ‘fundit të fundit’ të shfaqur). ts Nëse blloku u kthye me më pak se 26 regjistrime — ai është i fundit.
Ose e njëjta gjë në formë figure:

Pasi tani zgjedhja jonë nuk ka një ‘fillim’ të caktuar

Taniq së tani kemi grupimi nuk ka një "fillim" të caktuar, atëherë nuk ka asgjë që na pengon të «zhvillojmë» këtë kërkesë në mënyrë të kundërt dhe të realizojmë ngarkimin dinamik të blloqeve të të dhënave nga «pikë mbështetje» në të dyja drejtimet — si poshtë ashtu edhe lart.

Vërejtje

Po, në këtë rast i referohemi indeksit dy herë, por gjithçka është «qartësisht sipas indeksit». Prandaj, kërkesa e brendshme do të çojë vetëm në një skanimin të vetëm të Indeksit vetëm.
Mjafton të jetë e qartë se kjo metodë mund të përdoret vetëm kur ju keni vlera ts që mund të përputhen vetëm rastësisht, dhe janë pak. Por nëse rasti juaj tipik është «miliona regjistrime në 00:00:00.000», nuk duhet ta bëni këtë. Prandaj, nuk duhet të lejoni një rast të tillë. Por nëse kjo ka ndodhur, përdorni variantin me indeks të zgjeruar.

Burimi: habr.com