Χιλιάδες διευθυντές από γραφεία πωλήσεων σε όλη τη χώρα επισκευάζονται δεκάδες χιλιάδες επαφές καθημερινά — γεγονότα επικοινωνίας με πιθανούς ή ήδη συνεργαζόμενους πελάτες μας. Και για αυτόν τον πελάτη, πρέπει πρώτα να βρείτε, και κατά προτίμηση πολύ γρήγορα. Και αυτό συμβαίνει τις περισσότερες φορές ονομαστικά.
Ως εκ τούτου, δεν προκαλεί έκπληξη το γεγονός ότι, για άλλη μια φορά αναλύοντας "βαριά" ερωτήματα σε μια από τις πιο φορτωμένες βάσεις δεδομένων - τη δική μας , βρήκα "στην κορυφή" ερώτηση για "γρήγορη" αναζήτηση με βάση το όνομα για επαγγελματικές κάρτες.
Επιπλέον, περαιτέρω έρευνα αποκάλυψε ένα ενδιαφέρον παράδειγμα πρώτα η βελτιστοποίηση και μετά η υποβάθμιση της απόδοσης αίτημα με τη συνεπή ολοκλήρωσή του από πολλές ομάδες, καθεμία από τις οποίες ενήργησε αποκλειστικά με τις καλύτερες προθέσεις.
0: τι ήθελε ο χρήστης
[KDPV ]
Τι εννοεί συνήθως ο χρήστης όταν μιλά για μια «γρήγορη» αναζήτηση με το όνομα; Σχεδόν ποτέ δεν αποδεικνύεται ότι είναι μια "δίκαιη" αναζήτηση υποσυμβολοσειράς όπως ... LIKE '%роза%' - εξάλλου, τότε το αποτέλεσμα δεν είναι μόνο 'Розалия' и 'Магазин Роза'Αλλά 'Гроза' και ακόμη 'Дом Деда Мороза'.
Ο χρήστης σημαίνει σε επίπεδο νοικοκυριού ότι θα του παρέχετε αναζήτηση στην αρχή μιας λέξης στον τίτλο και δείξτε πιο σχετικό τι αρχίζει στις μπήκε. Και κάντε το σχεδόν αμέσως - με είσοδο δείκτη.
1: περιορίστε την εργασία
Και ακόμη περισσότερο, ένα άτομο δεν θα εισαγάγει συγκεκριμένα 'роз магаз'ώστε να πρέπει να αναζητήσετε πρόθεμα για κάθε λέξη. Όχι, είναι πολύ πιο εύκολο για έναν χρήστη να απαντήσει σε μια γρήγορη υπόδειξη για την τελευταία λέξη από το να «υπο-εισάγει» σκόπιμα τις προηγούμενες - δείτε πώς το κάνει αυτό οποιαδήποτε μηχανή αναζήτησης.
Σε γενικές γραμμές, σωστά η διαμόρφωση των απαιτήσεων για το πρόβλημα είναι περισσότερο από το ήμισυ της λύσης. Μερικές φορές προσεκτική ανάλυση περιπτώσεων χρήσης .
Τι κάνει ένας αφηρημένος προγραμματιστής;
1.0: εξωτερική μηχανή αναζήτησης
Ω, η αναζήτηση είναι δύσκολη, δεν θέλετε να κάνετε κάτι καθόλου - ας το δώσουμε στον devops! Αφήστε τους να αναπτύξουν μια μηχανή αναζήτησης εκτός της βάσης δεδομένων για εμάς: Sphinx, ElasticSearch, ...
Μια λειτουργική, αν και χρονοβόρα επιλογή όσον αφορά τον συγχρονισμό και την αποτελεσματικότητα των αλλαγών. Όχι όμως στην περίπτωσή μας, αφού η αναζήτηση πραγματοποιείται για κάθε πελάτη μόνο στο πλαίσιο των δεδομένων του λογαριασμού του. Και τα δεδομένα έχουν μια αρκετά υψηλή μεταβλητότητα - και αν τώρα ο διαχειριστής έχει εισαγάγει μια κάρτα 'Магазин Роза', μετά από 5-10 δευτερόλεπτα μπορεί ήδη να θυμηθεί ότι ξέχασε να καθορίσει το email εκεί και θέλει να το βρει και να το διορθώσει.
Επομένως - ας αναζήτηση "απευθείας στη βάση δεδομένων". Ευτυχώς, η PostgreSQL μας επιτρέπει να το κάνουμε αυτό, και περισσότερες από μία επιλογές - θα τις εξετάσουμε.
1.1: "ειλικρινής" υποσυμβολοσειρά
Εμμένουμε στη λέξη «υποσυμβολοσειρά». Αλλά ακριβώς για αναζήτηση ευρετηρίου με υποσυμβολοσειρά (ακόμα και με κανονικές εκφράσεις!) Υπάρχει ένα εξαιρετικό ! Μόνο τότε θα χρειαστεί να γίνει σωστή ταξινόμηση.
Ας προσπαθήσουμε να πάρουμε για χάρη της απλότητας του μοντέλου ένα τέτοιο πιάτο:
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;
Λοιπόν, τέτοια... 26ms, 31MB ανάγνωση δεδομένων και περισσότερες από 1.7 χιλιάδες φιλτραρισμένες εγγραφές - για 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;
Ο παραλληλισμός της εκτέλεσης του ερωτήματος μας βοήθησε λίγο εδώ, μειώνοντας τον χρόνο στο μισό 11 ms. Ναι, και έπρεπε να διαβάσουμε 1.5 φορές λιγότερο - συνολικά 300 ΜΒ. Και εδώ όσο λιγότερο - τόσο το καλύτερο, γιατί όσο μεγαλύτερος είναι ο όγκος που αφαιρούμε, τόσο μεγαλύτερες είναι οι πιθανότητες να χάσετε την προσωρινή μνήμη και κάθε επιπλέον σελίδα δεδομένων που διαβάζεται από το δίσκο είναι ένα πιθανό "φρένο" για το αίτημα.
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;
Εξαιρετική απόδοση - σύνολο 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;
Εξαιρετική - και τα έργα ταξινόμησης, και η κατανάλωση πόρων παραμένει "μικροσκοπική", χιλιάδες φορές πιο αποτελεσματικό από το "καθαρό" 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 αριθμός γραμμών. Σχετικά με αυτόν τον τρόπο βελτιστοποίησης ερωτημάτων, I .
Οπότε ναι, τώρα έχουμε btree και gin στο τραπέζι ταυτόχρονα, αλλά στατιστικά αποδείχθηκε ότι λιγότερο από το 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: πώς ξινίζουν τα αιτήματα
Τώρα αφήνουμε το αίτημά μας να «παρασκευαστεί» για έξι μήνες ή ένα χρόνο και με έκπληξη το βρίσκουμε ξανά «στην κορυφή» με δείκτες της συνολικής ημερήσιας «άντλησης» μνήμης (buffers κοινόχρηστο χτύπημα) στο 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: ΔΙΑΚΡΙΤΙΚΟ ανόητο και ανελέητο
Κάπου στη διαδικασία μιας τέτοιας εξέλιξης από το 2ο υποερώτημα χαμένος NOT LIKE κατάσταση. Είναι σαφές ότι μετά από αυτό UNION ALL άρχισε να επιστρέφει μερικές συμμετοχές δύο φορές - βρέθηκε για πρώτη φορά στην αρχή της γραμμής και μετά ξανά - στην αρχή της πρώτης λέξης αυτής της γραμμής. Στο όριο, όλες οι εγγραφές του 2ου υποερωτήματος θα μπορούσαν να ταιριάζουν με τις εγγραφές του πρώτου.
Τι κάνει ένας προγραμματιστής αντί να ψάχνει για λόγο;.. Δεν είναι ερώτηση!
- διπλασιάσει το μέγεθος αρχικά δείγματα
- επιβάλλουν ΔΙΑΚΡΙΤΙΚΟγια να λάβετε μόνο μεμονωμένες περιπτώσεις κάθε σειράς
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>;Δηλαδή, είναι σαφές ότι το αποτέλεσμα, τελικά, είναι ακριβώς το ίδιο, αλλά η ευκαιρία να "πετάξουμε" στο 2ο υποερώτημα CTE έχει γίνει πολύ υψηλότερη, και ακόμη και χωρίς αυτό, ξεκάθαρα διαβάστε περισσότερα.
Αλλά αυτό δεν είναι το πιο λυπηρό πράγμα. Δεδομένου ότι ο προγραμματιστής ζήτησε να επιλέξει DISTINCT όχι για συγκεκριμένα, αλλά για όλα τα πεδία ταυτόχρονα εγγραφές, τότε συμπεριλαμβάνεται αυτόματα το πεδίο sub_query - το αποτέλεσμα του υποερωτήματος. Τώρα, για εκτέλεση DISTINCT, η βάση δεδομένων έπρεπε να εκτελεστεί ήδη όχι 10 δευτερεύοντα ερωτήματα, αλλά όλα <2 * N> + 10!
2.4: συνεργασία πάνω από όλα!
Έτσι, οι προγραμματιστές έζησαν - δεν θρήνησαν, επειδή στο μητρώο "βίδωσε" σε σημαντικές τιμές N με μια χρόνια επιβράδυνση στη λήψη κάθε επόμενης "σελίδας", ο χρήστης σαφώς δεν είχε την υπομονή.
Μέχρι που ήρθαν σε αυτούς προγραμματιστές από άλλο τμήμα και δεν ήθελαν να χρησιμοποιήσουν μια τόσο βολική μέθοδο για επαναληπτική αναζήτηση - δηλαδή παίρνουμε ένα κομμάτι από κάποιο δείγμα, φιλτράρουμε με πρόσθετες συνθήκες, σχεδιάζουμε το αποτέλεσμα, μετά το επόμενο κομμάτι (που στην περίπτωσή μας επιτυγχάνεται αυξάνοντας το Ν) και ούτω καθεξής μέχρι να γεμίσουμε την οθόνη.
Γενικά, σε ένα πιασμένο δείγμα Το N έφτασε σχεδόν τα 17K, και σε μόλις μια μέρα, τουλάχιστον 4K τέτοια αιτήματα εκτελέστηκαν «κατά μήκος της αλυσίδας». Το τελευταίο από αυτά έχει ήδη σαρωθεί με τόλμη 1 GB μνήμης ανά επανάληψη...
Σε συνολικά
Πηγή: www.habr.com