Mìosan air ais - poblach gu PostgreSQL.
Tha thu air a chleachdadh mu thràth còrr air 6000 uair, ach is e aon fheart feumail a dh’ fhaodadh a bhith gun mhothachadh comharran structarail, a tha a’ coimhead rudeigin mar seo:
Eisdibh riu, agus fàsaidh bhur n-iarrtasan " rèidh agus sìochail." 🙂
Ach dha-rìribh, tha mòran shuidheachaidhean a bheir iarrtas slaodach agus acrach air goireasan àbhaisteach agus faodar an aithneachadh le structar agus dàta a’ phlana.
Anns a 'chùis seo, chan fheum gach leasaiche fa leth a bhith a' coimhead airson roghainn optimization leotha fhèin, a 'crochadh a-mhàin air an eòlas aige - is urrainn dhuinn innse dha dè a tha a' tachairt an seo, dè a dh'fhaodadh a bhith na adhbhar, agus mar a dhèiligeas tu ri fuasgladh. Sin a rinn sinn.
Bheir sinn sùil nas mionaidiche air na cùisean sin - mar a tha iad air am mìneachadh agus dè na molaidhean a tha iad a’ leantainn.
Gus do bhogadh nas fheàrr sa chuspair, faodaidh tu an toiseach èisteachd ris a’ bhloc fhreagarrach bho , agus dìreach an uairsin gluais air adhart gu mion-sgrùdadh mionaideach air gach eisimpleir:
#1: clàr-amais “fo-ghnè”
Nuair a thig e suas
Seall an fhàirdeal as ùire airson an neach-dèiligidh "LLC Kolokolchik".
Ciamar a chomharrachadh
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
molaidhean
Clàr-innse air a chleachdadh leudachadh le raointean seòrsachaidh.
Eisimpleir:
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;
Faodaidh tu mothachadh sa bhad gun deach còrr air 100 clàr a thoirt air falbh bhon chlàr-amais, a chaidh an rèiteachadh an uairsin, agus an uairsin cha robh ach an aon fhear air fhàgail.
A' ceartachadh:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Eadhon air sampall cho prìomhadail - 8.5 tursan nas luaithe agus 33 tursan nas lugha de leughaidhean. Mar as motha de “fhìrinnean” a th’ agad airson gach luach, is ann as follaisiche a’ bhuaidh fk.
Tha mi a’ toirt fa-near nach obraich clàr-amais mar seo mar chlàr-amais “ro-leasachan” nas miosa na bha e roimhe airson ceistean eile le fk, far a bheil pk cha robh agus chan eil (faodaidh tu barrachd a leughadh mu dheidhinn seo ). A 'gabhail a-steach, bheir e seachad àbhaisteach taic cudromach cèin follaiseach air an raon seo.
#2: eadar-ghearradh clàr-amais (BitmapAnd)
Nuair a thig e suas
Seall a h-uile aonta airson an neach-dèiligidh "LLC Kolokolchik", air a cho-dhùnadh às leth "NAO Buttercup".
Ciamar a chomharrachadh
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanmolaidhean
a 'cruthachadh clàr-amais coimeasgaichte le raointean bhon dà fhear thùsail no leudaich aon den fheadhainn a th’ ann le raointean bhon dàrna fear.
Eisimpleir:
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); -- отбор по конкретной паре
A' ceartachadh:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Tha am pàigheadh an seo nas lugha, leis gu bheil Bitmap Heap Scan gu math èifeachdach leis fhèin. Ach co-dhiù 7 tursan nas luaithe agus 2.5 tursan nas lugha de leughaidhean.
#3: Cuir còmhla clàran-amais (BitmapOr)
Nuair a thig e suas
Seall a’ chiad 20 iarrtas “sinn” no neo-ainmichte airson giullachd, le prìomhachas agadsa.
Ciamar a chomharrachadh
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanmolaidhean
Airson a chleachdadh AONADH [NA H-UILE] gus subqueries a chur còmhla airson gach aon de na blocaichean OR-suidheachadh.
Eisimpleir:
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;
A' ceartachadh:
(
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, больше и не надо
Ghabh sinn brath air gun deach na 20 clàr riatanach uile fhaighinn sa chiad bhloc, agus mar sin cha deach an dàrna fear, leis an Bitmap Heap Scan nas “daor”, eadhon a chuir gu bàs - aig a ’cheann thall 22x nas luaithe, 44x nas lugha de leughadh!
Sgeulachd nas mionaidiche mun dòigh optimization seo cleachdadh eisimpleirean sònraichte faodar a leughadh ann an artaigilean и .
Tionndadh coitcheann taghadh òrdaichte stèidhichte air grunn iuchraichean (agus chan e dìreach am paidhir const / NULL) air a dheasbad san artaigil .
#4: Leugh sinn tòrr rudan neo-riatanach
Nuair a thig e suas
Mar riaghailt, bidh e ag èirigh nuair a tha thu airson “criathrag eile a cheangal” ri iarrtas a tha ann mu thràth.
“Agus chan eil an aon fhear agad, ach le putanan màthair-neamhnaid? " film "The Diamond Arm"
Mar eisimpleir, ag atharrachadh na h-obrach gu h-àrd, seall a’ chiad 20 iarrtas “deatamach” as sine airson giullachd, ge bith dè an adhbhar a th’ aca.
Ciamar a chomharrachadh
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
molaidhean
Cruthaich [barrachd] sònraichte clàr-amais le staid WHERE no cuir a-steach raointean a bharrachd sa chlàr-amais.
Ma tha suidheachadh a’ chriathrag “statach” airson na h-adhbharan agad - is e sin chan eil sin a’ ciallachadh leudachadh liosta de luachan san àm ri teachd - tha e nas fheàrr clàr-amais WHERE a chleachdadh. Tha diofar inbhean boolean/enum a’ freagairt gu math dhan roinn seo.
Ma tha an suidheachadh sìoltachaidh faodaidh ciall eadar-dhealaichte a ghabhail, an uairsin tha e nas fheàrr an clàr-amais a leudachadh leis na raointean sin - mar a tha san t-suidheachadh le BitmapAnd gu h-àrd.
Eisimpleir:
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;
A' ceartachadh:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Mar a chì thu, tha sìoladh air a dhol à bith gu tur bhon phlana, agus tha an t-iarrtas air fàs 5 tursan nas luaithe.
#5: clàr gann
Nuair a thig e suas
Diofar oidhirpean gus an ciudha giollachd gnìomh agad fhèin a chruthachadh, nuair a thig àireamh mhòr de dh’ ùrachadh / cuir às de chlàran air a ’bhòrd gu suidheachadh àireamh mhòr de chlàran“ marbh ”.
Ciamar a chomharrachadh
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
molaidhean
Dèan gu cunbhalach le làimh VACUUM [LÀN] no trèanadh tric gu leòr a choileanadh le bhith a’ gleusadh a pharamadairean, a’ gabhail a-steach .
Anns a’ mhòr-chuid de chùisean, tha na duilgheadasan sin air an adhbhrachadh le droch chruth ceist nuair a thathar a’ gairm bho loidsig gnìomhachais mar an fheadhainn a chaidh a dheasbad ann .
Ach feumaidh tu tuigsinn gur dòcha nach cuidich eadhon VACUUM FULL an-còmhnaidh. Airson leithid de chùisean, is fhiach eòlas fhaighinn air an algairim bhon artaigil .
#6: Leughadh bho “meadhan” a’ chlàr-amais
Nuair a thig e suas
Tha e coltach gun do leugh sinn beagan, agus chaidh a h-uile càil a chlàradh, agus cha do shìol sinn duine sam bith a bharrachd - ach fhathast leugh sinn tòrr a bharrachd dhuilleagan na bu toil leinn.
Ciamar a chomharrachadh
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
molaidhean
Thoir sùil mhionaideach air structar a’ chlàr-amais a chaidh a chleachdadh agus na prìomh raointean a tha air an sònrachadh sa cheist - is coltaiche chan eil pàirt den chlàr-amais air a shuidheachadh. Is coltaiche gum feum thu clàr-amais coltach ris a chruthachadh, ach às aonais na raointean ro-leasachan no .
Eisimpleir:
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;
Tha coltas gu bheil a h-uile dad ceart gu leòr, eadhon a rèir a’ chlàr-amais, ach tha e amharasach dòigh air choireigin - airson gach aon de na 20 clàr a chaidh a leughadh, bha againn ri 4 duilleagan dàta a thoirt air falbh, 32KB gach clàr - nach eil sin dàna? Agus an t-ainm clàr-amais tbl_fk_org_fk_cli_idx smuaineach.
A' ceartachadh:
CREATE INDEX ON tbl(fk_cli);
Gu h-obann - 10 tursan nas luaithe, agus 4 tursan nas lugha ri leughadh!
Chithear eisimpleirean eile de shuidheachaidhean de chleachdadh neo-èifeachdach de chlàran-amais san artaigil .
#7: CTE × CTE
Nuair a thig e suas
Ann an iarrtas fhuair e “saill” CTE bho dhiofar bhùird, agus an uairsin chuir iad romhpa a dhèanamh eatorra JOIN.
Tha a’ chùis buntainneach airson dreachan fo v12 no iarrtasan le WITH MATERIALIZED.
Ciamar a chomharrachadh
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
molaidhean
Dèan sgrùdadh gu faiceallach air an iarrtas - agus ? Ma tha, an uairsin cuir “dictionary” an sàs ann an hstore/json a rèir a’ mhodail a tha air a mhìneachadh ann an .
#8: suaip gu diosc (temp sgrìobhte)
Nuair a thig e suas
Chan eil giullachd aon-ùine (seòrsachadh no sònrachadh) àireamh mhòr de chlàran a’ freagairt air a’ chuimhne a chaidh a shònrachadh airson seo.
Ciamar a chomharrachadh
-> *
&& temp written > 0molaidhean
Mura h-eil an ìre de chuimhne a chleachdas an gnìomhachd gu mòr nas àirde na luach ainmichte a’ pharamadair , is fhiach a cheartachadh. Faodaidh tu sa bhad anns an config airson a h-uile duine, no faodaidh tu troimhe SET [LOCAL] airson iarrtas / gnothach sònraichte.
Eisimpleir:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
A' ceartachadh:
SET work_mem = '128MB'; -- перед выполнением запроса
Airson adhbharan follaiseach, mura cleachdar ach cuimhne agus chan e diosc, thèid a’ cheist a chuir gu bàs fada nas luaithe. Aig an aon àm, thèid pàirt den luchd bhon HDD a thoirt air falbh cuideachd.
Ach feumaidh tu tuigsinn nach bi thu an-còmhnaidh comasach air tòrr is tòrr cuimhne a riarachadh - dìreach cha bhith gu leòr ann airson a h-uile duine.
#9: staitistig neo-iomchaidh
Nuair a thig e suas
Dhòirt iad tòrr a-steach don stòr-dàta sa bhad, ach cha robh tìde aca a chuir air falbh ANALYZE.
Ciamar a chomharrachadh
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10molaidhean
Dèan e ANALYZE.
Tha an suidheachadh seo air a mhìneachadh ann am barrachd mionaideachd .
#10: “Chaidh rudeigin ceàrr”
Nuair a thig e suas
Bha feitheamh ri glas air a chuir an sàs le iarrtas farpaiseach, no cha robh goireasan bathar-cruaidh CPU/hypervisor gu leòr ann.
Ciamar a chomharrachadh
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
molaidhean
Cleachd taobh a-muigh siostam sgrùdaidh frithealaiche airson bacadh no caitheamh stòrais neo-àbhaisteach. Tha sinn mu thràth air bruidhinn mun dreach againn de bhith ag eagrachadh a’ phròiseas seo airson ceudan de luchd-frithealaidh и .
Source: www.habr.com