Mapishi ya Maswali ya Wagonjwa ya SQL

Miezi iliyopita tulitangaza eleza.tensor.ru - umma huduma ya kuchanganua na kuibua mipango ya hoja kwa PostgreSQL.

Tayari umeitumia zaidi ya mara 6000, lakini kipengele kimoja muhimu ambacho huenda hakijatambuliwa ni dalili za kimuundo, ambayo inaonekana kama hii:

Wasikilize, na maombi yako “yatakuwa laini na yenye hariri.” 🙂

Lakini kwa umakini, hali nyingi ambazo hufanya ombi kuwa polepole na njaa ya rasilimali ni za kawaida na zinaweza kutambuliwa na muundo na data ya mpango.

Katika kesi hii, kila msanidi programu sio lazima atafute chaguo la uboreshaji peke yake, akitegemea tu uzoefu wake - tunaweza kumwambia kinachotokea hapa, sababu inaweza kuwa nini, na. jinsi ya kushughulikia suluhisho. Ndivyo tulivyofanya.

Hebu tuchunguze kwa undani kesi hizi - jinsi zinavyofafanuliwa na ni mapendekezo gani wanayoongoza.

Ili kuzama vizuri katika mada, unaweza kwanza kusikiliza kizuizi kinacholingana kutoka ripoti yangu katika PGConf.Russia 2020, na kisha tu kuendelea na uchambuzi wa kina wa kila mfano:

#1: index "undersorting"

Wakati gani

Onyesha ankara ya hivi karibuni ya mteja "LLC Kolokolchik".

Jinsi ya kutambua

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

Mapendekezo

Index imetumika kupanua na mashamba ya aina.

Mfano:

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;

[tazama kwenye explain.tensor.ru]

Unaweza kugundua mara moja kuwa rekodi zaidi ya 100 zilitolewa kutoka kwa faharisi, ambazo zote zilipangwa, halafu moja pekee ikabaki.

Kurekebisha:

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

[tazama kwenye explain.tensor.ru]

Hata kwenye sampuli ya zamani kama hii - Mara 8.5 haraka na mara 33 kusoma kidogo. Kadiri unavyokuwa na "ukweli" zaidi kwa kila thamani, ndivyo athari inavyoonekana zaidi fk.

Ninagundua kuwa faharisi kama hiyo itafanya kazi kama faharisi ya "kiambishi awali" sio mbaya zaidi kuliko hapo awali kwa maswali mengine na fk, mahali pa kupanga pk haikuwepo na haipo (unaweza kusoma zaidi kuhusu hili katika makala yangu kuhusu kupata faharisi zisizofaa) Ikiwa ni pamoja na, itatoa kawaida usaidizi wa ufunguo wa kigeni wa wazi kwenye uwanja huu.

#2: makutano ya faharisi (BitmapAnd)

Wakati gani

Onyesha makubaliano yote kwa mteja "LLC Kolokolchik", iliyohitimishwa kwa niaba ya "NAO Buttercup".

Jinsi ya kutambua

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

Mapendekezo

kujenga index ya mchanganyiko kwa shamba kutoka kwa zote mbili asili au kupanua moja ya zilizopo na mashamba kutoka kwa pili.

Mfano:

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

[tazama kwenye explain.tensor.ru]

Kurekebisha:

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

[tazama kwenye explain.tensor.ru]

Malipo hapa ni madogo, kwa kuwa Bitmap Heap Scan ni bora peke yake. Lakini hata hivyo Mara 7 haraka na mara 2.5 kusoma kidogo.

#3: Unganisha faharasa (BitmapOr)

Wakati gani

Onyesha maombi ya kwanza 20 ya zamani zaidi "sisi" au maombi ambayo hayajakabidhiwa ya kuchakatwa, huku yako ikiwa kipaumbele.

Jinsi ya kutambua

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

Mapendekezo

Tumia MUUNGANO [WOTE] kuchanganya maswali madogo kwa kila moja ya OR-blocks ya masharti.

Mfano:

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;

[tazama kwenye explain.tensor.ru]

Kurekebisha:

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

[tazama kwenye explain.tensor.ru]

Tulichukua fursa ya ukweli kwamba rekodi zote 20 zinazohitajika zilipokelewa mara moja kwenye block ya kwanza, kwa hivyo ya pili, na "ghali" zaidi ya Bitmap Heap Scan, haikutekelezwa - mwishowe. 22x kasi, 44x chini ya kusoma!

Hadithi ya kina zaidi kuhusu mbinu hii ya uboreshaji kwa kutumia mifano maalum inaweza kusomwa katika makala PostgreSQL Antipatterns: JOIN hatari na ORs и PostgreSQL Antipatterns: hadithi ya uboreshaji wa mara kwa mara wa utafutaji kwa jina, au "Uboreshaji nyuma na mbele".

Toleo la jumla uteuzi ulioamuru kulingana na funguo kadhaa (na sio tu jozi ya const/NULL) inajadiliwa katika kifungu hicho SQL HowTo: kuandika kitanzi cha muda moja kwa moja kwenye hoja, au "Hatua tatu za Msingi".

#4: Tunasoma mambo mengi yasiyo ya lazima

Wakati gani

Kama sheria, hutokea unapotaka "kuambatisha kichujio kingine" kwa ombi lililopo tayari.

"Na huna sawa, lakini na vifungo vya mama-wa-lulu? " filamu "Mkono wa Diamond"

Kwa mfano, kurekebisha kazi iliyo hapo juu, onyesha maombi 20 ya kwanza "muhimu" ya usindikaji, bila kujali madhumuni yao.

Jinsi ya kutambua

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

Mapendekezo

Unda [zaidi] maalum index na hali WAPI au ujumuishe sehemu za ziada kwenye faharasa.

Ikiwa hali ya chujio ni "tuli" kwa madhumuni yako - yaani haimaanishi upanuzi orodha ya maadili katika siku zijazo - ni bora kutumia WHERE index. Hali mbalimbali za boolean/enum zinafaa katika kitengo hiki.

Ikiwa hali ya kuchuja inaweza kuchukua maana tofauti, basi ni bora kupanua faharisi na nyanja hizi - kama ilivyo katika BitmapAnd hapo juu.

Mfano:

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;

[tazama kwenye explain.tensor.ru]

Kurekebisha:

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

[tazama kwenye explain.tensor.ru]

Kama unaweza kuona, kuchuja kumetoweka kabisa kutoka kwa mpango, na ombi limekuwa Mara 5 kwa kasi zaidi.

#5: meza ndogo

Wakati gani

Majaribio mbalimbali ya kuunda foleni yako ya usindikaji wa kazi, wakati idadi kubwa ya sasisho / ufutaji wa rekodi kwenye meza husababisha hali ya idadi kubwa ya rekodi "zilizokufa".

Jinsi ya kutambua

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

Mapendekezo

Fanya kwa mikono mara kwa mara tupu [FULL] au kupata mafunzo ya mara kwa mara vya kutosha otomatiki kwa kurekebisha vyema vigezo vyake, ikiwa ni pamoja na kwa meza maalum.

Katika hali nyingi, shida kama hizo husababishwa na mpangilio mbaya wa hoja wakati wa kupiga simu kutoka kwa mantiki ya biashara kama zile zilizojadiliwa ndani Antipatterns za PostgreSQL: kupigana na kundi la "wafu".

Lakini unahitaji kuelewa kuwa hata VACUUM FULL haiwezi kusaidia kila wakati. Kwa hali kama hizi, inafaa kujijulisha na algorithm kutoka kwa kifungu DBA: wakati VACUUM inashindwa, tunasafisha meza kwa mikono.

#6: Kusoma kutoka "katikati" ya faharasa

Wakati gani

Inaonekana tulisoma kidogo, na kila kitu kiliorodheshwa, na hatukuchuja mtu yeyote zaidi - lakini bado tulisoma kurasa nyingi zaidi kuliko vile tungependa.

Jinsi ya kutambua

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

Mapendekezo

Angalia kwa karibu muundo wa faharisi iliyotumiwa na sehemu muhimu zilizoainishwa kwenye swali - uwezekano mkubwa sehemu ya faharasa haijawekwa. Uwezekano mkubwa zaidi utalazimika kuunda faharisi inayofanana, lakini bila sehemu za kiambishi awali au jifunze kurudia maadili yao.

Mfano:

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;

[tazama kwenye explain.tensor.ru]

Kila kitu kinaonekana kuwa sawa, hata kulingana na faharisi, lakini ni ya kutiliwa shaka - kwa kila rekodi 20 zilizosomwa, tulilazimika kutoa kurasa 4 za data, 32 KB kwa rekodi - sio ujasiri? Na jina la index tbl_fk_org_fk_cli_idx ya kufikirisha.

Kurekebisha:

CREATE INDEX ON tbl(fk_cli);

[tazama kwenye explain.tensor.ru]

Ghafla - Mara 10 haraka, na mara 4 chini ya kusoma!

Mifano mingine ya hali ya matumizi yasiyofaa ya fahirisi inaweza kuonekana katika makala DBA: kutafuta faharisi zisizo na maana.

#7: CTE × CTE

Wakati gani

Katika ombi alifunga "mafuta" CTE kutoka kwa meza tofauti, na kisha kuamua kuifanya kati yao JOIN.

Kesi hiyo inafaa kwa matoleo yaliyo chini ya v12 au maombi na WITH MATERIALIZED.

Jinsi ya kutambua

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

Mapendekezo

Kuchambua kwa makini ombi - na Je, CTE zinahitajika hapa kabisa?? Ikiwa ndio, basi tumia "kamusi" katika hstore/json kulingana na mfano ulioelezewa katika PostgreSQL Antipatterns: wacha tupige JOIN nzito na kamusi.

#8: badilisha hadi diski ( temp imeandikwa)

Wakati gani

Usindikaji wa wakati mmoja (kupanga au upekee) wa idadi kubwa ya rekodi hauingii kwenye kumbukumbu iliyotengwa kwa hili.

Jinsi ya kutambua

-> *
   && temp written > 0

Mapendekezo

Ikiwa kiasi cha kumbukumbu kinachotumiwa na operesheni haizidi sana thamani maalum ya parameter kazi_mem, inafaa kusahihisha. Unaweza mara moja katika usanidi wa kila mtu, au unaweza kupitia SET [LOCAL] kwa ombi/shughuli maalum.

Mfano:

SHOW work_mem;
-- "16MB"

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

[tazama kwenye explain.tensor.ru]

Kurekebisha:

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

[tazama kwenye explain.tensor.ru]

Kwa sababu za wazi, ikiwa kumbukumbu tu hutumiwa na sio diski, basi swala itatekelezwa kwa kasi zaidi. Wakati huo huo, sehemu ya mzigo kutoka kwa HDD pia imeondolewa.

Lakini unahitaji kuelewa kuwa hautaweza kutenga kumbukumbu nyingi kila wakati - hakutakuwa na kutosha kwa kila mtu.

#9: takwimu zisizo na maana

Wakati gani

Walimwaga mengi kwenye hifadhidata mara moja, lakini hawakuwa na wakati wa kuifukuza ANALYZE.

Jinsi ya kutambua

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

Mapendekezo

Ifanye ANALYZE.

Hali hii inaelezewa kwa undani zaidi katika PostgreSQL Antipatterns: takwimu ni kila kitu.

#10: "hitilafu imetokea"

Wakati gani

Kulikuwa na kusubiri kwa kufuli iliyowekwa na ombi shindani, au hakukuwa na rasilimali za kutosha za vifaa vya CPU/hypervisor.

Jinsi ya kutambua

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

Mapendekezo

Tumia nje mfumo wa ufuatiliaji seva ya kuzuia au matumizi yasiyo ya kawaida ya rasilimali. Tayari tumezungumza kuhusu toleo letu la kupanga mchakato huu kwa mamia ya seva hapa и hapa.

Chanzo: mapenzi.com