Oszlopalapú indexek az adattárházakban

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példányElemzési platformrendszer (PDW)SQL-adatbázis a Microsoft Fabricben

Az oszlopcentrikus indexek a particionálással együtt nélkülözhetetlenek egy SQL Server-adattárház létrehozásához. Ez a cikk az SQL Database Engine-beli adattárháztervek legfontosabb használati eseteire és példáira összpontosít.

Az adattárház fő funkciói

Az SQL Server 2016 (13.x) a következő funkciókat vezette be az oszlopcentrikus teljesítményfejlesztésekhez:

  • Az Always On rendelkezésre állási csoportok támogatják egy oszlop tárolású index lekérdezését egy olvasható másodlagos replikán.
  • Több aktív eredményhalmaz (MARS) támogatja az oszlopcentrikus indexeket.
  • Egy új dinamikus felügyeleti nézet sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) a sorcsoport szintjén nyújt teljesítménybeli hibaelhárítási információkat.
  • Az oszlopcentrikus indexeken lévő összes lekérdezés kötegelt módban futtatható. Korábban csak a párhuzamos lekérdezések futtathatóak voltak kötegelt módban.
  • A Rendezés, a Distinct rendezés és a Distinct operátorok kötegelt módban futnak.
  • Az ablakösszesítések mostantól kötegelt módban futnak a 130-as és újabb adatbázis-kompatibilitási szint esetén.
  • Az összesítések leküldése az összesítések hatékony feldolgozásához. Ez minden adatbázis-kompatibilitási szinten támogatott.
  • Sztring predikátumok leküldése a hatékony feldolgozás érdekében. Ez minden adatbázis-kompatibilitási szinten támogatott.
  • Pillanatkép-izoláció a 130-as vagy annál magasabb adatbázis-kompatibilitási szinthez.
  • A rendezett fürtözött oszlopos indexeket az SQL Server 2022-vel (16.x) vezették be. További információért lásd: CREATE COLUMNSTORE INDEX és Teljesítményhangolás rendezett oszlopcentrikus indexekkel. A rendezett oszlopstore index elérhetőségét lásd a Rendezett oszlopstore index elérhetőségecímű részben.

További információ az SQL Server és az Azure SQL verzióinak és platformjainak új funkcióiról: Az oszlopcentrikus indexek újdonságai.

A teljesítmény növelése érdekében nem halmozott és oszlopalapú indexek kombinálása

Az SQL Server 2016 -tól kezdve (13.x) sorkataszter nélküli indexeket hozhat létre fürtözött oszlopcentrikus indexeken.

Példa: A táblakeresések hatékonyságának javítása nem klaszterezett indexszel

Az adattárházban a táblakeresések hatékonyságának javítása érdekében létrehozhat egy nemclustered indexet, amely olyan lekérdezések futtatására lett kialakítva, amelyek a táblakeresésekkel a legjobban teljesítenek. Például azok a lekérdezések, amelyek egyező értékeket keresnek, vagy egy kis értéktartományt ad vissza, jobban teljesítnek egy B-fa indexen, mint oszlopcentrikus indexen. Nem igénylik az oszlopcentrikus index teljes vizsgálatát, és gyorsabban visszaadják a megfelelő eredményt egy bináris kereséssel egy B-fa indexen keresztül.

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

Példa: Használjon nem csoportosított indexet az oszlopcentrikus tábla elsődleges kulcskorlátozásának érvényesítésére.

Mivel egy táblának legfeljebb egy fürtözött indexe lehet, a fürtözött oszlopcentrikus indexet tartalmazó táblák nem tartalmazhatnak fürtözött elsődlegeskulcs-korlátozást. Ha elsődleges kulcskorlátozást szeretne létrehozni egy oszlopcentrikus táblán, deklarálnia kell azt nem klaszterezettként.

Az alábbi példa létrehoz egy táblát, amely nem klaszterezett elsődleges kulcskorláttal rendelkezik, majd létrehoz egy klaszterezett oszloptárolós indexet a táblán. Mivel az oszlopcentrikus tábla bármely beszúrása vagy frissítése a nemclustered indexet is módosítja, az elsődleges kulcskorlátot megsértő összes művelet a teljes művelet meghiúsulását okozza.

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

A teljesítmény javítása a sorszintű és a sorcsoportszintű zárolás engedélyezésével

Az oszlopalapú index funkciójának nemclustered indexének kiegészítéseként az SQL Server 2016 (13.x) részletes zárolási képességet biztosít a SELECT, UPDATE, és DELETE műveletekhez. A lekérdezések sorszintű zárolással futtathatók az indexkereséseken a nem klaszteres index használatával, vagyis sorcsoportszintű zárolással az oszloptár index teljes táblavizsgálatán. Ezzel nagyobb olvasási/írási egyidejűséget érhet el a sorszintű és a sorcsoportszintű zárolás megfelelő használatával.

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

Pillanatképek elkülönítése és olvasási célú pillanatkép-elkülönítés

A pillanatkép-elkülönítés (SI) használatával garantálja a tranzakciós konzisztenciát, és az olvasásra kötelezett pillanatkép-elkülönítés (RCSI) garantálja az utasításszintű konzisztenciát az oszlopcentrikus indexeken lévő lekérdezésekhez. Ez lehetővé teszi, hogy a lekérdezések az adatírók blokkolása nélkül fussanak. Ez a nem blokkoló viselkedés jelentősen csökkenti az összetett tranzakciók holtpontjainak valószínűségét is. További információ: Sorverzió-alapú elkülönítési szintek az adatbázismotorban.

Kapcsolódó tartalom

  • Last updated on