par Guillaume Lelarge.

L'impact du volume de données sur les performances


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.

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Livre de Markus

Couverture du livre « SQL : Au cœur des performances »

L'essence de SQL tuning dans 200 pages.

Acheter de Markus
(Livre de poche et PDF)

Achetez chez Amazon
(Seulement en poche)

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license