Optimisation SQL

L’optimisation des performances sous SQL peut passer par plusieurs étapes, en commençant par l’installation de MySQL (ou tout autre système), en prenant en compte la structure de données et également en optimisant chacune des requêtes SQL. Ce petit guide liste certaines bonnes pratiques, ainsi que quelques astuces, permettant d’optimiser les performances. La richesse de SQL étant très important, cette liste n’est bien entendu par exhaustive.

Sommaire

Optimiser l’installation et la configuration

  • Serveur avec suffisamment de RAM
  • Analyser et monitorer les performances du serveur afin de surveiller l’usage actuel de celui-ci et préconiser des adaptations (exemple : augmenter la RAM, allouer plus d’espace disque …)
  • Installer le Système de Gestion de Base de Données SGBD sur le même serveur que l’application (sauf cas particulier). Lorsque le SGBD est sur le même équipement informatique, bien utiliser les sockets UNIX
  • Utiliser des connexions persistantes pour éviter trop d’ouvertures et fermetures de connexion (ce qui est très coûteux en performance)
  • Toujours bien s’assurer que les connexions se ferment correctement
  • Activer le log des “slow queries” sur le serveur puis vérifier ce log régulièrement. Une analyse peut être effectuée pour chacune des lignes ajoutées dans ce log, par exemple avec EXPLAIN.
  • Utiliser le système de mise en cache disponible par le SGBD. Sinon, utiliser un système de mise en cache externe (exemple : memcached).

Optimiser la structure

  • Utilisation d’une clé primaire (PRIMARY KEY) de type numérique avec une auto-incrémentation (AUTO_INCREMENT)
  • Stocker dans une nouvelle table les données textuelles qui sont trop répétées, et les lier avec une jointure.
    Exemple : une table contenant une colonne “Catégorie” (type VARCHAR) en écrivant en texte la valeur, serait plutôt à remplacer par un type numérique qui sera une clé étrangère (FOREIGN KEY) pour lire la valeur via une nouvelle table.
  • Indexer les données qui sont utilisées dans WHERE, JOIN, ORDER BY et GROUP BY
  • Eviter les index pour les colonnes de type BLOB ou les champs de texte libre
  • Le moteur de données MyISAM est à privilégié à InnoDB pour les requêtes SELECT (voir l’article “MyISAM ou InnoDB ?“)
  • Utiliser un index de type UNIQUE pour les colonnes qui doivent contenir des données uniques et que vous souhaitez être sûr qu’il n’y aura pas de doublon superflu.

Optimiser les requêtes SQL

  • Filtrer les données directement via le filtre WHERE et/ou LIMIT, en évitant que cela ne soit fait par l’application.
  • Eviter d’utiliser des fonctions dans les clauses de recherche, telle que WHERE.
  • Eviter les lectures via “SELECT *” en privilégiant plutôt de lister uniquement les colonnes qui seront exploitées.
  • Eviter d’utiliser le wildcard “%” au début d’une recherche LIKE.
    Exemple : une requête “SELECT * FROM table WHERE title LIKE ‘%abc’ est consommateur en performance
  • Utiliser des requêtes préparées ou procédures stockées (stored procedure) pour mettre en cache certaines requêtes ou créer un script directement du côté du système de gestion de base de données.

Optimiser l’usage des requêtes SQL dans l’application

  • Compter le nombre de requêtes SQL utilisé au sein d’une page web. Cela permet d’analyser les pages qui pourraient contenir trop d’appels de requêtes SQL, notamment :
    • Une requête SQL similaire qui est appelée à plusieurs reprises
      Exemple : la requête “SELECT email FROM utilisateur WHERE id = 456”, puis la requête “SELECT date_inscription FROM utilisateur WHERE id = 456” pourrait être extrait en une seule fois.
    • Eviter d’appeler des requêtes SQL dans une boucle.
      Exemple : un requête “SELECT * FROM commentaire WHERE article_id = 123” appelée plusieurs dizaines de fois dans une boucle serait plutôt à remplacer par une requête “SELECT * FROM commentaire WHERE article_id IN (123, 124, 125 …)”.
  • Pour un système de pagination, privilégier l’usage de SQL_CALC_FOUND_ROWS, et éviter de faire 2 requêtes, celle contenant les résultats et l’autre pour compter le nombre de résultats total.
  • Eviter d’utiliser la requête “WHERE IN” si vous pouvez utiliser “WHERE EXISTS”.
  • Eviter les sous-requêtes si une jointure est possible.
  • Eviter de compter une colonne (cf. “SELECT COUNT(colonne) FROM table) lorsqu’il faut compter le nombre d’enregistrement (cf. “SELECT COUNT(*) FROM table).

Optimisation sur le long terme

  • Purger les vieilles données non utile permet d’alléger le poids d’une base de données. Idéal pour accélérer la vitesse d’exécution, notamment pour une table contenant beaucoup d’enregistrement
  • Une requête OPTIMIZE permet de réorganiser le stockage physique des données et améliore l’efficacité lors de l’accès aux données
  • Rappel : consulter les informations recueillies par le fichier “slow queries” pour continuer à détecter si des requêtes peuvent être améliorées. Une requête peut fonctionner très rapidement lorsqu’il n’y a que 1.000 enregistrements, mais peut causer de sérieux problèmes de performance lorsqu’il a plusieurs centaines de milliers de lignes.

Aller plus loin

Cette liste n’est bien entendu pas exhaustive. Il convient de toujours essayer d’en apprendre plus sur le langage SQL lorsque vous constatez des ralentissements de certaines requêtes. Des sites tels que Stackoverflow permettent par exemple de découvrir des retours d’expérience de professionnels expérimentés, ou des mises en avant de test de performance entre 2 solutions.

Partager