La quantité de données enregistrées dans une base de données a un gros impact sur ses performances. Il est généralement accepté qu'une requête devienne lente quand il y a plus de données dans la base de données. Mais quel est le niveau de l'impact sur les performances quand le volume de données double ? Et comment pouvons-nous améliorer ce ratio ? Voici les questions clés autour de la scalabilité des bases de données.
Comme exemple, nous allons analyser le temps de réponse de la requête suivante lors de l'utilisation de deux index différents. Les définitions des index resteront inconnues pour l'instant, mais elles seront révélées au fil de la discussion.
SELECT count(*)
FROM grosses_donnees
WHERE section = ?
AND id2 = ?La colonne SECTION a un but précis dans cette requête :
elle contrôle le volume de données. Plus le nombre SECTION
devient grand, plus la requête sélectionne de lignes. La Figure 3.1 montre le temps
de réponse pour une petite SECTION.
Figure 3.1 Comparaison de performances
Il existe une différence considérable sur les performances avec les deux variantes d'index. Ces temps de réponse sont toujours bien en-dessous d'un dixième de seconde, donc même la requête la plus lente est assez rapide dans la plupart des cas. Néanmoins, le graphe des performances montre seulement un point de test. Discuter de la scalabilité signifie regarder l'impact des performances lors de changements de paramètres environnementaux, comme le volume de données.
Important
La scalabilité montre la dépendance des performances sur des facteurs tels que le volume de données.
Une valeur de performance est un simple point de données sur un graphe de scalabilité.
La Figure 3.2 montre
le temps de réponse par rapport à la SECTION, autrement dit
pour un volume de données grossissant.
Figure 3.2 Scalabilité par le volume des données
Le graphe affiche un temps de réponse augmentant pour chaque index. Sur le côté droit du graphe, quand le volume de données est cent fois plus important, la requête la plus rapide a besoin de deux fois plus de temps alors que le temps de réponse de la requête la plus lente a augmenté d'un facteur de 20 (plus d'une seconde).
Astuce
Annexe C, « Example Schema » contient les scripts pour répéter ce test dans une base de données Oracle, PostgreSQL ou SQL Server.
Le temps de réponse d'une requête SQL dépend de nombreux facteurs. Le volume de données en est un. Si une requête est suffisamment rapide sous certaines conditions de tests, cela ne signifie pas pour autant qu'elle sera suffisamment rapide en production. Ceci est tout spécialement vrai dans le cas des environnements de développement qui disposent généralement d'une fraction des données du système de production.
Néanmoins, il n'est pas surprenant que les requêtes soient plus lentes quand le volume de données augmente. Mais la différence entre les deux index est parfois inattendue. Quelle est la raison de cette différence ?
Trouver la raison devrait être simple en comparant les plans d'exécution.
------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 972 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| SCALE_SLOW | 3000 | 972 |
------------------------------------------------------
------------------------------------------------------
| Id Operation | Name | Rows | Cost |
------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| SCALE_FAST | 3000 | 13 |
------------------------------------------------------Les plans d'exécution sont pratiquement identiques. La seule différence réside dans l'index sélectionné. Même si les coûts reflètent la différence de performances, la raison n'est pas visible dans le plan d'exécution.
Il semble que nous faisons face à un « index lent » ; la requête est lente bien qu'elle utilise un index. Néanmoins, nous ne croyons plus dans le mythe de l' « index cassé ». Rappelons-nous plutôt des deux ingrédients qui rendent une recherche par index lente : (1) l'accès à la table, et (2) le parcours d'un gros intervalle dans l'index.
Aucun des plans d'exécution n'affiche une opération TABLE ACCESS BY INDEX ROWID, donc un des
plans d'exécution doit parcourir un intervalle plus grand qu'un autre. Où
un plan d'exécution affiche-t-il l'intervalle d'index parcouru ? Dans les
informations de prédicats !
Astuce
Faites attention aux informations de prédicats.
L'information du prédicat n'est pas du tout un détail inutile que vous pouvez ignorer. Un plan d'exécution sans information de prédicat est incomplet. Sans lui, vous ne pouvez pas comprendre la différence de performances dans les plans affichés ci-dessus. Si nous regardons les plans d'exécution complets, nous pouvons voir la différence.
------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 972 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| SCALE_SLOW | 3000 | 972 |
------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("SECTION"=TO_NUMBER(:A))
filter("ID2"=TO_NUMBER(:B))
------------------------------------------------------
| Id Operation | Name | Rows | Cost |
------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| SCALE_FAST | 3000 | 13 |
------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("SECTION"=TO_NUMBER(:A) AND "ID2"=TO_NUMBER(:B))Remarque
Le plan d'exécution a été simplifié pour qu'il soit plus clair. « Distinguer les prédicats d'accès et de filtre » explique les détails de la section « Predicate Information » dans un plan d'exécution Oracle.
La différence est évidente maintenant : seule la condition sur
SECTION est un prédicat d'accès lors de l'utilisation de
l'index SCALE_SLOW. La base de données doit lire toutes les
lignes de la section, puis doit ignorer ceux qui ne correspondent pas au
prédicat de filtre sur ID2. Le temps de réponse grossit avec
le nombre de lignes dans la section. Avec l'index SCALE_FAST,
la base de données utilise toutes les conditions comme prédicats d'accès.
Le temps de réponse grandit avec le nombre de lignes sélectionnées.
Important
Les prédicats de filtre sont comme des explosifs non déclenchés. Ils peuvent exploser à tout moment.
Les dernières pièces manquantes du puzzle sont les définitions d'index. Pouvons-nous deviner les définitions d'index à partir des plans d'exécution ?
La définition de l'index SCALE_SLOW doit commencer avec
la colonne SECTION. Dans le cas contraire, il ne pourrait pas
être utilisé comme prédicat d'accès. La condition sur ID2
n'est pas un prédicat d'accès, donc elle ne peut pas suivre
SECTION dans la définition de l'index. Cela signifie que
l'index SCALE_SLOW doit avoir au moins trois colonnes où
SECTION est la première et ID2 n'est pas la
deuxième. C'est le cas dans la définition de l'index utilisé pour ce test :
CREATE INDEX scale_slow ON grosses_donnees (section, id1, id2);La base de données ne peut pas utiliser ID2 comme
prédicat d'accès à cause de la colonne ID1 en deuxième
position.
La définition de l'index SCALE_FAST doit avoir les
colonnes SECTION et ID2 dans les deux premières
positions car elles sont toutes les deux utilisées pour des prédicats
d'accès. Néanmoins, nous ne pouvons rien dire sur leur ordre. L'index qui
a été utilisé pour le test commence avec la colonne SECTION
et a la colonne supplémentaire ID1 en troisième position :
CREATE INDEX scale_fast ON grosses_donnees (section, id2, id1);La colonne ID1 a été ajoutée uniquement pour que cet
index ait la même taille que SCALE_SLOW. Sinon, vous auriez
pu avoir l'impression que la différence de performance était due à la
différence de taille.
Liens
Les prédicats de filtre d'index expliqués : Conditions plus grand que, plus petit que et entre
Trouver les prédicats de filtres d'index dans la base de données Oracle, PostgreSQL et SQL Server.
Indexer les filtres LIKE : prédicats d'accès et de filtre à l'index dans une expression.
Comparaison asymptotique : l'approche mathématique à la scalabilité.
