بيمار SQL سوالن لاءِ ترڪيبون

مهينا اڳ اسان اعلان ڪيو وضاحت.tensor.ru - عوامي سوالن جي منصوبن جي تجزيي ۽ ڏسڻ لاءِ خدمت PostgreSQL ڏانهن.

توھان اڳ ۾ ئي ان کي 6000 کان وڌيڪ ڀيرا استعمال ڪيو آھي، پر ھڪڙي ھلندڙ خصوصيت جيڪا ٿي سگھي ٿي ڪنھن جو ڌيان نه ويو آھي ساخت جا اشارا، جيڪو ڪجهه هن طرح نظر اچي ٿو:

انهن کي ٻڌو، ۽ توهان جون درخواستون "هموار ۽ ريشمي ٿي ويندا." 🙂

پر سنجيدگي سان، ڪيتريون ئي حالتون جيڪي درخواست ڏين ٿيون سست ۽ وسيلن جي بکيو عام آهن ۽ پلان جي ڍانچي ۽ ڊيٽا کي سڃاڻي سگهجي ٿو.

انهي صورت ۾، هر فرد ڊولپر کي پنهنجي طور تي هڪ اصلاحي اختيار ڳولڻ جي ضرورت ناهي، صرف پنهنجي تجربي تي ڀروسو ڪندي - اسان کيس ٻڌائي سگهون ٿا ته هتي ڇا ٿي رهيو آهي، سبب ڇا ٿي سگهي ٿو، ۽ هڪ حل کي ڪيئن حاصل ڪجي. اهو ئي اسان ڪيو.

اچو ته انهن ڪيسن تي هڪ ويجهي نظر رکون - اهي ڪيئن بيان ڪيا ويا آهن ۽ ڪهڙيون سفارشون انهن جي اڳواڻي ڪن ٿا.

موضوع ۾ پنهنجو پاڻ کي بهتر ڪرڻ لاءِ، توهان پهرين ٻڌي سگهو ٿا لاڳاپيل بلاڪ تان منهنجي رپورٽ PGConf.Russia 2020 تي، ۽ صرف پوءِ هر مثال جي تفصيلي تجزيو ڏانهن وڃو:

#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;

[explanation.tensor.ru تي ڏسو]

توهان فوري طور تي نوٽيس ڪري سگهو ٿا ته 100 کان وڌيڪ رڪارڊ انڊيڪس مان ڪڍيا ويا، جيڪي پوء سڀني کي ترتيب ڏني وئي، ۽ پوء صرف هڪ رهجي ويو.

درست ڪرڻ:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[explanation.tensor.ru تي ڏسو]

جيتوڻيڪ اهڙي ابتدائي نموني تي - 8.5 دفعا تيز ۽ 33 ڀيرا گھٽ پڙھڻ. وڌيڪ "حقيقت" توهان وٽ هر قيمت لاء، وڌيڪ واضح اثر fk.

مان نوٽ ڪريان ٿو ته اهڙي انڊيڪس هڪ "اڳوڻي" انڊيڪس جي طور تي ڪم ڪندي ٻين سوالن لاء اڳ کان وڌيڪ خراب ناهي. fk، جتي ترتيب ڏيو pk اتي نه هو ۽ نه آهي (توهان هن بابت وڌيڪ پڙهي سگهو ٿا منهنجي آرٽيڪل ۾ غير موثر انڊيڪس ڳولڻ بابت). سميت، ان کي عام مهيا ڪندو واضح پرڏيهي اهم حمايت هن ميدان تي.

#2: انڊيڪس چونڪ (BitmapAnd)

جڏهن اڀري

ڪلائنٽ لاء سڀ معاهدو ڏيکاريو "LLC Kolokolchik"، "NAO Buttercup" جي طرفان ختم ڪيو ويو.

سڃاڻپ ڪيئن ڪجي

-> 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); -- отбор по конкретной паре

[explanation.tensor.ru تي ڏسو]

درست ڪرڻ:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[explanation.tensor.ru تي ڏسو]

هتي ادائيگي ننڍو آهي، ڇاڪاڻ ته Bitmap هيپ اسڪين پنهنجي پاڻ تي ڪافي اثرائتو آهي. پر بهرحال 7 دفعا تيز ۽ 2.5 ڀيرا گھٽ پڙھڻ.

#3: انڊيڪس کي ضم ڪريو (BitmapOr)

جڏهن اڀري

پهرين 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;

[explanation.tensor.ru تي ڏسو]

درست ڪرڻ:

(
  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, больше и не надо

[explanation.tensor.ru تي ڏسو]

اسان ان حقيقت جو فائدو ورتو ته سڀ 20 گهربل رڪارڊ فوري طور تي پهرئين بلاڪ ۾ ملي ويا، تنهن ڪري ٻيو، وڌيڪ ”مهانگو“ Bitmap Heap Scan سان، ان تي عمل نه ڪيو ويو - آخر ۾ 22x تيز، 44x گھٽ پڙھڻ!

هن اصلاح جي طريقي جي باري ۾ وڌيڪ تفصيلي ڪهاڻي مخصوص مثال استعمال ڪندي مضمونن ۾ پڙهي سگهجي ٿو PostgreSQL Antipatterns: نقصانڪار JOINs ۽ ORs и PostgreSQL Antipatterns: نالي جي ذريعي ڳولا جي ٻيهر سڌاري جي هڪ ڪهاڻي، يا "اڳتي ۽ اڳتي وڌڻ جي اصلاح".

عام نسخو ڪيترن ئي ڪنجين جي بنياد تي ترتيب ڏنل چونڊ (۽ نه صرف const/NULL جوڙو) مضمون ۾ بحث ڪيو ويو آهي SQL HowTo: لکندا ڪجھ دير لوپ سڌو سوال ۾، يا ”ايليمينٽري ٽي قدم“.

#4: اسان ڪيتريون ئي غير ضروري شيون پڙهون ٿا

جڏهن اڀري

ضابطي جي طور تي، اهو پيدا ٿئي ٿو جڏهن توهان چاهيو ٿا "ٻيو فلٽر ڳنڍيو" اڳ ۾ ئي موجود درخواست تي.

”۽ توهان وٽ ساڳيو ناهي، پر ماء جي موتي بٽڻ سان؟ » فلم "هيرن جي بازو"

مثال طور، مٿي ڏنل ڪم کي تبديل ڪندي، ڏيکاريو پهرين 20 پراڻين ”نازڪ“ درخواستن کي پروسيسنگ لاءِ، انهن جي مقصد کان سواءِ.

سڃاڻپ ڪيئن ڪجي

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

سفارشون

ٺاهيو [وڌيڪ] خاص انڊيڪس WHERE شرط سان يا انڊيڪس ۾ اضافي شعبا شامل ڪريو.

جيڪڏهن فلٽر شرط توهان جي مقصدن لاء "جامد" آهي - اهو آهي وڌائڻ جو مطلب نه آهي مستقبل ۾ قدرن جي فهرست - اھو بھتر آھي استعمال ڪرڻ لاءِ WHERE انڊيڪس. مختلف boolean/enum Status هن درجي ۾ چڱيءَ طرح فٽ ٿين ٿا.

جيڪڏهن فلٽرنگ جي حالت مختلف معنائون وٺي سگھي ٿو، پوءِ اھو بھتر آھي ته انڊيڪس کي انھن شعبن سان وڌايو - جيئن 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;

[explanation.tensor.ru تي ڏسو]

درست ڪرڻ:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[explanation.tensor.ru تي ڏسو]

جئين توهان ڏسي سگهو ٿا، فلٽرنگ پلان مان مڪمل طور تي غائب ٿي چڪو آهي، ۽ درخواست ٿي چڪي آهي 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

سفارشون

دستي طور تي باقاعده عمل ڪريو ويڪيوم [مڪمل] يا مناسب طور تي بار بار تربيت حاصل ڪريو خودڪار ويڪيوم ان جي پيٽرولن کي ٺيڪ ڪرڻ سان، بشمول هڪ مخصوص ٽيبل لاء.

اڪثر صورتن ۾، اهڙا مسئلا خراب سوالن جي جوڙجڪ جي ڪري پيدا ٿيندا آهن جڏهن ڪاروباري منطق کان ڪال ڪندا آهن جيئن انهن تي بحث ڪيو ويو آهي. PostgreSQL Antipatterns: ”مئل“ جي لشڪر سان وڙهڻ.

پر توهان کي سمجهڻ جي ضرورت آهي ته جيتوڻيڪ VACUUM FULL هميشه مدد نه ڪري سگهي ٿي. اهڙين حالتن لاء، اهو ضروري آهي ته پاڻ کي آرٽيڪل کان الگورتھم سان واقف ڪرڻ DBA: جڏهن VACUUM ناڪام ٿئي ٿو، اسان ميز کي دستي طور صاف ڪريون ٿا.

#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;

[explanation.tensor.ru تي ڏسو]

سڀ ڪجھ ٺيڪ لڳي ٿو، حتي انڊيڪس جي مطابق، پر اھو ڪنھن طرح مشڪوڪ آھي - پڙھيل 20 رڪارڊن مان ھر ھڪ لاءِ، اسان کي 4 صفحن جي ڊيٽا کي گھٽائڻو پيو، 32KB في رڪارڊ - ڇا اھو جرئتمند نه آھي؟ ۽ انڊيڪس جو نالو tbl_fk_org_fk_cli_idx سوچڻ وارو.

درست ڪرڻ:

CREATE INDEX ON tbl(fk_cli);

[explanation.tensor.ru تي ڏسو]

اوچتو - 10 ڀيرا تيز، ۽ پڙهڻ لاء 4 ڀيرا گهٽ!

انڊيڪس جي غير موثر استعمال جي حالتن جا ٻيا مثال مضمون ۾ ڏسي سگهجن ٿا DBA: بيڪار انڊيڪس ڳولڻ.

#7: CTE × CTE

جڏهن اڀري

درخواست ۾ "ٿلهو" CTE اسڪور ڪيو مختلف جدولن کان، ۽ پوء فيصلو ڪيو ته ان جي وچ ۾ JOIN.

ڪيس هيٺ ڏنل نسخن لاءِ لاڳاپيل آهي v12 يا درخواستن سان WITH MATERIALIZED.

سڃاڻپ ڪيئن ڪجي

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

سفارشون

احتياط سان درخواست جو تجزيو ڪريو - ۽ ڇا هتي CTEs جي ضرورت آهي؟؟ جيڪڏهن ها، ته پوءِ hstore/json ۾ ”لغت“ لاڳو ڪريو بيان ڪيل ماڊل جي مطابق PostgreSQL Anti-Patterns: اچو ته ھٿ ھڻي ھڻي ھتي شامل ٿي ڊڪشنري سان.

#8: ڊسڪ ڏانهن ادل بدليو (لکيل عارضي)

جڏهن اڀري

ھڪڙي وقت جي پروسيسنگ (ترتيب يا منفرد ڪرڻ) جي رڪارڊ جي وڏي تعداد ۾ ھن لاء مختص ڪيل ياداشت ۾ مناسب نه آھي.

سڃاڻپ ڪيئن ڪجي

-> *
   && temp written > 0

سفارشون

جيڪڏهن آپريشن پاران استعمال ڪيل ميموري جي مقدار کي پيٽرولر جي مخصوص قيمت کان وڌيڪ نه آهي ڪم_ميم، ان کي درست ڪرڻ جي قابل آهي. توهان فوري طور تي هر ڪنهن لاء ترتيب ۾ ڪري سگهو ٿا، يا توهان ذريعي ڪري سگهو ٿا SET [LOCAL] مخصوص درخواست / ٽرانزيڪشن لاءِ.

مثال طور

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[explanation.tensor.ru تي ڏسو]

درست ڪرڻ:

SET work_mem = '128MB'; -- перед выполнением запроса

[explanation.tensor.ru تي ڏسو]

واضح سببن لاء، جيڪڏهن صرف ميموري استعمال ڪئي وئي آهي ۽ ڊسڪ نه، پوء سوال تمام تيزيء سان عمل ڪيو ويندو. ساڳئي وقت، HDD مان لوڊ جو حصو پڻ هٽايو ويو آهي.

پر توهان کي اهو سمجهڻ جي ضرورت آهي ته توهان هميشه تمام گهڻو ۽ تمام گهڻو ياداشت مختص ڪرڻ جي قابل نه هوندا - اتي صرف هر ڪنهن لاءِ ڪافي نه هوندو.

#9: غير لاڳاپيل انگ اکر

جڏهن اڀري

انهن هڪ ئي وقت ڊيٽابيس ۾ تمام گهڻو وجهي ڇڏيو، پر ان کي هلائڻ جو وقت نه هو ANALYZE.

سڃاڻپ ڪيئن ڪجي

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

سفارشون

ان کي ٻاهر ڪڍو ANALYZE.

هن صورتحال ۾ وڌيڪ تفصيل سان بيان ڪيو ويو آهي PostgreSQL Antipatterns: انگ اکر سڀ ڪجهه آهن.

#10: "ڪجهه غلط ٿي ويو"

جڏهن اڀري

مقابلي واري درخواست پاران لاڳو ٿيل تالا جو انتظار هو، يا اتي ڪافي نه هئا CPU/hypervisor هارڊويئر وسيلا.

سڃاڻپ ڪيئن ڪجي

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

سفارشون

خارجي استعمال ڪريو نگراني نظام سرور بلاڪ ڪرڻ يا غير معمولي وسيلن جي استعمال لاء. اسان اڳ ۾ ئي ڳالهائي چڪا آهيون اسان جي ورزن کي ترتيب ڏيڻ جي هن پروسيس کي سوين سرورز لاءِ هتي и هتي.

جو ذريعو: www.habr.com