Database Optimization Deep Dive – Index Strategy, Query Rewrite, Deadlock

Nếu bài trước bạn hiểu Eloquent performance,thì bài này sẽ trả lời câu hỏi lớn hơn:

Tại sao DB vẫn chậm dù code đã tối ưu?

Câu trả lời: Database design & query execution

#1. Problem (Production thật)

API:

GET /api/orders

Query:

SELECT * FROM orders
WHERE user_id = 100
AND status = 'completed'
ORDER BY created_at DESC;

#Symptoms

• Response: 2–5s
• CPU DB: cao
• Slow query log xuất hiện liên tục

#2. Root Cause

90% đến từ:

• Không có index
• Index sai
• Query không tối ưu

#3. Index Strategy (Deep)

#3.1 Single Index

CREATE INDEX idx_user_id ON orders(user_id);

Chỉ giúp 1 phần

#3.2 Composite Index

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);

Match hoàn toàn query

#⚠️ Sai lầm

(status, user_id)

Sai thứ tự → query chậm

#3.3 Covering Index

CREATE INDEX idx_orders_cover
ON orders(user_id, status, created_at, total);

SELECT không cần đọc table

#3.4 Index Selectivity

Column có giá trị unique cao → hiệu quả

❌ status (low selectivity) ✅ user_id (high selectivity)

#4. Query Rewrite (rất mạnh)

#❌ Bad query

SELECT * FROM orders
WHERE DATE(created_at) = '2026-01-01';

Không dùng index

#✅ Good query

WHERE created_at >= '2026-01-01 00:00:00'
AND created_at < '2026-01-02 00:00:00'

Dùng index được

#❌ Using OR

WHERE user_id = 1 OR status = 'pending'

Khó optimize

#✅ Rewrite

UNION ALL

#5. EXPLAIN thực chiến

EXPLAIN SELECT * FROM orders ...

#Field quan trọng

• type
• rows
• key
• Extra

#Red flags

• ALL → full scan
• filesort
• temporary

#6. Transaction Deep Dive

#ACID

• Atomicity
• Consistency
• Isolation
• Durability

#Isolation Level

• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Laravel mặc định MySQL: REPEATABLE READ

#Phantom Read (advanced)

Query cùng điều kiện nhưng data khác

#7. Deadlock (Production)

#Scenario thật

• Update nhiều bảng
• Order khác nhau

#Fix

• Lock order
• Retry
• Reduce transaction scope

#8. Partitioning (Advanced)

#Khi nào cần?

• Table > 10M rows

#Ví dụ

PARTITION BY RANGE (YEAR(created_at))

Query nhanh hơn

#9. Real Case (x10 performance)

#Before

• Query: 3s
• rows scan: 1M

#Fix

• Add composite index
• Rewrite query

#After

• Query: 200ms
• rows scan: 500

#10. Tips & Tricks

• Không SELECT *
• Luôn EXPLAIN query
• Log slow query
• Index theo query, không theo cảm giác

#11. Mindset Senior

Junior:

“Thêm index là xong”

Senior:

“Phải hiểu query plan và data distribution”

#12. Interview Questions

#Kết luận

Database optimization là skill bắt buộc.

ORM không cứu bạn nếu DB sai.

Hiểu DB = level senior thật sự.