Proyek: Skema dan Data

Proyek penutup jalur ini membangun satu basis data laporan dari awal sampai siap ditala. Topik pertama merancang skema basis data laporan dari kebutuhan query, mengisi datanya dari basis data operasional sakila, lalu memverifikasi isi beban kerjanya. Basis data yang dihasilkan bernama laporan_sewa, berisi satu tabel sewa dengan 16044 baris yang dirangkum dari tujuh tabel sakila. Setelah topik ini, beban kerja yang realistis dan dapat diulang siap dipakai pada topik penalaan berbasis EXPLAIN.

Kebutuhan proyek#

Basis data contoh sakila memodelkan basis data operasional, yaitu tempat transaksi harian dicatat satu per satu. Basis data operasional memang dirancang untuk pencatatan, tetapi laporan bulanan yang dibaca manajemen jarang cocok diambil langsung dari sana karena query laporan memerlukan penggabungan banyak tabel dan pemindaian besar. Pola yang lazim pada aplikasi nyata adalah menyalin data yang sudah jadi ke dalam satu tabel laporan yang dirancang khusus untuk dibaca, sehingga query laporan tidak mengganggu basis data operasional. Pola yang sama muncul pada topik replikasi, di mana pembacaan berat dipisahkan dari penulisan melalui server salinannya.

Proyek ini ditetapkan dari tiga kebutuhan laporan berikut.

Ketiga kebutuhan itu sengaja dipilih karena masing-masing mewakili satu bentuk beban kerja yang berbeda, yaitu jangkauan tanggal, pencarian teks dengan pengurutan, dan agregasi penuh. Penanganannya memerlukan indeks yang berbeda pula, sehingga topik penalaan nanti memiliki tiga ronde perbaikan yang sebab dan akibatnya jelas.

Rancangan tabel#

Satu tabel sewa cukup untuk ketiga kebutuhan karena setiap barisnya sudah merupakan satu peristiwa sewa yang lengkap dengan konteksnya. Nama pelanggan, judul film, dan nama kategori disalin sebagai teks ke dalam tabel ini, bukan disimpan sebagai rujukan ke tabel lain. Langkah penggandaan seperti ini disebut denormalisasi, yaitu penyengajaan mengulang data agar query pembacaan tidak perlu bergabung lagi. Biayanya jelas, yaitu data yang sama tersimpan di beberapa tempat dan penulisannya lebih mahal. Untungnya sesuai dengan peruntukan tabel laporan, yaitu pembacaan cepat tanpa join.

Tabel ini sengaja tidak diberi indeks secondary apa pun selain primary key. Keputusan itu bukan kelalaian, melainkan titik berangkat penalaan, karena indeks yang tepat ditambahkan pada ronde demi ronde proyek ini sambil mengamati pengaruhnya melalui EXPLAIN. Dengan demikian, perbaikan yang terjadi benar-benar terlihat dari nol.

Berkas berikut membangun kerangka basis data ini dari nol. Opsi STATS_SAMPLE_PAGES=1024 menjaga kestabilan angka perkiraan optimizer pada tabel yang sama dari eksekusi ke eksekusi.

-- tidak-dijalankan
-- Skema basis data laporan "laporan_sewa" untuk proyek jalur MySQL.
-- Berkas ini hanya membangun kerangkanya, sedangkan pengisian datanya
-- dikerjakan pada blok tersendiri agar pernyataan JOIN-nya terbaca.

DROP DATABASE IF EXISTS laporan_sewa;
CREATE DATABASE laporan_sewa;
USE laporan_sewa;

CREATE TABLE sewa (
  sewa_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tanggal_sewa DATETIME NOT NULL,
  tanggal_kembali DATETIME NULL,
  pelanggan VARCHAR(91) NOT NULL,
  film VARCHAR(128) NOT NULL,
  kategori VARCHAR(25) NOT NULL,
  staf TINYINT UNSIGNED NOT NULL,
  jumlah DECIMAL(5,2) NOT NULL,
  tanggal_bayar DATETIME NOT NULL
) STATS_SAMPLE_PAGES=1024;

# membangun kerangka basis data laporan
mysql -u root < skema-laporan.sql

Perintah DDL melalui klien batch tidak mencetak apa pun dan berakhir dengan status sukses, sehingga tanpa blok keluaran pun kerangkanya sudah terbentuk.

Pengisian dari basis data operasional#

Pengisian memakai INSERT ... SELECT yang membaca tujuh tabel sakila sekaligus, sehingga menjadi latihan join dengan rantai yang lebih panjang daripada latihan mana pun di modul join. Rantainya dimulai dari rental yang menyimpan peristiwa sewa, lalu melebar ke empat arah, yaitu payment untuk nilai bayar, customer untuk nama pelanggan, inventory dan film untuk judul film, serta film_category dan category untuk nama kategori. Setiap pasangan digabungkan melalui kunci yang sudah dipelajari bentuknya, dan hasilnya satu baris laporan untuk setiap pasangan sewa dan pembayaran.

# mengisi tabel sewa dari basis data operasional sakila
mysql -u root laporan_sewa <<'SQL'
INSERT INTO sewa (tanggal_sewa, tanggal_kembali, pelanggan, film, kategori, staf, jumlah, tanggal_bayar)
SELECT
  r.rental_date,
  r.return_date,
  CONCAT(c.first_name, ' ', c.last_name),
  f.title,
  ca.name,
  r.staff_id,
  p.amount,
  p.payment_date
FROM sakila.rental r
JOIN sakila.payment p ON p.rental_id = r.rental_id
JOIN sakila.customer c ON c.customer_id = r.customer_id
JOIN sakila.inventory i ON i.inventory_id = r.inventory_id
JOIN sakila.film f ON f.film_id = i.film_id
JOIN sakila.film_category fc ON fc.film_id = f.film_id
JOIN sakila.category ca ON ca.category_id = fc.category_id;
SQL

Verifikasi beban kerja#

Data yang baru dimuat belum tentu dikenal optimizer dengan baik, sehingga statistik tabel diperbarui terlebih dahulu dengan ANALYZE TABLE, lalu isi tabel diverifikasi dengan penghitungan yang memanfaatkan semantik NULL.

# memperbarui statistik dan memverifikasi isi tabel
mysql -u root laporan_sewa -t -e "ANALYZE TABLE sewa; SELECT COUNT(*) AS jumlah_baris, COUNT(tanggal_kembali) AS sudah_kembali, COUNT(*) - COUNT(tanggal_kembali) AS belum_kembali FROM sewa; SELECT MIN(tanggal_sewa) AS sewa_pertama, MAX(tanggal_sewa) AS sewa_terakhir FROM sewa;"

+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| laporan_sewa.sewa | analyze | status   | OK       |
+-------------------+---------+----------+----------+
+--------------+---------------+---------------+
| jumlah_baris | sudah_kembali | belum_kembali |
+--------------+---------------+---------------+
|        16044 |         15861 |           183 |
+--------------+---------------+---------------+
+---------------------+---------------------+
| sewa_pertama        | sewa_terakhir       |
+---------------------+---------------------+
| 2005-05-24 22:53:30 | 2006-02-14 15:16:03 |
+---------------------+---------------------+

Angka-angka di atas menegaskan tiga hal. Jumlah baris 16044 sama persis dengan jumlah baris payment di sakila, karena setiap sewa pada dataset ini memiliki tepat satu pembayaran. Kolom belum_kembali bernilai 183, yaitu baris yang tanggal_kembali-nya masih NULL, dan nilai itu bisa dihitung justru karena kolom tanggal_kembali memang boleh NULL. Rentang tanggal sewa membentang hampir sembilan bulan dari Mei 2005 sampai Februari 2006, sehingga laporan bulanan pada topik penalaan memiliki cukup banyak periode untuk dipilih.

Lima baris pertama ditampilkan untuk mencontohkan bentuk data yang terbentuk.

# menampilkan contoh isi tabel laporan
mysql -u root laporan_sewa -t -e "SELECT * FROM sewa ORDER BY sewa_id LIMIT 5;"

+---------+---------------------+---------------------+---------------+--------------+----------+------+--------+---------------------+
| sewa_id | tanggal_sewa        | tanggal_kembali     | pelanggan     | film         | kategori | staf | jumlah | tanggal_bayar       |
+---------+---------------------+---------------------+---------------+--------------+----------+------+--------+---------------------+
|       1 | 2005-08-02 01:16:59 | 2005-08-03 02:41:59 | JANE BENNETT  | AMADEUS HOLY | Action   |    2 |   0.99 | 2005-08-02 01:16:59 |
|       2 | 2005-08-18 04:26:54 | 2005-08-23 06:40:54 | DEBRA NELSON  | AMADEUS HOLY | Action   |    2 |   0.99 | 2005-08-18 04:26:54 |
|       3 | 2005-06-20 20:35:28 | 2005-06-26 01:01:28 | REBECCA SCOTT | AMADEUS HOLY | Action   |    1 |   0.99 | 2005-06-20 20:35:28 |
|       4 | 2005-07-09 05:01:58 | 2005-07-18 08:17:58 | MAXINE SILVA  | AMADEUS HOLY | Action   |    2 |   3.99 | 2005-07-09 05:01:58 |
|       5 | 2005-07-30 08:02:39 | 2005-08-06 12:02:39 | BRANDY GRAVES | AMADEUS HOLY | Action   |    1 |   1.99 | 2005-07-30 08:02:39 |
+---------+---------------------+---------------------+---------------+--------------+----------+------+--------+---------------------+

Setiap kolom pada contoh tersebut sudah dalam bentuk siap laporan, misalnya nama pelanggan sudah menjadi satu teks utuh dan nilai uang sudah presisi dua desimal. Isi sakila bersifat statis, sehingga seluruh angka pada topik ini dan pada topik penalaan dapat diulang kapan pun dengan hasil yang sama.

Ringkasan#

• Tabel laporan dirancang dari kebutuhan query, dengan tiga bentuk beban kerja yang mewakili jangkauan tanggal, pencarian dengan pengurutan, dan agregasi penuh.
• Denormalisasi nama pelanggan, judul film, dan kategori membuat query pembacaan tidak perlu join lagi, dengan biaya penyimpanan ganda yang sengaja dibayar.
• Pengisian memakai INSERT ... SELECT yang menggabungkan tujuh tabel sakila, lalu isinya diverifikasi melalui ANALYZE TABLE dan penghitungan 16044 baris dengan 183 nilai NULL.
• Tabel sengaja baru memiliki primary key, sehingga pengaruh setiap indeks pada topik penalaan dapat diukur dari titik nol.

Referensi#

• INSERT … SELECT Statement — MySQL 8.0 Reference Manual — Aturan pengisian tabel dari hasil SELECT yang dipakai pada proyek ini.
• The Sakila Sample Database — MySQL Documentation — Struktur basis data contoh sakila yang menjadi sumber data proyek.
• CREATE TABLE Statement — MySQL 8.0 Reference Manual — Opsi pembuatan tabel, termasuk STATS_SAMPLE_PAGES yang menjaga kestabilan statistik.