Concevoir des index
SQL Server est doté de plusieurs types d’index permettant de prendre en charge différents types de charges de travail. Globalement, un index peut être considéré comme une structure sur disque associée à une table ou à une vue, ce qui permet à SQL Server de trouver plus facilement la ou les lignes associées à la clé d’index (qui se compose d’une ou de plusieurs colonnes de la table ou de la vue), si on le compare à l’analyse de la table entière.
Index cluster
Une question souvent posée lors d’un entretien de recrutement d’un administrateur de base de données est de demander au candidat quelle différence il y a entre un index cluster et un index non-cluster, attendu que les index représentent une technologie de stockage de données fondamentale dans SQL Server. Un index cluster est la table sous-jacente, stockée selon un ordre de tri basé sur la valeur de clé. Il ne peut y avoir qu’un index cluster sur une table donnée, car les lignes peuvent être stockées dans un ordre. Une table sans index cluster est appelée « segment de mémoire », et ces segments de mémoire ne sont le plus souvent utilisés que comme tables intermédiaires. Un principe important en conception de performances consiste à conserver votre clé d’index cluster aussi étroite que possible. Quand vous réfléchissez à l’utilisation de la ou des colonnes clés de votre index cluster, vous devez tenir compte des colonnes qui sont uniques ou qui contiennent de nombreuses valeurs distinctes. Une autre propriété de bonne clé d’index cluster se trouve dans les enregistrements accessibles de façon séquentielle, et qui sont fréquemment utilisés pour trier les données extraites de la table. Le fait d’avoir l’index cluster sur la colonne utilisée pour le tri peut économiser le coût du tri à chaque exécution de la requête, car les données seront déjà stockées dans l’ordre souhaité.
Notes
Lorsque nous disons que la table est « stockée » dans un ordre particulier, nous faisons référence à l’ordre logique, pas nécessairement à l’ordre physique sur le disque. Les index sont dotés de pointeurs entre les pages, et les pointeurs permettent de créer l’ordre logique. Lors de l’analyse d’un index « en ordre », SQL Server suit les pointeurs de page en page. Immédiatement après sa création, un index est aussi stocké très probablement dans l’ordre physique sur le disque, mais dès lors que vous commencez à apporter des modifications aux données, et que de nouvelles pages doivent être ajoutées à l’index, les pointeurs continuent de nous donner l’ordre logique correct, alors que les nouvelles pages ne le sont sans doute pas dans l’ordre physique.
Index non cluster
Les index non-cluster constituent une structure distincte issue des lignes de données. Un index non-cluster contient les valeurs de clé définies pour l’index, et un pointeur vers la ligne de données qui contient la valeur de clé. Vous pouvez ajouter une autre colonne non-clé au niveau feuille de l’index non-cluster pour couvrir davantage de colonnes à l’aide de la fonctionnalité de colonnes incluse dans SQL Server. Vous pouvez créer plusieurs index non-cluster sur une table.
Vous trouverez ci-dessous un exemple montrant à quel moment il faut ajouter un index ou ajouter des colonnes à un index non-cluster existant :
Le plan de requête indique que pour chaque ligne extraite à l’aide de la recherche d’index, d’autres données devront être récupérées de l’index cluster (la table elle-même). Il existe un index non-cluster, mais il n’inclut que la colonne de produits. Si vous ajoutez les autres colonnes de la requête à un index non-cluster, comme illustré ci-dessous, vous pouvez constater la modification du plan d’exécution pour éliminer la recherche de clé.
L’index créé ci-dessus est un exemple d’index de couverture, où, en plus de la colonne clé, vous incluez d’autres colonnes pour couvrir la requête et éliminer la nécessité d’accéder à la table elle-même.
Les index cluster et non-cluster peuvent être définis comme étant uniques, ce qui signifie qu’il ne peut pas y avoir de duplication des valeurs de clés. Les index uniques sont créés automatiquement lorsque vous créez une contrainte PRIMARY KEY ou UNIQUE sur une table.
Cette section se concentre sur les index d’arbre B (B-tree) dans SQL Server ; ceux-ci sont également appelés « index de magasin de lignes ». La structure générale d’un arbre B est illustrée ci-dessous :
Chaque page d’un index d’arbre B est appelée « nœud d’index », tandis que le nœud supérieur de l’arbre B forme le « nœud racine ». Les nœuds inférieurs d’un index sont nommés « nœuds terminaux », et la collection de nœuds terminaux constitue le niveau feuille.
La conception d’index est un mélange d’art et de science. Un index étroit ayant peu de colonnes dans sa clé demande moins de temps pour sa mise à jour, et présente une surcharge de maintenance moindre ; toutefois, il peut ne pas être utile car qui dit un grand nombre de requêtes dit un index plus étendu comportant davantage de colonnes. Vous devrez peut-être expérimenter plusieurs approches d’indexation en fonction des colonnes sélectionnées par les requêtes de votre application. L’optimiseur de requête choisit généralement ce qu’il considère être le meilleur index existant pour une requête. Cela ne signifie pas pour autant qu’il n’est pas possible de créer d’index encore plus efficace.
L’indexation correcte d’une base de données est une tâche complexe. Lorsque vous planifiez vos index pour une table, vous devez garder à l’esprit quelques principes de base :
- Comprendre les charges de travail du système. Une table qui est utilisée principalement pour des opérations d’insertion tirera beaucoup moins profit d’autres index qu’une table utilisée pour des opérations d’entrepôt de données qui sont à 90 % des activités de lecture.
- Comprendre quelles requêtes sont exécutées le plus fréquemment, et optimiser vos index autour de ces requêtes.
- Comprendre les types de données des colonnes utilisées dans vos requêtes. Les index sont parfaits pour les types de données Integer, ou les colonnes uniques ou non null.
- Créer des index non-cluster sur les colonnes fréquemment utilisées dans les prédicats et les clauses de jointure, et conserver ces index aussi étroits que possible pour éviter la surcharge.
- Comprendre la taille/le volume des données : une analyse de table sur une petite table est une opération relativement peu coûteuse, et SQL Server peut décider d’effectuer une analyse de table simplement parce qu’il est facile (banal) de le faire. Une analyse de table sur une grande table serait coûteuse.
Une autre option que SQL Server fournit est la création d’index filtrés. Les index filtrés conviennent mieux aux colonnes de grandes tables, dans lesquelles le pourcentage de lignes ayant la même valeur dans une colonne est élevé. Un exemple concret serait une table Employee, comme celle ci-dessus, qui stockait les enregistrements de tous les employés, y compris de ceux qui avaient quitté l’entreprise ou qui avaient pris leur retraite.
CREATE TABLE [HumanResources].[Employee](
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[OrganizationNode] [hierarchyid] NULL,
[OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
[SalariedFlag] [bit] NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL,
[CurrentFlag] [bit] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL)
Dans cette table, il existe une colonne nommée CurrentFlag, qui précise si un employé fait actuellement partie de l’entreprise. Cet exemple utilise le type de données bit, qui indique uniquement deux valeurs, un pour les employés actuellement embauchés, et zéro pour ceux qui ne le sont pas. Un index filtré avec WHERE CurrentFlag = 1, sur la colonne CurrentFlag, donnerait des requêtes efficaces sur les employés actuels.
Vous pouvez également créer des index sur des vues, ce qui peut améliorer considérablement les performances lorsque les vues contiennent des éléments de requête, tels que des agrégations et/ou des jointures de table.
Index columnstore
Columnstore offre de meilleures performances pour les requêtes qui exécutent de grandes charges de travail d’agrégation. Ce type d’index était initialement destiné aux entrepôts de données, mais avec le temps, les index columnstore ont été utilisés dans de nombreuses autres charges de travail pour faciliter la résolution des problèmes de performances de requêtes sur les grandes tables. Depuis SQL Server 2014, il existe des index columnstore non-cluster et cluster. À l’instar des index d’arbre B, un index columnstore cluster est la table elle-même stockée d’une certaine manière, tandis que les index columnstore non-cluster sont stockés indépendamment de la table. Les index columnstore cluster incluent par nature toutes les colonnes dans une table donnée. Par contre, contrairement aux index rowstore cluster, les index columnstore cluster ne sont PAS triés.
Les index columnstore non-cluster sont généralement utilisés dans deux scénarios, le premier lorsqu’une colonne de la table présente un type de données qui n’est pas pris en charge dans un index columnstore. La plupart des types de données sont pris en charge, mais XML, CLR, sql_variant, ntext, text et image ne le sont pas dans un index columnstore. Étant donné qu’un index columnstore cluster contient toujours toutes les colonnes de la table (puisqu’il est la table), un index non-cluster est la seule solution. Le second scénario est un index filtré. Ce scénario est utilisé dans une architecture nommée HTAP (Hybrid Transactional Analytic Processing), par l’intermédiaire de laquelle les données sont chargées dans la table sous-jacente tandis que les rapports sont exécutés sur la table. En filtrant l’index (généralement sur un champ de date), cette conception permet d’obtenir de bonnes performances d’insertion et de création de rapports.
Les index columnstore sont uniques dans leur mécanisme de stockage, du fait que chaque colonne de l’index est stockée indépendamment. Il offre un double avantage. Une requête qui utilise un index columnstore a uniquement besoin d’analyser les colonnes nécessaires pour satisfaire la requête, ce qui réduit le nombre total d’E/S effectuées et offre une meilleure compression, car les données de la même colonne sont susceptibles d’être de nature similaire.
Les index columnstore fonctionnent mieux sur les requêtes analytiques qui analysent de grandes quantités de données, telles que les tables de faits d’un entrepôt de données. À partir de SQL Server 2016, vous pouvez accroître un index columnstore avec un autre index non-cluster d’arbre B, ce qui peut s’avérer utile si certaines de vos requêtes effectuent des recherches sur des valeurs singleton.
Les index columnstore bénéficient également du mode d’exécution par lot, ce qui fait référence au traitement d’un ensemble de lignes (généralement autour de 900) en une fois contrairement au moteur de base de données qui traite ces lignes une par une. Au lieu de charger chaque enregistrement indépendamment et de les traiter, le moteur de requête procède au calcul dans ce groupe de 900 enregistrements. Ce modèle de traitement réduit considérablement le nombre d’instructions du processeur.
SELECT SUM(Sales) FROM SalesAmount;
Le mode batch peut apporter une augmentation de performances significative par rapport au traitement traditionnel des lignes. SQL Server 2019 inclut également le mode batch pour les données rowstore. Bien que le mode batch pour rowstore n’atteigne pas le même niveau de performances de lecture qu’un index columnstore, les requêtes analytiques peuvent enregistrer une amélioration des performances jusqu’à 5 fois supérieure.
L’autre avantage apporté par les index columnstore aux charges de travail d’entrepôt de données est un chemin de chargement optimisé pour les opérations d’insertion en bloc d’au moins 102 400 lignes. Si 102 400 constitue la valeur minimale à charger directement dans le columnstore, chaque collection de lignes, appelée rowgroup, peut atteindre quelque 1 024 000 lignes. Disposer d’un plus petit nombre de rowgroups, mais que ceux-ci soient plus complets, rend vos requêtes SELECT plus efficaces, car il y a moins de groupes de lignes à analyser pour récupérer les enregistrements demandés. Ces charges ont lieu en mémoire et sont directement chargées dans l’index. Pour les volumes plus petits, les données sont écrites dans une structure d’arbre B appelée magasin delta, et sont chargées de façon asynchrone dans l’index.
Dans cet exemple, les mêmes données sont chargées dans deux tables, FactResellerSales_CCI_Demo et FactResellerSales_Page_Demo. La table FactResellerSales_CCI_Demo a un index columnstore cluster, tandis que la table FactResellerSales_Page_Demo a un index d’arbre B cluster à page compressée et à deux colonnes. Comme vous pouvez le voir, chaque table charge 1 024 000 lignes à partir de la table FactResellerSalesXL_CCI. Quand SET STATISTICS TIME est ON, SQL Server suit le temps écoulé pendant l’exécution de la requête. Le chargement des données dans la table columnstore a duré 8 secondes environ, alors que le chargement dans la table à page compressée a demandé à peine 20 secondes. Dans cet exemple, toutes les lignes entrant dans l’index columnstore sont chargées dans un seul rowgroup.
Si vous chargez moins de 102 400 lignes de données dans un index columnstore en une seule opération, celui-ci est chargé dans une structure d’arbre B appelée magasin delta. Le moteur de base de données déplace ces données vers l’index columnstore à l’aide d’un processus asynchrone nommé le moteur de tuple. Le fait d’avoir des magasins delta ouverts peut affecter les performances de vos requêtes, car la lecture de ces enregistrements est moins efficace que la lecture faite à partir de l’index columnstore. Vous pouvez également réorganiser l’index avec l’option COMPRESS_ALL_ROW_GROUPS afin de forcer l’ajout et la compression des magasins delta dans les index columnstore.