Qu'est-ce qu'un index?
Les index dans MySQL trient les données de manière séquentielle organisée. Ils sont créés sur la ou les colonnes qui seront utilisées pour filtrer les données. Considérez un index comme une liste triée par ordre alphabétique. Il est plus facile de rechercher des noms qui ont été triés par ordre alphabétique que ceux qui ne le sont pas.
L'utilisation d'un index sur des tables fréquemment mises à jour peut entraîner de mauvaises performances. En effet, MySQL crée un nouveau bloc d'index à chaque fois que des données sont ajoutées ou mises à jour dans la table. En règle générale, les index doivent être utilisés sur des tables dont les données ne changent pas fréquemment mais sont beaucoup utilisées dans les requêtes de recherche sélectionnées.
À quoi sert un index?
Personne n'aime les systèmes lents. Des performances système élevées sont d'une importance primordiale dans presque tous les systèmes de bases de données. La plupart des entreprises investissent massivement dans le matériel afin que la récupération et les manipulations de données puissent être plus rapides. Mais il y a des limites aux investissements matériels qu'une entreprise peut faire. L'optimisation de votre base de données est une solution moins chère et meilleure.
La lenteur du temps de réponse est généralement due au stockage aléatoire des enregistrements dans les tables de la base de données. Les requêtes de recherche doivent parcourir tous les enregistrements stockés au hasard les uns après les autres pour localiser les données souhaitées. Cela entraîne des bases de données de performances médiocres lorsqu'il s'agit de récupérer des données à partir de tables volumineuses. Par conséquent, les index sont utilisés pour trier les données pour faciliter la recherche.
Syntaxe: Créer un index
Les index peuvent être définis de 2 manières
- Au moment de la création de la table
- Après la création de la table
Pour notre myflixdb, nous attendons de nombreuses recherches dans la base de données sur le nom complet.
Nous ajouterons la colonne "full_names" à Index dans une nouvelle table "members_indexed".
Le script ci-dessous nous aide à y parvenir.
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;
Exécutez le script SQL ci-dessus dans MySQL workbench contre le "myflixdb".
L'actualisation de myflixdb affiche la table nouvellement créée nommée members_indexed.
La table "members_indexed" a "full_names" dans le noeud des index.
Au fur et à mesure que la base de membres s'agrandit et que le nombre d'enregistrements augmente, les requêtes de recherche sur la table members_indexed qui utilisent les clauses WHERE et ORDER BY seront beaucoup plus rapides que celles effectuées dans la table members sans l'index défini.
Ajouter la syntaxe de base de l'index
L'exemple ci-dessus a créé l'index lors de la définition de la table de base de données. Supposons que nous ayons déjà défini une table et que les requêtes de recherche y soient très lentes. Ils mettent trop de temps à renvoyer les résultats. Après avoir étudié le problème, nous découvrons que nous pouvons grandement améliorer les performances du système en créant INDEX sur la colonne la plus couramment utilisée dans la clause WHERE.
Nous pouvons utiliser la requête suivante pour ajouter un index
CREATE INDEX id_index ON table_name(column_name);
Supposons que les requêtes de recherche sur la table des films soient très lentes et que nous souhaitons utiliser un index sur le "titre du film" pour accélérer les requêtes, nous pouvons utiliser le script suivant pour y parvenir.
CREATE INDEX `title_index` ON `movies`(`title`);
L'exécution de la requête ci-dessus crée un index sur le champ de titre dans la table des films.
Cela signifie que toutes les requêtes de recherche sur la table des films utilisant le "titre" seront plus rapides.
Les requêtes de recherche sur d'autres champs de la table des films seront cependant toujours plus lentes que celles basées sur le champ indexé.
Remarque: vous pouvez créer des index sur plusieurs colonnes si nécessaire en fonction des champs que vous prévoyez d'utiliser pour votre moteur de recherche de base de données.
Si vous souhaitez afficher les index définis sur une table particulière, vous pouvez utiliser le script suivant pour le faire.
SHOW INDEXES FROM table_name;
Jetons maintenant un coup d'œil à tous les index définis sur la table movies dans myflixdb.
SHOW INDEXES FROM `movies`;
L'exécution du script ci-dessus dans MySQL workbench contre myflixdb nous donne des résultats sur l'index créé.
Remarque: les clés primaires et étrangères de la table ont déjà été indexées par MySQL. Chaque index a son propre nom unique et la colonne sur laquelle il est défini est également affichée.
Syntaxe: Drop index
La commande drop est utilisée pour supprimer les index déjà définis sur une table.
Il peut arriver que vous ayez déjà défini un index sur une table fréquemment mise à jour. Vous souhaiterez peut-être supprimer les index sur une telle table pour améliorer les performances des requêtes UPDATE et INSERT. La syntaxe de base utilisée pour déposer un index sur une table est la suivante.
DROP INDEX `index_id` ON `table_name`;
Regardons maintenant un exemple pratique.
DROP INDEX ` full_names` ON `members_indexed`;
L'exécution de la commande ci-dessus supprime l'index avec l'id `full_names` de la table members_indexed.
Résumé
- Les index sont très puissants lorsqu'il s'agit d'améliorer considérablement les performances des requêtes de recherche MySQL.
- Les index peuvent être définis lors de la création d'une table ou ajoutés ultérieurement après la création de la table.
- Vous pouvez définir des index sur plusieurs colonnes d'une table.
- Le SHOW INDEX FROM nom_table est utilisé pour afficher les index définis sur une table.
- La commande DROP est utilisée pour supprimer un index défini sur une table donnée.