ProHoster > Blog > Tsamaiso > PostgreSQL Antipatterns: Ho tsamaisa Registry

PostgreSQL Antipatterns: Ho tsamaisa Registry

Kajeno ha ho na ho ba le linyeoe tse rarahaneng le li-algorithms tse rarahaneng ho SQL. Ntho e 'ngoe le e' ngoe e tla ba bonolo haholo, boemong ba Captain Obvious - ha re e etse ho sheba ngoliso ea liketsahalo hlophisoa ka nako.

Ke hore, ho na le lets'oao ho database events, mme o na le tshimo ts - hantle nako eo re batlang ho hlahisa lirekoto tsena ka mokhoa o hlophisehileng:

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

CREATE INDEX ON events(ts DESC);

Ho hlakile hore re ke ke ra ba le lirekoto tse leshome le metso e 'meli moo, kahoo re tla hloka mofuta o itseng oa ho tsamaea leqepheng.

#0. "Ke 'na motlatsi oa' mè oa ka"

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

E batla e se metlae - ha e tloaelehe, empa e fumanoa naheng. Ka linako tse ling, ka mor'a ho sebetsa le ORM, ho ka ba thata ho fetohela mosebetsing oa "direct" le SQL.

Empa ha re feteleng pele ho mathata a tloaelehileng le a sa bonahaleng haholo.

#1. TSOHLE

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

Nomoro ea 26 e tsoa hokae? Ena ke palo e lekantsoeng ea likenyo ho tlatsa skrini se le seng. Hantle-ntle, lirekoto tse 25 tse bonts'itsoeng, hammoho le 1, tse bontšang hore bonyane ho na le ntho e 'ngoe hape ka har'a sampole mme hoa utloahala ho tsoela pele.

Ha e le hantle, boleng bona bo ke ke ba "rokoa" 'meleng oa kopo, empa bo fetisoa ka parameter. Empa tabeng ena, kemiso ea PostgreSQL e ke ke ea khona ho itšetleha ka tsebo ea hore ho lokela ho ba le litlaleho tse fokolang - mme o tla khetha habonolo moralo o sa sebetseng.

'Me ha u ntse u le sebopeho sa kopo, ho shebella registry ho kenngoa ts'ebetsong pakeng tsa "maqephe" a bonahalang, ha ho motho ea hlokomelang ntho leha e le efe e belaetsang ka nako e telele. Hantle-ntle ho fihlela motsotso oo, ntoeng ea ho ba bonolo, UI / UX e etsa qeto ea ho fetola sebopeho hore se "phelloe ka ho sa feleng" - ke hore, lipehelo tsohle tsa ngoliso li huloa lethathamong le le leng leo mosebelisi a ka le tsamaisang holimo le tlase.

'Me kahoo, nakong ea teko e latelang, ua tšoaroa phetiso ea litlaleho ho ngolisoang. Hobaneng, hobane tafole e na le index e tloaelehileng (ts), eo potso ea hau e itšetlehileng ka eona?

Hantle hobane o ne o sa nahane ka seo ts ha se senotlolo se ikhethileng tafoleng ena. Ha e le hantle, le boleng ba eona ha bo ikhethang, joalo ka "nako" efe kapa efe maemong a nnete - ka hona, rekoto e tšoanang lipotsong tse peli tse haufi "e tlolela" habonolo ho tloha leqepheng ho ea leqepheng ka lebaka la tatellano e fapaneng ea ho qetela ka har'a moralo oa ho hlophisa boleng bo tšoanang ba senotlolo.

Ebile, ho boetse ho na le bothata ba bobeli bo patiloeng mona, boo ho leng thata haholo ho bo bona - tse ling li ke ke tsa bontšoa ho hang! Ntle le moo, lirekoto tsa "kopi" li nkile sebaka sa motho e mong. Tlhaloso e qaqileng e nang le litšoantšo tse ntle e ka fumanoa bala mona.

Ho atolosa index

Moqapi ea masene o utloisisa hore senotlolo sa index se hloka ho etsoa se ikhethang, 'me tsela e bonolo ka ho fetisisa ke ho e atolosa ka tšimo e ikhethang, eo PK e loketseng bakeng sa eona:

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

Mme kopo e fetoha:

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

#2. Fetolela ho "cursors"

Nako hamorao, DBA e tla ho uena 'me e "thabela" likopo tsa hau ba kenya seva joalo ka lihele ka melao ea bona ea OFFSET, 'me ka kakaretso, ke nako ea ho fetohela ho navigation ho tsoa ho boleng ba ho qetela bo bontšitsoeng. Potso ea hau e fetoha hape:

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

O ile wa hema ho fihlela e fihla...

#3. Ho hloekisa li-index

Hobane ka letsatsi le leng DBA ea hau e ile ea bala sengoloa se mabapi le ho fumana li-index tse sa sebetseng mme a hlokomela seo Setempe sa nako "eseng sa morao-rao" ha se hantle. Mme ke ile ka tla ho lona hape - jwale ka mohopolo wa hore index eo e ntse e lokela ho fetoha (ts DESC).

Empa u lokela ho etsa'ng ka bothata ba pele ba "ho qhomela" litlaleho pakeng tsa maqephe?

Ka kakaretso, ke mang ea re hanelang ho bala eseng "hantle 26", empa "eseng ka tlase ho 26"? Ka mohlala, e le hore ka block e latelang ho na le direkoto tse nang le meelelo e fapaneng ka ho hlaka ts - joale ho ke ke ha e-ba le bothata ka "ho qhoma" litlaleho pakeng tsa li-blocks!

Mona ke mokhoa oa ho fihlela sena:

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;

Ho etsahalang moo?

  1. Re hata lirekoto tsa 25 "fatše" 'me re fumana boleng ba "moeli". ts.
  2. Haeba ha ho na letho le se le ntse le le teng, fetola boleng ba NULL ka -infinity.
  3. Re tlosa karolo eohle ea boleng lipakeng tsa boleng bo amoheloang ts le paramethara ea $ 1 e fetisitsoeng ho tsoa ho sehokelo (boleng bo fanoeng ke "ho qetela" bo fetileng).
  4. Haeba boloko bo khutlisoa ka lirekoto tse ka tlase ho 26, ke ea ho qetela.

Kapa setšoantšo se tšoanang:
PostgreSQL Antipatterns: Ho tsamaisa Registry

Hobane joale re na le sampole ha e na "qalo" e tobileng, joale ha ho letho le re thibelang ho "atolosa" kopo ena ka lehlakoreng le fapaneng le ho kenya ts'ebetsong ho kenya li-block tsa data ho tloha "reference point" mahlakoreng ka bobeli - ho ea tlase le holimo.

Hlokomela

  1. E, tabeng ena re fumana index habeli, empa ntho e 'ngoe le e' ngoe e "purely by index". Ka hona, subquery e tla fella feela ho e 'ngoe e eketsehileng ea Index Feela Scan.
  2. Ho hlakile hore mokhoa ona o ka sebelisoa feela ha o na le litekanyetso ts ka tšela feela ka monyetla, le ha ho ba bangata ba bona. Haeba nyeoe ea hau e tloaelehileng e le "litlaleho tse milione ka 00:00:00.000", ha ua lokela ho etsa sena. Ke bolela, ha ua lokela ho lumella nyeoe e joalo hore e etsahale. Empa haeba sena se etsahala, sebelisa khetho ka index e atolositsoeng.

Source: www.habr.com

Eketsa ka tlhaloso