KAL
Menu

Storage Engine: InnoDB dan MyISAM

Sejarah InnoDB dan MyISAM serta alasan InnoDB menjadi engine pilihan sejak MySQL 5.5.

Daftar isi

Topik ini membahas storage engine, yaitu lapisan MySQL yang menentukan cara data benar-benar disimpan dan dibaca. Fokusnya ada pada dua engine yang paling berpengaruh dalam sejarah MySQL, yaitu MyISAM dan InnoDB, beserta alasan InnoDB menjadi engine bawaan sejak MySQL 5.5. Setelah menyelesaikan topik ini, pilihan engine pada CREATE TABLE dan isi keluaran SHOW ENGINES sudah dapat dibaca dengan makna lengkapnya.

Lapisan storage engine pada arsitektur MySQL#

Arsitektur MySQL memisahkan dua lapisan yang bertugas berbeda. Lapisan atas disebut lapisan SQL dan menangani pekerjaan yang sama untuk semua query, mulai dari menafsirkan sintaks, menyusun rencana eksekusi, hingga mengirim hasilnya. Lapisan bawah disebut storage engine dan menangani pekerjaan tingkat data, yaitu menyimpan baris, membacanya kembali, memelihara indeks, serta mengatur kunci dan transaksi. Keduanya berhubungan melalui antarmuka bernama handler API, sehingga lapisan SQL tidak perlu mengetahui cara engine menyimpan berkasnya.

Engine bersifat pluggable, artinya dapat dipasang dan diganti tanpa mengubah lapisan SQL. Pilihan engine dinyatakan per tabel melalui klausa ENGINE pada CREATE TABLE, sehingga satu basis data boleh memuat tabel InnoDB dan tabel MyISAM sekaligus. Selain keduanya, server menyediakan engine lain seperti MEMORY, ARCHIVE, dan CSV, yang masing-masing memang ditujukan untuk kegunaan khusus.

Diagram berikut menempatkan kedua engine utama tersebut di dalam arsitektur MySQL. Perhatikan bahwa engine berada di bawah lapisan SQL dan dipilih per tabel, bukan per server.

Klien: mysql dan aplikasiLapisan SQL serverparser, optimizer, dan eksekusi queryAPI penangan (handler API)storage engine, dipilih per tabelInnoDBdefault sejak MySQL 5.5MyISAMnontransaksionalMEMORYdi dalam RAMARCHIVEterkompresi
Posisi storage engine dalam arsitektur MySQL

MyISAM: engine generasi awal#

MyISAM adalah engine bawaan MySQL sejak versi 3.23 pada akhir dekade 1990-an hingga versi 5.1. Namanya berakar pada ISAM, yaitu metode penyimpanan lama yang menjadi dasarnya, dengan huruf My yang diambil dari MySQL. MyISAM populer karena sederhana dan cepat pada beban baca, sehingga banyak aplikasi web era itu dibangun di atasnya.

Karakteristik MyISAM penting dipahami karena masih banyak muncul pada tutorial lama. MyISAM mengunci tabel seluruhnya saat terjadi penulisan (table-level locking), tidak mendukung transaksi, dan tidak mendukung foreign key. Data setiap tabel disimpan dalam dua berkas, yaitu .MYD untuk data dan .MYI untuk indeks. Keunggulan historis MyISAM terletak pada indeks teks penuh (full-text index) yang didukungnya lebih dulu, sedangkan kelemahan terbesarnya ada pada ketahanan data. Tabel MyISAM yang terputus di tengah proses penulisan dapat ditinggalkan dalam keadaan rusak, dan perbaikannya perlu dilakukan manual melalui REPAIR TABLE atau perangkat myisamchk.

Kombinasi karakteristik itu menentukan pola pemakaiannya. Situs yang didominasi pembacaan dengan penulisan jarang dapat bekerja nyaman di atas MyISAM, sedangkan aplikasi dengan penulisan sering dan data yang bernilai tinggi cepat menemui batasnya. Kesadaran akan batas inilah yang kelak mendorong pergeseran komunitas menuju InnoDB.

InnoDB: engine transaksional yang menjadi standar#

InnoDB dikembangkan oleh Innobase Oy, perusahaan yang didirikan Heikki Tuuri di Finlandia, dan disertakan di dalam MySQL sejak versi 3.23 pada tahun 2001. Oracle mengakuisisi Innobase Oy pada tahun 2005, kemudian juga mengakuisisi Sun pada tahun 2010 sehingga InnoDB dan MySQL berada di bawah satu penerbit yang sama. Integrasi yang semakin erat itu terlihat dari perkembangan fitur pada versi-versi berikutnya yang dibangun langsung di atas InnoDB.

Perbedaan perilakunya terhadap MyISAM bersifat mendasar. InnoDB mendukung transaksi dengan jaminan ACID (atomicity, consistency, isolation, durability) beserta perintah commit dan rollback. Penguncian dilakukan pada tingkat baris (row-level locking), sehingga penulis dan pembaca tidak saling menunggu.

Selain itu, InnoDB memelihara foreign key untuk menjaga keutuhan rujukan antartabel. Struktur datanya disusun sebagai clustered index, artinya baris tersimpan menurut urutan kunci primernya. InnoDB juga mencatat perubahan pada log (redo log) sebelum data benar-benar selesai ditulis, sehingga server yang mati mendadak dapat memulihkan tabelnya secara otomatis ketika dinyalakan kembali.

Peran InnoDB terus menguat pada versi-versi berikutnya. Sejak MySQL 8.0, kamus data (data dictionary) yang mencatat struktur seluruh tabel juga dipindahkan ke tabel InnoDB, dan fitur baru seperti CTE serta fungsi window dikembangkan di atasnya. Pada praktiknya, InnoDB kini menjadi wajah utama MySQL.

Mengapa InnoDB menjadi pilihan sejak MySQL 5.5#

Pada tahun 2010, MySQL 5.5 menjadikan InnoDB sebagai engine bawaan, menggantikan MyISAM. Keputusan ini mengubah perilaku default MySQL menjadi jauh lebih aman untuk aplikasi nyata, karena data terlindungi transaksi, penulisan bersamaan tidak mengunci seluruh tabel, dan server yang mati mendadak pulih tanpa perbaikan manual. MyISAM tetap tersedia demi kompatibilitas, tetapi statusnya bergeser dari pilihan utama menjadi pilihan khusus.

Kebijakan tersebut dapat diperiksa langsung pada server. Jalankan perintah berikut untuk melihat engine bawaan yang dipakai tabel tanpa klausa ENGINE.

bash 
# memeriksa engine bawaan server
mysql -t -u root -e "SELECT @@default_storage_engine;"
text Keluaran 
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

Nilai InnoDB pada keluaran berarti tabel baru otomatis dibuat dengan InnoDB. Daftar lengkap engine beserta status dukungannya diperlihatkan oleh SHOW ENGINES.

bash 
# menampilkan daftar storage engine dan status dukungannya
mysql -t -u root -e "SHOW ENGINES;"
text Keluaran 
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Kolom Support menyatakan tingkat dukungan server terhadap tiap engine, dengan nilai DEFAULT untuk engine bawaan, YES untuk yang tersedia, dan NO untuk yang tidak dikompilasi ke dalam server ini. Baris InnoDB menunjukkan DEFAULT sekaligus dukungan penuh pada kolom Transactions, XA, dan Savepoints yang semuanya bernilai YES. Bandingkan dengan baris MyISAM yang bernilai NO pada ketiga kolom transaksional tersebut.

Query setara juga tersedia melalui tabel information_schema.engines, yang berguna ketika hasilnya perlu disaring atau digabungkan dengan query lain.

bash 
# menyaring dua engine utama dari katalog information_schema
mysql -t -u root -e "SELECT ENGINE, SUPPORT FROM information_schema.engines WHERE ENGINE IN ('InnoDB', 'MyISAM');"
text Keluaran 
+--------+---------+
| ENGINE | SUPPORT |
+--------+---------+
| InnoDB | DEFAULT |
| MyISAM | YES     |
+--------+---------+

Sifat engine yang dipilih per tabel dapat dibuktikan dengan membuat dua tabel pada satu basis data. Jalankan blok berikut, yang membuat basis data latihan berisi satu tabel InnoDB dan satu tabel MyISAM.

bash 
# membuat basis data latihan dengan dua tabel berbeda engine
mysql -u root <<'SQL'
DROP DATABASE IF EXISTS latihan_engine;
CREATE DATABASE latihan_engine;
USE latihan_engine;
CREATE TABLE catatan (id INT PRIMARY KEY, isi VARCHAR(100)) ENGINE=InnoDB;
CREATE TABLE arsip (id INT PRIMARY KEY, isi VARCHAR(100)) ENGINE=MyISAM;
SQL

Perintah pembuatan seperti di atas tidak mencetak apa pun, dan keberhasilannya dapat dibaca dari katalog information_schema. Jalankan query berikut untuk melihat engine yang benar-benar terpasang pada kedua tabel.

bash 
# membaca engine tiap tabel pada basis data latihan
mysql -t -u root -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'latihan_engine' ORDER BY TABLE_NAME;"
text Keluaran 
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| arsip      | MyISAM |
| catatan    | InnoDB |
+------------+--------+

Keluaran menunjukkan kedua tabel hidup berdampingan pada satu basis data, dengan arsip memakai MyISAM dan catatan memakai InnoDB. Setelah pembuktian selesai, hapus basis data latihan agar server tetap bersih.

bash 
# menghapus basis data latihan
mysql -u root -e "DROP DATABASE latihan_engine;"

Kesalahan umum#

  • Memilih MyISAM hanya karena mengikuti tutorial lama. Banyak materi yang ditulis sebelum MySQL 5.5 menyertakan ENGINE=MyISAM tanpa alasan teknis, dan menyalinnya mentah-mentah membuat tabel baru kehilangan transaksi, foreign key, dan pemulihan otomatis. Perbaikannya, hapus klausa tersebut atau gantinya dengan ENGINE=InnoDB.
  • Menganggap engine sebagai pengaturan yang berlaku untuk seluruh server. Engine dipilih per tabel, sehingga satu basis data dapat memuat tabel dengan engine berbeda dan tabel yang sudah dibuat tidak ikut berubah ketika engine bawaan diganti. Pemeriksaannya dilakukan per tabel melalui kolom ENGINE pada information_schema.TABLES.
  • Menyamakan isi SHOW ENGINES dengan engine yang dipakai tabel. Keluaran SHOW ENGINES hanya menyatakan engine yang tersedia di server beserta status dukungannya, sedangkan engine aktif setiap tabel berada pada information_schema.TABLES. Keduanya menjawab pertanyaan yang berbeda, yaitu kemampuan server dan keadaan tabel.
  • Membiarkan tabel penting tetap pada MyISAM demi ukuran berkasnya yang lebih kecil. Ukuran yang lebih ramping itu tidak sebanding dengan risikonya, karena satu crash dapat meninggalkan tabel yang rusak tanpa jaminan pemulihan otomatis. Perbaikannya, konversikan tabel tersebut dengan ALTER TABLE ... ENGINE=InnoDB, lalu verifikasi hasilnya melalui kolom ENGINE pada information_schema.TABLES.

Ringkasan#

  • Storage engine adalah lapisan penyimpanan data di bawah lapisan SQL dan dipilih per tabel melalui klausa ENGINE.
  • MyISAM, engine bawaan hingga MySQL 5.1, mengunci selevel tabel dan tidak mendukung transaksi maupun foreign key.
  • InnoDB menyediakan transaksi ACID, row-level locking, foreign key, dan pemulihan otomatis setelah crash.
  • MySQL 5.5 menjadikan InnoDB engine bawaan pada tahun 2010, dan MySQL 8.0 mengukuhkannya dengan memindahkan kamus data ke InnoDB.

Referensi#