ProHoster > Blogi > antaminen > PostgreSQL-antipatterns: Rekisterissä liikkuminen

PostgreSQL-antipatterns: Rekisterissä liikkuminen

Nykyään SQL:ssä ei ole monimutkaisia ​​tapauksia ja kehittyneitä algoritmeja. Kaikki on hyvin yksinkertaista kapteeni Ilmeisen tasolla - tehdään se tapahtumarekisterin katselu ajan mukaan lajiteltuna.

Eli tietokannassa on merkki events, ja hänellä on kenttä ts - tarkalleen milloin haluamme näyttää nämä tietueet järjestyksessä:

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

CREATE INDEX ON events(ts DESC);

On selvää, että meillä ei ole siellä tusinaa levyä, joten tarvitsemme jonkinlaisen sivun navigointi.

#0. "Olen äitini pogromisti"

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

Se ei ole läheskään vitsi - se on harvinainen, mutta luonnosta löytyy. Joskus ORM:n kanssa työskentelyn jälkeen voi olla vaikeaa siirtyä "suoraan" työhön SQL:n kanssa.

Mutta siirrytään yleisempiin ja vähemmän ilmeisiin ongelmiin.

#1. OFFSET

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

Mistä numero 26 tuli? Tämä on likimääräinen merkintöjen määrä yhden näytön täyttämiseen. Tarkemmin sanottuna 25 näytettävää tietuetta plus 1 merkkinä siitä, että näytteessä on ainakin jotain muuta ja on järkevää jatkaa eteenpäin.

Tätä arvoa ei tietenkään voida "ommella" pyynnön runkoon, vaan se voidaan siirtää parametrin kautta. Mutta tässä tapauksessa PostgreSQL-aikataulu ei voi luottaa siihen, että tietueita pitäisi olla suhteellisen vähän - ja valitsee helposti tehottoman suunnitelman.

Ja vaikka sovellusliittymässä rekisterin katselu toteutetaan visuaalisten "sivujen" välillä vaihtamisena, kukaan ei huomaa mitään epäilyttävää pitkään aikaan. Täsmälleen siihen hetkeen asti, jolloin UI/UX päättää mukavuustaistelussa muuttaa käyttöliittymän "loputtomaan vieritykseen" - eli kaikki rekisterimerkinnät piirretään yhteen luetteloon, jota käyttäjä voi vierittää ylös ja alas.

Ja niin seuraavan testin aikana jäät kiinni tietueiden kopiointi rekisterissä. Miksi, koska taulukossa on normaali indeksi (ts), johon kyselysi perustuu?

Juuri siksi, että et ottanut sitä huomioon ts ei ole ainutlaatuinen avain tässä taulukossa. Itse asiassa ja sen arvot eivät ole ainutlaatuisia, kuten mikä tahansa "aika" todellisissa olosuhteissa - siksi sama tietue kahdessa vierekkäisessä kyselyssä "hyppää" helposti sivulta sivulle erilaisen lopullisen järjestyksen vuoksi saman avainarvon lajittelun puitteissa.

Itse asiassa tässä on piilotettu myös toinen ongelma, jota on paljon vaikeampi havaita - joitain merkintöjä ei näytetä ollenkaan! Loppujen lopuksi "kaksoiskappaleet" ottivat jonkun toisen paikan. Yksityiskohtainen selitys kauniilla kuvilla löytyy lue täältä.

Indeksin laajentaminen

Ovela kehittäjä ymmärtää, että indeksiavaimesta on tehtävä ainutlaatuinen, ja helpoin tapa on laajentaa sitä ilmeisen ainutlaatuisella kentällä, johon PK sopii täydellisesti:

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

Ja pyyntö muuttuu:

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

#2. Vaihda "kursoriin"

Jonkin ajan kuluttua DBA tulee luoksesi ja on "tyytyväisenä" pyyntöösi he lataavat palvelinta kuin helvettiä OFFSET-säännöillään, ja yleensä, on aika vaihtaa navigointi viimeisestä näytetystä arvosta. Kyselysi muuttuu jälleen:

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

Hengitit helpotuksesta, kunnes se tuli...

#3. Puhdistusindeksit

Koska eräänä päivänä DBA-lukemasi artikkeli tehottomien indeksien löytämisestä ja tajusi sen "ei uusin" aikaleima ei ole hyvä. Ja tulin taas luoksesi - nyt ajatuksella, että indeksin pitäisi silti muuttua takaisin (ts DESC).

Mutta mitä tehdä alkuperäisen ongelman kanssa "hyppäämällä" tietueita sivujen välillä?.. Ja kaikki on yksinkertaista - sinun on valittava lohkot, joissa on kiinteä määrä tietueita!

Yleisesti, kuka kieltää meitä lukemasta "täsmälleen 26", vaan "vähintään 26"? Esimerkiksi niin, että seuraavassa lohkossa on tietueita, joilla on selvästi eri merkitys ts - silloin ei tule olemaan ongelmia tietueiden "hyppäämisessä" lohkojen välillä!

Näin saavutat tämän:

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;

Mitä täällä tapahtuu?

  1. Askelemme 25 tietuetta "alas" ja saamme "raja-arvon". ts.
  2. Jos siellä ei ole jo mitään, korvaa NULL-arvo arvolla -infinity.
  3. Vähennämme koko arvojen segmentin vastaanotetun arvon väliltä ts ja käyttöliittymästä välitetty parametri $1 (edellinen "viimeinen" renderöity arvo).
  4. Jos lohko palautetaan alle 26 tietueella, se on viimeinen.

Tai sama kuva:
PostgreSQL-antipatterns: Rekisterissä liikkuminen

Koska nyt meillä on näytteellä ei ole mitään erityistä "alkua", niin mikään ei estä meitä "laajentamasta" tätä pyyntöä vastakkaiseen suuntaan ja toteuttamasta tietolohkojen dynaamista lataamista "viitepisteestä" molempiin suuntiin - sekä alas että ylös.

huomautus

  1. Kyllä, tässä tapauksessa käytämme hakemistoa kahdesti, mutta kaikki tapahtuu "puhtaasti indeksillä". Siksi alikysely johtaa vain yhteen ylimääräiseen vain hakemistoskannaukseen.
  2. On aivan selvää, että tätä tekniikkaa voidaan käyttää vain, kun sinulla on arvot ts voi ylittää vain sattumalta, ja niitä ei ole montaa. Jos tyypillinen tapauksesi on "miljoona tietuetta klo 00:00:00.000", sinun ei pitäisi tehdä tätä. Tarkoitan, sinun ei pitäisi antaa tällaisen tapauksen tapahtua. Mutta jos näin tapahtuu, käytä vaihtoehtoa laajennetulla indeksillä.

Lähde: will.com

Lisää kommentti