🥇PostgreSQL Antipatterns: Lilọ kiri iforukọsilẹ

Loni kii yoo si awọn ọran idiju ati awọn algoridimu fafa ni SQL. Ohun gbogbo yoo rọrun pupọ, ni ipele Captain O han - jẹ ki a ṣe wiwo iforukọsilẹ iṣẹlẹ lẹsẹsẹ nipasẹ akoko.

Iyẹn ni, ami kan wa ninu ibi ipamọ data events, ó sì ní oko ts - deede akoko ti a fẹ lati ṣafihan awọn igbasilẹ wọnyi ni ọna titoto:

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

CREATE INDEX ON events(ts DESC);

O han gbangba pe a kii yoo ni awọn igbasilẹ mejila nibẹ, nitorina a yoo nilo diẹ ninu awọn fọọmu lilọ kiri oju-iwe.

#0. "Emi ni pogromist iya mi"

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

O fẹrẹ kii ṣe awada - o ṣọwọn, ṣugbọn a rii ninu egan. Nigba miiran, lẹhin ṣiṣẹ pẹlu ORM, o le nira lati yipada si iṣẹ “taara” pẹlu SQL.

Ṣugbọn jẹ ki a lọ siwaju si awọn iṣoro ti o wọpọ ati ti ko han gbangba.

#1. OFFSET

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

Nibo ni nọmba 26 ti wa? Eyi ni nọmba isunmọ awọn titẹ sii lati kun iboju kan. Ni deede diẹ sii, awọn igbasilẹ ifihan 25, pẹlu 1, ti n ṣe afihan pe o kere ju nkan miiran wa siwaju ninu apẹẹrẹ ati pe o jẹ oye lati lọ siwaju.
Nitoribẹẹ, iye yii ko le “ṣen” sinu ara ti ibeere naa, ṣugbọn kọja nipasẹ paramita kan. Ṣugbọn ninu ọran yii, oluṣeto PostgreSQL kii yoo ni anfani lati gbẹkẹle imọ pe o yẹ ki o jẹ awọn igbasilẹ diẹ diẹ - ati pe yoo ni irọrun yan ero ti ko munadoko.

Ati pe lakoko ti o wa ni wiwo ohun elo, wiwo iforukọsilẹ ti wa ni imuse bi iyipada laarin “awọn oju-iwe” wiwo, ko si ẹnikan ti o ṣe akiyesi ohunkohun ifura fun igba pipẹ. Gangan titi di akoko ti, ninu Ijakadi fun wewewe, UI/UX pinnu lati tun wiwo si “yi lọ ailopin” - iyẹn ni, gbogbo awọn titẹ sii iforukọsilẹ ni a fa sinu atokọ kan ti olumulo le yi lọ si oke ati isalẹ.

Ati nitorinaa, lakoko idanwo atẹle, o ti mu išẹpo ti awọn igbasilẹ ninu awọn iforukọsilẹ. Kilode, nitori pe tabili ni itọka deede (ts), kini ibeere rẹ da lori?

Gangan nitori pe o ko ṣe akiyesi iyẹn ts kii ṣe bọtini alailẹgbẹ ninu tabili yii. Ni otitọ, ati awọn iye rẹ kii ṣe alailẹgbẹ, bii eyikeyi “akoko” ni awọn ipo gidi - nitorinaa, igbasilẹ kanna ni awọn ibeere isunmọ meji ni irọrun “fo” lati oju-iwe si oju-iwe nitori aṣẹ ipari ti o yatọ laarin ilana ti yiyan iye bọtini kanna.

Ni otitọ, iṣoro keji tun wa ti o farapamọ nibi, eyiti o nira pupọ lati ṣe akiyesi - diẹ ninu awọn titẹ sii kii yoo han rara! Lẹhinna, awọn igbasilẹ "ẹda" gba aaye elomiran. A alaye alaye pẹlu lẹwa awọn aworan le ṣee ri ka nibi.

Faagun atọka

Olùgbéejáde ọgbọ́n ẹ̀wẹ́ kan lóye pé kọ́kọ́rọ́ atọ́ka náà ní láti jẹ́ aláyọ̀, àti pé ọ̀nà tí ó rọrùn jùlọ ni láti faagun rẹ̀ pẹ̀lú pápá àkànṣe tí ó han gbangba, èyí tí PK jẹ́ pípé fún:

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

Ati pe ibeere naa yipada:

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

#2. Yipada si "awọn kọsọ"

Ni akoko diẹ lẹhinna, DBA kan wa si ọ ati pe o “dun” pe awọn ibeere rẹ nwọn fifuye awọn olupin bi apaadi pẹlu wọn OFFSET ofin, ati ni gbogbogbo, o to akoko lati yipada si lilọ lati kẹhin iye han. Ibeere rẹ tun yipada:

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

O s'ẹmi kan simi titi o fi de...

#3. Awọn atọka mimọ

Nitori ọjọ kan DBA rẹ ka nkan nipa wiwa awọn atọka ti ko wulo o si mọ pe "kii ṣe tuntun" timestamp ko dara. Ati pe Mo tun wa si ọdọ rẹ - ni bayi pẹlu ero pe atọka yẹn yẹ ki o tun pada si (ts DESC).

Ṣugbọn kini lati ṣe pẹlu iṣoro akọkọ ti awọn igbasilẹ "fifo" laarin awọn oju-iwe? .. Ati pe ohun gbogbo rọrun - o nilo lati yan awọn bulọọki pẹlu nọmba ti a ko fi sii ti awọn igbasilẹ!

Ni gbogbogbo, tani kọ fun wa lati ka kii ṣe “gangan 26”, ṣugbọn “ko kere ju 26”? Fun apẹẹrẹ, ki ninu awọn tókàn Àkọsílẹ nibẹ ni o wa igbasilẹ pẹlu o han ni o yatọ si itumo ts - lẹhinna kii yoo ni iṣoro pẹlu awọn igbasilẹ “fifo” laarin awọn bulọọki!

Eyi ni bii o ṣe le ṣaṣeyọri eyi:

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;

Kini n ṣẹlẹ nibi?

A ṣe igbasilẹ awọn igbasilẹ 25 "isalẹ" ati gba iye "aala". ts.
Ti ko ba si nkankan tẹlẹ, lẹhinna rọpo NULL iye pẹlu -infinity.
A yọkuro gbogbo apakan awọn iye laarin iye ti o gba ts ati paramita $ 1 ti o kọja lati wiwo (iṣaaju “ipari” iye ti a ṣe).
Ti o ba ti pada Àkọsílẹ pẹlu kere ju 26 igbasilẹ, o jẹ awọn ti o kẹhin.

Tabi aworan kanna:

Nitori bayi a ni apẹẹrẹ ko ni eyikeyi “ibẹrẹ” kan pato, lẹhinna ko si ohun ti o ṣe idiwọ fun wa lati “fifẹ” ibeere yii ni ọna idakeji ati imuse ikojọpọ agbara ti awọn bulọọki data lati “ojuami itọkasi” ni awọn itọnisọna mejeeji - mejeeji isalẹ ati si oke.

Akiyesi

Bẹẹni, ninu ọran yii a wọle si atọka lẹẹmeji, ṣugbọn ohun gbogbo jẹ “odasaka nipasẹ atọka”. Nitorina, a subquery yoo nikan ja si ni si ọkan afikun Atọka Nikan wíwo.
O han gbangba pe ilana yii le ṣee lo nigbati o ba ni awọn iye ts le rekọja nikan nipa anfani, ati nibẹ ni o wa ko ọpọlọpọ awọn ti wọn. Ti ọran aṣoju rẹ jẹ “awọn igbasilẹ miliọnu kan ni 00:00:00.000”, o yẹ ki o ko ṣe eyi. Mo tumọ si, o ko yẹ ki o jẹ ki iru ọran bẹ ṣẹlẹ. Ṣugbọn ti eyi ba ṣẹlẹ, lo aṣayan pẹlu itọka ti o gbooro sii.

orisun: www.habr.com

PostgreSQL Antipatterns: Lilọ kiri ni iforukọsilẹ