ProHoster > Blog > Rianachd > Antipatterns PostgreSQL: A’ seòladh a’ Chlàr

Antipatterns PostgreSQL: A’ seòladh a’ Chlàr

An-diugh cha bhi cùisean iom-fhillte agus algorithms sòlaimte ann an SQL. Bidh a h-uile dad gu math sìmplidh, aig ìre Caiptean Obvious - dèanamaid e coimhead air clàr an tachartais air an òrdachadh a rèir ùine.

Is e sin, tha soidhne anns an stòr-dàta events, agus tha achadh aice ts - an dearbh àm aig a bheil sinn airson na clàran sin a thaisbeanadh ann an dòigh òrdail:

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

CREATE INDEX ON events(ts DESC);

Tha e soilleir nach bi dusan clàr againn an sin, agus mar sin bidh feum againn air seòrsa air choreigin seòladh duilleag.

#0. "Is mise pogromist mo mhàthair"

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

Cha mhòr nach e fealla-dhà a th’ ann - tha e tearc, ach lorgar e sa bhlàr a-muigh. Aig amannan, às deidh a bhith ag obair le ORM, faodaidh e a bhith duilich atharrachadh gu obair “dìreach” le SQL.

Ach gluaisidh sinn air adhart gu duilgheadasan nas cumanta agus nach eil cho follaiseach.

#1. IOMRADH

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

Cò às a thàinig an àireamh 26? Is e seo an àireamh tuairmseach de inntrigidhean airson aon scrion a lìonadh. Nas mionaidiche, sheall 25 clàran, a bharrachd air 1, a’ comharrachadh gu bheil co-dhiù rudeigin eile nas fhaide san sampall agus gu bheil e ciallach gluasad air adhart.

Gu dearbh, chan urrainnear an luach seo a bhith “air fhuaigheal” ann am bodhaig an iarrtais, ach a dhol tro pharamadair. Ach anns a 'chùis seo, cha bhith e comasach don chlàr-ama PostgreSQL a bhith an urra ris an eòlas gum bu chòir glè bheag de chlàran a bhith ann - agus taghaidh e plana neo-èifeachdach gu furasta.

Agus fhad ‘s a tha e ann an eadar-aghaidh an tagraidh, thathas a’ coimhead air a ’chlàr mar atharrachadh eadar“ duilleagan, ”lèirsinneach, chan eil duine a’ mothachadh dad amharasach airson ùine mhòr. Dìreach chun na h-ìre nuair a cho-dhùnas UI / UX, anns an strì airson goireasachd, an eadar-aghaidh ath-dhèanamh gu “scrolladh gun chrìoch” - is e sin, tha a h-uile clàr clàraidh air a tharraing ann an aon liosta as urrainn don neach-cleachdaidh gluasad suas is sìos.

Agus mar sin, rè an ath dheuchainn, tha thu air do ghlacadh dùblachadh chlàran anns a' chlàr. Carson, leis gu bheil clàr-amais àbhaisteach aig a’ chlàr (ts), air a bheil do cheist an urra?

Dìreach air sgàth nach tug thu aire dha sin ts chan e iuchair shònraichte a th’ ann anns a' chlàr so. Gu fìrinneach, agus chan eil a luachan gun samhail, mar “ùine” sam bith ann an suidheachaidhean fìor - mar sin, bidh an aon chlàr ann an dà cheist faisg air làimh gu furasta “a’ leum ”bho dhuilleag gu duilleag air sgàth òrdugh deireannach eadar-dhealaichte taobh a-staigh frèam a bhith a’ rèiteach an aon phrìomh luach.

Gu dearbh, tha dàrna duilgheadas falaichte an seo cuideachd, a tha tòrr nas duilghe a thoirt fa-near - cha tèid cuid de na h-inntrigidhean a shealltainn Idir idir! Às deidh na h-uile, ghabh na clàran “dùblaichte” àite cuideigin eile. Gheibhear mìneachadh mionaideach le dealbhan brèagha Leugh an seo.

A 'leudachadh an clàr-amais

Tha leasaiche seòlta a’ tuigsinn gum feumar an iuchair clàr-amais a dhèanamh gun samhail, agus is e an dòigh as fhasa a leudachadh le raon a tha follaiseach gun samhail, a tha PK foirfe airson:

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

Agus tha an t-iarrtas ag atharrachadh:

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

#2. Atharraich gu “cursors”

Greis às deidh sin, thig DBA thugad agus tha e “toilichte” na h-iarrtasan agad bidh iad a’ luchdachadh an fhrithealaiche mar ifrinn leis na riaghailtean OFFSET aca, agus san fharsaingeachd, tha an t-àm ann tionndadh gu seòladh bhon luach mu dheireadh a chaidh a shealltainn. Bidh do cheist ag atharrachadh a-rithist:

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

Ghabh thu osna faochadh gus an tàinig e...

#3. Glanadh clàran-amais

Air sgàth aon latha leugh an DBA agad artaigil mu bhith a’ lorg clàran-amais neo-èifeachdach agus thuig sin Chan eil an clàr-ama “chan e an tè as ùire” math. Agus thàinig mi thugad a-rithist - a-nis leis a’ bheachd gum bu chòir don chlàr-amais sin tionndadh air ais (ts DESC).

Ach dè a nì thu leis a 'chiad dhuilgheadas de chlàran "leum" eadar duilleagan? ... Agus tha a h-uile dad sìmplidh - feumaidh tu blocaichean a thaghadh le àireamh neo-shuidhichte de chlàran!

San fharsaingeachd, cò a tha a’ toirmeasg dhuinn a bhith a’ leughadh chan e “dìreach 26”, ach “nach eil nas lugha na 26”? Mar eisimpleir, gus am bi anns an ath bhloca clàran le brìgh gu tur eadar-dhealaichte ts - an uairsin cha bhith duilgheadas ann le clàran “leum” eadar blocaichean!

Seo mar a choileanas tu seo:

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;

Dè tha dol an seo?

  1. Bidh sinn a’ ceumnachadh 25 clàr “sìos” agus a’ faighinn an luach “crìoch”. ts.
  2. Mura h-eil dad ann mu thràth, cuir an àite an luach NULL -infinity.
  3. Bheir sinn air falbh an earrann gu lèir de luachan eadar an luach a fhuaireadh ts agus chaidh am paramadair $1 seachad bhon eadar-aghaidh (an luach “mu dheireadh” a chaidh a thoirt seachad roimhe).
  4. Ma thèid bloc a thilleadh le nas lugha na clàran 26, is e seo am fear mu dheireadh.

No an aon dealbh:
Antipatterns PostgreSQL: A’ seòladh a’ Chlàr

Oir a-nis tha againn chan eil “tòiseachadh” sònraichte sam bith aig an sampall, an uairsin chan eil dad a’ cur stad oirnn bho bhith “a’ leudachadh ”an t-iarrtas seo an taobh eile agus a’ cur an gnìomh luchdachadh fiùghantach de bhlocaichean dàta bhon “phuing iomraidh” anns gach taobh - an dà chuid sìos is suas.

Nota

  1. Tha, anns a 'chùis seo bidh sinn a' faighinn cothrom air a 'chlàr-amais dà uair, ach tha a h-uile dad "dìreach le clàr-amais". Mar sin, cha toir subquery ach toradh gu aon Scan Clàr-amais a-mhàin a bharrachd.
  2. Tha e gu math follaiseach nach urrainnear an dòigh seo a chleachdadh ach nuair a tha luachan agad ts chan urrainn dha a dhol tarsainn ach le cothrom, agus chan eil mòran dhiubh ann. Mas e a’ chùis àbhaisteach agad “millean clàr aig 00:00:00.000”, cha bu chòir dhut seo a dhèanamh. Tha mi a’ ciallachadh, cha bu chòir dhut leigeil le leithid de chùis tachairt. Ach ma thachras seo, cleachd an roghainn le clàr-amais leudaichte.

Source: www.habr.com

Cuir beachd ann