Сарын өмнө - нийтийн PostgreSQL руу.
Түүнээс хойш та үүнийг 6000 гаруй удаа ашигласан ч анзаарагдахгүй байсан ашигтай функцүүдийн нэг нь бүтцийн ул мөр, энэ нь иймэрхүү харагдаж байна:
Тэднийг сонс, тэгвэл таны хүсэлт "торгомсог гөлгөр болно". 🙂
Гэхдээ нухацтай хэлэхэд, хүсэлтийг удаан, нөөцийн хувьд "хоолуур" болгодог олон нөхцөл байдал, Эдгээр нь ердийн шинж чанартай бөгөөд төлөвлөгөөний бүтэц, өгөгдлөөр танигдах боломжтой.
Энэ тохиолдолд хувь хүн хөгжүүлэгч бүр зөвхөн өөрийн туршлагад тулгуурлан оновчлолын сонголтыг бие даан хайх шаардлагагүй болно - бид түүнд юу болж байгааг, шалтгаан нь юу байж болохыг хэлж чадна. хэрхэн шийдэл гаргах вэ. Энэ нь бидний хийсэн зүйл юм.
Эдгээр тохиолдлуудыг нарийвчлан авч үзье - тэдгээр нь хэрхэн тодорхойлогддог, ямар зөвлөмжид хүргэдэг.
Энэ сэдвийг илүү сайн шингээхийн тулд та эхлээд холбогдох блокийг сонсож болно , зөвхөн дараа нь жишээ тус бүрийн нарийвчилсан шинжилгээнд очно уу:
№1: индекс "дутуу эрэмбэлэх"
Үүсэх үед
Үйлчлүүлэгч "Колокольчик" ХХК-ийн сүүлчийн нэхэмжлэхийг харуул.
Хэрхэн тодорхойлох вэ
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
зөвлөмж
Ашигласан индекс эрэмбэлэх талбаруудаар өргөжүүлнэ.
Жишээ нь:
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;
100 гаруй бичлэгийг индексээр хасч, дараа нь бүгдийг эрэмбэлж, дараа нь цорын ганц нь үлдсэнийг та шууд анзаарч болно.
Бид засах:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Ийм энгийн дээж дээр ч гэсэн - 8.5 дахин хурдан, 33 дахин бага уншилт. Үр нөлөө нь илүү тодорхой байх тусам үнэ цэнэ тус бүрт илүү олон "баримт" байх болно. fk.
Ийм индекс нь бусад асуултын хувьд өмнөхөөсөө муугүй "утгалт" индекс болж ажиллах болно гэдгийг би тэмдэглэж байна. fk, хаана эрэмбэлэх pk байгаагүй, байхгүй ч (та энэ талаар илүү ихийг уншиж болно ). Ялангуяа энэ нь хэвийн байдлыг хангах болно тодорхой гадаад гол дэмжлэг энэ талбараар.
№2: индексийн огтлолцол (BimapAnd)
Үүсэх үед
"Lyutik NJSC"-ийн нэрийн өмнөөс байгуулсан "ХХК Колокольчик" үйлчлүүлэгчийн бүх гэрээг харуул.
Хэрхэн тодорхойлох вэ
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanзөвлөмж
бий болгох нийлмэл индекс аль аль эх үүсвэрийн талбаруудаар эсвэл одоо байгаа талбаруудын аль нэгийг хоёр дахь талаас нь өргөтгөх.
Жишээ нь:
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); -- отбор по конкретной паре
Бид засах:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Bitmap Heap Scan нь өөрөө нэлээд үр дүнтэй байдаг тул энд ашиг бага байна. Гэхдээ ямар ч байсан 7 дахин хурдан, 2.5 дахин бага уншилт.
#3: Индексүүдийг нэгтгэх (BimapOr)
Үүсэх үед
Боловсруулах эхний 20 "өөрийн" эсвэл хуваарилагдаагүй хүсэлтийг давуу эрхтэйгээр харуул.
Хэрхэн тодорхойлох вэ
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanзөвлөмж
Хэрэглэх НЭГДЭЛ [БҮГД] нөхцөл OR блок бүрийн дэд асуулга нэгтгэх.
Жишээ нь:
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;
Бид засах:
(
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, больше и не надо
Эхний блок дээр шаардлагатай бүх 20 бичлэгийг нэн даруй хүлээн авсан тул бид илүү "үнэтэй" Bitmap Heap Scan-тай хоёр дахь нь ч гүйцэтгэгдээгүй. 22 дахин хурдан, 44 дахин бага уншилт!
Энэ оновчлолын аргын талаар илүү дэлгэрэнгүй түүх тодорхой жишээн дээр нийтлэлээс уншиж болно и .
Ерөнхий хувилбар хэд хэдэн товчлуураар дараалсан сонголт хийх (зөвхөн const / NULL хосын хувьд биш) нийтлэлд авч үзэх болно .
# 4: Бид хэтэрхий их уншдаг
Үүсэх үед
Дүрмээр бол энэ нь одоо байгаа хүсэлтэд "өөр шүүлтүүр хавсаргах" үед тохиолддог.
"Тэгээд чамд тийм зүйл байхгүй, гэхдээ сувдан товчтой? "Очир эрдэнийн гар" кино
Жишээлбэл, дээрх даалгаврыг өөрчлөхдөө зорилгоос үл хамааран боловсруулах хамгийн эртний "чухал" эхний 20 хүсэлтийг харуул.
Хэрхэн тодорхойлох вэ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
зөвлөмж
Мэргэшсэн [илүү] үүсгэх WHERE заалттай индекс эсвэл индекст нэмэлт талбар оруулах.
Хэрэв шүүлтүүрийн нөхцөл нь таны даалгаварт "статик" байвал - энэ нь өргөтгөлийг оруулаагүй болно ирээдүйн утгуудын жагсаалт - WHERE индексийг ашиглах нь дээр. Төрөл бүрийн логикийн/тооцооны статусууд энэ ангилалд сайн тохирдог.
Хэрэв шүүлтүүрийн нөхцөл өөр өөр утгыг авч болно, эдгээр талбаруудаар индексийг өргөжүүлэх нь илүү дээр юм - Bitmap болон дээрх нөхцөл байдлын адил.
Жишээ нь:
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;
Бид засах:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Таны харж байгаагаар төлөвлөгөөний шүүлтүүр бүрэн алга болж, хүсэлт нь болсон 5 дахин хурдан.
№5: сийрэг ширээ
Үүсэх үед
Ширээн дээрх олон тооны шинэчлэлтүүд / бичлэгүүдийг устгах нь олон тооны "үхсэн" бичлэгүүдийн нөхцөл байдалд хүргэдэг үед өөрийн даалгавар боловсруулах дараалал үүсгэх янз бүрийн оролдлого.
Хэрхэн тодорхойлох вэ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
зөвлөмж
Гараар тогтмол хийх ТОСООС [БҮРЭН] эсвэл хангалттай давтамжтай боловсруулалтанд хүрэх түүний параметрүүдийг нарийн тохируулах замаар, үүнд .
Ихэнх тохиолдолд эдгээр асуудлууд нь бизнесийн логикоор дуудагдах үед асуулгын буруу зохион байгуулалтаас үүдэлтэй байдаг. .
Гэхдээ VACUUM FULL ч гэсэн үргэлж тусалж чадахгүй гэдгийг бид ойлгох ёстой. Ийм тохиолдолд та нийтлэлээс алгоритмтай танилцах хэрэгтэй. .
№6: индексийн "дунд" хэсгээс унших
Үүсэх үед
Тэд бага зэрэг уншиж, бүх зүйлийг индексжүүлсэн, хэнийг ч нэмэлт шүүлтүүргүй байсан бололтой - гэхдээ бидний хүссэнээс хамаагүй олон хуудас уншсан.
Хэрхэн тодорхойлох вэ
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
зөвлөмж
Ашигласан индексийн бүтэц болон асуулгад заасан гол талбаруудыг сайтар хараарай - магадгүй, индексийн хэсгийг тохируулаагүй байна. Та ижил төстэй индекс үүсгэх шаардлагатай болно, гэхдээ угтвар талбаргүй, эсвэл .
Жишээ нь:
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;
Индексийн хувьд ч бүх зүйл зүгээр юм шиг санагдаж байгаа ч ямар нэг байдлаар сэжигтэй - уншсан 20 бичлэг бүрт 4 хуудас өгөгдөл, бичлэг бүрт 32KB хасах шаардлагатай байсан - энэ нь зоригтой биш гэж үү? Тийм ба индексийн нэр tbl_fk_org_fk_cli_idx бодолд хүргэдэг.
Бид засах:
CREATE INDEX ON tbl(fk_cli);
Гэнэт - Уншихад 10 дахин хурдан, 4 дахин бага!
Индексийг үр ашиггүй ашиглах жишээг нийтлэлээс үзнэ үү .
№7: CTE × CTE
Үүсэх үед
Хүсэлтийн дагуу "тарган" CTE оноо авсан өөр өөр хүснэгтээс, дараа нь тэдгээрийн хооронд хийхээр шийдсэн JOIN.
Энэ тохиолдол нь v12-оос доош хувилбарууд болон хүсэлтүүдэд хамааралтай WITH MATERIALIZED.
Хэрхэн тодорхойлох вэ
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
зөвлөмж
Хүсэлтийг сайтар шинжлэх ? Хэрэв тийм бол hstore/json дээр "толь бичиг"-ийг хэрэглэнэ -д тодорхойлсон загварын дагуу .
# 8: диск рүү солих (түр бичсэн)
Үүсэх үед
Олон тооны бичлэгийг нэг удаагийн боловсруулалт (ангилах эсвэл өвөрмөц болгох) нь үүнд зориулагдсан санах ойд тохирохгүй байна.
Хэрхэн тодорхойлох вэ
-> *
&& temp written > 0зөвлөмж
Хэрэв үйл ажиллагаанд ашигласан санах ойн хэмжээ нь параметрийн тогтоосон утгаас хэтрээгүй бол , үүнийг засах хэрэгтэй. Та нэн даруй хүн бүрийн тохиргоонд орж болно, эсвэл та үүнийг хийж болно SET [LOCAL] тодорхой хүсэлт/гүйлгээний хувьд.
Жишээ нь:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Бид засах:
SET work_mem = '128MB'; -- перед выполнением запроса
Тодорхой шалтгааны улмаас, хэрэв диск биш, зөвхөн санах ой ашигладаг бол асуулга илүү хурдан хийгдэх болно. Үүний зэрэгцээ ачааллын нэг хэсгийг HDD-ээс хасдаг.
Гэхдээ та маш их санах ойг хуваарилах нь үргэлж ажиллахгүй гэдгийг ойлгох хэрэгтэй - энэ нь хүн бүрт хангалтгүй байх болно.
№9: Үл хамаарах статистик
Үүсэх үед
Суурь руу нэг дор маш их цутгасан боловч тэд үүнийг хөөж амжсангүй ANALYZE.
Хэрхэн тодорхойлох вэ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10зөвлөмж
Үүнтэй адил зарцуул ANALYZE.
Энэ байдлыг илүү дэлгэрэнгүй тайлбарласан болно .
№10: "ямар нэг зүйл буруу болсон"
Үүсэх үед
Өрсөлдөгч хүсэлт дээр түгжээ хүлээгдэж байсан эсвэл CPU/hypervisor тоног төхөөрөмжийн нөөц хангалтгүй байна.
Хэрхэн тодорхойлох вэ
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
зөвлөмж
Гаднах хэрэгслийг ашиглана уу хяналтын систем блоклох эсвэл хэвийн бус нөөцийн зарцуулалтад зориулсан сервер. Олон зуун серверт зориулж энэ процессыг зохион байгуулах хувилбарынхаа талаар бид аль хэдийн ярьсан. и .
Эх сурвалж: www.habr.com