Optimisation des bases de données

Ce que j'aime bien dans le métier d'informaticien c'est de l'optimisation. C'est tout un art de faire la même chose mais dix à vingt fois, voire des centaines de fois plus rapide. Quand je suis entré au collège, j'ai commencé à optimiser mon code écrit Turbo Pascal en écrivant les routines en assembleur 386. Cependant, j'aime particulièrement l'optimisation dans le domaine des bases de données.

Durant ma carrière, j'ai pu pratiquer sur les différents systèmes de gestion de bases de données relationnelles disponibles sur le marché: MySQL, PostgreSQL, SQL Server et évidemment Oracle dont j'admire énormément la haute technicité.

Avant de vous livrer dans l'optimisation de votre propre base de données avec des méga extra millions de lignes, arrêtez-vous un instant et demandez-vous si votre requête d'accès à la base de données est bien écrite. Car j'ai déjà vu un certain nombre de personnes écrire des jointures non pas en SQL mais dans le langage de programmation qu'elles utilisent. C'est comme si elles ignoraient l'existence des jointures en SQL. Concrètement, ces gens font cinq requêtes pour joindre les données provenant des cinq tables différentes en codant ça au niveau de l'application alors que normalement, une seule requête SQL avec des jointures adéquates peut faire la même chose dix fois plus vite.

L'autre jour, il y a un "ingénieur" qui vient me demander: je dois faire une requête avec des jointures sur des tables mais j'aimerais savoir comment faire des jointures... Si je n'étais pas installé confortablement sur mon siège, sa question m'aurait balancé par terre. Comme quoi tout le monde peut avoir son diplôme d'ingénieur.

L'index est majeur

La technique d'optimisation de base passe par la maîtrise des plans d'exécution et l'art d'utilisation des indexes. Mon pauvre professeur n'arrêtais pas de crier "mettez des indexes sur les colonnes de jointures" mais hélas, il y a très peu d'élèves qui s'en souviennent. Et ben, tant mieux ! Ça crée des emplois pour des gens comme moi, qui passent pour un dieu en créant des indexes juste là où il faut.

Vous l'avez compris ! L'indexe c'est majeur dans une base de données. Un indexe permet d'accéder quasiment de façon instantanée à la colonne recherchée. Un indexe, ça aide à diminuer les accès disques, à augmenter la vitesse d'exécution des requêtes avec des jointures ou avec des critères de recherche portant sur la colonne indexée.

Mais attention ! Comme pour tout sur cette Terre, trop d'indexes tue l'indexe. Selon Oracle, une table disposant un indexe met trois fois plus longtemps pour terminer les instructions de manipulation de données par rapport à une même table sans indexe. Donc, approximativement, l'insertion d'une nouvelle ligne dans une table disposant dix indexes met trente fois plus de temps par rapport à l'insertion d'une même ligne dans la même table sans aucun index.

Les types d'index

J'ai découvert il n'y a pas très long temps qu'il existe plusieurs types d'index. Et oui ! On m'a appris à l'école un certain CREATE INDEX et puis c'était tout. C'est en lisant de la documentation sur Oracle que j'ai su qu'il existe d'autres types d'index. Les indexes les plus utilisés et les plus parlés dans les écoles ce sont des indexes appelés B-tree ou les arbres B équilibrés en français. Au passage, un arbre équilibré B, n'est pas un arbre binaire. Ce type d'index est adapté surtout pour les colonnes avec beaucoup de valeurs distinctes comme les colonnes de clés primaires.

Il y a encore des indexes par hash. Un fonction de hash permet à la base de données de classer les données d'une ou plusieurs colonnes indexées. Cela permet de créer un indexe plus homogène et éviter de se trouver avec des indexes normaux mal équilibrés.

L'index bitmap

Le type d'index qui m'a le plus impressionné ce sont les indexes bitmaps. C'est méga utile pour les colonnes de faible cardinalité telle que la colonne de civilité. En fait, dans un index bitmap, les valeurs distinctes d'une colonne sont représentées par autant de colonnes distinctes dans l'index. Les lignes qui ont une valeur précise vont se voir attribuer le bit 1 dans la colonne d'index bitmap portant cette valeur.

Mais "pourquoi donc des 0 et des 1 et cinquante mille colonnes d'index supplémentaires ?", me diriez-vous ! En fait les 0 et des 1 permettent de faire des opérations logiques qui vont beaucoup plus vite pour trouver des lignes satisfaisantes d'un critère de recherche que d'autres types d'opération de comparaison. De plus, les indexes bitmap sont compressés et de ce fait, occupent moins de place que les indexes d'arbre binaire normaux.

Enfin, les indexes bitmap permettent aussi de compter les nuls très rapidement. Ce dernier point n'est pas le moindre car on le sait tous qu'en base de données, il y a des nuls. Bon, d'accord, c'est pas drôle.

L'index inversé

C'est le terme que j'ai trouvé pour traduire REVERSE INDEX en anglais. Il ne faut pas confondre un index inversé avec un index descendant. L'intérêt de ce type d'index inversé c'est qu'il permet de répartir les données sur l'arbre de façon plus homogène. En gros, ça facilite les insertions fréquentes dans une table. Cependant, Oracle ne peut pas utiliser cet index pour faire une recherche de plusieurs valeurs. Oracle ne peut s'en servir que pour trouver rapidement une valeur unique.

Le problème des newbies c'est quand ils découvrent une nouvelle fonctionnalité, ils essayent d'en mettre partout... C'est ce qui est arrivé avec nos tables en production. Lors des requêtes de sélection, Oracle faisait une lecture complète de la table qui compte une vingtaine de millions de lignes. Alors qu'un index normal (non inversé) aurait pu être utilisé dans ce cas là pour accélérer les recherches.

L'index hash

Le grand avantage d'un index de type hash c'est sa vitesse: il est beaucoup plus rapide qu'un index normal. Cependant, la base de données ne peut utiliser un index hash que lorsqu'il y a des prédicats d'égalité ou d'inégalité (opérateur = ou opérateur <>).

Mysql: l'index de type hash est seulement supporté par le type de stockage "Memory"
PostgreSQL: l'index de type hash est supporté mais il doit être reconstruit après un crash.

Choisir son index

Le moment le plus difficile c'est de choisir quel type d'indexe utiliser et sur quelle colonne indexée. Si une colonne a plein plein de valeurs différentes, prenez un index B-tree normal et tout ira bien. Si c'est une colonne avec très peu de valeurs distinctes, prenez un index bitmap. Quoiqu'on dise, vous pouvez toujours dropper un indexe bitmap et le reconstruire rapidement.

Les colonnes à indexer absolument sont les colonnes de clé primaire. C'est très utile quand vous faites un DELETE ou UPDATE avec la clé primaire comme critère WHERE. Sans index, la base de données va parcourir votre table de plusieurs millions de ligne pour trouver une seule ligne à modifier.

D'habitude, vous créez un index sur une seule colonne, mais vous pouvez aussi créer un index sur 2 ou plusieurs colonnes. Mais attention, dans ce cas, l'ordre des colonnes indexées est important. Dans un index de 2 colonnes, si vous précisez un filtre avec la première colonne, l'index est utilisé. Mais si vous utilisez la deuxième colonne comme filtre WHERE Colonne2 = 123 par exemple, l'index n'est pas utilisé. Il faut utiliser les colonnes indexées de gauche à droite. Pour notre indexe, vous pouvez évidemment utiliser les 2 colonnes dans un SELECT. La base de données va utiliser l'indexe créé sur ces 2 colonnes.

Les curseurs

Ah, les curseurs ! Pour être simple: ce ne sont pas ce que vous voyez sur l'écran. ^.^. Dans les bases de données, il y a deux types de curseurs: implicite et explicite. Ce qu'on nous apprend à l'école ce sont des curseurs explicites ou plutôt des curseurs PL/SQL. Récemment, j'ai appris au boulot qu'au niveau technique, le nombre de collègues "ingénieurs" qui savent ce qu'un curseur signifie est un entier inférieur à 2 dans un service d'une vingtaine de personnes ! D'où la panique générale quand on est confronté aux problèmes de "maximum open cursors exceeded" sous Oracle.

C'est comme le problème de fuite mémoire. "Ca arrive aux meilleurs mais surtout aux autres" disait mon collègue de bureau. Si on a l'habitude de fermer un truc quand a ouvert, ou de libérer la mémoire qu'on a alloué, ce problème n'arrivera pas. Malheureusement, le monde n'est pas parfait, et il existe trop de développeurs moyens. La preuve, j'ai déjà vu du code C++ avec des "new" partout sans aucun "delete" ... Et ça crée de la mauvaise pub pour les ordinateurs qui se plantent un peu trop souvent.

Revenons à nos moutons. Si le problème de curseurs survient dans du code PL/SQL, vous avez peut être oublié de faire des CLOSE CURSOR quelque part. Si ce problème prend racine dans votre code .NET, vous avez peut être oublié de faire un Reader.Close(). C'est aussi simple que ça.

Les partitions

Plus on a de l'espace, plus on stocke des choses. C'est connu ! C'est aussi vrai pour les bases de données surtout des bases qui servent aux études de statistiques ou d'analyse. Le problème qui peut survenir c'est le volume de données géré. Même si la base est optimisée avec des indexes qu'il faut, le temps d'exécution des requêtes devient de plus en plus longue. Une innovation récente dans ce domaine c'est l'utilisation des tables paginées. Si une table est paginée en plusieurs morceaux, les indexes de cette table sont aussi paginés.

Linq et la performance

Il faut éviter le plus possible de faire un Cast<TNouveauType>(); ou Cast<INouvelleInterface>();. Cela peut coûter très cher avec un framework .Net <= 3.5 sp1.

...

-- to be continued --