Ως διαχειριστές βάσεων δεδομένων, πρέπει να έχουμε εμμονή με τα εργαλεία και τις μεθόδους για τη βελτίωση της απόδοσης της βάσης δεδομένων.
Στην PostgreSQL, έχουμε πρόσβαση στην εντολή EXPLAIN ANALYZE που μας επιτρέπει να αναλύσουμε το σχέδιο εκτέλεσης και την απόδοση ενός δεδομένου ερωτήματος βάσης δεδομένων. Η εντολή επιστρέφει λεπτομερείς πληροφορίες σχετικά με τον τρόπο με τον οποίο η μηχανή βάσης δεδομένων επεξεργάζεται το ερώτημα. Αυτό περιλαμβάνει τη σειρά των πράξεων που εκτελούνται, το εκτιμώμενο κόστος αναζήτησης, το χρονοδιάγραμμα εκτέλεσης και άλλα.
Στη συνέχεια, μπορούμε να χρησιμοποιήσουμε αυτές τις πληροφορίες για να προσδιορίσουμε τα ερωτήματα της βάσης δεδομένων καθώς και να εντοπίσουμε και να διορθώσουμε τα πιθανά σημεία συμφόρησης στην απόδοση.
Αυτό το σεμινάριο περιγράφει τον τρόπο χρήσης της εντολής EXPLAIN ANALYZE στο PostgreSQL για προβολή και βελτιστοποίηση της απόδοσης του ερωτήματος.
PostgreSQL EXPLAIN ANALYZE
Η εντολή είναι αρκετά απλή. Αρχικά, πρέπει να προβάλουμε την εντολή EXPLAIN ANALYZE στην αρχή του ερωτήματος που θέλουμε να αναλύσουμε.
Η σύνταξη της εντολής είναι η εξής:
ΕΞΗΓΗΣΤΕ ΑΝΑΛΥΣΗΜόλις εκτελέσετε την εντολή, η PostgreSQL επιστρέφει μια λεπτομερή έξοδο σχετικά με το παρεχόμενο ερώτημα.
Κατανόηση της εξόδου ερωτήματος EXPLAIN ANALYZE
Όπως αναφέρθηκε, μόλις εκτελέσουμε την εντολή EXPLAIN ANALYZE, η PostgreSQL δημιουργεί μια λεπτομερή αναφορά του σχεδίου ερωτήματος και των στατιστικών εκτέλεσης.
Η έξοδος αποτελείται από ένα σύνολο στηλών που περιέχουν χρήσιμες πληροφορίες. Οι στήλες που προκύπτουν είναι όπως φαίνεται με την αντίστοιχη σημασία τους:
ΣΧΕΔΙΟ QUERY – Αυτή η στήλη εμφανίζει το σχέδιο εκτέλεσης του καθορισμένου ερωτήματος. Το σχέδιο εκτέλεσης αναφέρεται σε μια ακολουθία λειτουργιών που εκτελεί η μηχανή βάσης δεδομένων για να ολοκληρώσει το ερώτημα με επιτυχία.
ΣΧΕΔΙΟ – Η δεύτερη στήλη είναι η στήλη ΣΧΕΔΙΟ. Αυτό περιέχει μια αναπαράσταση κειμένου για κάθε λειτουργία ή βήμα στο σχέδιο εκτέλεσης. Και πάλι, κάθε λειτουργία έχει εσοχή για να υποδείξει την ιεραρχία των λειτουργιών.
ΣΥΝΟΛΙΚΟ ΚΟΣΤΟΣ – Η στήλη συνολικού κόστους αντιπροσωπεύει το εκτιμώμενο συνολικό κόστος του ερωτήματος. Το κόστος αναφέρεται σε ένα σχετικό μέτρο που χρησιμοποιεί ο προγραμματιστής ερωτημάτων βάσης δεδομένων για να καθορίσει το βέλτιστο σχέδιο εκτέλεσης.
ΠΡΑΓΜΑΤΙΚΕΣ ΣΕΙΡΕΣ – Αυτή η στήλη δείχνει τον ακριβή αριθμό των γραμμών που υποβάλλονται σε επεξεργασία σε κάθε βήμα της εκτέλεσης του ερωτήματος.
ΠΡΑΓΜΑΤΙΚΗ ΩΡΑ – Αυτή η στήλη δείχνει τον πραγματικό χρόνο που χρειάστηκε για κάθε λειτουργία, ο οποίος περιλαμβάνει τόσο τον χρόνο εκτέλεσης της πράξης όσο και τον χρόνο που αφιερώθηκε σε πόρους.
ΧΡΟΝΟΣ ΠΡΟΓΡΑΜΜΑΤΙΣΜΟΥ – Αυτή η στήλη δείχνει τον χρόνο που χρειάζεται ο προγραμματιστής ερωτημάτων για να δημιουργήσει ένα σχέδιο εκτέλεσης. Αυτό περιλαμβάνει τον συνολικό χρόνο της βελτιστοποίησης του ερωτήματος και τη δημιουργία σχεδίου.
ΧΡΟΝΟΣ ΕΚΤΕΛΕΣΗΣ – Αυτή η στήλη δείχνει τον συνολικό χρόνο εκτέλεσης του ερωτήματος. Αυτό περιλαμβάνει επίσης τον χρόνο που δαπανάται για τον προγραμματισμό και τον χρόνο εκτέλεσης ερωτημάτων.
PostgreSQL EXPLAIN ANALYZE Παράδειγμα
Ας δούμε μερικά βασικά παραδείγματα χρήσης της πρότασης EXPLAIN ANALYZE.
Παράδειγμα 1: Επιλέξτε Δήλωση
Ας χρησιμοποιήσουμε την πρόταση EXPLAIN ANALYZE για να δείξουμε την εκτέλεση μιας απλής πρότασης επιλογής στο PostgreSQL.
Μόλις εκτελέσουμε την προηγούμενη πρόταση, θα πρέπει να λάβουμε μια έξοδο ως εξής:
ΣΧΕΔΙΟ QUERY-------------------------------------------------- ------------------
Seq Scan σε wp_users (κόστος=0,00..10.38 σειρές=10 πλάτος=2256) (πραγματικός χρόνος=0.009..0.010 σειρές=7 βρόχοι=1)
Φίλτρο: (αναγνωριστικό > 3)
Σειρές που αφαιρέθηκαν με φίλτρο: 3
Χρόνος προγραμματισμού: 0,995 ms
Χρόνος εκτέλεσης: 0,021 ms
(5 σειρές)
Σε αυτήν την περίπτωση, μπορούμε να δούμε ότι η ενότητα 'Σχέδιο ερωτήματος' υποδεικνύει ότι το ερώτημα εκτελεί μια διαδοχική σάρωση στον πίνακα wp_users. Η γραμμή φίλτρου υποδηλώνει τη συνθήκη που χρησιμοποιείται για το φιλτράρισμα των σειρών που προκύπτουν.
Στη συνέχεια, βλέπουμε το 'Rows Removed by Filter' που δείχνει τον αριθμό των σειρών που εξαλείφονται από τη συνθήκη φίλτρου.
Τέλος, ο χρόνος εκτέλεσης δείχνει τον συνολικό χρόνο εκτέλεσης του ερωτήματος. Σε αυτήν την περίπτωση, το ερώτημα διαρκεί 0,021ms.
Παράδειγμα 2: Ανάλυση Συμμετοχής
Ας πάρουμε ένα πιο σύνθετο ερώτημα που περιλαμβάνει μια σύνδεση SQL. Για αυτό, χρησιμοποιούμε το δείγμα βάσης δεδομένων Pagila. Μπορείτε να κάνετε λήψη και να εγκαταστήσετε το δείγμα βάσης δεδομένων στο μηχάνημά σας για λόγους επίδειξης.
Μπορούμε να εκτελέσουμε μια απλή ένωση όπως φαίνεται παρακάτω:
εξηγώ αναλύω SELECT f.title, c.nameΑΠΟ την ταινία f
JOIN film_category fc ON f.film_id = fc.film_id
ΕΓΓΡΑΦΕΙΤΕ στην κατηγορία c ON fc.category_id = c.category_id;
Μόλις εκτελέσουμε το συγκεκριμένο ερώτημα, θα πρέπει να δούμε την έξοδο ως εξής:
Ας εξερευνήσουμε το ακόλουθο σχέδιο ερωτημάτων:
- Ένθετος βρόχος – Αυτό υποδηλώνει ότι η ένωση χρησιμοποιεί μια στρατηγική ένωσης ένθετου βρόχου.
- Hash Join – Αυτή η λειτουργία ενώνει τους πίνακες film_category και film χρησιμοποιώντας έναν αλγόριθμο Hash join. Αυτή η λειτουργία έχει κόστος 77,50 και εκτιμάται ότι είναι 1000 σειρές. Ωστόσο, ο πραγματικός χρόνος που απαιτείται για αυτήν τη λειτουργία είναι 0,254 έως 0,439 χιλιοστά του δευτερολέπτου και ανακτά 1000 σειρές.
- Συνθήκη κατακερματισμού – Αυτό υποδηλώνει ότι η συνθήκη σύνδεσης χρησιμοποιεί μια σύνδεση κατακερματισμού για να ταιριάζει με τις στήλες film_id και τις στήλες film_category στους πίνακες φιλμ.
- Seq Scan σε film_category – Αυτή η λειτουργία εκτελεί μια διαδοχική σάρωση στον πίνακα film_category με κόστος 16,00 και εκτιμώμενες 1000 σειρές. Ο πραγματικός χρόνος που απαιτείται για αυτήν τη λειτουργία είναι 0,008 έως 0,056 χιλιοστά του δευτερολέπτου και ανακτά 1000 σειρές.
- Seq Scan on film – Το ερώτημα εκτελεί μια διαδοχική σάρωση στον πίνακα φιλμ με το εκτιμώμενο και πραγματικό κόστος και τις σειρές που προκύπτουν σε αυτήν τη λειτουργία.
- Απομνημόνευση – Αυτή η λειτουργία αποθηκεύει τα αποτελέσματα της ένωσης μεταξύ των πινάκων κατηγορίας_φιλμ και ταινιών για μεταγενέστερη χρήση.
- Κλειδί προσωρινής μνήμης – Αυτό υποδηλώνει ότι το κλειδί κρυφής μνήμης που χρησιμοποιείται για την απομνημόνευση βασίζεται στη στήλη «αναγνωριστικό_κατηγορίας» από την κατηγορία film_category.
- Λειτουργία προσωρινής μνήμης – Αυτό υποδηλώνει ότι το ερώτημα χρησιμοποιεί τη λειτουργία λογικής προσωρινής μνήμης.
- Hits, Misses, Evictions, Overflows – Οι τρεις γραμμές παρέχουν στατιστικά στοιχεία σχετικά με την προσωρινή μνήμη, τον αριθμό των επισκέψεων, τις αστοχίες, τις εξώσεις και τις υπερχειλίσεις κατά τη διάρκεια της εκτέλεσης. Αυτό το μπλοκ περιλαμβάνει επίσης τη χρήση της μνήμης κατά την εκτέλεση του ερωτήματος.
- Index Scan using category_pkey – Αυτό δείχνει τη λειτουργία που εκτελεί μια σάρωση ευρετηρίου στον πίνακα κατηγορίας χρησιμοποιώντας το ευρετήριο του πρωτεύοντος κλειδιού.
- Κατάσταση ευρετηρίου – Αυτό δείχνει ότι η σάρωση ευρετηρίου βασίζεται στη συνθήκη που αντιστοιχεί στη στήλη κατηγορία_αναγνωριστικό στον πίνακα κατηγορίας.
- Χρόνος προγραμματισμού – Αυτή η γραμμή δείχνει τον χρόνο που απαιτείται για τον προγραμματισμό ερωτήματος που είναι 3,005 χιλιοστά του δευτερολέπτου.
- Χρόνος εκτέλεσης – Τέλος, αυτή η γραμμή δείχνει τον συνολικό χρόνο εκτέλεσης του ερωτήματος που είναι 0,745 χιλιοστά του δευτερολέπτου.
Ορίστε το! Λεπτομερείς πληροφορίες σχετικά με την εκτέλεση μιας απλής σύνδεσης στο PostgreSQL.
συμπέρασμα
Ανακαλύψατε τη δύναμη και τη χρήση της δήλωσης EXPLAIN ANALYZE στο PostgreSQL. Η δήλωση EXPLAIN ANALYZE είναι ένα ισχυρό εργαλείο για ανάλυση και βελτιστοποίηση ερωτημάτων. Χρησιμοποιήστε αυτό το εργαλείο για να δημιουργήσετε αποτελεσματικά και λιγότερο απαιτητικά ερωτήματα.