View, Index, dan Stored Procedure

Selain tabel, MySQL menyediakan objek-objek lain yang meningkatkan efisiensi dan maintainability: VIEW untuk menyederhanakan query, INDEX untuk mempercepat pencarian, serta STORED PROCEDURE, FUNCTION, dan TRIGGER untuk logika yang tersimpan di server.

Pendahuluan

Sampai sekarang kita sudah banyak bermain dengan tabel dan query. Namun MySQL juga menyediakan objek-objek lain yang membuat database lebih efisien dan mudah dipelihara:

  • VIEW — “tabel virtual” dari hasil query, menyederhanakan query kompleks.
  • INDEX — struktur data yang mempercepat pencarian.
  • STORED PROCEDURE — kumpulan perintah SQL yang disimpan di server.
  • FUNCTION — mirip stored procedure tetapi mengembalikan nilai.
  • TRIGGER — kode yang otomatis dijalankan saat terjadi event pada tabel.

Artikel ini membahas kelima objek tersebut dengan contoh menggunakan skema toko_online.

Asumsi: Anda berada di database toko_online (USE toko_online;) dengan data contoh pada tabel pelanggan, produk, pesanan, dan detail_pesanan.


VIEW — Tabel Virtual

VIEW adalah query yang disimpan dengan nama, seolah-olah sebuah tabel. View tidak menyimpan data sendiri; ia menjalankan query dasarnya setiap kali diakses.

Manfaat VIEW

  • Menyederhanakan query kompleks — simpan JOIN dan agregasi sebagai view, lalu query view seperti tabel biasa.
  • Keamanan — berikan akses ke view yang hanya menampilkan kolom tertentu, tanpa memberi akses ke tabel asli.
  • Konsistensi — jika logika query berubah, cukup ubah view, bukan seluruh aplikasi.

Membuat VIEW

CREATE VIEW nama_view AS
SELECT ...;

Contoh: View ringkasan pesanan

CREATE VIEW v_ringkasan_pesanan AS
SELECT
  ps.id AS pesanan_id,
  p.nama AS pelanggan,
  ps.status,
  ps.total,
  ps.dibuat_pada
FROM pesanan AS ps
INNER JOIN pelanggan AS p ON ps.pelanggan_id = p.id;

Setelah dibuat, view bisa di-query seperti tabel:

SELECT * FROM v_ringkasan_pesanan WHERE status = 'selesai';

Contoh: View dengan agregasi

CREATE VIEW v_total_belanja_pelanggan AS
SELECT
  p.id AS pelanggan_id,
  p.nama,
  COUNT(ps.id) AS jumlah_pesanan,
  IFNULL(SUM(ps.total), 0) AS total_belanja
FROM pelanggan AS p
LEFT JOIN pesanan AS ps ON ps.pelanggan_id = p.id
GROUP BY p.id, p.nama;

Query view:

SELECT * FROM v_total_belanja_pelanggan
ORDER BY total_belanja DESC
LIMIT 5;

Melihat Daftar VIEW

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Melihat Definisi VIEW

SHOW CREATE VIEW v_ringkasan_pesanan;

Mengubah VIEW

CREATE OR REPLACE VIEW v_ringkasan_pesanan AS
SELECT
  ps.id AS pesanan_id,
  p.nama AS pelanggan,
  p.email,
  ps.status,
  ps.total,
  ps.dibuat_pada
FROM pesanan AS ps
INNER JOIN pelanggan AS p ON ps.pelanggan_id = p.id;

Atau:

ALTER VIEW v_ringkasan_pesanan AS
SELECT ...;

Menghapus VIEW

DROP VIEW IF EXISTS v_ringkasan_pesanan;

VIEW yang Bisa Di-Update

View sederhana (tanpa JOIN, GROUP BY, DISTINCT, fungsi agregasi, atau subquery) bisa di-UPDATE, INSERT, dan DELETE. Perubahan akan diteruskan ke tabel dasar.

CREATE VIEW v_pelanggan_aktif AS
SELECT id, nama, email FROM pelanggan;

-- Bisa di-update
UPDATE v_pelanggan_aktif SET email = '[email protected]' WHERE id = 1;

Catatan: View dengan JOIN atau agregasi umumnya read-only. Untuk view yang kompleks, gunakan WITH CHECK OPTION jika ingin membatasi baris yang bisa diubah.

VIEW dengan WITH CHECK OPTION

Mencegah INSERT/UPDATE yang menghasilkan baris di luar kondisi view:

CREATE VIEW v_produk_tersedia AS
SELECT * FROM produk WHERE stok > 0
WITH CHECK OPTION;

Jika Anda mencoba INSERT produk dengan stok = 0 melalui view ini, MySQL akan menolak.


INDEX — Mempercepat Pencarian

INDEX adalah struktur data (biasanya B-tree) yang memungkinkan MySQL menemukan baris dengan cepat tanpa memindai seluruh tabel (full table scan).

Kapan INDEX Digunakan?

  • Kolom yang sering muncul di WHERE, JOIN, dan ORDER BY.
  • Kolom dengan banyak nilai unik (high cardinality).
  • Kolom PRIMARY KEY dan UNIQUE otomatis memiliki index.

Jenis INDEX di MySQL

Jenis Keterangan
PRIMARY KEY Index unik utama; satu per tabel
UNIQUE Index yang memastikan nilai unik
INDEX / KEY Index biasa (non-unique)
FULLTEXT Untuk pencarian teks (InnoDB & MyISAM)
SPATIAL Untuk data geometri (InnoDB)

Membuat INDEX

Saat membuat tabel:

CREATE TABLE produk (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  harga DECIMAL(12,2) NOT NULL,
  INDEX idx_harga (harga)
);

Setelah tabel dibuat:

CREATE INDEX idx_nama_produk ON produk (nama);

Index komposit (beberapa kolom):

CREATE INDEX idx_status_total ON pesanan (status, total);

Urutan kolom penting: index (status, total) bisa dipakai untuk query yang memfilter status saja, atau status + total, tetapi tidak untuk total saja.

Unique index:

CREATE UNIQUE INDEX idx_email ON pelanggan (email);

Fulltext index:

CREATE FULLTEXT INDEX idx_deskripsi ON produk (deskripsi);

Query fulltext:

SELECT * FROM produk
WHERE MATCH(deskripsi) AGAINST('gaming' IN NATURAL LANGUAGE MODE);

Melihat INDEX pada Tabel

SHOW INDEX FROM produk;

Menghapus INDEX

DROP INDEX idx_harga ON produk;

Atau via ALTER TABLE:

ALTER TABLE produk DROP INDEX idx_harga;

Kapan INDEX Tidak Berguna?

Index bisa tidak dipakai (atau bahkan memperlambat) jika:

  • Tabel sangat kecil (full scan lebih cepat).
  • Kolom memiliki low cardinality (sedikit nilai unik, misalnya kolom gender).
  • Query menggunakan fungsi pada kolom: WHERE YEAR(dibuat_pada) = 2022 tidak memakai index pada dibuat_pada. Gunakan rentang: WHERE dibuat_pada >= '2022-01-01' AND dibuat_pada < '2023-01-01'.
  • Query menggunakan LIKE '%kata' (wildcard di awal) — index tidak bisa dipakai.
  • Query memilih sebagian besar baris tabel — optimizer mungkin memilih full scan.

Melihat Apakah Index Dipakai: EXPLAIN

EXPLAIN SELECT * FROM produk WHERE harga > 500000;

Perhatikan kolom type dan key:

  • type = ALL → full table scan (buruk untuk tabel besar).
  • type = ref / range / eq_ref → index dipakai.
  • key → nama index yang dipakai.

Tips: Gunakan EXPLAIN sebelum dan sesudah menambah index untuk melihat dampaknya.

Biaya INDEX

Index mempercepat SELECT tetapi memperlambat INSERT, UPDATE, dan DELETE karena index juga harus diperbarui. Jangan membuat index berlebihan — buat hanya untuk kolom yang benar-benar sering di-query.


STORED PROCEDURE

Stored Procedure adalah kumpulan perintah SQL yang disimpan di server dan bisa dipanggil dengan CALL. Berguna untuk:

  • Menyederhanakan logika kompleks.
  • Mengurangi lalu lintas jaringan (satu panggilan menggantikan banyak query).
  • Menyentralisasi logika bisnis.

Membuat STORED PROCEDURE

DELIMITER //

CREATE PROCEDURE nama_prosedur(parameter)
BEGIN
  -- perintah SQL
END //

DELIMITER ;

Mengapa DELIMITER? MySQL menggunakan ; sebagai pemisah perintah. Di dalam procedure ada banyak ;, jadi kita ubah sementara delimiter menjadi // agar MySQL tidak berhenti di ; pertama.

Contoh 1: Procedure Tanpa Parameter

DELIMITER //

CREATE PROCEDURE sp_semua_produk()
BEGIN
  SELECT id, nama, harga, stok FROM produk ORDER BY nama;
END //

DELIMITER ;

Memanggil:

CALL sp_semua_produk();

Contoh 2: Procedure dengan Parameter IN

DELIMITER //

CREATE PROCEDURE sp_produk_by_harga_min(IN harga_min DECIMAL(12,2))
BEGIN
  SELECT id, nama, harga
  FROM produk
  WHERE harga >= harga_min
  ORDER BY harga;
END //

DELIMITER ;

Memanggil:

CALL sp_produk_by_harga_min(500000);

Contoh 3: Procedure dengan Parameter OUT

DELIMITER //

CREATE PROCEDURE sp_total_penjualan(
  IN p_status VARCHAR(20),
  OUT p_total DECIMAL(12,2)
)
BEGIN
  SELECT IFNULL(SUM(total), 0) INTO p_total
  FROM pesanan
  WHERE status = p_status;
END //

DELIMITER ;

Memanggil:

CALL sp_total_penjualan('selesai', @total);
SELECT @total;

Contoh 4: Procedure dengan Logika IF dan Transaksi

DELIMITER //

CREATE PROCEDURE sp_buat_pesanan(
  IN p_pelanggan_id INT UNSIGNED,
  IN p_produk_id INT UNSIGNED,
  IN p_jumlah INT,
  OUT p_pesanan_id INT UNSIGNED
)
BEGIN
  DECLARE v_harga DECIMAL(12,2);
  DECLARE v_stok INT;

  -- Cek stok dan ambil harga
  SELECT harga, stok INTO v_harga, v_stok
  FROM produk WHERE id = p_produk_id;

  IF v_stok < p_jumlah THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Stok tidak mencukupi';
  END IF;

  START TRANSACTION;

  -- Buat header pesanan
  INSERT INTO pesanan (pelanggan_id, total, status)
  VALUES (p_pelanggan_id, v_harga * p_jumlah, 'baru');

  SET p_pesanan_id = LAST_INSERT_ID();

  -- Buat detail pesanan
  INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah, harga_satuan)
  VALUES (p_pesanan_id, p_produk_id, p_jumlah, v_harga);

  -- Kurangi stok
  UPDATE produk SET stok = stok - p_jumlah WHERE id = p_produk_id;

  COMMIT;
END //

DELIMITER ;

Memanggil:

CALL sp_buat_pesanan(1, 2, 3, @pesanan_id);
SELECT @pesanan_id;

Melihat Daftar STORED PROCEDURE

SHOW PROCEDURE STATUS WHERE Db = 'toko_online';

Melihat definisi:

SHOW CREATE PROCEDURE sp_semua_produk;

Menghapus STORED PROCEDURE

DROP PROCEDURE IF EXISTS sp_semua_produk;

FUNCTION — Procedure yang Mengembalikan Nilai

Function mirip stored procedure, tetapi wajib mengembalikan satu nilai dan bisa dipakai di dalam query SELECT.

Membuat FUNCTION

DELIMITER //

CREATE FUNCTION fn_harga_pajak(harga DECIMAL(12,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
  RETURN harga * 1.11;
END //

DELIMITER ;

Memakai function di query:

SELECT nama, harga, fn_harga_pajak(harga) AS harga_pajak FROM produk;

Perbedaan PROCEDURE vs FUNCTION

Aspek STORED PROCEDURE FUNCTION
Nilai kembali Bisa OUT parameter, tidak wajib return Wajib RETURNS satu nilai
Dipanggil dengan CALL Di dalam SELECT / ekspresi
Bisa di query SELECT? Tidak Ya
Bisa mengandung transaksi? Ya Umumnya tidak
Kegunaan Logika bisnis, batch Perhitungan, transformasi nilai

Menghapus FUNCTION

DROP FUNCTION IF EXISTS fn_harga_pajak;

TRIGGER — Aksi Otomatis pada Event

Trigger adalah kode yang otomatis dijalankan saat terjadi INSERT, UPDATE, atau DELETE pada tabel.

Jenis Trigger

  • BEFORE INSERT / AFTER INSERT
  • BEFORE UPDATE / AFTER UPDATE
  • BEFORE DELETE / AFTER DELETE

Contoh 1: Trigger untuk Mencatat Perubahan Stok

CREATE TABLE log_stok (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  produk_id INT UNSIGNED NOT NULL,
  stok_lama INT NOT NULL,
  stok_baru INT NOT NULL,
  diubah_pada TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE TRIGGER trg_stok_update
AFTER UPDATE ON produk
FOR EACH ROW
BEGIN
  IF OLD.stok <> NEW.stok THEN
    INSERT INTO log_stok (produk_id, stok_lama, stok_baru)
    VALUES (OLD.id, OLD.stok, NEW.stok);
  END IF;
END //

DELIMITER ;

Setiap kali stok produk berubah, perubahan tercatat di log_stok.

Contoh 2: Trigger untuk Validasi Sebelum Insert

DELIMITER //

CREATE TRIGGER trg_validasi_harga
BEFORE INSERT ON produk
FOR EACH ROW
BEGIN
  IF NEW.harga < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Harga tidak boleh negatif';
  END IF;
END //

DELIMITER ;

Melihat Daftar TRIGGER

SHOW TRIGGERS FROM toko_online;

Menghapus TRIGGER

DROP TRIGGER IF EXISTS trg_stok_update;

Keterbatasan TRIGGER

  • Trigger tidak bisa memodifikasi tabel yang sama yang memicunya (mencegah rekursi tak terbatas).
  • Trigger menyulitkan debugging karena logika “tersembunyi”.
  • Gunakan trigger secukupnya — untuk audit log, validasi sederhana, atau sinkronisasi data.

Ringkasan

Objek Fungsi Perintah Utama
VIEW Tabel virtual dari query CREATE VIEW, DROP VIEW
INDEX Mempercepat pencarian CREATE INDEX, DROP INDEX, EXPLAIN
STORED PROCEDURE Kumpulan SQL yang bisa dipanggil CREATE PROCEDURE, CALL
FUNCTION Seperti procedure, mengembalikan nilai CREATE FUNCTION, dipakai di SELECT
TRIGGER Aksi otomatis pada event tabel CREATE TRIGGER, DROP TRIGGER

Poin penting:

  • VIEW menyederhanakan query kompleks dan bisa membatasi akses kolom.
  • INDEX mempercepat SELECT tetapi memperlambat INSERT/UPDATE/DELETE — gunakan secukupnya.
  • Gunakan EXPLAIN untuk memeriksa apakah index dipakai.
  • STORED PROCEDURE dan FUNCTION menyimpan logika di server; FUNCTION bisa dipakai di SELECT.
  • TRIGGER berguna untuk audit dan validasi, tetapi jangan berlebihan.

Pada artikel berikutnya, kita akan membahas Manajemen User & Hak Akses — mengelola akun dan privilege di MySQL.

Top