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 )

Java-XML et oracle : E-commerce - EAI - portails d'entreprise - Applications mobiles

Cet ouvrage montre comment tirer parti de l'offre Java-XML d'Oracle, tant au niveau de la base de données Oracle8i (drivers JDBC, conteneur EJB, ORB Corba, XSQL...), que des produits associés, outils XML-XSLT, JDeveloper, Oracle9i Application Server, etc.

L'ouvrage insiste tout particulièrement sur les problèmes d'intégration de ces technologies, de design des architectures et de scalabilité des applications. II est illustré de nombreux exemples de code et de deux études de cas, une application e-commerce construite à l'aide d'EJB et un serveur de documents XML multithread.

Références

Lien sur le site d'Amazon.

Fréderic Berque, Serge Frezefond, Ludovic Sorriaux

Titre : Java, XML et Oracle
Éditeur : Eyrolles
Collection : Solutions Développeurs
Parution : mars 2001
634 pages
ISBN : 2-212-09149-4
EAN13 : 9782212091496