Duizenden managers van verkoopkantoren in het hele land registreren dit tienduizenden contacten per dag — feiten van communicatie met potentiële of bestaande klanten. En daarvoor moet je eerst een klant vinden, en liefst heel snel. En dit gebeurt meestal bij naam.
Het is daarom niet verwonderlijk dat we bij het opnieuw analyseren van ‘zware’ zoekopdrachten in een van de meest geladen databases – die van onszelf – , ik vond "in de top" verzoek om een “snelle” zoekopdracht op naam voor organisatiekaarten.
Bovendien bracht verder onderzoek een interessant voorbeeld aan het licht eerst optimalisatie en daarna prestatievermindering verzoek met zijn opeenvolgende verfijning door verschillende teams, die elk uitsluitend met de beste bedoelingen handelden.
0: wat wilde de gebruiker?
[KDPV ]
Wat bedoelt een gebruiker gewoonlijk als hij het heeft over een ‘snelle’ zoekopdracht op naam? Het blijkt bijna nooit een ‘eerlijke’ zoektocht naar een substring als ... LIKE '%роза%' - want dan omvat het resultaat niet alleen 'Розалия' и 'Магазин Роза'Maar 'Гроза' en zelfs 'Дом Деда Мороза'.
De gebruiker gaat er op het dagelijkse niveau van uit dat u hem zult voorzien zoeken op begin van woord in de titel en maak het relevanter begint met ingevoerde. En jij zult het doen bijna onmiddellijk - voor interlineaire invoer.
1: beperk de taak
En nog meer, een persoon zal niet specifiek binnenkomen 'роз магаз', zodat u elk woord op voorvoegsel moet zoeken. Nee, het is voor een gebruiker veel gemakkelijker om te reageren op een snelle hint voor het laatste woord dan om doelbewust de vorige hints ‘onder te specificeren’ – kijk eens hoe een zoekmachine hiermee omgaat.
In het algemeen juist het formuleren van de eisen voor het probleem is meer dan de helft van de oplossing. Soms zorgvuldige use case-analyse .
Wat doet een abstracte ontwikkelaar?
1.0: externe zoekmachine
Oh, zoeken is moeilijk, ik wil helemaal niets doen - laten we het aan devops geven! Laat ze een zoekmachine buiten de database inzetten: Sphinx, ElasticSearch,...
Een werkende optie, zij het arbeidsintensief in termen van synchronisatie en snelheid van veranderingen. Maar in ons geval niet, aangezien de zoekopdracht voor elke klant alleen wordt uitgevoerd in het kader van zijn accountgegevens. En de gegevens hebben een vrij hoge variabiliteit - en als de manager nu de kaart heeft ingevoerd 'Магазин Роза', dan herinnert hij zich na 5-10 seconden misschien al dat hij vergeten is zijn e-mailadres daar aan te geven en wil hij deze opzoeken en corrigeren.
Daarom - laten we zoek “direct in de database”. Gelukkig stelt PostgreSQL ons in staat dit te doen, en niet slechts één optie: we zullen ernaar kijken.
1.1: substring "eerlijk".
Wij houden vast aan het woord ‘substring’. Maar voor indexzoeken op substring (en zelfs op reguliere expressies!) is er een uitstekende ! Alleen dan is het nodig om correct te sorteren.
Laten we proberen de volgende plaat te nemen om het model te vereenvoudigen:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);We uploaden daar 7.8 miljoen records van echte organisaties en indexeren ze:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Laten we naar de eerste tien records zoeken voor interlineair zoeken:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Nou, zo... 26 ms, 31 MB lees gegevens en meer dan 1.7K gefilterde records - voor 10 doorzochte records. De overheadkosten zijn te hoog, bestaat er niet iets efficiënter?
1.2: zoeken op tekst? Het is FTS!
PostgreSQL biedt inderdaad een zeer krachtig (Volledige tekst zoeken), inclusief de mogelijkheid om vooraf te zoeken. Een uitstekende optie, je hoeft niet eens extensies te installeren! Laten we proberen:
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;
Hier hielp de parallellisatie van de uitvoering van zoekopdrachten ons een beetje, waardoor de tijd werd gehalveerd 11 ms. En we moesten in totaal 1.5 keer minder lezen 20MB. Maar hier geldt: hoe minder hoe beter, want hoe groter het volume dat we lezen, hoe groter de kans dat we een cache missen, en elke extra pagina met gegevens die van de schijf wordt gelezen, is een potentiële “rem” voor het verzoek.
1.3: nog steeds LIKE?
Het vorige verzoek is goed voor iedereen, maar alleen als je er honderdduizend keer per dag aan trekt, zal het komen 2TB gegevens lezen. In het beste geval uit het geheugen, maar als je pech hebt, dan vanaf schijf. Dus laten we proberen het kleiner te maken.
Laten we onthouden wat de gebruiker wil zien eerst “die beginnen met...”. Dit is dus in zijn puurste vorm via text_pattern_ops! En alleen als we “niet genoeg” hebben van maximaal 10 records waarnaar we op zoek zijn, zullen we ze moeten lezen met behulp van FTS-zoeken:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Uitstekende prestaties - totaal 0.05 ms en iets meer dan 100 KB lezen! Alleen wij zijn het vergeten Sorteren op naamzodat de gebruiker niet verdwaalt in de resultaten:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Oh, iets is niet meer zo mooi - het lijkt alsof er een index is, maar de sortering vliegt er voorbij... Het is natuurlijk al vele malen effectiever dan de vorige optie, maar...
1.4: “afsluiten met een bestand”
Maar er is een index waarmee u op bereik kunt zoeken en toch normaal kunt sorteren - reguliere btree!
CREATE INDEX ON firms(lower(name));Alleen het verzoek daartoe zal “handmatig moeten worden verzameld”:
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Uitstekend - de sortering werkt en het verbruik van hulpbronnen blijft "microscopisch", duizenden keren effectiever dan ‘pure’ FTS! Het enige dat overblijft is om het samen te brengen in één enkel verzoek:
(
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 op dat de tweede subquery wordt uitgevoerd alleen als de eerste minder terugkwam dan verwacht de laatste LIMIT aantal lijnen. Ik heb het over deze methode van query-optimalisatie .
Dus ja, we hebben nu zowel btree als gin op tafel, maar statistisch gezien blijkt dat zo te zijn minder dan 10% van de verzoeken bereikt de uitvoering van het tweede blok. Dat wil zeggen, met zulke typische beperkingen die vooraf bekend waren voor de taak, konden we het totale verbruik van serverbronnen bijna duizend keer verminderen!
1.5*: we kunnen zonder bestand
hoger LIKE Er werd voorkomen dat we een onjuiste sortering toepasten. Maar het kan “op het goede pad worden gezet” door de USING-operator op te geven:
Standaard wordt hiervan uitgegaan
ASC. Bovendien kunt u de naam van een specifieke sorteeroperator in een clausule opgevenUSING. De sorteeroperator moet lid zijn van de kleiner dan of groter dan van een familie van B-boomoperatoren.ASCmeestal gelijkwaardigUSING <иDESCmeestal gelijkwaardigUSING >.
In ons geval is ‘minder’ dat wel ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: hoe verzoeken zuur worden
Nu laten we ons verzoek zes maanden of een jaar 'sudderen', en we zijn verrast om het weer 'bovenaan' te vinden met indicatoren van het totale dagelijkse 'pompen' van geheugen (buffers gedeelde hit) in 5.5TB - dat wil zeggen, zelfs meer dan het oorspronkelijk was.
Nee, natuurlijk is ons bedrijf gegroeid en is onze werkdruk toegenomen, maar niet in dezelfde mate! Dit betekent dat er hier iets niet klopt. Laten we het uitzoeken.
2.1: de geboorte van paging
Op een gegeven moment wilde een ander ontwikkelingsteam het mogelijk maken om van een snelle subscript-zoekopdracht naar het register te ‘springen’ met dezelfde, maar uitgebreide resultaten. Wat is een register zonder paginanavigatie? Laten we het verpesten!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Nu was het mogelijk om het register met zoekresultaten ‘pagina voor pagina’ te laten laden zonder enige stress voor de ontwikkelaar.
Natuurlijk, in feite voor elke volgende pagina met gegevens wordt er steeds meer gelezen (allemaal van de vorige keer, die we zullen weggooien, plus de noodzakelijke "staart") - dat wil zeggen, dit is een duidelijk antipatroon. Maar het zou juister zijn om de zoekopdracht te starten bij de volgende iteratie vanaf de sleutel die in de interface is opgeslagen, maar daarover een andere keer.
2.2: Ik wil iets exotisch
Op een gegeven moment wilde de ontwikkelaar diversifieer het resulterende monster met gegevens uit een andere tabel, waarvoor het volledige eerdere verzoek naar CTE is verzonden:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;En toch is het niet slecht, aangezien de subquery slechts voor 10 geretourneerde records wordt geëvalueerd, zo niet ...
2.3: DISTINCT is zinloos en genadeloos
Ergens in het proces van een dergelijke evolutie vanaf de tweede subquery kwijt NOT LIKE voorwaarde. Het is duidelijk dat hierna UNION ALL begon terug te keren sommige vermeldingen tweemaal - eerst gevonden aan het begin van de regel, en dan opnieuw - aan het begin van het eerste woord van deze regel. Binnen de limiet kunnen alle records van de tweede subquery overeenkomen met de records van de eerste.
Wat doet een ontwikkelaar in plaats van op zoek te gaan naar de oorzaak?.. Geen vraag!
- dubbel zo groot originele monsters
- DISTINCT toepassenom slechts enkele exemplaren van elke regel te krijgen
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 wil zeggen, het is duidelijk dat het resultaat uiteindelijk precies hetzelfde is, maar de kans om naar de 2e CTE-subquery te "vliegen" is veel groter geworden, en zelfs zonder dit, duidelijk beter leesbaar.
Maar dit is niet het meest trieste. Omdat de ontwikkelaar vroeg om te selecteren DISTINCT niet voor specifieke velden, maar voor alle velden tegelijk records, waarna het veld sub_query – het resultaat van de subquery – daar automatisch werd opgenomen. Nu, om uit te voeren DISTINCT, moest de database al worden uitgevoerd niet 10 subquery's, maar allemaal <2 * N> + 10!
2.4: samenwerking boven alles!
Dus de ontwikkelaars leefden voort - ze namen niet de moeite, omdat de gebruiker duidelijk niet genoeg geduld had om het register te "aanpassen" aan significante N-waarden met een chronische vertraging bij het ontvangen van elke volgende "pagina".
Totdat ontwikkelaars van een andere afdeling naar hen toe kwamen en zo'n handige methode wilden gebruiken voor iteratief zoeken - dat wil zeggen, we nemen een stukje uit een monster, filteren het op aanvullende voorwaarden, tekenen het resultaat, dan het volgende stuk (wat in ons geval wordt bereikt door N te vergroten), enzovoort totdat we het scherm vullen.
Over het algemeen in het gevangen exemplaar N bereikte waarden van bijna 17K, en in slechts één dag werden minstens 4 van dergelijke verzoeken “langs de keten” uitgevoerd. De laatsten van hen werden moedig langs gescand 1 GB geheugen per iteratie...
In totaal
Bron: www.habr.com