Bagikan melalui

Indeks Penyimpan Kolom: Ringkasan

  • Artikel

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

Indeks penyimpan kolom adalah standar untuk menyimpan dan meminta tabel fakta pergudangan data besar. Indeks ini menggunakan penyimpanan data berbasis kolom dan pemrosesan kueri untuk mencapai keuntungan hingga 10 kali performa kueri di gudang data Anda dibandingkan dengan penyimpanan berorientasi baris tradisional. Anda juga dapat memperoleh keuntungan hingga 10 kali pemadatan data atas ukuran data yang tidak dipadatkan. Dimulai dengan SQL Server 2016 (13.x) SP1, indeks penyimpan kolom mengaktifkan analitik operasional: kemampuan untuk menjalankan analitik real-time yang bekerja pada beban kerja transaksional.

Pelajari tentang skenario terkait:

Apa itu indeks penyimpan kolom?

Indeks penyimpan kolom adalah teknologi untuk menyimpan, mengambil, dan mengelola data dengan menggunakan format data kolom, yang disebut penyimpan kolom.

Istilah dan konsep utama

Istilah dan konsep utama berikut dikaitkan dengan indeks penyimpan kolom.

Columnstore

Penyimpan kolom adalah data yang secara logis diatur sebagai tabel dengan baris dan kolom, dan disimpan secara fisik dalam format data yang bijaksana kolom.

Rowstore

Rowstore adalah data yang secara logis diatur sebagai tabel dengan baris dan kolom, dan disimpan secara fisik dalam format data yang bijaksana baris. Format ini adalah cara tradisional untuk menyimpan data tabel relasional. Di SQL Server, rowstore mengacu pada tabel di mana format penyimpanan data yang mendasar adalah tumpukan, indeks berkluster, atau tabel yang dioptimalkan memori.

Catatan

Dalam diskusi tentang indeks penyimpan kolom, istilah rowstore dan columnstore digunakan untuk menekankan format untuk penyimpanan data.

Grup Baris

Grup baris adalah sekelompok baris yang dikompresi ke dalam format penyimpan kolom secara bersamaan. Grup baris biasanya berisi jumlah maksimum baris per grup baris, yaitu 1.048.576 baris.

Untuk performa tinggi dan tingkat kompresi tinggi, indeks penyimpan kolom mengiris tabel ke dalam grup baris, lalu memadatkan setiap grup baris dengan cara yang bijaksana kolom. Jumlah baris dalam grup baris harus cukup besar untuk meningkatkan tingkat kompresi, dan cukup kecil untuk mendapatkan manfaat dari operasi dalam memori.

Grup baris dari mana semua data telah dihapus transisi dari TERKOMPRESI ke status TOMBSTONE, dan kemudian dihapus oleh proses latar belakang bernama tuple-mover. Untuk informasi selengkapnya tentang status grup baris, lihat sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Tip

Memiliki terlalu banyak grup baris kecil mengurangi kualitas indeks penyimpan kolom. Hingga SQL Server 2017 (14.x), operasi reorganisasi diperlukan untuk menggabungkan grup baris TERKOMPRESI yang lebih kecil, mengikuti kebijakan ambang batas internal yang menentukan cara menghapus baris yang dihapus dan menggabungkan grup baris terkompresi.
Dimulai dengan SQL Server 2019 (15.x), tugas penggabungan latar belakang juga berfungsi untuk menggabungkan grup baris TERKOMPRESI dari tempat sejumlah besar baris telah dihapus.
Setelah menggabungkan grup baris yang lebih kecil, kualitas indeks harus ditingkatkan.

Catatan

Dimulai dengan SQL Server 2019 (15.x), Azure SQL Database, Azure SQL Managed Instance, dan kumpulan SQL khusus di Azure Synapse Analytics, tuple-mover dibantu oleh tugas penggabungan latar belakang yang secara otomatis mengompresi grup baris delta TERBUKA yang lebih kecil yang telah ada selama beberapa waktu seperti yang ditentukan oleh ambang internal, atau menggabungkan grup baris TERKOMPRESI dari tempat sejumlah besar baris telah dihapus. Ini meningkatkan kualitas indeks penyimpan kolom dari waktu ke waktu.

Segmen kolom

Segmen kolom adalah kolom data dari dalam grup baris.

  • Setiap grup baris berisi satu segmen kolom untuk setiap kolom dalam tabel.
  • Setiap segmen kolom dikompresi bersama-sama dan disimpan di media fisik.
  • Ada metadata dengan setiap segmen untuk memungkinkan eliminasi segmen dengan cepat tanpa membacanya.

Diagram logis segmen kolom. Setiap kolom memiliki satu segmen kolom per grup baris.

Indeks penyimpan kolom dalam kluster

Indeks penyimpan kolom berkluster adalah penyimpanan fisik untuk seluruh tabel.

Diagram logis indeks penyimpan kolom berkluster. Menyertakan segmen kolom terkompresi ditambah baris dalam indeks, tetapi tidak di penyimpan kolom.

Untuk mengurangi fragmentasi segmen kolom dan meningkatkan performa, indeks penyimpan kolom mungkin menyimpan beberapa data untuk sementara ke dalam indeks berkluster yang disebut deltastore dan daftar B-tree ID untuk baris yang dihapus. Operasi deltastore ditangani di belakang layar. Untuk mengembalikan hasil kueri yang benar, indeks penyimpan kolom berkluster menggabungkan hasil kueri dari penyimpan kolom dan deltastore.

Catatan

Dokumentasi menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

Grup baris Delta

Grup baris delta adalah indeks pohon B berkluster yang hanya digunakan dengan indeks penyimpan kolom. Ini meningkatkan kompresi dan performa penyimpan kolom dengan menyimpan baris hingga jumlah baris mencapai ambang batas (1.048.576 baris) dan kemudian dipindahkan ke penyimpanan kolom.

Ketika grup baris delta mencapai jumlah baris maksimum, grup baris tersebut beralih dari status TERBUKA ke TERTUTUP. Proses latar belakang bernama tuple-mover memeriksa grup baris tertutup. Jika proses menemukan grup baris tertutup, proses ini memadatkan grup baris delta dan menyimpannya ke dalam penyimpan kolom sebagai grup baris TERKOMPRESI.

Ketika grup baris delta telah dikompresi, grup baris delta yang ada beralih ke status TOMBSTONE untuk dihapus nanti oleh tuple-mover ketika tidak ada referensi ke sana.

Untuk informasi selengkapnya tentang status grup baris, lihat sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Catatan

Dimulai dengan SQL Server 2019 (15.x), tuple-mover dibantu oleh tugas penggabungan latar belakang yang secara otomatis memadatkan grup baris delta TERBUKA yang lebih kecil yang telah ada selama beberapa waktu seperti yang ditentukan oleh ambang internal, atau menggabungkan grup baris TERKOMPRESI dari tempat sejumlah besar baris telah dihapus. Ini meningkatkan kualitas indeks penyimpan kolom dari waktu ke waktu.

Deltastore

Indeks penyimpan kolom dapat memiliki lebih dari satu grup baris delta. Semua grup baris delta secara kolektif disebut deltastore.

Selama beban massal besar, sebagian besar baris langsung masuk ke penyimpan kolom tanpa melewati deltastore. Beberapa baris di akhir beban massal mungkin terlalu sedikit jumlahnya untuk memenuhi ukuran minimum grup baris, yaitu 102.400 baris. Akibatnya, baris akhir masuk ke deltastore alih-alih penyimpan kolom. Untuk beban massal kecil dengan kurang dari 102.400 baris, semua baris langsung masuk ke deltastore.

Indeks penyimpan kolom nonclustered

Indeks penyimpan kolom non-kluster dan indeks penyimpan kolom berkluster berfungsi sama. Perbedaannya adalah bahwa indeks non-kluster adalah indeks sekunder yang dibuat pada tabel rowstore, tetapi indeks penyimpan kolom berkluster adalah penyimpanan utama untuk seluruh tabel.

Indeks nonclustered berisi salinan bagian atau semua baris dan kolom dalam tabel yang mendasar. Indeks didefinisikan sebagai satu atau beberapa kolom tabel dan memiliki kondisi opsional yang memfilter baris.

Indeks penyimpan kolom non-kluster memungkinkan analitik operasional real time di mana beban kerja OLTP menggunakan indeks berkluster yang mendasarinya sementara analitik berjalan bersamaan pada indeks penyimpan kolom. Untuk informasi selengkapnya, lihat Mulai menggunakan penyimpan kolom untuk analitik operasional real time.

Eksekusi mode batch

Eksekusi mode batch adalah metode pemrosesan kueri yang digunakan untuk memproses beberapa baris bersama-sama. Eksekusi mode batch terintegrasi erat dengan, dan dioptimalkan di sekitar, format penyimpanan penyimpan kolom. Eksekusi mode batch terkadang dikenal sebagai eksekusi berbasis vektor atau vektor . Kueri pada indeks penyimpan kolom menggunakan eksekusi mode batch, yang meningkatkan performa kueri biasanya sebanyak dua hingga empat kali. Untuk informasi selengkapnya, lihat Panduan arsitektur pemrosesan kueri.

Mengapa saya harus menggunakan indeks penyimpan kolom?

Indeks penyimpan kolom dapat memberikan tingkat kompresi data yang sangat tinggi, biasanya sebesar 10 kali, untuk mengurangi biaya penyimpanan gudang data Anda secara signifikan. Untuk analitik, indeks penyimpan kolom menawarkan urutan performa yang lebih baik dari indeks pohon B. Indeks penyimpan kolom adalah format penyimpanan data pilihan untuk beban kerja pergudangan data dan analitik. Dimulai dengan SQL Server 2016 (13.x), Anda dapat menggunakan indeks penyimpan kolom untuk analitik real time pada beban kerja operasional Anda.

Alasan mengapa indeks penyimpan kolom sangat cepat:

  • Kolom menyimpan nilai dari domain yang sama dan biasanya memiliki nilai serupa, yang mengakibatkan tingkat kompresi tinggi. Hambatan I/O dalam sistem Anda diminimalkan atau dihilangkan, dan jejak memori berkurang secara signifikan.

  • Tingkat kompresi tinggi meningkatkan performa kueri dengan menggunakan jejak dalam memori yang lebih kecil. Pada gilirannya, performa kueri dapat meningkat karena SQL Server dapat melakukan lebih banyak operasi kueri dan data dalam memori.

  • Eksekusi batch meningkatkan performa kueri, biasanya dua hingga empat kali, dengan memproses beberapa baris bersama-sama.

  • Kueri sering memilih hanya beberapa kolom dari tabel, yang mengurangi total I/O dari media fisik.

Kapan saya harus menggunakan indeks penyimpan kolom?

Kasus penggunaan yang direkomendasikan:

Bagaimana cara memilih antara indeks rowstore dan indeks penyimpan kolom?

Indeks rowstore berkinerja terbaik pada kueri yang mencari data, saat mencari nilai tertentu, atau untuk kueri pada rentang kecil nilai. Gunakan indeks rowstore dengan beban kerja transaksi karena cenderung memerlukan sebagian besar pencarian tabel alih-alih pemindaian tabel.

Indeks penyimpan kolom memberikan perolehan performa tinggi untuk kueri analitik yang memindai data dalam jumlah besar, terutama pada tabel besar. Gunakan indeks penyimpan kolom pada beban kerja pergudangan data dan analitik, terutama pada tabel fakta, karena cenderung memerlukan pemindaian tabel penuh daripada pencarian tabel.

Indeks penyimpan kolom berkluster yang diurutkan meningkatkan performa untuk kueri berdasarkan predikat kolom yang diurutkan. Indeks penyimpan kolom yang diurutkan dapat meningkatkan eliminasi grup baris, yang dapat memberikan peningkatan performa dengan melewati grup baris sama sekali. Untuk informasi selengkapnya, lihat Penyetelan performa dengan indeks penyimpan kolom berkluster yang diurutkan. Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Ketersediaan indeks kolom yang diurutkan.

Bisakah saya menggabungkan rowstore dan columnstore pada tabel yang sama?

Ya. Dimulai dengan SQL Server 2016 (13.x), Anda dapat membuat indeks penyimpan kolom noncluster yang dapat diperbarui pada tabel rowstore. Indeks penyimpan kolom menyimpan salinan kolom yang dipilih, sehingga Anda memerlukan ruang ekstra untuk data ini, tetapi data yang dipilih dikompresi rata-rata 10 kali. Anda dapat menjalankan analitik pada indeks penyimpan kolom dan transaksi pada indeks rowstore secara bersamaan. Penyimpan kolom diperbarui saat data berubah dalam tabel rowstore, sehingga kedua indeks bekerja terhadap data yang sama.

Dimulai dengan SQL Server 2016 (13.x), Anda dapat memiliki satu atau beberapa indeks rowstore nonclustered pada indeks penyimpan kolom dan melakukan pencarian tabel yang efisien di penyimpan kolom yang mendasar. Opsi lain juga tersedia. Misalnya, Anda dapat memberlakukan batasan kunci utama dengan menggunakan batasan UNIK pada tabel rowstore. Karena nilai nonunique gagal disisipkan ke dalam tabel rowstore, SQL Server tidak dapat menyisipkan nilai ke dalam penyimpan kolom.

Indeks penyimpan kolom yang diurutkan

Dengan mengaktifkan eliminasi segmen yang efisien, indeks penyimpan kolom terkluster (CCI) yang diurutkan memberikan performa yang jauh lebih cepat dengan melewati sejumlah besar data yang diurutkan yang tidak cocok dengan predikat kueri. Memuat data ke dalam tabel CCI yang diurutkan dapat memakan waktu lebih lama dari tabel CCI yang tidak diurutkan karena operasi pengurutan data, namun kueri dapat berjalan lebih cepat setelahnya dengan CCI yang diurutkan.

Ketersediaan indeks penyimpan kolom yang diurutkan

Pertama kali diperkenalkan dengan SQL Server 2022 (16.x), indeks penyimpan kolom yang diurutkan tersedia di platform berikut.

Platform Indeks penyimpan kolom berkluster yang diurutkan Indeks penyimpan kolom berkluster yang diurutkan
Database Azure SQL Ya Ya
SQL Server 2022 (16.x) Ya Tidak
Instans Terkelola Azure SQL Ya Ya
Kumpulan SQL khusus di Azure Synapse Analytics Ya Tidak

Metadata

Semua kolom dalam indeks penyimpan kolom disimpan dalam metadata sebagai kolom yang disertakan. Indeks penyimpan kolom tidak memiliki kolom kunci.

sys.indexes (Transact-SQL)

sys.index_columns (T-SQL)

sys.partitions (Transact-SQL)

sys.internal_partitions (T-SQL)

sys.column_store_segments (T-SQL)

sys.column_store_dictionaries (T-SQL)

sys.column_store_row_groups (T-SQL)

sys.dm_db_column_store_row_group_operational_stats (T-SQL)

sys.dm_db_column_store_row_group_physical_stats (T-SQL)

sys.dm_column_store_object_pool (T-SQL)

sys.dm_db_column_store_row_group_operational_stats (T-SQL)

sys.dm_db_index_operational_stats (T-SQL)

sys.dm_db_index_physical_stats (T-SQL)

Semua tabel relasional, kecuali Anda menentukannya sebagai indeks penyimpan kolom berkluster, gunakan rowstore sebagai format data yang mendasar. CREATE TABLE membuat tabel rowstore kecuali Anda menentukan WITH CLUSTERED COLUMNSTORE INDEX opsi .

Saat Anda membuat tabel dengan pernyataan, CREATE TABLE Anda bisa membuat tabel sebagai penyimpan kolom dengan menentukan WITH CLUSTERED COLUMNSTORE INDEX opsi . Jika Anda sudah memiliki tabel rowstore dan ingin mengonversinya ke penyimpan kolom, Anda bisa menggunakan pernyataan tersebut CREATE COLUMNSTORE INDEX .

Tugas Artikel referensi Catatan
Buat tabel sebagai penyimpan kolom. BUAT TABEL (Transact-SQL) Dimulai dengan SQL Server 2016 (13.x), Anda dapat membuat tabel sebagai indeks penyimpan kolom berkluster. Anda tidak perlu terlebih dahulu membuat tabel rowstore lalu mengonversinya menjadi penyimpan kolom.
Buat tabel yang dioptimalkan memori dengan indeks penyimpan kolom. BUAT TABEL (Transact-SQL) Dimulai dengan SQL Server 2016 (13.x), Anda dapat membuat tabel yang dioptimalkan memori dengan indeks penyimpan kolom. Indeks penyimpan kolom juga dapat ditambahkan setelah tabel dibuat dengan menggunakan sintaks.ALTER TABLE ADD INDEX
Mengonversi tabel rowstore menjadi penyimpan kolom. BUAT INDEKS PENYIMPAN KOLOM (Transact-SQL) Mengonversi timbunan atau B-tree yang ada menjadi penyimpan kolom. Contoh menunjukkan cara menangani indeks yang ada dan juga nama indeks saat melakukan konversi ini.
Mengonversi tabel penyimpan kolom menjadi rowstore. MEMBUAT INDEKS TERKLUSTER (Transact-SQL) atau Mengonversi tabel penyimpan kolom kembali ke tumpukan rowstore Biasanya konversi ini tidak diperlukan, tetapi mungkin ada saat-saat ketika Anda perlu mengonversi. Contoh menunjukkan cara mengonversi penyimpan kolom menjadi tumpuk atau indeks berkluster.
Buat indeks penyimpan kolom pada tabel rowstore. BUAT INDEKS PENYIMPAN KOLOM (Transact-SQL) Tabel rowstore dapat memiliki satu indeks penyimpan kolom. Dimulai dengan SQL Server 2016 (13.x), indeks penyimpan kolom dapat memiliki kondisi yang difilter. Contoh memperlihatkan sintaks dasar.
Buat indeks berkinerja untuk analitik operasional. Mulai menggunakan penyimpan kolom untuk analitik operasional real time Menjelaskan cara membuat penyimpan kolom pelengkap dan indeks pohon B, sehingga kueri OLTP menggunakan indeks pohon B dan kueri analitik menggunakan indeks penyimpan kolom.
Buat indeks penyimpan kolom berkinerja untuk pergudangan data. Indeks penyimpan kolom untuk pergudangan data Menjelaskan cara menggunakan indeks pohon B pada tabel penyimpan kolom untuk membuat kueri pergudangan data berkinerja.
Gunakan indeks pohon B untuk memberlakukan batasan kunci utama pada indeks penyimpan kolom. Indeks penyimpan kolom untuk pergudangan data Memperlihatkan cara menggabungkan indeks pohon B dan penyimpan kolom untuk memberlakukan batasan kunci utama pada indeks penyimpan kolom.
Hilangkan indeks penyimpan kolom. DROP INDEX (Transact-SQL) Menghilangkan indeks penyimpan kolom menggunakan sintaks standar DROP INDEX yang digunakan indeks pohon B. Menghilangkan indeks penyimpan kolom berkluster mengonversi tabel penyimpan kolom menjadi tumpukan.
Menghapus baris dari indeks penyimpan kolom. DELETE (Transact-SQL) Gunakan DELETE (Transact-SQL) untuk menghapus baris.

baris penyimpan kolom: SQL Server menandai baris sebagai dihapus secara logis, tetapi tidak mengklaim kembali penyimpanan fisik untuk baris hingga indeks dibangun kembali.
baris deltastore: SQL Server secara logis dan menghapus baris secara fisik.
Perbarui baris di indeks penyimpan kolom. UPDATE (Transact-SQL) Gunakan UPDATE (Transact-SQL) untuk memperbarui baris.

baris penyimpan kolom: SQL Server menandai baris sebagai dihapus secara logis lalu menyisipkan baris yang diperbarui ke dalam deltastore.
baris deltastore: SQL Server memperbarui baris di deltastore.
Muat data ke dalam indeks penyimpan kolom. Penyimpan kolom mengindeks pemuatan data
Paksa semua baris di deltastore untuk masuk ke penyimpan kolom. UBAH INDEKS (Transact-SQL) ... REBUILD

Optimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya		 ALTER INDEXREBUILD dengan opsi memaksa semua baris untuk masuk ke penyimpan kolom.
Defragmentasi indeks penyimpan kolom. ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE defragments indeks penyimpan kolom secara online.
Gabungkan tabel dengan indeks penyimpan kolom. MERGE (Transact-SQL)

Konten terkait