Keuntungan dan Kerugian Pengindeksan Dalam SQL – Indeks adalah objek skema yang berisi kunci dari berbagai kolom tabel data kami. B-Trees digunakan untuk menyimpan kunci indeks. Ini membantu kami menemukan baris atau nilai yang akan dicari lebih cepat.
Keuntungan dan Kerugian Pengindeksan Dalam SQL
pgsql – Mereka memiliki berbagai keunggulan seperti peningkatan kinerja dalam mencari catatan, menyortir catatan, mengelompokkan catatan, atau mempertahankan kolom unik. Beberapa kelemahannya termasuk peningkatan ruang disk, modifikasi data yang lebih lambat, dan memperbarui catatan dalam indeks berkerumun.
Baca Juga : Panduan Lengkap untuk Penyesuaian Kinerja PostgreSQL
Indeks dalam SQL membantu kami menemukan catatan atau daftar catatan dengan mencocokkan kondisi klausa WHERE . Ini dapat membantu kueri untuk mencari nilai tertentu atau di dalam rentang nilai. Itu membuat pencarian lebih cepat, yang pada akhirnya mengarah pada peningkatan kinerja kueri. Pernyataan seperti SELECT , UPDATE dan DELETE memanfaatkan indeks untuk meningkatkan eksekusi pencarian. Mari kita lihat masing-masing melalui contoh.
Mari kita mulai dengan pernyataan SELECT .
SELECT * FROM Employee WHERE Salary > 65000 AND Salary < 81000
Dalam contoh di atas, kita dapat melihat pernyataan SQL SELECT yang mendapat manfaat dari indeks di kolom Gaji. Indeks mengurutkan gaji dalam urutan menaik. Pernyataan SELECT menggunakan indeks ini untuk mengidentifikasi dan memisahkan catatan dengan gaji antara 65.000 dan 81.000.
Kueri berikut menggunakan pernyataan DELETE di SQL .
DELETE FROM Employee where Salary = 70000
Pernyataan DELETE di atas menghapus catatan karyawan dengan gaji sama dengan 70000. Indeks membantu kueri menemukan baris lebih cepat, meningkatkan kinerja pencarian kueri. Logika yang sama berulang untuk pernyataan UPDATE .
UPDATE Employee SET Salary = 80000 WHERE Salary = 55000
Kueri SQL di atas memperbarui catatan karyawan yang gajinya saat ini adalah 55000. Nilai gaji yang diperbarui diatur ke 80000. Baris dengan gaji 55000 ditemukan menggunakan indeks.
Menyortir Catatan
Kami menggunakan indeks dalam menyortir kumpulan data. Basis data menemukan indeks untuk menghindari penyortiran selama eksekusi kueri. Urutan yang diurutkan ditentukan oleh kata kunci ASC dan DESC untuk menaik dan menurun, masing-masing. Klausa ORDER BY menentukan bidang tunggal atau ganda untuk membatasi pengurutan kumpulan data. Mari kita lihat lebih dekat menggunakan contoh.
SELECT * FROM Employee ORDER BY Salary ASC
SQL Query di atas mengembalikan detail karyawan dengan gaji mereka yang diurutkan dalam urutan menaik.
Jika tidak ada indeks, database akan memindai tabel Karyawan. Lebih lanjut memproses kueri dengan menyortir baris. Namun, indeks pada Gaji memberikan daftar gaji yang telah disortir ke database. Ini kemudian memindai indeks dari pertama hingga terakhir untuk mendapatkan baris dalam urutan yang diurutkan. Indeks yang sama berfungsi dengan baik untuk menyortir kueri dalam urutan terbalik.
SELECT * FROM Employee ORDER BY Salary DESC
Kueri SQL di atas mengembalikan detail karyawan dengan gaji mereka yang diurutkan dalam urutan menurun. Itu terjadi dengan menelusuri indeks dalam urutan terbalik.
Pengelompokan Catatan
Indeks membantu mengelompokkan rekaman dengan kolom terindeks yang digunakan dengan klausa GROUP BY . Kueri SQL dengan klausa GROUP BY mengurutkan kolom yang termasuk dalam klausa untuk menemukan hasilnya. Mari kita memiliki pemahaman yang lebih baik dengan menggunakan contoh.
SELECT Count(*), Salary FROM Employee GROUP BY Salary
Kueri SQL di atas mengembalikan hitungan jumlah karyawan dengan setiap jumlah gaji. Ini terjadi dengan mengelompokkan karyawan berdasarkan gajinya sehingga semua karyawan yang memiliki gaji yang sama tetap bersama.
Indeks IDX_Salary digunakan untuk mendapatkan gaji dalam urutan terurut. Menyortir menurut gaji menyatukan karyawan dengan gaji yang sama. Ini membantu database menghitung jumlah karyawan dari setiap gaji lebih cepat karena muncul secara berurutan di database yang dihasilkan. Dengan demikian, pengindeksan mempercepat pengelompokan catatan dengan klausa GROUP BY .
Mempertahankan Kolom Unik
Kami sering mengerjakan kunci primer di SQL . Indeks unik berlaku untuk kolom kunci utama yang memerlukan nilai unik. Ada berbagai cara untuk membuat indeks unik. Salah satu caranya adalah dengan menandai kolom sebagai primary key. Kolom kunci utama memiliki indeks unik pada kolom yang dibuat secara otomatis di atasnya. Cara lain adalah dengan mencentang CREATE UNIQUE yang memberikan indeks unik ke kolom yang ditentukan.
Mari buat indeks unik pada kolom EmpId.
CREATE UNIQUE INDEX IDX_EmpId On Employee (EmpId)
Kueri SQL di atas memberikan indeks unik ke kolom EmpId dari tabel Karyawan. Ini mencegah nilai duplikat di kolom EmpId dengan menggunakan indeks. Indeks adalah alat terbaik untuk database untuk membuat kolom dengan nilai unik. Selama manipulasi data dengan menambahkan atau memodifikasi baris, pertama-tama dicari dan dibandingkan dengan semua record yang ada. Ini memastikan bahwa nilai baru tetap unik dan tidak menduplikasi nilai yang sudah ada. Dengan demikian, indeks mempercepat kinerja dan meningkatkan kecepatan pencarian.
Kerugian dari Indeks dalam SQL
Setiap komponen pemrograman dilengkapi dengan kelebihan dan kekurangannya sendiri. Indeks juga tidak terkecuali. Meskipun meningkatkan kinerja pencarian dalam mencari dan menyortir catatan, ini memiliki kekurangan yang berbeda. Mari kita bahas masing-masing secara mendetail.
Ruang Disk Tambahan
Indeks berkerumun yang digunakan dalam SQL tidak memerlukan ruang ekstra karena menyimpan urutan fisik catatan tabel dalam database. Karena hanya ada satu urutan fisik tabel, hanya ada satu indeks berkerumun.
Tetapi berbeda untuk indeks non-cluster yang membutuhkan ruang disk ekstra. Indeks non-cluster adalah struktur terpisah dari baris data dalam tabel. Itu menyusun ulang satu atau lebih kolom karena urutan logis berbeda dari urutan fisik. Urutan fisik data adalah urutan penyimpanannya dalam database.
Ruang bergantung pada berbagai faktor yang digunakan dalam indeks, seperti ukuran tabel, jumlah kolom, dan jenis kolom. Saat memproses database dengan banyak pengguna, ruang disk dianggap lebih murah daripada kinerja aplikasi. Kode berikut digunakan untuk melihat ruang yang dibutuhkan oleh tabel.
EXEC sp_spaceused Employee
Kode di atas menggunakan sp_spaceused dengan nama tabel yang mengembalikan ruang yang ditempati oleh data tabel dan indeks yang terkait dengan tabel. Ini menampilkan jumlah baris, ukuran data, ukuran indeks, ruang disk yang dicadangkan, dan jumlah ruang disk yang digunakan dan tidak digunakan yang digunakan oleh tabel.
Perlambatan Modifikasi Data
Indeks memiliki respons yang buruk terhadap kinerja pernyataan modifikasi data seperti INSERT , UPDATE , atau DELETE . Setiap kali kueri meminta untuk mengubah data dalam tabel, database memperbarui dirinya sendiri dengan indeks baru tempat data berubah.
Seperti yang telah dibahas sebelumnya, indeks membantu kami menemukan catatan lebih cepat, yang mengarah ke performa penyortiran dan pencarian yang lebih cepat. Oleh karena itu, memiliki terlalu banyak indeks dapat membantu kami menemukan catatan lebih cepat tetapi berdampak buruk pada kecepatan modifikasi data. Jadi, kita perlu memiliki indeks yang baik yang menyeimbangkan kinerja sistem.
Kerugian dari Indeks Berkelompok
Setiap pembaruan catatan atau perubahan nilai kolom yang diindeks dalam indeks berkerumun mungkin memerlukan pemosisian ulang baris untuk mempertahankan urutan yang diurutkan. Query UPDATE juga dapat dilihat sebagai Query DELETE diikuti dengan pernyataan INSERT , yang menurunkan kinerja. Indeks berkerumun dari sebuah tabel, secara default, dibuat pada kolom kunci utama atau kunci asing. Itu karena indeks berkerumun menyimpan urutan fisik tabel yang hanya bisa satu dan unik seperti kunci utama.