Denormalisasi jadual dalam pangkalan data

8.1 Mengapakah penyahnormalan diperlukan?

Operasi yang paling mahal secara pengiraan antara jadual besar ialah gabungan. Sehubungan itu, jika dalam satu pertanyaan perlu "mengudarakan" beberapa jadual yang terdiri daripada berjuta-juta baris, maka DBMS akan menghabiskan banyak masa untuk pemprosesan sedemikian.

Pengguna pada masa ini boleh beralih untuk minum kopi. Interaktiviti pemprosesan secara praktikal hilang dan menghampiri pemprosesan kelompok. Lebih teruk lagi, dalam mod kelompok, pengguna menerima semua data yang diminta sehari sebelumnya pada waktu pagi dan dengan tenang bekerja dengan mereka, menyediakan permintaan baharu untuk malam itu.

Untuk mengelakkan situasi cantuman berat, jadual dinyahnormalkan. Tetapi tidak pula. Terdapat beberapa peraturan yang membolehkan anda mempertimbangkan jadual nyahnormal transaksi sebagai "dinormalkan" mengikut peraturan untuk membina jadual untuk gudang data.

Terdapat dua skim utama yang dianggap "normal" dalam pemprosesan analisis: "kepingan salji" dan "bintang". Nama-nama mencerminkan intipati dengan baik dan mengikuti terus dari gambar jadual yang berkaitan.

Dalam kedua-dua kes, apa yang dipanggil jadual fakta ialah unsur utama skema, yang mengandungi peristiwa, urus niaga, dokumen dan perkara menarik lain yang menarik minat penganalisis. Tetapi jika dalam pangkalan data transaksi satu dokumen "dioleskan" merentasi beberapa jadual (sekurang-kurangnya dua: pengepala dan baris kandungan), maka dalam jadual fakta satu dokumen, lebih tepat lagi, setiap barisnya atau satu set baris terkumpul, sepadan kepada satu rekod.

Ini boleh dilakukan dengan menyahnormalkan kedua-dua jadual di atas.

8.2 Contoh nyahnormalisasi

Kini anda boleh menilai sejauh mana lebih mudah untuk DBMS melaksanakan pertanyaan, contohnya, jenis berikut: untuk menentukan jumlah jualan tepung kepada pelanggan Pirozhki LLC dan Vatrushki CJSC untuk tempoh tersebut.

Dalam pangkalan data transaksi yang dinormalkan:

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 pangkalan data analisis:

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

Daripada gabungan berat antara dua jadual dokumen dan komposisinya dengan berjuta-juta baris, DBMS mendapat kerja langsung dengan jadual fakta dan cantuman ringan dengan jadual tambahan kecil, yang anda juga boleh lakukan tanpa mengetahui pengecamnya.

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 kita kembali kepada skim "bintang" dan "kepingan salji". Di sebalik tabir gambar pertama adalah meja pelanggan, kumpulan mereka, kedai, penjual dan, sebenarnya, barangan. Apabila dinyahnormalkan, jadual ini, dipanggil dimensi, juga disambungkan ke jadual fakta. Jika jadual fakta merujuk kepada jadual dimensi yang mempunyai pautan ke dimensi lain (dimensi tahap kedua dan ke atas), maka skema sedemikian dipanggil "snowflake".

Seperti yang anda lihat, untuk pertanyaan yang termasuk penapisan mengikut kumpulan pelanggan, anda perlu membuat sambungan 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 kes ini, penyahnormalan boleh diteruskan dan menjatuhkan dimensi peringkat kedua kepada yang pertama, menjadikannya lebih mudah untuk menanyakan jadual fakta.

Skema di mana jadual fakta hanya merujuk kepada dimensi yang tidak mempunyai tahap kedua dipanggil skema bintang. Bilangan jadual ukuran sepadan dengan bilangan "sinar" dalam bintang.

Skema Bintang menghapuskan sepenuhnya hierarki dimensi dan keperluan untuk menyertai jadual yang sepadan dalam satu pertanyaan.

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

Kelemahan penyahnormalan sentiasa redundansi , yang menyebabkan peningkatan dalam saiz pangkalan data dalam kedua-dua aplikasi transaksi dan analisis. Mari kita hitung anggaran delta pada contoh di atas untuk menukar "kepingan salji" kepada "bintang".

Dalam sesetengah DBMS, seperti Oracle, tiada jenis integer khas pada peringkat takrifan skema pangkalan data, anda mesti menggunakan jenis boolean generik numeric(N), di mana N ialah bilangan bit yang disimpan. Saiz storan nombor sedemikian dikira menggunakan formula khas yang diberikan dalam dokumentasi untuk penyimpanan data fizikal, dan, sebagai peraturan, ia melebihi jenis peringkat rendah seperti "16 bit integer" sebanyak 1-3 bait.

id_customer_groupKatakan jadual jualan tidak menggunakan pemampatan data dan mengandungi kira-kira 500 juta baris, dan bilangan kumpulan pelanggan ialah kira-kira 1000. Dalam kes ini, kita boleh menggunakan integer pendek (shortint, smallint) yang menduduki 2 bait sebagai jenis pengecam .

Kami akan menganggap bahawa DBMS kami menyokong jenis integer dua bait (contohnya, PostgreSQL, SQL Server, Sybase dan lain-lain). Kemudian menambah lajur yang sepadan id_customer_grouppada jadual jualan akan meningkatkan saiznya sekurang-kurangnya 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Bilakah penyahnormalan diperlukan?

Mari lihat beberapa situasi biasa di mana penyahnormalan boleh berguna.

Bilangan besar gabungan meja

Dalam pertanyaan kepada pangkalan data yang dinormalisasi sepenuhnya, anda selalunya perlu menyertai sehingga sedozen atau lebih banyak jadual. Dan setiap sambungan adalah operasi yang sangat intensif sumber. Akibatnya, permintaan sedemikian menggunakan sumber pelayan dan dilakukan dengan perlahan.

Dalam keadaan sedemikian, ia boleh membantu:

• penyahnormalan dengan mengurangkan bilangan jadual. Adalah lebih baik untuk menggabungkan menjadi satu beberapa jadual yang bersaiz kecil, mengandungi maklumat yang jarang diubah (seperti yang sering mereka katakan, pemalar bersyarat atau rujukan) dan maklumat yang berkait rapat dalam makna.
• Secara umum, jika anda perlu menyertai lebih daripada lima atau enam jadual dalam sejumlah besar pertanyaan, anda harus mempertimbangkan untuk menyahnormalkan pangkalan data.
• Penyahnormalan dengan menambahkan medan tambahan pada salah satu jadual. Dalam kes ini, lebihan data muncul, tindakan tambahan diperlukan untuk mengekalkan integriti pangkalan data.

Anggaran nilai

Selalunya, pertanyaan adalah perlahan dan menggunakan banyak sumber, di mana beberapa pengiraan rumit dilakukan, terutamanya apabila menggunakan kumpulan dan fungsi agregat (Jumlah, Maks, dsb.). Kadangkala masuk akal untuk menambah 1-2 lajur tambahan pada jadual yang mengandungi data yang dikira yang kerap digunakan (dan sukar untuk dikira).

Katakan anda ingin menentukan jumlah kos setiap pesanan. Untuk melakukan ini, anda mesti terlebih dahulu menentukan kos setiap produk (mengikut formula "bilangan unit produk" * "harga unit produk" - diskaun). Selepas itu, anda perlu mengumpulkan kos mengikut pesanan.

Melaksanakan pertanyaan ini agak rumit dan, jika pangkalan data menyimpan maklumat tentang sejumlah besar pesanan, boleh mengambil masa yang lama. Daripada melaksanakan pertanyaan sedemikian, anda boleh menentukan kosnya pada peringkat membuat pesanan dan menyimpannya dalam lajur berasingan jadual pesanan. Dalam kes ini, untuk mendapatkan hasil yang diingini, cukup untuk mengekstrak nilai yang telah dikira dari lajur ini.

Mencipta lajur yang mengandungi nilai prakiraan menjimatkan banyak masa semasa menjalankan pertanyaan, tetapi memerlukan anda mengemas kini data dalam lajur itu tepat pada masanya.

tepi panjang

Jika kita mempunyai jadual besar dalam pangkalan data yang mengandungi medan panjang (Blob, Long, dll.), maka kita boleh mempercepatkan pelaksanaan pertanyaan ke jadual sedemikian dengan serius jika kita mengalihkan medan panjang ke jadual berasingan. Kami mahu, katakan, untuk mencipta katalog foto dalam pangkalan data, termasuk menyimpan foto itu sendiri dalam medan gumpalan (kualiti profesional, resolusi tinggi dan saiz yang sesuai). Dari sudut pandangan normalisasi, struktur jadual berikut benar-benar betul:

• ID foto
• ID Pengarang
• ID Model Kamera
• foto itu sendiri (medan gumpalan)

Dan sekarang mari kita bayangkan berapa lama pertanyaan akan dijalankan, mengira bilangan foto yang diambil oleh mana-mana pengarang ...

Penyelesaian yang betul (walaupun melanggar prinsip normalisasi) dalam situasi sedemikian adalah dengan membuat jadual lain yang hanya terdiri daripada dua medan - ID foto dan medan gumpalan dengan foto itu sendiri. Kemudian pilihan dari jadual utama (di mana tidak ada lagi medan gumpalan besar) akan pergi serta-merta, tetapi apabila kita mahu melihat foto itu sendiri, baiklah, tunggu ...

Bagaimana untuk menentukan apabila denormalisasi adalah wajar?

8.4 Kebaikan dan keburukan penyahnormalan

Satu cara untuk menentukan sama ada langkah-langkah tertentu adalah wajar adalah dengan menjalankan analisis dari segi kos dan kemungkinan faedah. Berapakah kos model data yang tidak normal?

Tentukan keperluan (apa yang kita ingin capai) → tentukan keperluan data (apa yang perlu kita ikuti) → cari langkah minimum yang memenuhi keperluan ini → kira kos pelaksanaan → laksana.

Kos termasuk aspek fizikal seperti ruang cakera, sumber yang diperlukan untuk mengurus struktur ini dan kehilangan peluang disebabkan kelewatan masa yang berkaitan dengan mengekalkan proses ini. Anda perlu membayar untuk penyahnormalan. Pangkalan data yang tidak normal meningkatkan lebihan data, yang boleh meningkatkan prestasi tetapi memerlukan lebih banyak usaha untuk mengawal data berkaitan. Proses mencipta aplikasi akan menjadi lebih sukar, kerana data akan berulang dan lebih sukar untuk dikesan. Di samping itu, pelaksanaan integriti rujukan tidak mudah - data berkaitan dibahagikan kepada jadual yang berbeza.

Faedah termasuk prestasi pertanyaan yang lebih pantas dan keupayaan untuk mendapatkan respons yang lebih pantas. Anda juga boleh meraih faedah lain, termasuk peningkatan daya pengeluaran, kepuasan pelanggan dan produktiviti, serta penggunaan alat pembangun luaran yang lebih cekap.

Kadar Permintaan dan Konsistensi Prestasi

Sebagai contoh, 72% daripada 1,000 pertanyaan yang dijana setiap hari oleh perusahaan adalah pertanyaan peringkat ringkasan, bukan pertanyaan gerudi. Apabila menggunakan jadual ringkasan, pertanyaan dijalankan dalam kira-kira 6 saat dan bukannya 4 minit, menyebabkan 3,000 minit kurang masa pemprosesan. Walaupun selepas melaraskan untuk 100 minit yang mesti diluangkan untuk mengekalkan jadual pangsi setiap minggu, itu menjimatkan 2,500 minit seminggu, yang mewajarkan penciptaan jadual pangsi. Dari masa ke masa, mungkin berlaku bahawa kebanyakan pertanyaan tidak akan ditujukan kepada data ringkasan, tetapi kepada data terperinci. Semakin sedikit pertanyaan yang menggunakan jadual ringkasan, lebih mudah untuk menggugurkannya tanpa menjejaskan proses lain.

Dan…

Kriteria yang disenaraikan di atas bukanlah satu-satunya yang perlu dipertimbangkan semasa membuat keputusan sama ada untuk mengambil langkah seterusnya dalam pengoptimuman. Faktor lain perlu dipertimbangkan, termasuk keutamaan perniagaan dan keperluan pengguna akhir. Pengguna mesti memahami bagaimana, dari sudut teknikal, seni bina sistem dipengaruhi oleh keperluan pengguna yang mahu semua permintaan diselesaikan dalam beberapa saat. Cara paling mudah untuk mencapai pemahaman ini adalah dengan menggariskan kos yang berkaitan dengan mencipta dan mengurus jadual tersebut.

8.5 Cara melaksanakan denormalisasi dengan cekap.

Simpan jadual terperinci

Untuk tidak mengehadkan keupayaan pangkalan data yang penting kepada perniagaan, adalah perlu untuk menggunakan strategi kewujudan bersama, bukan penggantian, iaitu, menyimpan jadual terperinci untuk analisis mendalam, menambah struktur yang tidak normal kepada mereka. Sebagai contoh, kaunter pukulan. Untuk perniagaan, anda perlu mengetahui bilangan lawatan ke halaman web. Tetapi untuk analisis (mengikut tempoh, mengikut negara...) kami berkemungkinan besar memerlukan data terperinci - jadual dengan maklumat tentang setiap lawatan.

Menggunakan pencetus

Adalah mungkin untuk menyahnormalkan struktur pangkalan data dan masih menikmati faedah normalisasi dengan menggunakan pencetus pangkalan data untuk memelihara integriti integritydata pendua.

Sebagai contoh, apabila menambah medan yang dikira, setiap lajur yang bergantung kepada medan yang dikira, digantung dengan pencetus yang memanggil prosedur tersimpan tunggal (ini penting!), Yang menulis data yang diperlukan ke medan yang dikira. Ia hanya perlu untuk tidak melangkau mana-mana lajur yang bergantung kepada medan yang dikira.

Sokongan perisian

Jika anda tidak menggunakan pencetus terbina dalam dan prosedur tersimpan, maka pembangun aplikasi harus menjaga memastikan ketekalan data dalam pangkalan data yang tidak normal.

Dengan analogi dengan pencetus, harus ada satu fungsi yang mengemas kini semua medan yang bergantung pada medan yang diubah.

kesimpulan

Apabila menyahnormalkan, adalah penting untuk mengekalkan keseimbangan antara meningkatkan kelajuan pangkalan data dan meningkatkan risiko data tidak konsisten, antara menjadikan kehidupan lebih mudah untuk pengaturcara menulis Select-sdan merumitkan tugas mereka yang menyediakan populasi pangkalan data dan kemas kini data. Oleh itu, adalah perlu untuk menyahnormalkan pangkalan data dengan sangat berhati-hati, sangat selektif, hanya di mana ia amat diperlukan.

Sekiranya mustahil untuk mengira kebaikan dan keburukan denormalisasi terlebih dahulu, maka pada mulanya adalah perlu untuk melaksanakan model dengan jadual ternormal, dan hanya kemudian, untuk mengoptimumkan pertanyaan bermasalah, menjalankan denormalisasi.

Adalah penting untuk memperkenalkan penyahnormalan secara beransur-ansur dan hanya untuk kes yang terdapat pengambilan berulang data berkaitan daripada jadual yang berbeza. Ingat, apabila menduplikasi data, bilangan rekod akan meningkat, tetapi bilangan bacaan akan berkurangan. Ia juga mudah untuk menyimpan data yang dikira dalam lajur untuk mengelakkan pemilihan agregat yang tidak perlu.