MySQL Index : Tutoriel Créer, Ajouter et Déposer

Qu'est-ce que la MySQL Indice?

An indice in MySQL Un index est une structure de données qui stocke les valeurs des colonnes de manière ordonnée, permettant ainsi au moteur de recherche de parcourir rapidement les lignes. Les index sont créés sur la ou les colonnes les plus fréquemment utilisées pour filtrer les données. Imaginez un index comme une liste triée par ordre alphabétique : il est beaucoup plus rapide de trouver un nom dans une liste triée que dans un ensemble non trié.

L'utilisation d'index présente un inconvénient : chaque instruction INSERT ou UPDATE doit mettre à jour l'index. Par conséquent, ajouter trop d'index sur une table fréquemment consultée en écriture peut nuire aux performances globales. En règle générale, il est conseillé d'indexer les colonnes qui apparaissent dans les clauses WHERE, JOIN et ORDER BY des tables plus souvent consultées qu'écrites.

Pourquoi utiliser un index ?

Personne n'apprécie les systèmes lents. La haute performance est une priorité absolue pour la quasi-totalité des applications s'appuyant sur des bases de données. Les entreprises investissent massivement dans le matériel pour garantir la rapidité des requêtes, mais les performances du matériel seul ont leurs limites. L'optimisation des index constitue un levier plus économique et plus efficace.

Les temps de réponse lents sont généralement dus au stockage des lignes dans l'ordre physique sur le disque. Sans index, MySQL Il faut parcourir chaque ligne pour trouver celles qui correspondent à un prédicat — un « parcours complet de la table ». Les index permettent MySQL passer directement aux lignes correspondantes, ce qui transforme le plan de requête de O(n) à environ O(log n) pour les recherches dans un arbre B.

Syntaxe : Créer un index

Un index peut être défini à deux endroits :

1. Au moment de la création du tableau.
2. Après que la table existe déjà.

Exemple : Créer un index directement dans la table CREATE TABLE

Pour la myflixdb Dans la base de données, nous prévoyons de nombreuses recherches sur la colonne « nom complet ». Le script ci-dessous crée une nouvelle base de données. members_indexed tableau avec un index sur le full_names colonne.

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 dans MySQL Établi contre le myflixdb base de données.

Refresh myflixdb pour voir le nouveau members_indexed tableau. le full_names La colonne apparaît désormais sous la Index nœud.

À mesure que le nombre de membres augmente, les requêtes de recherche sur members_indexed qui utilisent WHERE et ORDER BY contre full_names sont beaucoup plus rapides que les mêmes requêtes sur l'original members tableau sans index.

Ajoutez un index après que la table existe déjà.

Vous constaterez souvent qu'une table existante a besoin d'un index : les requêtes de recherche sont lentes et un plan EXPLAIN indique une analyse complète de la table sur une colonne qui apparaît dans la clause WHERE. CREATE INDEX Cette instruction ajoute un index sans recréer la table.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Exemple concret : accélérer les recherches sur le title colonne du movies table:

CREATE INDEX `title_index` ON `movies` (`title`);

Chaque requête qui filtre sur movies.title est désormais prise en charge par le nouvel index. Les requêtes qui filtrent sur d'autres colonnes analysent toujours la table, sauf si elles possèdent leur propre index.

À noter: Vous pouvez créer un index composite sur plusieurs colonnes lorsque vos requêtes filtrent ou trient toujours selon la même combinaison. L'ordre est important : la première colonne détermine si l'index peut être utilisé.

Liste des index d'une table

Utilisez le SHOW INDEXES pour afficher tous les index définis sur une table.

SHOW INDEXES FROM `table_name`;

Exemple — index de liste sur le movies table:

SHOW INDEXES FROM `movies`;

Exécutez l'instruction dans MySQL Workbench à opposer à myflixdb pour voir les index existants et les colonnes qu'ils couvrent.

À noter: Les clés primaires et étrangères sont indexées automatiquement par MySQLChaque index possède un nom unique et répertorie la ou les colonnes qu'il couvre.

Syntaxe : Drop Index

Utilisez le DROP INDEX Supprimer un index existant d'une table. Cette opération est utile lorsqu'une table effectuant de nombreuses écritures est ralentie par un index qui n'est plus rentable en lecture.

DROP INDEX `index_id` ON `table_name`;

Exemple concret : laissez tomber le full_names index de members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Types d' MySQL Index

MySQL Il prend en charge plusieurs types d'index, chacun adapté à une charge de travail différente.

Type	Interet
Clé principale	Identifiant unique de la ligne ; regroupé avec les données de la table dans InnoDB.
UN GOUT	Garantit l'unicité tout en servant d'index.
INDEX (arbre B)	Index secondaire par défaut utilisé pour les requêtes par plage et les recherches d'égalité.
TEXTE INTÉGRAL	Optimisé pour la recherche de texte en langage naturel avec MATCH … AGAINST.
SPATIAL	Index R-tree pour les types de données SIG tels que POINT et POLYGON.
HASH	Recherches d'égalité en temps constant ; utilisées par le moteur de stockage MEMORY.
Composite (multi-colonnes)	Combine plusieurs colonnes en un seul index ; respecte la règle du préfixe le plus à gauche.

Meilleures pratiques pour MySQL Index

Les habitudes ci-dessous permettent de conserver l'utilité des index et d'éviter qu'ils ne deviennent un poids mort.

• Indexez le modèle de requête, et non le nom de la colonne : Ajoutez des index qui correspondent aux véritables clauses WHERE, JOIN et ORDER BY, et non à « toutes les colonnes qui semblent importantes ».
• Ordre des indices composites de surveillance : La première colonne doit figurer dans la requête pour que l'index soit utilisé.
• Évitez les index dupliqués : Un préfixe initial d'un index composite couvre déjà les recherches sur une seule colonne à partir de ce préfixe.
• Inspecter avec EXPLAIN : Vérifiez que le planificateur choisit bien le nouvel indice.
• Supprimer les index inutilisés : utilisé sys.schema_unused_indexes in MySQL 5.7+ pour trouver les index qui ne sont pas lus.
• Types de données correspondants : Si une clause WHERE compare une colonne VARCHAR à un nombre, l'index ne peut pas être utilisé en raison d'une conversion implicite.

FAQ