Types de clé et index MySQL : Booster vos requêtes SQL

Dans MySQL, les index et les clés sont liés mais distincts. Un index est une structure de données qui optimise les recherches, tandis qu’une clé est une contrainte qui garantit l’unicité ou l’intégrité des données.

Définition et rôle d’une clé et d’un index MySQL

✅ Index

Un index est un mécanisme d’optimisation qui accélère l’accès aux données d’une table. Il fonctionne comme un catalogue qui permet à MySQL de retrouver rapidement les enregistrements sans parcourir toute la table.

👉 Un index peut être appliqué sur n’importe quelle colonne pour améliorer les performances des requêtes SQL.

Exemple :

CREATE INDEX idx_nom ON utilisateurs(nom);

💡 Ici, un index est créé sur nom pour accélérer les recherches WHERE nom = 'Dupont'. Cela permettra d’effectuer des recherches par rapport au nom plus rapidement.

✅ Clé (Key)

Une clé est une contrainte utilisée pour garantir l’intégrité des données. Il en existe plusieurs types :

• Clé primaire (PRIMARY KEY) : identifie de manière unique chaque ligne d’une table. Par exemple un ID qui doit être unique.
• Clé étrangère (FOREIGN KEY) : établit une relation entre deux tables.
• Clé unique (UNIQUE) : empêche les doublons sur une colonne.

Exemple :

CREATE TABLE utilisateurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

💡 Ici, id est une clé primaire (elle garantit l’unicité) et email est une clé unique. Deux mails identiques ne pourront pas être enregistrés dans la table.

Différences principales entre une clé et un index MySQL

Relation entre index et clé

• Toutes les clés primaires et uniques sont automatiquement indexées.
• Tous les index ne sont pas des clés ! On peut créer un index sans contrainte d’unicité.

✅ Exemple combiné :

CREATE TABLE commandes (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- Clé primaire = index unique
    client_id INT,
    montant DECIMAL(10,2),
    FOREIGN KEY (client_id) REFERENCES clients(id), -- Clé étrangère
    INDEX idx_montant (montant)  -- Index non contraignant
);

✔ id est une clé primaire donc MySQL crée un index automatiquement.
✔ client_id est une clé étrangère, mais pas un index (sauf si précisé).
✔ montant a un index, mais n’est pas une clé.

Quand utiliser un index et / ou une clé ?

Pour résumer :

🔹 Un index MySQL sert à accélérer les requêtes MySQL, tandis qu’une clé garantit l’intégrité des données.
🔹 Une clé (PRIMARY, UNIQUE) est toujours indexée, mais un index seul n’est pas une clé.
🔹 Utiliser les index de manière stratégique permet d’optimiser MySQL sans alourdir les performances. 

Les différents types de clés MySQL

Dans MySQL, une clé (ou key) est une contrainte qui permet d’assurer l’intégrité et l’organisation des données dans une table. Il existe plusieurs types de clés MySQL, chacune ayant un rôle spécifique.

Clé primaire (PRIMARY KEY)

La clé primaire est une contrainte qui garantit l’unicité de chaque ligne d’une table. Elle ne peut contenir que des valeurs uniques et non nulles (NOT NULL). Une table ne peut avoir qu’une seule clé primaire, mais celle-ci peut être composée de plusieurs colonnes (clé primaire composite).

✅ Exemple de clé primaire sur une seule colonne :

CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(50),
    email VARCHAR(100)
);

📌 id est la clé primaire, MySQL créera automatiquement un index unique dessus.

✅ Exemple de clé primaire composite :

CREATE TABLE commandes (
    client_id INT,
    produit_id INT,
    date_commande DATE,
    PRIMARY KEY (client_id, produit_id)
);

📌 Ici, la clé primaire est composée de client_id et produit_id, garantissant qu’un même client ne peut commander qu’un seul produit unique à la fois.

Clé étrangère (FOREIGN KEY)

Une clé étrangère est utilisée pour créer une relation entre deux tables. Elle garantit que les valeurs d’une colonne existent déjà dans une autre table, évitant ainsi les incohérences de données.

✅ Exemple d’une clé étrangère :

CREATE TABLE commandes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT,
    montant DECIMAL(10,2),
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

📌 client_id est une clé étrangère, ce qui signifie qu’il doit correspondre à un id existant dans la table clients.
📌 L’option ON DELETE CASCADE permet de supprimer automatiquement toutes les commandes du client si ce dernier est supprimé.

Il est conseillé de réaliser un schéma relationnel avec la création d’une base de données. Ce schéma relationnel ou ERD définira au préalable les différentes clés.

Clé unique (UNIQUE KEY)

Une clé unique garantit que les valeurs d’une colonne sont uniques, mais contrairement à la clé primaire, elle peut contenir plusieurs valeurs NULL. Une table peut avoir plusieurs clés uniques.

✅ Exemple de clé unique :

CREATE TABLE utilisateurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

📌 Ici, email est unique, donc MySQL empêchera l’insertion de deux utilisateurs avec la même adresse email.

✅ Clé unique sur plusieurs colonnes :

CREATE TABLE stocks (
    produit_id INT,
    entrepot_id INT,
    quantite INT,
    UNIQUE (produit_id, entrepot_id)
);

📌 Ici, chaque produit_id ne peut apparaître qu’une seule fois par entrepot_id, garantissant ainsi l’unicité d’un produit dans un entrepôt.

Clé indexée (INDEX KEY)

Une clé indexée (ou index simple) accélère les recherches mais ne garantit pas l’unicité. Elle est utilisée pour optimiser les requêtes MySQL fréquentes contenant WHERE, ORDER BY, ou JOIN.

✅ Exemple d’index simple :

CREATE INDEX idx_nom ON utilisateurs(nom);

📌 Ici, MySQL crée un index sur nom, accélérant ainsi les requêtes SELECT * FROM utilisateurs WHERE nom = 'Dupont'.

Clé composite (COMPOSITE KEY)

Une clé composite combine plusieurs colonnes pour garantir l’unicité ou optimiser les performances. Elle peut être utilisée comme clé primaire, clé unique ou index.

✅ Exemple de clé composite (PRIMARY KEY) :

CREATE TABLE inscription (
    etudiant_id INT,
    cours_id INT,
    date_inscription DATE,
    PRIMARY KEY (etudiant_id, cours_id)
);

📌 Ici, un étudiant ne peut pas s’inscrire deux fois au même cours.

✅ Exemple de clé composite (INDEX) :

CREATE INDEX idx_nom_prenom ON utilisateurs(nom, prenom);

📌 Ici, l’index composite accélère les recherches sur (nom, prenom).

Clé fulltext (FULLTEXT KEY)

Une clé FULLTEXT est utilisée pour les recherches en texte intégral (MATCH() AGAINST()). Contrairement à LIKE, qui est lent, FULLTEXT permet une recherche plus rapide et plus efficace sur de gros volumes de texte.

✅ Exemple de clé FULLTEXT :

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(255),
    contenu TEXT,
    FULLTEXT (titre, contenu)
);

📌 Ici, MySQL pourra exécuter des recherches rapides sur titre et contenu.

Clé spatiale (SPATIAL KEY)

Une clé SPATIAL est utilisée pour les données géographiques (POINT, POLYGON, etc.). Elle est prise en charge par le moteur MyISAM et InnoDB.

✅ Exemple d’index spatial :

CREATE TABLE lieux (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100),
    coordonnees POINT NOT NULL,
    SPATIAL INDEX (coordonnees)
);

📌 Cet index spatial optimise les requêtes sur des coordonnées géographiques (ST_Distance(), ST_Contains(), etc.).

Résumé des types de clés MySQL

Les clés MySQL permettent d’assurer l’intégrité des données et d’optimiser les performances. Chaque type de clé a un rôle spécifique :

✅ PRIMARY KEY : identifie chaque ligne de manière unique.
✅ FOREIGN KEY : garantit l’intégrité référentielle entre tables.
✅ UNIQUE KEY : empêche les doublons.
✅ INDEX KEY : accélère les requêtes mais n’impose pas l’unicité.
✅ FULLTEXT et SPATIAL sont spécialisés pour les recherches textuelles et géographiques.

Des formations informatique pour tous !

Débutant ou curieux ? Apprenez le développement web, le référencement, le webmarketing, la bureautique, à maîtriser vos appareils Apple et bien plus encore…

Formateur indépendant, professionnel du web depuis 2006, je vous accompagne pas à pas et en cours particulier, que vous soyez débutant ou que vous souhaitiez progresser. En visio, à votre rythme, et toujours avec pédagogie.

Découvrez mes formations Qui suis-je ?

Bien choisir ses clés est essentiel pour avoir une base de données MySQL performante et fiable !

Les index MySQL pour optimiser la vitesse des requêtes

Dans le monde des bases de données relationnelles, MySQL est l’un des systèmes les plus utilisés. Pourtant, nombre d’administrateurs et de développeurs rencontrent des problèmes de performances liés à l’exécution des requêtes SQL. L’une des meilleures solutions pour optimiser ces performances est l’utilisation efficace des index MySQL.

Qu’est-ce qu’un index MySQL ?

Un index MySQL est une structure de données qui permet d’accélérer l’exécution des requêtes en facilitant l’accès aux enregistrements d’une table. Plutôt que d’effectuer une recherche séquentielle sur l’ensemble des lignes d’une table, MySQL utilise l’index pour localiser rapidement les lignes correspondant à la requête.

Les index MySQL fonctionnent comme un annuaire téléphonique : au lieu de parcourir toutes les entrées pour trouver un nom, vous utilisez l’index alphabétique pour y accéder rapidement.

Pourquoi utiliser un index MySQL ?

L’indexation améliore considérablement la vitesse des requêtes SELECT, surtout sur des tables contenant un grand volume de données. Toutefois, l’utilisation excessive d’index peut ralentir les opérations d’INSERT, UPDATE et DELETE, car la base de données doit maintenir et mettre à jour ces index en permanence.

Avantages des index MySQL :

• Accélération des recherches : les requêtes SELECT deviennent plus rapides.
• Optimisation des jointures : les index facilitent la recherche et l’association de données entre plusieurs tables.
• Meilleure gestion des contraintes d’unicité : un index UNIQUE garantit qu’aucune valeur dupliquée n’existe dans une colonne.

Inconvénients potentiels :

• Consommation d’espace mémoire : chaque index MySQL occupe de l’espace disque.
• Impact sur les performances des INSERT, UPDATE, DELETE : la base doit mettre à jour les index après chaque modification.

Les différents types d’index MySQL

Index primaire (PRIMARY KEY)

L’index PRIMARY KEY est un index unique appliqué sur la clé primaire d’une table. Il est automatiquement créé lorsque vous définissez une clé primaire.

Exemple :

CREATE TABLE utilisateurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

Dans cet exemple, MySQL crée un index PRIMARY KEY sur la colonne id, optimisant ainsi les recherches basées sur cet identifiant.

Index unique (UNIQUE INDEX)

Un index UNIQUE empêche les valeurs dupliquées dans une colonne. Il est souvent utilisé pour des champs comme l’adresse e-mail ou un numéro de série.

Exemple :

CREATE UNIQUE INDEX idx_email ON utilisateurs(email);

Cet index garantit que deux utilisateurs ne pourront pas avoir la même adresse e-mail.

Index standard (INDEX)

Un index classique est utilisé pour accélérer les requêtes SELECT sur des colonnes non uniques.

Exemple :

CREATE INDEX idx_nom ON utilisateurs(nom);

Dans une table contenant plusieurs millions d’utilisateurs, cet index accélérera les recherches basées sur le nom.

Index composite

Un index composite combine plusieurs colonnes pour optimiser les requêtes utilisant plusieurs critères de recherche.

Exemple :

CREATE INDEX idx_nom_prenom ON utilisateurs(nom, prenom);

Cet index est utile si des requêtes fréquentes filtrent sur nom et prenom simultanément.

Index FULLTEXT

L’index FULLTEXT est utilisé pour la recherche textuelle avancée, notamment dans des colonnes contenant du texte volumineux.

Exemple :

CREATE FULLTEXT INDEX idx_description ON articles(description);

Grâce à FULLTEXT, MySQL peut exécuter des recherches performantes avec MATCH et AGAINST.

Index spatial (SPATIAL INDEX)

Utilisé pour stocker et interroger des données géospatiales, l’index SPATIAL est disponible avec le moteur InnoDB et MyISAM.

Exemple :

CREATE SPATIAL INDEX idx_location ON points(coordonnees);

Cas pratiques : amélioration des performances avec les index MySQL

Cas n°1 : Requêtes lentes sur une table volumineuse

Imaginons une table ventes avec 1 million d’enregistrements. Une requête sans index sur produit_id prend plusieurs secondes :

SELECT * FROM ventes WHERE produit_id = 123;

L’ajout d’un index MySQL sur produit_id accélère la requête :

CREATE INDEX idx_produit ON ventes(produit_id);

Après indexation, la requête s’exécute presque instantanément.

Cas n°2 : Optimisation des jointures

Une requête avec jointure entre commandes et clients peut être optimisée en indexant les clés étrangères :

CREATE INDEX idx_client_id ON commandes(client_id);

Cela améliore la vitesse des recherches sur client_id.

Cas n°3 : Recherche textuelle optimisée

Au lieu d’utiliser LIKE '%mot%', qui est lent, l’index FULLTEXT optimise les recherches :

SELECT * FROM articles WHERE MATCH(description) AGAINST('optimisation');

Avec un index FULLTEXT, cette requête devient extrêmement rapide.

Bonnes pratiques pour utiliser les index MySQL efficacement

• N’indexez que les colonnes utilisées fréquemment dans les WHERE, JOIN et ORDER BY.
• Évitez l’indexation excessive : trop d’index ralentissent les INSERT, UPDATE et DELETE.
• Utilisez EXPLAIN pour analyser les requêtes et identifier les index à améliorer : EXPLAIN SELECT * FROM ventes WHERE produit_id = 123;
• Optez pour des index composite uniquement si plusieurs colonnes sont souvent utilisées ensemble dans les requêtes.
• Supprimez les index inutiles pour libérer de l’espace disque : DROP INDEX idx_nom ON utilisateurs;

Pour résumer :

Les index MySQL sont des outils puissants pour améliorer la rapidité des requêtes, mais ils doivent être utilisés avec précaution. En choisissant les bons types d’index en fonction des besoins, il est possible d’optimiser significativement la performance des bases de données MySQL.

Si vous souhaitez aller plus loin, utilisez régulièrement EXPLAIN pour analyser l’impact des index et adaptez vos stratégies d’indexation en fonction de l’évolution de vos données.

Aller plus loin avec les bases de données MySQL :

• Apprenez à concevoir un schéma relationnel entre les tables d’une base de données
• Liste complète des requêtes SQL ou MySQL à utiliser en PHP
• Gestion d’une base de données en orientée objet PHP avec MySQLI
• Stocker et manipuler des fichiers JSON directement en MySQL
• Exporter une base de données au format CSV en PHP avec MySQLI

Alban

Fondateur de l’agence Créa-troyes.
Intervenant en Freelance.
Contactez-moi

crea-troyes.fr