KAL
Menu

Intuisi Performa Join

Mengapa indeks menentukan kecepatan join dan cara membaca biaya penggabungan.

Daftar isi

Kecepatan sebuah join tidak ditentukan oleh panjang query, melainkan oleh cara server mengakses baris dari kedua tabel. MySQL mengeksekusi join dengan nested loop, yaitu mencari pasangan untuk setiap baris satu per satu, sehingga pencarian yang berindeks dan yang tidak berindeks berbeda jauh biayanya. Topik ini menyiapkan dua tabel latihan berukuran kecil, membaca rencana eksekusi dengan EXPLAIN sebelum dan sesudah indeks dibuat, lalu menunjukkan peran tabel penggerak pada pembacaan rencana. Angka pada keluaran merupakan perkiraan optimizer yang stabil setelah statistik diperbarui dengan ANALYZE TABLE.

Dua tabel latihan#

Latihan memakai dua tabel, yaitu anggota dengan 500 baris dan peminjaman dengan 2000 baris. Kolom peminjaman.anggota_id berisi nilai yang merata di seluruh rentang 1 sampai 500, sehingga setiap anggota memiliki tepat empat peminjaman. Berkas berikut membuat ulang basis data uji_join dari nol, mengisi kedua tabel, lalu menjalankan ANALYZE TABLE untuk setiap tabelnya.

sql skema-uji-join.sql 
-- tidak-dijalankan
-- Skema basis data latihan "uji_join" untuk mengamati pengaruh indeks pada join.
-- Menjalankan ulang berkas ini selalu menghasilkan isi yang sama.

DROP DATABASE IF EXISTS uji_join;
CREATE DATABASE uji_join;
USE uji_join;

SET SESSION cte_max_recursion_depth = 1000000;

CREATE TABLE anggota (
  anggota_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nama VARCHAR(30) NOT NULL
);

INSERT INTO anggota (nama)
WITH RECURSIVE urut AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM urut WHERE n < 500
)
SELECT CONCAT('Anggota ', n) FROM urut;

CREATE TABLE peminjaman (
  peminjaman_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  anggota_id INT UNSIGNED NOT NULL
);

INSERT INTO peminjaman (anggota_id)
WITH RECURSIVE urut AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM urut WHERE n < 2000
)
SELECT (n MOD 500) + 1 FROM urut;

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

Keluaran ANALYZE TABLE memuat satu baris status OK untuk setiap tabel. Pembaruan statistik ini penting karena optimizer mengambil keputusan dari statistik indeks, dan statistik yang basi membuat angka pada EXPLAIN tidak stabil. Ukuran tabel sengaja dibuat kecil agar seluruh angka pada keluaran mudah dibandingkan.

Rencana eksekusi tanpa indeks#

Perintah EXPLAIN ditulis di depan SELECT dan tidak menjalankan query, tetapi menampilkan rencana eksekusinya. Query yang dipakai mencari peminjaman milik satu anggota lalu menampilkan namanya. Kolom yang paling menentukan adalah type, key, dan rows, yaitu cara akses, indeks yang dipilih, dan perkiraan jumlah baris yang diperiksa.

bash 
# rencana eksekusi sebelum indeks dibuat
mysql -u root uji_join -e "EXPLAIN SELECT a.nama FROM peminjaman p JOIN anggota a ON a.anggota_id = p.anggota_id WHERE p.anggota_id = 42\G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
     filtered: 10.00
        Extra: Using where

Baris pertama menyatakan tabel anggota diakses dengan type: const, yaitu pembacaan satu baris melalui primary key karena kondisi p.anggota_id = 42 menunjuk tepat satu nilai. Baris kedua menyatakan type: ALL pada tabel peminjaman, yang berarti pemindaian seluruh tabel. Kolom key berisi NULL karena tidak ada indeks yang dapat dipilih, dan perkiraan rows mencapai 2000. Inilah biaya dasar join tanpa indeks, yaitu memeriksa setiap baris satu per satu untuk mencari pasangannya.

Indeks pada kolom join#

Kolom anggota_id pada peminjaman adalah kolom yang dicocokkan pada ON, sehingga indeks yang tepat dibuat di sana. Perintah berikut membuat indeks lalu memperbarui statistiknya segera setelah itu.

bash 
# membuat indeks pada kolom join lalu memperbarui statistik
mysql -t -u root uji_join -e "ALTER TABLE peminjaman ADD KEY idx_anggota (anggota_id); ANALYZE TABLE peminjaman;"
text Keluaran 
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| uji_join.peminjaman | analyze | status   | OK       |
+---------------------+---------+----------+----------+

Rencana eksekusi yang sama diperiksa ulang setelah indeks siap.

bash 
# rencana eksekusi setelah indeks idx_anggota ada
mysql -u root uji_join -e "EXPLAIN SELECT a.nama FROM peminjaman p JOIN anggota a ON a.anggota_id = p.anggota_id WHERE p.anggota_id = 42\G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
   partitions: NULL
         type: ref
possible_keys: idx_anggota
          key: idx_anggota
      key_len: 4
          ref: const
         rows: 4
     filtered: 100.00
        Extra: Using index

Baris kedua kini memakai type: ref dengan key: idx_anggota, dan perkiraan rows tinggal 4. Angka itu sesuai isi data, karena 2000 peminjaman terbagi rata ke 500 anggota. Kolom Extra berisi Using index, yang berarti seluruh kolom yang dibutuhkan dari peminjaman sudah tersedia pada indeks sehingga tabelnya tidak dibaca sama sekali. Perbedaannya nyata, yaitu dari perkiraan 2000 baris yang diperiksa menjadi 4 baris. Secara kasar, pemindaian penuh berbiaya O(n)O(n) untuk setiap pencarian, sedangkan pencarian melalui struktur B-tree indeks berbiaya O(log⁡n)O(\log n).

Tabel penggerak dan nested loop#

EXPLAIN menampilkan rencana dari atas ke bawah, dan urutan barisnya menyatakan urutan penggabungan. Tabel yang dibaca pertama disebut tabel penggerak, sedangkan tabel berikutnya dicari berulang kali untuk setiap baris penggerak. Query tanpa penyaringan berikut memperlihatkan urutan yang berbeda karena tidak ada kondisi awal yang menunjuk satu baris.

bash 
# rencana eksekusi tanpa WHERE pada keadaan berindeks
mysql -u root uji_join -e "EXPLAIN SELECT a.nama FROM peminjaman p JOIN anggota a ON a.anggota_id = p.anggota_id\G"
text Keluaran 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 500
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
   partitions: NULL
         type: ref
possible_keys: idx_anggota
          key: idx_anggota
      key_len: 4
          ref: uji_join.a.anggota_id
         rows: 4
     filtered: 100.00
        Extra: Using index

Tanpa WHERE, optimizer memilih anggota sebagai tabel penggerak dan memindai 500 barisnya. Setiap baris anggota kemudian dicocokkan ke peminjaman melalui indeks dengan perkiraan 4 baris per anggota. Total perkiraan pencariannya menjadi 500×4=2000500 \times 4 = 2000, persis jumlah baris peminjaman. Memahami pola penggerak dan pencarian ini adalah kunci membaca rencana join yang lebih panjang, sebab setiap tabel tambahan menambah satu lapis perulangan.

Kesalahan umum#

Kesalahan pertama adalah menganggap angka pada kolom rows sebagai hitungan pasti. Nilai itu adalah perkiraan dari statistik, dan statistik yang belum diperbarui setelah muatan data besar dapat menyimpang dari kenyataan. Jalankan ANALYZE TABLE terlebih dahulu sebelum menilai sebuah rencana, lalu baca angkanya sebagai skala, bukan sebagai hitungan teliti.

Kesalahan kedua adalah membuat indeks pada kolom yang tidak dipakai ON maupun WHERE. Indeks semacam itu menambah biaya penulisan tanpa pernah dipilih optimizer. Kolom yang tepat adalah yang muncul pada kondisi pencocokan, sebagaimana anggota_id pada latihan ini. Kesalahan ketiga adalah menyimpulkan kecepatan dari panjang teks query, padahal biaya join ditentukan oleh rencana eksekusinya, bukan oleh jumlah kata yang ditulis.

Ringkasan#

  • MySQL mengeksekusi join sebagai nested loop, sehingga cara mencari pasangan baris menentukan biayanya.
  • EXPLAIN menampilkan rencana eksekusi, dan kolom type, key, serta rows adalah kolom yang paling menentukan.
  • Indeks pada kolom join mengubah akses ALL menjadi ref dan menurunkan perkiraan baris dari 2000 menjadi 4 pada latihan ini.
  • ANALYZE TABLE memperbarui statistik agar angka EXPLAIN stabil, sedangkan kolom rows tetap merupakan perkiraan.

Referensi#