Index dan Transaction MySQL: Bikin Query Lebih Efisien dan Aman

Persiapan Database#

Kita tetap menggunakan database dari Bab 3 sampai Bab 6.

Buka DBeaver, hubungkan ke MySQL, lalu jalankan:

1
USE db_toko_bapakgabut;

Database kita mempunyai empat tabel:

Relasinya:

1
pelanggan (1) --- (N) pesanan
2
pesanan (1) --- (N) detail_pesanan
3
produk (1) --- (N) detail_pesanan

Seluruh tabel pada contoh sebelumnya menggunakan mesin penyimpanan InnoDB.

Ini penting karena InnoDB mendukung transaksi dan foreign key.

Kamu bisa memeriksa struktur tabel dengan:

1
SHOW CREATE TABLE produk;

Untuk melihat mesin penyimpanan seluruh tabel:

1
SELECT
2
    TABLE_NAME,
3
    ENGINE
4
FROM information_schema.TABLES
5
WHERE TABLE_SCHEMA = 'db_toko_bapakgabut'
6
  AND TABLE_NAME IN (
7
      'pelanggan',
8
      'produk',
9
      'pesanan',
10
      'detail_pesanan'
11
  );

Pastikan keempat tabel menggunakan InnoDB sebelum melanjutkan latihan transaksi.

Apa Itu Index pada Database?#

Bayangkan kamu sedang mencari sebuah topik di buku yang mempunyai seribu halaman.

Tanpa daftar isi atau indeks, kamu mungkin harus memeriksa banyak halaman sampai menemukan topik yang dicari.

Dengan indeks, kamu mempunyai petunjuk untuk menemukan bagian yang relevan.

Konsep index pada database kurang lebih seperti itu.

Index adalah struktur data yang membantu MySQL menemukan baris tertentu tanpa harus selalu memeriksa seluruh isi tabel.

Pada InnoDB, index biasa menggunakan struktur B-tree.

Struktur tersebut membantu pencarian berdasarkan nilai kolom tertentu.

Namun, index bukan tombol ajaib yang membuat semua query otomatis lebih cepat.

Efektivitasnya bergantung pada kondisi query, jumlah data, distribusi nilai, dan keputusan query optimizer.

Primary Key Sudah Memiliki Index#

Sebelum membuat index baru, perlu diketahui bahwa primary key sudah mempunyai index.

Pada tabel produk, kita memiliki:

1
id_produk INT AUTO_INCREMENT PRIMARY KEY

Karena id_produk adalah primary key, MySQL sudah membuat index untuk kolom tersebut.

Jadi, kita tidak perlu membuat index tambahan dengan kolom yang sama hanya untuk pencarian berdasarkan ID.

Untuk melihat index yang tersedia:

1
SHOW INDEX FROM produk;

Hasilnya menampilkan informasi index, termasuk nama index, kolom, dan urutan kolom di dalam index.

Pada tabel InnoDB, primary key juga digunakan sebagai clustered index yang menentukan organisasi penyimpanan baris.

Membuat Index pada Kolom#

Misalnya, pengguna toko online sering mencari produk berdasarkan nama.

Query-nya:

1
SELECT
2
    id_produk,
3
    nama_produk,
4
    harga,
5
    stok
6
FROM produk
7
WHERE nama_produk = 'Keyboard Mechanical';

Kita dapat mempertimbangkan index pada kolom nama_produk.

1
CREATE INDEX idx_produk_nama
2
ON produk (nama_produk);

Nama idx_produk_nama merupakan nama index yang kita tentukan sendiri.

Setelah index dibuat, periksa:

1
SHOW INDEX FROM produk;

Sekarang tabel produk mempunyai index tambahan pada kolom nama.

Mengenal EXPLAIN#

Bagaimana cara mengetahui strategi yang dipilih MySQL ketika menjalankan query?

Kita bisa menggunakan EXPLAIN.

Contohnya:

1
EXPLAIN
2
SELECT
3
    id_produk,
4
    nama_produk,
5
    harga,
6
    stok
7
FROM produk
8
WHERE nama_produk = 'Keyboard Mechanical';

EXPLAIN menampilkan rencana eksekusi yang diperkirakan oleh optimizer.

Beberapa kolom penting yang sering muncul:

Misalnya, jika kolom key berisi idx_produk_nama, berarti optimizer memilih index tersebut dalam rencana eksekusinya.

Jika key bernilai NULL, tidak ada index yang dipilih untuk akses tersebut.

Namun, hal ini tidak otomatis berarti query buruk.

Pada tabel kecil, membaca seluruh tabel bisa saja merupakan pilihan paling efisien.

Membandingkan Query Sebelum dan Sesudah Index#

Jika ingin melakukan eksperimen yang lebih terukur, gunakan tabel latihan yang cukup besar dan bandingkan query dengan kondisi yang sama.

Alurnya:

1. Siapkan data uji dengan jumlah baris yang memadai.
2. Jalankan EXPLAIN sebelum membuat index.
3. Catat rencana eksekusi dan waktu pengujian.
4. Buat index yang relevan.
5. Jalankan kembali query yang sama.
6. Bandingkan hasilnya.

Pada MySQL 8.0.18 atau lebih baru, tersedia juga:

1
EXPLAIN ANALYZE
2
SELECT
3
    id_produk,
4
    nama_produk,
5
    harga
6
FROM produk
7
WHERE nama_produk = 'Keyboard Mechanical';

Berbeda dengan EXPLAIN biasa, EXPLAIN ANALYZE menjalankan query dan menampilkan informasi eksekusi aktual.

Gunakan dengan hati-hati, terutama untuk query berat.

Contoh pada artikel ini berupa SELECT, sehingga tidak mengubah data. Namun, eksekusinya tetap dapat menggunakan sumber daya server.

Mengenal Composite Index#

Selain index pada satu kolom, MySQL mendukung index yang terdiri dari beberapa kolom.

Index seperti ini disebut composite index atau index gabungan.

Misalnya, aplikasi sering mencari pesanan berdasarkan pelanggan dan tanggal pesanan.

Query-nya:

1
SELECT
2
    id_pesanan,
3
    id_pelanggan,
4
    tanggal_pesanan,
5
    status_pesanan
6
FROM pesanan
7
WHERE id_pelanggan = 1
8
ORDER BY tanggal_pesanan DESC;

Angka 1 hanya contoh ID pelanggan. Sesuaikan dengan data yang tersedia.

Kita dapat membuat index gabungan:

1
CREATE INDEX idx_pesanan_pelanggan_tanggal
2
ON pesanan (id_pelanggan, tanggal_pesanan);

Index tersebut menyusun key berdasarkan id_pelanggan terlebih dahulu, kemudian tanggal_pesanan.

1
(id_pelanggan, tanggal_pesanan)

1
SHOW INDEX FROM pesanan;

Kapan Sebaiknya Membuat Index?#

Index biasanya layak dipertimbangkan pada kolom yang sering digunakan untuk:

• Kondisi pencarian pada WHERE.
• Penggabungan tabel melalui JOIN.
• Pengurutan pada ORDER BY.
• Pengelompokan tertentu pada GROUP BY.

Namun, tidak semua kondisi tersebut otomatis membutuhkan index baru.

Beberapa faktor yang perlu diperhatikan:

Selektivitas. Kolom yang mempunyai banyak nilai berbeda sering lebih berguna untuk pencarian selektif dibandingkan kolom yang hanya berisi sedikit variasi nilai.

Ukuran tabel. Pada tabel kecil, full table scan mungkin lebih murah.

Beban perubahan data. Setiap INSERT, UPDATE, dan DELETE dapat menambah pekerjaan pemeliharaan index.

Ruang penyimpanan. Index memerlukan ruang tambahan.

Pola query nyata. Index sebaiknya dibuat berdasarkan kebutuhan aplikasi, bukan sekadar perkiraan.

Kesimpulannya, index adalah alat optimasi, bukan koleksi yang harus dilengkapi semuanya.

Masalah Kedua: Perubahan Data yang Tidak Konsisten#

Sekarang kita beralih dari performa query ke keamanan perubahan data.

Bayangkan Andi membeli satu Keyboard Mechanical.

Aplikasi perlu melakukan beberapa pekerjaan:

1. Memastikan stok tersedia.
2. Membuat pesanan.
3. Membuat detail pesanan.
4. Mengurangi stok produk.

Jika seluruh langkah berhasil, transaksi dapat dianggap sukses.

Tetapi bagaimana jika langkah ketiga gagal setelah langkah kedua berhasil?

Kita bisa mempunyai pesanan tanpa detail.

Atau bagaimana jika stok sudah berkurang, tetapi pesanan gagal tersimpan?

Data menjadi tidak konsisten.

Untuk mengatasi masalah tersebut, kita membutuhkan database transaction.

Apa Itu Transaction?#

Transaction adalah satu unit pekerjaan yang terdiri dari satu atau lebih operasi database.

Pada database transaksional seperti InnoDB, perubahan yang termasuk dalam satu transaction dapat diselesaikan bersama melalui COMMIT atau dibatalkan melalui ROLLBACK, selama perubahan tersebut memang mendukung transaksi.

Contoh sederhananya:

1
Mulai transaksi
2
       |
3
Buat pesanan
4
       |
5
Buat detail pesanan
6
       |
7
Kurangi stok
8
       |
9
Semua berhasil?
10
    /       \
11
   Ya       Tidak
12
   |          |
13
 COMMIT    ROLLBACK

Dengan transaction, kita bisa menjaga agar perubahan data tidak berhenti dalam keadaan setengah selesai.

Mengenal Konsep ACID#

Transaction pada database relasional sering dijelaskan menggunakan empat sifat yang disebut ACID.

START TRANSACTION, COMMIT, dan ROLLBACK#

Tiga perintah dasar yang perlu kita pahami adalah:

Mari kita praktikkan dengan contoh sederhana.

1
START TRANSACTION;
2

3
INSERT INTO produk (
4
    nama_produk,
5
    harga,
6
    stok
7
)
8
VALUES (
9
    'Produk Latihan Transaksi',
10
    10000,
11
    5
12
);
13

14
COMMIT;

1
SELECT *
2
FROM produk
3
WHERE nama_produk = 'Produk Latihan Transaksi';

1
START TRANSACTION;
2

3
UPDATE produk
4
SET harga = 99999
5
WHERE nama_produk = 'Produk Latihan Transaksi';
6

7
SELECT
8
    nama_produk,
9
    harga
10
FROM produk
11
WHERE nama_produk = 'Produk Latihan Transaksi';
12

13
ROLLBACK;

1
SELECT
2
    nama_produk,
3
    harga
4
FROM produk
5
WHERE nama_produk = 'Produk Latihan Transaksi';

Penting: ROLLBACK Tidak Membatalkan Semua Jenis Perintah#

Ada hal yang perlu diperhatikan.

Pada MySQL, banyak perintah DDL seperti CREATE TABLE, ALTER TABLE, dan DROP TABLE menyebabkan implicit commit.

Artinya, jangan menganggap semua perubahan struktur database bisa dibatalkan menggunakan ROLLBACK seperti perubahan data InnoDB biasa.

Contoh latihan transaction kita menggunakan operasi DML seperti INSERT dan UPDATE.

Ketika melakukan perubahan struktur database, periksa dokumentasi dan siapkan strategi migrasi serta backup yang sesuai.

Simulasi Transaksi Pembelian yang Aman#

Sekarang kita membuat contoh yang lebih mendekati aplikasi nyata.

Skenarionya:

Andi membeli satu Keyboard Mechanical.

Sistem perlu:

1. Mengambil ID pelanggan dan produk.
2. Memastikan stok tersedia.
3. Membuat pesanan.
4. Membuat detail pesanan.
5. Mengurangi stok.
6. Melakukan COMMIT hanya jika seluruh langkah berhasil.

Kita akan menggunakan SQL pada MySQL 8 dengan InnoDB.

Penting: Contoh berikut adalah latihan manual yang dijalankan bertahap dalam satu sesi database. Jangan menjalankan semua perintah tanpa memeriksa hasil di setiap tahap.

1
SELECT
2
    id_pelanggan,
3
    nama_pelanggan,
4
    email
5
FROM pelanggan
6
WHERE email = 'andi@example.com';

1
SELECT
2
    id_produk,
3
    nama_produk,
4
    harga,
5
    stok
6
FROM produk
7
WHERE nama_produk = 'Keyboard Mechanical';

1
START TRANSACTION;

1
SELECT
2
    id_produk,
3
    nama_produk,
4
    harga,
5
    stok
6
FROM produk
7
WHERE id_produk = 1
8
FOR UPDATE;

1
ROLLBACK;

1
INSERT INTO pesanan (
2
    id_pelanggan,
3
    status_pesanan
4
)
5
VALUES (
6
    1,
7
    'baru'
8
);

1
SET @id_pesanan_baru = LAST_INSERT_ID();

1
INSERT INTO detail_pesanan (
2
    id_pesanan,
3
    id_produk,
4
    jumlah,
5
    harga_satuan
6
)
7
VALUES (
8
    @id_pesanan_baru,
9
    1,
10
    1,
11
    325000
12
);

1
UPDATE produk
2
SET stok = stok - 1
3
WHERE id_produk = 1
4
  AND stok >= 1;

1
SELECT ROW_COUNT() AS jumlah_baris_diubah;

1
ROLLBACK;

1
COMMIT;

1
ROLLBACK;

Kenapa Tidak Cukup Menggunakan UPDATE Stok Saja?#

Mungkin kamu bertanya:

Bukankah kita bisa langsung menjalankan:

1
UPDATE produk
2
SET stok = stok - 1
3
WHERE id_produk = 1
4
  AND stok >= 1;

Query tersebut memang membantu mencegah stok berkurang ketika jumlahnya tidak mencukupi.

Namun, pembelian tidak hanya mengubah stok.

Kita juga perlu membuat pesanan dan detail pesanan.

Tanpa transaction, ketiga perubahan tersebut dapat berhasil atau gagal secara terpisah.

Dengan transaction, kita bisa mengelola semuanya sebagai satu proses bisnis.

Pada implementasi backend, operasi tersebut biasanya dijalankan menggunakan satu koneksi database yang sama, dengan COMMIT ketika sukses dan ROLLBACK ketika gagal.

Mengenal Locking pada InnoDB#

Saat membahas transaction, kita juga perlu mengenal locking.

Locking merupakan mekanisme yang membantu database mengatur akses bersamaan terhadap data.

Misalnya, stok keyboard tinggal satu unit.

Andi dan Budi sama-sama menekan tombol beli.

Jika aplikasi membaca stok secara terpisah tanpa pengendalian konkurensi yang tepat, keduanya bisa sama-sama menganggap barang masih tersedia.

Penguncian baris dan UPDATE bersyarat dapat membantu mencegah kondisi tersebut.

Contohnya:

1
START TRANSACTION;
2

3
SELECT stok
4
FROM produk
5
WHERE id_produk = 1
6
FOR UPDATE;

Selama transaksi memegang lock yang relevan, transaksi lain yang membutuhkan lock yang bertentangan mungkin harus menunggu.

Setelah transaksi melakukan COMMIT atau ROLLBACK, lock transaksional tersebut dilepaskan.

Namun, locking juga memiliki konsekuensi.

Jika transaksi dibiarkan terbuka terlalu lama, transaksi lain dapat menunggu dan performa aplikasi menurun.

Karena itu, transaksi sebaiknya dibuat sesingkat mungkin.

Jangan membiarkan transaksi database terbuka sambil menunggu pengguna menyelesaikan pembayaran atau mengisi formulir selama beberapa menit.

Mengenal Deadlock#

Deadlock terjadi ketika dua atau lebih transaksi saling menunggu lock yang dipegang transaksi lainnya sehingga tidak ada yang bisa melanjutkan tanpa intervensi.

Contoh sederhana:

• Transaksi A mengunci produk 1, lalu mencoba mengunci produk 2.
• Transaksi B mengunci produk 2, lalu mencoba mengunci produk 1.

Keduanya dapat saling menunggu.

InnoDB mempunyai mekanisme pendeteksian deadlock dan dapat memilih salah satu transaksi sebagai korban untuk dibatalkan.

Aplikasi perlu menangani kondisi tersebut, misalnya dengan melakukan retry secara terbatas terhadap keseluruhan unit transaksi yang aman untuk diulang.

Beberapa praktik yang membantu mengurangi risiko deadlock:

• Mengakses tabel dan baris dalam urutan yang konsisten.
• Menjaga transaksi tetap singkat.
• Menggunakan index yang sesuai agar operasi locking lebih terarah.
• Menghindari transaksi yang melakukan terlalu banyak pekerjaan sekaligus.
• Menyiapkan penanganan retry untuk error yang memang layak dicoba kembali.

Transaction membuat perubahan data lebih aman, tetapi tetap perlu dirancang dengan baik.

Autocommit pada MySQL#

Secara umum, MySQL menggunakan mode autocommit yang aktif secara default.

Dalam mode tersebut, setiap statement yang berhasil biasanya di-commit secara otomatis jika tidak berada dalam transaksi eksplisit.

Periksa pengaturannya:

1
SELECT @@autocommit;

Jika hasilnya 1, autocommit aktif.

Ketika kita menjalankan:

1
START TRANSACTION;

Kita memulai transaksi eksplisit yang dapat diselesaikan dengan COMMIT atau ROLLBACK.

Perlu diperhatikan bahwa beberapa database client, termasuk DBeaver, mempunyai pengaturan transaction mode tersendiri.

Karena itu, pahami apakah editor sedang menggunakan auto-commit atau manual commit sebelum melakukan perubahan data penting.

Latihan Mandiri#

Sekarang waktunya mencoba.

Gunakan database db_toko_bapakgabut.

1
idx_pelanggan_email

1
SELECT *
2
FROM pelanggan
3
WHERE email = 'andi@example.com';

1
(status_pesanan, tanggal_pesanan)

1
idx_pesanan_status_tanggal

Pembahasan Latihan#

1
CREATE INDEX idx_pelanggan_email
2
ON pelanggan (email);

1
SHOW INDEX FROM pelanggan;

1
EXPLAIN
2
SELECT *
3
FROM pelanggan
4
WHERE email = 'andi@example.com';

1
CREATE INDEX idx_pesanan_status_tanggal
2
ON pesanan (status_pesanan, tanggal_pesanan);

1
SHOW INDEX FROM pesanan;

1
START TRANSACTION;
2

3
UPDATE produk
4
SET harga = harga + 10000
5
WHERE id_produk = 1;
6

7
SELECT
8
    id_produk,
9
    nama_produk,
10
    harga
11
FROM produk
12
WHERE id_produk = 1;
13

14
ROLLBACK;

1
SELECT
2
    id_produk,
3
    nama_produk,
4
    harga
5
FROM produk
6
WHERE id_produk = 1;

Kesimpulan#

Pada Bab 7 ini, kita sudah mempelajari dua konsep penting dalam pengembangan database.

Index membantu MySQL menemukan data dengan lebih efisien pada kondisi yang sesuai.

Kita mengenal index tunggal, composite index, konsep leftmost prefix, dan penggunaan EXPLAIN untuk memeriksa rencana eksekusi query.

Kita juga memahami bahwa terlalu banyak index dapat menambah biaya penyimpanan dan perubahan data.

Sementara itu, transaction membantu mengelola beberapa operasi database sebagai satu kesatuan.

Kita sudah mempelajari konsep ACID, START TRANSACTION, COMMIT, ROLLBACK, locking, dan deadlock.

Melalui simulasi pembelian, kita melihat mengapa pembuatan pesanan, pencatatan detail, dan pengurangan stok perlu dikelola secara konsisten.

Sekarang database toko online kita bukan hanya mampu menyimpan data dan menghasilkan laporan.

Kita juga mulai memahami cara membuat akses data lebih efisien dan proses perubahan data lebih aman.

Pada Bab 8, kita akan menutup series ini dengan studi kasus lengkap:

Studi Kasus MySQL: Membuat Database Toko Online Sederhana.

Kita akan menyusun kembali struktur database, relasi, data contoh, query laporan, serta alur transaksi menjadi satu studi kasus yang bisa dipraktikkan dari awal.

Sampai jumpa di bab terakhir, Gabuters!