Ajouter un index composite pour les requêtes multi-colonnes
Dans cette étape, vous allez apprendre à créer un index composite en MySQL. Un index composite est un index sur deux colonnes ou plus d'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 le plus efficace lorsque les colonnes sont spécifiées dans le même ordre dans la clause WHERE de la requête.
Continuons à utiliser la table users que nous avons créée dans les étapes précédentes. Ouvrez un terminal dans la machine virtuelle LabEx (en utilisant le raccourci Xfce Terminal sur le bureau) et connectez-vous au serveur MySQL en tant qu'utilisateur root :
mysql -u root -proot
Ajoutons plus de données à la table users, y compris différentes villes :
INSERT INTO users (username, email, city) VALUES
('alice_brown', '[email protected]', 'Los Angeles'),
('bob_davis', '[email protected]', 'Chicago'),
('charlie_wilson', '[email protected]', 'New York'),
('david_garcia', '[email protected]', 'Los Angeles');
Création d'un index composite
Supposons que vous exécutiez souvent des requêtes qui filtrent les utilisateurs à la fois 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érification de 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 de 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 | Visible | Expression |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_username | 1 | username | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_city_username | 1 | city | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL |
| users | 1 | idx_city_username | 2 | username | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Utilisation de l'index composite
Pour voir l'avantage de l'index composite, vous pouvez utiliser la commande EXPLAIN pour analyser une requête qui utilise à la fois les colonnes city et username dans la clause WHERE.
EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';
La sortie de 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 | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_city_username | idx_city_username | 770 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
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 d'abord 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 peut ne pas utiliser l'index, ou l'utiliser seulement partiellement, car la colonne city n'est pas la colonne principale de l'index.
exit;
