Tusenvis av ledere fra salgskontorer over hele landet registrerer titusenvis av kontakter daglig – fakta om kommunikasjon med potensielle eller eksisterende kunder. Og for dette må du først finne en klient, og helst veldig raskt. Og dette skjer oftest ved navn.
Derfor er det ikke overraskende at vi nok en gang analyserer "tunge" søk på en av de mest belastede databasene - vår egen , jeg fant "i toppen" be om et "raskt" søk etter navn for organisasjonskort.
Videre undersøkelser avslørte dessuten et interessant eksempel først optimalisering og deretter ytelsesforringelse forespørsel med sin sekvensielle foredling av flere team, som hver handlet utelukkende med de beste intensjoner.
0: hva ønsket brukeren?
[KDPV ]
Hva mener en bruker vanligvis når de snakker om et "raskt" søk etter navn? Det viser seg nesten aldri å være et "ærlig" søk etter en delstreng som ... LIKE '%роза%' – for da inkluderer resultatet ikke bare 'Розалия' и 'Магазин Роза'Men 'Гроза' og selv 'Дом Деда Мороза'.
Brukeren antar på hverdagsnivå at du vil gi ham søk etter begynnelsen av ordet i tittelen og gjør det mer relevant det starter kl inn. Og du vil gjøre det nesten umiddelbart - for interlineær inngang.
1: begrense oppgaven
Og enda mer, en person vil ikke spesifikt komme inn 'роз магаз', slik at du må søke etter hvert ord etter prefiks. Nei, det er mye lettere for en bruker å svare på et raskt hint for det siste ordet enn å målrettet "underspesifisere" de forrige - se på hvordan en søkemotor håndterer dette.
Generelt, riktig å formulere kravene til problemet er mer enn halve løsningen. Noen ganger nøye brukscaseanalyse .
Hva gjør en abstrakt utvikler?
1.0: ekstern søkemotor
Å, søk er vanskelig, jeg vil ikke gjøre noe i det hele tatt - la oss gi det til devops! La dem distribuere en søkemotor eksternt til databasen: Sphinx, ElasticSearch, ...
Et fungerende alternativ, om enn arbeidskrevende når det gjelder synkronisering og endringshastighet. Men ikke i vårt tilfelle, siden søket utføres for hver klient bare innenfor rammen av hans kontodata. Og dataene har en ganske høy variabilitet - og om lederen nå har lagt inn kortet 'Магазин Роза', så etter 5-10 sekunder kan han allerede huske at han glemte å angi e-posten sin der og vil finne den og rette den.
Derfor - la oss søk "direkte i databasen". Heldigvis lar PostgreSQL oss gjøre dette, og ikke bare ett alternativ - vi skal se på dem.
1.1: "ærlig" understreng
Vi klamrer oss til ordet "understreng". Men for indekssøk etter understreng (og til og med etter regulære uttrykk!) er det en utmerket ! Først da vil det være nødvendig å sortere riktig.
La oss prøve å ta følgende plate for å forenkle modellen:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Vi laster opp 7.8 millioner poster over ekte organisasjoner der og indekserer dem:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);La oss se etter de første 10 postene for interlineært søk:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Vel, det er... 26 ms, 31 MB lese data og mer enn 1.7K filtrerte poster - for 10 søkte. Overheadkostnadene er for høye, er det ikke noe mer effektivt?
1.2: søke etter tekst? Det er FTS!
Faktisk gir PostgreSQL en veldig kraftig (Fulltekstsøk), inkludert muligheten til å prefikssøke. Et utmerket alternativ, du trenger ikke engang å installere utvidelser! La oss prøve:
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;
Her hjalp parallellisering av utførelse av spørringer oss litt, og halverte tiden til 11 ms. Og vi måtte lese 1.5 ganger mindre – totalt 20MB. Men her, jo mindre, jo bedre, fordi jo større volumet vi leser, jo større er sjansene for å få en cache-miss, og hver ekstra side med data som leses fra disken er en potensiell "brems" for forespørselen.
1.3: fortsatt LIKE?
Den forrige forespørselen er bra for alle, men bare hvis du trekker den hundre tusen ganger om dagen, kommer den 2TB lese data. I beste fall fra minnet, men hvis du er uheldig, så fra disk. Så la oss prøve å gjøre det mindre.
La oss huske hva brukeren vil se først "som begynner med...". Så dette er i sin reneste form via text_pattern_ops! Og bare hvis vi "ikke har nok" opptil 10 poster vi ser etter, må vi lese dem ferdig med FTS-søk:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Utmerket ytelse - totalt 0.05ms og litt mer enn 100KB lese! Bare vi glemte Sorter etter navnslik at brukeren ikke går seg vill i resultatene:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Å, noe er ikke så vakkert lenger - det virker som det er en indeks, men sorteringen flyr forbi den... Den er selvfølgelig allerede mange ganger mer effektiv enn det forrige alternativet, men...
1.4: "avslutt med en fil"
Men det er en indeks som lar deg søke etter område og fortsatt bruke sortering normalt - vanlig btre!
CREATE INDEX ON firms(lower(name));Bare forespørselen om den må "samles inn manuelt":
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Utmerket - sorteringen fungerer, og ressursforbruket forblir "mikroskopisk", tusenvis av ganger mer effektiv enn "ren" FTS! Alt som gjenstår er å sette det sammen til en enkelt forespørsel:
(
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;
Merk at den andre underspørringen utføres bare hvis den første returnerte mindre enn forventet den siste LIMIT antall linjer. Jeg snakker om denne metoden for spørringsoptimalisering .
Så ja, vi har nå både btree og gin på bordet, men statistisk viser det seg det mindre enn 10 % av forespørslene når utførelsen av den andre blokken. Det vil si at med slike typiske begrensninger kjent på forhånd for oppgaven, klarte vi å redusere det totale forbruket av serverressurser med nesten tusen ganger!
1.5*: vi klarer oss uten fil
Høyere LIKE Vi ble forhindret fra å bruke feilsortering. Men det kan "settes på rett vei" ved å spesifisere USING-operatøren:
Som standard er det antatt
ASC. I tillegg kan du spesifisere navnet på en spesifikk sorteringsoperatør i en klausulUSING. Sorteringsoperatøren må være medlem av mindre enn eller større enn i en familie av B-tre-operatører.ASCvanligvis tilsvarendeUSING <иDESCvanligvis tilsvarendeUSING >.
I vårt tilfelle er "mindre". ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: hvordan forespørsler blir sure
Nå lar vi forespørselen vår om å "simme" i seks måneder eller et år, og vi er overrasket over å finne den igjen "på toppen" med indikatorer for den totale daglige "pumpingen" av minne (buffere delt treff) inn 5.5TB – altså enda mer enn det opprinnelig var.
Nei, selvfølgelig har virksomheten vår vokst og arbeidsmengden har økt, men ikke like mye! Dette betyr at noe er fishy her - la oss finne ut av det.
2.1: fødselen av personsøking
På et tidspunkt ønsket et annet utviklingsteam å gjøre det mulig å "hoppe" fra et raskt abonnementssøk til registeret med de samme, men utvidede resultatene. Hva er et register uten sidenavigering? La oss knuse det!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Nå var det mulig å vise registeret over søkeresultater med «side-for-side»-lasting uten stress for utvikleren.
Selvfølgelig, faktisk, for hver påfølgende side med data leses mer og mer (alt fra forrige gang, som vi vil forkaste, pluss den nødvendige "halen") - det vil si at dette er et tydelig antimønster. Men det ville være mer riktig å starte søket ved neste iterasjon fra nøkkelen som er lagret i grensesnittet, men om det en annen gang.
2.2: Jeg vil ha noe eksotisk
På et tidspunkt ønsket utvikleren diversifisere den resulterende prøven med data fra et annet bord, som hele forrige forespørsel ble sendt til CTE:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;Og likevel er det ikke dårlig, siden underspørringen vurderes bare for 10 returnerte poster, hvis ikke ...
2.3: DISTINCT er meningsløst og nådeløst
Et sted i prosessen med en slik utvikling fra den andre underspørringen gikk seg vill NOT LIKE tilstand. Det er klart at etter dette UNION ALL begynte å komme tilbake noen oppføringer to ganger - først funnet i begynnelsen av linjen, og så igjen - i begynnelsen av det første ordet i denne linjen. I grensen kan alle postene for den andre underspørringen samsvare med postene til den første.
Hva gjør en utvikler i stedet for å lete etter årsaken?.. Ingen spørsmål!
- dobbel størrelse originale prøver
- bruk DISTINCTfor å få bare enkeltforekomster av hver linje
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>;Det vil si at det er klart at resultatet til slutt er nøyaktig det samme, men sjansen for å "fly" inn i den andre CTE-underspørringen har blitt mye høyere, og selv uten dette, klart mer lesbar.
Men dette er ikke det tristeste. Siden utvikleren ba om å velge DISTINCT ikke for spesifikke, men for alle felt samtidig poster, så ble sub_query-feltet - resultatet av underspørringen - automatisk inkludert der. Nå, for å utføre DISTINCT, måtte databasen kjøre allerede ikke 10 underspørringer, men alle <2 * N> + 10!
2.4: samarbeid fremfor alt!
Så utviklerne levde uten å bry seg, fordi brukeren tydeligvis ikke hadde nok tålmodighet til å "justere" registeret til betydelige N-verdier med en kronisk nedgang i mottak av hver påfølgende "side".
Helt til utviklere fra en annen avdeling kom til dem og ønsket å bruke en så praktisk metode for iterativt søk - det vil si at vi tar et stykke fra en prøve, filtrerer det etter tilleggsbetingelser, tegner resultatet, deretter neste stykke (som i vårt tilfelle oppnås ved å øke N), og så videre til vi fyller skjermen.
Generelt i det fangede eksemplaret N nådde verdier på nesten 17K, og på bare én dag ble minst 4K av slike forespørsler utført "langs kjeden". De siste av dem ble dristig skannet av 1 GB minne per iterasjon...
Totalt
Kilde: www.habr.com