ProHoster > blog > Utawala > Antipatterns za PostgreSQL: Kuelekeza kwenye Usajili

Antipatterns za PostgreSQL: Kuelekeza kwenye Usajili

Leo hakutakuwa na kesi ngumu na algorithms ya kisasa katika SQL. Kila kitu kitakuwa rahisi sana, kwa kiwango cha Kapteni Dhahiri - wacha tuifanye kutazama rejista ya tukio kupangwa kwa wakati.

Hiyo ni, kuna ishara katika hifadhidata events, na ana shamba ts - wakati hasa ambao tunataka kuonyesha rekodi hizi kwa njia ya utaratibu:

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

CREATE INDEX ON events(ts DESC);

Ni wazi kuwa hatutakuwa na rekodi kadhaa hapo, kwa hivyo tutahitaji aina fulani ya urambazaji wa ukurasa.

#0. "Mimi ni mchawi wa mama yangu"

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

Ni karibu si utani - ni nadra, lakini hupatikana katika pori. Wakati mwingine, baada ya kufanya kazi na ORM, inaweza kuwa vigumu kubadili kazi ya "moja kwa moja" na SQL.

Lakini wacha tuendelee kwenye shida za kawaida na zisizo wazi.

#1. OFFSET

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

Namba 26 ilitoka wapi? Hii ndiyo idadi ya takriban ya maingizo ya kujaza skrini moja. Kwa usahihi zaidi, rekodi 25 zilizoonyeshwa, pamoja na 1, zikiashiria kwamba kuna angalau kitu kingine zaidi katika sampuli na inaleta maana kuendelea.

Kwa kweli, dhamana hii haiwezi "kushonwa" kwenye mwili wa ombi, lakini kupita kwa paramu. Lakini katika kesi hii, kipanga ratiba cha PostgreSQL hataweza kutegemea maarifa kwamba kunapaswa kuwa na rekodi chache - na atachagua kwa urahisi mpango usiofaa.

Na ukiwa kwenye kiolesura cha programu, kutazama sajili kunatekelezwa kama kubadili kati ya "kurasa" zinazoonekana, hakuna mtu anayeona chochote cha kutiliwa shaka kwa muda mrefu. Hasa hadi wakati ambapo, katika mapambano ya urahisishaji, UI/UX itaamua kutengeneza tena kiolesura kuwa "kusogeza bila mwisho" - yaani, maingizo yote ya usajili yamechorwa katika orodha moja ambayo mtumiaji anaweza kusogeza juu na chini.

Na kwa hivyo, wakati wa majaribio yanayofuata, utakamatwa kurudia kumbukumbu katika Usajili. Kwa nini, kwa sababu meza ina index ya kawaida (ts), swali lako linategemea lipi?

Hasa kwa sababu haukuzingatia hilo ts sio ufunguo wa kipekee katika meza hii. Kwa kweli, na maadili yake si ya kipekee, kama "wakati" wowote katika hali halisi - kwa hivyo, rekodi sawa katika hoja mbili zilizo karibu "huruka" kwa urahisi kutoka ukurasa hadi ukurasa kwa sababu ya mpangilio tofauti wa mwisho ndani ya mfumo wa kupanga thamani sawa ya ufunguo.

Kwa kweli, pia kuna shida ya pili iliyofichwa hapa, ambayo ni ngumu zaidi kugundua - baadhi ya maingizo hayataonyeshwa hata kidogo! Baada ya yote, rekodi za "duplicate" zilichukua nafasi ya mtu mwingine. Maelezo ya kina na picha nzuri yanaweza kupatikana soma hapa.

Kupanua index

Msanidi programu janja anaelewa kuwa ufunguo wa faharasa unahitaji kufanywa kuwa wa kipekee, na njia rahisi ni kuupanua kwa uga wa kipekee, ambao PK inafaa kabisa:

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

Na ombi linabadilika:

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

#2. Badili hadi "cursors"

Muda fulani baadaye, DBA inakuja kwako na "imefurahishwa" kwamba maombi yako wanapakia seva kama kuzimu na sheria zao za OFFSET, na kwa ujumla, ni wakati wa kubadili urambazaji kutoka kwa thamani ya mwisho iliyoonyeshwa. Hoja yako inabadilika tena:

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

Ulishusha pumzi hadi ikafika...

#3. Fahirisi za kusafisha

Kwa sababu siku moja DBA yako ilisoma makala kuhusu kupata faharasa zisizofaa na kutambua hilo Muhuri wa wakati wa "sio wa hivi punde zaidi" sio mzuri. Na nilikuja kwako tena - sasa nikiwa na wazo kwamba faharisi hiyo inapaswa kugeuka kuwa tena (ts DESC).

Lakini ni nini cha kufanya na shida ya awali ya rekodi za "kuruka" kati ya kurasa?

Kwa ujumla, ni nani anayetuzuia kusoma "haswa 26", lakini "si chini ya 26"? Kwa mfano, ili katika block inayofuata kuna rekodi zenye maana tofauti dhahiri ts - basi hakutakuwa na shida na rekodi za "kuruka" kati ya vitalu!

Hivi ndivyo jinsi ya kufikia hili:

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;

Nini kinaendelea hapa?

  1. Tunaweka rekodi 25 "chini" na kupata thamani ya "mpaka". ts.
  2. Ikiwa hakuna kitu hapo tayari, basi badilisha thamani ya NULL na -infinity.
  3. Tunaondoa sehemu nzima ya thamani kati ya thamani iliyopokelewa ts na kigezo cha $1 kilichopitishwa kutoka kwa kiolesura (thamani ya awali ya "mwisho" iliyotolewa).
  4. Ikiwa kizuizi kitarejeshwa na rekodi chini ya 26, ni ya mwisho.

Au picha sawa:
Antipatterns za PostgreSQL: Kuelekeza kwenye Usajili

Kwa sababu sasa tunayo sampuli haina "mwanzo" maalum, basi hakuna kitakachotuzuia "kupanua" ombi hili katika mwelekeo tofauti na kutekeleza upakiaji unaobadilika wa vizuizi vya data kutoka kwa "hatua ya marejeleo" katika pande zote mbili - chini na juu.

Kumbuka

  1. Ndiyo, katika kesi hii tunapata index mara mbili, lakini kila kitu ni "purely by index". Kwa hivyo, subquery itasababisha tu kwa Fahirisi moja ya ziada Pekee Scan.
  2. Ni dhahiri kabisa kwamba mbinu hii inaweza kutumika tu wakati una maadili ts inaweza kuvuka kwa bahati tu, na hakuna wengi wao. Ikiwa kesi yako ya kawaida ni "rekodi milioni saa 00:00:00.000", hupaswi kufanya hivyo. Namaanisha, haupaswi kuruhusu kesi kama hiyo kutokea. Lakini ikiwa hii itatokea, tumia chaguo na index iliyopanuliwa.

Chanzo: mapenzi.com

Kuongeza maoni