MySQL Ευρετήριο: Οδηγός δημιουργίας, προσθήκης και απόθεσης

Τι είναι ένα MySQL Δείκτης?

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

Τα ευρετήρια έχουν ένα συμβιβασμό — κάθε ΕΙΣΑΓΩΓΗ ή ΕΝΗΜΕΡΩΣΗ πρέπει να διατηρεί το ευρετήριο, επομένως η προσθήκη πάρα πολλών ευρετηρίων σε έναν πίνακα με πολλές εγγραφές μπορεί να βλάψει τη συνολική απόδοση. Κατά γενικό κανόνα, οι στήλες ευρετηρίου που εμφανίζονται στις ρήτρες WHERE, JOIN και ORDER BY σε πίνακες που διαβάζονται συχνότερα από ό,τι γράφονται, δημιουργούν ευρετήριο.

Γιατί να χρησιμοποιήσετε ένα ευρετήριο;

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

Οι αργοί χρόνοι απόκρισης συνήθως οφείλονται στο γεγονός ότι οι γραμμές αποθηκεύονται με φυσική σειρά στον δίσκο. Χωρίς ευρετήριο, MySQL πρέπει να σαρώσει κάθε γραμμή για να βρει εκείνες που ταιριάζουν με ένα κατηγόρημα — μια «πλήρης σάρωση πίνακα». Τα ευρετήρια αφήνουν MySQL μεταβείτε κατευθείαν στις αντίστοιχες γραμμές, οι οποίες μετατρέπουν το σχέδιο ερωτήματος από O(n) σε περίπου O(log n) για αναζητήσεις B-tree.

Σύνταξη: Δημιουργία ευρετηρίου

Ένας δείκτης μπορεί να οριστεί σε δύο σημεία:

1. Κατά τη στιγμή της δημιουργίας του πίνακα.
2. Αφού ο πίνακας υπάρχει ήδη.

Παράδειγμα: Δημιουργήστε ένα ευρετήριο ενσωματωμένα με την εντολή CREATE TABLE

Για την myflixdb βάση δεδομένων, αναμένουμε πολλές αναζητήσεις στη στήλη με το πλήρες όνομα. Το παρακάτω σενάριο δημιουργεί ένα νέο members_indexed πίνακα με ευρετήριο στο full_names στήλη.

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;

Εκτελέστε το σενάριο σε MySQL Πάγκος εργασίας ενάντια στο myflixdb βάση δεδομένων.

Φρεσκάρω myflixdb να δω το νέο members_indexed τραπέζι. ο full_names η στήλη εμφανίζεται τώρα κάτω από το Δείκτες κόμβος.

Καθώς η συνδρομή αυξάνεται, τα ερωτήματα αναζήτησης members_indexed που χρησιμοποιούν WHERE και ORDER BY έναντι full_names είναι πολύ πιο γρήγορα από τα ίδια ερωτήματα στο αρχικό members πίνακα χωρίς το ευρετήριο.

Προσθήκη ευρετηρίου αφού ο πίνακας υπάρχει ήδη

Συχνά θα ανακαλύπτετε ότι ένας υπάρχοντας πίνακας χρειάζεται ένα ευρετήριο — τα ερωτήματα αναζήτησης είναι αργά και ένα σχέδιο EXPLAIN εμφανίζει μια πλήρη σάρωση πίνακα σε μια στήλη που εμφανίζεται στο WHERE. CREATE INDEX Η εντολή προσθέτει ένα ευρετήριο χωρίς να αναδημιουργήσει τον πίνακα.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Συγκεκριμένο παράδειγμα — επιτάχυνση των αναζητήσεων στο title στήλη του movies τραπέζι:

CREATE INDEX `title_index` ON `movies` (`title`);

Κάθε ερώτημα που φιλτράρεται movies.title υποστηρίζεται πλέον από το νέο ευρετήριο. Τα ερωτήματα που φιλτράρουν σε άλλες στήλες εξακολουθούν να σαρώνουν τον πίνακα, εκτός εάν έχουν το δικό τους ευρετήριο.

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

Λίστα ευρετηρίων σε έναν πίνακα

Χρήση SHOW INDEXES για να δείτε κάθε δείκτη που έχει οριστεί σε έναν πίνακα.

SHOW INDEXES FROM `table_name`;

Παράδειγμα — λίστα ευρετηρίων στο movies τραπέζι:

SHOW INDEXES FROM `movies`;

Εκτελέστε την πρόταση σε MySQL Πάγκος εργασίας κατά myflixdb για να δείτε τα υπάρχοντα ευρετήρια και τις στήλες που καλύπτουν.

Σημείωση: Τα πρωτεύοντα και τα ξένα κλειδιά καταχωρούνται αυτόματα από MySQLΚάθε ευρετήριο έχει ένα μοναδικό όνομα και παραθέτει τη/τις στήλη/ες που καλύπτει.

Σύνταξη: Δείκτης πτώσης

Χρήση DROP INDEX για να καταργήσετε ένα υπάρχον ευρετήριο από έναν πίνακα. Αυτό είναι χρήσιμο όταν ένας πίνακας με πολλές εγγραφές επιβραδύνεται από ένα ευρετήριο που δεν διατηρεί πλέον την αξία του στην πλευρά ανάγνωσης.

DROP INDEX `index_id` ON `table_name`;

Συγκεκριμένο παράδειγμα — παραλείψτε το full_names ευρετήριο από members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Τύποι MySQL Δείκτες

MySQL Υποστηρίζει διάφορους τύπους ευρετηρίου, ο καθένας κατάλληλος για διαφορετικό φόρτο εργασίας.

Χαρακτηριστικά	Σκοπός
Κύριο κλειδί	Μοναδικό αναγνωριστικό γραμμής· ομαδοποιημένο με τα δεδομένα πίνακα στο InnoDB.
ΜΟΝΑΔΙΚΌΣ	Επιβάλλει τη μοναδικότητα ενώ χρησιμεύει ως ευρετήριο.
ΕΥΡΕΤΗΡΙΟ (Δέντρο B)	Προεπιλεγμένος δευτερεύων δείκτης που χρησιμοποιείται για ερωτήματα εύρους και αναζητήσεις ισότητας.
ΠΛΗΡΕΣ ΚΕΙΜΕΝΟ	Βελτιστοποιημένο για αναζήτηση κειμένου σε φυσική γλώσσα με MATCH … AGAINST.
ΧΩΡΙΚΗ	Ευρετήριο R-tree για τύπους δεδομένων GIS όπως POINT και POLYGON.
ΧΑΣΙΣΙ	Αναζητήσεις ισότητας σταθερού χρόνου· χρησιμοποιούνται από τη μηχανή αποθήκευσης MEMORY.
Σύνθετο (πολυστηλικό)	Συνδυάζει πολλές στήλες σε ένα ευρετήριο· τηρεί τον κανόνα του προθέματος που βρίσκεται στην άκρη αριστερά.

καλυτερα Πρακτικές για MySQL Δείκτες

Οι παρακάτω συνήθειες διατηρούν τους δείκτες χρήσιμους και τους εμποδίζουν να γίνουν περιττοί.

• Ευρετήριο για το μοτίβο ερωτήματος, όχι για το όνομα της στήλης: προσθέστε ευρετήρια που ταιριάζουν με πραγματικές ρήτρες WHERE, JOIN και ORDER BY, όχι "κάθε στήλη που ακούγεται σημαντική".
• Παρακολουθήστε τη σειρά του σύνθετου δείκτη: Η κύρια στήλη πρέπει να εμφανίζεται στο ερώτημα για να χρησιμοποιηθεί το ευρετήριο.
• Αποφύγετε τα διπλότυπα ευρετήρια: Ένα αρχικό πρόθεμα ενός σύνθετου ευρετηρίου καλύπτει ήδη αναζητήσεις σε μία στήλη σε αυτό το πρόθεμα.
• Επιθεωρήστε με EXPLAIN: επιβεβαιώστε ότι ο σχεδιαστής επιλέγει πράγματι τον νέο δείκτη.
• Απόρριψη αχρησιμοποίητων ευρετηρίων: χρήση sys.schema_unused_indexes in MySQL 5.7+ για να βρείτε ευρετήρια που δεν διαβάζονται με τίποτα.
• Τύποι δεδομένων αντιστοίχισης: Εάν μια ρήτρα WHERE συγκρίνει μια στήλη VARCHAR με έναν αριθμό, ο δείκτης δεν μπορεί να χρησιμοποιηθεί λόγω μιας έμμεσης μετατροπής.

Συχνές Ερωτήσεις