Membuat indeks yang difilter

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Artikel ini menjelaskan cara membuat indeks yang difilter menggunakan SQL Server Management Studio (SSMS) atau Transact-SQL. Indeks yang difilter adalah indeks nonclustered rowstore berbasis disk yang dioptimalkan terutama cocok untuk mencakup kueri yang memilih dari subset data yang ditentukan dengan baik. Ini menggunakan predikat filter untuk mengindeks sebagian baris dalam tabel. Indeks terfilter yang dirancang dengan baik dapat meningkatkan performa kueri dan mengurangi pemeliharaan indeks dan biaya penyimpanan dibandingkan dengan indeks tabel penuh.

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

  1. Peningkatan performa kueri dan kualitas rencana.

    Indeks terfilter yang dirancang dengan baik meningkatkan performa kueri dan kualitas rencana eksekusi karena lebih kecil dari indeks non-kluster tabel penuh dan memiliki statistik yang difilter. Statistik yang difilter lebih akurat daripada statistik tabel penuh karena hanya mencakup baris dalam indeks yang difilter.

  2. Mengurangi biaya pemeliharaan indeks.

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

  3. Mengurangi biaya penyimpanan indeks.

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

Pertimbangan Desain

Saat kolom hanya memiliki beberapa nilai yang relevan untuk kueri, Anda dapat membuat indeks yang difilter pada subset nilai. Indeks yang dihasilkan akan lebih kecil dan lebih murah untuk dipertahankan daripada indeks nonkluster tabel penuh yang ditentukan pada kolom kunci yang sama.

Misalnya, pertimbangkan indeks yang difilter dalam skenario data berikut. Dalam setiap kasus, WHERE klausa kueri harus menjadi subset klausa WHERE indeks yang difilter, untuk mendapatkan manfaat dari indeks yang difilter.

  • Saat nilai dalam kolom sebagian besar NULL dan kueri hanya memilih dari nilai non-NULL. Anda dapat membuat indeks yang difilter untuk baris data non-NULL.
  • Saat baris dalam tabel ditandai sebagai diproses oleh alur kerja atau proses antrean berulang. Seiring waktu, sebagian besar baris dalam tabel akan ditandai sebagai diproses. Indeks yang difilter pada baris yang belum diproses akan menguntungkan kueri berulang yang mencari baris yang belum diproses.
  • Saat tabel memiliki baris data heterogen. Anda dapat membuat indeks yang difilter untuk satu atau beberapa kategori data. Ini dapat meningkatkan performa kueri pada baris data ini dengan mempersempit fokus kueri ke area tabel tertentu. Sekali lagi, indeks yang dihasilkan akan lebih kecil dan lebih murah untuk dipertahankan daripada indeks nonclustered tabel penuh.

Pembatasan dan batasan

  • Anda tidak dapat membuat indeks yang difilter pada tampilan. Namun, pengoptimal kueri dapat memperoleh manfaat dari indeks yang difilter yang ditentukan pada tabel yang direferensikan dalam tampilan. Pengoptimal kueri mempertimbangkan indeks yang difilter untuk kueri yang memilih dari tampilan jika hasil kueri akan benar.

  • Anda tidak dapat membuat indeks yang difilter pada tabel saat kolom yang diakses dalam ekspresi filter adalah jenis data CLR.

  • Indeks yang difilter memiliki keuntungan berikut daripada tampilan terindeks:

    • Mengurangi biaya pemeliharaan indeks. Misalnya, prosesor kueri menggunakan lebih sedikit sumber daya CPU untuk memperbarui indeks yang difilter daripada tampilan terindeks.

    • Peningkatan kualitas rencana. Misalnya, selama kompilasi kueri, pengoptimal kueri mempertimbangkan untuk menggunakan indeks yang difilter dalam situasi yang lebih banyak daripada tampilan terindeks yang setara.

    • Pembangunan ulang indeks online. Anda dapat membangun kembali indeks yang difilter saat tersedia untuk kueri. Pembangunan ulang indeks online tidak didukung untuk tampilan terindeks. Untuk informasi selengkapnya, lihat REBUILD opsi untuk ALTER INDEX (Transact-SQL).

    • Indeks yang tidak unik. Indeks yang difilter bisa tidak unik, sedangkan tampilan terindeks harus unik.

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

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

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

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

  • 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. Pelajari selengkapnya dalam arsitektur indeks dan panduan desain.

  • Jika operator perbandingan yang ditentukan dalam ekspresi indeks yang difilter dari indeks yang difilter menghasilkan konversi data implisit atau eksplisit, kesalahan akan 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.

  • Tinjau opsi yang diperlukan SET untuk pembuatan indeks yang difilter dalam sintaks CREATE INDEX (Transact-SQL)

  • Filter tidak dapat diterapkan ke kunci primer atau batasan unik, tetapi dapat diterapkan ke indeks dengan UNIQUE properti .

  • Anda tidak dapat membuat indeks yang difilter pada kolom komputasi.

Izin

Memerlukan izin ALTER pada tabel atau tampilan. Pengguna harus menjadi anggota peran server tetap sysadmin atau peran database tetap db_ddladmin dan db_owner . Untuk mengubah ekspresi indeks yang difilter, gunakan CREATE INDEX WITH DROP_EXISTING.

Membuat indeks yang difilter dengan SSMS

  1. Di Object Explorer, pilih tanda plus untuk memperluas database yang berisi tabel tempat Anda ingin membuat indeks yang difilter.

  2. Pilih tanda plus untuk memperluas folder Tabel .

  3. Pilih tanda plus untuk memperluas tabel tempat Anda ingin membuat indeks yang difilter.

  4. Klik kanan folder Indeks, arahkan ke Indeks Baru, dan pilih Indeks Non-Kluster....

  5. Dalam kotak dialog Indeks Baru, pada halaman Umum , masukkan nama indeks baru dalam kotak Nama indeks.

  6. Di bawah Kolom kunci indeks, pilih Tambahkan....

  7. Dalam kotak dialog Pilih Kolom daritable_name , pilih kotak centang atau kotak centang kolom tabel atau kolom yang akan ditambahkan ke indeks.

  8. PilihOK.

  9. Pada halaman Filter , di bawah Ekspresi Filter, masukkan ekspresi SQL yang akan Anda gunakan untuk membuat indeks yang difilter.

  10. PilihOK.

Membuat indeks yang difilter dengan Transact-SQL

Artikel ini memerlukan AdventureWorks2022 database sampel, yang dapat Anda unduh dari beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

  1. Di Object Explorer, sambungkan ke instans Mesin Database.

  2. Pada bilah Standar, pilih Kueri Baru.

  3. Salin dan tempel contoh berikut ke dalam jendela kueri dan pilih Jalankan.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

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

Indeks FIBillOfMaterialsWithEndDate yang difilter valid untuk kueri berikut. Anda bisa menampilkan rencana eksekusi kueri untuk menentukan apakah pengoptimal kueri menggunakan indeks yang difilter.

USE AdventureWorks2022;
GO

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

Langkah berikutnya

Untuk mempelajari selengkapnya tentang membuat indeks dan konsep terkait, lihat artikel berikut ini: