EXPLAIN va EXPLAIN ANALYZE — so'rovni tahlil qilish

~20 daq · MySQL · Index va Performance

Muammodan boshlaylik

So'rov sekin. Siz indeks qo'shdingiz. Hali ham sekin. Yana bir indeks qo'shdingiz. Biroz tezlashdi... yoki tasodifmi?

Taxmin qilib optimizatsiya qilish — qorong'i xonada kalit qidirish. MySQL so'rovni qanday bajarishni rejalashtirganini ko'rsata oladi: qaysi indeksni ishlatadi, nechta qator o'qiydi, qayerda saralaydi. Buning uchun — EXPLAIN.

EXPLAIN — rejani ko'rish

So'rov oldiga EXPLAIN qo'shing:

EXPLAIN SELECT * FROM buyurtmalar WHERE mijoz_id = 2;

+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | buyurtmalar | ref  | mijoz_id      | mijoz_id | 4       | const |    2 | NULL  |
+----+-------------+-------------+------+---------------+----------+---------+-------+------+-------+

So'rov bajarilmaydi — faqat reja ko'rsatiladi. Production da ham xavfsiz.

Eng muhim ustunlar

type — qanday qidiradi

Yaxshidan yomonga:

type	Ma'nosi	Baho
`const` / `system`	Primary key yoki UNIQUE bo'yicha bitta qator	🟢 A'lo
`eq_ref`	JOIN da har bir qatorga bittadan (PK/UNIQUE orqali)	🟢 A'lo
`ref`	Oddiy indeks bo'yicha teng qiymat	🟢 Yaxshi
`range`	Indeks bo'yicha oraliq: `>`, `BETWEEN`, `IN`	🟡 Yaxshi
`index`	Butun indeksni ko'rib chiqish	🟠 Ehtiyot
`ALL`	Butun jadvalni ko'rib chiqish (full table scan)	🔴 Yomon

Katta jadvalda type: ALL — birinchi belgi.

key — qaysi indeks ishlatildi

possible_keys — ishlatish mumkin bo'lgan indekslar
key — MySQL tanlagan indeks
key: NULL — hech qanday indeks ishlatilmadi

rows — taxminan nechta qator o'qiladi

rows: 2          ← yaxshi
rows: 4800000    ← muammo

Bu taxmin (statistikaga asoslangan), aniq son emas. Lekin tartibi to'g'ri.

Extra — qo'shimcha ma'lumot

Extra	Ma'nosi	Baho
`Using index`	Covering index — jadvalga kirilmadi	🟢
`Using where`	Indeksdan keyin qo'shimcha filtr	🟡 Normal
`Using index condition`	Filtr indeks darajasida (ICP)	🟢
`Using temporary`	Vaqtinchalik jadval yaratildi (`GROUP BY`, `DISTINCT`)	🟠
`Using filesort`	Indekssiz saralash	🟠

Using temporary; Using filesort birga — katta jadvalda sekinlikning tez-tez uchraydigan sababi.

Misol: optimizatsiya jarayoni

EXPLAIN SELECT id, yaratilgan FROM buyurtmalar
WHERE holat = 'yangi'
ORDER BY yaratilgan DESC
LIMIT 20;

1-qadam — indekssiz:

type: ALL   key: NULL   rows: 4800000   Extra: Using where; Using filesort

Butun jadval o'qiladi, keyin saralanadi. 🔴

2-qadam — holat ga indeks:

CREATE INDEX idx_holat ON buyurtmalar (holat);

type: ref   key: idx_holat   rows: 310000   Extra: Using filesort

Qatorlar kamaydi, lekin 310 000 ta "yangi" buyurtmani saralash kerak. 🟡

3-qadam — composite indeks:

CREATE INDEX idx_holat_vaqt ON buyurtmalar (holat, yaratilgan);
DROP INDEX idx_holat ON buyurtmalar;   -- endi keraksiz (chap prefiks)

type: ref   key: idx_holat_vaqt   rows: 310000   Extra: Backward index scan

Using filesort yo'qoldi — natija indeksda allaqachon tartiblangan. LIMIT 20 tufayli MySQL faqat 20 ta yozuvni o'qiydi va to'xtaydi. 🟢

(rows hali ham katta ko'rinadi — chunki EXPLAIN taxmini LIMIT ni har doim hisobga olmaydi. Haqiqatni EXPLAIN ANALYZE ko'rsatadi.)

EXPLAIN ANALYZE — haqiqiy bajarilish (MySQL 8.0.18+)

EXPLAIN — reja (taxmin). EXPLAIN ANALYZE — so'rovni haqiqatan bajaradi va har bir qadamda qancha vaqt va nechta qator bo'lganini ko'rsatadi:

EXPLAIN ANALYZE
SELECT m.ism, COUNT(*) FROM mijozlar m
JOIN buyurtmalar b ON b.mijoz_id = m.id
GROUP BY m.id;

-> Table scan on <temporary>  (actual time=0.1..0.1 rows=3 loops=1)
    -> Aggregate using temporary table  (actual time=0.09..0.09 rows=3 loops=1)
        -> Nested loop inner join  (cost=2.5 rows=5) (actual time=0.04..0.07 rows=5 loops=1)
            -> Table scan on m  (cost=0.65 rows=4) (actual time=0.02..0.03 rows=4 loops=1)
            -> Index lookup on b using mijoz_id (mijoz_id=m.id)  (cost=0.3 rows=1.25)
               (actual time=0.008..0.009 rows=1.25 loops=4)

Daraxtni ichkaridan tashqariga (pastdan yuqoriga) o'qing:

actual time=boshlanish..tugash — millisekundlarda
rows — haqiqiy qatorlar soni
loops — necha marta bajarildi
cost=... rows=... — rejadagi taxmin; actual bilan katta farq — statistika eskirgan

[!WARNING] EXPLAIN ANALYZE so'rovni haqiqatan bajaradi. SELECT uchun xavfsiz, lekin og'ir so'rovni production da ishga tushirish serverga yuk beradi. UPDATE/DELETE bilan ishlatmang.

FORMAT=TREE

EXPLAIN ning o'zi ham daraxt ko'rinishida chiqishi mumkin:

EXPLAIN FORMAT=TREE SELECT ...;

JOIN lar tahlili

EXPLAIN
SELECT p.nom, SUM(q.soni)
FROM buyurtma_qatorlari q
JOIN mahsulotlar p ON p.id = q.mahsulot_id
GROUP BY p.id;

| table | type   | key     | rows |
| q     | ALL    | NULL    | 8    |
| p     | eq_ref | PRIMARY | 1    |

Har bir qator — bitta jadval, bajarilish tartibida. q to'liq o'qiladi (8 qator — kichik, normal), har bir qator uchun p dan primary key bo'yicha bitta qator (eq_ref) — ideal.

Katta jadvalda ichki jadval type: ALL bo'lsa — JOIN ustunida indeks yo'q. rows lar ko'paytmasi taxminan umumiy ishni ko'rsatadi: 1000 × 50000 — xavfli.

Statistikani yangilash

MySQL rejani jadval statistikasiga qarab tuzadi. Katta import yoki o'chirishdan keyin statistika eskirishi va MySQL noto'g'ri indeksni tanlashi mumkin:

ANALYZE TABLE buyurtmalar;

Indeksni majburlash (oxirgi chora)

SELECT * FROM buyurtmalar FORCE INDEX (idx_holat_vaqt) WHERE ...;
SELECT * FROM buyurtmalar IGNORE INDEX (idx_holat) WHERE ...;

Odatda optimizator sizdan yaxshiroq biladi. FORCE INDEX — faqat EXPLAIN ANALYZE bilan noto'g'ri tanlov isbotlanganda va ANALYZE TABLE yordam bermaganda. Ma'lumot o'zgargach, majburlangan indeks yomon tanlovga aylanishi mumkin.

Optimizatsiya tartibi

Sekin so'rovni toping (33-dars, slow query log)
EXPLAIN — type: ALL, key: NULL, katta rows, Using filesort / Using temporary ni qidiring
Gipoteza: "(holat, yaratilgan) indeks kerak"
O'zgartiring
EXPLAIN ANALYZE bilan o'lchang — haqiqatan tezlashdimi?
Keraksiz bo'lib qolgan indekslarni o'chiring

Nima uchun shunday?

Nima uchun bir xil so'rov uchun MySQL bugun bir indeksni, ertaga boshqasini tanlashi mumkin?

MySQL optimizatori — xarajatga asoslangan (cost-based): har bir mumkin bo'lgan rejaning "narxini" statistika asosida hisoblab, eng arzonini tanlaydi. Statistika o'zgarsa — narxlar o'zgaradi. Masalan, "yangi" holatidagi buyurtmalar 1% bo'lsa — indeks foydali; 60% bo'lsa — butun jadvalni o'qish arzonroq. Bu aqlli xatti-harakat — lekin shuning uchun optimizatsiyani doim real ma'lumotlar ustida tekshirish kerak, 10 qatorli test bazada emas.

Xulosa

Eslab qoling:

EXPLAIN so'rov — reja, bajarilmaydi
type: const > eq_ref > ref > range > index > ALL
key: NULL va type: ALL katta jadvalda — indeks kerak
Extra: Using index 🟢, Using filesort / Using temporary 🟠
EXPLAIN ANALYZE — haqiqiy vaqt va qatorlar (so'rovni bajaradi!)
ANALYZE TABLE — statistikani yangilash
O'lchang, taxmin qilmang

Keyingi dars

33-dars: Slow Query Log — sekin so'rovlarni topish