🥇PostgreSQL Antipatterns: një tregim mbi përmirësimin iterativ të kërkimit sipas emrit, ose "Optimizimi këtu dhe aty"

Mijëra menaxherë nga zyra të shitjeve në të gjithë vendin regjistrojnë në sistemin tonë CRM dhe çdo ditë dhjetëra mijëra kontakte - fakte komunikimi me klientët potencialë ose ata që tashmë punojnë me ne. Dhe për këtë klient, duhet të gjendet së pari, dhe preferably shumë shpejt. Kjo ndodh shpesh sipas emrit.

Prandaj, nuk është çudi që, duke shqyrtuar përsëri kërkesat "e rënda" në një nga bazat më të ngarkuara - llogarinë tonë korporative të SBIS , zbulova "në krye"kërkesën për "kërkimin e shpejtë" sipas emrit për kartat e organizatave. Rastësisht, një hetim i mëtejshëm tregoi një shembull interesante

së pari optimizimi, dhe pastaj degradimi i performancës të kërkesës gjatë përmirësimeve sekondare nga disa ekipe, secili vepronte ekskluzivisht me qëllime të mira. 0: çfarë donte përdoruesi

[КДПВ

[КДПВ këtu]

Çfarë nënkupton zakonisht përdoruesi kur flet për "kërkimin e shpejtë" sipas emrit? Në shumicën e rastëve, kjo nuk është një kërkim "i ndershëm" për një nënstring si ... LIKE '%mëngjese%' — sepse në rezultatet e kërkimit përfshihen jo vetëm 'Mëngjeselia' dhe 'Dyqani Mëngjese', por edhe 'Gmëngjese' dhe madje 'Shtëpia e Deda Mëngjesit'.

Përdoruesi përkundrazi nënkupton në një nivel të zakonshëm se ju do t'i siguroni kërkimin nga fillimi i fjalës në emër dhe do të tregoni më të rëndësishmet ato që fillojnë me të dhënat e futura. Dhe do ta bëni këtë për më shumë se një moment — kur futen nënstringa.

1: kufizojmë detyrën

Dhe me siguri nuk do të jetë dikush që fut 'mëngjese dyq',që çdo fjalë të duhet të kërkohet me prefiks. Jo, për përdoruesin është shumë më e lehtë të reagojë ndaj sugjerimit të shpejtë për fjalën e fundit sesa të përpiqet me qëllim "ndërprerë" fjalët e mëparshme — shikoni si funksionon kjo me çdo motor kërkimi.

Në përgjithësi, duhet formulimi i kërkesave për detyrën është më shumë se gjysma e zgjidhjes. Ndonjëherë një analizë e kujdesshme e rastit të përdorimit mund të ndikojë ndjeshëm në rezultat.

Çfarë bën ndërkohë zhvilluesi abstrakt?

1.0: motori i jashtëm i kërkimit

Eh, kërkimi është i komplikuar, nuk dëshiroj të më merret me këtë - le ta japim këtë devops-it! Le të na vendosin një sistem kërkimi të jashtëm në lidhje me DB-në: Sphinx, ElasticSearch,…

Një zgjidhje funksionale, megjithëse e punës së rëndë në lidhje me sinkronizimin dhe operativitetin e ndryshimeve. Por jo në rastin tonë, sepse kërkimi bëhet për çdo klient vetëm brenda të dhënave të llogarisë së tij. Të dhënat kanë një ndryshueshmëri të mjaftueshme - dhe nëse menaxheri tani ka shtuar një rekord 'Dyqani Rrëza', atëherë pas 5-10 sekondash ai mund të kujtojë se harroi të caktojë emailin atje dhe të dëshirojë ta gjejë dhe ta rregullojë.

Prandaj - le të kërkojmë «direkt në bazë». Fatmirësisht, PostgreSQL na lejon ta bëjmë këtë, dhe jo me një variant - do t'i shqyrtojmë.

1.1: 'nënstring' e drejtë

Kapim fjalën 'nënstring'. Ajo është në të vërtetë për kërkimin indeksik të nënstringeve (dhe madje edhe për shprehjet e rregullta!) ka një modulin të shkëlqyer pg_trgm! Vetëm pastaj duhet të renditen saktësisht.

Le të provojmë të marrim për thjeshtësinë e modelit një tabelë të tillë:

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

Ngarkojmë aty 7.8 milion të dhëna të organizatave reale dhe indextojmë:

KRIJO ZGJATJEN pg_trgm;
KRIJO INDEKSI TE FIRMA PERMES përdorimit gin(lower(emri) gin_trgm_ops);

Do të kërkojmë për nëntë regjistrat e parë për kërkimin nënvizon:

ZGJEDH
  *
FROM
  firma
KU
  lower(emri) ~ ('(^|s)' || 'roza')
ORDER BY
  lower(emri) ~ ('^' || 'roza') DESC -- përpara "fillon me"
, lower(emri) -- të tjera sipas alfabetit
LIMIT 10;

[shiko në explain.tensor.ru]

No, diçka e tillë… 26ms, 31MB të dhënave të lexuara dhe mbi 1.7K regjistrash të filtruar — për 10 kërkesa. Shpenzimet janë shumë të mëdha, a nuk mund ta bëjmë ndonjëherë më efektive?

1.2: kërkimi në tekst? kjo është FTS!

Të vërtetë, PostgreSQL ofron një mekanizëm shumë të fuqishëm për kërkimin me tekst të plotë (Full Text Search), madje me mundësinë e kërkimit me prefiks. Një opsion i shkëlqyer, madje nuk është nevojë të instalojmë zgjerime! Le të provoni:

KRIJO INDEKSI TE FIRMA PERMES përdorimit gin(to_tsvector('simple'::regconfig, lower(emri)));

Zgjedh
  *
FROM
  firma
KU
  to_tsvector('simple'::regconfig, lower(emri)) @@ to_tsquery('simple', 'roza:*')
ORDER BY
  lower(emri) ~ ('^' || 'roza') DESC
, lower(emri)
LIMIT 10;

[shiko në explain.tensor.ru]

Këtu na ndihmoi pak paralelizimi i ekzekutimit të pyetjes, duke e reduktuar kohën në gjysmën e saj në 11ms. Po ashtu lexuam 1.5 herë më pak — gjithsej 20MB. Këtu më pak është më mirë, sepse sa më shumë volum të kemi, aq më të larta janë shanset për një cache miss, dhe çdo faqe e lexuar e tepruar nga disku është një potencial "ngadalësues" për kërkesën.

1.3: Pra, a është LIKE?

Të gjithë kërkesa e mëparshme është e mirë, megjithatë, nëse e thërrasim atë njëqind mijë herë në ditë, do të grumbullohen 2TB të dhënave të lexuara. Në rastin më të mirë — nga memoria, por nëse nuk kemi fat, atëherë edhe nga disku. Pra, le të përpiqemi ta bëjmë më të vogël.

Le të kujtojmë se përdoruesi dëshiron të shohë së pari "që fillojnë me ...". Sepse kjo është qartësisht kërkimi me prefiks me anë të text_pattern_ops! Dhe vetëm nëse na "mungon" deri në 10 regjistrat e kërkuar, do të duhet t’i lexojmë ato përmes kërkimit FTS:

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

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('ruzë' || '%')
LIMIT 10;

[shiko në explain.tensor.ru]

Të dhëna të shkëlqyera — vetëm 0.05ms dhe pak më shumë se 100KB të lexuara! Por harrojmë renditjen sipas emrit, në mënyrë që përdoruesi të mos humbasë në rezultatet:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('ruzë' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[shiko në explain.tensor.ru]

Oh, diçka nuk duket më kaq bukur — duket se indeksi është aty, por renditja nuk po funksionon… Sigurisht, ai është shumë më efektiv se varianti i mëparshëm, por…

1.4: «të përmirësohet me dorë»

Por ekziston një indeks që lejon të kërkosh brenda një diapazoni dhe të përdorësh renditjen normalisht — btree i zakonshëm!

KRIJO INDAXH NË firms(lower(name));

Vetëm se kërkesa për të do të duhet të «ndërtohet manualisht»:

ZGJEDH
  *
PËR
  firms
KU
  lower(name) >= 'rusa' DHE
  lower(name) <= ('rusa' || chr(65535)) -- për UTF8, për një byte - chr(255)
RENDIT ME
   lower(name)
LIMIT 10;

[shiko në explain.tensor.ru]

Shkëlqyer — dhe renditja funksionon, dhe konsumimi i burimeve mbetet «mikroskopik», mijëra herë më efektiv se «FTS i pastër»! Ka mbetur të bashkohen në një kërkesë të vetme:

(
  ZGJEDH
    *
  PËR
    firms
  KU
    lower(name) >= 'rusa' DHE
    lower(name) <= ('rusa' || chr(65535)) -- për UTF8, për kodime me një byte - chr(255)
  RENDIT ME
     lower(name)
  LIMIT 10
)
UNION ALL
(
  ZGJEDH
    *
  PËR
    firms
  KU
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'rusa:*') DHE
    lower(name) NUK SI 'rusa' || '%' -- "fillojnë me" ne i kemi gjetur më sipër
  RENDIT ME
    lower(name) ~ ('^' || 'rusa') DESC -- përdorim të njëjtën renditje për të MOS shkuar përmes indekseve btree
  , lower(name)
  LIMIT 10
)
LIMIT 10;

Do të theksoj se nënkërkesa e dytë ekzekutohet vetëm nëse i pari ktheu më pak se pritej i fundit LIMIT sasia e rreshtave. Kam shkruar më parë për këtë mënyrë optimizimi të kërkesave më parë.

Po, tani kemi një tabelë që përdor njëkohësisht btree dhe gin, megjithatë në përgjithësi rezultoi se më pak se 10% e kërkesave arrijnë të ekzekutohen në bllokun e dytë. Kjo do të thotë se me këto kufij të njohur përpara për problemin arritëm të zvogëlojmë konsumimin e total të burimeve të serverit praktikisht deri në mijëra herë!

1.5*: do ta kalojmë pa file

Lart LIKE na pengoi të përdorim renditjen e gabuar. Por mund ta 'drejtojmë në rrugën e duhur' duke përmendur operatorin USING:

Për defaut nënkuptohet ASC. Për më tepër, mund të caktosh emrin e operatorit specifik të renditjes në propozimin USING. Operator mënyre të renditjes duhet të jetë një anëtar i familjes së operatorëve B-peme. ASC zakonisht është ekuivalente me USING < dhe DESC zakonisht është ekuivalente me USING >.

Në rastin tonë 'më pak' është ~<~:

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

[shiko në explain.tensor.ru]

2: si 'fermentojnë' kërkesat

Tani e lëmë kërkesën tonë "të qëndrojë" për gjashtë muaj deri në një vit, dhe me befasinë e madhe e zbulojmë përsëri në "top" me treguesit e përmirësimit të përgjithshëm të "memorisë" (hitët e ndarjes së buferëve) në 5.5TB — domethënë edhe më shumë se sa ishte fillimisht.

Sigurisht, biznesi ynë ka rritur dhe ngarkesa është rritur, por jo aq shumë! Pra, ka diçka këtu që nuk shkon — le të hetojmë.

2.1: lindja e pagesës

Në një moment, një ekip tjetër zhvilluesish dëshiroi të krijonte mundësinë për të "kërcyer" nga kërkimi i shpejtë për në regjistrin me ato të njëjtat, por të zgjeruara rezultate. Dhe cili është një regjistër pa navigimin për faqe? Le ta lidhim!

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

Tani mund të shfaqet regjistri i rezultateve të kërkimit me ngarkesë "tip-postpages" pa ndonjë problem për zhvilluesin.

Sigurisht, në të vërtetë, për çdo faqe të ardhshme të dhënash lexohen gjithnjë e më shumë dhe më shumë (gjithçka nga hera e kaluar që do e heqim, plus "bishtin" e nevojshëm) — pra, ky është një anti-model i qartë. Më mirë do të ishte — të fillonim kërkimin në iteracionin e ardhshëm nga çelësi i mbajtur në ndërfaqe, por për këtë — një herë tjetër.

2.2: dëshira për ekzotikë

Në një moment, zhvilluesit i erdhi dëshira të ndryshonte rezultatin përfundimtar me të dhëna nga një tabelë tjetër, për këtë arsye e gjithë kërkesa e mëparshme u dërgua në CTE:

ME q SI (
  ...
  LIMIT  + 10
)
ZGJEDH
  *
, (ZGJEDH ... ) nën_kërkesë -- ndonjë kërkesë për tabelën e lidhur
NGA
  q
LIMIT 10 OFFSET ;

Dhe madje ashtu — nuk është keq, pasi kërkesat e ngulitura llogariten vetëm për 10 regjistrat që rikthehen, nëse nuk do të ishte…

2.3: DISTINCT pa kuptim dhe i pamëshirshëm

Diku gjatë këtij procesi evolucioni nga nën-kërkesa e 2-të u humb NOT LIKE kushti. E qartë është, pas kësaj UNION ALL filloi të rikthejë disa regjistra dy herë — fillimisht të gjetura sipas fillimit të fjalës, dhe pastaj edhe një herë — sipas fillimit të fjalës së parë të këtij rreshti. Në ekstrem, të gjitha regjistrat e nën-kërkesës 2 mund të përputhen me regjistrat e parë.

Çfarë bën zhvilluesi në vend që të kërkojë arsyejen?.. Asnjë pyetje!

do ta zgjerojmë dyfish madhësinë e mostrave fillestare
do të vendosim DISTINCT, që të rezultojnë vetëm një kopje të çdo rreshti

ME q SI (
  ( ... LIMIT  + 10)
  UNION ALL
  ( ... LIMIT  + 10)
  LIMIT  + 10
)
ZBARDHNI DISTINCT
  *
, (ZBARDHNI ...) nën_kërkesa
PREJ
  q
LIMIT 10 OFFSET ;

Pra, është e qartë se rezultati, për fund, është pikërisht ai, por shansi i "dështimit" në nënkërkesën e dytë CTE është rritur ndjeshëm, dhe pa këtë, lexohet dukshëm më shumë.

Por kjo nuk është trishtimi më i madh. Sepse zhvilluesi kërkoi të përzgjidhte DISTINCT jo për fusha të caktuara, por menjëherë për të gjitha fushat e regjistrimeve, kështu që automatikisht hyri edhe fusha nën_kërkesa — rezultati i nënkërkesës. Tani, për të realizuar DISTINCT, baza duhej të kryente tashmë jo 10 nënkërkesa, por të gjitha + 10!

2.4: bashkëpunimi mbi gjithçka!

Kështu ishte, zhvilluesit jetonin — nuk shqetësoheshin, sepse në regjistër "të rregullohej" deri në vlera të konsiderueshme N në një ngadalësim kronik të marrjes së çdo "faqe" të re nga përdoruesi, dukshëm nuk kishte durim.

Derisa atyre iu erdhën zhvilluesit nga departamenti tjetër, dhe nuk donin të shfrytëzonin një metodë kaq të këndshme për kërkimin iterativ — pra ndryshe marrim një pjesë nga ndonjë mostër, filtrojmë sipas kushteve shtesë, vizatojmë rezultatin, pastaj pjesën tjetër (çka në rastin tonë arrihet duke rritur N), dhe ashtu deri sa të mbushim ekranin.

Në përgjithësi, në mostër e kapur N arriti vlera gati në 17K, dhe gjithsej brenda 24 orëve ishin realizuar „në zinxhir“ të paktën 4K të tillë kërkesash. Të fundit prej tyre u skanuan me guxim nëpër 1GB memorie në çdo iteracion…