Introduction à l'optimisation de base de données

Très bon article:

http://www.notes-de-cours.com/dba/blogue/6/introduction-a-l-optimisation-de-base-de-donnees

Introduction à l'optimisation de base 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:

Code:

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 :

Code:

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 :

Code:


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 :

Code:

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.