ProHoster > Blogs > Administrācija > PostgreSQL antipatterns: Navigācija reģistrā

PostgreSQL antipatterns: Navigācija reģistrā

Å odien SQL nebÅ«s sarežģītu gadÄ«jumu un izsmalcinātu algoritmu. Viss bÅ«s ļoti vienkārÅ”i, Captain Obvious lÄ«menÄ« - darÄ«sim to apskatot notikumu reÄ£istru sakārtoti pēc laika.

Tas ir, datu bāzē ir zÄ«me events, un viņai ir lauks ts - tieÅ”i laiks, kurā mēs vēlamies Å”os ierakstus parādÄ«t sakārtotā veidā:

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

CREATE INDEX ON events(ts DESC);

Ir skaidrs, ka mums tur nebūs ducis ierakstu, tāpēc mums būs vajadzīgs kāds no ierakstiem lapas navigācija.

#0. "Es esmu savas mātes pogromists"

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

Tas gandrÄ«z nav joks ā€“ tas ir reti sastopams, bet savvaļā sastopams. Dažreiz pēc darba ar ORM var bÅ«t grÅ«ti pārslēgties uz ā€œtieÅ”oā€ darbu ar SQL.

Bet pāriesim pie biežāk sastopamām un mazāk acīmredzamām problēmām.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - Š·Š°ŠæŠøсŠµŠ¹ Š½Š° стрŠ°Š½ŠøцŠµ, $1 - Š½Š°Ń‡Š°Š»Š¾ стрŠ°Š½Šøцы

No kurienes cēlies skaitlis 26? Šis ir aptuvenais ierakstu skaits, lai aizpildītu vienu ekrānu. Precīzāk, 25 parādītie ieraksti plus 1, kas signalizē, ka tālāk paraugā ir vismaz kaut kas cits un ir jēga doties tālāk.

Protams, Å”o vērtÄ«bu nevar ā€œieŔūtā€ pieprasÄ«juma pamattekstā, bet gan nodot caur parametru. Bet Å”ajā gadÄ«jumā PostgreSQL plānotājs nevarēs paļauties uz zināŔanām, ka ierakstu vajadzētu bÅ«t salÄ«dzinoÅ”i maz - un viegli izvēlēsies neefektÄ«vu plānu.

Un, lai gan lietojumprogrammas saskarnē reÄ£istra apskate tiek Ä«stenota kā pārslēgÅ”anās starp vizuālajām ā€œlapāmā€, neviens neko aizdomÄ«gu ilgu laiku nepamana. TieÅ”i lÄ«dz brÄ«dim, kad, cÄ«noties par ērtÄ«bām, UI/UX nolemj interfeisu pārveidot uz ā€œbezgalÄ«gu ritināŔanuā€ - tas ir, visi reÄ£istra ieraksti tiek sastādÄ«ti vienā sarakstā, kuru lietotājs var ritināt uz augÅ”u un uz leju.

Un tā nākamās pārbaudes laikā jÅ«s tiekat pieÄ·erts ierakstu dublÄ“Å”anās reÄ£istrā. Kāpēc, jo tabulā ir normāls indekss (ts), uz kuru balstās jÅ«su vaicājums?

TieÅ”i tāpēc, ka jÅ«s to neņēmāt vērā ts nav unikāla atslēga Å”ajā tabulā. PatiesÄ«bā, un tās vērtÄ«bas nav unikālas, tāpat kā jebkurÅ” ā€œlaiksā€ reālos apstākļos ā€“ tāpēc viens un tas pats ieraksts divos blakus esoÅ”ajos vaicājumos viegli ā€œpārlecā€ no lapas uz lapu, jo vienas un tās paÅ”as atslēgas vērtÄ«bas kārtoÅ”anas ietvaros ir atŔķirÄ«ga galÄ«gā secÄ«ba.

PatiesÄ«bā Å”eit ir paslēpta arÄ« otra problēma, kuru ir daudz grÅ«tāk pamanÄ«t - daži ieraksti netiks rādÄ«ti pavisam! Galu galā ierakstu dublikāti ieņēma kāda cita vietu. Detalizētu skaidrojumu ar skaistiem attēliem var atrast lasÄ«t Å”eit.

Indeksa paplaŔināŔana

ViltÄ«gs izstrādātājs saprot, ka indeksa atslēga ir jāpadara unikāla, un vienkārŔākais veids ir to paplaÅ”ināt ar acÄ«mredzami unikālu lauku, kuram PK ir lieliski piemērots:

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

Un pieprasījums mainās:

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

#2. Pārslēgties uz ā€œkursoriemā€

Pēc kāda laika pie jums atnāk DBA un ir ā€œgandarÄ«tsā€ par jÅ«su pieprasÄ«jumu viņi kā ellē noslogo serveri ar saviem OFFSET noteikumiem, un vispār ir pienācis laiks pārslēgties uz navigācija no pēdējās parādÄ«tās vērtÄ«bas. JÅ«su vaicājums atkal mainās:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- ŠæŠ¾ŃŠ»ŠµŠ“Š½ŠøŠµ ŠæŠ¾Š»ŃƒŃ‡ŠµŠ½Š½Ń‹Šµ Š½Š° ŠæрŠµŠ“ыŠ“ущŠµŠ¼ шŠ°Š³Šµ Š·Š½Š°Ń‡ŠµŠ½Šøя
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Tu atviegloti nopūties, līdz tas atnāca...

#3. TīrīŔanas indeksi

Jo kādu dienu jÅ«su DBA izlasÄ«ja raksts par neefektÄ«vu indeksu atraÅ”anu un to sapratu ā€œNav jaunākaisā€ laikspiedols nav labs. Un es atkal atnācu pie jums - tagad ar domu, ka Å”im indeksam tomēr vajadzētu atgriezties (ts DESC).

Bet ko darÄ«t ar sākotnējo problēmu ā€œpārlēktā€ ierakstus starp lapām?.. Un viss ir vienkārÅ”i - jāatlasa bloki ar nefiksētu ierakstu skaitu!

Vispār, kurÅ” mums aizliedz lasÄ«t nevis ā€œtieÅ”i 26ā€, bet gan ā€œne mazāk par 26ā€? Piemēram, lai nākamajā blokā bÅ«tu ieraksti ar acÄ«mredzami atŔķirÄ«gu nozÄ«mi ts - tad nebÅ«s problēmu ar ierakstu ā€œlēkÅ”anuā€ starp blokiem!

Lūk, kā to sasniegt:

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;

Kas Ŕeit notiek?

  1. Mēs veicam 25 ierakstus ā€œuz lejuā€ un iegÅ«stam ā€œrobežasā€ vērtÄ«bu ts.
  2. Ja tur jau nekā nav, aizstājiet NULL vērtību ar -infinity.
  3. No saņemtās vērtÄ«bas mēs atņemam visu vērtÄ«bu segmentu ts un parametrs $1, kas nodots no saskarnes (iepriekŔējā ā€œpēdējāā€ renderētā vērtÄ«ba).
  4. Ja bloks tiek atgriezts ar mazāk nekā 26 ierakstiem, tas ir pēdējais.

Vai arī tas pats attēls:
PostgreSQL antipatterns: Navigācija reģistrā

Jo tagad mums ir paraugam nav nekāda konkrēta ā€œsākumaā€, tad nekas neliedz mums ā€œizvērstā€ Å”o pieprasÄ«jumu pretējā virzienā un Ä«stenot datu bloku dinamisku ielādi no ā€œatskaites punktaā€ abos virzienos - gan uz leju, gan uz augÅ”u.

Piezīme:

  1. Jā, Å”ajā gadÄ«jumā mēs piekļūstam indeksam divas reizes, bet viss notiek ā€œtÄ«ri pēc indeksaā€. Tāpēc apakÅ”vaicājums radÄ«s tikai uz vienu papildu tikai indeksa skenÄ“Å”anu.
  2. Ir pilnÄ«gi skaidrs, ka Å”o paņēmienu var izmantot tikai tad, ja jums ir vērtÄ«bas ts var Ŕķērsot tikai nejauÅ”i, un tādu nav daudz. Ja jÅ«su tipiskais gadÄ«jums ir ā€œmiljons ierakstu pulksten 00:00:00.000ā€, jums tas nevajadzētu darÄ«t. Es domāju, jums nevajadzētu pieļaut, ka Ŕāds gadÄ«jums notiek. Bet, ja tā notiek, izmantojiet opciju ar paplaÅ”inātu indeksu.

Avots: www.habr.com

Pievieno komentāru