Denormalizatsiya — qachon va nima uchun

~15 daq · MySQL · Ma'lumotlar Bazasini Loyihalash

Muammodan boshlaylik

Sxema to'liq normallashtirilgan (46-dars). Endi internet do'konning bosh sahifasida mahsulot kartalari: nomi, narxi, reytingi (o'rtacha baho) va sharhlar soni.

SELECT p.id, p.nom, p.narx,
       ROUND(AVG(s.baho), 1) AS reyting,
       COUNT(s.id)           AS sharhlar
FROM mahsulotlar p
LEFT JOIN sharhlar s ON s.mahsulot_id = p.id
GROUP BY p.id
ORDER BY reyting DESC
LIMIT 20;

sharhlar jadvalida 30 million qator. Bosh sahifa daqiqada 5000 marta ochiladi. Har safar 30 million qatorni guruhlash — server bardosh bermaydi.

Normalizatsiya to'g'rilikni kafolatlaydi. Lekin ba'zan tezlik uchun ataylab qoidani buzish kerak bo'ladi. Bu — denormalizatsiya.

Denormalizatsiya nima?

Ma'lumotni ataylab takrorlash yoki oldindan hisoblab saqlash — o'qishni tezlashtirish uchun.

ALTER TABLE mahsulotlar
    ADD COLUMN reyting DECIMAL(2,1) NULL,
    ADD COLUMN sharhlar_soni INT UNSIGNED NOT NULL DEFAULT 0;

CREATE INDEX idx_reyting ON mahsulotlar (reyting);

Endi bosh sahifa:

SELECT id, nom, narx, reyting, sharhlar_soni
FROM mahsulotlar
ORDER BY reyting DESC
LIMIT 20;

30 million qatorni guruhlash o'rniga — indeks bo'yicha 20 ta qator. Ming barobar tez.

Narxi: endi reyting ikki joyda — sharhlar da (haqiqiy manba) va mahsulotlar.reyting da (nusxa). Ular har doim mos bo'lishi kerak. Bu — sizning mas'uliyatingiz.

Asosiy qoida

[!WARNING] Avval normallashtiring. Denormalizatsiya — faqat o'lchangan muammo uchun: EXPLAIN, slow query log yoki monitoring sekinlikni ko'rsatganda. "Ehtimol sekin bo'ladi" — sabab emas.

Normallashtirilmagan sxema — bu xato. Denormallashtirilgan sxema — bu ongli qaror: normallashtirilgan sxemadan boshlab, aniq bir joyni aniq bir sabab bilan o'zgartirish.

Denormalizatsiya turlari

1. Hisoblangan qiymatlar (agregatlar)

mahsulotlar.sharhlar_soni, mahsulotlar.reyting
mijozlar.buyurtmalar_soni, mijozlar.jami_xarid
maqolalar.korishlar, maqolalar.izohlar_soni

Eng ko'p uchraydigan tur. "Soni", "jami", "o'rtacha" — har safar hisoblash qimmat.

2. Takrorlangan ustunlar

-- Buyurtmalar ro'yxatida mijoz ismi har doim kerak
ALTER TABLE buyurtmalar ADD COLUMN mijoz_ismi VARCHAR(100);

JOIN dan qochish uchun. Lekin mijoz ismini o'zgartirsa — barcha buyurtmalarni yangilash kerak. Ehtiyot bo'ling.

[!INFO] Farqlang: buyurtma_qatorlari.narx — denormalizatsiya emas. Bu boshqa fakt: "sotilgan paytdagi narx", mahsulotning hozirgi narxidan mustaqil (18-dars). Xuddi shunday — buyurtmadagi yetkazish manzili. Tarixiy "surat" — to'g'ri normallashtirilgan dizayn.

3. Hisobot jadvallari

CREATE TABLE kunlik_statistika (kun DATE PRIMARY KEY, buyurtmalar INT, tushum BIGINT);

Har kecha hisoblanadi (34-dars, 44-dars). Yillik hisobot — 365 qator.

4. JSON ichida bog'liq ma'lumot

-- Mahsulot kartasi uchun hammasi bitta ustunda
ALTER TABLE mahsulotlar ADD COLUMN karta_kesh JSON;

Sinxronlash usullari

Nusxani asl manba bilan qanday mos tutish kerak?

A. Trigger (43-dars)

DELIMITER //
CREATE TRIGGER trg_sharh_qoshildi AFTER INSERT ON sharhlar
FOR EACH ROW
BEGIN
    UPDATE mahsulotlar
    SET sharhlar_soni = sharhlar_soni + 1,
        reyting = (SELECT ROUND(AVG(baho), 1) FROM sharhlar WHERE mahsulot_id = NEW.mahsulot_id)
    WHERE id = NEW.mahsulot_id;
END //
DELIMITER ;

✅ Har qanday manbadan yozilganda ishlaydi, bitta tranzaksiyada ❌ Yashirin, har bir yozishni sekinlashtiradi; UPDATE va DELETE uchun ham trigger kerak

B. Ilova kodida

DB::transaction(function () use ($data) {
    $sharh = Sharh::create($data);
    $sharh->mahsulot->increment('sharhlar_soni');
    $sharh->mahsulot->update(['reyting' => $sharh->mahsulot->sharhlar()->avg('baho')]);
});

✅ Ko'rinadigan, test qilinadigan ❌ Boshqa manbalar (import, qo'lda SQL) uni chetlab o'tadi

C. Davriy qayta hisoblash

-- Har soatda (Event yoki cron)
UPDATE mahsulotlar p
JOIN (
    SELECT mahsulot_id, ROUND(AVG(baho), 1) AS r, COUNT(*) AS n
    FROM sharhlar GROUP BY mahsulot_id
) s ON s.mahsulot_id = p.id
SET p.reyting = s.r, p.sharhlar_soni = s.n;

✅ Oddiy, ishonchli, xatolarni o'z-o'zidan tuzatadi ❌ Ma'lumot bir soatgacha eskirgan bo'lishi mumkin

Qaysi biri? Ko'pincha kombinatsiya: tez yangilanish uchun A yoki B, va har kecha C — vaqt o'tib to'plangan xatolarni tuzatish uchun.

Savolni bering: "Bu raqam 1 soatga eskirsa — kimdir zarar ko'radimi?" Reyting — yo'q. Bank balansi — ha. Javobga qarab usul tanlanadi.

Denormalizatsiyadan oldin sinab ko'ring

Ko'pincha muammo boshqa yo'l bilan hal bo'ladi:

Denormalizatsiya — bularning hammasi yetmaganda.

OLTP va OLAP

Katta kompaniyalarda ikkalasi alohida: asosiy baza normallashtirilgan, analitika uchun ma'lumot muntazam ravishda alohida, denormallashtirilgan omborga (data warehouse) ko'chiriladi.

Nima uchun shunday?

Nima uchun "avval normallashtir, keyin kerak bo'lsa denormallashtir" — teskari tartibda emas?

Chunki normallashtirilgan sxemadan istalgan denormallashtirilgan ko'rinishni hosil qilish mumkin — JOIN, VIEW, hisobot jadvali bilan. Teskarisi esa qiyin: takrorlangan ma'lumot allaqachon bir-biriga zid bo'lib qolgan bo'lsa, qaysi biri to'g'ri ekanini bilib bo'lmaydi. Normallashtirilgan ma'lumot — haqiqat manbai. Denormalizatsiya — undan olingan, kerak bo'lsa qayta yasaladigan nusxa.

Xulosa

Eslab qoling:

• Denormalizatsiya — o'qish tezligi uchun ataylab takrorlash yoki oldindan hisoblash
• Faqat o'lchangan muammo uchun; avval indeks, kesh, replica
• Turlari: agregatlar (soni, reyting), takrorlangan ustunlar, hisobot jadvallari
• Tarixiy "surat" (sotilgan narx) — denormalizatsiya emas, to'g'ri dizayn
• Sinxronlash: trigger, ilova kodi, davriy qayta hisoblash — ko'pincha kombinatsiya
• "Bu raqam eskirsa kim zarar ko'radi?" — usulni shu belgilaydi

Keyingi dars

48-dars: ER Diagram — jadvallar va munosabatlarni chizish