Créer un index dans MySQL : tutoriel sur l'index MySQL [2022]
Publié: 2021-01-01Un Data Scientist ou un programmeur devra travailler avec de grandes quantités de données et doit pouvoir les traiter efficacement pour une exécution plus rapide. Ils doivent également savoir comment les données sont organisées et les méthodes les plus rapides pour accéder facilement aux données particulières nécessaires.
Étant donné que MySQL est un système de gestion de bases de données relationnelles qui a des applications dans divers secteurs tels que les bases de données Web, les applications de commerce électronique, les médias sociaux, etc., les scientifiques des données doivent connaître toutes les techniques liées à MySQL, dont les index. L'article décrit comment créer un index dans MySQL.
Table des matières
Qu'est-ce qu'un indice ?
Dans MySQL, les index sont utilisés pour trier les données de manière séquentielle organisée et récupérer les données efficacement et rapidement. Ils sont créés en utilisant une ou plusieurs colonnes qui seront utilisées pour filtrer les données. Les index sont un type de table qui conserve un champ primaire ou d'index et un pointeur vers chaque enregistrement de la table principale.
Pour une meilleure compréhension, un index de livre peut être utilisé comme exemple. Au lieu de rechercher une page en parcourant toutes les pages d'un livre, l'index du livre peut être consulté pour trouver la page recherchée.
Dans la plupart des systèmes de base de données, des performances élevées et rapides sont nécessaires. De nombreuses entreprises investissent d'énormes sommes d'argent dans le matériel pour accélérer la récupération et la manipulation des données. Mais en optimisant la base de données, les coûts peuvent être réduits.
Le temps de réponse est généralement plus long car les enregistrements sont stockés de manière aléatoire et les requêtes de recherche doivent trouver les données nécessaires en parcourant l'ensemble des enregistrements stockés. Les index rendent cette recherche plus facile et plus rapide.
Lire : Renommer le nom de la colonne en SQL
Créer un index
Vous pouvez créer un index dans MySQL sur une table en utilisant la syntaxe de base suivante.
CREATE INDEX nom_index nom_table (nom_colonne);
Vous pouvez également créer un index unique sur une table. Cela signifie que le nom est unique et que deux lignes ne peuvent pas porter le même nom. La syntaxe suivante peut être utilisée pour cela.
CREATE UNIQUE INDEX nom_index ON nom_table (nom_colonne);
Si un index secondaire pour une colonne est créé, MySQL stocke les valeurs dans une structure de données distincte telle que B-Tree et Hash. Dans MySQL, si les colonnes sont des colonnes de chaîne, vous pouvez créer un index pour la partie principale des valeurs de colonne de la colonne de chaîne en utilisant la syntaxe :
nom_colonne(longueur)
Pour créer la partie clé du préfixe de colonne lors de la création de la table, la syntaxe suivante peut être utilisée :
CREATE TABLE nom_table(
liste_colonne,
INDEX(nom_colonne(longueur))
);
Et pour ajouter un index à une table existante :
CREATE INDEX nom_index ON nom_table (nom_colonne(longueur));
La longueur ici est le nombre de caractères s'il s'agit d'un type de chaîne non binaire comme CHAR, VARCHAR et TEXT et le nombre d'octets s'il s'agit d'un type de chaîne binaire comme BINARY, VARBINARY et BLOB. Vous pouvez également créer des parties de clé de préfixe de colonne pour les colonnes CHAR, VARCHAR, BINARY et VARBINARY dans MySQL. Il est nécessaire de spécifier les parties clés du préfixe de colonne si vous avez créé des index pour les colonnes BLOB et TEXT.
Dans le cas d'index FULLTEXT qui ne sont pris en charge que pour les tables InnoDB et MyISAM, ils ne peuvent inclure que des colonnes CHAR, VARCHAR et TEXT. Il ne prend pas en charge l'indexation des préfixes de colonne et si une longueur de préfixe est spécifiée, elle est ignorée.
Doit lire : Idées de projets SQL
Modification des tables pour ajouter ou supprimer l'index
Pour ajouter des index à une table, quatre types d'instructions peuvent être utilisées :
- Pour ajouter une clé primaire :
ALTER TABLE nom_table ADD PRIMARY KEY (column_list);
Les valeurs indexées ici doivent être uniques et ne peuvent pas être NULL.
- Pour un index avec des valeurs uniques :
ALTER TABLE nom_table ADD UNIQUE nom_index (liste_colonne);
Les valeurs doivent être uniques, à l'exception de NULL qui peut apparaître plusieurs fois.
- Pour ajouter un index ordinaire :
ALTER TABLE nom_table ADD INDEX nom_index (liste_colonne);
Ici, les valeurs peuvent apparaître plusieurs fois.
- Pour créer un index FULLTEXT spécial :
ALTER TABLE nom_table ADD FULLTEXT nom_index (liste_colonne);
L'index FULLTEXT peut être utilisé à des fins de recherche de texte.
Dans les tableaux existants
Pour ajouter un index dans n'importe quelle table existante, la syntaxe suivante peut être utilisée :
ALTER TABLE nom_table ADD INDEX (nom_index);
Pour supprimer un index dans une table, l'instruction DROP INDEX est utilisée :
ALTER TABLE nom_table DROP INDEX (nom_index);
Afficher les informations d'index
Pour lister tous les index associés à une table, la commande SHOW INDEX est utilisée.
AFFICHER L'INDEX À PARTIR DE nom_table\G
Le '\G' est utilisé pour créer la liste dans un format vertical, cela évite le retour à la ligne longue.
Lisez aussi: Questions et réponses de l'entretien SQL
Index décroissants
InnoDB peut stocker les entrées dans l'ordre décroissant lorsque l'index est un index décroissant. Ainsi, lorsque l'ordre décroissant est demandé dans la requête, l'optimiseur choisit cet index. Ceci est plus efficace pour les requêtes avec des clauses ORDER BY. Ceux-ci ne sont pris en charge que par le moteur de stockage InnoDB.
La syntaxe de création ou d'ajout d'index descendants est similaire à la syntaxe alter ou create utilisée ci-dessus.
ALTER TABLE nom_table ADD nom_index (col1_name desc, col2_name asc);
CREATE INDEX nom_index ON nom_table (col1_name desc, col2_name asc);
Index invisibles
Pour marquer les index comme indisponibles pour l'optimiseur de requête, des index invisibles peuvent être utilisés. MySQL met à jour les index invisibles lorsque les données des colonnes associées à l'index changent. Les index sont visibles par défaut et pour les rendre invisibles, il faut déclarer explicitement la visibilité au moment de la création ou en utilisant la commande ALTER TABLE.
Pour créer un index invisible, la syntaxe suivante est utilisée :
CREATE INDEX nom_index ON nom_table (c1, c2…) INVISIBLE ;
Pour modifier la visibilité des index existants, la syntaxe suivante est utilisée :
ALTER TABLE nom_table ALTER INDEX nom_index [VISIBLE | INVISIBLE];
Inscrivez-vous aux cours de développement de logiciels en ligne des meilleures universités du monde. Gagnez des programmes Executive PG, des programmes de certificat avancés ou des programmes de maîtrise pour accélérer votre carrière.
Conclusion
Les performances des requêtes de recherche MySQL peuvent être améliorées en utilisant des index. Ceux-ci peuvent être définis lors de la création d'une table ou peuvent être ajoutés ultérieurement si la table est déjà créée.
Cet article a donné une brève description de ce qu'est un index et pourquoi il est utilisé. La section suivante explique comment créer un index dans MySQL, comment l'ajouter à une table existante et comment créer un index unique.
Les différentes commandes pour modifier les tables et créer ou supprimer un index ont également été décrites ainsi que l'affichage des informations d'index. Les index décroissants et invisibles ont également été brièvement expliqués.
Dans l'ensemble, l'apprentissage et la pratique pratique de toutes les bases de données mentionnées dans cet article ajouteront à votre expérience. Parcourez l'aperçu du cours, l'apprentissage et les opportunités d'emploi qui y sont associés - des plates-formes comme upGrad offrent des cours puissants conçus par des instituts très réputés comme l'IIIT-B dans le programme Executive PG Program Full Stack Development.
