KAL
Menu

Indeks Covering

Pemindaian index-only ketika seluruh kolom yang dibutuhkan ada pada indeks.

Daftar isi

Sebuah pencarian melalui secondary index melewati dua pohon, yaitu indeksnya sendiri lalu clustered index, dan lompatan kedua itulah yang sering menjadi biaya tersembunyi sebuah query. Topik ini membahas indeks covering, yaitu indeks yang memuat seluruh kolom yang dibutuhkan sebuah query. Dengan indeks demikian, pemindaian selesai di indeks tanpa membaca tabel, dan teknik ini disebut index-only scan. Latihan memakai tabel peminjaman berisi 100000 baris pada basis data perpustakaan_covering, dan tanda Using index pada EXPLAIN menjadi bukti utamanya. Setelah topik ini, kolom-kolom query dapat disusun ke dalam indeks secara sadar, bukan sekadar mengindeks kolom pada WHERE.

Data latihan#

Berkas berikut membuat ulang basis data perpustakaan_covering dari nol. Tabel peminjaman berisi 100000 baris yang terbagi rata, yaitu setiap 40 baris berturut-turut milik satu anggota sehingga terdapat 2500 anggota. Satu-satunya indeks secondary adalah idx_anggota pada kolom anggota_id, yang dibuat setelah data dimuat dan dianalisis dengan ANALYZE TABLE. Opsi STATS_SAMPLE_PAGES menjaga angka perkiraan pada EXPLAIN tetap stabil antar eksekusi.

sql skema-covering.sql 
-- tidak-dijalankan
-- Skema basis data latihan "perpustakaan_covering" untuk topik indeks covering.
-- Menjalankan ulang berkas ini selalu menghasilkan isi yang sama.

DROP DATABASE IF EXISTS perpustakaan_covering;
CREATE DATABASE perpustakaan_covering;
USE perpustakaan_covering;

SET SESSION cte_max_recursion_depth = 1000000;

CREATE TABLE peminjaman (
  peminjaman_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  anggota_id INT UNSIGNED NOT NULL,
  buku_id INT UNSIGNED NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  status VARCHAR(10) NOT NULL
) STATS_SAMPLE_PAGES=1024;

INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, status)
WITH RECURSIVE urut AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM urut WHERE n < 100000
)
SELECT
  ((n - 1) DIV 40) + 1,
  (n MOD 500) + 1,
  DATE_ADD('2024-01-01', INTERVAL (n MOD 730) DAY),
  ELT((n MOD 3) + 1, 'dipinjam', 'kembali', 'terlambat')
FROM urut;

CREATE INDEX idx_anggota ON peminjaman (anggota_id);

ANALYZE TABLE peminjaman;
bash 
# membuat ulang basis data latihan beserta statistiknya
mysql -t -u root < skema-covering.sql
text Keluaran 
+----------------------------------+---------+----------+----------+
| Table                            | Op      | Msg_type | Msg_text |
+----------------------------------+---------+----------+----------+
| perpustakaan_covering.peminjaman | analyze | status   | OK       |
+----------------------------------+---------+----------+----------+

Baris status OK menandakan statistik indeks sudah diperbarui. Basis data ini sengaja sederhana, karena fokus topik adalah lompatan ke tabel, bukan variasi indeks.

Lompatan ke clustered index#

Daun idx_anggota menyimpan pasangan (anggota_id, peminjaman_id), sedangkan kolom lain seperti tanggal_pinjam hanya tersedia pada clustered index. Query berikut mengambil tanggal pinjam milik anggota 42, dan karena jumlah barisnya 40, jawabannya dibayar dengan 40 lompatan dari entri indeks ke baris tabel.

bash 
# rentang tanggal pinjam milik anggota 42
mysql -u root perpustakaan_covering -t -e "SELECT MIN(tanggal_pinjam) AS tanggal_awal, MAX(tanggal_pinjam) AS tanggal_akhir, COUNT(*) AS jumlah FROM peminjaman WHERE anggota_id = 42;"
text Keluaran 
+--------------+---------------+--------+
| tanggal_awal | tanggal_akhir | jumlah |
+--------------+---------------+--------+
| 2024-06-30   | 2024-08-08    |     40 |
+--------------+---------------+--------+

Diagram berikut membandingkan jalur query ini dengan jalur yang selesai di indeks. Panel kiri memperlihatkan lompatan per baris ke tabel, sedangkan panel kanan memperlihatkan pemindaian tanpa lompatan sama sekali.

Tanpa covering: query melompat ke tabelidx_anggota: (42, 1641), (42, 1642)tanggal_pinjam tidak ada di sinitabel peminjaman: baris lengkaptanggal_pinjam dibaca dari sini40 baris berarti 40 kali lompatan ke tabel.Dengan covering: selesai di indeksidx_anggota_tanggal:(42, 2024-06-30), (42, 2024-07-01)jawaban dirakit dari entri indeksTabel tidak dibaca sama sekali (Using index).
Dua jalur pembacaan: melompat ke tabel atau selesai di indeks

Dua keluaran EXPLAIN berikut menunjukkan perbedaan kedua jalur itu pada kolom Extra. Query pertama menanyakan peminjaman_id, yang ternyata sudah tersedia pada daun indeks karena primary key ikut tersimpan di setiap entri secondary index.

bash 
# peminjaman_id sudah ada di dalam indeks
mysql -u root perpustakaan_covering -e "EXPLAIN SELECT peminjaman_id FROM peminjaman WHERE anggota_id = 42 \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: ref
possible_keys: idx_anggota
          key: idx_anggota
      key_len: 4
          ref: const
         rows: 40
     filtered: 100.00
        Extra: Using index
bash 
# tanggal_pinjam harus diambil dari tabel
mysql -u root perpustakaan_covering -e "EXPLAIN SELECT tanggal_pinjam FROM peminjaman WHERE anggota_id = 42 \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: ref
possible_keys: idx_anggota
          key: idx_anggota
      key_len: 4
          ref: const
         rows: 40
     filtered: 100.00
        Extra: NULL

Kedua rencana memakai indeks yang sama dengan perkiraan 40 baris, tetapi Extra-nya berbeda. Tanda Using index pada query pertama berarti seluruh jawaban dirakit dari isi indeks, sedangkan Extra: NULL pada query kedua berarti setiap baris tetap dilompati untuk mengambil tanggalnya.

Membuat indeks covering#

Cara menghilangkan lompatan adalah memasukkan kolom yang dibutuhkan ke dalam indeks. Pernyataan berikut membuat indeks komposit (anggota_id, tanggal_pinjam), sehingga query yang menyaring anggota dan menanyakan tanggal terjawab penuh dari indeks.

bash 
# membuat indeks komposit dan memperbarui statistiknya
mysql -u root perpustakaan_covering -t -e "CREATE INDEX idx_anggota_tanggal ON peminjaman (anggota_id, tanggal_pinjam); ANALYZE TABLE peminjaman;"
text Keluaran 
+----------------------------------+---------+----------+----------+
| Table                            | Op      | Msg_type | Msg_text |
+----------------------------------+---------+----------+----------+
| perpustakaan_covering.peminjaman | analyze | status   | OK       |
+----------------------------------+---------+----------+----------+

Query tanggal kemudian dijalankan lagi tanpa perubahan apa pun pada pernyataannya.

bash 
# query yang sama setelah indeks covering tersedia
mysql -u root perpustakaan_covering -e "EXPLAIN SELECT tanggal_pinjam FROM peminjaman WHERE anggota_id = 42 \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: ref
possible_keys: idx_anggota,idx_anggota_tanggal
          key: idx_anggota_tanggal
      key_len: 4
          ref: const
         rows: 40
     filtered: 100.00
        Extra: Using index

Indeks terpilih berpindah ke idx_anggota_tanggal dan Extra berubah menjadi Using index, yang berarti pemindaian selesai di indeks tanpa satu lompatan pun ke tabel. Perkiraan 40 baris tidak berkurang, karena jumlah baris yang dibaca tetap sama, dan yang hilang adalah lompatannya.

Jangkauan tetap covering#

Saringan jangkauan pada kolom yang tercakup tidak membatalkan sifat covering, karena evaluasinya tetap berlangsung di dalam indeks. Query berikut menanyakan tanggal pinjam mulai dari satu hari tertentu untuk anggota 42.

bash 
# jangkauan tanggal yang tetap selesai di indeks
mysql -u root perpustakaan_covering -e "EXPLAIN SELECT tanggal_pinjam FROM peminjaman WHERE anggota_id = 42 AND tanggal_pinjam >= '2024-07-01' \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: range
possible_keys: idx_anggota,idx_anggota_tanggal
          key: idx_anggota_tanggal
      key_len: 7
          ref: NULL
         rows: 39
     filtered: 100.00
        Extra: Using where; Using index

Kombinasi Using where; Using index menunjukkan dua hal sekaligus, yaitu kondisi jangkauan dievaluasi dan jawabannya dirakit dari indeks. Nilai key_len naik menjadi 7 byte karena kedua kolom kunci, yaitu anggota_id dan tanggal_pinjam, ikut mencari, dan perkiraan 39 baris dihitung langsung dari entri indeks.

Batasan indeks covering#

Sifat covering hilang begitu ada satu kolom saja yang berada di luar indeks. Query berikut menanyakan kolom status, yang tidak dimuat oleh kedua indeks yang ada.

bash 
# satu kolom di luar indeks membatalkan covering
mysql -u root perpustakaan_covering -e "EXPLAIN SELECT status FROM peminjaman WHERE anggota_id = 42 \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: peminjaman
   partitions: NULL
         type: ref
possible_keys: idx_anggota,idx_anggota_tanggal
          key: idx_anggota
      key_len: 4
          ref: const
         rows: 40
     filtered: 100.00
        Extra: NULL

Extra kembali menjadi NULL, yang berarti lompatan ke tabel terjadi lagi. Konsekuensi logisnya, query dengan SELECT * tidak pernah menjadi covering, karena kolom yang dibutuhkannya melampaui indeks mana pun. Batasan lain bersifat biaya, yaitu setiap kolom yang ditambahkan ke indeks memperbesar B-tree, memperlambat penulisan, dan menambah ruang simpan. Oleh karena itu, indeks covering layak dibuat hanya untuk query baca yang sering dijalankan dan bentuknya tetap.

Kesalahan umum#

Kesalahan pertama adalah membaca Using index sebagai bukti indeks dipakai, padahal bukti pemakaian ada pada kolom key, sedangkan tanda itu hanya menyatakan jawaban selesai di indeks. Kesalahan kedua adalah memasukkan terlalu banyak kolom ke dalam satu indeks demi meraih covering untuk semua query. Indeks yang melebar menggandakan biaya tulis dan simpan, dan query yang jarang dijalankan tidak sepadan dengan harganya. Kesalahan ketiga adalah menilai manfaat covering dari kolom rows, karena angka itu tetap 40 pada keluaran di atas. Yang benar-benar hilang adalah lompatan ke clustered index, dan jejaknya hanya tampak pada perubahan Extra.

Ringkasan#

  • Daun secondary index hanya memuat kuncinya plus primary key, sehingga kolom lain memaksa lompatan ke clustered index per baris.
  • Apabila seluruh kolom query tercakup satu indeks, EXPLAIN menampilkan Using index pada Extra sebagai tanda pemindaian index-only.
  • Saringan jangkauan pada kolom tercakup tetap covering, sedangkan satu kolom di luar indeks atau SELECT * membatalkannya.
  • Manfaat covering tidak terlihat pada kolom rows, melainkan pada hilangnya lompatan, dan harganya adalah indeks yang lebih lebar untuk ditulis.

Referensi#