Τι είναι το ευρετήριο;
Τα ευρετήρια στο MySQL ταξινομούν δεδομένα με οργανωμένο διαδοχικό τρόπο. Δημιουργούνται στη στήλη (ες) που θα χρησιμοποιηθούν για το φιλτράρισμα των δεδομένων. Σκεφτείτε ένα ευρετήριο ως μια αλφαβητικά ταξινομημένη λίστα. Είναι πιο εύκολο να αναζητήσετε ονόματα που έχουν ταξινομηθεί με αλφαβητική σειρά από αυτά που δεν έχουν ταξινομηθεί.
Η χρήση ευρετηρίου σε πίνακες που ενημερώνονται συχνά μπορεί να οδηγήσει σε κακή απόδοση. Αυτό συμβαίνει επειδή η MySQL δημιουργεί ένα νέο μπλοκ ευρετηρίου κάθε φορά που τα δεδομένα προστίθενται ή ενημερώνονται στον πίνακα. Γενικά, τα ευρετήρια πρέπει να χρησιμοποιούνται σε πίνακες των οποίων τα δεδομένα δεν αλλάζουν συχνά αλλά χρησιμοποιούνται πολύ σε επιλεγμένα ερωτήματα αναζήτησης.
Τι χρήση ευρετηρίου;
Σε κανέναν δεν αρέσει τα αργά συστήματα. Η υψηλή απόδοση του συστήματος είναι πρωταρχικής σημασίας σε όλα σχεδόν τα συστήματα βάσεων δεδομένων. Οι περισσότερες επιχειρήσεις επενδύουν σε μεγάλο βαθμό σε υλικό, έτσι ώστε η ανάκτηση δεδομένων και οι χειρισμοί να είναι πιο γρήγορες. Αλλά υπάρχει όριο στις επενδύσεις υλικού που μπορεί να κάνει μια επιχείρηση. Η βελτιστοποίηση της βάσης δεδομένων σας είναι μια φθηνότερη και καλύτερη λύση.
Η βραδύτητα στο χρόνο απόκρισης οφείλεται συνήθως στο ότι οι εγγραφές αποθηκεύονται τυχαία σε πίνακες βάσης δεδομένων. Τα ερωτήματα αναζήτησης πρέπει να περιηγούνται σε ολόκληρα τα τυχαία αποθηκευμένα αρχεία το ένα μετά το άλλο για να εντοπίσουν τα επιθυμητά δεδομένα. Αυτό οδηγεί σε βάσεις δεδομένων με χαμηλή απόδοση όσον αφορά την ανάκτηση δεδομένων από μεγάλους πίνακες. Ως εκ τούτου, χρησιμοποιούνται ευρετήρια που ταξινομούν δεδομένα για να διευκολύνουν την αναζήτηση.
Σύνταξη: Δημιουργία ευρετηρίου
Τα ευρετήρια μπορούν να οριστούν με 2 τρόπους
- Τη στιγμή της δημιουργίας τραπεζιού
- Μετά τη δημιουργία του πίνακα
Για το myflixdb περιμένουμε πολλές αναζητήσεις στη βάση δεδομένων με το πλήρες όνομα.
Θα προσθέσουμε τη στήλη "full_names" στο Ευρετήριο σε έναν νέο πίνακα "members_indexed".
Το σενάριο που φαίνεται παρακάτω μας βοηθά να το επιτύχουμε.
CREATE TABLE `members_indexed` (`membership_number` int(11) NOT NULL AUTO_INCREMENT,`full_names` varchar(150) DEFAULT NULL,`gender` varchar(6) DEFAULT NULL,`date_of_birth` date DEFAULT NULL,`physical_address` varchar(255) DEFAULT NULL,`postal_address` varchar(255) DEFAULT NULL,`contact_number` varchar(75) DEFAULT NULL,`email` varchar(255) DEFAULT NULL,PRIMARY KEY (`membership_number`),INDEX(full_names)) ENGINE=InnoDB;
Εκτελέστε το παραπάνω σενάριο SQL στον πάγκο εργασίας MySQL έναντι του "myflixdb".
Η ανανέωση του myflixdb δείχνει τον πίνακα που δημιουργήθηκε πρόσφατα με όνομα member_indexed.
Ο πίνακας "note" members_indexed έχει "full_names" στον κόμβο ευρετηρίων.
Καθώς η βάση των μελών επεκτείνεται και ο αριθμός των εγγραφών αυξάνεται, τα ερωτήματα αναζήτησης στον πίνακα Members_indexed που χρησιμοποιούν τις ρήτρες WHERE και ORDER BY θα είναι πολύ ταχύτερα σε σύγκριση με αυτά που εκτελούνται στον πίνακα μελών χωρίς να ορίζεται το ευρετήριο.
Προσθήκη βασικής σύνταξης ευρετηρίου
Το παραπάνω παράδειγμα δημιούργησε το ευρετήριο κατά τον καθορισμό του πίνακα βάσης δεδομένων. Ας υποθέσουμε ότι έχουμε ήδη ορίσει έναν πίνακα και τα ερωτήματα αναζήτησης σε αυτόν είναι πολύ αργά. Χρειάζονται πολύς χρόνος για να επιστρέψουν τα αποτελέσματα. Μετά τη διερεύνηση του προβλήματος, ανακαλύπτουμε ότι μπορούμε να βελτιώσουμε σημαντικά την απόδοση του συστήματος δημιουργώντας INDEX στη στήλη που χρησιμοποιείται πιο συχνά στον όρο WHERE.
Μπορούμε να χρησιμοποιήσουμε το ακόλουθο ερώτημα για να προσθέσουμε ευρετήριο
CREATE INDEX id_index ON table_name(column_name);
Ας υποθέσουμε ότι τα ερωτήματα αναζήτησης στον πίνακα ταινιών είναι πολύ αργά και θέλουμε να χρησιμοποιήσουμε ένα ευρετήριο στον "τίτλο ταινίας" για να επιταχύνουμε τα ερωτήματα, μπορούμε να χρησιμοποιήσουμε το ακόλουθο σενάριο για να το επιτύχουμε.
CREATE INDEX `title_index` ON `movies`(`title`);
Η εκτέλεση του παραπάνω ερωτήματος δημιουργεί ένα ευρετήριο στο πεδίο τίτλου στον πίνακα ταινιών.
Αυτό σημαίνει ότι όλα τα ερωτήματα αναζήτησης στον πίνακα ταινιών που χρησιμοποιούν τον "τίτλο" θα είναι ταχύτερα.
Ωστόσο, τα ερωτήματα αναζήτησης σε άλλα πεδία στον πίνακα ταινιών θα είναι πιο αργά σε σύγκριση με αυτά που βασίζονται στο ευρετήριο.
Σημείωση: Μπορείτε να δημιουργήσετε ευρετήρια σε πολλές στήλες, εάν είναι απαραίτητο, ανάλογα με τα πεδία που σκοπεύετε να χρησιμοποιήσετε για τη μηχανή αναζήτησης βάσης δεδομένων.
Εάν θέλετε να δείτε τα ευρετήρια που ορίζονται σε έναν συγκεκριμένο πίνακα, μπορείτε να χρησιμοποιήσετε το ακόλουθο σενάριο για να το κάνετε αυτό.
SHOW INDEXES FROM table_name;
Ας ρίξουμε μια ματιά σε όλα τα ευρετήρια που ορίζονται στον πίνακα ταινιών στο myflixdb.
SHOW INDEXES FROM `movies`;
Η εκτέλεση του παραπάνω σεναρίου στον πάγκο εργασίας MySQL έναντι του myflixdb μας δίνει αποτελέσματα στο ευρετήριο που δημιουργήθηκε.
Σημείωση: Τα πρωτεύοντα και ξένα κλειδιά στον πίνακα έχουν ήδη ευρετηριαστεί από τη MySQL. Κάθε ευρετήριο έχει το δικό του μοναδικό όνομα και εμφανίζεται επίσης η στήλη στην οποία ορίζεται.
Σύνταξη: Ευρετήριο πτώσης
Η εντολή drop χρησιμοποιείται για την κατάργηση ήδη καθορισμένων ευρετηρίων σε έναν πίνακα.
Μπορεί να υπάρχουν στιγμές που έχετε ήδη ορίσει ένα ευρετήριο σε έναν πίνακα που ενημερώνεται συχνά. Ίσως θελήσετε να καταργήσετε τα ευρετήρια σε έναν τέτοιο πίνακα για να βελτιώσετε την απόδοση των ερωτημάτων ΕΝΗΜΕΡΩΣΗ και ΕΙΣΑΓΩΓΗ Η βασική σύνταξη που χρησιμοποιείται για την κατάθεση ενός ευρετηρίου σε έναν πίνακα έχει ως εξής.
DROP INDEX `index_id` ON `table_name`;
Ας δούμε τώρα ένα πρακτικό παράδειγμα.
DROP INDEX ` full_names` ON `members_indexed`;
Η εκτέλεση της παραπάνω εντολής ρίχνει το ευρετήριο με αναγνωριστικό "full_names" από τον πίνακα Members_indexed.
Περίληψη
- Τα ευρετήρια είναι πολύ ισχυρά όσον αφορά τη σημαντική βελτίωση της απόδοσης των ερωτημάτων αναζήτησης MySQL.
- Τα ευρετήρια μπορούν να οριστούν κατά τη δημιουργία ενός πίνακα ή να προστεθούν αργότερα μετά τη δημιουργία του πίνακα.
- Μπορείτε να ορίσετε ευρετήρια σε περισσότερες από μία στήλες σε έναν πίνακα.
- Το SHOW INDEX FROM table_name χρησιμοποιείται για την εμφάνιση των καθορισμένων ευρετηρίων σε έναν πίνακα.
- Η εντολή DROP χρησιμοποιείται για την κατάργηση ενός καθορισμένου ευρετηρίου σε έναν δεδομένο πίνακα.