[Backend Masterclass] Tối ưu hóa Database: Từ những câu query ngây ngô đến nghệ thuật đánh Index

Chào anh em, trải qua các bài viết trước, chúng ta đã tối ưu code ở tầng App (Queue, Race Condition, Đa luồng) và tầng OS (Garbage Collection). Nhưng sự thật phũ phàng là: 90% các nút thắt cổ chai (bottleneck) của một hệ thống Backend nằm ở Database. Giả sử anh em đang vận hành một hệ thống e-commerce bán lẻ mỹ phẩm với hàng triệu lượt truy cập mỗi ngày. Server App CPU mới nhích lên 20%, nhưng CPU của con PostgreSQL (hay MySQL) đã gào thét ở mức 100%, query chọc vào DB mất tận 5-10 giây không ra kết quả. Lúc đó, tăng thêm RAM cho server cũng vô dụng.

Hôm nay, chúng ta sẽ lột mặt nạ những câu query "sát thủ" giấu mặt và bước vào con đường nghệ thuật của một kĩ sư dữ liệu: Đánh Index.

1. Những câu query "ngây ngô" làm sập DB

Nhiều anh em xài ORM (như Eloquent của Laravel, hay GORM của Golang) sướng quá sinh ra lười, phó mặc mọi thứ cho Framework generate SQL. Và đây là những "tội ác" kinh điển:

Tội ác 1: Lạm dụng SELECT *

• Junior code: SELECT * FROM products WHERE id = 123
• Sự thật: Bảng products của bạn có 50 cột, bao gồm cả những cột chứa text mô tả sản phẩm siêu dài. App chỉ cần lấy tên và giá để hiển thị, nhưng bạn lôi tuột cả 50 cột về. Hành động này ngốn cực nhiều I/O Disk, băng thông mạng (Network Bandwidth) và RAM của cả DB lẫn Server App.
• Cách sửa: Cần gì lấy nấy: SELECT name, price FROM products WHERE id = 123.

Tội ác 2: Căn bệnh nan y N+1 Query

Kịch bản: Hiển thị danh sách 50 đơn hàng, kèm theo tên của User mua đơn hàng đó.

// Code trông rất gọn gàng
$orders = Order::limit(50)->get();
foreach ($orders as $order) {
    echo $order->user->name; // Lấy tên user
}

Bên dưới mâm:

1 câu query lấy 50 orders: SELECT * FROM orders LIMIT 50; Và... 50 câu query khác để lấy user: SELECT * FROM users WHERE id = ?; (chạy 50 lần). Tổng cộng bạn bắn 51 câu query vào DB chỉ để hiển thị 1 cái danh sách! Nếu có 1000 người vào trang đó cùng lúc, DB sẽ hứng 51,000 queries/giây và... sập.

• Cách sửa: Dùng Eager Loading (Gộp query bằng IN): Order::with('user')->limit(50)->get(); -> DB chỉ tốn đúng 2 câu query!

Tội ác 3: Dùng Wildcard LIKE '%keyword%' ở đầu

Code search: SELECT * FROM products WHERE name LIKE '%kem chống nắng%'; Dấu % nằm ở đầu câu có nghĩa là: "DB ơi, tìm cho tao cái tên nào chứa chữ này ở BẤT CỨ ĐÂU". Lúc này, DB phải lật từng trang, đọc từng dòng của hàng triệu sản phẩm để kiểm tra. Đây gọi là thảm họa Full Table Scan.

2. Giải ngố Index: Vị cứu tinh của tốc độ

Nếu coi Database của bạn là một cuốn từ điển tiếng Anh dày 5000 trang.

• Không có Index: Tìm từ "Viblo", bạn lật từ trang 1 đến trang 5000 để tìm. (Full Table Scan - O(N)).
• Có Index: Bạn lật ra phần Mục lục chữ cái ở cuối sách, tìm ngay đến vần "V", tra được từ "Viblo" nằm ở trang 4200, và lật thẳng đến đó. Thời gian chớp nhoáng!

Bên dưới vỏ bọc, Index thường được xây dựng bằng cấu trúc cây B-Tree (Balanced Tree). Nó giúp giảm độ phức tạp tìm kiếm từ O(N) xuống còn O(log N). Với một bảng 10 triệu bản ghi, thay vì quét 10 triệu lần, DB có cấu trúc B-Tree chỉ mất khoảng... 23 lần so sánh là tìm ra dữ liệu. Một sự tối ưu kinh hoàng!

3. Nghệ thuật đánh Index (Không phải cứ đánh bừa là ngon)

Nhiều anh em thấy Index thần thánh quá, thế là vào DB, cột nào cũng tick vào ô "Add Index". Vài tháng sau DB phình to gấp 3 lần bình thường, tốc độ Insert/Update chậm như rùa bò.

Nguyên lý phải nhớ: Index là một "Bảng mục lục" vật lý.

• Mỗi lần bạn INSERT, UPDATE hoặc DELETE một dòng dữ liệu, DB không chỉ ghi vào bảng chính, mà nó còn phải xây lại cái cây B-Tree của Index. Đánh càng nhiều Index, tốc độ Ghi (Write) càng chậm, và càng tốn dung lượng ổ cứng.

Bí kíp 1: Index Gộp (Composite Index) và Nguyên tắc "Left-Most Prefix"

Giả sử bạn cần query tìm các sản phẩm thuộc danh mục "Sữa rửa mặt" và đang ở trạng thái "Còn hàng":

SELECT * FROM products WHERE category_id = 5 AND status = 'active';

Bạn đánh 1 Index gộp lên cả 2 cột: CREATE INDEX idx_cat_status ON products(category_id, status);

Nguyên tắc "Trái sang phải" (Left-Most Prefix):

Index gộp giống như danh bạ điện thoại sắp xếp theo (Họ, Tên).

• Nếu bạn tìm: WHERE category_id = 5 AND status = 'active' -> Index hoạt động.
• Nếu bạn tìm: WHERE category_id = 5 -> Index vẫn hoạt động (Tìm những người Họ Nguyễn).
• Nếu bạn tìm: WHERE status = 'active' -> Index VÔ TÁC DỤNG (Đi tìm những người Tên Minh mà không biết Họ gì, thì danh bạ vứt đi).

Kinh nghiệm: Khi tạo Composite Index, luôn đặt các cột được sử dụng nhiều nhất, hoặc có tính chọn lọc cao (Cardinality cao - nhiều giá trị khác biệt) lên đầu tiên!

Bí kíp 2: EXPLAIN ANALYZE - Chiếc gương chiếu yêu

Đừng bao giờ đoán mò xem câu query của mình có ăn Index hay không. Hãy đặt chữ EXPLAIN ANALYZE (trong PostgreSQL/MySQL) trước câu lệnh SELECT.

DB sẽ trả về một bản báo cáo chi tiết: Nó dùng index nào (Index Scan), hay đang phải quét toàn bộ bảng (Seq Scan / Full Table Scan), và mất chính xác bao nhiêu mili-giây ở từng khâu. Đó là vũ khí tối thượng của mọi Backend Engineer.

4. Tổng kết & Câu hỏi mở

Tối ưu Database không phải là nghệ thuật gì quá cao siêu, nó là sự kỉ luật trong việc hiểu rõ dữ liệu của mình và hiểu cách Database vận hành bên dưới (Under the hood). Đánh đúng Index, hệ thống của bạn có thể nhanh lên gấp hàng nghìn lần mà không cần tốn thêm một đồng tiền server nào.

Nhưng khoan đã...

Như mình đã nói ở "Tội ác 3", toán tử LIKE '%keyword%' làm vô hiệu hóa hoàn toàn B-Tree index Vậy làm sao các trang thương mại điện tử lớn có thể cho phép người dùng gõ sai chính tả, gõ một từ khóa bất kỳ ở giữa câu (VD: tìm "chống nắng" ra "Kem chống nắng siêu cấp") mà kết quả vẫn trả về chưa tới 100ms?

SQL truyền thống lúc này đã bất lực. Chúng ta cần một "quái vật" chuyên trị tìm kiếm văn bản. Anh em có đoán được nó là gì không?

Gợi ý cho bài sau: Elasticsearch - Giải mã sức mạnh của Inverted Index và Full-text Search.

Anh em có từng "cháy máy" vì câu query nào chưa? Hãy chia sẻ xuống phần comment nhé! Nhớ Upvote để giữ lửa cho chuỗi bài viết của chúng ta!