Introduction aux index
Les index sont une partie importante de l’optimisation des bases de données MySQL. Un index permet à la base de données de trouver et de récupérer les données des tables beaucoup plus rapidement en stockant une liste triée de valeurs qui pointent vers les enregistrements complets.
Sans index, MySQL doit analyser chaque ligne d’une table pour trouver les données pertinentes. Cela peut devenir très lent à mesure que les tables grossissent. L’ajout d’index appropriés permet à MySQL de naviguer dans les données de la table de manière beaucoup plus efficace.
Quelques avantages clés de l’utilisation d’index:
- Recherches plus rapides pour des lignes et des valeurs spécifiques
- Amélioration des performances de diverses requêtes, en particulier les JOINTURES et les clauses WHERE
- Capacité à optimiser la récupération des données sur de grandes tables de base de données
- Tris et regroupements plus rapides sur les colonnes indexées
MySQL prend en charge plusieurs types d’index pour améliorer les requêtes de différentes manières:
- NORMAL – Index de base sur une ou plusieurs colonnes
- UNIQUE – Garantit l’unicité des valeurs indexées
- FULLTEXT – Pour optimiser les recherches basées sur du texte
- SPATIAL – Pour les données spatiales comme les emplacements
Ce guide fournira des exemples de création et d’utilisation de ces différents types d’index MySQL pour améliorer les performances de la base de données.
Connexion à MySQL et configuration d’une base de données d’exemple
Avant de démontrer les index, nous avons besoin d’une base de données MySQL avec quelques exemples de données. Voici les étapes pour se connecter à MySQL et créer une simple base de données «contacts»:
- Connectez-vous au serveur MySQL depuis la ligne de commande avec le client mysql:
$ mysql -u root -p- Lorsque vous y êtes invité, entrez le mot de passe de l’utilisateur root MySQL. Vous devriez maintenant être au moniteur MySQL.
- Créez une nouvelle base de données appelée
contacts:
mysql> CREATE DATABASE contacts;- Basculez vers la nouvelle base de données contacts:
mysql> USE contacts;- Créez un tableau
contactsavec quelques exemples de données:
mysql> CREATE TABLE contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20)
);
mysql> INSERT INTO contacts (first_name, last_name, email, phone)
VALUES
('John', 'Doe', '[email protected]', '555-555-5555'),
('Jane', 'Doe', '[email protected]', '555-555-5556'),
('Bob', 'Smith', '[email protected]', '555-555-5557');Cela crée une table simple pour stocker des informations de contact comme le nom, l’e-mail et le numéro de téléphone.
Nous pouvons confirmer que cela a fonctionné en interrogeant la table:
mysql> SELECT * FROM contacts;Cela devrait afficher les exemples de données que nous avons insérés. Nous avons maintenant une base de données et une table prêtes à démontrer l’utilisation d’index.
+----+------------+-----------+--------------------+---------------+
| id | first_name | last_name | email | phone |
+----+------------+-----------+--------------------+---------------+
| 1 | John | Doe | [email protected] | 555-555-5555 |
| 2 | Jane | Doe | [email protected] | 555-555-5556 |
| 3 | Bob | Smith | [email protected] | 555-555-5557 |
+----+------------+-----------+--------------------+---------------+
3 lignes trouvées (0.00 sec)Utilisation d’index sur une seule colonne
Un index standard dans MySQL indexe une colonne d’une table de base de données. Cela permet des recherches et des tris rapides sur cette colonne particulière.
Pour ajouter un index à une colonne, nous pouvons utiliser l’instruction CREATE INDEX:
mysql> CREATE INDEX idx_last_name ON contacts(last_name);Cela ajoute un index nommé idx_last_name sur la colonne last_name du tableau contacts.
L’indexation de la colonne last_name peut optimiser les requêtes comme celle-ci qui filtrent par un nom de famille:
mysql> SELECT * FROM contacts WHERE last_name='Doe';Au lieu d’analyser chaque ligne, MySQL peut rechercher rapidement la colonne last_name indexée pour trouver les lignes pertinentes.
+----+------------+-----------+--------------------+---------------+
| id | first_name | last_name | email | phone |
+----+------------+-----------+--------------------+---------------+
| 1 | John | Doe | [email protected] | 555-555-5555 |
| 2 | Jane | Doe | [email protected] | 555-555-5556 |
+----+------------+-----------+--------------------+---------------+
2 lignes trouvées (0.00 sec)Cela montre les lignes renvoyées de la table contacts où le nom de famille est ‘Doe’. L’index sur la colonne last_name permet à cette requête de s’exécuter rapidement sans analyse de toute la table.
Nous pouvons ajouter des index similaires sur d’autres colonnes de table comme email ou phone:
mysql> CREATE INDEX idx_email ON contacts(email);
mysql> CREATE INDEX idx_phone ON contacts(phone);Cela permet également des recherches rapides par ces colonnes.
Pour supprimer un index lorsqu’il n’est plus nécessaire, utilisez DROP INDEX:
mysql> DROP INDEX idx_email ON contacts;Les index sur une seule colonne fonctionnent le mieux sur des colonnes fréquemment utilisées pour les recherches et les jointures. Ne sur-indexez pas toutes les colonnes, car les index occupent de l’espace de stockage et ralentissent les opérations d’écriture comme INSERT et UPDATE car les index doivent également être mis à jour.
Utilisation d’index uniques pour éviter la duplication des données
Dans de nombreux cas, nous voulons empêcher la duplication de valeurs dans certaines colonnes, telles que les adresses e-mail ou les noms d’utilisateur. MySQL fournit un index UNIQUE spécial qui applique cette contrainte:
mysql> CREATE UNIQUE INDEX idx_email ON contacts(email);Cet index ne permet que des valeurs d’e-mail uniques à insérer dans cette colonne. Si nous essayons d’insérer un e-mail en double:
mysql> INSER TINTO contacts (first_name, last_name, email, phone)
VALUES ('Bob', 'Jones', '[email protected]', '555-555-5558');Nous obtiendrions une erreur:
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'idx_email' L’index unique a empêché l’insertion de l’e-mail en double. Cela aide à appliquer l’intégrité des données dans les colonnes importantes.
Comme les autres index, l’index UNIQUE fournit toujours des recherches rapides par colonne indexée. L’unicité est simplement une contrainte supplémentaire.
Utilisation d’index sur plusieurs colonnes
Les index peuvent également être créés sur plusieurs colonnes. Cela permet d’optimiser les requêtes qui filtrent sur ces colonnes dans le même ordre.
Par exemple, pour indexer les colonnes first_name et last_name:
mysql> CREATE INDEX idx_name ON contacts(first_name, last_name); Cela peut optimiser une requête avec une clause WHERE sur les deux colonnes:
mysql> SELECT * FROM contacts WHERE first_name='Jane'AND last_name='Doe';Cela fonctionne également pour les requêtes ne filtrant que la première colonne indexée:
mysql> SELECT * FROM contacts WHERE first_name='Jane';Mais cela n’optimiserait pas une requête ne filtrant que la deuxième colonne indexée:
mysql> SELECT * FROM contacts WHERE last_name='Doe';Dans ce cas, l’index sur une seule colonne sur last_name serait utilisé à la place.
L’ordre des colonnes dans un index sur plusieurs colonnes est important. Les optimisations s’appliquent aux clauses WHERE avec des filtres sur les préfixes des colonnes indexées.
Liste et suppression des index existants
Pour comprendre quels index existent sur une table, nous pouvons interroger la base de données système INFORMATION_SCHEMA:
mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA ='contacts'AND TABLE_NAME ='contacts';Cela afficherait les métadonnées sur les index de notre table, y compris leurs noms et colonnes.
Par example:
+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT |
+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+
| def | contacts | contacts | 0 | contacts | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | |
| def | contacts | contacts | 1 | contacts | idx_email | 1 | email | A | 3 | NULL | NULL | YES | BTREE | |
+------+------------+------------+------------+--------------+-------------+----------+------------+----------+--------+------+------------+---------+---------------+Lorsqu’un index n’est plus nécessaire, nous pouvons le supprimer avec DROP INDEX:
mysql> DROP INDEX idx_name ON contacts;Supprimer les index qui ne sont pas optimisés et régulièrement utilisés peut aider à améliorer les performances d’écriture et à réduire les exigences de stockage.
Conclusion
L’ajout d’index fournit des optimisations puissantes pour l’interrogation et la manipulation des données dans les tables MySQL. Les bons index peuvent grandement accélérer les recherches, les filtres, le tri et les jointures.
Quelques points clés à retenir:
- Utilisez des index sur une seule colonne pour les colonnes fréquemment filtrées
- Les index UNIQUES empêchent les entrées en double dans une colonne
- Les index sur plusieurs colonnes optimisent les requêtes filtrant sur ces colonnes dans l’ordre
- Ne sur-indexez pas! Évaluez les besoins des requêtes et supprimez les index qui ne sont pas utilisés.
Tirer pleinement parti des index est crucial pour des performances optimales de base de données MySQL, surtout à mesure que les données augmentent. Prenez le temps de comprendre les compromis d’indexation et les meilleures pratiques lors de la conception du schéma de base de données et des requêtes.
Français
English
العربية
Webhi.Technology
@WebHiTechnology