Memahami arsitektur MySQL membuat kita tidak sekadar menghafal perintah, tetapi benar-benar paham mengapa suatu query berjalan cepat atau lambat, mengapa ada server dan client, dan di mana data sebenarnya disimpan. MySQL memakai client-server architecture: satu program server bernama mysqld mengelola seluruh data, sementara banyak client mengirim permintaan lewat jaringan. Pada Chapter ini kita bedah arsitektur tersebut, telusuri perjalanan sebuah query dari koneksi sampai hasil, dan kenali storage engine InnoDB.

Arsitektur Client-Server #

MySQL Server berjalan terus-menerus sebagai proses latar belakang di satu mesin. Program client — mysql di terminal, aplikasi PHP atau Python melalui connector, maupun MySQL Workbench — tersambung ke server lewat jaringan TCP dengan port standar 3306, atau lewat Unix socket apabila client dan server berada di mesin yang sama. Server menjadi satu-satunya pihak yang menyentuh data; client hanya mengirim statement SQL dan menerima hasil.

Diagram di atas memperlihatkan tiga jalur client yang berbeda menuju satu server yang sama. Pada sisi server, statement yang masuk melewati rangkaian tahapan: pemeriksaan sintaks oleh parser, penyusunan rencana eksekusi oleh optimizer, lalu pengelolaan data oleh storage engine yang menulis ke disk. Server menangani banyak client secara bersamaan; setiap koneksi mendapat thread sendiri sehingga ribuan query dapat berjalan paralel tanpa saling menunggu.

Pemisahan client dan server membawa konsekuensi praktis. Data hidup di satu tempat yang terpusat, sehingga beberapa aplikasi berbeda dapat membaca sumber data yang sama. Server juga menjadi gerbang tunggal untuk autentikasi user dan penerapan privilege, dan koneksi dapat dienkripsi saat client berjalan di mesin atau jaringan yang berbeda dengan server.

Perjalanan Sebuah Query #

Setiap statement SQL yang kita kirim menempuh perjalanan yang sama di dalam server. Memahami tahapannya membantu kita menemukan letak masalah ketika query gagal atau lambat:

  1. Koneksi dan autentikasi — client membuka koneksi TCP atau Unix socket; server memeriksa nama user, password, dan host asal koneksi. Kombinasi yang tidak terdaftar ditolak sebelum query dibaca.
  2. Parsing — server membaca teks statement dan memeriksa sintaksnya. Kesalahan ketik seperti SELEC ditolak di tahap ini dengan pesan galat sintaks.
  3. Optimisasi — optimizer menganalisis berbagai cara menjalankan query, misalnya index mana yang dipakai, lalu memilih rencana eksekusi termurah.
  4. Eksekusi dan storage engine — eksekutor menjalankan rencana tersebut dan meminta baris data dari storage engine yang mengelola table terkait.
  5. Pengembalian hasil — baris hasil dirangkai menjadi result set dan dikirim balik ke client, ditutup dengan baris ringkasan seperti N rows in set.

Pembagian tahap tersebut menjelaskan gejala yang sering membingungkan pemula. Galat yang muncul sebelum query berjalan biasanya berasal dari tahap parsing, sedangkan query yang sah tetapi lambat biasanya berakar pada tahap optimisasi — paling sering karena index yang kurang. Dengan mengetahui pipeline-nya, kita tahu harus mencari kesalahan di mana.

Storage Engine: Lapisan Pengelola Table #

Keunikan arsitektur MySQL adalah pemisahan lapisan SQL dari lapisan penyimpanan. Storage engine adalah modul yang mengatur bagaimana sebuah table disimpan di disk: format filenya, dukungan transaksi, mekanisme penguncian, sampai pemulihan setelah server mati mendadak. Table yang kita buat dapat memilih storage engine yang berbeda-beda dalam satu database yang sama.

Storage engine default MySQL modern adalah InnoDB. InnoDB mendukung transaksi ACID, penguncian per baris (row-level locking) supaya update paralel tidak saling menghalangi, foreign key, serta pemulihan otomatis dari crash. Mayoritas aplikasi memakai InnoDB dan tidak perlu berpindah ke engine lain.

Kita bisa memastikan storage engine default server dengan membaca variabel sistem berikut:

sql 
SELECT @@default_storage_engine;

Output yang Dihasilkan:

text 
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

Daftar seluruh storage engine yang tersedia pada server dapat dilihat dengan SHOW ENGINES:

sql 
SHOW ENGINES;

Output yang Dihasilkan:

text 
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

Output di atas memuat satu baris per storage engine dengan kolom-kolom berikut: Engine adalah nama engine, Support menyatakan apakah engine tersebut aktif, dan kolom Transactions, XA, serta Savepoints menyatakan dukungan fitur transaksional. Baris InnoDB bernilai DEFAULT pada kolom Support, yang menegaskan posisinya sebagai engine bawaan; nilai NO pada baris seperti FEDERATED berarti engine tersebut tidak dapat dipakai pada server ini. Engine lain seperti MEMORY untuk data sementara di RAM dan CSV untuk table berbentuk file CSV memenuhi kebutuhan khusus, dan akan kita bahas pada bagian storage engine.

Menyambungkan Client ke Server #

Program mysql adalah client teks bawaan yang akan kita pakai sepanjang course. Untuk menyambung ke server, kita menjalankan perintah berikut di terminal setelah MySQL terpasang:

bash 
mysql -h 127.0.0.1 -P 3306 -u root -p

Empat opsi pada perintah tersebut punya arti masing-masing. Opsi -h 127.0.0.1 menunjuk alamat host server, -P 3306 menunjuk port TCP server, -u root memilih nama user, dan -p meminta mysql menanyakan password secara interaktif. Setelah tersambung, client menampilkan prompt mysql> yang siap menerima statement SQL.

Client versinya sendiri dapat diperiksa tanpa menyambung ke server:

bash 
mysql --version

Opsi --version hanya mencetak nomor versi program client lalu keluar — berguna untuk memastikan mysql terpasang dengan benar di mesin kita.

Referensi #

Kini kita memahami mesin di balik layar: client mengirim SQL, server mem-parse, mengoptimalkan, dan menyerahkan data kepada InnoDB. Sebelum mulai memasang MySQL pada mesin kita sendiri, ada satu keputusan yang perlu diambil terlebih dahulu: edisi MySQL mana yang akan dipakai. Chapter berikutnya membandingkan MySQL Community dengan edisi-edisi lainnya.