ProHoster > blogg > administration > PostgreSQL-antimönster: Navigera i registret

PostgreSQL-antimönster: Navigera i registret

Idag kommer det inte att finnas några komplexa fall och sofistikerade algoritmer i SQL. Allt kommer att vara väldigt enkelt, på nivån Captain Obvious - låt oss göra det visa händelseregistret sorterade efter tid.

Det vill säga att det finns en skylt i databasen events, och hon har ett fält ts - exakt den tidpunkt då vi vill visa dessa poster på ett ordnat sätt:

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

CREATE INDEX ON events(ts DESC);

Det är klart att vi inte kommer att ha ett dussin rekord där, så vi kommer att behöva någon form av sidnavigering.

#0. "Jag är min mammas 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]);

Det är nästan inte ett skämt - det är sällsynt, men finns i det vilda. Ibland, efter att ha arbetat med ORM, kan det vara svårt att byta till "direkt" arbete med SQL.

Men låt oss gå vidare till vanligare och mindre uppenbara problem.

#1. OFFSET

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

Var kom siffran 26 ifrån? Detta är det ungefärliga antalet poster för att fylla en skärm. Mer exakt, 25 visade poster, plus 1, som signalerar att det åtminstone finns något annat längre i urvalet och att det är vettigt att gå vidare.

Naturligtvis kan detta värde inte "sys" in i förfrågan, utan skickas genom en parameter. Men i det här fallet kommer PostgreSQL-schemaläggaren inte att kunna lita på vetskapen om att det borde finnas relativt få poster – och kommer lätt att välja en ineffektiv plan.

Och medan det i applikationsgränssnittet, visning av registret implementeras som växling mellan visuella "sidor", märker ingen något misstänkt under lång tid. Exakt fram till det ögonblick då UI/UX i kampen för bekvämlighet bestämmer sig för att göra om gränssnittet till "ändlös rullning" - det vill säga alla registerposter ritas i en enda lista som användaren kan scrolla upp och ner.

Och så, under nästa test, fångas du dubblering av register i registret. Varför, eftersom tabellen har ett normalt index (ts), som din fråga bygger på?

Exakt för att du inte tog hänsyn till det ts är inte en unik nyckel i denna tabell. Faktiskt, och dess värden är inte unika, som vilken "tid" som helst i verkliga förhållanden - därför "hoppar" samma post i två angränsande frågor lätt från sida till sida på grund av en annan slutlig ordning inom ramen för att sortera samma nyckelvärde.

Det finns faktiskt också ett andra problem gömt här, som är mycket svårare att lägga märke till - vissa poster kommer inte att visas alls! När allt kommer omkring tog "duplicerade" poster någon annans plats. En detaljerad förklaring med vackra bilder kan hittas läs här.

Utökar indexet

En listig utvecklare förstår att indexnyckeln måste göras unik, och det enklaste sättet är att utöka den med ett uppenbart unikt fält, vilket PK är perfekt för:

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

Och begäran muterar:

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

#2. Byt till "markörer"

En tid senare kommer en DBA till dig och är "nöjd" att dina önskemål de laddar servern som fan med sina OFFSET-regler, och i allmänhet är det dags att byta till navigering från det senaste värdet som visas. Din fråga muterar igen:

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

Du andades lättad tills den kom...

#3. Rengöringsindex

För en dag läste din DBA artikel om att hitta ineffektiva index och insåg det "inte den senaste" tidsstämpeln är inte bra. Och jag kom till dig igen - nu med tanken att det indexet ändå skulle bli tillbaka till (ts DESC).

Men vad ska man göra med det initiala problemet med att "hoppa" poster mellan sidor?.. Och allt är enkelt - du måste välja block med ett ofixerat antal poster!

I allmänhet, vem förbjuder oss att inte läsa "exakt 26", men "inte mindre än 26"? Till exempel så att det i nästa block finns register med uppenbart olika betydelser ts - då blir det inga problem med att "hoppa" poster mellan blocken!

Så här uppnår du detta:

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;

Vad händer här?

  1. Vi steg 25 registrerar "nedåt" och får värdet för "gränsen". ts.
  2. Om det inte redan finns något där, ersätt NULL-värdet med -infinity.
  3. Vi subtraherar hela segmentet av värden mellan det mottagna värdet ts och $1-parametern som skickades från gränssnittet (det tidigare "sista" renderade värdet).
  4. Om ett block returneras med mindre än 26 poster är det det sista.

Eller samma bild:
PostgreSQL-antimönster: Navigera i registret

För nu har vi det provet har ingen specifik "början", då hindrar ingenting oss från att "expandera" denna begäran i motsatt riktning och implementera dynamisk laddning av datablock från "referenspunkten" i båda riktningarna - både nedåt och uppåt.

anmärkning

  1. Ja, i det här fallet kommer vi åt indexet två gånger, men allt är "rent av index". Därför kommer en underfråga endast att resultera i till en ytterligare Index Endast Scan.
  2. Det är ganska uppenbart att denna teknik bara kan användas när man har värderingar ts kan bara passera av en slump, och det finns inte många av dem. Om ditt typiska fall är "en miljon poster vid 00:00:00.000", bör du inte göra detta. Jag menar, du bör inte tillåta ett sådant fall att hända. Men om detta händer, använd alternativet med ett utökat index.

Källa: will.com

Lägg en kommentar