Εργο
Η σάρωση πίνακα είναι πολύ αργή. Ή η εισαγωγή και η ενημέρωση εγγραφών διαρκεί πολύ.

Λύση
Ο όρος ALTER TABLE μπορεί να αφαιρέσει και να προσθέσει όχι μόνο στήλες, αλλά και ευρετήρια σε αυτές τις στήλες. Τέτοιες λειτουργίες συχνά βελτιώνουν την απόδοση της βάσης δεδομένων. Συνήθως, η δημιουργία ευρετηρίου μιας στήλης που χρησιμοποιείται συχνά επιταχύνει τις δηλώσεις SELECT, καθώς δεν χρειάζεται να σαρώσετε πλήρως τους πίνακες. Σε ορισμένες περιπτώσεις, η διαγραφή του ευρετηρίου μπορεί επίσης να είναι ευεργετική. Κάθε φορά που ενημερώνεται μια σειρά, η MySQL πρέπει να ενημερώνει όλα τα ευρετήρια που περιέχουν τις αλλαγμένες στήλες. Εάν χρησιμοποιείτε σπάνια ευρετήριο, αυτό μπορεί να υποδεικνύει ότι ο πίνακας είναι υπερφορτωμένος με ευρετήρια και η κατάργηση ενός από αυτά μπορεί να βελτιώσει την αποτελεσματικότητα της επεξεργασίας πίνακα.

Συζήτηση
Για ευκολία στη χρήση, ας ξεκινήσουμε δημιουργώντας μια νέα παρουσία του πίνακα δοκιμών mytbl. Χρησιμοποιούμε τις προτάσεις DROP TABLE και CREATE TABLE για να απορρίψουμε την υπάρχουσα έκδοση και να δημιουργήσουμε ξανά τον πίνακα στην αρχική του μορφή:

DROP TABLE mytbl;
ΔΗΜΙΟΥΡΓΙΑ ΤΡΑΠΕΖΙΟΥ mytbl
i INT,
c CHAR(1)
);

Στην αρχή του κεφαλαίου, χρησιμοποιήσαμε SHOW COLUMNS για να παρακολουθήσουμε τα αποτελέσματα της αλλαγής ενός πίνακα.

Τώρα θα εξετάσουμε τις αλλαγές ευρετηρίου και θα εμφανίσουμε τα αποτελέσματα χρησιμοποιώντας SHOW INDEX αντί για SHOW COLUMNS. Αυτήν τη στιγμή δεν υπάρχουν ευρετήρια στον πίνακα, επειδή δεν προσδιορίστηκαν στη δήλωση CREATE TABLE:

Mysql> ΕΜΦΑΝΙΣΗ ΕΥΡΕΤΗΡΙΟΥ ΑΠΟ mytbl;
Κενό σύνολο (0,00 δευτ.)

Προσθήκη Ευρετηρίων
Υπάρχουν τέσσερις τύποι ρητρών που προσθέτουν ευρετήρια σε έναν πίνακα:

ΑΛΛΑΓΗ ΠΙΝΑΚΑ όνομα_πίνακα ΠΡΟΣΘΗΚΗ ΚΥΡΙΑΤΟΥ ΚΛΕΙΔΙΟΥ (στήλη_λίστα);
ΑΛΛΑΓΗ ΠΙΝΑΚΑ όνομα_πίνακα ΠΡΟΣΘΗΚΗ ΜΟΝΑΔΙΚΟΥ ονόματος_ευρετηρίου (στήλη_λίστα);
ΑΛΛΑΓΗ ΠΙΝΑΚΑ όνομα_πίνακα ΠΡΟΣΘΗΚΗ_όνομα_ευρετηρίου_ευρετηρίου (στήλη_λίστα);
ALTER TABLE table_name ΠΡΟΣΘΗΚΗ ΠΛΗΡΗΣ ΚΕΙΜΕΝΟΥ index_name (στήλη_λίστα);

Η πρώτη ρήτρα προσθέτει ένα πρωτεύον κλειδί (PRIMARY KEY), δηλαδή, οι τιμές με ευρετήριο πρέπει να είναι μοναδικές και να μην περιέχουν NULL. Η δεύτερη ρήτρα δημιουργεί ένα ευρετήριο για το οποίο οι τιμές πρέπει να είναι μοναδικές (εκτός από τις τιμές NULL, οι οποίες μπορούν να εμφανιστούν πολλές φορές). Η τρίτη ρήτρα προσθέτει ένα κανονικό ευρετήριο στο οποίο οποιαδήποτε τιμή μπορεί να εμφανιστεί πολλές φορές. Το τελευταίο δημιουργεί ένα ειδικό ευρετήριο FULLTEXT, το οποίο χρησιμοποιείται για την προβολή κειμένου.

Εάν υπάρχει ένα index_name στις κατασκευές προτάσεων, τότε είναι προαιρετικό.

Εάν δεν το καθορίσετε, η MySQL θα εκχωρήσει αυτόματα ένα όνομα στο ευρετήριο.

Οι στήλες που πρόκειται να ευρετηριαστούν καθορίζονται στην παράμετρο στήλη_λίστα — μια λίστα με ένα ή περισσότερα ονόματα στηλών που χωρίζονται με κόμματα.

Ας δούμε δύο απλά παραδείγματα: το πρώτο δημιουργεί ένα ευρετήριο μίας στήλης στο c και το δεύτερο δημιουργεί ένα ευρετήριο πολλών στηλών που περιλαμβάνει τα c και i:

ΑΛΛΑΓΗ ΠΙΝΑΚΑ mytbl ΠΡΟΣΘΗΚΗ ΕΥΡΕΤΗ (c);
ALTER TABLE mytbl ΠΡΟΣΘΗΚΗ ΕΥΡΕΤΗΡΙΟΥ (c,i);

Σε πολλές περιπτώσεις, οι στήλες που ευρετηριάζονται πρέπει να δηλώνονται ως μη NULL. Για παράδειγμα, εάν δημιουργήσετε το mytbl ως πίνακα ISAM, οι παραπάνω δηλώσεις ADD INDEX θα αποτύχουν επειδή οι πίνακες ISAM δεν επιτρέπουν NULL σε κανέναν τύπο ευρετηρίου. Επιπλέον, τα ευρετήρια PRIMARY KEY δεν μπορούν να περιέχουν NULL τιμές, ανεξάρτητα από τον τύπο του πίνακα. Εάν προσπαθείτε να προσθέσετε ένα ευρετήριο και η MySQL παραπονιέται για ζητήματα NULL, χρησιμοποιήστε τον όρο ALTER TABLE για να αλλάξετε τις σχετικές στήλες σε non-NULL και δοκιμάστε να δημιουργήσετε ξανά το ευρετήριο. Για παράδειγμα, εάν προσπαθήσετε να κάνετε τη στήλη i το πρωτεύον κλειδί, θα παρουσιαστεί ένα σφάλμα:

Mysql>
ΣΦΑΛΜΑ 1171 στη γραμμή 5: Όλα τα μέρη ενός ΚΥΡΙΟΥ ΚΛΕΙΔΙΟΥ δεν πρέπει να είναι NULL.
Εάν χρειάζεστε NULL σε ένα κλειδί, χρησιμοποιήστε το UNIQUE

Πρέπει πρώτα να επαναπροσδιορίσετε τη στήλη i έτσι ώστε να μην επιτρέπει NULL:

Mysql> ALTER TABLE mytbl TODIFY i INT NOT NULL.
mysql> ΑΛΛΑΓΗ ΠΙΝΑΚΑ mytbl ΠΡΟΣΘΗΚΗ ΠΡΩΤΕΥΝΟΥ ΚΛΕΙΔΙΟΥ (i);

Όλα λειτούργησαν.

Και στην πρώτη περίπτωση, όπως φαίνεται από το μήνυμα σφάλματος, αντί για το πρωτεύον κλειδί, θα μπορούσατε να δημιουργήσετε ένα ΜΟΝΑΔΙΚΟ ευρετήριο εάν χρειάζεστε NULL τιμές στο ευρετήριο Κατάργηση ευρετηρίων Για να διαγράψετε ένα ευρετήριο, χρησιμοποιήστε ένα από τα παρακάτω προτάσεις:

ΑΛΛΑΓΗ ΠΙΝΑΚΑ_όνομα πίνακα ΑΠΟΔΟΣΗ ΠΡΩΤΕΥΤΙΚΟ ΚΛΕΙΔΙ.
ΑΛΛΑΓΗ ΠΙΝΑΚΑ όνομα_πίνακα DROP INDEX index_name;

Ο ευκολότερος τρόπος για να διαγράψετε ένα ευρετήριο ΠΡΩΤΙΚΟΥ ΚΛΕΙΔΙΟΥ είναι επειδή δεν χρειάζεται να γνωρίζετε το όνομα του ευρετηρίου:

ALTER TABLE mytbl DROP PRIMARY KEY;

Για να διαγράψετε ένα ευρετήριο που δεν είναι πρωτεύον κλειδί, πρέπει να καθορίσετε το όνομά του. Εάν δεν ξέρετε πώς ονομάζεται το ευρετήριο, χρησιμοποιήστε SHOW INDEX. Για να αποφύγετε την έξοδο πολύ μεγάλων γραμμών, χρησιμοποιήστε κάθετη έξοδο (\G):

Mysql> ΕΜΦΑΝΙΣΗ ΕΥΡΕΤΗΡΙΟΥ ΑΠΟ mytbl\G
****************************** 1. σειρά ******************** * ******
Πίνακας: mytbl
Non_unique: 1
Όνομα_κλειδιού: γ
Seq_in_index: 1
Όνομα_στήλης: γ
Συλλογή: Α
Καρδιναλότητα: NULL
Sub_part: NULL
Συσκευασία: NULL
Σχόλιο:
****************************** 2. σειρά ******************** * ******
Πίνακας: mytblNon_unique: 1
Όνομα_κλειδιού: c_2
Seq_in_index: 1
Όνομα_στήλης: γ
Συλλογή: Α
Καρδιναλότητα: NULL
Sub_part: NULL
Συσκευασία: NULL
Σχόλιο:
****************************** 3. σειρά ******************** * ******
Πίνακας: mytbl
Non_unique: 1
Όνομα_κλειδιού: c_2
Seq_in_index: 2
Όνομα_στήλης: i
Συλλογή: Α
Καρδιναλότητα: NULL
Sub_part: NULL
Συσκευασία: NULL
Σχόλιο:

Οι τιμές Key_name και Seq_in_index αντιστοιχούν στα ονόματα των ευρετηρίων και στις θέσεις των στηλών στο ευρετήριο. Τώρα ξέρετε ότι ο πίνακας mytbl έχει ένα ευρετήριο μίας στήλης που ονομάζεται c και ένα ευρετήριο πολλών στηλών με το όνομα c_2 (αυτά τα ονόματα επιλέχθηκαν από τη MySQL για τα δύο ευρετήρια που δημιουργήσαμε νωρίτερα). Η δήλωση κατάργησης ευρετηρίων θα ήταν:

ALTER TABLE mytbl DROP INDEX c, DROP INDEX c_2;

Όπως μπορείτε να δείτε, πολλαπλές λειτουργίες μπορούν να εκτελεστούν σε μία πρόταση ALTER TABLE, η οποία πρέπει να διαχωρίζεται με κόμματα.

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

Οι ακόλουθοι τύποι ευρετηρίων χρησιμοποιούνται στους πίνακες mysql:

Πρωτεύων κλειδί
Μοναδικό ευρετήριο
Κανονικός δείκτης
Ευρετήριο πλήρους κειμένου

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

Πρωτεύων κλειδί

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

1. Ένας πίνακας δεν μπορεί να έχει μόνο ένα μοναδικό κλειδί.

2. Η τιμή κλειδιού πρέπει να είναι μοναδική στον πίνακα.

3. Το πρωτεύον κλειδί δεν μπορεί να είναι τύπου NULL.

Παράδειγμα δημιουργίας πρωτεύοντος κλειδιού:

Δημιουργία πρωτεύοντος κλειδιού κατά τη δημιουργία πίνακα στην περιγραφή του πεδίου

Παράδειγμα 3:

Εάν το πρωτεύον κλειδί είναι πεδίο κειμένου, τότε πρέπει να υποδείξετε σε παρένθεση τον αριθμό των χαρακτήρων που εμπλέκονται στην ευρετηρίαση.

Παράδειγμα 5:

Εάν ο πίνακας έχει ήδη δημιουργηθεί, τότε μπορείτε να προσθέσετε ένα πρωτεύον ευρετήριο ως εξής.

$query = "ΑΥΤΟΜΑΤΗ ΕΝΑΛΛΑΓΗ πίνακα ΠΡΟΣΘΗΚΗ ΠΡΩΤΕΥΝΟΥ ΚΛΕΙΔΙΟΥ ΠΕΡΙΟΡΙΣΜΟΥ (ID_number)";
$result = $connection->query($query);
?>

Τακτικά και μοναδικά ευρετήρια

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

Η δήλωση κανονικών ευρετηρίων γίνεται με χρήση λέξεων-κλειδιών κλειδίή δείκτης. Εάν πρέπει να δηλώσετε ένα μοναδικό ευρετήριο, τότε πριν κλειδίή δείκτηςτίθεται η λέξη-κλειδί μοναδικός.

Παράδειγμα 6:

Δημιουργήστε ένα μοναδικό ευρετήριο

Αφαίρεση ευρετηρίων

Η αφαίρεση των ευρετηρίων πραγματοποιείται χρησιμοποιώντας την εντολή πτώσης ευρετηρίου, διαγράφονται τυχόν ευρετήρια.

Παράδειγμα 8:

$connection = new mysqli("localhost","root","","mybase");
$query = "απόθεση μοντέλου ευρετηρίου σε αυτόματη λειτουργία";
$result = $connection->query($query);
?>

υποσέλιδο στο word 2007, 0000002111

Χρήση ευρετηρίων στη MySQL

Τα ευρετήρια χρησιμοποιούνται για τη γρήγορη εύρεση σειρών με καθορισμένη τιμή σε μία στήλη. Χωρίς ευρετήριο, ένας πίνακας διαβάζεται σε ολόκληρο τον πίνακα, ξεκινώντας από την πρώτη εγγραφή, μέχρι να βρεθούν οι αντίστοιχες σειρές. Όσο μεγαλύτερο είναι το τραπέζι, τόσο μεγαλύτερο είναι το γενικό κόστος. Εάν ο πίνακας περιέχει ένα ευρετήριο στις εν λόγω στήλες, τότε η MySQL μπορεί να προσδιορίσει γρήγορα τη θέση αναζήτησης στη μέση του αρχείου δεδομένων χωρίς να ανατρέξει σε όλα τα δεδομένα. Για έναν πίνακα που περιέχει 1000 σειρές, αυτό θα είναι τουλάχιστον 100 φορές πιο γρήγορο σε σύγκριση με την επανάληψη σε όλες τις εγγραφές διαδοχικά. Ωστόσο, στην περίπτωση που χρειάζεται πρόσβαση σχεδόν και στις 1000 σειρές, οι διαδοχικές αναγνώσεις θα είναι ταχύτερες επειδή δεν απαιτούνται αναζητήσεις δίσκου.

Όλα τα ευρετήρια MySQL (PRIMARY, UNIQUE και INDEX) αποθηκεύονται ως B-trees. Οι συμβολοσειρές συμπιέζονται αυτόματα, αφαιρώντας τα προθέματα και τα κενά μετάδοσης.

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

Βρείτε γρήγορα σειρές που ταιριάζουν με έναν όρο WHERE.

Ανάκτηση σειρών από άλλους πίνακες κατά την εκτέλεση ενώσεων.

Βρείτε τις τιμές MAX() ή MIN() για μια δεδομένη στήλη με ευρετήριο. Αυτή η λειτουργία βελτιστοποιείται από τον προεπεξεργαστή, ο οποίος ελέγχει ότι δεν χρησιμοποιείτε WHERE key_part_4 = σταθερό, σε όλα τα μέρη του σύνθετου κλειδιού< N . В этом случае MySQL сделает один просмотр ключа и заменит выражение константой MIN() . Если все выражения заменяются константой, запрос моментально вернет результат:

SELECT MIN(key_part2),MAX(key_part2) FROM table_name όπου key_part1=10

Εκτελέστε ταξινόμηση ή ομαδοποίηση σε έναν πίνακα εάν αυτές οι λειτουργίες εκτελούνται στο αριστερό πρόθεμα του κλειδιού που χρησιμοποιείται (για παράδειγμα, ORDER BY key_part_1,key_part_2). Εάν όλα τα μέρη ενός κλειδιού ακολουθούνται από DESC, τότε το κλειδί διαβάζεται με αντίστροφη σειρά

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

SELECT key_part3 FROM table_name WHERE key_part1=1

Ας υποθέσουμε ότι ονομάζεται η ακόλουθη πρόταση SELECT:

Mysql> SELECT * FROM tbl_name WHERE col1=val1 ΚΑΙ col2=val2;

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

Εάν ένας δεδομένος πίνακας έχει ευρετήριο πολλών στηλών, τότε οποιοδήποτε αριστερό πρόθεμα αυτού του ευρετηρίου μπορεί να χρησιμοποιηθεί από το βελτιστοποιητή για την εύρεση σειρών. Για παράδειγμα, εάν έχετε ένα ευρετήριο σε τρεις στήλες (col1,col2,col3), τότε υπάρχει η δυνατότητα για ευρετηριασμένες αναζητήσεις στις (col1), (col1,col2) και (col1,col2,col3).

Στη MySQL, δεν μπορείτε να χρησιμοποιήσετε ένα μερικό ευρετήριο εκτός εάν οι στήλες αποτελούν το αριστερό πρόθεμα αυτού του ευρετηρίου. Ας υποθέσουμε ότι έχετε τις εντολές SELECT που εμφανίζονται παρακάτω:

Mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 ΚΑΙ col3=val3;

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

Η MySQL χρησιμοποιεί επίσης ευρετήρια για συγκρίσεις LIKE εάν το όρισμα στην έκφραση LIKE είναι μια σταθερή συμβολοσειρά που δεν ξεκινά με χαρακτήρα μπαλαντέρ. Για παράδειγμα, οι ακόλουθες εντολές SELECT χρησιμοποιούν ευρετήρια:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

Η πρώτη εντολή κοιτάζει μόνο γραμμές με "Patrick"<= key_col < "Patricl" , а во второй - только строки с "Pat" <= key_col < "Pau" .

Οι ακόλουθες εντολές SELECT δεν θα χρησιμοποιούν ευρετήρια:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

Στην πρώτη εντολή, η τιμή LIKE ξεκινά με έναν χαρακτήρα μπαλαντέρ. Στη δεύτερη εντολή, η τιμή LIKE δεν είναι σταθερά.

Η MySQL 4.0 εισάγει μια διαφορετική βελτιστοποίηση στην έκφραση LIKE. Εάν χρησιμοποιείται η έκφραση... LIKE "%string%" και το μήκος της συμβολοσειράς είναι μεγαλύτερο από 3 χαρακτήρες, τότε η MySQL θα χρησιμοποιήσει τον αλγόριθμο Turbo Boyer-Moore για να προετοιμάσει ένα μοτίβο για τη συμβολοσειρά και στη συνέχεια θα χρησιμοποιήσει αυτό το μοτίβο για να εκτελέσει η αναζήτηση πιο γρήγορα.

Κατά την αναζήτηση χρησιμοποιώντας το όνομα στήλης ΕΙΝΑΙ NULL, θα χρησιμοποιούνται ευρετήρια εάν το όνομα_στόλου είναι ευρετήριο.

Η MySQL χρησιμοποιεί συνήθως το ευρετήριο που βρίσκει τις λιγότερες σειρές. Το ευρετήριο χρησιμοποιείται σε στήλες που συγκρίνονται χρησιμοποιώντας τους ακόλουθους τελεστές: =, >, >=,<, <=, BETWEEN и LIKE с префиксом, не содержащим шаблонного символа, такого как something% .

Εάν ένα ευρετήριο δεν καλύπτει όλα τα επίπεδα ΚΑΙ στον όρο WHERE, τότε δεν χρησιμοποιείται για τη βελτιστοποίηση του ερωτήματος. Με άλλα λόγια: για να μπορεί να χρησιμοποιηθεί ένα ευρετήριο, το πρόθεμα αυτού του ευρετηρίου πρέπει να εμφανίζεται σε κάθε ομάδα AND.

Οι παρακάτω όροι WHERE χρησιμοποιούν ευρετήρια:

WHERE index_part1=1 AND index_part2=2 AND other_column=3 ... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */ ... WHERE index_part1="γεια" ΚΑΙ index_part_3= 5 /* βελτιστοποιημένο ως "index_part1="hello"" */ ... WHERE index1=1 and index2=2 or index1=3 and index3=3; /* Μπορείτε να χρησιμοποιήσετε ένα ευρετήριο στο index1, αλλά όχι στο index2 ή στο ευρετήριο 3 */

Οι ακόλουθες ρήτρες WHERE Δενχρησιμοποιήστε ευρετήρια:

WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */ ... WHERE index=1 OR A=10 /* Index δεν χρησιμοποιείται και στα δύο μέρη του AND */ ... WHERE index_part1=1 OR index_part2 =10 /* Δεν υπάρχει ευρετήριο που να καλύπτει όλες τις σειρές*/

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

Εάν η χρήση ενός ευρετηρίου απαιτεί η MySQL να διασχίζει περισσότερο από το 30% των σειρών σε έναν δεδομένο πίνακα (σε τέτοιες περιπτώσεις, η διέλευση πίνακα θα είναι πιθανότατα πολύ πιο γρήγορη καθώς θα απαιτούνται λιγότερες αναζητήσεις). Σημειώστε ότι εάν ένα ερώτημα όπως αυτό χρησιμοποιεί LIMIT μόνο στο υποσύνολο των γραμμών που ανακτώνται, τότε η MySQL θα χρησιμοποιήσει το ευρετήριο ούτως ή άλλως, καθώς ένας μικρός αριθμός σειρών μπορεί να βρεθεί πολύ πιο γρήγορα για να επιστρέψει το αποτέλεσμα.

Ευρετήρια στηλών

Οι στήλες όλων των τύπων μπορούν να ευρετηριαστούν στη MySQL. Η χρήση ευρετηρίων σε σχετικές στήλες είναι ένας καλός τρόπος για να επιταχύνετε τις λειτουργίες SELECT.

Ο μέγιστος αριθμός κλειδιών και το μέγιστο μήκος των ευρετηρίων καθορίζονται από τη μηχανή του πίνακα. Μπορείτε να έχετε τουλάχιστον 16 κλειδιά σε όλες τις μηχανές πίνακα και ένα συνολικό μήκος ευρετηρίου τουλάχιστον 256 byte.

Για τις στήλες CHAR και VARCHAR, μπορείτε να δημιουργήσετε ευρετήριο για το πρόθεμα της στήλης. Αυτό είναι πολύ πιο γρήγορο και απαιτεί λιγότερο χώρο στο δίσκο από την ευρετηρίαση ολόκληρης της στήλης. Η σύνταξη που χρησιμοποιείται στην εντολή CREATE TABLE για την ευρετηρίαση ενός προθέματος στήλης είναι κάπως έτσι:

KEY index_name (όνομα_κολώνας(μήκος))

Το ακόλουθο παράδειγμα δημιουργεί ένα ευρετήριο στους 10 πρώτους χαρακτήρες στη στήλη ονόματος:

Mysql> Τεστ ΔΗΜΙΟΥΡΓΙΑΣ ΠΙΝΑΚΑΣ ( -> όνομα CHAR(200) NOT NULL, -> KEY index_name(name(10)));

Για τις στήλες BLOB και TEXT, το πρόθεμα στήλης πρέπει να είναι ευρετήριο. Δεν μπορείτε να δημιουργήσετε ευρετήριο μιας ολόκληρης στήλης.

Στην MySQL έκδοση 3.23.23 και μεταγενέστερη, μπορείτε επίσης να δημιουργήσετε προσαρμοσμένα ευρετήρια FULLTEXT. Χρησιμοποιούνται για αναζήτηση πλήρους κειμένου. Τα ευρετήρια πλήρους κειμένου FULLTEXT υποστηρίζουν μόνο πίνακες MyISAM. Μπορούν να δημιουργηθούν μόνο σε στήλες VARCHAR και TEXT. Η ευρετηρίαση εκτελείται πάντα σε ολόκληρη τη στήλη και η μερική ευρετηρίαση δεν υποστηρίζεται.

Ευρετήρια πολλαπλών στηλών

Η MySQL μπορεί να δημιουργήσει ευρετήρια σε πολλές στήλες. Ένα ευρετήριο μπορεί να περιλαμβάνει έως και 15 στήλες (στις στήλες CHAR και VARCHAR μπορείτε επίσης να χρησιμοποιήσετε το πρόθεμα στήλης ως μέρος του ευρετηρίου)

Ένα ευρετήριο πολλών στηλών μπορεί να θεωρηθεί ως ένας ταξινομημένος πίνακας που περιέχει τιμές που δημιουργούνται συνενώνοντας τις τιμές των ευρετηριασμένων στηλών.

Η MySQL χρησιμοποιεί ευρετήρια πολλών στηλών με τέτοιο τρόπο ώστε τα ερωτήματα να εκτελούνται γρήγορα όταν προσδιορίζετε το γνωστό τμήμα για την πρώτη στήλη στο ευρετήριο στον όρο WHERE, ακόμα κι αν δεν καθορίσετε τιμές για τις άλλες στήλες.

Ας υποθέσουμε ότι δημιουργήθηκε ο παρακάτω πίνακας:

Mysql> Τεστ ΔΗΜΙΟΥΡΓΙΑΣ ΠΙΝΑΚΑΣ ( -> id INT NOT NULL, -> last_name CHAR(30) NOT NULL, -> first_name CHAR(30) NOT NULL, -> ΚΥΡΙΟ ΚΛΕΙΔΙ (αναγνωριστικό), -> Όνομα INDEX(επώνυμο,όνομα));

Το ευρετήριο ονόματος είναι ένα ευρετήριο στις στήλες last_name και first_name. Αυτό το ευρετήριο θα χρησιμοποιηθεί για ερωτήματα που καθορίζουν τιμές στη γνωστή περιοχή για το last_name ή και για τις δύο στήλες last_name και first_name. Έτσι, το ευρετήριο ονόματος θα χρησιμοποιηθεί στα ακόλουθα ερωτήματα:

Mysql> SELECT * FROM test WHERE last_name="Widenius"; mysql> -> ΚΑΙ first_name="Michael"; mysql> SELECT * FROM test WHERE last_name="Widenius" -> ΚΑΙ (first_name="Michael" OR first_name="Monty"); mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND first_name >="M" ΚΑΙ first_name< "N";

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

Άνοιγμα και κλείσιμο πινάκων στη MySQL

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

Οι τιμές table_cache και max_connections σχετίζονται. Για παράδειγμα, για 200 ταυτόχρονες συνδέσεις, πρέπει να έχετε μια κρυφή μνήμη πίνακα τουλάχιστον 200 * n, όπου n είναι ο μέγιστος αριθμός σχετικών πινάκων. Πρέπει επίσης να κρατήσετε μερικούς επιπλέον περιγραφείς αρχείων για προσωρινούς πίνακες και αρχεία.

Θα πρέπει να βεβαιωθείτε ότι το λειτουργικό σας σύστημα μπορεί να χειριστεί τον αριθμό των ανοιχτών περιγραφών αρχείων που λαμβάνει αυτή η ρύθμιση table_cache. Εάν το table_cache έχει οριστεί πολύ ψηλά, η MySQL μπορεί να εξαντλήσει τους περιγραφείς αρχείων, να ακυρώσει τη σύνδεση, να αποτύχει ερωτήματα και να γίνει πολύ αναξιόπιστη. Θα πρέπει επίσης να λάβετε υπόψη ότι η μηχανή πινάκων MyISAM απαιτεί δύο περιγραφείς αρχείων για κάθε μοναδικό ανοιχτό πίνακα. Ο αριθμός των περιγραφικών αρχείων που επιτρέπεται από τη MySQL μπορεί να αυξηθεί χρησιμοποιώντας την επιλογή εκκίνησης --open-files-limit=#.

Ο μέγιστος αριθμός πινάκων στην κρυφή μνήμη ανοιχτού πίνακα θα είναι ο αριθμός που καθορίζεται στο table_cache (η προεπιλογή είναι 64. Αυτός ο αριθμός μπορεί να αλλάξει χρησιμοποιώντας την επιλογή -O table_cache=# για το mysqld). Λάβετε υπόψη ότι η MySQL ενδέχεται να ανοίξει προσωρινά περισσότερους πίνακες για την εκτέλεση ερωτημάτων.

Ένας πίνακας που δεν χρησιμοποιείται κλείνει και αφαιρείται από την κρυφή μνήμη του πίνακα στις ακόλουθες περιπτώσεις:

Όταν η κρυφή μνήμη είναι γεμάτη και το νήμα προσπαθεί να ανοίξει έναν πίνακα που δεν βρίσκεται σε αυτήν την κρυφή μνήμη.

Όταν η κρυφή μνήμη περιέχει περισσότερες από τιμές εισόδου table_cache και το νήμα δεν χρησιμοποιεί πλέον τον πίνακα.

Όταν κάποιος εκτελεί mysqladmin refresh ή mysqladmin flush-tables .

Όταν κάποιος εκτελεί FLUSH TABLES .

Όταν η κρυφή μνήμη του πίνακα γεμίσει, ο διακομιστής χρησιμοποιεί την ακόλουθη διαδικασία για να εκχωρήσει εισόδους προσωρινής μνήμης για χρήση:

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

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

Εάν η κρυφή μνήμη βρίσκεται σε προσωρινά εκτεταμένη κατάσταση και ένας πίνακας μεταβαίνει από κατάσταση χρησιμοποιημένης σε αχρησιμοποίητη, ο πίνακας κλείνει και ελευθερώνεται από την κρυφή μνήμη.

Ο πίνακας ανοίγει για κάθε ταυτόχρονη πρόσβαση. Αυτό σημαίνει ότι εάν υπάρχουν δύο νήματα που έχουν πρόσβαση στον ίδιο πίνακα ή ο ίδιος πίνακας έχει πρόσβαση δύο φορές στο ίδιο ερώτημα (χρησιμοποιώντας AS), τότε αυτός ο πίνακας πρέπει να ανοίξει δύο φορές. Το άνοιγμα οποιουδήποτε πίνακα για πρώτη φορά απαιτεί δύο περιγραφείς αρχείων. για κάθε πρόσθετη χρήση - μόνο μία. Μια πρόσθετη λαβή για το πρώτο άνοιγμα χρησιμοποιείται για το αρχείο ευρετηρίου. αυτή η λαβή είναι κοινή από όλα τα νήματα.

Όταν ανοίγετε έναν πίνακα με την εντολή HANDLER table_name OPEN, δημιουργείται ένα αποκλειστικό αντικείμενο πίνακα για αυτό το νήμα. Αυτό το αντικείμενο πίνακα δεν είναι προσβάσιμο σε άλλα νήματα και δεν θα κλείσει έως ότου το νήμα εκδώσει την εντολή HANDLER table_name CLOSE ή το ίδιο το νήμα σκοτωθεί. Εάν συμβεί αυτό, ο πίνακας τοποθετείται ξανά στην κρυφή μνήμη του πίνακα (αν δεν είναι γεμάτος).

Για να δείτε αν η κρυφή μνήμη του πίνακα είναι πολύ μικρή, θα πρέπει να ελέγξετε τη μεταβλητή Opened_tables. Εάν η τιμή είναι αρκετά μεγάλη, ακόμα κι αν δεν έχετε εκτελέσει την εντολή FLUSH TABLES πολύ συχνά, τότε θα πρέπει να αυξήσετε την προσωρινή μνήμη αυτού του πίνακα.

κεφαλίδες και υποσέλιδα στο word 2007, 000000211111

Τα ευρετήρια χρησιμοποιούνται για γρήγορη εύρεση σειρών με καθορισμένη τιμή σε μία στήλη. Χωρίς ευρετήριο, ένας πίνακας διαβάζεται σε ολόκληρο τον πίνακα, ξεκινώντας από την πρώτη εγγραφή, μέχρι να βρεθούν οι αντίστοιχες σειρές. Όσο μεγαλύτερο είναι το τραπέζι, τόσο μεγαλύτερο είναι το γενικό κόστος. Εάν ο πίνακας περιέχει ένα ευρετήριο στις εν λόγω στήλες, τότε η MySQL μπορεί να προσδιορίσει γρήγορα τη θέση αναζήτησης στη μέση του αρχείου δεδομένων χωρίς να ανατρέξει σε όλα τα δεδομένα. Για έναν πίνακα που περιέχει 1000 σειρές, αυτό θα είναι τουλάχιστον 100 φορές ταχύτερο από την επανάληψη σε όλες τις εγγραφές διαδοχικά. Ωστόσο, στην περίπτωση που χρειάζεται πρόσβαση σχεδόν και στις 1000 σειρές, οι διαδοχικές αναγνώσεις θα είναι ταχύτερες επειδή δεν απαιτούνται αναζητήσεις δίσκου.

Όλα τα ευρετήρια MySQL (PRIMARY, UNIQUE και INDEX) αποθηκεύονται ως B-trees. Οι συμβολοσειρές συμπιέζονται αυτόματα, αφαιρώντας τα προθέματα και τα τελικά κενά (βλ. ενότητα 6.5.7 ΔΗΜΙΟΥΡΓΙΑ ΔΗΜΙΟΥΡΓΙΑΣ ΕΥΡΕΤΗΡΙΟΥ Σύνταξη δήλωσης).

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

Βρείτε γρήγορα σειρές που ταιριάζουν με έναν όρο WHERE.
Ανάκτηση σειρών από άλλους πίνακες κατά την εκτέλεση ενώσεων.
Βρείτε τις τιμές MAX() ή MIN() για μια δεδομένη στήλη με ευρετήριο. Αυτή η λειτουργία βελτιστοποιείται από έναν προεπεξεργαστή που ελέγχει εάν χρησιμοποιείτε WHERE key_part_4 = σταθερά, σε όλα τα μέρη του σύνθετου κλειδιού . Σε αυτήν την περίπτωση, η MySQL θα κάνει μία αναζήτηση στο κλειδί και θα αντικαταστήσει την έκφραση με τη σταθερά MIN(). Εάν όλες οι εκφράσεις αντικατασταθούν με μια σταθερά, το ερώτημα θα επιστρέψει αμέσως το αποτέλεσμα: SELECT MIN(key_part2),MAX(key_part2) FROM table_name όπου key_part1=10
Εκτελέστε ταξινόμηση ή ομαδοποίηση σε έναν πίνακα εάν αυτές οι λειτουργίες εκτελούνται στο αριστερό πρόθεμα του κλειδιού που χρησιμοποιείται (για παράδειγμα ORDER BY key_part_1,key_part_2). Εάν όλα τα μέρη ενός κλειδιού ακολουθούνται από DESC, τότε το κλειδί διαβάζεται με αντίστροφη σειρά (δείτε την ενότητα 5.2.7 Πώς η MySQL βελτιστοποιεί ORDER BY).
Σε ορισμένες περιπτώσεις, το ερώτημα μπορεί να βελτιστοποιηθεί για την ανάκτηση τιμών χωρίς πρόσβαση στο αρχείο δεδομένων. Εάν όλες οι χρησιμοποιούμενες στήλες σε έναν συγκεκριμένο πίνακα είναι αριθμητικές και αποτελούν το αριστερό πρόθεμα για ένα συγκεκριμένο κλειδί, τότε για να παρέχεται μεγαλύτερη ταχύτητα, οι τιμές που αναζητήθηκαν μπορούν να ανακτηθούν απευθείας από το δέντρο ευρετηρίου: SELECT key_part3 FROM table_name WHERE key_part1=1

Ας υποθέσουμε ότι ονομάζεται η ακόλουθη πρόταση SELECT:

Mysql> SELECT * FROM tbl_name WHERE col1=val1 ΚΑΙ col2=val2;

Mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 ΚΑΙ col3=val3;

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

Η πρώτη εντολή εξετάζει μόνο τις γραμμές με "Patrick" , και στη δεύτερη μόνο γραμμή με "Pat" .

Οι ακόλουθες εντολές SELECT δεν θα χρησιμοποιούν ευρετήρια:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> ΕΠΙΛΟΓΗ * ΑΠΟ tbl_name ΠΟΥ key_col LIKE other_col;

Η MySQL 4.0 εισάγει μια διαφορετική βελτιστοποίηση στην έκφραση LIKE. Εάν χρησιμοποιείται η έκφραση ... LIKE "%string%" και το μήκος της συμβολοσειράς είναι μεγαλύτερο από 3 χαρακτήρες, τότε η MySQL θα χρησιμοποιήσει τον αλγόριθμο Turbo Boyer-Moore για να προετοιμάσει ένα μοτίβο για τη συμβολοσειρά και στη συνέχεια θα χρησιμοποιήσει αυτό το μοτίβο για να εκτελέσει η αναζήτηση πιο γρήγορα.

Η MySQL χρησιμοποιεί συνήθως το ευρετήριο που βρίσκει τις λιγότερες σειρές. Ένα ευρετήριο χρησιμοποιείται σε στήλες που συγκρίνονται χρησιμοποιώντας τους ακόλουθους τελεστές: =, >, >= και LIKE με ένα πρόθεμα που δεν περιέχει χαρακτήρα μπαλαντέρ όπως κάτι% .

Εάν ένα ευρετήριο δεν καλύπτει όλα τα επίπεδα ΚΑΙ σε μια πρόταση WHERE, τότε δεν χρησιμοποιείται για τη βελτιστοποίηση του ερωτήματος. Με άλλα λόγια: για να μπορεί να χρησιμοποιηθεί ένα ευρετήριο, το πρόθεμα αυτού του ευρετηρίου πρέπει να εμφανίζεται σε κάθε ομάδα AND.

Οι παρακάτω όροι WHERE χρησιμοποιούν ευρετήρια:

Οι ακόλουθες ρήτρες WHERE Δενχρησιμοποιήστε ευρετήρια:

Εάν η χρήση ενός ευρετηρίου απαιτεί η MySQL να διασχίζει περισσότερο από το 30% των σειρών σε έναν δεδομένο πίνακα (σε τέτοιες περιπτώσεις, η διέλευση πίνακα θα είναι πιθανότατα πολύ πιο γρήγορη καθώς θα απαιτούνται λιγότερες αναζητήσεις). Σημειώστε ότι εάν ένα ερώτημα όπως αυτό χρησιμοποιεί LIMIT μόνο στο υποσύνολο των γραμμών που ανακτώνται, τότε η MySQL θα χρησιμοποιήσει το ευρετήριο ούτως ή άλλως, καθώς ένας μικρός αριθμός σειρών μπορεί να βρεθεί πολύ πιο γρήγορα για να επιστρέψει το αποτέλεσμα.
Εάν το εύρος αλλαγής ευρετηρίου μπορεί να περιέχει NULL τιμές όταν χρησιμοποιείτε εκφράσεις ORDER BY ... DESC.

Εάν έχετε οποιεσδήποτε άλλες ερωτήσεις ή κάτι δεν είναι ξεκάθαρο - καλώς ήρθατε στην παρέα μας

Σε προηγούμενα άρθρα ανέφερα συχνά ευρετήρια στη MySQL. και υποσχέθηκα ότι θα σου πω σύντομα γι' αυτά. Έφτασε, λοιπόν, αυτή η ώρα και σήμερα θα μάθετε Ευρετήρια MySQL, σχετικά με το σκοπό τους και τον τρόπο δημιουργίας τους.

Τα ευρετήρια χρησιμοποιούνται για την επιτάχυνση της ανάκτησης δεδομένων από πίνακες βάσεων δεδομένων. Στην πραγματικότητα, ευρετήριο στη MySQL- αυτό είναι η ταξινόμηση ενός συγκεκριμένου πεδίου σε έναν πίνακα. Δηλαδή, εάν ένα πεδίο γίνει ευρετήριο, τότε ολόκληρος ο πίνακας θα ταξινομηθεί με αυτό το πεδίο. Γιατί είναι αυτό ωφέλιμο;

Ας πούμε ότι ο πίνακας μας περιέχει 1000000 εγγραφές. Κάθε καταχώρηση έχει ένα μοναδικό αναγνωριστικό ταυτότητα. Και ας πούμε ότι πρέπει να βγάλουμε έναν δίσκο από ID = 530124. Αν δεν υπάρχει ευρετήριο, τότε MySQLθα επαναλάβει όλες τις εγγραφές του πίνακα μέχρι να βρει αυτή που χρειάζεται. Στη χειρότερη περίπτωση, θα αναγκαστεί να τακτοποιήσει 1000000 εγγραφές. Φυσικά, αυτό θα είναι πολύ μεγάλο χρονικό διάστημα. Και αν υπήρχε ευρετήριο (δηλαδή, το πεδίο θα ταξινομηθεί), τότε η εγγραφή θα δειγματιζόταν κατά μέσο όρο σε 100.000 φορέςγρηγορότερα. Όπως μπορείτε να δείτε, τα οφέλη είναι προφανή.

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

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

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

Για πρωτεύοντα κλειδιά ( ΠΡΩΤΕΥΩΝ ΚΛΕΙΔΙ) το ευρετήριο δημιουργείται αυτόματα, αλλά για άλλα πεδία η ακολουθία ενεργειών είναι PHPMyAdminΕπόμενο:

Και τέλος, θα ήθελα να κάνω μια σύντομη περίληψη για να καταλάβετε: " Πότε να δημιουργήσετε ευρετήρια MySQL":

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

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