Rendre les requêtes SELECT … WHERE plus rapides

L’utilisation des indexes reste de loin la meilleure technique pour optimiser les performances des requêtes de type SELECT…. WHERE. Notons par ailleurs que MySQL dispose de plusieurs utilitaires qui permettent de suivre de plus près l’exécution de ces requêtes et de comprendre les traitements effectués en arrière plan. Parmi ces utilitaires nous pouvons citer :

• EXPLAIN : à l'aide d’EXPLAIN, il est possible d’identifier les indexes à ajouter pour accélérer les commandes SELECT (pour plus de détails au sujet d’EXPLAIN visiter le site de MySQL à l’adresse : http://dev.mysql.com/doc/mysql/fr/explain.html  )

• ANALYZE TABLE : cet utilitaire analyse et stocke la clé de distribution des tables MyISAM et BDB. MySQL utilise les clés de distribution pour décider dans quel ordre les tables doivent être rassemblées lors des jointures qui ne s'effectuent pas sur une constante (pour plus de détails sur ANALYSE TABLE, visiter le site de MySQL à l’adresse : http://dev.mysql.com/doc/mysql/fr/analyze-table.html  ).

Performance des requêtes SELECT et ordre des jointures

Lorsqu’une requête porte sur plusieurs tables, l’ordre dans lequel ces tables sont parcourues est un facteur qui affecte directement les performances. Par exemple dans la requête infra, il faut parcourir la table customer avant la table order et la table region (le but final étant de réduire au strict minimum le nombre d’enregistrements parcourus).

Pour MySQL la détermination de l’ordre dans lequel les tables doivent être scannées n’est pas aussi simple parce qu’il doit, au préalable, identifier toutes les combinaisons possibles. Cette identification est très pénalisante pour les ressources et consomme plus de temps que l’exécution de la requête elle-même. En fait l’identification des combinaisons possibles peut prendre jusqu'à 29 fois plus de temps qu’il faut pour exécuter la requête .

SELECT customer.name, order.date_placed, region.name
FROM customer, order, region
WHERE order.customer_id = customer.id
AND customer.region_id = region.id
AND customer.name = 'Rajaz Camel'

Cela dit, pour réduire le délai d’exécution des requêtes portant sur plusieurs tables, on peut procéder de la manière suivante:

* Utiliser l’utilitaire EXPLAIN pour déterminer l’ordre dans lequel MySQL scanne les tables.

* Si cet ordre n’est pas efficient, le forcer à scanner les tables dans un ordre précis en utilisant l’instruction STRAIGHT_JOIN (par exemple si on exécute la requête SELECT * FROM table1 STRAIGHT_JOIN table2 WHERE ... on force MySQL à scanner la table1 avant la table 2).

Query cache

Le Query cache sauvegarde le texte d’une requête avec le résultat qui a été renvoyé au client. Si une requête identique est appelée par la suite, MySQL retournera le résultat à partir du cache au lieu d’exécuter la requête à nouveau. Il est important de noter que le cache de requêtes ne retourne pas des données expirées parce qu’il est systématiquement effacé à chaque fois que les données mises en cache sont modifiées.

Comme nous l’avons vu dans la partie 4 le cache est extrêmement utile lorsque le contenu des tables change peu et qu’il y a une série de requêtes identiques à exécuter.
Les gains en performance peuvent atteindre 238% pour effectuer des recherches sur une colonne . Pour que MySQL aille chercher les résultats d’une requête dans le cache, il faudrait que le texte de celle-ci soit parfaitement identique à celui d’une requête déjà traitée. Aucune différence, aussi mineure soit elle, n’est tolérée.

Exemple : pour MySQL les deux requêtes suivantes ne sont pas identiques lorsqu’il s’agit de chercher la réponse dans le cache.


SELECT * FROM tbl_name
Select * from tbl_name

Avec les requêtes SELECT le développeur peut préciser s’il souhaite que le résultat soit mis en cache. Ainsi :

* Une requête comme SELECT SQL_CACHE id, name FROM customer met en cache le résultat.
* Par contre la requête SELECT SQL_NO_CACHE id, name FROM customer ne met pas le résultat en cache.

NB: pour activer le Query Cache il faut configurer le fichier my.cnf en attribuant la valeur 1 à la propriété query_cache_type.