Query optimizatsiya — N+1, covering index, qoidalar

~25 daq · MySQL · Index va Performance

Muammodan boshlaylik

Slow query log ishga tushdi (33-dars), EXPLAIN ni o'qishni bilasiz (32-dars). Endi amaliy savol: qanday qilib so'rovni tezlashtirish kerak?

Bu darsda real loyihalarda eng ko'p uchraydigan muammolar va ularning yechimlarini ko'ramiz. Ko'pchilik holatda tezlik "qiyin optimizatsiya" dan emas, bir nechta oddiy qoidaga rioya qilishdan keladi.

1. Kamroq o'qing

Faqat kerakli ustunlar

-- ❌ 40 ta ustun, jumladan katta TEXT
SELECT * FROM mahsulotlar WHERE toifa_id = 1;

-- ✅ Katalog uchun faqat kerakligi
SELECT id, nom, narx FROM mahsulotlar WHERE toifa_id = 1;

Kamroq ma'lumot — kamroq disk o'qish, tarmoq, xotira. Va covering index imkoniyati (31-dars).

Faqat kerakli qatorlar

-- ❌ Hammasini olib, ilovada 10 tasini ko'rsatish
SELECT id, nom FROM mahsulotlar ORDER BY narx DESC;

-- ✅
SELECT id, nom FROM mahsulotlar ORDER BY narx DESC LIMIT 10;

Mavjudlikni tekshirish

-- ❌ Hammasini sanaydi
SELECT COUNT(*) FROM buyurtmalar WHERE mijoz_id = 42;   -- > 0 mi?

-- ✅ Birinchisida to'xtaydi
SELECT EXISTS (SELECT 1 FROM buyurtmalar WHERE mijoz_id = 42);

2. Sargable shartlar

Sargable (Search ARGument ABLE) — indeksdan foydalana oladigan shart. Asosiy qoida: indekslangan ustun WHERE da o'zgartirilmasin.

OR va indekslar

-- ⚠️ Ikki xil ustun bo'yicha OR — ko'pincha full scan
SELECT * FROM mijozlar WHERE email = ? OR telefon = ?;

-- ✅ Har biri o'z indeksini ishlatadi
SELECT * FROM mijozlar WHERE email = ?
UNION
SELECT * FROM mijozlar WHERE telefon = ?;

MySQL ba'zan "index merge" bilan o'zi hal qiladi — EXPLAIN bilan tekshiring.

3. N+1 muammosi

Ilovadagi eng ko'p uchraydigan sekinlik manbai. Kod:

buyurtmalar = SELECT * FROM buyurtmalar ORDER BY id DESC LIMIT 50      ← 1 so'rov
har bir buyurtma uchun:
    mijoz = SELECT * FROM mijozlar WHERE id = buyurtma.mijoz_id        ← 50 so'rov

51 ta so'rov. Har biri 1 ms bo'lsa ham — tarmoq kechikishi bilan 50-100 ms. Sahifada bir nechta shunday ro'yxat bo'lsa — soniyalar.

Har bir so'rov alohida tez — slow query log ularni ko'rsatmaydi! Faqat ilova darajasidagi kuzatuv (Debugbar, Telescope) yoki performance_schema dagi COUNT_STAR ko'rsatadi.

Yechim 1: JOIN

SELECT b.id, b.holat, m.ism
FROM buyurtmalar b
JOIN mijozlar m ON m.id = b.mijoz_id
ORDER BY b.id DESC LIMIT 50;

Yechim 2: IN bilan ikkinchi so'rov (eager loading)

SELECT * FROM buyurtmalar ORDER BY id DESC LIMIT 50;
SELECT * FROM mijozlar WHERE id IN (1, 2, 3, ...);   -- 50 ta noyob mijoz_id

2 ta so'rov, 51 emas. ORM lardagi yechim:

Laravel:  Buyurtma::with('mijoz')->latest()->take(50)->get();
Prisma:   prisma.buyurtma.findMany({ include: { mijoz: true }, take: 50 })
Sequelize: Buyurtma.findAll({ include: Mijoz, limit: 50 })

4. Indekslarni so'rovlarga moslash

Indeks — jadvalga emas, so'rovga qo'yiladi. Ilovadagi eng muhim so'rovlarni oling va har biri uchun:

-- So'rov
SELECT id, holat, yaratilgan FROM buyurtmalar
WHERE mijoz_id = ? AND holat IN ('yangi', 'tolangan')
ORDER BY yaratilgan DESC
LIMIT 10;

-- Ideal indeks: tenglik → IN → saralash, + covering uchun qolgan ustunlar
CREATE INDEX idx_mijoz_holat_vaqt ON buyurtmalar (mijoz_id, holat, yaratilgan);

id — primary key, u har bir secondary indeksda avtomatik bor. Shuning uchun bu indeks covering — EXPLAIN da Using index.

Ortiqcha indekslarni tozalash

-- (mijoz_id) va (mijoz_id, holat) ikkalasi bor bo'lsa — birinchisi ortiqcha
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'dokon';

5. Sahifalash

-- ❌ 10 000-sahifa: 200 000 qator o'qiladi, tashlab yuboriladi
SELECT id, nom FROM mahsulotlar ORDER BY id LIMIT 20 OFFSET 200000;

-- ✅ Keyset (11-dars)
SELECT id, nom FROM mahsulotlar WHERE id > 200000 ORDER BY id LIMIT 20;

Deferred join — OFFSET kerak bo'lsa, avval faqat ID larni (covering index orqali), keyin qatorlarni olish:

SELECT p.id, p.nom, p.narx, p.tavsif
FROM mahsulotlar p
JOIN (
    SELECT id FROM mahsulotlar ORDER BY narx LIMIT 20 OFFSET 200000
) AS t USING (id);

6. Hisoblarni oldindan tayyorlash

Bosh sahifada "Bugungi tushum", "Jami mijozlar" — har bir sahifa ochilishida millionlab qatorni SUM qilish keraksiz.

Variant A — kesh (ilovada): natijani Redis da 5 daqiqa saqlash.

Variant B — hisobot jadvali:

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

-- Har kecha (Event Scheduler, 44-dars yoki cron)
INSERT INTO kunlik_statistika (kun, buyurtmalar, tushum)
SELECT * FROM (
    SELECT DATE(b.yaratilgan) AS kun, COUNT(DISTINCT b.id) AS soni, SUM(q.soni * q.narx) AS jami
    FROM buyurtmalar b JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    WHERE b.yaratilgan >= CURDATE() - INTERVAL 1 DAY AND b.yaratilgan < CURDATE()
      AND b.holat <> 'bekor'
    GROUP BY DATE(b.yaratilgan)
) AS yangi
ON DUPLICATE KEY UPDATE buyurtmalar = yangi.soni, tushum = yangi.jami;

Yillik hisobot — 5 million qator emas, 365 qator.

Bu denormalizatsiya (47-dars) — ataylab qilinadigan takrorlash. Faqat haqiqatan kerak bo'lganda.

7. Katta yozish amallari

-- ❌ Million qatorni bitta so'rovda — jadval uzoq qulflanadi
DELETE FROM loglar WHERE vaqt < '2025-01-01';

-- ✅ Bo'laklarga bo'lib, sikl bilan
DELETE FROM loglar WHERE vaqt < '2025-01-01' LIMIT 5000;
-- 0 rows affected bo'lguncha takrorlash

Ko'p INSERT — bitta so'rovda (9-dars), yoki bitta tranzaksiya ichida:

START TRANSACTION;
INSERT ...; INSERT ...; -- 1000 marta
COMMIT;

Har bir INSERT alohida tranzaksiya bo'lsa — har biri diskka fsync qiladi. Bitta tranzaksiyada — bir marta.

8. Server sozlamalari — eng muhimi

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

innodb_buffer_pool_size — MySQL ma'lumot va indekslarni xotirada keshlaydigan joy. Standart qiymat (128 MB) production uchun juda kichik. Faqat baza ishlaydigan serverda — RAM ning 50-70% i.

[mysqld]
innodb_buffer_pool_size = 4G

Ish to'plami (tez-tez o'qiladigan ma'lumot) xotiraga sig'sa — disk deyarli o'qilmaydi va so'rovlar o'nlab marta tezlashadi.

Optimizatsiya ro'yxati

Nima uchun shunday?

Nima uchun "avval to'g'ri, keyin tez" degan qoida bor — boshidanoq optimal yozish yaxshiroq emasmi?

Chunki qaysi so'rov sekin bo'lishini oldindan bilish deyarli imkonsiz — u real ma'lumot va real foydalanuvchilar xatti-harakatiga bog'liq. Boshidanoq hamma narsani "optimallashtirish" — murakkab, o'qish qiyin kod va keraksiz indekslar. Lekin bu darsdagi asosiy qoidalar (kerakli ustunlar, sargable shartlar, N+1 dan qochish, foreign key indekslari) — "optimizatsiya" emas, gigiena. Ularni boshidan qiling. Qolgan hamma narsani — o'lchab, keyin.

Xulosa

Eslab qoling:

• Kamroq o'qing: kerakli ustunlar, LIMIT, EXISTS
• Sargable: ustunni funksiyaga o'ramang, hisobni qiymat tomoniga o'tkazing
• N+1 — slow log da ko'rinmaydi; JOIN yoki IN bilan eager loading
• Indeks — so'rovga moslab; covering index — Using index
• Chuqur sahifalar — keyset yoki deferred join
• Og'ir hisobotlar — kesh yoki oldindan hisoblangan jadval
• Katta o'chirish — bo'laklarda; ko'p INSERT — bitta tranzaksiyada
• innodb_buffer_pool_size — RAM ning 50-70%

Keyingi dars

35-dars: Table Partitioning — katta jadvallarni bo'lish