Uppskriftir að sjúkum SQL fyrirspurnum

Fyrir nokkrum mánuðum við tilkynntum útskýra.tensor.ru - opinbert þjónusta til að flokka og sýna fyrirspurnaáætlanir til PostgreSQL.

Þú hefur nú þegar notað það yfir 6000 sinnum síðan þá, en einn af handhægu eiginleikum gæti hafa farið óséður er burðarvirki vísbendingar, sem líta einhvern veginn svona út:

Hlustaðu á þær og beiðnir þínar verða "silkimjúkar". 🙂

En í alvöru, margar aðstæður sem gera beiðni hæga og „fátæka“ hvað varðar úrræði, eru dæmigerð og hægt er að þekkja þær á uppbyggingu og gögnum áætlunarinnar.

Í þessu tilviki mun hver einstakur verktaki ekki þurfa að leita að hagræðingarvalkosti á eigin spýtur, eingöngu að treysta á eigin reynslu - við getum sagt honum hvað er að gerast hér, hver gæti verið ástæðan og hvernig á að finna lausn. Sem er það sem við gerðum.

Skoðum þessi mál nánar - hvernig þau eru skilgreind og til hvaða tilmæla þau leiða.

Til að komast betur inn í efnið geturðu fyrst hlustað á samsvarandi blokk frá skýrslan mín á PGConf.Russia 2020, og aðeins þá fara í ítarlega greiningu á hverju dæmi:

#1: vísitölu "undirflokkun"

Hvenær gerir

Sýndu síðasta reikning fyrir viðskiptavininn "LLC Kolokolchik".

Hvernig á að bera kennsl á

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

Tillögur

Vísitalan notuð stækkaðu með flokkunarreitum.

Dæmi:

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;

[horfðu á explain.tensor.ru]

Þú getur strax tekið eftir því að meira en 100 færslur voru dregnar frá vísitölunni, sem síðan voru allar flokkaðar, og þá var aðeins ein eftir.

Við laga:

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

[horfðu á explain.tensor.ru]

Jafnvel á svo frumstæðu sýni - 8.5x hraðar og 33x færri lestur. Áhrifin verða skýrari, því fleiri "staðreyndir" sem þú hefur fyrir hvert gildi. fk.

Ég tek fram að slík vísitala mun virka sem „forskeyti“ vísitala ekki verri en sú fyrri fyrir aðrar fyrirspurnir með fk, þar sem flokkað er eftir pk var ekki og er ekki (þú getur lesið meira um þetta í grein minni um að finna óhagkvæmar vísitölur). Einkum mun það veita eðlilegt beinan erlendan lykilstuðning við þennan reit.

#2: vísitölumót (BitmapAnd)

Hvenær gerir

Sýna alla samninga fyrir viðskiptavininn "LLC Kolokolchik" sem gerðir eru fyrir hönd "NJSC Lyutik".

Hvernig á að bera kennsl á

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

Tillögur

búa til samsett vísitala eftir reitum frá báðum uppruna eða stækkaðu einn af núverandi reitum frá öðrum.

Dæmi:

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

[horfðu á explain.tensor.ru]

Við laga:

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

[horfðu á explain.tensor.ru]

Hér er hagnaðurinn minni, þar sem Bitmap Heap Scan er nokkuð árangursríkt eitt og sér. En allavega 7x hraðar og 2.5x færri lestur.

#3: Sameina vísitölur (BitmapOr)

Hvenær gerir

Sýna fyrstu 20 elstu „eigin“ eða óúthlutaðar beiðnir um vinnslu, með eigin í forgangi.

Hvernig á að bera kennsl á

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

Tillögur

Notaðu UNION [ALLIR] til að sameina undirfyrirspurnir fyrir hvert skilyrði EÐA blokkir.

Dæmi:

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;

[horfðu á explain.tensor.ru]

Við laga:

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

[horfðu á explain.tensor.ru]

Við nýttum okkur þá staðreynd að allar 20 nauðsynlegar færslur voru fengnar strax í fyrstu blokkinni, þannig að sú seinni, með „dýrari“ Bitmap Heap Scan, var ekki einu sinni framkvæmd - þar af leiðandi 22x hraðar, 44x færri lestur!

Nánari saga um þessa hagræðingaraðferð um áþreifanleg dæmi má lesa í greinum PostgreSQL andmynstur: Skaðleg JOIN og OR и PostgreSQL Antipatterns: A Tale of Iterative Refinement of Search by Name, eða „Bjartsýni fram og til baka“.

Almenn útgáfa pantað val eftir nokkrum lyklum (og ekki bara fyrir par af const / NULL) er fjallað um í greininni SQL HowTo: skrifa while-lykkju beint í fyrirspurnina, eða „Elementary three-way“.

#4: Við lesum of mikið

Hvenær gerir

Að jafnaði gerist það þegar þú vilt „tengja aðra síu“ við fyrirliggjandi beiðni.

„Og þú hefur ekki það sama, en með perluhnöppum? " kvikmynd "Diamond Hand"

Til dæmis, með því að breyta verkefninu hér að ofan, sýna fyrstu 20 elstu „mikilvægu“ beiðnirnar um vinnslu, óháð tilgangi þeirra.

Hvernig á að bera kennsl á

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

Tillögur

Búðu til [meira] sérhæfða vísitölu með WHERE ákvæði eða hafa fleiri reiti í skránni.

Ef síunarskilyrðið er "statískt" fyrir verkefni þín - það er felur ekki í sér stækkun lista yfir gildi í framtíðinni - það er betra að nota WHERE vísitölu. Ýmsar boolean/enum stöður passa vel inn í þennan flokk.

Ef síunarástandið getur tekið á sig mismunandi gildi, það er betra að stækka vísitöluna með þessum reitum - eins og í stöðunni með BitmapAnd hér að ofan.

Dæmi:

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;

[horfðu á explain.tensor.ru]

Við laga:

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

[horfðu á explain.tensor.ru]

Eins og þú sérð er síunin frá áætluninni alveg horfin og beiðnin orðin 5 sinnum hraðar.

#5: dreifður borð

Hvenær gerir

Ýmsar tilraunir til að búa til þína eigin verkefnavinnslu biðröð, þegar mikill fjöldi uppfærslna / eyðingar skráa á borðinu leiðir til þess að fjöldi "dauðra" skráa er að finna.

Hvernig á að bera kennsl á

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

Tillögur

Handvirkt framkvæma reglulega VAKUUM [FULLT] eða ná nægilega tíðri vinnslu sjálfvirkt tómarúm með því að fínstilla færibreytur þess, þ.m.t fyrir tiltekið borð.

Í flestum tilfellum stafa þessi vandamál af lélegu skipulagi fyrirspurna þegar hringt er frá viðskiptarökfræði, eins og þeim sem fjallað er um í PostgreSQL Antipatterns: berjast við hjörð af „dauðum“.

En við verðum að skilja að jafnvel VACUUM FULL getur ekki alltaf hjálpað. Í slíkum tilvikum ættir þú að kynna þér reikniritið úr greininni. DBA: þegar VACUUM líður, þrífum við borðið handvirkt.

#6: lesið úr "miðju" vísitölunnar

Hvenær gerir

Það virðist sem þeir hafi lesið aðeins og allt var skráð og þeir síuðu ekki neinn aukalega - en samt voru töluvert fleiri síður lesnar en við viljum.

Hvernig á að bera kennsl á

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

Tillögur

Skoðaðu vel uppbyggingu vísitölunnar sem notuð er og lykilreitin sem tilgreind eru í fyrirspurninni - líklegast, vísitöluhluti ekki stilltur. Þú þarft líklegast að búa til svipaða vísitölu, en án forskeytisreita, eða læra að endurtaka gildi þeirra.

Dæmi:

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;

[horfðu á explain.tensor.ru]

Allt virðist vera í lagi, jafnvel hvað varðar vísitöluna, en einhvern veginn grunsamlegt - fyrir hverja af 20 lesnum færslum þurfti að draga 4 síður af gögnum frá, 32KB á hverja skrá - er það ekki feitletrað? Já og vísitöluheiti tbl_fk_org_fk_cli_idx leiðir til umhugsunar.

Við laga:

CREATE INDEX ON tbl(fk_cli);

[horfðu á explain.tensor.ru]

Allt í einu - 10 sinnum hraðar og 4 sinnum minna að lesa!

Fyrir fleiri dæmi um óhagkvæma notkun vísitölu, sjá greinina DBA: finndu gagnslausar vísitölur.

#7: CTE × CTE

Hvenær gerir

Í beiðni skoraði "feit" CTE frá mismunandi borðum, og ákvað síðan að gera á milli þeirra JOIN.

Málið á við fyrir útgáfur undir v12 eða beiðnir með WITH MATERIALIZED.

Hvernig á að bera kennsl á

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

Tillögur

Greindu beiðnina vandlega er yfirleitt þörf á CTE hér? Ef já, þá notaðu "orðabók" í hstore/json samkvæmt fyrirmyndinni sem lýst er í PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN.

#8: skipta yfir á disk (tímarit skrifað)

Hvenær gerir

Einskiptisvinnsla (flokkun eða sérhæfing) á miklum fjölda skráa passar ekki inn í minnið sem úthlutað er fyrir þetta.

Hvernig á að bera kennsl á

-> *
   && temp written > 0

Tillögur

Ef minnismagnið sem aðgerðin notar fer ekki mikið yfir sett gildi færibreytunnar vinnu_mem, það ætti að leiðrétta. Þú getur strax í stillingu fyrir alla, eða þú getur í gegnum SET [LOCAL] fyrir tiltekna beiðni/færslu.

Dæmi:

SHOW work_mem;
-- "16MB"

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

[horfðu á explain.tensor.ru]

Við laga:

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

[horfðu á explain.tensor.ru]

Af augljósum ástæðum, ef aðeins minni er notað en ekki diskur, þá verður fyrirspurnin miklu hraðari. Á sama tíma er hluti af álaginu einnig fjarlægður af HDD.

En þú verður að skilja að það að úthluta miklu minni mun alltaf virka heldur - það mun einfaldlega ekki duga fyrir alla.

#9: Óviðkomandi tölfræði

Hvenær gerir

Mikið var hellt í botninn í einu, en þeir höfðu ekki tíma til að reka það í burtu ANALYZE.

Hvernig á að bera kennsl á

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

Tillögur

Eyddu því sama ANALYZE.

Þessu ástandi er lýst nánar í PostgreSQL Antipatterns: tölfræði er höfuðið á öllu.

#10: "eitthvað fór úrskeiðis"

Hvenær gerir

Það var læsing sem beið eftir samkeppnisbeiðni, eða það var ekki nóg CPU/hypervisor vélbúnaðartilföng.

Hvernig á að bera kennsl á

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

Tillögur

Notaðu utanaðkomandi eftirlitskerfi miðlara til að loka fyrir eða óeðlilega auðlindanotkun. Við höfum þegar talað um útgáfu okkar af því að skipuleggja þetta ferli fyrir hundruð netþjóna. hér и hér.

Heimild: www.habr.com