میاشتې مخکې - عامه PostgreSQL ته.
تاسو دا له هغه وخت راهیسې 6000 ځله کارولی دی، مګر یو له ګټورو ځانګړتیاوو څخه ممکن د پام وړ نه وي ساختماني نښې، کوم چې داسې ښکاري:
دوی ته غوږ ونیسئ او ستاسو غوښتنې به "د ورېښمو اسانه شي". 🙂
مګر په جدي توګه، ډیری شرایط چې د سرچینو په شرایطو کې غوښتنه ورو او "خوند" کوي، عادي دي او د پلان جوړښت او ډیټا لخوا پیژندل کیدی شي.
پدې حالت کې ، هر انفرادي پراختیا کونکی به په خپله د اصلاح کولو اختیار په لټه کې نه وي ، یوازې په خپله تجربه تکیه کوي - موږ کولی شو هغه ته ووایو چې دلته څه پیښیږي ، لامل یې څه کیدی شي ، او څنګه د حل سره راشي. دا هغه څه دي چې موږ یې وکړل.
راځئ چې دې قضیو ته نږدې وګورو - دوی څنګه تعریف شوي او کوم وړاندیزونه دوی رهبري کوي.
په موضوع کې د ښه ډوبیدو لپاره، تاسو کولی شئ لومړی د اړوند بلاک څخه واورئ ، او یوازې بیا د هرې بیلګې تفصيلي تحلیل ته لاړ شئ:
# 1: شاخص "کموالی"
کله کوي
د پیرودونکي "LLC Kolokolchik" لپاره وروستی رسید وښایاست.
د پیژندلو څرنګوالی
-> 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.5x ګړندی او 33x لږ لوستل. اغیزه به روښانه وي، نور "حقایق" تاسو د هر ارزښت لپاره لرئ. fk.
زه یادونه کوم چې دا ډول شاخص به د "مخکیني" شاخص په توګه کار وکړي د نورو پوښتنو لپاره د مخکیني څخه بد نه وي. fk، چیرته چې په ترتیب سره pk نه وه او نه ده (تاسو کولی شئ پدې اړه نور ولولئ ). په ځانګړې توګه، دا به نورمال چمتو کړي ښکاره بهرني کلیدي ملاتړ د دې ساحې لخوا.
#2: د شاخص تقاطع (BitmapAnd)
کله کوي
د پیرودونکي "LLC کولوکولچیک" لپاره ټول قراردادونه د "NJSC Lyutik" په استازیتوب پای ته ورسوئ.
د پیژندلو څرنګوالی
-> 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);
دلته لاسته راوړنه لږه ده، ځکه چې د بټ میپ هیپ سکین پخپله خورا اغیزمن دی. خو په هر صورت 7x ګړندی او 2.5x لږ لوستل.
#3: د شاخصونو ترکیب (BitmapOr)
کله کوي
د پروسس کولو لپاره لومړی 20 زاړه "خپل" یا غیر سپارل شوي غوښتنې وښایاست، د خپل لومړیتوب سره.
د پیژندلو څرنګوالی
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanسپارښتنې
استعمال اتحادیه [ټول] د هر حالت یا بلاک لپاره فرعي پوښتنو سره یوځای کول.
بېلګه:
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 اړین ریکارډونه سمدلاسه په لومړي بلاک کې ترلاسه شوي ، نو دوهم یې ، د ډیر "ګران" بټ میپ هیپ سکین سره ، حتی اعدام نه شو - په پایله کې. 22x ګړندی ، 44x لږ لوستل!
د دې اصلاح کولو میتود په اړه نور تفصيلي کیسه په کانکریټو مثالونو کې په مقالو کې لوستل کیدی شي и .
عمومي نسخه د څو کلیدونو لخوا ترتیب شوی انتخاب (او نه یوازې د یوې جوړې Const / NULL لپاره) په مقاله کې بحث شوی .
#4: موږ ډیر لوستل کوو
کله کوي
د یوې قاعدې په توګه، دا واقع کیږي کله چې تاسو غواړئ د موجوده غوښتنې سره "بل فلټر ضمیمه کړئ".
"او تاسو ورته نه لرئ، مګر د موتی تڼیو سره؟ » فلم "د الماس لاس"
د مثال په توګه، د پورتنۍ دندې تعدیل، د پروسس لپاره لومړنۍ 20 زاړه "مهم" غوښتنې ښکاره کړئ، پرته له دې چې هدف یې په پام کې ونیول شي.
د پیژندلو څرنګوالی
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
سپارښتنې
[نور] تخصص جوړ کړئ د WHERE بند سره شاخص یا په شاخص کې اضافي ساحې شامل کړئ.
که د فلټر کولو حالت ستاسو د دندو لپاره "جامد" وي - دا دی پراخول شامل نه دي په راتلونکي کې د ارزښتونو لیست - دا غوره ده چې د WHERE شاخص وکاروئ. مختلف بولین / اینوم حالتونه پدې کټګورۍ کې ښه فټ کوي.
که د چاڼ حالت کولی شي مختلف ارزښتونه واخلي، دا غوره ده چې د دې ساحو سره شاخص پراخه کړئ - لکه څنګه چې د بټ میپ او پورته حالت کې.
بېلګه:
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