PostgreSQL Antipatterns: A Tale of Iterative Refinement of Search by Name, eða „Bjartsýni fram og til baka“

Þúsundir stjórnenda frá söluskrifstofum um allt land meta CRM kerfið okkar tugþúsundir tengiliða daglega — staðreyndir um samskipti við hugsanlega eða núverandi viðskiptavini. Og fyrir þetta verður þú fyrst að finna viðskiptavin, og helst mjög fljótt. Og þetta gerist oftast með nafni.

Þess vegna kemur það ekki á óvart að enn og aftur að greina „þungar“ fyrirspurnir á einum af hlaðnasta gagnagrunninum - okkar eigin Fyrirtækjareikningur VLSI, ég fann "í toppnum" beiðni um „fljóta“ leit eftir nafni fyrir skipulagskort.

Ennfremur leiddi frekari rannsókn í ljós áhugavert dæmi fyrst hagræðingu og síðan skerðingu á frammistöðu beiðni með röð betrumbótum af nokkrum liðum, sem hvert um sig virkaði eingöngu af bestu ásetningi.

0: hvað vildi notandinn?

[KDPV þess vegna]

Hvað á notandi venjulega við þegar þeir tala um „fljóta“ leit eftir nafni? Það reynist næstum aldrei vera „heiðarleg“ leit að undirstreng eins og ... LIKE '%роза%' - því þá nær niðurstaðan ekki aðeins 'Розалия' и 'Магазин Роза'En 'Гроза' og jafnvel 'Дом Деда Мороза'.

Notandinn gerir ráð fyrir því á daglegu stigi að þú útvegar honum leita eftir upphafi orðs í titlinum og gera það viðeigandi að hefst kl inn. Og þú munt gera það næstum samstundis - fyrir millilínulegt inntak.

1: takmarkaðu verkefnið

Og enn frekar, manneskja fer ekki sérstaklega inn 'роз магаз', þannig að þú þarft að leita að hverju orði eftir forskeyti. Nei, það er miklu auðveldara fyrir notanda að bregðast við skjótri vísbendingu fyrir síðasta orðið en að „vantilgreina“ þau fyrri markvisst - sjáðu hvernig hvaða leitarvél sem er meðhöndlar þetta.

Almennt, rétt að móta kröfurnar fyrir vandamálið er meira en hálf lausnin. Stundum varkár notkun tilvikagreiningar getur haft veruleg áhrif á niðurstöðuna.

Hvað gerir abstrakt verktaki?

1.0: ytri leitarvél

Ó, leit er erfið, ég vil alls ekki gera neitt - við skulum gefa það til devops! Leyfðu þeim að setja upp leitarvél utan gagnagrunnsins: Sphinx, ElasticSearch,...

Vinnukostur, þó vinnufrekur hvað varðar samstillingu og hraða breytinga. En ekki í okkar tilviki, þar sem leit fer fram fyrir hvern viðskiptavin aðeins innan ramma reikningsgagna hans. Og gögnin hafa nokkuð mikla breytileika - og ef stjórnandinn hefur nú slegið inn kortið 'Магазин Роза', svo eftir 5-10 sekúndur gæti hann þegar munað að hann gleymdi að gefa upp tölvupóstinn sinn þar og vill finna hann og leiðrétta hann.

Þess vegna - við skulum leitaðu „beint í gagnagrunninum“. Sem betur fer gerir PostgreSQL okkur kleift að gera þetta, en ekki bara einn valkost - við munum skoða þá.

1.1: "heiðarlegur" undirstrengur

Við höldum okkur við orðið „undirstrengur“. En fyrir vísitöluleit eftir undirstreng (og jafnvel með reglulegum segðum!) er frábært mát pg_trgm! Aðeins þá verður nauðsynlegt að flokka rétt.

Við skulum reyna að taka eftirfarandi plötu til að einfalda líkanið:

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

Við hleðum upp 7.8 milljón skrám yfir raunverulegar stofnanir þangað og skráum þær:

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

Við skulum leita að fyrstu 10 færslunum fyrir millilínuleit:

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

[horfðu á explain.tensor.ru]

Jæja, það er... 26ms, 31MB lesið gögn og meira en 1.7K síaðar færslur - fyrir 10 leitað. Yfirkostnaðurinn er of hár, er ekki eitthvað hagkvæmara?

1.2: leita með texta? Það er FTS!

Reyndar, PostgreSQL veitir mjög öflugt leitarvél í fullri texta (Full Text Search), þar á meðal getu til að forskeyta leit. Frábær kostur, þú þarft ekki einu sinni að setja upp viðbætur! Reynum:

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;

[horfðu á explain.tensor.ru]

Hér hjálpaði samhliða framkvæmd fyrirspurna okkur aðeins og stytti tímann um helming 11 ms. Og við þurftum að lesa 1.5 sinnum minna - alls 20MB. En hér, því minna, því betra, vegna þess að því stærra sem við lesum, því meiri líkur eru á því að missa skyndiminni, og hver auka blaðsíða af gögnum sem lesin er af disknum eru hugsanlegar „bremsur“ fyrir beiðnina.

1.3: LIKE enn?

Fyrri beiðnin er góð fyrir alla, en aðeins ef þú dregur hana hundrað þúsund sinnum á dag kemur hún 2TB lesa gögn. Í besta falli, eftir minni, en ef þú ert óheppinn, þá af diski. Svo skulum við reyna að gera það minna.

Við skulum muna hvað notandinn vill sjá fyrst „sem byrja á...“. Þannig að þetta er í sinni hreinustu mynd forskeyti leit með hjálpinni text_pattern_ops! Og aðeins ef við „eigum ekki nóg“ allt að 10 færslur sem við erum að leita að, þá verðum við að klára að lesa þær með FTS leit:

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

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

[horfðu á explain.tensor.ru]

Frábær árangur - alls 0.05ms og aðeins meira en 100KB lestu! Aðeins við gleymdum flokka eftir nafnisvo að notandinn villist ekki í niðurstöðunum:

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

[horfðu á explain.tensor.ru]

Ó, eitthvað er ekki svo fallegt lengur - það virðist sem það sé vísitala, en flokkunin flýgur framhjá henni... Hún er auðvitað þegar margfalt áhrifaríkari en fyrri kosturinn, en ...

1.4: "klára með skrá"

En það er vísir sem gerir þér kleift að leita eftir sviðum og samt nota flokkun venjulega - venjulegur btree!

CREATE INDEX ON firms(lower(name));

Aðeins beiðni um það verður að "safna handvirkt":

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

[horfðu á explain.tensor.ru]

Frábært - flokkunin virkar og auðlindanotkun er enn „smásjáleg“ þúsund sinnum áhrifaríkari en „hreint“ FTS! Allt sem er eftir er að setja það saman í eina beiðni:

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

Athugaðu að önnur undirfyrirspurnin er keyrð aðeins ef sá fyrsti skilaði minna en búist var við síðasta LIMIT fjölda lína. Ég er að tala um þessa aðferð við fínstillingu fyrirspurna þegar skrifað áður.

Svo já, nú erum við með bæði btree og gin á borðinu, en tölfræðilega kemur það í ljós minna en 10% beiðna ná framkvæmd seinni blokkarinnar. Það er, með svona dæmigerðum takmörkunum sem vitað er um fyrirfram fyrir verkefnið, gátum við minnkað heildarnotkun netþjónaauðlinda um næstum þúsund sinnum!

1.5*: við getum verið án skráar

Hærra LIKE Það var komið í veg fyrir að við notum ranga flokkun. En það er hægt að „setja á rétta leið“ með því að tilgreina USING símafyrirtækið:

Sjálfgefið er gert ráð fyrir því ASC. Að auki geturðu tilgreint nafn tiltekins flokkunarrekstraraðila í ákvæði USING. Flokkunarstjórinn verður að vera meðlimur minni en eða stærri en í einhverri fjölskyldu B-tré rekstraraðila. ASC venjulega jafngild USING < и DESC venjulega jafngild USING >.

Í okkar tilviki er „minna“ ~<~:

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

[horfðu á explain.tensor.ru]

2: hvernig beiðnir verða súr

Nú látum við beiðni okkar um að „sjóða“ í sex mánuði eða eitt ár og við erum hissa á að finna það aftur „á toppnum“ með vísbendingum um heildar daglega „dælingu“ minnis (biðminni deilt hitt) í 5.5TB - það er jafnvel meira en það var upphaflega.

Nei, auðvitað hefur fyrirtækið okkar vaxið og vinnuálagið aukist, en ekki jafnmikið! Þetta þýðir að eitthvað er fiskilegt hér - við skulum reikna það út.

2.1: fæðing síðuboðs

Á einhverjum tímapunkti vildi annað þróunarteymi gera það mögulegt að „stökkva“ úr skjótri áskriftarleit yfir í skrárinn með sömu en stækkuðu niðurstöðum. Hvað er skrásetning án síðuleiðsögu? Við skulum klúðra því!

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

Nú var hægt að sýna skrá yfir leitarniðurstöður með „síðu-fyrir-síðu“ hleðslu án nokkurs álags fyrir þróunaraðilann.

Auðvitað, í raun, fyrir hverja síðari gagnasíðu er meira og meira lesið (allt frá fyrri tíma, sem við munum henda, auk nauðsynlegs „hala“) - það er, þetta er skýrt andmynstur. En réttara væri að hefja leitina við næstu endurtekningu frá lyklinum sem geymdur er í viðmótinu, en um það í annað sinn.

2.2: Mig langar í eitthvað framandi

Á einhverjum tímapunkti vildi verktaki auka fjölbreytni í úrtakinu sem myndast með gögnum frá öðru borði, sem öll fyrri beiðnin um var send til CTE:

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

Og jafnvel svo er það ekki slæmt, þar sem undirfyrirspurnin er aðeins metin fyrir 10 skilaðar færslur, ef ekki ...

2.3: DISTINCT er tilgangslaust og miskunnarlaust

Einhvers staðar í ferli slíkrar þróunar frá 2. undirfyrirspurninni týndist NOT LIKE ástand. Það er ljóst að eftir þetta UNION ALL byrjaði að snúa aftur sumar færslur tvisvar - fyrst að finna í upphafi línunnar, og svo aftur - í upphafi fyrsta orðs þessarar línu. Í takmörkunum gætu allar færslur 2. undirfyrirspurnar passa við færslur þeirrar fyrstu.

Hvað gerir verktaki í stað þess að leita að orsökinni?.. Engin spurning!

• tvöfalda stærð frumsýni
• beita DISTINCTað fá aðeins stök tilvik af hverri línu

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

Það er, það er ljóst að niðurstaðan, á endanum, er nákvæmlega sú sama, en líkurnar á að „fljúga“ inn í 2. CTE undirfyrirspurnina eru orðnar miklu meiri, og jafnvel án þessa, greinilega læsilegri.

En þetta er ekki það sorglegasta. Þar sem verktaki bað um að velja DISTINCT ekki fyrir sérstakar, heldur fyrir alla svið í einu færslur, þá var undirfyrirspurnarreiturinn - niðurstaða undirfyrirspurnarinnar - sjálfkrafa innifalin þar. Nú, að framkvæma DISTINCT, gagnagrunnurinn þurfti að keyra þegar ekki 10 undirfyrirspurnir, heldur allar <2 * N> + 10!

2.4: Samvinna umfram allt!

Svo, þróunaraðilarnir lifðu áfram - þeir nenntu því ekki, vegna þess að notandinn hafði greinilega ekki næga þolinmæði til að „aðlaga“ skrárinn að verulegum N gildum með langvarandi hægagangi á móttöku hverrar „síðu“ í kjölfarið.

Þangað til verktaki úr annarri deild komu til þeirra og vildu nota svona þægilega aðferð fyrir endurtekna leit - það er að segja, við tökum stykki úr einhverju sýni, síum það eftir viðbótarskilyrðum, teiknum niðurstöðuna, síðan næsta stykki (sem í okkar tilviki næst með því að auka N) og svo framvegis þar til við fyllum skjáinn.

Almennt, í veiddu eintakinu N náði næstum 17K gildi, og á aðeins einum degi voru að minnsta kosti 4K af slíkum beiðnum framkvæmdar „meðfram keðjunni“. Síðustu þeirra voru djarflega skannaðar af 1GB af minni í hverri endurtekningu...

Alls

Heimild: www.habr.com