Ecommerce DEV - Les indexes MySQL et leur impact sur les performances

Apr 12 2006

Les indexes MySQL et leur impact sur les performances

Programmation - Base de données
Ecrit par Kamal AOUDA
12-04-2006
Les indexes MySQL et leur impact sur les performances Les analogies qui existent entre un annuaire téléphonique et une base de données permettent de mieux comprendre ce que sont les indexes. Supposons que l’on souhaite rechercher le numéro de téléphone d’une personne dans une table qui contient près de 30 millions d’abonnés. Si on exécute une requête comme SELECT * FROM phonebook WHERE last_name = 'person', le gestionnaire de base de données sera obligé de parcourir tous les enregistrements de la table avant de retourner le résultat. Cette technique est bien sûr inefficace. Elle dégrade les performances à mesure que le nombre d’enregistrements croît (problème classique du O(n)). D’habitude lorsqu’on souhaite effectuer une recherche dans un annuaire papier, on se positionne directement sur le groupe de pages qui commencent avec la même lettre que celle du nom/prénom de la personne recherchée. Ce positionnement est facilité par le classement alphabétique des pages. Il permet de réduire considérablement le nombre de pages à compulser. Les index des bases de données fonctionnent de la même manière. Par exemple si on souhaite effectuer les recherches plus rapidement sur la base du prénom on peut appliquer un index à la colonne correspondante en utilisant l’instruction ALTER : ALTER TABLE phonebook ADD INDEX (last_name) Après l’exécution de cette instruction, MySQL crée une liste ordonnée de tous les prénoms de la table phonebook. Avec les tables MyISAM, les indexes sont stockés dans des fichiers à part pour rendre les recherches plus rapides (en fait si la table contient 1000 lignes, la recherche sera 100 fois plus rapide qu'une lecture séquentielle ). Il est important de noter que la création d'indexes consomme beaucoup d'espace mémoire dans la base de données. De plus leur mise à jour à chaque modification de la table peut augmenter le temps nécessaire pour réaliser des opérations d’écriture. De ce fait, il faut que la création d'indexes soit justifiée et que les colonnes auxquelles ils sont appliqués soient judicieusement choisies. Outre la célérité des recherches, les indexes permettent également d’optimiser la performance des opérations suivantes : * Lire des lignes dans d'autres tables lorsqu’il y a des jointures. * Trouver les valeurs MAX() et MIN() dans une colonne. * Trier ou grouper des lignes dans une table (exemple ORDER BY key_part_1,key_part_2). Dans MySQL il existe plusieurs types d’indexes. Les plus utiles sont : * Les indexes partiels : ils permettent de réduire la quantité de mémoire consommée par la création des indexes. Ainsi au lieu d’appliquer l’index à tous les caractères d’un nom/prénom il est possible de l’associer à certains caractères seulement. Dans l’exemple suivant, seuls les 4 premiers octets du prénom sont utilisés pour la création de l’index. ALTER TABLE phonebook ADD INDEX (last_name(4)). Après l’application de cet index une requête comme SELECT * FROM phonebook WHERE last_name = 'Jackie', retournera des résultats comme Jacky, Jackline, Jackie, Jackass. Par conséquent il faudra exécuter plus d’une requête pour retourner des résultats précis (Il s’agit donc de trouver une solution de compromis entre le nombre de requêtes à exécuter, le temps de réponse et la quantité de mémoire consommée par les index). * Les indexes multi-colonnes : MySQL permet d’appliquer les indexes à plusieurs colonnes. Exemple : ALTER TABLE phonebook ADD INDEX (last_name, first_name) Ce genre d’index permet d’améliorer considérablement la vitesse d’exécution des requêtes qui portent sur toutes les colonnes indexées (dans notre exemple ces indexes donnent de bonnes performances avec une requête comme SELECT * FROM phonebook WHERE last_name = 'Jhon' AND first_name = 'Pizarelli'). Bien sûr il est possible de combiner les indexes partiels avec les indexes multi-colonnes pour réduire la quantité de mémoire consommée : ALTER TABLE phonebook ADD INDEX (last_name(4), first_name(4)) * Les indexes FULLTEXT : ils sont utilisés pour les recherches en texte intégral uniquement avec les tables MyISAM. L'indexation se fait sur toute la largeur des colonnes de type CHAR, VARCHAR et TEXT. En règle générale, il faut toujours charger les données dans une table avant de leur appliquer les indexes FULLTEXT. La raison est que le chargement et l’écriture dans une table déjà indexée en FULLTEXT peuvent être anormalement longs.
Dernière mise à jour : ( 12-04-2006 )

Professional Development with Web APIs : Google, eBay, Amazon.com, MapPoint, FedEx

Un livre idéal pour les programmeurs .Net qui veulent intégrer à leurs applications de commerce électronique, les fonctionnalités offertes à travers les services web de Google, Fedex, Ebay, Amazon et MapPoimt.

Après un bref rappel des concepts de base, ce livre aborde des sujets avancés comme l'appel des API à partir d'appareils mobiles ou des applications développées avec VBA, l'envoi d'un fax via l'API Paypal, la création de votre propre web API (cette liste n'est pas limitative). A la fin de ce livre vous trouverez des études de cas qui montrent comment utiliser les API précitées pour développer rapidement une application CRM et un un tableau de bord électronique.

Références

http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584456.html, Denise M. Gosnell, Wrox, ISBN: 0-7645-8445-6, April 2005, 324 pages