<h1>Comparaison des performances MySQL : travailler avec et sans index</h1>
<p>Dans cet article, nous examinons comment les index affectent les performances des requêtes dans MySQL, en comparant l'exécution des opérations de filtrage et de JOIN avec et sans index.</p>
<h2>Que sont les index et pourquoi sont-ils nécessaires ?</h2>
<p>Les index dans MySQL sont des structures de données spéciales qui accélèrent la recherche et la récupération des données dans les tables. Sans index, MySQL doit effectuer un balayage complet de la table, ce qui revient à lire un livre entier pour trouver un seul mot. Avec les index, la recherche devient comparable à l'utilisation de la table des matières d'un livre.</p>
<h3>Exemple 1 : Filtrage des données par un seul champ</h3>
<p><strong>Configuration de l'environnement de test</strong></p>
<p>Créons une table de test et remplissons-la avec des données :</p>
<div class="code">-- Créer la base de données de test
CREATE DATABASE test_indexes;
USE test_indexes;
-- Créer une table sans index
CREATE TABLE users_no_index (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Créer une table similaire avec index
CREATE TABLE users_with_index (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_age (age)
);
-- Remplir les tables avec des données de test (100 000 enregistrements chacune)
DELIMITER $$
CREATE PROCEDURE GenerateTestData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO users_no_index (name, email, age)
VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
INSERT INTO users_with_index (name, email, age)
VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL GenerateTestData();</div>
<h3>Comparaison des performances de filtrage</h3>
<p><strong>Requête sans index :</strong></p>
<div class="code">-- Analyser la requête sans index
EXPLAIN SELECT * FROM users_no_index WHERE age = 25;</div>
<p><strong>Résultats EXPLAIN :</strong></p>
<ul>
<li>type : ALL (balayage complet de la table)</li>
<li>rows : 100000 (100 000 lignes vérifiées)</li>
<li>Extra : Using where</li>
</ul>
<p><strong>Temps d'exécution :</strong> ~120 ms</p>
<p><strong>Requête avec index :</strong></p>
<div class="code">-- Analyser la requête avec index
EXPLAIN SELECT * FROM users_with_index WHERE age = 25;</div>
<p><strong>Résultats EXPLAIN :</strong></p>
<ul>
<li>type : ref (recherche par index)</li>
<li>rows : ~1000 (seulement ~1000 lignes vérifiées)</li>
<li>key : idx_age (index utilisé)</li>
<li>Extra : Using index condition</li>
</ul>
<p><strong>Temps d'exécution :</strong> ~5 ms</p>
<p><strong>Conclusions sur le filtrage</strong></p>
<ul>
<li><strong>Sans index :</strong> MySQL effectue un balayage complet de la table, en vérifiant chaque ligne</li>
<li><strong>Avec index :</strong> MySQL utilise l'index B-tree pour trouver rapidement les lignes requises</li>
<li><strong>Différence de performance :</strong> 20 à 30 fois plus rapide</li>
</ul>
<h3>Exemple 2 : Opérations JOIN avec et sans index</h3>
<p><strong>Préparation des tables liées</strong></p>
<div class="code">-- Table des commandes sans index
CREATE TABLE orders_no_index (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20)
);
-- Table des commandes avec index
CREATE TABLE orders_with_index (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20),
INDEX idx_user_id (user_id)
);
-- Remplir les tables de commandes
DELIMITER $$
CREATE PROCEDURE GenerateOrderData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 50000 DO
INSERT INTO orders_no_index (user_id, amount, order_date, status)
VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
'completed');
INSERT INTO orders_with_index (user_id, amount, order_date, status)
VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000,
DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
'completed');
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL GenerateOrderData();</div>
<h3>Comparaison des opérations JOIN</h3>
<p><strong>JOIN sans index :</strong></p>
<div class="code">-- JOIN sans index
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users_no_index u
JOIN orders_no_index o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
<p><strong>Résultats EXPLAIN :</strong></p>
<ul>
<li>Pour les deux tables : type : ALL</li>
<li>rows : 100000 * 50000 = 5 000 000 000 comparaisons potentielles</li>
<li>Extra : Using where; Using temporary; Using filesort</li>
</ul>
<p><strong>Temps d'exécution :</strong> ~4500 ms</p>
<p><strong>JOIN avec index :</strong></p>
<div class="code">-- JOIN avec index
EXPLAIN
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users_with_index u
JOIN orders_with_index o ON u.id = o.user_id
WHERE u.age BETWEEN 25 AND 35
<p><strong>Résultats EXPLAIN :</strong></p>
<ul>
<li>Pour users : type : range (utilise l'index age)</li>
<li>Pour orders : type : ref (utilise l'index user_id)</li>
<li>rows : beaucoup moins</li>
<li>Utilisation plus efficace des tables temporaires</li>
</ul>
<p><strong>Temps d'exécution :</strong> ~150 ms</p>
<p><strong>Conclusions sur les opérations JOIN</strong></p>
<ul>
<li><strong>Sans index :</strong> MySQL est obligé d'effectuer des boucles imbriquées avec des balayages complets de tables</li>
<li><strong>Avec index :</strong> MySQL utilise efficacement les index pour trouver rapidement les enregistrements liés</li>
<li><strong>Différence de performance :</strong> 30 fois ou plus</li>
</ul>
<h3>Quand utiliser les index ?</h3>
<p><strong>Il est recommandé de créer des index pour :</strong></p>
<ul>
<li>Les champs fréquemment utilisés dans les conditions WHERE</li>
<li>Les champs impliqués dans les opérations JOIN</li>
<li>Les champs utilisés pour le tri (ORDER BY)</li>
<li>Les champs utilisés dans GROUP BY</li>
<li>Les champs uniques ou les clés primaires</li>
</ul>
<p><strong>Quand les index peuvent être inefficaces</strong> :</p>
<ul>
<li>Tables avec des opérations INSERT/UPDATE/DELETE fréquentes</li>
<li>Petites tables (moins de 1000 lignes)</li>
<li>Colonnes à faible sélectivité (peu de valeurs uniques)</li>
</ul>
<h3>Meilleures pratiques pour travailler avec les index</h3>
<ul>
<li><strong>Indexez consciencieusement</strong> — chaque index ralentit les opérations d'écriture</li>
<li><strong>Utilisez des index composites</strong> pour les combinaisons de champs fréquemment utilisées</li>
<li><strong>Surveillez la sélectivité</strong> — les index sur des champs avec peu de valeurs uniques sont moins efficaces</li>
<li><strong>Analysez régulièrement l'utilisation des index</strong> :</li>
</ul>
<div class="code">-- Analyser l'utilisation des index
SELECT * FROM sys.schema_unused_indexes;</div>
<p>5. <strong>Optimisez les index existants</strong> :</p>
<div class="code">-- Analyser les performances des requêtes
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age = 25;</div>
<div class="code">-- Vérifier la fragmentation des index
ANALYZE TABLE users_with_index;</div>
<h2>Conclusion</h2>
<p>Les index sont un outil puissant pour optimiser les performances de MySQL. Une utilisation appropriée peut accélérer l'exécution des requêtes de plusieurs dizaines de fois, en particulier pour les opérations de filtrage et de JOIN. Cependant, il est important de se rappeler l'équilibre — une indexation excessive peut ralentir les opérations d'écriture. Une surveillance régulière et une analyse des performances vous aideront à trouver la configuration d'index optimale pour votre application.</p>
<p>Testez avec vos propres données, car l'efficacité des index dépend fortement des caractéristiques spécifiques de vos données et des modèles de requêtes dans votre application.</p>