🥇PostgreSQL-i antimustrid: registris navigeerimine

Täna pole SQL-is keerulisi juhtumeid ega keerukaid algoritme. Kõik saab olema Captain Obvious'i tasemel väga lihtne - teeme ära sündmuste registri vaatamine aja järgi sorteeritud.

See tähendab, et andmebaasis on märk events, ja tal on põld ts - täpselt kellaaeg, mil tahame neid kirjeid korrapäraselt kuvada:

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

CREATE INDEX ON events(ts DESC);

On selge, et meil ei ole seal tosinat plaati, seega vajame mingit vormi lehel navigeerimine.

#0. "Ma olen oma ema 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]);

See pole peaaegu nali – see on haruldane, kuid looduses leidub. Mõnikord võib pärast ORM-iga töötamist olla raske SQL-iga "otsele" tööle lülituda.

Kuid liigume edasi levinumate ja vähem ilmsemate probleemide juurde.

#1. NIHE

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

Kust tuli number 26? See on ligikaudne kirjete arv ühe ekraani täitmiseks. Täpsemalt 25 kuvatud kirjet pluss 1, mis annab märku, et vähemalt midagi muud on näidises edasi ja on mõtet edasi liikuda.

Loomulikult ei saa seda väärtust päringu kehasse "õmmelda", vaid see edastatakse parameetri kaudu. Kuid sel juhul ei saa PostgreSQL-i planeerija tugineda teadmisele, et kirjeid peaks olema suhteliselt vähe – ja valib kergesti ebaefektiivse plaani.

Ja kuigi rakenduse liideses on registri vaatamine rakendatud visuaalsete "lehtede" vahel vahetamisena, ei märka keegi pikka aega midagi kahtlast. Täpselt selle hetkeni, mil UI/UX otsustab mugavuse nimel võideldes liidese ümber teha "lõputu kerimisega" - see tähendab, et kõik registrikirjed koostatakse ühte loendisse, mida kasutaja saab üles ja alla kerida.

Ja nii jäätegi järgmise testimise ajal vahele dokumentide dubleerimine registris. Miks, sest tabelis on tavaline indeks (ts), millele teie päring tugineb?

Täpselt sellepärast, et te ei võtnud seda arvesse ts ei ole unikaalne võti selles tabelis. Tegelikult ja selle väärtused ei ole ainulaadsed, nagu iga "aeg" reaalsetes tingimustes - seetõttu "hüppab" sama kirje kahes kõrvuti asetsevas päringus hõlpsalt lehelt lehele, kuna sama võtmeväärtuse sortimisel on erinev lõppjärjestus.

Tegelikult on siin peidus ka teine probleem, mida on palju raskem märgata - mõnda kirjet ei kuvata üleüldse! Lõppude lõpuks võtsid "duplikaat" kirjed kellegi teise koha. Täpsem seletus koos ilusate piltidega on leitav loe siit.

Indeksi laiendamine

Kaval arendaja mõistab, et indeksivõti tuleb muuta unikaalseks ja kõige lihtsam on seda laiendada ilmselgelt unikaalse väljaga, mille jaoks PK sobib suurepäraselt:

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

Ja taotlus muutub:

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

#2. Lülitu "kursoritele"

Mõni aeg hiljem tuleb teie juurde DBA ja on teie soovi üle hea meel nad koormavad serverit nagu paganama oma OFFSET-reeglitegaja üldiselt on aeg üle minna navigeerimine viimati näidatud väärtusest. Teie päring muteerub uuesti:

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

Hingasid kergendatult, kuni see tuli...

#3. Puhastusindeksid

Sest ühel päeval luges teie DBA artikkel ebaefektiivsete indeksite leidmise kohta ja sai sellest aru "Pole viimane" ajatempel ei ole hea. Ja ma tulin jälle teie juurde – nüüd mõttega, et see indeks peaks ikka tagasi pöörduma (ts DESC).

Aga mida teha esialgse kirjete lehtede vahel “hüppamise” probleemiga?.. Ja kõik on lihtne - tuleb valida fikseerimata kirjete arvuga plokid!

Üldiselt, kes keelab meil lugeda mitte "täpselt 26", vaid "mitte vähem kui 26"? Näiteks nii, et järgmises plokis on ilmselgelt erineva tähendusega plaadid ts - siis ei teki probleeme kirjete "hüppamisega" plokkide vahel!

Selle saavutamiseks tehke järgmist.

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;

Mis siin toimub?

Astume 25 kirjet "alla" ja saame "piiri" väärtuse ts.
Kui seal pole juba midagi, asendage väärtus NULL väärtusega -infinity.
Me lahutame saadud väärtuste vahelt kogu väärtuste segmendi ts ja liidesest edastatud parameeter $1 (eelmine "viimane" renderdatud väärtus).
Kui plokk tagastatakse vähem kui 26 kirjega, on see viimane.

Või sama pilt:

Sest nüüd on meil proovil ei ole mingit konkreetset "algust", siis ei takista miski meil seda taotlust vastupidises suunas "laiendamast" ja andmeplokkide dünaamilist laadimist "võrdluspunktist" mõlemas suunas - nii alla kui ka üles.

Märkus:

Jah, sel juhul pääseme indeksi juurde kaks korda, kuid kõik toimub "puhtalt indeksi järgi". Seetõttu annab alampäringu tulemuseks ainult ühele täiendavale Ainult registri skannimisele.
On üsna ilmne, et seda tehnikat saab kasutada ainult siis, kui teil on väärtused ts saab ületada ainult juhuslikult ja neid pole palju. Kui teie tüüpiline juhtum on "miljon kirjet kell 00:00:00.000", ei tohiks te seda teha. Ma mõtlen, et te ei tohiks lubada sellisel juhtumil juhtuda. Kuid kui see juhtub, kasutage laiendatud indeksiga valikut.

Allikas: www.habr.com

PostgreSQL-i antimustrid: registris navigeerimine