Disa muaj më parë - publike në PostgreSQL.
Ju e keni përdorur atë mbi 6000 herë që atëherë, por një nga veçoritë e dobishme mund të ketë kaluar pa u vënë re është të dhëna strukturore, të cilat duken diçka si kjo:
Dëgjoni ata dhe kërkesat tuaja do të "bëhen të qetë". 🙂
Por seriozisht, shumë situata që e bëjnë një kërkesë të ngadaltë dhe "grykëse" për sa i përket burimeve, janë tipike dhe mund të njihen nga struktura dhe të dhënat e planit.
Në këtë rast, çdo zhvillues individual nuk do të duhet të kërkojë një opsion optimizimi më vete, duke u mbështetur vetëm në përvojën e tij - ne mund t'i tregojmë atij se çfarë po ndodh këtu, cila mund të jetë arsyeja dhe si të gjeni një zgjidhje. Kjo është ajo që bëmë.
Le t'i hedhim një vështrim më të afërt në këto raste - si përkufizohen dhe në çfarë rekomandimesh çojnë.
Për një zhytje më të mirë në temë, së pari mund të dëgjoni bllokun përkatës nga , dhe vetëm atëherë shkoni në një analizë të detajuar të secilit shembull:
# 1: indeksi "nënrenditje"
Kur lind
Trego faturën e fundit për klientin "LLC Kolokolchik".
Si të identifikoni
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Rekomandime
Indeksi i përdorur zgjerohet me fushat e renditjes.
Shembull:
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;
Ju mund të vini re menjëherë se më shumë se 100 regjistrime u zbritën nga indeksi, të cilat më pas u renditën të gjitha, dhe më pas mbeti i vetmi.
Ne rregullojmë:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Edhe në një kampion kaq primitiv - 8.5 herë më shpejt dhe 33 herë më pak lexime. Efekti do të jetë më i qartë, aq më shumë "fakte" keni për secilën vlerë. fk.
Unë vërej se një indeks i tillë do të funksionojë si një indeks "prefiks" jo më keq se ai i mëparshmi për pyetje të tjera me fk, ku renditet sipas pk nuk ishte dhe nuk është (mund të lexoni më shumë për këtë ). Në veçanti, ajo do të sigurojë normale mbështetje eksplicite me çelës të huaj nga kjo fushë.
#2: kryqëzimi i indeksit (BitmapAnd)
Kur lind
Trego të gjitha kontratat për klientin "LLC Kolokolchik" të lidhura në emër të "NJSC Lyutik".
Si të identifikoni
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRekomandime
krijoj indeksi i përbërë sipas fushave nga të dy burimet ose zgjeroni njërën nga fushat ekzistuese nga e dyta.
Shembull:
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); -- отбор по конкретной паре
Ne rregullojmë:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Këtu fitimi është më i vogël, pasi Bitmap Heap Scan është mjaft efektiv më vete. Por gjithsesi 7 herë më shpejt dhe 2.5 herë më pak lexime.
#3: Kombinimi i indekseve (BitmapOr)
Kur lind
Trego 20 kërkesat e para më të vjetra "të veta" ose të pacaktuara për përpunim, me përparësi të tyre.
Si të identifikoni
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRekomandime
për t'u përdorur UNION [TE GJITHA] për të kombinuar nënpyetje për secilin prej blloqeve të kushtit OSE.
Shembull:
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;
Ne rregullojmë:
(
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, больше и не надо
Ne shfrytëzuam faktin që të 20 regjistrimet e nevojshme u morën menjëherë në bllokun e parë, kështu që i dyti, me Bitmap Heap Scan më "të shtrenjtë", as nuk u ekzekutua - si rezultat. 22 herë më shpejt, 44 herë më pak lexime!
Një histori më e detajuar në lidhje me këtë metodë optimizimi mbi shembuj konkretë mund të lexohet në artikuj и .
Version i përgjithësuar përzgjedhja e urdhëruar nga disa çelësa (dhe jo vetëm për çiftin const/NULL) diskutohet në artikull .
#4: Ne lexojmë shumë
Kur lind
Si rregull, ndodh kur dëshironi të "bashkoni një filtër tjetër" me një kërkesë ekzistuese.
"Dhe ju nuk keni të njëjtën gjë, por me kopsa perla? " filmi "Dora e Diamantit"
Për shembull, duke modifikuar detyrën e mësipërme, tregoni 20 kërkesat e para "kritike" më të vjetra për përpunim, pavarësisht nga qëllimi i tyre.
Si të identifikoni
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Rekomandime
Krijo [më shumë] të specializuar indeksi me klauzolën WHERE ose përfshini fusha shtesë në indeks.
Nëse gjendja e filtrimit është "statike" për detyrat tuaja - kjo është nuk përfshin zgjerimin lista e vlerave në të ardhmen - është më mirë të përdorni një indeks WHERE. Statuse të ndryshme boolean/enum përshtaten mirë në këtë kategori.
Nëse gjendja e filtrimit mund të marrë vlera të ndryshme, është më mirë të zgjeroni indeksin me këto fusha - si në situatën me BitmapAnd më lart.
Shembull:
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;
Ne rregullojmë:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Siç mund ta shihni, filtrimi nga plani është zhdukur plotësisht dhe kërkesa është bërë 5 herë më shpejt.
#5: tavolinë e rrallë
Kur lind
Përpjekje të ndryshme për të krijuar radhën tuaj të përpunimit të detyrave, kur një numër i madh përditësimesh / fshirjesh të rekordeve në tryezë çojnë në një situatë të një numri të madh regjistrimesh "të vdekura".
Si të identifikoni
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rekomandime
Kryeni me dorë rregullisht VAKUUM [E PLOTË] ose të arrihet përpunim mjaftueshëm i shpeshtë duke rregulluar mirë parametrat e tij, duke përfshirë .
Në shumicën e rasteve, probleme të tilla shkaktohen nga paraqitja e dobët e pyetjeve kur thirren nga logjika e biznesit, siç janë ato të diskutuara në .
Por ne duhet të kuptojmë se edhe VACUUM FULL nuk mund të ndihmojë gjithmonë. Për raste të tilla, duhet të njiheni me algoritmin nga artikulli. .
#6: lexim nga "mesi" i indeksit
Kur lind
Duket se ata lexuan pak, dhe gjithçka u indeksua, dhe ata nuk filtruan askënd shtesë - por megjithatë, u lexuan dukshëm më shumë faqe sesa do të donim.
Si të identifikoni
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rekomandime
Shikoni nga afër strukturën e indeksit të përdorur dhe fushat kryesore të specifikuara në pyetje - ka shumë të ngjarë, pjesa e indeksit nuk është vendosur. Me shumë mundësi do t'ju duhet të krijoni një indeks të ngjashëm, por pa fusha prefiksi, ose .
Shembull:
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;
Gjithçka duket të jetë mirë, edhe për sa i përket indeksit, por disi e dyshimtë - për secilin nga 20 regjistrimet e lexuara, duheshin zbritur 4 faqe të dhënash, 32 KB për çdo regjistrim - a nuk është e guximshme? Po dhe emri i indeksit tbl_fk_org_fk_cli_idx të çon në mendim.
Ne rregullojmë:
CREATE INDEX ON tbl(fk_cli);
Papritur - 10 herë më shpejt dhe 4 herë më pak për të lexuar!
Për më shumë shembuj të përdorimit joefikas të indekseve, shihni artikullin .
#7: CTE × CTE
Kur lind
Në kërkesë shënoi CTE "të majme". nga tavolina të ndryshme, dhe më pas vendosi të bëjë mes tyre JOIN.
Rasti është i rëndësishëm për versionet më poshtë v12 ose kërkesat me WITH MATERIALIZED.
Si të identifikoni
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Rekomandime
Analizoni me kujdes kërkesën ? Nëse po, atëherë aplikoni "dictionary" në hstore/json sipas modelit të përshkruar në .
#8: ndërroni në disk (e shkruar temp)
Kur lind
Përpunimi një herë (rendimi ose unikalizimi) i një numri të madh regjistrimesh nuk përshtatet në memorien e caktuar për këtë.
Si të identifikoni
-> *
&& temp written > 0Rekomandime
Nëse sasia e memories së përdorur nga operacioni nuk e kalon shumë vlerën e caktuar të parametrit , duhet korrigjuar. Ju mund menjëherë në konfigurimin për të gjithë, ose mundeni përmes SET [LOCAL] për një kërkesë/transaksion të caktuar.
Shembull:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Ne rregullojmë:
SET work_mem = '128MB'; -- перед выполнением запроса
Për arsye të dukshme, nëse përdoret vetëm memoria dhe jo disku, atëherë pyetja do të jetë shumë më e shpejtë. Në të njëjtën kohë, një pjesë e ngarkesës hiqet gjithashtu nga HDD.
Por ju duhet të kuptoni se ndarja e shumë memorie nuk do të funksionojë gjithmonë - thjesht nuk do të jetë e mjaftueshme për të gjithë.
#9: Statistikat e parëndësishme
Kur lind
Shumë u derdh në bazë menjëherë, por ata nuk patën kohë ta largonin ANALYZE.
Si të identifikoni
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Rekomandime
Shpenzoni të njëjtën gjë ANALYZE.
Kjo situatë përshkruhet më në detaje në .
#10: "diçka shkoi keq"
Kur lind
Kishte një bllokim duke pritur për një kërkesë konkurruese ose nuk kishte burime të mjaftueshme të harduerit të CPU/hipervizorit.
Si të identifikoni
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Rekomandime
Përdorni të jashtme sistemi i monitorimit server për bllokim ose konsum jonormal të burimeve. Ne kemi folur tashmë për versionin tonë të organizimit të këtij procesi për qindra serverë. и .
Burimi: www.habr.com