Receptes slimiem SQL vaicājumiem

Pirms mēnešiem mēs paziņojām paskaidrojiet.tensor.ru - publiska pakalpojums vaicājumu plānu parsēšanai un vizualizēšanai uz PostgreSQL.

Kopš tā laika esat to izmantojis vairāk nekā 6000 reižu, taču viena no noderīgajām funkcijām, iespējams, ir palikusi nepamanīta. strukturālas norādes, kas izskatās apmēram šādi:

Klausieties tos, un jūsu lūgumi "kļūs zīdaini gludi". 🙂

Bet, ja nopietni, daudzas situācijas, kas padara pieprasījumu lēnu un resursu ziņā “rijīgu”, ir tipiski, un tos var atpazīt pēc plāna struktūras un datiem.

Šajā gadījumā katram atsevišķam izstrādātājam nebūs pašam jāmeklē optimizācijas iespēja, paļaujoties tikai uz savu pieredzi - mēs varam viņam pastāstīt, kas šeit notiek, kas varētu būt iemesls un kā nākt klajā ar risinājumu. Tas ir tas, ko mēs darījām.

Apskatīsim šos gadījumus tuvāk – kā tie tiek definēti un pie kādiem ieteikumiem tie noved.

Lai labāk iedziļinātos tēmā, vispirms varat noklausīties atbilstošo bloku no mans ziņojums PGConf.Russia 2020, un tikai pēc tam pārejiet uz katra piemēra detalizētu analīzi:

#1: indekss "nepietiekama šķirošana"

Kad dara

Parādiet pēdējo rēķinu klientam "LLC Kolokolchik".

Kā identificēt

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

Ieteikumi

Izmantotais rādītājs izvērsiet ar kārtošanas laukiem.

Piemērs:

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;

[apskatiet skaidro.tensor.ru]

Uzreiz var pamanīt, ka no indeksa tika atņemti vairāk nekā 100 ieraksti, kas pēc tam tika sakārtoti, un tad palika tikai viens.

Mēs labojam:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[apskatiet skaidro.tensor.ru]

Pat tik primitīvā paraugā - 8.5 reizes ātrāk un 33 reizes mazāk lasīšanas. Efekts būs skaidrāks, jo vairāk "faktu" jums būs par katru vērtību. fk.

Es atzīmēju, ka šāds indekss darbosies kā “prefiksa” indekss, kas nav sliktāks par iepriekšējo citiem vaicājumiem ar fk, kur šķirošana pēc pk nebija un nav (par to varat lasīt vairāk manā rakstā par neefektīvu indeksu atrašanu). Jo īpaši tas nodrošinās normālu skaidrs ārējās atslēgas atbalsts šajā jomā.

#2: indeksa krustojums (BitmapAnd)

Kad dara

Parādīt visus klienta "LLC Kolokolchik" līgumus, kas noslēgti "NJSC Lyutik" vārdā.

Kā identificēt

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Ieteikumi

izveidot salikts indekss pēc laukiem no abiem avotiem vai izvērsiet vienu no esošajiem laukiem no otrā.

Piemērs:

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); -- отбор по конкретной паре

[apskatiet skaidro.tensor.ru]

Mēs labojam:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[apskatiet skaidro.tensor.ru]

Šeit ieguvums ir mazāks, jo bitkartes kaudzes skenēšana pati par sevi ir diezgan efektīva. Bet vienalga 7 reizes ātrāk un 2.5 reizes mazāk lasīšanas.

3: indeksu apvienošana (BitmapOr)

Kad dara

Rādīt pirmos 20 vecākos "savējos" vai nepiešķirtos apstrādes pieprasījumus ar prioritāti saviem.

Kā identificēt

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Ieteikumi

Lietot SAVIENĪBA [VISI] lai apvienotu apakšvaicājumus katram nosacījuma VAI blokam.

Piemērs:

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;

[apskatiet skaidro.tensor.ru]

Mēs labojam:

(
  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, больше и не надо

[apskatiet skaidro.tensor.ru]

Mēs izmantojām to, ka pirmajā blokā uzreiz tika iegūti visi 20 nepieciešamie ieraksti, tāpēc otrais ar “dārgāko” Bitmap Heap Scan pat netika izpildīts – rezultātā 22x ātrāk, 44x mazāk lasīšanas!

Plašāks stāsts par šo optimizācijas metodi par konkrētiem piemēriem var lasīt rakstos PostgreSQL antipatterns: kaitīgi JOIN un OR и PostgreSQL antipatterns: stāsts par iteratīvu meklēšanas pēc nosaukuma precizēšanu jeb "optimizēšanu uz priekšu un atpakaļ".

Vispārināta versija sakārtota atlase ar vairākiem taustiņiem (un ne tikai par const / NULL pāri) ir apspriests rakstā SQL HowTo: ierakstiet kamēr cilpu tieši vaicājumā vai "Elementary trīsceļu".

#4: Mēs lasām pārāk daudz

Kad dara

Parasti tas notiek, ja vēlaties esošam pieprasījumam “pievienot citu filtru”.

"Un jums nav tas pats, bet ar pērļu pogām? " filma "Dimanta roka"

Piemēram, mainot iepriekš minēto uzdevumu, parādiet pirmos 20 vecākos "kritiskos" apstrādes pieprasījumus neatkarīgi no to mērķa.

Kā identificēt

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

Ieteikumi

Izveidojiet [vairāk] specializētu indekss ar WHERE klauzulu vai iekļaut rādītājā papildu laukus.

Ja filtrēšanas nosacījums jūsu uzdevumiem ir "statisks", tas ir neietver paplašināšanu vērtību saraksts nākotnē - labāk ir izmantot WHERE indeksu. Šajā kategorijā labi iederas dažādi Būla/enum statusi.

Ja filtrēšanas stāvoklis var pieņemt dažādas vērtības, labāk ir paplašināt indeksu ar šiem laukiem - kā tas ir situācijā ar BitmapAnd iepriekš.

Piemērs:

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;

[apskatiet skaidro.tensor.ru]

Mēs labojam:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[apskatiet skaidro.tensor.ru]

Kā redzat, filtrēšana no plāna ir pilnībā pazudusi, un pieprasījums ir kļuvis 5 reizes ātrāk.

#5: rets galds

Kad dara

Dažādi mēģinājumi izveidot savu uzdevumu apstrādes rindu, kad liels skaits ierakstu atjauninājumu/dzēšanas uz galda noved pie situācijas, kad liels skaits "mirušo" ierakstu.

Kā identificēt

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Ieteikumi

Regulāri veiciet manuāli VAKUUMS [PILNS] vai panākt pietiekami biežu apstrādi autovakuums precizējot tā parametrus, t.sk konkrētam galdam.

Vairumā gadījumu šādas problēmas izraisa slikts vaicājuma izkārtojums, kad tiek izsaukts no biznesa loģikas, piemēram, tie, kas apspriesti PostgreSQL antipatterns: cīņa ar "mirušo" bariem.

Bet mums ir jāsaprot, ka pat VACUUM FULL ne vienmēr var palīdzēt. Šādos gadījumos jums vajadzētu iepazīties ar algoritmu no raksta. DBA: kad VACUUM pāriet, mēs tīrām galdu manuāli.

#6: lasīšana no indeksa "vidus".

Kad dara

Šķiet, ka viņi lasīja nedaudz, un viss tika indeksēts, un viņi nevienu papildus nefiltrēja - tomēr tika izlasīts ievērojami vairāk lapu, nekā mēs vēlētos.

Kā identificēt

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Ieteikumi

Rūpīgi apskatiet izmantotā indeksa struktūru un vaicājumā norādītos galvenos laukus - visticamāk, indeksa daļa nav iestatīta. Visticamāk, jums būs jāizveido līdzīgs rādītājs, bet bez prefiksu laukiem vai iemācīties atkārtot savas vērtības.

Piemērs:

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;

[apskatiet skaidro.tensor.ru]

Šķiet, ka viss ir kārtībā, pat indeksa ziņā, bet kaut kā aizdomīgi - par katru no 20 izlasītajiem ierakstiem bija jāatņem 4 lapas datu, 32KB par ierakstu - vai tas nav treknrakstā? Jā un indeksa nosaukums tbl_fk_org_fk_cli_idx vedina uz pārdomām.

Mēs labojam:

CREATE INDEX ON tbl(fk_cli);

[apskatiet skaidro.tensor.ru]

Pēkšņi - 10 reizes ātrāk un 4 reizes mazāk lasīt!

Vairāk piemēru par neefektīvu indeksu izmantošanu skatiet rakstā DBA: atrodiet bezjēdzīgus indeksus.

#7: CTE × CTE

Kad dara

Pēc pieprasījuma ieguva "resno" CTE no dažādām tabulām, un tad nolēma darīt starp tām JOIN.

Lieta attiecas uz versijām, kas vecākas par v12, vai pieprasījumiem ar WITH MATERIALIZED.

Kā identificēt

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

Ieteikumi

Rūpīgi analizējiet pieprasījumu vai šeit vispār ir vajadzīgi CTE? Ja jā, tad lietot "vārdnīcu" hstore/json saskaņā ar aprakstīto modeli PostgreSQL antipatterns: Dictionary Hit Heavy JOIN.

#8: pārslēgt uz disku (rakstīta temp.)

Kad dara

Liela skaita ierakstu vienreizēja apstrāde (šķirošana vai unikalizācija) neietilpst šim atvēlētajā atmiņā.

Kā identificēt

-> *
   && temp written > 0

Ieteikumi

Ja operācijai izmantotās atmiņas apjoms ievērojami nepārsniedz parametra iestatīto vērtību darba_atmiņa, tas ir jālabo. Varat nekavējoties iestatīt konfigurāciju visiem, vai arī varat to izdarīt SET [LOCAL] konkrētam pieprasījumam/darījumam.

Piemērs:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[apskatiet skaidro.tensor.ru]

Mēs labojam:

SET work_mem = '128MB'; -- перед выполнением запроса

[apskatiet skaidro.tensor.ru]

Acīmredzamu iemeslu dēļ, ja tiek izmantota tikai atmiņa, nevis disks, vaicājums būs daudz ātrāks. Tajā pašā laikā daļa slodzes tiek noņemta arī no HDD.

Bet jums ir jāsaprot, ka arī lielas atmiņas piešķiršana vienmēr nedarbosies - ar to vienkārši nepietiks visiem.

#9: neatbilstoša statistika

Kad dara

Bāzē uzreiz tika ieliets daudz, bet viņiem nebija laika to padzīt ANALYZE.

Kā identificēt

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

Ieteikumi

Tērē tikpat ANALYZE.

Šī situācija ir sīkāk aprakstīta rakstā PostgreSQL antipatterns: statistika ir visa pamatā.

#10: "kaut kas nogāja greizi"

Kad dara

Bija bloķēts, kas gaidīja konkurējošu pieprasījumu, vai arī nebija pietiekami daudz CPU/hipervizora aparatūras resursu.

Kā identificēt

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

Ieteikumi

Izmantojiet ārēju uzraudzības sistēma serveris bloķēšanai vai nenormālam resursu patēriņam. Mēs jau esam runājuši par mūsu versiju, kā organizēt šo procesu simtiem serveru. šeit и šeit.

Avots: www.habr.com