Tahap Penalaan

Topik ini menala basis data proyek perpustakaan dengan EXPLAIN QUERY PLAN sebagai alat ukurnya. Tahapannya bergerak dari pembacaan rencana query laporan utama, penambahan satu index komposit yang dibuktikan keluarannya, audit seluruh index yang terpasang, sampai PRAGMA optimize yang menutup pekerjaan. Setiap keputusan penalaan pada topik ini disertai perbandingan rencana sebelum dan sesudahnya, bukan dengan kesan cepat atau lambat.

Membaca rencana query laporan utama#

Penalaan diulang dari basis data yang bersih agar buktinya dapat direproduksi. Dua berkas rancangan dari tahap perencanaan dibangun ulang tanpa perubahan, sehingga kondisi awalnya sama dengan hasil tahap implementasi, kecuali mode journal yang kembali ke bawaan karena berkasnya baru diciptakan. Dari keadaan itu, rencana dua query laporan dibaca terlebih dahulu dengan EXPLAIN QUERY PLAN, yaitu pernyataan yang menampilkan rencana eksekusi tanpa menjalankan query-nya.

# membangun ulang perpustakaan.db agar titik awal penalaan pasti
rm -f perpustakaan.db
sqlite3 perpustakaan.db < skema.sql
sqlite3 perpustakaan.db < data.sql

Kandidat pertama adalah query laporan denda bulanan, kebutuhan laporan yang diperkirakan paling sering dijalankan pada akhir setiap bulan.

# rencana query laporan denda bulanan
sqlite3 perpustakaan.db "EXPLAIN QUERY PLAN SELECT strftime('%Y-%m', tanggal_kembali) AS bulan, SUM(denda) AS total_denda FROM peminjaman WHERE tanggal_kembali IS NOT NULL GROUP BY bulan ORDER BY bulan;"

QUERY PLAN
|--SCAN peminjaman
`--USE TEMP B-TREE FOR GROUP BY

Baris SCAN peminjaman memang terlihat mengkhawatirkan, tetapi keputusannya justru sudah benar. Laporan ini merangkum seluruh pengembalian, sehingga setiap baris tetap harus dibaca dan tidak ada index yang mengurangi pekerjaannya. Baris USE TEMP B-TREE FOR GROUP BY menandai pengelompokan yang dikerjakan penyangga sementara karena urutan pembacaan tidak cocok dengan kelompoknya. Index idx_peminjaman_tanggal_kembali tidak tersentuh oleh rencana ini, dan keberadaannya ditinjau kembali pada audit nanti.

Rencana view peminjaman_aktif memberi kontras yang berguna.

# rencana view peminjaman_aktif yang menyaring menurut status
sqlite3 perpustakaan.db "EXPLAIN QUERY PLAN SELECT * FROM peminjaman_aktif;"

QUERY PLAN
|--SEARCH p USING INDEX idx_peminjaman_status (status=?)
|--SEARCH a USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)

Tiga baris di atas adalah bentuk nested loop yang sehat. Syarat status dipinjam dilayani idx_peminjaman_status, dan setiap baris hasilnya langsung dicari pasangannya di tabel anggota serta buku melalui pencarian rowid yang tercepat. Query ini tidak membutuhkan tindakan apa pun, dan kontrasnya dengan laporan denda menegaskan bahwa keputusan penalaan selalu menempel pada bentuk query, bukan pada label SCAN atau SEARCH semata.

Menambah index komposit#

Kebutuhan yang belum terlayani sempurna adalah peminjaman aktif milik satu anggota. Syaratnya menyebut dua kolom, yaitu anggota_id dan status, sedangkan index yang ada hanya menyimpan salah satunya. Rencananya dibaca lebih dahulu sebagai titik ukur.

# rencana peminjaman aktif milik satu anggota sebelum komposit
sqlite3 perpustakaan.db "EXPLAIN QUERY PLAN SELECT b.judul, p.tanggal_pinjam FROM peminjaman p JOIN buku b ON b.id = p.buku_id WHERE p.anggota_id = 3 AND p.status = 'dipinjam';"

QUERY PLAN
|--SEARCH p USING INDEX idx_peminjaman_anggota (anggota_id=?)
`--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)

Rencana semula memakai idx_peminjaman_anggota untuk kolom anggota_id, sedangkan syarat status dipinjam tersisa sebagai penyaringan atas baris yang dikembalikan index. Tanda (anggota_id=?) menegaskan bahwa hanya satu syarat yang terlayani index. Index komposit pada (anggota_id, status) menutup celah itu karena kedua kolomnya menyesuaikan urutan syarat query.

# menambah index komposit lalu membaca rencana yang sama sekali lagi
sqlite3 perpustakaan.db "CREATE INDEX idx_peminjaman_anggota_status ON peminjaman(anggota_id, status);"
sqlite3 perpustakaan.db "EXPLAIN QUERY PLAN SELECT b.judul, p.tanggal_pinjam FROM peminjaman p JOIN buku b ON b.id = p.buku_id WHERE p.anggota_id = 3 AND p.status = 'dipinjam';"

QUERY PLAN
|--SEARCH p USING INDEX idx_peminjaman_anggota_status (anggota_id=? AND status=?)
`--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)

Setelah index komposit dibuat, tanda syaratnya berubah menjadi (anggota_id=? AND status=?), artinya kedua syarat diterapkan langsung ketika index dibaca tanpa penyaringan susulan. Baris yang dikunjungi pun lebih sedikit karena hanya kelompok anggota tertentu yang berstatus dipinjam. Inilah bukti yang wajib menyertai setiap index baru, yaitu perbandingan rencana sebelum dan sesudah yang dijalankan sungguhan.

Mengaudit index yang ada#

Menambah index tanpa mengaudit yang lama menumpuk biaya penulisan secara diam-diam. Audit dimulai dengan PRAGMA index_list yang mendaftar seluruh index tabel peminjaman beserta asal-usulnya.

# mengaudit seluruh index pada tabel peminjaman
sqlite3 perpustakaan.db "PRAGMA index_list(peminjaman);"

0|idx_peminjaman_anggota_status|0|c|0
1|idx_peminjaman_tanggal_kembali|0|c|0
2|idx_peminjaman_status|0|c|0
3|idx_peminjaman_anggota|0|c|0

Empat index tercatat pada tabel peminjaman, yaitu tiga dari rancangan dan satu hasil penalaan. Kolom origin bernilai c menyatakan index buatan CREATE INDEX, sedangkan nilai u menandai index otomatis milik constraint UNIQUE. Dua kandidat pembuangan muncul dari perbandingan dengan rencana-rencana di atas. idx_peminjaman_tanggal_kembali tidak pernah disebut oleh satu pun rencana query kebutuhan utama, sedangkan idx_peminjaman_anggota kini tergantikan oleh index komposit yang kolom pertamanya sama.

# membuang index yang tak terpakai lalu memeriksa ulang rencananya
sqlite3 perpustakaan.db "DROP INDEX idx_peminjaman_anggota;"
sqlite3 perpustakaan.db "DROP INDEX idx_peminjaman_tanggal_kembali;"
# riwayat peminjaman satu anggota kini dilayani prefix index komposit
sqlite3 perpustakaan.db "EXPLAIN QUERY PLAN SELECT b.judul, p.tanggal_pinjam, p.tanggal_kembali FROM peminjaman p JOIN buku b ON b.id = p.buku_id WHERE p.anggota_id = 3 ORDER BY p.tanggal_pinjam;"
# laporan denda tetap memindai tabel, dan itu memang rencana yang tepat
sqlite3 perpustakaan.db "EXPLAIN QUERY PLAN SELECT strftime('%Y-%m', tanggal_kembali) AS bulan, SUM(denda) AS total_denda FROM peminjaman WHERE tanggal_kembali IS NOT NULL GROUP BY bulan ORDER BY bulan;"

QUERY PLAN
|--SEARCH p USING INDEX idx_peminjaman_anggota_status (anggota_id=?)
|--SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY
QUERY PLAN
|--SCAN peminjaman
`--USE TEMP B-TREE FOR GROUP BY

Pembuangan dengan DROP INDEX tidak meninggalkan rencana yang memburuk. Query riwayat satu anggota yang sebelumnya memakai idx_peminjaman_anggota kini dilayani prefix kolom pertama milik index komposit, sehingga bentuknya tetap SEARCH. Rencana laporan denda tetap SCAN peminjaman, sebagaimana seharusnya untuk laporan penuh. Jumlah index yang harus dipelihara saat menulis turun dari empat menjadi dua pada tabel peminjaman, sedangkan layanan querynya tidak berkurang.

Menutup dengan PRAGMA optimize#

Langkah terakhir menyerahkan sisa analisis kepada mesin. PRAGMA optimize menjalankan ANALYZE hanya pada tabel yang menurut heuristiknya membutuhkan pembaruan statistik, dan hasilnya ditulis ke tabel sqlite_stat1. Statistik inilah yang dipakai query planner untuk menimbang rencana, sehingga pembaruannya menegakkan keputusan penalaan yang sudah diambil.

# menjalankan PRAGMA optimize lalu membaca statistik yang tercipta
sqlite3 perpustakaan.db "PRAGMA optimize;"
sqlite3 perpustakaan.db "SELECT * FROM sqlite_stat1;"

peminjaman|idx_peminjaman_anggota_status|13 3 2
peminjaman|idx_peminjaman_status|13 7
kategori|sqlite_autoindex_kategori_1|7 1
buku|idx_buku_kategori|8 2
anggota|sqlite_autoindex_anggota_1|6 1

Setiap baris sqlite_stat1 menyimpan perkiraan jumlah baris tabel beserta jumlah nilai yang berbeda pada setiap kolom indexnya, misalnya 13 3 2 pada index komposit. Angkanya kecil karena data contohnya kecil, tetapi bentuknya sama dengan basis data berskala mana pun. PRAGMA optimize lazim dijalankan ketika aplikasi menutup koneksinya, sehingga statistik selalu segar tanpa pekerjaan tambahan yang terjadwal terpisah.

Keputusan penalaan#

Seluruh langkah di atas meninggalkan jejak keputusan yang dapat ditinjau kembali kapan pun. Tabel berikut merangkumnya dengan bukti rencana yang menyertainya, sebagaimana laporan penalaan pada umumnya ditulis agar keputusannya dapat diuji ulang oleh siapa pun yang memegang basis datanya.

Dua index dibuang dan satu index ditambah, sehingga jumlahnya berkurang sementara cakupannya bertambah. Keputusan pembuangan selalu menimbang dua sisi, yaitu biaya penulisan yang dibayar pada setiap INSERT dan UPDATE, dibandingkan kecepatan baca yang ternyata tidak pernah dipakai. Index pada tabel yang padat tulis tanpa pembaca yang jelas adalah beban murni, sedangkan index yang terbukti pada rencana layak dipelihara. Pola kerja pada tabel itu diulang setiap kali pola pemakaian berubah, dan pembangunan ulang dari skema.sql membuat setiap eksperimen penalaan dapat diulang dari titik yang sama.

Ringkasan#

• EXPLAIN QUERY PLAN memberi bukti sebelum dan sesudah setiap tindakan penalaan, dan SCAN pada laporan penuh bukan masalah yang perlu diperbaiki.
• Index komposit (anggota_id, status) melayani kedua syarat sekaligus dan menggantikan peran index satu kolom anggota_id.
• Audit PRAGMA index_list membuang dua index tak terpakai, sedangkan PRAGMA optimize mengisi sqlite_stat1 bagi keputusan planner berikutnya.

Referensi#

• EXPLAIN QUERY PLAN — SQLite — Dokumentasi resmi bentuk keluaran rencana eksekusi beserta makna setiap barisnya.
• CREATE INDEX — SQLite — Sintaks index komposit dan urutan kolom yang melayani syarat query.
• PRAGMA Statements — SQLite — Dokumentasi pragma index_list dan optimize beserta tabel sqlite_stat1.