Nous avons vu comment repérer les scripts les plus lents. Voici des solutions pour accélérer ceux utilisant les bases de données.
Le plus gros potentiel d'optimisation se cache certainement dans les accès aux données.
Les moteurs SQL sont extrêmement puissants et rapides, mais l'optimisation nécessite malgré tout pas mal de recherches et de travail. L'optimisation passe par une structure de tables cohérentes et par une analyse de chacune des requêtes SQL.
Les grands principes exposés ici sont valables pour tous les systèmes de bases de données. Nous allons nous intéresser pourtant au système le plus utilisé actuellement en développement web : MySQL.
Si vous ne connaissez pas encore MySQL, lisez absolument l'introduction aux bases de données.

Les index
Les index sont des clés d'accès aux données d'une table. Des clés judicieusement choisies permettent des gains de performance incroyables. A l'inverse, un index absent ou mal créé génère très souvent des problèmes de performance.
Ne vous inquiétez pas, le choix des index est logique et grâce à la commande EXPLAIN que nous verrons plus loin, il est très facile de vérifier la pertinence d'un index.

En contrepartie de l'accélération de l'accès aux données, les index occupent une place importante sur le disque. De plus, à chaque insertion ou modification de la table, l'index doit également être mis à jour, entraînant un temps de traitement allongé. Mais généralement, ces inconvénients sont tout à fait acceptables par rapport aux bénéfices obtenus.

Il existe 2 types d'index, appelés clé (KEY) dans MySQL.
La clé primaire est l'identifiant unique qui permet de trouver de manière fiable une ligne dans la table.
Un index ou clé simple permet au moteur SQL de regrouper un ensemble de lignes d'une table grâce à des valeurs communes.

Prenons un exemple classique pour un site web. La table membre contient les informations des membres du site autorisés à s'exprimer. La table participation contient les participations des membres.
CREATE TABLE membre (
  login varchar(20) NOT NULL,
  motdepasse varchar(20),
  dateNaissance date,
  PRIMARY KEY login (login)
);

CREATE TABLE participation (
  id int NOT NULL auto_increment,
  auteur varchar(20) NOT NULL,
  dateParticipation datetime,
  titre varchar(100),
  corps text,
  PRIMARY KEY id (id),
  KEY auteur (auteur)
);

Voici quelques règles pour choisir les index à créer sur une table :
- Un index est efficace si il est positionné sur une colonne utilisée dans une recherche ou un regroupement.
Par exemple : si vous recherchez les messages postés par un membre, l'index sur l'auteur du message est pertinent.
- Un index est d'autant plus efficace qu'il regroupe un ensemble de lignes d'une table sur une valeur commune.

La commande EXPLAIN
Cette commande permet d'expliquer comment le moteur SQL parvient au résultat de la requête de type SELECT.
Le résultat d'un EXPLAIN peut paraître obscur au premier abord. Il est pourtant d'une efficacité redoutable. Associé à notre petit script d'affichage du temps d'exécution, EXPLAIN va vous faire croire aux miracles :)
Dans PhpMyAdmin, vous écrivez votre requête, puis vous cliquez sur "Expliquer SQL". Pour la requête
SELECT * FROM membre WHERE login="webmaster"
vous obtiendrez alors un résultat du type :


Cela signifie que sur la table membre, il y a 1 seul index possible (la clé primaire), que le moteur SQL a choisi de l'utiliser et que sur 20 lignes de tables, il y a 1 seule ligne sélectionnée.
Dans ce cas, un index est bien utilisé, tout va bien :)

Voyons le cas de la jointure entre la table des membres et la table des participations.
Pour trouver les participations d'un membre :
SELECT *
FROM membre, participation
WHERE membre.login = participation.auteur
AND membre.login = "webmaster";
La commande EXPLAIN donne un résultat du genre :


Il y a maintenant 2 lignes. Pour la table membre, l'index de la clé primaire est bien utilisé. Pour la table participation, l'index auteur est utilisé, permettant ainsi de réduire la sélection aux 8 participations du membre.
Au final, la requête retourne 8 lignes.

Repérez bien les requêtes les plus gourmandes en CPU et les plus fréquemment utilisées et ajoutez les index nécessaires.
Vérifiez avec la commande EXPLAIN que le moteur utilise bien les index. Vérifiez également avec notre fonction de mesure du temps que l'optimisation est bien mesurable

Optimize et Analyze
Ces commandes d'administration regénèrent et rafraichissent les tables et les index.
Ces opérations sont nécessaires pour les tables qui évoluent avec des insertions, suppression ou modification de lignes.
Je lance ces opérations sur les tables principales du site, par une commande programmée, toutes les nuits, lorsque la charge sur le serveur est la plus faible.
Voici la syntaxe :
OPTIMIZE TABLE nom_table qui défragmente les données de la table.
ANALYZE TABLE nom_table qui optimise les futures requêtes sur la table.


Les tables mémoire HEAP
Les tables mémoire ont été conçues pour accélérer la vitesse de traitement. Toutes les données de ces tables sont stockées en ram, les temps d'accès sont quasiment nuls par rapport aux temps d'accès disque.
L'inconvénient de ces tables, en dehors de l'occupation mémoire, est la volatilité des données. Si le serveur est redémarré, toutes les données sont perdues. En revanche la structure d'une table HEAP est conservée : il ne faut pas refaire le CREATE TABLE à chaque démarrage du serveur.
Pour créer une table mémoire, ajoutez simplement le type HEAP au CREATE. Par exemple :
CREATE TABLE table_memoire (
login varchar(20),
idsession varchar(20)
) TYPE = HEAP;


Connexion et fermeture
Le nombre maximal de connexions simultanées est une des nombreuses limitations des hébergeurs. Pour éviter d'avoir le message d'erreur "Maximum of connexions", deux possibilités s'offrent au développeur. Soit limiter le nombre de connexions, ce qui paraît difficile à réaliser. Soit réduire le temps d'ouverture de la connexion. En réduisant ce temps, on réduit également le nombre de connexions simultanées.
Voici la structure classique d'un script PHP :
Include de fichiers de fonctions
Ouverture de la connexion SQL
Requête SQL 1
print(resultat1)
Requête SQL 2
print(resultat2)
traitement PHP
print(resultat3)
Fin du script

Dans la plupart des scripts, la fin de la connexion est implicite car déclenchée par la fin du script PHP, sans être directement codée.
Cela implique que la connexion SQL est aussi longue que l'exécution du script, temps de chargement des pages inclus !

Il est donc vivement recommandé de réaliser tous les traitements SQL puis de fermer la connexion de manière explicite avec mysql_close() avant d'envoyer le résultat par les instructions print.
Cela réduit le temps de connexion à la durée réelle des traitements en excluant les temps de chargement des pages par les visiteurs.
La structure optimale d'un script est donc de ce type :
Include de fichiers de fonctions
Ouverture de la connexion SQL
Requête SQL 1
Requête SQL 2
Fermeture de la connexion SQL
traitement PHP
print(resultat1)
print(resultat2)
print(resultat3)
Fin du script


Quelques astuces
Pour éviter de manipuler un grand nombre de données, la plupart du temps inutilement, il est préférable de remplacer tous les SELECT * FROM par le nom explicite des colonnes réellement nécessaires.
Par exemple pour récupérer les titres des articles publiés, il est inutile de retourner tout le corps des articles. Une requête SELECT titre FROM table_articles suffit.
Pour optimiser une requête, oubliez la facilité d'écriture du SELECT * et soyez rigoureux.

Toujours pour éviter de manipuler trop de données, pensez à choisir les types de colonnes les plus compacts possibles. Inutile de déclarer un entier sur 4 octets si seulement 3 valeurs sont possibles.

D'une manière générale, le moteur SQL est bien plus performant pour la manipulation de données qu'un script PHP. Utilisez au maximum les possibilités de SQL. Voici quelques fonctionnalités très pratiques de MySQL.
SELECT FROM ORDER BY RAND() trie une table par ordre aléatoire. Très pratique pour effectuer un tirage aléatoire sans utiliser PHP.
SELECT FROM LIMIT 10,5 limite le résultat aux lignes comprises entre 10 et 15.
Lisez la documentation officielle, vous y trouverez sûrement la fonction qui vous manque.


Avec ces infos, vous avez déjà de quoi largement améliorer les temps de traitement de vos requêtes :)

A partir de la version 4, MySQL nous offre une gestion de cache d'une grande efficacité et très simple