Μετά τη δοκιμή, εκτελέστηκε ένα ερώτημα υπό όρους σε έναν πίνακα που περιείχε περισσότερες από 4 εκατομμύρια εγγραφές και ο χρόνος ερωτήματος ήταν τόσο υψηλός όσο 40 δευτερόλεπτα. Επομένως, ο τρόπος βελτίωσης της αποτελεσματικότητας του ερωτήματος δήλωσης SQL είναι πολύ σημαντικός. Ακολουθούν διάφορες μέθοδοι βελτιστοποίησης δηλώσεων ερωτημάτων που κυκλοφορούν ευρέως στο Διαδίκτυο:
Πρώτα απ 'όλα, όταν ο όγκος δεδομένων είναι μεγάλος, θα πρέπει να προσπαθήσετε να αποφύγετε τη σάρωση του πλήρους πίνακα και να εξετάσετε το ενδεχόμενο δημιουργίας ευρετηρίων στις στήλες που εμπλέκονται στο πού και τη σειρά κατά, γεγονός που μπορεί να επιταχύνει σημαντικά την ανάκτηση δεδομένων. Ωστόσο, υπάρχουν ορισμένες περιπτώσεις όπου η ευρετηρίαση δεν λειτουργεί:
1. Προσπαθήστε να αποφύγετε τη χρήση τελεστών != ή <> στον όρο where, διαφορετικά ο κινητήρας θα εγκαταλείψει τη χρήση ευρετηρίων και θα εκτελέσει πλήρη σάρωση πίνακα.
2. Προσπαθήστε να αποφύγετε την κρίση μηδενικής τιμής στα πεδία της ρήτρας where, διαφορετικά η μηχανή θα εγκαταλείψει τη χρήση ευρετηρίων και θα εκτελέσει πλήρη σάρωση πίνακα, όπως:
Επιλέξτε αναγνωριστικό από το T όπου ο αριθμός είναι μηδενικός
Μπορείτε να ορίσετε την προεπιλεγμένη τιμή 0 στο num, να βεβαιωθείτε ότι δεν υπάρχει μηδενική τιμή στη στήλη num στον πίνακα και, στη συνέχεια, να υποβάλετε ερώτημα ως εξής:
Επιλέξτε αναγνωριστικό από το T όπου NUM=0
3. Προσπαθήστε να αποφύγετε τη χρήση του OR στον όρο where για να ενώσετε τις συνθήκες, διαφορετικά ο κινητήρας θα εγκαταλείψει τη χρήση του ευρετηρίου και θα εκτελέσει μια πλήρη σάρωση πίνακα, όπως:
Επιλέξτε αναγνωριστικό από το T όπου NUM=10 ή NUM=20
Μπορείτε να ρωτήσετε ως εξής:
Επιλέξτε αναγνωριστικό από το T όπου NUM=10
Ένωση Όλα
επιλέξτε id από t όπου num=20
4. Το ακόλουθο ερώτημα θα έχει επίσης ως αποτέλεσμα μια πλήρη σάρωση πίνακα:
Επιλέξτε αναγνωριστικό από το t όπου όνομα όπως "%abc%"
Για να βελτιώσετε την αποτελεσματικότητα, εξετάστε την αναζήτηση πλήρους κειμένου.
5. Το In και το not in θα πρέπει επίσης να χρησιμοποιούνται με προσοχή, διαφορετικά θα οδηγήσει σε πλήρη σάρωση πίνακα, όπως:
Επιλέξτε αναγνωριστικό από το t όπου num in(1,2,3)
Για συνεχείς τιμές, εάν μπορείτε να χρησιμοποιήσετε το μεταξύ, μην το χρησιμοποιήσετε σε:
Επιλέξτε αναγνωριστικό από το T όπου αριθμός μεταξύ 1 και 3
6. Εάν χρησιμοποιήσετε την παράμετρο στον όρο where, θα προκαλέσει επίσης τη σάρωση ολόκληρου του πίνακα. Επειδή η SQL επιλύει μόνο τοπικές μεταβλητές κατά το χρόνο εκτέλεσης, αλλά ο βελτιστοποιητής δεν μπορεί να αναβάλει την επιλογή των σχεδίων πρόσβασης στο χρόνο εκτέλεσης. Πρέπει να επιλεγεί κατά τη στιγμή της μεταγλώττισης. Ωστόσο, εάν δημιουργηθεί ένα σχέδιο πρόσβασης κατά τη στιγμή της μεταγλώττισης, η τιμή της μεταβλητής εξακολουθεί να είναι άγνωστη και επομένως δεν μπορεί να χρησιμοποιηθεί ως στοιχείο εισόδου για την επιλογή ευρετηρίου. Οι ακόλουθες δηλώσεις θα σαρωθούν πλήρως:
Επιλέξτε αναγνωριστικό από το t όπου num=@num
Μπορείτε να επιβάλετε στο ερώτημα να χρησιμοποιήσει ένα ευρετήριο:
Επιλέξτε αναγνωριστικό από το T With(Index(Index(Index Name)) όπου Num=@num
7. Προσπαθήστε να αποφύγετε την έκφραση πεδίων στον όρο where, κάτι που θα προκαλέσει την εγκατάλειψη του μηχανισμού χρησιμοποιώντας το ευρετήριο και την εκτέλεση πλήρους σάρωσης πίνακα. Για παράδειγμα:
Επιλέξτε αναγνωριστικό από το T όπου NUM/2=100
θα πρέπει να αλλάξει σε:
Επιλέξτε αναγνωριστικό από το T όπου NUM=100*2
8. Προσπαθήστε να αποφύγετε την εκτέλεση λειτουργιών συνάρτησης σε πεδία της ρήτρας where, κάτι που θα προκαλέσει την εγκατάλειψη της μηχανής από τη χρήση ευρετηρίων και την εκτέλεση πλήρους σάρωσης πίνακα. Για παράδειγμα:
Επιλέξτε αναγνωριστικό από το t όπου substring(όνομα,1,3)='abc' – αναγνωριστικό ονόματος που ξεκινά με abc
Επιλέξτε αναγνωριστικό από το t όπου Datediff(Day,Createdate,'2005-11-30′)=0–'2005-11-30′ Δημιουργήθηκε αναγνωριστικό
θα πρέπει να αλλάξει σε:
Επιλέξτε αναγνωριστικό από το t όπου όνομα όπως "abc%"
επιλέξτε αναγνωριστικό από το t όπου δημιουργήθηκε>='2005-11-30′ και δημιουργήθηκε<'2005-12-1′
9. Μην εκτελείτε συναρτήσεις, αριθμητικές πράξεις ή άλλες πράξεις έκφρασης στα αριστερά του "=" στην πρόταση where, διαφορετικά το σύστημα ενδέχεται να μην μπορεί να χρησιμοποιήσει σωστά το ευρετήριο.
10. Όταν χρησιμοποιείτε ένα πεδίο ευρετηρίου ως συνθήκη, εάν το ευρετήριο είναι σύνθετο ευρετήριο, τότε το πρώτο πεδίο στο ευρετήριο πρέπει να χρησιμοποιείται ως συνθήκη για να διασφαλιστεί ότι το σύστημα χρησιμοποιεί το ευρετήριο, διαφορετικά το ευρετήριο δεν θα χρησιμοποιηθεί και η σειρά των πεδίων πρέπει να είναι συνεπής με τη σειρά ευρετηρίου όσο το δυνατόν περισσότερο.
11. Μην γράφετε κάποια ερωτήματα χωρίς νόημα, όπως η δημιουργία μιας κενής δομής πίνακα:
επιλέξτε col1,col2 σε #t από t όπου 1=0
Αυτός ο τύπος κώδικα δεν επιστρέφει κανένα σύνολο αποτελεσμάτων, αλλά καταναλώνει πόρους συστήματος, επομένως θα πρέπει να αλλάξει σε κάτι σαν αυτό:
Δημιουργία πίνακα #t(...)
12. Πολλές φορές είναι καλή επιλογή να χρησιμοποιήσετε υπάρχει αντί σε:
Επιλέξτε Num από το A όπου Num In(Επιλέξτε Num από το B)
Να αντικατασταθεί με την ακόλουθη δήλωση:
Επιλέξτε Num από το A όπου υπάρχει(επιλέξτε 1 από το B όπου num=a.num)
Πράγματα που πρέπει να προσέξετε κατά τη δημιουργία ενός ευρετηρίου:
1. Δεν είναι όλα τα ευρετήρια έγκυρα για ερωτήματα, η SQL βασίζεται στα δεδομένα του πίνακα για τη βελτιστοποίηση του ερωτήματος, όταν η στήλη ευρετηρίου έχει μεγάλο όγκο διπλότυπων δεδομένων, τα ερωτήματα SQL ενδέχεται να μην χρησιμοποιούν το ευρετήριο, όπως ένας πίνακας έχει πεδία φύλο, αρσενικό, θηλυκό σχεδόν το μισό το καθένα, τότε ακόμα κι αν το ευρετήριο βασίζεται στο φύλο, δεν θα παίξει ρόλο στην αποτελεσματικότητα του ερωτήματος.
2. Όσο περισσότερα ευρετήρια δεν είναι τόσο καλύτερα, το ευρετήριο μπορεί σίγουρα να βελτιώσει την αποτελεσματικότητα της αντίστοιχης επιλογής, αλλά μειώνει επίσης την αποτελεσματικότητα της εισαγωγής και της ενημέρωσης, επειδή το ευρετήριο μπορεί να ξαναχτιστεί κατά την εισαγωγή ή την ενημέρωση, επομένως ο τρόπος δημιουργίας ενός ευρετηρίου πρέπει να εξεταστεί προσεκτικά, ανάλογα με τη συγκεκριμένη κατάσταση. Είναι καλύτερο να μην έχετε περισσότερα από 6 ευρετήρια σε έναν πίνακα και εάν είναι πάρα πολλά, σκεφτείτε εάν είναι απαραίτητο να δημιουργήσετε ευρετήρια σε ορισμένες στήλες που δεν χρησιμοποιούνται συχνά.
3. Αποφύγετε όσο το δυνατόν περισσότερο την ενημέρωση των στηλών δεδομένων ομαδοποιημένου ευρετηρίου, επειδή η σειρά των στηλών δεδομένων με ευρετήριο είναι η φυσική σειρά αποθήκευσης των εγγραφών πίνακα και μόλις αλλάξει η τιμή της στήλης, θα οδηγήσει στην προσαρμογή της σειράς ολόκληρων των εγγραφών του πίνακα, η οποία θα καταναλώσει σημαντικούς πόρους. Εάν το σύστημα εφαρμογής χρειάζεται να ενημερώνει συχνά τις στήλες ομαδοποιημένου ευρετηρίου, πρέπει να εξετάσει εάν το ευρετήριο πρέπει να δημιουργηθεί ως ομαδοποιημένο ευρετήριο.
Άλλα σημεία που πρέπει να σημειωθούν:
1. Προσπαθήστε να χρησιμοποιήσετε αριθμητικά πεδία και προσπαθήστε να μην σχεδιάζετε πεδία που περιέχουν μόνο αριθμητικές πληροφορίες ως χαρακτήρες, γεγονός που θα μειώσει την απόδοση των ερωτημάτων και των συνδέσεων και θα αυξήσει την επιβάρυνση αποθήκευσης. Αυτό συμβαίνει επειδή η μηχανή συγκρίνει κάθε χαρακτήρα στη συμβολοσειρά έναν προς έναν κατά την επεξεργασία ερωτημάτων και ενώσεων, ενώ για αριθμητικούς τύπους, χρειάζεται να συγκριθεί μόνο μία φορά.
2. Μην χρησιμοποιείτε πουθενά το select * from t, αντικαταστήστε το "*" με μια συγκεκριμένη λίστα πεδίων και μην επιστρέψετε πεδία που δεν χρησιμοποιούνται.
3. Προσπαθήστε να χρησιμοποιήσετε μεταβλητές πίνακα αντί για προσωρινούς πίνακες. Εάν η μεταβλητή πίνακα περιέχει μεγάλο όγκο δεδομένων, σημειώστε ότι το ευρετήριο είναι πολύ περιορισμένο (μόνο το ευρετήριο πρωτεύοντος κλειδιού).
4. Αποφύγετε τη συχνή δημιουργία και διαγραφή προσωρινών πινάκων για να μειώσετε την κατανάλωση πόρων πίνακα συστήματος.
5. Οι προσωρινοί πίνακες δεν μπορούν να χρησιμοποιηθούν και η κατάλληλη χρήση τους μπορεί να κάνει ορισμένες ρουτίνες πιο αποτελεσματικές, για παράδειγμα, όταν χρειάζεται να αναφέρετε επανειλημμένα έναν μεγάλο πίνακα ή ένα σύνολο δεδομένων σε έναν πίνακα που χρησιμοποιείται συνήθως. Ωστόσο, για εφάπαξ συμβάντα, είναι καλύτερο να χρησιμοποιήσετε έναν πίνακα εξαγωγής.
6. Κατά τη δημιουργία ενός προσωρινού πίνακα, εάν ο όγκος των δεδομένων που εισάγονται ταυτόχρονα είναι μεγάλος, τότε μπορείτε να χρησιμοποιήσετε την επιλογή αντί για τη δημιουργία πίνακα για να αποφύγετε την πρόκληση μεγάλου αριθμού αρχείων καταγραφής για τη βελτίωση της ταχύτητας. Εάν ο όγκος των δεδομένων δεν είναι μεγάλος, για να διευκολύνετε τους πόρους του πίνακα συστήματος, θα πρέπει πρώτα να δημιουργήσετε πίνακα και μετά να εισαγάγετε.
7. Εάν χρησιμοποιείται προσωρινός πίνακας, φροντίστε να διαγράψετε ρητά όλους τους προσωρινούς πίνακες στο τέλος της αποθηκευμένης διαδικασίας, να περικόψετε πρώτα τον πίνακα και, στη συνέχεια, να αποθέσετε τον πίνακα, ώστε να αποφύγετε ένα μακρύ κλείδωμα του πίνακα συστήματος.
8. Προσπαθήστε να αποφύγετε τη χρήση του δρομέα, επειδή η απόδοση του δρομέα είναι κακή, εάν τα δεδομένα που λειτουργούν από τον κέρσορα υπερβαίνουν τις 10.000 γραμμές, τότε θα πρέπει να εξετάσετε το ενδεχόμενο να ξαναγράψετε.
9. Πριν χρησιμοποιήσετε τη μέθοδο που βασίζεται στον κέρσορα ή τη μέθοδο του προσωρινού πίνακα, θα πρέπει πρώτα να αναζητήσετε λύσεις που βασίζονται σε σύνολα για να λύσετε το πρόβλημα και η μέθοδος που βασίζεται σε σύνολα είναι συνήθως πιο αποτελεσματική.
10. Όπως και οι προσωρινοί πίνακες, ο κέρσορας δεν μπορεί να χρησιμοποιηθεί. Η χρήση FAST_FORWARD δρομέων για μικρά σύνολα δεδομένων είναι συχνά καλύτερη από άλλες μεθόδους επεξεργασίας γραμμή προς σειρά, ειδικά εάν πρέπει να αναφέρετε πολλούς πίνακες για να λάβετε τα δεδομένα που χρειάζεστε. Οι ρουτίνες που περιλαμβάνουν το "σύνολο" στο σύνολο αποτελεσμάτων είναι συνήθως ταχύτερες από αυτές που εκτελούνται με τον κέρσορα. Εάν ο χρόνος ανάπτυξης το επιτρέπει, μπορείτε να δοκιμάσετε τόσο τις μεθόδους που βασίζονται στον κέρσορα όσο και τις μεθόδους που βασίζονται σε σύνολα για να δείτε ποια λειτουργεί καλύτερα.
11. Ρυθμίστε το SET NOCOUNT ON στην αρχή όλων των αποθηκευμένων διαδικασιών και ενεργοποιητών και ρυθμίστε το SET NOCOUNT OFF στο τέλος. Δεν χρειάζεται να στείλετε μηνύματα DONE_IN_PROC στον πελάτη μετά την εκτέλεση κάθε δήλωσης της αποθηκευμένης διαδικασίας και ενεργοποίησης.
12. Προσπαθήστε να αποφύγετε την επιστροφή μεγάλων δεδομένων στον πελάτη, εάν ο όγκος δεδομένων είναι πολύ μεγάλος, θα πρέπει να εξετάσετε εάν η αντίστοιχη ζήτηση είναι λογική.
13. Προσπαθήστε να αποφύγετε μεγάλες συναλλαγές και βελτιώστε την ικανότητα συγχρονισμού του συστήματος.
|
Δημοσιεύτηκε στις 11/5/2018 1:57:06 μ.μ.
|
|
|
Δημοσιεύτηκε στις 17/5/2018 10:12:27 π.μ.