Indeks Penyimpan Kolom Dijelaskan
Indeks penyimpan kolom SQL Server dalam memori menyimpan dan mengelola data dengan menggunakan penyimpanan data berbasis kolom dan pemrosesan kueri berbasis kolom. Indeks penyimpan kolom berfungsi dengan baik untuk beban kerja pergudangan data yang terutama melakukan pemuatan massal dan kueri baca-saja. Gunakan indeks penyimpan kolom untuk mencapai perolehan performa kueri hingga 10x atas penyimpanan berorientasi baris tradisional, dan kompresi data hingga 7x selama ukuran data yang tidak dikompresi.
Catatan
Kami melihat indeks penyimpan kolom berkluster sebagai standar untuk menyimpan tabel fakta pergudangan data besar, dan mengharapkannya akan digunakan dalam sebagian besar skenario pergudangan data. Karena indeks penyimpan kolom berkluster dapat diperbarui, beban kerja Anda dapat melakukan sejumlah besar operasi sisipkan, perbarui, dan hapus.
Konten
Dasar
Indeks penyimpan kolom adalah teknologi untuk menyimpan, mengambil, dan mengelola data dengan menggunakan format data kolom, yang disebut penyimpan kolom. SQL Server mendukung indeks penyimpan kolom berkluster dan non-kluster. Keduanya menggunakan teknologi penyimpan kolom dalam memori yang sama, tetapi mereka memang memiliki perbedaan tujuan dan fitur yang mereka dukung.
Keuntungan
Indeks penyimpan kolom berfungsi dengan baik untuk sebagian besar kueri baca-saja yang melakukan analisis pada himpunan data besar. Seringkali, ini adalah kueri untuk beban kerja pergudangan data. Indeks penyimpan kolom memberikan keuntungan performa tinggi untuk kueri yang menggunakan pemindaian tabel penuh, dan tidak cocok untuk kueri yang mencari data, mencari nilai tertentu.
Manfaat Indeks Penyimpan Kolom:
Kolom sering memiliki data serupa, yang menghasilkan tingkat kompresi tinggi.
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.
Mekanisme eksekusi kueri baru yang disebut eksekusi mode batch telah ditambahkan ke SQL Server yang mengurangi penggunaan CPU dalam jumlah besar. 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 sering memilih hanya beberapa kolom dari tabel, yang mengurangi total I/O dari media fisik.
Versi Penyimpan Kolom
SQL Server 2012, SQL Server 2012 Parallel Data Warehouse, dan SQL Server 2014 semuanya menggunakan indeks penyimpan kolom untuk mempercepat kueri gudang data umum. SQL Server 2012 memperkenalkan dua fitur baru: indeks penyimpan kolom nonkluster dan kemampuan eksekusi kueri berbasis vektor yang memproses data dalam unit yang disebut "batch." SQL Server 2014 memiliki fitur SQL Server 2012 ditambah indeks penyimpan kolom berkluster yang dapat diperbarui.
Karakteristik Utama
| Berlaku untuk: SQL Server 2014 hingga SQL Server 2019 (15.x). |
Dalam SQL Server, indeks penyimpan kolom berkluster:
Tersedia di edisi Enterprise, Developer, dan Evaluation.
Dapat diperbarui.
Adalah metode penyimpanan utama untuk seluruh tabel.
Tidak memiliki kolom kunci. Semua kolom disertakan kolom.
Adalah satu-satunya indeks pada tabel. Ini tidak dapat dikombinasikan dengan indeks lain.
Dapat dikonfigurasi untuk menggunakan penyimpan kolom atau kompresi pengarsipan penyimpan kolom.
Tidak menyimpan kolom secara fisik dalam urutan yang diurutkan. Sebaliknya, ia menyimpan data untuk meningkatkan kompresi dan performa.
| Berlaku untuk: SQL Server 2012 hingga SQL Server 2019 (15.x). |
Dalam SQL Server, indeks penyimpan kolom nonkluster:
Dapat mengindeks subset kolom dalam indeks atau timbunan berkluster. Misalnya, dapat mengindeks kolom yang sering digunakan.
Memerlukan penyimpanan tambahan untuk menyimpan salinan kolom dalam indeks.
Diperbarui dengan membangun kembali indeks atau beralih partisi masuk dan keluar. Ini tidak dapat diperbarui dengan menggunakan operasi DML seperti menyisipkan, memperbarui, dan menghapus.
Dapat dikombinasikan dengan indeks lain pada tabel.
Dapat dikonfigurasi untuk menggunakan penyimpan kolom atau kompresi pengarsipan penyimpan kolom.
Tidak menyimpan kolom secara fisik dalam urutan yang diurutkan. Sebaliknya, ia menyimpan data untuk meningkatkan kompresi dan performa. Pra-pengurutan data sebelum membuat indeks penyimpan kolom tidak diperlukan, tetapi dapat meningkatkan kompresi penyimpan kolom.
Konsep dan Istilah Utama
Istilah dan konsep utama berikut dikaitkan dengan indeks penyimpan kolom.
indeks penyimpan kolom Indeks penyimpan kolom adalah teknologi untuk menyimpan, mengambil, dan mengelola data dengan menggunakan format data kolumnar, yang disebut penyimpan kolom. SQL Server mendukung indeks penyimpan kolom berkluster dan non-kluster. Keduanya menggunakan teknologi penyimpan kolom dalam memori yang sama, tetapi mereka memang memiliki perbedaan tujuan dan fitur yang mereka dukung.
columnstore Penyimpan kolom adalah data yang secara logis diatur sebagai tabel dengan baris dan kolom, dan disimpan secara fisik dalam format data kolom.
rowstore A rowstore adalah data yang secara logis diatur sebagai tabel dengan baris dan kolom, lalu disimpan secara fisik dalam format data yang bijaksana baris. Ini telah menjadi cara tradisional untuk menyimpan data tabel relasional.
grup baris dan segmen kolom Untuk performa tinggi dan tingkat pemadatan tinggi, indeks penyimpan kolom mengiris tabel menjadi grup baris, yang disebut grup baris, lalu memadatkan setiap grup baris dengan cara yang bijaksana kolom. Jumlah baris dalam grup baris harus cukup besar untuk meningkatkan tingkat pemadatan, dan cukup kecil untuk mendapatkan manfaat dari operasi dalam memori.
grup baris Grup baris adalah sekelompok baris yang dikompresi ke dalam format penyimpan kolom secara bersamaan.
segmen kolom Segmen kolom adalah kolom data dari dalam grup baris.
Grup baris biasanya berisi jumlah maksimum baris per grup baris yaitu 1.048.576 baris.
Setiap grup baris berisi satu segmen kolom untuk setiap kolom dalam tabel.
Setiap segmen kolom dikompresi bersama-sama dan disimpan di media fisik.
indeks penyimpan kolom non-kluster Indeks penyimpan kolom non-kluster adalah indeks baca-saja yang dibuat pada indeks berkluster atau tabel tumpukan yang ada. Ini berisi salinan subset kolom, hingga dan termasuk semua kolom dalam tabel.. Tabel bersifat baca-saja sementara berisi indeks penyimpan kolom non-kluster.
Indeks penyimpan kolom non-kluster menyediakan cara untuk memiliki indeks penyimpan kolom untuk menjalankan kueri analisis sekaligus melakukan operasi baca-saja pada tabel asli.
indeks penyimpan kolom berkluster Indeks penyimpan kolom berkluster adalah penyimpanan fisik untuk seluruh tabel dan merupakan satu-satunya indeks untuk tabel. Indeks berkluster dapat diperbarui. Anda dapat melakukan operasi sisipkan, hapus, dan perbarui pada indeks dan Anda dapat memuat data secara massal ke dalam indeks.
Indeks
Untuk mengurangi fragmentasi segmen kolom dan meningkatkan performa, indeks penyimpan kolom mungkin menyimpan beberapa data untuk sementara ke dalam tabel rowstore, yang disebut deltastore, ditambah 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.
deltastore Digunakan dengan indeks penyimpan kolom berkluster saja, deltastore adalah tabel penyimpanan baris yang menyimpan baris hingga jumlah baris cukup besar untuk dipindahkan ke penyimpanan kolom. Deltastore digunakan dengan indeks penyimpan kolom berkluster untuk meningkatkan performa pemuatan dan operasi DML lainnya.
Selama beban massal yang 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. Ketika ini terjadi, 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.
Ketika deltastore mencapai jumlah baris maksimum, deltastore akan ditutup. Proses pemindahan tuple memeriksa grup baris tertutup. Ketika menemukan grup baris tertutup, grup baris tersebut memadatkannya dan menyimpannya ke dalam penyimpan kolom.
Memuat Data
Memuat Data ke dalam Indeks Penyimpan Kolom Non-Klusster
Untuk memuat data ke dalam indeks penyimpan kolom non-kluster, muat data terlebih dahulu ke dalam tabel rowstore tradisional yang disimpan sebagai indeks tumpukan atau berkluster, lalu buat indeks penyimpan kolom non-kluster dengan CREATE COLUMNSTORE INDEX (Transact-SQL).
Tabel dengan indeks penyimpan kolom non-kluster bersifat baca-saja hingga indeks dihilangkan atau dinonaktifkan. Untuk memperbarui tabel dan indeks penyimpan kolom nonkluster, Anda dapat mengalihkan partisi masuk dan keluar. Anda juga dapat menonaktifkan indeks, memperbarui tabel, lalu membangun ulang indeks.
Untuk informasi selengkapnya, lihat Menggunakan Indeks Penyimpan Kolom Non-Klusster
Memuat Data ke dalam Indeks Penyimpan Kolom Berkluster
Seperti yang disarankan diagram, untuk memuat data ke dalam indeks penyimpan kolom berkluster, SQL Server:
Menyisipkan grup baris ukuran maksimum langsung ke dalam penyimpan kolom. Saat data dimuat, SQL Server menetapkan baris data dalam urutan first-serve first-come ke dalam grup baris terbuka.
Untuk setiap grup baris, setelah mencapai ukuran maksimum, SQL Server:
Menandai grup baris sebagai TERTUTUP.
Melewati deltastore.
Memadatkan setiap segmen kolom dengan grup baris dengan pemadatan penyimpan kolom.
Secara fisik menyimpan setiap segmen kolom terkompresi ke dalam penyimpan kolom.
Sisipkan baris yang tersisa ke dalam penyimpan kolom atau deltastore sebagai berikut:
Jika jumlah baris memenuhi baris minimum per persyaratan grup baris, baris ditambahkan ke penyimpan kolom.
Jika jumlah baris kurang dari baris minimum per grup baris, baris ditambahkan ke deltastore.
Untuk informasi selengkapnya tentang tugas dan proses deltastore, lihat Menggunakan Indeks Penyimpan Kolom Berkluster
Tips Performa
Rencanakan memori yang cukup untuk membuat indeks penyimpan kolom secara paralel
Membuat indeks penyimpan kolom secara default adalah operasi paralel, kecuali memori tersebut dibatasi. Membuat indeks secara paralel membutuhkan lebih banyak memori daripada membuat indeks secara serial. Ketika ada memori yang cukup, membuat indeks penyimpan kolom mengambil urutan 1,5 kali selama membangun pohon B pada kolom yang sama.
Memori yang diperlukan untuk membuat indeks penyimpan kolom tergantung pada jumlah kolom, jumlah kolom string, tingkat paralelisme (DOP), dan karakteristik data. Misalnya, jika tabel Anda memiliki kurang dari satu juta baris, SQL Server hanya akan menggunakan satu utas untuk membuat indeks penyimpan kolom.
Jika tabel Anda memiliki lebih dari satu juta baris, tetapi SQL Server tidak bisa mendapatkan peruntukan memori yang cukup besar untuk membuat indeks menggunakan MAXDOP, SQL Server akan secara otomatis mengurangi MAXDOP sesuai kebutuhan agar sesuai dengan peruntukan memori yang tersedia. Di dalam beberapa kasus, DOP harus dikurangi menjadi satu agar dapat membangun indeks di bawah memori yang dibatasi.
Tugas dan Topik Terkait
Indeks Penyimpan Kolom Nonkluster
Untuk tugas umum, lihat Menggunakan Indeks Penyimpan Kolom Nonkluster.
ALTER INDEX (Transact-SQL) dengan REBUILD.
Indeks Penyimpan Kolom Berkluster
Untuk tugas umum, lihat Menggunakan Indeks Penyimpan Kolom Berkluster.
ALTER INDEX (Transact-SQL) dengan REBUILD atau REORGANIZE.
Metadata
Semua kolom dalam indeks penyimpan kolom disimpan dalam metadata sebagai kolom yang disertakan. Indeks penyimpan kolom tidak memiliki kolom kunci.