MySQL Tutoriel d'indexation - Créer, ajouter et supprimer

Qu'est-ce qu'un index?

Index dans MySQL trier les données de manière séquentielle organisée. Ils sont créés sur la ou les colonnes qui serviront à 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 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. Ceci est dû au fait MySQL crée un nouveau bloc d'index chaque fois que des données sont ajoutées ou mises à jour dans la table. Généralement, 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.

A 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 la manipulation des données soient plus rapides. Mais il y a des limites aux investissements matériels qu’une entreprise peut réaliser. 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 l’intégralité des enregistrements stockés de manière aléatoire les uns après les autres pour localiser les données souhaitées. Cela entraîne des bases de données aux 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 afin de faciliter la recherche.

Syntaxe : Créer un index

Les index peuvent être définis de 2 manières

Au moment de la création du tableau
Une fois la table créée

Mise en situation :

Pour notre myflixdb, nous 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 présenté 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.

"Noter" La table Members_indexed a « full_names » dans le nœud index.

À mesure que la base de membres s'étend 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 sur la table des membres sans que l'index soit défini.

Ajouter une syntaxe de base d'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à une table définie 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 films soient très lentes et que nous souhaitions 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 titre dans la table 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 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 comptez 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 œil à tous les index définis sur la table movies dans myflixdb.

SHOW INDEXES FROM `movies`;

Exécuter le script ci-dessus dans MySQL établi 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 : Supprimer l'index

La commande drop est utilisée pour supprimer des 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 d'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'identifiant ` 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 de MySQL Requêtes de recherche.
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 table_name 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.