Consignes générales pour la création d'index
Les administrateurs de bases de données expérimentés peuvent concevoir de bons ensembles d'index, mais cette tâche est très complexe, sujette à erreurs et demande beaucoup de temps, même dans le cas de bases de données et de charges de travail peu complexes. La compréhension des caractéristiques de votre base de données, de vos requêtes et de vos colonnes de données peut vous aider à créer des index optimaux.
Directives relatives aux bases de données
Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux bases de données :
La définition de nombreux index sur une table affecte les performances des instructions INSERT, UPDATE, DELETE et MERGE , car à mesure que les données de la table changent, tous les index doivent être mis à jour en conséquence.
Évitez que les tables mises à jour ne soient trop abondamment indexées et faites en sorte que les index soient étroits, c'est-à-dire qu'ils comprennent le moins de colonnes possible.
Utilisez de nombreux index pour améliorer les performances des requêtes sur les tables possédant des besoins réduits en matière de mise à jour, mais de grands volumes de données. Un grand nombre d'index peut améliorer les performances des requêtes qui ne modifient pas les données (instructions SELECT), car l'optimiseur de requête dispose d'un choix d'index plus vaste pour déterminer la méthode d'accès la plus rapide.
Il n'est peut-être pas idéal d'indexer des tables de taille réduite, car le temps nécessaire à l'optimiseur de requête pour parcourir l'index à la recherche de données peut être supérieur à la durée d'une simple analyse de la table. Par conséquent, les index de petites tables peuvent ne jamais être utilisés, mais doivent néanmoins être gérés, car les données de la table changent.
Les index de vues peuvent vous permettre d'améliorer considérablement les performances lorsque la vue contient des agrégations, des jointures de tables ou une combinaison d'agrégations et de jointures. La vue ne doit pas être explicitement référencée dans la requête pour que l'optimiseur de requête puisse l'utiliser. Pour plus d'informations, consultez Conception des vues indexées.
Servez-vous de l'Assistant Paramétrage du moteur de base de données pour analyser votre base de données et obtenir des recommandations sur les index. Pour plus d'informations, consultez Présentation de l'Assistant Paramétrage du moteur de base de données.
Directives relatives aux requêtes
Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux requêtes :
Créez des index non-cluster sur toutes les colonnes fréquemment utilisées dans des prédicats et des conditions de jointure dans des requêtes.
Important
Évitez d'ajouter des colonnes superflues. L'ajout d'un trop grand nombre de colonnes d'index peut avoir une influence négative sur les performances de gestion des index et de l'espace disque.
La couverture des index peut améliorer les performances des requêtes, car toutes les données nécessaires pour répondre aux exigences de la requête existent dans l'index proprement dit. Cela signifie que seules les pages d'index, et non les pages de données de la table ou de l'index cluster, sont nécessaires pour récupérer les données demandées, réduisant ainsi globalement le nombre d'E/S des disques. Par exemple, une requête de colonnes a et b sur une table possédant un index composite créé sur les colonnes a, b et c peut récupérer les données spécifiées à partir du seul index.
Rédigez des requêtes insérant ou modifiant un maximum de lignes en une seule instruction, plutôt que de recourir à plusieurs requêtes pour mettre à jour les mêmes lignes. De cette façon, la maintenance d'index optimisée peut être exploitée.
Évaluez le type de requête et la manière dont les colonnes sont utilisées dans la requête. Par exemple, une colonne utilisée dans un type de requête de correspondance exacte constitue un candidat valable à un index non-cluster ou cluster. Pour plus d'informations, consultez Types de requêtes et index.
Directives relatives aux colonnes
Lorsque vous créez un index, prenez en compte les directives suivantes relatives aux colonnes :
Veillez à ce que la clé d'index des index cluster soit courte. En outre, les index cluster bénéficient du fait d'être créés sur des colonnes uniques ou non NULL. Pour plus d'informations, consultez Indications pour la conception d'index cluster.
Les colonnes de types de données ntext, text, image, varchar(max), nvarchar(max) et varbinary(max) ne peuvent pas être spécifiées comme colonnes de clés d'index. Cependant, les types de données varchar(max), nvarchar(max), varbinary(max) et xml peuvent participer à des index non-cluster en tant que colonnes d'index non clé. Pour plus d'informations, consultez Index avec colonnes incluses.
Un type de données xml ne peut être qu'une colonne clé dans un index XML. Pour plus d'informations, consultez Index sur les colonnes de type de données XML.
Vérifiez l'unicité des colonnes. Un index unique plutôt que non unique sur la même combinaison de colonnes procure des informations supplémentaires à l'optimiseur de requête, ce qui améliore l'utilité de l'index. Pour plus d'informations, consultez Directives pour la conception d'index uniques.
Examinez la distribution des données dans la colonne. Bien souvent, la longueur d'exécution d'une requête est due à l'indexation d'une colonne comportant peu de valeurs uniques ou à la réalisation d'une jointure sur ce type de colonne. Il s'agit d'un problème crucial pour les données et la requête, que l'on ne peut généralement pas résoudre sans identifier clairement la situation. Un répertoire téléphonique physique, par exemple, trié dans l'ordre alphabétique par nom de famille, ne permettra pas l'identification rapide d'une personne si tous les habitants de la ville se nomment Smith ou Jones. Pour plus d'informations sur la distribution de données, consultez Utilisation des statistiques pour améliorer les performances des requêtes.
Envisagez d'utiliser des index filtrés sur les colonnes qui ont des sous-ensembles bien définis, par exemple les colonnes fragmentées, les colonnes contenant principalement des valeurs NULL, les colonnes contenant des catégories de valeurs et les colonnes contenant des plages de valeurs distinctes. Un index filtré bien conçu peut améliorer les performances des requêtes et réduire les coûts de maintenance et de stockage. Pour plus d'informations, consultez Règles de conception d'index filtrés.
Tenez compte de l'ordre des colonnes si l'index doit en contenir plusieurs. La colonne utilisée dans la clause WHERE au sein d'une condition de recherche de type égal à (=), supérieur à (>), inférieur à (<) ou BETWEEN, ou qui participe à une jointure, doit être insérée en premier. Les colonnes supplémentaires doivent être classées en fonction de leur niveau de différenciation, c'est-à-dire de la plus distincte à la moins distincte.
Par exemple, si l'index est défini en tant que LastName, la valeur FirstName de l'index sera utile si la condition de recherche est WHERE LastName = 'Smith' ou WHERE LastName = Smith AND FirstName LIKE 'J%'. Cependant, l'optimiseur de requête n'utilise pas l'index pour une requête portant uniquement sur FirstName (WHERE FirstName = 'Jane').
Pensez à indexer les colonnes calculées. Pour plus d'informations, consultez Création d'index sur des colonnes calculées.
Caractéristiques des index
Après avoir déterminé qu'un index est approprié pour une requête, vous pouvez sélectionner le type d'index qui convient le mieux à la situation. Un index doit posséder les caractéristiques suivantes :
être cluster ou non-cluster ;
être unique ou non unique ;
être à une ou plusieurs colonnes ;
être trié par ordre croissant ou décroissant d'après les colonnes qui le constituent ;
table entière plutôt que filtré pour les index non cluster.
Vous pouvez également personnaliser les caractéristiques de stockage initiales de l'index afin d'optimiser ses performances ou sa maintenance en définissant une option telle que FILLFACTOR. Pour plus d'informations, consultez Configuration des options d'index. Vous pouvez également déterminer l'emplacement de stockage de l'index en utilisant des groupes de fichiers ou des schémas de partition pour optimiser les performances. Pour plus d'informations, consultez Placement d'index dans des groupes de fichiers.
Voir aussi