Πέρασαν οι εποχές που δεν έπρεπε να ανησυχείτε για τη βελτιστοποίηση της απόδοσης της βάσης δεδομένων. Ο χρόνος δεν σταματά. Κάθε νέος επιχειρηματίας τεχνολογίας θέλει να δημιουργήσει το επόμενο Facebook, ενώ προσπαθεί να συλλέξει όλα τα δεδομένα που μπορεί να πάρει στα χέρια του. Οι επιχειρήσεις χρειάζονται αυτά τα δεδομένα για να εκπαιδεύσουν καλύτερα μοντέλα που τις βοηθούν να βγάλουν χρήματα. Σε τέτοιες συνθήκες, οι προγραμματιστές πρέπει να δημιουργήσουν API που τους επιτρέπουν να εργάζονται γρήγορα και αξιόπιστα με τεράστιες ποσότητες πληροφοριών.
Εάν σχεδιάζετε εφαρμογές υποστήριξης ή βάσεων δεδομένων για μεγάλο χρονικό διάστημα, πιθανότατα έχετε γράψει κώδικα για την εκτέλεση σελιδοποιημένων ερωτημάτων. Για παράδειγμα, όπως αυτό:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Ετσι οπως ειναι?
Αλλά αν έτσι κάνατε τη σελιδοποίηση σας, λυπάμαι που δεν το κάνατε με τον πιο αποτελεσματικό τρόπο.
Θέλεις να μου κάνεις αντίρρηση; . , и Χρησιμοποιούν ήδη τις τεχνικές για τις οποίες θέλω να μιλήσω σήμερα.
Ονομάστε τουλάχιστον έναν προγραμματιστή υποστήριξης που δεν έχει χρησιμοποιήσει ποτέ OFFSET и LIMIT για την εκτέλεση σελιδοποιημένων ερωτημάτων. Στο MVP (Minimum Viable Product) και σε έργα όπου χρησιμοποιούνται μικρές ποσότητες δεδομένων, αυτή η προσέγγιση είναι αρκετά εφαρμόσιμη. «Απλώς λειτουργεί», ας πούμε.
Αλλά εάν χρειάζεται να δημιουργήσετε αξιόπιστα και αποτελεσματικά συστήματα από την αρχή, θα πρέπει να φροντίσετε εκ των προτέρων για την αποτελεσματικότητα της αναζήτησης ερωτημάτων στις βάσεις δεδομένων που χρησιμοποιούνται σε τέτοια συστήματα.
Σήμερα θα μιλήσουμε για τα προβλήματα με τις κοινώς χρησιμοποιούμενες (πολύ κακές) εφαρμογές σελιδοποιημένων μηχανών ερωτημάτων και πώς να επιτύχετε υψηλή απόδοση κατά την εκτέλεση τέτοιων ερωτημάτων.
Τι συμβαίνει με το OFFSET και το LIMIT;
Όπως ήδη είπα, OFFSET и LIMIT Έχουν καλή απόδοση σε έργα που δεν χρειάζεται να εργαστούν με μεγάλο όγκο δεδομένων.
Το πρόβλημα προκύπτει όταν η βάση δεδομένων μεγαλώνει σε τέτοιο μέγεθος που δεν χωράει πλέον στη μνήμη του διακομιστή. Ωστόσο, όταν εργάζεστε με αυτήν τη βάση δεδομένων, πρέπει να χρησιμοποιείτε σελιδοποιημένα ερωτήματα.
Για να εκδηλωθεί αυτό το πρόβλημα, πρέπει να υπάρχει μια κατάσταση στην οποία το DBMS καταφεύγει σε μια αναποτελεσματική λειτουργία σάρωσης πλήρους πίνακα σε κάθε σελιδοποιημένο ερώτημα (ενώ ενδέχεται να προκύψουν λειτουργίες εισαγωγής και διαγραφής και δεν χρειαζόμαστε παλιά δεδομένα!).
Τι είναι η «σάρωση πλήρους πίνακα» (ή «διαδοχική σάρωση πίνακα», Διαδοχική σάρωση); Αυτή είναι μια λειτουργία κατά την οποία το DBMS διαβάζει διαδοχικά κάθε γραμμή του πίνακα, δηλαδή τα δεδομένα που περιέχονται σε αυτόν, και τα ελέγχει για συμμόρφωση με μια δεδομένη συνθήκη. Αυτός ο τύπος σάρωσης πίνακα είναι γνωστό ότι είναι ο πιο αργός. Το γεγονός είναι ότι όταν εκτελείται, εκτελούνται πολλές λειτουργίες εισόδου/εξόδου που αφορούν το υποσύστημα δίσκου του διακομιστή. Η κατάσταση επιδεινώνεται από τον λανθάνοντα χρόνο που σχετίζεται με την εργασία με δεδομένα που είναι αποθηκευμένα σε δίσκους και το γεγονός ότι η μεταφορά δεδομένων από το δίσκο στη μνήμη είναι μια λειτουργία έντασης πόρων.
Για παράδειγμα, έχετε εγγραφές 100000000 χρηστών και εκτελείτε ένα ερώτημα με την κατασκευή OFFSET 50000000. Αυτό σημαίνει ότι το DBMS θα πρέπει να φορτώσει όλες αυτές τις εγγραφές (και δεν τις χρειαζόμαστε καν!), να τις τοποθετήσει στη μνήμη και μετά να λάβει, ας πούμε, 20 αποτελέσματα που αναφέρονται στο LIMIT.
Ας υποθέσουμε ότι μπορεί να μοιάζει με αυτό: "επιλέξτε σειρές από 50000 έως 50020 από 100000". Δηλαδή, το σύστημα θα χρειαστεί πρώτα να φορτώσει 50000 σειρές για να ολοκληρώσει το ερώτημα. Βλέπεις πόση περιττή δουλειά θα έχει να κάνει;
Αν δεν με πιστεύετε, ρίξτε μια ματιά στο παράδειγμα που δημιούργησα χρησιμοποιώντας τις δυνατότητες .
Παράδειγμα στο db-fiddle.com
Εκεί, στα αριστερά, στο χωράφι Schema SQL, υπάρχει κώδικας που εισάγει 100000 σειρές στη βάση δεδομένων και στα δεξιά, στο πεδίο Query SQL, εμφανίζονται δύο ερωτήματα. Το πρώτο, αργό, μοιάζει με αυτό:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Και το δεύτερο, που είναι μια αποτελεσματική λύση στο ίδιο πρόβλημα, έχει ως εξής:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Για να ικανοποιήσετε αυτά τα αιτήματα, απλώς κάντε κλικ στο κουμπί Run στην κορυφή της σελίδας. Έχοντας κάνει αυτό, συγκρίνουμε πληροφορίες σχετικά με το χρόνο εκτέλεσης του ερωτήματος. Αποδεικνύεται ότι η εκτέλεση ενός αναποτελεσματικού ερωτήματος διαρκεί τουλάχιστον 30 φορές περισσότερο από την εκτέλεση του δεύτερου (αυτός ο χρόνος διαφέρει από εκτέλεση σε εκτέλεση· για παράδειγμα, το σύστημα μπορεί να αναφέρει ότι το πρώτο ερώτημα χρειάστηκε 37 ms για να ολοκληρωθεί, αλλά η εκτέλεση του δεύτερο - 1 ms).
Και αν υπάρχουν περισσότερα δεδομένα, τότε όλα θα φαίνονται ακόμη χειρότερα (για να πειστείτε για αυτό, ρίξτε μια ματιά στο δικό μου με 10 εκατομμύρια σειρές).
Αυτό που μόλις συζητήσαμε θα πρέπει να σας δώσει κάποια εικόνα για τον τρόπο με τον οποίο επεξεργάζονται πραγματικά τα ερωτήματα της βάσης δεδομένων.
Σημειώστε ότι όσο μεγαλύτερη είναι η τιμή OFFSET — τόσο περισσότερος χρόνος θα χρειαστεί για να ολοκληρωθεί το αίτημα.
Τι πρέπει να χρησιμοποιήσω αντί του συνδυασμού OFFSET και LIMIT;
Αντί για συνδυασμό OFFSET и LIMIT Αξίζει να χρησιμοποιήσετε μια δομή κατασκευασμένη σύμφωνα με το ακόλουθο σχήμα:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Αυτή είναι η εκτέλεση ερωτήματος με σελιδοποίηση βάσει δρομέα.
Αντί να αποθηκεύετε τα τρέχοντα τοπικά OFFSET и LIMIT και να τα μεταδώσετε με κάθε αίτημα, πρέπει να αποθηκεύσετε το τελευταίο πρωτεύον κλειδί που λάβατε (συνήθως είναι αυτό ID) Και LIMIT, ως αποτέλεσμα, θα ληφθούν ερωτήματα παρόμοια με τα παραπάνω.
Γιατί; Το θέμα είναι ότι καθορίζοντας ρητά το αναγνωριστικό της τελευταίας σειράς που διαβάστηκε, λέτε το DBMS σας πού πρέπει να ξεκινήσει την αναζήτηση για τα απαραίτητα δεδομένα. Επιπλέον, η αναζήτηση, χάρη στη χρήση του κλειδιού, θα πραγματοποιηθεί αποτελεσματικά· το σύστημα δεν θα πρέπει να αποσπάται η προσοχή από γραμμές εκτός του καθορισμένου εύρους.
Ας ρίξουμε μια ματιά στην ακόλουθη σύγκριση απόδοσης διαφόρων ερωτημάτων. Εδώ είναι ένα αναποτελεσματικό ερώτημα.
Αργό αίτημα
Και εδώ είναι μια βελτιστοποιημένη έκδοση αυτού του αιτήματος.
Γρήγορο αίτημα
Και τα δύο ερωτήματα επιστρέφουν ακριβώς τον ίδιο όγκο δεδομένων. Αλλά το πρώτο χρειάζεται 12,80 δευτερόλεπτα για να ολοκληρωθεί και το δεύτερο χρειάζεται 0,01 δευτερόλεπτα. Νιώθεις τη διαφορά;
Πιθανά προβλήματα
Για να λειτουργήσει αποτελεσματικά η προτεινόμενη μέθοδος ερωτήματος, ο πίνακας πρέπει να έχει μια στήλη (ή στήλες) που να περιέχει μοναδικά, διαδοχικά ευρετήρια, όπως ένα ακέραιο αναγνωριστικό. Σε ορισμένες συγκεκριμένες περιπτώσεις, αυτό μπορεί να καθορίσει την επιτυχία της χρήσης τέτοιων ερωτημάτων για την αύξηση της ταχύτητας εργασίας με τη βάση δεδομένων.
Φυσικά, κατά τη δημιουργία ερωτημάτων, πρέπει να λάβετε υπόψη τη συγκεκριμένη αρχιτεκτονική των πινάκων και να επιλέξετε τους μηχανισμούς που θα λειτουργήσουν καλύτερα στους υπάρχοντες πίνακες. Για παράδειγμα, εάν πρέπει να εργαστείτε σε ερωτήματα με μεγάλο όγκο σχετικών δεδομένων, μπορεί να το βρείτε ενδιαφέρον άρθρο.
Εάν αντιμετωπίζουμε το πρόβλημα της έλλειψης ενός πρωτεύοντος κλειδιού, για παράδειγμα, εάν έχουμε έναν πίνακα με σχέση πολλά προς πολλά, τότε η παραδοσιακή προσέγγιση της χρήσης OFFSET и LIMIT, είναι εγγυημένο ότι μας ταιριάζει. Ωστόσο, η χρήση του μπορεί να οδηγήσει σε δυνητικά αργά ερωτήματα. Σε τέτοιες περιπτώσεις, θα συνιστούσα να χρησιμοποιήσετε ένα πρωτεύον κλειδί αυτόματης αύξησης, ακόμα κι αν χρειάζεται μόνο για τον χειρισμό σελιδοποιημένων ερωτημάτων.
Εάν ενδιαφέρεστε για αυτό το θέμα - , и - πολλά χρήσιμα υλικά.
Αποτελέσματα της
Το κύριο συμπέρασμα που μπορούμε να καταλήξουμε είναι ότι, ανεξάρτητα από το μέγεθος των βάσεων δεδομένων που μιλάμε, είναι πάντα απαραίτητο να αναλύεται η ταχύτητα εκτέλεσης του ερωτήματος. Σήμερα, η επεκτασιμότητα των λύσεων είναι εξαιρετικά σημαντική και εάν όλα έχουν σχεδιαστεί σωστά από την αρχή της εργασίας σε ένα συγκεκριμένο σύστημα, αυτό, στο μέλλον, μπορεί να σώσει τον προγραμματιστή από πολλά προβλήματα.
Πώς αναλύετε και βελτιστοποιείτε τα ερωτήματα της βάσης δεδομένων;
Πηγή: www.habr.com