8.1 Mengapa denormalisasi diperlukan?

Operasi yang paling mahal secara komputasi antara tabel-tabel besar adalah penggabungan. Oleh karena itu, jika dalam satu kueri perlu "ventilasi" beberapa tabel yang terdiri dari jutaan baris, DBMS akan menghabiskan banyak waktu untuk pemrosesan tersebut.

Pengguna saat ini bisa menjauh untuk minum kopi. Interaktivitas pemrosesan praktis menghilang dan mendekati pemrosesan batch. Lebih buruk lagi, dalam mode batch, pengguna menerima semua data yang diminta sehari sebelumnya di pagi hari dan dengan tenang bekerja dengannya, menyiapkan permintaan baru untuk malam itu.

Untuk menghindari situasi gabungan yang berat, tabel didenormalisasi. Tapi bagaimanapun juga tidak. Ada beberapa aturan yang memungkinkan Anda menganggap tabel yang didenormalisasi secara transaksional sebagai "dinormalisasi" menurut aturan untuk membuat tabel untuk gudang data.

Ada dua skema utama yang dianggap "normal" dalam pemrosesan analitik: "kepingan salju" dan "bintang". Nama mencerminkan esensi dengan baik dan mengikuti langsung dari gambar tabel terkait.

Dalam kedua kasus, yang disebut tabel fakta adalah elemen sentral dari skema, yang berisi peristiwa, transaksi, dokumen, dan hal menarik lainnya yang menarik bagi analis. Tetapi jika dalam database transaksional satu dokumen "dioleskan" di beberapa tabel (setidaknya dua: header dan baris konten), maka di tabel fakta satu dokumen, lebih tepatnya, masing-masing barisnya atau kumpulan baris yang dikelompokkan, sesuai ke satu rekor.

Ini dapat dilakukan dengan mendenormalisasi kedua tabel di atas.

8.2 Contoh denormalisasi

Sekarang Anda dapat mengevaluasi seberapa mudah DBMS mengeksekusi kueri, misalnya, dari jenis berikut: untuk menentukan volume penjualan tepung kepada klien Pirozhki LLC dan Vatrushki CJSC untuk periode tersebut.

Dalam database transaksional yang dinormalisasi:


SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

Dalam database analitik:


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Alih-alih gabungan berat antara dua tabel dokumen dan komposisinya dengan jutaan baris, DBMS bekerja langsung dengan tabel fakta dan gabungan ringan dengan tabel tambahan kecil, yang juga dapat Anda lakukan tanpa mengetahui pengidentifikasi.

Java webinar 

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Mari kembali ke skema "bintang" dan "kepingan salju". Di balik layar gambar pertama terdapat tabel pelanggan, grup mereka, toko, penjual, dan bahkan barang. Saat didenormalisasi, tabel ini, yang disebut dimensi, juga digabungkan ke tabel fakta. Jika tabel fakta mengacu pada tabel dimensi yang memiliki tautan ke dimensi lain (dimensi tingkat kedua ke atas), maka skema seperti itu disebut "kepingan salju".

Seperti yang Anda lihat, untuk kueri yang menyertakan pemfilteran menurut grup klien, Anda harus membuat koneksi tambahan.


SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

Dalam hal ini, denormalisasi dapat berlanjut dan menurunkan dimensi tingkat kedua ke tingkat pertama, sehingga mempermudah kueri tabel fakta.

Skema di mana tabel fakta hanya mengacu pada dimensi yang tidak memiliki level kedua disebut skema bintang. Jumlah tabel pengukuran sesuai dengan jumlah "sinar" di bintang.

Skema Bintang sepenuhnya menghilangkan hierarki dimensi dan kebutuhan untuk menggabungkan tabel yang sesuai dalam satu kueri.


SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

Kelemahan dari denormalisasi selalu redundansi , yang menyebabkan peningkatan ukuran database baik dalam aplikasi transaksional maupun analitik. Mari kita hitung perkiraan delta pada contoh konversi "kepingan salju" menjadi "bintang" di atas.

Dalam beberapa DBMS, seperti Oracle, tidak ada tipe integer khusus pada level definisi skema database, Anda harus menggunakan tipe boolean generik numeric(N), di mana N adalah jumlah bit yang disimpan. Ukuran penyimpanan nomor tersebut dihitung menggunakan rumus khusus yang diberikan dalam dokumentasi untuk penyimpanan data fisik, dan, sebagai aturan, melebihi jenis tingkat rendah seperti "integer 16 bit" sebanyak 1-3 byte.

Misalkan tabel penjualan tidak menggunakan kompresi data dan berisi sekitar 500 juta baris, dan jumlah grup pelanggan sekitar 1000. Dalam hal ini, kita dapat menggunakan bilangan bulat pendek (shortint, smallint) yang menempati 2 byte sebagai pengidentifikasi tipe id_customer_group.

Kami akan berasumsi bahwa DBMS kami mendukung tipe integer dua byte (misalnya, PostgreSQL, SQL Server, Sybase, dan lainnya). Kemudian menambahkan kolom yang sesuai id_customer_groupke tabel penjualan akan menambah ukurannya setidaknya 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Kapan denormalisasi diperlukan?

Mari kita lihat beberapa situasi umum di mana denormalisasi dapat bermanfaat.

Sejumlah besar tabel bergabung

Dalam kueri ke database yang sepenuhnya dinormalisasi, Anda sering kali harus menggabungkan hingga selusin atau bahkan lebih banyak tabel. Dan setiap koneksi adalah operasi yang sangat intensif sumber daya. Akibatnya, permintaan tersebut menghabiskan sumber daya server dan dilakukan dengan lambat.

Dalam situasi seperti itu, ini dapat membantu:

  • denormalisasi dengan mengurangi jumlah tabel. Lebih baik menggabungkan menjadi satu beberapa tabel yang berukuran kecil, berisi informasi yang jarang berubah (seperti yang sering mereka katakan, konstanta bersyarat atau referensi), dan informasi yang terkait erat dalam arti.
  • Secara umum, jika Anda perlu menggabungkan lebih dari lima atau enam tabel dalam sejumlah besar kueri, Anda harus mempertimbangkan denormalisasi database.
  • Denormalisasi dengan menambahkan bidang tambahan ke salah satu tabel. Dalam hal ini, muncul redundansi data, diperlukan tindakan tambahan untuk menjaga integritas database.

Nilai perkiraan

Seringkali, kueri lambat dan menghabiskan banyak sumber daya, di mana beberapa perhitungan rumit dilakukan, terutama saat menggunakan pengelompokan dan fungsi agregat (Jumlah, Maks, dll.). Terkadang masuk akal untuk menambahkan 1-2 kolom tambahan ke tabel yang berisi data kalkulasi yang sering digunakan (dan sulit dihitung).

Misalkan Anda ingin menentukan biaya total setiap pesanan. Untuk melakukan ini, Anda harus terlebih dahulu menentukan biaya setiap produk (sesuai dengan rumus "jumlah unit produk" * "harga satuan produk" - diskon). Setelah itu, Anda perlu mengelompokkan biaya berdasarkan pesanan.

Mengeksekusi kueri ini cukup rumit dan, jika database menyimpan informasi tentang pesanan dalam jumlah besar, dapat memakan waktu lama. Alih-alih menjalankan kueri seperti itu, Anda dapat menentukan biayanya pada tahap melakukan pemesanan dan menyimpannya di kolom terpisah dari tabel pesanan. Dalam hal ini, untuk mendapatkan hasil yang diinginkan, cukup mengekstrak nilai yang telah dihitung sebelumnya dari kolom ini.

Membuat kolom yang berisi nilai yang dihitung sebelumnya menghemat banyak waktu saat menjalankan kueri, tetapi mengharuskan Anda memperbarui data di kolom tersebut tepat waktu.

pinggiran panjang

Jika kita memiliki tabel besar dalam database yang berisi bidang panjang (Blob, Long, dll.), Maka kita dapat mempercepat eksekusi kueri ke tabel tersebut jika kita memindahkan bidang panjang ke tabel terpisah. Kami ingin, katakanlah, membuat katalog foto di database, termasuk menyimpan foto itu sendiri di bidang blob (kualitas profesional, resolusi tinggi, dan ukuran yang sesuai). Dari sudut pandang normalisasi, struktur tabel berikut ini benar sekali:

  • foto ID
  • ID penulis
  • ID Model Kamera
  • foto itu sendiri (bidang gumpalan)

Dan sekarang bayangkan berapa lama kueri akan berjalan, menghitung jumlah foto yang diambil oleh penulis mana pun ...

Solusi yang tepat (walaupun melanggar prinsip normalisasi) dalam situasi seperti itu adalah membuat tabel lain yang hanya terdiri dari dua bidang - ID foto dan bidang gumpalan dengan foto itu sendiri. Kemudian pilihan dari tabel utama (di mana tidak ada lagi bidang gumpalan besar) akan langsung masuk, tetapi ketika kita ingin melihat fotonya sendiri, mari kita tunggu ...

Bagaimana cara menentukan kapan denormalisasi dibenarkan?

8.4 Pro dan kontra denormalisasi

Salah satu cara untuk menentukan apakah langkah-langkah tertentu dibenarkan adalah dengan melakukan analisis dalam hal biaya dan kemungkinan keuntungan. Berapa biaya model data yang didenormalisasi?

Tentukan persyaratan (apa yang ingin kita capai) → tentukan persyaratan data (apa yang perlu kita ikuti) → temukan langkah minimum yang memenuhi persyaratan tersebut → hitung biaya implementasi → implementasi.

Biaya mencakup aspek fisik seperti ruang disk, sumber daya yang diperlukan untuk mengelola struktur ini, dan peluang yang hilang karena penundaan waktu yang terkait dengan pemeliharaan proses ini. Anda harus membayar untuk denormalisasi. Database yang didenormalisasi meningkatkan redundansi data, yang dapat meningkatkan kinerja tetapi membutuhkan lebih banyak upaya untuk mengontrol data terkait. Proses pembuatan aplikasi akan menjadi lebih sulit, karena data akan berulang dan lebih sulit untuk dilacak. Selain itu, penerapan integritas referensial tidak mudah - data terkait dibagi ke dalam tabel yang berbeda.

Manfaatnya mencakup kinerja kueri yang lebih cepat dan kemampuan untuk mendapatkan respons yang lebih cepat. Anda juga dapat memperoleh keuntungan lain, termasuk peningkatan throughput, kepuasan pelanggan, dan produktivitas, serta penggunaan alat pengembang eksternal yang lebih efisien.

Tingkat Permintaan dan Konsistensi Performa

Misalnya, 72% dari 1.000 kueri yang dihasilkan setiap hari oleh perusahaan adalah kueri tingkat ringkasan, bukan kueri perincian. Saat menggunakan tabel ringkasan, kueri dijalankan dalam waktu sekitar 6 detik, bukan 4 menit, sehingga mengurangi waktu pemrosesan 3.000 menit. Bahkan setelah menyesuaikan 100 menit yang harus digunakan untuk mempertahankan tabel pivot setiap minggu, ini menghemat 2.500 menit per minggu, yang membenarkan pembuatan tabel pivot. Seiring waktu, mungkin saja sebagian besar kueri tidak ditujukan ke data ringkasan, tetapi ke data detail. Semakin sedikit kueri yang menggunakan tabel ringkasan, semakin mudah untuk menghapusnya tanpa memengaruhi proses lainnya.

Dan…

Kriteria yang tercantum di atas bukan satu-satunya yang perlu dipertimbangkan saat memutuskan apakah akan mengambil langkah pengoptimalan selanjutnya. Faktor lain perlu dipertimbangkan, termasuk prioritas bisnis dan kebutuhan pengguna akhir. Pengguna harus memahami bagaimana, dari sudut pandang teknis, arsitektur sistem dipengaruhi oleh kebutuhan pengguna yang menginginkan semua permintaan diselesaikan dalam beberapa detik. Cara termudah untuk mencapai pemahaman ini adalah dengan menguraikan biaya yang terkait dengan pembuatan dan pengelolaan tabel tersebut.

8.5 Bagaimana menerapkan denormalisasi secara kompeten.

Simpan tabel terperinci

Agar tidak membatasi kemampuan database yang penting bagi bisnis, perlu mengadopsi strategi koeksistensi, bukan penggantian, yaitu menyimpan tabel terperinci untuk analisis mendalam, menambahkan struktur yang didenormalisasi ke dalamnya. Misalnya, hit counter. Untuk bisnis, Anda perlu mengetahui jumlah kunjungan ke halaman web. Tetapi untuk analisis (berdasarkan periode, menurut negara...) kemungkinan besar kami membutuhkan data terperinci - sebuah tabel dengan informasi tentang setiap kunjungan.

Menggunakan pemicu

Dimungkinkan untuk mendenormalisasi struktur database dan tetap menikmati manfaat normalisasi dengan menggunakan pemicu database untuk menjaga integritas integritydata duplikat.

Misalnya, saat menambahkan kolom kalkulasi, setiap kolom tempat kolom kalkulasi bergantung, ditutup dengan pemicu yang memanggil satu prosedur tersimpan (ini penting!), Yang menulis data yang diperlukan ke kolom kalkulasi. Hanya perlu untuk tidak melewatkan salah satu kolom yang menjadi sandaran bidang terhitung.

Dukungan perangkat lunak

Jika Anda tidak menggunakan pemicu bawaan dan prosedur tersimpan, maka pengembang aplikasi harus memastikan konsistensi data dalam database yang didenormalisasi.

Dengan analogi pemicu, harus ada satu fungsi yang memperbarui semua bidang yang bergantung pada bidang yang sedang diubah.

kesimpulan

Saat mendenormalisasi, penting untuk menjaga keseimbangan antara meningkatkan kecepatan database dan meningkatkan risiko data yang tidak konsisten, antara membuat hidup lebih mudah bagi programmer untuk menulis Select-s, dan memperumit tugas mereka yang menyediakan populasi database dan pembaruan data. Oleh karena itu, denormalisasi basis data perlu dilakukan dengan sangat hati-hati, sangat selektif, hanya jika sangat diperlukan.

Jika tidak mungkin menghitung pro dan kontra dari denormalisasi sebelumnya, maka pada awalnya perlu mengimplementasikan model dengan tabel yang dinormalisasi, dan baru kemudian, untuk mengoptimalkan kueri yang bermasalah, lakukan denormalisasi.

Penting untuk memperkenalkan denormalisasi secara bertahap dan hanya untuk kasus-kasus di mana ada pengulangan pengambilan data terkait dari tabel yang berbeda. Ingat, saat menduplikasi data, jumlah record akan bertambah, tetapi jumlah pembacaan akan berkurang. Juga nyaman untuk menyimpan data yang dihitung dalam kolom untuk menghindari pemilihan agregat yang tidak perlu.