Cours MariaDB / Choisir les colonnes d’un index composé et vérifier son utilisation
Un index est une structure qui peut aider MariaDB à retrouver et à ordonner des lignes ; l’ordre des colonnes d’un index composé influence les requêtes qu’il sert. Examinez les conditions, le tri, les colonnes demandées et les index existants avant d’ajouter une clé, puis évaluez le gain en lecture avec son coût en écriture et en stockage.
Considérer l’index comme une structure à entretenir
Au lieu de parcourir toutes les lignes, le moteur peut utiliser des clés ordonnées pour limiter la zone recherchée. Mais INSERT, UPDATE et DELETE doivent aussi entretenir les index concernés. Ajouter des index partout n’améliore donc pas automatiquement l’ensemble de la charge.
Partez des requêtes réellement fréquentes : valeurs comparées par égalité, plages, jointures et ordre attendu. La sélectivité, c’est-à-dire la proportion de lignes retenues, compte également. Une recherche qui renvoie presque toute la table n’a pas les mêmes besoins qu’une recherche très ciblée. Le guide MariaDB de conception d’un index pour un SELECT présente cette démarche et ses limites.
| Requête ou règle | Ordre de clé à examiner | Point de contrôle |
|---|---|---|
| Commandes récentes d’un client | customer_id, ordered_at, order_id | Égalité, période puis départage |
| Toutes les commandes d’une période | Un ordre commençant par la date peut être nécessaire | Besoin différent d’une clé commençant par le client |
| Code produit unique | UNIQUE(sku) | Intégrité des données autant que recherche |
| Support d’une clé étrangère existante | Consulter SHOW INDEX | Éviter un doublon involontaire |
Examiner les index déjà présents
Connectez-vous au serveur d’apprentissage avec course_user dans shopcourse. Le compte doit pouvoir créer, remplir, lire et nettoyer les objets d’exercice et y créer des index. Le guide du client MariaDB et des fichiers SQL UTF-8 décrit la connexion. Créez un dossier et enregistrez le bloc complet ci-dessous en UTF-8, par exemple dans C:/mariadb-lab/c39-index.sql. Dans le client MariaDB, lancez SOURCE C:/mariadb-lab/c39-index.sql; avec le chemin réel. Le SQL se lance dans la connexion au serveur, pas directement dans l’invite du système.
Contrôlez le serveur et la base avec SELECT VERSION(), DATABASE();, puis les noms avec SHOW TABLES;. Les tables c39_customers, c39_products, c39_orders et c39_items sont propres à cet exercice. Si elles existent déjà, examinez leurs définitions et leurs lignes avant toute suppression. Ne remplacez pas des données à conserver.
USE shopcourse;
CREATE TABLE c39_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c39_order_customer FOREIGN KEY (customer_id)
REFERENCES c39_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c39_item_order FOREIGN KEY (order_id)
REFERENCES c39_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id)
REFERENCES c39_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c39_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c39_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c39_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c39_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Vous obtenez quatre clients, quatre produits, quatre commandes et six lignes de détail. Les littéraux coréens sont conservés pour reproduire le jeu de données entre langues. Les produits 1 à 4 sont un carnet, un stylo, une tasse et une lampe ; les clients 1 et 3 ont la ville Séoul, les clients 2 et 4 Busan et Daejeon. Gardez les chaînes UTF-8 et comparez les identifiants.
Avant d’ajouter un index sur les commandes, consultez ceux qui existent :
SHOW INDEX FROM c39_orders;
La clé primaire et les clés étrangères peuvent déjà disposer d’index de support. Lisez Key_name pour regrouper les lignes d’un index, Seq_in_index pour l’ordre de ses colonnes, Column_name pour la colonne et Non_unique pour son caractère unique ou non. Vérifiez aussi les éventuelles longueurs de préfixe avec Sub_part. Un nom différent ne prouve pas une structure différente.
Conservez cette sortie pour la comparer à celle obtenue après l’ajout. La référence des clés étrangères explique les conditions des index qui les supportent. Ne supposez pas qu’un index a été créé ou conservé automatiquement sans examiner le résultat réel.
Créer une clé composée par client, horaire et identifiant
Le besoin de cet exemple est de retrouver les commandes d’un client dans une période, avec un ordre stable. Vérifiez que le nom idx_c39_customer_date n’est pas déjà utilisé, puis exécutez le bloc suivant. Pour comparer les plans avant et après, vous pouvez lancer son EXPLAIN et son SELECT avant le CREATE INDEX, puis les relancer après, sans changer leurs conditions.
CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SELECT order_id FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SHOW INDEX FROM c39_orders;
Le SELECT renvoie 101, puis 102. L’égalité customer_id=1 fixe le client, ordered_at délimite janvier, et order_id départage des horaires identiques. La borne du 1er février est exclue : l’intervalle couvre janvier sans devoir inventer une heure de fin de mois. Aucun filtre de statut n’est ajouté ici ; il s’agit des commandes qui satisfont ces conditions, pas d’un rapport limité implicitement aux paiements.
EXPLAIN décrit un plan estimé, pas une mesure du temps réellement gagné. Relevez notamment le type d’accès, possible_keys, la clé choisie dans key et les indications de Extra. Avec seulement quatre commandes, un parcours complet peut rester un choix raisonnable de l’optimiseur. Ne forcez pas un résultat de plan attendu pour prétendre que l’index est plus rapide.
La référence CREATE INDEX précise la syntaxe et les options. C’est une opération de structure : ne comptez pas sur un ROLLBACK ordinaire pour annuler sa création. Sur un système réel, prévoyez également les dépendances et les attentes possibles pendant l’opération.
Interpréter les colonnes de tête, les plages et la couverture
Un index composé organise ses clés selon l’ordre déclaré. Commencer par customer_id permet de regrouper les entrées du même client ; à l’intérieur de ce groupe, ordered_at permet de rechercher une période. Une requête portant seulement sur la date ne doit pas être supposée profiter du même accès efficace par plage dans une clé commençant par le client.
Cette règle pratique ne signifie pas qu’aucune lecture d’index n’est jamais possible sans condition sur la première colonne. Le moteur peut envisager d’autres parcours, dont un parcours d’index plus large. Comparez le chemin effectivement choisi au besoin de la requête au lieu de transformer une règle d’ordre des colonnes en interdiction absolue.
Les plages, le sens du tri, les préfixes de chaînes et les colonnes sélectionnées changent aussi l’utilisation possible d’une clé. Un index est dit couvrant pour une requête lorsque les informations nécessaires peuvent être obtenues dans l’index, ce qui peut éviter certains accès aux lignes de la table. La requête EXPLAIN ci-dessus demande aussi status, absent de la liste explicite de cette clé : ne la déclarez pas couvrante par simple association avec l’autre SELECT qui ne demande que order_id.
Dans InnoDB, les index secondaires contiennent aussi des informations de clé primaire. Relisez la structure et le plan avant d’ajouter des colonnes uniquement pour la couverture. Inclure systématiquement toutes les colonnes agrandit les clés et leur coût de maintenance.
Vérifier les recouvrements et les contraintes avant une suppression
Deux noms peuvent masquer les mêmes colonnes, ou des séquences dont les premières colonnes se recouvrent. Comparez l’ordre, l’unicité et les longueurs de préfixe, puis relancez SHOW INDEX après l’ajout. Observez aussi comment le support de la clé étrangère sur customer_id est représenté dans la nouvelle définition.
Supprimer le seul index permettant de conserver une clé étrangère peut être refusé. Identifiez la contrainte réelle et préparez si nécessaire un index de remplacement compatible avant de supprimer un support. Ne désactivez pas les contrôles d’intégrité pour contourner ce refus. Vérifiez également les autres requêtes qui utilisaient l’ancienne clé.
Une clé primaire ou UNIQUE impose une règle de données, au-delà d’un avantage de recherche. Le fait qu’une requête ne la choisisse pas n’autorise pas à supprimer la règle métier qu’elle représente. Le guide PRIMARY KEY et UNIQUE distingue ces rôles.
Comparer les mêmes résultats avant de mesurer les coûts
L’ajout de l’index doit conserver les lignes attendues et le sens du tri. Rejouez la même requête et comparez ses résultats, puis examinez les plans sur des données représentatives. Une mesure utile tient compte des lectures réelles, de la latence et du coût des écritures, en conservant les conditions et la méthode de mesure.
Un second passage plus rapide peut simplement bénéficier d’un cache déjà rempli. Ne lui attribuez pas automatiquement tout le gain de l’index. Ce petit jeu de données sert à comprendre la structure et la préservation des résultats ; il ne permet pas de conclure à une amélioration chiffrée pour une application.
Avant de recommencer, contrôlez la base, les définitions et les données réelles, puis sauvegardez ce qui doit être conservé. Supprimez seulement les objets jetables de cet exercice, les enfants avant les tables référencées. DROP TABLE supprime structure et données et n’est pas annulé par un ROLLBACK ordinaire ; séparez ce nettoyage des essais de modification réversible.
DROP TABLE shopcourse.c39_items;
DROP TABLE shopcourse.c39_orders;
DROP TABLE shopcourse.c39_products;
DROP TABLE shopcourse.c39_customers;




