ProHoster > Blog > fitantanan-draharaha > Antipatterns PostgreSQL: Fikarohana ny rejisitra

Antipatterns PostgreSQL: Fikarohana ny rejisitra

Amin'izao fotoana izao dia tsy hisy tranga sarotra sy algorithm be pitsiny ao amin'ny SQL. Ny zava-drehetra dia ho tena tsotra, amin'ny haavon'ny Kapiteny Obvious - andao hanao izany mijery ny rejisitry ny hetsika voalamina araka ny fotoana.

Izany hoe, misy famantarana ao amin'ny database events, ary manana saha izy ts - ny fotoana tena tiantsika hanehoana ireo firaketana ireo amin'ny fomba mirindra:

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

CREATE INDEX ON events(ts DESC);

Mazava fa tsy hanana firaketana am-polony any isika, ka mila endrika iray fitetezana pejy.

#0. "Izaho no pogromista amin'ny reniko"

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

Saika tsy vazivazy izany - tsy fahita firy, fa hita any anaty ala. Indraindray, rehefa avy niasa tamin'ny ORM dia mety ho sarotra ny hifindra amin'ny asa "mivantana" miaraka amin'ny SQL.

Fa aleo hiroso amin'ny olana mahazatra kokoa sy tsy dia mibaribary loatra.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записСй Π½Π° страницС, $1 - Π½Π°Ρ‡Π°Π»ΠΎ страницы

Avy aiza ny isa 26? Ity no isan'ny fidirana tokony hameno ny efijery iray. Raha ny marimarina kokoa, 25 no nampiseho firaketana, miampy 1, izay manambara fa misy zavatra hafa farafaharatsiny ao anatin'ilay santionany ary misy dikany ny mandroso.

Mazava ho azy, io sanda io dia tsy azo "nozairina" ao amin'ny vatan'ny fangatahana, fa nandalo parameter iray. Saingy amin'ity tranga ity, ny mpandrindra PostgreSQL dia tsy afaka miantehitra amin'ny fahalalana fa tokony ho vitsy ny firaketana - ary hisafidy mora foana drafitra tsy mahomby.

Ary raha ao amin'ny interface interface, ny fijerena ny rejisitra dia ampiharina amin'ny fifandimbiasana eo amin'ny "pejy" hita maso, tsy misy olona mahatsikaritra zavatra mampiahiahy mandritra ny fotoana maharitra. Mandra-pahatongan'ny fotoana, ao anatin'ny tolona ho amin'ny fanamorana, ny UI/UX dia manapa-kevitra ny hamerina ny interface ho "horonam-boky tsy misy farany" - izany hoe, ny lisitry ny rejisitra rehetra dia voasintona ao anaty lisitra tokana ahafahan'ny mpampiasa mivezivezy miakatra sy midina.

Ary noho izany, mandritra ny fitsapana manaraka dia tratra ianao dika mitovy ny firaketana ao amin'ny rejisitra. Nahoana, satria ny latabatra dia manana index mahazatra (ts), miankina amin'ny inona ny fanontanianao?

Satria tsy niraharaha an'izany mihitsy ianao ts dia tsy fanalahidy tokana amin'ity latabatra ity. Raha ny marina, ary tsy miavaka ny soatoaviny, toy ny "fotoana" rehetra amin'ny toe-javatra tena izy - noho izany, ny firaketana mitovy amin'ny fanontaniana roa mifanakaiky dia mora "mitsambikina" isaky ny pejy noho ny filaharana farany hafa ao anatin'ny rafitry ny fanasokajiana ny sanda fototra mitovy.

Raha ny marina, misy olana faharoa miafina eto, izay sarotra kokoa ny manamarika - tsy haseho ny lahatsoratra sasany mihitsy! Rehefa dinihina tokoa, dia naka ny toeran'ny olon-kafa ny rakitsoratra "duplicate". Misy fanazavana amin'ny antsipiriany misy sary mahafinaritra azo jerena vakio ato.

Manitatra ny fanondro

Ny mpamorona fetsifetsy dia mahatakatra fa ny fanalahidin'ny fanondroana dia tsy maintsy atao tokana, ary ny fomba tsotra indrindra dia ny fanitarana azy amin'ny sehatra tsy manam-paharoa, izay mety amin'ny PK:

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

Ary miova ny fangatahana:

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

#2. Mivadika ho "cursors"

Fotoana vitsy taty aoriana, misy DBA tonga aminao ary "faly" amin'ny fangatahanao mameno ny mpizara toy ny helo izy ireo amin'ny fitsipika OFFSET, ary amin'ny ankapobeny, fotoana tokony hifindrana amin'ny fitetezana avy amin'ny sanda farany naseho. Miova indray ny fanontanianao:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- послСдниС ΠΏΠΎΠ»ΡƒΡ‡Π΅Π½Π½Ρ‹Π΅ Π½Π° ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰Π΅ΠΌ шагС значСния
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Nisento kely ianao mandra-pahatongany...

#3. Fanondroana fanadiovana

Satria indray andro dia namaky ny DBA-nao lahatsoratra momba ny fitadiavana indeksa tsy mahomby ary nahatsapa izany Tsy tsara ny famantaranandro "tsy farany".. Ary tonga teo aminao indray aho - ankehitriny miaraka amin'ny eritreritra fa mbola tokony hiverina any amin'ny index izany (ts DESC).

Fa inona no tokony hatao amin'ny olana voalohany amin'ny firaketana "mitsambikina" eo anelanelan'ny pejy?.. Ary tsotra ny zava-drehetra - mila misafidy sakana misy rakitra tsy voafaritra ianao!

Amin'ny ankapobeny, iza no mandrara antsika tsy hamaky β€œ26 marina”, fa β€œtsy latsaky ny 26”? Ohatra, ka ao amin'ny bloc manaraka misy rakitsoratra misy dikany mazava ho azy ts - dia tsy hisy olana amin'ny firaketana "mitsambikina" eo anelanelan'ny sakana!

Toy izao ny fomba hanatanterahana izany:

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;

Inona no mitranga eto?

  1. Ny dingana 25 dia mirakitra "midina" ary mahazo ny sandan'ny "faritra". ts.
  2. Raha tsy misy na inona na inona ao dia soloy ny sanda NULL amin'ny -infinity.
  3. Esorinay ny ampahany manontolo amin'ny soatoavina eo anelanelan'ny sanda voaray ts ary ny mari-pamantarana $1 dia nandalo tamin'ny interface (ny sanda "farany" teo aloha).
  4. Raha averina miaraka amin'ny rakitra latsaky ny 26 ny sakana iray, dia io no farany.

Na ny sary mitovy:
Antipatterns PostgreSQL: Fikarohana ny rejisitra

Satria manana izao isika ny santionany dia tsy manana "fiandohan'ny" manokana, dia tsy misy manakana antsika tsy β€œhanitarana” ity fangatahana ity amin'ny lalana mifanohitra sy hampihatra ny fampidinana ireo sakana data avy amin'ny β€œpoint reference” amin'ny lafiny roa - na midina na miakatra.

fanamarihana

  1. Eny, amin'ity tranga ity dia miditra indroa ny index, fa ny zava-drehetra dia "amin'ny index". Noho izany, ny subquery dia hiteraka ihany amin'ny Index Only Scan fanampiny.
  2. Tena miharihary fa io teknika io dia tsy azo ampiasaina raha tsy manana soatoavina ianao ts tsy afaka miampita afa-tsy ho azy, ary tsy maro izy ireo. Raha ny tranga mahazatra anao dia "rakitra an-tapitrisany amin'ny 00:00:00.000", tsy tokony hanao izany ianao. Ny tiako holazaina dia tsy tokony havelanao hitranga ny tranga toy izany. Fa raha mitranga izany, ampiasao ny safidy miaraka amin'ny fanondro miitatra.

Source: www.habr.com

Add a comment