ProHoster > Pūnaewele > Nā Administration > Nā Antipatterns PostgreSQL: Ke hoʻokele nei i ka Registry

Nā Antipatterns PostgreSQL: Ke hoʻokele nei i ka Registry

I kēia lā, ʻaʻohe hihia paʻakikī a me nā algorithm maʻalahi i SQL. E maʻalahi loa nā mea a pau, ma ka pae o Kāpena Obvious - e hana kāua ke nānā ʻana i ka papa inoa hanana hoʻokaʻawale ʻia e ka manawa.

ʻO ia hoʻi, aia kahi hōʻailona ma ka waihona events, a he mahinaai kana ts - ʻo ia ka manawa a mākou e makemake ai e hōʻike i kēia mau moʻolelo ma ke ʻano kūpono:

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

CREATE INDEX ON events(ts DESC);

Ua maopopo ʻaʻole mākou e loaʻa i kahi mau moʻolelo ma laila, no laila pono mākou i kekahi ʻano hoʻokele ʻaoʻao.

#0. "ʻO wau ka pogromist a koʻu makuahine"

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

ʻAʻole ia he mea hoʻohenehene - he kakaikahi, akā loaʻa ma ka nahele. I kekahi manawa, ma hope o ka hana ʻana me ORM, hiki ke paʻakikī ke hoʻololi i ka hana "pololei" me SQL.

Akā, e neʻe kākou i nā pilikia maʻamau a emi ʻole.

#1. OFFSET

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

No hea mai ka helu 26? ʻO kēia ka helu kokoke o nā helu e hoʻopiha i hoʻokahi pale. ʻOi aku ka pololei, 25 hōʻike i nā moʻolelo, a me 1, e hōʻailona ana aia kekahi mea ʻē aʻe i loko o ka laʻana a he mea kūpono ke neʻe aku.

ʻOiaʻiʻo, ʻaʻole hiki ke "humuhumu" i kēia waiwai i loko o ke kino o ka noi, akā hele i kahi ʻāpana. Akā i kēia hihia, ʻaʻole hiki i ka mea hoʻonohonoho PostgreSQL ke hilinaʻi i ka ʻike he liʻiliʻi nā moʻolelo - a koho maʻalahi i kahi hoʻolālā kūpono ʻole.

A ʻoiai i loko o ka ʻaoʻao noi, ʻike ʻia ka nānā ʻana i ke kākau inoa e like me ka hoʻololi ʻana ma waena o nā "ʻaoʻao" ʻike, ʻaʻohe mea i ʻike i kekahi mea kānalua no ka manawa lōʻihi. A hiki i ka manawa, i ka hakakā ʻana no ka maʻalahi, ua hoʻoholo ʻo UI/UX e hana hou i ka interface i ka "scroll endless scroll" - ʻo ia hoʻi, ua huki ʻia nā mea kākau inoa āpau i kahi papa inoa e hiki ai i ka mea hoʻohana ke ʻōwili i luna a i lalo.

A no laila, i ka wā o ka hoʻāʻo aʻe, ua hopu ʻia ʻoe ka lua o na mooolelo ma ka papa inoa. No ke aha, no ka mea, he index maʻamau ka papaʻaina (ts), e hilina'i nei kāu nīnau?

No ka mea ʻaʻole ʻoe i noʻonoʻo i kēlā ts ʻaʻole ia he kī kū hoʻokahi ma keia papaaina. ʻOiaʻiʻo, a ʻAʻole kū hoʻokahi kāna mau waiwai, e like me kēlā me kēia "manawa" i nā kūlana maoli - no laila, ʻo ka moʻolelo like i loko o nā nīnau pili ʻelua e "lele" maʻalahi mai kahi ʻaoʻao a i kahi ʻaoʻao ma muli o kahi kauoha hope ʻē aʻe i loko o ke ʻano o ka hoʻokaʻawale ʻana i ka waiwai kī like.

ʻOiaʻiʻo, aia kekahi pilikia lua i hūnā ʻia ma aneʻi, ʻoi aku ka paʻakikī o ka ʻike - ʻaʻole e hōʻike ʻia kekahi mau helu i nā mea āpau! Ma hope o nā mea a pau, ua lawe nā moʻolelo "ʻelua" i kahi o kekahi. Hiki ke loaʻa kahi wehewehe kikoʻī me nā kiʻi nani heluhelu maanei.

Ka hoʻonui ʻana i ka papa kuhikuhi

Hoʻomaopopo ka mea hoʻomohala maalea e pono ke hana kūʻokoʻa ke kī kuhikuhi, a ʻo ke ala maʻalahi ka hoʻonui ʻana me kahi kahua kūʻokoʻa maopopo, kahi kūpono o PK no:

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

A hoʻololi ke noi:

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

#2. E hoʻololi i nā "cursors"

Ma hope o kekahi manawa, hiki mai kahi DBA iā ʻoe a "ʻoluʻolu" i kāu mau noi hoʻouka lākou i ke kikowaena e like me Gehena me kā lākou mau lula OFFSET, a ma ka laulā, ua hiki i ka manawa e hoʻololi i hoʻokele mai ka waiwai hope i hōʻike ʻia. Hoʻololi hou kāu nīnau:

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

Ua hanu ʻoe i ka ʻoluʻolu a hiki mai ...

#3. Hoʻomaʻemaʻe i nā kuhikuhi

No ka mea i kekahi lā heluhelu kāu DBA ʻatikala e pili ana i ka ʻimi ʻana i nā index pono ʻole a hoʻomaopopo i kēlā ʻAʻole maikaʻi ka manawa "ʻaʻole ka hope loa".. A hele hou mai nei au iā ʻoe - i kēia manawa me ka manaʻo e hoʻi hou kēlā kuhikuhi (ts DESC).

Akā he aha ka mea e hana ai me ka pilikia mua o ka "lele" moʻolelo ma waena o nā ʻaoʻao?

Ma keʻano laulā, ʻo wai ka mea e pāpā iā mākou e heluhelu ʻaʻole "26 pololei", akā "ʻaʻole emi ma lalo o 26"? No ka laʻana, i loko o ka poloka aʻe aia nā moʻolelo me nā manaʻo like ʻole ts - a laila, ʻaʻohe pilikia me ka "lele" moʻolelo ma waena o nā poloka!

Eia pehea e hoʻokō ai i kēia:

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 ka hana ma ʻaneʻi?

  1. Hoʻopaʻa mākou i nā moʻolelo 25 "lalo" a loaʻa ka waiwai "palena". ts.
  2. Inā ʻaʻohe mea ma laila, e hoʻololi i ka waiwai NULL me -infinity.
  3. Wehe mākou i ka ʻāpana holoʻokoʻa o nā waiwai ma waena o ka waiwai i loaʻa ts a ua hala ka ʻāpana $1 mai ke kikowaena (ka waiwai i hāʻawi ʻia "hope" mua).
  4. Inā hoʻihoʻi ʻia kahi poloka me ka liʻiliʻi o 26 mau moʻolelo, ʻo ia ka mea hope loa.

A i ʻole ke kiʻi like:
Nā Antipatterns PostgreSQL: Ke hoʻokele nei i ka Registry

No ka mea i kēia manawa ua loaʻa iā mākou ʻaʻohe "hoʻomaka" kikoʻī o ka hāpana, a laila ʻaʻohe mea e pale iā mākou mai ka "hoʻonui" i kēia noi ma ka ʻaoʻao ʻē aʻe a me ka hoʻokō ʻana i ka hoʻouka ikaika ʻana o nā poloka ʻikepili mai ka "helu kuhikuhi" ma nā ʻaoʻao ʻelua - i lalo a i luna.

Kākau

  1. ʻAe, i kēia hihia ke komo mākou i ka index ʻelua, akā ʻo nā mea āpau "ma ka index". No laila, he subquery wale nō ka hopena i hoʻokahi hou Index Only Scan.
  2. ʻIke maopopo loa hiki ke hoʻohana ʻia kēia ʻenehana inā loaʻa iā ʻoe nā waiwai ts hiki ke hele ma ka manawa wale nō, a aole nui o lakou. Inā ʻo kāu hihia maʻamau "he miliona mau moʻolelo ma 00:00:00.000", ʻaʻole pono ʻoe e hana i kēia. ʻO kaʻu manaʻo, ʻaʻole pono ʻoe e ʻae i kēlā hihia e hana. Akā inā loaʻa kēia, e hoʻohana i ke koho me kahi kuhikuhi lōʻihi.

Source: www.habr.com

Pākuʻi i ka manaʻo hoʻopuka