Denormalization ng mga talahanayan sa database

8.1 Bakit kailangan ang denormalization?

Ang pinakamahal na pagpapatakbo sa pagitan ng malalaking talahanayan ay ang pagsali. Alinsunod dito, kung sa isang query ay kinakailangan na "mag-ventilate" ng ilang mga talahanayan na binubuo ng maraming milyon-milyong mga hilera, kung gayon ang DBMS ay gugugol ng maraming oras sa naturang pagproseso.

Ang gumagamit sa oras na ito ay maaaring lumayo upang uminom ng kape. Ang interaktibidad ng pagproseso ay halos nawawala at lumalapit sa batch processing. Kahit na mas masahol pa, sa batch mode, natatanggap ng user ang lahat ng data na hiniling sa araw bago ang umaga at mahinahong nakikipagtulungan sa kanila, naghahanda ng mga bagong kahilingan para sa gabi.

Upang maiwasan ang sitwasyon ng mabibigat na pagsasama, ang mga talahanayan ay na-denormalize. Pero hindi naman. Mayroong ilang mga panuntunan na nagbibigay-daan sa iyong isaalang-alang ang mga transactionally denormalized na talahanayan bilang "na-normalize" ayon sa mga panuntunan para sa pagbuo ng mga talahanayan para sa mga data warehouse.

Mayroong dalawang pangunahing mga scheme na itinuturing na "normal" sa analytical processing: "snowflake" at "star". Ang mga pangalan ay sumasalamin sa kakanyahan nang maayos at direktang sumusunod sa larawan ng mga kaugnay na talahanayan.

Sa parehong mga kaso, ang tinatawag na mga talahanayan ng katotohanan ay ang pangunahing elemento ng schema, na naglalaman ng mga kaganapan, transaksyon, dokumento, at iba pang mga kawili-wiling bagay na interesado sa analyst. Ngunit kung sa isang transactional database ang isang dokumento ay "pinahiran" sa ilang mga talahanayan (hindi bababa sa dalawa: mga header at mga hilera ng nilalaman), kung gayon sa talahanayan ng katotohanan ay isang dokumento, mas tiyak, ang bawat isa sa mga hilera nito o isang hanay ng mga nakagrupong hilera, ay tumutugma. sa isang record.

Magagawa ito sa pamamagitan ng pag-denormalize ng dalawang talahanayan sa itaas.

8.2 Halimbawa ng denormalization

Ngayon ay masusuri mo kung gaano magiging madali para sa DBMS na magsagawa ng isang query, halimbawa, ng sumusunod na uri: upang matukoy ang dami ng mga benta ng harina sa mga kliyente ng Pirozhki LLC at Vatrushki CJSC para sa panahon.

Sa isang normalized na transactional database:

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

Sa analytical database:

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

Sa halip na isang mabigat na pagsasama sa pagitan ng dalawang talahanayan ng mga dokumento at ang kanilang komposisyon na may milyun-milyong mga hilera, ang DBMS ay nakakakuha ng direktang gawain sa talahanayan ng katotohanan at ang mga light joint na may maliliit na auxiliary table, na magagawa mo rin nang wala, alam ang mga identifier.

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

Bumalik tayo sa "star" at "snowflake" scheme. Sa likod ng mga eksena ng unang larawan ay ang mga talahanayan ng mga customer, kanilang mga grupo, mga tindahan, nagbebenta at, sa katunayan, mga kalakal. Kapag na-denormalize, ang mga talahanayan na ito, na tinatawag na mga dimensyon, ay pinagsama rin sa talahanayan ng katotohanan. Kung ang talahanayan ng katotohanan ay tumutukoy sa mga talahanayan ng dimensyon na may mga link sa iba pang mga dimensyon (mga sukat ng pangalawang antas at mas mataas), kung gayon ang naturang schema ay tinatawag na "snowflake".

Gaya ng nakikita mo, para sa mga query na may kasamang pag-filter ayon sa mga grupo ng kliyente, kailangan mong gumawa ng karagdagang koneksyon.

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)

Sa kasong ito, maaaring magpatuloy ang denormalization at ibaba ang dimensyon sa pangalawang antas sa una, na ginagawang mas madaling i-query ang talahanayan ng katotohanan.

Ang isang schema kung saan ang isang talahanayan ng katotohanan ay tumutukoy lamang sa mga dimensyon na walang pangalawang antas ay tinatawag na isang star schema. Ang bilang ng mga talahanayan ng pagsukat ay tumutugma sa bilang ng mga "ray" sa bituin.

Ganap na inaalis ng Star schema ang hierarchy ng mga dimensyon at ang pangangailangang pagsamahin ang mga kaukulang talahanayan sa isang query.

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

Ang downside ng denormalization ay palaging redundancy , na nagdudulot ng pagtaas sa laki ng database sa parehong transactional at analytical na application. Magkalkula tayo ng tinatayang delta sa halimbawa sa itaas ng pag-convert ng "snowflake" sa "star".

Sa ilang DBMS, tulad ng Oracle, walang mga espesyal na uri ng integer sa antas ng mga kahulugan ng schema ng database, dapat mong gamitin ang generic na uri ng boolean numeric(N), kung saan ang N ay ang bilang ng mga nakaimbak na bit. Ang laki ng imbakan ng naturang numero ay kinakalkula gamit ang isang espesyal na formula na ibinigay sa dokumentasyon para sa pisikal na pag-iimbak ng data, at, bilang panuntunan, lumalampas ito sa mga uri ng mababang antas tulad ng "16 bit integer" ng 1-3 byte.

Ipagpalagay na ang talahanayan ng mga benta ay hindi gumagamit ng compression ng data at naglalaman ng humigit-kumulang 500 milyong row, at ang bilang ng mga pangkat ng customer ay humigit-kumulang 1000. Sa kasong ito, maaari kaming gumamit ng maikling integer (shortint, smallint) na sumasakop sa 2 byte bilang isang uri ng identifier id_customer_group.

Ipagpalagay namin na ang aming DBMS ay sumusuporta sa isang dalawang-byte na uri ng integer (halimbawa, PostgreSQL, SQL Server, Sybase at iba pa). Pagkatapos, ang pagdaragdag ng kaukulang column id_customer_groupsa talahanayan ng mga benta ay tataas ang laki nito nang hindi bababa sa 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Kailan kailangan ang denormalization?

Tingnan natin ang ilang karaniwang sitwasyon kung saan maaaring maging kapaki-pakinabang ang denormalization.

Malaking bilang ng mga pinagsamang talahanayan

Sa mga query sa isang ganap na na-normalize na database, madalas kang kailangang sumali ng hanggang sa isang dosenang o higit pang mga talahanayan. At ang bawat koneksyon ay isang napaka-resource-intensive na operasyon. Bilang resulta, ang mga naturang kahilingan ay kumokonsumo ng mga mapagkukunan ng server at mabagal na ginagawa.

Sa ganitong sitwasyon, makakatulong ito:

• denormalization sa pamamagitan ng pagbabawas ng bilang ng mga talahanayan. Mas mainam na pagsamahin sa isa ang ilang mga talahanayan na maliit ang sukat, na naglalaman ng bihirang baguhin (tulad ng madalas nilang sinasabi, kondisyon na pare-pareho o sanggunian) na impormasyon, at impormasyon na malapit na nauugnay sa kahulugan.
• Sa pangkalahatan, kung kailangan mong sumali sa higit sa lima o anim na talahanayan sa isang malaking bilang ng mga query, dapat mong isaalang-alang ang pag-denormalize ng database.
• Denormalization sa pamamagitan ng pagdaragdag ng karagdagang field sa isa sa mga talahanayan. Sa kasong ito, lumilitaw ang redundancy ng data, ang mga karagdagang aksyon ay kinakailangan upang mapanatili ang integridad ng database.

Mga tinantyang halaga

Kadalasan, ang mga query ay mabagal at gumagamit ng maraming mapagkukunan, kung saan ang ilang kumplikadong kalkulasyon ay ginagawa, lalo na kapag gumagamit ng mga pagpapangkat at pinagsama-samang mga function (Sum, Max, atbp.). Minsan makatuwirang magdagdag ng 1-2 karagdagang column sa talahanayan na naglalaman ng madalas na ginagamit (at mahirap kalkulahin) na nakalkulang data.

Ipagpalagay na gusto mong matukoy ang kabuuang halaga ng bawat order. Upang gawin ito, kailangan mo munang matukoy ang halaga ng bawat produkto (ayon sa formula na "bilang ng mga yunit ng produkto" * "presyo ng yunit ng produkto" - diskwento). Pagkatapos nito, kailangan mong pangkatin ang mga gastos ayon sa mga order.

Ang pagsasagawa ng query na ito ay medyo kumplikado at, kung ang database ay nag-iimbak ng impormasyon tungkol sa isang malaking bilang ng mga order, maaaring tumagal ng mahabang panahon. Sa halip na magsagawa ng ganoong query, matutukoy mo ang gastos nito sa yugto ng paglalagay ng order at iimbak ito sa isang hiwalay na column ng talahanayan ng mga order. Sa kasong ito, upang makuha ang ninanais na resulta, sapat na upang kunin ang paunang nakalkula na mga halaga mula sa hanay na ito.

Ang paggawa ng column na naglalaman ng mga precalculated values ​​ay nakakatipid ng maraming oras kapag nagpapatakbo ng query, ngunit kailangan mong i-update ang data sa column na iyon sa isang napapanahong paraan.

mahabang labi

Kung mayroon kaming malalaking talahanayan sa database na naglalaman ng mahahabang field (Blob, Long, atbp.), kung gayon maaari naming seryosong mapabilis ang pagpapatupad ng mga query sa naturang talahanayan kung ililipat namin ang mahabang field sa isang hiwalay na talahanayan. Nais naming, sabihin, na lumikha ng isang katalogo ng mga larawan sa database, kabilang ang pag-iimbak ng mga larawan mismo sa mga patlang ng blob (propesyonal na kalidad, mataas na resolution, at naaangkop na laki). Mula sa punto ng view ng normalisasyon, ang sumusunod na istraktura ng talahanayan ay magiging ganap na tama:

• Photo ID
• ID ng may-akda
• ID ng Modelo ng Camera
• ang larawan mismo (patlang ng blob)

At ngayon isipin natin kung gaano katagal tatakbo ang query, binibilang ang bilang ng mga larawang kinunan ng sinumang may-akda ...

Ang tamang solusyon (kahit na lumalabag sa mga prinsipyo ng normalisasyon) sa ganoong sitwasyon ay ang lumikha ng isa pang talahanayan na binubuo lamang ng dalawang field - ang photo ID at isang blob field na may mismong larawan. Pagkatapos ang mga seleksyon mula sa pangunahing talahanayan (kung saan wala nang malaking patlang ng patak) ay pupunta kaagad, ngunit kapag gusto nating makita ang larawan mismo, mabuti, maghintay tayo ...

Paano matukoy kung kailan makatwiran ang denormalization?

8.4 Mga kalamangan at kahinaan ng denormalization

Ang isang paraan upang matukoy kung ang ilang mga hakbang ay makatwiran ay ang pagsasagawa ng pagsusuri sa mga tuntunin ng mga gastos at posibleng mga benepisyo. Magkano ang halaga ng isang denormalized na modelo ng data?

Tukuyin ang mga kinakailangan (kung ano ang gusto nating makamit) → tukuyin ang mga kinakailangan sa data (kung ano ang kailangan nating sundin) → hanapin ang pinakamababang hakbang na nakakatugon sa mga kinakailangang ito → kalkulahin ang mga gastos sa pagpapatupad → ipatupad.

Kasama sa mga gastos ang mga pisikal na aspeto tulad ng espasyo sa disk, ang mga mapagkukunang kinakailangan upang pamahalaan ang istrukturang ito, at mga nawalang pagkakataon dahil sa mga pagkaantala sa oras na nauugnay sa pagpapanatili ng prosesong ito. Kailangan mong magbayad para sa denormalization. Ang isang denormalized na database ay nagpapataas ng data redundancy, na maaaring mapabuti ang pagganap ngunit nangangailangan ng higit na pagsisikap upang makontrol ang nauugnay na data. Ang proseso ng paglikha ng mga application ay magiging mas mahirap, dahil ang data ay mauulit at mas mahirap subaybayan. Sa karagdagan, ang pagpapatupad ng referential integridad ay hindi madali - ang mga kaugnay na data ay nahahati sa iba't ibang mga talahanayan.

Kasama sa mga benepisyo ang mas mabilis na pagganap ng query at ang kakayahang makakuha ng mas mabilis na tugon. Maaari ka ring umani ng iba pang mga benepisyo, kabilang ang tumaas na throughput, kasiyahan ng customer, at pagiging produktibo, pati na rin ang mas mahusay na paggamit ng mga external na tool ng developer.

Rate ng Kahilingan at Pagkakatugma ng Pagganap

Halimbawa, 72% ng 1,000 query na nabuo araw-araw ng isang enterprise ay mga query sa antas ng buod, hindi mga drill-down na query. Kapag gumagamit ng talahanayan ng buod, ang mga query ay tumatakbo sa humigit-kumulang 6 na segundo sa halip na 4 na minuto, na nagreresulta sa 3,000 minutong mas kaunting oras ng pagproseso. Kahit na pagkatapos mag-adjust para sa 100 minuto na dapat gugulin sa pagpapanatili ng mga pivot table bawat linggo, nakakatipid iyon ng 2,500 minuto bawat linggo, na nagbibigay-katwiran sa paglikha ng pivot table. Sa paglipas ng panahon, maaaring mangyari na ang karamihan sa mga query ay hindi tutugunan sa buod ng data, ngunit sa detalyadong data. Ang mas kaunting mga query na gumagamit ng talahanayan ng buod, mas madaling i-drop ito nang hindi naaapektuhan ang iba pang mga proseso.

At…

Ang mga pamantayang nakalista sa itaas ay hindi lamang ang dapat isaalang-alang kapag nagpapasya kung gagawin ang susunod na hakbang sa pag-optimize. Kailangang isaalang-alang ang iba pang mga salik, kabilang ang mga priyoridad ng negosyo at mga pangangailangan ng end user. Dapat na maunawaan ng mga user kung paano, mula sa teknikal na pananaw, ang arkitektura ng system ay apektado ng pangangailangan ng mga user na gustong makumpleto ang lahat ng kahilingan sa loob ng ilang segundo. Ang pinakamadaling paraan upang makamit ang pag-unawang ito ay ang pagbabalangkas ng mga gastos na nauugnay sa paggawa at pamamahala ng mga naturang talahanayan.

8.5 Paano mahusay na ipatupad ang denormalization.

I-save ang mga detalyadong talahanayan

Upang hindi limitahan ang mga kakayahan ng database na mahalaga sa negosyo, kinakailangan na magpatibay ng isang diskarte ng magkakasamang buhay, hindi kapalit, iyon ay, panatilihin ang mga detalyadong talahanayan para sa malalim na pagsusuri, pagdaragdag ng mga denormalized na istruktura sa kanila. Halimbawa, ang hit counter. Para sa negosyo, kailangan mong malaman ang bilang ng mga pagbisita sa isang web page. Ngunit para sa pagsusuri (ayon sa panahon, ayon sa bansa...) malamang na kailangan namin ng detalyadong data - isang talahanayan na may impormasyon tungkol sa bawat pagbisita.

Paggamit ng mga trigger

Posibleng i-denormalize ang istraktura ng database at tamasahin pa rin ang mga benepisyo ng normalisasyon sa pamamagitan ng paggamit ng mga trigger ng database upang mapanatili ang integrityintegridad ng duplicate na data.

Halimbawa, kapag nagdaragdag ng isang kalkuladong field, ang bawat isa sa mga column kung saan nakasalalay ang kinakalkula na field, ay nakabitin sa isang trigger na tumatawag sa isang solong naka-imbak na pamamaraan (ito ay mahalaga!), Na nagsusulat ng kinakailangang data sa kinakalkula na field. Kinakailangan lamang na huwag laktawan ang alinman sa mga column kung saan nakasalalay ang kalkuladong field.

Suporta sa software

Kung hindi ka gumagamit ng mga built-in na trigger at naka-imbak na mga pamamaraan, dapat pangalagaan ng mga developer ng application na tiyakin ang pagkakapare-pareho ng data sa isang denormalized na database.

Sa pamamagitan ng pagkakatulad sa mga nag-trigger, dapat mayroong isang function na nag-a-update sa lahat ng mga field na nakadepende sa field na binago.

mga konklusyon

Kapag nag-denormalize, mahalagang mapanatili ang balanse sa pagitan ng pagtaas ng bilis ng database at pagtaas ng panganib ng hindi pare-parehong data, sa pagitan ng pagpapadali ng buhay para sa pagsusulat ng mga programmer , Select-sat pagpapakumplikado sa gawain ng mga nagbibigay ng populasyon ng database at pag-update ng data. Samakatuwid, kinakailangang i-denormalize ang database nang maingat, napakapili, kung saan ito ay kailangang-kailangan.

Kung imposibleng kalkulahin ang mga kalamangan at kahinaan ng denormalization nang maaga, pagkatapos ay sa una ay kinakailangan upang ipatupad ang isang modelo na may mga normalized na talahanayan, at pagkatapos lamang, upang ma-optimize ang mga problemang query, isagawa ang denormalization.

Mahalagang ipakilala ang denormalization nang unti-unti at para lamang sa mga kaso kung saan may paulit-ulit na pagkuha ng mga nauugnay na data mula sa iba't ibang talahanayan. Tandaan, kapag nagdodoble ng data, tataas ang bilang ng mga tala, ngunit bababa ang bilang ng mga nabasa. Maginhawa din na mag-imbak ng nakalkulang data sa mga column upang maiwasan ang mga hindi kinakailangang pinagsama-samang pagpili.