Generated Column di MariaDB - Menjaga Nilai Turunan di Satu Tempat
Sebuah tabel menyimpan quantity dan unit_price, sementara aplikasi juga menyimpan line_total. Nilai ketiga itu terlihat praktis sampai satu jalur kode memperbarui harga tanpa menghitung ulang totalnya. Apakah aplikasi harus terus mengoordinasikan ketiga nilai tersebut, atau sebaiknya database menurunkan nilai ketiga dari dua nilai pertama?
Generated column di MariaDB menawarkan salah satu jawaban. Fitur ini menjadikan expression dalam baris yang sama sebagai bagian dari definisi tabel, sehingga pemanggil dapat membaca column bernama tanpa memberikan nilainya secara terpisah. Cara ini bisa menghilangkan satu jenis state duplikat, tetapi bukan berarti setiap perhitungan cocok dijadikan column database. Pertanyaan yang lebih tepat adalah: apakah nilai ini memiliki satu definisi stabil, hanya bergantung pada barisnya sendiri, dan patut dijaga oleh schema?
Apa yang diubah oleh generated column
MariaDB mendefinisikan generated column sebagai column yang nilainya berasal dari expression. Ini bukan field biasa yang bebas ditulis. Operasi insert atau update memberikan source column, lalu MariaDB mengevaluasi generated expression.
Fitur ini berbeda dari dua fitur schema yang mirip:
DEFAULTmemberikan nilai ketika operasi insert tidak menyertakan sebuah column. Setelah itu, column tersebut tetap dapat ditulis secara terpisah.CHECKmenerima atau menolak baris berdasarkan suatu kondisi. Fitur ini tidak menampilkan nilai turunan untuk query.- Generated column menampilkan hasil expression yang telah ditentukan dan mempertahankan keterikatannya dengan source column.
Perbedaan itu penting. Total yang dihasilkan bukan angka cache yang harus selalu diingat untuk diperbarui oleh kode aplikasi. Nilai tersebut menjadi bagian dari bentuk baris yang dideklarasikan.
Contoh kecil
Perhatikan tabel baris pesanan yang sengaja dibuat sederhana berikut:
CREATE TABLE order_lines (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
quantity INT UNSIGNED NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
line_total DECIMAL(12,2)
AS (quantity * unit_price) PERSISTENT,
PRIMARY KEY (id),
INDEX idx_line_total (line_total)
);
Aplikasi menulis quantity dan unit_price, bukan line_total yang dihitung secara terpisah:
INSERT INTO order_lines (quantity, unit_price)
VALUES (2, 19.95), (3, 7.50);
SELECT id, quantity, unit_price, line_total
FROM order_lines;
Pada MariaDB 11.8.6, syntax ini diperiksa dalam temporary table yang terisolasi. Dua hasil yang dibuat adalah 39.90 dan 22.50, sedangkan SHOW CREATE TABLE menampilkan definisi PERSISTENT sebagai STORED. Ini hanya pemeriksaan terbatas atas syntax dan perilaku, bukan bukti bahwa desain tersebut cocok atau cepat untuk setiap tabel production.
Contoh ini juga menyembunyikan pertanyaan bisnis yang nyata. Pajak, diskon, mata uang, dan aturan pembulatan dapat membuat total transaksi jauh lebih rumit daripada perkalian. Generated column hanya tepat setelah seluruh makna itu dinyatakan dengan jelas. Jangan sampai fitur ini mengubah kebijakan akuntansi yang belum selesai menjadi angka yang tampak otoritatif.
VIRTUAL dan PERSISTENT menempatkan pekerjaan secara berbeda
MariaDB mendukung dua bentuk generated column:
VIRTUALdihitung ketika di-query dan tidak disimpan sebagai data column biasa. Ini menjadi default jika tidak ada bentuk yang disebutkan.PERSISTENT, yang juga menerima aliasSTOREDdi MariaDB, menyimpan generated value di dalam tabel.
Ini bukan pilihan sederhana antara lambat dan cepat. Virtual column memindahkan evaluasi expression ke sisi pembacaan. Persistent column mengerjakan dan menyimpan perhitungan ketika source data ditulis. Biaya penyimpanan, frekuensi penulisan, frekuensi pembacaan, biaya expression, dan indexing sama-sama memengaruhi hasil. Tanpa workload yang representatif, rekomendasi performa universal hanya akan menjadi tebakan.
Ada pula pertanyaan semantik yang perlu dijawab sebelum pertanyaan performa. Jika hasil jarang dipilih dan murah untuk dihitung, virtual column dapat menghindari data tersimpan yang tidak perlu. Jika nilai sering dibaca atau expression mahal, persistent storage mungkin layak dievaluasi. Apa pun pilihannya, ukur schema dan workload yang sebenarnya alih-alih memperlakukan keyword sebagai tombol optimasi.
Index dapat digunakan, tetapi periksa query plan
Dokumentasi MariaDB saat ini menyatakan bahwa index dapat didefinisikan pada generated column VIRTUAL maupun PERSISTENT. Optimizer dapat mempertimbangkan index tersebut seperti index pada column biasa. Kata “dapat mempertimbangkan” penting: tersedianya index bukan janji bahwa setiap query akan memakainya.
Periksa plan untuk bentuk query yang memang penting:
EXPLAIN
SELECT id, quantity, unit_price, line_total
FROM order_lines
WHERE line_total = 39.90;
Referensi EXPLAIN menjelaskan field seperti possible_keys, key, access type, dan estimasi rows. Dalam verifikasi lokal yang kecil, MariaDB melaporkan access type ref dengan idx_line_total. Karena hanya ada dua baris, hasil itu sekadar menunjukkan bahwa index memenuhi syarat untuk query tersebut; hasil itu tidak memberi informasi berguna tentang elapsed time atau manfaat di production.
EXPLAIN menunjukkan plan yang dimaksudkan optimizer, bukan benchmark workload. Statement ANALYZE MariaDB melangkah lebih jauh dengan menjalankan statement dan menambahkan informasi eksekusi yang teramati. Pada data nyata, pemeriksaan plan dan pengukuran sebaiknya dilakukan bersama.
Detail versi dapat mengubah cara expression dicocokkan
Menggunakan nama generated column adalah bentuk query yang paling jelas dan paling mudah dipakai lintas versi MariaDB:
SELECT * FROM order_lines WHERE line_total >= 100.00;
Mulai MariaDB 11.8, optimizer juga dapat mengenali expression yang persis sama di balik indexed virtual column dalam clause WHERE, lalu menggantinya dengan generated column. Dokumentasi optimizer menyatakan bahwa expression tersebut harus sama persis dengan definisi column. Sebelum 11.8, expression substitution ini belum tersedia.
Batas versi ini menjadi alasan untuk tidak menyembunyikan asumsi di dalam query aplikasi. Menyebut generated column secara langsung mengomunikasikan interface schema yang dimaksud, tetap bekerja pada rilis lama yang sudah mendukung column tersebut, dan tidak bergantung pada pencocokan expression. Jika aplikasi sengaja menggunakan expression substitution, versi minimum MariaDB dan query plan perlu menjadi bagian dari pemeriksaan deployment.
Expression memiliki batas yang nyata
Generated column dirancang untuk nilai yang dapat diturunkan dari barisnya sendiri. MariaDB tidak mendukung subquery dalam generated expression, sedangkan data di luar baris bukan dependency yang sesuai. Stored function juga tidak didukung di sana. Batas ini mencegah generated column berubah menjadi mesin perhitungan lintas tabel yang tersembunyi.
Sebagian besar expression deterministic yang legal dan banyak built-in function didukung, tetapi hasil persistent atau indexed membutuhkan konsistensi yang lebih kuat. MariaDB memperingatkan bahwa expression yang hasilnya berubah mengikuti pengaturan sql_mode tertentu dapat membuat nilai yang disimpan atau di-index berbeda dari evaluasi berikutnya. Dokumentasinya memberi character padding dan unsigned subtraction sebagai contoh konkret, serta merekomendasikan operasi eksplisit seperti RTRIM() atau CAST() bila diperlukan.
Ada pula batas struktural. Generated column tidak dapat menjadi primary key MariaDB, meskipun kedua bentuk generated column dapat memiliki index biasa. Beberapa kombinasi foreign key dan operasi ALTER TABLE juga memiliki batas tambahan. Karena itu, referensi yang tepat untuk versi MariaDB pada deployment perlu diperiksa sebelum migration, bukan hanya setelah development schema berhasil.
Kapan expression biasa mungkin lebih baik
Generated column mengurangi satu jenis duplikasi, tetapi juga menambah keterikatan pada schema. Expression biasa dalam query atau aplikasi mungkin lebih jelas ketika:
- nilai tersebut hanya muncul pada satu laporan atau endpoint;
- aturannya sering berubah dan mengikuti rilis aplikasi, bukan database migration;
- perhitungannya bergantung pada baris lain, tabel lain, layanan eksternal, atau request context;
- hasil harus mempertahankan aturan historis yang digunakan ketika suatu peristiwa terjadi;
- portabilitas database lebih penting daripada satu fitur schema khusus MariaDB.
Kasus historis perlu mendapat perhatian khusus. Misalnya, pembaruan harga di kemudian hari seharusnya tidak mengubah total invoice lama. Nilai yang dihasilkan dari price column saat ini akan menghitung ulang expression terkini dengan tepat, padahal itu mungkin justru makna historis yang salah. Transaction field yang immutable atau desain ledger terpisah dapat menjadi pilihan yang lebih jujur.
Urutan pengambilan keputusan yang hati-hati
- Tulis aturannya dengan bahasa biasa dan tentukan apakah aturan itu hanya bergantung pada baris saat ini.
- Pilih data type, perilaku null, pembulatan, dan perilaku overflow secara sengaja.
- Tanyakan apakah nilai itu membutuhkan nama column stabil untuk beberapa pemanggil.
- Pilih
VIRTUALatauPERSISTENTberdasarkan lokasi computation dan storage yang diinginkan, bukan berdasarkan slogan. - Tambahkan index hanya untuk access pattern yang nyata, lalu periksa
EXPLAINdan ukur dengan data representatif. - Uji migration pada versi MariaDB dan
sql_modeyang sama dengan deployment. - Dokumentasikan apakah nilai itu mewakili derivation saat ini atau fakta historis.
Kesimpulan
Generated column MariaDB dapat memberikan satu tempat di tingkat schema untuk satu aturan dalam baris. Fitur ini dapat mencegah jalur aplikasi menulis source value dan derived value yang saling bertentangan, sekaligus menampilkan nama yang dapat di-index untuk sebuah expression. Itu semua merupakan sifat yang berguna, tetapi bukan bukti otomatis bahwa desain menjadi lebih sederhana atau lebih cepat.
Pilihan yang lebih aman dimulai dari makna: satu expression stabil, berdasarkan baris saat ini, dengan hasil yang berguna bagi beberapa query atau constraint. Setelah itu barulah storage, indexing, kompatibilitas versi, dan pengukuran dipertimbangkan. Jika aturan bersifat kontekstual, historis, atau lintas tabel, menempatkannya di luar generated column mungkin menjadi desain yang lebih akurat.
