ProHoster > Блог > Whakahaerenga > PostgreSQL Antipatterns: Te whakatere i te Rēhita

PostgreSQL Antipatterns: Te whakatere i te Rēhita

I tenei ra karekau he keehi uaua me nga algorithms maamaa i roto i te SQL. Ka tino ngawari nga mea katoa, i te taumata o Captain Obvious - me mahi te tiro i te rehita takahanga kōmakahia mā te wā.

Arā, he tohu kei roto i te pātengi raraunga events, a he mara tana ts - ko te wa tika e hiahia ana matou ki te whakaatu i enei rekoata i runga i te raupapa tika:

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

CREATE INDEX ON events(ts DESC);

E marama ana karekau he tatini rekoata kei reira, no reira me hiahia etahi ahua whakatere wharangi.

#0. "Ko ahau te kaiparapara o toku whaea"

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

Karekau he katakata - he onge, engari ka kitea i te ngahere. I etahi wa, i muri i te mahi me te ORM, he uaua ki te huri ki te mahi "tika" me te SQL.

Engari kia neke atu ki nga raruraru noa ake me te kore e kitea.

#1. WAHI

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

No hea te nama 26? Koinei te tata o nga whakaurunga hei whakaki i te mata kotahi. He tika ake, 25 nga rekoata i whakaatuhia, me te 1, e tohu ana he mea ke atu ano kei roto i te tauira, a he mea tika kia haere tonu.

Ko te tikanga, kaore e taea te "tuia" tenei uara ki roto i te tinana o te tono, engari ka paahitia i roto i tetahi tawhā. Engari i tenei keehi, kaore e taea e te Kaitakataka PostgreSQL te whakawhirinaki ki runga i te mohiotanga he iti noa nga rekoata - a ka ngawari te whiriwhiri i tetahi mahere koretake.

A, i te wa i roto i te atanga tono, ko te tiro i te rehita ka whakatinanahia hei huri i waenga i nga "wharangi" tirohanga, kaore tetahi e kite i tetahi mea whakapae mo te wa roa. Tae noa ki te wa, i roto i te pakanga mo te waatea, ka whakatau a UI / UX ki te hanga ano i te atanga ki te "panuku mutunga kore" - ara, ko nga whakaurunga rehita katoa ka tuhia ki te rarangi kotahi ka taea e te kaiwhakamahi te panuku ki runga me raro.

Na, i nga whakamatautau e whai ake nei, ka mau koe te tāruarua o ngā rekoata i roto i te rehita. He aha, na te mea he taupū noa te ripanga (ts), e whakawhirinaki ana to patai?

Na te mea kaore koe i whai whakaaro ki tera ts ehara i te taviri ahurei i tenei tepu. Mau, a ehara i te mea ahurei ona uara, pera i nga "wa" i roto i nga ahuatanga tuuturu - na reira, ko te rekoata kotahi i roto i nga patai e rua e tata ana ka "peke" mai i tetahi wharangi ki tetahi wharangi na te rereke o nga ota whakamutunga i roto i te anga o te komaka i te uara matua kotahi.

Inaa, he raru tuarua kei te huna i konei, he uaua ake te kite - e kore e whakaatuhia etahi whakaurunga rawa! I muri i nga mea katoa, ko nga rekoata "takirua" i tango i te waahi o tetahi atu. Ka kitea he whakamaramatanga me nga pikitia ataahua pānuihia i konei.

Te whakawhānui i te taupū

Kei te mohio te kaiwhakawhanake mohio me hanga motuhake te taviri taurangi, a ko te huarahi ngawari ko te whakawhanui me tetahi mara motuhake, he mea tino pai te PK mo:

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

Na ka huri te tono:

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

#2. Huri ki "pehu"

I etahi wa i muri mai, ka tae mai he DBA ki a koe ka "koa" ki o tono ka utaina e ratou te tūmau ano he reinga me o ratou ture OFFSET, me te nuinga, kua tae ki te wa ki te huri ki whakaterenga mai i te uara whakamutunga kua whakaatuhia. Ka huri ano to patai:

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

I manawa koe i te manawa kia tae mai...

#3. Whakapai i nga tohu

No te mea i tetahi ra ka panui to DBA tuhinga e pa ana ki te rapu i nga taurangi kore whai hua a ka mohio tera "ehara i te mea hou" kaore i te pai te tohu waahi. Na ka hoki mai ano ahau ki a koe - inaianei me te whakaaro me huri ano taua taurangi ki roto (ts DESC).

Engari me aha ki te raru tuatahi o te "peke" rekoata i waenga i nga wharangi?

I te nuinga o te waa, ko wai te mea e aukati ana kia kaua e panui "tika 26", engari "kaua e iti iho i te 26"? Hei tauira, na i te poraka e whai ake nei kei reira he rekoata he rereke nga tikanga ts - karekau he raruraru ki te "peke" rekoata i waenga i nga poraka!

Anei me pehea te whakatutuki i tenei:

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;

He aha kei konei?

  1. Ka takahia e matou nga rekoata 25 "ki raro" ka whiwhi i te uara "rohe". ts.
  2. Mena kaore he mea i reira, katahi ka whakakapi i te uara NULL ki -infinity.
  3. Ka tangohia e matou te waahanga katoa o nga uara i waenga i te uara kua riro ts me te tawhā $1 i tukuna mai i te atanga (te uara "whakamutunga" o mua).
  4. Mena ka whakahokia mai he poraka me nga rekoata iti iho i te 26, koinei te mea whakamutunga.

Ko te pikitia ano ranei:
PostgreSQL Antipatterns: Te whakatere i te Rēhita

No te mea kei a tatou inaianei karekau he "timatanga" motuhake o te tauira, kaore he mea e aukati i a maatau ki te "whakawhanui" i tenei tono ki tera taha me te whakatinana i te uta kaha o nga poraka raraunga mai i te "tohu tohutoro" ki nga taha e rua - ki raro me runga.

Tuhipoka:

  1. Ae, i roto i tenei take ka uru atu matou ki te taurangi e rua, engari ko nga mea katoa "ma te taurangi". No reira, ka puta noa he patai ki tetahi atu Taurangi Matawai Anake.
  2. E tino kitea ana ka taea anake te whakamahi i tenei tikanga ina whai uara koe ts ka taea te whiti noa ma te tupono noa, a kahore he tokomaha o ratou. Mena ko to keehi "he miriona rekoata i te 00:00:00.000", kaore koe e mahi i tenei. Ko taku tikanga, kaua koe e whakaae kia puta he keehi penei. Engari ki te tupu tenei, whakamahia te whiringa me te taurangi roa.

Source: will.com

Tāpiri i te kōrero