Berî çend mehan - giştî ji bo PostgreSQL.
We berê wê zêdetirî 6000 carî bikar aniye, lê yek taybetmendiyek kêrhatî ya ku dibe ku ji nedîtî ve çûbe ev e nîşanên strukturel, ku tiştek mîna vê xuya dike:
Guh bidin wan, û daxwazên we dê "bişirîn û sipî bibin." 🙂
Lê bi giranî, gelek rewşên ku daxwazek hêdî û birçî-çavkaniyê dikin tîpîk in û ji hêla avahî û daneyên planê ve têne nas kirin.
Di vê rewşê de, her pêşdebirker ne hewce ye ku bi tena serê xwe li vebijarkek xweşbîniyê bigere, ku tenê xwe dispêre ezmûna xwe - em dikarin jê re bibêjin ka li vir çi diqewime, sedem çi dibe, û çawa nêzîkî çareseriyê dibe. Tiştê ku me kir ev bû.
Ka em hûrgulî li van dozan binihêrin - ka ew çawa têne diyar kirin û ew ber bi kîjan pêşniyaran ve diçin.
Ji bo ku hûn xwe baştir di mijarê de bihelînin, hûn dikarin pêşî li bloka têkildar ji guhdarî bikin , û tenê wê hingê berbi analîzek berfireh a her nimûneyê ve diçin:
#1: index "binavkirin"
Dema ku çêdibe
Ji bo xerîdar "LLC Kolokolchik" fatûreya herî dawî nîşan bide.
Çawa nas bike
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
pêşnîyarên
Indeksa bikaranîn bi qadên cûrbecûr berfireh bikin.
Nimûne:
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;
Hûn dikarin tavilê bala xwe bidin ku zêdetirî 100 tomar ji pêvekê hatine derxistin, ku paşê hemî hatine rêz kirin, û dûv re yek tenê ma.
Rastkirin:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Tewra li ser nimûneyek weha primitive - 8.5 qat leztir û 33 car kêm dixwînin. Zêdetir "rastiyên" we ji bo her nirxê heye, bandorek bêtir eşkere ye fk.
Ez bala xwe didim ku pêdekek wusa dê ji bo pirsên din ên ku bi wan re wekî pêvekek "pêşgir" ne ji berê xirabtir bixebite. fk, li ku derê rêz kirin pk tune bû û tune (hûn dikarin li ser vê yekê bêtir bixwînin ). Di nav de, ew ê normal peyda bike piştgiriya biyanî ya eşkere li ser vê qadê.
# 2: hevberdana îndeksê (BitmapAnd)
Dema ku çêdibe
Hemî peymanên ji bo xerîdar "LLC Kolokolchik", ku li ser navê "NAO Buttercup" hatî çêkirin, nîşan bidin.
Çawa nas bike
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanpêşnîyarên
ava bike index pêkhatî bi qadên ji her du yên orîjînal an yek ji yên heyî bi qadên ji ya duyemîn ve berfireh bikin.
Nimûne:
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); -- отбор по конкретной паре
Rastkirin:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Berdêla li vir piçûktir e, ji ber ku Bitmap Heap Scan bi serê xwe pir bi bandor e. Lê bi her awayî 7 qat leztir û 2.5 car kêm dixwînin.
#3: Endeksên hevgirtinê (BitmapOr)
Dema ku çêdibe
20 daxwazên herî kevn ên "me" yên ji bo pêvajoyê, bi yên we re pêşîn nîşan bidin.
Çawa nas bike
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanpêşnîyarên
Bikar bînin UNION [HEMÛ] ji bo her yek ji OR-blokên şert û mercan jêrpirsiyan bi hev re bikin.
Nimûne:
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;
Rastkirin:
(
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, больше и не надо
Me ji vê yekê sûd wergirt ku hemî 20 tomarên pêwîst tavilê di bloka yekem de hatin wergirtin, ji ber vê yekê ya duyemîn, bi Bitmap Heap Scan-a "bihatir" re, ne jî hate darve kirin - di dawiyê de. 22x zûtir, 44x kêm xwendin!
Çîrokek berfirehtir li ser vê rêbazê xweşbîniyê bikaranîna mînakên taybetî dikare di gotaran de were xwendin и .
Guhertoya gelemperî emir hilbijartina li ser bingeha çend keys (û ne tenê cotek const / NULL) di gotarê de tê nîqaş kirin .
#4: Me gelek tiştên nehewce dixwînin
Dema ku çêdibe
Wekî qaîdeyek, ew gava ku hûn dixwazin "filterek din" bi daxwazek berê ve girêbidin, derdikeve.
"Û we heman yek tune, lê bi bişkokên diya-ji-mirwarî? » fîlmê "Artê Elmas"
Mînakî, guheztina peywira li jor, 20 daxwazên herî kevn ên "krîtîk" ên ji bo pêvajoyê nîşan bidin, bêyî ku armanca wan be.
Çawa nas bike
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
pêşnîyarên
Biafirînin [bêhtir] pispor index bi şertê WHERE an jî qadên zêde di îndeksê de bihewînin.
Ger rewşa fîlterê ji bo mebestên we "statîk" e - ew e nayê wateya berfirehbûnê navnîşa nirxan di pêşerojê de - çêtir e ku meriv pêdekek WHERE bikar bîne. Rewşên cûrbecûr yên boolean / enum di vê kategoriyê de baş in.
Ger rewşa fîlterkirin dikare wateyên cuda werbigire, wê hingê çêtir e ku meriv bi van qadan pêvekê berfireh bike - wekî di rewşa BitmapAnd-ê de li jor.
Nimûne:
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;
Rastkirin:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Wekî ku hûn dibînin, fîlterkirin bi tevahî ji planê winda bûye, û daxwaz bûye 5 caran zûtir.
#5: tabloya kêm
Dema ku çêdibe
Hewldanên cûrbecûr ji bo afirandina rêzika hilberandina peywira xwe, dema ku hejmareke mezin ji nûvekirin / jêbirina tomarên li ser sifrê dibe sedema rewşek hejmareke mezin a tomarên "mirî".
Çawa nas bike
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
pêşnîyarên
Bi rêkûpêk bi destan pêk bînin VAKUUM [TÎM] an jî bi têra xwe pir caran perwerdehiyê bi dest bixin bi hûrgulîkirina parametreyên wê, di nav de .
Di pir rewşan de, pirsgirêkên weha ji ber pêkhatina pirsê ya nebaş dema ku ji mantiqa karsaziyê bang dikin mîna yên ku tê de têne nîqaş kirin têne çêkirin .
Lê hûn hewce ne ku fêm bikin ku VACUUM FULL jî dibe ku her gav ne alîkar be. Ji bo rewşên weha, hêja ye ku xwe bi algorîtmaya ji gotarê re nas bikin .
# 6: Xwendina ji "navîn" ya navnîşê
Dema ku çêdibe
Wusa dixuye ku me piçekî xwend, û her tişt hate navnîş kirin, û me kesek zêde fîltre nekir - lê dîsa jî me ji ya ku em dixwazin bêtir rûpelan dixwînin.
Çawa nas bike
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
pêşnîyarên
Nêzîk li strukturên pêveka hatî bikar anîn û qadên sereke yên ku di pirsê de hatine destnîşan kirin - bi îhtîmalek mezin binihêrin beşek ji index nayê diyarkirin. Bi îhtîmalek mezin hûn ê neçar bin ku pêdekek wusa biafirînin, lê bêyî qadên pêşgir an .
Nimûne:
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;
Dixuye ku her tişt baş e, tewra li gorî navnîşê jî, lê ew bi rengek gumanbar e - ji bo her yek ji 20 tomarên ku hatine xwendin, me neçar ma ku 4 rûpelên daneyê, her tomarê 32 KB jêbikin - ma ne wêrek e? Û navê navnîşê tbl_fk_org_fk_cli_idx fikirîn.
Rastkirin:
CREATE INDEX ON tbl(fk_cli);
Ji nişka ve - 10 caran zûtir, û 4 caran kêmtir xwendin!
Nimûneyên din ên rewşên karanîna bêbandor a indexan dikarin di gotarê de werin dîtin .
#7: CTE × CTE
Dema ku çêdibe
Di daxwazê de CTE "qelew" xist ji maseyên cûda, û paşê biryar da ku di navbera wan de bikin JOIN.
Doz ji bo guhertoyên li jêr v12 an daxwazên pê re têkildar e WITH MATERIALIZED.
Çawa nas bike
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
pêşnîyarên
Daxwazê bi baldarî analîz bikin - û ? Ger erê, hingê "ferheng" li hstore/json bicîh bikin li gorî modela ku tê de hatî destnîşan kirin .
#8: bi dîskê veguhezîne (dema hatî nivîsandin)
Dema ku çêdibe
Pêvajoya yek-carî (rêveberî an yekalîkirina) hejmareke mezin a tomaran di bîranîna ku ji bo vê hatî veqetandin de cîh nagire.
Çawa nas bike
-> *
&& temp written > 0pêşnîyarên
Ger mîqdara bîra ku ji hêla operasyonê ve hatî bikar anîn ji nirxa diyarkirî ya pîvanê pir derbas nebe , hêjayî rastkirina wê ye. Hûn dikarin tavilê ji bo her kesî vesazkirinê bikin, an jî hûn dikarin bi rê ve bibin SET [LOCAL] ji bo daxwazek / danûstendinek taybetî.
Nimûne:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Rastkirin:
SET work_mem = '128MB'; -- перед выполнением запроса
Ji ber sedemên diyar, heke tenê bîranîn û ne dîsk were bikar anîn, wê hingê pirs dê pir zûtir were darve kirin. Di heman demê de, beşek ji barkirina HDD-ê jî tê rakirin.
Lê hûn hewce ne ku fêm bikin ku hûn ê her gav nikaribin pir û pir bîranîn veqetînin - dê ji her kesî re bes nebe.
#9: statîstîkên negirêdayî
Dema ku çêdibe
Wan di yekcarê de gelek tişt rijandin nav databasê, lê wextê wan tune ku wê dûr bixin ANALYZE.
Çawa nas bike
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10pêşnîyarên
Wê pêk bînin ANALYZE.
Ev rewş bi hûrgulî tê vegotin .
#10: "tiştek xelet çû"
Dema ku çêdibe
Li benda kilîtkirinek ku ji hêla daxwazek hevrikî ve hatî ferz kirin hebû, an jî çavkaniyên hardware yên CPU/hipervisor têr nedibûn.
Çawa nas bike
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
pêşnîyarên
Derveyî bikar bînin sîstema çavdêriyê server ji bo astengkirin an mezaxtina çavkaniyê anormal. Me berê jî qala guhertoya xwe ya organîzekirina vê pêvajoyê ji bo bi sedan serveran kir и .
Source: www.habr.com