PostgreSQL Antipatterns: नाम से खोज के पुनरावृत्त शोधन की कहानी, या "आगे और पीछे का अनुकूलन"

देश भर के बिक्री कार्यालयों के हजारों प्रबंधक रिकॉर्ड बनाते हैं हमारा सीआरएम सिस्टम प्रतिदिन हजारों संपर्क - संभावित या मौजूदा ग्राहकों के साथ संचार के तथ्य। और इसके लिए, आपको सबसे पहले एक ग्राहक ढूंढना होगा, और अधिमानतः बहुत जल्दी। और ऐसा अक्सर नाम से होता है.

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

इसके अलावा, आगे की जांच से एक दिलचस्प उदाहरण सामने आया पहले अनुकूलन और फिर प्रदर्शन में गिरावट कई टीमों द्वारा इसके क्रमिक परिशोधन के साथ अनुरोध किया गया, जिनमें से प्रत्येक ने पूरी तरह से सर्वोत्तम इरादों के साथ काम किया।

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

[केडीपीवी अत:]

जब कोई उपयोगकर्ता नाम से "त्वरित" खोज के बारे में बात करता है तो उसका आमतौर पर क्या मतलब होता है? यह लगभग कभी भी किसी सबस्ट्रिंग जैसे "ईमानदार" खोज के रूप में सामने नहीं आता है ... LIKE '%роза%' - क्योंकि तब परिणाम में न केवल शामिल होता है 'Розалия' и 'Магазин Роза'लेकिन 'Гроза' और यहां तक ​​कि 'Дом Деда Мороза'.

उपयोगकर्ता रोजमर्रा के स्तर पर यह मानता है कि आप उसे क्या प्रदान करेंगे शब्द की शुरुआत से खोजें शीर्षक में और इसे और अधिक प्रासंगिक बनाएं इसके साथ आरंभ होता है प्रविष्टि की। और आप यह करेंगे लगभग तुरन्त - इंटरलीनियर इनपुट के लिए.

1: कार्य को सीमित करें

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

आम तौर पर, ठीक प्रकार से समस्या के लिए आवश्यकताओं को तैयार करना आधे से अधिक समाधान है। कभी-कभी सावधानीपूर्वक केस विश्लेषण का उपयोग करें परिणाम को महत्वपूर्ण रूप से प्रभावित कर सकता है.

एक अमूर्त डेवलपर क्या करता है?

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

ओह, खोज कठिन है, मैं कुछ भी नहीं करना चाहता - चलो इसे देवोप्स को दे दें! उन्हें डेटाबेस के बाहर एक खोज इंजन तैनात करने दें: स्फिंक्स, इलास्टिकसर्च,...

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

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

1.1: "ईमानदार" सबस्ट्रिंग

हम "सबस्ट्रिंग" शब्द से चिपके हुए हैं। लेकिन सबस्ट्रिंग द्वारा सूचकांक खोज के लिए (और नियमित अभिव्यक्ति द्वारा भी!) एक उत्कृष्ट है मॉड्यूल 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;

[देखें व्याख्या.टेंसर.आरयू]

कि... 26 एमएस, 31 एमबी डेटा पढ़ें और 1.7K से अधिक फ़िल्टर किए गए रिकॉर्ड - 10 खोजे गए के लिए। ओवरहेड लागत बहुत अधिक है, क्या कुछ अधिक कुशल नहीं है?

1.2: पाठ द्वारा खोजें? यह एफटीएस है!

दरअसल, 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;

[देखें व्याख्या.टेंसर.आरयू]

यहां क्वेरी निष्पादन के समानांतरीकरण से हमें थोड़ी मदद मिली, जिससे समय आधा हो गया 11 मि.से. और हमें कुल मिलाकर 1.5 गुना कम पढ़ना पड़ा 20MB. लेकिन यहां, जितना कम, उतना बेहतर, क्योंकि जितनी अधिक मात्रा में हम पढ़ते हैं, कैश मिस होने की संभावना उतनी ही अधिक होती है, और डिस्क से पढ़ा गया डेटा का प्रत्येक अतिरिक्त पृष्ठ अनुरोध के लिए संभावित "ब्रेक" होता है।

1.3: अभी भी पसंद है?

पिछला अनुरोध सभी के लिए अच्छा है, लेकिन यदि आप इसे दिन में एक लाख बार खींचेंगे तो ही यह आएगा 2TB डेटा पढ़ें. सर्वोत्तम स्थिति में, मेमोरी से, लेकिन यदि आप बदकिस्मत हैं, तो डिस्क से। तो आइए इसे छोटा करने का प्रयास करें।

आइए याद रखें कि उपयोगकर्ता क्या देखना चाहता है पहला "जिससे शुरू होता है...". तो यह अपने शुद्धतम रूप में है उपसर्ग खोज के माध्यम से text_pattern_ops! और केवल यदि हमारे पास 10 रिकॉर्ड तक "पर्याप्त नहीं" हैं जिन्हें हम ढूंढ रहे हैं, तो हमें एफटीएस खोज का उपयोग करके उन्हें पढ़ना समाप्त करना होगा:

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

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

[देखें व्याख्या.टेंसर.आरयू]

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

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

[देखें व्याख्या.टेंसर.आरयू]

ओह, कुछ अब इतना सुंदर नहीं है - ऐसा लगता है जैसे कोई सूचकांक है, लेकिन छँटाई उससे आगे निकल जाती है... बेशक, यह पिछले विकल्प की तुलना में पहले से ही कई गुना अधिक प्रभावी है, लेकिन...

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;

[देखें व्याख्या.टेंसर.आरयू]

उत्कृष्ट - छँटाई काम करती है, और संसाधन की खपत "सूक्ष्म" रहती है, "शुद्ध" एफटीएस से हजारों गुना अधिक प्रभावी! जो कुछ बचा है उसे एक ही अनुरोध में एक साथ रखना है:

(
  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 पंक्तियों की संख्या. मैं क्वेरी अनुकूलन की इस पद्धति के बारे में बात कर रहा हूँ पहले भी लिख चुका हूँ.

तो हां, अब हमारे पास टेबल पर बीट्री और जिन दोनों हैं, लेकिन सांख्यिकीय रूप से यह पता चला है 10% से भी कम अनुरोध दूसरे ब्लॉक के निष्पादन तक पहुंचते हैं. अर्थात्, कार्य के लिए पहले से ज्ञात ऐसी विशिष्ट सीमाओं के साथ, हम सर्वर संसाधनों की कुल खपत को लगभग एक हजार गुना कम करने में सक्षम थे!

1.5*: हम फ़ाइल के बिना काम कर सकते हैं

उच्चतर LIKE हमें ग़लत सॉर्टिंग का उपयोग करने से रोका गया. लेकिन USING ऑपरेटर को निर्दिष्ट करके इसे "सही रास्ते पर सेट" किया जा सकता है:

डिफ़ॉल्ट रूप से यह मान लिया गया है ASC. इसके अतिरिक्त, आप किसी क्लॉज में किसी विशिष्ट सॉर्ट ऑपरेटर का नाम निर्दिष्ट कर सकते हैं USING. सॉर्ट ऑपरेटर को बी-ट्री ऑपरेटरों के कुछ परिवार से कम या अधिक का सदस्य होना चाहिए। ASC आमतौर पर समतुल्य USING < и DESC आमतौर पर समतुल्य USING >.

हमारे मामले में, "कम" है ~<~:

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

[देखें व्याख्या.टेंसर.आरयू]

2: अनुरोध कैसे ख़राब हो जाते हैं

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

नहीं, बेशक, हमारा व्यवसाय बढ़ गया है और हमारा कार्यभार बढ़ गया है, लेकिन उतनी मात्रा में नहीं! इसका मतलब है कि यहां कुछ गड़बड़ है - आइए इसका पता लगाएं।

2.1: पेजिंग का जन्म

कुछ बिंदु पर, एक अन्य विकास टीम त्वरित सबस्क्रिप्ट खोज से रजिस्ट्री तक समान, लेकिन विस्तारित परिणामों के साथ "कूदना" संभव बनाना चाहती थी। पेज नेविगेशन के बिना रजिस्ट्री क्या है? चलो इसे खराब कर दो!

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

यानी, यह स्पष्ट है कि परिणाम, अंत में, बिल्कुल वैसा ही है, लेकिन दूसरी सीटीई सबक्वेरी में "उड़ान" की संभावना बहुत अधिक हो गई है, और इसके बिना भी, स्पष्ट रूप से अधिक पठनीय.

लेकिन ये सबसे दुखद बात नहीं है. चूँकि डेवलपर ने चयन करने के लिए कहा था DISTINCT विशिष्ट क्षेत्रों के लिए नहीं, बल्कि एक ही बार में सभी क्षेत्रों के लिए रिकॉर्ड्स, फिर सब_क्वेरी फ़ील्ड - सबक्वेरी का परिणाम - स्वचालित रूप से वहां शामिल किया गया था। अब, निष्पादित करने के लिए DISTINCT, डेटाबेस को पहले ही निष्पादित करना था 10 उपश्रेणियाँ नहीं, बल्कि सभी <2 * एन> + 10!

2.4: सहयोग सर्वोपरि!

इसलिए, डेवलपर्स जीवित रहे - उन्होंने परेशान नहीं किया, क्योंकि उपयोगकर्ता के पास स्पष्ट रूप से प्रत्येक बाद के "पेज" प्राप्त करने में पुरानी मंदी के साथ महत्वपूर्ण एन मानों के लिए रजिस्ट्री को "समायोजित" करने के लिए पर्याप्त धैर्य नहीं था।

जब तक दूसरे विभाग के डेवलपर्स उनके पास नहीं आए और ऐसी सुविधाजनक विधि का उपयोग करना चाहते थे पुनरावृत्तीय खोज के लिए - यानी, हम कुछ नमूने से एक टुकड़ा लेते हैं, इसे अतिरिक्त शर्तों के अनुसार फ़िल्टर करते हैं, परिणाम निकालते हैं, फिर अगला टुकड़ा (जो हमारे मामले में एन बढ़ाकर हासिल किया जाता है), और इसी तरह जब तक हम स्क्रीन नहीं भर देते।

सामान्य तौर पर, पकड़े गए नमूने में N लगभग 17K के मान तक पहुंच गया, और केवल एक दिन में कम से कम 4K ऐसे अनुरोधों को "श्रृंखला के साथ" निष्पादित किया गया। उनमें से अंतिम को साहसपूर्वक स्कैन किया गया प्रति पुनरावृत्ति 1GB मेमोरी...

कुल मिलाकर

स्रोत: www.habr.com