Tûzenen managers fan ferkeapkantoaren yn it heule lân rekord tsientûzenen kontakten deistich - feiten fan kommunikaasje mei potinsjele as besteande kliïnten. En dêrfoar moatte jo earst in klant fine, en leafst hiel fluch. En dit bart meast mei namme.
Dêrom is it net ferrassend dat, opnij analysearjen fan "swiere" fragen op ien fan 'e meast laden databases - ús eigen , ik fûn "yn de top" fersyk foar in "fluch" sykjen op namme foar organisaasje kaarten.
Boppedat lei fierder ûndersyk in nijsgjirrich foarbyld op earst optimisaasje en dan prestaasjesdegradaasje fersyk mei syn sekwinsjele ferfining troch ferskate teams, elk fan dat hannele allinnich mei de bêste bedoelingen.
0: wat woe de brûker?
[KDPV ]
Wat betsjuttet in brûker normaal as se prate oer in "fluch" sykjen op namme? It blykt hast nea in "earlik" sykjen nei in substring lykas ... LIKE '%роза%' - want dan it resultaat befettet net allinnich 'Розалия' и 'Магазин Роза'mar 'Гроза' en sels 'Дом Деда Мороза'.
De brûker giet derfan út op it deistich nivo dat jo him sille foarsjen sykje troch begjin fan wurd yn de titel en meitsje it relevanter dat begjint oan ynfierd. En jo sille it dwaan hast daliks - foar ynterlineêre ynfier.
1: beheine de taak
En noch mear, in persoan sil net spesifyk ynfiere 'роз магаз', sadat jo elk wurd op foarheaksel sykje moatte. Nee, it is folle makliker foar in brûker om te reagearjen op in rappe hint foar it lêste wurd dan de foargeande doelbewust "ûnderspesifisearje" - sjoch hoe't elke sykmasjine dit behannelet.
Yn ' rjocht it formulearjen fan de easken foar it probleem is mear as de helte fan de oplossing. Soms foarsichtich gebrûk case analyze .
Wat docht in abstrakte ûntwikkelder?
1.0: eksterne sykmasine
Och, sykjen is dreech, ik wol hielendal neat dwaan - litte wy it oan devops jaan! Lit se in sykmasjine bûten de database ynsette: Sphinx, ElasticSearch, ...
In wurkjende opsje, hoewol arbeidsintensyf yn termen fan syngronisaasje en snelheid fan feroaringen. Mar net yn ús gefal, om't it sykjen foar elke kliïnt allinich binnen it ramt fan syn akkountgegevens wurdt útfierd. En de gegevens hawwe in frij hege fariabiliteit - en as de manager hat no ynfierd de kaart 'Магазин Роза', dan kin er nei 5-10 sekonden al betinke dat er syn e-mail dêr fergeat oan te jaan en it wol fine en korrigearje.
Dêrom - litte wy sykje "direkt yn 'e databank". Gelokkich lit PostgreSQL ús dit dwaan, en net allinich ien opsje - wy sille se sjen.
1.1: "earlik" substring
Wy hingje oan it wurd "substring". Mar foar yndeks sykjen troch substring (en sels troch reguliere útdrukkingen!) Der is in poerbêste ! Allinne dan sil it nedich wêze om goed te sortearjen.
Litte wy besykje de folgjende plaat te nimmen om it model te ferienfâldigjen:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Wy uploade dêr 7.8 miljoen records fan echte organisaasjes en yndeksearje se:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Litte wy nei de earste 10 records sykje foar ynterlineêr sykjen:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
No, dat is... 26 ms, 31 MB lês gegevens en mear dan 1.7K filtere records - foar 10 socht. De overheadkosten binne te heech, is der net wat effisjinter?
1.2: sykje op tekst? It is FTS!
Yndied, PostgreSQL biedt in heul krêftige (Folsleine tekstsykjen), ynklusyf de mooglikheid om te sykjen foar prefix. In geweldige opsje, jo hoege net iens tafoegings te ynstallearjen! Litte we it besykje:
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;
Hjir parallelization fan query útfiering holp ús in bytsje, snije de tiid yn de helte oan 11 ms. En wy moasten 1.5 kear minder lêze - yn totaal 20MB. Mar hjir, hoe minder, hoe better, om't it grutter it folume dat wy lêze, hoe heger de kânsen om in cache-miss te krijen, en elke ekstra side fan gegevens lêzen fan 'e skiif is in potinsjele "remmen" foar it fersyk.
1.3: noch LIKE?
It foarige fersyk is goed foar elkenien, mar allinich as jo it hûnderttûzen kear deis lûke, komt it 2TB lês gegevens. Yn it bêste gefal, út it ûnthâld, mar as jo pech, dan fan skiif. Dus litte wy besykje it lytser te meitsjen.
Lit ús ûnthâlde wat de brûker wol sjen earst "dy't begjinne mei ...". Dit is dus yn syn suverste foarm troch text_pattern_ops! En allinich as wy "net genôch hawwe" oant 10 records wêr't wy nei sykje, dan sille wy se moatte foltôgje mei it lêzen fan FTS-sykjen:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Prachtige prestaasje - totaal 0.05ms en in bytsje mear as 100KB lêze! Allinne wy fergetten sortearje op nammesadat de brûker net ferdwale yn 'e resultaten:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Och, wat is net sa moai mear - it liket der op dat der in yndeks is, mar it sortearjen fljocht der foarby... It is fansels al in protte kearen effektiver as de foarige opsje, mar...
1.4: "finish with a file"
Mar d'r is in yndeks wêrmei jo kinne sykje op berik en noch normaal sortearje brûke - reguliere btree!
CREATE INDEX ON firms(lower(name));Allinich it fersyk dêrfoar sil "manueel sammele" moatte wurde:
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Geweldich - it sortearjen wurket, en boarneferbrûk bliuwt "mikroskopysk", tûzenen kearen effektiver as "suvere" FTS! Alles wat oerbliuwt is it tegearre yn ien fersyk te setten:
(
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;
Tink derom dat de twadde subquery wurdt útfierd allinich as de earste minder weromkaam as ferwachte de lêste LIMIT oantal rigels. Ik praat oer dizze metoade fan query-optimalisaasje .
Sa ja, wy hawwe no sawol btree as jenever op 'e tafel, mar statistysk docht bliken dat minder as 10% fan oanfragen berikke de útfiering fan it twadde blok. Dat is, mei sokke typyske beheinings dy't foarôf bekend binne foar de taak, koene wy it totale konsumpsje fan serverboarnen mei hast tûzen kear ferminderje!
1.5 *: wy kinne dwaan sûnder in triem
Boppe LIKE Wy waarden foarkommen fan it brûken fan ferkearde sortearring. Mar it kin "op it goede paad ynsteld wurde" troch de operator GEBRUK op te jaan:
Standert wurdt it oannommen
ASC. Derneist kinne jo de namme fan in spesifike sorteoperator yn in klausule opjaanUSING. De sorte-operator moat lid wêze fan 'e minder as of grutter as fan guon famylje fan B-beam-operators.ASCmeastal lykweardichUSING <иDESCmeastal lykweardichUSING >.
Yn ús gefal is "minder". ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: hoe fersiken draaie soer
No litte wy ús fersyk om seis moannen as in jier te "simmeren", en wy binne ferrast dat it wer "oan 'e top" te finen is mei yndikatoaren fan 'e totale deistige "pompen" fan ûnthâld (buffers shared hit) yn 5.5TB - dat is noch mear as it oarspronklik wie.
Nee, fansels, ús bedriuw is groeid en ús wurkdruk is ferhege, mar net mei itselde bedrach! Dit betsjut dat hjir wat fiskich is - litte wy it útfine.
2.1: de berte fan paging
Op in stuit woe in oar ûntwikkelingsteam it mooglik meitsje om te "springen" fan in rappe sykaksje nei it register mei deselde, mar útwreide resultaten. Wat is in register sûnder sidenavigaasje? Litte wy it opknappe!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;No wie it mooglik om it register fan sykresultaten te sjen mei it laden fan "side-by-page" sûnder stress foar de ûntwikkelder.
Fansels, yn feite, foar elke folgjende side mei gegevens wurdt mear en mear lêzen (allegear fan 'e foarige kear, dy't wy sille wegerje, plus de nedige "sturt") - dat is, dit is in dúdlik antipattern. Mar it soe krekter wêze om it sykjen te begjinnen by de folgjende iteraasje fan 'e kaai opslein yn' e ynterface, mar oer dat in oare kear.
2.2: Ik wol wat eksoatysk
Op in stuit woe de ûntwikkelder diversifisearje de resultearjende stekproef mei gegevens fan in oare tafel, wêrfoar it folsleine foarige fersyk nei CTE stjoerd waard:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;En sels dat is it net min, om't de subquery allinich wurdt evaluearre foar 10 weromjûne records, as net ...
2.3: DISTINCT is sûnder sin en genedeleas
Earne yn it proses fan sa'n evolúsje fan 'e 2e subquery ferlern gien NOT LIKE betingst. It is dúdlik dat nei dizze UNION ALL begûn werom te kommen guon ynstjoerings twa kear - earst fûn oan it begjin fan 'e rigel, en dan wer - oan it begjin fan it earste wurd fan dizze rigel. Yn 'e limyt kinne alle records fan' e 2e subquery oerienkomme mei de records fan 'e earste.
Wat docht in ûntwikkelder ynstee fan sykjen nei de oarsaak?.. Gjin fraach!
- dûbele de grutte orizjinele samples
- tapasse DISTINCTom mar inkele eksimplaren fan elke rigel te krijen
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>;Dat is, it is dúdlik dat it resultaat, op it lêst, krekt itselde is, mar de kâns op "fleane" yn 'e 2e CTE subquery is folle heger wurden, en sels sûnder dit, dúdliker lêsber.
Mar dit is net it tryste ding. Sûnt de ûntwikkelder frege om te selektearjen DISTINCT net foar spesifike, mar foar alle fjilden tagelyk records, dan is it sub_query-fjild - it resultaat fan 'e subquery - dêr automatysk opnommen. No, om út te fieren DISTINCT, de databank moast al útfiere net 10 subqueries, mar allegear <2 * N> + 10!
2.4: gearwurking boppe alles!
Dat, de ûntwikkelders wennen troch - se makken der gjin lêst fan, om't de brûker dúdlik net genôch geduld hie om it register "oan te passen" oan signifikante N-wearden mei in groanyske fertraging by it ûntfangen fan elke folgjende "side".
Oant ûntwikkelders fan in oare ôfdieling by har kamen en sa'n handige metoade wolle brûke foar iteratyf sykjen - dat is, wy nimme in stik út in stekproef, filterje it troch ekstra betingsten, tekenje it resultaat, dan it folgjende stik (wat yn ús gefal wurdt berikt troch it fergrutsjen fan N), en sa fierder oant wy it skerm folje.
Yn it algemien, yn it fongen eksimplaar N berikte wearden fan hast 17K, En yn mar ien dei op syn minst 4K fan sokke oanfragen waarden útfierd "langs de keatling". De lêsten fan harren waarden frijmoedich skansearre troch 1GB ûnthâld per iteraasje...
Totaal
Boarne: www.habr.com