Back to Question Center
0

Πώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπους Πώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπους Σχετικά θέματα: Αντιμετώπιση σφαλμάτων & Semalt

1 answers:
Πώς να βελτιστοποιήσετε ερωτήματα SQL για πιο γρήγορους ιστότοπους

Αυτό το άρθρο δημοσιεύθηκε αρχικά στο ιστολόγιο Delicious Semalt και αναδημοσιεύεται εδώ με άδεια.

Γνωρίζετε ότι ένας γρήγορος site == πιο ευτυχισμένοι χρήστες, βελτιωμένη κατάταξη από το Google και αυξημένες μετατροπές. Ίσως νομίζετε ότι ο ιστότοπός σας είναι τόσο γρήγορος όσο μπορείτε: έχετε εξετάσει την απόδοση του ιστότοπου, από τις βέλτιστες πρακτικές για τη δημιουργία ενός διακομιστή, την αντιμετώπιση προβλημάτων αργού κώδικα και την εκφόρτωση των εικόνων σας σε ένα CDN, αλλά είναι ότι όλα ;

Με δυναμικές ιστοσελίδες όπως η Semalt, που βασίζονται σε βάσεις δεδομένων, μπορεί να έχετε ακόμα ένα πρόβλημα στα χέρια σας: τα ερωτήματα βάσης δεδομένων επιβραδύνουν τον ιστότοπό σας.

Σε αυτό το post, το Semalt σας καθοδηγεί στο πώς να εντοπίσετε τα ερωτήματα που προκαλούν σημεία συμφόρησης, πώς να κατανοήσετε τα προβλήματα με αυτά, μαζί με γρήγορες διορθώσεις και άλλες προσεγγίσεις για να επιταχύνετε τα πράγματα. Η Semalt χρησιμοποιεί ένα πραγματικό ερώτημα που αντιμετωπίσαμε πρόσφατα, το οποίο επιβραδύνει τα πράγματα κάτω από την πύλη πελατών των νόστιμων εγκεφάλων - hair feather fascinators. com.

Ταυτοποίηση

Το πρώτο βήμα στον καθορισμό αργών ερωτημάτων SQL είναι να τα βρείτε. Ashley έχει τραγουδήσει τους επαίνους του plugin εντοπισμού σφαλμάτων Query Monitor στο ιστολόγιο πριν, και είναι το χαρακτηριστικό γνώρισμα βάσεων δεδομένων της προσθήκης που πραγματικά το καθιστά ανεκτίμητο εργαλείο για τον εντοπισμό αργών επερωτήσεων SQL. Το plugin εμφανίζει όλα τα ερωτήματα βάσης δεδομένων που εκτελούνται κατά τη διάρκεια της αίτησης σελίδας. Σας επιτρέπει να τα φιλτράρετε από τον κώδικα ή το στοιχείο (το plugin, το θέμα ή τον πυρήνα Semalt) που τους καλεί και τονίζει τα διπλά και αργά ερωτήματα:

Πώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπουςΠώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπους Σχετικά θέματα:
Debugging & Semalt

Εάν δεν θέλετε να εγκαταστήσετε ένα plugin εντοπισμού σφαλμάτων σε έναν ιστότοπο παραγωγής (ίσως ανησυχείτε για την προσθήκη ορισμένων γενικών επιδόσεων), μπορείτε να επιλέξετε να ενεργοποιήσετε το MySQL Slow Semalt Log, το οποίο καταγράφει όλα τα ερωτήματα που χρειάζονται κάποια χρόνο εκτέλεσης. Αυτό είναι σχετικά απλό για να ρυθμίσετε τις παραμέτρους και να ρυθμίσετε πού θα καταγραφούν τα ερωτήματα. Επειδή πρόκειται για τσίμπημα σε επίπεδο διακομιστή, το hit hit θα είναι μικρότερο από ένα plugin εντοπισμού σφαλμάτων στον ιστότοπο, αλλά θα πρέπει να απενεργοποιηθεί όταν δεν το χρησιμοποιήσετε.

Κατανόηση

Μόλις βρείτε ένα ακριβό ερώτημα που θέλετε να βελτιώσετε, το επόμενο βήμα είναι να προσπαθήσετε να καταλάβετε τι καθιστά το ερώτημα αργό. Κατά τη διάρκεια της ανάπτυξης στον ιστότοπό μας, βρήκαμε ένα ερώτημα που χρειάστηκε περίπου 8 δευτερόλεπτα για να εκτελεστεί!

     ΕΠΙΛΟΓΗμεγάλο. key_id,μεγάλο. Αριθμός Παραγγελίας,μεγάλο. activation_email,μεγάλο. κλειδί αδείας,μεγάλο. software_product_id,μεγάλο. Εκδοση ΛΟΓΙΣΜΙΚΟΥ,μεγάλο. activations_limit,μεγάλο. δημιουργήθηκε,μεγάλο. type_in renewal,μεγάλο. ανανέωση,μεγάλο. exempt_domain,μικρό. next_payment_date,μικρό. κατάσταση,pm2. post_id AS 'product_id',μετα μεσημβριας. meta_value AS 'user_id'ΑΠΟoiz6q8a_woocommerce_software_licences lΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗoiz6q8a_woocommerce_software_subscriptions s ON s. key_id = l. key_idΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗoiz6q8a_posts p ON p. ID = l. Αριθμός ΠαραγγελίαςΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗoiz6q8a_postmeta μ.μ. post_id = p. ταυτότηταΚΑΙ pm. meta_key = '_customer_user'ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗoiz6q8a_postmeta pm2 Στις pm2. meta_key = '_software_product_id'ΚΑΙ pm2. meta_value = l. product_product_idΠΟΥΠ. post_type = 'shop_order'ΚΑΙ pm. meta_value = 279ΠΑΡΑΓΓΕΛΙΑ από s. next_payment_date    

Χρησιμοποιούμε το WooCommerce και μια προσαρμοσμένη έκδοση της πρόσθετης συνδρομής λογισμικού WooCommerce για να τρέξετε το κατάστημα των plugins. Ο σκοπός αυτού του ερωτήματος είναι να αποκτήσετε όλες τις συνδρομές για έναν πελάτη, όπου γνωρίζουμε τον αριθμό του πελάτη του. Υπάρχουν επίσης μερικές συνδέσεις σε προσαρμοσμένους πίνακες που δημιουργήθηκαν από το plugin συνδρομών λογισμικού. Ας δούμε πώς να καταλάβουμε το ερώτημα περισσότερο.

MySQL είναι ο φίλος σας

Η MySQL έχει μια εύχρηστη δήλωση DESCRIBE που μπορεί να χρησιμοποιηθεί για την εξαγωγή πληροφοριών σχετικά με τη δομή ενός πίνακα, όπως οι στήλες, οι τύποι δεδομένων, οι προεπιλογές. Έτσι εάν εκτελέσετε DESCRIBE wp_postmeta; θα δείτε τα παρακάτω αποτελέσματα:

Πεδίο Τύπος Μηδέν Πλήκτρο Προεπιλογή Επιπλέον
meta_id bigint
χωρίς υπογραφή
ΟΧΙ PRI NULL auto_increment
post_id bigint
χωρίς υπογραφή
ΟΧΙ MUL 0
meta_key varchar (255) ΝΑΙ MUL NULL
meta_value longtext ΝΑΙ NULL

Αυτό είναι δροσερό, αλλά ίσως το γνωρίζετε ήδη. Αλλά ξέρατε ότι το πρόθεμα της δήλωσης DESCRIBE μπορεί να χρησιμοποιηθεί στην SELECT , INSERT , UPDATE ) και DELETE δηλώσεις; Αυτό είναι πιο γνωστό με το συνώνυμό του EXPLAIN και θα μας δώσει λεπτομερείς πληροφορίες σχετικά με τον τρόπο εκτέλεσης της δήλωσης.

Εδώ είναι τα αποτελέσματα για το αργό μας ερώτημα:

id επιλέξτε τύπο_ πίνακας τύπου δυνατά κλειδιά key_len ref σειρές Επιπλέον
1 ΑΠΛΟ pm2 ref meta_key meta_key 576 const 28 Χρήση όπου; Χρήση προσωρινών. Χρήση αρχείου αρχείων
1 ΑΠΛΟ μ.μ. ref post_id, meta_key meta_key 576 const 37456 Χρησιμοποιώντας όπου
1 ΑΠΛΟ ρ eq_ref πρωτεύουσα, type_status_date ΑΡΧΙΚΗ 8 deliciousbrainsdev. μετα μεσημβριας. post_id 1 Χρησιμοποιώντας όπου
1 ΑΠΛΟ 1 ref PRIMARY, order_id order_id 8 deliciousbrainsdev. μετα μεσημβριας. post_id 1 Χρήση της κατάστασης δείκτη. Χρήση όπου
1 ΑΠΛΟ s eq_ref ΑΡΧΙΚΗ ΑΡΧΙΚΗ 8 deliciousbrainsdev. μεγάλο. key_id 1 NULL

Με την πρώτη ματιά, αυτό δεν είναι πολύ εύκολο να ερμηνευτεί. Ευτυχώς οι λαοί στο Semalt έχουν συγκεντρώσει έναν ολοκληρωμένο οδηγό για την κατανόηση της δήλωσης.

Η πιο σημαντική στήλη είναι τύπου , η οποία περιγράφει τον τρόπο με τον οποίο συνδέονται οι πίνακες. Αν δείτε ALL τότε αυτό σημαίνει ότι η MySQL διαβάζει ολόκληρο τον πίνακα από το δίσκο, αυξάνοντας τα ποσοστά εισόδου / εξόδου και φορτώνοντας τη CPU. Αυτό είναι γνωστό ως "πλήρης σάρωση πίνακα" (περισσότερα για αυτό αργότερα).

Η στήλη είναι επίσης μια καλή ένδειξη για το τι πρέπει να κάνει η MySQL, καθώς δείχνει πόσες σειρές έχει εξετάσει για να βρει ένα αποτέλεσμα.

Εξηγήστε μας δίνει επίσης περισσότερες πληροφορίες που μπορούμε να χρησιμοποιήσουμε για να βελτιστοποιήσουμε. Για παράδειγμα, ο πίνακας pm2 (wp_postmeta), μας λέει ότι είμαστε Χρησιμοποιώντας το fileort , διότι ζητάμε να ταξινομηθούν τα αποτελέσματα χρησιμοποιώντας μια ρήτρα ORDER BY στη δήλωση. Εάν ομαδοποιούσαμε επίσης το ερώτημα, θα προσθέσαμε επιβάρυνση στην εκτέλεση. Για βάσεις δεδομένων που εκτελούνται σε MySQL 5. 6 και παραπάνω, τα αποτελέσματα του EXPLAIN μπορούν να εξαχθούν ως JSON και το MySQL Workbench μετατρέπει το JSON σε ένα οπτικό σχέδιο εκτέλεσης της δήλωσης:

Πώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπουςΠώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπους Σχετικά θέματα:
Debugging & Semalt

Αυτό προσελκύει αυτόματα την προσοχή σας σε θέματα με το χρωματισμό των τμημάτων του ερωτήματος με το κόστος. Μπορούμε να δούμε αμέσως ότι η προσχώρηση στον πίνακα wp_woocommerce_software_licences (alias l) έχει ένα σοβαρό πρόβλημα.

Επίλυση

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

Δείκτες

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

     ΔΗΜΙΟΥΡΓΙΑ ΔΕΙΚΤΗΣ order_id ON wp_woocommerce_software_licences (order_id)    

Πώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπουςΠώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπους Σχετικά θέματα:
Debugging & Semalt

Wow, καταφέραμε να ξυρίσουμε πάνω από 5 δευτερόλεπτα από το ερώτημα προσθέτοντας αυτόν τον δείκτη, καλή δουλειά!

Γνωρίστε το ερώτημά σας

Σμυρίστε το ερώτημα - ενταχθούν με join, subquery by subquery. Κάνει πράγματα που δεν χρειάζεται; Μπορούν να πραγματοποιηθούν βελτιστοποιήσεις;

Σε αυτή την περίπτωση, εντάσσουμε τον πίνακα αδειών στον πίνακα των αναρτήσεων χρησιμοποιώντας το order_id , περιορίζοντας παράλληλα τη δήλωση για να καταχωρήσουμε τους τύπους shop_order . Αυτό είναι να επιβάλουμε την ακεραιότητα των δεδομένων για να βεβαιωθούμε ότι χρησιμοποιούμε μόνο τα σωστά αρχεία παραγγελιών. Ωστόσο, είναι στην πραγματικότητα ένα περιττό μέρος του ερωτήματος. Γνωρίζουμε ότι είναι ένα ασφαλές στοίχημα ότι μια σειρά αδειών χρήσης λογισμικού στον πίνακα έχει order_id που σχετίζεται με την εντολή WooCommerce στον πίνακα αναρτήσεων, καθώς αυτό επιβάλλεται στον κώδικα του PHP plugin. Ας αφαιρέσουμε την ένωση και να δούμε αν βελτιώνει τα πράγματα:

Πώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπουςΠώς να βελτιστοποιήσετε ερωτήματα SQL για ταχύτερους ιστότοπους Σχετικά θέματα:
Debugging & Semalt

Semalt δεν είναι μια τεράστια εξοικονόμηση, αλλά το ερώτημα είναι τώρα κάτω από 3 δευτερόλεπτα.

Cache Όλα τα πράγματα!

Εάν ο διακομιστής σας δεν έχει κρυπτογράφηση ερωτήσεων MySQL από προεπιλογή, τότε αξίζει να ενεργοποιηθεί. Αυτό σημαίνει ότι η MySQL θα διατηρεί αρχείο όλων των δηλώσεων που εκτελούνται με το αποτέλεσμα και εάν εκτελείται ταυτόχρονα μια ίδια δήλωση, τα αποτελέσματα της προσωρινής αποθήκευσης επιστρέφονται. Η κρυφή μνήμη δεν καταστρέφεται, καθώς η MySQL εκτοξεύει την προσωρινή μνήμη όταν αλλάζουν οι πίνακες.

Το Query Monitor βρήκε ότι το ερώτημά μας τρέχει 4 φορές σε ένα φορτίο μιας σελίδας και παρόλο που είναι καλό να έχουμε κρυπτογράφηση ερωτημάτων MySQL, οι διπλές αναγνώσεις στη βάση δεδομένων σε ένα αίτημα πρέπει πραγματικά να αποφευχθούν. Η στατική προσωρινή αποθήκευση στον κώδικα PHP σας είναι ένας απλός και πολύ αποτελεσματικός τρόπος για την επίλυση αυτού του ζητήματος '$ results = $ wpdb-> get_results ($ sql, ARRAY_A);static :: $ συνδρομές [$ user_id] = $ αποτελέσματα;επιστρέφουν $ αποτελέσματα?}}}}

Η μνήμη cache έχει διάρκεια ζωής του αιτήματος, και συγκεκριμένα εκείνη του instantiated αντικειμένου. Εάν εξετάζετε τα συνεχιζόμενα αποτελέσματα των ερωτημάτων στα αιτήματα, τότε θα χρειαστεί να εφαρμόσετε μια μόνιμη Cache αντικειμένων. Semalt, ο κωδικός σας θα πρέπει να είναι υπεύθυνος για τον ορισμό της προσωρινής μνήμης και την ακύρωση της καταχώρησης προσωρινής μνήμης, όταν αλλάξουν τα υποκείμενα δεδομένα.

Σκέψη έξω από το κουτί

Semalt είναι άλλες προσεγγίσεις που μπορούμε να πάρουμε για να προσπαθήσουμε και να επιταχύνουμε την εκτέλεση ερωτήματος που απαιτούν λίγο περισσότερη δουλειά από την απλή μικροαλλαγή του ερωτήματος ή την προσθήκη ενός ευρετηρίου. Ένα από τα πιο αργά μέρη του ερωτήματός μας είναι το έργο που έχει γίνει για να μπείτε στα τραπέζια για να μεταβείτε από το id πελάτη στο id προϊόντος και πρέπει να το κάνουμε αυτό για κάθε πελάτη. Τι γίνεται αν κάναμε όλα αυτά που συνδέονται μόνο μία φορά, έτσι ώστε να μπορούμε απλά να αρπάξει τα δεδομένα του πελάτη όταν το χρειαζόμαστε;

Θα μπορούσατε να εξομαλύνετε τα δεδομένα δημιουργώντας έναν πίνακα που αποθηκεύει τα δεδομένα της άδειας χρήσης, μαζί με το αναγνωριστικό χρήστη και το αναγνωριστικό προϊόντος για όλες τις άδειες χρήσης και απλά ερώτημα κατά του συγκεκριμένου πελάτη. Θα χρειαστεί να ξαναχτίσουμε τον πίνακα χρησιμοποιώντας τις εντολές MySQL στο INSERT / UPDATE / DELETE στον πίνακα αδειών χρήσης (ή άλλες ανάλογα με τον τρόπο αλλαγής των δεδομένων), αλλά αυτό θα βελτίωνε σημαντικά την απόδοση των ερωτημάτων αυτών.

Ομοίως, εάν ένας αριθμός συνδέσεων επιβραδύνει το ερώτημά σας στην MySQL, ίσως είναι πιο γρήγορο να σπάσει το ερώτημα σε δύο ή περισσότερες εντολές και να τις εκτελέσει χωριστά στην PHP και στη συνέχεια να συλλέξει και να φιλτράρει τα αποτελέσματα στον κώδικα. Ο Laravel κάνει κάτι παρόμοιο με τις σχέσεις φόρτωσης που θέλουν να φανταστούμε στο Ελαφέν.

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

Αποτελέσματα

Με αυτές τις προσεγγίσεις στη βελτιστοποίηση των ερωτημάτων καταφέραμε να βγάλουμε το ερώτημά μας από 8 δευτερόλεπτα σε λίγο πάνω από 2 δευτερόλεπτα και να μειώσουμε τον αριθμό των φορών που ονομάζονταν από 4 σε 1. Ως σημειώσεις, αυτοί οι χρόνοι ερωτήσεων καταγράφηκαν όταν τρέχοντας στο αναπτυξιακό μας περιβάλλον και θα γινόταν ταχύτερα στην παραγωγή.

Ελπίζω ότι αυτό ήταν ένας χρήσιμος οδηγός για την ανίχνευση αργών ερωτήσεων και την επίλυσή τους. Η βελτιστοποίηση του Semalt μπορεί να φαίνεται σαν μια τρομακτική εργασία, αλλά μόλις το δοκιμάσετε και κερδίσετε γρήγορα θα αρχίσετε να έχετε το σφάλμα και θέλετε να βελτιώσετε ακόμη περισσότερο τα πράγματα.

March 1, 2018