I-ProHoster > Блог > Ukuphatha > I-PostgreSQL Antipatterns: Ukuzulazula kuRegistry

I-PostgreSQL Antipatterns: Ukuzulazula kuRegistry

Namuhla ngeke kube namacala ayinkimbinkimbi nama-algorithms ayinkimbinkimbi ku-SQL. Konke kuzoba lula kakhulu, ezingeni likaCaptain Obvious - ake sikwenze ukubuka ukubhaliswa komcimbi ihlungwe ngesikhathi.

Okusho ukuthi, kukhona uphawu ku-database events, futhi unensimu ts - ngaso kanye isikhathi lapho sifuna ukubonisa khona lawa marekhodi ngendlela ehlelekile:

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

CREATE INDEX ON events(ts DESC);

Kuyacaca ukuthi ngeke sibe namarekhodi ayishumi nambili lapho, ngakho-ke sizodinga uhlobo oluthile ukuzulazula kwekhasi.

#0. "Ngingumbulali kamama wami"

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

Cishe akulona ihlaya - akuvamile, kodwa kutholakala endle. Kwesinye isikhathi, ngemuva kokusebenza ne-ORM, kungaba nzima ukushintshela emsebenzini "oqondile" nge-SQL.

Kodwa ake siqhubekele ezinkingeni ezivamile nezingacacile kangako.

#1. OFFSET

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

Inombolo 26 yavelaphi? Lena inombolo elinganisiwe yokufakiwe ukuze kugcwalise isikrini esisodwa. Ngokunembayo, amarekhodi abonisiwe angama-25, kanye no-1, okubonisa ukuthi kukhona okunye okunye okwengeziwe kusampula futhi kunengqondo ukuqhubeka.

Yiqiniso, leli nani alikwazi "ukuthungwa" emzimbeni wesicelo, kodwa lidlule kupharamitha. Kodwa kulokhu, umhleli we-PostgreSQL ngeke akwazi ukuthembela olwazini lokuthi kufanele kube namarekhodi ambalwa uma kuqhathaniswa - futhi uzokhetha kalula uhlelo olungasebenzi.

Futhi ngenkathi ukwisixhumi esibonakalayo sohlelo lokusebenza, ukubuka ukubhalisa kusetshenziswa njengokushintsha phakathi “kwamakhasi” abonakalayo, akekho oqaphela noma yini esolisayo isikhathi eside. Impela kuze kube isikhathi lapho, emzabalazweni wokwenza lula, i-UI/UX inquma ukwenza kabusha isixhumi esibonakalayo ukuze “siskrole ngokungapheli” - okungukuthi, konke okufakiwe kokubhalisa zidwetshwe ohlwini olulodwa umsebenzisi angakwazi ukuskrola phezulu naphansi.

Ngakho-ke, phakathi nokuhlolwa okulandelayo, uyabanjwa ukuphindwaphindwa kwamarekhodi endaweni yokubhalisa. Kungani, ngoba ithebula linenkomba evamile (ts), umbuzo wakho uncike kuphi?

Impela ngoba awuzange ukucabange lokho ts akuyona ukhiye oyingqayizivele kuleli tafula. Eqinisweni, futhi amanani ayo awahlukile, njenganoma yisiphi “isikhathi” ezimweni zangempela - ngakho-ke, irekhodi elifanayo emibuzweni emibili eseduze “leqa” kalula lisuka ekhasini liye ekhasini ngenxa yokuhleleka kokugcina okuhlukile ngaphakathi kohlaka lokuhlunga inani lokhiye elifanayo.

Eqinisweni, kuphinde kube nenkinga yesibili efihliwe lapha, okunzima kakhulu ukuyibona - okunye okufakiwe ngeke kuboniswe kube bonke! Phela, amarekhodi "ayimpinda" athathe indawo yomunye umuntu. Incazelo enemininingwane enezithombe ezinhle ingatholakala funda lapha.

Ukunweba inkomba

Unjiniyela onobuqili uyaqonda ukuthi ukhiye wenkomba udinga ukwenziwa uhluke, futhi indlela elula ukuwukhulisa ngenkambu ehlukile esobala, i-PK eyilungele:

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

Futhi isicelo siyashintsha:

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

#2. Shintshela kokuthi “izikhombisi”

Ngemva kwesikhathi esithile, i-DBA iza kuwe futhi “iyajabula” ukuthi izicelo zakho balayisha iseva njengesihogo ngemithetho yabo ye-OFFSET, futhi ngokujwayelekile, yisikhathi sokushintshela kuso ukuzulazula kusuka kunani lokugcina elibonisiwe. Umbuzo wakho uyashintsha futhi:

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

Waphefumulela phezulu waze wafika...

#3. Ukuhlanza izinkomba

Ngoba ngolunye usuku i-DBA yakho yafunda isihloko esimayelana nokuthola izinkomba ezingasebenzi futhi wakuqaphela lokho Isitembu sesikhathi esithi “hhayi esakamuva” asisihle. Futhi ngize kuwe futhi - manje ngomcabango wokuthi leyo nkomba kusafanele ibuyele emuva (ts DESC).

Kodwa yini okufanele uyenze ngenkinga yokuqala "yokweqa" amarekhodi phakathi kwamakhasi? .. Futhi konke kulula - udinga ukukhetha amabhulokhi ngenombolo engagxiliwe yamarekhodi!

Ngokuvamile, ubani osenqabela ukuthi singafundi “ngqo 26”, kodwa “hhayi ngaphansi kuka-26”? Ngokwesibonelo, ukuze block elilandelayo kukhona amarekhodi anezincazelo ezahlukene ngokusobala ts - khona-ke ngeke kube nenkinga ngamarekhodi "okweqa" phakathi kwamabhulokhi!

Nansi indlela yokufeza lokhu:

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;

Kwenzakalani lapha?

  1. Sinyathela amarekhodi angama-25 "phansi" futhi sithole inani "lomngcele". ts.
  2. Uma kungekho lutho kakade, shintsha inani le-NULL ngokuthi -infinity.
  3. Sikhipha yonke ingxenye yamanani phakathi kwevelu etholiwe ts kanye nepharamitha engu-$1 edluliselwe kusixhumi esibonakalayo (inani elinikeziwe “lokugcina” langaphambilini).
  4. Uma ibhulokhi ibuyiswa inamarekhodi angaphansi kuka-26, ingeyokugcina.

Noma isithombe esifanayo:
I-PostgreSQL Antipatterns: Ukuzulazula kuRegistry

Ngoba manje sesinabo isampula alinaso "isiqalo" esiqondile, lapho-ke akukho okusivimbelayo “ekwandiseni” lesi sicelo kwelinye icala futhi sisebenzise ukulayishwa okuguquguqukayo kwamabhulokhi edatha ukusuka “kuphoyinti lereferensi” kuzo zombili izinkomba - kokubili phansi naphezulu.

Qaphela

  1. Yebo, kulesi simo sifinyelela inkomba kabili, kodwa yonke into "i-purely by index". Ngakho-ke, i-subquery izophumela kuphela kokukodwa okwengeziwe Ukuskena Kwenkomba Kuphela.
  2. Kusobala ukuthi le ndlela ingasetshenziswa kuphela uma unamanani ts angawela ngenhlanhla kuphela, futhi azikho eziningi zazo. Uma icala lakho elijwayelekile “lingamarekhodi ayisigidi ngo-00:00:00.000”, akufanele ukwenze lokhu. Ngisho ukuthi, akufanele uvumele icala elinjalo ukuba lenzeke. Kodwa uma lokhu kwenzeka, sebenzisa inketho ngenkomba enwetshiwe.

Source: www.habr.com

Engeza amazwana