Indeks MS sql. Indeks di SQL Server

Untuk memulainya, saya sarankan Anda mencari tahu apa itu meliputi indeks, saya akan memberikan kutipan dari artikel di Habré:

Mengapa menggunakan indeks penutup dan bukan indeks komposit?
Pertama, mari kita pastikan kita memahami perbedaan antara keduanya.
Indeks komposit itu hanya indeks biasa yang mencakup lebih dari satu kolom. Beberapa kolom kunci dapat digunakan untuk memastikan keunikan masing-masing baris tabel, mungkin juga kunci utama terdiri dari beberapa kolom untuk memastikan keunikannya, atau Anda mencoba mengoptimalkan eksekusi kueri yang sering dipanggil pada beberapa kolom. Namun secara umum, semakin banyak kolom kunci yang terdapat dalam suatu indeks, semakin kurang efisien indeks tersebut, yang berarti bahwa indeks gabungan harus digunakan dengan bijaksana.

Seperti yang dinyatakan, sebuah kueri bisa mendapatkan keuntungan besar jika semua data yang diperlukan segera ditempatkan di daun indeks, sama seperti indeks itu sendiri. Ini bukan masalah untuk indeks berkerumun karena semua data sudah ada (itulah sebabnya sangat penting untuk berpikir hati-hati saat membuat indeks berkerumun). Namun indeks non-cluster pada daun hanya berisi kolom kunci. Untuk mengakses semua data lainnya, pengoptimal kueri memerlukan langkah tambahan, yang dapat menambah overhead yang signifikan untuk mengeksekusi kueri Anda.

Di situlah meliputi indeks bergegas menyelamatkan. Saat Anda menentukan indeks noncluster, Anda bisa menentukan kolom tambahan ke kolom kunci Anda.

Dengan demikian, indeks penutup tidak boleh berisi semua kolom kueri yang dapat dipilih dalam struktur pohon indeks, tetapi hanya kolom yang akan digunakan untuk memfilter atau mengelompokkan data dalam kueri, kolom sisa dari bagian SELECT harus ditempatkan di TERMASUK bagian indeks.

Anda mungkin menemukan jawaban dari pertanyaan lain bermanfaat.

Contoh di atas menggunakan indeks komposit 3 bidang dan bukan indeks penutup, kode untuk membuat indeks penutup akan terlihat seperti ini:

BUAT INDEKS NONCLUSTERED PADA . ( ASC) TERMASUK (, ) DENGAN (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON

Untuk menjawab pertanyaan Anda:

untuk indeks penutup, urutan kolom di bagian TERMASUK tidak penting, tapi urutan kolom penting untuk indeks komposit, Karena Data kolom ditempatkan di pohon indeks sesuai urutan kolom yang dicantumkan, dan pengoptimal kueri tidak akan dapat menggunakan indeks 2 kolom untuk mencari nilai dari 2 kolom saja. Anda dapat melihat contoh jelas tampilan struktur indeks 2 kolom (EMPLOYEE_ID, SUBSIDIARY_ID) pada gambar.

Indeks- ini adalah hal pertama yang perlu Anda pahami dengan baik dalam pekerjaan Anda SQLServer, tapi anehnya pertanyaan mendasar tidak terlalu sering ditanyakan di forum dan tidak mendapat banyak jawaban.
Rob Sheldon menjawab pertanyaan-pertanyaan ini yang menyebabkan kebingungan di kalangan profesional tentang indeks di SQLServer: beberapa di antaranya memang malu untuk kita tanyakan, namun sebelum bertanya pada yang lain kita akan berpikir dua kali terlebih dahulu.

Terminologi yang digunakan:

indeks indeks
tumpukan banyak
meja meja
melihat pertunjukan
B-pohon pohon yang seimbang
indeks berkerumun indeks berkerumun
indeks tidak berkerumun indeks tidak berkerumun
indeks komposit indeks komposit
meliputi indeks meliputi indeks
batasan kunci utama batasan kunci utama
kendala unik pembatasan keunikan nilai
pertanyaan meminta
mesin kueri subsistem kueri
basis data basis data
mesin basis data subsistem penyimpanan
faktor pengisi faktor pengisian indeks
kunci primer pengganti kunci primer pengganti
pengoptimal kueri pengoptimal kueri
selektivitas indeks selektivitas indeks
indeks yang difilter indeks yang dapat disaring
rencana eksekusi rencana eksekusi

Dasar-dasar indeks di SQL Server.

Salah satu cara terpenting untuk mencapai produktivitas tinggi SQLServer adalah penggunaan indeks. Indeks mempercepat proses kueri dengan menyediakan akses cepat ke baris data dalam tabel, seperti indeks dalam buku yang membantu Anda menemukan informasi yang Anda perlukan dengan cepat. Pada artikel kali ini saya akan memberikannya ulasan singkat indeks di SQLServer dan menjelaskan bagaimana mereka diatur dalam database dan bagaimana mereka membantu mempercepat query database.

Indeks dibuat pada kolom tabel dan tampilan. Indeks menyediakan cara untuk mencari data dengan cepat berdasarkan nilai di kolom tersebut. Misalnya, jika Anda membuat indeks pada kunci utama lalu mencari baris data menggunakan nilai kunci utama, maka SQLServer pertama-tama akan menemukan nilai indeks dan kemudian menggunakan indeks untuk menemukan seluruh baris data dengan cepat. Akan dieksekusi tanpa indeks tampilan penuh(memindai) semua baris tabel, yang dapat berdampak signifikan terhadap kinerja.
Anda dapat membuat indeks pada sebagian besar kolom dalam tabel atau tampilan. Pengecualiannya terutama adalah kolom dengan tipe data untuk menyimpan objek besar ( LOB), seperti gambar, teks atau varchar(maks). Anda juga dapat membuat indeks pada kolom yang dirancang untuk menyimpan data dalam format XML, namun indeks ini disusun sedikit berbeda dari indeks standar dan pertimbangannya berada di luar cakupan artikel ini. Selain itu, artikel tersebut tidak membahas toko kolom indeks. Sebaliknya, saya fokus pada indeks yang paling umum digunakan dalam database SQLServer.
Indeks terdiri dari sekumpulan halaman, node indeks, yang disusun dalam struktur pohon - pohon yang seimbang. Struktur ini bersifat hierarkis dan dimulai dengan simpul akar di bagian atas hierarki dan simpul daun, yaitu daun, di bagian bawah, seperti yang ditunjukkan pada gambar:

Saat Anda mengkueri kolom yang diindeks, mesin kueri dimulai dari bagian atas simpul akar dan turun ke bawah melalui simpul perantara, dengan setiap lapisan perantara berisi informasi lebih rinci tentang data. Mesin kueri terus bergerak melalui node indeks hingga mencapai level terbawah dengan indeks keluar. Misalnya, jika Anda mencari nilai 123 di kolom yang diindeks, mesin kueri akan terlebih dahulu menentukan halaman pada tingkat menengah pertama di tingkat akar. Dalam hal ini, halaman pertama menunjuk ke nilai dari 1 hingga 100, dan halaman kedua dari 101 hingga 200, sehingga mesin kueri akan mengakses halaman kedua dari tingkat menengah ini. Selanjutnya Anda akan melihat bahwa Anda harus membuka halaman ketiga dari tingkat menengah berikutnya. Dari sini, subsistem kueri akan membaca nilai indeks itu sendiri di tingkat yang lebih rendah. Daun indeks dapat berisi data tabel itu sendiri atau sekadar penunjuk ke baris dengan data dalam tabel, bergantung pada jenis indeks: indeks berkerumun atau indeks tidak berkerumun.

Indeks berkerumun

Indeks berkerumun menyimpan baris data sebenarnya di daun indeks. Kembali ke contoh sebelumnya, ini berarti baris data yang terkait dengan nilai kunci 123 akan disimpan dalam indeks itu sendiri. Ciri penting dari indeks berkerumun adalah semua nilai diurutkan dalam urutan tertentu, baik menaik atau menurun. Oleh karena itu, tabel atau tampilan hanya dapat memiliki satu indeks berkerumun. Selain itu, perlu diperhatikan bahwa data dalam tabel disimpan dalam bentuk terurut hanya jika indeks berkerumun telah dibuat pada tabel ini.
Tabel yang tidak memiliki indeks berkerumun disebut heap.

Indeks non-cluster

Berbeda dengan indeks yang berkerumun, daun indeks yang tidak berkerumun hanya berisi kolom-kolom tersebut ( kunci) yang menentukan indeks ini, dan juga berisi penunjuk ke baris dengan data nyata dalam tabel. Ini berarti bahwa sistem subkueri memerlukan operasi tambahan untuk mencari dan mengambil data yang diperlukan. Konten penunjuk data bergantung pada cara data disimpan: tabel berkerumun atau heap. Jika sebuah penunjuk menunjuk ke tabel berkerumun, ia menunjuk ke indeks berkerumun yang dapat digunakan untuk menemukan data sebenarnya. Jika sebuah penunjuk mengacu pada heap, maka penunjuk tersebut menunjuk ke pengidentifikasi baris data tertentu. Indeks noncluster tidak dapat diurutkan seperti indeks cluster, namun Anda dapat membuat lebih dari satu indeks noncluster pada tabel atau tampilan, hingga 999. Ini tidak berarti Anda harus membuat indeks sebanyak mungkin. Indeks dapat meningkatkan atau menurunkan kinerja sistem. Selain dapat membuat beberapa indeks non-cluster, Anda juga dapat menyertakan kolom tambahan ( kolom yang disertakan) ke dalam indeksnya: daun indeks tidak hanya akan menyimpan nilai kolom yang diindeks itu sendiri, tetapi juga nilai kolom tambahan yang tidak diindeks ini. Pendekatan ini akan memungkinkan Anda untuk melewati beberapa batasan yang ditempatkan pada indeks. Misalnya, Anda dapat menyertakan kolom yang tidak dapat diindeks atau melewati batas panjang indeks (umumnya 900 byte).

Jenis Indeks

Selain dikelompokkan atau tidak, indeks selanjutnya dapat dikonfigurasi sebagai indeks komposit, indeks unik, atau indeks penutup.

Indeks komposit

Indeks tersebut dapat berisi lebih dari satu kolom. Anda dapat menyertakan hingga 16 kolom dalam indeks, namun panjang totalnya dibatasi hingga 900 byte. Indeks berkerumun dan tidak berkerumun dapat berupa komposit.

Indeks unik

Indeks ini memastikan bahwa setiap nilai dalam kolom yang diindeks adalah unik. Jika indeksnya komposit, maka keunikan berlaku untuk semua kolom dalam indeks, namun tidak untuk setiap kolom individual. Misalnya, jika Anda membuat indeks unik pada kolom NAMA Dan NAMA BELAKANG, maka nama lengkap harus unik, tetapi duplikat pada nama depan atau belakang dapat dilakukan.
Indeks unik secara otomatis dibuat saat Anda menentukan batasan kolom: batasan kunci utama atau nilai unik:

  • Kunci utama
    Saat Anda menentukan batasan kunci utama pada satu atau lebih kolom SQLServer secara otomatis membuat indeks berkerumun unik jika indeks berkerumun belum pernah dibuat sebelumnya (dalam hal ini, indeks unik non-berkelompok dibuat pada kunci utama)
  • Keunikan nilai
    Saat Anda menentukan batasan pada keunikan nilai, maka SQLServer secara otomatis membuat indeks non-cluster unik. Anda dapat menentukan bahwa indeks berkerumun unik akan dibuat jika belum ada indeks berkerumun yang dibuat pada tabel
Meliputi indeks

Indeks semacam itu memungkinkan kueri tertentu untuk segera mendapatkan semua data yang diperlukan dari daun indeks tanpa akses tambahan ke catatan tabel itu sendiri.

Merancang Indeks

Meskipun indeks bermanfaat, indeks harus dirancang dengan hati-hati. Karena indeks dapat menghabiskan banyak ruang disk, Anda tidak ingin membuat indeks lebih banyak dari yang diperlukan. Selain itu, indeks diperbarui secara otomatis ketika baris data itu sendiri diperbarui, yang dapat menyebabkan tambahan sumber daya tambahan dan penurunan kinerja. Saat merancang indeks, beberapa pertimbangan mengenai database dan pertanyaan terhadapnya harus dipertimbangkan.

Basis data

Seperti disebutkan sebelumnya, indeks dapat meningkatkan kinerja sistem karena mereka menyediakan mesin kueri cara cepat untuk menemukan data. Namun, Anda juga harus memperhitungkan seberapa sering Anda ingin menyisipkan, memperbarui, atau menghapus data. Saat Anda mengubah data, indeks juga harus diubah untuk mencerminkan tindakan terkait pada data, yang dapat mengurangi kinerja sistem secara signifikan. Pertimbangkan panduan berikut saat merencanakan strategi pengindeksan Anda:

  • Untuk tabel yang sering diperbarui, gunakan indeks sesedikit mungkin.
  • Jika tabel berisi data dalam jumlah besar namun perubahannya kecil, gunakan indeks sebanyak yang diperlukan untuk meningkatkan performa kueri Anda. Namun, pikirkan baik-baik sebelum menggunakan indeks pada tabel kecil, karena... Ada kemungkinan bahwa penggunaan pencarian indeks memerlukan waktu lebih lama daripada sekadar memindai semua baris.
  • Untuk indeks berkerumun, cobalah untuk membuat kolom sesingkat mungkin. Pendekatan terbaik adalah dengan menggunakan indeks berkerumun pada kolom yang memiliki nilai unik dan tidak mengizinkan NULL. Inilah sebabnya mengapa kunci utama sering digunakan sebagai indeks berkerumun.
  • Keunikan nilai dalam suatu kolom mempengaruhi kinerja indeks. Secara umum, semakin banyak duplikat yang Anda miliki dalam sebuah kolom, semakin buruk kinerja indeksnya. Di sisi lain, semakin banyak nilai unik yang dimilikinya, semakin baik kinerja indeksnya. Gunakan indeks unik bila memungkinkan.
  • Untuk indeks gabungan, perhatikan urutan kolom dalam indeks. Kolom yang digunakan dalam ekspresi DI MANA(Misalnya, DIMANA Nama Depan = 'Charlie') harus menjadi yang pertama dalam indeks. Kolom berikutnya harus dicantumkan berdasarkan keunikan nilainya (kolom dengan jumlah nilai unik terbanyak didahulukan).
  • Anda juga dapat menentukan indeks pada kolom terhitung jika memenuhi persyaratan tertentu. Misalnya, ekspresi yang digunakan untuk mendapatkan nilai kolom harus bersifat deterministik (selalu memberikan hasil yang sama untuk sekumpulan parameter masukan tertentu).
Kueri basis data

Pertimbangan lain ketika merancang indeks adalah kueri apa yang dijalankan terhadap database. Seperti yang dinyatakan sebelumnya, Anda harus mempertimbangkan seberapa sering data berubah. Selain itu, prinsip-prinsip berikut harus digunakan:

  • Cobalah untuk menyisipkan atau memodifikasi baris sebanyak mungkin dalam satu kueri, daripada melakukannya dalam beberapa kueri tunggal.
  • Buat indeks non-cluster pada kolom yang sering digunakan sebagai istilah pencarian dalam kueri Anda. DI MANA dan koneksi di BERGABUNG.
  • Pertimbangkan untuk mengindeks kolom yang digunakan dalam kueri pencarian baris untuk mendapatkan nilai yang sama persis.

Mengapa sebuah tabel tidak dapat memiliki dua indeks berkerumun?

Ingin jawaban singkat? Indeks berkerumun adalah sebuah tabel. Saat Anda membuat indeks berkerumun pada tabel, mesin penyimpanan mengurutkan semua baris dalam tabel dalam urutan menaik atau menurun, sesuai dengan definisi indeks. Indeks berkerumun bukanlah entitas terpisah seperti indeks lainnya, tetapi merupakan mekanisme untuk mengurutkan data dalam tabel dan memfasilitasi akses cepat ke baris data.
Bayangkan Anda memiliki tabel yang berisi riwayat transaksi penjualan. Tabel Penjualan mencakup informasi seperti ID pesanan, posisi barang dalam pesanan, nomor barang, jumlah barang, nomor dan tanggal pesanan, dll. Anda membuat indeks berkerumun pada kolom Id pemesanan Dan ID Garis, diurutkan dalam urutan menaik seperti yang ditunjukkan berikut ini T-SQL kode:

BUAT INDEKS CLUSTERED UNIK ix_oriderid_lineid PADA dbo.Sales(OrderID, LineID);

Saat Anda menjalankan skrip ini, semua baris dalam tabel akan diurutkan secara fisik terlebih dahulu berdasarkan kolom OrderID dan kemudian berdasarkan LineID, namun datanya sendiri akan tetap berada dalam satu blok logis, yaitu tabel. Karena alasan ini, Anda tidak dapat membuat dua indeks berkerumun. Hanya ada satu tabel dengan satu data dan tabel tersebut hanya dapat diurutkan satu kali dalam urutan tertentu.

Jika tabel cluster memberikan banyak manfaat, lalu mengapa menggunakan heap?

Kamu benar. Tabel terkluster sangat bagus dan sebagian besar kueri Anda akan berperforma lebih baik pada tabel yang memiliki indeks terkluster. Namun dalam beberapa kasus, Anda mungkin ingin membiarkan tabel dalam keadaan alami dan murni, yaitu. dalam bentuk heap, dan buat hanya indeks non-cluster agar kueri Anda tetap berjalan.
Heap, seperti yang Anda ingat, menyimpan data dalam urutan acak. Biasanya, subsistem penyimpanan menambahkan data ke tabel sesuai urutan penyisipannya, namun subsistem penyimpanan juga suka memindahkan baris untuk penyimpanan yang lebih efisien. Akibatnya, Anda tidak memiliki kesempatan untuk memprediksi urutan data yang akan disimpan.
Jika mesin kueri perlu menemukan data tanpa memanfaatkan indeks noncluster, mesin kueri akan melakukan pemindaian penuh terhadap tabel untuk menemukan baris yang diperlukan. Pada tabel yang sangat kecil, hal ini biasanya tidak menjadi masalah, namun seiring bertambahnya ukuran heap, kinerja akan menurun dengan cepat. Tentu saja, indeks non-cluster dapat membantu dengan menggunakan penunjuk ke file, halaman, dan baris tempat data yang diperlukan disimpan - ini biasanya merupakan alternatif yang jauh lebih baik daripada pemindaian tabel. Meski begitu, sulit untuk membandingkan manfaat indeks berkerumun ketika mempertimbangkan kinerja kueri.
Namun, heap dapat membantu meningkatkan kinerja dalam situasi tertentu. Pertimbangkan sebuah tabel dengan banyak sisipan tetapi sedikit pembaruan atau penghapusan. Misalnya, tabel yang menyimpan log terutama digunakan untuk memasukkan nilai hingga diarsipkan. Di heap, Anda tidak akan melihat paging dan fragmentasi data seperti yang Anda lihat dengan indeks berkerumun karena baris hanya ditambahkan ke akhir heap. Memisahkan halaman terlalu banyak dapat berdampak signifikan pada kinerja, dan tidak berdampak baik. Secara umum, heap memungkinkan Anda memasukkan data dengan relatif mudah dan Anda tidak perlu berurusan dengan biaya penyimpanan dan pemeliharaan seperti yang Anda lakukan dengan indeks berkerumun.
Namun kurangnya pembaruan dan penghapusan data tidak boleh dianggap sebagai satu-satunya alasan. Cara pengambilan sampel data juga merupakan faktor penting. Misalnya, Anda tidak boleh menggunakan heap jika Anda sering menanyakan rentang data atau data yang Anda kueri sering kali perlu diurutkan atau dikelompokkan.
Artinya, Anda sebaiknya hanya mempertimbangkan penggunaan heap ketika Anda bekerja dengan tabel yang sangat kecil atau semua interaksi Anda dengan tabel terbatas pada penyisipan data dan kueri Anda sangat sederhana (dan Anda menggunakan indeks non-cluster). Bagaimanapun). Jika tidak, tetap gunakan indeks berkerumun yang dirancang dengan baik, seperti indeks yang ditentukan pada bidang kunci menaik sederhana, seperti kolom yang banyak digunakan dengan IDENTITAS.

Bagaimana cara mengubah faktor pengisian indeks default?

Mengubah faktor pengisian indeks default adalah satu hal. Memahami cara kerja rasio default adalah masalah lain. Tapi pertama-tama, mundurlah beberapa langkah. Faktor pengisian indeks menentukan jumlah ruang pada halaman untuk menyimpan indeks di tingkat paling bawah (tingkat daun) sebelum mulai mengisi lembaran baru. Misalnya, jika koefisien disetel ke 90, maka ketika indeks bertambah, ia akan menempati 90% halaman dan kemudian berpindah ke halaman berikutnya.
Secara default, nilai faktor pengisian indeks ada di SQLServer adalah 0, yang sama dengan 100. Hasilnya, semua indeks baru secara otomatis mewarisi pengaturan ini kecuali Anda secara spesifik menentukan nilai dalam kode Anda yang berbeda dari nilai standar sistem atau mengubah perilaku default. Anda dapat gunakan Studio Manajemen SQL Server untuk menyesuaikan nilai default atau menjalankan prosedur tersimpan sistem sp_configure. Misalnya himpunan berikut T-SQL perintah menetapkan nilai koefisien menjadi 90 (Anda harus terlebih dahulu beralih ke mode pengaturan lanjutan):

EXEC sp_configure "menampilkan opsi lanjutan ", 1; BUKA KONFIGURASI ULANG; BUKA EXEC sp_configure " faktor pengisian", 90; BUKA KONFIGURASI ULANG; BUKA

Setelah mengubah nilai faktor pengisian indeks, Anda perlu memulai ulang layanan SQLServer. Anda sekarang dapat memeriksa nilai yang ditetapkan dengan menjalankan sp_configure tanpa argumen kedua yang ditentukan:

EXEC sp_configure "faktor pengisian" GO

Perintah ini harus mengembalikan nilai 90. Akibatnya, semua indeks yang baru dibuat akan menggunakan nilai ini. Anda dapat mengujinya dengan membuat indeks dan menanyakan nilai faktor pengisian:

GUNAKAN AdventureWorks2012; -- basis data Anda BUAT INDEKS NONCLUSTERED ix_people_lastname PADA Person.Person(LastName); PILIH fill_factor DARI sys .indexes WHERE object_id = object_id("Person.Person" ) DAN name ="ix_people_lastname" ;

Dalam contoh ini, kami membuat indeks non-cluster pada sebuah tabel Orang dalam basis data PetualanganWorks2012. Setelah membuat indeks, kita bisa mendapatkan nilai faktor pengisian dari tabel sistem sys.indexes. Kueri harus menghasilkan 90.
Namun, bayangkan kita menghapus indeks dan membuatnya lagi, namun sekarang kita menentukan nilai faktor pengisian tertentu:

BUAT INDEKS NONCLUSTERED ix_people_lastname PADA Person.Person(LastName) DENGAN (fillfactor=80 ); PILIH fill_factor DARI sys .indexes WHERE object_id = object_id("Person.Person" ) DAN name ="ix_people_lastname" ;

Kali ini kami telah menambahkan instruksi DENGAN dan pilihan faktor pengisi untuk operasi pembuatan indeks kami BUAT INDEKS dan menentukan nilainya 80. Operator PILIH sekarang mengembalikan nilai yang sesuai.
Sejauh ini, semuanya berjalan cukup mudah. Hal yang membuat Anda benar-benar bosan dalam keseluruhan proses ini adalah saat Anda membuat indeks yang menggunakan nilai koefisien default, dengan asumsi Anda mengetahui nilai tersebut. Misalnya, seseorang mengutak-atik pengaturan server dan begitu keras kepala sehingga menetapkan faktor pengisian indeks ke 20. Sementara itu, Anda terus membuat indeks, dengan asumsi nilai defaultnya adalah 0. Sayangnya, Anda tidak memiliki cara untuk mengetahui isiannya. faktor sampai selama Anda tidak membuat indeks dan kemudian memeriksa nilainya seperti yang kami lakukan dalam contoh kami. Jika tidak, Anda harus menunggu saat kinerja kueri turun drastis sehingga Anda mulai mencurigai sesuatu.
Masalah lain yang harus Anda waspadai adalah membangun kembali indeks. Seperti halnya membuat indeks, Anda dapat menentukan nilai faktor pengisian indeks saat Anda membangunnya kembali. Namun, tidak seperti perintah buat indeks, pembangunan kembali tidak menggunakan pengaturan default server, apa pun tampilannya. Terlebih lagi, jika Anda tidak secara spesifik menentukan nilai faktor pengisian indeks, maka SQLServer akan menggunakan nilai koefisien yang dimiliki indeks ini sebelum direstrukturisasi. Misalnya operasi berikut MENGUBAH INDEKS membangun kembali indeks yang baru saja kita buat:

ALTER INDEX ix_people_lastname PADA Person.Person REBUILD ; PILIH fill_factor DARI sys .indexes WHERE object_id = object_id("Person.Person" ) DAN name ="ix_people_lastname" ;

Saat kita mengecek nilai faktor pengisian, kita akan mendapatkan nilai 80, karena itulah yang kita tentukan saat terakhir kali kita membuat indeks. Nilai default diabaikan.
Seperti yang Anda lihat, mengubah nilai faktor pengisian indeks tidaklah sulit. Jauh lebih sulit untuk mengetahui nilai saat ini dan memahami kapan nilai tersebut diterapkan. Jika Anda selalu menentukan koefisien secara spesifik saat membuat dan membangun kembali indeks, maka Anda selalu mengetahui hasil spesifiknya. Kecuali Anda harus khawatir untuk memastikan orang lain tidak mengacaukan pengaturan server lagi, menyebabkan semua indeks dibangun kembali dengan faktor pengisian indeks yang sangat rendah.

Apakah mungkin membuat indeks berkerumun pada kolom yang berisi duplikat?

Iya dan tidak. Ya, Anda dapat membuat indeks berkerumun pada kolom kunci yang berisi nilai duplikat. Tidak, nilai kolom kunci tidak boleh tetap dalam keadaan tidak unik. Biar saya jelaskan. Jika Anda membuat indeks berkerumun non-unik pada kolom, mesin penyimpanan menambahkan keunikan ke nilai duplikat untuk memastikan keunikan dan oleh karena itu dapat mengidentifikasi setiap baris dalam tabel berkerumun.
Misalnya, Anda mungkin memutuskan untuk membuat indeks berkerumun pada kolom yang berisi data pelanggan Nama keluarga menjaga nama keluarga. Kolom tersebut berisi nilai Franklin, Hancock, Washington, dan Smith. Kemudian Anda memasukkan nilai Adams, Hancock, Smith dan Smith lagi. Namun nilai kolom kunci harus unik, sehingga mesin penyimpanan akan mengubah nilai duplikat sehingga terlihat seperti ini: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567, dan Smith5678.
Pada pandangan pertama, pendekatan ini tampak baik-baik saja, tetapi nilai integer meningkatkan ukuran kunci, yang dapat menjadi masalah jika ada banyak duplikat, dan nilai-nilai ini akan menjadi dasar dari indeks non-cluster atau indeks asing. referensi kunci. Karena alasan ini, Anda harus selalu mencoba membuat indeks berkerumun unik jika memungkinkan. Jika hal ini tidak memungkinkan, setidaknya coba gunakan kolom dengan konten nilai unik yang sangat tinggi.

Bagaimana tabel disimpan jika indeks berkerumun belum dibuat?

SQLServer mendukung dua jenis tabel: tabel berkerumun yang memiliki indeks berkerumun dan tabel tumpukan atau hanya tumpukan. Tidak seperti tabel berkerumun, data di heap tidak diurutkan dengan cara apa pun. Intinya, ini adalah tumpukan (heap) data. Jika Anda menambahkan baris ke tabel tersebut, mesin penyimpanan hanya akan menambahkannya ke akhir halaman. Jika halaman sudah terisi data, maka akan ditambahkan ke halaman baru. Dalam kebanyakan kasus, Anda ingin membuat indeks berkerumun pada tabel untuk memanfaatkan kemampuan pengurutan dan kueri yang lebih cepat (coba bayangkan menemukan nomor telepon dalam buku alamat yang tidak diurutkan berdasarkan prinsip apa pun). Namun, jika Anda memilih untuk tidak membuat indeks berkerumun, Anda masih dapat membuat indeks tidak berkerumun di heap. Dalam hal ini, setiap baris indeks akan memiliki penunjuk ke baris heap. Indeks mencakup ID file, nomor halaman, dan nomor baris data.

Apa hubungan antara batasan keunikan nilai dan kunci utama dengan indeks tabel?

Kunci utama dan batasan unik memastikan bahwa nilai dalam kolom bersifat unik. Anda hanya dapat membuat satu kunci utama untuk sebuah tabel dan tidak boleh berisi nilai BATAL. Anda dapat membuat beberapa batasan pada keunikan nilai untuk sebuah tabel, dan masing-masing batasan tersebut dapat memiliki satu entri BATAL.
Saat Anda membuat kunci utama, mesin penyimpanan juga membuat indeks berkerumun unik jika indeks berkerumun belum dibuat. Namun, Anda dapat mengganti perilaku default dan indeks non-cluster akan dibuat. Jika indeks berkerumun ada saat Anda membuat kunci utama, indeks noncluster unik akan dibuat.
Saat Anda membuat batasan unik, mesin penyimpanan membuat indeks unik dan tidak terkluster. Namun, Anda dapat menentukan pembuatan indeks berkerumun unik jika belum pernah dibuat sebelumnya.
Secara umum, batasan nilai unik dan indeks unik adalah hal yang sama.

Mengapa indeks berkerumun dan tidak berkerumun disebut B-tree di SQL Server?

Indeks dasar di SQL Server, berkerumun atau tidak berkerumun, didistribusikan ke seluruh kumpulan halaman yang disebut simpul indeks. Halaman-halaman ini disusun dalam hierarki tertentu dengan struktur pohon yang disebut pohon seimbang. Pada tingkat atas terdapat simpul akar, di bagian bawah, simpul daun terminal, dengan simpul perantara antara tingkat atas dan bawah, seperti terlihat pada gambar:

Node akar menyediakan titik masuk utama untuk kueri yang mencoba mengambil data melalui indeks. Mulai dari node ini, mesin kueri memulai navigasi ke bawah struktur hierarki ke node daun yang sesuai yang berisi data.
Misalnya, bayangkan permintaan telah diterima untuk memilih baris yang berisi nilai kunci 82. Subsistem kueri mulai bekerja dari simpul akar, yang merujuk ke simpul perantara yang sesuai, dalam kasus kita 1-100. Dari node perantara 1-100 terjadi transisi ke node 51-100, dan dari sana ke node akhir 76-100. Jika ini adalah indeks berkerumun, maka daun simpul berisi data dari baris yang terkait dengan kunci sama dengan 82. Jika ini adalah indeks yang tidak berkerumun, maka daun indeks berisi penunjuk ke tabel berkerumun atau baris tertentu di tumpukan.

Bagaimana indeks dapat meningkatkan kinerja kueri jika Anda harus melintasi semua node indeks ini?

Pertama, indeks tidak selalu meningkatkan kinerja. Terlalu banyak indeks yang salah dibuat mengubah sistem menjadi rawa dan menurunkan kinerja kueri. Lebih tepat dikatakan bahwa jika indeks diterapkan dengan hati-hati, indeks dapat memberikan peningkatan kinerja yang signifikan.
Bayangkan sebuah buku besar yang didedikasikan untuk penyetelan kinerja SQLServer(versi kertas, bukan versi elektronik). Bayangkan Anda ingin mencari informasi tentang mengonfigurasi Resource Governor. Anda dapat menyeret jari Anda halaman demi halaman ke seluruh buku, atau membuka daftar isi dan mencari tahu nomor halaman pasti dengan informasi yang Anda cari (asalkan buku tersebut diindeks dengan benar dan isinya memiliki indeks yang benar). Hal ini tentunya akan menghemat banyak waktu Anda, meskipun Anda harus mengakses struktur yang sama sekali berbeda (indeks) terlebih dahulu untuk mendapatkan informasi yang Anda perlukan dari struktur utama (buku).
Seperti indeks buku, indeks masuk SQLServer memungkinkan Anda melakukan kueri yang tepat pada data yang Anda perlukan alih-alih memindai seluruh data yang terdapat dalam tabel. Untuk tabel kecil, pemindaian penuh biasanya tidak menjadi masalah, namun meja besar memakan banyak halaman data, yang dapat mengakibatkan waktu eksekusi kueri yang signifikan kecuali ada indeks yang memungkinkan mesin kueri segera mendapatkan lokasi data yang benar. Bayangkan tersesat di persimpangan jalan bertingkat di depan kota metropolitan besar tanpa peta dan Anda akan mendapatkan idenya.

Jika indeks sangat bagus, mengapa tidak membuatnya saja di setiap kolom?

Tidak ada perbuatan baik yang dibiarkan begitu saja. Setidaknya itulah yang terjadi dengan indeks. Tentu saja, indeks berfungsi dengan baik selama Anda menjalankan kueri pengambilan operator PILIH, tetapi segera setelah panggilan rutin ke operator dimulai MENYISIPKAN, MEMPERBARUI Dan MENGHAPUS, jadi lanskap berubah dengan sangat cepat.
Saat Anda memulai permintaan data oleh operator PILIH, mesin kueri menemukan indeks, menelusuri struktur pohonnya, dan menemukan data yang dicarinya. Apa yang lebih sederhana? Tapi segalanya berubah jika Anda memulai pernyataan perubahan seperti MEMPERBARUI. Ya, untuk bagian pertama pernyataan, mesin kueri dapat kembali menggunakan indeks untuk menemukan baris yang sedang dimodifikasi - itu kabar baik. Dan jika ada perubahan sederhana pada data dalam satu baris yang tidak mempengaruhi perubahan pada kolom kunci, maka proses perubahan tidak akan menimbulkan kesulitan sama sekali. Namun bagaimana jika perubahan menyebabkan halaman berisi data terpecah, atau nilai kolom kunci diubah sehingga dipindahkan ke node indeks lain - hal ini akan mengakibatkan indeks mungkin memerlukan reorganisasi yang memengaruhi semua indeks dan operasi terkait , mengakibatkan penurunan produktivitas secara luas.
Proses serupa terjadi saat memanggil operator MENGHAPUS. Indeks dapat membantu menemukan data yang dihapus, namun menghapus data itu sendiri dapat mengakibatkan perombakan halaman. Mengenai operator MENYISIPKAN, musuh utama semua indeks: Anda mulai menambahkan sejumlah besar data, yang menyebabkan perubahan dalam indeks dan reorganisasinya dan semua orang menderita.
Jadi pertimbangkan jenis kueri ke database Anda ketika memikirkan jenis indeks apa dan berapa banyak yang harus dibuat. Lebih banyak tidak berarti lebih baik. Sebelum menambahkan indeks baru ke tabel, pertimbangkan biaya tidak hanya dari kueri yang mendasarinya, tetapi juga jumlah ruang disk yang dikonsumsi, biaya pemeliharaan fungsionalitas dan indeks, yang dapat menyebabkan efek domino pada operasi lainnya. Strategi desain indeks Anda adalah salah satu aspek terpenting dalam penerapan Anda dan harus mencakup banyak pertimbangan, mulai dari ukuran indeks, jumlah nilai unik, hingga jenis kueri yang akan didukung indeks.

Apakah perlu membuat indeks berkerumun pada kolom dengan kunci utama?

Anda dapat membuat indeks berkerumun pada kolom mana pun yang cocok kondisi yang diperlukan. Memang benar bahwa indeks berkerumun dan batasan kunci utama dibuat untuk satu sama lain dan merupakan kecocokan yang dibuat di surga, jadi pahami fakta bahwa ketika Anda membuat kunci utama, maka indeks berkerumun akan otomatis dibuat jika belum ada. dibuat sebelumnya. Namun, Anda mungkin memutuskan bahwa indeks berkerumun akan berkinerja lebih baik di tempat lain, dan sering kali keputusan Anda dapat dibenarkan.
Tujuan utama indeks berkerumun adalah untuk mengurutkan semua baris dalam tabel Anda berdasarkan kolom kunci yang ditentukan saat menentukan indeks. Ini menyediakan pencarian Cepat dan akses mudah ke data tabel.
Kunci utama suatu tabel dapat menjadi pilihan yang baik karena kunci tersebut secara unik mengidentifikasi setiap baris dalam tabel tanpa harus menambahkan data tambahan. Dalam beberapa kasus pilihan terbaik Akan ada kunci utama pengganti yang tidak hanya unik, tetapi juga berukuran kecil, dan nilainya meningkat secara berurutan, yang membuat indeks non-cluster berdasarkan nilai ini menjadi lebih efisien. Pengoptimal kueri juga menyukai kombinasi indeks berkerumun dan kunci utama ini karena menggabungkan tabel lebih cepat daripada menggabungkan dengan cara lain yang tidak menggunakan kunci utama dan indeks berkerumun terkait. Seperti yang saya katakan, ini adalah pasangan yang dibuat di surga.
Namun yang terakhir, perlu dicatat bahwa saat membuat indeks berkerumun, ada beberapa aspek yang perlu dipertimbangkan: berapa banyak indeks non-kluster yang akan didasarkan pada indeks tersebut, seberapa sering nilai kolom indeks kunci akan berubah, dan seberapa besar. Jika nilai dalam kolom indeks berkerumun berubah atau kinerja indeks tidak sesuai yang diharapkan, semua indeks lain pada tabel dapat terpengaruh. Indeks berkerumun harus didasarkan pada kolom paling persisten yang nilainya meningkat dalam urutan tertentu tetapi tidak berubah secara acak. Indeks harus mendukung kueri terhadap data tabel yang paling sering diakses, sehingga kueri memanfaatkan sepenuhnya fakta bahwa data diurutkan dan dapat diakses di node akar, bagian daun indeks. Jika kunci utama cocok dengan skenario ini, gunakanlah. Jika tidak, pilih kumpulan kolom lain.

Bagaimana jika Anda mengindeks suatu tampilan, apakah masih berupa tampilan?

Presentasi adalah meja maya, yang menghasilkan data dari satu atau lebih tabel. Pada dasarnya, ini adalah kueri bernama yang mengambil data dari tabel yang mendasarinya saat Anda menanyakan tampilan tersebut. Anda dapat meningkatkan kinerja kueri dengan membuat indeks berkerumun dan indeks non-kluster pada tampilan ini, mirip dengan cara Anda membuat indeks pada tabel, namun peringatan utamanya adalah Anda terlebih dahulu membuat indeks berkerumun, lalu Anda dapat membuat indeks yang tidak berkerumun.
Ketika tampilan yang diindeks (tampilan terwujud) dibuat, definisi tampilan itu sendiri tetap menjadi entitas terpisah. Bagaimanapun, ini hanyalah operator yang dikodekan secara keras PILIH, disimpan dalam database. Namun indeksnya adalah cerita yang sangat berbeda. Saat Anda membuat indeks berkerumun atau tidak berkerumun pada penyedia, data disimpan secara fisik ke disk, seperti indeks biasa. Selain itu, ketika data dalam tabel yang mendasarinya berubah, indeks tampilan secara otomatis berubah (ini berarti Anda mungkin ingin menghindari pengindeksan tampilan pada tabel yang sering berubah). Bagaimanapun, tampilannya tetap berupa tampilan - tampilan tabel, tetapi dieksekusi dengan tepat saat ini, dengan indeks yang sesuai dengannya.
Sebelum Anda dapat membuat indeks pada suatu tampilan, indeks tersebut harus memenuhi beberapa batasan. Misalnya, suatu tampilan hanya dapat mereferensikan tabel dasar, namun tidak dapat mereferensikan tampilan lainnya, dan tabel tersebut harus berada dalam database yang sama. Sebenarnya masih banyak batasan lainnya, jadi pastikan untuk memeriksa dokumentasinya SQLServer untuk semua detail kotornya.

Mengapa menggunakan indeks penutup dan bukan indeks komposit?

Pertama, mari kita pastikan kita memahami perbedaan antara keduanya. Indeks gabungan hanyalah indeks biasa yang berisi lebih dari satu kolom. Beberapa kolom kunci dapat digunakan untuk memastikan bahwa setiap baris dalam tabel bersifat unik, atau Anda mungkin memiliki beberapa kolom untuk memastikan bahwa kunci utama bersifat unik, atau Anda mungkin mencoba mengoptimalkan eksekusi kueri yang sering dipanggil pada beberapa kolom. Namun secara umum, semakin banyak kolom kunci yang terdapat dalam suatu indeks, semakin kurang efisien indeks tersebut, yang berarti bahwa indeks gabungan harus digunakan dengan bijaksana.
Seperti yang dinyatakan, sebuah kueri bisa mendapatkan keuntungan besar jika semua data yang diperlukan segera ditempatkan di daun indeks, sama seperti indeks itu sendiri. Ini bukan masalah untuk indeks berkerumun karena semua data sudah ada (itulah sebabnya sangat penting untuk berpikir hati-hati saat membuat indeks berkerumun). Namun indeks non-cluster pada daun hanya berisi kolom kunci. Untuk mengakses semua data lainnya, pengoptimal kueri memerlukan langkah tambahan, yang dapat menambah overhead yang signifikan untuk menjalankan kueri Anda.
Di sinilah indeks cakupan berperan sebagai penyelamat. Saat Anda menentukan indeks noncluster, Anda bisa menentukan kolom tambahan ke kolom kunci Anda. Misalnya, aplikasi Anda sering menanyakan data kolom Id pemesanan Dan Tanggal pemesanan di meja Penjualan:

PILIH ID Pesanan, Tanggal Pesanan DARI Penjualan DI MANA ID Pesanan = 12345 ;

Anda dapat membuat indeks gabungan non-cluster pada kedua kolom, namun kolom OrderDate hanya akan menambahkan overhead pemeliharaan indeks tanpa berfungsi sebagai kolom kunci yang sangat berguna. Keputusan terbaik adalah membuat indeks penutup pada kolom kunci Id pemesanan dan tambahan termasuk kolom Tanggal pemesanan:

BUAT INDEKS NONCLUSTERED ix_orderid PADA dbo.Sales(OrderID) INCLUDE (OrderDate);

Hal ini menghindari kerugian pengindeksan kolom redundan sambil tetap mempertahankan manfaat menyimpan data di daun saat menjalankan kueri. Kolom yang disertakan bukan bagian dari kunci, namun data disimpan pada node daun, daun indeks. Hal ini dapat meningkatkan kinerja kueri tanpa biaya tambahan apa pun. Selain itu, kolom-kolom yang termasuk dalam indeks penutup memiliki batasan yang lebih sedikit dibandingkan kolom-kolom utama indeks.

Apakah jumlah duplikat di kolom kunci penting?

Saat Anda membuat indeks, Anda harus mencoba mengurangi jumlah duplikat di kolom kunci Anda. Atau lebih tepatnya: usahakan untuk menjaga tingkat pengulangan serendah mungkin.
Jika Anda bekerja dengan indeks komposit, maka duplikasi berlaku untuk semua kolom kunci secara keseluruhan. Satu kolom bisa berisi banyak nilai duplikat, namun harus ada pengulangan minimal di antara semua kolom indeks. Misalnya, Anda membuat indeks gabungan noncluster pada kolom Nama depan Dan Nama keluarga, Anda dapat memiliki banyak nilai John Doe dan banyak nilai Doe, tetapi Anda ingin memiliki nilai John Doe sesedikit mungkin, atau sebaiknya hanya satu nilai John Doe.
Rasio keunikan nilai kolom kunci disebut selektivitas indeks. Semakin banyak nilai uniknya, semakin tinggi selektivitasnya: indeks unik memiliki selektivitas sebesar mungkin. Mesin kueri sangat menyukai kolom dengan nilai selektivitas tinggi, terutama jika kolom tersebut termasuk dalam klausa WHERE dari kueri yang paling sering Anda jalankan. Semakin selektif indeksnya, semakin cepat mesin kueri dapat mengurangi ukuran kumpulan data yang dihasilkan. Sisi negatifnya Intinya, tentu saja, kolom dengan nilai unik yang relatif sedikit jarang menjadi kandidat yang baik untuk diindeks.

Apakah mungkin membuat indeks non-cluster hanya pada subset tertentu dari data kolom kunci?

Secara default, indeks noncluster berisi satu baris untuk setiap baris dalam tabel. Tentu saja, Anda dapat mengatakan hal yang sama tentang indeks berkerumun, dengan asumsi bahwa indeks tersebut adalah sebuah tabel. Namun jika menyangkut indeks non-cluster, hubungan satu-ke-satu merupakan konsep yang penting karena, dimulai dengan versi SQLServer 2008, Anda memiliki opsi untuk membuat indeks yang dapat difilter yang membatasi baris yang disertakan di dalamnya. Indeks yang difilter dapat meningkatkan kinerja kueri karena... ukurannya lebih kecil dan berisi statistik yang terfilter dan lebih akurat daripada semua statistik tabel - hal ini mengarah pada pembuatan rencana pelaksanaan yang lebih baik. Indeks yang difilter juga memerlukan lebih sedikit ruang penyimpanan dan biaya pemeliharaan yang lebih rendah. Indeks diperbarui hanya ketika data yang cocok dengan filter berubah.
Selain itu, indeks yang dapat disaring juga mudah dibuat. Di operator BUAT INDEKS Anda hanya perlu masuk DI MANA kondisi penyaring. Misalnya, Anda dapat memfilter semua baris yang mengandung NULL dari indeks, seperti yang ditunjukkan pada kode:

BUAT INDEKS NONCLUSTERED ix_trackingnumber PADA Sales.SalesOrderDetail(CarrierTrackingNumber) DI MANA CarrierTrackingNumber BUKAN NULL;

Faktanya, kami dapat memfilter data apa pun yang tidak penting dalam kueri penting. Tapi hati-hati, karena... SQLServer menerapkan beberapa batasan pada indeks yang dapat difilter, seperti ketidakmampuan membuat indeks yang dapat difilter pada tampilan, jadi bacalah dokumentasi dengan cermat.
Mungkin juga Anda bisa mencapai hasil serupa dengan membuat tampilan yang diindeks. Namun, indeks yang difilter memiliki beberapa keuntungan, seperti kemampuan untuk mengurangi biaya pemeliharaan dan meningkatkan kualitas rencana pelaksanaan Anda. Indeks yang difilter juga dapat dibuat ulang secara online. Coba ini dengan tampilan yang diindeks.

6. Indeks dan optimalisasi kinerja

Indeks dalam database: tujuan, dampak terhadap kinerja, prinsip pembuatan indeks

6.1 Untuk apa indeks?

Indeks adalah struktur khusus dalam database 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 indeks, maka kita harus menelusuri keseluruhan buku untuk menemukan tempat yang tepat, namun dengan indeks, tindakan yang sama dapat dilakukan lebih cepat.

Biasanya, semakin banyak indeks, semakin baik performa kueri database. Namun, jika jumlah indeks meningkat secara berlebihan, kinerja operasi modifikasi data (memasukkan/mengubah/menghapus) menurun dan ukuran database meningkat, sehingga penambahan indeks harus dilakukan dengan hati-hati.

Beberapa prinsip-prinsip umum terkait dengan pembuatan indeks:

· Indeks harus dibuat untuk kolom yang digunakan dalam gabungan, yang sering digunakan untuk operasi pencarian dan pengurutan. Harap dicatat bahwa indeks selalu dibuat secara otomatis untuk kolom yang tunduk pada batasan kunci utama. Paling sering mereka dibuat untuk kolom dengan kunci asing (di Access - secara otomatis);

· indeks diperlukan dalam mode otomatis dibuat untuk kolom yang memiliki batasan unik;

· Yang terbaik adalah membuat indeks untuk bidang yang memiliki jumlah minimum nilai berulang dan datanya didistribusikan secara merata. Oracle memiliki indeks bit khusus untuk kolom dengan sejumlah besar nilai duplikat; SQL Server dan Access tidak menyediakan indeks jenis ini;

· jika pencarian terus-menerus dilakukan pada kumpulan kolom tertentu (bersamaan), maka dalam hal ini mungkin masuk akal untuk membuat indeks komposit (hanya di SQL Server) - satu indeks untuk sekelompok kolom;

· Ketika perubahan dilakukan pada tabel, indeks yang ditumpangkan pada tabel ini secara otomatis berubah. Akibatnya, indeks menjadi sangat terfragmentasi, sehingga berdampak pada kinerja. Anda harus secara berkala memeriksa tingkat fragmentasi indeks dan mendefragnya. Saat memuat data dalam jumlah besar, terkadang masuk akal untuk menghapus semua indeks terlebih dahulu dan membuatnya kembali setelah operasi selesai;

· 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 satu atau lebih kolom.

Ada banyak variasi dalam operator ini karena tidak terstandarisasi, karena standar tidak mengatasi masalah kinerja.

2) Membuat indeks
BUAT INDEKS
PADA()

3) Mengubah dan menghapus indeks
Untuk mengontrol aktivitas indeks, operator digunakan:
MENGUBAH INDEKS
Untuk menghapus indeks, gunakan operator:
JATUHKAN INDEKS

a) Aturan pemilihan meja
1. Dianjurkan untuk mengindeks tabel yang tidak lebih dari 5% barisnya dipilih.
2. Tabel yang tidak mempunyai duplikat pada klausa WHERE pada pernyataan SELECT harus diindeks.
3. Tidaklah praktis untuk mengindeks tabel yang sering diperbarui.
4. Tidak tepat untuk mengindeks tabel yang menempati tidak lebih dari 2 halaman (untuk Oracle 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, mengambil data, dan mencari. Ini selalu merupakan indeks unik dengan utilitas maksimal
2. Saat menggunakan opsi integritas referensial, Anda selalu memerlukan indeks pada FK.
3. Kolom yang sering digunakan untuk mengurutkan dan/atau mengelompokkan data.
4. Kolom yang sering dicari pada klausa WHERE pada pernyataan SELECT.
5. Anda tidak boleh membuat indeks pada kolom deskriptif yang panjang.

c) Prinsip pembuatan indeks komposit
1. Indeks gabungan baik jika masing-masing kolom memiliki sedikit nilai unik, namun indeks gabungan memberikan lebih banyak keunikan.
2. Jika semua nilai yang dipilih oleh pernyataan SELECT termasuk dalam indeks komposit, maka nilai tersebut dipilih dari indeks.
3. Indeks gabungan harus dibuat jika klausa WHERE menggunakan dua nilai atau lebih yang digabungkan dengan operator AND.

d) Tidak disarankan untuk membuat
Tidak disarankan membuat indeks pada kolom, termasuk kolom komposit, yang:
1. Jarang digunakan untuk pencarian, penggabungan, dan pengurutan hasil query.
2. Berisi nilai-nilai yang sering berubah, yang memerlukan pembaruan yang sering indeks memperlambat kinerja basis data.
3. Berisi sejumlah kecil nilai unik (kurang dari 10% m/f) atau sejumlah baris dominan dengan satu atau dua nilai (kota tempat tinggal pemasok adalah Moskow).
4. Fungsi atau ekspresi diterapkan padanya dalam klausa WHERE, dan indeks tidak berfungsi.

e) Kita tidak boleh lupa
Anda harus berusaha mengurangi jumlah indeks, karena jumlah indeks yang banyak mengurangi kecepatan pembaruan data. Oleh karena itu, MS SQL Server merekomendasikan pembuatan tidak lebih dari 16 indeks per tabel.
Biasanya, indeks dibuat untuk tujuan kueri dan untuk menjaga integritas referensial.
Jika indeks tidak digunakan untuk kueri, maka indeks tersebut harus dihapus dan integritas referensial harus dipastikan menggunakan pemicu.

Materi kali ini akan membahas tentang objek database tersebut Microsoft SQLServer Bagaimana indeks Anda akan mempelajari apa itu indeks, jenis indeks apa saja, cara membuat, mengoptimalkan, dan menghapusnya.

Apa yang dimaksud dengan indeks dalam database?

Indeks adalah objek database yang merupakan struktur data yang terdiri dari kunci yang dibangun dari satu atau lebih kolom tabel atau tampilan, dan pointer yang memetakan ke tempat penyimpanan data tertentu. Indeks dirancang untuk mengambil baris dari tabel dengan lebih cepat; dengan kata lain, indeks memungkinkan pencarian data dalam tabel dengan cepat, yang sangat meningkatkan kinerja kueri dan aplikasi. Indeks juga dapat digunakan untuk memastikan bahwa baris dalam tabel bersifat unik, sehingga menjamin integritas data.

Jenis Indeks di Microsoft SQL Server

Di Microsoft SQL Server ada jenis berikut indeks:

  • Berkelompok (Berkelompok) adalah indeks yang menyimpan data tabel yang diurutkan berdasarkan nilai kunci indeks. Sebuah tabel hanya dapat memiliki satu indeks cluster karena datanya hanya dapat diurutkan dalam satu urutan. Jika memungkinkan, setiap tabel harus memiliki indeks berkerumun; jika tabel tidak memiliki indeks berkerumun, tabel tersebut disebut " dalam satu kelompok" Indeks berkerumun dibuat secara otomatis ketika Anda membuat batasan PRIMARY KEY ( kunci utama) dan UNIK jika indeks berkerumun pada tabel belum ditentukan. Jika Anda membuat indeks berkerumun pada tabel ( tumpukan) yang berisi indeks non-cluster, maka semuanya harus dibangun kembali setelah pembuatan.
  • Tidak berkerumun (Tidak berkerumun) adalah indeks yang berisi nilai kunci dan penunjuk ke baris data yang berisi nilai kunci tersebut. Sebuah tabel dapat memiliki beberapa indeks yang tidak dikelompokkan. Indeks non-cluster dapat dibuat pada tabel dengan atau tanpa indeks cluster. Jenis indeks inilah yang digunakan untuk meningkatkan kinerja kueri yang sering digunakan, karena indeks non-cluster menyediakan pencarian cepat dan akses ke data berdasarkan nilai kunci;
  • Dapat disaring (Tersaring) adalah indeks noncluster yang dioptimalkan yang menggunakan predikat filter untuk mengindeks subset baris dalam tabel. Jika dirancang dengan baik, jenis indeks ini dapat meningkatkan kinerja kueri dan juga mengurangi biaya pemeliharaan dan penyimpanan indeks dibandingkan dengan indeks tabel lengkap;
  • Unik (Unik) adalah indeks yang memastikan tidak ada duplikat ( identik) mengindeks nilai kunci, sehingga menjamin keunikan baris menurut kunci ini. Indeks berkerumun dan tidak berkerumun bisa bersifat unik. Jika Anda membuat indeks unik pada beberapa kolom, indeks tersebut memastikan bahwa setiap kombinasi nilai dalam kunci adalah unik. Saat Anda membuat batasan PRIMARY KEY atau UNIQUE, server SQL secara otomatis membuat indeks unik pada kolom kunci. Indeks unik hanya dapat dibuat jika tabel saat ini tidak memiliki nilai duplikat di kolom kunci;
  • berbentuk kolom (toko kolom) adalah indeks berdasarkan teknologi penyimpanan data kolom. Tipe ini Indeks ini efektif untuk penyimpanan data yang besar karena dapat meningkatkan performa kueri ke penyimpanan hingga 10 kali lipat dan juga mengurangi ukuran data hingga 10 kali lipat, karena data dalam indeks Columnstore dikompresi. Ada indeks kolom berkerumun dan yang tidak berkerumun;
  • Teks lengkap (Teks lengkap) - Ini tipe khusus indeks yang memberikan dukungan efisien untuk operasi pencarian kata kompleks pada data string karakter. Proses membuat dan memelihara indeks teks lengkap disebut " isian" Ada jenis pengisian seperti: pengisian penuh dan pengisian berdasarkan pelacakan perubahan. Oleh SQL bawaan Server akan mengisi penuh indeks teks lengkap baru segera setelah dibuat, namun hal ini memerlukan sejumlah besar sumber daya, bergantung pada ukuran tabel, sehingga mungkin saja menunda seluruh populasi. Penyemaian berbasis pelacakan perubahan digunakan untuk mempertahankan indeks teks lengkap setelah awalnya terisi penuh;
  • Spasial (Spasial) adalah indeks yang memungkinkan lebih banyak penggunaan yang efektif operasi khusus pada objek spasial dalam kolom dengan tipe data geometri atau geografi. Jenis indeks ini hanya dapat dibuat pada kolom spasial, dan tabel yang menentukan indeks spasial harus berisi kunci utama ( KUNCI UTAMA);
  • XML adalah tipe indeks khusus lainnya yang dirancang untuk kolom dengan tipe data XML. Indeks XML meningkatkan efisiensi pemrosesan kueri terhadap kolom XML. Ada dua jenis indeks XML: primer dan sekunder. Indeks XML utama mengindeks semua tag, nilai, dan jalur yang disimpan dalam kolom XML. Itu hanya dapat dibuat jika tabel memiliki indeks berkerumun pada kunci utama. Indeks XML sekunder hanya dapat dibuat jika tabel memiliki indeks XML primer dan digunakan untuk meningkatkan kinerja kueri pada jenis akses tertentu ke kolom XML, dalam hal ini, ada beberapa jenis indeks sekunder: PATH , NILAI dan PROPERTI;
  • Ada juga indeks khusus untuk tabel dengan memori yang dioptimalkan ( OLTP Dalam Memori) seperti: Hash ( hash) indeks yang dioptimalkan memori dan indeks noncluster yang dibuat untuk pemindaian jangkauan dan pemindaian yang dipesan.

Membuat dan Menghapus Indeks di Microsoft SQL Server

Sebelum Anda mulai membuat indeks, Anda perlu mendesainnya dengan baik agar dapat menggunakan indeks secara efektif, karena indeks yang dirancang dengan buruk mungkin tidak meningkatkan kinerja, melainkan menurunkannya. Misalnya, memiliki banyak indeks pada tabel akan mengurangi kinerja pernyataan INSERT, UPDATE, DELETE, dan MERGE karena ketika data dalam tabel berubah, semua indeks harus diperbarui sesuai dengan itu. Kami akan melihat rekomendasi umum untuk mendesain indeks di artikel terpisah, tetapi sekarang mari kita beralih langsung ke proses pembuatan dan penghapusan indeks.

Catatan! Server SQL saya adalah Microsoft SQL Server 2016 Express.

Membuat Indeks

Ada dua cara untuk membuat indeks di Microsoft SQL Server: yang pertama adalah menggunakan GUI lingkungan SQL Server Management Studio (SSMS), dan yang kedua menggunakan bahasa Transact-SQL, kami akan menganalisis kedua metode tersebut.

Sumber data misalnya

Bayangkan kita memiliki tabel produk bernama TestTable, yang memiliki tiga kolom:

  • ProductId – pengenal produk;
  • Nama Produk – nama produk;
  • KategoriID – kategori produk.
BUAT TABEL TestTable(IDENTITAS INT ProductId(1,1) BUKAN NULL, NamaProduk VARCHAR(50) BUKAN NULL, ID Kategori INT NULL,) AKTIF

Contoh pembuatan indeks berkerumun

Seperti yang sudah saya katakan, indeks berkerumun dibuat secara otomatis jika, misalnya, saat membuat tabel, kita menentukan kolom tertentu sebagai kunci utama ( KUNCI UTAMA), tetapi karena kita belum melakukannya, mari kita lihat sendiri contoh pembuatan indeks berkerumun.

Untuk membuat indeks berkerumun, kita dapat menentukan kunci utama untuk tabel tersebut, sehingga indeks berkerumun akan dibuat secara otomatis, atau kita dapat membuat indeks berkerumun secara terpisah.

Misalnya saja kita membuat indeks berkerumun, tanpa membuat kunci utama. Pertama kita akan melakukan ini menggunakan Management Studio.

Buka SSMS dan temukan tabel yang diinginkan di browser objek dan klik kanan pada item “ Indeks", Pilih " Buat indeks" dan tipe indeks, dalam kasus kami " Berkelompok».


Formulir " Indeks baru", dimana kita perlu menentukan nama indeks baru ( itu harus unik di dalam tabel), kami juga menunjukkan apakah indeks ini akan unik; jika kita berbicara tentang pengidentifikasi produk di tabel produk, tentu saja harus unik. Kemudian pilih kolom ( kunci indeks), atas dasar itu kita akan membuat indeks berkerumun, mis. baris data dalam tabel akan diurutkan menggunakan " Menambahkan».


Setelah memasukkan semua parameter yang diperlukan, klik “ OKE", akhirnya indeks berkerumun akan dibuat.


Demikian pula, seseorang dapat membuat indeks berkerumun menggunakan pernyataan T-SQL INDEKS MAKHLUK, misalnya seperti ini

BUAT INDEKS CLUSTERED UNIK IX_Clustered PADA TestTable (ProductId ASC) GO

Atau, seperti yang sudah kami katakan, kita juga bisa menggunakan pernyataan untuk membuat kunci utama, misalnya

ALTER TABLE TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ProductId ASC) GO

Contoh pembuatan indeks noncluster dengan kolom yang disertakan

Sekarang mari kita lihat contoh pembuatan indeks non-cluster, dalam hal ini kita akan menunjukkan kolom yang tidak akan menjadi kunci, tetapi akan dimasukkan dalam indeks. Ini berguna jika Anda membuat indeks untuk kueri tertentu, misalnya, sehingga indeks tersebut sepenuhnya mencakup kueri, yaitu. berisi semua kolom ( ini disebut "Cakupan Permintaan"). Cakupan kueri meningkatkan kinerja karena pengoptimal kueri dapat menemukan semua nilai kolom dalam indeks tanpa mengakses data tabel, sehingga mengurangi operasi I/O disk. Namun perlu diingat bahwa memasukkan kolom non-kunci ke dalam indeks memerlukan peningkatan ukuran indeks, yaitu. menyimpan indeks akan memerlukan lebih banyak ruang disk dan juga dapat mengakibatkan penurunan kinerja untuk operasi INSERT, UPDATE, DELETE, dan MERGE pada tabel dasar.

Untuk membuat indeks non-cluster menggunakan GUI Management Studio, kami juga menemukan tabel yang diinginkan dan item Indeks, hanya dalam hal ini kami memilih “ Buat -> Indeks Non-Cluster».


Setelah membuka formulir" Indeks baru"kita tentukan nama indeks, tambahkan kolom atau kolom kunci menggunakan tombol" Menambahkan", misalnya, untuk kasus pengujian kita, tentukan ID Kategori.



Di Transact-SQL akan terlihat seperti ini.

BUAT INDEKS NONCLUSTERED IX_NonClustered PADA TestTable (CategoryID ASC) INCLUDE (Nama Produk) GO

Contoh menghapus indeks di Microsoft SQL Server

Untuk menghapus indeks, Anda dapat mengklik kanan pada indeks yang diinginkan dan klik " Menghapus", lalu konfirmasikan tindakan Anda dengan mengklik" OKE».

atau bisa juga menggunakan petunjuknya JATUHKAN INDEKS, Misalnya

JATUHKAN INDEX IX_NonClustered PADA TestTable

Perlu dicatat bahwa pernyataan DROP INDEX tidak berlaku untuk indeks yang dibuat dengan membuat batasan PRIMARY KEY dan UNIQUE. Dalam hal ini, untuk menghapus indeks, Anda harus menggunakan pernyataan ALTER TABLE dengan klausa DROP CONSTRAINT.

Mengoptimalkan Indeks di Microsoft SQL Server

Sebagai hasil dari memperbarui, menambah atau menghapus data dalam tabel SQL, server secara otomatis membuat perubahan yang sesuai pada indeks, namun seiring waktu semua perubahan ini dapat menyebabkan fragmentasi data dalam indeks, mis. mereka pada akhirnya akan tersebar di seluruh database. Fragmentasi indeks menyebabkan penurunan kinerja query, sehingga secara berkala perlu dilakukan operasi pemeliharaan indeks, yaitu defragmentasi, seperti operasi reorganisasi indeks dan pembangunan kembali.

Kapan menggunakan reorganisasi indeks dan kapan harus membangun kembali?

Untuk menjawab pertanyaan ini, pertama-tama Anda perlu menentukan tingkat fragmentasi indeks, karena bergantung pada fragmentasi indeks, satu atau beberapa metode defragmentasi akan lebih disukai dan efektif. Anda dapat menggunakan fungsi tabel sistem untuk menentukan tingkat fragmentasi indeks sys.dm_db_index_physical_stats, yang mengembalikan informasi rinci tentang ukuran dan fragmentasi indeks. Misalnya, dengan menggunakan kueri berikut, Anda bisa mengetahui tingkat fragmentasi indeks untuk semua tabel di database saat ini.

PILIH OBJECT_NAME(T1.object_id) SEBAGAI NameTable, T1.index_id SEBAGAI IndexId, T2.name SEBAGAI IndexName, T1.avg_fragmentation_in_percent SEBAGAI Fragmentasi FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) SEBAGAI T1 KIRI GABUNG sys. indeks SEBAGAI T2 PADA T1.object_id = T2.object_id DAN T1.index_id = T2.index_id

Dalam hal ini, kami tertarik pada kolom tersebut rata-rata_fragmentasi_dalam_persen, yaitu persentase fragmentasi logis.

  • Jika tingkat fragmentasi kurang dari 5%, maka penataan ulang atau pembangunan kembali indeks tidak boleh dimulai sama sekali;
  • Jika tingkat fragmentasi berkisar antara 5 hingga 30%, maka masuk akal untuk memulai reorganisasi indeks, karena operasi ini menggunakan sumber daya sistem minimal dan tidak memerlukan pemblokiran jangka panjang;
  • Jika derajat fragmentasi lebih dari 30%, maka indeks perlu dibangun kembali, karena operasi ini, dengan fragmentasi yang signifikan, memberikan pengaruh yang lebih besar daripada operasi reorganisasi indeks.

Secara pribadi, saya dapat menambahkan yang berikut ini jika Anda memiliki perusahaan kecil dan database tidak memerlukan output maksimal 24 jam sehari, yaitu. Karena ini bukan database super aktif, Anda dapat dengan aman melakukan operasi pembuatan ulang indeks secara berkala, bahkan tanpa menentukan tingkat fragmentasi.

Mengatur Ulang Indeks

Reorganisasi indeks adalah proses defragmentasi indeks yang mendefrag indeks cluster dan non-cluster tingkat daun pada tabel dan tampilan dengan menyusun ulang secara fisik halaman tingkat daun berdasarkan urutan logis ( dari kiri ke kanan) simpul akhir.

Untuk mengatur ulang indeks Anda dapat menggunakan like alat grafis Pernyataan SSMS dan Transact-SQL.

Menata Ulang Indeks Menggunakan Management Studio


Mengatur Ulang Indeks Menggunakan Transact-SQL

ALTER INDEX IX_NonClustered PADA TestTable REORGANISASI GO

Membangun kembali indeks

Membangun kembali indeks adalah proses yang menghapus indeks lama dan membuat indeks baru, sehingga menghilangkan fragmentasi.

Anda dapat menggunakan dua metode untuk membangun kembali indeks.

Pertama. Menggunakan pernyataan ALTER INDEX dengan klausa REBUILD. Pernyataan ini menggantikan pernyataan DBCC DBREINDEX. Biasanya, ini adalah metode yang digunakan untuk membangun kembali indeks secara massal.

Contoh

ALTER INDEX IX_NonClustered PADA TestTable REBUILD GO

Dan yang kedua, menggunakan pernyataan CREATE INDEX dengan klausa DROP_EXISTING. Dapat digunakan, misalnya, untuk membangun kembali indeks dengan mengubah definisinya, yaitu. menambah atau menghapus kolom kunci.

Contoh

BUAT INDEKS NONCLUSTERED IX_NonClustered PADA TestTable (CategoryID ASC) DENGAN(DROP_EXISTING = ON) GO

Fungsionalitas pembangunan kembali juga tersedia di Management Studio. Klik kanan dengan indeks yang diperlukan " Membangun kembali».


Demikianlah materi tentang dasar-dasar indeks di Microsoft SQL Server, jika Anda tertarik bahasa T-SQL, maka saya sarankan membaca buku saya “



Memuat...
Atas