Statistik dan Optimizer — ANALYZE dan Selektivitas

Dataset untuk artikel ini — perf_sales (50.000 baris penjualan)

Artikel ini tentang bagaimana database memutuskan indeks mana yang dipakai.

Bagian kuncinya adalah statistik.

Statistik adalah fakta distribusi tentang data kamu — "berapa baris yang dimiliki tabel", "berapa nilai berbeda yang dipegang kolom" — dan kamu mengumpulkannya dengan perintah ANALYZE.

Dari statistik itu database menghitung selektivitas (estimasi fraksi baris yang lolos sebuah kondisi) dan memutuskan apakah memakai indeks atau membaca seluruh tabel.

Kamu akan bekerja dengan tabel penjualan perf_sales (50.000 baris).

Ia punya kolom dengan kardinalitas yang sangat berbeda: emp_id (30 rep berbeda), region (4 nilai: East / West / North / South), product (200 nilai, P001 sampai P200), dan status (sebagian besar paid, plus pending dan refunded).

Kamu akan memperhatikan bagaimana plan berubah sebelum dan sesudah ANALYZE.

Sebelum latihan, periksa definisi kolom, sampel data, dan bagaimana nilai tersebar di setiap kolom perf_sales. Generasi data butuh waktu, jadi eksekusi pertama mungkin terhenti beberapa detik.

① Pakai PRAGMA table_info(perf_sales); untuk melihat nama kolom, tipe, dan primary key.

② Pakai SELECT * FROM perf_sales LIMIT 5; untuk mengintip 5 baris pertama.

③ Hitung baris per nilai region untuk melihat bagaimana nilai tersebar (kardinalitas).

ANALYZE dan sqlite_stat1 — mengumpulkan statistik

Jalankan ANALYZE dan database menelusuri tabel dan indeks, mengumpulkan statistik, dan menyimpannya ke tabel internal bernama sqlite_stat1.

sqlite_stat1 mencatat fakta seperti "berapa baris, rata-rata, yang berkorespondensi dengan satu nilai indeks ini".

Menjalankan ANALYZE membangun statistik yang berlandaskan data aktual kamu, yang membantu optimizer memilih execution plan yang akurat.

Pakai ANALYZE sendiri untuk mencakup setiap tabel, atau ANALYZE table_name untuk menargetkan satu tabel spesifik.

-- Indeks dua kolom dengan kardinalitas berbeda (status=3 nilai / qty=20 nilai)
DROP INDEX IF EXISTS ix_status;
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_status ON perf_sales(status);
CREATE INDEX ix_qty    ON perf_sales(qty);

-- Kumpulkan statistik dan lihat apa yang disimpan (rata-rata baris per nilai)
ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1
WHERE tbl = 'perf_sales' ORDER BY idx;
--> ix_status: baris seperti 50000 16667 (sekitar 16667 baris per nilai)
--> ix_qty:    baris seperti 50000 2500 (sekitar 2500 baris per nilai)

Kamu akan benar-benar mengumpulkan statistik yang menangkap "berapa banyak setiap indeks bisa mempersempit" dan membaca hasilnya. Lakukan pembuatan indeks, pengumpulan statistik, dan verifikasi dalam satu eksekusi. (Jalankan dengan benar untuk memunculkan penjelasan.)

① Buang indeks yang bernama sama dengan DROP INDEX IF EXISTS, lalu pakai CREATE INDEX untuk membangun satu indeks pada emp_id dan satu lagi pada region.

② Jalankan ANALYZE; untuk mengumpulkan statistik.

③ Dari sqlite_stat1, tarik baris di mana tbl adalah perf_sales dan lihat kolom stat untuk setiap indeks (jumlah total baris ditambah rata-rata baris per nilai).

Selektivitas mengubah plan — kolom yang terbayar versus yang tidak

Selektivitas adalah estimasi "fraksi mana dari semua baris yang lolos setelah filter".

Semakin kecil fraksi itu, semakin tinggi selektivitasnya dan semakin terbayar indeksnya.

emp_id = 7 mempersempit ke sekitar 1.667 baris (sekitar 3% tabel), jadi selektivitasnya tinggi dan indeks layak dipakai.

Di sisi lain, status = 'paid' meninggalkan sekitar 40.000 baris (sekitar 80% tabel), jadi selektivitasnya rendah dan membaca setiap baris berurutan mengalahkan melompat lewat indeks satu baris pada satu waktu.

Setelah statistik tersedia, optimizer (query planner) membaca selektivitas dan membandingkan seberapa mahal plan indeks dibandingkan full scan.

Dengan indeks komposit atau beberapa kandidat yang bermain, ia mengandalkan statistik untuk lebih memilih "indeks yang mempersempit ke lebih sedikit baris".

Latihan berikutnya menunjukkan bahwa bahkan dengan tabel yang sama dan indeks yang sama, plan bisa berubah tergantung selektivitas kolom WHERE dan apakah kolom yang kamu pilih ada di indeks.

-- Indeks kolom selektivitas tinggi (amount: hampir unik) dan rendah (status: 3 nilai),
-- lalu setelah ANALYZE bandingkan plan untuk kueri berbentuk sama
DROP INDEX IF EXISTS ix_amount;
DROP INDEX IF EXISTS ix_status;
CREATE INDEX ix_amount ON perf_sales(amount);
CREATE INDEX ix_status ON perf_sales(status);
ANALYZE;

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount = 500000;
--> SEARCH perf_sales USING INDEX ix_amount (amount=?)

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'paid';
--> selektivitas rendah memungkinkan plan yang condong ke SCAN

Bandingkan "kondisi yang hampir tidak ada baris cocok" dengan "kondisi yang hampir setiap baris cocok", diungkapkan sebagai kueri yang memilih kolom yang tidak ada di indeks. Dengan COUNT(*), SQLite bisa menyelesaikan hitungan memakai indeks saja, jadi indeks dipakai tanpa peduli selektivitas. Untuk memperhatikan SCAN benar-benar menang pada filter selektivitas rendah, latihan ini memakai bentuk SELECT sale_id, amount — menarik `amount`, yang tidak ada di indeks.

① Buang indeks bernama sama dengan DROP INDEX IF EXISTS, bangun satu indeks pada emp_id dan satu lagi pada status, lalu jalankan ANALYZE; untuk mengumpulkan statistik.

② Tambahkan EXPLAIN QUERY PLAN dan periksa plan untuk kueri yang memilih sale_id dan amount untuk baris dengan emp_id = 7 (selektivitas tinggi: sekitar 1.667 baris / 3%).

③ Lalu tambahkan EXPLAIN QUERY PLAN dan periksa plan untuk bentuk sama dengan status = 'paid', dan baca bagaimana sisi emp_id dan sisi status terbagi antara SEARCH dan SCAN (selektivitas rendah: sekitar 40.000 baris / 80%).

Di konsol kursus ini, selektivitas rendah masih bisa mempertahankan INDEX SCAN

Di konsol browser ini (SQLite berjalan di memory), plan status = 'paid' langkah ③ mungkin tetap di SEARCH perf_sales USING INDEX ix_status (status=?). Inilah alasannya.

- Ia berjalan di memory: di database disk, jarak antara "random I/O lewat indeks" dan "SCAN berurutan" besar, itulah sebabnya selektivitas rendah lebih menguntungkan SCAN. Di memory, keduanya cepat dan jarak menyempit.

- Entri indeks diurutkan berdasarkan nilai: ix_status diurutkan berdasarkan status, jadi blok 'paid' dibaca sebagai rentang kontigu — tidak ada random access.

- Akses tabel berbasis rowid murah: SQLite bisa mengambil baris tabel langsung lewat rowid internalnya, jadi bahkan pembacaan tabel yang didorong indeks tetap relatif murah.

Perilaku peralihan bersih ke `SCAN` ketika selektivitas turun lebih mudah diamati di PostgreSQL atau Oracle, RDBMS dengan storage disk-backed nyata dan optimizer berbasis biaya penuh. Ambil dari latihan ini bahwa ada prinsip yang berperan: bahkan dengan indeks yang identik, kombinasi selektivitas dan kolom yang dipilih bisa membalik pilihan optimizer. Ambang batas pasti antara SCAN dan SEARCH bergerak dengan engine, kehadiran disk, dan presisi statistik.

Optimasi berbasis biaya dan bind variable — internal database lain

Mesin yang kamu lihat — "estimasi selektivitas dari statistik, lalu pilih plan paling murah" — disebut cost-based optimizer (CBO).

Di database besar seperti Oracle dan SQL Server, mesin ini punya lebih banyak lapisan di dalam.

Salah satunya adalah parsing (menganalisis pernyataan SQL): menganalisis sebuah pernyataan SQL untuk pertama kalinya dan membangun plan adalah hard parse, sedangkan memakai ulang plan yang sudah dianalisis sebelumnya adalah soft parse.

Yang lain adalah bind variable (bind variable: mekanisme yang tidak menaruh nilai langsung di SQL tapi meninggalkan slot seperti ? dan melewatkan nilai saat run time), bersama dengan bind peeking — mengintip nilai yang diberikan ke optimizer dan memilih plan berdasarkan seberapa miring nilai itu.

Ini adalah mekanisme internal mesin eksekusi database besar. Mengetahui konsepnya membuat diskusi tuning jauh lebih mudah diikuti.

Di konsol browser kursus ini, kamu sebenarnya bisa mengamati bagaimana plan bereaksi terhadap selektivitas (bagian sebelumnya), tapi peralihan antara hard dan soft parse dan perilaku bind peeking tidak bisa direproduksi di sini seperti yang dijelaskan callout di bawah, jadi kamu akan bekerja melewati konsep dengan diagram dan sampel kode read-only.

Hard / soft parse dan bind peeking adalah internal bergaya Oracle

Peralihan antara hard dan soft parse dan bind peeking (memilih ulang plan berdasarkan seberapa miring nilai yang dilewatkan) tinggal di dalam mesin eksekusi database besar seperti Oracle dan SQL Server.

Konsol browser kursus ini tidak mengekspos view yang diperlukan untuk mengamati keadaan internal ini (pikirkan V$SQL Oracle), jadi tidak ada cara untuk mendemonstrasikannya di konsol.

Di sini kamu akan mendapat konsepnya dari diagram dan sampel kode read-only di bawah.

Di sisi lain, pengumpulan statistik dengan ANALYZE dan cara plan bereaksi terhadap selektivitas, keduanya dibahas sebelumnya, bisa diamati secara nyata.

Jantung optimasi berbasis biaya — "statistik -> selektivitas -> pilihan plan" — adalah sesuatu yang bisa kamu konfirmasi langsung di konsol kursus ini, jadi kuasai itu terlebih dulu.

-- Gagasan bagaimana ini terlihat di Oracle (read-only; jangan jalankan di konsol kursus ini)
-- Bind variable melewatkan nilai nanti (:s diisi saat run time)
-- SELECT * FROM perf_sales WHERE status = :s;
--
-- String SQL yang identik memakai ulang plan = soft parse
-- String yang sedikit berbeda pun membangun ulang = hard parse
-- Di Oracle kamu akan memeriksa parsing lewat V$SQL.SQL_TEXT, tapi tidak di sini

-- Apa yang bisa kamu amati di konsol kursus ini:
-- setelah statistik dikumpulkan, estimasi selektivitas berlandaskan data nyata
ANALYZE;
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'pending';
--> SEARCH perf_sales USING INDEX ... (status=?)

Dari optimasi berbasis biaya, bagian yang konsol kursus ini benar-benar memungkinkan kamu perhatikan adalah "mengumpulkan statistik menggeser estimasi", dan kamu akan memverifikasinya pada kolom product (200 nilai, selektivitas tinggi). Lakukan pembuatan indeks dan pemeriksaan plan sebelum dan sesudah mengumpulkan statistik dalam satu eksekusi.

① Buang indeks bernama sama dengan DROP INDEX IF EXISTS, lalu buat indeks pada kolom product.

② Tambahkan EXPLAIN QUERY PLAN dan periksa plan untuk kueri yang menghitung baris di mana product adalah 'P050', tanpa statistik.

③ Jalankan ANALYZE; untuk mengumpulkan statistik, lalu jalankan ulang EXPLAIN QUERY PLAN kueri yang sama. Karena product punya 200 nilai dan selektivitas tinggi, plan harus tetap memakai indeks setelah ANALYZE juga.