ProHoster > Блог > Gudanarwa > PostgreSQL Antipatterns: Kewaya da Registry

PostgreSQL Antipatterns: Kewaya da Registry

A yau ba za a sami hadaddun lokuta da nagartattun algorithms a cikin SQL ba. Komai zai zama mai sauqi qwarai, a matakin Kyaftin bayyane - bari mu yi kallon wurin rajistar taron tsara ta lokaci.

Wato akwai alama a cikin rumbun adana bayanai events, kuma tana da filin ts - daidai lokacin da muke son nuna waɗannan bayanan a cikin tsari:

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

CREATE INDEX ON events(ts DESC);

A bayyane yake cewa ba za mu sami dozin dozin a can ba, don haka za mu buƙaci wani nau'i na kewayawa shafi.

#0. "Ni ne ma'aikacin pogromist mahaifiyata"

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

Kusan ba wasa ba ne - yana da wuya, amma ana samuwa a cikin daji. Wani lokaci, bayan aiki tare da ORM, yana iya zama da wahala a canza zuwa aikin "kai tsaye" tare da SQL.

Amma bari mu ci gaba zuwa matsalolin gama-gari kuma marasa ma'ana.

#1. OFFSET

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

Daga ina lambar 26 ta fito? Wannan shine kusan adadin shigarwar don cika allo ɗaya. Daidai daidai, 25 bayanan da aka nuna, da 1, suna nuna cewa akwai aƙalla wani abu dabam a cikin samfurin kuma yana da ma'ana don ci gaba.

Tabbas, wannan darajar ba za a iya "sanya" a cikin jikin buƙatun ba, amma an wuce ta hanyar siga. Amma a wannan yanayin, mai tsarawa na PostgreSQL ba zai iya dogara da sanin cewa yakamata a sami ƴan bayanai kaɗan ba - kuma cikin sauƙin zabar shirin mara inganci.

Kuma yayin da ke cikin aikace-aikacen aikace-aikacen, ana aiwatar da kallon rajista azaman sauyawa tsakanin "shafukan gani" na gani, ba wanda ya lura da wani abu mai tuhuma na dogon lokaci. Daidai har zuwa lokacin da, a cikin gwagwarmayar dacewa, UI/UX ya yanke shawarar sake yin mu'amala zuwa "gungurawa mara iyaka" - wato, duk shigarwar rajista ana zana su cikin jeri ɗaya wanda mai amfani zai iya gungurawa sama da ƙasa.

Don haka, yayin gwaji na gaba, an kama ku Kwafi na records a cikin rajista. Me ya sa, saboda tebur yana da ma'auni na al'ada (ts), wacce tambayar ku ta dogara a kai?

Daidai saboda ba ku yi la'akari da hakan ba ts ba maɓalli ba ne na musamman a cikin wannan tebur. A gaskiya, kuma dabi'unsa ba na musamman ba ne, kamar kowane "lokaci" a cikin yanayi na ainihi - don haka, rikodin guda ɗaya a cikin tambayoyin guda biyu a cikin sauƙi yana "tsalle" daga shafi zuwa shafi saboda wani tsari na ƙarshe na daban a cikin tsarin rarraba maɓalli ɗaya.

A gaskiya ma, akwai kuma matsala ta biyu da ke ɓoye a nan, wanda ya fi wuya a lura - ba za a nuna wasu shigarwar ba kwata-kwata! Bayan haka, bayanan "kwafi" sun ɗauki wurin wani. Ana iya samun cikakken bayani tare da kyawawan hotuna karanta a nan.

Fadada fihirisa

Mai haɓaka wayo ya fahimci cewa maɓallin fihirisar yana buƙatar zama na musamman, kuma hanya mafi sauƙi ita ce faɗaɗa shi tare da fili na musamman, wanda PK ya dace da:

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

Kuma buƙatar ta canza:

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

#2. Canja zuwa "cursors"

Wani lokaci daga baya, DBA ta zo gare ku kuma ya “ji daɗin” da buƙatun ku suna loda uwar garken kamar jahannama tare da dokokin OFFSET, kuma a gaba ɗaya, lokaci yayi da za a canza zuwa kewayawa daga ƙimar ƙarshe da aka nuna. Tambayar ku ta sake canzawa:

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

Taja numfashi har tazo...

#3. Fihirisar tsaftacewa

Domin wata rana DBA ku karanta labarin game da gano maƙasudai marasa inganci kuma ya gane cewa "ba sabon abu" timestamp ba shi da kyau. Kuma na sake zuwa gare ku - yanzu da tunanin cewa wannan fihirisar ya kamata ya koma baya (ts DESC).

Amma abin da za a yi tare da matsala ta farko na rubutun "tsalle" tsakanin shafuka? .. Kuma duk abin da yake mai sauƙi - kana buƙatar zaɓar tubalan tare da adadin bayanan da ba a kayyade ba!

Gabaɗaya, wa ya hana mu karanta ba "daidai 26", amma "ba ƙasa da 26 ba"? Misali, ta yadda a cikin toshe na gaba akwai records tare da a fili daban-daban ma'ana ts - to, ba za a sami matsala tare da "tsalle" rikodin tsakanin tubalan!

Ga yadda ake cimma wannan:

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;

Me ke faruwa a nan?

  1. Muna mataki na 25 rikodin "ƙasa" kuma muna samun darajar "iyaka". ts.
  2. Idan babu wani abu a can, to maye gurbin ƙimar NULL tare da -infinity.
  3. Muna cire duk ɓangaren ƙimar tsakanin ƙimar da aka karɓa ts da sigar $1 ta wuce daga mahaɗin (ƙimar "ƙarshe" da ta gabata).
  4. Idan an dawo da toshe tare da bayanan ƙasa da 26, shine na ƙarshe.

Ko hoto iri daya:
PostgreSQL Antipatterns: Kewaya da Registry

Domin yanzu muna da samfurin ba shi da takamaiman "farko", to, babu abin da zai hana mu "fadada" wannan buƙatar ta hanyar da aka saba da shi da kuma aiwatar da nauyin nauyin bayanai daga "ma'anar tunani" a cikin duka kwatance - duka ƙasa da sama.

Lura

  1. Ee, a cikin wannan yanayin muna samun damar yin amfani da index sau biyu, amma duk abin da yake "tsallai ta hanyar index". Don haka, ƙaddamarwa kawai zai haifar da zuwa ƙarin Fihirisar Fihirisa Kawai.
  2. A bayyane yake cewa ana iya amfani da wannan dabara kawai idan kuna da ƙima ts zai iya ketare kawai da kwatsam, kuma ba su da yawa. Idan al'amuran ku na yau da kullun shine "rubutun miliyan a 00:00:00.000", bai kamata ku yi wannan ba. Ina nufin, bai kamata ku ƙyale irin wannan lamari ya faru ba. Amma idan wannan ya faru, yi amfani da zaɓi tare da fihirisa mai tsawo.

source: www.habr.com

Add a comment