INDEX pada FOREIGN KEY

Foreign key yang kita kenal di section table-design menjaga integritas referensial — tapi integritas bukan satu-satunya urusan server ketika constraint itu terpasang. Setiap join anak-ke-induk perlu menemukan baris anak berdasarkan kuncinya, dan setiap DELETE pada tabel induk memaksa server memeriksa apakah masih ada anak yang menggantung. Kedua pekerjaan itu butuh jalur pencarian cepat pada kolom foreign key, dan InnoDB mengambil jalan praktis: bila belum ada index yang cocok, server membuatnya diam-diam saat tabel anak diciptakan. Pada chapter ini kita membuktikan index otomatis tersebut lewat SHOW INDEX, melihat perannya di rencana join, lalu menguji langsung apa yang terjadi ketika index itu hendak dihapus — hasil uji nyata di MySQL 8.4: server menolak keras.

Menyiapkan Tabel Induk dan Anak #

Dua tabel berperan sebagai induk dan anak: departemen sebagai induk, pegawai sebagai anak yang merujuknya:

sql
DROP DATABASE IF EXISTS belajar_index;
CREATE DATABASE belajar_index;
USE belajar_index;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

sql
CREATE TABLE departemen (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL
);

INSERT INTO departemen (nama) VALUES
  ('Keuangan'),
  ('Pemasaran'),
  ('Teknologi');

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

sql
CREATE TABLE pegawai (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,
  departemen_id INT NOT NULL,
  CONSTRAINT fk_pegawai_departemen
    FOREIGN KEY (departemen_id) REFERENCES departemen(id)
);

Output yang Dihasilkan:

text

Query OK, 0 rows affected (0.00 sec)

Definisi pegawai tidak memuat satu pun klausa INDEX atau KEY untuk kolom departemen_id — kita sengaja tidak membuat jalur pencarian apa pun untuk kolom itu. Instruksi INSERT untuk mengisi tabel baru dijalankan setelah kita membuktikan apa yang terjadi pada struktur index tabel yang baru lahir ini.

InnoDB Membuat Index FK Secara Otomatis #

Mari kita inspeksi tabel anak yang bahkan belum berisi satu baris data:

sql
SHOW INDEX FROM pegawai;

Output yang Dihasilkan:

text
+---------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name              | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| pegawai |          0 | PRIMARY               |            1 | id            | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| pegawai |          1 | fk_pegawai_departemen |            1 | departemen_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

Dua index terdaftar. Yang pertama PRIMARY — kita tahu sudah menjadi bawaan kolom primary key. Yang kedua, fk_pegawai_departemen, hadir tanpa pernah kita minta: kolom departemen_id terindeks, Non_unique = 1 karena satu departemen boleh dimiliki banyak pegawai, dan namanya persis mengikuti nama constraint fk_pegawai_departemen yang kita tulis di CREATE TABLE. Inilah perilaku yang dijanjikan InnoDB: pada saat tabel anak diciptakan, bila kolom foreign key belum tercakup oleh index yang ada, server otomatis membangun index baru dan menamainya sama dengan constraint-nya.

Index otomatis ini bukan hiasan — dua pekerjaan rutin bergantung padanya. Pertama, join yang menyambungkan anak ke induk memakainya sebagai jalur lompatan; kita lihat buktinya di EXPLAIN sebentar lagi. Kedua, setiap kali baris induk hendak dihapus atau kuncinya diubah, server harus mencari anak-anak yang merujuknya untuk memutuskan boleh-tidaknya operasi itu — tanpa index pada kolom FK, pemeriksaan ini menjadi full table scan pada tabel anak untuk satu statement induk.

sql
INSERT INTO pegawai (nama, departemen_id) VALUES
  ('Budi',  1), ('Siti',  2), ('Andi',  3), ('Rina',  1), ('Dewi',  2),
  ('Tono',  1), ('Sari',  3), ('Joko',  2), ('Nurul', 1), ('Rizky', 3);

Output yang Dihasilkan:

text

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Index FK Bekerja di Depan Mata: Join Anak ke Induk #

Query tipikal aplikasi — tampilkan pegawai dari satu departemen — menggabungkan kedua tabel. Rencana eksekusinya memperlihatkan pembagian peran index dengan jelas:

sql
EXPLAIN SELECT d.nama AS departemen, p.nama AS pegawai
FROM departemen d
JOIN pegawai p ON p.departemen_id = d.id
WHERE d.nama = 'Keuangan';

Output yang Dihasilkan:

text
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys         | key                   | key_len | ref                | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY               | NULL                  | NULL    | NULL               |    3 |    33.33 | Using where |
|  1 | SIMPLE      | p     | NULL       | ref  | fk_pegawai_departemen | fk_pegawai_departemen | 4       | belajar_index.d.id |    3 |   100.00 | NULL        |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+--------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Rencana dua baris itu dibaca dari atas ke bawah. Baris pertama: tabel d diakses type = ALL — hanya tiga baris departemen, satu kali baca penuh tidak masalah. Baris kedua adalah bintang utamanya: tabel p diakses type = ref dengan key = fk_pegawai_departemen — index otomatis FK. Tanpa index ini, setiap baris d yang lolos saringan akan memicu pembacaan seluruh tabel pegawai untuk mencocokkan departemen_id; dengan index, kecocokan ditemukan lewat lompatan langsung. Pola berpasangan tabel kecil dibaca penuh, tabel besar dilompati lewat index inilah bentuk kerja join yang sehat. Hasil query-nya sendiri:

sql
SELECT d.nama AS departemen, p.nama AS pegawai
FROM departemen d
JOIN pegawai p ON p.departemen_id = d.id
WHERE d.nama = 'Keuangan'
ORDER BY p.nama;

Output yang Dihasilkan:

text
+------------+---------+
| departemen | pegawai |
+------------+---------+
| Keuangan   | Budi    |
| Keuangan   | Nurul   |
| Keuangan   | Rina    |
| Keuangan   | Tono    |
+------------+---------+
4 rows in set (0.00 sec)

Bila Kita Sudah Punya Index Sendiri #

Aturan otomatis InnoDB punya sisi elegan: server tidak memaksakan index baru bila sudah ada yang cocok. Kita buktikan dengan tabel anak kedua yang mendeklarasikan index sendiri sebelum constraint-nya:

sql
CREATE TABLE pegawai2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,
  departemen_id INT NOT NULL,
  KEY idx_departemen (departemen_id),
  CONSTRAINT fk_p2_departemen
    FOREIGN KEY (departemen_id) REFERENCES departemen(id)
);

Output yang Dihasilkan:

text

Query OK, 0 rows affected (0.00 sec)

sql
SHOW INDEX FROM pegawai2;

Output yang Dihasilkan:

text
+----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| pegawai2 |          0 | PRIMARY        |            1 | id            | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| pegawai2 |          1 | idx_departemen |            1 | departemen_id | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

Hanya dua index tercatat: PRIMARY dan idx_departemen — buatan tangan kita sendiri. Constraint fk_p2_departemen tetap aktif menjaga integritas, tetapi InnoDB menggunakannya tanpa membangun apa pun karena idx_departemen sudah mencakup kolom departemen_id di posisi terdepannya. Pola ini berguna saat kita ingin kendali penuh atas penamaan: index FK otomatis mewarisi nama constraint, sedangkan index buatan sendiri bisa mengikuti konvensi idx_ yang seragam di seluruh skema.

Menghapus Index yang Dipakai Foreign Key #

Sekarang eksperimen kunci chapter ini. Index idx_departemen pada pegawai2 tidak lagi kita butuhkan — bisakah dijatuhkan begitu saja?

sql
-- @error
DROP INDEX idx_departemen ON pegawai2;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

ERROR 1553 (HY000): Cannot drop index 'idx_departemen': needed in a foreign key constraint
Bye

Jawaban dari MySQL 8.4 tegas: ERROR 1553 (HY000): Cannot drop index 'idx_departemen': needed in a foreign key constraint. Server menolak menghapus index yang masih dibutuhkan constraint — dan yang penting dicatat, tidak ada mekanisme pembuatan ulang otomatis: perintah gagal total, tabel dibiarkan utuh apa adanya. Perlindungan ini masuk akal — bila penghapusan diizinkan, setiap DELETE pada departemen dan setiap join anak-induk mendadak kehilangan jalur pencariannya tanpa sepengetahuan siapa pun. Urutan kerja yang benar bila index memang ingin dilepas: buang constraint-nya lebih dulu, baru index-nya:

sql
ALTER TABLE pegawai2 DROP FOREIGN KEY fk_p2_departemen;
DROP INDEX idx_departemen ON pegawai2;

SHOW INDEX FROM pegawai2;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| pegawai2 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

Setelah constraint dilepas lewat ALTER TABLE ... DROP FOREIGN KEY, DROP INDEX berjalan tanpa hambatan — kini tidak ada lagi yang membutuhkan index tersebut. Yang tersisa hanya PRIMARY. Perlu dicatat dengan jujur: melepas FK berarti melepas pula jaminan integritasnya — tabel pegawai2 kini boleh memuat departemen_id yang tidak merujuk departemen mana pun. Keputusan ini hanya pantas bila integritas benar-benar tidak lagi dibutuhkan, atau bila constraint akan dipasang ulang dengan index lain yang lebih sesuai.

Membersihkan Database Latihan #

Database belajar_index kita jatuhkan kembali:

sql
DROP DATABASE belajar_index;

Output yang Dihasilkan:

text

Query OK, 3 rows affected (0.00 sec)

Referensi #

Index pada foreign key kini tuntas: otomatis saat tabel anak lahir, dipakai untuk join dan pemeriksaan induk, di-reuse bila kita sudah menyediakannya, dan dilindungi ERROR 1553 dari penghapusan sembarangan. Index semacam ini hadir karena server tahu persis pola aksesnya. Pertanyaan berikutnya lebih luas: setelah berbagai index berdiri — tunggal, unik, gabungan, prefix, FK — kapan dan bagaimana MySQL memilih memakainya? Chapter berikutnya memperkenalkan EXPLAIN sebagai jendela keputusan optimizer.