KAL
Menu

Anti-pola Index

Pengindeksan berlebihan, kolom selektivitas rendah, dan cara mengaudit index.

Daftar isi

Topik ini membahas kebiasaan pengindeksan yang memberatkan basis data tanpa mempercepat kuerinya. Empat anti-polanya dibahas satu per satu, yaitu mengindeks setiap kolom, mengindeks kolom dengan selektivitas rendah, membuat index duplikat, dan membuat index yang tidak dilayani kueri. Bagian penutup menyediakan prosedur audit untuk membenahi index yang sudah terpasang.

Mengindeks setiap kolom#

Kebiasaan yang paling umum adalah membuat index untuk setiap kolom yang ada. Setiap index menambah halaman basis data sebagaimana diamati melalui PRAGMA page_count, dan setiap INSERT, DELETE, serta UPDATE pada kolom terindeks harus memperbarui seluruh index yang memuat kolom itu. Blok berikut menambahkan tiga index pada tabel 20000 baris, lalu mengamati dampak ruangnya sebelum dan sesudah.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
PRAGMA page_count;
CREATE INDEX idx_buku_judul ON buku(judul);
CREATE INDEX idx_buku_penulis ON buku(penulis);
CREATE INDEX idx_buku_tahun ON buku(tahun);
PRAGMA page_count;
text Keluaran 
160
389

Tiga index menambah 229 halaman, sekitar satu setengah kali ukuran tabelnya. Biaya ruang hanya separuh ceritanya, karena setiap penulisan kini memperbarui empat struktur B-tree sekaligus, yaitu tabel dan tiga indexnya. Pada tabel yang ditulis terus-menerus, beban ini terasa pada seluruh beban kerja, bukan hanya pada kueri yang dibantu index. Index dibuat untuk kolom yang benar-benar menjadi syarat, kunci join, atau dasar pengurutan pada kueri nyata, bukan untuk setiap kolom yang terlihat.

Kolom selektivitas rendah#

Selektivitas adalah ukuran keunikan nilai sebuah kolom, yaitu perbandingan banyaknya nilai berbeda terhadap jumlah baris. Kolom dengan selektivitas rendah memuat sedikit nilai berbeda, misalnya kolom rak yang hanya menampung A, B, dan C untuk 20000 buku. Index pada kolom semacam ini terlihat sah, tetapi kelompok baris yang cocoknya terlalu besar untuk dilompati dengan efisien.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  rak     TEXT NOT NULL
);
INSERT INTO buku (judul, rak)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, CASE i % 3 WHEN 0 THEN 'A' WHEN 1 THEN 'B' ELSE 'C' END FROM deret;
CREATE INDEX idx_buku_rak ON buku(rak);
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE rak = 'B';
ANALYZE;
EXPLAIN QUERY PLAN SELECT judul FROM buku WHERE rak = 'B';
text Keluaran 
QUERY PLAN
`--SEARCH buku USING INDEX idx_buku_rak (rak=?)
QUERY PLAN
`--SEARCH buku USING INDEX idx_buku_rak (rak=?)

Rencananya bertuliskan SEARCH, tetapi keluaran itu tidak boleh dibaca sebagai bukti efisiensi. Index pada rak = ‘B’ mencakup sekitar 6667 baris atau sepertiga tabel, dan setiap baris yang cocok masih diambil dari tabel melalui rowidnya, sehingga total pekerjaannya mendekati pemindaian tabel ditambah biaya jalan index. Perintah ANALYZE yang mengumpulkan statistik nyata pun tidak mengubah rencana pada kasus ini. Kolom dengan sedikit nilai berbeda jarang menjadi penyaring yang efektif, sehingga index sebaiknya disimpan untuk kolom dengan banyak nilai berbeda seperti judul atau tahun.

Index duplikat#

Index duplikat adalah index yang kolom awalnya sama dengan index lain, sehingga kebutuhan pencariannya sudah dilayani index yang lebih lengkap. Index pada kolom judul menjadi tidak berguna setelah index (judul, penulis) dibuat, karena kesamaan pada judul dilayani oleh awalan index komposit tersebut. Duplikat semacam ini biasanya lahir dari skrip inisialisasi yang bertambah dari waktu ke waktu tanpa pernah ditinjau.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL
);
INSERT INTO buku (judul, penulis) VALUES
  ('Kisah Kota Tua', 'Sari Wulandari'),
  ('Anak Sungai', 'Rahmat Hidayat'),
  ('Musim di Ujung Jalan', 'Lina Marlina');
CREATE INDEX idx_buku_judul ON buku(judul);
CREATE INDEX idx_buku_judul_penulis ON buku(judul, penulis);
PRAGMA index_list(buku);
DROP INDEX idx_buku_judul;
PRAGMA index_list(buku);
text Keluaran 
0|idx_buku_judul_penulis|0|c|0
1|idx_buku_judul|0|c|0
0|idx_buku_judul_penulis|0|c|0

PRAGMA index_list menampilkan kedua index, lalu DROP INDEX menghapus yang redundan dan daftar berikutnya tinggal satu. Menghapus index duplikat aman dilakukan karena isinya dapat dibangun ulang kapan saja, dan setiap penghapusan langsung memangkas biaya tulis serta ruangnya. Index komposit yang tersisa tetap melayani pencarian pada judul maupun pasangan judul dan penulis.

Kueri yang tidak dilayani index#

Anti-pola terakhir bukan pada indexnya, melainkan pada kuerinya. Kueri yang menerapkan fungsi pada kolom, seperti upper(judul) = ‘BUKU 7’, tidak dapat dilayani index pada kolom judul, karena isian index menyimpan judul apa adanya dan bukan hasil fungsinya. Keluarga yang sama mencakup LIKE dengan awal wildcard dan perhitungan pada kolom seperti tahun + 1 = 2021.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
CREATE INDEX idx_buku_judul ON buku(judul);
EXPLAIN QUERY PLAN SELECT id FROM buku WHERE upper(judul) = 'BUKU 7';
CREATE INDEX idx_buku_judul_upper ON buku(upper(judul));
EXPLAIN QUERY PLAN SELECT id FROM buku WHERE upper(judul) = 'BUKU 7';
text Keluaran 
QUERY PLAN
`--SCAN buku USING COVERING INDEX idx_buku_judul
QUERY PLAN
`--SEARCH buku USING COVERING INDEX idx_buku_judul_upper (<expr>=?)

Rencana pertama tetap bertuliskan SCAN meskipun menyebut index, yang berarti seluruh isian idx_buku_judul dibaca satu per satu, dan itu tetap pemindaian penuh atas index. Keluaran berubah menjadi SEARCH setelah index ekspresi buku (upper(judul)) dibuat, dengan tanda (=?) yang merujuk ekspresi indexnya. Perbaikan semacam ini dikerjakan pada sisi kueri dan skema, bukan dengan menambah index kolom baru yang tidak akan pernah ditunjuk.

Mengaudit index yang ada#

Audit index membandingkan index yang terpasang dengan kueri yang benar-benar dijalankan. Prosedurnya sebagai berikut.

  1. Daftar index setiap tabel dengan PRAGMA index_list, termasuk index otomatis dari UNIQUE dan PRIMARY KEY yang origin-nya u dan pk.
  2. Rinci kolom setiap index dengan PRAGMA index_info atau index_xinfo, termasuk arah urutannya.
  3. Kumpulkan kueri nyata dari aplikasi, lalu tandai kolom yang menjadi syarat WHERE, kunci join, atau dasar ORDER BY.
  4. Jalankan EXPLAIN QUERY PLAN untuk kueri utama, lalu hapus index yang tidak pernah ditunjuk dengan DROP INDEX.

Blok berikut memerinci satu index komposit berarah dengan index_xinfo pada tabel yang sudah dikenal.

sql 
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL,
  tahun   INTEGER NOT NULL
);
INSERT INTO buku (judul, penulis, tahun)
WITH RECURSIVE deret(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM deret WHERE i < 20000
)
SELECT 'Buku ' || i, 'Penulis ' || (1 + (i % 200)), 1950 + (i % 75) FROM deret;
CREATE INDEX idx_buku_tahun_desc ON buku(tahun DESC, judul);
PRAGMA index_xinfo(idx_buku_tahun_desc);
text Keluaran 
0|3|tahun|1|BINARY|1
1|1|judul|0|BINARY|1
2|-1||0|BINARY|0

Keluaran index_xinfo memuat enam kolom, yaitu seqno, cid, name, desc, coll, dan key. Baris pertama memuat tahun dengan desc 1, yang berarti kolom ini terurut menurun, dan kolom judul mengikutinya menaik. Baris terakhir dengan cid -1 adalah isian rowid yang melekat pada setiap index dan bertanda key 0. Dengan daftar index dan daftar kueri berdampingan, keputusan menghapus atau menambah index menjadi keputusan yang berdasar.

Ringkasan#

  • Index pada setiap kolom menumpuk biaya ruang dan memperberat setiap penulisan, sehingga index dibuat hanya untuk kolom yang dipakai kueri nyata.
  • Index pada kolom selektivitas rendah melayani kelompok baris yang terlalu besar, dan teks SEARCH pada rencananya bukan bukti efisiensi.
  • Index dengan kolom awal yang sama merupakan duplikat yang aman dihapus, dan kueri yang menerapkan fungsi pada kolom tidak dilayani index kolom biasa.
  • Audit index memadukan PRAGMA index_list dan index_xinfo dengan daftar kueri nyata, lalu menghapus index yang tidak pernah ditunjuk.

Referensi#