PostgreSQL-antipatterns: Tarina nimihaun iteratiivisesta tarkentamisesta eli "edestakaisin optimoinnista"

Tuhansia johtajia myyntikonttoreista eri puolilla maata CRM-järjestelmämme kymmeniä tuhansia yhteydenottoja päivittäin — tosiasiat kommunikaatiosta mahdollisten tai olemassa olevien asiakkaiden kanssa. Ja tätä varten sinun on ensin löydettävä asiakas, ja mieluiten hyvin nopeasti. Ja tämä tapahtuu useimmiten nimellä.

Siksi ei ole yllättävää, että kun taas kerran analysoimme "raskaita" kyselyitä yhdellä ladatuimmista tietokannoista - omassamme VLSI-yritystili, löysin "ylhäältä" pyytää "nopeaa" hakua nimellä organisaatiokortteja varten.

Lisäksi lisätutkimukset paljastivat mielenkiintoisen esimerkin ensin optimointi ja sitten suorituskyvyn heikkeneminen pyyntöä sen peräkkäisellä tarkennuksella useiden ryhmien toimesta, joista jokainen toimi yksinomaan parhaiden aikomusten mukaisesti.

0: mitä käyttäjä halusi?

[KDPV siten]

Mitä käyttäjä yleensä tarkoittaa puhuessaan "pikasta" nimenhausta? Se ei tuskin koskaan ole "rehellistä" alimerkkijonon kaltaista hakua ... LIKE '%роза%' - koska silloin tulos ei sisällä vain 'Розалия' и 'Магазин Роза'Mutta 'Гроза' ja jopa 'Дом Деда Мороза'.

Käyttäjä olettaa jokapäiväisellä tasolla, että annat hänelle haku sanan alun perusteella otsikossa ja tee siitä osuvampi alkaa kanssa astui sisään. Ja sinä teet sen melkein heti - interlineaariseen tuloon.

1: rajaa tehtävää

Ja vielä enemmän, henkilö ei pääse erityisesti sisään 'роз магаз', joten sinun on etsittävä jokaista sanaa etuliitteellä. Ei, käyttäjän on paljon helpompaa vastata nopeaan vihjeeseen viimeisestä sanasta kuin tarkoituksellisesti "alimäärittää" aiempia vihjeitä - katso, kuinka mikä tahansa hakukone käsittelee tämän.

Yleensä oikein ongelman vaatimusten muotoileminen on yli puolet ratkaisusta. Joskus huolellinen käyttötapausanalyysi voi vaikuttaa merkittävästi tulokseen.

Mitä abstrakti kehittäjä tekee?

1.0: ulkoinen hakukone

Voi, haku on vaikeaa, en halua tehdä yhtään mitään - annetaan se devopsille! Anna heidän ottaa käyttöön tietokannan ulkopuolinen hakukone: Sphinx, ElasticSearch,...

Toimiva vaihtoehto, vaikkakin työvaltainen synkronoinnin ja muutosten nopeuden kannalta. Mutta ei meidän tapauksessamme, koska haku suoritetaan jokaiselle asiakkaalle vain hänen tilitietojensa puitteissa. Ja tiedoissa on melko suuri vaihtelu - ja jos johtaja on nyt syöttänyt kortin 'Магазин Роза', sitten 5-10 sekunnin kuluttua hän saattaa jo muistaa unohtaneensa merkitä sähköpostiosoitteensa ja haluta löytää sen ja korjata sen.

Siksi - mennään haku "suoraan tietokannasta". Onneksi PostgreSQL antaa meille mahdollisuuden tehdä tämä, eikä vain yksi vaihtoehto - katsomme niitä.

1.1: "rehellinen" alimerkkijono

Pidämme kiinni sanasta "alimerkkijono". Mutta indeksihakuun osamerkkijonon (ja jopa säännöllisten lausekkeiden!) perusteella on erinomainen moduuli pg_trgm! Vasta sitten on tarpeen lajitella oikein.

Yritetään ottaa seuraava levy mallin yksinkertaistamiseksi:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

Lataamme sinne 7.8 miljoonaa tietuetta todellisista organisaatioista ja indeksoimme ne:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

Etsitään 10 ensimmäistä tietuetta interlineaarista hakua varten:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[katso selittää.tensor.ru]

No sellaista... 26ms, 31MB lue tiedot ja yli 1.7 10 suodatettua tietuetta - XNUMX haetulle tietueelle. Yleiskustannukset ovat liian korkeat, eikö ole olemassa jotain tehokkaampaa?

1.2: haku tekstillä? Se on FTS!

Todellakin, PostgreSQL tarjoaa erittäin tehokkaan koko tekstin hakukone (Koko tekstihaku), mukaan lukien mahdollisuus etuliitehakuun. Erinomainen vaihtoehto, sinun ei tarvitse edes asentaa laajennuksia! Kokeillaan:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[katso selittää.tensor.ru]

Tässä kyselyn suorittamisen rinnakkaistaminen auttoi meitä hieman ja lyhensi aikaa puoleen 11 ms. Ja meidän piti lukea 1.5 kertaa vähemmän - yhteensä 20MB. Mutta tässä, mitä vähemmän, sen parempi, koska mitä suurempi määrä luemme, sitä suurempi mahdollisuus saada välimuisti puuttuu, ja jokainen levyltä luettu ylimääräinen datasivu on mahdollinen "jarru" pyynnölle.

1.3: Tykkäätkö edelleen?

Edellinen pyyntö on hyvä kaikille, mutta vain jos vedät sitä satatuhatta kertaa päivässä, se tulee 2TB lukea dataa. Parhaassa tapauksessa muistista, mutta jos olet epäonninen, niin levyltä. Joten yritetään tehdä siitä pienempi.

Muistetaan, mitä käyttäjä haluaa nähdä ensimmäinen "joka alkaa...". Tämä siis puhtaimmassa muodossaan etuliitehaku kautta text_pattern_ops! Ja vain, jos meillä "ei ole tarpeeksi" jopa 10 etsimämme tietuetta, meidän on luettava ne loppuun FTS-haun avulla:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[katso selittää.tensor.ru]

Erinomainen suorituskyky - yhteensä 0.05 ms ja hieman yli 100 kt lukea! Vain me unohdimme Lajittele nimen mukaanjotta käyttäjä ei eksy tuloksiin:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[katso selittää.tensor.ru]

Ai, mikään ei ole enää niin kaunista - indeksi näyttää olevan, mutta lajittelu lentää sen ohi... Se on tietysti jo monta kertaa tehokkaampi kuin edellinen vaihtoehto, mutta...

1.4: "lopeta tiedosto"

Mutta on hakemisto, jonka avulla voit etsiä alueen mukaan ja silti käyttää lajittelua normaalisti - tavallinen btree!

CREATE INDEX ON firms(lower(name));

Vain sitä koskeva pyyntö on "keräättävä manuaalisesti":

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[katso selittää.tensor.ru]

Erinomainen - lajittelu toimii ja resurssien kulutus pysyy "mikroskooppisena", tuhansia kertoja tehokkaampi kuin "puhdas" FTS! Jäljelle jää vain koota se yhdeksi pyynnöksi:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

Huomaa, että toinen alikysely suoritetaan vain jos ensimmäinen palasi odotettua vähemmän viimeinen LIMIT rivien määrä. Puhun tästä kyselyn optimointimenetelmästä kirjoitti jo aiemmin.

Joten kyllä, meillä on nyt sekä btree että gin pöydällä, mutta tilastollisesti käy ilmi, että alle 10 % pyynnöistä saavuttaa toisen lohkon suorituksen. Eli tällaisilla tyypillisillä rajoituksilla, jotka tiedettiin etukäteen tehtävälle, pystyimme vähentämään palvelinresurssien kokonaiskulutusta lähes tuhat kertaa!

1.5*: pärjäämme ilman tiedostoa

korkeampi LIKE Meitä estettiin käyttämästä väärää lajittelua. Mutta se voidaan "asettaa oikealle polulle" määrittämällä USING-operaattori:

Oletuksena se oletetaan ASC. Lisäksi voit määrittää lauseessa tietyn lajitteluoperaattorin nimen USING. Lajitteluoperaattorin on kuuluttava johonkin B-puun operaattoriperheeseen, joka on pienempi tai suurempi kuin. ASC yleensä vastaava USING < и DESC yleensä vastaava USING >.

Meidän tapauksessamme "vähemmän" on ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[katso selittää.tensor.ru]

2: kuinka pyynnöt muuttuvat happamaksi

Nyt jätämme pyyntömme "hautua" kuudeksi kuukaudeksi tai vuodeksi, ja olemme yllättyneitä nähdessämme sen jälleen "ylhäältä" muistin päivittäisen "pumppauksen" indikaattoreineen (puskurit jaettu osuma) sisään 5.5TB - eli jopa enemmän kuin alun perin.

Ei tietenkään, liiketoimintamme on kasvanut ja työmäärämme lisääntynyt, mutta ei samalla tavalla! Tämä tarkoittaa, että tässä on jotain hämärää - selvitetään se.

2.1: henkilöhakujen synty

Jossain vaiheessa toinen kehitystiimi halusi mahdollistaa "hyppäämisen" nopeasta alaindeksihausta rekisteriin samoilla, mutta laajennetuilla tuloksilla. Mikä on rekisteri ilman sivunavigointia? Pilkotaan se!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

Nyt oli mahdollista näyttää hakutulosrekisteri "sivu kerrallaan" latautuneena ilman, että kehittäjälle aiheutui stressiä.

Tietenkin itse asiassa jokaista seuraavaa sivua kohti luetaan enemmän ja enemmän (kaikki edellisestä ajasta, jonka hylkäämme, plus tarvittava "häntä") - eli tämä on selkeä vastakuvio. Mutta olisi oikeampaa aloittaa haku seuraavassa iteraatiossa käyttöliittymään tallennetusta avaimesta, mutta siitä toisella kerralla.

2.2: Haluan jotain eksoottista

Jossain vaiheessa kehittäjä halusi monipuolistaa tuloksena olevaa otosta tiedoilla toisesta taulukosta, josta koko edellinen pyyntö lähetettiin CTE:lle:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

Ja silti se ei ole huono, koska alikysely arvioidaan vain 10 palautetulle tietueelle, jos ei ...

2.3: DISTINCT on järjetön ja armoton

Jossain tällaisen kehityksen prosessissa toisesta alikyselystä menetetty NOT LIKE ehto. On selvää, että tämän jälkeen UNION ALL alkoi palata jotkut merkinnät kahdesti - löytyi ensin rivin alusta ja sitten uudelleen - tämän rivin ensimmäisen sanan alusta. Rajassa kaikki 2. alikyselyn tietueet voivat vastata ensimmäisen tietueita.

Mitä kehittäjä tekee sen sijaan, että etsiisi syytä?... Ei epäilystäkään!

• kaksinkertainen koko alkuperäiset näytteet
• soveltaa DISTINCTsaadaksesi vain yksittäiset esiintymät jokaisesta rivistä

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

Eli on selvää, että lopputulos on täsmälleen sama, mutta mahdollisuus "lentää" toiseen CTE-alikyselyyn on kasvanut paljon, ja jopa ilman tätä selvästi luettavampi.

Mutta tämä ei ole surullisin asia. Koska kehittäjä pyysi valitsemaan DISTINCT ei tietyille, vaan kaikille aloille kerralla tietueet, alikysely-kenttä – alikyselyn tulos – sisällytettiin siihen automaattisesti. Nyt toteuttamaan DISTINCT, tietokannan oli jo suoritettava ei 10 alikyselyä, vaan kaikki <2 * N> + 10!

2.4: yhteistyö ennen kaikkea!

Joten kehittäjät elivät eteenpäin - he eivät vaivautuneet, koska käyttäjällä ei selvästikään ollut tarpeeksi kärsivällisyyttä "säätää" rekisteriä merkittäviin N-arvoihin, jolloin jokaisen seuraavan "sivun" vastaanottaminen hidastui kroonisesti.

Kunnes kehittäjät toiselta osastolta tulivat heidän luokseen ja halusivat käyttää tällaista kätevää menetelmää iteratiiviseen hakuun - eli otamme palan jostakin näytteestä, suodatamme sen lisäehdoilla, piirrämme tuloksen, sitten seuraavan palan (joka meidän tapauksessamme saavutetaan lisäämällä N) ja niin edelleen, kunnes täytämme näytön.

Yleensä pyydetyssä näytteessä N saavutti lähes 17 XNUMX arvot, ja vain yhdessä päivässä vähintään 4 XNUMX tällaisia ​​pyyntöjä suoritettiin "ketjun varrella". Viimeiset heistä skannasivat rohkeasti 1 Gt muistia per iteraatio...

Yhteensä

Lähde: will.com