Administration des bases de données

On voit trop souvent en entreprise des bases de données mal conçues. Alors qu'à la conception rien d'anormal n'est détecté, on est rapidement confronté à plusieurs problèmes lorsque la base de données est mise en production.

Ces situations, souvent causées par un manque d'expertise en conception de bases de données, peuvent potentiellement être évitées en comprenant ces 2 notions de base.


Primo - Utiliser des index adéquats

Grâce aux index, Oracle est en mesure d'exécuter des requêtes sur des tables très rapidement et sans faire de lecture complète (c'est-à-dire analyser chaque enregistrement d'une table lors d'une requête " full table scan ")

En considérant que la table suivante possède 1.5 millions de lignes avec des valeurs différentes:

CREATE TABLE PRODUITS (
    ID NUMBER PRIMARY KEY,            
    NOM VARCHAR2(50),
    DESCRIPTION VARCHAR2(2000),
    ACTIF CHAR(1) CHECK (ACTIF IN ('O', 'N'))
) TABLESPACE PERFORMANCE;

Supposons 2 situations : 1 - La colonne NOM n'est pas indexée. 2 - La colonne NOM est indexée. (CREATE INDEX prod_idx ON PRODUITS(nom); )

Et la requête suivante :

SELECT COUNT(*) FROM PRODUITS WHERE NOM LIKE 'T%';

Dans la situation 1, la requête s'est exécutée en 4.5 secondes. Dans la situation 2, puisque la colonne NOM est indexée, la requête s'est exécutée en 0.01 seconde. Grande différence, n'est-ce pas ?

Si vous pensez que 4.5 secondes est acceptable pour une requête sur une table de cette taille, passez directement au point #2 (segundo) de cet article.

Vous n'êtes pas au point #2 de l'article ? Bien ! Continuons…

Lors de la création d'un index, il faut toujours considérer la possibilité de faire des index à colonnes multiples. Par exemple :

CREATE INDEX prod_mul_idx ON PRODUITS(nom, description); au lieu de :
CREATE INDEX prod_idx ON PRODUITS(nom);

Ainsi, lorsque Oracle devra filtrer les résultats d'une requête selon le NOM et la DESCRIPTION du produit, l'index à colonnes multiples permettra un temps d'exécution moindre.

Par exemple, considérons la requête SQL suivante :

SELECT COUNT(*) FROM PRODUITS WHERE NOM LIKE 'T%' AND DESCRIPTION Like 'T%';

Avec un index simple sur la colonne NOM, le temps d'exécution de la requête est de : 1 minute 45 secondes, alors qu'avec un index sur les deux colonnes (NOM, DESCRIPTION), le temps d'exécution est de : 0.01 seconde. Intéressant, non ?


Segundo - Toujours garder en tête l'achalandage

À prime abord, exécuter une requête prenant quelques secondes à se terminer peut paraître inoffensif, non ? Hé bien vous avez raison… si vous êtes le seul utilisateur de la base de données (et du serveur!). Cependant, la plupart du temps, une base de données est utilisée par plusieurs usagers en même temps.

Imaginons un scénario ou il y a l'ouverture d'un site de ventes de produits en ligne. Les premiers jours de l'ouverture du site Web, le serveur de base de données ne traite que 10 requêtes SQL par minute et le temps moyen d'exécution est de 1 seconde par requête. Ainsi, le serveur répond bien à la demande. Cependant, en peu de temps l'achalandage du site augmente, et le site passe à 2 requêtes par seconde! Malheureusement, ces requêtes prennent toujours plus ou moins une seconde pour s'exécuter et le serveur à maintenant plus de difficulté à répondre à la demande. Qu'adviendra-t-il du serveur si le site Web prend encore plus de popularité ?

Une telle situation peut s'aggraver au point où elle finirait par tuer le serveur de base de données. Alors adieu les performances, les ventes, et les profits…


Conclusion

Prenez toujours le temps de bien analyser vos requêtes afin d'y créer les meilleurs index possible. Et prenez en considération l'achalandage qu'il y aura sur votre application. Une base de données optimisée peut facilement servir plusieurs dizaines de requêtes par seconde !

Si vous voulez en savoir plus sur l'optimisation d'index, je vous suggère de lire sur les index BITMAP, qui sont très utiles pour les colonnes à faible cardinalité (où il y a peu de valeurs différentes).

J'espère que cet article clarifiera certaines notions sur la conception de vos bases de données. Si vous avez des commentaires ou questions, n'hésitez pas à me contacter.

Tous les scripts SQL permettant de concevoir les tests de performance sont disponibles (voir plus bas). Notez que ces scripts ont été testés sous Oracle 11g release 1.