Indeks. Indeks dalam indeks unik SQL Sql
Dan indeks, ini tabel pencarian khusus mesin pencari database mana yang dapat digunakan untuk mempercepat pengambilan data. Sederhananya, indeks adalah penunjuk ke data dalam tabel. Indeks dalam database sangat mirip dengan indeks di bagian belakang buku.
Misalnya, jika Anda ingin tautan ke semua halaman dalam buku tentang topik tertentu, pertama-tama lihat indeks yang mencantumkan semua topik dalam urutan abjad, lalu berikan satu atau beberapa nomor halaman tertentu.
Indeks membantu mempercepat kueri dan saran, tetapi memperlambat entri data, dengan pernyataan MEMPERBARUI Dan MENYISIPKAN. Indeks dapat dibuat atau dijatuhkan tanpa memengaruhi data.
Membuat indeks melibatkan pernyataan BUAT INDEKS, yang memungkinkan Anda memberi nama indeks untuk menentukan tabel dan kolom atau kolom mana yang akan diindeks dan untuk menentukan apakah indeks dalam urutan menaik atau menurun.
Indeks juga bisa unik, dengan kendala UNIK, sehingga indeks mencegah duplikat entri dalam kolom atau kombinasi kolom yang memiliki indeks.
Perintah BUAT INDEKS
Sintaks Dasar BUAT INDEKS sebagai berikut:
BUAT INDEX nama_indeks PADA nama_tabel;
Indeks kolom tunggal
Indeks kolom tunggal dibuat hanya dari satu kolom tabel. Sintaks dasarnya adalah sebagai berikut.
BUAT INDEX nama_indeks PADA nama_tabel(nama_kolom);
Indeks Unik
Indeks unik digunakan tidak hanya untuk pekerjaan, tetapi juga untuk memastikan integritas data. Indeks unik tidak mengizinkan nilai duplikat apa pun untuk dimasukkan ke dalam tabel. Sintaks dasarnya adalah sebagai berikut.
BUAT nama_indeks INDEKS UNIK pada nama_tabel(nama_kolom);
Indeks komposit
Indeks komposit adalah indeks pada dua atau lebih kolom dalam sebuah tabel. Sintaks dasarnya adalah sebagai berikut.
BUAT INDEX nama_indeks pada nama_tabel(kolom1, kolom2);
Apakah Anda membuat indeks pada satu kolom atau indeks gabungan, pertimbangkan kolom yang mungkin sering Anda gunakan dalam kueri WHERE sebagai kondisi filter.
Jika hanya ada satu kolom yang digunakan, indeks harus dipilih pada satu kolom. Jika ada dua atau lebih kolom yang sering digunakan sebagai filter dalam klausa WHERE, indeks komposit akan menjadi pilihan yang lebih baik.
Indeks implisit
Indeks implisit adalah indeks yang dibuat secara otomatis di server basis data saat objek dibuat. Indeks secara otomatis dibuat untuk kunci utama dan batasan unik.
Perintah DROP INDEKS
Indeks dapat dijatuhkan menggunakan perintah SQL MENJATUHKAN. Berhati-hatilah saat menurunkan indeks karena kinerja dapat melambat atau meningkat.
Sintaks dasar terlihat seperti ini:
DROP INDEX index_name;
Anda dapat melihat contoh kendala INDEX untuk melihat beberapa contoh nyata pada indeks.
Kapan indeks harus dihindari?
Meskipun indeks dimaksudkan untuk meningkatkan kinerja database, ada kalanya indeks tersebut harus dihindari.
Petunjuk berikut menunjukkan kapan penggunaan indeks harus dipertimbangkan kembali.
- Indeks tidak boleh digunakan pada tabel kecil.
- Tabel yang sering memiliki pembaruan atau sisipan besar.
- Indeks tidak boleh digunakan pada kolom yang berisi sejumlah besar nilai nol.
- Kolom yang sering dimanipulasi sebaiknya tidak diindeks.
6. Pembuatan dan penggunaan indeks dan kunci dalam sistem sql server.
Indeks adalah struktur fisik database yang terpisah, dibuat berdasarkan tabel dan dirancang untuk mempercepat pemilihan data, pencarian yang dilakukan berdasarkan nilai dari kolom yang diindeks. Selain itu, SQL Server menggunakan indeks untuk memastikan keunikan baris dan kolom tabel, mengatur informasi, dan mendistribusikan data tabel dalam satu file atau grup file untuk meningkatkan kecepatan akses.
Di SQL Server, data tabel dan indeks disimpan sebagai halaman dalam format berikut:
Di SQL Server, ruang disk untuk tabel dan indeks dibagi menjadi blok 8 halaman yang disebut luasan. Setelah mengisi satu tingkat, objek dialokasikan berikutnya (8 halaman lagi).
SQL Server menggunakan skema pohon biner untuk mewakili indeks:
Tingkat 1
Tingkat 0
Setiap persegi panjang dalam diagram mewakili halaman indeks. Saat jumlah level meningkat, kinerja pemrosesan indeks menurun. SQL Server mendukung dua jenis indeks: berkerumun dan tidak berkerumun.
Indeks berkerumun adalah pohon biner di mana pada halaman tingkat nol (tingkat daun) dari data aktual tabel terkandung dan informasi disimpan secara fisik dalam urutan logis dari indeks ini. Membuat indeks berkerumun membutuhkan 1,2 kali lebih banyak ruang disk kosong di database daripada jumlah data tabel yang ada. Hanya satu indeks berkerumun yang dapat ada per tabel.
Dalam kasus indeks non-cluster, halaman tingkat daun tidak berisi data tabel aktual, tetapi penunjuk ke deretan data, termasuk nomor halaman data dan nomor urut catatan pada halaman. Indeks non-cluster tidak memerlukan penataan ulang fisik baris data tabel dan, karenanya, tidak memerlukan ruang disk kosong yang besar di database.
Indeks non-cluster selalu memiliki satu level lebih banyak daripada indeks cluster, jadi setelah mencapai level daun, diperlukan pembacaan tambahan dari halaman data. Jika tabel memiliki indeks berkerumun, pointer baris indeks nonclustered akan menunjuk ke tingkat daun dari indeks berkerumun. Jika tabel tidak memiliki indeks berkerumun, indeks baris menyertakan pengidentifikasi file, nomor halaman data, dan nomor catatan halaman.
Hingga 249 indeks nonclustered dapat dibuat pada satu tabel. Baris indeks tidak boleh lebih dari 900 byte dan tidak boleh menyertakan lebih dari 16 kolom nilai.
Untuk tabel apa pun, cukup memiliki satu indeks berkerumun dan 2 - 6 indeks tidak berkerumun (dengan pengecualian membuat gudang data).
Indeks tidak dapat dibuat pada kolom dengan tipe data berikut: BIT, TEXT, IMAGE. Indeks tidak dapat dibuat untuk tampilan.
Anda dapat menggunakan salah satu metode berikut untuk membuat indeks pada tabel database SQL Server tertentu:
Buat indeks menggunakan perintah CREATE INDEX SQL;
Manfaatkan utilitas SQL Server Enterprise Manager.
Pertimbangkan cara kedua untuk membuat indeks. Langkah pertama dalam membuat indeks adalah memilih database dan tabel yang diperlukan yang akan ditentukan.
Eksekusi perintah. Semua tugas/MengelolaIndeks menu Tindakan akan menampilkan kotak dialog manajemen indeks database. Anda harus memperhatikan daftar drop-down dari kotak dialog ini. basis data Dan meja, yang memungkinkan Anda menavigasi antara database dan tabelnya. Sementara dalam daftar Yang adaindeks menampilkan indeks yang tersedia untuk tabel database yang dipilih.
Di bagian bawah kotak dialog ini terdapat tombol kontrol yang melakukan tindakan berikut:
Baru- pembuatan indeks baru untuk tabel database yang dipilih;
Sunting- mengedit parameter indeks yang ada;
Menghapus- menghapus indeks yang dipilih sebelumnya;
menutup- tutup kotak dialog;
Membantu- Mendapatkan informasi latar belakang tentang masalah ini.
Untuk membuat indeks baru, gunakan tombol Baru kotak dialog ini. Tindakan ini akan membuka kotak dialog lain MembuatBaruDi dalam dex, dengan mana parameter indeks ditetapkan. Di lapangan Indeksnama Di kotak dialog ini, Anda harus memasukkan nama indeks yang akan dibuat, lalu menentukan daftar bidang yang berpartisipasi dalam indeks. Untuk menambahkan bidang tertentu ke indeks, pilih kotak centang di sebelah kiri namanya. Anda juga dapat melihat informasi bidang berikut di sini: Kolom- nama bidang, Datajenis- tipe data, Panjang- ukuran, Dapat dibatalkan- kemampuan untuk menggunakan nilai Null, presisi- akurasi dan Skala- urutan nilai masukan. Anda dapat mengubah urutan bidang dalam daftar yang disajikan.
Grup opsi Indekspilihan memungkinkan Anda mengonfigurasi parameter tambahan dari indeks yang dibuat:
Uniknilai-nilai– jika Anda hanya perlu memasukkan nilai unik di bidang tertentu, Anda harus mengatur opsi ini. Ini akan memungkinkan pemeriksaan keunikan otomatis setiap kali entri baru ditambahkan. Jika upaya dilakukan untuk memasukkan nilai yang sudah ada dalam catatan untuk bidang ini, pesan kesalahan akan dikeluarkan. Dalam hal ini, Anda harus memperhatikan larangan keberadaan nilai NULL di bidang ini. Kesalahan dapat terjadi saat menggunakan nilai NULL dan menyetel opsi ini. Oleh karena itu, disarankan untuk menetapkan entri nilai wajib di bidang yang Anda rencanakan untuk membuat indeks unik;
berkerumunindeks- dalam sistem SQL-server ada kemungkinan pengindeksan fisik data. Dengan kata lain, menggunakan indeks membuat struktur terpisah yang dikaitkan dengan lokasi fisik data dalam tabel. Menggunakan opsi ini memungkinkan Anda untuk melakukan apa yang disebut pengindeksan berkerumun, sebagai akibatnya data dalam tabel itu sendiri akan diurutkan sesuai dengan urutan indeks ini, dan semua informasi tambahan akan menyebabkan perubahan urutan fisik data. Dalam hal ini, harus diperhatikan bahwa hanya satu indeks berkerumun yang dapat ditentukan dalam sebuah tabel;
Mengabaikanduplikatnilai-nilai- memilih opsi ini menyebabkan pengabaian masuknya nilai duplikat di bidang yang diindeks;
Melakukanbukanmenghitung ulangstatistik- pengaturan opsi ini menentukan fungsi pembaruan otomatis statistik untuk tabel;
Mengajukankelompok- menggunakan opsi ini, Anda dapat memilih grup file tempat indeks yang dibuat akan ditempatkan;
Mengisifaktor- Fitur ini jarang digunakan. Opsi ini mengonfigurasi halaman indeks. Jika Anda berencana untuk sering mengubah, menghapus, dan menambahkan informasi ke tabel database, maka koefisiennya FAKTOR PENGISI harus ditetapkan serendah mungkin, misalnya 20. Mengatur faktor ke 100 disarankan saat menggunakan tabel besar yang biasanya hanya-baca;
Bantalanindeks- opsi menentukan pengisian ruang indeks internal dan digunakan bersamaan dengan opsi Mengisifakta tor;
Menjatuhkanyang ada- saat menggunakan indeks berkerumun, memilih opsi ini menentukan pembuatan ulangnya, yang membantu mencegah pembaruan indeks berkerumun yang tidak diinginkan.
Penggunaan tombol SuntingSQL kotak dialog ini memberi pengguna perintah SQL yang dihasilkan, yang akan digunakan untuk melakukan pengaturan yang dibuat. Jendela memiliki tombol kontrol Mengurai Dan Menjalankan, yang dengannya Anda dapat menganalisis kebenaran pengaturan yang diinstal (Parse), serta menjalankan perintah SQL yang diterima (Jalankan).
Indeks yang dibuat selanjutnya dapat digunakan dalam pernyataan SQL PILIH dengan cara berikut:
PILIH...
DARI <имя таблицы> (INDEKS= <имя_индекса>)
Salah satu konsep dasar basis data yang digunakan untuk mengontrol integritas informasi adalah kunci. Pisahkan kunci primer dan kunci asing. kunci utama adalah bidang unik (atau beberapa bidang) yang secara unik mengidentifikasi catatan dalam tabel database. Kunci asing adalah bidang dalam tabel yang biasanya sesuai dengan kunci utama dari tabel lain.
Pertimbangkan perbedaan utama antara indeks dan kunci:
SQL Server memungkinkan Anda untuk menentukan hanya satu kunci utama untuk sebuah tabel, sementara Anda dapat membuat beberapa indeks unik;
saat menggunakan kunci utama, kemungkinan memasukkan nilai NULL dilarang, sedangkan saat bekerja dengan indeks unik, larangan ini tidak wajib, tetapi disarankan untuk mematuhinya.
Pertimbangkan proses pembuatan kunci utama menggunakan utilitas SQL Server Enterprise Manager. Langkah pertama dalam memecahkan masalah ini adalah memilih tabel dalam daftar objek database. Eksekusi perintah desainmeja menu Tindakan akan memuat perancang tabel, di jendela di mana Anda harus memilih bidang yang diperlukan, hapus centang pada kolom mengizinkanNull untuk bidang-bidang ini. Pengaturan kunci utama dilakukan dengan menggunakan tombol mengaturutamakunci.
Jika operasi ini dilakukan dengan benar, maka ikon kunci akan muncul di sebelah kiri nama bidang/bidang. Menghapus kunci utama dilakukan dengan cara yang sama seperti memasangnya.
Perintah Transact-SQL digunakan untuk membuat indeks.
CREATE INDEX, sintaks umumnya adalah:
BUAT INDEKS<имя индекса>PADA<имя таблицы>(nama kolom[, nama kolom]...)
[,] FILLFACTOR=x][[,]
IGNORE_DUP_KEY][[,] DROP_EXISTING)[[,]
STATISTICS_NORECOMPUTE]]
Pertimbangkan parameter dari perintah ini:
PAD_INDEX adalah jumlah ruang yang dibiarkan terbuka di setiap halaman dalam. Secara default, jumlah item di halaman dalam adalah ≥2. Parameter ini digunakan bersamaan dengan FILLFACTOR dan menggunakan nilai persentase dari parameter ini.
IGNORE_DUP_KEY - memungkinkan Anda untuk terus bekerja meskipun Anda mencoba memasukkan baris dengan nilai duplikat dari bidang kunci unik ke dalam tabel - pesan ditampilkan di layar, dan baris tersebut diabaikan.
DROP_EXISTING - saat menggunakan opsi ini, indeks berkerumun yang ada dijatuhkan dan dibuat ulang, indeks non-berkerumun yang ada dibangun kembali hanya setelah indeks berkerumun baru dibuat
STATISTICS_NORECOMPUTE - memblokir pembaruan otomatis informasi statistik pada indeks.
Mari pertimbangkan operasi lain pada indeks.
1) Lihat indeks:
a) di jendela SQL Server Enterprise Manager, pilih database (ikon di folder Database);
b) pergi ke tab mejaDanindeks- menampilkan nama semua tabel dan nama indeks terkait untuk database yang dipilih.
2) Mengganti nama, menghapus indeks.
Untuk menjatuhkan indeks, gunakan perintah Transact-SQL:
MENJATUHKANINDEKS[pemilik.]<имя_ таблицы>. <имя_индекса>[,[pemilik.]<имя_таблицы>, <имя_индекса>]
Mengganti nama indeks dilakukan dengan perintah:
sp_ ganti nama <имя_объекта>, <новое имя> [,KOLOM| INDEKS]
Anda juga dapat menggunakan jendela SQL Server Enterprise Manager: buka tabel di desainer, pilih dari menu konteks untuk tabel Properti, di kotak dialog mejaProperti pilih tab Indeks/ kunci- di sini Anda dapat mengganti nama indeks dan membuat indeks baru.
Strategi indeks:
1) Harus diindeks:
kolom yang digunakan untuk menggabungkan tabel;
kolom yang digunakan untuk membatasi rentang data yang diuraikan saat menjalankan kueri;
kolom yang digunakan dalam arahan ORDEROLEH Dan
KELOMPOKOLEH;
kolom yang digunakan untuk menjumlahkan dan meringkas.
2) Tidak boleh diindeks:
tabel dengan beberapa baris;
kolom yang memiliki rentang nilai yang luas;
kolom dengan nilai yang sangat panjang (>25 byte);
kolom yang tidak digunakan saat membuat kueri.
3) Dianjurkan untuk menggunakan indeks berkerumun untuk kolom:
jika kolom digunakan dalam banyak kueri;
jika kolom digunakan dalam ORDER BY atau
jika kolom digunakan untuk menggabungkan tabel.
Menggunakan fitur pemilihan jenis indeks otomatis:
buka Query Analyzer;
pilih nama database dalam daftar DB;
masukkan teks dari perintah SQL;
Pilih tim Kueri/Lakukan Analisis Indeks.
SQL Server akan menganalisis kueri untuk menentukan apakah mungkin membuat indeks yang akan membantu mempercepat kueri. Jika indeks dapat ditemukan, sebuah jendela akan ditampilkan PertanyaanPenganalisis. Untuk membuat indeks yang disarankan, klik tombol menerima.
6. Indeks dan optimalisasi kinerja
Indeks dalam database: tujuan, dampak pada kinerja, prinsip pembuatan indeks
6.1 Mengapa indeks diperlukan
Indeks adalah struktur khusus dalam basis data yang memungkinkan Anda mempercepat pencarian dan pengurutan berdasarkan bidang atau kumpulan bidang tertentu dalam tabel, dan juga digunakan untuk memastikan keunikan data. Cara termudah untuk membandingkan indeks adalah dengan indeks di buku. Jika tidak ada penunjuk, maka kita harus melihat seluruh buku untuk menemukan tempat yang tepat, dan dengan penunjuk, tindakan yang sama dapat dilakukan lebih cepat.
Biasanya, semakin banyak indeks, semakin baik kinerja kueri basis data. Namun, dengan peningkatan jumlah indeks yang berlebihan, kinerja operasi modifikasi data (memasukkan/memodifikasi/menghapus) menurun, ukuran database meningkat, sehingga menambahkan indeks harus diperlakukan dengan hati-hati.
Beberapa prinsip umum terkait pembuatan indeks:
· Indeks harus dibuat untuk kolom yang digunakan dalam gabungan, yang sering dicari dan disortir. Perlu dicatat bahwa indeks selalu dibuat secara otomatis untuk kolom yang tunduk pada batasan kunci utama. Paling sering mereka juga dibuat untuk kolom dengan kunci asing (di Access - secara otomatis);
· indeks harus dibuat secara otomatis untuk kolom yang memiliki batasan unik;
· Yang terbaik adalah membuat indeks untuk bidang-bidang di mana - jumlah minimum nilai duplikat dan datanya didistribusikan secara merata. Oracle memiliki indeks bitwise khusus untuk kolom dengan sejumlah besar nilai duplikat, SQL Server dan Access tidak menyediakan indeks semacam ini;
· jika pencarian terus-menerus dilakukan pada kumpulan kolom tertentu (pada waktu yang sama), maka dalam kasus ini, mungkin masuk akal untuk membuat indeks komposit (hanya di SQL Server) - satu indeks untuk sekelompok kolom;
· Ketika perubahan dilakukan pada tabel, indeks yang dikenakan pada tabel ini secara otomatis berubah. Akibatnya, indeks bisa sangat terfragmentasi, yang memengaruhi kinerja. Indeks harus diperiksa secara berkala untuk fragmentasi dan defragmented. Saat memuat sejumlah besar data, terkadang masuk akal untuk menghapus semua indeks terlebih dahulu, dan setelah operasi selesai, buat lagi;
· Indeks dapat dibuat tidak hanya untuk tabel, tetapi juga untuk tampilan (hanya di SQL Server). Keuntungan - kemampuan untuk menghitung bidang bukan pada saat permintaan, tetapi pada saat nilai baru muncul di tabel.
1) Konsep indeks
Indeks adalah alat yang menyediakan akses cepat ke baris tabel berdasarkan nilai dari satu atau lebih kolom.
Variasi operator ini banyak sekali, karena tidak terstandarisasi, karena standar tidak menyangkut masalah kinerja.
2) Buat indeks
BUAT INDEKS
PADA()
3) Mengubah dan menghapus indeks
Operator digunakan untuk mengontrol aktivitas indeks:
MENGUBAH INDEKS
Untuk menghapus indeks, gunakan operator:
INDEKS JATUH
a) Aturan pemilihan tabel
1. Dianjurkan untuk mengindeks tabel yang tidak lebih dari 5% dari baris yang dipilih.
2. Tabel indeks yang tidak memiliki duplikat di klausa WHERE dari pernyataan SELECT.
3. Mengindeks tabel yang sering diperbarui tidak praktis.
4. Tidak pantas mengindeks tabel yang menempati tidak lebih dari 2 halaman (untuk Oracle, ini kurang dari 300 baris), karena pemindaian penuhnya tidak memakan waktu lebih lama.
b) Aturan Pemilihan Kolom
1. Kunci primer dan asing - sering digunakan untuk menggabungkan tabel, memilih data, dan mencari. Ini selalu merupakan indeks unik dengan utilitas maksimum
2. Saat menggunakan opsi integritas referensial, indeks pada FK selalu diperlukan.
3. Kolom dimana data sering diurutkan dan/atau dikelompokkan.
4. Kolom sering dicari di klausa WHERE dari pernyataan SELECT.
5. Jangan membuat indeks untuk kolom deskriptif yang panjang.
c) Prinsip untuk membuat indeks komposit
1. Indeks komposit bagus jika kolom secara individual memiliki sedikit nilai unik, dan indeks komposit memberikan lebih banyak keunikan.
2. Jika semua nilai yang dipilih oleh pernyataan SELECT termasuk dalam indeks komposit, maka nilai tersebut dipilih dari indeks.
3. Anda harus membuat indeks gabungan jika klausa WHERE menggunakan dua nilai atau lebih yang digabungkan dengan operator AND.
d) Tidak disarankan untuk membuat
Tidak disarankan untuk membuat indeks pada kolom, termasuk yang komposit, yang:
1. Jarang digunakan untuk mencari, menggabungkan, dan menyortir hasil kueri.
2. Berisi nilai yang sering berubah, yang membutuhkan pembaruan indeks yang sering memperlambat kinerja database.
3. Berisi sejumlah kecil nilai unik (kurang dari 10% m/f) atau sejumlah besar baris dengan satu atau dua nilai (kota tempat tinggal pemasok Moskow).
4. Fungsi atau ekspresi diterapkan padanya di klausa WHERE, dan indeks tidak berfungsi.
e) Jangan lupa
Anda harus berusaha untuk mengurangi jumlah indeks, karena jumlah indeks yang banyak mengurangi kecepatan pembaruan data. Jadi MS SQL Server merekomendasikan untuk membuat tidak lebih dari 16 indeks per tabel.
Biasanya, indeks dibuat untuk menanyakan dan mempertahankan integritas referensial.
Jika indeks tidak digunakan untuk kueri, indeks harus dihapus dan integritas referensial ditegakkan menggunakan pemicu.
Artikel ini membahas indeks dan perannya dalam mengoptimalkan waktu eksekusi kueri. Bagian pertama artikel ini membahas berbagai bentuk indeks dan cara penyimpanannya. Tiga pernyataan Transact-SQL utama yang digunakan untuk bekerja dengan indeks diperiksa selanjutnya: CREATE INDEX, ALTER INDEX, dan DROP INDEX. Kemudian fragmentasi indeks dipertimbangkan dan dampaknya terhadap kinerja sistem. Setelah itu, beberapa pedoman umum untuk membuat indeks diberikan dan beberapa jenis indeks khusus dijelaskan.
Informasi Umum
Sistem basis data biasanya menggunakan indeks untuk menyediakan akses cepat ke data relasional. Indeks adalah struktur data fisik terpisah yang memungkinkan Anda mengakses satu atau beberapa baris data dengan cepat. Dengan demikian, penyetelan indeks yang tepat adalah aspek kunci untuk meningkatkan kinerja kueri.
Indeks basis data mirip dalam banyak hal dengan indeks (indeks abjad) dari sebuah buku. Ketika kita perlu menemukan topik dalam sebuah buku dengan cepat, pertama-tama kita melihat indeks di halaman buku mana topik ini dibahas, dan kemudian segera membuka halaman yang diinginkan. Demikian pula, saat mencari baris tertentu dalam tabel, Mesin Basis Data mencari indeks untuk mengetahui lokasi fisiknya.
Tapi ada dua perbedaan signifikan antara indeks buku dan indeks basis data:
Pembaca buku bebas memutuskan apakah akan menggunakan indeks berdasarkan kasus per kasus atau tidak. Pengguna basis data tidak memiliki kesempatan seperti itu, dan keputusan ini dibuat untuknya oleh komponen sistem yang disebut pengoptimal kueri. (Pengguna dapat memanipulasi penggunaan indeks melalui petunjuk indeks, tetapi petunjuk ini hanya direkomendasikan dalam kasus khusus dalam jumlah terbatas.)
Indeks untuk buku tertentu dibuat dengan buku tersebut, setelah itu tidak lagi dimodifikasi. Artinya, indeks untuk topik tertentu akan selalu mengarah ke nomor halaman yang sama. Sebaliknya, indeks basis data dapat berubah setiap kali data terkait berubah.
Jika tabel tidak memiliki indeks yang sesuai, sistem menggunakan metode pemindaian tabel untuk mengambil baris. Ekspresi pemindaian tabel berarti bahwa sistem mengambil dan memeriksa setiap baris tabel secara berurutan (dari yang pertama hingga yang terakhir), dan menempatkan baris tersebut di kumpulan hasil jika kondisi pencarian di klausa WHERE dipenuhi untuk itu. Dengan demikian, semua baris diambil sesuai dengan lokasi fisiknya di memori. Metode ini kurang efisien dibandingkan akses indeks, seperti yang akan dijelaskan selanjutnya.
Indeks disimpan dalam struktur database tambahan yang disebut halaman indeks. Untuk setiap baris yang diindeks, ada entri indeks, yang disimpan di halaman indeks. Setiap elemen indeks terdiri dari kunci indeks dan pointer. Inilah mengapa entri indeks jauh lebih pendek daripada baris tabel yang ditunjuknya. Untuk alasan ini, jumlah entri indeks di setiap halaman indeks jauh lebih besar daripada jumlah baris di halaman data.
Properti indeks ini sangat penting karena jumlah I/O yang diperlukan untuk melintasi halaman indeks jauh lebih sedikit daripada jumlah I/O yang diperlukan untuk melintasi halaman data yang sesuai. Dengan kata lain, pemindaian tabel kemungkinan akan membutuhkan lebih banyak I/O daripada pemindaian indeks dari tabel itu.
Indeks Database Engine dibuat menggunakan struktur data pohon B+. Pohon B+ memiliki struktur pohon di mana semua simpul terendah memiliki jumlah level yang sama jauhnya dari puncak (simpul akar) pohon. Properti ini dipertahankan bahkan saat data ditambahkan atau dihapus dari kolom yang diindeks.
Gambar di bawah menunjukkan struktur pohon B+ untuk tabel Karyawan dan akses langsung ke baris dalam tabel tersebut dengan nilai 25348 untuk kolom Id. (Diasumsikan bahwa tabel Karyawan diindeks oleh kolom Id.) Pada gambar ini, Anda juga dapat melihat bahwa pohon B+ terdiri dari simpul akar, simpul pohon, dan simpul perantara, yang jumlahnya bisa nol atau lebih :
Anda dapat mencari pohon ini untuk nilai 25348 sebagai berikut. Dimulai dari akar pohon, pencarian dilakukan untuk nilai kunci terkecil yang lebih besar atau sama dengan nilai yang diinginkan. Jadi, di simpul akar, nilai ini akan menjadi 29346, jadi transisi dilakukan ke simpul perantara yang terkait dengan nilai ini. Pada simpul ini, nilai 28559 memenuhi persyaratan yang ditentukan, yang melompat ke simpul pohon yang terkait dengan nilai ini. Node ini berisi nilai yang diinginkan 25348. Setelah menentukan indeks yang diperlukan, kita dapat mengekstrak barisnya dari tabel data menggunakan pointer yang sesuai. (Pendekatan ekuivalen alternatif adalah dengan mencari kurang dari atau sama dengan nilai indeks.)
Pencarian yang diindeks umumnya merupakan metode yang disukai untuk mencari tabel dengan banyak baris karena manfaatnya yang jelas. Dengan menggunakan pencarian terindeks, kita dapat menemukan baris apa pun dalam tabel dalam waktu yang sangat singkat hanya dengan beberapa I/O. Dan pencarian berurutan (mis. Memindai tabel dari baris pertama ke baris terakhir) membutuhkan lebih banyak waktu, semakin jauh baris yang diinginkan berada.
Di bagian berikut, kita akan melihat dua jenis indeks yang ada, terkelompok dan tidak terkelompok, dan mempelajari cara membuat indeks.
Indeks berkerumun
Indeks berkerumun mendefinisikan urutan fisik data dalam tabel. Mesin Basis Data memungkinkan Anda untuk membuat hanya satu indeks berkerumun di atas meja, karena baris tabel tidak dapat dipesan secara fisik dengan lebih dari satu cara. Pencarian menggunakan indeks berkerumun dilakukan dari simpul akar pohon B+ menuju simpul pohon, yang dihubungkan bersama dalam daftar tertaut ganda yang disebut rangkaian halaman.
Properti penting dari indeks berkerumun adalah simpul pohonnya berisi halaman data. (Semua tingkat lain dari node indeks berkerumun berisi halaman indeks.) Sebuah tabel di mana indeks berkerumun didefinisikan (secara eksplisit atau implisit) disebut tabel berkerumun. Struktur pohon B+ dari indeks berkerumun ditunjukkan pada gambar di bawah ini:
Indeks berkerumun dibuat secara default untuk setiap tabel yang memiliki kunci utama yang ditentukan menggunakan batasan kunci utama. Selain itu, setiap indeks berkerumun unik secara default, mis. dalam kolom yang menentukan indeks berkerumun, setiap nilai data hanya dapat muncul sekali. Jika indeks berkerumun dibuat pada kolom yang berisi nilai duplikat, sistem database menerapkan ketidakjelasan dengan menambahkan pengidentifikasi empat byte ke baris yang berisi nilai duplikat.
Indeks berkerumun memberikan akses data yang sangat cepat saat kueri mencari rentang nilai.
Indeks Non-Cluster
Struktur indeks non-cluster sama persis dengan indeks cluster, tetapi dengan dua perbedaan penting:
indeks nonclustered tidak mengubah urutan fisik baris tabel;
halaman node indeks noncluster terdiri dari kunci indeks dan bookmark.
Jika Anda menentukan satu atau lebih indeks noncluster pada tabel, urutan fisik baris tabel tidak akan berubah. Untuk setiap indeks nonclustered, Database Engine membuat struktur indeks tambahan yang disimpan di halaman indeks. Struktur pohon B+ dari indeks non-cluster ditunjukkan pada gambar di bawah ini:
Bookmark dalam indeks noncluster menunjukkan di mana baris yang sesuai dengan kunci indeks berada. Komponen bookmark kunci indeks dapat terdiri dari dua jenis, bergantung pada apakah tabel tersebut adalah tabel berkerumun atau tumpukan. (Menurut terminologi SQL Server, tumpukan adalah tabel tanpa indeks berkerumun.) Jika ada indeks berkerumun, tab indeks nonclustered menunjukkan pohon B+ dari indeks berkerumun tabel. Jika tabel tidak memiliki indeks berkerumun, bookmarknya identik pengidentifikasi baris (RID - Pengidentifikasi Baris), yang terdiri dari tiga bagian: alamat file tempat tabel disimpan, alamat blok fisik (halaman) tempat string disimpan, dan offset string di halaman.
Seperti disebutkan sebelumnya, pencarian data menggunakan indeks non-cluster dapat dilakukan dengan dua cara berbeda, bergantung pada jenis tabelnya:
tumpukan - melintasi struktur indeks non-cluster, setelah itu baris diambil menggunakan pengidentifikasi baris;
tabel berkerumun - melintasi struktur indeks berkerumun diikuti dengan melintasi indeks berkerumun yang sesuai.
Dalam kedua kasus tersebut, jumlah I/O cukup tinggi, jadi Anda harus berhati-hati saat merancang indeks noncluster, dan gunakan hanya jika Anda tahu itu akan meningkatkan kinerja secara signifikan.
Transact-SQL dan Indeks
Sekarang setelah kita melihat struktur fisik indeks, bagian ini akan membahas cara membuat, memodifikasi, dan menghapus indeks, serta cara mendapatkan informasi fragmentasi indeks dan mengedit informasi indeks. Semua ini akan mempersiapkan kita untuk pembahasan berikut tentang penggunaan indeks untuk meningkatkan kinerja sistem.
Membuat indeks
Indeks untuk tabel dibuat menggunakan pernyataan BUAT INDEKS. Instruksi ini memiliki sintaks berikut:
BUAT INDEX nama_indeks PADA nama_tabel (kolom1,...) [ TERMASUK (nama_kolom [ ,... ]) ] [[, ] PAD_INDEX = (ON | OFF)] [[, ] DROP_EXISTING = (ON | OFF)] [[ , ] SORT_IN_TEMPDB = (ON | OFF)] [[, ] IGNORE_DUP_KEY = (ON | OFF)] [[, ] ALLOW_ROW_LOCKS = (ON | OFF)] [[, ] ALLOW_PAGE_LOCKS = (ON | OFF)] [[, ] STATISTICS_NORECOMPUTE = (ON | OFF)] [[, ] ONLINE = (ON | OFF)]] Konvensi sintaksis
Parameter index_name menentukan nama indeks yang akan dibuat. Indeks dapat dibuat pada satu atau lebih kolom dari satu tabel, dilambangkan dengan parameter table_name. Kolom tempat indeks dibuat ditentukan oleh parameter column1. Akhiran numerik untuk parameter ini menunjukkan bahwa indeks dapat dibuat pada beberapa kolom dalam sebuah tabel. Mesin Basis Data juga mendukung pembuatan indeks pada tampilan.
Anda dapat mengindeks setiap kolom tabel. Artinya, kolom yang berisi nilai tipe data VARBINARY(max), BIGINT, dan SQL_VARIANT juga dapat diindeks.
Indeks bisa sederhana atau majemuk. Indeks sederhana dibuat pada satu kolom, sedangkan indeks komposit dibuat pada banyak kolom. Indeks komposit memiliki batasan tertentu terkait dengan ukuran dan jumlah kolomnya. Indeks dapat memiliki maksimal 900 byte dan maksimal 16 kolom.
Parameter UNIK menentukan bahwa kolom yang diindeks hanya dapat berisi nilai tunggal (yaitu, tidak berulang). Dalam indeks komposit bernilai tunggal, kombinasi nilai semua kolom di setiap baris harus bernilai tunggal. Jika kata kunci UNIK tidak ditentukan, maka nilai duplikat dalam kolom yang diindeks diperbolehkan.
opsi CLUSTERED menentukan indeks berkerumun, dan parameter NONCLUSTERED(default) menentukan bahwa indeks tidak menyusun ulang baris dalam tabel. Mesin Basis Data memungkinkan maksimal 249 indeks nonclustered di atas meja.
Mesin Basis Data telah ditingkatkan untuk mendukung indeks dengan urutan nilai kolom yang menurun. Opsi ASC setelah nama kolom menentukan bahwa indeks dibuat dengan nilai kolom dalam urutan menaik, dan opsi DESC menunjukkan nilai kolom indeks dalam urutan menurun. Dengan demikian, banyak fleksibilitas diberikan dalam penggunaan indeks. Dengan urutan menurun, buat indeks gabungan pada kolom yang nilainya diurutkan berlawanan arah.
TERMASUK parameter memungkinkan Anda menentukan kolom non-kunci yang ditambahkan ke halaman node indeks non-cluster. Nama kolom dalam daftar TERMASUK tidak boleh diulang, dan kolom tidak dapat digunakan sebagai kolom kunci dan bukan kunci.
Untuk benar-benar memahami kegunaan parameter TERMASUK, Anda perlu memahami apa itu meliputi indeks. Jika semua kolom kueri disertakan dalam indeks, maka Anda bisa mendapatkan peningkatan kinerja yang signifikan, karena. pengoptimal kueri dapat menemukan semua nilai kolom di seluruh halaman indeks tanpa mengakses data dalam tabel. Kemampuan ini disebut indeks penutup atau kueri penutup. Oleh karena itu, menyertakan kolom non-kunci tambahan di halaman node indeks noncluster Anda akan memungkinkan Anda mendapatkan lebih banyak kueri cakupan, sementara kinerjanya akan ditingkatkan secara signifikan.
FILLFACTOR Parameter menetapkan persentase setiap halaman dalam indeks pada saat dibuat. Nilai parameter FILLFACTOR dapat diatur dalam rentang dari 1 hingga 100. Dengan nilai n=100, setiap halaman indeks terisi 100%, yaitu. halaman situs yang ada, seperti halaman non-situs, tidak akan memiliki ruang kosong untuk menyisipkan baris baru. Oleh karena itu, nilai ini disarankan hanya untuk tabel statis. (Nilai default, n=0, berarti halaman node indeks terisi penuh, dan setiap halaman perantara berisi ruang kosong untuk satu record.)
Dengan nilai FILLFACTOR antara 1 dan 99, halaman simpul dari struktur indeks yang Anda buat akan berisi ruang kosong. Semakin besar nilai n, semakin sedikit ruang kosong di halaman node indeks. Misalnya, dengan n=60, setiap halaman node indeks akan memiliki 40% ruang kosong untuk memasukkan baris indeks nanti. (Baris indeks disisipkan dengan pernyataan INSERT atau UPDATE.) Jadi, n=60 masuk akal untuk tabel yang datanya sering berubah. Dengan nilai FILLFACTOR antara 1 dan 99, halaman indeks perantara berisi ruang kosong untuk masing-masing satu entri.
Setelah indeks dibuat, nilai FILLFACTOR tidak didukung saat sedang digunakan. Dengan kata lain, ini hanya menunjukkan jumlah ruang yang dipesan dengan data yang tersedia saat mengatur persentase ruang kosong. Pernyataan ALTER INDEX digunakan untuk mengembalikan parameter FILLFACTOR ke nilai aslinya.
Parameter PAD_INDEX terkait erat dengan parameter FILLFACTOR. Parameter FILLFACTOR pada dasarnya menentukan jumlah ruang kosong sebagai persentase dari total halaman node indeks. Dan parameter PAD_INDEX menentukan bahwa nilai parameter FILLFACTOR berlaku untuk halaman indeks dan halaman data dalam indeks.
DROP_EXISTING parameter meningkatkan kinerja saat memutar ulang indeks berkerumun pada tabel yang juga memiliki indeks tidak berkerumun. Untuk informasi selengkapnya, lihat bagian "Membangun Ulang Indeks" di bawah.
opsi SORT_IN_TEMPDB digunakan untuk dimasukkan ke dalam database sistem tempdb, data sortir menengah yang digunakan untuk membuat indeks. Ini dapat meningkatkan kinerja jika database tempdb dihosting di drive yang berbeda dari data.
Parameter IGNORE_DUP_KEY memungkinkan sistem untuk mengabaikan upaya memasukkan nilai duplikat ke dalam kolom yang diindeks. Opsi ini sebaiknya hanya digunakan untuk menghindari transaksi yang berjalan lama dibatalkan saat pernyataan INSERT menyisipkan data duplikat ke dalam kolom yang diindeks. Jika opsi ini diaktifkan, maka saat pernyataan INSERT mencoba menyisipkan baris ke dalam tabel yang melanggar keunikan indeks, sistem database hanya mengeluarkan peringatan alih-alih menghentikan seluruh pernyataan. Namun, Mesin Basis Data tidak menyisipkan baris dengan nilai kunci duplikat, tetapi mengabaikannya dan menambahkan baris yang benar. Jika parameter ini tidak disetel, maka eksekusi seluruh pernyataan akan macet.
Kapan Parameter ALLOW_ROW_LOCKS diaktifkan (diaktifkan), sistem menerapkan penguncian baris. Begitu pula saat diaktifkan Parameter ALLOW_PAGE_LOCKS, sistem menerapkan penguncian halaman pada akses bersamaan. opsi STATISTICS_NORECOMPUTE menentukan status penghitungan ulang statistik secara otomatis untuk indeks yang ditentukan.
Diaktifkan parameter ONLINE memungkinkan Anda membuat, membuat ulang, dan menghapus indeks secara interaktif. Opsi ini memungkinkan Anda untuk memodifikasi tabel yang mendasari atau data indeks berkerumun dan indeks terkait lainnya secara bersamaan selama proses pembaruan indeks. Misalnya, saat membuat ulang indeks tergugus, Anda dapat terus memperbarui datanya dan menjalankan kueri pada data tersebut.
Parameter AKTIF membuat inode yang ditentukan baik pada filegroup default (nilai default) atau pada filegroup yang ditentukan (nilai file_group).
Contoh berikut menunjukkan cara membuat indeks non-cluster pada kolom Id tabel Karyawan:
GUNAKAN SampleDb; BUAT INDEKS ix_empid ON Karyawan(Id);
Membuat indeks komposit unik ditunjukkan pada contoh di bawah ini:
GUNAKAN SampleDb; BUAT INDEKS UNIK ix_empid_prnu ON Works_on (EmpId, ProjectNumber) DENGAN FILLFACTOR= 80;
Dalam contoh ini, nilai di setiap kolom harus satu digit. Saat indeks dibuat, 80% ruang di setiap halaman node indeks terisi.
Anda tidak dapat membuat indeks unik pada kolom jika kolom tersebut berisi nilai duplikat. Indeks seperti itu hanya dapat dibuat jika setiap nilai (termasuk nol) muncul hanya sekali dalam kolom. Selain itu, upaya apa pun untuk memasukkan atau mengubah nilai data yang ada ke dalam kolom yang termasuk dalam indeks unik yang ada akan ditolak oleh sistem jika ada nilai duplikat.
Mendapatkan informasi tentang fragmentasi indeks
Selama umur indeks, itu bisa menjadi terfragmentasi, membuat proses penyimpanan data di halaman indeks menjadi tidak efisien. Ada dua jenis fragmentasi indeks: fragmentasi internal dan fragmentasi eksternal. Fragmentasi internal menentukan jumlah data yang disimpan di setiap halaman, sedangkan fragmentasi eksternal terjadi saat halaman tidak sesuai urutan logis.
Untuk mendapatkan informasi tentang fragmentasi internal indeks, tampilan manajemen dinamis (DMV) digunakan, disebut sys.dm_db_index_physical_stats. DMV ini mengembalikan informasi tentang volume dan fragmentasi data dan indeks dari halaman yang ditentukan. Untuk setiap halaman, satu baris dikembalikan untuk setiap level pohon B+. Dengan DMV ini, Anda bisa mendapatkan informasi tentang tingkat fragmentasi baris di halaman data, yang menjadi dasar Anda memutuskan apakah akan mengatur ulang data.
Penggunaan tampilan sys.dm_db_index_physical_stats ditunjukkan pada contoh di bawah ini. (Sebelum menjalankan paket contoh, setiap indeks yang ada pada tabel Works_on harus dihapus. Untuk menghapus indeks, gunakan pernyataan DROP INDEX, yang akan ditampilkan nanti.)
GUNAKAN SampleDb; MENYATAKAN @dbId INT; MENYATAKAN @tabId INT; MENYATAKAN @indId INT; SET @dbId = DB_ID("SampleDb"); SET @tabId = OBJECT_ID("Karyawan"); PILIH avg_fragmentation_in_percent, avg_page_space_used_in_percent DARI sys.dm_db_index_physical_stats (@dbId, @tabId, NULL, NULL, NULL);
Seperti yang Anda lihat dari contoh, tampilan sys.dm_db_index_physical_stats memiliki lima parameter. Tiga parameter pertama menentukan pengidentifikasi dari database, tabel, dan indeks saat ini, masing-masing. Parameter keempat menentukan pengidentifikasi partisi, dan parameter terakhir menentukan tingkat pemindaian yang digunakan untuk memperoleh informasi statistik. (Nilai default untuk parameter tertentu dapat ditentukan dengan nilai NULL.)
Kolom terpenting dalam tampilan ini adalah kolom avg_fragmentation_in_percent dan avg_page_space_used_in_percent. Yang pertama menunjukkan tingkat rata-rata fragmentasi sebagai persentase, dan yang kedua menunjukkan jumlah ruang yang digunakan sebagai persentase.
Mengedit Informasi Indeks
Setelah Anda terbiasa dengan informasi fragmentasi indeks seperti yang dibahas di bagian sebelumnya, Anda dapat mengedit ini dan informasi indeks lainnya menggunakan alat sistem berikut:
tampilan katalog sys.indexes;
tampilan katalog sys.index_columns;
prosedur sistem sp_helpindex;
fungsi properti objekproperti;
lingkungan manajemen SQL Server Management Studio;
Tampilan manajemen dinamis DMV sys.dm_db_index_usage_stats;
DMV sys.dm_db_missing_index_details DMV.
Tampilan katalog sys.indexes berisi satu baris untuk setiap indeks dan satu baris untuk setiap tabel tanpa indeks berkerumun. Kolom terpenting dalam tampilan katalog ini adalah kolom object_id, name, dan index_id. Kolom object_id berisi nama objek database tempat indeks berada, dan kolom name dan index_id masing-masing berisi nama dan ID indeks tersebut.
Tampilan katalog sys.indeks_kolom berisi baris untuk setiap kolom yang merupakan bagian dari indeks atau heap. Informasi ini dapat digunakan bersama dengan informasi yang diperoleh melalui tampilan katalog sys.indexes untuk mendapatkan informasi tambahan tentang properti indeks yang ditentukan.
Prosedur Sistem sp_helpindex mengembalikan data tentang indeks tabel serta informasi statistik untuk kolom. Prosedur ini memiliki sintaks berikut:
sp_helpindex[@db_object = ] "nama"
Di sini variabel @db_object mewakili nama tabel.
Untuk indeks, fungsi properti objekproperti memiliki dua properti: IsIndexed dan IsIndexable. Properti pertama memberikan informasi tentang apakah tabel atau tampilan memiliki indeks, dan properti kedua menunjukkan apakah tabel atau tampilan dapat diindeks.
Untuk mengedit informasi indeks yang ada menggunakan SQL Server Management Studio, pilih database yang diperlukan di folder Databases, perluas simpul Tabel, di simpul ini perluas tabel yang diperlukan dan folder Indeksnya. Folder tabel Indeks menampilkan daftar semua indeks yang ada untuk tabel tersebut. Mengklik dua kali pada indeks akan membuka kotak dialog Properti Indeks dengan properti untuk indeks tersebut. (Anda juga dapat membuat indeks baru atau menghapus indeks yang sudah ada menggunakan Management Studio.)
Pertunjukan sys.dm_db_index_usage_stats mengembalikan hitungan berbagai jenis operasi indeks dan waktu eksekusi terakhir dari setiap jenis operasi. Setiap operasi pencarian, pemindaian, atau pembaruan individual pada indeks yang ditentukan dalam eksekusi kueri tunggal dihitung sebagai penggunaan indeks dan menambah nilai penghitung yang sesuai di DMV tersebut. Dengan cara ini, Anda bisa mendapatkan informasi umum tentang frekuensi penggunaan indeks, untuk menentukan indeks mana yang lebih banyak digunakan dan mana yang lebih sedikit.
Pertunjukan sys.dm_db_missing_index_details mengembalikan informasi mendetail tentang kolom tabel yang tidak memiliki indeks. Kolom paling penting dari DMV ini adalah kolom index_handle dan object_id. Nilai di kolom pertama mengidentifikasi indeks tertentu yang hilang, dan nilai di kolom kedua mengidentifikasi tabel tempat indeks hilang.
Mengubah indeks
Mesin Basis Data adalah salah satu dari sedikit sistem basis data yang mendukung pernyataan tersebut MENGUBAH INDEKS. Pernyataan ini dapat digunakan untuk melakukan operasi pemeliharaan indeks. Sintaks pernyataan ALTER INDEX sangat mirip dengan pernyataan CREATE INDEX. Dengan kata lain, pernyataan ini memungkinkan Anda untuk mengubah nilai opsi ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY, dan STATISTICS_NORECOMPUTE yang dijelaskan sebelumnya dalam pernyataan CREATE INDEX.
Selain opsi di atas, pernyataan ALTER INDEX mendukung tiga opsi lain:
MEMBANGUN KEMBALI parameter Yang digunakan untuk membangun kembali indeks;
REORGANISASI parameter Digunakan untuk mengatur ulang halaman dari node indeks;
opsi NONAKTIFKAN Digunakan untuk menonaktifkan index. Ketiga opsi ini dibahas dalam subbab berikut.
Membangun kembali indeks
Setiap kali data dimodifikasi menggunakan pernyataan INSERT, UPDATE, atau DELETE, fragmentasi data dimungkinkan. Jika data ini diindeks, maka fragmentasi indeks juga dimungkinkan, ketika informasi indeks tersebar di halaman fisik yang berbeda. Fragmentasi data indeks dapat memaksa Mesin Basis Data untuk melakukan pembacaan data tambahan, yang menurunkan kinerja sistem secara keseluruhan. Dalam hal ini, Anda perlu membangun kembali (MEMBANGUN KEMBALI) semua indeks yang terfragmentasi.
Ini dapat dilakukan dengan dua cara:
melalui opsi REBUILD dari pernyataan ALTER INDEX;
melalui opsi DROP_EXISTING dari pernyataan CREATE INDEX.
Opsi REBUILD digunakan untuk membangun kembali indeks. Jika Anda menetapkan ALL untuk parameter ini, bukan nama indeks, semua indeks pada tabel akan dibuat ulang. (Dengan mengaktifkan indeks untuk dibuat ulang secara dinamis, Anda tidak perlu menghapus dan membuatnya kembali.)
Opsi DROP_EXISTING dari pernyataan CREATE INDEX meningkatkan kinerja saat membuat ulang indeks berkerumun pada tabel yang juga memiliki indeks nonclustered. Ini menentukan bahwa indeks berkerumun atau tidak berkerumun yang ada harus dihapus dan indeks yang ditentukan harus dibuat ulang. Seperti disebutkan sebelumnya, setiap indeks noncluster pada tabel berkerumun berisi nilai yang sesuai dari indeks berkerumun tabel di node pohonnya. Untuk alasan ini, saat Anda menjatuhkan indeks berkerumun di atas meja, Anda harus membuat ulang semua indeks yang tidak berkerumun. Menggunakan opsi DROP_EXISTING menghindari pembuatan ulang indeks non-cluster.
Opsi DROP_EXISTING lebih kuat daripada opsi REBUILD karena lebih fleksibel dan menyediakan beberapa opsi, seperti mengubah kolom yang menyusun indeks dan mengubah indeks nonclustered menjadi clustered.
Reorganisasi halaman node indeks
Opsi REORGANIZE dari pernyataan ALTER INDEX mengatur ulang halaman node dalam indeks yang ditentukan sehingga urutan fisik halaman cocok dengan urutan logisnya, dari kiri ke kanan. Ini menghilangkan sejumlah fragmentasi indeks, meningkatkan kinerja indeks.
Menonaktifkan indeks
Opsi DISABLE menonaktifkan indeks yang ditentukan. Indeks yang dinonaktifkan tidak tersedia untuk digunakan hingga diaktifkan kembali. Perhatikan bahwa indeks yang dinonaktifkan tidak berubah saat perubahan dilakukan pada data terkait. Untuk alasan ini, untuk menggunakan kembali indeks yang dinonaktifkan, itu harus dibuat ulang sepenuhnya. Untuk mengaktifkan indeks yang dinonaktifkan, gunakan opsi REBUILD dari pernyataan ALTER TABLE.
Ketika indeks berkerumun pada tabel dinonaktifkan, data tabel tidak akan dapat diakses karena semua halaman data dari tabel dengan indeks berkerumun disimpan di simpul pohonnya.
Menghapus dan Mengganti Nama Indeks
Untuk menghapus indeks dalam database saat ini, gunakan Pernyataan DROP INDEX. Perhatikan bahwa menghapus indeks berkerumun pada tabel bisa menjadi operasi yang sangat intensif sumber daya karena Anda perlu membuat ulang semua indeks non-cluster. (Semua indeks nonclustered menggunakan kunci indeks dari indeks berkerumun sebagai indeks di halaman node mereka.) Penggunaan pernyataan DROP INDEX untuk menjatuhkan indeks ditunjukkan dalam contoh berikut:
GUNAKAN SampleDb; DROP INDEX ix_empid ON Karyawan;
Instruksi DROP INDEX memiliki tambahan PINDAH KE parameter, yang nilainya sama dengan parameter ON dari pernyataan CREATE INDEX. Dengan kata lain, Anda dapat menggunakan opsi ini untuk menentukan ke mana harus memindahkan baris data yang ada di halaman node dari indeks berkerumun. Data dipindahkan ke lokasi baru sebagai tumpukan. Anda dapat menentukan filegroup default atau filegroup bernama untuk lokasi penyimpanan data baru.
Pernyataan DROP INDEX tidak dapat digunakan untuk menjatuhkan indeks yang dibuat secara implisit oleh sistem untuk batasan integritas, indeks seperti PRIMARY KEY dan UNIQUE. Untuk menghapus indeks tersebut, Anda perlu menghapus batasan yang sesuai.
Indeks dapat diganti namanya menggunakan prosedur sistem sp_rename.
Indeks juga dapat dibuat, dimodifikasi, dan dihapus di Management Studio menggunakan Database Diagram atau Object Explorer. Tetapi cara termudah adalah dengan menggunakan folder Indeks dari tabel yang diperlukan. Mengelola indeks di Management Studio mirip dengan mengelola tabel di lingkungan ini.
Meskipun Mesin Basis Data tidak memberlakukan batasan praktis pada jumlah indeks, ada beberapa alasan mengapa jumlah ini harus dibatasi. Pertama, setiap indeks membutuhkan sejumlah ruang disk, sehingga ada kemungkinan bahwa jumlah halaman indeks basis data dapat melebihi jumlah halaman data dalam basis data. Kedua, tidak seperti manfaat menggunakan indeks untuk mengambil data, menyisipkan dan menghapus data tidak memberikan manfaat tersebut karena kebutuhan untuk memelihara indeks. Semakin banyak indeks yang dimiliki tabel, semakin banyak pekerjaan yang diperlukan untuk mengaturnya kembali. Sebagai aturan umum, adalah bijaksana untuk memilih indeks untuk kueri yang sering dan kemudian mengevaluasi penggunaannya.
Beberapa panduan untuk membuat dan menggunakan indeks disediakan di bagian ini. Rekomendasi berikut hanyalah aturan umum. Pada akhirnya, keefektifannya akan bergantung pada bagaimana database digunakan dalam praktik dan jenis kueri yang paling sering dilakukan. Mengindeks kolom yang tidak akan pernah digunakan tidak akan ada gunanya.
Indeks dan ketentuan klausa WHERE
Jika klausa WHERE dari pernyataan SELECT berisi kondisi pencarian satu kolom, maka indeks harus dibuat pada kolom tersebut. Ini terutama direkomendasikan dalam kondisi yang sangat selektif. Selektivitas suatu kondisi mengacu pada rasio jumlah baris yang memenuhi kondisi terhadap jumlah total baris dalam tabel. Selektivitas tinggi sesuai dengan nilai yang lebih rendah dari rasio ini. Pemrosesan pencarian menggunakan kolom yang diindeks akan paling berhasil bila selektivitas kondisi tidak melebihi 5%.
Kolom tidak boleh diindeks pada tingkat selektivitas kondisi konstan 80% atau lebih. Dalam kasus seperti itu, halaman indeks akan memerlukan operasi I/O tambahan, yang akan mengurangi penghematan waktu yang diperoleh dengan menggunakan indeks. Dalam hal ini, lebih cepat melakukan pencarian pemindaian tabel, yang biasanya akan dipilih oleh pengoptimal kueri, menjadikan indeks tidak berguna.
Jika istilah pencarian dari kueri yang sering digunakan berisi operator AND, yang terbaik adalah membuat indeks gabungan pada semua kolom tabel yang ditentukan dalam klausa WHERE dari pernyataan SELECT. Pembuatan indeks gabungan seperti itu ditunjukkan pada contoh di bawah ini:
Contoh ini membuat indeks komposit pada semua kolom di klausa WHERE. Dalam kueri ini, dua kondisi di-AND bersama-sama, sehingga indeks komposit nonclustered harus dibuat pada kedua kolom di bawah kondisi ini.
Indeks dan bergabung dengan operator
Dalam kasus operasi gabungan, Anda disarankan untuk membuat indeks untuk setiap kolom gabungan. Kolom yang digabungkan sering mewakili kunci utama dari salah satu tabel dan kunci asing yang sesuai dari tabel lainnya. Jika Anda menetapkan batasan integritas PRIMARY KEY dan FOREIGN KEY pada kolom gabungan yang sesuai, Anda sebaiknya hanya membuat indeks non-cluster pada kolom kunci asing karena sistem secara implisit akan membuat indeks berkerumun pada kolom kunci utama.
Contoh di bawah menunjukkan cara membuat indeks yang akan digunakan jika Anda memiliki kueri dengan operasi gabungan dan filter tambahan:
Meliputi indeks
Seperti disebutkan sebelumnya, menyertakan semua kolom kueri dalam indeks dapat sangat meningkatkan kinerja kueri. Pembuatan indeks semacam itu, yang disebut indeks penutup, ditunjukkan pada contoh di bawah ini:
GUNAKAN AdventureWorks2012; GO DROP INDEX Person.Address.IX_Address_StateProvinceID; GO CREATE INDEX ix_address_zip ON Person.Address(PostalCode) INCLUDE(City, StateProvinceID); PERGI PILIH Kota, StateProvinceID DARI Person.Address WHERE PostalCode = 84407;
Contoh ini pertama menghapus indeks IX_Address_StateProvinceID dari tabel Address. Kemudian indeks baru dibuat yang menyertakan dua kolom tambahan selain kolom Kode Pos. Terakhir, pernyataan SELECT di akhir contoh menunjukkan kueri yang dicakup oleh indeks. Untuk kueri ini, sistem tidak perlu mencari halaman data untuk data karena pengoptimal kueri dapat menemukan semua nilai kolom di halaman node dari indeks nonclustered.
Meliputi indeks direkomendasikan karena halaman indeks biasanya berisi lebih banyak rekaman daripada halaman data terkait. Selain itu, untuk menggunakan metode ini, kolom yang difilter harus menjadi kolom kunci pertama dalam indeks.
Indeks untuk kolom terhitung
Mesin Basis Data memungkinkan Anda membuat jenis indeks khusus berikut:
tampilan yang diindeks;
indeks yang dapat disaring;
indeks untuk kolom terhitung;
indeks yang dipartisi;
indeks kegigihan kolom;
indeks XML;
indeks teks lengkap.
Bagian ini membahas kolom yang dihitung dan indeks terkaitnya.
kolom terhitung adalah kolom tabel yang menyimpan hasil perhitungan data tabel. Kolom seperti itu bisa virtual atau permanen. Kedua jenis kolom ini dibahas pada subbab berikut.
Kolom terhitung virtual
Kolom terhitung yang tidak memiliki indeks berkerumun yang sesuai adalah boolean, mis. itu tidak disimpan secara fisik di hard drive. Jadi, ini dievaluasi setiap kali string diakses. Penggunaan kolom terhitung virtual ditunjukkan pada contoh di bawah ini:
GUNAKAN SampleDb; CREATE TABLE Orders(OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD(DAY, 7, orderdate));
Tabel Pesanan dalam contoh ini memiliki dua kolom terhitung virtual: total dan tanggal pengiriman. Kolom total dihitung menggunakan dua kolom lainnya, harga dan kuantitas, dan kolom tanggal pengiriman dihitung menggunakan fungsi DATEADD dan kolom tanggal pemesanan.
Kolom terhitung yang persisten
Mesin Basis Data memungkinkan Anda membuat indeks pada kolom yang dihitung deterministik di mana kolom yang mendasarinya memiliki tipe data yang tepat. (Kolom yang dihitung dikatakan deterministik jika selalu mengembalikan nilai yang sama untuk data tabel yang sama.)
Kolom terhitung yang diindeks hanya dapat dibuat jika opsi berikut untuk pernyataan SET disetel ke AKTIF (opsi ini membuat kolom deterministik):
QUOTED_IDENTIFIER
CONCAT_NULL_YIELDS_NULL
Selain itu, parameter NUMERIC_ROUNDABORT harus dinonaktifkan.
Jika Anda membuat indeks berkerumun pada kolom yang dihitung, maka nilai kolom secara fisik akan ada di baris yang sesuai dalam tabel, karena halaman node dari indeks berkerumun berisi baris data. Contoh berikut menunjukkan cara membuat indeks tergugus pada total kolom yang dihitung dari tabel Pesanan:
GUNAKAN SampleDb; CREATE CLUSTERED INDEX ix1 ON Orders (Total);
Setelah menjalankan pernyataan CREATE INDEX, total kolom yang dihitung akan secara fisik ada di tabel. Ini berarti bahwa semua pembaruan pada kolom yang mendasari kolom terhitung akan menyebabkannya diperbarui.
Kolom dapat dibuat permanen dengan cara lain, menggunakan parameter BERTAHAN. Opsi ini memungkinkan Anda untuk menentukan keberadaan fisik kolom yang dihitung bahkan tanpa membuat indeks berkerumun yang sesuai. Kemampuan ini diperlukan untuk membuat kolom terhitung fisik yang dibuat pada kolom dengan tipe data perkiraan (float atau real). (Seperti disebutkan sebelumnya, indeks hanya dapat dibuat pada kolom yang dihitung jika kolom dasarnya adalah tipe data yang tepat.)
