ProHoster > Blog > Pulega > PostgreSQL Antipatterns: Su'e ile Resitala

PostgreSQL Antipatterns: Su'e ile Resitala

I aso nei o le a leai ni faʻalavelave lavelave ma faʻapitoa algorithms i SQL. O mea uma o le a faigofie tele, i le tulaga o Kapeteni Obvious - tatou faia matamata i le resitala o mea na tutupu fa'avasega i le taimi.

O lona uiga, o loʻo i ai se faʻailoga i totonu o faʻamaumauga events, ma e iai lona fanua ts - o le taimi tonu tatou te mananaʻo e faʻaalia ai nei faʻamaumauga i se faiga faʻatonu:

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

CREATE INDEX ON events(ts DESC);

E manino lava o le a le maua ni a matou faamaumauga iina, o lea matou te manaʻomia ai se ituaiga folauga itulau.

#0. "O aʻu o loʻu tina pogromist"

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 toetoe lava a le o se mea ula - e seasea, ae maua i le vao. O nisi taimi, pe a uma ona galue ma le ORM, e mafai ona faigata ona fesuiaʻi i le "saʻo" galuega ma SQL.

Ae se'i o tatou aga'i atu i fa'afitauli masani ma fa'aletonu.

#1. FA'AFIA

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

O fea na sau ai le numera 26? Ole numera fa'atatau lea o fa'amaumauga e fa'atumu ai le lau e tasi. E sili atu le saʻo, 25 faʻaalia faamaumauga, faʻatasi ai ma le 1, faʻailoga o loʻo i ai se isi mea e sili atu i le faʻataʻitaʻiga ma e talafeagai le faʻaauau.

Ioe, o lenei tau e le mafai ona "su'iina" i totonu o le tino o le talosaga, ae pasia se parakalafa. Ae i lenei tulaga, o le a le mafai e le PostgreSQL scheduler ona faʻalagolago i le malamalama e tatau ona i ai ni nai faʻamaumauga - ma o le a faigofie ona filifili se fuafuaga le aoga.

Ma aʻo iai i totonu o le faʻaoga faʻaoga, o le vaʻavaʻai i le resitala e faʻatinoina e pei o le fesuiaiga i le va o "itulau" vaaia, e leai se tasi na te matauina se mea masalosalo mo se taimi umi. Seʻia oʻo lava i le taimi, i le tauiviga mo le faʻaogagofie, ua filifili UI / UX e toe faʻaleleia le atinaʻe i le "taʻavale e le gata" - o lona uiga, o faʻamaumauga uma o le resitala e tusia i se lisi e tasi e mafai e le tagata faʻaoga ona taʻavale i luga ma lalo.

Ma o lea, i le isi suʻega, ua maua oe kopiina o faamaumauga i le resitala. Aisea, ona o le laulau o loʻo i ai se faʻailoga masani (ts), o fea e fa'alagolago iai lau fesili?

E tonu ona e te leʻi amanaia lena mea ts e le o se ki tulaga ese i lenei laulau. O le mea moni, ma e le tulaga ese ona tau, pei o so'o se "taimi" i tulaga moni - o le mea lea, o le fa'amaumauga tutusa i fesili e lua e sosoo ai e faigofie lava ona "oso" mai lea itulau i lea itulau ona o se fa'atonuga mulimuli e ese i totonu o le fa'avae o le fa'avasegaina o le tau autu tutusa.

O le mea moni, o loʻo i ai foi se faʻafitauli lona lua o loʻo natia iinei, lea e sili atu ona faigata ona matauina - o nisi fa'amatalaga o le a le fa'aalia o na uma lava! A uma mea uma, o faʻamaumauga "faʻalua" na ave i se isi tagata. E mafai ona maua se faʻamatalaga auiliili ma ata matagofie faitau ii.

Faalautele le faasinoupu

E malamalama se tagata poto poto e manaʻomia ona faʻapitoa le faʻasino igoa, ma o le auala pito sili ona faigofie o le faʻalauteleina lea i se tulaga tulaga ese, lea PK e lelei mo:

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

Ma e suia le talosaga:

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

#2. Su'e i "cursors"

I se taimi mulimuli ane, e sau se DBA ia te oe ma e "fiafia" i au talosaga latou utaina le server e pei o seoli ma latou tulafono OFFSET, ma i se tulaga lautele, ua oʻo i le taimi e sui ai i fa'asagaga mai le tau mulimuli na fa'aalia. Ua toe suia lau fesili:

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

Na e mānavaina se mapuea o le toomaga seia oo mai...

#3. Fa'amama fa'ailoga

Aua i se tasi aso na faitau lau DBA tusiga e uiga i le sailia o fa'asino igoa le aoga ma iloa ai lena mea “e le o le taimi fou” e le lelei. Ma sa ou toe sau ia te oe - i le taimi nei ma le manatu e tatau lava ona toe foi i tua lena faasino upu (ts DESC).

Ae o le a le mea e fai i le faʻafitauli muamua o le "osooso" faʻamaumauga i le va o itulau?.. Ma o mea uma e faigofie - e tatau ona e filifilia poloka ma se numera le tumau o faamaumauga!

I se tulaga lautele, o ai e faʻasaina i tatou e le faitau "saʻo 26", ae "ia le itiiti ifo i le 26"? Mo se faʻataʻitaʻiga, ina ia i ai i le isi poloka fa'amaumauga e manino le eseese o uiga ts - ona leai lea o se faʻafitauli i faʻamaumauga "oso" i le va o poloka!

O le auala lenei e ausia ai lenei mea:

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;

O le a le mea o tupu iinei?

  1. Matou te laa 25 faʻamaumauga "i lalo" ma maua le tau o le "tuaoi". ts.
  2. Afai e leai se mea iina, ona sui lea o le tau NULL i -infinity.
  3. Matou te toesea le vaega atoa o tau i le va o le tau na maua ts ma le $1 parakalafa pasi mai le atinaʻe (o le "mulimuli" na tuʻuina atu le tau).
  4. Afai e toe faafoi se poloka e itiiti ifo i le 26 faamaumauga, o le mea mulimuli lea.

Po o le ata lava lea e tasi:
PostgreSQL Antipatterns: Su'e ile Resitala

Aua o lea ua tatou maua ole fa'ata'ita'iga e leai se "amataga" fa'apitoa, ona leai lea o se mea e taofia ai i tatou mai le "faʻalauteleina" lenei talosaga i le isi itu ma le faʻatinoina o le utaina malosi o poloka faʻamaumauga mai le "faʻasinomaga" i itu uma e lua - i lalo ma luga.

Manatua

  1. Ioe, i lenei tulaga tatou te maua le faasino igoa faalua, ae o mea uma e "mama i le faasino igoa". O le mea lea, o se subquery o le a naʻo le iʻuga i le tasi fa'aopoopo Fa'asinomaga Na'o le Va'aiga.
  2. E matua manino lava o lenei metotia e mafai ona faʻaaogaina pe a iai sau faʻatauga ts e mafai ona sopoia na o se avanoa, ma e le toatele i latou. Afai o lau mataupu masani o le "miliona faamaumauga i le 00:00:00.000", e le tatau ona e faia lenei mea. Ou te fai atu, e le tatau ona e faatagaina se tulaga faapena e tupu. Ae afai e tupu lenei mea, faʻaaoga le filifiliga ma se faʻamatalaga faʻalautele.

puna: www.habr.com

Faaopoopo i ai se faamatalaga