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 tabelpelanggan,produk,pesanan, dandetail_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
JOINatau agregasi umumnya read-only. Untuk view yang kompleks, gunakanWITH CHECK OPTIONjika 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, danORDER BY. - Kolom dengan banyak nilai unik (high cardinality).
- Kolom
PRIMARY KEYdanUNIQUEotomatis 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) = 2022tidak memakai index padadibuat_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
EXPLAINsebelum 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 INSERTBEFORE UPDATE/AFTER UPDATEBEFORE 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
SELECTtetapi memperlambatINSERT/UPDATE/DELETE— gunakan secukupnya. - Gunakan
EXPLAINuntuk memeriksa apakah index dipakai. - STORED PROCEDURE dan FUNCTION menyimpan logika di server;
FUNCTIONbisa dipakai diSELECT. - 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.