PostgreSQL Antipatterns: नामद्वारा खोजको पुनरावृत्ति परिष्करणको कथा, वा "अप्टिमाइजेसन अगाडि र पछाडि"

देशभरका बिक्री कार्यालयहरूबाट हजारौं प्रबन्धकहरू रेकर्ड छन् हाम्रो CRM प्रणाली दैनिक हजारौं सम्पर्कहरू - सम्भावित वा अवस्थित ग्राहकहरु संग संचार को तथ्यहरु। र यसको लागि, तपाईंले पहिले एक ग्राहक फेला पार्नु पर्छ, र अधिमानतः धेरै चाँडै। र यो प्राय: नाम द्वारा हुन्छ।

त्यसकारण, यो अचम्मको कुरा होइन कि, एक पटक फेरि "भारी" प्रश्नहरूको विश्लेषण गर्दा सबैभन्दा लोड गरिएको डाटाबेसहरू - हाम्रो आफ्नै। VLSI कर्पोरेट खाता, मैले "शीर्षमा" फेला पारे नामद्वारा "छिटो" खोजको लागि अनुरोध संगठन कार्डहरूको लागि।

यसबाहेक थप अनुसन्धानले एउटा रोचक उदाहरण देखायो पहिलो अनुकूलन र त्यसपछि प्रदर्शन गिरावट धेरै टोलीहरू द्वारा यसको क्रमिक परिष्करणको साथ अनुरोध, जसमध्ये प्रत्येकले उत्कृष्ट उद्देश्यका साथ मात्र कार्य गरेको छ।

0: प्रयोगकर्ता के चाहन्थे?

[KDPV यहाँ देखि]

प्रयोगकर्ताले नामद्वारा "छिटो" खोजको बारेमा कुरा गर्दा सामान्यतया के मतलब हुन्छ? यो लगभग कहिल्यै एक substring को लागी एक "इमानदार" खोज हुन जान्छ ... LIKE '%роза%' - किनभने त्यसपछि परिणाम मात्र समावेश छैन 'Розалия' и 'Магазин Роза'तर 'Гроза' र पनि 'Дом Деда Мороза'.

प्रयोगकर्ताले दैनिक स्तरमा मान्दछ कि तपाइँ उसलाई प्रदान गर्नुहुनेछ शब्दको सुरुमा खोज्नुहोस् शीर्षकमा र यसलाई अझ सान्दर्भिक बनाउनुहोस् मा सुरु हुन्छ प्रवेश गरेको। र तपाईंले यो गर्नुहुनेछ लगभग तुरुन्तै - इन्टरलाइनर इनपुटको लागि।

1: कार्य सीमित गर्नुहोस्

र अझ बढी, एक व्यक्ति विशेष रूपमा प्रवेश गर्दैन 'роз магаз', ताकि तपाईंले उपसर्ग द्वारा प्रत्येक शब्द खोज्नु पर्छ। होइन, प्रयोगकर्ताको लागि अन्तिम शब्दको लागि द्रुत सङ्केतमा जवाफ दिनको लागि अघिल्लो शब्दहरूलाई उद्देश्यपूर्वक "अन्डरस्पेसिफाइ" गर्नु भन्दा धेरै सजिलो छ - हेर्नुहोस् कुनै पनि खोज इन्जिनले यसलाई कसरी ह्यान्डल गर्छ।

सामान्य दायाँ समस्याको लागि आवश्यकताहरू निर्माण गर्नु आधा भन्दा बढी समाधान हो। कहिलेकाहीँ सावधान प्रयोग केस विश्लेषण नतिजामा उल्लेखनीय प्रभाव पार्न सक्छ.

एक सार विकासकर्ताले के गर्छ?

1.0: बाह्य खोज इन्जिन

ओह, खोजी गर्न गाह्रो छ, म केहि पनि गर्न चाहन्न - यसलाई डेभप्सलाई दिऔं! तिनीहरूलाई डाटाबेसमा बाहिरी खोज इन्जिन प्रयोग गर्न दिनुहोस्: Sphinx, ElasticSearch,...

सिंक्रोनाइजेसन र परिवर्तनहरूको गतिको सन्दर्भमा श्रम-गहन भए पनि काम गर्ने विकल्प। तर हाम्रो अवस्थामा होइन, किनकि खोज प्रत्येक ग्राहकको लागि मात्र उसको खाता डेटाको ढाँचा भित्र गरिन्छ। र डाटामा एकदम उच्च परिवर्तनशीलता छ - र यदि प्रबन्धकले अब कार्डमा प्रवेश गरेको छ 'Магазин Роза', त्यसपछि 5-10 सेकेन्ड पछि उसले पहिले नै याद गर्न सक्छ कि उसले त्यहाँ आफ्नो इमेल संकेत गर्न बिर्सेको छ र यसलाई फेला पार्न र यसलाई सच्याउन चाहन्छु।

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

१.१: "इमानदार" सबस्ट्रिङ

हामी "सबस्ट्रिंग" शब्दमा टाँसिन्छौं। तर सबस्ट्रिंग द्वारा अनुक्रमणिका खोजको लागि (र नियमित अभिव्यक्तिहरू पनि!) त्यहाँ उत्कृष्ट छ मोड्युल pg_trgm! त्यसपछि मात्र यो सही क्रमबद्ध गर्न आवश्यक हुनेछ।

मोडेल सरल बनाउन निम्न प्लेट लिन प्रयास गरौं:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

हामी त्यहाँ वास्तविक संगठनहरूको 7.8 मिलियन रेकर्डहरू अपलोड गर्छौं र तिनीहरूलाई अनुक्रमणिका:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

इन्टरलाइनर खोजको लागि पहिलो 10 रेकर्डहरू हेरौं:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

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

खैर, त्यो ... 26ms, 31MB डाटा र 1.7K फिल्टर गरिएका रेकर्डहरू पढ्नुहोस् - 10 खोजीहरूका लागि। ओभरहेड लागत धेरै उच्च छ, त्यहाँ केहि अधिक कुशल छैन?

1.2: पाठ द्वारा खोज? यो FTS हो!

वास्तवमा, PostgreSQL ले एक धेरै शक्तिशाली प्रदान गर्दछ पूर्ण पाठ खोज इन्जिन (पूर्ण पाठ खोज), उपसर्ग खोज गर्ने क्षमता सहित। एक उत्कृष्ट विकल्प, तपाईंले विस्तारहरू स्थापना गर्न आवश्यक पर्दैन! प्रयास गराैँ:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

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

यहाँ क्वेरी कार्यान्वयनको समानान्तरले हामीलाई थोरै मद्दत गर्‍यो, समयलाई आधामा काटेर 11ms। र हामी 1.5 पटक कम पढ्न थियो - कुल मा 20MB। तर यहाँ, कम, राम्रो, किनकि हामीले पढ्ने भोल्युम जति ठूलो हुन्छ, क्यास मिस हुने सम्भावना उच्च हुन्छ, र डिस्कबाट पढिएको डेटाको प्रत्येक अतिरिक्त पृष्ठ अनुरोधको लागि सम्भावित "ब्रेक" हो।

1.3: अझै मनपर्छ?

अघिल्लो माग सबैको लागि राम्रो छ, तर दिनमा लाखौं पटक तान्दा मात्र आउँछ 2TB डाटा पढ्नुहोस्। उत्तम अवस्थामा, मेमोरीबाट, तर यदि तपाईं अशुभ हुनुहुन्छ भने, डिस्कबाट। त्यसैले यसलाई सानो बनाउन प्रयास गरौं।

प्रयोगकर्ताले के हेर्न चाहन्छ सम्झौं पहिले "जसबाट सुरु हुन्छ..."। त्यसैले यो यसको शुद्ध रूप मा छ उपसर्ग खोज सहयोगको साथ text_pattern_ops! र हामीले खोजिरहेका 10 वटा रेकर्डहरू सम्म "पर्याप्त छैन" भने मात्र, हामीले FTS खोज प्रयोग गरेर तिनीहरूलाई पढ्नुपर्नेछ:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

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

उत्कृष्ट प्रदर्शन - कुल 0.05ms र 100KB भन्दा अलि बढी पढ्नुहोस्! हामीले मात्र बिर्सियौं नाम द्वारा क्रमबद्ध गर्नुहोस्ताकि प्रयोगकर्ता परिणामहरूमा हराउन नपरोस्:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

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

ओह, केहि अब यति सुन्दर छैन - यस्तो देखिन्छ कि त्यहाँ एक अनुक्रमणिका छ, तर क्रमबद्ध गर्दै उड्छ ... यो, अवश्य पनि, अघिल्लो विकल्प भन्दा धेरै गुणा बढी प्रभावकारी छ, तर ...

1.4: "फाइलसँग समाप्त गर्नुहोस्"

तर त्यहाँ एउटा अनुक्रमणिका छ जसले तपाईंलाई दायराद्वारा खोजी गर्न र अझै पनि क्रमबद्ध रूपमा प्रयोग गर्न अनुमति दिन्छ - नियमित btree!

CREATE INDEX ON firms(lower(name));

यसको लागि अनुरोध मात्र "म्यानुअल रूपमा सङ्कलन" हुनुपर्छ:

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

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

उत्कृष्ट - क्रमबद्ध कार्यहरू, र स्रोत खपत "माइक्रोस्कोपिक" रहन्छ, "शुद्ध" FTS भन्दा हजारौं गुणा बढी प्रभावकारी! सबै बाँकी छ यसलाई एकै अनुरोधमा सँगै राख्नु हो:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

ध्यान दिनुहोस् कि दोस्रो सबक्वेरी कार्यान्वयन गरिएको छ पहिलोले सोचेभन्दा कम फर्केमा मात्र अन्तिम LIMIT रेखाहरूको संख्या। म क्वेरी अप्टिमाइजेसनको यो विधिको बारेमा कुरा गर्दैछु पहिले नै लेखेका थिए.

त्यसोभए हो, हामीसँग अब टेबलमा btree र gin दुवै छ, तर सांख्यिकीय रूपमा यो बाहिर जान्छ कि 10% भन्दा कम अनुरोधहरू दोस्रो ब्लकको कार्यान्वयनमा पुग्छन्। त्यो हो, कार्यको लागि अग्रिम ज्ञात यस्तो विशिष्ट सीमितताहरूको साथ, हामी लगभग एक हजार पटक सर्भर स्रोतहरूको कुल खपत घटाउन सक्षम भयौं!

1.5*: हामी फाइल बिना गर्न सक्छौं

माथि LIKE हामीलाई गलत क्रमबद्ध प्रयोग गर्नबाट रोकिएको थियो। तर यसलाई प्रयोग गर्ने अपरेटर निर्दिष्ट गरेर "सही मार्गमा सेट" गर्न सकिन्छ:

पूर्वनिर्धारित रूपमा यो मानिन्छ ASC। थप रूपमा, तपाइँ क्लजमा निर्दिष्ट क्रमबद्ध अपरेटरको नाम निर्दिष्ट गर्न सक्नुहुन्छ USING। क्रमबद्ध अपरेटर बी-ट्री अपरेटरहरूको केही परिवारको भन्दा कम वा बढीको सदस्य हुनुपर्छ। ASC सामान्यतया बराबर USING < и DESC सामान्यतया बराबर USING >.

हाम्रो मामला मा, "कम" छ ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

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

2: कसरी अनुरोधहरू खट्टा हुन्छन्

अब हामी हाम्रो अनुरोधलाई छ महिना वा एक वर्षको लागि "सिमर" गर्न छोड्छौं, र मेमोरीको कुल दैनिक "पम्पिङ" को सूचकहरूको साथ फेरि "शीर्षमा" फेला पार्न पाउँदा हामी छक्क पर्छौं।बफर साझा हिट) मा 5.5TB - अर्थात्, यो पहिले भन्दा बढी थियो।

होइन, निस्सन्देह, हाम्रो व्यापार बढेको छ र हाम्रो कार्यभार बढेको छ, तर उही रकमले होइन! यसको मतलब यहाँ केहि माछा छ - यसलाई बाहिर निकालौं।

२.१: पेजिङको जन्म

केहि बिन्दुमा, अर्को विकास टोलीले द्रुत सबस्क्रिप्ट खोजबाट रजिस्ट्रीमा उही, तर विस्तारित परिणामहरू "जम्प" गर्न सम्भव बनाउन चाहन्थे। पृष्ठ नेभिगेसन बिना रजिस्ट्री के हो? यसलाई बिगारौं!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

अब विकासकर्ताको लागि कुनै तनाव बिना "पृष्ठ-द्वारा-पृष्ठ" लोडिङको साथ खोज परिणामहरूको रजिस्ट्री देखाउन सम्भव थियो।

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

2.2: म केहि विदेशी चाहन्छु

कुनै बिन्दुमा विकासकर्ता चाहन्थे डेटाको साथ नतिजा नमूना विविधता अर्को तालिकाबाट, जसको लागि पुरा अघिल्लो अनुरोध CTE मा पठाइएको थियो:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

र त्यसो भए पनि, यो खराब छैन, किनकि सबक्वेरी 10 फिर्ता गरिएका रेकर्डहरूको लागि मात्र मूल्याङ्कन गरिन्छ, यदि होइन भने ...

2.3: DISTINCT विवेकहीन र निर्दयी छ

कतै दोस्रो सबक्वेरीबाट यस्तो विकासको प्रक्रियामा हरायो NOT LIKE अवस्था। यसपछि प्रष्ट हुन्छ UNION ALL फर्किन थाले केहि प्रविष्टिहरू दुई पटक - पहिलो पङ्क्तिको सुरुमा फेला पर्यो, र त्यसपछि फेरि - यो रेखाको पहिलो शब्दको सुरुमा। सीमामा, दोस्रो सबक्वेरीका सबै रेकर्डहरू पहिलोको रेकर्डसँग मेल खान सक्छन्।

कारण खोज्नुको सट्टा विकासकर्ताले के गर्छ? .. कुनै प्रश्न छैन!

• आकार दोब्बर मूल नमूनाहरू
• DISTINCT लागू गर्नुहोस्प्रत्येक रेखाको मात्र एकल उदाहरणहरू प्राप्त गर्न

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

त्यो हो, यो स्पष्ट छ कि परिणाम, अन्तमा, ठ्याक्कै उस्तै छ, तर 2nd CTE सबक्वेरीमा "उडान" को संभावना धेरै उच्च भएको छ, र यो बिना पनि, स्पष्ट रूपमा अधिक पठनीय.

तर यो सबैभन्दा दुखद कुरा होइन। विकासकर्ताले चयन गर्न भनेपछि DISTINCT विशेषका लागि होइन, तर एकै पटक सबै क्षेत्रहरूको लागि रेकर्डहरू, त्यसपछि sub_query फिल्ड — सबक्वेरीको नतिजा — त्यहाँ स्वचालित रूपमा समावेश गरिएको थियो। अब, कार्यान्वयन गर्न DISTINCT, डाटाबेस पहिले नै कार्यान्वयन गर्न थियो 10 subqueries होइन, तर सबै <2 * N> + 10!

2.4: सबै भन्दा माथि सहयोग!

त्यसोभए, विकासकर्ताहरू बाँचिरहेका थिए - उनीहरूले चिन्ता गरेनन्, किनभने प्रयोगकर्तासँग स्पष्ट रूपमा प्रत्येक पछिको "पृष्ठ" प्राप्त गर्नको लागि पुरानो मन्दीको साथ महत्त्वपूर्ण N मानहरूमा रजिस्ट्रीलाई "समायोजित" गर्न पर्याप्त धैर्य थिएन।

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

सामान्यतया, समातिएको नमूनामा N लगभग 17K को मान पुग्यो, र केवल एक दिनमा कम्तिमा 4K यस्ता अनुरोधहरू "चेनको साथ" कार्यान्वयन गरियो। तिनीहरूमध्ये अन्तिमलाई साहसपूर्वक स्क्यान गरिएको थियो प्रति पुनरावृत्ति 1GB मेमोरी...

कुल

स्रोत: www.habr.com