ProHoster > blog > Gweinyddiaeth > Gwrthbatrymau PostgreSQL: Llywio'r Gofrestrfa

Gwrthbatrymau PostgreSQL: Llywio'r Gofrestrfa

Heddiw ni fydd unrhyw achosion cymhleth ac algorithmau soffistigedig yn SQL. Bydd popeth yn syml iawn, ar lefel Capten Amlwg - gadewch i ni ei wneud edrych ar y gofrestr digwyddiadau trefnu yn ôl amser.

Hynny yw, mae arwydd yn y gronfa ddata events, ac mae ganddi faes ts - yr union amser yr ydym am arddangos y cofnodion hyn mewn modd trefnus:

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

CREATE INDEX ON events(ts DESC);

Mae’n amlwg na fydd gennym ddwsin o gofnodion yno, felly bydd angen rhyw fath o llywio tudalen.

#0. “Fi yw pogromydd fy mam”

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

Nid yw bron yn jôc - mae'n brin, ond i'w chael yn y gwyllt. Weithiau, ar ôl gweithio gydag ORM, gall fod yn anodd newid i waith “uniongyrchol” gyda SQL.

Ond gadewch i ni symud ymlaen at broblemau mwy cyffredin a llai amlwg.

#1. GWRTHOD

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

O ble daeth y rhif 26? Dyma amcangyfrif nifer y cofnodion i lenwi un sgrin. Yn fwy manwl gywir, roedd 25 yn dangos cofnodion, ynghyd ag 1, yn nodi bod o leiaf rhywbeth arall ymhellach yn y sampl a'i fod yn gwneud synnwyr i symud ymlaen.

Wrth gwrs, ni ellir “gwnïo” y gwerth hwn i gorff y cais, ond ei basio trwy baramedr. Ond yn yr achos hwn, ni fydd yr amserlennydd PostgreSQL yn gallu dibynnu ar y wybodaeth y dylai fod nifer gymharol fach o gofnodion - a bydd yn hawdd dewis cynllun aneffeithiol.

Ac er yn y rhyngwyneb cais, mae edrych ar y gofrestrfa yn cael ei weithredu fel newid rhwng “tudalennau” gweledol, nid oes unrhyw un yn sylwi ar unrhyw beth amheus am amser hir. Yn union tan yr eiliad pan, yn y frwydr am gyfleustra, mae UI/UX yn penderfynu ail-wneud y rhyngwyneb i “sgrolio diddiwedd” - hynny yw, mae holl gofnodion y gofrestrfa yn cael eu tynnu mewn un rhestr y gall y defnyddiwr sgrolio i fyny ac i lawr.

Ac felly, yn ystod y profion nesaf, rydych chi'n cael eich dal dyblygu cofnodion yn y gofrestrfa. Pam, oherwydd bod gan y tabl fynegai arferol (ts), ar ba un y mae eich ymholiad yn dibynnu?

Yn union oherwydd na wnaethoch chi ystyried hynny ts nid yw'n allwedd unigryw yn y tabl hwn. Mewn gwirionedd, a nid yw ei werthoedd yn unigryw, fel unrhyw “amser” mewn amodau real – felly, mae’r un cofnod mewn dau ymholiad cyfagos yn “neidio” yn hawdd o dudalen i dudalen oherwydd trefn derfynol wahanol o fewn y fframwaith o ddidoli’r un gwerth allweddol.

Mewn gwirionedd, mae ail broblem wedi'i chuddio yma hefyd, sy'n llawer anoddach i'w sylwi - ni fydd rhai cofnodion yn cael eu dangos o gwbl! Wedi’r cyfan, cymerodd y cofnodion “dyblyg” le rhywun arall. Gellir dod o hyd i esboniad manwl gyda lluniau hardd darllenwch yma.

Ehangu'r mynegai

Mae datblygwr cyfrwys yn deall bod angen gwneud yr allwedd mynegai yn unigryw, a'r ffordd hawsaf yw ei ehangu gyda maes amlwg unigryw, y mae PK yn berffaith ar ei gyfer:

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

Ac mae'r cais yn treiglo:

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

#2. Newid i "Cyrchwyr"

Beth amser yn ddiweddarach, daw DBA atoch ac mae'n “falch” bod eich ceisiadau maent yn llwytho'r gweinydd fel uffern gyda'u rheolau OFFSET, ac yn gyffredinol, mae'n bryd newid i llywio o'r gwerth diwethaf a ddangosir. Mae eich ymholiad yn treiglo eto:

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

Fe wnaethoch chi anadlu ochenaid o ryddhad nes iddo ddod ...

#3. Glanhau mynegeion

Oherwydd un diwrnod darllenodd eich DBA erthygl am ddod o hyd i fynegeion aneffeithiol a sylweddoli hynny nid yw'r stamp amser “nid y diweddaraf” yn dda. A dyma fi'n dod atoch chi eto - nawr gyda'r meddwl y dylai'r mynegai hwnnw droi'n ôl iddo o hyd (ts DESC).

Ond beth i'w wneud gyda'r broblem gychwynnol o "neidio" cofnodion rhwng tudalennau?.. Ac mae popeth yn syml - mae angen i chi ddewis blociau gyda nifer ansefydlog o gofnodion!

Yn gyffredinol, pwy sy’n ein gwahardd i ddarllen nid “yn union 26”, ond “dim llai na 26”? Er enghraifft, fel bod yn y bloc nesaf mae cofnodion gyda gwahanol ystyron amlwg ts - yna ni fydd problem gyda “neidio” cofnodion rhwng blociau!

Dyma sut i gyflawni hyn:

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;

Beth sy'n digwydd yma?

  1. Rydyn ni'n camu 25 o gofnodion “i lawr” ac yn cael y gwerth “ffin”. ts.
  2. Os nad oes unrhyw beth yno eisoes, rhowch y gwerth NULL yn ei le -infinity.
  3. Rydym yn tynnu'r segment cyfan o werthoedd rhwng y gwerth a dderbyniwyd ts a'r paramedr $1 wedi'i basio o'r rhyngwyneb (y gwerth rendrad “olaf” blaenorol).
  4. Os dychwelir bloc gyda llai na 26 o gofnodion, dyma'r un olaf.

Neu'r un llun:
Gwrthbatrymau PostgreSQL: Llywio'r Gofrestrfa

Achos nawr mae gennym ni nid oes gan y sampl unrhyw “ddechrau” penodol, yna nid oes dim yn ein hatal rhag “ehangu” y cais hwn i'r cyfeiriad arall a gweithredu llwytho deinamig o flociau data o'r “pwynt cyfeirio” i'r ddau gyfeiriad - i lawr ac i fyny.

Nodyn:

  1. Ydym, yn yr achos hwn rydym yn cyrchu'r mynegai ddwywaith, ond mae popeth “yn ôl mynegai yn unig”. Felly, dim ond canlyniad fydd subquery i un Sgan Mynegai yn Unig ychwanegol.
  2. Mae'n eithaf amlwg mai dim ond pan fydd gennych werthoedd y gellir defnyddio'r dechneg hon ts yn gallu croesi trwy hap a damwain yn unig, a nid oes llawer ohonynt. Os mai eich achos nodweddiadol yw “miliwn o gofnodion yn 00:00:00.000”, ni ddylech wneud hyn. Hynny yw, ni ddylech ganiatáu i achos o'r fath ddigwydd. Ond os bydd hyn yn digwydd, defnyddiwch yr opsiwn gyda mynegai estynedig.

Ffynhonnell: hab.com

Ychwanegu sylw