Bagikan melalui

Indeks penyimpan kolom: gambaran umum

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL database di Microsoft Fabric

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 mencapai tingkat kompresi data hingga 10 kali lipat dibandingkan ukuran data yang tidak terkompresi. Dimulai dengan SQL Server 2016 (13.x) SP1, indeks kolom penyimpan memungkinkan analitik operasional: kemampuan untuk menjalankan analitik real-time yang bekerja pada beban kerja transaksional.

Pelajari tentang skenario terkait:

Apa itu indeks penyimpanan kolom?

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

Istilah dan konsep utama

Istilah dan konsep utama berikut dikaitkan dengan indeks penyimpan kolom.

Columnstore (teknologi penyimpanan kolom)

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

Rowstore

Rowstore adalah data yang secara logis diatur sebagai tabel dengan struktur baris dan kolom, dan disimpan secara fisik dalam format data per 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 penyimpanan 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 kolom memotong tabel menjadi grup baris, lalu memadatkan setiap grup baris secara berorientasi kolom. Jumlah baris dalam grup baris harus cukup besar untuk meningkatkan tingkat kompresi, namun cukup kecil untuk mendapatkan manfaat dari operasi di dalam memori.

Kelompok baris yang seluruh datanya telah dihapus beralih dari status TERKOMPRESI ke status BATU NISAN, dan kemudian dihapus oleh proses latar belakang yang disebut tuple-mover. Untuk informasi selengkapnya tentang status grup baris, lihat sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Petunjuk / Saran

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 OPEN yang lebih kecil yang telah ada selama beberapa waktu sesuai dengan ambang batas internal, atau menggabungkan grup baris yang telah dikompresi dari mana sejumlah besar baris dihapus. Ini meningkatkan kualitas indeks penyimpan kolom seiring 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 konteks 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 Rowgroup Delta

Grup baris delta adalah indeks pohon B berkluster yang khusus digunakan untuk indeks 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 yang bernama tuple-mover memeriksa grup baris yang 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 yang sebelumnya sudah dikompresi, grup baris delta tersebut beralih ke status "TOMBSTONE" untuk dihapus nantinya oleh tuple-mover ketika tidak ada lagi referensi.

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

Catatan

Dimulai dari SQL Server 2019 (15.x), fungsi tuple-mover dibantu oleh tugas penggabungan latar belakang yang secara otomatis mengompres grup baris delta TERBUKA yang lebih kecil yang telah ada selama jangka waktu tertentu berdasarkan ambang batas internal, atau menggabungkan grup baris TERKOMPRESI dari mana sejumlah besar baris telah dihapus. Ini meningkatkan kualitas indeks penyimpan kolom seiring waktu.

Deltastore

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

Selama pemrosesan muatan besar, sebagian besar baris langsung masuk ke penyimpanan 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 muatan 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 sebagian atau seluruh baris dan kolom dari tabel dasar. 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 dengan penyimpan kolom untuk analitik operasional waktu nyata.

Eksekusi mode kelompok

Eksekusi mode batch adalah metode pemrosesan kueri yang digunakan untuk memproses beberapa baris bersama-sama. Eksekusi mode batch terintegrasi erat dan dioptimalkan untuk format penyimpanan 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 kolom menawarkan kinerja yang jauh lebih baik dibandingkan 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 jenis beban kerja ini cenderung memerlukan pencarian tabel daripada 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 menghindari seluruh grup baris. Untuk informasi selengkapnya, lihat penyetelan kinerja dengan indeks penyimpan kolom yang diurutkan. Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Ketersediaan indeks penyimpan 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 analisis pada indeks penyimpan kolom dan transaksi pada indeks penyimpan baris secara bersamaan. Penyimpan kolom diperbarui saat data berubah dalam tabel penyimpan baris, 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 yang tidak unik gagal disisipkan ke dalam tabel rowstore, SQL Server tidak dapat menyisipkan nilainya ke dalam columnstore.

Indeks penyimpan kolom yang diurutkan

Dengan memungkinkan eliminasi segmen yang efisien, indeks penyimpan kolom yang diurutkan memberikan performa yang lebih cepat dengan melewatkan sejumlah besar data yang diurutkan yang tidak cocok dengan predikat kueri. Memuat data ke dalam indeks penyimpan kolom yang diurutkan dapat memakan waktu lebih lama daripada dalam indeks yang tidak diurutkan karena operasi pengurutan data, namun dengan kueri indeks penyimpan kolom yang diurutkan dapat berjalan lebih cepat setelahnya.

Ketersediaan indeks penyimpanan kolom yang diurutkan

Indeks penyimpan kolom yang diurutkan tersedia di platform berikut:

Platforma Indeks penyimpan kolom berkluster yang diurutkan Indeks penyimpan kolom tidak berkluster yang diurutkan
Database Azure SQL Ya Ya
Azure SQL Managed InstanceAUTD Ya Ya
Azure SQL Managed Instance2022 Ya Tidak
Database SQL di Microsoft Fabric Ya1 Ya
Pratinjau SQL Server 2025 (17.x) Ya Ya
SQL Server 2022 (16.x) Ya Tidak
Kumpulan SQL khusus di Azure Synapse Analytics Ya Tidak

AUTD Berlaku untuk Azure SQL Managed Instance yang dikonfigurasi dengan kebijakan pembaruan Always-up-to-date.
2022 Berlaku untuk Azure SQL Managed Instance yang dikonfigurasi dengan kebijakan pembaruan SQL Server 2022.
1Dalam database Fabric SQL, tabel dengan indeks penyimpan kolom berkluster tidak dicerminkan ke Fabric OneLake.

Metainformasi

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)

Tugas Artikel referensi Catatan
Buat tabel sebagai penyimpan kolom. BUAT TABEL (Transact-SQL) Secara default, saat Anda membuat tabel, tabel menggunakan rowstore sebagai format data yang mendasar. Dimulai dengan SQL Server 2016 (13.x), Anda dapat membuat tabel dengan indeks penyimpan kolom berkluster dengan menentukan INDEX ... CLUSTERED COLUMNSTORE opsi . Anda tidak perlu terlebih dahulu membuat tabel rowstore lalu mengonversinya menjadi penyimpan kolom.
Mengonversi tabel rowstore menjadi tabel columnstore. BUAT INDEKS PENYIMPAN KOLOM (Transact-SQL) Mengonversi heap atau B-tree yang ada menjadi kolomstore. Contoh menunjukkan cara menangani indeks yang ada dan juga nama indeks saat melakukan konversi ini.
Buat indeks penyimpan kolom non-klusster pada tabel rowstore. BUAT INDEKS PENYIMPAN KOLOM (Transact-SQL) Tabel penyimpanan baris dapat memiliki satu indeks penyimpan kolom nonclustered. Dimulai dengan SQL Server 2016 (13.x), indeks penyimpan kolom non-kluster dapat memiliki kondisi yang difilter. Contoh memperlihatkan sintaks dasar.
Mengonversi tabel penyimpan kolom menjadi penyimpan baris. 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 untuk pergudangan data. Indeks penyimpan kolom untuk pergudangan data Menjelaskan cara menggunakan indeks penyimpan kolom untuk kueri pergudangan data yang cepat.
Buat indeks untuk analitik operasional. Memulai dengan Columnstore untuk analitik operasional waktu nyata Menjelaskan cara membuat penyimpan kolom pelengkap dan indeks pohon B, sehingga kueri OLTP menggunakan indeks pohon B dan kueri analitik menggunakan indeks penyimpan kolom.
Gunakan indeks pohon B untuk memberlakukan batasan kunci utama pada indeks penyimpan kolom. Indeks penyimpan kolom untuk pergudangan data Memperlihatkan cara menggabungkan indeks B-tree dan columnstore untuk memberlakukan batasan kunci utama pada tabel columnstore.
Buat tabel yang dioptimalkan untuk 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.
Muat data ke dalam indeks penyimpan kolom. Penyimpan kolom mengindeks pemuatan data
Hilangkan indeks penyimpan kolom. DROP INDEX (Transact-SQL) Menghapus indeks penyimpan kolom menggunakan sintaks standar DROP INDEX yang dipakai oleh indeks pohon B. Menghilangkan indeks penyimpan kolom berkluster mengonversi tabel penyimpan kolom menjadi tumpukan.
Menghapus baris dari indeks penyimpan kolom. HAPUS (Transact-SQL) Gunakan DELETE (Transact-SQL) untuk menghapus baris.

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

baris columnstore: SQL Server menandai baris sebagai dihapus secara logis dan lalu menyisipkan baris yang diperbarui ke dalam deltastore.
baris deltastore: SQL Server memperbarui baris di deltastore.
Pertahankan indeks penyimpan kolom. UBAH INDEKS ... MEMBANGUN ULANG

MENGATUR ULANG indeks kolom penyimpanan

Metode pemeliharaan indeks: mengatur ulang dan membangun kembali		 Dalam kebanyakan kasus, ALTER INDEX ... REORGANIZE memberikan hasil yang mirip ALTER INDEX ... REBUILD dengan tetapi dengan konsumsi sumber daya yang lebih rendah. ALTER INDEX ... REORGANIZE selalu beroperasi secara daring. Kedua opsi mendefrigasi indeks penyimpan kolom dan memaksa baris di deltastore untuk masuk ke penyimpan kolom.

Dimulai dengan SQL Server 2019 (15.x), di Azure SQL Database, dan di Azure SQL Managed Instance, kualitas indeks penyimpan kolom dipertahankan secara otomatis, menghapus kebutuhan akan pemeliharaan indeks berkala dalam banyak kasus.

Konten terkait