Bagikan melalui

Arsitektur indeks dan panduan desain

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

Merancang indeks yang efisien adalah kunci untuk mencapai performa database dan aplikasi yang baik. Kurangnya indeks, pengindeksan berlebihan, atau indeks yang dirancang dengan buruk adalah sumber teratas masalah performa database.

Panduan ini menjelaskan arsitektur dan dasar-dasar indeks, dan menyediakan praktik terbaik untuk membantu Anda merancang indeks yang efektif untuk memenuhi kebutuhan aplikasi Anda.

Untuk informasi selengkapnya tentang jenis indeks yang tersedia, lihat Indeks.

Panduan ini mencakup jenis indeks berikut:

Format penyimpanan utama Jenis indeks
Rowstore berbasis disk
Clustered
Nonclustered
Unique
Filtered
Columnstore
Penyimpan kolom berkluster
Penyimpan kolom berkluster
Memory-optimized
Hash
Nonclustered yang dioptimalkan memori

Untuk informasi tentang indeks XML, lihat Indeks XML (SQL Server) dan indeks XML Selektif (SXI).

Untuk informasi tentang indeks spasial, lihat Gambaran Umum Indeks Spasial.

Untuk informasi tentang indeks teks lengkap, lihat Mengisi Indeks Teks Lengkap.

Dasar-dasar indeks

Pikirkan tentang buku biasa: di akhir buku, ada indeks yang membantu menemukan informasi dengan cepat dalam buku. Indeks adalah daftar kata kunci yang diurutkan dan di samping setiap kata kunci adalah sekumpulan nomor halaman yang menunjuk ke halaman tempat setiap kata kunci dapat ditemukan.

Indeks rowstore serupa: ini adalah daftar nilai yang diurutkan dan untuk setiap nilai ada penunjuk ke halaman data tempat nilai ini berada. Indeks itu sendiri juga disimpan di halaman, disebut sebagai halaman indeks. Dalam buku biasa, jika indeks mencakup beberapa halaman dan Anda harus menemukan penunjuk ke semua halaman yang berisi kata SQL misalnya, Anda harus membalik-balik dari awal indeks sampai Anda menemukan halaman indeks yang mencantumkan kata kunci SQL. Dari sana, Anda mengikuti pointer ke semua halaman buku. Ini dapat dioptimalkan lebih lanjut jika pada awal indeks, Anda membuat satu halaman yang berisi daftar alfabet tempat setiap huruf dapat ditemukan. Misalnya: "A hingga D - halaman 121", "E hingga G - halaman 122" dan sebagainya. Halaman tambahan ini akan menghilangkan langkah daun melalui indeks untuk menemukan tempat awal. Halaman seperti itu tidak ada dalam buku biasa, tetapi memang ada di indeks rowstore. Halaman tunggal ini disebut sebagai halaman akar indeks. Halaman akar adalah halaman awal struktur pohon yang digunakan oleh indeks. Mengikuti analogi pohon, halaman akhir yang berisi penunjuk ke data aktual disebut sebagai "halaman daun" pohon.

Indeks adalah struktur pada disk atau dalam memori yang terkait dengan tabel atau tampilan yang mempercepat pengambilan baris dari tabel atau tampilan. Indeks rowstore berisi kunci yang dibangun dari nilai dalam satu atau beberapa kolom dalam tabel atau tampilan. Untuk indeks rowstore, kunci ini disimpan dalam struktur pohon (pohon B+) yang memungkinkan Mesin Database menemukan baris yang terkait dengan nilai kunci dengan cepat dan efisien.

Indeks rowstore menyimpan data yang diatur secara logis sebagai tabel dengan baris dan kolom, dan disimpan secara fisik dalam format data per baris yang disebut rowstore1. Ada cara alternatif untuk menyimpan data column-wise, yang disebut columnstore.

Desain indeks yang tepat untuk database dan beban kerjanya adalah tindakan penyeimbangan yang kompleks antara kecepatan kueri, biaya pembaruan indeks, dan biaya penyimpanan. Indeks rowstore berbasis disk yang sempit, atau indeks dengan beberapa kolom di kunci indeks, memerlukan lebih sedikit ruang penyimpanan dan overhead pembaruan yang lebih kecil. Indeks yang lebih lebar, di sisi lain, mungkin meningkatkan jumlah kueri lebih banyak. Anda mungkin harus bereksperimen dengan beberapa desain yang berbeda sebelum menemukan kumpulan indeks yang paling efisien. Seiring berkembangnya aplikasi, indeks mungkin perlu berubah untuk mempertahankan performa yang optimal. Indeks dapat ditambahkan, dimodifikasi, dan dihapus tanpa memengaruhi skema database atau desain aplikasi. Oleh karena itu, Anda tidak boleh ragu untuk bereksperimen dengan indeks yang berbeda.

Pengoptimal kueri di Mesin Database biasanya memilih indeks yang paling efektif untuk menjalankan kueri. Untuk melihat indeks mana yang digunakan pengoptimal kueri untuk kueri tertentu, di SQL Server Management Studio, pada menu Kueri , pilih Tampilkan Perkiraan Rencana Eksekusi atau Sertakan Rencana Eksekusi Aktual.

Jangan selalu menyamakan penggunaan indeks dengan performa yang baik, dan performa yang baik dengan penggunaan indeks yang efisien. Jika menggunakan indeks selalu membantu menghasilkan performa terbaik, pekerjaan pengoptimal kueri akan sederhana. Pada kenyataannya, pilihan indeks yang salah dapat menyebabkan performa yang kurang dari optimal. Oleh karena itu, tugas pengoptimal kueri adalah memilih indeks atau kombinasi indeks hanya ketika itu meningkatkan performa, dan menghindari pengambilalihan yang terindeks ketika menghambat performa.

Kesalahan desain umum adalah membuat banyak indeks secara spekulatif untuk "memberikan pilihan pengoptimal". Overindexing yang dihasilkan memperlambat modifikasi data dan dapat menyebabkan masalah konkurensi.

1 Rowstore telah menjadi cara tradisional untuk menyimpan data tabel relasional. Rowstore mengacu pada tabel di mana format penyimpanan data yang mendasar adalah tumpukan, pohon B+ (indeks berkluster), atau tabel yang dioptimalkan memori. Rowstore berbasis disk mengecualikan tabel yang dioptimalkan memori.

Tugas desain indeks

Tugas-tugas berikut membentuk strategi kami yang direkomendasikan untuk merancang indeks:

  1. Pahami karakteristik database dan aplikasi.

    Misalnya, dalam database pemrosesan transaksi online (OLTP) dengan seringnya modifikasi data yang harus mempertahankan throughput tinggi, beberapa indeks rowstore dengan rentang yang sempit yang ditargetkan untuk kueri paling kritis akan menjadi desain indeks awal yang baik. Untuk throughput yang sangat tinggi, pertimbangkan tabel dan indeks yang dioptimalkan memori, yang menyediakan desain bebas kunci dan kait. Untuk informasi selengkapnya, lihat Panduan desain indeks nonclustered yang dioptimalkan memori dan Panduan desain indeks hash di panduan ini.

    Sebaliknya, untuk database analitik atau pergudangan data (OLAP) yang harus memproses himpunan data yang sangat besar dengan cepat, menggunakan indeks penyimpan kolom berkluster akan sangat sesuai. Untuk informasi selengkapnya, lihat Indeks penyimpan kolom: gambaran umum atau arsitektur indeks Penyimpan kolom dalam panduan ini.

  2. Pahami karakteristik kueri yang paling sering digunakan.

    Misalnya, mengetahui bahwa kueri yang sering digunakan bergabung dengan dua tabel atau lebih membantu Anda menentukan kumpulan indeks untuk tabel ini.

  3. Pahami distribusi data dalam kolom yang digunakan dalam predikat kueri.

    Misalnya, indeks mungkin berguna untuk kolom dengan banyak nilai data yang berbeda, tetapi kurang untuk kolom dengan banyak nilai duplikat. Untuk kolom dengan banyak NULL atau yang memiliki subset data yang ditentukan dengan baik, Anda dapat menggunakan indeks yang difilter. Untuk informasi selengkapnya, lihat Panduan desain indeks yang difilter dalam panduan ini.

  4. Tentukan opsi indeks mana yang dapat meningkatkan performa.

    Misalnya, membuat indeks berkluster pada tabel besar yang ada dapat memperoleh manfaat dari ONLINE opsi indeks. Opsi ini ONLINE memungkinkan aktivitas bersamaan pada data yang mendasar untuk dilanjutkan saat indeks sedang dibuat atau dibangun kembali. Menggunakan kompresi data pada tingkat baris atau halaman dapat meningkatkan performa dengan mengurangi I/O dan jejak memori pada indeks. Untuk informasi selengkapnya, lihat MEMBUAT INDEKS.

  5. Periksa indeks yang ada pada tabel untuk mencegah pembuatan indeks duplikat atau sangat mirip.

    Seringkali lebih baik memodifikasi indeks yang ada daripada membuat indeks baru tetapi sebagian besar duplikat. Misalnya, pertimbangkan untuk menambahkan satu atau dua kolom tambahan yang disertakan ke indeks yang sudah ada, alih-alih membuat indeks baru dengan kolom ini. Ini sangat relevan ketika Anda menyetel indeks nonclustered dengan saran indeks yang hilang, atau jika Anda menggunakan Database Engine Tuning Advisor, di mana Anda mungkin ditawarkan variasi indeks serupa pada tabel dan kolom yang sama.

Panduan desain indeks umum

Memahami karakteristik database, kueri, dan kolom tabel Anda dapat membantu Anda merancang indeks optimal pada awalnya dan memodifikasi desain seiring berkembangnya aplikasi Anda.

Pertimbangan database

Saat Anda merancang indeks, pertimbangkan panduan database berikut:

  • Banyaknya indeks pada tabel memengaruhi kinerja INSERT, UPDATE, DELETE, dan MERGE karena data dalam indeks mungkin harus berubah saat data dalam tabel berubah. Misalnya, jika kolom digunakan dalam beberapa indeks dan Anda menjalankan UPDATE pernyataan yang memodifikasi data kolom tersebut, setiap indeks yang berisi kolom tersebut juga harus diperbarui.

    • Hindari pengindeksan berlebihan tabel yang sangat diperbarui dan pertahankan indeks tetap sempit, yaitu, dengan kolom seserang mungkin.

    • Anda dapat menambahkan lebih banyak indeks pada tabel dengan modifikasi data yang sedikit tetapi memiliki volume data yang besar. Untuk tabel-tabel tersebut, berbagai indeks dapat membantu meningkatkan performa kueri sambil mempertahankan overhead pembaruan indeks yang tetap dalam batas yang dapat diterima. Namun, jangan membuat indeks secara spekulatif. Pantau penggunaan indeks, dan hapus indeks yang tidak digunakan dari waktu ke waktu.

  • Mengindeks tabel kecil mungkin tidak optimal karena dapat memakan waktu lebih lama mesin database untuk melintasi indeks yang mencari data daripada melakukan pemindaian tabel dasar. Oleh karena itu, indeks pada tabel kecil mungkin tidak pernah digunakan, tetapi masih harus diperbarui karena data dalam tabel diperbarui.

  • Indeks pada tampilan dapat memberikan keuntungan performa yang signifikan saat tampilan berisi agregasi dan/atau gabungan. Untuk informasi selengkapnya, lihat Membuat tampilan terindeks.

  • Database pada replika utama di Azure SQL Database secara otomatis menghasilkan rekomendasi performa penasihat database untuk indeks. Anda dapat mengaktifkan penyetelan indeks otomatis secara opsional.

  • Penyimpanan Kueri membantu mengidentifikasi kueri dengan performa suboptimal dan menyediakan riwayat rencana eksekusi kueri yang memungkinkan Anda melihat indeks yang dipilih oleh pengoptimal. Anda dapat menggunakan data ini untuk membuat perubahan penyetelan indeks Anda paling berdampak dengan berfokus pada kueri yang paling sering dan memakan sumber daya.

Pertimbangan kueri

Saat Anda mendesain indeks, pertimbangkan panduan kueri berikut:

  • Buat indeks non-kluster pada kolom yang sering digunakan dalam predikat dan ekspresi gabungan dalam kueri. Ini adalah kolom SARGable Anda. Namun, Anda harus menghindari penambahan kolom yang tidak perlu ke indeks. Menambahkan terlalu banyak kolom indeks dapat berdampak buruk pada ruang disk dan performa pembaruan indeks.

    Istilah SARGable dalam database relasional mengacupada predikat kemampuan ument ARGearch Syang dapat menggunakan indeks untuk mempercepat eksekusi kueri. Untuk informasi selengkapnya, lihat Panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

    Tip

    Selalu pastikan bahwa indeks yang Anda buat benar-benar digunakan oleh beban kerja kueri. Hilangkan indeks yang tidak digunakan.

    Statistik penggunaan indeks tersedia dalam sys.dm_db_index_usage_stats dan sys.dm_db_index_operational_stats.

  • Mencakup indeks dapat meningkatkan performa kueri karena semua data yang diperlukan untuk memenuhi persyaratan kueri ada dalam indeks itu sendiri. Artinya, hanya halaman indeks, dan bukan halaman data tabel atau indeks berkluster, yang diperlukan untuk mengambil data yang diminta; oleh karena itu, mengurangi I/O disk secara keseluruhan. Misalnya, kueri kolom A dan B pada tabel yang memiliki indeks komposit yang dibuat pada kolom A, B, dan C dapat mengambil data yang ditentukan dari indeks saja.

    Note

    Indeks yang mencakup adalah indeks non-klusifikasi yang memenuhi semua akses data oleh kueri secara langsung tanpa mengakses tabel dasar.

    Indeks tersebut memiliki semua kolom SARGable yang diperlukan di kunci indeks, dan kolom yang tidak dapat di-SARGable sebagai kolom yang disertakan . Ini berarti bahwa semua kolom yang diperlukan oleh kueri, baik dalam klausa WHERE, JOIN, dan GROUP BY, atau dalam klausa SELECT atau UPDATE, ada dalam indeks.

    Ada kemungkinan I/O yang diperlukan bisa jauh lebih sedikit untuk melakukan kueri, jika indeksnya yang cukup sempit jika dibandingkan dengan baris dan kolom dalam tabel itu sendiri, artinya indeks tersebut adalah subset kecil dari seluruh kolom.

    Pertimbangkan untuk menggunakan indeks saat mengambil sebagian kecil dari tabel besar, di mana sebagian kecil tersebut didefinisikan oleh predikat yang tetap.

    Hindari membuat indeks penutup dengan terlalu banyak kolom karena mengurangi manfaatnya saat meningkatkan penyimpanan database, I/O, dan jejak memori.

  • Tulis kueri yang menyisipkan atau memodifikasi baris sebanyak mungkin dalam satu pernyataan, alih-alih menggunakan beberapa kueri untuk memperbarui baris yang sama. Ini mengurangi overhead pembaruan indeks.

Pertimbangan kolom

Saat Anda mendesain indeks, pertimbangkan panduan kolom berikut:

  • Jaga agar panjang kunci indeks tetap pendek, terutama untuk indeks berkluster.

  • Kolom yang merupakan tipe data ntext, teks, gambar, varchar(maks), nvarchar(max), varbinary(max), json, dan vektor tidak dapat ditentukan sebagai kolom kunci indeks. Namun, kolom dengan jenis data ini dapat ditambahkan ke indeks non-klusster sebagai kolom indeks non-kunci (disertakan). Untuk informasi selengkapnya, lihat bagian Gunakan kolom terlampir pada indeks nonclustered dalam panduan ini.

  • Periksa keunikan kolom. Indeks unik alih-alih indeks nonunique pada kolom kunci yang sama menyediakan informasi tambahan untuk pengoptimal kueri yang membuat indeks lebih berguna. Untuk informasi selengkapnya, lihat Panduan desain indeks unik dalam panduan ini.

  • Periksa distribusi data di kolom. Membuat indeks pada kolom dengan banyak baris tetapi beberapa nilai berbeda mungkin tidak meningkatkan performa kueri meskipun indeks digunakan oleh pengoptimal kueri. Sebagai analogi, direktori telepon fisik yang diurutkan menurut abjad pada nama keluarga tidak mempercepat menemukan seseorang jika semua orang di kota diberi nama Smith atau Jones. Untuk informasi selengkapnya tentang distribusi data, lihat Statistik.

  • Pertimbangkan untuk menggunakan indeks terfilter pada kolom yang memiliki subset yang ditentukan dengan baik, misalnya kolom dengan banyak NULL, kolom dengan kategori nilai, dan kolom dengan rentang nilai yang berbeda. Indeks terfilter yang dirancang dengan baik dapat meningkatkan performa kueri, mengurangi biaya pembaruan indeks, dan mengurangi biaya penyimpanan dengan menyimpan subset kecil dari semua baris dalam tabel jika subset tersebut relevan untuk banyak kueri.

  • Pertimbangkan urutan kolom kunci indeks jika kunci berisi beberapa kolom. Kolom yang digunakan dalam predikat kueri untuk kesetaraan (=), ketidaksetaraan (>,>=,<,<=), atau ekspresi BETWEEN, atau berpartisipasi dalam join, harus ditempatkan terlebih dahulu. Kolom tambahan harus diurutkan berdasarkan tingkat perbedaannya, yaitu, dari yang paling berbeda dengan yang paling tidak berbeda.

    Misalnya, jika indeks didefinisikan sebagai LastName, FirstName, indeks berguna saat predikat kueri dalam WHERE klausa adalah WHERE LastName = 'Smith' atau WHERE LastName = Smith AND FirstName LIKE 'J%'. Namun, pengoptimal kueri tidak akan menggunakan indeks untuk kueri yang hanya mencari di WHERE FirstName = 'Jane', atau indeks tidak akan meningkatkan performa kueri tersebut.

  • Pertimbangkan untuk mengindeks kolom komputasi jika disertakan dalam predikat kueri. Untuk informasi selengkapnya, lihat Indeks pada kolom komputasi.

Karakteristik indeks

Setelah Anda menentukan bahwa indeks sesuai untuk kueri, Anda bisa memilih jenis indeks yang paling sesuai dengan situasi Anda. Karakteristik indeks meliputi:

  • Terkluster atau tidak terkluster
  • Unik atau tidak unik
  • Kolom tunggal atau multikolom
  • Urutan naik atau turun untuk kolom kunci dalam indeks
  • Semua baris atau difilter, untuk indeks non-kluster
  • Penyimpan kolom atau penyimpan baris
  • Hash atau nonclustered untuk tabel yang dioptimalkan memori

Penempatan indeks pada skema grup file atau partisi

Saat Anda mengembangkan strategi desain indeks, Anda harus mempertimbangkan penempatan indeks pada grup file yang terkait dengan database.

Secara default, indeks disimpan dalam grup file yang sama dengan tabel dasar (indeks berkluster atau timbunan) tempat indeks dibuat. Konfigurasi lain dimungkinkan, termasuk:

  • Buat indeks nonclustered pada grup file yang berbeda dari grup file tabel dasar.

  • Indeks terkluster dan non-kluster partisi untuk menjangkau beberapa grup file.

Untuk tabel yang tidak dipartisi, pendekatan paling sederhana biasanya yang terbaik: membuat semua tabel pada grup file yang sama, dan menambahkan file data sebanyak mungkin ke grup file seperlunya untuk menggunakan semua penyimpanan fisik yang tersedia.

Pendekatan penempatan indeks yang lebih canggih dapat dipertimbangkan ketika penyimpanan berjenjang tersedia. Misalnya, Anda dapat membuat grup file untuk tabel yang sering diakses dengan file pada disk yang lebih cepat, dan grup file untuk tabel arsip pada disk yang lebih lambat.

Anda dapat memindahkan tabel dengan indeks terkluster dari satu grup file ke grup file lainnya dengan menghilangkan indeks berkluster dan menentukan grup file atau skema partisi baru dalam MOVE TO klausul DROP INDEX pernyataan atau dengan menggunakan CREATE INDEX pernyataan dengan DROP_EXISTING klausa.

Indeks yang dipartisi

Anda juga dapat mempertimbangkan partisi tumpukan berbasis disk, indeks terkluster, dan non-kluster di beberapa grup file. Indeks yang dipartisi dipartisi secara horizontal (menurut baris), berdasarkan fungsi partisi. Fungsi partisi menentukan bagaimana setiap baris dipetakan ke partisi berdasarkan nilai kolom tertentu yang Anda tentukan, yang disebut kolom partisi. Skema partisi menentukan pemetaan sekumpulan partisi ke grup file.

Pemartisian indeks dapat memberikan manfaat berikut:

  • Membuat database besar lebih mudah dikelola. Sistem OLAP, misalnya, dapat menerapkan ETL sadar partisi yang sangat menyederhanakan penambahan dan penghapusan data secara massal.

  • Buat jenis kueri tertentu, seperti kueri analitik yang berjalan lama, berjalan lebih cepat. Saat kueri menggunakan indeks yang dipartisi, Mesin Database dapat memproses beberapa partisi secara bersamaan dan melewati (menghilangkan) partisi yang tidak diperlukan oleh kueri.

Peringatan

Pemartisian jarang meningkatkan performa kueri dalam sistem OLTP, tetapi dapat memperkenalkan overhead yang signifikan jika kueri transaksional harus mengakses banyak partisi.

Untuk informasi selengkapnya, lihat Tabel dan indeks yang dipartisi.

Panduan desain urutan pengurutan indeks

Saat menentukan indeks, pertimbangkan apakah setiap kolom kunci indeks harus disimpan dalam urutan naik atau menurun. Menaik adalah bawaan. Sintaks pernyataan CREATE INDEX, , dan CREATE TABLE mendukung kata kunci ALTER TABLE (naik) dan ASC (menurun) pada kolom individual dalam indeks DESCdan batasan.

Menentukan urutan di mana nilai kunci disimpan dalam indeks berguna saat kueri yang merujuk tabel memiliki ORDER BY klausa yang menentukan arah yang berbeda untuk kolom kunci atau kolom dalam indeks tersebut. Dalam kasus ini, indeks dapat menghapus perlunya Urutkanoperator dalam rencana kueri.

Misalnya, pembeli di departemen pembelian Adventure Works Cycles harus mengevaluasi kualitas produk yang mereka beli dari vendor. Pembeli paling tertarik untuk menemukan produk yang dikirim oleh vendor dengan tingkat penolakan yang tinggi.

Seperti yang ditunjukkan dalam kueri berikut terhadap database sampel AdventureWorks, mengambil data untuk memenuhi kriteria ini mengharuskan RejectedQty kolom dalam tabel diurutkan Purchasing.PurchaseOrderDetail dalam urutan turun (besar ke kecil) dan ProductID kolom yang akan diurutkan dalam urutan naik (kecil ke besar).

SELECT RejectedQty,
       ((RejectedQty / OrderQty) * 100) AS RejectionRate,
       ProductID,
       DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;

Rencana eksekusi berikut untuk kueri ini menunjukkan bahwa pengoptimal kueri menggunakan operator Urutkan untuk mengembalikan tataan hasil dalam urutan yang ditentukan oleh ORDER BY klausa.

Diagram rencana eksekusi untuk kueri ini memperlihatkan bahwa pengoptimal kueri menggunakan operator SORT untuk mengembalikan tataan hasil dalam urutan yang ditentukan oleh klausa ORDER BY.

Jika indeks rowstore berbasis disk dibuat dengan kolom kunci yang cocok dengan yang ada dalam ORDER BY klausul dalam kueri, operator Urutkan dalam rencana kueri dihilangkan, membuat rencana kueri lebih efisien.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);

Setelah kueri dijalankan lagi, rencana eksekusi berikut menunjukkan bahwa operator Urutkan tidak lagi ada dan indeks non-klusster yang baru dibuat digunakan.

Diagram rencana eksekusi yang menunjukkan bahwa operator SORT telah dihilangkan dan indeks nonclustered yang baru dibuat digunakan.

Mesin Database dapat memindai indeks di kedua arah. Indeks yang didefinisikan sebagai RejectedQty DESC, ProductID ASC masih dapat digunakan untuk kueri di mana arah pengurutan kolom dalam ORDER BY klausa dibalik. Misalnya, kueri dengan ORDER BY klausa ORDER BY RejectedQty ASC, ProductID DESC dapat menggunakan indeks yang sama.

Urutan pengurutan hanya dapat ditentukan untuk kolom kunci dalam indeks. Tampilan katalog sys.index_columns melaporkan apakah kolom indeks disimpan dalam urutan naik atau menurun.

Panduan desain indeks berkluster

Indeks berkluster menyimpan semua baris dan semua kolom tabel. Baris diurutkan dalam urutan nilai kunci indeks. Hanya ada satu indeks berkluster per tabel.

Istilah tabel dasar dapat merujuk ke indeks berkluster atau ke timbunan. Tumpukan adalah struktur data yang tidak diurutkan pada disk yang berisi semua baris dan semua kolom tabel.

Dengan beberapa pengecualian, setiap tabel harus memiliki indeks berkluster. Properti yang diinginkan dari indeks berkluster adalah:

Harta benda Description
Sempit Kunci indeks berkluster adalah bagian dari indeks non-kluster apa pun pada tabel dasar yang sama. Kunci yang sempit, atau kunci yang panjang total kolom kuncinya kecil, dapat mengurangi kebutuhan penyimpanan, I/O, dan beban memori semua indeks pada sebuah tabel.

Untuk menghitung panjang kunci, tambahkan ukuran penyimpanan untuk jenis data yang digunakan oleh kolom kunci. Untuk informasi selengkapnya, lihat Kategori jenis data.
Unik Jika indeks berkluster tidak unik, kolom pengidentifikasi unik internal 4 byte secara otomatis ditambahkan ke kunci indeks untuk memastikan keunikan. Penambahan kolom unik yang ada ke kunci indeks berkluster menghindari overhead penyimpanan, I/O, dan memori dari kolom pengidentifikasi unik di semua indeks pada tabel. Selain itu, pengoptimal kueri dapat menghasilkan rencana kueri yang lebih efisien saat indeks unik.
Terus meningkat Dalam indeks yang terus meningkat, data selalu ditambahkan di halaman terakhir indeks. Ini menghindari pemisahan halaman di tengah indeks, yang mengurangi kepadatan halaman dan mengurangi performa.
Immutabel Kunci indeks berkluster adalah bagian dari indeks non-kluster apa pun. Ketika kolom kunci indeks berkluster dimodifikasi, perubahan juga harus dilakukan di semua indeks non-kluster, yang menambahkan CPU, pengelogan, I/O, dan overhead memori. Overhead dapat dihindari jika kolom kunci indeks berkluster tidak dapat diubah.
Hanya memiliki kolom yang tidak nullable Jika suatu baris memiliki kolom bernilai null (nullable), baris tersebut harus menyertakan sebuah struktur internal yang disebut blok NULL, yang mengakibatkan penambahan 3-4 byte penyimpanan per baris pada sebuah indeks. Membuat semua kolom indeks berkluster tidak dapat bernilai nol menghindari overhead ini.
Memiliki kolom lebar tetap saja Kolom yang menggunakan jenis data lebar variabel seperti varchar atau nvarchar menggunakan tambahan 2 byte per nilai dibandingkan dengan jenis data lebar tetap. Menggunakan jenis data lebar tetap seperti int menghindari overhead ini di semua indeks pada tabel.

Memuaskan sebanyak mungkin properti ini saat merancang indeks berkluster tidak hanya membuat indeks berkluster, tetapi juga semua indeks non-kluster pada tabel yang sama lebih efisien. Performa ditingkatkan dengan menghindari penyimpanan, I/O, dan overhead memori.

Misalnya, kunci indeks berkluster dengan satu int atau bigint yang tidak dapat bernilai null memiliki semua properti ini jika diisi oleh sebuah IDENTITY klausul atau batasan default menggunakan sequence dan tidak diperbarui setelah baris disisipkan.

Sebaliknya, kunci indeks berkluster dengan kolom uniqueidentifier tunggal lebih lebar karena menggunakan 16 byte penyimpanan dibandingkan dengan 4 byte untuk int dan 8 byte untuk bigint, dan tidak memenuhi sifat selalu bertambah kecuali nilai-nilainya dihasilkan secara berurutan.

Tip

Saat Anda membuat PRIMARY KEY batasan, indeks unik yang mendukung batasan dibuat secara otomatis. Secara default, indeks ini diklusterkan; namun, jika indeks ini tidak memenuhi properti yang diinginkan dari indeks berkluster, Anda dapat membuat batasan sebagai non-kluster dan membuat indeks berkluster yang berbeda sebagai gantinya.

Jika Anda tidak membuat indeks berkluster, tabel disimpan sebagai tumpukan, yang umumnya tidak disarankan.

Arsitektur indeks berkluster

Indeks rowstore diatur sebagai pohon B+. Setiap halaman dalam pohon indeks B+ disebut simpul indeks. Simpul atas pohon B+ disebut simpul akar. Simpul bawah dalam indeks disebut simpul daun. Setiap tingkat indeks antara akar dan simpul daun secara kolektif dikenal sebagai tingkat menengah. Dalam indeks berkluster, simpul daun berisi halaman data tabel yang mendasar. Simpul tingkat akar dan menengah berisi halaman indeks yang menyimpan baris indeks. Setiap baris indeks berisi nilai kunci dan penunjuk ke halaman tingkat menengah di pohon B+, atau baris data di tingkat daun indeks. Halaman di setiap tingkat indeks ditautkan dalam daftar tertaut doubly.

Indeks berkluster memiliki satu baris dalam sys.partitions untuk setiap partisi yang digunakan oleh indeks, dengan index_id = 1. Secara default, indeks berkluster memiliki satu partisi. Ketika indeks berkluster memiliki beberapa partisi, setiap partisi memiliki struktur pohon B+ terpisah yang berisi data untuk partisi tertentu tersebut. Misalnya, jika indeks berkluster memiliki empat partisi, ada empat struktur pohon B+, satu di setiap partisi.

Bergantung pada jenis data dalam indeks berkluster, setiap struktur indeks berkluster memiliki satu atau beberapa unit alokasi untuk menyimpan dan mengelola data untuk partisi tertentu. Minimal, setiap indeks berkluster memiliki satu IN_ROW_DATA unit alokasi per partisi. Indeks berkluster juga memiliki satu LOB_DATA unit alokasi per partisi jika berisi kolom objek besar (LOB) seperti nvarchar(maks). Ini juga memiliki satu ROW_OVERFLOW_DATA unit alokasi per partisi jika berisi kolom panjang variabel yang melebihi batas ukuran baris 8.060 byte.

Halaman dalam struktur pohon B+ diurutkan pada nilai kunci indeks berkluster. Semua sisipan dibuat pada halaman di mana nilai kunci di baris yang disisipkan pas dalam urutan pengurutan di antara halaman yang ada. Dalam halaman, baris tidak selalu disimpan dalam urutan fisik apa pun. Namun, halaman mempertahankan urutan baris yang logis menggunakan struktur internal yang disebut array slot. Entri dalam array slot dipertahankan sesuai urutan indeks kunci.

Ilustrasi ini menunjukkan struktur indeks berkluster dalam satu partisi.

Diagram memperlihatkan struktur indeks berkluster dalam satu partisi.

Pedoman desain indeks non-klusster

Perbedaan utama antara indeks terkluster dan berkluster adalah bahwa indeks non-kluster berisi subset kolom dalam tabel, biasanya diurutkan secara berbeda dari indeks berkluster. Secara opsional, indeks non-kluster dapat difilter, yang berarti berisi subset semua baris dalam tabel.

Indeks nonclustered rowstore berbasis disk berisi pencari baris yang menunjuk ke lokasi penyimpanan baris dalam tabel dasar. Anda dapat membuat beberapa indeks nonclustered pada tabel atau tampilan terindeks. Umumnya, indeks non-kluster harus dirancang untuk meningkatkan performa kueri yang sering digunakan yang perlu memindai tabel dasar jika tidak.

Mirip dengan cara Anda menggunakan indeks dalam buku, pengoptimal kueri mencari nilai data dengan mencari indeks nonclustered untuk menemukan lokasi nilai data dalam tabel lalu mengambil data langsung dari lokasi tersebut. Ini membuat indeks non-kluster pilihan optimal untuk kueri kecocokan yang tepat karena indeks berisi entri yang menjelaskan lokasi yang tepat dalam tabel nilai data yang sedang dicari dalam kueri.

Misalnya, untuk melakukan kueri pada tabel HumanResources.Employee untuk semua karyawan yang melaporkan ke manajer tertentu, pengoptimal kueri mungkin menggunakan indeks nonclustered IX_Employee_ManagerID; dengan ManagerID sebagai kolom kunci pertamanya. ManagerID Karena nilai diurutkan dalam indeks non-kluster, pengoptimal kueri dapat dengan cepat menemukan semua entri dalam indeks yang cocok dengan nilai yang ditentukanManagerID. Setiap entri indeks menunjuk ke halaman dan baris yang tepat dalam tabel dasar tempat data terkait dari semua kolom lain dapat diambil. Setelah pengoptimal kueri menemukan semua entri dalam indeks, pengoptimal kueri dapat langsung masuk ke halaman dan baris yang tepat untuk mengambil data alih-alih memindai seluruh tabel dasar.

Arsitektur indeks nonclustered

Indeks rowstore nonclustered yang berbasis disk memiliki struktur pohon B+ yang sama dengan indeks berkluster, kecuali pada perbedaan-perbedaan berikut:

  • Indeks nonclustered tidak harus mengandung semua kolom dan baris dalam tabel.

  • Tingkat daun indeks non-klusster terdiri dari halaman indeks alih-alih halaman data. Halaman indeks pada tingkat daun indeks nonkluster berisi kolom kunci. Secara opsional, kolom tersebut mungkin juga berisi subset kolom lain dalam tabel sebagai kolom yang disertakan, untuk menghindari pengambilannya dari tabel dasar.

Pencari baris dalam baris indeks non-kluster adalah penunjuk ke baris atau merupakan kunci indeks berkluster untuk baris, dijelaskan sebagai berikut:

  • Jika tabel memiliki indeks berkluster, atau indeks berada pada tampilan terindeks, pencari baris adalah kunci indeks berkluster untuk baris tersebut.

  • Jika tabel adalah tumpukan, yang berarti tidak memiliki indeks berkluster, pencari baris adalah penunjuk ke baris. Penunjuk dibangun dari pengidentifikasi file (ID), nomor halaman, dan jumlah baris di halaman. Seluruh pointer dikenal sebagai ID Baris (RID).

Pencari baris juga memastikan keunikan untuk baris indeks nonclustered. Tabel berikut ini menjelaskan bagaimana Mesin Database menambahkan pencari baris ke indeks non-kluster:

Jenis tabel dasar Jenis indeks nonclustered Pencari lokasi baris
Heap
Nonunique RID ditambahkan ke kolom kunci
Unique RID ditambahkan ke kolom yang disertakan
Indeks berkluster unik
Nonunique Kunci indeks berkluster ditambahkan ke kolom kunci
Unique Kunci indeks berkluster ditambahkan ke kolom yang disertakan
Indeks berkluster yang tidak unik
Nonunique Kunci indeks berkluster dan pengidentifikasi unik (saat ada) ditambahkan ke kolom kunci
Unique Kunci indeks berkluster dan pengidentifikasi unik (saat ada) ditambahkan ke kolom yang disertakan

Mesin Database tidak pernah menyimpan kolom tertentu lebih dari sekali dalam indeks nonclustered. Urutan kunci indeks yang ditentukan oleh pengguna ketika mereka membuat indeks non-kluster selalu dihormati: kolom pencari baris apa pun yang perlu ditambahkan ke kunci indeks non-kluster ditambahkan di akhir kunci, mengikuti kolom yang ditentukan dalam definisi indeks. Pencari baris kunci indeks berkluster dalam indeks non-kluster dapat digunakan dalam pemrosesan kueri, terlepas dari apakah mereka secara eksplisit ditentukan dalam definisi indeks atau ditambahkan secara implisit.

Contoh berikut menunjukkan bagaimana pencari baris diimplementasikan dalam indeks nonclustered:

Indeks dalam kluster Definisi indeks yang tidak di-noncluster Definisi indeks yang tidak terkluster dengan pencari baris Explanation
Indeks berkluster unik dengan kolom kunci (A, B, C) Indeks nonunique nonclustered dengan kolom kunci (B, A) dan kolom yang disertakan (E, G) Kolom kunci (B, , AC) dan kolom yang disertakan (E, G) Indeks nonclustered nonunique, sehingga pencari baris perlu ada di kunci indeks. B Kolom dan A dari pencari baris sudah ada, jadi hanya kolom C yang ditambahkan. Kolom C ditambahkan ke akhir daftar kolom kunci.
Indeks berkluster unik dengan kolom kunci (A) Indeks nonunique nonclustered dengan kolom kunci (B, C) dan kolom yang disertakan (A) Kolom kunci (B, C, A) Indeks nonclustered nonunique, sehingga pencari baris ditambahkan ke kunci. Kolom A belum ditentukan sebagai kolom kunci, sehingga ditambahkan ke akhir daftar kolom kunci. Kolom A sekarang ada di kunci, jadi tidak perlu menyimpannya sebagai kolom yang disertakan.
Indeks berkluster unik dengan kolom kunci (A, B) Indeks nonclustered unik dengan kolom kunci (C) Kolom kunci (C) dan kolom yang disertakan (A, B) Indeks nonclustered unik, sehingga pencari baris ditambahkan ke kolom yang disertakan.

Indeks nonclustered memiliki satu baris dalam sys.partitions untuk setiap partisi yang digunakan oleh indeks, dengan index_id > 1. Secara default, indeks nonclustered memiliki satu partisi. Ketika indeks nonclustered memiliki beberapa partisi, setiap partisi memiliki struktur pohon B+ yang berisi baris indeks untuk partisi tertentu tersebut. Misalnya, jika indeks nonclustered memiliki empat partisi, ada empat struktur pohon B+, satu di setiap partisi.

Bergantung pada jenis data dalam indeks nonclustered, setiap struktur indeks nonclustered memiliki satu atau beberapa unit alokasi untuk menyimpan dan mengelola data untuk partisi tertentu. Sekurang-kurangnya, setiap indeks non-kluster memiliki satu IN_ROW_DATA unit alokasi per partisi yang menyimpan halaman indeks B+ tree. Indeks nonclustered juga memiliki satu LOB_DATA unit alokasi per partisi jika berisi kolom objek besar (LOB) seperti nvarchar(max). Selain itu, ia memiliki satu ROW_OVERFLOW_DATA unit alokasi per partisi jika berisi kolom panjang variabel yang melebihi batas ukuran baris 8.060 byte.

Ilustrasi berikut menunjukkan struktur indeks nonclustered dalam satu partisi.

Diagram memperlihatkan struktur indeks non-klusster dalam satu partisi.

Penggunaan kolom terlampir dalam indeks nonclustered

Selain kolom kunci, indeks nonclustered juga dapat memiliki kolom non-kunci yang disimpan di tingkat daun. Kolom non-kunci ini disebut kolom yang disertakan dan ditentukan dalam INCLUDE klausa CREATE INDEX pernyataan.

Indeks dengan kolom non-kunci yang disertakan dapat secara signifikan meningkatkan performa kueri saat mencakup kueri, yaitu, ketika semua kolom yang digunakan dalam kueri berada dalam indeks baik sebagai kolom kunci atau non-kunci. Perolehan performa dicapai karena Mesin Database dapat menemukan semua nilai kolom dalam indeks; tabel dasar tidak diakses, menghasilkan lebih sedikit operasi I/O disk.

Jika kolom harus diambil oleh kueri, tetapi tidak digunakan dalam predikat kueri, agregasi, dan pengurutan, tambahkan sebagai kolom yang disertakan dan bukan sebagai kolom kunci. Ini memiliki keuntungan berikut:

  • Kolom yang disertakan dapat menggunakan jenis data yang tidak diizinkan sebagai kolom kunci indeks.

  • Kolom yang disertakan tidak dipertimbangkan oleh Mesin Database saat menghitung jumlah kolom kunci indeks atau ukuran kunci indeks. Dengan kolom yang disertakan, Anda tidak dibatasi oleh ukuran kunci maksimum 900 byte. Anda dapat membuat indeks yang lebih luas yang mencakup lebih banyak kueri.

  • Saat Anda memindahkan kolom dari kunci indeks ke kolom yang disertakan, build indeks membutuhkan lebih sedikit waktu karena operasi pengurutan indeks menjadi lebih cepat.

Jika tabel memiliki indeks berklaster, kolom-kolom yang ditentukan dalam kunci indeks berklaster secara otomatis ditambahkan ke setiap indeks nonklaster tidak unik pada tabel. Tidak perlu menentukannya baik di kunci indeks non-kluster atau sebagai kolom yang disertakan.

Panduan untuk indeks dengan kolom yang disertakan

Pertimbangkan panduan berikut saat Anda merancang indeks nonclustered dengan kolom yang disertakan:

  • Kolom yang disertakan hanya dapat ditentukan dalam indeks nonclustered pada tabel atau tampilan terindeks.

  • Semua jenis data diizinkan kecuali teks, ntext, dan gambar.

  • Kolom komputasi yang deterministik dan tepat atau tidak tepat dapat disertakan kolom. Untuk informasi selengkapnya, lihat Indeks pada kolom komputasi.

  • Seperti halnya kolom kunci, kolom komputasi yang berasal dari jenis data gambar, ntext, dan teks dapat disertakan kolom selama jenis data kolom komputasi diizinkan dalam kolom yang disertakan.

  • Nama kolom tidak dapat ditentukan dalam INCLUDE daftar dan di daftar kolom kunci.

  • Nama kolom tidak dapat diulang dalam INCLUDE daftar.

  • Setidaknya satu kolom kunci harus ditentukan dalam indeks. Jumlah maksimum kolom yang disertakan adalah 1.023. Ini adalah jumlah maksimum kolom tabel dikurangi 1.

  • Terlepas dari keberadaan kolom yang disertakan, kolom kunci indeks harus mengikuti batasan ukuran indeks yang ada maksimum 16 kolom kunci, dan ukuran kunci indeks total 900 byte.

Rekomendasi desain untuk indeks dengan kolom yang disertakan

Pertimbangkan untuk mendesain ulang indeks nonclustered dengan ukuran kunci indeks besar sehingga hanya kolom yang digunakan dalam predikat kueri, agregasi, dan pengurutan yang merupakan kolom kunci. Buat semua kolom lain yang mencakup kolom nonkey yang disertakan kueri. Dengan cara ini, Anda memiliki semua kolom yang diperlukan untuk mencakup kueri, tetapi kunci indeks itu sendiri kecil dan efisien.

Misalnya, asumsikan bahwa Anda ingin merancang indeks untuk mencakup kueri berikut.

SELECT AddressLine1,
       AddressLine2,
       City,
       StateProvinceID,
       PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';

Untuk mencakup kueri, setiap kolom harus ditentukan dalam indeks. Meskipun Anda dapat menentukan semua kolom sebagai kolom kunci, ukuran kuncinya adalah 334 byte. Karena satu-satunya kolom yang digunakan sebagai kriteria pencarian adalah PostalCode kolom , memiliki panjang 30 byte, desain indeks yang lebih baik akan menentukan PostalCode sebagai kolom kunci dan menyertakan semua kolom lain sebagai kolom non-kunci.

Pernyataan berikut membuat indeks dengan kolom yang disertakan untuk mencakup kueri.

CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Untuk memvalidasi bahwa indeks mencakup kueri, buat indeks, lalu tampilkan perkiraan rencana eksekusi. Jika rencana eksekusi menunjukkan operator Pencarian Indeks untuk IX_Address_PostalCode indeks, kueri dicakup oleh indeks.

Pertimbangan performa untuk indeks dengan kolom yang disertakan

Hindari membuat indeks dengan sejumlah besar kolom yang disertakan. Meskipun indeks mungkin mencakup lebih banyak kueri, manfaat performanya menurun karena:

  • Lebih sedikit baris indeks yang pas pada halaman. Ini meningkatkan I/O disk dan mengurangi efisiensi cache.

  • Lebih banyak ruang disk diperlukan untuk menyimpan indeks. Secara khusus, menambahkan jenis data varchar(max), nvarchar(max), varbinary(max), atau xml dalam kolom yang disertakan dapat secara signifikan meningkatkan persyaratan ruang disk. Ini karena nilai kolom disalin ke tingkat daun indeks. Oleh karena itu, mereka berada di indeks dan tabel dasar.

  • Performa modifikasi data menurun karena banyak kolom harus dimodifikasi baik dalam tabel berbasis maupun dalam indeks nonclustered.

Anda harus menentukan apakah perolehan dalam performa kueri melebihi penurunan performa modifikasi data dan peningkatan persyaratan ruang disk.

Panduan desain indeks unik

Indeks unik menjamin bahwa kunci indeks tidak berisi nilai duplikat. Membuat indeks unik hanya dimungkinkan ketika keunikan adalah karakteristik data itu sendiri. Misalnya, jika Anda ingin memastikan bahwa nilai dalam NationalIDNumber kolom dalam HumanResources.Employee tabel unik, saat kunci utama adalah EmployeeID, buat UNIQUE batasan pada NationalIDNumber kolom. Keterbatasan menolak setiap upaya untuk memasukkan entri dengan nomor ID nasional yang duplikat.

Dengan indeks unik multikolom, indeks menjamin bahwa setiap kombinasi nilai dalam kunci indeks unik. Misalnya, jika indeks unik dibuat pada kombinasi LastNamekolom , , FirstNamedan MiddleName , tidak ada dua baris dalam tabel yang dapat memiliki nilai yang sama untuk kolom ini.

Indeks berkluster dan non-kluster dapat bersifat unik. Anda dapat membuat indeks berkluster yang unik dan beberapa indeks nonclustered unik pada tabel yang sama.

Manfaat indeks unik meliputi:

  • Aturan bisnis yang memerlukan keunikan data diberlakukan.
  • Informasi tambahan yang berguna untuk pengoptimal kueri disediakan.

PRIMARY KEY Membuat atau UNIQUE membatasi secara otomatis membuat indeks unik pada kolom yang ditentukan. Tidak ada perbedaan signifikan antara membuat UNIQUE batasan dan membuat indeks unik yang independen dari batasan. Validasi data terjadi dengan cara yang sama dan pengoptimal kueri tidak membedakan antara indeks unik yang dibuat oleh batasan atau dibuat secara manual. Namun, Anda harus membuat UNIQUE atau PRIMARY KEY membatasi kolom ketika penegakan aturan bisnis adalah tujuannya. Dengan melakukan ini, tujuan indeks jelas.

Pertimbangan indeks unik

  • Indeks, UNIQUE batasan, atau PRIMARY KEY batasan unik tidak dapat dibuat jika nilai kunci duplikat ada dalam data.

  • Jika data unik dan Anda ingin keunikan diberlakukan, membuat indeks unik alih-alih indeks nonunique pada kombinasi kolom yang sama menyediakan informasi tambahan untuk pengoptimal kueri yang dapat menghasilkan rencana eksekusi yang lebih efisien. UNIQUE Membuat batasan atau indeks unik direkomendasikan dalam kasus ini.

  • Indeks nonclustered unik dapat berisi kolom non-kunci yang disertakan. Untuk informasi selengkapnya, lihat Menggunakan kolom yang disertakan dalam indeks nonclustered.

  • Tidak seperti batasan PRIMARY KEY, batasan UNIQUE atau indeks unik dapat dibuat dengan kolom yang mengizinkan nilai null di kunci indeks. Untuk tujuan penegakan keunikan, dua NULL dianggap sama. Misalnya, ini berarti bahwa dalam indeks unik kolom tunggal, kolom dapat berupa NULL untuk satu baris dalam tabel saja.

Panduan desain indeks yang difilter

Indeks yang difilter adalah indeks non-kluster yang dioptimalkan, terutama cocok untuk kueri yang memerlukan subset kecil data dalam tabel. Ini menggunakan predikat filter dalam definisi indeks untuk mengindeks sebagian baris dalam tabel. Indeks terfilter yang dirancang dengan baik dapat meningkatkan performa kueri, mengurangi biaya pembaruan indeks, dan mengurangi biaya penyimpanan indeks dibandingkan dengan indeks tabel penuh.

Indeks yang difilter dapat memberikan keuntungan berikut daripada indeks tabel penuh:

  • Peningkatan performa kueri dan kualitas rencana

    Indeks terfilter yang dirancang dengan baik meningkatkan performa kueri dan kualitas rencana eksekusi karena lebih kecil dari indeks nonclustered tabel penuh. Indeks yang difilter telah memfilter statistik, yang lebih akurat daripada statistik tabel penuh karena hanya mencakup baris dalam indeks yang difilter.

  • Mengurangi biaya pembaruan indeks

    Indeks diperbarui hanya ketika pernyataan bahasa manipulasi data (DML) memengaruhi data dalam indeks. Indeks yang difilter mengurangi biaya pembaruan indeks dibandingkan dengan indeks non-kluster tabel penuh karena lebih kecil dan hanya diperbarui ketika data dalam indeks terpengaruh. Dimungkinkan untuk memiliki sejumlah besar indeks yang difilter, terutama ketika berisi data yang jarang terpengaruh. Demikian pula, jika indeks yang difilter hanya berisi data yang sering terpengaruh, ukuran indeks yang lebih kecil mengurangi biaya pembaruan statistik.

  • Mengurangi biaya penyimpanan indeks

    Membuat indeks yang difilter dapat mengurangi penyimpanan disk untuk indeks non-kluster saat indeks tabel penuh tidak diperlukan. Anda mungkin dapat mengganti indeks non-kluster tabel penuh dengan beberapa indeks yang difilter tanpa meningkatkan persyaratan penyimpanan secara signifikan.

Indeks yang difilter berguna saat kolom berisi subset data yang ditentukan dengan baik. Contohnya adalah:

  • Kolom yang berisi banyak nilai NULL (nilai yang tidak ada/tiada data).

  • Kolom heterogen yang berisi kategori data.

  • Kolom yang berisi rentang nilai seperti jumlah, waktu, dan tanggal.

Pengurangan biaya pembaruan untuk indeks yang difilter paling terlihat ketika jumlah baris dalam indeks kecil dibandingkan dengan indeks tabel penuh. Jika indeks yang difilter menyertakan sebagian besar baris dalam tabel, mungkin lebih mahal untuk dipertahankan daripada indeks tabel penuh. Dalam hal ini, Anda harus menggunakan indeks tabel penuh alih-alih indeks yang difilter.

Indeks yang difilter ditentukan pada satu tabel dan hanya mendukung operator perbandingan sederhana. Jika Anda memerlukan ekspresi filter yang memiliki logika kompleks atau mereferensikan beberapa tabel, Anda harus membuat kolom komputasi terindeks atau tampilan terindeks.

Pertimbangan desain indeks yang difilter

Untuk merancang indeks yang difilter yang efektif, penting untuk memahami kueri apa yang digunakan aplikasi Anda dan bagaimana mereka berhubungan dengan subset data Anda. Beberapa contoh data yang memiliki subset yang terdefinisi dengan baik adalah kolom dengan banyak NULL, kolom dengan kategori nilai dan kolom heterogen dengan rentang nilai yang berbeda.

Pertimbangan desain berikut memberikan beberapa skenario ketika indeks yang difilter dapat memberikan keuntungan daripada indeks tabel penuh.

Indeks yang difilter untuk subset data

Saat kolom hanya memiliki beberapa nilai yang relevan untuk kueri, Anda dapat membuat indeks yang difilter pada subset nilai. Misalnya, ketika kolom sebagian besar NULL dan kueri hanya memerlukan nilai non-NULL, Anda dapat membuat indeks yang difilter yang berisi baris non-NULL.

Misalnya, database sampel AdventureWorks memiliki Production.BillOfMaterials tabel dengan 2.679 baris. Kolom EndDate hanya memiliki 199 baris yang berisi nilai non-NULL dan 2480 baris lainnya berisi NULL. Indeks yang difilter berikut mencakup kueri yang mengembalikan kolom yang ditentukan dalam indeks dan yang hanya memerlukan baris dengan nilai non-NULL untuk EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

Indeks FIBillOfMaterialsWithEndDate yang difilter valid untuk kueri berikut. Tampilkan Perkiraan Rencana Eksekusi untuk menentukan apakah pengoptimal kueri menggunakan indeks yang difilter.

SELECT ProductAssemblyID,
       ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
      AND ComponentID = 5
      AND StartDate > '20080101';

Untuk informasi selengkapnya tentang cara membuat indeks yang difilter dan cara menentukan ekspresi predikat indeks yang difilter, lihat Membuat indeks yang difilter.

Indeks yang difilter untuk data heterogen

Saat tabel memiliki baris data heterogen, Anda bisa membuat indeks yang difilter untuk satu atau beberapa kategori data.

Misalnya, produk yang tercantum dalam Production.Product tabel masing-masing ditetapkan ke ProductSubcategoryID, yang pada gilirannya terkait dengan kategori produk Sepeda, Komponen, Pakaian, atau Aksesori. Kategori ini bersifat heterogen karena nilai kolomnya dalam Production.Product tabel tidak berkorelasi erat. Misalnya, kolom Color, , ReorderPointListPrice, Weight, Class, dan Style memiliki karakteristik unik untuk setiap kategori produk. Misalkan ada kueri yang sering untuk aksesori, yang memiliki subkatoner antara 27 dan 36 inklusif. Anda dapat meningkatkan performa kueri untuk aksesori dengan membuat indeks yang difilter pada subkatoner aksesori seperti yang ditunjukkan dalam contoh berikut.

CREATE NONCLUSTERED INDEX FIProductAccessories
ON Production.Product (ProductSubcategoryID, ListPrice)
INCLUDE (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;

Indeks FIProductAccessories yang difilter mencakup kueri berikut karena hasil kueri terkandung dalam indeks dan rencana kueri tidak memerlukan akses tabel dasar. Misalnya, ekspresi ProductSubcategoryID = 33 predikat kueri adalah subset dari predikat ProductSubcategoryID >= 27 indeks yang difilter dan ProductSubcategoryID <= 36, ProductSubcategoryID kolom dan ListPrice dalam predikat kueri adalah kolom kunci dalam indeks, dan nama disimpan dalam tingkat daun indeks sebagai kolom yang disertakan.

SELECT Name,
       ProductSubcategoryID,
       ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33
      AND ListPrice > 25.00;

Kunci dan kolom yang disertakan dalam indeks yang difilter

Ini adalah praktik terbaik untuk menambahkan sejumlah kecil kolom dalam definisi indeks yang difilter, hanya seperlunya bagi pengoptimal kueri untuk memilih indeks yang difilter untuk rencana eksekusi kueri. Pengoptimal kueri dapat memilih indeks yang difilter untuk kueri terlepas dari apakah itu terjadi atau tidak mencakup kueri. Namun, pengoptimal kueri lebih mungkin memilih indeks yang difilter jika mencakup kueri.

Dalam beberapa kasus, indeks yang difilter mencakup kueri tanpa menyertakan kolom dalam ekspresi indeks yang difilter sebagai kunci atau kolom yang disertakan dalam definisi indeks yang difilter. Panduan berikut menjelaskan kapan kolom dalam ekspresi indeks yang difilter harus berupa kunci atau kolom yang disertakan dalam definisi indeks yang difilter. Contoh mengacu pada indeks yang difilter, FIBillOfMaterialsWithEndDate yang dibuat sebelumnya.

Kolom dalam ekspresi indeks yang difilter tidak perlu menjadi kunci atau kolom yang disertakan dalam definisi indeks yang difilter jika ekspresi indeks yang difilter setara dengan predikat kueri dan kueri tidak mengembalikan kolom dalam ekspresi indeks yang difilter dengan hasil kueri. Misalnya, FIBillOfMaterialsWithEndDate mencakup kueri berikut karena predikat kueri setara dengan ekspresi filter, dan EndDate tidak dikembalikan dengan hasil kueri. Indeks FIBillOfMaterialsWithEndDate tidak diperlukan EndDate sebagai kunci atau kolom yang disertakan dalam definisi indeks yang difilter.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Kolom dalam ekspresi indeks yang difilter harus berupa kunci atau kolom yang disertakan dalam definisi indeks yang difilter jika predikat kueri menggunakan kolom dalam perbandingan yang tidak setara dengan ekspresi indeks yang difilter. Misalnya, FIBillOfMaterialsWithEndDate valid untuk kueri berikut karena memilih subset baris dari indeks yang difilter. Namun, kueri ini tidak mencakup kueri berikut karena EndDate digunakan dalam perbandingan EndDate > '20040101', yang tidak setara dengan ekspresi indeks yang difilter. Prosesor kueri tidak dapat menjalankan kueri ini tanpa memeriksa nilai EndDate. Oleh karena itu, EndDate harus berupa kunci atau kolom yang disertakan dalam definisi indeks yang difilter.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

Kolom dalam ekspresi indeks yang difilter harus berupa kunci atau kolom yang disertakan dalam definisi indeks yang difilter jika kolom berada dalam tataan hasil kueri. Misalnya, FIBillOfMaterialsWithEndDate tidak mencakup kueri berikut karena mengembalikan EndDate kolom dalam hasil kueri. Oleh karena itu, EndDate harus berupa kunci atau kolom yang disertakan dalam definisi indeks yang difilter.

SELECT ComponentID,
       StartDate,
       EndDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Kunci indeks terkluster tabel tidak perlu menjadi kunci atau kolom yang disertakan dalam definisi indeks yang difilter. Kunci indeks berkluster secara otomatis disertakan dalam semua indeks non-kluster, termasuk indeks yang difilter.

Operator konversi data dalam predikat filter

Jika operator perbandingan yang ditentukan dalam ekspresi indeks yang difilter dari indeks yang difilter menghasilkan konversi data implisit atau eksplisit, kesalahan terjadi jika konversi terjadi di sisi kiri operator perbandingan. Solusinya adalah menulis ekspresi indeks yang difilter dengan operator konversi data (CAST atau CONVERT) di sisi kanan operator perbandingan.

Contoh berikut membuat tabel dengan kolom dari jenis data yang berbeda.

CREATE TABLE dbo.TestTable
(
    a INT,
    b VARBINARY(4)
);

Dalam definisi indeks yang difilter berikut, kolom b secara implisit dikonversi ke jenis data bilangan bulat untuk membandingkannya dengan konstanta 1. Ini menghasilkan pesan kesalahan 10611 karena konversi terjadi di sisi kiri operator dalam predikat yang difilter.

CREATE NONCLUSTERED INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = 1;

Solusinya adalah mengonversi konstanta di sisi kanan menjadi tipe yang sama dengan kolom b, seperti yang terlihat dalam contoh berikut:

CREATE INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = CONVERT (VARBINARY(4), 1);

Memindahkan konversi data dari sisi kiri ke sisi kanan operator perbandingan dapat mengubah arti konversi. Dalam contoh sebelumnya, ketika CONVERT operator ditambahkan ke sisi kanan, perbandingan berubah dari perbandingan int dengan perbandingan varbiner .

Arsitektur indeks penyimpan kolom

Indeks penyimpan kolom adalah teknologi untuk menyimpan, mengambil, dan mengelola data dengan menggunakan format data kolom, yang disebut penyimpan kolom. Untuk informasi selengkapnya, lihat Indeks penyimpan kolom: gambaran umum.

Untuk informasi versi dan untuk mengetahui apa yang baru, kunjungi Apa yang baru dalam indeks penyimpan kolom.

Mengetahui dasar-dasar ini memudahkan untuk memahami artikel columnstore lain yang menjelaskan cara menggunakan teknologi ini secara efektif.

Penyimpanan data menggunakan penyimpan kolom dan penyimpan baris.

Saat membahas indeks penyimpan kolom, kami menggunakan istilah rowstore dan columnstore untuk menekankan format untuk penyimpanan data. Indeks penyimpan kolom menggunakan kedua jenis penyimpanan.

Diagram indeks penyimpan kolom berkluster.

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

    Indeks penyimpan kolom secara fisik menyimpan sebagian besar data dalam format penyimpan kolom. Dalam format penyimpan kolom, data dikompresi dan tidak dikompresi sebagai kolom. Tidak perlu membongkar nilai lain di setiap baris yang tidak diminta oleh kueri. Ini membuatnya cepat untuk memindai seluruh kolom tabel besar.

  • 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 seperti indeks pohon B+ berkluster atau timbunan.

    Indeks penyimpan kolom juga secara fisik menyimpan beberapa baris dalam format rowstore yang disebut deltastore. Deltastore, juga disebut grup baris delta, adalah tempat penahanan untuk baris yang jumlahnya terlalu sedikit untuk memenuhi syarat kompresi ke penyimpanan kolom. Setiap grup baris delta diimplementasikan sebagai indeks pohon B+ berkluster, yang merupakan penyimpanan baris.

Operasi dilakukan pada grup baris dan segmen kolom

Indeks penyimpan kolom mengelompokkan baris ke dalam unit yang dapat dikelola. Masing-masing unit ini disebut grup baris. Untuk performa terbaik, jumlah baris dalam grup baris cukup besar untuk meningkatkan rasio kompresi dan cukup kecil untuk diuntungkan dalam operasi memori.

Misalnya, indeks penyimpan kolom melakukan operasi ini pada grup baris:

  • Memadatkan grup baris ke dalam penyimpan kolom. Pemadatan dilakukan pada setiap segmen kolom dalam grup baris.

  • Menggabungkan kelompok baris dalam operasi ALTER INDEX ... REORGANIZE, termasuk penghapusan data yang telah dihapus.

  • Mengulangi semua grup baris selama operasi ALTER INDEX ... REBUILD.

  • Laporan tentang kesehatan grup baris dan fragmentasi dalam tampilan manajemen dinamis (DMV).

Deltastore terdiri dari satu atau beberapa grup baris yang disebut grup baris delta. Setiap grup baris delta adalah indeks pohon B+ berkluster yang menyimpan beban massal kecil dan menyisipkan hingga grup baris berisi 1.048.576 baris, pada saat itu proses yang disebut tuple-mover secara otomatis memadatkan grup baris tertutup ke dalam columnstore.

Untuk informasi selengkapnya tentang status grup baris, lihat sys.dm_db_column_store_row_group_physical_stats.

Tip

Memiliki terlalu banyak grup baris kecil mengurangi kualitas indeks penyimpan kolom. Operasi reorganisasi menggabungkan grup baris yang lebih kecil, mengikuti kebijakan ambang internal yang menentukan cara menghapus baris yang dihapus dan menggabungkan grup baris terkompresi. Setelah penggabungan, kualitas indeks ditingkatkan.

Di SQL Server 2019 (15.x) dan versi yang lebih baru, 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 tempat sejumlah besar baris telah dihapus.

Setiap kolom memiliki beberapa nilainya di setiap grup baris. Nilai-nilai ini disebut segmen kolom. Setiap grup baris berisi satu segmen kolom untuk setiap kolom dalam tabel. Setiap kolom memiliki satu segmen kolom di setiap grup baris.

Diagram segmen kolom penyimpan kolom berkluster.

Saat indeks penyimpan kolom memadatkan grup baris, indeks tersebut memadatkan setiap segmen kolom secara terpisah. Untuk membongkar seluruh kolom, indeks penyimpan kolom hanya perlu membongkar satu segmen kolom dari setiap grup baris.

Beban dan sisipan kecil masuk ke deltastore

Indeks penyimpan kolom meningkatkan pemadatan dan performa penyimpan kolom dengan memadatkan setidaknya 102.400 baris sekaligus ke dalam indeks penyimpan kolom. Untuk memadatkan baris secara massal, indeks penyimpan kolom mengakumulasi beban kecil dan sisipan di deltastore. Operasi deltastore ditangani di latar belakang. Untuk mengembalikan hasil kueri, indeks penyimpan kolom berkluster menggabungkan hasil kueri dari penyimpan kolom dan deltastore.

Baris masuk ke deltastore saat:

  • Disisipkan dengan INSERT INTO ... VALUES pernyataan.

  • Di akhir beban massal, dan jumlahnya kurang dari 102.400.

  • Updated. Setiap pembaruan diimplementasikan sebagai penghapusan dan penyisipan.

Deltastore juga menyimpan daftar ID untuk baris yang dihapus yang telah ditandai sebagai dihapus tetapi belum dihapus secara fisik dari penyimpan kolom.

Saat grup baris delta penuh, grup baris tersebut akan dikompresi ke dalam penyimpan kolom

Indeks penyimpan kolom berkluster mengumpulkan hingga 1.048.576 baris di setiap grup baris delta sebelum memadatkan grup baris ke dalam penyimpan kolom. Ini meningkatkan pemadatan indeks penyimpan kolom. Saat grup baris delta mencapai jumlah baris maksimum, grup baris tersebut beralih dari status OPEN ke CLOSED . Proses latar belakang bernama tuple-mover memeriksa grup baris tertutup. Jika proses menemukan grup baris tertutup, proses akan memadatkan grup baris dan menyimpannya ke dalam penyimpan kolom.

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, dan grup baris terkompresi baru ditandai sebagai COMPRESSED.

Untuk informasi selengkapnya tentang status grup baris, lihat sys.dm_db_column_store_row_group_physical_stats.

Anda dapat memaksa grup baris delta ke dalam penyimpan kolom dengan menggunakan ALTER INDEX untuk membangun kembali atau mengatur ulang indeks. Jika ada tekanan memori selama pemadatan, indeks penyimpan kolom mungkin mengurangi jumlah baris dalam grup baris terkompresi.

Setiap partisi tabel memiliki grup baris dan grup baris delta sendiri

Konsep pembagian sama dalam indeks berkluster, heap, dan indeks penyimpan kolom. Pemartisian tabel membagi tabel menjadi grup baris yang lebih kecil sesuai dengan rentang nilai kolom. Ini sering digunakan untuk mengelola data. Misalnya, Anda dapat membuat partisi untuk setiap tahun data, lalu menggunakan pengalihan partisi untuk mengarsipkan data lama ke penyimpanan yang lebih murah.

Grup baris selalu ditentukan dalam partisi tabel. Ketika indeks penyimpan kolom dipartisi, setiap partisi memiliki grup baris terkompresi dan grup baris delta sendiri. Tabel yang tidak dipartisi berisi satu partisi.

Tip

Pertimbangkan untuk menggunakan pemartisian tabel jika ada kebutuhan untuk menghapus data dari penyimpan kolom. Mengganti dan memotong partisi yang tidak diperlukan lagi adalah strategi yang efisien dalam menghapus data tanpa menyebabkan fragmentasi dalam columnstore.

Setiap partisi dapat memiliki beberapa grup baris delta

Setiap partisi dapat memiliki lebih dari satu grup baris delta. Ketika indeks penyimpan kolom perlu menambahkan data ke grup baris delta dan grup baris delta dikunci oleh transaksi lain, indeks penyimpan kolom mencoba mendapatkan kunci pada grup baris delta yang berbeda. Jika tidak ada grup baris delta yang tersedia, indeks penyimpan kolom membuat grup baris delta baru. Misalnya, tabel dengan 10 partisi dapat dengan mudah memiliki 20 grup baris delta atau lebih.

Menggabungkan indeks penyimpan kolom dan rowstore pada tabel yang sama

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.

Anda dapat membuat indeks penyimpan kolom nonclustered yang dapat diperbarui pada tabel rowstore. Indeks penyimpan kolom menyimpan salinan data sehingga Anda memerlukan penyimpanan tambahan. Namun, data dalam indeks columnstore dikompresi ke ukuran yang jauh lebih kecil daripada yang dibutuhkan oleh tabel rowstore. Dengan melakukan ini, Anda dapat menjalankan analitik pada indeks penyimpan kolom dan beban kerja OLTP pada indeks rowstore secara bersamaan. Penyimpan kolom diperbarui saat data berubah dalam tabel rowstore, sehingga kedua indeks bekerja terhadap data yang sama.

Tabel penyimpanan baris dapat memiliki satu indeks penyimpan kolom nonclustered. Untuk informasi selengkapnya, lihat Indeks penyimpan kolom - panduan desain.

Anda dapat memiliki satu atau beberapa indeks rowstore non-kluster pada tabel penyimpan kolom berkluster. Dengan melakukan ini, Anda dapat melakukan pencarian tabel yang efisien di penyimpan kolom yang mendasar. Opsi lain juga tersedia. Misalnya, Anda dapat menerapkan keunikan dengan menggunakan UNIQUE batasan pada tabel rowstore. Ketika nilai yang tidak unik gagal dimasukkan ke dalam tabel rowstore, Mesin Database juga tidak memasukkan nilai tersebut ke dalam columnstore.

Pertimbangan performa penyimpan kolom tidak berkluster

Definisi indeks penyimpan kolom non-kluster mendukung penggunaan kondisi yang difilter. Untuk meminimalkan efek performa menambahkan indeks penyimpan kolom, gunakan ekspresi filter untuk membuat indeks penyimpan kolom non-kluster hanya pada subset data yang diperlukan untuk analitik.

Tabel yang dioptimalkan memori dapat memiliki satu indeks penyimpan kolom. Anda dapat membuatnya saat tabel dibuat atau menambahkannya nanti dengan ALTER TABLE.

Untuk informasi selengkapnya, lihat Indeks penyimpan kolom - performa kueri.

Panduan desain indeks hash yang dioptimalkan memori

Saat menggunakan In-Memory OLTP, semua tabel yang dioptimalkan memori harus memiliki setidaknya satu indeks. Untuk tabel yang dioptimalkan untuk memori, setiap indeks juga dioptimalkan untuk memori. Indeks hash adalah salah satu jenis indeks yang mungkin dalam tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat Indeks pada Tabel yang Dioptimalkan Memori.

Arsitektur indeks hash yang dioptimalkan untuk memori

Indeks hash terdiri dari array pointer, dan setiap elemen array disebut wadah hash.

  • Setiap wadah adalah 8 byte, yang digunakan untuk menyimpan alamat memori daftar tautan entri kunci.
  • Setiap entri adalah nilai untuk kunci indeks, ditambah alamat baris yang sesuai dalam tabel yang dioptimalkan memori yang mendasar.
  • Setiap entri menunjuk ke entri berikutnya dalam daftar tautan entri, semua ditautkan ke wadah saat ini.

Jumlah wadah harus ditentukan pada waktu pembuatan indeks:

  • Semakin rendah rasio wadah terhadap baris tabel atau ke nilai yang berbeda, semakin lama daftar tautan wadah rata-rata.
  • Daftar tautan pendek berkinerja lebih cepat daripada daftar tautan panjang.
  • Jumlah maksimum wadah dalam indeks hash adalah 1.073.741.824.

Tip

Untuk menentukan hak BUCKET_COUNT untuk data Anda, lihat Mengonfigurasi jumlah wadah indeks hash.

Fungsi hash diterapkan ke kolom kunci indeks dan hasil fungsi menentukan wadah apa yang termasuk dalam kunci tersebut. Setiap wadah memiliki pointer ke baris yang nilai kunci hash-nya dipetakan ke wadah tersebut.

Fungsi hashing yang digunakan untuk indeks hash memiliki karakteristik berikut:

  • Mesin Database memiliki satu fungsi hash yang digunakan untuk semua indeks hash.
  • Fungsi hash adalah deterministik. Nilai kunci input yang sama selalu dipetakan ke wadah yang sama dalam indeks hash.
  • Beberapa kunci indeks mungkin dipetakan ke wadah hash yang sama.
  • Fungsi hash seimbang, yang berarti bahwa distribusi nilai kunci indeks melalui wadah hash biasanya mengikuti Distribusi kurva Poisson atau bel, bukan distribusi linier datar.
  • Distribusi Poisson bukanlah distribusi yang merata. Nilai kunci indeks tidak didistribusikan secara merata dalam wadah hash.
  • Jika dua kunci indeks dipetakan ke wadah hash yang sama, ada tabrakan hash. Sejumlah besar tabrakan hash dapat memiliki efek performa pada operasi baca. Tujuan realistis adalah untuk 30 persen wadah untuk berisi dua nilai kunci yang berbeda.

Interplay indeks hash dan wadah dirangkum dalam gambar berikut.

Diagram memperlihatkan interaksi antara indeks hash dan wadah.

Mengonfigurasi jumlah wadah indeks hash

Jumlah wadah indeks hash ditentukan pada waktu pembuatan indeks, dan dapat diubah menggunakan ALTER TABLE...ALTER INDEX REBUILD sintaks.

Dalam kebanyakan kasus, jumlah bucket harus antara 1 hingga 2 kali dari jumlah nilai unik dalam kunci indeks. Anda mungkin tidak selalu dapat memprediksi berapa banyak nilai yang dimiliki kunci indeks tertentu. Performa biasanya masih baik jika BUCKET_COUNT nilainya dalam 10 kali dari jumlah nilai kunci aktual, dan menilai secara berlebihan umumnya lebih baik daripada meremehkan.

Terlalu sedikit wadah dapat memiliki kelemahan berikut:

  • Lebih banyak tabrakan hash dari nilai kunci yang berbeda.
  • Setiap nilai berbeda dipaksa untuk berbagi wadah yang sama dengan nilai yang berbeda.
  • Panjang rantai rata-rata per ember tumbuh.
  • Semakin lama rantai wadah, semakin lambat kecepatan pencarian kesetaraan dalam indeks.

Terlalu banyak wadah dapat memiliki kelemahan berikut:

  • Jumlah wadah yang terlalu tinggi dapat menghasilkan wadah yang lebih kosong.
  • Wadah kosong memengaruhi performa pemindaian indeks penuh. Jika pemindaian dilakukan secara teratur, pertimbangkan untuk memilih jumlah wadah yang dekat dengan jumlah nilai kunci indeks yang berbeda.
  • Wadah kosong menggunakan memori, meskipun setiap wadah hanya menggunakan 8 byte.

Note

Menambahkan lebih banyak wadah tidak melakukan apa pun untuk mengurangi penautan bersama entri yang berbagi nilai duplikat. Tingkat duplikasi nilai digunakan untuk memutuskan apakah indeks hash atau indeks non-kluster adalah jenis indeks yang sesuai, bukan untuk menghitung jumlah wadah.

Pertimbangan performa untuk indeks hash

Performa indeks hash adalah:

  • Sangat baik ketika predikat dalam WHERE klausul menentukan nilai yang tepat untuk setiap kolom dalam kunci indeks hash. Indeks hash kembali ke pemindaian yang diberikan predikat ketidaksetaraan.
  • Buruk ketika predikat dalam WHERE klausa mencari rentang nilai dalam kunci indeks.
  • Buruk ketika predikat dalam WHERE klausul menetapkan satu nilai tertentu untuk kolom pertama dari kunci indeks hash dua kolom, tetapi tidak menentukan nilai untuk kolom kunci lainnya .

Tip

Predikat harus menyertakan semua kolom dalam kunci indeks hash. Indeks hash memerlukan seluruh kunci untuk mengakses indeks.

Jika indeks hash digunakan, dan jumlah kunci indeks unik lebih dari 100 kali lebih kecil dari jumlah baris, pertimbangkan untuk meningkatkan ke jumlah wadah yang lebih besar untuk menghindari rantai baris besar, atau gunakan indeks yang tidak dikluster sebagai gantinya.

Membuat indeks hash

Saat membuat indeks hash, pertimbangkan:

  • Indeks hash hanya dapat ada pada tabel yang dioptimalkan memori. Ini tidak dapat ada pada tabel berbasis disk.
  • Indeks hash tidak unik secara default, tetapi dapat dinyatakan sebagai unik.

Contoh berikut membuat indeks hash unik:

ALTER TABLE MyTable_memop ADD INDEX ix_hash_Column2
    UNIQUE HASH (Column2) WITH (BUCKET_COUNT = 64);

Versi baris dan pengumpulan sampah dalam tabel yang dioptimalkan memori

Dalam tabel yang dioptimalkan memori, saat baris dipengaruhi oleh UPDATE pernyataan, tabel membuat versi baris yang diperbarui. Selama transaksi pembaruan, sesi lain mungkin dapat membaca versi baris yang lebih lama, sehingga menghindari perlambatan performa yang terkait dengan kunci baris.

Indeks hash juga dapat memiliki versi entri yang berbeda untuk mengakomodasi pembaruan.

Kemudian ketika versi lama tidak lagi diperlukan, utas pengumpulan sampah (GC) melintasi wadah dan daftar tautan mereka untuk membersihkan entri lama. Utas GC berkinerja lebih baik jika panjang rantai daftar tautan pendek. Untuk informasi selengkapnya, lihat Pengumpulan Sampah OLTP Dalam Memori.

Pedoman desain indeks nonclustered yang dioptimalkan memori

Selain indeks hash, indeks nonclustered adalah jenis indeks lain yang tersedia dalam tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat Indeks pada Tabel yang Dioptimalkan Memori.

Arsitektur indeks nonclustered yang dioptimalkan memori

Indeks non-kluster pada tabel yang dioptimalkan memori diimplementasikan menggunakan struktur data yang disebut pohon Bw, awalnya digambarkan dan dijelaskan oleh Microsoft Research pada tahun 2011. Pohon Bw adalah variasi kunci dan bebas kait dari pohon B. Untuk informasi selengkapnya, lihat Bw-tree: Pohon B untuk Platform Perangkat Keras Baru.

Pada tingkat tinggi, pohon Bw dapat dipahami sebagai peta halaman yang diatur berdasarkan ID halaman (PidMap), sebuah fasilitas untuk mengalokasikan dan menggunakan kembali ID halaman (PidAlloc), serta sekumpulan halaman yang saling terhubung dalam peta halaman maupun di antara satu sama lain. Ketiga subkomponen tingkat tinggi ini membentuk struktur internal dasar pohon Bw.

Strukturnya mirip dengan pohon B normal dalam arti setiap halaman memiliki sekumpulan nilai kunci yang diurutkan dan ada tingkat dalam indeks yang masing-masing menunjuk ke tingkat yang lebih rendah dan tingkat daun menunjuk ke baris data. Namun ada beberapa perbedaan.

Sama seperti indeks hash, beberapa baris data dapat ditautkan bersama-sama untuk mendukung penerapan versi. Penunjuk halaman di antara tingkat adalah ID halaman logis, yang merupakan offset ke dalam tabel pemetaan halaman, yang pada gilirannya memiliki alamat fisik untuk setiap halaman.

Tidak ada pembaruan halaman indeks di tempat. Halaman delta baru diperkenalkan untuk tujuan ini.

  • Tidak diperlukan kait atau penguncian untuk pembaruan halaman.
  • Halaman indeks bukan ukuran tetap.

Nilai kunci di setiap halaman tingkat nonleaf adalah nilai tertinggi yang dimiliki oleh anak yang ditunjukkan olehnya, dan setiap baris juga berisi ID halaman logis dari halaman tersebut. Pada halaman tingkat daun, bersama dengan nilai kunci, berisi alamat fisik baris data.

Pencarian titik mirip dengan pohon B, kecuali karena halaman hanya ditautkan ke satu arah, Mesin Database mengikuti pointer ke halaman kanan, di mana setiap halaman nonleaf memiliki nilai tertinggi dari anak halaman tersebut, bukan nilai terendah seperti di pohon B.

Ketika terjadi perubahan pada halaman tingkat daun, Mesin Database tidak mengubah halaman itu sendiri. Sebaliknya, Mesin Database membuat catatan delta yang menjelaskan perubahan, dan menambahkannya ke halaman sebelumnya. Kemudian juga memperbarui alamat tabel peta halaman untuk halaman sebelumnya, ke alamat catatan delta yang sekarang menjadi alamat fisik untuk halaman ini.

Ada tiga operasi berbeda yang dapat diperlukan untuk mengelola struktur pohon Bw: konsolidasi, pemisahan, dan penggabungan.

Konsolidasi Delta

Rantai panjang rekaman delta akhirnya dapat menurunkan performa pencarian karena dapat memerlukan traversal rantai panjang saat mencari melalui indeks. Jika catatan delta baru ditambahkan ke rantai yang sudah memiliki 16 elemen, perubahan dalam rekaman delta dikonsolidasikan ke dalam halaman indeks yang direferensikan, dan halaman kemudian dibangun kembali, termasuk perubahan yang ditunjukkan oleh rekaman delta baru yang memicu konsolidasi. Halaman yang baru dibangun ulang memiliki ID halaman yang sama tetapi alamat memori baru.

Diagram memperlihatkan tabel pemetaan halaman yang dioptimalkan memori.

Pisahkan halaman

Halaman indeks di pohon Bw tumbuh sesuai kebutuhan mulai dari menyimpan satu baris hingga menyimpan maksimum 8 KB. Setelah halaman indeks tumbuh menjadi 8 KB, sisipan baru dari satu baris menyebabkan halaman indeks terpisah. Untuk halaman internal, ini berarti ketika tidak ada lagi ruang untuk menambahkan nilai kunci dan penunjuk lain, dan untuk halaman daun, itu berarti bahwa baris akan terlalu besar agar pas di halaman setelah semua rekaman delta dimasukkan. Informasi statistik di header halaman untuk halaman daun melacak berapa banyak ruang yang diperlukan untuk mengonsolidasikan rekaman delta. Informasi ini disesuaikan saat setiap catatan delta baru ditambahkan.

Operasi pemisahan dilakukan dalam dua langkah atom. Dalam diagram berikut, asumsikan halaman daun memaksa pemisahan karena kunci dengan nilai 5 sedang disisipkan, dan halaman nonleaf ada yang menunjuk ke akhir halaman tingkat daun saat ini (nilai kunci 4).

Diagram memperlihatkan operasi pemisahan indeks yang dioptimalkan memori.

Langkah 1: Alokasikan dua halaman P1 baru dan P2, dan pisahkan baris dari halaman lama P1 ke halaman baru ini, termasuk baris yang baru disisipkan. Slot baru dalam tabel pemetaan halaman digunakan untuk menyimpan alamat fisik halaman P2. Halaman P1 dan P2 belum dapat diakses oleh operasi bersamaan apa pun. Selain itu, pointer logis dari P1 ke P2 diatur. Kemudian, dalam satu langkah atom memperbarui tabel pemetaan halaman untuk mengubah penunjuk dari lama P1 ke yang baru P1.

Langkah 2: Halaman nonleaf menunjuk ke P1 tetapi tidak ada penunjuk langsung dari halaman nonleaf ke P2. P2 hanya dapat dijangkau melalui P1. Untuk membuat penunjuk dari halaman nonleaf ke P2, alokasikan halaman nonleaf baru (halaman indeks internal), salin semua baris dari halaman nonleaf lama, dan tambahkan baris baru untuk menunjuk ke P2. Setelah ini selesai, dalam satu langkah atom, perbarui tabel pemetaan halaman untuk mengubah penunjuk dari halaman nonleaf lama ke halaman nonleaf baru.

Gabungkan halaman

DELETE Ketika operasi menghasilkan halaman yang memiliki kurang dari 10 persen dari ukuran halaman maksimum (8 KB), atau dengan satu baris di dalamnya, halaman tersebut digabungkan dengan halaman yang berdekatan.

Saat baris dihapus dari halaman, catatan delta untuk penghapusan ditambahkan. Selain itu, pemeriksaan dilakukan untuk menentukan apakah halaman indeks (halaman nonleaf) memenuhi syarat untuk penggabungan. Pemeriksaan ini memverifikasi apakah ruang yang tersisa setelah menghapus baris kurang dari 10 persen dari ukuran halaman maksimum. Jika memenuhi syarat, penggabungan dilakukan dalam tiga langkah atomik.

Dalam gambar berikut, asumsikan DELETE operasi menghapus nilai kunci 10.

Diagram memperlihatkan operasi penggabungan indeks yang dioptimalkan memori.

Langkah 1: Halaman delta yang mewakili nilai 10 kunci (segitiga biru) dibuat dan penunjuknya di halaman Pp1 nonleaf diatur ke halaman delta baru. Selain itu, halaman merge-delta khusus (segitiga hijau) dibuat, dan ditautkan untuk menunjuk ke halaman delta. Pada tahap ini, kedua halaman (halaman delta dan halaman merge-delta) tidak terlihat oleh transaksi bersamaan. Dalam satu langkah atomik, penunjuk ke halaman P1 tingkat daun dalam tabel pemetaan halaman diperbarui untuk menunjuk ke halaman merge-delta. Setelah langkah ini, entri untuk nilai 10 kunci di Pp1 sekarang menunjuk ke halaman merge-delta.

Langkah 2: Baris yang mewakili nilai 7 kunci di halaman Pp1 nonleaf perlu dihapus, dan entri untuk nilai 10 kunci yang diperbarui untuk menunjuk ke P1. Untuk melakukan ini, halaman Pp2 nonleaf baru dialokasikan dan semua baris dari Pp1 disalin, kecuali untuk baris yang mewakili nilai 7kunci ; maka baris untuk nilai 10 kunci diperbarui untuk menunjuk ke halaman P1. Setelah ini selesai, dalam satu langkah atom, entri tabel pemetaan halaman yang menunjuk ke Pp1 diperbarui untuk menunjuk ke Pp2. Pp1 tidak lagi dapat dijangkau.

Langkah 3: Halaman tingkat P2 daun dan P1 digabungkan dan halaman delta dihapus. Untuk melakukan ini, halaman P3 baru dialokasikan dan baris dari P2 dan P1 digabungkan, dan perubahan halaman delta disertakan dalam baru P3. Kemudian, dalam satu langkah atomik, entri tabel pemetaan halaman yang menunjuk ke halaman P1 diperbarui untuk menunjuk ke halaman P3.

Pertimbangan performa untuk indeks nonclustered yang dioptimalkan memori

Performa indeks nonclustered lebih baik daripada dengan indeks hash saat mengkueri tabel yang dioptimalkan memori dengan predikat ketidaksamaan.

Kolom dalam tabel yang dioptimalkan memori dapat menjadi bagian dari indeks hash dan indeks nonclustered.

Saat kolom kunci dalam indeks yang tidak dikluster memiliki banyak nilai duplikat, performa dapat menurun untuk pembaruan, penyisipan, dan penghapusan. Salah satu cara untuk meningkatkan performa dalam situasi ini adalah dengan menambahkan kolom yang memiliki selektivitas yang lebih baik dalam kunci indeks.

Indeks metadata

Untuk memeriksa metadata indeks seperti definisi indeks, properti, dan statistik data, gunakan tampilan sistem berikut:

Tampilan sebelumnya berlaku untuk semua jenis indeks. Untuk indeks penyimpan kolom, gunakan juga tampilan berikut:

Untuk indeks penyimpan kolom, semua kolom disimpan dalam metadata sebagai kolom yang disertakan. Indeks penyimpan kolom tidak memiliki kolom kunci.

Untuk indeks pada tabel yang dioptimalkan memori, selain itu gunakan tampilan berikut:

Konten terkait

  • Last updated on