Tối Ưu Database & Tốc Độ Web: Hướng Dẫn Chi Tiết Có Code

Database chậm là nguyên nhân #1 khiến web doanh nghiệp ì ạch sau khi data vượt vài triệu rows. Bài này không nói lý thuyết suông — em sẽ đi qua đúng 7 kỹ thuật tối ưu database mà team Alodev áp dụng cho khách hàng có production traffic, kèm code SQL/Node.js chạy được ngay trên PostgreSQL hoặc MySQL.

1. Đo trước khi tối ưu — EXPLAIN ANALYZE

Đừng đoán. Mọi query chậm đều phải được chạy qua EXPLAIN ANALYZE để xem engine thực sự làm gì. Đây là công cụ đầu tiên trong quy trình tối ưu database.

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
ORDER BY orders DESC
LIMIT 50;

Kết quả cần đọc 3 chỉ số: Seq Scan trên bảng lớn = thiếu index, actual time > 100ms = nóng, rows removed by filter cao = index không trúng. Trên MySQL, dùng EXPLAIN FORMAT=TREE hoặc EXPLAIN ANALYZE (8.0.18+).

2. Bật slow query log để bắt thủ phạm thực sự

Lý thuyết EXPLAIN chỉ dùng được khi anh đã biết query nào chậm. Slow log tự động lọc giúp:

-- PostgreSQL: postgresql.conf
log_min_duration_statement = 200    -- log mọi query > 200ms
log_statement_stats = off
log_line_prefix = '%t [%p] %u@%d '

-- MySQL: my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.2
log_queries_not_using_indexes = 1

Sau 24h chạy, parse log với pt-query-digest (MySQL) hoặc pgBadger (PostgreSQL) để ra top 10 query nóng nhất. 80% pain đến từ 5-10 query lặp lại — fix tới đó là đủ giảm 70% load.

3. Index đúng cột — composite theo thứ tự WHERE → ORDER BY

Đây là kỹ thuật tối ưu database mang lại ROI cao nhất. Quy tắc 3 bước:

1. Cột trong WHERE equals (=) đứng trước
2. Cột trong WHERE range (>, <) đứng giữa
3. Cột trong ORDER BY đứng cuối

-- Query thật từ một e-commerce có 12M orders
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 12345
  AND status = 'paid'
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- Index sai (chậm vì user_id và status đều equals nhưng đảo thứ tự)
CREATE INDEX idx_orders_bad ON orders(created_at, user_id, status);
-- → 480ms, Seq Scan + Sort

-- Index đúng (composite theo cardinality giảm dần + ORDER BY ở cuối)
CREATE INDEX idx_orders_good ON orders(user_id, status, created_at DESC);
-- → 4ms, Index Scan, no Sort needed (DESC khớp ORDER BY)

Lưu ý covering index: nếu query chỉ cần vài cột, thêm chúng vào INCLUDE để engine tránh hit table:

-- PostgreSQL covering index
CREATE INDEX idx_orders_covering
  ON orders(user_id, status, created_at DESC)
  INCLUDE (total);
-- Index-Only Scan, không cần đọc bảng → giảm I/O đáng kể

4. Diệt N+1 query — kẻ giết hiệu năng âm thầm

N+1 là pattern đứng đầu danh sách lý do app Node.js/PHP chậm bất thường. Thay vì 1 query, ORM bắn N+1 query liên tiếp:

// ❌ Sai — N+1 query (1 query cho users + N query cho orders)
const users = await db.query('SELECT * FROM users LIMIT 50')
for (const u of users) {
  u.orders = await db.query('SELECT * FROM orders WHERE user_id = ?', [u.id])
}
// → 51 round trip, ~500ms

// ✅ Đúng — JOIN 1 query, group ở app
const rows = await db.query(`
  SELECT u.id AS user_id, u.email, o.id AS order_id, o.total, o.status
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.id IN (SELECT id FROM users LIMIT 50)
`)
const grouped = new Map()
for (const r of rows) {
  if (!grouped.has(r.user_id)) grouped.set(r.user_id, { id: r.user_id, email: r.email, orders: [] })
  if (r.order_id) grouped.get(r.user_id).orders.push({ id: r.order_id, total: r.total, status: r.status })
}
// → 1 round trip, ~25ms

Hoặc dùng dataloader pattern (batch + cache trong 1 request) — chuẩn trong GraphQL.

5. Connection pool và prepared statement

Mỗi new Connection() mất 5-30ms handshake. Dưới load cao, đó là nguồn timeout chính. Pool tái sử dụng connection:

// Node.js — pg pool, đặt ở module-level (không tạo trong handler)
import { Pool } from 'pg'

export const pool = new Pool({
  host: process.env.DB_HOST,
  database: 'app',
  max: 20,                  // tổng connection
  min: 4,                   // giữ ấm
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2_000,
  statement_timeout: 5_000, // kill query chạy > 5s
})

// Prepared statement — query plan được cache, parse 1 lần
const result = await pool.query({
  name: 'fetch-user-orders',  // tên = key cache
  text: 'SELECT * FROM orders WHERE user_id = $1 AND status = $2',
  values: [userId, 'paid'],
})

Đặt max = (CPU cores của DB) × 2-4. Quá cao → context switch, RAM cạn. Dùng PgBouncer/ProxySQL trước Postgres/MySQL khi có >100 client.

6. Redis cache cho hot read

Khi query đã tối ưu hết mà vẫn chậm vì bị gọi 1000 lần/giây cho cùng input, đưa Redis vào trước database:

import { createClient } from 'redis'
const redis = createClient({ url: process.env.REDIS_URL })
await redis.connect()

async function getUserById(id) {
  const key = `user:${id}`
  const cached = await redis.get(key)
  if (cached) return JSON.parse(cached)

  const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [id])
  if (rows[0]) {
    await redis.set(key, JSON.stringify(rows[0]), { EX: 300 }) // TTL 5 phút
  }
  return rows[0] ?? null
}

// Invalidate khi update
async function updateUserEmail(id, email) {
  await pool.query('UPDATE users SET email = $1 WHERE id = $2', [email, id])
  await redis.del(`user:${id}`)
}

Pattern: read-through + write-invalidation. Cẩn thận thundering herd — khi 1 key hết hạn, 1000 request cùng query DB. Dùng SET NX + jitter TTL để tránh.

7. Partitioning cho bảng cực lớn

Khi 1 bảng > 50M rows và phần lớn query chỉ chạm range gần đây (logs, events, orders theo tháng), partition theo thời gian giúp engine bỏ qua hầu hết partition:

-- PostgreSQL declarative partitioning
CREATE TABLE events (
  id BIGSERIAL,
  user_id BIGINT NOT NULL,
  event_type TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_05 PARTITION OF events
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- Query chỉ scan đúng partition của tháng 5
EXPLAIN ANALYZE
SELECT user_id, COUNT(*) FROM events
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01'
GROUP BY user_id;

Tự động tạo partition mới hàng tháng bằng cron + script. Dropping partition cũ = DROP TABLE events_2025_01 (tức thì, không cần vacuum).

Checklist tối ưu database cho mọi production app

• ✅ Đã bật log_min_duration_statement / slow_query_log
• ✅ Mọi cột trong WHERE/JOIN có index đúng thứ tự
• ✅ Connection pool có statement_timeout tránh query treo
• ✅ Không còn N+1 — kiểm bằng query log trong staging
• ✅ Hot read được cache (Redis hoặc in-memory)
• ✅ Bảng > 50M rows đã partition
• ✅ Backup script chạy hằng đêm — xem bài Backup Database
• ✅ Đọc thêm Index trong SQL và Denormalization để hiểu sâu trade-off

Kết luận

Tối ưu database không phải là dùng "secret command" nào cả — đó là quy trình kỷ luật: đo, sửa từng query nóng, thêm cache khi cần, partition khi bảng quá lớn. Hầu hết hệ thống có thể chịu 100x traffic hiện tại nếu áp dụng đầy đủ 7 bước trên trước khi nghĩ đến scale dọc/ngang. Nếu anh muốn audit cụ thể database của doanh nghiệp mình, đọc tiếp Database Trong Ứng Dụng Web để hiểu kiến trúc, sau đó áp checklist này trong 1 buổi.

Bài viết liên quan

• Index Trong SQL Là Gì? Tăng Hiệu Suất Database — Index trong SQL giúp tăng tốc truy vấn nhưng có chi phí ghi và dung lượng. Tìm hiểu B-tree, composite index, EXPLAIN và lỗi thường gặp.
• Backup Database Hiệu Quả Và Không Mất Dữ Liệu — Backup database hiệu quả cần chiến lược full/incremental, kiểm thử restore, RPO/RTO rõ ràng, mã hóa, giám sát và quy trình rollback.
• SQL Là Gì? Cách Viết Query Chuẩn Dev — SQL là gì và cách viết query chuẩn cho dev: SELECT, JOIN, WHERE, GROUP BY, index, subquery, CTE và những pitfall thường gặp khi mới học.
• Denormalization: Khi Nào Nên Dùng? — Denormalization giúp tăng tốc đọc bằng cách lưu dữ liệu lặp, nhưng đổi lại là rủi ro sai lệch dữ liệu. Tìm hiểu khi nào nên dùng.