Orisirisi awọn osu sẹyin - àkọsílẹ si PostgreSQL.
O ti lo diẹ sii ju awọn akoko 6000 lọ lati igba naa, ṣugbọn ọkan ninu awọn ẹya ti o ni ọwọ le ti ko ni akiyesi ni igbekale awọn amọran, eyi ti o dabi iru eyi:
Tẹtisi wọn ati pe awọn ibeere rẹ yoo “di didan siliki”. 🙂
Ṣugbọn ni pataki, ọpọlọpọ awọn ipo ti o jẹ ki ibeere lọra ati “ajẹun” ni awọn ofin ti awọn orisun, jẹ aṣoju ati pe o le ṣe idanimọ nipasẹ ọna ati data ti ero naa.
Ni ọran yii, olupilẹṣẹ kọọkan kii yoo ni lati wa aṣayan iṣapeye lori tirẹ, ti o da lori iriri tirẹ nikan - a le sọ fun u ohun ti n ṣẹlẹ nibi, kini o le jẹ idi, ati bi o si wá soke pẹlu kan ojutu. Eyi ti a ṣe.
Jẹ ki a wo awọn ọran wọnyi ni pẹkipẹki - bawo ni wọn ṣe ṣalaye ati awọn iṣeduro wo ni wọn yorisi.
Fun kan ti o dara immersion ni koko, o le akọkọ gbọ awọn ti o baamu Àkọsílẹ lati , ati pe lẹhinna lọ si itupalẹ alaye ti apẹẹrẹ kọọkan:
#1: atọka "aiṣedeede"
Nigbati o dide
Ṣe afihan risiti ti o kẹhin fun alabara “LLC Kolokolchik”.
Bawo ni lati ṣe idanimọ
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Awọn iṣeduro
Atọka ti a lo faagun pẹlu too awọn aaye.
Apeere:
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;
O le ṣe akiyesi lẹsẹkẹsẹ pe diẹ sii ju awọn igbasilẹ 100 ti a yọkuro nipasẹ atọka, eyiti o jẹ lẹsẹsẹ gbogbo wọn, lẹhinna ọkan nikan ni o ku.
A ṣe atunṣe:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Paapaa lori iru apẹẹrẹ alakoko - 8.5x yiyara ati 33x diẹ kika. Ipa naa yoo jẹ kedere, diẹ sii "awọn otitọ" ti o ni fun iye kọọkan. fk.
Mo ṣe akiyesi pe iru atọka bẹẹ yoo ṣiṣẹ bi atọka “iṣaaju” ko buru ju ti iṣaaju lọ fun awọn ibeere miiran pẹlu fk, nibo tito nipasẹ pk je ko ati ki o jẹ ko (o le ka diẹ ẹ sii nipa yi ). Ni pato, yoo pese deede fojuhan ajeji bọtini support nipasẹ aaye yii.
#2: ikorita atọka (BitmapAnd)
Nigbati o dide
Ṣe afihan gbogbo awọn iwe adehun fun alabara “LLC Kolokolchik” ti pari ni ipo “NJSC Lyutik”.
Bawo ni lati ṣe idanimọ
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanAwọn iṣeduro
Ṣẹda atọka apapo nipasẹ awọn aaye lati orisun mejeeji tabi faagun ọkan ninu awọn aaye to wa lati keji.
Apeere:
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 ṣe atunṣe:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Nibi ere naa kere si, nitori Bitmap Heap Scan jẹ doko gidi funrararẹ. Sugbon lonakona 7x yiyara ati 2.5x diẹ kika.
#3: Iṣajọpọ Awọn atọka (BitmapOr)
Nigbati o dide
Ṣe afihan 20 akọkọ ti “ti ara” tabi awọn ibeere ti a ko pin fun sisẹ, pẹlu tirẹ ni pataki.
Bawo ni lati ṣe idanimọ
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanAwọn iṣeduro
Lo UNION [GBOGBO] lati darapọ awọn ibeere fun ọkọọkan ipo OR awọn bulọọki.
Apeere:
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 ṣe atunṣe:
(
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, больше и не надо
A lo anfani ti otitọ pe gbogbo awọn igbasilẹ pataki 20 ni a gba lẹsẹkẹsẹ ni bulọọki akọkọ, nitorinaa keji, pẹlu “gbowolori” Bitmap Heap Scan, ko paapaa ti pa - bi abajade 22x yiyara, 44x diẹ kika!
Itan alaye diẹ sii nipa ọna iṣapeye yii lori nja apẹẹrẹ le ti wa ni ka ninu ìwé и .
Akopọ ti ikede aṣayan ti a paṣẹ nipasẹ awọn bọtini pupọ (ati ki o ko o kan fun bata ti const / NULL) ti wa ni sísọ ninu awọn article .
#4: A ka pupọ
Nigbati o dide
Gẹgẹbi ofin, o waye nigbati o fẹ lati “so àlẹmọ miiran” si ibeere ti o wa tẹlẹ.
"Ati pe o ko ni kanna, ṣugbọn pẹlu awọn bọtini pali? » fiimu "Diamond Hand"
Fun apẹẹrẹ, iyipada iṣẹ-ṣiṣe ti o wa loke, ṣafihan awọn ibeere 20 akọkọ ti “lominu ni” fun sisẹ, laibikita idi wọn.
Bawo ni lati ṣe idanimọ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Awọn iṣeduro
Ṣẹda [siwaju sii] specialized atọka pẹlu WHERE gbolohun tabi ni afikun awọn aaye ninu atọka.
Ti ipo sisẹ ba jẹ "aimi" fun awọn iṣẹ ṣiṣe rẹ - iyẹn ni ko pẹlu imugboroosi atokọ ti awọn iye ni ọjọ iwaju - o dara lati lo itọka NIBI kan. Orisirisi awọn ipo boolean/enum baamu daradara sinu ẹka yii.
Ti o ba ti ase ipo le gba lori yatọ si iye, o dara lati faagun atọka pẹlu awọn aaye wọnyi - bi ninu ipo pẹlu BitmapAti loke.
Apeere:
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 ṣe atunṣe:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Bii o ti le rii, sisẹ lati ero naa ti lọ patapata, ati pe ibeere naa ti di 5 igba yiyara.
# 5: fọnka tabili
Nigbati o dide
Awọn igbiyanju pupọ lati ṣe isinyi ṣiṣatunṣe iṣẹ ṣiṣe tirẹ, nigbati nọmba nla ti awọn imudojuiwọn / piparẹ awọn igbasilẹ lori tabili yori si ipo ti nọmba nla ti awọn igbasilẹ “okú”.
Bawo ni lati ṣe idanimọ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Awọn iṣeduro
Pẹlu ọwọ gbe jade nigbagbogbo VACUUM [FULL] tabi se aseyori to loorekoore processing nipa itanran-yiyi awọn oniwe-sile, pẹlu .
Ni ọpọlọpọ awọn ọran, iru awọn iṣoro bẹ jẹ idi nipasẹ iṣeto ibeere ti ko dara nigbati a pe lati inu ọgbọn iṣowo, gẹgẹbi awọn ti a jiroro ninu .
Ṣugbọn a gbọdọ loye pe paapaa VACUUM FULL ko le ṣe iranlọwọ nigbagbogbo. Fun iru awọn ọran, o yẹ ki o mọ ararẹ pẹlu algorithm lati nkan naa. .
# 6: kika lati "arin" ti atọka
Nigbati o dide
O dabi pe wọn ka diẹ diẹ, ati pe ohun gbogbo ni atọka, ati pe wọn ko ṣe àlẹmọ ẹnikẹni ni afikun - ṣugbọn sibẹ, awọn oju-iwe diẹ sii ni pataki ni a ka ju ti a fẹ lọ.
Bawo ni lati ṣe idanimọ
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Awọn iṣeduro
Wo ilana itọka ti a lo ati awọn aaye bọtini ti a sọ pato ninu ibeere naa - o ṣeeṣe julọ, apakan atọka ko ṣeto. O ṣeese yoo nilo lati ṣẹda atọka ti o jọra, ṣugbọn laisi awọn aaye iṣaaju, tabi .
Apeere:
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;
Ohun gbogbo dabi pe o dara, paapaa ni awọn ofin ti atọka, ṣugbọn bakan ifura - fun ọkọọkan awọn igbasilẹ 20 ti o ka, awọn oju-iwe 4 ti data ni lati yọkuro, 32KB fun igbasilẹ - ṣe kii ṣe igboya? Bẹẹni ati orukọ atọka tbl_fk_org_fk_cli_idx nyorisi ero.
A ṣe atunṣe:
CREATE INDEX ON tbl(fk_cli);
Lojiji - Awọn akoko 10 yiyara ati awọn akoko 4 kere si lati ka!
Fun awọn apẹẹrẹ diẹ sii ti lilo aiṣedeede ti awọn atọka, wo nkan naa .
#7: CTE × CTE
Nigbati o dide
Ni ìbéèrè gba wọle "sanra" CTE lati oriṣiriṣi tabili, ati lẹhinna pinnu lati ṣe laarin wọn JOIN.
Ẹjọ naa ṣe pataki fun awọn ẹya ni isalẹ v12 tabi awọn ibeere pẹlu WITH MATERIALIZED.
Bawo ni lati ṣe idanimọ
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Awọn iṣeduro
Ṣe itupalẹ ibeere naa daradara ? Ti o ba jẹ bẹẹni, lẹhinna lo "itumọ-itumọ" ni hstore/json gẹgẹ bi awoṣe ti a sapejuwe ninu .
#8: paarọ si disk (ti a kọ iwọn otutu)
Nigbati o dide
Sisẹ-akoko kan (titọ tabi iyasọtọ) ti nọmba nla ti awọn igbasilẹ ko baamu si iranti ti a pin fun eyi.
Bawo ni lati ṣe idanimọ
-> *
&& temp written > 0Awọn iṣeduro
Ti iye iranti ti a lo nipasẹ iṣẹ naa ko kọja iye ti a ṣeto ti paramita naa , o yẹ ki o ṣe atunṣe. O le lẹsẹkẹsẹ ni atunto fun gbogbo eniyan, tabi o le nipasẹ SET [LOCAL] fun kan pato ìbéèrè / idunadura.
Apeere:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
A ṣe atunṣe:
SET work_mem = '128MB'; -- перед выполнением запроса
Fun awọn idi ti o han gbangba, ti o ba lo iranti nikan, kii ṣe disk, lẹhinna ibeere naa yoo ṣiṣẹ ni iyara pupọ. Ni akoko kanna, apakan ti ẹru naa tun yọ kuro lati HDD.
Ṣugbọn o nilo lati ni oye pe pipin iranti pupọ kii yoo ṣiṣẹ nigbagbogbo boya - kii yoo to fun gbogbo eniyan.
# 9: Awọn iṣiro ti ko ṣe pataki
Nigbati o dide
Pupọ ti dà sinu ipilẹ ni ẹẹkan, ṣugbọn wọn ko ni akoko lati lé e kuro ANALYZE.
Bawo ni lati ṣe idanimọ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Awọn iṣeduro
Na kanna ANALYZE.
Ipo yii jẹ apejuwe ni awọn alaye diẹ sii ni .
#10: "Nkankan ti ko tọ"
Nigbati o dide
Titiipa kan wa ti o nduro fun ibeere idije, tabi ko si awọn orisun ohun elo Sipiyu/hypervisor ti o to.
Bawo ni lati ṣe idanimọ
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Awọn iṣeduro
Lo ita monitoring eto olupin fun idinamọ tabi ilo awọn orisun ajeji. A ti sọrọ tẹlẹ nipa ẹya wa ti siseto ilana yii fun awọn ọgọọgọrun awọn olupin. и .
orisun: www.habr.com