Columnstore Indexes Described
L’index columnstore en mémoire SQL Server stocke et gère les données à l’aide du stockage de données basé sur les colonnes et du traitement des requêtes basées sur les colonnes. Les index columnstore fonctionnent bien pour les charges de travail de stockage de données qui effectuent principalement des chargements en masse et des requêtes en lecture seule. Utilisez l’index columnstore pour atteindre des performances des requêtes pouvant être multipliées par 10 par rapport au stockage orienté lignes traditionnel, et une compression de données multipliée par 7 par rapport à la taille des données non compressées.
Notes
Nous considérons l'index columnstore cluster comme étant la norme pour le stockage de grandes tables de faits de stockage des données, et nous pensons qu'il va être utilisé dans la plupart des scénarios de stockage des données. Étant donné que l'index columnstore cluster est modifiable, votre charge de travail peut exécuter un grand nombre d'insertions, mises à jour, et suppressions.
Contents
Concepts de base
Un columnstore index est une technologie permettant de stocker, extraire et gérer les données à l'aide d'un format de données en colonnes, appelé columnstore. SQL Server prend en charge les index columnstore cluster et non cluster. Les deux utilisent la même technologie columnstore en mémoire, mais sont différents en ce qui concerne leur but et les fonctionnalités qu'ils prennent en charge.
Avantages
Les index columnstore fonctionnent bien pour la plupart des requêtes en lecture seule qui effectuent des analyses sur des ensembles de données volumineux. Bien souvent, il s'agit de requêtes destinées à des charges de travail de stockage de données. Les index columnstore offrent des gains de performances importants pour les requêtes qui utilisent des analyses de table complètes, et ne conviennent pas pour les requêtes qui effectuent des opérations de recherche de données, notamment qui recherchent une valeur particulière.
Avantages de l'index columnstore :
Les colonnes contenant souvent des données similaires, les taux de compression sont élevés.
Les taux de compression élevés améliorent les performances des requêtes en utilisant un plus faible encombrement en mémoire. À son tour, les performances des requêtes peuvent s’améliorer, car SQL Server peut effectuer davantage d’opérations de requête et de données en mémoire.
Un nouveau mécanisme d'exécution de requête, appelé « exécution en mode batch » a été ajouté à SQL Server pour réduire considérablement l'utilisation de l'UC. L'exécution en mode batch est étroitement intégrée avec (et optimisée pour) le format de stockage columnstore. L'exécution en mode batch est parfois appelée « exécution vectorielle ou vectorisée ».
Les requêtes sélectionnent souvent seulement quelques colonnes d'une table, ce qui réduit les E/S totales à partir du support physique.
Versions de columnstore
SQL Server 2012, SQL Server 2012 Parallel Data Warehouse, et SQL Server 2014 utilisent tous des index columnstore pour accélérer les requêtes d'entrepôts de données communs. SQL Server 2012 a introduit deux nouvelles fonctionnalités : un index columnstore non cluster et une fonctionnalité d’exécution de requête basée sur des vecteurs qui traite les données en unités appelées « lots ». SQL Server 2014 a les fonctionnalités de SQL Server 2012 ainsi que les index columnstore en cluster mis à jour.
Caractéristiques clés
| S’applique à : SQL Server 2014 à SQL Server 2019 (15.x). |
Dans SQL Server, un index columnstore cluster :
Est disponible dans les éditions Enterprise, Developer et Evaluation.
Peut être mis à jour.
Est la méthode de stockage principale de la table entière.
N'a pas de colonnes clés. Toutes les colonnes sont des colonnes incluses.
Est le seul index sur la table. Ne peut pas être associé à d'autres index.
Peut être configuré pour utiliser columnstore ou la compression d'archivage columnstore.
Ne stocke pas physiquement les colonnes dans un ordre de tri. Au lieu de cela, il stocke les données pour améliorer la compression et les performances.
| S’applique à : SQL Server 2012 à SQL Server 2019 (15.x). |
Dans SQL Server, un index columnstore non cluster :
Peut indexer un sous-ensemble de colonnes dans l'index cluster ou le segment de mémoire. Par exemple, il peut indexer les colonnes utilisées fréquemment.
Nécessite un stockage supplémentaire pour stocker une copie des colonnes dans l'index.
Est mis à jour en régénérant l’index ou en basculant les partitions. Il n’est pas modifiable à l’aide des opérations DML telles que l’insertion, la mise à jour et la suppression.
Peut être associé à d'autres index sur la table.
Peut être configuré pour utiliser columnstore ou la compression d'archivage columnstore.
Ne stocke pas physiquement les colonnes dans un ordre de tri. Au lieu de cela, il stocke les données pour améliorer la compression et les performances. Le prétri des données avant de créer l'index columnstore n'est pas requis, mais cela peut améliorer la compression columnstore.
Concepts et termes clés
Les termes et concepts clés suivants sont associés aux index columnstore.
index columnstore Un index columnstore est une technologie permettant de stocker, de récupérer et de gérer des données à l’aide d’un format de données columnar, appelé columnstore. SQL Server prend en charge les index columnstore cluster et non cluster. Les deux utilisent la même technologie columnstore en mémoire, mais sont différents en ce qui concerne leur but et les fonctionnalités qu'ils prennent en charge.
columnstore Un columnstore est des données qui sont organisées logiquement en tant que table avec des lignes et des colonnes, et qui sont physiquement stockées dans un format de données au niveau des colonnes.
rowstore Un magasin de lignes est des données qui sont organisées logiquement en tant que table avec des lignes et des colonnes, puis stockées physiquement dans un format de données au niveau des lignes. Il s'agit de la méthode traditionnelle de stockage des données de table relationnelles.
rowgroups et segments de colonne Pour des performances élevées et des taux de compression élevés, l’index columnstore découpe la table en groupes de lignes, appelés groupes de lignes, puis compresse chaque groupe de lignes de manière en colonne. Le nombre de lignes dans le groupe de lignes doit être assez grand pour améliorer le taux de compression, et assez petit tirer parti des opérations en mémoire.
groupe de lignes Un groupe de lignes est un groupe de lignes qui sont compressées au format columnstore en même temps.
segment de colonne Un segment de colonne est une colonne de données provenant du rowgroup.
Un rowgroup contient généralement le nombre maximal de lignes par rowgroup qui est de 1 048 576 lignes.
Chaque rowgroup contient un segment de colonne pour chaque colonne dans la table.
Chaque segment de colonne est compressé et stocké sur un support physique.
index columnstore non cluster Un index columnstore non cluster est un index en lecture seule créé sur un index cluster ou une table de tas existante. Il contient une copie d'un sous-ensemble de colonnes, et peut contenir toutes les colonnes de la table. La table est en lecture seule alors qu’elle contient un index columnstore non cluster.
Un index columnstore non cluster permet d'avoir un index columnstore pour exécuter les requêtes d'analyse tout en exécutant des opérations en lecture seule sur la table d'origine.
index columnstore cluster Un index columnstore cluster est le stockage physique de l’ensemble de la table et est le seul index de la table. L'index cluster peut être mis à jour. Effectuez des opérations d'insertion, suppression, et mise à jour sur l'index et chargez en masse des données dans l'index.
Pour réduire la fragmentation des segments de colonne et améliorer les performances, l'index columnstore peut stocker des données temporaires dans une table rowstore, appelée un deltastore, plus un arbre B d'ID pour les lignes supprimées. Les opérations deltastore sont effectuées en coulisse. Pour retourner des résultats de requête corrects, l'index columnstore cluster associe les résultats de columnstore et de deltastore.
deltastore Utilisé avec les index columnstore en cluster uniquement, un deltastore est une table rowstore qui stocke les lignes jusqu’à ce que le nombre de lignes soit suffisamment grand pour être déplacé dans le columnstore. Un deltastore est utilisé avec des index columnstore cluster pour améliorer les performances du chargement et d'autres opérations DML.
Lors d'un chargement en masse important, la plupart des lignes sont directement placées dans le columnstore sans passer par le deltastore. Certaines lignes à la fin du chargement en masse peuvent être en trop petit nombre pour atteindre la taille minimale d'un rowgroup, qui est de 102 400 lignes. Dans ce cas, les lignes finales sont placées dans le deltastore plutôt que dans le columnstore. Pour les petits chargements en masse de taille inférieure à 102 400 lignes, toutes les lignes vont directement au deltastore.
Lorsque le deltastore atteint le nombre maximal de lignes, il est fermé. Un processus de déplacement de tuple vérifie les groupes de lignes fermés. Lorsqu'il trouve un rowgroup fermé, il le compresse et le stocke dans le columnstore.
Chargement des données
Chargement de données dans un index columnstore non cluster
Pour charger des données dans un index columnstore non cluster, chargez d’abord des données dans une table rowstore traditionnelle stockée sous la forme d’un tas ou d’un index cluster, puis créez l’index columnstore non cluster avec CREATE COLUMNSTORE INDEX (Transact-SQL) .
Une table avec un index columnstore non cluster est en lecture seule jusqu'à ce que l'index soit désactivé ou supprimé. Pour mettre à jour la table et l’index columnstore non cluster, vous pouvez basculer les partitions dans et vers l’extérieur. Vous pouvez également désactiver l’index, mettre à jour la table, puis reconstruire l’index.
Pour plus d'informations, consultez Using Nonclustered Columnstore Indexes
Chargement de données dans un index columnstore cluster
Comme le suggère le diagramme, pour charger des données dans un index columnstore cluster, SQL Server :
Insère les rowgroups de taille maximale directement dans le columnstore. À mesure que les données sont chargées, SQL Server affecte les lignes de données dans l’ordre premier arrivé, premier servi, dans un rowgroup ouvert.
Pour chaque rowgroup, une fois qu’il a atteint la taille maximale, SQL Server :
Marque le rowgroup comme étant CLOSED.
Ignore le deltastore.
Compresse chaque segment de colonne avec le rowgroup à l'aide de la compression columnstore.
Stocke physiquement chaque segment de colonne compressé dans le columnstore.
Insère les lignes restantes dans le columnstore ou le deltastore comme suit :
Si le nombre de lignes répond à l'exigence de nombre de lignes minimal par rowgroup, les lignes sont ajoutées au columnstore.
Si le nombre de lignes est inférieur au nombre de lignes minimal par rowgroup, les lignes sont ajoutées au deltastore.
Pour plus d'informations sur les tâches et les processus deltastore, consultez Using Clustered Columnstore Indexes
Conseils relatifs aux performances
Planifiez suffisamment de mémoire pour créer des index columnstore en parallèle
La création d'un index columnstore par défaut est une opération parallèle tant que la mémoire est contrainte. La création de l'index en parallèle requiert plus de mémoire que la création de l'index en série. Lorsqu'il y a suffisamment de mémoire, la création d'un index columnstore prend 1,5 fois plus de temps que créer un arbre B sur les mêmes colonnes.
La mémoire requise pour créer un index columnstore dépend du nombre de colonnes, du nombre de colonnes de chaîne, du degré de parallélisme (DOP), et des caractéristiques des données. Par exemple, si la table a moins d’un million de lignes, SQL Server utilise un seul thread pour créer l’index columnstore.
Si votre table a plus d'un million de lignes, mais SQL Server ne peut pas obtenir suffisamment d'allocation de mémoire pour créer l'index à l'aide de MAXDOP, il réduira automatiquement MAXDOP de sorte qu'il tienne dans l'allocation de mémoire disponible. Dans certains cas, le DOP doit être réduit à un pour pouvoir créer l'index sous une mémoire contrainte.
Tâches et rubriques connexes
Index columnstore non cluster
Pour les tâches courantes, consultez Using Nonclustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) avec REBUILD.
Index columnstore cluster
Pour les tâches courantes, consultez Using Clustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) avec REBUILD ou REORGANIZE.
Métadonnées
Toutes les colonnes dans un index columnstore sont stockées dans les métadonnées en tant que colonnes incluses. L'index columnstore n'a pas de colonnes clés.