ProHoster > Blog > administratë > Antimodelet e PostgreSQL: Lundrimi në Regjistrin

Antimodelet e PostgreSQL: Lundrimi në Regjistrin

Sot nuk do të ketë raste komplekse dhe algoritme të sofistikuara në SQL. Gjithçka do të jetë shumë e thjeshtë, në nivelin e kapitenit të qartë - le ta bëjmë atë duke parë regjistrin e ngjarjeve të renditura sipas kohës.

Kjo do të thotë, ka një shenjë në bazën e të dhënave events, dhe ajo ka një fushë ts - saktësisht koha në të cilën duam t'i shfaqim këto regjistrime në mënyrë të rregullt:

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

CREATE INDEX ON events(ts DESC);

Është e qartë se ne nuk do të kemi një duzinë regjistrimesh atje, kështu që do të na duhet një formë e tillë navigimi i faqes.

#0. "Unë jam pogromisti i nënës sime"

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

Nuk është pothuajse një shaka - është e rrallë, por gjendet në të egra. Ndonjëherë, pas punës me ORM, mund të jetë e vështirë të kaloni në punë "të drejtpërdrejtë" me SQL.

Por le të kalojmë te problemet më të zakonshme dhe më pak të dukshme.

#1. OFFSET

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

Nga erdhi numri 26? Ky është numri i përafërt i hyrjeve për të mbushur një ekran. Më saktësisht, 25 regjistrime të shfaqura, plus 1, duke sinjalizuar se ka të paktën diçka tjetër më tej në mostër dhe ka kuptim të vazhdohet.

Sigurisht, kjo vlerë nuk mund të "qepet" në trupin e kërkesës, por të kalojë përmes një parametri. Por në këtë rast, programuesi PostgreSQL nuk do të jetë në gjendje të mbështetet në njohurinë se duhet të ketë relativisht pak regjistrime - dhe do të zgjedhë lehtësisht një plan joefektiv.

Dhe ndërsa në ndërfaqen e aplikacionit, shikimi i regjistrit zbatohet si ndërrim midis "faqeve" vizuale, askush nuk vëren asgjë të dyshimtë për një kohë të gjatë. Pikërisht deri në momentin kur, në luftën për komoditet, UI/UX vendos të ribëjë ndërfaqen në "lëvizje të pafund" - domethënë, të gjitha shënimet e regjistrit tërhiqen në një listë të vetme që përdoruesi mund të lëvizë lart e poshtë.

Dhe kështu, gjatë testimit të radhës, ju kapeni dyfishimi i të dhënave në regjistër. Pse, sepse tabela ka një indeks normal (ts), ku mbështetet pyetja juaj?

Pikërisht sepse nuk e keni marrë parasysh këtë ts nuk është një çelës unik në këtë tabelë. Në fakt, dhe vlerat e tij nuk janë unike, si çdo "kohë" në kushte reale - prandaj, i njëjti rekord në dy pyetje ngjitur "kërcen" lehtësisht nga faqja në faqe për shkak të një renditje të ndryshme përfundimtare brenda kornizës së renditjes së së njëjtës vlerë kryesore.

Në fakt, këtu fshihet edhe një problem i dytë, i cili është shumë më i vështirë të vërehet - disa hyrje nuk do të shfaqen fare! Në fund të fundit, të dhënat "kopjuese" zunë vendin e dikujt tjetër. Mund të gjeni një shpjegim të detajuar me foto të bukura lexo ketu.

Zgjerimi i indeksit

Një zhvillues dinak e kupton që çelësi i indeksit duhet të bëhet unik dhe mënyra më e lehtë është ta zgjerosh atë me një fushë dukshëm unike, për të cilën PK është e përsosur:

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

Dhe kërkesa ndryshon:

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

#2. Kalo te "kursorët"

Disa kohë më vonë, një DBA vjen tek ju dhe është "i kënaqur" që kërkesat tuaja ata ngarkojnë serverin si dreq me rregullat e tyre OFFSET, dhe në përgjithësi, është koha për të kaluar në lundrimi nga vlera e fundit e treguar. Kërkesa juaj ndryshon përsëri:

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

Ju morët një psherëtimë të lehtësuar derisa erdhi...

#3. Indekset e pastrimit

Sepse një ditë DBA juaj lexoi artikull për gjetjen e indekseve joefektive dhe e kuptoi se Vula kohore "jo e fundit" nuk është e mirë. Dhe unë erdha tek ju përsëri - tani me mendimin se ai indeks duhet të kthehet përsëri në (ts DESC).

Por çfarë të bëni me problemin fillestar të "kërcimit" të regjistrimeve midis faqeve?.. Dhe gjithçka është e thjeshtë - ju duhet të zgjidhni blloqe me një numër të pafiksuar regjistrimesh!

Në përgjithësi, kush na ndalon të lexojmë jo "saktësisht 26", por "jo më pak se 26"? Për shembull, në mënyrë që në bllokun tjetër të ketë regjistrime me kuptime dukshëm të ndryshme ts - atëherë nuk do të ketë problem me "kërcimin" e rekordeve midis blloqeve!

Ja si ta arrini këtë:

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;

Cfare po ndodh ketu?

  1. Ne hedhim 25 rekorde "poshtë" dhe marrim vlerën "kufitare". ts.
  2. Nëse nuk ka asgjë atje, atëherë zëvendësoni vlerën NULL me -infinity.
  3. Ne zbresim të gjithë segmentin e vlerave midis vlerës së marrë ts dhe parametri $1 kaloi nga ndërfaqja (vlera e mëparshme "e fundit" e dhënë).
  4. Nëse një bllok kthehet me më pak se 26 regjistrime, ai është i fundit.

Ose e njëjta foto:
Antimodelet e PostgreSQL: Lundrimi në Regjistrin

Sepse tani kemi mostra nuk ka ndonjë "fillim" specifik, atëherë asgjë nuk na pengon të "zgjerojmë" këtë kërkesë në drejtim të kundërt dhe të zbatojmë ngarkimin dinamik të blloqeve të të dhënave nga "pika e referencës" në të dy drejtimet - poshtë dhe lart.

vërejtje

  1. Po, në këtë rast ne i aksesojmë indeksit dy herë, por gjithçka është "thjesht sipas indeksit". Prandaj, një nënpyetje do të rezultojë vetëm në në një skanim shtesë vetëm për indeksin.
  2. Është mjaft e qartë se kjo teknikë mund të përdoret vetëm kur keni vlera ts mund të kalojë vetëm rastësisht, dhe nuk ka shumë prej tyre. Nëse rasti juaj tipik është "një milion regjistrime në orën 00:00:00.000", nuk duhet ta bëni këtë. Dua të them, ju nuk duhet të lejoni të ndodhë një rast i tillë. Por nëse kjo ndodh, përdorni opsionin me një indeks të zgjeruar.

Burimi: www.habr.com

Shto një koment