Partager via

Index Columnstore dans l’entreposage de données

  • Article

S’applique à : Base de données SQL Azure SQL Database Azure SQL Managed Instance Analytics System (PDW) SQL Database dans Microsoft Fabric

Les index columnstore, conjointement avec le partitionnement, sont essentiels pour générer un entrepôt de données SQL Server. Cet article se concentre sur les cas d’usage clés et les exemples de conceptions d’entreposage de données avec le Moteur de base de données SQL.

Fonctionnalités clés pour l’entreposage de données

SQL Server 2016 (13.x) a introduit les fonctionnalités suivantes pour les améliorations des performances columnstore :

  • Always On prend en charge l’interrogation d’un index columnstore sur un réplica secondaire lisible.
  • MARS (Multiple Active Result Sets) prend en charge les index columnstore.
  • Une nouvelle vue de gestion dynamique sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) fournit des informations sur la résolution des problèmes de performances au niveau du groupe de lignes.
  • Les requêtes monothread sur les index columnstore peuvent s’exécuter en mode batch. Auparavant, seules les requêtes multi-threads pouvaient s’exécuter en mode batch.
  • L’opérateur SORT s’exécute en mode batch.
  • Les opérations DISTINCT multiples s’exécutent en mode batch.
  • Les agrégats de fenêtre s’exécutent maintenant en mode batch pour les niveaux de compatibilité de base de données 130 et supérieurs.
  • Agrégation en mode Push pour un traitement efficace des agrégats. Ceci est pris en charge sur tous les niveaux de compatibilité de base de données.
  • Prédicats de chaîne en mode Push pour un traitement efficace des prédicats de chaîne. Ceci est pris en charge sur tous les niveaux de compatibilité de base de données.
  • Isolation d’instantané pour les niveaux de compatibilité de base de données 130 et supérieurs.
  • Les index columnstore de cluster ordonnés ont été introduits avec SQL Server 2022 (16.x). Pour plus d’informations, consultez CREATE COLUMNSTORE INDEX and Performance tuning with ordered clustered columnstore indexes. Pour obtenir la disponibilité de l’index columnstore ordonné, consultez la disponibilité de l’index de colonne ordonné.

Pour plus d’informations sur les nouvelles fonctionnalités des versions et des plateformes de SQL Server et d’Azure SQL, consultez Nouveautés des index columnstore.

Amélioration des performances grâce à l’association d’index non cluster et columnstore

À compter de SQL Server 2016 (13.x), vous pouvez définir des index non cluster rowstore sur un index columnstore cluster.

Exemple : Améliorer l’efficacité des recherches dans la table avec un index non cluster

Pour améliorer l’efficacité des recherches dans la table dans un entrepôt de données, vous pouvez créer un non cluster conçu pour exécuter des requêtes plus efficaces avec les recherches dans la table. Par exemple, les requêtes pour rechercher des valeurs correspondantes ou retourner une petite plage de valeurs sont plus performantes avec un index B-tree qu’avec un index columnstore. Elles ne nécessitent pas une analyse complète de la table via l’index columnstore et elles retournent le résultat correct plus rapidement en effectuant une recherche binaire à l’aide d’un index B-tree.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.  
  
--Create the table  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int  
);  
GO  
  
--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;  
GO  
  
--Add a nonclustered index.  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

Exemple : Utiliser un index non cluster pour appliquer une contrainte de clé primaire sur une table columnstore

Par défaut, une table columnstore n’autorise pas une contrainte de clé primaire cluster. Maintenant, vous pouvez utiliser un index non cluster sur une table columnstore pour appliquer une contrainte de clé primaire. Une clé primaire est équivalente à une contrainte UNIQUE sur une colonne non NULL et SQL Server implémente une contrainte UNIQUE comme index non cluster. En combinant ces faits, l’exemple suivant définit une contrainte UNIQUE sur la valeur accountkey de colonne non NULL. Il en résulte un index non cluster qui applique une contrainte de clé primaire comme une contrainte UNIQUE sur une colonne non NULL.

Ensuite, la table est convertie en un index columnstore en cluster. Lors de la conversion, l’index non cluster est conservé. Il en résulte un index columnstore en cluster avec un index non cluster qui applique une contrainte de clé primaire. Étant donné que toute mise à jour ou insertion dans la table columnstore affecte également l’index non cluster, toutes les opérations qui violent la contrainte unique et la valeur non NULL entraînent l’échec de toute l’opération.

Il en résulte un index columnstore avec un index non cluster qui applique une contrainte de clé primaire sur les deux index.

--EXAMPLE: Enforce a primary key constraint on a columnstore table.   
  
--Create a rowstore table with a unique constraint.  
--The unique constraint is implemented as a nonclustered index.  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int,  
  
    CONSTRAINT uniq_account UNIQUE (AccountKey)  
);  
  
--Store the table as a columnstore.   
--The unique constraint is preserved as a nonclustered index on the columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account  
  
--By using the previous two steps, every row in the table meets the UNIQUE constraint  
--on a non-NULL column.  
--This has the same end-result as having a primary key constraint  
--All updates and inserts must meet the unique constraint on the nonclustered index or they will fail.  
  
--If desired, add a foreign key constraint on AccountKey.  
  
ALTER TABLE [dbo].[t_account]  
WITH CHECK ADD FOREIGN KEY([AccountKey]) REFERENCES my_dimension(Accountkey);

Amélioration des performances avec l’activation du verrouillage au niveau de la ligne et au niveau du groupe de lignes

Pour compléter l’index non cluster sur une fonctionnalité d’index columnstore, SQL Server 2016 (13.x) offre une fonctionnalité de verrouillage granulaire pour les opérations de sélection, de mise à jour et de suppression. Les requêtes peuvent être exécutées avec le verrouillage au niveau de la ligne sur les recherches d’index avec un index non cluster et avec le verrouillage au niveau du groupe de lignes sur les analyses de tables complètes avec l’index columnstore. Cela permet d’obtenir une concurrence en lecture/écriture plus élevée avec le verrouillage au niveau de la ligne et au niveau du groupe de ligne utilisé de façon appropriée.

--Granular locking example  
--Store table t_account as a columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account  
GO  
  
--Add a nonclustered index for use with this example  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);  
GO  
  
--Look at locking with access through the nonclustered index  
SET TRANSACTION ISOLATION LEVEL repeatable read;  
GO  
  
BEGIN TRAN  
    -- The query plan chooses a seek operation on the nonclustered index  
    -- and takes the row lock  
    SELECT * FROM t_account WHERE AccountKey = 100;  
COMMIT TRAN

Isolement d'instantané et isolements d’instantanés de lecture validée

Utilisez l’isolement d’instantané (SI) pour garantir la cohérence transactionnelle et les isolements d’instantanés de lecture validée (RCSI) pour garantir la cohérence au niveau de l’instruction des requêtes sur les index columnstore. Ainsi, les requêtes s’exécutent sans bloquer les enregistreurs de données. Ce comportement non bloquant réduit également sensiblement la probabilité de blocages pour les transactions complexes. Pour plus d’informations, consultez Isolement de capture instantanée dans SQL Server.

Contenu connexe