MySQL Query Optimization với EXPLAIN — Hiểu và tối ưu truy vấn từ gốc rễ

Bạn đã bao giờ viết một query chạy nhanh trên máy local nhưng lại "đơ" khi lên production với hàng triệu records? Hoặc tự hỏi tại sao một query đơn giản lại mất đến vài giây để thực thi?

EXPLAIN là công cụ "X-ray" giúp bạn nhìn thấu cách MySQL thực sự xử lý query của bạn. Thay vì đoán mò, bạn sẽ biết chính xác:

MySQL có sử dụng index hay không
Bao nhiêu rows cần scan
Thứ tự JOIN như thế nào
Có temporary table hay filesort không

EXPLAIN cơ bản

Cú pháp

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Hoặc với format dễ đọc hơn:

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'john@example.com';
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE email = 'john@example.com'; -- MySQL 8.0+

Kết quả mẫu

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

Kết quả này cho thấy một vấn đề nghiêm trọng: type = ALL nghĩa là MySQL đang scan toàn bộ 1000 rows!

Hiểu từng cột trong EXPLAIN

1. `id` — Thứ tự thực thi

Cùng id: thực thi cùng lúc (JOIN)
id khác nhau: thực thi tuần tự (số lớn hơn chạy trước)

EXPLAIN 
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

+----+-------------+--------+...
| id | select_type | table  |...
+----+-------------+--------+...
|  1 | PRIMARY     | orders |...
|  2 | SUBQUERY    | users  |...
+----+-------------+--------+...

2. `select_type` — Loại truy vấn

Giá trị	Ý nghĩa
SIMPLE	Query đơn giản, không có subquery hay UNION
PRIMARY	Query ngoài cùng
SUBQUERY	Subquery trong SELECT hoặc WHERE
DERIVED	Subquery trong FROM (tạo bảng tạm)
UNION	Phần thứ hai trở đi của UNION
DEPENDENT SUBQUERY	Subquery phụ thuộc vào outer query (⚠️ slow!)

Cảnh báo: DEPENDENT SUBQUERY thường rất chậm vì MySQL phải chạy subquery cho MỖI row của outer query.

3. `type` — Cách MySQL truy cập dữ liệu (QUAN TRỌNG NHẤT!)

Xếp theo thứ tự từ tốt nhất đến tệ nhất:

Type	Mô tả	Performance
`system`	Bảng chỉ có 1 row	🟢 Excellent
`const`	Match tối đa 1 row (PRIMARY KEY hoặc UNIQUE)	🟢 Excellent
`eq_ref`	JOIN với PRIMARY KEY, mỗi row bên trái match đúng 1 row bên phải	🟢 Excellent
`ref`	Tìm bằng index non-unique	🟢 Good
`range`	Tìm trong một khoảng index (BETWEEN, <, >, IN)	🟡 OK
`index`	Full scan trên index (không đọc data rows)	🟠 Poor
`ALL`	Full table scan	🔴 Terrible

Mục tiêu: Query của bạn nên có type là const, eq_ref, ref, hoặc range.

-- BAD: type = ALL (full table scan)
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- GOOD: type = range
EXPLAIN SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

4. `possible_keys` và `key`

possible_keys: Các index MySQL có thể sử dụng
key: Index MySQL thực sự chọn

possible_keys: idx_email,idx_status,idx_created_at
key: idx_email

Nếu possible_keys có nhiều index nhưng MySQL chọn sai, bạn có thể dùng index hint:

SELECT * FROM users USE INDEX (idx_status) WHERE status = 'active';
SELECT * FROM users FORCE INDEX (idx_created_at) WHERE created_at > '2024-01-01';

5. `key_len` — Độ dài index được sử dụng

Số byte của index được sử dụng. Quan trọng với composite index:

-- Index: (country, city, district)
-- key_len = 3 (chỉ dùng country)
SELECT * FROM addresses WHERE country = 'VN';

-- key_len = 255 (dùng country + city)
SELECT * FROM addresses WHERE country = 'VN' AND city = 'Hanoi';

-- key_len = 507 (dùng cả 3 columns)
SELECT * FROM addresses WHERE country = 'VN' AND city = 'Hanoi' AND district = 'CauGiay';

6. `rows` — Số rows cần scan (ước tính)

Con số này là ước tính, không chính xác 100%. Nhưng nó cho thấy quy mô công việc MySQL phải làm.

Rule of thumb:

rows < 100: Tốt
rows < 1000: Chấp nhận được
rows > 10000: Cần xem xét
rows > 100000: Đỏ đèn!

7. `Extra` — Thông tin bổ sung quan trọng

Giá trị	Ý nghĩa
`Using index`	🟢 Covering index — không cần đọc data row
`Using where`	🟡 Filter được áp dụng sau khi đọc data
`Using temporary`	🔴 Tạo bảng tạm (thường do GROUP BY, DISTINCT)
`Using filesort`	🔴 Sort không dùng index (tốn tài nguyên)
`Using index condition`	🟢 Index Condition Pushdown (ICP)

Mục tiêu: Tránh Using temporary và Using filesort khi có thể.

EXPLAIN ANALYZE — Đo thời gian thực tế (MySQL 8.0.18+)

EXPLAIN chỉ cho ước tính. EXPLAIN ANALYZE thực sự chạy query và đo thời gian:

EXPLAIN ANALYZE 
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

Kết quả:

-> Group aggregate: count(o.id)  (cost=1234.56 rows=500) (actual time=12.34..45.67 rows=487 loops=1)
    -> Nested loop left join  (cost=987.65 rows=5000) (actual time=0.12..34.56 rows=4523 loops=1)
        -> Filter: (u.created_at > '2024-01-01')  (actual time=0.05..2.34 rows=487 loops=1)
            -> Table scan on u  (cost=123.45 rows=1000) (actual time=0.03..1.23 rows=1000 loops=1)
        -> Index lookup on o using idx_user_id (user_id=u.id)  (cost=1.23 rows=5) (actual time=0.02..0.05 rows=9.28 loops=487)

Giải thích:

cost: Chi phí ước tính
rows: Số rows ước tính
actual time: Thời gian thực tế (ms) — thời gian đến row đầu tiên..thời gian tổng
loops: Số lần lặp (quan trọng với nested loop)

Các vấn đề phổ biến và cách khắc phục

Vấn đề 1: Full Table Scan

-- ❌ BAD
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- type: ALL, rows: 1000000

Nguyên nhân: Sử dụng function trên column khiến MySQL không dùng được index.

-- ✅ GOOD
EXPLAIN SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- type: range, rows: 45000

Vấn đề 2: Using filesort với ORDER BY

-- ❌ BAD
EXPLAIN SELECT * FROM products WHERE category_id = 5 ORDER BY price;
-- Extra: Using filesort

Giải pháp: Tạo composite index bao gồm cả WHERE và ORDER BY columns:

CREATE INDEX idx_category_price ON products(category_id, price);

-- ✅ GOOD - không còn filesort
EXPLAIN SELECT * FROM products WHERE category_id = 5 ORDER BY price;

Vấn đề 3: Using temporary với GROUP BY

-- ❌ BAD
EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- Extra: Using temporary; Using filesort

Giải pháp:

CREATE INDEX idx_user_id ON orders(user_id);

-- ✅ GOOD - dùng index để group
EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- Extra: Using index

Vấn đề 4: DEPENDENT SUBQUERY

-- ❌ BAD - Subquery chạy cho MỖI row của outer query
EXPLAIN SELECT * FROM products p 
WHERE price > (SELECT AVG(price) FROM products WHERE category_id = p.category_id);
-- select_type: DEPENDENT SUBQUERY

Giải pháp: Chuyển thành JOIN:

-- ✅ GOOD
EXPLAIN 
SELECT p.* FROM products p
JOIN (
    SELECT category_id, AVG(price) as avg_price 
    FROM products 
    GROUP BY category_id
) cat_avg ON p.category_id = cat_avg.category_id
WHERE p.price > cat_avg.avg_price;

Vấn đề 5: JOIN không dùng index

-- ❌ BAD
EXPLAIN 
SELECT * FROM orders o 
JOIN customers c ON o.customer_email = c.email;
-- type: ALL cho cả hai bảng

Giải pháp:

CREATE INDEX idx_customer_email ON orders(customer_email);
CREATE INDEX idx_email ON customers(email);

-- ✅ GOOD - type: ref hoặc eq_ref

Ví dụ thực tế: Tối ưu query phức tạp

Query ban đầu (chậm)

EXPLAIN
SELECT 
    u.name,
    u.email,
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
    AND u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY total_spent DESC
LIMIT 10;

Kết quả EXPLAIN:

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | u     | ALL  | PRIMARY       | NULL | NULL    | NULL | 100000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 500000 | Using where; Using join buffer               |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+

Vấn đề:

Full table scan trên users (type: ALL)
Full table scan trên orders (type: ALL)
Using temporary + filesort

Bước 1: Thêm index cho WHERE conditions

CREATE INDEX idx_users_status_created ON users(status, created_at);

Bước 2: Thêm index cho JOIN

CREATE INDEX idx_orders_user_id ON orders(user_id);

Bước 3: Tạo covering index (tùy chọn)

CREATE INDEX idx_orders_user_total ON orders(user_id, total_amount);

Query sau khi tối ưu

EXPLAIN
SELECT 
    u.name,
    u.email,
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
    AND u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY total_spent DESC
LIMIT 10;

Kết quả:

+----+-------------+-------+-------+---------------------------+------------------------+---------+--------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys             | key                    | key_len | ref    | rows | Extra                                        |
+----+-------------+-------+-------+---------------------------+------------------------+---------+--------+------+----------------------------------------------+
|  1 | SIMPLE      | u     | range | idx_users_status_created  | idx_users_status_created | 8      | NULL   | 5000 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | ref   | idx_orders_user_total     | idx_orders_user_total  | 4       | u.id   | 5    | Using index                                  |
+----+-------------+-------+-------+---------------------------+------------------------+---------+--------+------+----------------------------------------------+

Cải thiện:

users: từ 100,000 rows → 5,000 rows (type: range)
orders: từ 500,000 rows → 5 rows per user (type: ref, Using index)

EXPLAIN trong Laravel

Sử dụng toRawSql() + EXPLAIN

$query = User::where('status', 'active')
    ->where('created_at', '>', '2024-01-01')
    ->with('orders');

// Lấy raw SQL
$sql = $query->toRawSql();
dump($sql);

// Chạy EXPLAIN
$explain = DB::select('EXPLAIN ' . $sql);
dump($explain);

Debug với Laravel Debugbar

// config/debugbar.php
'collectors' => [
    'queries' => true,
],

Debugbar sẽ hiển thị tất cả queries và thời gian thực thi.

Macro để EXPLAIN trực tiếp

// app/Providers/AppServiceProvider.php
use Illuminate\Database\Query\Builder;

Builder::macro('explain', function () {
    $sql = $this->toSql();
    $bindings = $this->getBindings();
    
    return DB::select('EXPLAIN ' . $sql, $bindings);
});

// Sử dụng
User::where('status', 'active')->explain();

Checklist tối ưu query

Khi gặp query chậm, hãy kiểm tra theo thứ tự:

Chạy EXPLAIN và xem type column
- type là ALL? → Cần thêm index
- type là index? → Có thể cải thiện
Kiểm tra rows
- rows > 10000? → Cần filtering tốt hơn
Kiểm tra Extra
- Using filesort? → Xem lại ORDER BY
- Using temporary? → Xem lại GROUP BY
Kiểm tra WHERE clause
- Có dùng function trên column không?
- Có implicit type conversion không?
Kiểm tra JOINs
- Có index trên JOIN columns không?
- JOIN order có hợp lý không?

Kết luận

EXPLAIN là công cụ không thể thiếu cho bất kỳ developer nào làm việc với MySQL. Thay vì đoán mò hoặc thử sai, EXPLAIN cho bạn cái nhìn chính xác về cách MySQL xử lý query.

Key takeaways:

Luôn chạy EXPLAIN trước khi deploy query phức tạp
Mục tiêu: type = const/eq_ref/ref/range, tránh ALL
Tránh Using temporary và Using filesort
Đừng dùng function trên column trong WHERE
Composite index phải đúng thứ tự columns
Dùng EXPLAIN ANALYZE (MySQL 8.0+) để đo thời gian thực

Bài tiếp theo, chúng ta sẽ đi sâu vào MySQL Indexing Strategies — cách thiết kế index hiệu quả cho các use case thực tế.