Antimodelet PostgreSQL: Një përrallë e përsosjes përsëritëse të kërkimit sipas emrit, ose "Optimizimi mbrapa dhe me radhë"

Mijëra menaxherë nga zyrat e shitjeve në të gjithë vendin regjistrojnë sistemin tonë CRM dhjetëra mijëra kontakte çdo ditë — faktet e komunikimit me klientë të mundshëm ose ekzistues. Dhe për këtë, së pari duhet të gjeni një klient, dhe mundësisht shumë shpejt. Dhe kjo ndodh më shpesh me emër.

Prandaj, nuk është për t'u habitur që, duke analizuar edhe një herë pyetje "të rënda" në një nga bazat e të dhënave më të ngarkuara - tonën Llogaria e korporatës VLSI, gjeta "në krye" kërkesa për një kërkim "të shpejtë" sipas emrit për kartat e organizimit.

Për më tepër, hetimi i mëtejshëm zbuloi një shembull interesant fillimisht optimizimi dhe më pas degradimi i performancës kërkesë me përsosjen e saj vijuese nga disa ekipe, secila prej të cilave veproi vetëm me qëllimet më të mira.

0: çfarë donte përdoruesi?

[KDPV prandaj]

Çfarë do të thotë zakonisht një përdorues kur flet për një kërkim "të shpejtë" me emër? Pothuajse kurrë nuk rezulton të jetë një kërkim "i sinqertë" për një nënvarg si ... LIKE '%роза%' - sepse atëherë rezultati përfshin jo vetëm 'Розалия' и 'Магазин Роза'Por 'Гроза' dhe madje edhe 'Дом Деда Мороза'.

Përdoruesi supozon në nivelin e përditshëm që ju do t'i siguroni atij kërko sipas fillimit të fjalës në titull dhe e bëjnë më të rëndësishme se fillon me hyri. Dhe ju do ta bëni atë pothuajse në çast - për hyrje ndërlineare.

1: kufizoni detyrën

Dhe aq më tepër, një person nuk do të hyjë në mënyrë specifike 'роз магаз', kështu që ju duhet të kërkoni për çdo fjalë me parashtesë. Jo, është shumë më e lehtë për një përdorues që t'i përgjigjet një sugjerimi të shpejtë për fjalën e fundit sesa të "nënspecifikuar" qëllimisht ato të mëparshme - shikoni se si çdo motor kërkimi e trajton këtë.

Në përgjithësi, korrekt formulimi i kërkesave për problemin është më shumë se gjysma e zgjidhjes. Ndonjëherë analiza e kujdesshme e rasteve mund të ndikojë ndjeshëm në rezultat.

Çfarë bën një zhvillues abstrakt?

1.0: motor kërkimi i jashtëm

Oh, kërkimi është i vështirë, nuk dua të bëj asgjë - le t'ia japim devops! Lërini ata të vendosin një motor kërkimi jashtë bazës së të dhënave: Sphinx, ElasticSearch,...

Një opsion pune, megjithëse kërkon punë intensive për sa i përket sinkronizimit dhe shpejtësisë së ndryshimeve. Por jo në rastin tonë, pasi kërkimi kryhet për secilin klient vetëm brenda kornizës së të dhënave të llogarisë së tij. Dhe të dhënat kanë një ndryshueshmëri mjaft të lartë - dhe nëse menaxheri tani ka hyrë në kartë 'Магазин Роза', pastaj pas 5-10 sekondash ai tashmë mund të kujtojë se ka harruar të tregojë emailin e tij atje dhe dëshiron ta gjejë dhe ta korrigjojë.

Prandaj - le kërkoni "direkt në bazën e të dhënave". Për fat të mirë, PostgreSQL na lejon ta bëjmë këtë, dhe jo vetëm një opsion - ne do t'i shikojmë ato.

1.1: nënvarg "i ndershëm".

Ne kapemi pas fjalës "nënstring". Por për kërkimin e indeksit me nënvarg (dhe madje edhe me shprehje të rregullta!) ekziston një e shkëlqyer moduli pg_trgm! Vetëm atëherë do të jetë e nevojshme të renditni saktë.

Le të përpiqemi të marrim pjatën e mëposhtme për të thjeshtuar modelin:

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

Ne ngarkojmë 7.8 milion regjistrime të organizatave reale atje dhe i indeksojmë ato:

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

Le të kërkojmë 10 rekordet e para për kërkimin ndërlinear:

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

[shikoni në shpjegojnë.tensor.ru]

Epo, kjo është ... 26 ms, 31 MB të dhëna të lexuara dhe më shumë se 1.7 mijë regjistrime të filtruara - për 10 të kërkuara. Kostot e përgjithshme janë shumë të larta, a nuk ka diçka më efikase?

1.2: kërkoni me tekst? Është FTS!

Në të vërtetë, PostgreSQL ofron një shumë të fuqishme motor kërkimi me tekst të plotë (Kërkimi i tekstit të plotë), duke përfshirë aftësinë për të prefiksuar kërkimin. Një opsion i shkëlqyeshëm, nuk keni nevojë as të instaloni shtesa! Le te perpiqemi:

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;

[shikoni në shpjegojnë.tensor.ru]

Këtu na ndihmoi pak paralelizimi i ekzekutimit të pyetjeve, duke e shkurtuar kohën në gjysmë 11 ms. Dhe ne duhej të lexonim 1.5 herë më pak - në total 20MB. Por këtu, sa më pak, aq më mirë, sepse sa më i madh të jetë vëllimi që lexojmë, aq më të larta janë shanset për të marrë një humbje të cache-it dhe çdo faqe shtesë e të dhënave e lexuar nga disku është një "frenim" i mundshëm për kërkesën.

1.3: ende LIKE?

Kërkesa e mëparshme është e mirë për të gjithë, por vetëm nëse e tërheq njëqind mijë herë në ditë, ajo do të vijë 2TB lexoni të dhënat. Në rastin më të mirë, nga kujtesa, por nëse nuk jeni me fat, atëherë nga disku. Pra, le të përpiqemi ta bëjmë atë më të vogël.

Le të kujtojmë atë që përdoruesi dëshiron të shohë e para "që fillon me...". Pra, kjo është në formën e saj më të pastër kërkimi i prefiksit me ndihmën e text_pattern_ops! Dhe vetëm nëse "nuk kemi mjaftueshëm" deri në 10 regjistrime që kërkojmë, atëherë do të duhet të përfundojmë leximin e tyre duke përdorur kërkimin FTS:

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

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

[shikoni në shpjegojnë.tensor.ru]

Performancë e shkëlqyer - total 0.05 ms dhe pak më shumë se 100 KB lexo! Vetëm ne harruam rendit sipas emritnë mënyrë që përdoruesi të mos humbasë në rezultatet:

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

[shikoni në shpjegojnë.tensor.ru]

Oh, diçka nuk është më aq e bukur - duket sikur ka një indeks, por renditja e kalon atë... Sigurisht, tashmë është shumë herë më efikas se opsioni i mëparshëm, por...

1.4: "përfundo me një skedar"

Por ekziston një indeks që ju lejon të kërkoni sipas diapazonit dhe ende të përdorni renditjen normalisht - bpeme e rregullt!

CREATE INDEX ON firms(lower(name));

Vetëm kërkesa për të do të duhet "të mblidhet manualisht":

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

[shikoni në shpjegojnë.tensor.ru]

E shkëlqyeshme - renditja funksionon, dhe konsumi i burimeve mbetet "mikroskopik", mijëra herë më efektive se FTS "e pastër".! Gjithçka që mbetet është ta bashkojmë atë në një kërkesë të vetme:

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

Vini re se nënpyetja e dytë është ekzekutuar vetëm nëse i pari kthehej më pak se sa pritej i fundit LIMIT numri i rreshtave. Unë jam duke folur për këtë metodë të optimizimit të pyetjeve shkruar tashmë më parë.

Pra, po, tani kemi edhe btree dhe xhin në tryezë, por statistikisht rezulton se më pak se 10% e kërkesave arrijnë në ekzekutimin e bllokut të dytë. Kjo do të thotë, me kufizime të tilla tipike të njohura paraprakisht për detyrën, ne ishim në gjendje të zvogëlojmë konsumin total të burimeve të serverit me pothuajse një mijë herë!

1.5*: mund të bëjmë pa skedar

më i lartë LIKE Ne u penguam nga përdorimi i renditjes së gabuar. Por mund të "vendoset në rrugën e duhur" duke specifikuar operatorin USING:

Si parazgjedhje supozohet ASC. Për më tepër, mund të specifikoni emrin e një operatori specifik të renditjes në një klauzolë USING. Operatori i renditjes duhet të jetë anëtar i më pak se ose më i madh se i disa familjeve të operatorëve të pemës B. ASC zakonisht ekuivalente USING < и DESC zakonisht ekuivalente USING >.

Në rastin tonë, "më pak" është ~<~:

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

[shikoni në shpjegojnë.tensor.ru]

2: si bëhen të tharta kërkesat

Tani e lëmë kërkesën tonë të "ziejë" për gjashtë muaj ose një vit dhe jemi të befasuar kur e gjejmë përsëri "në krye" me treguesit e "pompimit" total ditor të kujtesës (buffers hit të përbashkëta) në 5.5TB - pra edhe më shumë se sa ishte fillimisht.

Jo, sigurisht, biznesi ynë është rritur dhe ngarkesa jonë është rritur, por jo me të njëjtën masë! Kjo do të thotë se diçka është e çuditshme këtu - le ta kuptojmë.

2.1: lindja e faqes

Në një moment, një ekip tjetër zhvillimi donte të bënte të mundur "kërcimin" nga një kërkim i shpejtë i abonimit në regjistër me të njëjtat rezultate, por të zgjeruara. Çfarë është një regjistër pa navigim faqe? Le ta prishim!

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

Tani ishte e mundur të shfaqej regjistri i rezultateve të kërkimit me ngarkimin "faqe pas faqe" pa asnjë stres për zhvilluesin.

Sigurisht, në fakt, për çdo faqe vijuese të të dhënave lexohet gjithnjë e më shumë (të gjitha nga hera e mëparshme, të cilat do t'i hedhim poshtë, plus "bishtin" e nevojshëm) - domethënë, ky është një antimodel i qartë. Por do të ishte më e saktë të filloni kërkimin në përsëritjen tjetër nga çelësi i ruajtur në ndërfaqe, por për këtë një herë tjetër.

2.2: Unë dua diçka ekzotike

Në një moment zhvilluesi donte diversifikoni mostrën që rezulton me të dhëna nga një tabelë tjetër, për të cilën e gjithë kërkesa e mëparshme është dërguar në CTE:

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

Dhe edhe kështu, nuk është keq, pasi nënpyetja vlerësohet vetëm për 10 rekorde të kthyera, nëse jo ...

2.3: DISTINCT është i pakuptimtë dhe i pamëshirshëm

Diku në procesin e një evolucioni të tillë nga nënpyetja e 2-të kam humbur NOT LIKE gjendje. Është e qartë se pas kësaj UNION ALL filloi të kthehej disa hyrje dy herë - së pari gjendet në fillim të rreshtit, dhe pastaj përsëri - në fillim të fjalës së parë të kësaj rreshti. Në kufi, të gjitha rekordet e nënpyetjes së dytë mund të përputhen me rekordet e të parit.

Çfarë bën një zhvillues në vend që të kërkojë shkakun?.. Nuk ka dyshim!

• dyfishi i madhësisë mostra origjinale
• zbato SHQYRTIMpër të marrë vetëm shembuj të vetëm të çdo rreshti

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

Kjo do të thotë, është e qartë se rezultati, në fund, është saktësisht i njëjtë, por mundësia për të "fluturuar" në nën-pyetjen e 2-të CTE është bërë shumë më e lartë, dhe madje edhe pa këtë, qartësisht më i lexueshëm.

Por kjo nuk është gjëja më e trishtueshme. Meqenëse zhvilluesi kërkoi të zgjidhte DISTINCT jo për ato specifike, por për të gjitha fushat njëherësh të dhënat, më pas fusha sub_query - rezultati i nënpyetjes - u përfshi automatikisht atje. Tani, për të ekzekutuar DISTINCT, baza e të dhënave duhej të ekzekutohej tashmë jo 10 nënpyetje, por të gjitha + 2!

2.4: bashkëpunimi mbi të gjitha!

Pra, zhvilluesit jetuan pa u shqetësuar, sepse përdoruesi qartësisht nuk kishte durim të mjaftueshëm për të "rregulluar" regjistrin në vlera të rëndësishme N me një ngadalësim kronik në marrjen e secilës "faqe" pasuese.

Derisa zhvilluesit nga një departament tjetër erdhën tek ata dhe donin të përdornin një metodë kaq të përshtatshme për kërkim përsëritës - domethënë, marrim një copë nga një mostër, e filtrojmë sipas kushteve shtesë, nxjerrim rezultatin, pastaj copën tjetër (që në rastin tonë arrihet duke rritur N) dhe kështu me radhë derisa të mbushim ekranin.

Në përgjithësi, në ekzemplarin e kapur N arriti vlera gati 17K, dhe në vetëm një ditë të paktën 4 mijë kërkesa të tilla u ekzekutuan “përgjatë zinxhirit”. Të fundit prej tyre u skanuan me guxim 1 GB memorie për përsëritje...

Në total

Burimi: www.habr.com