MySQL / EXPLAIN ба query оновчлол

EXPLAIN ба query оновчлол

Удаан query нь хэрэглэгчийн туршлагыг муутгах гол шалтгаан. EXPLAIN нь MySQL query-г яаж гүйцэтгэж байгааг харуулдаг — index ашиглаж байгаа эсэх, хэдэн мөр шалгаж байгааг тод харуулна.

Суурь жишээ

sql

CREATE TABLE orders (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  user_id    INT NOT NULL,
  product_id INT NOT NULL,
  amount     DECIMAL(10,2),
  status     VARCHAR(20),
  created_at DATETIME DEFAULT NOW()
);

-- 500,000 мөр оруулсан гэж үзье

EXPLAIN ашиглах

sql

EXPLAIN SELECT * FROM orders WHERE status = 'paid';

код

id | select_type | table  | type | possible_keys | key  | rows   | Extra
---|-------------|--------|------|---------------|------|--------|------
1  | SIMPLE      | orders | ALL  | NULL          | NULL | 498234 | Using where

type = ALL ба key = NULL — full table scan, 498,234 мөр бүгдийг шалгаж байна. Муу!

type багана — хамгийн чухал үзүүлэлт

type нь MySQL хэрхэн өгөгдөл олж байгааг харуулна. Дээрээс доош руу чанар буурна:

| type | Утга | Хурд | | -------- | ----------------------------------------- | --------------------- | | const | PRIMARY KEY эсвэл UNIQUE-ээр нэг мөр | ⚡⚡⚡ Хамгийн хурдан | | eq_ref | JOIN дахь PRIMARY KEY / UNIQUE | ⚡⚡⚡ | | ref | Index-ээр олон мөр | ⚡⚡ Сайн | | range | Index-ийн хүрээгээр (>, <, BETWEEN) | ⚡⚡ | | index | Бүх index уншина (index scan) | ⚡ | | ALL | Бүх мөр уншина (full table scan) | 🐌 Муу |

Зорилго: ALL ба index-ийг арилгах, ref буюу const болгох.

key багана — ашигласан index

sql

-- status-д index нэмье
CREATE INDEX idx_status ON orders (status);

-- Дахин шалгая
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

код

id | type | key        | rows  | Extra
---|------|------------|-------|------
1  | ref  | idx_status | 12453 | Using index condition

type нь ALL → ref болж, rows нь 498,234 → 12,453 болов. Index ажилласан.

possible_keys vs key

sql

EXPLAIN SELECT * FROM orders WHERE status = 'paid' AND user_id = 42;

код

possible_keys          | key
-----------------------|----------
idx_status,idx_user_id | idx_user_id

possible_keys — ашиглаж болох index-үүд key — MySQL сонгосон index

MySQL нь статистик дээр үндэслэн хамгийн үр дүнтэй index-ийг сонгодог. Заримдаа буруу сонгодог — тийм үед FORCE INDEX ашиглаж болно.

EXPLAIN ANALYZE — бодит гүйцэтгэл

MySQL 8.0-аас EXPLAIN ANALYZE нь query-г бодитоор ажиллуулж, таамаглал ба бодит хугацааг харьцуулна:

sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'paid';

код

-> Index lookup on orders using idx_status (status='paid')
   (cost=1245.30 rows=12453)
   (actual time=0.842..18.234 rows=12891 loops=1)

cost — MySQL-н таамаглал, actual time — бодит гүйцэтгэлийн мс.

Нийтлэг асуудал ба засах арга

1. WHERE дахь функц — index тасална

sql

-- МУУ: YEAR() функц index ашиглахгүй болгодог
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2025;
-- type = ALL

-- САЙН: хүрээгээр хайх
EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31 23:59:59';
-- type = range, index ашиглана

2. LIKE эхэнд `%` — index тасална

sql

-- МУУ: эхэнд % байвал index ашиглахгүй
SELECT * FROM users WHERE email LIKE '%gmail.com';
-- type = ALL

-- САЙН: эхэнд % байхгүй
SELECT * FROM users WHERE email LIKE 'bold%';
-- type = range, index ашиглана

3. OR — composite index тасарна

sql

-- МУУ: OR нь index сонголтыг хүндрүүлнэ
SELECT * FROM orders WHERE status = 'paid' OR status = 'pending';

-- САЙН: IN ашиглах
SELECT * FROM orders WHERE status IN ('paid', 'pending');

4. SELECT * — хэрэггүй өгөгдөл татна

sql

-- МУУ: бүх баганыг татна
SELECT * FROM orders WHERE user_id = 42;

-- САЙН: хэрэгтэй баганыг л татна
SELECT id, amount, status FROM orders WHERE user_id = 42;

Slow query log

MySQL-д удаан query-г автоматаар бүртгэх боломжтой:

sql

-- Slow query log идэвхжүүлэх
SET GLOBAL slow_query_log = 'ON';

-- 1 секундоос удаан query-г бүртгэх
SET GLOBAL long_query_time = 1;

-- Log хадгалах байршил
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Тохиргоо харах
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

bash

# Slow query log дүн шинжилгээ хийх
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: цагаар эрэмблэх
# -t 10: хамгийн удаан 10 query

Оновчлолын дараалал

код

1. EXPLAIN ашиглан асуудалтай query олох
2. type = ALL байвал → index нэмэх
3. WHERE дахь функц байвал → хүрээгээр солих
4. SELECT * байвал → зөвхөн хэрэгтэй баганыг
5. JOIN-ны ON баганад index байгаа эсэх шалгах
6. EXPLAIN ANALYZE-р бодит хугацааг хэмжих
7. Slow query log-оос байнга хянах

Дараагийн хичээлд:

Node.js + MySQL холбох — mysql2 package ашиглан JavaScript аппликейшнаас MySQL-тэй ажиллах аргыг үзнэ.

← Өмнөх хичээлFull-text search Дараах хичээл →Node.js + MySQL холбох