Roinnt míonna ó shin - poiblí chuig PostgreSQL.
D'úsáid tú é níos mó ná 6000 uair cheana féin, ach gné áisiúil amháin a d'fhéadfadh nár tugadh faoi deara é leideanna struchtúracha, a bhreathnaíonn rud mar seo:
Éist leo, agus beidh do chuid iarrataí "réidh agus síodaí." 🙂
Ach dáiríre, is iomaí cás a fhágann go bhfuil iarratas mall agus ocras ar acmhainní atá tipiciúil agus is féidir iad a aithint trí struchtúr agus sonraí an phlean.
Sa chás seo, ní gá do gach forbróir aonair rogha leas iomlán a bhaint as a chuid féin a lorg, ag brath go hiomlán ar a thaithí - is féidir linn a insint dó cad atá ag tarlú anseo, cad a d'fhéadfadh a bheith ina chúis, agus conas teacht ar réiteach. Sin a rinne muid.
Breathnaímis níos géire ar na cásanna seo - conas a shainítear iad agus cad iad na moltaí as a dtagann siad.
Chun tú féin a thumadh níos fearr san ábhar, is féidir leat éisteacht leis an mbloc comhfhreagrach ó , agus gan ach ansin bogadh ar aghaidh chuig anailís mhionsonraithe ar gach sampla:
#1: innéacs “undersorting”
Nuair a thagann sé chun cinn
Taispeáin an sonrasc is déanaí don chliant "LLC Kolokolchik".
Conas a aithint
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Moltaí
Innéacs a úsáidtear leathnú le réimsí sórtála.
Sampla:
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;
Is féidir leat a thabhairt faoi deara láithreach gur baineadh níos mó ná 100 taifead ón innéacs, a shórtáil go léir ansin, agus ansin fágadh an t-aon cheann.
Ag ceartú:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Fiú ar shampla den sórt sin primitive - 8.5 uair níos tapúla agus 33 huaire níos lú léamh. Dá mhéad “fíricí” atá agat do gach luach, is léir an éifeacht fk.
Tugaim faoi deara go n-oibreoidh a leithéid d’innéacs mar innéacs “réimír” ní ba mheasa ná roimhe do cheisteanna eile le fk, áit a shórtáil de réir pk ní raibh agus ní raibh (is féidir leat tuilleadh a léamh faoi seo ). Lena n-áirítear, soláthróidh sé gnáth tacaíocht eochair choigríche shainráite ar an bpáirc seo.
#2: crosbhealach innéacs (BitmapAnd)
Nuair a thagann sé chun cinn
Taispeáin gach comhaontú don chliant “LLC Kolokolchik”, a tugadh i gcrích ar son “NAO Buttercup”.
Conas a aithint
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanMoltaí
a chruthú innéacs ilchodach de réir réimsí ón dá cheann bhunaidh nó leathnaigh ceann amháin de na cinn atá ann cheana féin le réimsí ón dara ceann.
Sampla:
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); -- отбор по конкретной паре
Ag ceartú:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Tá an íocaíocht airgid níos lú anseo, ós rud é go bhfuil Bitmap Heap Scan éifeachtach go leor leis féin. Ach mar sin féin 7 uair níos tapúla agus 2.5 huaire níos lú léamh.
#3: Cumaisc innéacsanna (BitmapOr)
Nuair a thagann sé chun cinn
Taispeáin an chéad 20 iarratas is sine “sinn” nó neamhshannta le haghaidh próiseála, agus tabhair tosaíocht duitse.
Conas a aithint
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanMoltaí
Úsáid AONTAS [GACH] chun subqueries a chomhcheangal le haghaidh gach ceann de na OR-bloic coinníollacha.
Sampla:
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;
Ag ceartú:
(
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, больше и не надо
Bhaineamar leas as go bhfuarthas na 20 taifead riachtanacha go léir láithreach sa chéad bhloc, agus mar sin níor cuireadh an dara ceann, leis an Scanadh Bitmap Heap a bhí níos “daor”, fiú - sa deireadh. 22x níos tapúla, 44x níos lú léamh!
Scéal níos mionsonraithe faoin modh leas iomlán a bhaint seo ag baint úsáide as samplaí ar leith is féidir a léamh in ailt и .
Leagan ginearálaithe roghnú ordaithe bunaithe ar eochracha éagsúla (agus ní hamháin an const/NULL péire) a phléitear san alt .
#4: Léimid go leor rudaí gan ghá
Nuair a thagann sé chun cinn
De ghnáth, tarlaíonn sé nuair is mian leat “scagaire eile” a cheangal le hiarratas atá ann cheana féin.
“Agus níl an ceann céanna agat, ach le cnaipí máthair-de-Pearl? " scannán "The Diamond Arm"
Mar shampla, agus an tasc thuas á mhodhnú, taispeáin na chéad 20 iarratas “criticiúla” is sine ar phróiseáil, beag beann ar a gcuspóir.
Conas a aithint
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Moltaí
Cruthaigh [níos mó] speisialaithe innéacs le coinníoll WHERE nó cuir réimsí breise san innéacs.
Má tá riocht an scagaire "statach" chun do chríocha - is é sin ní chiallaíonn leathnú liosta de na luachanna sa todhchaí - tá sé níos fearr a úsáid innéacs WHERE. Tagann stádais boolean/enum éagsúla go maith leis an gcatagóir seo.
Má tá an riocht scagtha is féidir le bríonna éagsúla a ghlacadh, ansin is fearr an t-innéacs a leathnú leis na réimsí seo - mar atá sa chás le BitmapAnd thuas.
Sampla:
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;
Ag ceartú:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Mar a fheiceann tú, tá an scagadh imithe go hiomlán ón bplean, agus tá an t-iarratas tar éis éirí 5 huaire níos tapúla.
#5: tábla gann
Nuair a thagann sé chun cinn
Iarrachtaí éagsúla chun do scuaine próiseála tascanna féin a chruthú, nuair a bhíonn líon mór taifead “marbh” mar thoradh ar líon mór nuashonruithe/scriosadh taifead ar an tábla.
Conas a aithint
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Moltaí
Déan é de láimh go rialta Folamh [IOMLÁN] nó oiliúint sách minic a bhaint amach trí mhionchoigeartú a dhéanamh ar a paraiméadair, lena n-áirítear .
I bhformhór na gcásanna, is de bharr droch-chomhdhéanamh fiosrúcháin is cúis le fadhbanna den sórt sin agus iad ag glaoch ó loighic ghnó cosúil leis na cinn a pléadh i .
Ach ní mór duit a thuiscint go mb'fhéidir nach gcabhróidh fiú folúntas iomlán leat. I gcásanna den sórt sin, is fiú eolas a chur ar an algartam ón alt .
#6: Léamh ó “lár” an innéacs
Nuair a thagann sé chun cinn
Dealraíonn sé gur léigh muid beagán, agus bhí gach rud innéacsaithe, agus níor rinne muid scagadh ar aon duine sa bhreis - ach fós léigh muid i bhfad níos mó leathanaigh ná mar ba mhaith linn.
Conas a aithint
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Moltaí
Breathnaigh go géar ar struchtúr an innéacs a úsáideadh agus ar na príomhréimsí a shonraítear sa cheist - is dócha níl cuid den innéacs socraithe. Is dócha go mbeidh ort innéacs comhchosúil a chruthú, ach gan na réimsí réimír nó .
Sampla:
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;
Is cosúil go bhfuil gach rud ceart go leor, fiú de réir an innéacs, ach tá sé amhrasach ar bhealach éigin - i gcás gach ceann de na 20 taifead a léadh, bhí orainn 4 leathanach de shonraí a dhealú, 32KB in aghaidh an taifid - nach bhfuil sé sin dána? Agus an t-ainm innéacs tbl_fk_org_fk_cli_idx smaoinimh.
Ag ceartú:
CREATE INDEX ON tbl(fk_cli);
Go tobann - 10 n-uaire níos tapúla, agus 4 huaire níos lú le léamh!
Tá samplaí eile de chásanna ina bhfuil úsáid neamhéifeachtach innéacsanna le feiceáil san alt .
#7: CTE × CTE
Nuair a thagann sé chun cinn
Ar iarratas scóráil “saille” CTE ó táblaí éagsúla, agus ansin chinn chun é a dhéanamh eatarthu JOIN.
Tá an cás ábhartha do leaganacha thíos v12 nó iarratais le WITH MATERIALIZED.
Conas a aithint
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Moltaí
Déan anailís chúramach ar an iarratas - agus ? Má tá, ansin cuir "foclóir" i bhfeidhm in hstore/json de réir an mhúnla a gcuirtear síos air i .
#8: babhtáil go diosca (teocht scríofa)
Nuair a thagann sé chun cinn
Ní luíonn próiseáil aonuaire (sórtáil nó uathúlacht) líon mór taifead leis an gcuimhne a leithdháiltear chuige seo.
Conas a aithint
-> *
&& temp written > 0Moltaí
Mura sáraíonn an méid cuimhne a úsáideann an oibríocht go mór luach sonraithe an pharaiméadar , is fiú é a cheartú. Is féidir leat láithreach sa config do gach duine, nó is féidir leat tríd SET [LOCAL] le haghaidh iarratais/idirbheart ar leith.
Sampla:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Ag ceartú:
SET work_mem = '128MB'; -- перед выполнением запроса
Ar chúiseanna soiléire, mura n-úsáidtear ach cuimhne agus ní diosca, cuirfear an cheist i gcrích i bhfad níos tapúla. Ag an am céanna, baintear cuid den ualach ón HDD freisin.
Ach caithfidh tú a thuiscint nach mbeidh tú in ann go leor agus go leor cuimhne a leithdháileadh i gcónaí - ní bheidh go leor ann do gach duine.
#9: staitisticí nach mbaineann le hábhar
Nuair a thagann sé chun cinn
Doirt siad go leor isteach sa bhunachar sonraí láithreach, ach ní raibh am acu é a thiomáint uaidh ANALYZE.
Conas a aithint
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Moltaí
Déan é ANALYZE.
Déantar cur síos níos mine ar an gcás seo i .
#10: "Tharla rud éigin mícheart"
Nuair a thagann sé chun cinn
Bhíothas ag feitheamh le glas a fhorchuirtear le hiarratas iomaíoch, nó ní raibh dóthain acmhainní crua-earraí CPU/hypervisor.
Conas a aithint
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Moltaí
Úsáid seachtrach córas monatóireachta freastalaí le haghaidh blocála nó tomhaltas neamhghnách acmhainní. Táimid tar éis labhairt cheana féin faoinár leagan den phróiseas seo a eagrú do na céadta freastalaithe и .
Foinse: will.com