Οδηγός MySQL Create Index: Βελτιστοποίηση των queries στη MySQL

Εισαγωγή

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

Πριν συνεχίσεις το διάβασμα, ήθελα απλώς να σου πω για τον σύγχρονο και γρήγορο διαχειριστή βάσεων δεδομένων MySQL μας, το Beekeeper Studio. Ο επεξεργαστής δομής πινάκων του σου επιτρέπει να δημιουργείς ένα index, ακόμα και σύνθετα indexes, επιλέγοντας στήλες από μια λίστα, και είναι δωρεάν για MySQL. Κατέβασέ το εδώ.

Κατανόηση των ευρετηρίων στη MySQL

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

Πώς να δημιουργήσεις ένα ευρετήριο στη MySQL

Βασική δημιουργία ευρετηρίου

Μπορείς να δημιουργήσεις ένα ευρετήριο στη MySQL με την εντολή CREATE INDEX:

Query:

CREATE INDEX idx_lastname ON employees(lastname);

Αυτή η εντολή δημιουργεί ένα ευρετήριο με όνομα idx_lastname στη στήλη lastname του πίνακα employees, κάνοντας πιο γρήγορες τις αναζητήσεις με βάση τη στήλη lastname.

Παράδειγμα αποτελέσματος:

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Δημιουργία μοναδικού ευρετηρίου

Ένα μοναδικό ευρετήριο (unique index) εξασφαλίζει ότι όλες οι τιμές στη στήλη του ευρετηρίου είναι διαφορετικές.

Query:

CREATE UNIQUE INDEX idx_employee_id ON employees(employee_id);

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

Παράδειγμα αποτελέσματος:

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Χρήση ευρετηρίων με σύνθετα κλειδιά

Μπορείς να δημιουργήσεις ευρετήριο σε πολλές στήλες για να βελτιστοποιήσεις queries που φιλτράρουν ή ταξινομούν με βάση αυτές τις στήλες.

Query:

CREATE INDEX idx_name_department ON employees(lastname, department_id);

Αυτή η εντολή δημιουργεί ένα ευρετήριο που περιλαμβάνει τις στήλες lastname και department_id, κάτι χρήσιμο για queries που αφορούν και τα δύο πεδία.

Παράδειγμα αποτελέσματος:

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Πρακτικές περιπτώσεις χρήσης

Βελτίωση της απόδοσης των queries

Τα ευρετήρια βελτιώνουν σημαντικά την απόδοση των queries, ειδικά για εντολές SELECT σε μεγάλα σύνολα δεδομένων.

Πριν από το ευρετήριο:

Ας υποθέσουμε ένα query SELECT στον πίνακα employees χωρίς ευρετήριο στη στήλη lastname.

Μετά το ευρετήριο:

Το ίδιο query SELECT μετά τη δημιουργία του idx_lastname.

Query:

SELECT * FROM employees WHERE lastname = 'Doe';

Παράδειγμα αποτελέσματος χωρίς ευρετήριο:

/* Takes significantly longer, e.g., 1.5 seconds */

Παράδειγμα αποτελέσματος με ευρετήριο:

/* Takes significantly less time, e.g., 0.02 seconds */

Διασφάλιση της ακεραιότητας των δεδομένων

Τα μοναδικά ευρετήρια αποτρέπουν τις διπλότυπες τιμές σε μια στήλη, διασφαλίζοντας την ακεραιότητα των δεδομένων.

Σενάριο:

Απόπειρα εισαγωγής διπλότυπου employee_id μετά τη δημιουργία μοναδικού ευρετηρίου.

Παράδειγμα αποτελέσματος:

ERROR 1062 (23000): Duplicate entry '12345' for key 'idx_employee_id'

Συμπέρασμα

Η δημιουργία ευρετηρίων στη MySQL είναι σημαντική τεχνική για τη βελτιστοποίηση των queries της βάσης δεδομένων σου. Επιλέγοντας τις κατάλληλες στήλες για ευρετήρια —ειδικά αυτές που χρησιμοποιούνται συχνά σε συνθήκες αναζήτησης ή σε joins— μπορείς να πετύχεις σημαντική βελτίωση στην απόδοση.

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

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

Καλή βελτιστοποίηση!