Βελτιστοποίηση ευρετηρίου και ερωτημάτων

1) Στόχοι τιμαριθμικής αναπροσαρμογής και βελτιστοποίησης

Καθυστέρηση: μείωση P50/P95/P99.
Απόδοση: ανάπτυξη QPS χωρίς εξάπλωση.
Προβλεψιμότητα: σταθερά σχέδια και χωρίς «άλματα» στο χρόνο απόκρισης.
Εξοικονόμηση: Λιγότερη IO/ΚΜΕ, λιγότερος λογαριασμός υπολογιστικού νέφους.
Αξιοπιστία: μείωση των κλειδαριών και των αδιεξόδων λόγω της σωστής πρόσβασης.

• Κάθε βελτιστοποίηση πρέπει να διατηρεί την ορθότητα και τη συνοχή.
• Παρακολουθήστε την επίδραση σε μετρήσεις και καταγραφές προγραμματισμού.

2) Βασικές δομές δεικτών και χρόνος εφαρμογής τους

2. 1 Δένδρο Β (εξ ορισμού)

Ίσα/εύρος, ταξινόμηση, 'ORDER BY'.
Καλό για τα περισσότερα φίλτρα χρόνου/ταυτότητας/κατάστασης.

2. 2 Hash

Καθαρές ισότητες ('='), φθηνότερες στη μνήμη αλλά εκτός λειτουργίας (PG: περιορισμοί αφαιρούνται αλλά εξακολουθούν να είναι εξειδικευμένες επιλογές).

2. 3 GIN/GIST (PostgreSQL)

GIN: συστοιχίες/πλήκτρα JSONB, πλήρες κείμενο (tsvector), περιορισμός ('@>').
GIST: geo, εύρος, kNN.

2. 4 BRIN (PostgreSQL)

Υπερ-φτηνός δείκτης με πίνακες «φυσικής διαλογής» (προσθήκη μόνο ανά χρόνο). Καλό για χρονοσειρές με μεγάλους πίνακες.

2. 5 Bitmap (MySQL/InnoDB: κανένας εγγενής; DW-DBMS/OLAP)

Αποτελεσματικό για χαμηλή πληθικότητα και όψεις, πιο συχνά στην αποθήκευση στήλης.

2. 6 Δείκτες στήλης (ClickHouse)

Πρωτογενές κλειδί + παραλείψιμο δεδομένων (minmax), δευτερεύοντες δείκτες через 'skip' (άνθηση, σύνολο).
Ερωτήματα OLAP με ομαδοποιήσεις και εύρη τιμών.

2. 7 Ανεστραμμένοι δείκτες (Elasticsearch/OpenSearch)

Πλήρες κείμενο, όψεις, υβριδική αναζήτηση. Για ακριβή φίλτρα, χρησιμοποιήστε πεδία λέξεων-κλειδιών και τιμές doc.

2. 8 MongoDB

Απλό, σύνθετο, πολυ-κλειδί (συστοιχίες), μερικό, TTL, κείμενο, hashed (για ομοιόμορφο πλήκτρο).

3) Σχεδιασμός βασικών και σύνθετων δεικτών

3. 1 Αριστερός κανόνας πρόθεμα

Η σειρά των πεδίων του δείκτη καθορίζει τη χρηστικότητα.
Ερώτηση 'WHERE tenant_id =? ΚΑΙ created_at> =? ΔΙΑΤΑΞΗ ΑΠΟ created_at DESC '→ индекс' (tenant_id, created_at DESC, id DESC) '.

3. 2 Διακόπτης ισοπαλίας

Προσθήκη μιας μοναδικής ουράς (συνήθως 'id') για σταθερή διαλογή και αναζήτηση ειδοποίησης.

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 Κατάτμηση (ιθαγενής PG/κληρονομιά πίνακα; Εύρος MySQL/ΚΑΤΑΛΟΓΟΣ)

Η εναλλαγή των μερών ανά χρόνο («ημερήσια/εβδομαδιαία») απλουστεύει το «ΚΕΝΟ/ΔΙΑΓΡΑΦΗ».
Οι δείκτες είναι τοπικά χωρίσματα → μικρότερα από το B-Tree, ταχύτερο σχέδιο.

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

4. 2 Κλείδα κατάτμησης

Στο OLTP - by 'tenant _ i (load localization).
Σε χρονοσειρές/OLAP - από 'ts' (ερωτήματα εύρους).
Υβριδικό: '(tenant_id, ts)' + επιμέρους μέρη.

4. 3 Βραχίονας

Σταθερό θραύσμα/εύρος-θραύσμα από τον «ενοικιαστή _ id» ή από τον χρόνο.
Cross-shard query → scatter-gather και k-way συγχωνεύονται. κρατούν τον δρομέα ανά θραύσμα.

5) Στατιστικές, πληθικότητα και σχέδια

5. 1 Επικαιροποιημένα στατιστικά στοιχεία

Ενεργοποίηση αυτόματης ανάλυσης ('autovacuum/autoanalyze'), αύξηση 'default _ statistics _ target' για βρώμικες διανομές.

5. 2 Προηγμένες στατιστικές (PG)

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

5. 3 Σχέδιο εκτέλεσης

• 'Row ,' Loop , 'Πραγματικός χρόνος', 'Shared Read/Hit', 'Recheck Cond'.
• join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5. 4 Σταθερότητα των σχεδίων

Η παραμετροποίηση (προετοιμασμένες δηλώσεις) μπορεί να «κολλήσει» σε ένα κακό σχέδιο. Χρησιμοποιήστε το σχέδιο φραγμάτων μνήμης (PG: 'plan _ cache _ mode = force_custom_plan' για ερωτήματα προβλημάτων) ή σταθερές «προώθησης».

6) Βελτιστοποίηση ενώσεων και ειδών

6. 1 Στρατηγικές

Nested Loop: μικρός εξωτερικός, γρήγορος δείκτης για το εσωτερικό.
Hash Join: μεγάλα σύνολα, αρκετή μνήμη για hash table.
Συγχώνευση συνδέσμου: ταξινομημένες καταχωρήσεις, επωφελείς με τη σειρά που είναι ήδη διαθέσιμη.

6. 2 Δείκτες υπό ένταξη

Για το 'A JOIN B ON B.a_id = A.id', → ο δείκτης στο 'B (a_id)'.
Για το φίλτρο μετά τη σύνδεση - ο δείκτης στις στήλες του φίλτρου του εσωτερικού πίνακα.

6. 3 Τριάδα

ΑΠΟΦΥΓΗ ΑΠΟ ΤΗΝ ΠΑΡΟΥΣΑ ΔΙΑΤΑΞΗ αντίστοιχου δείκτη, η διαλογή σε μεγάλα σύνολα είναι ακριβή από μνήμη/δίσκο.

7) Επαναγραφή ερωτημάτων

Απαλλαγείτε από τις «νιφάδες χιονιού» των υποκατηγοριών. να επεκταθεί στην JOIN.
Χρησιμοποιήστε τις προεπιλεγμένες γραμμές CTE (PG ≥12 CTE, αλλά το 'MATERIALIZED' μπορεί να δεσμεύσει ένα ενδιάμεσο αποτέλεσμα αν χρειαστεί).
Αφαίρεση 'SELECT' → λίστα των πεδίων (IO/εξοικονόμηση δικτύου).
Υπολογισμοί μεταφοράς από το 'WHERE' στο τιμαριθμοποιημένο έντυπο (προκαθορισμένες στήλες).
Συγκεντρωτικά στοιχεία: προκαταρκτικοί συνοπτικοί πίνακες/υλοποιημένες απόψεις με επαυξητική ενημέρωση.

8) Ρυμούλκηση, περιορισμός και σελιδοποίηση

Εισαγωγή παρτίδας/επικαιροποίηση: 500-5000 παρτίδες αντί μιας προς μία.
Αναζήτηση σελίδας με '(sort_key, id)' αντί για βαθύ 'OFFSET'.
Περιορισμός της κλήσης πριν από τη διαλογή/joyne (push-down 'LIMIT').

9) Αποθήκευση και απομαλοποίηση

Ερωτηματολόγιο σε επίπεδο εφαρμογής (κλειδί = SQL + bind-vars + έκδοση δικαιωμάτων).
Υλοποιημένες απόψεις για βαρέα συγκεντρωτικά στοιχεία. σχέδιο περιστροφής/αναφοράς.
Απομαλοποίηση - Αποθηκεύστε συχνά υπολογισμένα πεδία (τιμή συμπεριλαμβανομένης της έκπτωσης) αλλά με εργασία ενεργοποίησης/υποβάθρου για συνέπεια.
Redis ως L2 για καυτά κλειδιά (με TTL και αναπηρία γεγονότων).

10) Οι ιδιαιτερότητες των δημοφιλών κινητήρων

10. 1 PostgreSQL

: B-Tree, Hash, GIN/GIST, BRIN, μερική, λειτουργική, ΣΥΜΠΕΡΙΛΗΨΗ.

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

Στατιστικές ιστογράμματος ('ANALYZE TABLE... Ενημερώστε το HISTOGRAM 'в 8. 0).

10. 3 ClickHouse

Πρωτογενές κλειδί = ταξινόμηση. «ORDER BY (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. Αναζήτηση/OpenSearch 5

Λέξη-κλειδί έναντι πεδίων κειμένου; για διαλογή/συγκεντρωτικά στοιχεία.
Κατάτμηση σωρού: συγκεντρωτικά στοιχεία - βαριά· περιορίζουν τις συγκεντρώσεις «μεγέθους» και χρησιμοποιούν «composite» (τηλεειδοποίηση).
Δεν περιλαμβάνονται αναλυτές όπου απαιτείται ακριβής σύγκριση.

11) Ανταγωνιστικότητα, διασυνδέσεις και MVCC

Βραχυπρόθεσμες συναλλαγές· αποφυγή της «μακράς» ανάγνωσης στο «ΕΠΑΝΑΛΑΜΒΑΝΟΜΕΝΗ ΔΙΑΒΑΣΗ» χωρίς λόγο.
Οι εργασίες του δείκτη λαμβάνουν επίσης κλειδαριές (μείωση της απόδοσης).
Σχέδιο διαδικτυακής ευρετηρίασης: 'ΔΗΜΙΟΥΡΓΙΑ ΔΕΙΚΤΗ CONCRETELY' (PG), 'ΑΛΓΟΡΙΘΜ = INPLACE '/' ONLINE' (MySQL).
Εισάγεται στην ουρά επί μία ώρα/ταυτότητα → «θερμές σελίδες» του ευρετηρίου. διανέμει το κλειδί (UUIDv7/αλάτι).

12) Παρατηρησιμότητα και SLO

• 'db _ query _ latency _ m ( ) με το όνομα της ερώτησης.
• 'σειρές _ εξετασθείσες', 'σειρές _ επιστρέφονται', 'buffer _ hit _ ratio'.
• 'deadlock ,' lock _ wait _ m , 'temp _ sort _ disk _ use'.
• Μερίδιο των σχεδίων με το 'Seq Scan' where 'Index Scan' αναμενόταν.
• Προειδοποιήσεις παλινδρόμησης κατά την αλλαγή της έκδοσης/των παραμέτρων του DBMS.

• Ενεργοποιήστε το αργό ημερολόγιο ερωτήσεων με ένα κατώφλι (για παράδειγμα, 200 ms).
• Συσχέτιση ερωτήσεων με την έκταση (trace_id).
• Αφαίρεση σχεδίων προβληματικών ερωτήσεων και αποθήκευση αντικειμένου για αναδρομική αποθήκευση.

• Διαβάστε P95 '<= 150 ms' με 'LIMIT <= 50' και ζεστό ενοικιαστή.
• καταγράφει "<= 200 m P95 με παρτίδες έως 1000 γραμμές.

13) Ασφάλεια και πολυπλοκότητα

Απαιτούνται δείκτες για τα πεδία ελέγχου πρόσβασης («ενοικιαστής _ id», «ιδιοκτήτης _ id»).
Οι πολιτικές (RLS/ABAC) πρέπει να είναι προ-φίλτρο. Διαφορετικά, ο βελτιστοποιητής σχεδιάζει εσφαλμένα.
Να μην καταγράφονται ευαίσθητα πεδία σε σαφές κείμενο. χρησιμοποιήστε χασίς/μάρκες.

14) Αντι-μοτίβα

Βαθιά «OFFSET» χωρίς αναζήτηση-δρομέας εναλλακτική λύση.
«Ένας δείκτης για όλους» - υπερφόρτωση μνήμης και διαδρομή εγγραφής.
'SELECT' in κρίσιμες διαδρομές.
Συναρτήσεις πάνω από τη στήλη στο 'WHERE' χωρίς δείκτη συνάρτησης.
Ασταθή σχέδια λόγω παλαιών στατιστικών.
ΑΓΝΟΟΥΜΕΝΗ ΔΙΑΤΑΞΗ ΜΕ ΤΗΝ ΑΝΑΜΟΝΉ ΣΤΑΘΕΡΗΣ ΠΑΡΑΓΓΕΛΙΑΣ.
Δείκτες για χάρη των δεικτών: ROI <0 λόγω δαπανηρής εγγραφής/υποστήριξης.

15) Κατάλογος ελέγχου εφαρμογής

1. Τα κορυφαία N αιτήματα της QPS και ο χρόνος επιλογής 3-5 υποψηφίων.
2. Αφαίρεση των σχεδίων 'ΕΞΗΓΗΣΗ ΑΝΑΛΥΣΗ', έλεγχος της πληθικότητας έναντι πραγματικού.
3. Δείκτες σχεδιασμού: τάξη πεδίου, ΣΥΜΠΕΡΙΛΗΨΗ/μερική/λειτουργική.
4. Εφαρμογή διαχωρισμού για μεγάλους πίνακες (κλειδιά προσωρινού/μισθωτή).
5. Αντικατάσταση ερωτημάτων: αφαίρεση 'Select', inline simple CTE, περιορισμός συνόλου.
6. Ενεργοποιήστε το butching και αναζητήστε σελιδοδείκτες.
7. Ρύθμιση μνήμης: L1/L2, αναπηρία από γεγονότα.
8. Εισαγωγή της παρακολούθησης των σχεδίων και της βραδείας καταγραφής, των προειδοποιήσεων για οπισθοδρόμηση.
9. Εκτέλεση δοκιμών φορτίου με πραγματική κατανομή δεδομένων.
10. Επικαιροποίηση των κατευθυντήριων γραμμών ανάπτυξης (υποδείξεις ORM, ευρετηρίαση, όρια).

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) Πρωτόκολλα ORM και API

Αποφύγετε N + 1: άπληστα δείγματα ('περιλαμβάνει', 'JOIN FETCH', 'preload').
Σαφείς προβολές πεδίου, με ειδοποίηση δρομέα.
gRPC/REST: όριο 'page _ size', διόρθωση 'sor , χρήση αδιαφανών σημάτων.
Σχεδιασμός κρύπτης: χρήση παραμετροποίησης. δεν παράγουν «μοναδικό» SQL ανά κλήση.

18) Μεταναστεύσεις και επιχειρήσεις

Προσθήκη δεικτών online και σήμανση ως INVISIBLE/CONCURRENT, σχέδια δοκιμών και στη συνέχεια αλλαγή.
Αναθεωρήσεις δεικτών - τακτικός καθαρισμός χώρων υγιεινής: αντίγραφα, αχρησιμοποίητα, «νεκρά» για παλαιά χαρακτηριστικά.
Πρόγραμμα περιστροφής μέρους (drop old) και πρόγραμμα «VACUUM/OPTIMIZE».

19) Περίληψη

Βελτιστοποίηση ερωτημάτων είναι η μηχανική συστημάτων: σωστά κλειδιά και ευρετήρια, τακτοποιημένα σχέδια, στοχαστική κατάτμηση και κοπή, πειθαρχία στα ερωτήματα και ORM, αποθήκευση και παρατηρησιμότητα. Ακολουθώντας τα περιγραφόμενα πρότυπα, θα αποκτήσετε ένα γρήγορο, προβλέψιμο και οικονομικό σύστημα που θα είναι ανθεκτικό στην ανάπτυξη και το φορτίο των δεδομένων.