Bagikan melalui

Indeks penyimpan kolom di pergudangan data

  • Artikel

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)

Indeks penyimpan kolom, bersama dengan pemartisian, sangat penting untuk membangun gudang data SQL Server. Artikel ini berfokus pada kasus penggunaan utama dan contoh desain pergudangan data dengan SQL Database Engine.

Fitur utama untuk pergudangan data

SQL Server 2016 (13.x) memperkenalkan fitur-fitur ini untuk peningkatan performa penyimpan kolom:

  • Always On mendukung kueri indeks penyimpan kolom pada replika sekunder yang dapat dibaca.
  • Beberapa Active Result Sets (MARS) mendukung indeks penyimpan kolom.
  • Tampilan manajemen dinamis baru sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) menyediakan informasi pemecahan masalah performa di tingkat grup baris.
  • Kueri berutas tunggal pada indeks penyimpan kolom dapat berjalan dalam mode batch. Sebelumnya, hanya kueri multi-utas yang dapat berjalan dalam mode batch.
  • Operator SORT berjalan dalam mode batch.
  • Beberapa DISTINCT operasi berjalan dalam mode batch.
  • Agregat Jendela sekarang berjalan dalam mode batch untuk tingkat kompatibilitas database 130 dan yang lebih tinggi.
  • Agregat Pushdown untuk pemrosesan agregat yang efisien. Ini didukung pada semua tingkat kompatibilitas database.
  • String predikat pushdown untuk pemrosesan predikat string yang efisien. Ini didukung pada semua tingkat kompatibilitas database.
  • Isolasi rekam jepret untuk tingkat kompatibilitas database 130 dan yang lebih tinggi.
  • Indeks penyimpan kolom kluster yang diurutkan diperkenalkan dengan SQL Server 2022 (16.x). Untuk informasi selengkapnya, lihat MEMBUAT INDEKS PENYIMPAN KOLOM dan Penyetelan performa dengan indeks penyimpan kolom berkluster yang diurutkan. Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Ketersediaan indeks kolom yang diurutkan.

Untuk informasi selengkapnya tentang fitur baru dalam versi dan platform SQL Server dan Azure SQL, lihat Apa yang baru dalam indeks penyimpan kolom.

Meningkatkan performa dengan menggabungkan indeks nonclustered dan columnstore

Dimulai dengan SQL Server 2016 (13.x), Anda dapat menentukan indeks nonclustered rowstore pada indeks penyimpan kolom berkluster.

Contoh: Meningkatkan efisiensi pencarian tabel dengan indeks nonclustered

Untuk meningkatkan efisiensi pencarian tabel di gudang data, Anda dapat membuat indeks nonclustered yang dirancang untuk menjalankan kueri yang berkinerja terbaik dengan pencarian tabel. Misalnya, kueri yang mencari nilai yang cocok atau mengembalikan rentang kecil nilai berkinerja lebih baik terhadap indeks pohon B daripada indeks penyimpan kolom. Mereka tidak memerlukan pemindaian tabel penuh melalui indeks penyimpan kolom dan mengembalikan hasil yang benar lebih cepat dengan melakukan pencarian biner melalui indeks pohon B.

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

Contoh: Gunakan indeks non-kluster untuk memberlakukan batasan kunci utama pada tabel penyimpan kolom

Secara desain, tabel penyimpan kolom tidak mengizinkan batasan kunci primer berkluster. Sekarang Anda dapat menggunakan indeks non-kluster pada tabel penyimpan kolom untuk memberlakukan batasan kunci primer. Kunci primer setara dengan batasan UNIK pada kolom non-NULL, dan SQL Server menerapkan batasan UNIK sebagai indeks non-kluster. Menggabungkan fakta-fakta ini, contoh berikut mendefinisikan batasan UNIK pada accountkey kolom non-NULL. Hasilnya adalah indeks non-kluster yang memberlakukan batasan kunci primer sebagai batasan UNIK pada kolom non-NULL.

Selanjutnya, tabel dikonversi ke indeks penyimpan kolom berkluster. Selama konversi, indeks nonclustered tetap ada. Hasilnya adalah indeks penyimpan kolom berkluster dengan indeks non-kluster yang memberlakukan batasan kunci primer. Karena pembaruan atau penyisipan apa pun pada tabel penyimpan kolom juga memengaruhi indeks non-klusifikasi, semua operasi yang melanggar batasan unik dan non-NULL menyebabkan seluruh operasi gagal.

Hasilnya adalah indeks penyimpan kolom dengan indeks non-kluster yang memberlakukan batasan kunci utama pada kedua indeks.

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

Meningkatkan performa dengan mengaktifkan penguncian tingkat baris dan tingkat grup baris

Untuk melengkapi indeks nonclustered pada fitur indeks penyimpan kolom, SQL Server 2016 (13.x) menawarkan kemampuan penguncian terperinci untuk operasi pilih, perbarui, dan hapus. Kueri dapat berjalan dengan penguncian tingkat baris pada pencarian indeks terhadap indeks nonclustered dan penguncian tingkat grup baris pada pemindaian tabel penuh terhadap indeks penyimpan kolom. Gunakan ini untuk mencapai konkurensi baca/tulis yang lebih tinggi dengan menggunakan penguncian tingkat baris dan tingkat grup baris dengan tepat.

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

Isolasi rekam jepret dan isolasi rekam jepret yang diterapkan baca

Gunakan isolasi rekam jepret (SI) untuk menjamin konsistensi transaksional, dan isolasi rekam jepret yang diterapkan baca (RCSI) untuk menjamin konsistensi tingkat pernyataan untuk kueri pada indeks penyimpan kolom. Ini memungkinkan kueri berjalan tanpa memblokir penulis data. Perilaku non-blokir ini juga secara signifikan mengurangi kemungkinan kebuntuan untuk transaksi yang kompleks. Untuk informasi selengkapnya, lihat Isolasi Rekam Jepret di SQL Server.

Konten terkait