Partager via

Index columnstore dans l’entrepôt de données

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)Base de données SQL 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 :

  • Les groupes de disponibilité Always On prennent 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.
  • Toutes les requêtes sur les index columnstore peuvent s’exécuter en mode batch. Auparavant, seules les requêtes parallèles pouvaient s’exécuter en mode batch.
  • Les opérateurs Sort, Distinct Sort et Distinct s’exécutent en mode batch.
  • Les agrégats de fenêtres s'exécutent désormais en mode batch pour le niveau de compatibilité de base de données 130 et supérieur.
  • Poussée d'agrégats 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 cluster ordonnés ont été introduits avec SQL Server 2022 (16.x). Pour plus d’informations, consultez CREATE COLUMNSTORE INDEX et Optimisation des performances avec des index columnstore ordonnés. Pour la disponibilité des index columnstore ordonnés, consultez la disponibilité des index columnstore ordonnés.

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

À partir de SQL Server 2016 (13.x), vous pouvez créer des index d'entrepôt de lignes non-cluster sur un index d'entrepôt de colonnes clusterisé.

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. Ils ne nécessitent pas d’analyse complète de l’index columnstore et retournent le résultat correct plus rapidement en effectuant une recherche binaire via 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
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--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

Étant donné qu'une table ne peut avoir qu'un seul index clusterisé, une table avec un index columnstore clusterisé ne peut pas avoir de contrainte de clé primaire clusterisée. Pour créer une contrainte de clé primaire sur une table columnstore, vous devez la déclarer comme non clusterisé.

L'exemple suivant crée une table avec une contrainte de clé primaire non clusterisée, puis crée un index columnstore clusterisé sur la table. Étant donné que toute insertion ou mise à jour sur la table columnstore modifie également l’index non-clusterisé, toute opération qui viole la contrainte de clé primaire échoue dans son intégralité.

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

Améliorer les performances en activant le verrouillage au niveau des lignes et des groupes de lignes.

Pour compléter la fonctionnalité d'index non clusterisé sur un index columnstore, SQL Server 2016 (13.x) offre une capacité de verrouillage granulaire pour les opérations SELECT, UPDATE, et DELETE. 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

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

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 de capture instantanée et isolement de capture instantanée Read Committed

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 Niveaux d'isolement basés sur le contrôle de version des lignes dans le Moteur de base de données Microsoft SQL Server.

Contenu connexe

  • Last updated on