EXPLAIN bilan so'rovni tahlil qilish - Ma'lumotlar bazasini loyihalash

EXPLAIN - ma'lumotlar bazasining rejasini ko'rsatadi. Uni o'qishni bilish - so'rovni tezlashtirishning birinchi qadami.

SQL

CREATE TABLE sonlar (n INT PRIMARY KEY);

INSERT INTO sonlar (n)
WITH RECURSIVE k AS (
    SELECT 1 AS n UNION ALL SELECT n + 1 FROM k WHERE n < 50000
)
SELECT n FROM k;

CREATE TABLE mijozlar (
    id     INT PRIMARY KEY,
    ism    VARCHAR(50) NOT NULL,
    shahar VARCHAR(30) NOT NULL,
    email  VARCHAR(80) NOT NULL,
    UNIQUE KEY uq_email (email),
    KEY idx_shahar (shahar)
);

INSERT INTO mijozlar (id, ism, shahar, email)
SELECT n,
       CONCAT('Mijoz', n),
       ELT(1 + (n % 5), 'Namangan', 'Toshkent', 'Andijon', 'Farg''ona', 'Buxoro'),
       CONCAT('mijoz', n, '@pochta.uz')
FROM sonlar WHERE n <= 10000;

CREATE TABLE buyurtmalar (
    id       INT PRIMARY KEY,
    mijoz_id INT NOT NULL,
    summa    INT NOT NULL,
    sana     DATE NOT NULL,
    KEY idx_mijoz (mijoz_id),
    CONSTRAINT fk_b_m FOREIGN KEY (mijoz_id) REFERENCES mijozlar(id)
);

INSERT INTO buyurtmalar (id, mijoz_id, summa, sana)
SELECT n,
       1 + (n % 10000),
       10000 + (n % 100) * 1000,
       DATE_ADD('2026-01-01', INTERVAL (n % 365) DAY)
FROM sonlar;

Birinchi `EXPLAIN` #

SQL

EXPLAIN SELECT * FROM mijozlar WHERE id = 500;

Natija

+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | mijozlar | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

Ustun	Nima anglatadi
`select_type`	So'rov turi (`SIMPLE`, `SUBQUERY`, ...)
`table`	Qaysi jadval
`type`	Kirish usuli - eng muhim ustun
`possible_keys`	Ishlatilishi mumkin bo'lgan indekslar
`key`	Haqiqatan tanlangan indeks
`key_len`	Indeksning necha bayti ishlatildi
`ref`	Nima bilan solishtirilmoqda
`rows`	Taxminan nechta qator o'qiladi
`Extra`	Qo'shimcha amallar

Kirish turlari - yaxshidan yomonga #

type ustunini birinchi o'qing - u eng ko'p ma'lumot beradi

SQL

-- ref: indeks bo'yicha bir necha qator
EXPLAIN SELECT id FROM mijozlar WHERE shahar = 'Andijon';

Natija

+------+-------------+----------+------+---------------+------------+---------+-------+------+--------------------------+
| id   | select_type | table    | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+------+-------------+----------+------+---------------+------------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | mijozlar | ref  | idx_shahar    | idx_shahar | 122     | const | 2000 | Using where; Using index |
+------+-------------+----------+------+---------------+------------+---------+-------+------+--------------------------+

SQL

-- range: diapazon
EXPLAIN SELECT id FROM buyurtmalar WHERE id BETWEEN 100 AND 200;

Natija

+------+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | buyurtmalar | range | PRIMARY       | PRIMARY | 4       | NULL | 101  | Using where; Using index |
+------+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+

SQL

-- ALL: indekssiz ustun
EXPLAIN SELECT id FROM buyurtmalar WHERE summa = 50000;

Natija

+------+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | buyurtmalar | ALL  | NULL          | NULL | NULL    | NULL | 50000 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+-------+-------------+

rows - taxmin, aniq son emas

Bu misolda rows = 50000 - jadvaldagi qatorlar soniga aniq mos keldi. Lekin bu tasodif.

EXPLAIN statistikaga asoslanadi va aniq son bermaydi. Quyiroqda mijozlar jadvali uchun rows = 9987 chiqadi - aslida u yerda 10 000 qator bor. Farq odatda kichik, lekin ba'zan sezilarli bo'ladi.

Statistikani yangilash:

SQL

ANALYZE TABLE buyurtmalar;

Agar rows haqiqatdan juda farq qilsa, optimizator noto'g'ri reja tanlashi mumkin. Bu sekin so'rovlarning ko'p uchraydigan sababi.

Haqiqiy sonni ko'rish uchun MariaDB da:

SQL

ANALYZE SELECT id FROM buyurtmalar WHERE summa = 50000;

ANALYZE (EXPLAIN emas) so'rovni haqiqatan bajaradi va r_rows ustunida haqiqiy sonni ko'rsatadi. MySQL 8 da bu EXPLAIN ANALYZE deb ataladi.

`JOIN` rejasi #

SQL

EXPLAIN SELECT m.ism, b.summa
FROM buyurtmalar b
JOIN mijozlar m ON m.id = b.mijoz_id
WHERE b.id = 42;

Natija

+------+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | b     | const | PRIMARY,idx_mijoz | PRIMARY | 4       | const | 1    |       |
|    1 | SIMPLE      | m     | const | PRIMARY           | PRIMARY | 4       | const | 1    |       |
+------+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

Ikkala jadval ham const - eng tez holat.

SQL

-- Tashqi kalit indeksi bo'lmasa nima bo'lardi
EXPLAIN SELECT m.ism, COUNT(*) AS soni
FROM mijozlar m
JOIN buyurtmalar b ON b.mijoz_id = m.id
WHERE m.shahar = 'Buxoro'
GROUP BY m.id, m.ism;

Natija

+------+-------------+-------+------+--------------------+------------+---------+-------------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys      | key        | key_len | ref               | rows | Extra                                                               |
+------+-------------+-------+------+--------------------+------------+---------+-------------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | m     | ref  | PRIMARY,idx_shahar | idx_shahar | 122     | const             | 2000 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | b     | ref  | idx_mijoz          | idx_mijoz  | 4       | sr_sql_check.m.id | 1    | Using index                                                         |
+------+-------------+-------+------+--------------------+------------+---------+-------------------+------+---------------------------------------------------------------------+

Using temporary va Using filesort

Bu ikki belgi qo'shimcha ish bajarilganini bildiradi:

Belgi	Nima bo'lmoqda
`Using temporary`	Vaqtinchalik jadval yaratildi (odatda `GROUP BY` uchun)
`Using filesort`	Natija alohida saralandi

"Filesort" nomi chalg'ituvchi - u har doim ham diskda bo'lmaydi. Kichik natija xotirada saralanadi, katta natija esa diskka tushadi va ancha sekinlashadi.

Ular har doim ham muammo emas - kichik natijada tez bajariladi. Lekin rows katta bo'lsa, jiddiy to'siq bo'ladi.

Ulardan qutulish usullari:

SQL

-- ORDER BY ustuni indeksda bo'lsa, filesort kerak emas
CREATE INDEX idx_sana ON buyurtmalar (sana);
SELECT * FROM buyurtmalar ORDER BY sana LIMIT 10;

-- GROUP BY ustuni indeksda bo'lsa, temporary kerak emas
CREATE INDEX idx_mijoz_summa ON buyurtmalar (mijoz_id, summa);

Indeks tartiblangan bo'lgani uchun (14-bo'lim), saralash bepul bo'ladi.

Indeksni buzadigan yozuvlar #

SQL

-- YOMON: ustunga funksiya qo'llanilgan
EXPLAIN SELECT id FROM buyurtmalar WHERE YEAR(sana) = 2026;

Natija

+------+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | buyurtmalar | ALL  | NULL          | NULL | NULL    | NULL | 50000 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+-------+-------------+

SQL

CREATE INDEX idx_sana ON buyurtmalar (sana);

SQL

-- Indeks bor, lekin funksiya uni buzadi
EXPLAIN SELECT id FROM buyurtmalar WHERE YEAR(sana) = 2026;

Natija

+------+-------------+-------------+-------+---------------+----------+---------+------+-------+--------------------------+
| id   | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows  | Extra                    |
+------+-------------+-------------+-------+---------------+----------+---------+------+-------+--------------------------+
|    1 | SIMPLE      | buyurtmalar | index | NULL          | idx_sana | 3       | NULL | 50000 | Using where; Using index |
+------+-------------+-------------+-------+---------------+----------+---------+------+-------+--------------------------+

SQL

-- YAXSHI: diapazon sifatida yozilgan
EXPLAIN SELECT id FROM buyurtmalar
WHERE sana >= '2026-01-01' AND sana < '2027-01-01';

Natija

+------+-------------+-------------+-------+---------------+----------+---------+------+-------+--------------------------+
| id   | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows  | Extra                    |
+------+-------------+-------------+-------+---------------+----------+---------+------+-------+--------------------------+
|    1 | SIMPLE      | buyurtmalar | range | idx_sana      | idx_sana | 3       | NULL | 25000 | Using where; Using index |
+------+-------------+-------------+-------+---------------+----------+---------+------+-------+--------------------------+

Ustunga funksiya qo'llasangiz - indeks o'ladi

Indeks ustunning qiymatini saqlaydi, YEAR(ustun) ni emas. Shuning uchun quyidagilar indeksni buzadi:

Yomon	Yaxshi
`WHERE YEAR(sana) = 2026`	`WHERE sana >= '2026-01-01' AND sana < '2027-01-01'`
`WHERE DATE(yaratilgan) = '2026-09-10'`	`WHERE yaratilgan >= '2026-09-10' AND yaratilgan < '2026-09-11'`
`WHERE UPPER(ism) = 'ALI'`	`WHERE ism = 'Ali'` (registrsiz collation bilan)
`WHERE narx * 1.12 > 1000`	`WHERE narx > 1000 / 1.12`
`WHERE CONCAT(a, b) = 'xy'`	Alohida tekshiring
`WHERE id + 0 = 5`	`WHERE id = 5`

Yuqoridagi misolda YEAR(sana) bilan type index bo'ldi - butun indeksni skanerlash. Diapazon bilan esa range - haqiqiy qidiruv.

Qoida: ustunni yolg'iz qoldiring, o'zgartirishni ifodaning ikkinchi tomoniga o'tkazing.

Ba'zan funksiya muqarrar bo'ladi - unda generatsiyalangan ustun va unga indeks yarating:

SQL

ALTER TABLE buyurtmalar
    ADD yil SMALLINT AS (YEAR(sana)) STORED,
    ADD INDEX idx_yil (yil);

Tur mos kelmasligi #

SQL

-- email VARCHAR, lekin son bilan solishtirilmoqda
EXPLAIN SELECT id FROM mijozlar WHERE email = 12345;

Natija

+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
|    1 | SIMPLE      | mijozlar | index | uq_email      | uq_email | 322     | NULL | 9987 | Using where; Using index |
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+

SQL

-- To'g'ri tur bilan
EXPLAIN SELECT id FROM mijozlar WHERE email = '[email protected]';

Natija

+------+-------------+----------+-------+---------------+----------+---------+-------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref   | rows | Extra       |
+------+-------------+----------+-------+---------------+----------+---------+-------+------+-------------+
|    1 | SIMPLE      | mijozlar | const | uq_email      | uq_email | 322     | const | 1    | Using index |
+------+-------------+----------+-------+---------------+----------+---------+-------+------+-------------+

Tur mos kelmasa indeks ishlamaydi

Birinchi so'rovda type index (butun indeksni skanerlash), ikkinchisida const (bitta qator).

Sabab: email = 12345 da MariaDB har bir email ni songa aylantirib solishtirishga majbur - bu funksiya qo'llash bilan teng.

Bu xato ayniqsa dinamik so'rovlarda uchraydi:

PHP

// Xavfli - $id satr bo'lsa
$db->query("SELECT * FROM t WHERE son_ustun = '$id'");

Yoki teskarisi - satr ustunga son berish.

Yechim - parametrlarni to'g'ri turda uzatish:

PHP

$stmt->bindValue(1, (int) $id, PDO::PARAM_INT);

Xuddi shu muammo JOIN da ham bo'ladi:

SQL

-- a.kod VARCHAR(10), b.kod INT bo'lsa - indeks ishlamaydi
JOIN b ON b.kod = a.kod

Bog'langan ustunlar bir xil turda bo'lishi kerak - bu loyihalash qoidasi.

`LIKE` va indeks #

SQL

-- Prefiks bo'yicha - indeks ISHLAYDI
EXPLAIN SELECT id FROM mijozlar WHERE email LIKE 'mijoz500%';

Natija

+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
|    1 | SIMPLE      | mijozlar | range | uq_email      | uq_email | 322     | NULL | 11   | Using where; Using index |
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+

SQL

-- Boshida % bo'lsa - indeks YARAMAYDI
EXPLAIN SELECT id FROM mijozlar WHERE email LIKE '%[email protected]';

Natija

+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
|    1 | SIMPLE      | mijozlar | index | NULL          | uq_email | 322     | NULL | 9987 | Using where; Using index |
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+

range (11 qator) va index (10 000 qator) - farq katta.

Matn ichidan qidirish uchun LIKE emas

LIKE '%matn%' katta jadvalda hech qachon tez bo'lmaydi.

Muqobillar:

Ehtiyoj	Yechim
So'z bo'yicha qidirish	`FULLTEXT` indeks
Murakkab qidiruv	Elasticsearch, Meilisearch
Oxiridan qidirish	Teskari satrni alohida ustunda saqlash
Qisqa ro'yxat	`LIKE` ham yetarli

FULLTEXT misoli:

SQL

ALTER TABLE maqolalar ADD FULLTEXT KEY ft_matn (sarlavha, matn);

SELECT * FROM maqolalar
WHERE MATCH(sarlavha, matn) AGAINST('dasturlash' IN NATURAL LANGUAGE MODE);

FULLTEXT so'zlarni ajratib indekslaydi, shuning uchun so'z bo'yicha qidiruv tez ishlaydi. Lekin u qism-so'zni topmaydi va tilga bog'liq nozikliklar bor.

Sekin so'rovlar jurnali #

SQL

SELECT @@slow_query_log AS yoqilganmi,
       @@long_query_time AS chegara_soniya;

Natija

+------------+----------------+
| yoqilganmi | chegara_soniya |
+------------+----------------+
|          0 |      10.000000 |
+------------+----------------+

Sekin so'rovlarni topish

Ishlab chiqarishda qaysi so'rov sekin ekanini taxmin qilmang - o'lchang.

SQL

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;      -- 500 ms dan sekin
SET GLOBAL log_queries_not_using_indexes = 1;

Jurnalni tahlil qilish:

Terminal

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Bu eng ko'p vaqt olgan 10 ta so'rov turini ko'rsatadi.

MariaDB 10.x da performance_schema orqali ham ko'rish mumkin:

SQL

SELECT digest_text, count_star, avg_timer_wait / 1e9 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;

Muhim: long_query_time ni juda past qo'ysangiz, jurnal tez to'ladi va diskni to'ldirishi mumkin. Ishlab chiqarishda 0.5 yoki 1 soniya odatda to'g'ri.

Optimallashtirish tartibi #

So'rovni tezlashtirish qadamlari

Qadam	Nima qilinadi
1	O'lchang - sekin so'rovlar jurnali
2	`EXPLAIN` bilan rejani ko'ring
3	`type` `ALL` bo'lsa - indeks kerakmi?
4	`Using filesort`/`temporary` bormi?
5	`WHERE` da funksiya bormi?
6	Turlar mos keladimi?
7	Indeks qo'shing va qayta o'lchang
8	Foyda bo'lmasa - indeksni o'chiring

Oxirgi qadam ko'pincha unutiladi. Yordam bermagan indeks faqat zarar keltiradi (14-bo'lim).

Ba'zan yechim indeksda emas:

So'rovni qayta yozish (IN o'rniga JOIN);
Kerak bo'lmagan ustunlarni olib tashlash (SELECT * emas);
Natijani keshlash;
Sxemani o'zgartirish (9-bo'limdagi denormalizatsiya).

EXPLAIN faqat rejani ko'rsatadi - u so'rov qanchalik tez ekanini aytmaydi. Har doim haqiqiy vaqtni ham o'lchang.

Amaliy topshiriq

EXPLAIN ning to'qqiz ustunini tushuntiring.
const, ref, range, ALL turlarini ko'rsating.
rows nima uchun taxminiy ekanini ayting.
Using temporary va Using filesort chiqadigan so'rov yozing.
YEAR(sana) bilan diapazon so'rovini taqqoslang.
Indeksni buzadigan beshta yozuvni sanang.
Tur mos kelmasligi indeksni buzishini isbotlang.
LIKE 'abc%' va LIKE '%abc' rejalarini solishtiring.
FULLTEXT qachon kerakligini tushuntiring.
Optimallashtirishning sakkiz qadamini sanang.

Xulosa #

type ustuni eng ko'p ma'lumot beradi.
Tartib: const → eq_ref → ref → range → index → ALL.
rows - statistikaga asoslangan taxmin.
Haqiqiy sonni ANALYZE SELECT ko'rsatadi.
Using index yaxshi, Using filesort/temporary yomon.
Ustunga funksiya qo'llash indeksni buzadi.
Diapazonni YEAR() emas, >= va < bilan yozing.
Tur mos kelmasa indeks ishlamaydi - JOIN da ham.
LIKE 'abc%' indeksdan foydalanadi, LIKE '%abc' yo'q.
Matn qidiruvi uchun FULLTEXT yoki qidiruv tizimi.
Avval o'lchang, keyin optimallashtiring.
Yordam bermagan indeksni o'chiring.

Keyingi bo'limda tranzaksiyalar va ACID xossalarini ko'ramiz.

Birinchi EXPLAIN #

Kirish turlari - yaxshidan yomonga #

JOIN rejasi #

Indeksni buzadigan yozuvlar #

Tur mos kelmasligi #

LIKE va indeks #

Sekin so'rovlar jurnali #

Optimallashtirish tartibi #

Xulosa #

Xatolik topdingizmi?

Birinchi `EXPLAIN` #

`JOIN` rejasi #

`LIKE` va indeks #