Sdílet prostřednictvím

Indexy založené na sloupcích v datových skladech

  • Článek

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)databáze SQL v Microsoft Fabric

Indexy columnstore společně s dělením jsou nezbytné pro vytvoření datového skladu SQL Serveru. Tento článek se zaměřuje na klíčové případy použití a příklady návrhů datových skladů pomocí databázového stroje SQL.

Klíčové funkce pro datové sklady

SQL Server 2016 (13.x) zavedl tyto funkce pro vylepšení výkonu columnstore:

  • AlwaysOn podporuje dotazování indexu columnstore na čitelné sekundární replice.
  • Několik aktivních sad výsledků (MARS) podporuje indexy sloupcového úložiště.
  • Nové zobrazení dynamické správy sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) poskytuje informace o řešení potíží s výkonem na úrovni skupiny řádků.
  • Jednovláknové dotazy na indexech columnstore mohou být spouštěny v dávkovém režimu. Dříve se v dávkovém režimu mohly spouštět pouze dotazy s více vlákny.
  • Operátor SORT běží v dávkovém režimu.
  • Více operací DISTINCT se spouští v dávkovém režimu.
  • Agregace oken se teď spouští v dávkovém režimu pro úroveň kompatibility databáze 130 a vyšší.
  • Agregační odsdílení pro efektivní zpracování agregací To je podporováno na všech úrovních kompatibility databáze.
  • Odsadení predikátu řetězců pro efektivní zpracování predikátů řetězců To je podporováno na všech úrovních kompatibility databáze.
  • Izolace snímků pro úroveň kompatibility databáze 130 a vyšší
  • Uspořádané clusterované indexy columnstore byly zavedeny s SQL Serverem 2022 (16.x). Další informace najdete v tématu CREATE COLUMNSTORE INDEX a Ladění výkonu seřazenými columnstore indexy. Informace o dostupnosti seřazeného indexu columnstore najdete v tématu Dostupnost indexu seřazených sloupců.

Další informace o nových funkcích ve verzích a platformách SQL Serveru a Azure SQL najdete v tématu Novinky v indexech columnstore.

Zvýšení výkonu kombinováním neclusterovaných indexů a indexů columnstore

Počínaje SQL Serverem 2016 (13.x) můžete definovat neclusterované indexy úložiště řádků v clusterovém indexu columnstore.

Příklad: Zlepšení efektivity hledání tabulek pomocí neclusterovaného indexu

Pokud chcete zvýšit efektivitu hledání tabulek v datovém skladu, můžete vytvořit neclusterovaný index navržený ke spouštění dotazů, které nejlépe fungují s hledáním tabulek. Například dotazy, které hledají odpovídající hodnoty, nebo vrací malou oblast hodnot, fungují lépe s indexem B-tree místo indexu columnstore. Nevyžadují úplné prohledávání tabulky pomocí columnstore indexu a vrací správný výsledek rychleji díky binárnímu vyhledávání v B-tree indexu.

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

Příklad: Použití neclusterovaného indexu k vynucení omezení primárního klíče v tabulce columnstore

Záměrně není možné, aby tabulka columnstore podporovala omezení na clusterovaný primární klíč. Teď můžete v tabulce columnstore použít neclusterovaný index k vynucení omezení primárního klíče. Primární klíč je ekvivalentem omezení UNIQUE ve sloupci bez hodnoty NULL a SQL Server implementuje omezení UNIQUE jako neclusterovaný index. Kombinováním těchto faktů definuje následující příklad omezení UNIQUE na sloupci accountkey, který není NULL. Výsledkem je neclusterovaný index, který vynucuje omezení primárního klíče jako omezení UNIQUE ve sloupci bez hodnoty NULL.

V dalším kroku se tabulka převede na clusterovaný columnstore index. Během převodu se neclusterovaný index zachová. Výsledkem je clusterovaný columnstore index a neclusterovaný index, který zajišťuje dodržení omezení primárního klíče. Vzhledem k tomu, že jakákoli aktualizace nebo vložení v tabulce columnstore má vliv také na neclusterovaný index, všechny operace, které porušují jedinečné omezení a hodnota non-NULL způsobí selhání celé operace.

Výsledkem je index columnstore s neclusterovaným indexem, který vynucuje omezení primárního klíče v obou indexech.

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

Zvýšení výkonu povolením uzamčení na úrovni řádků a skupin řádků

SQL Server 2016 (13.x) nabízí podrobné možnosti zamykání pro operace výběru, aktualizace a odstranění, které doplňují neklastrovaný index v rámci funkce columnstore indexu. Dotazy mohou běžet se zamykáním na úrovni řádků při prohledávání indexů v neklastrovaném indexu a se zamykáním na úrovni skupin řádků při celkovém prohledávání tabulky se sloupcovým indexem. Pomocí tohoto příkazu dosáhnete vyšší souběžnosti čtení a zápisu pomocí zamykání na úrovni řádků a skupiny řádků.

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

Izolace snímků a izolace snímků potvrzená pro čtení

Použijte izolaci snímků (SI) k zajištění transakční konzistence a izolaci snímků při potvrzeném čtení (RCSI) k zajištění konzistence na úrovni příkazů pro dotazy na indexy columnstore. To umožňuje spouštění dotazů bez blokování zapisovačů dat. Toto neblokující chování také výrazně snižuje pravděpodobnost zablokování u složitých transakcí. Další informace naleznete v tématu Izolace snímků v SYSTÉMU SQL Server.

Související obsah