Bài 9: Query Optimizer Sâu 🐳

Bài 8 đã cho thấy EXPLAIN QUERY PLAN phân biệt được SCAN (quét toàn bảng) và SEARCH ... USING INDEX (tra qua index) — nhưng chưa trả lời câu hỏi sâu hơn: vì sao engine chọn phương án này chứ không phải phương án khác? Bài này mở "hộp đen" đó ra: ANALYZE cho optimizer dữ liệu thống kê thật (thay vì đoán mặc định), cách optimizer quyết định thứ tự truy cập bảng trong JOIN, 2 loại index nâng cao (partial/expression), và cuối cùng là đọc thẳng bytecode VDBE — thứ mà mọi câu SQL thực sự biên dịch thành trước khi chạy. Mọi con số trong bài đều đo được thật trong sân chơi bên dưới, không phải lý thuyết suông.

1. ANALYZE & sqlite_stat1: Cho Optimizer "Nhìn Thấy" Dữ Liệu Thật

Không có thống kê thật, optimizer phải đoán mặc định: khi thấy 1 index tồn tại trên cột đang lọc bằng =, nó thường tin rằng tra index luôn rẻ hơn quét toàn bảng — bất kể giá trị đang lọc phổ biến tới đâu. Đây là tình huống kinh doanh thật: bảng big_orders (100.000 đơn, kế thừa từ Bài 8) có thêm cột channel (kênh đặt hàng) — 95% qua web, chỉ 5% qua app di động:

-- Trước khi có index nào trên channel:
EXPLAIN QUERY PLAN
SELECT total_amount FROM big_orders WHERE channel = 'web';
-- → SCAN big_orders (cost 216) — không có lựa chọn nào khác

CREATE INDEX idx_channel ON big_orders(channel);

-- Có index rồi, NHƯNG CHƯA chạy ANALYZE:
EXPLAIN QUERY PLAN
SELECT total_amount FROM big_orders WHERE channel = 'web';
-- → SEARCH big_orders USING INDEX idx_channel (channel=?) (cost 62)
--   Chú ý: dùng index NGAY CẢ CHO 'web' — giá trị chiếm 95% bảng!

Đây chính là hệ quả của việc đoán mặc định: SQLite chưa biết channel phân bố lệch cỡ nào, nên tin tưởng mù quáng rằng "có index nghĩa là rẻ" — kể cả khi tra 'web' trả về tới 95.000/100.000 dòng, biến việc "tra index" thành gần như đọc lại toàn bộ bảng theo kiểu tốn kém hơn (nhảy qua lại giữa index và bảng gốc cho từng dòng) thay vì quét tuần tự 1 lượt.

ANALYZE;

EXPLAIN QUERY PLAN
SELECT total_amount FROM big_orders WHERE channel = 'web';
-- → SEARCH big_orders USING INDEX idx_channel (channel=?) (cost 180)
--   Chi phí ước lượng TĂNG GẦN GẤP 3 (62 → 180) sau ANALYZE — engine giờ biết
--   index này không hề chọn lọc, nhưng với bảng/cấu trúc dữ liệu này, mức chi
--   phí 180 vẫn được đánh giá rẻ hơn quét toàn bảng nên QUYẾT ĐỊNH cuối không đổi.

Điểm mấu chốt: ANALYZE không phải lúc nào cũng đảo ngược quyết định — nó sửa lại con số ước lượng chi phí cho sát thực tế hơn. Với dữ liệu lệch nhiều hơn nữa, hoặc bảng có cấu trúc khác, mức tăng chi phí này hoàn toàn có thể đủ lớn để engine đổi hẳn sang SCAN — quan trọng là luôn đo thật trên dữ liệu của bạn, không suy diễn lý thuyết suông.

Bảng thống kê sqlite_stat1 mà ANALYZE vừa cập nhật đọc được trực tiếp bằng SELECT thường:

SELECT * FROM sqlite_stat1;
-- tbl          | idx          | stat
-- big_orders   | idx_channel  | "100000 50000"

Cột stat là chuỗi: số dòng trong bảng, theo sau là số dòng trung bình khớp mỗi giá trị khác nhau của index đó — ở đây 100000 / 2 giá trị khác nhau ('web', 'mobile_app') = 50000.

2. Thứ Tự JOIN & Selectivity: Engine Chọn Bảng Nào Trước?

Với JOIN nhiều bảng, engine phải quyết định: bảng nào nên đọc/lọc trước (bảng dẫn dắt), bảng nào chỉ nên tra cứu theo từng dòng (bảng dò). Quyết định này dựa vào chi phí ước lượng — hoàn toàn không liên quan tới thứ tự bạn viết trong FROM/JOIN. Tái sử dụng JOIN đã học ở Bài 3, giờ trên quy mô lớn: tìm đơn hàng của khách Singapore, JOIN big_orders (100.000 dòng) với big_customers (5.000 dòng, quốc gia lệch: 70% Việt Nam, 10% Singapore, 10% Mỹ, 10% Hàn Quốc):

-- Chưa có index nào phục vụ JOIN này (chỉ mới có idx_channel từ mục 1):
EXPLAIN QUERY PLAN
SELECT bo.order_id, bo.total_amount, bc.country
FROM big_orders bo
JOIN big_customers bc ON bo.customer_id = bc.customer_id
WHERE bc.country = 'Singapore';
-- → SCAN bo                                          (cost 182)
--   BLOOM FILTER ON bc (customer_id=?)                (cost 0)
--   SEARCH bc USING INTEGER PRIMARY KEY (rowid=?)     (cost 39)

Đây là lựa chọn bắt buộc: big_orders.customer_id chưa có index nào, nên engine không thể "nhảy" tới đúng khách hàng — phải quét toàn bộ 100.000 đơn. Dòng BLOOM FILTER ON bc là 1 tối ưu phụ engine tự thêm vào vì đã có thống kê thật (từ ANALYZE chạy ở mục 1): trước khi tra rowid thật trên bc cho từng đơn, nó dựng sẵn 1 bloom filter (cấu trúc xác suất, kiểm tra rất nhanh "giá trị này CHẮC CHẮN KHÔNG tồn tại" hay "có thể tồn tại") để loại bớt các customer_id chắc chắn không khớp country = 'Singapore', giảm số lần phải tra rowid thật. Giờ tạo đúng 2 index: 1 trên khoá JOIN, 1 trên cột đang lọc:

CREATE INDEX idx_bo_customer ON big_orders(customer_id);
CREATE INDEX idx_bc_country ON big_customers(country);

EXPLAIN QUERY PLAN
SELECT bo.order_id, bo.total_amount, bc.country
FROM big_orders bo
JOIN big_customers bc ON bo.customer_id = bc.customer_id
WHERE bc.country = 'Singapore';
-- → SEARCH bc USING COVERING INDEX idx_bc_country (country=?)  (cost 52)
--   SEARCH bo USING INDEX idx_bo_customer (customer_id=?)       (cost 60)

Kế hoạch đảo ngược hoàn toàn: thay vì quét 100.000 dòng bo trước, engine giờ lọc bc theo country trước (chỉ còn ~500 dòng Singapore trong 5.000), rồi mới dò ngược sang bo qua idx_bo_customer cho từng khách hàng đó. Đây là selectivity đúng nghĩa: lọc theo điều kiện chọn lọc nhất trước để thu hẹp tập dữ liệu càng sớm càng tốt, rồi mới xử lý phần còn lại. Chú ý: chỉ riêng việc tạo đúng index đã đủ khiến engine đảo thứ tự — chưa cần chạy lại ANALYZE:

ANALYZE;

EXPLAIN QUERY PLAN
SELECT bo.order_id, bo.total_amount, bc.country
FROM big_orders bo
JOIN big_customers bc ON bo.customer_id = bc.customer_id
WHERE bc.country = 'Singapore';
-- → SEARCH bc USING COVERING INDEX idx_bc_country (country=?)  (cost 115, trước đó 52)
--   SEARCH bo USING INDEX idx_bo_customer (customer_id=?)       (cost 68,  trước đó 60)
-- Kế hoạch KHÔNG đổi — nhưng con số chi phí đã được tinh chỉnh theo
-- sqlite_stat1 thật ("5000 1250" cho idx_bc_country, "100000 20" cho
-- idx_bo_customer) thay vì đoán mặc định.

Tổng kết mục 1 + 2: sự tồn tại của index đúng chỗ mới là yếu tố mở ra lựa chọn kế hoạch tốt hơn; ANALYZE chỉ tinh chỉnh độ chính xác của ước lượng chi phí cho các lựa chọn đã có sẵn — đôi khi việc tinh chỉnh đó đủ lớn để đổi hẳn quyết định (mục 1), đôi khi chỉ làm con số sát thực tế hơn mà không đổi quyết định cuối (mục 2). Việc này cũng liên hệ trực tiếp tới GROUP BY/HAVING ở Bài 4 và CTE đệ quy ở Bài 5, 6: bất kỳ truy vấn nào JOIN hoặc lọc trước khi tổng hợp/duyệt cây đều hưởng lợi từ đúng nguyên lý selectivity này.

3. Partial Index & Expression Index: Index Chỉ Đúng Phần Cần

Index thông thường phủ toàn bộ giá trị của 1 cột. Nhưng nhiều truy vấn thực tế chỉ quan tâm tới 1 tập con nhỏ — đánh index cho toàn bộ cột vừa lãng phí dung lượng vừa làm chậm mọi lần ghi vào các dòng không liên quan. Partial index (index có điều kiện WHERE) giải quyết đúng việc này. Tình huống thật: đội vận hành (ops) tra cứu hàng chục lần/ngày các đơn đang pending gần đây để xử lý gấp — 4/5 trạng thái còn lại gần như không bao giờ được lọc kiểu này:

-- Trước khi có partial index:
EXPLAIN QUERY PLAN
SELECT * FROM big_orders WHERE status = 'pending' AND order_date > '2024-06-01';
-- → SCAN big_orders (cost 182)

CREATE INDEX idx_pending ON big_orders(order_date) WHERE status = 'pending';

EXPLAIN QUERY PLAN
SELECT * FROM big_orders WHERE status = 'pending' AND order_date > '2024-06-01';
-- → SEARCH big_orders USING INDEX idx_pending (order_date>?) (cost 160)

idx_pending chỉ chứa các dòng có status = 'pending' (~20% bảng, khoảng 20.000/100.000 dòng) — nhỏ hơn hẳn 1 index đầy đủ trên order_date, và mọi INSERT/UPDATE lên các đơn không phải pending hoàn toàn không phải cập nhật thêm cấu trúc này. Vài tình huống thật khác dùng đúng kỹ thuật này:

• Nhắc nợ: CREATE INDEX ... ON invoices(due_date) WHERE paid = 0 — chỉ cần tra nhanh hoá đơn chưa thanh toán, bỏ qua hàng triệu hoá đơn đã xong.
• Nhắc xác minh email: CREATE INDEX ... ON users(created_at) WHERE email_verified = 0 — job gửi email nhắc chỉ quan tâm tài khoản chưa xác minh, thường chỉ vài % tổng số user.
• Dashboard giám sát lỗi: CREATE INDEX ... ON logs(created_at) WHERE level = 'error' — log info/debug chiếm 99% dòng nhưng dashboard chỉ bao giờ lọc theo error.

Expression index giải quyết vấn đề khác: index trên kết quả 1 biểu thức, không phải giá trị thô của cột. Tình huống thật: đội hỗ trợ khách hàng tra cứu bằng email khách gõ vào, không phân biệt hoa/thường (dữ liệu email trong big_customers vốn lộn xộn hoa/thường do import từ nhiều nguồn):

-- Trước khi có expression index:
EXPLAIN QUERY PLAN
SELECT * FROM big_customers WHERE LOWER(email) = '[email protected]';
-- → SCAN big_customers (cost 138)

CREATE INDEX idx_email_lower ON big_customers(LOWER(email));

EXPLAIN QUERY PLAN
SELECT * FROM big_customers WHERE LOWER(email) = '[email protected]';
-- → SEARCH big_customers USING INDEX idx_email_lower (<expr>=?) (cost 59)

Chú ý placeholder <expr> thay vì tên cột trong dòng kế hoạch — đây là cách SQLite báo hiệu index này được xây trên 1 biểu thức (LOWER(email)), không phải giá trị thô của email. Index thường trên email sẽ vô dụng ở đây vì '[email protected]' và '[email protected]' là 2 chuỗi khác nhau về mặt byte — chỉ LOWER() mới quy chúng về cùng 1 giá trị so sánh được. Vài tình huống thật khác:

• Báo cáo theo tháng: CREATE INDEX ... ON big_orders(strftime('%Y-%m', order_date)) — tăng tốc GROUP BY theo tháng (Bài 4) mà không cần thêm cột phái sinh lưu sẵn.
• Tra mã sản phẩm không phân biệt hoa/thường: CREATE INDEX ... ON products(UPPER(product_code)), tương tự nguyên lý LOWER(email) ở trên.
• Dữ liệu JSON (xem trước Bài 13): CREATE INDEX ... ON events(json_extract(payload, '$.user_id')) — index trên 1 trường bên trong cột JSON, không cần tách trường đó ra cột riêng.

4. Đọc Bytecode VDBE Qua EXPLAIN Đầy Đủ

Mọi câu SQL SQLite chạy đều được biên dịch trước thành bytecode cho 1 máy ảo nội bộ gọi là VDBE (Virtual Database Engine) — giống hệt cách 1 trình biên dịch biến mã nguồn thành bytecode chạy trên JVM hay .NET CLR. EXPLAIN QUERY PLAN (Bài 8, mục 1-2 bài này) chỉ là bản tóm tắt dễ đọc; EXPLAIN đơn thuần (bỏ QUERY PLAN) phơi bày toàn bộ bytecode thật sự được thực thi:

EXPLAIN
SELECT customer_id, total_amount FROM big_orders WHERE customer_id = 2500;

Rút gọn 15 dòng bytecode thật thành các nhóm lệnh chính (đã chạy thật để lấy đúng kết quả này):

Trong công việc hàng ngày, EXPLAIN QUERY PLAN đã đủ cho 95% nhu cầu tối ưu truy vấn — bytecode VDBE chi tiết tới mức này chỉ thật sự cần khi debug các trường hợp cực kỳ hiếm gặp, hoặc khi tò mò muốn hiểu SQL "biến mất" đi đâu trước khi thành kết quả. Điều quan trọng cần nhớ: SQL cũng chỉ là 1 ngôn ngữ được biên dịch giống bao ngôn ngữ khác — không có phép màu nào cả, chỉ là 1 tập lệnh máy ảo được thực thi tuần tự.

5. Thực Hành Với Docker 🐳: So Sánh Optimizer PostgreSQL

Khác biệt cốt lõi: EXPLAIN QUERY PLAN của SQLite chỉ là ước lượng — không bao giờ chạy thật câu lệnh. PostgreSQL's EXPLAIN ANALYZE chạy thật câu lệnh rồi báo cáo thời gian đo được thực tế cho từng bước, song song với chi phí ước lượng — 1 công cụ có bản chất khác hẳn, không chỉ là "phiên bản Postgres của EXPLAIN QUERY PLAN". Dùng lại container techmart-pg đã dựng ở Bài 2:

psql -h localhost -U postgres -d techmart -c "
EXPLAIN ANALYZE
SELECT o.order_id, o.total_amount, c.country
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'Singapore';
"

Hash Join  (cost=1.14..1.42 rows=2 width=44) (actual time=0.045..0.052 rows=2 loops=1)
  Hash Cond: (o.customer_id = c.customer_id)
  ->  Seq Scan on orders o  (cost=0.00..1.28 rows=28 width=16) (actual time=0.008..0.010 rows=28 loops=1)
  ->  Hash  (cost=1.12..1.12 rows=2 width=36) (actual time=0.015..0.015 rows=2 loops=1)
        ->  Seq Scan on customers c  (cost=0.00..1.12 rows=2 width=36) (actual time=0.010..0.011 rows=2 loops=1)
              Filter: (country = 'Singapore'::text)
Planning Time: 0.312 ms
Execution Time: 0.089 ms

Với chỉ 28 đơn/12 khách trong dataset TechMart gốc, PostgreSQL cũng chọn Seq Scan cho mọi thứ — giống hệt lý do Bài 8 phải tự sinh big_orders 100.000 dòng: dữ liệu quá nhỏ để bất kỳ optimizer nào (SQLite hay PostgreSQL) thể hiện rõ sự khác biệt. Muốn thấy Postgres thật sự "vật lộn" với optimizer trên quy mô lớn, dùng generate_series — phiên bản PostgreSQL của kỹ thuật CTE đệ quy sinh dữ liệu đã dùng ở Bài 8:

INSERT INTO orders (customer_id, order_date, status, total_amount)
SELECT
  1 + (random() * 11)::int,
  DATE '2024-01-01' + (random() * 700)::int,
  (ARRAY['delivered','shipped','cancelled','refunded','pending'])[1 + (random() * 4)::int],
  ROUND((10 + random() * 400)::numeric, 2)
FROM generate_series(1, 100000);

Chạy lại đúng EXPLAIN ANALYZE ở trên sau khi có 100.000 dòng thật, PostgreSQL sẽ tự động chuyển sang Index Scan nếu có index phù hợp, đúng nguyên lý selectivity đã học ở mục 2 — nhưng với thống kê giàu hơn hẳn.