Insertion

Le nombre d'index sur une table est le facteur le plus important pour les performances d'un insert. Plus une table a d'index, plus l'exécution sera lente. L'instruction insert est la seule opération qui ne peut pas bénéficier directement de l'indexation car elle n'a pas de clause where.

Ajouter une nouvelle ligne dans une table implique de suivre plusieurs étapes. Tout d'abord, la base de données doit trouver une place pour enregistrer la ligne. Pour une table standard, sans ordre particulier pour les lignes, la base de données peut utiliser tout bloc de la table qui dispose de suffisamment d'espace libre. C'est un traitement très simple et très rapide, généralement exécuté en mémoire. Tout ce qui reste à faire est d'ajouter la nouvelle entrée dans le bloc de données respectif.

Si la table dispose d'index, la base de données doit s'assurer que la nouvelle entrée se trouve dans les index. Pour cela, il doit ajouter la nouvelle entrée dans chaque index de la table. Le nombre d'index est donc un multiplicateur du coût pour une requête insert.

De plus, l'ajout d'une entrée dans un index est bien plus coûteux que son ajout dans une structure de table car la base de données doit conserver l'ordre de l'index et la propriété balancée de l'arbre. La nouvelle entrée ne peut donc pas être écrite dans n'importe quel bloc. Elle appartient à un nœud feuille spécifique. Bien que la base de données utilise l'arbre de l'index pour trouver le bon nœud feuille, elle doit toujours lire quelques blocs d'index pour parcourir l'arbre.

Une fois que le nœud feuille valide a été identifié, la base de données vérifie s'il reste suffisamment de place libre dans ce nœud. Si ce n'est pas le cas, la base de données doit diviser en deux le nœud feuille et distribuer les entrées entre l'ancien nœud et le nouveau nœud. Ce traitement affecte aussi la référence dans le nœud branche correspondant. Il se peut aussi que le nœud branche n'ait plus d'espace, auquel cas il devra être divisé en deux. Dans le pire des cas, la base de données doit diviser tous les nœuds jusqu'au nœud racine. Dans ce seul cas, l'arbre dispose d'une couche supplémentaire, autrement dit sa profondeur grossit.

Il découle de tout cela que la maintenance de l'index est la partie la plus coûteuse de l'instruction insert. C'est aussi visible dans la Figure 8.1, « Performance des insertions suivant le nombre d'index » : le temps d'exécution est difficilement visible si la table n'a pas d'index mais ajouter un seul index est suffisant pour multiplier le temps d'exécution par 100. Et chaque index supplémentaire ralentit encore plus l'exécution.

Pour optimiser les performances des insert, il est tout particulièrement important d'avoir un nombre d'index le plus petit possible.

En ne considérant que les requêtes insert, il serait préférable d'éviter complètement les index. C'est le seul moyen pour obtenir les meilleures performances pour un insert. Néanmoins, des tables sans index sont inimaginables pour les applications. Vous voulez généralement récupérer les données stockées dans la base, donc vous avez besoin d'index pour améliorer les performances des requêtes d'extraction. Même les tables en écriture seule ont souvent une clé primaire, et du coup un index.

Néanmoins, la performance sans les index est si bonne qu'il peut être intéressant de supprimer temporairement tous les index lors du chargement d'une grosse quantité de données, à condition que les index ne soient pas nécessaires pour d'autres requêtes SQL au même moment. Cela peut donner une accélération très importante sur la vitesse d'insertion, à tel point que c'est devenu une pratique courante pour les entrepôts de données.