Bài 8: Index & Query Plan

Dataset TechMart chỉ có 28 đơn hàng — dù thêm hay bỏ index, mọi truy vấn đều nhanh tới mức không thể đo được sự khác biệt. Đây chính là lý do bài này cần 1 dataset lớn hơn hẳn: bài học tự sinh 100.000 dòng dữ liệu giả ngay trong phiên làm việc để bạn tự mắt thấy và tự đo chênh lệch tốc độ thật giữa quét toàn bảng và tra cứu qua index — không phải lý thuyết suông.

1. B-Tree Index Hoạt Động Thế Nào?

Không có index, tìm 1 hàng trong bảng 100.000 dòng đồng nghĩa engine phải đọc lần lượt từng dòng để kiểm tra điều kiện — giống lật từng trang sách để tìm 1 từ. Index là 1 cấu trúc dữ liệu B-Tree riêng, lưu sẵn giá trị cột đã sắp xếp kèm con trỏ trỏ ngược về đúng hàng gốc — giống mục lục cuối sách: tra thẳng tới đúng trang, không cần đọc hết. Chi phí: index chiếm thêm dung lượng đĩa, và mỗi lần ghi (INSERT/UPDATE/DELETE) engine phải cập nhật thêm cả cấu trúc B-Tree đó, không chỉ bảng gốc.

Để có dữ liệu đủ lớn minh hoạ, sân chơi bên dưới tự tạo bảng big_orders với 100.000 dòng bằng 1 kỹ thuật SQL thuần, không cần vòng lặp JavaScript chậm chạp — CTE đệ quy sinh dãy số liên tiếp rồi INSERT ... SELECT trực tiếp từ dãy đó:

CREATE TABLE big_orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT,
  total_amount REAL
);

WITH RECURSIVE seq(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM seq WHERE x < 100000
)
INSERT INTO big_orders (order_id, customer_id, order_date, status, total_amount)
SELECT
  x,
  1 + (abs(random()) % 5000),                                    -- customer_id ngẫu nhiên 1-5000
  date('2024-01-01', '+' || (abs(random()) % 700) || ' days'),   -- ngày ngẫu nhiên trong ~2 năm
  CASE abs(random()) % 5
    WHEN 0 THEN 'delivered' WHEN 1 THEN 'shipped' WHEN 2 THEN 'cancelled'
    WHEN 3 THEN 'refunded' ELSE 'pending' END,
  ROUND(10 + (abs(random()) % 40000) / 100.0, 2)
FROM seq;

random() của SQLite trả về số nguyên có dấu — abs() đảm bảo luôn dương trước khi lấy phần dư (%). Đây là bảng bonus riêng cho bài này (giống category_tree ở Bài 5, routes ở Bài 6) — không phải phần cố định của dataset TechMart.

2. EXPLAIN QUERY PLAN: Nhìn Thấy Engine Đang Nghĩ Gì

Đặt EXPLAIN QUERY PLAN trước bất kỳ câu SELECT nào để xem engine dự định thực thi như thế nào, không chạy thật câu lệnh:

EXPLAIN QUERY PLAN
SELECT * FROM big_orders WHERE customer_id = 2500;

Trên bảng big_orders vừa tạo (chưa có index nào ngoài khoá chính), kết quả là:

SCAN big_orders

SCAN nghĩa là quét toàn bảng — đọc tuần tự cả 100.000 dòng, kiểm tra từng dòng có khớp customer_id = 2500 hay không. Đây chính xác là "lật từng trang sách" đã nói ở mục 1.

3. Full Scan vs Index Seek: Đo Bằng Số Thật

Tạo index trên cột đang lọc, rồi chạy lại đúng EXPLAIN QUERY PLAN ở trên:

CREATE INDEX idx_customer ON big_orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM big_orders WHERE customer_id = 2500;

SEARCH big_orders USING INDEX idx_customer (customer_id=?)

SEARCH ... USING INDEX nghĩa là engine tra thẳng B-Tree của idx_customer để nhảy ngay tới đúng vị trí, không quét toàn bảng nữa. Chạy thử trong sân chơi bên dưới và tự đo: trên máy phát triển bài này, quét toàn bảng mất ~4-6ms, tra qua index chỉ còn ~0.03-0.3ms — nhanh hơn 10-100 lần. Với bảng 100 triệu dòng thay vì 100.000, chênh lệch này còn kịch tính hơn nữa: sự khác biệt giữa "tức thì" và "treo hàng giây, thậm chí hàng phút".

4. Composite Index: Nhiều Cột Trong 1 Index, Thứ Tự Quan Trọng

Index cũng gộp được nhiều cột — nhưng thứ tự cột lúc khai báo quyết định index có giúp được truy vấn nào, theo quy tắc gọi là left-prefix (tiền tố bên trái): index (A, B) giúp được truy vấn lọc theo A, hoặc A và B cùng lúc — nhưng không giúp được truy vấn chỉ lọc theo B đơn lẻ (giống mục lục sách sắp theo "chương rồi tới trang" — muốn tìm 1 trang cụ thể mà không biết chương nào thì mục lục đó vô dụng).

CREATE INDEX idx_status_date ON big_orders(status, order_date);

Kết quả đo thật khớp chính xác quy tắc left-prefix: lọc theo order_date đơn lẻ vẫn quay về SCAN big_orders dù index idx_status_date đã tồn tại — vì order_date không phải cột đầu. Muốn cả 2 hướng lọc đều nhanh, cần 2 index riêng (hoặc đảo thứ tự tuỳ query nào chạy thường xuyên hơn).

5. Covering Index: Không Cần Chạm Vào Bảng Gốc

Khi index chứa đủ mọi cột câu lệnh cần (cả cột lọc lẫn cột hiển thị), engine không cần quay lại đọc bảng gốc nữa — chỉ đọc index là đủ trả lời toàn bộ câu hỏi. Đây gọi là covering index (index bao phủ):

-- Với idx_status_date(status, order_date) đã có ở mục 4:
EXPLAIN QUERY PLAN
SELECT customer_id, order_date FROM big_orders WHERE status = 'delivered';
-- → SEARCH ... USING INDEX idx_status_date (status=?)
--   VẪN PHẢI đọc bảng gốc để lấy customer_id (không có trong index này)

CREATE INDEX idx_covering ON big_orders(status, customer_id, order_date);

EXPLAIN QUERY PLAN
SELECT customer_id, order_date FROM big_orders WHERE status = 'delivered';
-- → SEARCH ... USING COVERING INDEX idx_covering (status=?)
--   Không cần đọc bảng gốc nữa — mọi cột cần đều có sẵn trong index

Chú ý từ khoá COVERING xuất hiện thêm trong dòng kế hoạch thứ 2 — đây là tín hiệu trực tiếp từ SQLite xác nhận truy vấn không cần động tới bảng gốc, chỉ cần quét index. Đánh đổi: index càng nhiều cột càng chiếm thêm dung lượng và làm chậm thao tác ghi — không nên biến mọi index thành covering index cho mọi truy vấn có thể có, chỉ áp dụng cho các truy vấn thật sự chạy thường xuyên và quan trọng về tốc độ.

Bảng Tổng Kết: Khi Nào Nên/Không Nên Tạo Index

Sân chơi tương tác: So Sánh Full Scan vs Index Seek Trên 100.000 Dòng

Bấm "Tạo bảng 100k dòng" trước (mất khoảng vài trăm mili-giây), rồi thử các câu lệnh EXPLAIN QUERY PLAN và đo thời gian thực thi trước/sau khi tạo index.