अनुक्रमण और क्वेरी अनुकूलन

1) अनुक्रमण और अनुकूलन लक्ष्य

विलंबता: P50/P95/P99 कमी।

थ्रूपुट: स्केल-आउट के बिना QPS विकास।

भविष्यवाणी: प्रतिक्रिया समय में स्थिर योजनाएं और कोई "कूदता" नहीं।

बचत: कम IO/CPU, कम क्लाउड बिल।

विश्वसनीयता: सही पहुंच के कारण ताले और गतिरोध को कम करना।

• किसी भी अनुकूलन को शुद्धता और स्थिरता बनाए रखना चाहिए।
• मैट्रिक्स और प्लान लॉग में प्रभाव को ट्रैक करें।

2) बुनियादी सूचकांक संरचनाएं और उन्हें कब लागू करना है

2. 1 बी-ट्री (डिफ़ॉल्ट)

बराबर है/रेंज, सॉर्ट, 'ऑर्डर बाय'।

अधिकांश समय/आईडी/स्थिति फ़िल्टर के लिए अच्छा है।

2. 2 हैश

शुद्ध समानताएं ('='), स्मृति में सस्ता लेकिन क्रम से बाहर (पीजी: बाधाओं को हटा दिया गया लेकिन फिर भी आला विकल्प)।

2. 3 GIN/GiST (PostgreSQL)

GIN: arrays/JSONB कुंजी, पूर्ण पाठ (tsvector), नियंत्रण ('@>')।

GiST: जियो, रेंज, kNN।

2. 4 ब्रिन (PostgreSQL)

"स्वाभाविक रूप से क्रमबद्ध" तालिकाओं द्वारा सुपर-सस्ता सूचकांक (केवल समय के अनुसार)। बड़ी तालिकाओं के साथ समय-श्रृंखला के लिए अच्छा है।

2. 5 बिटमैप (MySQL/InnoDB: कोई मूल नहीं; DW-DBMS/OLAP)

कम कार्डिनैलिटी और पहलुओं के लिए प्रभावी, स्तंभ भंडारण में अधिक बार।

2. 6 स्तंभ सूचकांक (क्लिकहाउस)

प्राथमिक कुंजी + डेटा स्किपिंग (मिनमैक्स), माध्यमिक через 'स्किप इंडेक्स' (खिलना, सेट)।

एकत्रीकरण और रेंज के साथ OLAP प्रश्न।

2. 7 उलटा सूचकांक (Elasticsearch/OpenSearch)

पूर्ण पाठ, पहलू, संकर खोज। सटीक फिल्टर के लिए, कीवर्ड फ़ील्ड और डॉक मान का उपयोग करें।

2. 8 MongoDB

एकल, यौगिक, मल्टीकी (सरणी), आंशिक, टीटीएल, पाठ, हैशेड (समान कुंजी शार्डिंग के लिए)।

3) कुंजी और समग्र सूचकांक डिजाइन

3. 1 वाम उपसर्ग नियम

सूचकांक में क्षेत्रों का क्रम उपयोगिता निर्धारित करता है।

क्वेरी 'WHORE =? और created_at> =? ऑर्डर बाय created_at DESC '→ индекс' (tenant_id, created_at DESC, id DESC) '।

3. 2 टाई-ब्रेकर

स्थिर छंटाई और पृष्ठभूमि की तलाश के लिए एक अद्वितीय पूंछ (आमतौर पर 'आईडी') जोड़ें।

3. 3 आंशिक/फ़िल्टर किए गए सूचकांक

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3. 4 सूचकांकों को कवर करना

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3. 5 कार्यात्मक/गणना की गई

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) विभाजन और शार्टिंग

4. 1 विभाजन (पीजी देशी/तालिका विरासत; MySQL RANGE/LIST)

समय द्वारा पार्टियों का घुमाव ('दैनिक/साप्ताहिक') 'VACUUM/DELETE' को सरल बनाता है।

इंडेक्स स्थानीय विभाजन हैं - बी-ट्री से छोटे, तेज योजना।

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4. 2 पार्टिशनिंग कुंजी

OLTP में - 'tenant _ id' (लोड स्थानीयकरण)।

समय-श्रृंखला/OLAP में - 'ts' (रेंज क्वेरी) द्वारा।

हाइब्रिड: '(tenant_id, ts)' + उप-पार्टियां।

4. 3 शार्डिंग

'किरायेदार _ आईडी' या समय के अनुसार लगातार हैशिंग/रेंज-शार्ड।

क्रॉस-शार्ड क्वेरी - स्कैटर-इकट्ठा और के-वे विलय; प्रति-शार्ड कर्सर पकड़ो।

5) सांख्यिकी, कार्डिनैलिटी और योजनाएं

5. 1 अप-टू-डेट आंकड़े

स्वतः विश्लेषण सक्षम करें ('autovacum/autoanalyze'), गंदे वितरण के लिए 'default _ statics _ targe' बढ़ाएँ.

5. 2 उन्नत सांख्यिकी (पीजी)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5. 3 निष्पादन योजना

देखें 'EXPLEVE (विश्लेषण, बफर्स, VERBOSE)'; प्रमुख क्षेत्

'पंक्तियाँ', 'लूप्स', 'वास्तविक समय', 'साझा पढ़ें/हिट', 'रीचेक कॉन्ड'।

Типы शामिल हों: नेस्टेड लूप, हैश ज्वाइन, मर्ज ज्वाइन करें।

सेक स्कैन बनाम इंडेक्स स्कैन/ओनली स्कैन/बिटमैप हीप स्कैन।

5. 4 योजनाओं की स्थिरता

पैरामेटराइजेशन (तैयार बयान) एक बुरी योजना पर "छड़ी" कर सकता है। प्लान कैश रेलिंग (PG: 'plan _ cache _ mode = force_custom_plan' समस्या प्रश्नों के लिए) या "अग्रेषण" स्थिरांक का उपयोग करें।

6) जोड़ों और प्रकारों का अनुकूलन

6. 1 रणनीति

नेस्टेड लूप: आंतरिक पर छोटा बाहरी, तेज सूचकांक।

हैश ज्वाइन: बड़े सेट, हैश टेबल के लिए पर्याप्त मेमोरी।

सम्मिलित करें: क्रमबद्ध प्रविष्टियाँ, पहले से उपलब्ध क्रम में लाभप्रद।

6. शामिल होने के तहत 2 सूचकांक

'A JINT B ON = A.id' के लिए, 'B ( )' का सूचकांक।

जुड़ ने के बाद फ़िल्टर के लिए - आंतरिक तालिका के फ़िल्टर के स्तंभों पर सूचकांक।

6. 3 ट्राइएज

'ऑर्डर बाय' विथआउट एक संबंधित सूचकांक से बचें; बड़े सेटों पर छंटाई स्मृति/डिस्क द्वारा महंगा है।

7) क्वेरी फिर से लिखना

सबक्वेरी के "स्नोफ्लेक्स" से छुटकारा पाएं; JINT में विस्तार।

CTE-inline (PG ≥12 CTE डिफ़ॉल्ट इनलाइन्स का उपयोग करें, लेकिन 'MATERTIALIZED' यदि आवश्यक हो तो एक मध्यवर्ती परिणाम कर सकता है)।

'SELECT' → फ़ील्ड (IO/नेटवर्क बचत) को सूचीबद्ध करें।

अनुक्रमित रूप (पूर्व-गणना कॉलम) में 'व्हेयर' से गणना स्थानांतरित करें।

एकत्रीकरण: वृद्धिशील अद्यतन के साथ प्रारंभिक सारांश तालिकाएं/भौतिक दृश्य।

8) कसाई, सीमित और पृष्ठभूमि

बैच-सम्मिलित/अद्यतन: एक के बजाय 500-5000 बैच।

गहरे 'OFSET' के बजाय '(sort_key, id)' द्वारा पृष्ठभूमि की तलाश करें।

सॉर्ट/जॉइन (पुश-डाउन 'लिमिट') से पहले डायलिंग को सीमित करें।

9) कैचिंग और डेनोर्मलाइजेशन

एप्लिकेशन-लेवल क्वेरी-कैश (कुंजी = SQL + bind-vars + राइट्स संस्करण)।

भारी समुच्चय के लिए भौतिक विचार; रोटेशन/संदर्भ योजना।

विमुद्रीकरण - स्टोर अक्सर गणना किए गए क्षेत्रों (छूट सहित मूल्य) को पढ़ ता है लेकिन स्थिरता के लिए ट्रिगर/पृष्ठभूमि कार्य के

हॉट कीज़के लिए L2 के रूप में Redis (TTL और इवेंट डिसेबिलिटी के साथ)।

10) लोकप्रिय इंजनों की बारीकियां

10. 1 PostgreSQL

Индексы: बी-ट्री, हैश, जीआईएन/जीआईएसटी, ब्रिन, आंशिक, कार्यात्मक, शामिल।

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10. 2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

हिस्टोग्राम आंकड़े ('विश्लेषण तालिका... अद्यतन HISTOGRAM 'в 8। 0).

10. 3 क्लिकहाउस

प्राथमिक कुंजी = सॉर्ट; 'ऑर्डर बाय (tenant_id, ts, id)'।

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10. 4 MongoDB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

निदान के लिए 'संकेत ()' का उपयोग करें, 'कवर क्वेरी' के लिए देखें।

10. 5 Elasticsearch/Opensearch

कीवर्ड बनाम पाठ क्षेत्र; doc_values छंटाई/समुच्चय के लिए।

ढेर विभाजन: एकत्रीकरण - भारी; प्रतिबंधित 'साइज़' और 'कॉम्पोसाइट' एकत्रीकरण (पेजिंग) का उपयोग करें।

विश्लेषकों को शामिल न करें जहां एक सटीक तुलना की आवश्यकता है।

11) प्रतिस्पर्धा, इंटरलॉक और एमवीसीसी

लघु लेनदेन; अनावश्यक रूप से 'REPREPATABLE READ' के तहत "लंबे" से बचें।

सूचकांक संचालन भी ताले लेते हैं (थ्रूपुट कमी लिखें)।

ऑनलाइन इंडेक्सिंग की योजना: 'क्रिएट इंडेक्स CONCRETELY' (PG), 'ALGORITHM = INPLACE '/' ऑनलाइन' (MySQL)।

सूचकांक के एक घंटे/आईडी → "हॉट पेज" के लिए पूंछ में सम्मिलित; कुंजी (UUIDv7/नमक) वितरित करें।

12) अवलोकन और एसएलओ

• क्वेरी नाम से 'db _ query _ latency _ ms' (P50/P95/P99)।
• 'rows _ exected', 'rows _ reutered', 'बफर _ hit _ ratio'।
• 'deadlocks', 'lock _ wet _ ms', 'temp _ sont _ disk _ usage'।
• 'सेक स्कैन' के साथ योजनाओं का हिस्सा जहां 'इंडेक्स स्कैन' की उम्मीद थी।
• DBMS के संस्करण/मापदंडों को बदलते समय प्रतिगमन अलर्ट करता है।

• धीमी प्रश्न लॉग को सीमा के साथ सक्षम करें (उदाहरण के लिए, 200 ms).
• स्पैन के साथ प्रश्नों का सहसंबंध (trace_id)।
• समस्या क्वेरी प्लान हटाएँ और पूर्वव्यापी के लिए वस्तु भंडारण में सहेजें

• P95 '<= 150 ms' पढ़ें 'LIMT <= 50' and हॉट किरायेदार के साथ।
• P95 1000 लाइनों तक के बैचों के साथ '<= 200 ms' रिकॉर्ड करता है।

13) सुरक्षा और बहु-किरायेदारी

अभिगम नियंत्रण क्षेत्र ('किरायेदार _ id', 'मालिक _ id') पर अनुक्रमित करना आवश्यक है.

नीतियां (RLS/ABAC) प्री-फ़िल्टर होनी चाहिए; अन्यथा, अनुकूलक गलत तरीके से योजना बनाता है।

स्पष्ट पाठ में संवेदनशील क्षेत्रों को निर्दिष्ट हैश/टोकन का उपयोग करें।

14) एंटी-पैटर्न

खोज-संकेतक विकल्प के बिना डीप 'ऑफसेट'।

"सभी के लिए एक सूचकांक" - मेमोरी ओवरलोड और राइट-पाथ।

'चुनें' महत्वपूर्ण रास्ते।

फंक्शन इंडेक्स के बिना 'व्हेयर' में स्तंभ के ऊपर कार्य।

पुराने आंकड़ों के कारण अस्थिर योजनाएं।

स्थिर आदेश की प्रतीक्षा करते हुए मिसिंग 'ऑर्डर बाय'।

सूचकांक के लिए सूचकांक: ROI <0 महंगा लेखन/समर्थन के कारण।

15) कार्यान्वयन चेकलिस्ट

1. QPS और समय द्वारा शीर्ष N अनुरोध - 3-5 उम्मीदवारों का चयन करें।

2. योजनाओं को हटाएं 'EXPLEVE ANALIZE', कार्डिनै

3. डिजाइन सूचकांक: क्षेत्र क्रम, शामिल/आंशिक/कार्यात्मक।

4. बड़ी तालिकाओं (अस्थायी/किरायेदार कुंजियों) के लिए विभाजन लागू करना।

5. प्रश्नों को अधिलेखित करें: 'SELECT', इनलाइन सरल CTE, प्रतिबंधित सेट।

6. कसाई सक्षम करें और पृष्ठभूमि की तलाश करें।

7. कैश कॉन्फ़िगर करें: L1/L2, घटनाओं द्वारा विकलांगता।

8. योजनाओं की निगरानी और धीमी गति से लॉग, रीग्रेशन के लिए अलर्ट पेश करें।

9. वास्तविक डाटा वितरण के साथ लोड परीक्षण करें।

10. अद्यतन विकास दिशानिर्देश (ओआरएम संकेत, अनुक्रमण, सीमा)।

16) उदाहरणों से पहले/बाद में

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) ओआरएम और एपीआई प्रोटोकॉल

N + 1 से बचें: लालची नमूने ('शामिल हैं', 'JINT FETCH', 'प्रीलोड')।

स्पष्ट क्षेत्र अनुमान, संकेतक द्वारा पृष्ठीय।

gRPC/REST: 'पृष्ठ _ आकार' सीमित करें, 'सॉर्ट' को ठीक करें, अपारदर्शी टोकन का उपयोग करें।

योजना कैश: पैरामेटराइजेशन का उपयोग करें; प्रति कॉल "अद्वितीय" SQL उत्पन्न न करें।

18) प्रवासन और संचालन

इंडेक्स ऑनलाइन जोड़ें और INVISABLE/CONCURRENT के रूप में चिह्नित करें, परीक्षण योजनाएं, फिर स्विच करें।

सूचकांक संशोधन - नियमित स्वच्छता सफाई: पुरानी सुविधाओं के लिए डुप्लिकेट, अप्रयुक्त, "मृत"।

पार्टी रोटेशन प्लान (पुराना छोड़ दें) और 'VACUUM/OPLIZE' शेड्यूल।

19) सारांश

क्वेरी ऑप्टिमाइजेशन सिस्टम इंजीनियरिंग है: सही कुंजी और इंडेक्स, साफ-सुथरी योजनाएं, विचारशील विभाजन और शार्डिंग, प्रश्नों और ओआरएम में अनुशासन, कैशिंग और अवलोकन। वर्णित पैटर्न का पालन करके, आपको एक तेज, पूर्वानुमानित और किफायती प्रणाली मिलेगी जो डेटा विकास और लोड के लिए प्रतिरोधी है।