Aller au contenu Aller au menu
Vous êtes ici : Développement > MySQL > 3) Optimiser les requêtes SQL > c) Mettre à profit une bonne indexation
Langue : fr

c) Mettre à profit une bonne indexation

Le fonctionnement interne des index est à prendre en compte lors de l'écriture des requêtes pour tirer profit de l'indexation. Certaines constructions empêchent notamment l'utilisation des index.

Les requêtes du type criteria LIKE '%text%' sont à proscrire : le parcours de l'arbre d'index correspond à la lecture du mot cherché en partant de sa racine (première lettre à gauche), chercher par milieu de mot désactive donc l'utilisation d'un éventuel index pour ce critère. Notez qu'une requête de type criteria LIKE 'Dum%' ne les désactive pas en revanche car l'optimiseur de requêtes la transforme en criteria > 'Dul' AND criteria < 'Dun'.

L'utilisation du mot-clé OR est à bannir. Si vous cherchez sur deux critères différents (WHERE id_dossier=1 OR id_client=2), l'efficacité de l'indexation est fortement amoindrie, puisqu'un seul index peut être utilisé, et qu'ici seul l'un des deux pourra être utilisé (la recherche sur l'autre critère se faisant par un parcours de la table entière). On peut d'ailleurs se poser la question du sens d'une requête de ce type (deux critères totalement différents). Si vous utilisez OR pour chercher sur un seul critère (WHERE id=1 OR id=2), remplacez-le par le mot-clé IN (WHERE id IN( 1, 2 )).

Le wildcard "all" est à exclure (requête de type SELECT *). En toutes circonstances, ne sélectionnez que les champs dont vous avez réellement besoin. Même si aujourd'hui, vous n'avez que des champs de type INT dans votre table, rien ne prouve que vous ne créerez pas à l'avenir un champ de commentaire de type TEXT de 2 Mb en moyenne par enregistrement et indexé en full text, qui plombera donc l'ensemble des requêtes déjà écrites avec des SELECT *. En outre, si le seul champ demandé est celui dont l'index est utilisé pour la requête, MySQL parcourt uniquement la table d'index pour retourner les résultats, et non la table de données, ce qui est beaucoup plus rapide.

Lorsque c'est possible, il faut utiliser des constantes dans les critères de sélection de la clause WHERE afin de ne pas désactiver l'indexation. Une clause dont la vérification oblige à effectuer un calcul sur les données contraint en effet MySQL à effectuer ce calcul pour chacune des lignes concernées, avant de déterminer les enregistrements correspondants à la sélection, ce qui signifie qu'un index ne peut pas être utilisé. Prenons l'exemple d'une requête destinée à sélectionner tous les enregistrements créés en 2005. Traduite naturellement en SQL, cette clause s'écrirait ainsi : WHERE YEAR( date_creation ) = 2005. Cette écriture n'est cependant pas efficace du tout, car elle oblige MySQL à réaliser l'opération d'extraction de l'année de la date de création pour l'ensemble des enregistrements présents en base, avant de retourner les enregistrements correspondants. A contrario, la clause réécrite de cette façon : WHERE date_creation > 2004-12-31 AND date_creation < 2006-01-01 supprime l'opération sur chaque ligne et permet à MySQL d'utiliser l'index posé sur date_creation.

De la même façon, récupérer les dossiers créés depuis moins de 7 jours peut être créé de deux façons différentes :

  • la mauvaise : SELECT * FROM dossiers WHERE DATE_SUB( date_creation, INTERVAL 7 DAY ) > NOW();
  • la bonne : SELECT * FROM dossiers WHERE date_creation > DATE_SUB( NOW(), INTERVAL 7 DAY );

Dans la deuxième version, MySQL calcule la partie droite de l'expression une seule fois et la transforme en constante, avant d'exécuter la requête. Le champ est donc comparé à une constante, sélectionnant uniquement les enregistrements concernés en base, au lieu qu'un calcul soit exécuté pour chaque enregistrement présent en base.

  1. MySQL
    1. 1) Bien concevoir la base de données
      1. a) Choix des tables – Normalisation des données
      2. b) Choix et typage des champs
    2. 2) Indexer les données
      1. a) L'index : un marque-page dans la base de données
      2. b) Une forte cardinalité pour un bon index
      3. c) Une utilisation récurrente pour un bon index
      4. d) Gare aux contre-optimisations
    3. 3) Optimiser les requêtes SQL
      1. a) Utiliser la richesse du langage SQL
      2. b) Optimiser les jointures
      3. c) Mettre à profit une bonne indexation
    4. 4) Corriger les requêtes lentes
      1. a) L'optimisation de requête par EXPLAIN
      2. b) L'analyse post-mortem
    5. 5) Le serveur MySQL – maintenance et backup
      1. a) Minimiser l'impact des opérations de maintenance
      2. b) Sauvegarde et restauration des bases de données
      3. c) Maintenance des tables