Τεχνικές βελτιστοποίησης ερωτημάτων SQL

Αναδημοσίευση από τον Πλάτωνα

Ακολουθούν: 0

Τεχνικές βελτιστοποίησης ερωτημάτων SQL
Εικόνα από συγγραφέα

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

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

Πρέπει να κατανοήσετε τη σημασία της βελτιστοποίησης ερωτημάτων SQL. Σε αυτό το σεμινάριο, θα σας δείξω μερικές συμβουλές και κόλπα για να βελτιστοποιήσετε τα ερωτήματά σας SQL και να τα κάνετε να εκτελούνται πιο γρήγορα. Η κύρια προϋπόθεση είναι να έχετε βασικές γνώσεις SQL.

Για να ελέγξετε εάν ένα συγκεκριμένο στοιχείο υπάρχει στον πίνακα, χρησιμοποιήστε το EXIST() λέξη-κλειδί αντί για το COUNT() θα εκτελέσει το ερώτημα με πιο βελτιστοποιημένο τρόπο.

Χρησιμοποιώντας COUNT(), το ερώτημα πρέπει να μετρήσει όλες τις εμφανίσεις αυτού του συγκεκριμένου στοιχείου που μπορεί να είναι αναποτελεσματικό όταν η βάση δεδομένων είναι εκτεταμένη. Αφ 'ετέρου, EXIST() θα ελέγξει μόνο την πρώτη εμφάνιση αυτού του στοιχείου και στη συνέχεια θα σταματήσει όταν βρει την πρώτη εμφάνιση. Αυτό εξοικονομεί πολύ χρόνο.

Επίσης, ενδιαφέρεστε μόνο να βρείτε εάν ένα συγκεκριμένο στοιχείο υπάρχει ή όχι. Δεν σας ενδιαφέρει να βρείτε τον αριθμό των εμφανίσεων. Γι' αυτό επίσης EXIST() είναι καλύτερη.

SELECT EXISTS( SELECT * FROM table WHERE myColumn = 'val' );

Το παραπάνω ερώτημα θα επιστρέψει 1 εάν τουλάχιστον μία γραμμή πίνακα περιέχει μια καταχώρηση όπου μια στήλη ονομάζεται myColumn έχει τιμή ίση με val. Διαφορετικά, θα επιστρέψει 0.

Και τα δύο char και varchar Οι τύποι δεδομένων χρησιμοποιούνται για την αποθήκευση συμβολοσειρών χαρακτήρων στον πίνακα. Αλλά varchar είναι πολύ πιο αποδοτική μνήμη από char.

Ο τύπος δεδομένων char μπορεί να αποθηκεύσει μόνο τη συμβολοσειρά χαρακτήρων καθορισμένου μήκους. Εάν το μήκος της χορδής είναι μικρότερο από το σταθερό μήκος, τότε θα συμπληρώσει τα κενά κενά ώστε το μήκος της να είναι ίσο με το καθορισμένο μήκος. Αυτό θα σπαταλήσει άσκοπα τη μνήμη στο padding. Για παράδειγμα,CHAR(100) θα πάρει 100 byte μνήμης ακόμα κι αν είναι αποθηκευμένος ένας χαρακτήρας.

Από την άλλη πλευρά, ο τύπος δεδομένων varchar αποθηκεύει τη συμβολοσειρά χαρακτήρων μεταβλητού μήκους με μήκος μικρότερο από το μέγιστο μήκος που έχει καθοριστεί. Δεν συμπληρώνει τα κενά κενά και παίρνει μόνο τη μνήμη ίση με το πραγματικό μήκος της συμβολοσειράς. Για παράδειγμα, VARCHAR(100) παίρνει μόνο 1 byte μνήμης κατά την αποθήκευση ενός χαρακτήρα.

CREATE TABLE myTable ( id INT PRIMARY KEY, charCol CHAR(10), varcharCol VARCHAR(10)
);

Στο παραπάνω παράδειγμα, ένας πίνακας myTable δημιουργείται με δύο στήλες, charCol και varcharCol που έχουν τύπους δεδομένων char και varchar αντίστοιχα. charCol θα παίρνει πάντα 10 byte μνήμης. Σε αντίθεση, varcharCol παίρνει μνήμη ίση με το πραγματικό μέγεθος της συμβολοσειράς χαρακτήρων που είναι αποθηκευμένη σε αυτήν.

Πρέπει να αποφύγουμε τη χρήση υποερωτημάτων μέσα στον όρο WHERE για τη βελτιστοποίηση ενός ερωτήματος SQL. Καθώς τα δευτερεύοντα ερωτήματα μπορεί να είναι ακριβά και δύσκολο να εκτελεστούν όταν επιστρέφουν μεγάλο αριθμό σειρών.

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

Παρακάτω είναι ένα παράδειγμα για να κατανοήσετε τη διαφορά μεταξύ των δύο.

# Using a subquery
SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE country = 'INDIA' ); # Using a join operation
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'INDIA';

Στο 1ο παράδειγμα, το υποερώτημα συλλέγει πρώτα όλα τα αναγνωριστικά πελατών που ανήκουν στην ΙΝΔΙΑ και, στη συνέχεια, το εξωτερικό ερώτημα θα λάβει όλες τις παραγγελίες των επιλεγμένων αναγνωριστικών πελατών. Και στο 2ο παράδειγμα, έχουμε πετύχει το ίδιο αποτέλεσμα με την ένταξη στο customers και orders πίνακες και στη συνέχεια επιλέγοντας μόνο παραγγελίες όπου ανήκουν οι πελάτες από την ΙΝΔΙΑ.

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

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

Ακολουθεί ένα παράδειγμα για καλύτερη κατανόηση.

# Order table is larger than the Customer table # Join from a larger table to a smaller table
SELECT * FROM Order JOIN Customer ON Customer.id = Order.id # Join from a smaller table to a larger table
SELECT * FROM Customer JOIN Order ON Customer.id = Order.id

Σε αντίθεση με την LIKE ρήτρα, regexp_like χρησιμοποιείται επίσης για αναζήτηση μοτίβων. ο LIKE Ο όρος είναι ένας βασικός τελεστής αντιστοίχισης προτύπων που μπορεί να εκτελέσει μόνο βασικές λειτουργίες όπως _ or %, τα οποία χρησιμοποιούνται για την αντιστοίχιση ενός μεμονωμένου χαρακτήρα ή οποιουδήποτε αριθμού χαρακτήρων αντίστοιχα. ο LIKE Ο όρος πρέπει να σαρώσει την πλήρη βάση δεδομένων για να βρει το συγκεκριμένο μοτίβο, το οποίο είναι αργό για μεγάλους πίνακες.

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

Παρακαλώ σημειώστε ότι regexp_like ενδέχεται να μην υπάρχει σε όλα τα συστήματα διαχείρισης βάσεων δεδομένων. Η σύνταξη και η λειτουργικότητά του ενδέχεται να διαφέρουν σε άλλα συστήματα.

Ακολουθεί ένα παράδειγμα για καλύτερη κατανόηση.

# Query using the LIKE clause
SELECT * FROM mytable WHERE ( name LIKE 'A%' OR name LIKE 'B%' ); # Query using regexp_like clause
SELECT * FROM mytable WHERE regexp_like(name, '^[AB].*');

Τα παραπάνω ερωτήματα χρησιμοποιούνται για την εύρεση των στοιχείων που το όνομά τους αρχίζει με Α ή Β. Στο πρώτο παράδειγμα, LIKE χρησιμοποιείται για την αναζήτηση όλων των ονομάτων που ξεκινούν με Α ή Β. A% σημαίνει ότι ο πρώτος χαρακτήρας είναι Α. Μετά από αυτό, μπορεί να υπάρχει οποιοσδήποτε αριθμός χαρακτήρων. Στο δεύτερο παράδειγμα, regexp_like χρησιμοποιείται. Μέσα ^[AB], ^ αντιπροσωπεύει ότι το σύμβολο θα ταιριάζει στην αρχή της συμβολοσειράς, [AB] αντιπροσωπεύει ότι ο αρχικός χαρακτήρας μπορεί να είναι A ή B, και .* αντιπροσωπεύει όλους τους χαρακτήρες μετά από αυτό.

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

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

Άριαν Γκαργκ είναι B.Tech. Φοιτητής Ηλεκτρολόγος Μηχανικός, στο τελευταίο έτος του προπτυχιακού του. Το ενδιαφέρον του βρίσκεται στον τομέα της Ανάπτυξης Ιστού και της Μηχανικής Μάθησης. Έχει επιδιώξει αυτό το ενδιαφέρον και είναι πρόθυμος να εργαστεί περισσότερο προς αυτές τις κατευθύνσεις.