Tusindvis af ledere fra salgskontorer over hele landet fikser ind titusindvis af kontakter dagligt — fakta om kommunikation med potentielle eller allerede arbejder med os kunder. Og for denne klient skal du først finde, og helst meget hurtigt. Og dette sker oftest ved navn.
Derfor er det ikke overraskende, at vi igen analyserer "tunge" forespørgsler på en af de mest belastede databaser - vores egen , jeg fandt "i toppen" forespørgsel om "hurtig" søgning efter navn til visitkort.
Desuden afslørede yderligere undersøgelser et interessant eksempel optimering først, derefter ydeevneforringelse anmodning med dens konsekvente udfyldelse af flere teams, som hver især handlede udelukkende ud fra de bedste intentioner.
0: hvad ønskede brugeren
[KDPV ]
Hvad mener brugeren normalt, når han taler om en "hurtig" søgning efter navn? Det viser sig næsten aldrig at være en "fair" substring-søgning som ... LIKE '%роза%' - så er resultatet jo ikke kun 'Розалия' и 'Магазин Роза'Men 'Гроза' og endog 'Дом Деда Мороза'.
Brugeren mener på husstandsniveau, at du vil give ham søg i begyndelsen af et ord i titlen og vis mere relevant hvad starter kl ind. Og lave det næsten øjeblikkeligt - med subscript input.
1: begrænse opgaven
Og endnu mere vil en person ikke specifikt introducere 'роз магаз'så du skal søge præfiks for hvert ord. Nej, det er meget nemmere for en bruger at svare på et hurtigt tip til det sidste ord end bevidst at "underskrive" de foregående - se, hvordan enhver søgemaskine klarer dette.
Generelt, korrekt at formulere kravene til problemet er mere end halvdelen af løsningen. Nogle gange omhyggelig use case-analyse .
Hvad laver en abstrakt udvikler?
1.0: ekstern søgemaskine
Åh, det er svært at søge, du vil slet ikke gøre noget - lad os give det til devops! Lad dem implementere en søgemaskine eksternt til databasen for os: Sphinx, ElasticSearch, ...
En fungerende, omend tidskrævende mulighed med hensyn til synkronisering og effektivitet af ændringer. Men ikke i vores tilfælde, da søgningen kun udføres for hver klient inden for rammerne af hans kontodata. Og dataene har en ret høj variabilitet - og hvis nu lederen har indtastet et kort 'Магазин Роза', så kan han allerede efter 5-10 sekunder huske, at han glemte at angive e-mailen der og vil finde og rette den.
Derfor - lad os søg "direkte i databasen". Heldigvis tillader PostgreSQL os at gøre dette, og mere end én mulighed - vi vil overveje dem.
1.1: "ærlig" understreng
Vi klynger os til ordet "understreng". Men præcis til indekssøgning efter understreng (og endda efter regulære udtryk!) Der er en fremragende ! Først da vil det være nødvendigt at sortere korrekt.
Lad os prøve at tage en sådan plade af hensyn til modellens enkelhed:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Vi uploader 7.8 millioner registreringer af rigtige organisationer der og indekserer dem:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Lad os se efter de første 10 poster til understrengssøgning:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Nå, sådan... 26 ms, 31 MB læste data og mere end 1.7K filtrerede poster - for 10 søgte. Overheaden er for høj, er det muligt at gøre noget mere effektivt?
1.2: tekstsøgning? det er FTS!
Faktisk giver PostgreSQL en meget kraftfuld (Fuld tekstsøgning), herunder med mulighed for præfikssøgning. Fantastisk mulighed, du behøver ikke engang at installere udvidelser! Lad os 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;
Parallelisering af forespørgselsudførelsen hjalp os lidt her, og halverede tiden til 11 ms. Ja, og vi skulle læse 1.5 gange mindre – i alt 20MB. Og her jo mindre - jo bedre, for jo større volumen vi trækker fra, jo større er chancerne for at få en cache-miss, og hver ekstra side med data læst fra disken er en potentiel "bremse" for anmodningen.
1.3: stadig LIKE?
Den tidligere anmodning er god for alle, men kun hvis du trækker den hundrede tusinde gange om dagen, så kører den 2TB læse data. I bedste fald - fra hukommelsen, men hvis du ikke er heldig, så fra disken. Så lad os prøve at gøre det mindre.
Husk, hvad brugeren ønsker at se først "der starter med...". Så det er i sin reneste form. via text_pattern_ops! Og kun hvis vi "ikke har nok" op til 10 nødvendige poster, så bliver vi nødt til at læse dem ved hjælp af FTS-søgningen:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Fremragende præstation - i alt 0.05ms og lidt over 100KB Læs! Vi har bare glemt sortere efter navnså brugeren ikke farer vild i resultaterne:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Åh, noget er ikke så smukt længere - det ser ud til, at der er et indeks, men sorteringen flyver forbi det ... Selvfølgelig er det allerede mange gange mere effektivt end den tidligere version, men ...
1.4: "afslut med en fil"
Men der er et indeks, der giver dig mulighed for at søge efter område, og det er normalt at bruge sortering - almindelig btree!
CREATE INDEX ON firms(lower(name));Kun anmodningen om det skal "samles manuelt":
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Fremragende - og sortering fungerer, og ressourceforbrug forbliver "mikroskopisk", tusindvis af gange mere effektiv end "ren" FTS! Det er tilbage at indsamle i en enkelt anmodning:
(
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;
Bemærk, at den anden underforespørgsel udføres kun hvis den første returnerede mindre end forventet den sidste LIMIT antal linjer. Om denne måde at optimere forespørgsler, I .
Så ja, vi har nu btree og gin på bordet på samme tid, men statistisk viste det sig at mindre end 10 % af anmodningerne når udførelsen af den anden blok. Det vil sige, med så typiske begrænsninger for opgaven kendt på forhånd, var vi i stand til at reducere det samlede forbrug af serverressourcer med næsten tusind gange!
1.5*: undvær en fil
højere LIKE vi blev forhindret i at bruge den forkerte sortering. Men det kan "sættes på den rigtige vej" ved at angive operatoren USING:
Standard er
ASC. Derudover kan du angive navnet på en specifik sorteringsoperator i klausulenUSING. Sorteringsoperatoren skal være et "mindre end" eller "større end" medlem af en familie af B-træoperatorer.ASCnormalt tilsvarendeUSING <иDESCnormalt tilsvarendeUSING >.
I vores tilfælde er "mindre". ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: hvordan anmodninger bliver sure
Nu forlader vi vores anmodning om at "brygge" i seks måneder eller et år, og med overraskelse finder vi det igen "i toppen" med indikatorer for den samlede daglige "pumpning" af hukommelse (buffere delt hit) i 5.5TB - altså endnu mere, end det var oprindeligt.
Nej, selvfølgelig, og vores forretning er vokset, og arbejdsbyrden er steget, men ikke i samme mængde! Så noget er ikke rent her - lad os finde ud af det.
2.1: fødslen af personsøgning
På et tidspunkt ønskede et andet udviklingsteam at gøre det muligt at "hoppe" ind i registreringsdatabasen fra en hurtig subscript-søgning med de samme, men udvidede resultater. Og hvilket register uden paginering? Lad os skrue på det!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Nu var det muligt for udvikleren at vise registret over søgeresultater med en "sidetype"-indlæsning uden at anstrenge sig.
Selvfølgelig, faktisk, mere og mere læses for hver næste side med data (alt fra forrige gang, som vi kasserer, plus den ønskede "hale") - det vil sige, dette er et utvetydigt anti-mønster. Og det ville være mere korrekt at starte søgningen ved næste iteration fra nøglen gemt i grænsefladen, men mere om det en anden gang.
2.2: ønsker eksotisk
På et tidspunkt ville udvikleren diversificere den resulterende prøve med data fra en anden tabel, for hvilken hele den tidligere forespørgsel blev sendt til CTE:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;Og alligevel ikke dårligt, da underforespørgslen kun evalueres for 10 returnerede poster, hvis ikke ...
2.3: SÆRLIGE meningsløs og nådesløs
Et sted i processen med en sådan udvikling fra den 2. underforespørgsel faret vild NOT LIKE betingelse. Det er klart, at efter dette UNION ALL begyndte at vende tilbage nogle poster to gange - først fundet i begyndelsen af linjen, og så igen - i begyndelsen af det første ord i denne linje. I grænsen kunne alle poster i den 2. underforespørgsel matche posterne for den første.
Hvad gør en udvikler i stedet for at lede efter en grund?.. Ikke et spørgsmål!
- dobbelt størrelse indledende prøver
- pålægge DISTINKTfor kun at få enkelte forekomster af hver række
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 sige, at det er klart, at resultatet i sidste ende er nøjagtigt det samme, men chancen for at "flyve" ind i den 2. CTE underforespørgsel er blevet meget højere, og selv uden den, klart læse mere.
Men dette er ikke det mest sørgelige. Siden udvikleren bad om at vælge DISTINCT ikke for specifikke, men for alle felter på én gang poster, så inkluderes sub_query-feltet automatisk - resultatet af underforespørgslen. Nu for at udføre DISTINCT, skulle databasen allerede køre ikke 10 underforespørgsler, men alle <2 * N> + 10!
2.4: Samarbejde frem for alt!
Så udviklerne levede - de sørgede ikke, for i registreringsdatabasen "skru op" til signifikante N-værdier med en kronisk opbremsning i at få hver næste "side", havde brugeren tydeligvis ikke tålmodigheden.
Indtil udviklere fra en anden afdeling kom til dem og ikke ønskede at bruge sådan en bekvem metode til iterativ søgning - det vil sige, vi tager et stykke fra en prøve, filtrerer efter yderligere betingelser, tegner resultatet, derefter det næste stykke (som i vores tilfælde opnås ved at øge N), og så videre, indtil vi fylder skærmen.
Generelt i et fanget eksemplar N har nået næsten 17K, og på bare en dag blev mindst 4K sådanne anmodninger eksekveret "langs kæden". Den sidste af dem scannede dristigt allerede forbi 1 GB hukommelse pr. iteration...
I alt
Kilde: www.habr.com