बिरामी SQL प्रश्नहरूको लागि व्यञ्जनहरू

धेरै महिना अघि हामीले घोषणा गर्यौं व्याख्या.tensor.ru - सार्वजनिक क्वेरी योजनाहरू पार्सिङ र भिजुअलाइज गर्ने सेवा PostgreSQL मा।

तपाईंले यसलाई वर्षौंदेखि ६,००० भन्दा बढी पटक प्रयोग गरिसक्नुभएको छ, तर यसको एउटा उपयोगी विशेषता कसैले नदेखेको हुन सक्छ - संरचनात्मक संकेतहरू, जुन यस्तो देखिन्छ:

उनीहरूको कुरा सुन्नुहोस्, अनि तपाईंका अनुरोधहरू "चिल्लो र रेशमी" हुनेछन्। 🙂

तर गम्भीरतापूर्वक, धेरै परिस्थितिहरू छन् जसले क्वेरीलाई ढिलो र स्रोत-भोको बनाउँछ, विशिष्ट छन् र योजनाको संरचना र डेटाबाट पहिचान गर्न सकिन्छ.

यस अवस्थामा, प्रत्येक व्यक्तिगत विकासकर्ताले आफ्नो अनुभवमा मात्र भर पर्दै आफैंले अप्टिमाइजेसन विकल्प खोज्नु पर्दैन - हामी उनीहरूलाई यहाँ के भइरहेको छ, कारण के हुन सक्छ, र बताउन सक्छौं। समाधान कसरी खोज्न सकिन्छ?। हामीले त्यही गर्यौं।

यी केसहरूलाई नजिकबाट हेरौं—यिनीहरूलाई कसरी परिभाषित गरिन्छ र तिनीहरूले कस्ता सिफारिसहरूतर्फ डोऱ्याउछन्।

विषयमा राम्रोसँग डुब्नको लागि, तपाईंले पहिले सम्बन्धित ब्लक सुन्न सक्नुहुन्छ PGConf.Russia २०२० मा मेरो रिपोर्ट, र त्यसपछि प्रत्येक उदाहरणको विस्तृत विश्लेषणमा जानुहोस्:

#१: अनुक्रमणिका "अन्डरसर्टिङ"

यो कहिले उत्पन्न हुन्छ?

"OOO 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;

[explan.tensor.ru मा हेर्नुहोस्]

तपाईंले तुरुन्तै याद गर्न सक्नुहुन्छ कि अनुक्रमणिकाबाट १०० भन्दा बढी रेकर्डहरू घटाइएका थिए, जुन सबै क्रमबद्ध गरिएका थिए, र त्यसपछि एउटा मात्र बाँकी थियो।

हामी समाधान गर्छौं:

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

[explan.tensor.ru मा हेर्नुहोस्]

यस्तो आदिम नमुना हुँदा पनि - ८.५ गुणा छिटो र ३३ गुणा कम पठनप्रत्येक मानको लागि तपाईंसँग जति धेरै "तथ्यहरू" हुन्छन्, प्रभाव त्यति नै स्पष्ट हुनेछ। fk.

म यो कुरा याद गर्न चाहन्छु कि यस्तो अनुक्रमणिकाले "उपसर्ग" अनुक्रमणिकाको रूपमा काम गर्नेछ जुन अन्य प्रश्नहरूको लागि अघिल्लो भन्दा खराब हुनेछैन। fk, जहाँ क्रमबद्ध गर्दै pk थिएन र छैन (तपाईं यस बारे थप पढ्न सक्नुहुन्छ) अकुशल अनुक्रमणिकाहरू फेला पार्ने बारेमा मेरो लेखमा)। विशेष गरी, यसले सामान्य सुनिश्चित गर्नेछ स्पष्ट विदेशी कुञ्जीहरूको लागि समर्थन यस क्षेत्रभरि।

#२: अनुक्रमणिका प्रतिच्छेदन (बिटम्यापअन्ड)

यो कहिले उत्पन्न हुन्छ?

"NAO Lyutik" को तर्फबाट सम्पन्न ग्राहक "OOO 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); -- отбор по конкретной паре

[explan.tensor.ru मा हेर्नुहोस्]

हामी समाधान गर्छौं:

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

[explan.tensor.ru मा हेर्नुहोस्]

यहाँ लाभ कम छ, किनकि बिटम्याप हिप स्क्यान आफैंमा धेरै प्रभावकारी छ। तर अझै पनि ८.५ गुणा छिटो र ३३ गुणा कम पठन.

#३: संयोजन अनुक्रमणिकाहरू (BitmapOr)

यो कहिले उत्पन्न हुन्छ?

प्रशोधनको लागि पहिलो २० पुराना "आफ्नै" वा तोकिएका अनुरोधहरू देखाउनुहोस्, जसमा आफ्नै अनुरोधहरू प्राथमिकतामा पर्नेछन्।

कसरी पहिचान गर्ने

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

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

[explan.tensor.ru मा हेर्नुहोस्]

हामीले पहिलो ब्लकमा सबै २० आवश्यक रेकर्डहरू तुरुन्तै प्राप्त भएको तथ्यको फाइदा उठायौं, त्यसैले दोस्रो, "महँगो" बिटम्याप हिप स्क्यान भएको, पनि प्रदर्शन गरिएन - परिणामस्वरूप २२ गुणा छिटो, ४४ गुणा कम पठन!

यस अनुकूलन विधिको थप विस्तृत विवरण विशिष्ट उदाहरणहरू प्रयोग गर्दै तपाईं लेखहरूमा पढ्न सक्नुहुन्छ PostgreSQL Antipatterns: हानिकारक JOINs र ORs и PostgreSQL Antipatterns: नामद्वारा खोजको पुनरावृत्ति परिष्करणको कथा, वा "अप्टिमाइजेसन अगाडि र पछाडि".

सामान्यीकृत संस्करण धेरै कुञ्जीहरूद्वारा क्रमबद्ध चयन (र केवल const/NULL जोडीको लागि मात्र होइन) लेखमा छलफल गरिएको छ SQL HowTo: क्वेरीमा सिधै केही समय लुप लेख्दै, वा "प्राथमिक तीन-चरण".

#४: हामी धेरै पढ्छौं

यो कहिले उत्पन्न हुन्छ?

सामान्यतया, यो तब हुन्छ जब तपाईं अवस्थित क्वेरीमा "अर्को फिल्टर थप्न" चाहनुहुन्छ।

"र तपाईंसँग पनि त्यस्तै छैन, तर मदर-अफ-पर्ल बटनहरू सहित? " फिचर फिल्म "द डायमंड आर्म"

उदाहरणका लागि, माथिको कार्यलाई परिमार्जन गर्दै प्रशोधनको लागि पहिलो २० पुराना "महत्वपूर्ण" अनुरोधहरू देखाउन, तिनीहरूको असाइनमेन्ट जेसुकै भए पनि।

कसरी पहिचान गर्ने

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

सिफारिसहरू

[थप] विशेषज्ञता सिर्जना गर्नुहोस् WHERE अवस्था भएको सूचकांक वा अनुक्रमणिकामा थप क्षेत्रहरू समावेश गर्नुहोस्।

यदि फिल्टर अवस्था तपाईंको कार्यहरूको लागि "स्थिर" छ भने - अर्थात्, विस्तारलाई जनाउँदैन भविष्यमा मानहरूको सूचीको लागि, WHERE अनुक्रमणिका प्रयोग गर्नु राम्रो हुन्छ। विभिन्न बुलियन/एनम स्थितिहरू यस श्रेणीमा राम्रोसँग फिट हुन्छन्।

यदि निस्पंदन अवस्था फरक मान लिन सक्छ, त्यसपछि यी क्षेत्रहरूसँग अनुक्रमणिका विस्तार गर्नु राम्रो हुन्छ - जस्तै माथिको BitmapAnd को अवस्थामा।

उदाहरण:

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;

[explan.tensor.ru मा हेर्नुहोस्]

हामी समाधान गर्छौं:

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

[explan.tensor.ru मा हेर्नुहोस्]

हामी देख्न सक्छौं, योजनाबाट फिल्टरिङ पूर्ण रूपमा गायब भएको छ, र क्वेरी बनेको छ ५ गुणा छिटो.

#५: स्पर्स टेबल

यो कहिले उत्पन्न हुन्छ?

टेबलमा धेरै संख्यामा रेकर्डहरू अद्यावधिक/मेटाउँदा ठूलो संख्यामा "मृत" रेकर्डहरूको स्थिति निम्त्याउँदा अनुकूलन कार्य प्रशोधन क्यु सिर्जना गर्ने विभिन्न प्रयासहरू।

कसरी पहिचान गर्ने

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

सिफारिसहरू

यसलाई नियमित रूपमा म्यानुअल रूपमा सञ्चालन गर्नुहोस् भ्याकुम [पूर्ण] वा पर्याप्त रूपमा बारम्बार प्रशोधन प्राप्त गर्नुहोस् autovacuum यसको प्यारामिटरहरू फाइन-ट्युन गरेर, सहित विशेष तालिकाको लागि.

धेरैजसो अवस्थामा, यस्ता समस्याहरू व्यवसायिक तर्कलाई कल गर्दा खराब क्वेरी संरचनाको कारणले गर्दा हुन्छन् जस्तै मा छलफल गरिएका PostgreSQL Antipatterns: "मृतक" को भीडसँग लड्दै.

तर यो बुझ्नु महत्त्वपूर्ण छ कि भ्याकुम फुलले पनि सधैं मद्दत नगर्न सक्छ। त्यस्ता अवस्थाहरूको लागि, लेखमा दिइएको एल्गोरिथ्मसँग परिचित हुनु उचित हुन्छ। DBA: भ्याकुम असफल हुँदा, टेबल म्यानुअल रूपमा सफा गर्नुहोस्.

#६: अनुक्रमणिकाको "बीच" बाट पढाइ

यो कहिले उत्पन्न हुन्छ?

यस्तो देखिन्छ कि हामीले थोरै पढ्यौं, र सबै कुरा अनुक्रमित गरियो, र कुनै अनावश्यक जानकारी फिल्टर गरिएको थिएन - तर हामीले अझै पनि चाहेको भन्दा धेरै पृष्ठहरू पढ्यौं।

कसरी पहिचान गर्ने

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

[explan.tensor.ru मा हेर्नुहोस्]

सबै कुरा ठीकठाक देखिन्छ, अनुक्रमणिकाको साथ पनि, तर यो अलि शंकास्पद छ - पढिएका २० रेकर्डहरू मध्ये प्रत्येकको लागि, ४ पृष्ठको डेटा पढ्नु पर्यो, प्रति रेकर्ड ३२ केबी - के त्यो अलि धेरै होइन र? र अनुक्रमणिकाको नाम पनि। tbl_fk_org_fk_cli_idx सोच्न बाध्य पार्ने।

हामी समाधान गर्छौं:

CREATE INDEX ON tbl(fk_cli);

[explan.tensor.ru मा हेर्नुहोस्]

अचानक - १० गुणा छिटो, र पढ्नको लागि ४ गुणा कम!

अनुक्रमणिकाहरू कुशलतापूर्वक प्रयोग नगरिएका परिस्थितिहरूका अन्य उदाहरणहरू लेखमा हेर्न सकिन्छ। DBA: बेकार अनुक्रमणिकाहरू खोज्दै.

#७: CTE × CTE

यो कहिले उत्पन्न हुन्छ?

अनुरोधमा "मोटो" CTE भर्ती गरियो विभिन्न टेबलहरूबाट, र त्यसपछि तिनीहरू बीच सम्बन्ध बनाउने निर्णय गरें JOIN.

यो केस v12 मुनिका संस्करणहरू वा अनुरोधहरूको लागि सान्दर्भिक छ WITH MATERIALIZED.

कसरी पहिचान गर्ने

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

सिफारिसहरू

अनुरोधलाई ध्यानपूर्वक विश्लेषण गर्नुहोस् - र के यहाँ CTE हरूको आवश्यकता छ?? यदि त्यसो हो भने, त्यसो भए hstore/json मा "शब्दकोश" लागू गर्नुहोस् मा वर्णन गरिएको मोडेल अनुसार PostgreSQL Antipatterns: एउटा शब्दकोशको साथ भारी JOIN मा हिट गरौं.

#८: डिस्कमा स्व्याप गर्नुहोस् (अस्थायी लेखिएको)

यो कहिले उत्पन्न हुन्छ?

ठूलो संख्यामा रेकर्डहरूको एक पटकको प्रशोधन (क्रमबद्ध गर्ने वा अद्वितीयीकरण) यस उद्देश्यको लागि छुट्याइएको मेमोरीमा फिट हुँदैन।

कसरी पहिचान गर्ने

-> *
   && temp written > 0

सिफारिसहरू

यदि अपरेशनले प्रयोग गरेको मेमोरीको मात्रा प्यारामिटरको सेट मानभन्दा धेरै बढी छैन भने काम_मेम, यो समायोजन गर्न लायक छ। तपाईं यसलाई सबैको लागि कन्फिगरेसनमा तुरुन्तै गर्न सक्नुहुन्छ, वा मार्फत SET [LOCAL] विशेष अनुरोध/लेनदेनको लागि।

उदाहरण:

SHOW work_mem;
-- "16MB"

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

[explan.tensor.ru मा हेर्नुहोस्]

हामी समाधान गर्छौं:

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

[explan.tensor.ru मा हेर्नुहोस्]

स्पष्ट कारणहरूका लागि, यदि डिस्क स्पेसको सट्टा मेमोरी मात्र प्रयोग गरियो भने, क्वेरी धेरै छिटो कार्यान्वयन हुनेछ। यसले HDD बाट केही भार पनि घटाउँछ।

तर हामीले बुझ्नुपर्छ कि सधैं धेरै मेमोरी छुट्याउन पनि सम्भव छैन - सबैको लागि पर्याप्त हुनेछैन।

#९: पुरानो तथ्याङ्क

यो कहिले उत्पन्न हुन्छ?

तिनीहरूले एकैचोटि डाटाबेसमा धेरै कुराहरू खन्याए, तर त्यसलाई चलाउन समय पाएनन्। ANALYZE.

कसरी पहिचान गर्ने

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

सिफारिसहरू

जे भए पनि गरौं। ANALYZE.

यो अवस्थालाई थप विस्तृतमा वर्णन गरिएको छ PostgreSQL एन्टिप्याटर्नहरू: तथ्याङ्कहरू राजा हुन्.

#१०: "केही गलत भयो"

यो कहिले उत्पन्न हुन्छ?

प्रतिस्पर्धी अनुरोधद्वारा लगाइएको लकको लागि पर्खनु परेको थियो, वा अपर्याप्त CPU/हाइपरभाइजर हार्डवेयर स्रोतहरू थिए।

कसरी पहिचान गर्ने

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

सिफारिसहरू

बाह्य प्रयोग गर्नुहोस् अनुगमन प्रणाली अवरोध वा असामान्य स्रोत खपतको लागि सर्भरहरू। हामीले सयौं सर्भरहरूको लागि यो प्रक्रिया व्यवस्थित गर्ने हाम्रो दृष्टिकोण पहिले नै वर्णन गरिसकेका छौं। यहाँ и यहाँ.

स्रोत: www.habr.com