For flere måneder siden - offentlig til PostgreSQL.
Du har allerede brukt den mer enn 6000 ganger, men en praktisk funksjon som kanskje har gått ubemerket hen er strukturelle ledetråder, som ser omtrent slik ut:
Lytt til dem, og forespørslene dine vil "bli glatte og silkemyke." 🙂
Men seriøst, mange situasjoner som gjør en forespørsel treg og ressurskrevende er typiske og kan gjenkjennes av planens struktur og data.
I dette tilfellet trenger ikke hver enkelt utvikler å se etter et optimaliseringsalternativ på egen hånd, kun stole på hans erfaring - vi kan fortelle ham hva som skjer her, hva som kan være årsaken, og hvordan man nærmer seg en løsning. Det var det vi gjorde.
La oss se nærmere på disse sakene – hvordan de er definert og hvilke anbefalinger de fører til.
For bedre å fordype deg i emnet, kan du først lytte til den tilsvarende blokken fra , og bare deretter gå videre til en detaljert analyse av hvert eksempel:
#1: indekser "undersortering"
Når gjør det
Vis den siste fakturaen for klienten "LLC Kolokolchik".
Hvordan identifisere
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
anbefalinger
Indeks brukt utvide med sorteringsfelt.
Eksempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Du kan umiddelbart legge merke til at mer enn 100 poster ble trukket fra indeksen, som deretter ble sortert, og så var den eneste igjen.
Retter opp:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Selv på et så primitivt utvalg - 8.5 ganger raskere og 33 ganger færre lesninger. Jo flere "fakta" du har for hver verdi, desto tydeligere blir effekten fk.
Jeg legger merke til at en slik indeks vil fungere som en "prefiks"-indeks ikke dårligere enn før for andre søk med fk, hvor sorteres etter pk det var ikke og det er det ikke (du kan lese mer om dette ). Inkludert vil det gi normal eksplisitt utenlandsk nøkkelstøtte på dette feltet.
#2: indekskryss (BitmapAnd)
Når gjør det
Vis alle avtaler for klienten “LLC Kolokolchik”, inngått på vegne av “NAO Buttercup”.
Hvordan identifisere
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scananbefalinger
skape sammensatt indeks etter felt fra begge de opprinnelige eller utvide ett av de eksisterende med felt fra det andre.
Eksempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Retter opp:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Utbetalingen her er mindre, siden Bitmap Heap Scan er ganske effektiv alene. Men uansett 7 ganger raskere og 2.5 ganger færre lesninger.
#3: Slå sammen indekser (BitmapOr)
Når gjør det
Vis de første 20 eldste "oss" eller ikke-tildelte forespørslene for behandling, med dine i prioritet.
Hvordan identifisere
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scananbefalinger
Bruk UNION [ALLE] å kombinere underspørringer for hver av ELLER-blokkene med betingelser.
Eksempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Retter opp:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Vi utnyttet det faktum at alle de 20 nødvendige postene ble mottatt umiddelbart i den første blokken, så den andre, med den "dyrere" Bitmap Heap Scan, ble ikke engang utført - til slutt 22x raskere, 44x færre avlesninger!
En mer detaljert historie om denne optimaliseringsmetoden ved hjelp av konkrete eksempler kan leses i artikler и .
Generalisert versjon bestilt utvalg basert på flere taster (og ikke bare const/NULL-paret) er omtalt i artikkelen .
#4: Vi leser mye unødvendig
Når gjør det
Som regel oppstår det når du ønsker å "feste et annet filter" til en allerede eksisterende forespørsel.
"Og du har ikke den samme, men med perlemorknapper? » filmen "The Diamond Arm"
For eksempel, ved å endre oppgaven ovenfor, vis de første 20 eldste "kritiske" forespørslene for behandling, uavhengig av formålet.
Hvordan identifisere
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
anbefalinger
Lag [mer] spesialisert indeks med WHERE-tilstand eller inkludere flere felt i indeksen.
Hvis filterbetingelsen er "statisk" for dine formål - altså innebærer ikke utvidelse liste over verdier i fremtiden - det er bedre å bruke en WHERE-indeks. Ulike boolske/enum-statuser passer godt inn i denne kategorien.
Hvis filtreringstilstanden kan få ulike betydninger, da er det bedre å utvide indeksen med disse feltene - som i situasjonen med BitmapAnd ovenfor.
Eksempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Retter opp:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Som du kan se, har filtrering helt forsvunnet fra planen, og forespørselen har blitt 5 ganger raskere.
#5: sparsomt bord
Når gjør det
Ulike forsøk på å lage din egen oppgavebehandlingskø, når et stort antall oppdateringer/slettinger av poster på bordet fører til en situasjon med et stort antall "døde" poster.
Hvordan identifisere
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
anbefalinger
Utfør manuelt regelmessig VAKUUM [FULL] eller oppnå tilstrekkelig hyppig trening ved å finjustere parameterne, inkludert .
I de fleste tilfeller er slike problemer forårsaket av dårlig søkeoppsett når du ringer fra forretningslogikk som de som er omtalt i .
Men du må forstå at selv VAKUUM FULL kanskje ikke alltid hjelper. For slike tilfeller er det verdt å gjøre deg kjent med algoritmen fra artikkelen .
#6: Lesing fra "midten" av indeksen
Når gjør det
Det ser ut til at vi leste litt, og alt ble indeksert, og vi filtrerte ikke ut noen i overkant - men likevel leser vi betydelig flere sider enn vi ønsker.
Hvordan identifisere
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
anbefalinger
Ta en nærmere titt på strukturen til indeksen som brukes og nøkkelfeltene som er spesifisert i spørringen - mest sannsynlig en del av indeksen er ikke satt. Mest sannsynlig må du lage en lignende indeks, men uten prefiksfeltene eller .
Eksempel:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Alt ser ut til å være bra, selv i henhold til indeksen, men det er på en eller annen måte mistenkelig - for hver av de 20 postene som ble lest, måtte vi trekke fra 4 sider med data, 32 KB per post - er ikke det fet? Og indeksnavnet tbl_fk_org_fk_cli_idx tankevekkende.
Retter opp:
CREATE INDEX ON tbl(fk_cli);
Plutselig - 10 ganger raskere, og 4 ganger mindre å lese!
Andre eksempler på situasjoner med ineffektiv bruk av indekser kan sees i artikkelen .
#7: CTE × CTE
Når gjør det
På forespørsel scoret «feit» CTE fra forskjellige bord, og bestemte seg deretter for å gjøre det mellom dem JOIN.
Saken er relevant for versjoner under v12 eller forespørsler med WITH MATERIALIZED.
Hvordan identifisere
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
anbefalinger
Analyser forespørselen nøye - og ? Hvis ja, da bruk "ordbok" i hstore/json i henhold til modellen beskrevet i .
#8: bytt til disk (temp skrevet)
Når gjør det
Engangsbehandling (sortering eller unikisering) av et stort antall poster passer ikke inn i minnet som er tildelt for dette.
Hvordan identifisere
-> *
&& temp written > 0anbefalinger
Hvis mengden minne som brukes av operasjonen ikke i stor grad overstiger den angitte verdien av parameteren , det er verdt å korrigere det. Du kan umiddelbart i konfigurasjonen for alle, eller du kan gjennom SET [LOCAL] for en spesifikk forespørsel/transaksjon.
Eksempel:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Retter opp:
SET work_mem = '128MB'; -- перед выполнением запроса
Av åpenbare grunner, hvis bare minne brukes og ikke disk, vil spørringen bli utført mye raskere. Samtidig fjernes også deler av belastningen fra harddisken.
Men du må forstå at du ikke alltid vil være i stand til å tildele massevis av minne - det vil rett og slett ikke være nok til alle.
#9: irrelevant statistikk
Når gjør det
De helte mye inn i databasen på en gang, men hadde ikke tid til å kjøre det bort ANALYZE.
Hvordan identifisere
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10anbefalinger
Gjennomfør det ANALYZE.
Denne situasjonen er beskrevet mer detaljert i .
#10: "noe gikk galt"
Når gjør det
Det var en venting på en lås pålagt av en konkurrerende forespørsel, eller det var utilstrekkelig CPU/hypervisor-maskinvareressurser.
Hvordan identifisere
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
anbefalinger
Bruk ekstern overvåkningsstystem server for blokkering eller unormalt ressursforbruk. Vi har allerede snakket om vår versjon for å organisere denne prosessen for hundrevis av servere и .
Kilde: www.habr.com