IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Optimisations SGBD Discussion :

Fragmentation d'un index clusterisé


Sujet :

Optimisations SGBD

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre à l'essai
    Profil pro
    Inscrit en
    Mars 2010
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 6
    Par défaut Fragmentation d'un index clusterisé
    Chers développeurs,

    J'ai une table de positions gps de 9Go qui possède donc un grand nombre de lignes.
    Cette table a comme index clusterisé

    GpsVehicleID bigint,
    GpsTime dateTime

    Il y a beaucoup d'insertions (plus de 100 par secondes). En règle générale, les lignes insérées se suivent plus ou moins selon la colonne GpsTime.
    Le nombre total de véhicules est lui peut élevé (à peu près 5000).

    Au départ mon cluster était inversé, le temps étant en premier.
    Mais nous nous sommes alors aperçu que toutes les requêtes faites étaient d'abord filtrées par le GpsVehicleID puis par le temps.
    Nous avons donc décidé d'inverser le cluster afin d'économiser la maintenance d'un index non clusterisé sur une table aussi obèse.

    Cependant, aujourd'hui j'ai un doute. Est-ce que cela ne créerait pas plus rapidement de la fragmentation dans le cluster de ne pas mettre en premier la colonne sensée augmenter de manière monotone ?

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    22 010
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 22 010
    Billets dans le blog
    6
    Par défaut
    évidemment oui !

    Mais pour la meilleure configuration il nous faudrait connaître le SGBDR. Par exemple pour SQL Server je vous aurait conseillé de rajouter une clef auto incrémentée de type BIGINT et de créer l'index cluster dessus, puis de créer une contrainte d'unicité sur vos colonnes avec un FILL factor de l'ordre de 90%

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre Expert
    Homme Profil pro
    Retraité
    Inscrit en
    Octobre 2005
    Messages
    1 473
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 66
    Localisation : France, Seine Saint Denis (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Finance

    Informations forums :
    Inscription : Octobre 2005
    Messages : 1 473
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    ... pour SQL Server je vous aurait conseillé de rajouter une clef auto incrémentée de type BIGINT et de créer l'index cluster dessus, puis de créer une contrainte d'unicité sur vos colonnes avec un FILL factor de l'ordre de 90% ...
    Donc deux index au lieu d'un ... Si c'est le cas, l'optimisation proposée m'échappe quelque peu ...

  4. #4
    Membre à l'essai
    Profil pro
    Inscrit en
    Mars 2010
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 6
    Par défaut
    Je ne comprends pas bien l'intérêt de créer un index cluster sur une colonne que je devrai rajouter, et qui ne me sera d'aucune utilité...
    Et comme l'a souligné Luc, cela me rajoute un index en plus.

    Ma question portait plus sur l'intérêt du cluster regroupé par VehiculeID et non par le temps gps. Je me demande si ça vaut le coup d'économiser un index non cluster grâce à ce cluster (rajouter ce type d'index fait grossir la table de 25%, non négligeable, avec tous les traitements à rajouter au niveau de la mise à jour de l'index), sachant qu'apparemment selon vous et comme je le soupçonnais cela va créer de la fragmentation plus rapidement qu'un cluster sur le temps gps.

    Donc ma question est plutôt qu'est-ce qui a le plus d'impact sur les performances entre ces deux possibilité ?

    Merci.

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    22 010
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 22 010
    Billets dans le blog
    6
    Par défaut
    Je n'ai pas le temps de faire une explication de 3 heures sur le sujet.

    Commencez par lire ceci : http://sqlpro.developpez.com/optimis...ntenanceIndex/
    Il vous faut comprendre comment sont structurées les pages de données et d'index dans un SGBDR

    Ensuite comprenez que :
    sans index cluster, la ligne d'une table est repéré à l'aide d'une clef physique comportant 3 éléments :
    • le n° de fichier
    • le N° de page dans le fichier
    • et enfin le N° de slot de ligne dans une page

    Tous les index créés doivent référencer la ligne de la table pour qu'après recherche dans l'index on puisse revenir sur le table.
    Si vous utilisez un index cluster, alors la ligne de la table n'est plus repérée par cet identifiant physique, mais par l'indentifiant logique que constitue la valeur de la clef cluster. Tous les index secondaire utiliseront donc cet info pour retrouver la ligne

    Si votre clef de cluster est longue ou composé de plusieurs parties, alors elle devient moins intéressante que de ne pas clusteriser.
    C'est pourquoi l'index cluster doit être le plus court possible.
    Or à l'évidence DATETIME + BIGINT = 2 colonnes et 16 octets.
    Alors que BIGINT d'auto incrément = 1 colonne et 8 octets.
    Qu'est ce qui sera plus optimisé selon vous : que tous vos index secondaires ait à faire référence à la ligne par 16 octets en 2 colonnes ou 8 octets en 1 colonne ?

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  6. #6
    Membre à l'essai
    Profil pro
    Inscrit en
    Mars 2010
    Messages
    6
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 6
    Par défaut
    Tout d'abord merci pour le lien!
    Celui-ci a répondu de manière concrète à ma question principale.
    En effet, avec un GpsVehiculeID en première colonne de cluster, cela va créer un nombre important de split de pages, et donc de fragmentation... Dans mon index cluster.

    Par contre, et malgré la lecture de votre article, je ne vois toujours pas l'avantage du cluster sur clé auto-incrémentée.
    Avec cette solution, je dois créer un index non cluster en plus, donc je me retrouve à gérer l'index non-cluster sur (GpsVehiculeID, GpsTime) ET l'index cluster sur la clé auto-incrémentée.

    Lorsqu'une recherche sera faite dans cet index, cela me coutera le coût du parcours de l'index + le lien vers l'index cluster.
    Sa taille sera aussi grossie par ce nouvel index cluster.

    Alors que si je crée un seul index, cluster, sur (GpsVehiculeID, GpsTime), j'accède directement à mes lignes, sans indirection.
    Je paye certes la taille du cluster sur deux colonnes, mais j'économise quand même par rapport à un cluster + un index non cluster sur ces mêmes deux colonnes.

    Maintenant je dis ça bien humblement, c'est juste qu'il y a quelque chose qui doit m'échapper. Si vous pouviez m'éclairer cela me permettrait de progresser dans ces méandres...

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [SQL2008][SQL2012] Index Clusterisé Vs Include
    Par Donpi dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 14/10/2012, 14h56
  2. Index clusterisé
    Par chris_013 dans le forum Administration
    Réponses: 3
    Dernier message: 09/01/2009, 13h13
  3. Fragmentation d'Index jamais inferieur a 50 % ?
    Par Bronks dans le forum MS SQL Server
    Réponses: 7
    Dernier message: 16/03/2007, 09h14
  4. [ASE]Création d'un index non clusterisé
    Par Mehdi3 dans le forum Sybase
    Réponses: 2
    Dernier message: 13/04/2006, 11h18
  5. [oracle 8i /java] Index et fragmentation
    Par miloux32 dans le forum Oracle
    Réponses: 4
    Dernier message: 01/02/2006, 16h16

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo