आजारी SQL प्रश्नांसाठी पाककृती

महिन्यापूर्वी आम्ही घोषणा केली explain.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;

[explain.tensor.ru पहा]

तुम्ही ताबडतोब लक्षात घेऊ शकता की अनुक्रमणिकेद्वारे 100 पेक्षा जास्त रेकॉर्ड वजा केले गेले होते, जे नंतर सर्व क्रमवारी लावले गेले होते आणि नंतर फक्त एकच उरला होता.

आम्ही निराकरण करतो:

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

[explain.tensor.ru पहा]

अशा आदिम नमुन्यावरही - 8.5x जलद आणि 33x कमी वाचन. प्रभाव अधिक स्पष्ट होईल, प्रत्येक मूल्यासाठी आपल्याकडे अधिक "तथ्ये" असतील. fk.

मी लक्षात घेतो की अशी अनुक्रमणिका "उपसर्ग" निर्देशांक म्हणून कार्य करेल इतर प्रश्नांसाठी मागीलपेक्षा वाईट नाही fk, जेथे क्रमवारी लावत आहे pk नव्हते आणि नाही (आपण याबद्दल अधिक वाचू शकता अकार्यक्षम अनुक्रमणिका शोधण्याबद्दल माझ्या लेखात). विशेषतः, ते सामान्य प्रदान करेल स्पष्ट विदेशी की समर्थन या क्षेत्राद्वारे.

#2: अनुक्रमणिका छेदनबिंदू (बिटमॅप आणि)

जेव्हा उद्भवते

"NJSC Lyutik" च्या वतीने समारोप झालेल्या "LLC Kolokolchik" क्लायंटसाठी सर्व करार दर्शवा.

कसे ओळखावे

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

[explain.tensor.ru पहा]

आम्ही निराकरण करतो:

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

[explain.tensor.ru पहा]

येथे फायदा कमी आहे, कारण बिटमॅप हीप स्कॅन स्वतःच प्रभावी आहे. पण असो 7x जलद आणि 2.5x कमी वाचन.

#3: अनुक्रमणिका एकत्र करणे (बिटमॅपओआर)

जेव्हा उद्भवते

पहिल्या 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;

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

[explain.tensor.ru पहा]

आम्ही या वस्तुस्थितीचा फायदा घेतला की पहिल्या ब्लॉकमध्ये सर्व 20 आवश्यक नोंदी ताबडतोब प्राप्त झाल्या होत्या, म्हणून दुसरा, अधिक "महाग" बिटमॅप हीप स्कॅनसह, अंमलात आणला गेला नाही - परिणामी 22x जलद, 44x कमी वाचन!

या ऑप्टिमायझेशन पद्धतीबद्दल अधिक तपशीलवार कथा ठोस उदाहरणांवर लेखांमध्ये वाचता येईल PostgreSQL अँटीपॅटर्न: हानिकारक सामील होणे आणि ORs и पोस्टग्रेएसक्यूएल अँटीपॅटर्न: नावानुसार शोधाच्या पुनरावृत्ती परिष्करणाची कथा, किंवा "पुढे आणि पुढे ऑप्टिमाइझ करणे".

सामान्यीकृत आवृत्ती अनेक की द्वारे ऑर्डर केलेली निवड (आणि फक्त const/NULL च्या जोडीसाठी नाही) लेखात चर्चा केली आहे SQL HowTo: क्वेरीमध्ये थेट एक while-loop लिहा, किंवा "Elementary three-way".

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

[explain.tensor.ru पहा]

आम्ही निराकरण करतो:

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

[explain.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 अँटीपॅटर्न: "मृत" ची लढाई.

परंतु आपण हे समजून घेतले पाहिजे की व्हॅक्यूम फुल देखील नेहमीच मदत करू शकत नाही. अशा प्रकरणांसाठी, आपण लेखातील अल्गोरिदमसह स्वत: ला परिचित केले पाहिजे. DBA: जेव्हा व्हॅक्यूम पास होतो, तेव्हा आम्ही टेबल स्वहस्ते स्वच्छ करतो.

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

[explain.tensor.ru पहा]

सर्व काही ठीक आहे असे दिसते, अगदी निर्देशांकाच्या बाबतीतही, परंतु कसे तरी संशयास्पद - ​​वाचलेल्या 20 रेकॉर्डपैकी प्रत्येकासाठी 4 पृष्ठे डेटा वजा करावा लागला, प्रति रेकॉर्ड 32KB - हे धाडसी नाही का? होय आणि अनुक्रमणिका नाव tbl_fk_org_fk_cli_idx विचाराकडे नेतो.

आम्ही निराकरण करतो:

CREATE INDEX ON tbl(fk_cli);

[explain.tensor.ru पहा]

अचानक - वाचण्यासाठी 10 पट जलद आणि 4 पट कमी!

निर्देशांकांच्या अकार्यक्षम वापराच्या अधिक उदाहरणांसाठी, लेख पहा DBA: निरुपयोगी अनुक्रमणिका शोधा.

#7: CTE × CTE

जेव्हा उद्भवते

विनंती मध्ये "फॅट" CTE स्कोअर केले वेगवेगळ्या टेबल्सवरून, आणि नंतर त्या दरम्यान करायचे ठरवले JOIN.

केस v12 च्या खाली असलेल्या आवृत्त्यांसाठी किंवा विनंत्यांसाठी संबंधित आहे WITH MATERIALIZED.

कसे ओळखावे

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

शिफारसी

विनंतीचे काळजीपूर्वक विश्लेषण करा येथे CTE ची अजिबात गरज आहे? जर होय, तर hstore/json मध्ये "शब्दकोश" लागू करा मध्ये वर्णन केलेल्या मॉडेलनुसार PostgreSQL अँटीपॅटर्न: शब्दकोश हिट हेवी जॉइन.

#8: डिस्कवर स्वॅप (तापमान लिहिले)

जेव्हा उद्भवते

मोठ्या संख्येने रेकॉर्डची एक-वेळ प्रक्रिया (वर्गीकरण किंवा विशिष्टीकरण) यासाठी वाटप केलेल्या मेमरीमध्ये बसत नाही.

कसे ओळखावे

-> *
   && temp written > 0

शिफारसी

जर ऑपरेशनद्वारे वापरल्या जाणार्‍या मेमरीचे प्रमाण पॅरामीटरच्या सेट मूल्यापेक्षा जास्त नसेल काम_मेम, ते दुरुस्त केले पाहिजे. तुम्ही प्रत्येकासाठी कॉन्फिगरेशनमध्ये ताबडतोब करू शकता किंवा तुम्ही ते करू शकता SET [LOCAL] विशिष्ट विनंती/व्यवहारासाठी.

उदाहरण:

SHOW work_mem;
-- "16MB"

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

[explain.tensor.ru पहा]

आम्ही निराकरण करतो:

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

[explain.tensor.ru पहा]

स्पष्ट कारणास्तव, जर फक्त मेमरी वापरली गेली आणि डिस्क नाही, तर क्वेरी खूप वेगवान होईल. त्याच वेळी, लोडचा भाग HDD वरून देखील काढला जातो.

परंतु आपल्याला हे समजून घेणे आवश्यक आहे की भरपूर मेमरी वाटप करणे नेहमीच कार्य करणार नाही - ते प्रत्येकासाठी पुरेसे नाही.

#9: असंबद्ध आकडेवारी

जेव्हा उद्भवते

बेसमध्ये एकाच वेळी बरेच काही ओतले गेले, परंतु ते दूर करण्यासाठी त्यांच्याकडे वेळ नव्हता ANALYZE.

कसे ओळखावे

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

शिफारसी

समान खर्च करा ANALYZE.

या परिस्थितीचे अधिक तपशीलवार वर्णन केले आहे PostgreSQL अँटीपॅटर्न: आकडेवारी हे प्रत्येक गोष्टीचे प्रमुख असते.

#10: "काहीतरी चूक झाली"

जेव्हा उद्भवते

प्रतिस्पर्धी विनंतीची प्रतीक्षा करत असलेला लॉक होता, किंवा पुरेशी CPU/हायपरवाइजर हार्डवेअर संसाधने नव्हती.

कसे ओळखावे

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

शिफारसी

बाह्य वापरा देखरेख प्रणाली ब्लॉकिंग किंवा असामान्य संसाधन वापरासाठी सर्व्हर. आम्ही शेकडो सर्व्हरसाठी ही प्रक्रिया आयोजित करण्याच्या आमच्या आवृत्तीबद्दल आधीच बोललो आहोत. येथे и येथे.

स्त्रोत: www.habr.com