Accélérer les requêtes SQL en créant des indexes
Alors qu'il semble automatique aux développeurs de penser à l'optimisation du stockage des données dans une base et au choix le plus adapté des types de données,
la création d'indexes pertinents passe souvent à la trappe, peut-être parce que leur intérêt prend forme sur des larges tables et est donc indécelable au moment du développement
et des premiers tests. Certes, les indexes prennent un peu de place, mais de manière aussi importante (voire plus, avec l'augmentation constante des espaces disque) que la taille même
de la base, ses performances sont capitales.
Un index SQL est similaire à un ensemble de marqueurs ou pointeurs, et comparable au classement alphabétique des contacts d'un agenda : si je cherche dans mon agenda le nom d'une personne
commençant par "T", je saute immédiatement à l'intercalaire "T" sans parcourir la liste complète de mes contacts.
Les indexes SQL permettent ainsi de gagner un temps précieux en recherche et en tri sur des colonnes ou groupes de colonnes de types numériques ou chaînes de caractères,
spécialement sur les requêtes comportant des jointures (JOIN).
Si les clés primaires sont automatiquement indexées, le choix des indexes sur d'autres colonnes revient au concepteur de la BDD.
Il convient d'en créer sur toutes les colonnes qu'une application peut vouloir trier (notes données à des produits, titres si l'on veut pouvoir afficher des résultats par ordre alphabétique,
dates pour affichages chronologiques, etc.), et particulièrement sur les clés étrangères (en vue des jointures).
On peut noter que les indexes, en accélérant la vitesse de lecture (SELECT) d'une table augmentent sa vitesse d'édition (INSERT, UPDATE), mais généralement de manière peu perceptible et
portée sur le back-office. Dans le doute des besoins futurs d'une application, et pour un site majoritairement mis à jour par back-office,
ne pas hésiter donc à créer des indexes susceptibles d'être utilisés plus tard.
Exemple d'utilisation, performances comparées :
On crée 2 tables "test1" et "test2" contenant chacune 2 champs "annee" (type INT) et "titre" (type TEXT). La seule différence entre ces 2 tables est que les champs de "test2" sont indexés.
On insère dans chacune 100.000 enregistrements (le champ "titre" est alimenté par une chaîne aléatoire md5) :
SELECT * FROM test1 WHERE annee=1980;
=> 0.00341 s
SELECT * FROM test2 WHERE annee=1980;
=> 0.0353 s
SELECT * FROM test1 WHERE titre='29bea3a3219aa93b14e103aa2058156b';
=> 0.000467 s
SELECT * FROM test2 WHERE titre='29bea3a3219aa93b14e103aa2058156b';
=> 0.0390 s
On obtient sur cet exemple simple des temps de calculs différents d'un facteur ~10 pour une recherche d'entier, et d'un facteur ~80 pour une recherche de texte!
Pensons aux différences que cela fera sur une jointure de plusieurs tables...
Accepter les redondances
Les redondances dans une base de données sont généralement assimilées à des erreurs de conception ou des problèmes de modélisation. Elles peuvent parfois pourtant apporter, au prix d'un script de
synchronisation maintenant la cohérence des données, une simplification des requêtes (facilitant le développement) et une réelle optimisation des accès en lecture (SELECT), principalement
en supprimant la nécessité de certaines jointures (JOIN) et/ou unions (UNION).
Exemple d'utilisation :
Prenons l'exemple d'un site de vidéos, dans lequel
chaque vidéo peut être attribuée soit à un pays, soit à un produit, soit à un producteur de vidéos.
- Les pays, produits et producteurs étant des structures de données très différents les unes des autres, et étant liés à des tables distinctes décrivant leurs attributs,
nous ne souhaitons pas les regrouper dans une seule table, mais bien créer les 3 tables "pays", "produit" et "producteur".
- De plus chaque producteur est décrit comme appartenant à un pays, on trouvera donc dans sa table une colonne "pays_id".
- Et chaque produit est décrit comme appartenant à un producteur, on trouvera donc dans sa table une colonne "producteur_id".
- Dans la table "video" nous avons un champ enum('pays', 'produit', 'producteur') et un champ "parent_id" permettant de lier chaque vidéo à son possesseur.
- Nous voulons, dans le site, en de nombreux endroits, afficher les vidéos par pays, par produit ou par producteur, étant entendu que les vidéos d'un pays doivent comporter
également les vidéos liées aux produits et producteurs de ce pays.
En l'état de cette structure de base, afin d'afficher ou de compter les vidéos d'un pays, on doit donc à chaque fois écrire une requête du type :
SELECT * FROM video WHERE type='pays' AND parent_id=9
UNION
SELECT video.* FROM video JOIN producteur ON video.parent_id=producteur.id WHERE video.type='producteur' AND producteur.pays_id=9
UNION
SELECT video.* FROM video JOIN produit ON video.parent_id=produit.id JOIN producteur ON producteur.id=produit.producteur_id WHERE video.type='produit' AND producteur.pays_id=9
Cette requête pouvant comporter des nuances (effectuer un COUNT au lieu d'une sélection de champs, éventuels filtres et tris, etc.) est visiblement désagréable à composer, recopier et adapter,
mais aussi relativement lourde à exécuter :
Pourquoi ne pas introduire dans la table vidéo un champ "pays_id"?
Cette requête deviendrait ainsi :
SELECT * FROM video WHERE pays_id=9
Inconvénients :
- les puristes diront que c'est tout bonnement une mauvaise pratique / «une base redondante est une base mal conçue».
- la nécessité de créer un script qui met à jour le champ "pays_id" de la table "video" lorsque modifié dans la table "producteur".
Avantages :
- simplicité du développement et de la maintenance / re-lecture du code.
- un appel à la base ultra-léger et qui sera exécuté quotidiennement des milliers de fois, à chaque page calculée par chaque visiteur, autant d'économies de ressources.
Les listes paginées
Présentes dans presque tous les sites web, avec ou sans possibilité supplémentaire de filtrer ou trier les éléments, les listes paginées dans toutes leurs formes sont souvent le rebut du développeur.
Error-prone et polymorphe, une liste paginée est de plus facilement fuite de ressources serveur sur les sites présentant beaucoup de contenu. Elle est l'illustration idéale des remarques qui
vont suivre, ces remarques s'appliquant néanmoins de manière générique à tout type de conception web comportant une base de données.
Utiliser la clause COUNT
Une raison principale de ce gaspillage de ressources est dûe au besoin de connaître le nombre total d'éléments existants pour la requête courante, indispensable à l'affichage de la pagination
(boutons "précédent", "suivant", "première page", "dernière page", etc.). Et lorsque notre page propose en plus des filtres simples ou plus complexes, ce nombre total ne peut être mis en cache
si les combinaisons offertes à l'utilisateur sont nombreuses : on peut vouloir par exemple afficher les livres d'un revendeur et les filtrer par auteur, par année, par collection,
par thème, etc. avec toutes les combinaisons possibles de ces critères ; d'où la nécessité impérieuse d'obtenir ce nombre dynamiquement à l'aide d'une requête SQL quelle qu'elle soit.
Puisque l'on doit également, et c'est le but, extraire les champs à afficher dans notre page, l'idée la plus simple serait d'effectuer une requête complète puis de compter le nombre de résultats
obtenus (taille du tableau de résultats) et de n'afficher que les résultats 0 à 19, 20 à 39, etc. pour un exemple d'affichage à 20 résultats par page.
On se dit en plus qu'on n'effectue qu'une seule requête, ce qui est bien pratique.
Cette méthode est à proscrire complètement!
Cela donnerait pour cet exemple, en considérant que l'on veut également afficher les noms des auteurs et des thèmes des livres :
SELECT livre.titre, livre.annee, livre.image, auteur.titre AS auteur_titre, theme.titre AS theme_titre FROM livre JOIN auteur ON livre.auteur_id=auteur.id JOIN theme ON livre.theme_id=theme.id
WHERE livre.revendeur_id=5 ORDER BY livre.titre ASC
Imaginons que le revendeur d'id=5 aie 10.000 livres en librairie, cette méthode est catastrophique pour plusieurs raisons :
- on demande à MySQL d'extraire 10.000 références complètes, avec jointure sur 3 tables et tri des 10.000 éléments sur une colonne (ASC ou DESC), ce qui peut prendre plusieurs dixièmes de secondes ou secondes et occuper une connexion MySQL pendant autant de temps.
- les résultats obtenus, soit un tableau de 5x10.000 éléments, seront d'une manière ou d'une autre stockés dans un tableau PHP à faire peur, et pouvant probablement dans d'autres cas dépasser la mémoire maximum attribuée à chaque script.
Une meilleure pratique sera de procéder en 2 temps : une première requête effectuant un COUNT simple en tenant compte des filtres courants sélectionnés par l'utilisateur,
puis une seconde requête pour extraire uniquement les résultats à afficher sur la page, à l'aide des clauses OFFSET et LIMIT :
SELECT count(id) FROM livre WHERE annee=1985 AND collection_id=3
SELECT livre.titre, livre.annee, livre.image, auteur.titre AS auteur_titre, theme.titre AS theme_titre FROM livre JOIN auteur ON livre.auteur_id=auteur.id JOIN theme ON livre.theme_id=theme.id
WHERE livre.annee=1985 AND livre.collection_id=3 ORDER BY livre.titre ASC LIMIT 20 OFFSET 40
On n'effectue donc qu'un COUNT normalement très rapide si les tables sont correctement indéxées, puis l'extraction d'un tableau de taille 5x20 avec tri sur 20 valeurs,
soit quelques millisecondes ou centièmes de secondes au total pour MySQL.
Limiter l'extraction des champs, utiliser des sous-requêtes et la clause WHERE ... IN
Comme vu dans cet exemple, il est important de limiter l'extraction des champs à ceux nécessaires à l'affichage dans la liste. Puisque généralement une liste comportera
un sous-ensemble réduit d'attributs, éviter les "SELECT (*)" et préférer lister les champs uniquement indispensables ("SELECT livre.titre, livre.annee, livre.image").
Il est courant de rencontrer des cas plus complexes, où l'on a par exemple besoin de récupérer des données provenant de jointures "many-to-many". L'exemple le plus classique pour comprendre
cette relation est l'attribution de tags : chaque tags peut être attribué à plusieurs livres et chaque livre peut posséder plusieurs tags.
Quelle que soit la situation, il faut à la fois simplifier la requête principale (minimiser le nombre de jointures) et éviter d'avoir à effectuer une requête
supplémentaire par élément de la liste à afficher : si on affiche 100 éléments par page, au lieu d'effectuer 2 requêtes on en effectue 102..!
Une méthode répondant à ce besoin consiste à effectuer des sous-requêtes et utiliser des tableaux PHP. Pour afficher dans la liste de livres, pour chaque livre, la liste des tags
associés, on peut procéder ainsi :
$livreIDs = array(123,4,99,75,2654,201,6,645,770,40);
$liens = db::instance()->query('SELECT livre_id, tag_id FROM livre_tag
WHERE livre_id IN ('.implode(',',$livreIDs).')');
$livres_tags = array();
$tagIDs = array();
foreach($liens as $lien){
$livres_tags[$lien['livre_id']][] = $lien['tag_id'];
$tagIDs[] = $lien['tag_id'];
}
$tags = db::instance()->query('SELECT id, nom FROM tag
WHERE id IN ('.implode(',',$tagIDs).')');
$tagnoms = array();
foreach($tags as $tag){
$tagnoms[$tag['id']] = $tag['nom'];
}
foreach($livres_tags[$livreID] as $tagID){
echo $tagnoms[$tagID].'<br/>';
}
Cette technique peut paraître un peu longue à écrire mais optimise au maximum les ressources serveur (seulement 4 requêtes minimisées au total pour afficher une liste de livres et leurs tags associés,
quel que soit le nombre de livres affichés) et est assez systématique, elle peut donc être intégrée dans une classe générique de gestion de listes paginées.