Choisir un index adapté

Une recherche peut rester lente même si sa requête est préparée et que son plan a été examiné. Si la base doit parcourir beaucoup de lignes pour trouver quelques contacts, un index adapté au filtre peut réduire ce travail. Dans cette leçon, vous partez de la condition SQL effectivement exécutée, puis vous vérifiez si l’index choisi aide réellement.

La leçon précédente vous a appris à lire un plan EXPLAIN et à repérer un parcours de table ; utilisez cette méthode comme point de comparaison. Un index est une structure organisée par les valeurs d’une ou plusieurs colonnes : le moteur peut s’en servir pour localiser des lignes sans examiner toute la table. Il n’est pas automatiquement utile à chaque requête, et son intérêt dépend de la forme précise du filtre ainsi que des données.

Partir du filtre réellement exécuté

Ne choisissez pas une colonne au seul motif qu’elle paraît importante, ni un index sur chaque champ par précaution. Relevez la requête de recherche utilisée par le dépôt et regardez ses conditions : la colonne filtrée, l’opérateur et la valeur recherchée déterminent si un index peut servir. Pour illustrer la démarche, supposons que la recherche fréquente porte sur le début du nom, avec une condition équivalente à nom LIKE 'mar%', dans une table contacts comportant notamment une colonne nom.

Dans ce cas, un index simple sur nom est un candidat à vérifier. Un index B-tree peut généralement aider une égalité ou une recherche par préfixe, car les valeurs commencent par des caractères connus. En revanche, une condition telle que nom LIKE '%mar%' commence par un caractère générique : le moteur ne peut généralement pas exploiter de la même manière l’ordre de l’index pour retrouver le début d’une plage de valeurs. Ne remplacez donc pas une recherche de sous-chaîne par une recherche de préfixe sans vérifier que cela correspond au besoin fonctionnel.

Créer l’index candidat

Pour une base compatible avec la syntaxe MySQL présentée ici, exécutez cette instruction une seule fois sur la base de développement ou de test. Le nom explicite idx_contacts_nom permet d’identifier la colonne concernée lors de la maintenance. Si votre schéma ou votre moteur de base de données diffère, adaptez la syntaxe et confirmez le résultat dans l’environnement correspondant avant de déployer.

CREATE INDEX idx_contacts_nom
    ON contacts (nom);

L’index est une structure supplémentaire, pas une modification des règles métier ni des valeurs des contacts. Il doit être créé dans le schéma déployé par le projet, de façon à ce que les environnements de test et de production disposent de la même structure. Si l’instruction échoue parce que l’index existe déjà, vérifiez l’état du schéma plutôt que de multiplier les index portant des noms différents sur la même colonne.

Vérifier l’usage et le coût

Relancez EXPLAIN avec la requête et une valeur représentative, avant puis après la création de l’index. Vérifiez que le plan peut choisir idx_contacts_nom et comparez le nombre de lignes estimé ou examinées avec celui du scénario initial. La présence du nom d’un index dans un plan n’est pas, à elle seule, une preuve de gain : le jeu de données, la distribution des noms et la sélectivité du filtre influencent le choix du moteur.

EXPLAIN
SELECT id, nom, courriel, telephone
FROM contacts
WHERE nom LIKE 'mar%';

Le plan indique les stratégies envisagées par le moteur. Selon les données et la base utilisée, il peut sélectionner idx_contacts_nom ; vérifiez aussi le nombre de lignes estimé.

Pour évaluer le temps, reprenez exactement le scénario contrôlé et la méthode de mesure de la leçon sur la lenteur. Effectuez plusieurs passages dans des conditions comparables et confrontez les mesures au plan, plutôt que de tirer une conclusion d’une seule exécution. Si le moteur ne choisit pas l’index, recherchez d’abord une explication dans la requête, les statistiques et la distribution des données ; ne forcez pas l’index sans preuve que cela améliore le cas réel.

Un index accélère potentiellement certaines lectures, mais il a un coût : à chaque insertion, modification de la valeur indexée ou suppression, le moteur doit aussi maintenir cette structure. Il consomme également de l’espace. Ajoutez donc un index pour un filtre fréquent et mesuré, puis vérifiez qu’il apporte un bénéfice sans pénaliser excessivement les écritures. La pagination et la réduction des données traitées par requête seront abordées dans la leçon suivante.

À retenir

• Choisissez un index à partir de la colonne et de l’opérateur présents dans la requête réelle.
• Un index B-tree peut convenir à une égalité ou à une recherche par préfixe ; un motif commençant par % ne bénéficie généralement pas de cet accès.
• Comparez les plans EXPLAIN et des mesures répétées avant et après l’ajout.
• Prenez en compte l’espace utilisé et le travail supplémentaire sur les écritures.