Denormalisasi tabel ing basis data

8.1 Napa denormalisasi perlu?

Operasi sing paling larang komputasi antarane tabel gedhe yaiku gabung. Mulane, yen ing siji pitakonan perlu kanggo "ventilate" sawetara tabel dumadi saka akeh yuta larik, banjur DBMS bakal nglampahi akèh wektu ing Processing.

Pangguna ing wektu iki bisa pindhah adoh kanggo ngombe kopi. Interaktivitas pangolahan praktis ilang lan nyedhaki pangolahan batch. Malah luwih elek, ing mode batch, pangguna nampa kabeh data sing dijaluk dina sadurunge esuk lan kanthi tenang bisa digunakake, nyiapake panjaluk anyar ing wayah sore.

Kanggo ngindhari kahanan gabung abot, tabel denormalisasi. Nanging ora kepriye. Ana sawetara aturan sing ngidini sampeyan nganggep tabel denormalisasi transaksional minangka "dinormalisasi" miturut aturan kanggo mbangun tabel kanggo gudang data.

Ana rong skema utama sing dianggep "normal" ing proses analitis: "kepingan salju" lan "lintang". Jeneng kasebut nggambarake inti kanthi apik lan tindakake langsung saka gambar tabel sing ana gandhengane.

Ing kasus loro kasebut, tabel fakta sing diarani minangka unsur tengah skema, ngemot acara, transaksi, dokumen, lan barang-barang menarik liyane kanggo analis. Nanging yen ing basis data transaksional siji dokumen "diolesi" ing sawetara tabel (paling ora loro: header lan larik isi), banjur ing tabel kasunyatan siji dokumen, luwih tepat, saben baris utawa sakumpulan baris sing cocog. menyang siji rekaman.

Iki bisa ditindakake kanthi denormalisasi rong tabel ing ndhuwur.

8.2 Conto denormalisasi

Saiki sampeyan bisa ngevaluasi sepira luwih gampang kanggo DBMS nglakokake pitakon, umpamane, saka jinis ing ngisor iki: kanggo nemtokake volume penjualan glepung menyang klien Pirozhki LLC lan Vatrushki CJSC kanggo periode kasebut.

Ing basis data transaksional sing 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

Ing basis data 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

Tinimbang gabung abot antarane rong tabel dokumen lan komposisi karo mayuta-yuta larik, DBMS entuk karya langsung karo meja kasunyatan lan cahya gabung karo tabel tambahan cilik, sing uga bisa nindakake tanpa, ngerti pengenal.

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

Ayo bali menyang skema "bintang" lan "keping salju". Ing mburi gambar pisanan ana meja pelanggan, kelompok, toko, penjual lan, nyatane, barang. Nalika denormalisasi, tabel kasebut, sing diarani dimensi, uga digabung karo tabel fakta. Yen tabel fakta nuduhake tabel dimensi sing nduweni pranala menyang dimensi liyane (dimensi tingkat kapindho lan ndhuwur), mula skema kasebut diarani "snowflake".

Minangka sampeyan bisa ndeleng, kanggo pitakon sing kalebu nyaring dening klompok klien, sampeyan kudu nggawe 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)

Ing kasus iki, denormalisasi bisa terus lan nyelehake dimensi tingkat kapindho menyang pisanan, dadi luwih gampang kanggo takon tabel fakta.

Skema sing tabel fakta mung nuduhake dimensi sing ora duwe tingkat kapindho diarani skema bintang. Jumlah tabel pangukuran cocog karo jumlah "sinar" ing lintang kasebut.

Skema Star rampung ngilangi hirarki dimensi lan kudu nggabungake tabel sing cocog ing siji pitakon.

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

Kakurangan denormalisasi tansah redundansi , sing nyebabake paningkatan ukuran database ing aplikasi transaksional lan analitis. Ayo ngetung kira-kira delta ing conto ing ndhuwur kanggo ngowahi "snowflake" dadi "bintang".

Ing sawetara DBMS, kayata Oracle, ora ana jinis integer khusus ing tingkat definisi skema database, sampeyan kudu nggunakake jinis boolean umum numeric(N), ing ngendi N minangka jumlah bit sing disimpen. Ukuran panyimpenan saka nomer kasebut diwilang nggunakake rumus khusus sing diwenehake ing dokumentasi kanggo panyimpenan data fisik, lan, minangka aturan, ngluwihi jinis tingkat rendah kaya "16 bit integer" kanthi 1-3 bita.

Upaminipun tabel dodolan ora nggunakake komprèsi data lan ngemot babagan 500 yuta larik, lan nomer klompok customer kira 1000. Ing kasus iki, kita bisa nggunakake integer singkat (shortint, smallint) manggoni 2 bita minangka jinis pengenal id_customer_group.

Kita bakal nganggep manawa DBMS ndhukung jinis integer rong bait (contone, PostgreSQL, SQL Server, Sybase lan liya-liyane). Banjur nambahake kolom sing cocog id_customer_grouping tabel dodolan bakal nambah ukurane paling sethithik 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Nalika denormalisasi dibutuhake?

Ayo goleki sawetara kahanan umum ing ngendi denormalisasi bisa migunani.

Jumlah gedhe saka Tabel nggabung

Ing pitakon menyang database sing wis dinormalisasi kanthi lengkap, sampeyan kerep kudu nggabungake nganti rolas utawa luwih tabel. Lan saben sambungan iku operasi banget sumber-intensif. Akibaté, panjalukan kasebut nggunakake sumber daya server lan ditindakake kanthi alon.

Ing kahanan kaya mengkono, bisa mbantu:

• denormalisasi kanthi ngurangi jumlah tabel. Iku luwih apik kanggo gabungke dadi siji sawetara tabel sing ukurane cilik, ngemot arang diganti (minangka padha asring ngandika, kondisi tetep utawa referensi) informasi, lan informasi sing ana hubungane rapet ing makna.
• Umumé, yen sampeyan kudu nggabungake luwih saka lima utawa enem tabel ing akeh pitakon, sampeyan kudu nimbang denormalisasi database.
• Denormalisasi kanthi nambahake kolom tambahan menyang salah sawijining tabel. Ing kasus iki, redundansi data katon, tumindak tambahan dibutuhake kanggo njaga integritas database.

Nilai kira-kira

Asring, pitakon alon lan nggunakake akeh sumber daya, ing ngendi sawetara petungan rumit ditindakake, utamane nalika nggunakake panglompokan lan fungsi agregat (Sum, Max, lsp.). Kadhangkala iku ndadekake pangertèn kanggo nambah 1-2 kolom tambahan kanggo tabel ngemot asring digunakake (lan angel kanggo ngetung) data diwilang.

Upaminipun sampeyan pengin nemtokake biaya total saben pesenan. Kanggo nindakake iki, sampeyan kudu nemtokake biaya saben produk (miturut rumus "jumlah unit produk" * "rega unit produk" - diskon). Sawise iku, sampeyan kudu nglumpukake biaya kanthi pesenan.

Nglakokake pitakon iki cukup rumit lan, yen database nyimpen informasi babagan akeh pesenan, bisa suwe. Tinimbang nglakokake pitakon kasebut, sampeyan bisa nemtokake biaya ing tataran pesenan lan nyimpen ing kolom kapisah saka tabel pesenan. Ing kasus iki, kanggo entuk asil sing dikarepake, cukup kanggo ngekstrak nilai sing wis diwilang saka kolom iki.

Nggawe kolom sing ngemot nilai precalculated ngirit akeh wektu nalika mbukak pitakon, nanging mbutuhake sampeyan nganyari data ing kolom kasebut kanthi pas wektune.

pinggir dawa

Yen kita duwe tabel gedhe ing basis data sing ngemot kolom dawa (Blob, Long, lan sapiturute), mula kita bisa nyepetake eksekusi pitakon menyang tabel kasebut yen kita mindhah kolom dawa menyang tabel sing kapisah. Kita pengin, ucapake, nggawe katalog foto ing basis data, kalebu nyimpen foto dhewe ing lapangan blob (kualitas profesional, resolusi dhuwur, lan ukuran sing cocog). Saka sudut pandang normalisasi, struktur tabel ing ngisor iki pancen bener:

• ID foto
• ID Pengarang
• ID model kamera
• foto dhewe (bidang gumpalan)

Lan saiki ayo bayangake suwene pitakon bakal ditindakake, ngitung jumlah foto sing dijupuk dening penulis ...

Solusi sing bener (sanajan nglanggar prinsip normalisasi) ing kahanan kaya mengkono bakal nggawe tabel liyane sing mung dumadi saka rong lapangan - ID foto lan lapangan blob karo foto kasebut dhewe. Banjur pilihan saka meja utama (sing ora ana lapangan gumpalan gedhe) bakal langsung, nanging yen kita pengin ndeleng foto kasebut, ayo ngenteni ...

Kepiye carane nemtokake yen denormalisasi ditrapake?

8.4 Pros lan cons saka denormalization

Salah siji cara kanggo nemtokake manawa langkah-langkah tartamtu ditrapake yaiku nganakake analisis babagan biaya lan keuntungan sing bisa ditindakake. Pira rega model data sing ora normal?

Nemtokake syarat (apa sing arep digayuh) → nemtokake syarat data (apa sing kudu ditindakake) → golek langkah minimal sing nyukupi syarat kasebut → ngitung biaya implementasine → ngleksanakake.

Biaya kalebu aspek fisik kayata ruang disk, sumber daya sing dibutuhake kanggo ngatur struktur iki, lan kesempatan sing ilang amarga wektu tundha sing ana gandhengane karo njaga proses iki. Sampeyan kudu mbayar denormalisasi. Basis data denormalisasi nambah redundansi data, sing bisa nambah kinerja nanging mbutuhake luwih akeh gaweyan kanggo ngontrol data sing gegandhengan. Proses nggawe aplikasi bakal dadi luwih angel, amarga data bakal diulang lan luwih angel dilacak. Kajaba iku, implementasine integritas referensial ora gampang - data sing gegandhengan dipérang dadi tabel sing béda.

Keuntungan kalebu kinerja pitakon sing luwih cepet lan kemampuan kanggo entuk respon sing luwih cepet. Sampeyan uga bisa entuk keuntungan liyane, kalebu tambah throughput, kepuasan pelanggan, lan produktivitas, uga panggunaan alat pangembang eksternal sing luwih efisien.

Tingkat Panjaluk lan Konsistensi Kinerja

Contone, 72% saka 1.000 pitakon sing diasilake saben dina dening perusahaan minangka pitakon tingkat ringkesan, dudu pitakon pitakon. Nalika nggunakake tabel ringkesan, pitakon mlaku ing kira-kira 6 detik tinimbang 4 menit, nyebabake wektu pangolahan 3.000 menit luwih sithik. Malah sawise nyetel kanggo 100 menit sing kudu ngginakaken njaga tabel poros saben minggu, sing nyimpen 2.500 menit saben minggu, kang mbecikake nggawe meja poros. Sajrone wektu, bisa uga kedadeyan sing paling akeh pitakon ora bakal ditangani kanggo data ringkesan, nanging kanggo data rinci. Pitakon sing luwih sithik sing nggunakake tabel ringkesan, luwih gampang kanggo nyelehake tanpa mengaruhi proses liyane.

Lan…

Kritéria sing kadhaptar ing ndhuwur ora mung siji-sijine sing kudu ditimbang nalika mutusake apa bakal njupuk langkah sabanjure ing optimasi. Faktor liyane kudu dianggep, kalebu prioritas bisnis lan kabutuhan pangguna pungkasan. Pangguna kudu ngerti carane, saka sudut pandang teknis, arsitektur sistem kena pengaruh saka syarat pangguna sing pengin kabeh panjaluk rampung ing sawetara detik. Cara paling gampang kanggo entuk pangerten iki yaiku njelasake biaya sing ana gandhengane karo nggawe lan ngatur tabel kasebut.

8.5 Cara ngleksanakake denormalisasi kanthi kompeten.

Simpen tabel rinci

Supaya ora mbatesi kapabilitas database sing penting kanggo bisnis, sampeyan kudu nggunakake strategi coexistence, ora ngganti, yaiku, tetep tabel rinci kanggo analisis jero, nambah struktur denormalized. Contone, counter hit. Kanggo bisnis, sampeyan kudu ngerti jumlah kunjungan menyang kaca web. Nanging kanggo analisis (miturut periode, negara ...) kita bakal mbutuhake data sing rinci - tabel kanthi informasi babagan saben kunjungan.

Nggunakake pemicu

Sampeyan bisa denormalize struktur database lan isih seneng keuntungan saka normalisasi kanthi nggunakake pemicu database kanggo njaga integritas integritydata duplikat.

Contone, nalika nambah kolom sing diwilang, saben kolom sing diwilang kolom gumantung, Hung munggah karo pemicu sing nelpon prosedur siji disimpen (iki penting!), Kang nulis data perlu kanggo kolom diwilang. Sampeyan mung kudu ora ngliwati kolom sing gumantung saka kolom sing diitung.

Dhukungan piranti lunak

Yen sampeyan ora nggunakake pemicu sing dibangun lan prosedur sing disimpen, mula pangembang aplikasi kudu ngurus konsistensi data ing database denormalisasi.

Miturut analogi karo pemicu, kudu ana siji fungsi sing nganyari kabeh lapangan sing gumantung ing lapangan sing diganti.

kesimpulan

Nalika denormalizing, iku penting kanggo njaga imbangan antarane nambah kacepetan database lan nambah risiko data inconsistent, antarane nggawe urip luwih gampang kanggo programer nulis Select-s, lan complicating tugas sing nyedhiyani populasi database lan nganyari data. Mulane, perlu kanggo denormalize database kasebut kanthi teliti, banget selektif, mung ing ngendi iku indispensable.

Yen ora bisa ngetung pros lan cons saka denormalisasi ing advance, banjur pisanan kudu ngleksanakake model karo tabel normal, lan mung banjur kanggo ngoptimalake pitakonan masalah, nindakake denormalization.

Penting kanggo ngenalake denormalisasi kanthi bertahap lan mung kanggo kasus sing ana bola-bali njupuk data sing ana gandhengane saka tabel sing beda-beda. Elinga, nalika duplikat data, jumlah rekaman bakal nambah, nanging jumlah sing diwaca bakal suda. Sampeyan uga trep kanggo nyimpen data sing diwilang ing kolom supaya ora ana pilihan agregat sing ora perlu.