PostgreSQL-i antimustrid: lugu nimepõhise otsingu iteratiivsest täpsustamisest või "edasi-tagasi optimeerimisest"

Tuhanded juhid müügiesindustest üle kogu riigi meie CRM-süsteem kümneid tuhandeid kontakte päevas — potentsiaalsete või olemasolevate klientidega suhtlemise faktid. Ja selleks tuleb esmalt leida klient ja soovitavalt väga kiiresti. Ja see juhtub kõige sagedamini nime järgi.

Seetõttu pole üllatav, et analüüsides taas kord ühes enimkoormatud andmebaasis - meie oma VLSI ettevõtte konto, leidsin "ülaosas" taotleda "kiirotsingut" nime järgi organisatsioonikaartide jaoks.

Lisaks selgus edasine uurimine huvitava näite esmalt optimeerimine ja seejärel jõudluse halvenemine taotlus koos selle järjestikuse viimistlemisega mitme meeskonna poolt, millest igaüks tegutses ainult parimate kavatsustega.

0: mida kasutaja tahtis?

[KDPV siit]

Mida kasutaja tavaliselt mõtleb, kui ta räägib "kiirest" nimeotsingust? Peaaegu kunagi ei osutu see alamstringi sarnaseks "ausaks" otsimiseks ... LIKE '%роза%' - sest siis ei sisalda tulemus mitte ainult 'Розалия' и 'Магазин Роза'kuid 'Гроза' ja isegi 'Дом Деда Мороза'.

Kasutaja eeldab igapäevasel tasandil, et te talle pakute otsi sõna alguse järgi pealkirjas ja muutke see asjakohasemaks hakkab peale sisenes. Ja sa teed seda peaaegu koheselt - ridadevahelise sisendi jaoks.

1: piirake ülesannet

Ja veelgi enam, inimene ei sisene konkreetselt 'роз магаз', nii et peate otsima iga sõna eesliite järgi. Ei, kasutajal on palju lihtsam vastata kiirele vihjele viimase sõna kohta, kui eelnevaid sihipäraselt "alamäärata" – vaadake, kuidas iga otsingumootor sellega hakkama saab.

üldisemalt õigesti nõuete sõnastamine probleemile on üle poole lahendusest. Mõnikord hoolikas kasutusjuhtumite analüüs võib tulemust oluliselt mõjutada.

Mida teeb abstraktne arendaja?

1.0: väline otsingumootor

Oh, otsimine on keeruline, ma ei taha üldse midagi teha - anname selle devopsile! Laske neil juurutada andmebaasiväline otsingumootor: Sphinx, ElasticSearch,...

Töötav variant, kuigi sünkroniseerimise ja muudatuste kiiruse poolest töömahukas. Kuid mitte meie puhul, kuna otsing tehakse iga kliendi jaoks ainult tema kontoandmete raames. Ja andmed on üsna suure varieeruvusega - ja kui juht on nüüd kaardi sisestanud 'Магазин Роза', siis 5-10 sekundi pärast võib talle juba meelde tulla, et ta unustas oma meili sinna märkida ja soovib seda leida ja parandada.

Seega – lähme otsi "otse andmebaasist". Õnneks võimaldab PostgreSQL meil seda teha ja mitte ainult ühte võimalust – me vaatame neid.

1.1: "aus" alamstring

Me klammerdume sõna "alamsringi" külge. Kuid indeksi otsimiseks alamstringi (ja isegi regulaaravaldiste järgi!) jaoks on suurepärane moodul pg_trgm! Alles siis on vaja õigesti sorteerida.

Proovime mudeli lihtsustamiseks võtta järgmise plaadi:

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

Laadime sinna üles 7.8 miljonit kirjet tegelikest organisatsioonidest ja indekseerime need:

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

Otsime ridadevahelise otsingu jaoks 10 esimest kirjet:

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

[vaadake saidil magyarázat.tensor.ru]

No see on... 26 ms, 31 MB loe andmeid ja rohkem kui 1.7 10 filtreeritud kirjet – XNUMX otsitud kirje jaoks. Üldkulud on liiga suured, kas pole midagi tõhusamat?

1.2: otsida teksti järgi? See on FTS!

Tõepoolest, PostgreSQL pakub väga võimsat täisteksti otsingumootor (Täistekstiotsing), sealhulgas eesliiteotsingu võimalus. Suurepärane valik, te ei pea isegi laiendusi installima! Proovime:

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;

[vaadake saidil magyarázat.tensor.ru]

Siin aitas meid pisut päringu täitmise paralleelsus, lühendades aega poole võrra 11 ms. Ja lugema pidime 1.5 korda vähem – kokku 20MB. Kuid siin, mida vähem, seda parem, sest mida suurem on maht, mida loeme, seda suurem on vahemälu vahelejäämise tõenäosus ja iga kettalt loetud andmete lisaleht on päringu potentsiaalseks "piduriks".

1.3: ikka MEELDIB?

Eelmine palve on kõigile hea, aga ainult siis, kui sa tõmbad seda päevas sada tuhat korda, siis see tuleb 2TB lugeda andmeid. Parimal juhul mälust, aga kui ei vea, siis kettalt. Nii et proovime seda väiksemaks muuta.

Pidagem meeles, mida kasutaja näha tahab esimene "mis algab ...". Nii et see on kõige puhtamal kujul eesliite otsing abiga text_pattern_ops! Ja ainult siis, kui meil ei ole piisavalt kuni 10 kirjet, mida otsime, peame nende lugemise lõpetama FTS-otsingu abil:

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

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

[vaadake saidil magyarázat.tensor.ru]

Suurepärane jõudlus - kokku 0.05 ms ja veidi rohkem kui 100 KB loe! Ainult meie unustasime sorteeri nime järgiet kasutaja tulemustes ei kaoks:

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

[vaadake saidil magyarázat.tensor.ru]

Oh, miski pole enam nii ilus - näib, et indeks on, aga sorteerimine lendab sellest mööda... See on muidugi juba kordades efektiivsem kui eelmine variant, aga...

1.4: "lõpeta failiga"

Kuid on olemas register, mis võimaldab teil otsida vahemiku järgi ja siiski kasutada sortimist tavapäraselt - tavaline btree!

CREATE INDEX ON firms(lower(name));

Ainult selle taotlus tuleb "käsitsi koguda":

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

[vaadake saidil magyarázat.tensor.ru]

Suurepärane - sorteerimine toimib ja ressursikulu jääb "mikroskoopiliseks", tuhandeid kordi tõhusam kui "puhas" FTS! Jääb üle vaid see üheks päringuks koostada:

(
  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;

Pange tähele, et käivitatakse teine ​​alampäring ainult siis, kui esimene tuli oodatust vähem tagasi viimane LIMIT ridade arv. Ma räägin sellest päringu optimeerimise meetodist juba varem kirjutanud.

Nii et jah, meil on nüüd laual nii btree kui gin, kuid statistiliselt selgub, et vähem kui 10% päringutest jõuab teise ploki täitmiseni. See tähendab, et selliste tüüpiliste piirangutega, mis olid ülesande jaoks ette teada, suutsime vähendada serveriressursside kogutarbimist peaaegu tuhat korda!

1.5*: saame hakkama ka ilma failita

Ülal LIKE Meil hoiti ära vale sorteerimise kasutamine. Kuid selle saab "õigele teele seada", määrates operaatori USING:

Vaikimisi eeldatakse ASC. Lisaks saate klauslis määrata konkreetse sortimisoperaatori nime USING. Sorteerimisoperaator peab kuuluma mõnesse B-puu operaatorite perekonda, mis on väiksem või suurem kui. ASC tavaliselt samaväärne USING < и DESC tavaliselt samaväärne USING >.

Meie puhul on "vähem". ~<~:

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

[vaadake saidil magyarázat.tensor.ru]

2: kuidas taotlused hapuks muutuvad

Nüüd jätame oma taotluse kuueks kuuks või aastaks "hauduma" ja oleme üllatunud, kui leiame selle taas "ülaosas" koos mälu igapäevase "pumpamise" näitajatega (puhvrid jagatud tabamust) sisse 5.5TB – ehk isegi rohkem, kui see algselt oli.

Ei, loomulikult on meie äri kasvanud ja töökoormus kasvanud, aga mitte sama palju! See tähendab, et siin on midagi kahtlast – mõtleme selle välja.

2.1: otsingu sünd

Mingil hetkel soovis teine ​​arendusmeeskond võimaldada kiirelt alamindeksiotsingult samade, kuid laiendatud tulemustega registrisse “hüpata”. Mis on register ilma lehel navigeerimiseta? Paneme asja tuksi!

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

Nüüd oli võimalik näidata otsingutulemuste registrit laadimisega lehekülgede kaupa, ilma et see oleks arendaja jaoks stressi tekitanud.

Muidugi, tegelikult iga järgneva andmete lehekülje kohta loetakse järjest rohkem (kõik eelmisest korrast, mille me ära viskame, pluss vajalik “saba”) - see tähendab, et see on selge antimuster. Kuid õigem oleks alustada otsingut järgmisel iteratsioonil liidesesse salvestatud võtmest, kuid sellest mõni teine ​​kord.

2.2: Ma tahan midagi eksootilist

Mingil hetkel soovis arendaja mitmekesistada saadud valimit andmetega teisest tabelist, mille kohta kogu eelmine päring saadeti CTE-le:

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

Ja isegi nii pole see halb, kuna alampäringut hinnatakse ainult 10 tagastatud kirje puhul, kui mitte ...

2.3: DISTINCT on mõttetu ja halastamatu

Kuskil sellise evolutsiooni protsessis alates 2. alampäringust kadus NOT LIKE seisund. On selge, et pärast seda UNION ALL hakkas tagasi tulema mõned sissekanded kaks korda - esmalt leiti rea algusest ja seejärel uuesti - selle rea esimese sõna algusest. Limiidi korral võivad kõik 2. alampäringu kirjed kattuda esimese alampäringu kirjetega.

Mida teeb arendaja põhjuse otsimise asemel?.. Pole küsimust!

• kahekordne suurus originaalnäidised
• rakenda DISTINCTet saada igast reast ainult üks kord

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>;

See tähendab, et on selge, et tulemus on lõpuks täpselt sama, kuid võimalus "lennata" teise CTE alampäringusse on muutunud palju suuremaks ja isegi ilma selleta selgelt loetavam.

Kuid see pole kõige kurvem. Kuna arendaja palus valida DISTINCT mitte konkreetsetele, vaid kõikidele väljadele korraga kirjed, siis lisati sinna automaatselt alampäringu väli – alampäringu tulemus. Nüüd täitma DISTINCT, pidi andmebaas juba käivitama mitte 10 alampäringut, vaid kõik <2 * N> + 10!

2.4: koostöö ennekõike!

Nii et arendajad elasid edasi - nad ei viitsinud, sest kasutajal polnud selgelt piisavalt kannatust registri oluliste N väärtuste jaoks "kohandamiseks" ja iga järgneva "lehe" vastuvõtmise krooniline aeglustumine.

Kuni nende juurde tulid arendajad teisest osakonnast ja tahtsid sellist mugavat meetodit kasutada iteratiivseks otsinguks - see tähendab, et võtame mõnest proovist tüki, filtreerime selle lisatingimuste järgi, joonistame tulemuse, seejärel järgmise tüki (mis meie puhul saavutatakse N suurendamisega) ja nii edasi, kuni täidame ekraani.

Üldiselt püütud isendis N saavutas väärtused peaaegu 17K, ja kõigest ühe päevaga täideti ahelas vähemalt 4K selliseid taotlusi. Viimast neist skaneeris julgelt 1 GB mälu iteratsiooni kohta...

Kogusummas

Allikas: www.habr.com