KAL
Menu

Proyek: Ronde Query dan Penalaan

Mengukur, membaca EXPLAIN, membuat indeks, dan memverifikasi perbaikannya.

Daftar isi

Topik ini menalakan basis data laporan_sewa melalui tiga ronde perbaikan, satu ronde untuk setiap bentuk beban kerjanya. Basis data laporan itu sudah dibuat dan diisi lengkap sebelumnya. Setiap ronde mengikuti disiplin yang sama, yaitu membaca rencana eksekusi dengan EXPLAIN, mendiagnosis penyebabnya, membuat indeks yang tepat, lalu memeriksa ulang rencananya. Ukuran keberhasilannya adalah angka pada rencana eksekusi, yaitu type, key, rows, dan Extra, bukan stopwatch, karena durasi pada server latihan yang sepi berubah-ubah antar pengukuran sedangkan angka rencananya tetap. Setelah topik ini, alur kerja penalaan dari pengukuran sampai verifikasi dapat dijalankan utuh pada kasus nyata.

Persiapan#

Basis data laporan dibangun ulang dari nol agar seluruh ronde dapat diulang dengan hasil yang sama dan topik ini dapat dijalankan sendiri tanpa bergantung pada keadaan yang tertinggal. Berkas yang dipakai tetap skema-laporan.sql, yaitu membuat tabel sewa tanpa satu pun indeks secondary.

sql skema-laporan.sql 
-- tidak-dijalankan
-- Skema basis data laporan "laporan_sewa" untuk proyek jalur MySQL.
-- Berkas ini hanya membangun kerangkanya, sedangkan pengisian datanya
-- dikerjakan pada blok tersendiri agar pernyataan JOIN-nya terbaca.

DROP DATABASE IF EXISTS laporan_sewa;
CREATE DATABASE laporan_sewa;
USE laporan_sewa;

CREATE TABLE sewa (
  sewa_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tanggal_sewa DATETIME NOT NULL,
  tanggal_kembali DATETIME NULL,
  pelanggan VARCHAR(91) NOT NULL,
  film VARCHAR(128) NOT NULL,
  kategori VARCHAR(25) NOT NULL,
  staf TINYINT UNSIGNED NOT NULL,
  jumlah DECIMAL(5,2) NOT NULL,
  tanggal_bayar DATETIME NOT NULL
) STATS_SAMPLE_PAGES=1024;
bash 
# membangun ulang basis data laporan dan mengisinya dari sakila
mysql -u root < skema-laporan.sql
mysql -u root laporan_sewa <<'SQL'
INSERT INTO sewa (tanggal_sewa, tanggal_kembali, pelanggan, film, kategori, staf, jumlah, tanggal_bayar)
SELECT
  r.rental_date,
  r.return_date,
  CONCAT(c.first_name, ' ', c.last_name),
  f.title,
  ca.name,
  r.staff_id,
  p.amount,
  p.payment_date
FROM sakila.rental r
JOIN sakila.payment p ON p.rental_id = r.rental_id
JOIN sakila.customer c ON c.customer_id = r.customer_id
JOIN sakila.inventory i ON i.inventory_id = r.inventory_id
JOIN sakila.film f ON f.film_id = i.film_id
JOIN sakila.film_category fc ON fc.film_id = f.film_id
JOIN sakila.category ca ON ca.category_id = fc.category_id;
SQL
bash 
# memperbarui statistik dan memastikan isi tabel lengkap
mysql -u root laporan_sewa -t -e "ANALYZE TABLE sewa; SELECT COUNT(*) AS jumlah_baris FROM sewa;"
text Keluaran 
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| laporan_sewa.sewa | analyze | status   | OK       |
+-------------------+---------+----------+----------+
+--------------+
| jumlah_baris |
+--------------+
|        16044 |
+--------------+

Persiapan selesai ketika jumlah baris kembali 16044. Seluruh indeks pada ronde berikutnya dibuat di atas keadaan yang sama persis, sehingga perbandingan sebelum dan sesudahnya adil.

Ronde 1: pendapatan satu staf dalam satu bulan#

Query pertama menjawab kebutuhan laporan bulanan, yaitu jumlah sewa dan pendapatan milik staf dengan id 1 selama Juni 2005. Kondisi staf = 1 adalah kesetaraan, sedangkan rentang tanggal pada tanggal_sewa adalah jangkauan, sehingga bentuk kondisinya adalah kesetaraan diikuti jangkauan.

bash 
# rencana eksekusi laporan bulanan sebelum ada indeks
mysql -u root laporan_sewa -e "EXPLAIN SELECT COUNT(*) AS jumlah_sewa, SUM(jumlah) AS pendapatan FROM sewa WHERE staf = 1 AND tanggal_sewa >= '2005-06-01' AND tanggal_sewa < '2005-07-01' \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sewa
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16044
     filtered: 1.11
        Extra: Using where

Rencananya belum berbantu indeks, yaitu type: ALL dengan perkiraan 16044 baris diperiksa untuk sebuah laporan yang jawabannya hanya satu baris. Kolom filtered sebesar 1,11 menegaskan kelangkaannya, karena hanya sekitar satu persen baris yang lolos kondisi. Nilai laporan yang sebenarnya diperiksa dulu sebelum indeks dibuat, supaya angkanya menjadi pembanding pada pemeriksaan hasil di akhir ronde.

bash 
# hasil laporan bulanan yang menjadi acuan
mysql -u root laporan_sewa -t -e "SELECT COUNT(*) AS jumlah_sewa, SUM(jumlah) AS pendapatan FROM sewa WHERE staf = 1 AND tanggal_sewa >= '2005-06-01' AND tanggal_sewa < '2005-07-01';"
text Keluaran 
+-------------+------------+
| jumlah_sewa | pendapatan |
+-------------+------------+
|        1163 |    4832.37 |
+-------------+------------+

Indeks yang tepat untuk kondisi kesetaraan diikuti jangkauan adalah indeks komposit yang menyusun kolom kesetaraan lebih dahulu, sesuai aturan prefiks kiri. Indeks idx_staf_tanggal pada (staf, tanggal_sewa) membuat pencarian menuju langsung ke kelompok staf = 1, lalu berjalan pada jangkauan tanggal di dalam kelompok itu.

bash 
# membuat indeks komposit lalu memperbarui statistik
mysql -t -u root laporan_sewa -e "CREATE INDEX idx_staf_tanggal ON sewa (staf, tanggal_sewa); ANALYZE TABLE sewa;"
text Keluaran 
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| laporan_sewa.sewa | analyze | status   | OK       |
+-------------------+---------+----------+----------+
bash 
# rencana eksekusi yang sama setelah indeks dibuat
mysql -u root laporan_sewa -e "EXPLAIN SELECT COUNT(*) AS jumlah_sewa, SUM(jumlah) AS pendapatan FROM sewa WHERE staf = 1 AND tanggal_sewa >= '2005-06-01' AND tanggal_sewa < '2005-07-01' \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sewa
   partitions: NULL
         type: range
possible_keys: idx_staf_tanggal
          key: idx_staf_tanggal
      key_len: 6
          ref: NULL
         rows: 1163
     filtered: 100.00
        Extra: Using index condition

Rencananya berubah dari ALL menjadi range melalui idx_staf_tanggal, dan perkiraan baris yang diperiksa turun dari 16044 menjadi 1163. Angka 1163 bukan kebetulan, melainkan panjang jangkauan yang sebenarnya di dalam indeks, dan nilai itu sama dengan hasil COUNT(*) pada acuan di atas. Nilai key_len sebesar 6 menunjukkan kedua kolom dipakai, yaitu 1 byte untuk TINYINT dan 5 byte untuk bagian tanggal DATETIME. Hasil query akhir ronde ini sudah benar sejak awal, sehingga yang berubah hanya biaya untuk memperolehnya.

Ronde 2: riwayat sewa satu pelanggan#

Query kedua menampilkan lima sewa terlama milik satu pelanggan terurut dari yang paling awal. Kondisinya kesetaraan pada pelanggan, tetapi tambahan ORDER BY tanggal_sewa membuat rencananya berbeda.

bash 
# rencana eksekusi riwayat pelanggan sebelum ada indeks
mysql -u root laporan_sewa -e "EXPLAIN SELECT sewa_id, tanggal_sewa, film, jumlah FROM sewa WHERE pelanggan = 'MARY SMITH' ORDER BY tanggal_sewa LIMIT 5 \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sewa
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16044
     filtered: 10.00
        Extra: Using where; Using filesort

Nilai Extra kini memuat dua keterangan sekaligus, yaitu Using where untuk penyaringan dan Using filesort untuk pengurutan tambahan di luar urutan indeks. Pemindaian seluruh tabel hanya untuk mengambil baris milik satu pelanggan jelas boros, tetapi filesort itulah yang menjadi sasaran ronde ini, karena biaya pengurutannya tumbuh mengikuti banyaknya baris yang lolos penyaringan. Isi hasilnya diperlihatkan sebagai acuan.

bash 
# lima sewa terlama milik pelanggan yang menjadi acuan
mysql -u root laporan_sewa -t -e "SELECT sewa_id, tanggal_sewa, film, jumlah FROM sewa WHERE pelanggan = 'MARY SMITH' ORDER BY tanggal_sewa LIMIT 5;"
text Keluaran 
+---------+---------------------+-------------------+--------+
| sewa_id | tanggal_sewa        | film              | jumlah |
+---------+---------------------+-------------------+--------+
|    3886 | 2005-05-25 11:30:37 | PATIENT SISTER    |   2.99 |
|   15103 | 2005-05-28 10:35:23 | TALENTED HOMICIDE |   0.99 |
|    3835 | 2005-06-15 00:54:12 | MUSKETEERS WAIT   |   5.99 |
|    3409 | 2005-06-15 18:02:53 | DETECTIVE VISION  |   0.99 |
|    4408 | 2005-06-15 21:08:46 | FERRIS MOTHER     |   9.99 |
+---------+---------------------+-------------------+--------+

Indeks komposit (pelanggan, tanggal_sewa) menyelesaikan kedua masalah sekaligus, yaitu pencarian kesetaraan pada kolom pertama dan urutan bawaan pada kolom kedua. Baris yang dibaca lewat indeks ini sudah terurut menurut tanggal, sehingga server tidak perlu mengurutkan ulang apa pun.

bash 
# membuat indeks pelanggan dan tanggal lalu memperbarui statistik
mysql -t -u root laporan_sewa -e "CREATE INDEX idx_pelanggan_tanggal ON sewa (pelanggan, tanggal_sewa); ANALYZE TABLE sewa;"
text Keluaran 
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| laporan_sewa.sewa | analyze | status   | OK       |
+-------------------+---------+----------+----------+
bash 
# rencana eksekusi yang sama setelah indeks dibuat
mysql -u root laporan_sewa -e "EXPLAIN SELECT sewa_id, tanggal_sewa, film, jumlah FROM sewa WHERE pelanggan = 'MARY SMITH' ORDER BY tanggal_sewa LIMIT 5 \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sewa
   partitions: NULL
         type: ref
possible_keys: idx_pelanggan_tanggal
          key: idx_pelanggan_tanggal
      key_len: 366
          ref: const
         rows: 32
     filtered: 100.00
        Extra: NULL

Rencananya kini type: ref dengan perkiraan 32 baris, persis jumlah sewa milik pelanggan tersebut, dan Extra kosong karena kedua keterangan bermasalahnya hilang. Filesort tidak ada lagi karena urutan datang dari indeksnya sendiri. Nilai key_len sebesar 366 berasal dari kolom pelanggan, yaitu VARCHAR(91) dengan charset utf8mb4 yang menempati sampai empat byte per karakter ditambah dua byte pencatat panjangnya. Kolom tanggal_sewa tidak ikut terhitung karena kondisi yang dipakai hanya kesetaraan pada prefiks pertama.

Ronde 3: pendapatan per kategori#

Query ketiga adalah laporan yang menyentuh seluruh tabel, yaitu jumlah sewa dan pendapatan untuk setiap kategori. Tidak ada kondisi pencarian, sehingga pendekatan indeks pada dua ronde sebelumnya tidak berlaku lagi.

bash 
# rencana eksekusi laporan kategori sebelum ada indeks
mysql -u root laporan_sewa -e "EXPLAIN SELECT kategori, COUNT(*) AS jumlah_sewa, SUM(jumlah) AS pendapatan FROM sewa GROUP BY kategori ORDER BY kategori \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sewa
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16044
     filtered: 100.00
        Extra: Using temporary; Using filesort

Untuk query semacam ini, pemindaian seluruh tabel memang tak terhindarkan karena semua baris terpakai, tetapi ada dua biaya tambahan yang masih bisa dihapus. Keterangan Using temporary berarti hasil pengelompokannya dirakit pada tabel sementara, sedangkan Using filesort mengurutkan kelompoknya setelah terkumpul. Keluaran laporan ini dipertahankan utuh sebagai acuan karena menjadi jawaban akhir proyek.

bash 
# laporan pendapatan per kategori yang menjadi acuan
mysql -u root laporan_sewa -t -e "SELECT kategori, COUNT(*) AS jumlah_sewa, SUM(jumlah) AS pendapatan FROM sewa GROUP BY kategori ORDER BY kategori;"
text Keluaran 
+-------------+-------------+------------+
| kategori    | jumlah_sewa | pendapatan |
+-------------+-------------+------------+
| Action      |        1112 |    4375.85 |
| Animation   |        1166 |    4656.30 |
| Children    |         945 |    3655.55 |
| Classics    |         939 |    3639.59 |
| Comedy      |         941 |    4383.58 |
| Documentary |        1050 |    4217.52 |
| Drama       |        1060 |    4587.39 |
| Family      |        1096 |    4226.07 |
| Foreign     |        1033 |    4270.67 |
| Games       |         969 |    4281.33 |
| Horror      |         846 |    3722.54 |
| Music       |         830 |    3417.72 |
| New         |         940 |    4351.62 |
| Sci-Fi      |        1101 |    4756.98 |
| Sports      |        1179 |    5314.21 |
| Travel      |         837 |    3549.64 |
+-------------+-------------+------------+

Indeks yang tepat adalah indeks covering pada (kategori, jumlah), yaitu indeks yang memuat seluruh kolom yang dibutuhkan query. Dengan indeks itu, pengelompokan mengikuti urutan indeks dan kedua perhitungan agregatnya membaca nilai langsung dari isi indeks, sehingga tabel sementara dan pengurutan tidak diperlukan lagi.

bash 
# membuat indeks covering lalu memperbarui statistik
mysql -t -u root laporan_sewa -e "CREATE INDEX idx_kategori_jumlah ON sewa (kategori, jumlah); ANALYZE TABLE sewa;"
text Keluaran 
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| laporan_sewa.sewa | analyze | status   | OK       |
+-------------------+---------+----------+----------+
bash 
# rencana eksekusi yang sama setelah indeks dibuat
mysql -u root laporan_sewa -e "EXPLAIN SELECT kategori, COUNT(*) AS jumlah_sewa, SUM(jumlah) AS pendapatan FROM sewa GROUP BY kategori ORDER BY kategori \G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sewa
   partitions: NULL
         type: index
possible_keys: idx_kategori_jumlah
          key: idx_kategori_jumlah
      key_len: 105
          ref: NULL
         rows: 16044
     filtered: 100.00
        Extra: Using index

Perbaikan pada ronde ini berbeda bentuknya dari dua ronde sebelumnya. Kolom rows tetap 16044 karena seluruh baris memang terpakai, tetapi type berubah menjadi index dengan kunci idx_kategori_jumlah. Nilai Extra kini hanya Using index, yaitu tanda bahwa jawaban dirakit dari isi indeks saja tanpa menyentuh tabel. Pengelompokan dan pengurutan keduanya hilang dari rencana karena indeksnya sudah terurut menurut kategori. Ronde ini sekaligus menunjukkan bahwa pembacaan rencana harus memperhatikan seluruh kolomnya, bukan kolom rows seorang diri.

Kesalahan umum#

Kesalahan pertama adalah menilai perbaikan dari durasi yang diukur sekali jalan. Durasi pada server latihan yang sepi dipengaruhi cache dan beban sesaat, sehingga bisa turun hanya karena kebetulan. Rencana eksekusi yang berubah, yaitu type, key, rows, dan Extra, adalah bukti yang stabil, sedangkan durasi hanyalah penunjang. Kesalahan kedua adalah lupa menjalankan ANALYZE TABLE setelah membuat indeks. Statistik yang basi membuat perkiraan pada EXPLAIN menyimpang, dan pada kasus yang lebih besar dapat membuat optimizer menolak indeks yang baru dibuat. Kesalahan ketiga adalah memasang indeks lalu menganggap pekerjaan selesai tanpa memeriksa ulang rencananya. Indeks yang salah urutan kolomnya tetap muncul pada possible_keys, tetapi tetap tidak dipilih, sehingga satu-satunya bukti pemakaiannya adalah kolom key pada rencana sesudahnya.

Ringkasan#

  • Penalaan berjalan dalam ronde dengan pola yang tetap, yaitu membaca EXPLAIN, mendiagnosis, membuat indeks, memperbarui statistik, lalu memverifikasi ulang rencananya.
  • Kondisi kesetaraan diikuti jangkauan ditangani indeks komposit dengan kolom kesetaraan lebih dahulu, dan perkiraan barisnya turun dari 16044 menjadi 1163 pada ronde pertama.
  • Indeks komposit yang menyertakan kolom pengurutan menghilangkan filesort, sedangkan indeks covering pada kolom agregasi menghilangkan tabel sementara dan pengurutan sekaligus.
  • Bukti perbaikan ada pada type, key, rows, dan Extra, karena durasi yang terukur sekali jalan bukan bukti yang stabil.

Referensi#