Index MySQL et optimisation des performances

Introduction

Dans ce laboratoire, vous apprendrez les index MySQL et les techniques d'optimisation des performances. Le laboratoire se concentre sur la création et la gestion des index pour améliorer les performances des requêtes de base de données.

Vous commencerez par créer une table users et insérer des données d'exemple. Ensuite, vous créerez un index sur une seule colonne (username) et apprendrez à vérifier sa création. Le laboratoire couvrira également l'analyse des plans de requête à l'aide de EXPLAIN, l'ajout d'index composites pour les requêtes multi-colonnes et la suppression des index inutilisés pour maintenir l'efficacité de la base de données.

Créer un index sur une seule colonne dans une table

Dans cette étape, vous apprendrez comment créer un index sur une seule colonne dans MySQL. Les index sont cruciaux pour améliorer les performances des requêtes de base de données, surtout lorsqu'il s'agit de grandes tables. Un index sur une colonne permet à la base de données de localiser rapidement les lignes qui correspondent à une valeur spécifique dans cette colonne, sans avoir à parcourir toute la table.

Comprendre les index

Pensez à un index comme à l'index d'un livre. Au lieu de lire tout le livre pour trouver un sujet spécifique, vous pouvez utiliser l'index pour localiser rapidement les pages pertinentes. De même, un index de base de données aide le moteur de base de données à trouver rapidement des lignes spécifiques.

Créer une table

Tout d'abord, créons une table simple nommée users pour démontrer la création d'un index. Ouvrez un terminal dans la VM LabEx. Vous pouvez utiliser le raccourci Xfce Terminal sur le bureau.

Connectez-vous au serveur MySQL en tant qu'utilisateur root :

sudo mysql -u root

Tout d'abord, créez une base de données pour ce laboratoire et sélectionnez-la :

CREATE DATABASE lab_db;
USE lab_db;

Maintenant, créez la table users :

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Cette instruction SQL crée une table nommée users avec des colonnes pour id, username, email et created_at. La colonne id est définie comme clé primaire et s'auto-incrémente.

Insérons quelques données d'exemple dans la table users :

INSERT INTO users (username, email) VALUES
('john_doe', 'john.doe@example.com'),
('jane_smith', 'jane.smith@example.com'),
('peter_jones', 'peter.jones@example.com');

Créer un index sur une seule colonne

Maintenant, créons un index sur la colonne username. Cela aidera à accélérer les requêtes qui recherchent des utilisateurs par leur nom d'utilisateur.

CREATE INDEX idx_username ON users (username);

Cette instruction crée un index nommé idx_username sur la colonne username de la table users.

Vérifier l'index

Vous pouvez vérifier que l'index a été créé en utilisant la commande SHOW INDEXES :

SHOW INDEXES FROM users;

La sortie affichera les détails des index sur la table users, y compris l'index idx_username que vous venez de créer. Vous devriez voir une ligne où Key_name est idx_username et Column_name est username.

+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY      |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_username |            1 | username    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Vous avez maintenant créé avec succès un index sur une seule colonne dans une table. Cela améliorera les performances des requêtes qui filtrent les données en fonction de la colonne indexée.

Analyser un plan de requête avec EXPLAIN

Dans cette étape, vous apprendrez à utiliser l'instruction EXPLAIN dans MySQL pour analyser le plan d'exécution d'une requête. Comprendre le plan de requête est essentiel pour identifier les goulots d'étranglement de performance et optimiser vos requêtes.

Qu'est-ce qu'un plan de requête ?

Un plan de requête est une feuille de route que le moteur de base de données utilise pour exécuter une requête. Il décrit l'ordre dans lequel les tables sont accédées, les index utilisés et les algorithmes appliqués pour récupérer les données. En analysant le plan de requête, vous pouvez comprendre comment la base de données exécute votre requête et identifier les domaines à améliorer.

Utiliser l'instruction EXPLAIN

L'instruction EXPLAIN fournit des informations sur la manière dont MySQL exécute une requête. Elle montre les tables impliquées, les index utilisés, l'ordre de jointure et d'autres détails qui peuvent vous aider à comprendre les performances de la requête.

Maintenant, analysons une requête simple à l'aide de EXPLAIN.

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

La sortie de l'instruction EXPLAIN sera un tableau avec plusieurs colonnes. Voici une description de certaines des colonnes les plus importantes :

id : L'identifiant de l'instruction SELECT.
select_type : Le type de requête SELECT (par exemple, SIMPLE, PRIMARY, SUBQUERY).
table : La table accédée.
type : Le type de jointure. C'est l'une des colonnes les plus importantes. Les valeurs courantes incluent :
- system : La table ne contient qu'une seule ligne.
- const : La table contient au plus une ligne correspondante, qui est lue au début de la requête.
- eq_ref : Une ligne est lue à partir de cette table pour chaque combinaison de lignes des tables précédentes. Ceci est utilisé lors de la jointure sur une colonne indexée.
- ref : Toutes les lignes correspondantes sont lues à partir de cette table pour chaque combinaison de lignes des tables précédentes. Ceci est utilisé lors de la jointure sur une colonne indexée.
- range : Seules les lignes dans une plage donnée sont récupérées, en utilisant un index.
- index : Un scan complet de l'index est effectué.
- ALL : Un scan complet de la table est effectué. C'est le type le moins efficace.
possible_keys : Les index que MySQL pourrait utiliser pour trouver les lignes dans la table.
key : L'index que MySQL a réellement utilisé.
key_len : La longueur de la clé que MySQL a utilisée.
ref : Les colonnes ou constantes qui sont comparées à l'index.
rows : Le nombre de lignes que MySQL estime devoir examiner pour exécuter la requête.
Extra : Informations supplémentaires sur la manière dont MySQL exécute la requête. Les valeurs courantes incluent :
- Using index : La requête peut être satisfaite en utilisant uniquement l'index.
- Using where : MySQL doit filtrer les lignes après avoir accédé à la table.
- Using temporary : MySQL doit créer une table temporaire pour exécuter la requête.
- Using filesort : MySQL doit trier les lignes après avoir accédé à la table.

Interpréter la sortie de EXPLAIN

Pour la requête SELECT * FROM users WHERE username = 'john_doe', la sortie de EXPLAIN devrait ressembler à ceci :

+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | users | ref  | idx_username  | idx_username | 767     | const | 1    | Using index condition |
+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+

Dans cet exemple :

type est ref, ce qui signifie que MySQL utilise un index pour trouver la ligne correspondante.
possible_keys et key affichent tous deux idx_username, ce qui signifie que MySQL utilise l'index idx_username que nous avons créé à l'étape précédente.
rows est 1, ce qui signifie que MySQL estime devoir examiner une seule ligne pour exécuter la requête.

Analyser une requête sans index

Maintenant, analysons une requête qui n'utilise pas d'index. Tout d'abord, ajoutons une nouvelle colonne à la table users appelée city :

ALTER TABLE users ADD COLUMN city VARCHAR(255);

Maintenant, exécutons un EXPLAIN sur une requête qui recherche par city. Comme nous n'avons pas encore ajouté de données à la colonne city, mettons à jour l'une des lignes :

UPDATE users SET city = 'New York' WHERE username = 'john_doe';

Exécutez maintenant à nouveau l'instruction EXPLAIN :

EXPLAIN SELECT * FROM users WHERE city = 'New York';

La sortie pourrait ressembler à ceci :

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

Dans cet exemple :

type est ALL, ce qui signifie que MySQL effectue un scan complet de la table.
possible_keys et key sont tous deux NULL, ce qui signifie que MySQL n'utilise aucun index.
rows est 3, ce qui signifie que MySQL estime devoir examiner les 3 lignes de la table pour exécuter la requête.
Extra affiche Using where, ce qui signifie que MySQL doit filtrer les lignes après avoir accédé à la table.

Cela indique que la requête n'est pas optimisée et pourrait bénéficier d'un index sur la colonne city.

Ajouter un index composite pour les requêtes multi-colonnes

Dans cette étape, vous apprendrez comment créer un index composite dans MySQL. Un index composite est un index sur deux colonnes ou plus dans une table. Il peut améliorer considérablement les performances des requêtes qui filtrent les données en fonction de plusieurs colonnes.

Qu'est-ce qu'un index composite ?

Un index composite est un index qui couvre plusieurs colonnes. Il est utile lorsque les requêtes utilisent fréquemment plusieurs colonnes dans la clause WHERE. L'ordre des colonnes dans l'index composite est important. L'index est plus efficace lorsque les colonnes sont spécifiées dans le même ordre dans la clause WHERE de la requête.

Ajoutons des données supplémentaires à la table users, y compris différentes villes :

INSERT INTO users (username, email, city) VALUES
('alice_brown', 'alice.brown@example.com', 'Los Angeles'),
('bob_davis', 'bob.davis@example.com', 'Chicago'),
('charlie_wilson', 'charlie.wilson@example.com', 'New York'),
('david_garcia', 'david.garcia@example.com', 'Los Angeles');

Créer un index composite

Supposons que vous exécutiez fréquemment des requêtes qui filtrent les utilisateurs par city et username. Dans ce cas, vous pouvez créer un index composite sur les colonnes city et username.

CREATE INDEX idx_city_username ON users (city, username);

Cette instruction crée un index nommé idx_city_username sur les colonnes city et username de la table users.

Vérifier l'index

Vous pouvez vérifier que l'index a été créé en utilisant la commande SHOW INDEXES :

SHOW INDEXES FROM users;

La sortie affichera les détails des index sur la table users, y compris l'index idx_city_username que vous venez de créer. Vous devriez voir deux lignes pour idx_city_username, une pour la colonne city et une pour la colonne username.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY           |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_username      |            1 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            1 | city        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            2 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Utiliser l'index composite

Pour voir l'avantage de l'index composite, vous pouvez utiliser la commande EXPLAIN pour analyser une requête qui utilise les colonnes city et username dans la clause WHERE.

EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';

La sortie EXPLAIN montrera que la requête utilise l'index idx_city_username, ce qui signifie que la base de données peut trouver rapidement la ligne correspondante sans parcourir toute la table. Recherchez les colonnes possible_keys et key dans la sortie. Si l'index est utilisé, vous verrez idx_city_username dans ces colonnes.

+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+
| id   | select_type | table | type | possible_keys                  | key          | key_len | ref   | rows | Extra                              |
+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | users | ref  | idx_username,idx_city_username | idx_username | 767     | const | 1    | Using index condition; Using where |
+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+

Ordre des colonnes dans l'index

L'ordre des colonnes dans l'index composite est important. Si vous créez un index sur (username, city) au lieu de (city, username), l'index sera moins efficace pour les requêtes qui filtrent par city puis par username.

Par exemple, si nous avions un index sur (username, city) et que nous exécutons la requête suivante :

EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';

MySQL pourrait ne pas utiliser l'index, ou l'utiliser partiellement, car la colonne city n'est pas la colonne principale de l'index.

Supprimer un index inutilisé

Dans cette étape, vous apprendrez comment supprimer un index inutilisé dans MySQL. Bien que les index puissent améliorer considérablement les performances des requêtes, ils ajoutent également une surcharge aux opérations d'écriture (insertions, mises à jour et suppressions). Par conséquent, il est important d'identifier et de supprimer les index qui ne sont plus utilisés.

Pourquoi supprimer les index inutilisés ?

Les index inutilisés occupent de l'espace disque et peuvent ralentir les opérations d'écriture. Lorsque les données sont modifiées dans une table, le moteur de base de données doit également mettre à jour tous les index de cette table. Si un index n'est utilisé par aucune requête, il n'ajoute qu'une surcharge inutile.

Dans les étapes précédentes, nous avons créé un index nommé idx_username sur la colonne username. Supposons qu'après avoir analysé vos modèles de requêtes, vous déterminiez que cet index n'est plus utilisé.

Supprimer l'index

Pour supprimer l'index idx_username, vous pouvez utiliser l'instruction DROP INDEX :

DROP INDEX idx_username ON users;

Cette instruction supprime l'index idx_username de la table users.

Vérifier la suppression de l'index

Vous pouvez vérifier que l'index a été supprimé en utilisant la commande SHOW INDEXES :

SHOW INDEXES FROM users;

La sortie affichera les détails des index sur la table users. Vous ne devriez plus voir l'index idx_username dans la sortie.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY           |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            1 | city        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            2 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Identifier les index inutilisés

Dans un scénario réel, identifier les index inutilisés peut être difficile. MySQL fournit plusieurs outils et techniques pour vous aider dans cette tâche :

MySQL Enterprise Audit : Cette fonctionnalité vous permet d'enregistrer toutes les requêtes exécutées sur votre serveur. Vous pouvez ensuite analyser les journaux de requêtes pour identifier quels index sont utilisés.
Performance Schema : Le Performance Schema fournit des informations détaillées sur les performances du serveur, y compris l'utilisation des index.
Outils tiers : Plusieurs outils tiers peuvent vous aider à surveiller l'utilisation des index et à identifier les index inutilisés.

En surveillant régulièrement l'utilisation de vos index et en supprimant les index inutilisés, vous pouvez améliorer les performances globales de votre base de données.

Maintenant que nous avons terminé toutes les étapes, quittons la console MySQL :

exit;

Exemple de suppression d'index de table MySQL users

Résumé

Dans ce laboratoire, vous avez appris à créer un index sur une seule colonne dans MySQL pour améliorer les performances des requêtes. Vous avez également appris à utiliser l'instruction EXPLAIN pour analyser les plans d'exécution des requêtes et identifier les goulots d'étranglement en matière de performances. De plus, vous vous êtes exercé à créer des index composites pour les requêtes multi-colonnes et à supprimer les index inutilisés pour maintenir l'efficacité de la base de données. Comprendre et utiliser efficacement les index est une compétence fondamentale pour optimiser les performances des bases de données.