1.1 Mục tiêu — "biết tại sao query chậm"

Tám tuần qua bạn tập trung viết query đúng. Tuần này thêm chiều thứ hai: viết query nhanh.

Trong mock interview, sau khi bạn đưa lời giải đúng, interviewer thường hỏi follow-up:

• "Nếu bảng có 100 triệu dòng, query này có chạy được không?"
• "Bạn sẽ index như thế nào để tối ưu?"
• "EXPLAIN trên query này, bạn mong đợi thấy gì?"
• "Có cách nào viết khác để optimizer làm tốt hơn không?"

Đây là lúc phân tách candidate junior với senior. Tuần này trang bị cho bạn câu trả lời.

Sau tuần 9 bạn cần làm được

• Đọc EXPLAIN output cơ bản — phân biệt seq scan, index scan, hash join, nested loop.
• Viết CREATE INDEX phù hợp cho query thường gặp (single-col, composite).
• Nhận diện 5 anti-pattern phổ biến và biết cách sửa.
• Liệt kê khác biệt phương ngữ MySQL / PostgreSQL / SQL Server cho 6 nhóm function chính.

1.2 Mental model — storage, plan, execution

Khi bạn viết một query, ba thứ xảy ra trong DB engine:

1. Parse — kiểm tra cú pháp, tên cột/bảng tồn tại.
2. Plan — optimizer chọn execution plan: scan strategy, join order, join algorithm. Đây là phần EXPLAIN cho bạn xem.
3. Execute — chạy plan, đọc data từ disk/cache, áp filter, ghép, aggregate.

Tốc độ query phụ thuộc chính vào số trang disk phải đọc — không phải số dòng output. Một bảng 1M dòng có index phù hợp có thể đọc 100 trang. Cùng bảng full scan đọc 100,000 trang. Khác biệt 1000×.

Hai biến quan trọng nhất

• Selectivity của predicate — predicate giữ lại 0.1% dòng vs 50% dòng. 0.1% có lợi từ index; 50% có khi seq scan nhanh hơn.
• Cardinality của table — bảng 1000 dòng full scan 1ms; bảng 100M dòng full scan có thể nhiều giây hoặc phút.

2.1 B-tree index — cấu trúc và hoạt động

Tìm id = 250: từ root → node giữa (250 ≥ 200) → leaf. 3 lần đọc disk thay vì scan toàn bảng.

B-tree hỗ trợ tốt

• Equality: WHERE col = X
• Range: WHERE col > X, BETWEEN
• Prefix LIKE: WHERE col LIKE 'A%'
• ORDER BY: tránh sort khi sort key trùng index

KHÔNG hỗ trợ tốt: LIKE '%X', LIKE '%X%', function trên cột.

2.2 Composite index — column order quan trọng

Quy tắc "leftmost prefix"

Index (A, B, C) hỗ trợ:

• WHERE A = ... ✓
• WHERE A = ... AND B = ... ✓
• WHERE A = ... AND B = ... AND C = ... ✓ (toàn bộ)
• WHERE A = ... AND C = ... ✓ chỉ phần A; B không sort được nên C lookup mỗi A-value
• WHERE B = ... ✗ (không có A — index vô dụng)
• WHERE C = ... ✗

-- Query: WHERE country = 'VN' AND signup_date > '2024-01-01' ORDER BY signup_date
CREATE INDEX idx_country_date ON customers(country, signup_date);
-- (country) trước vì là equality, (signup_date) sau cho range + sort

2.3 Hash index và các loại khác

Khi nào dùng hash index?

Hiếm. PostgreSQL có hash index nhưng B-tree thường tốt tương đương cho equality trên data thông thường. Lý do duy nhất chọn hash: cột rất nhiều giá trị unique và bạn KHÔNG cần range query.

2.4 Khi index không giúp (hoặc làm chậm)

3.1 EXPLAIN — cú pháp và mục đích

EXPLAIN trả về kế hoạch thực thi (mà optimizer chọn). Không thực thi query, chỉ show plan.

EXPLAIN ANALYZE (PostgreSQL/MySQL 8.0+) chạy thật và đo thời gian — quan trọng hơn cho debug performance.

-- PostgreSQL / MySQL 8+
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- SQL Server
SET SHOWPLAN_TEXT ON;
SELECT * FROM orders WHERE customer_id = 42;

Output PostgreSQL EXPLAIN — cần biết đọc

Đọc:

• Index Scan using idx_customer — engine dùng index idx_customer.
• cost=0.42..8.44 — startup cost..total cost (đơn vị tương đối, không phải ms).
• rows=10 — ước lượng số dòng trả về.
• Index Cond — predicate được áp ngay tại index lookup.

3.2 Scan types — đọc ngắn gọn

3.3 Join algorithms — ba loại chính

3.4 EXPLAIN ANALYZE — actual vs estimated

EXPLAIN cho ước lượng. EXPLAIN ANALYZE chạy thật và so sánh.

Phần in đậm (actual time=...) là số đo thực tế. So sánh:

• rows estimated vs actual — nếu lệch >10× → statistics cũ. Chạy ANALYZE table_name;
• actual time — startup..total per loop. Nhân với loops cho tổng.
• Buffers (thêm EXPLAIN (ANALYZE, BUFFERS)) — số trang đọc từ cache (shared hit) vs disk (read). Cache hit cao = tốt.

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE order_date < '2020-01-01';
ROLLBACK;

4.1 Function trên cột đã index

Anti-pattern phổ biến nhất. Đặt function lên cột → optimizer không dùng index.

-- Function trên cột → seq scan
WHERE YEAR(order_date) = 2024

WHERE LOWER(email) = 'a@b.com'

WHERE order_date + INTERVAL 7 DAY > NOW()

-- Predicate có thể dùng index
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01'

WHERE email = LOWER('a@b.com')
-- (giả sử email đã lowercase)

WHERE order_date > NOW() - INTERVAL 7 DAY

-- PostgreSQL/SQL Server hỗ trợ
CREATE INDEX idx_email_lower ON users(LOWER(email));
-- Bây giờ WHERE LOWER(email) = '...' DÙNG được index

4.2 SELECT * vs cột cụ thể

SELECT * tiện cho ad-hoc query. Nhưng trong production code có ba vấn đề:

1. Băng thông và memory — kéo về cột không cần. Bảng có cột TEXT/BLOB lớn → đắt.
2. Phá covering index — index có thể phủ hết cột query (index only scan, nhanh nhất). SELECT * ép phải đọc bảng gốc.
3. Code fragility — schema thay đổi (thêm cột) → query có behavior khác mà người viết không lường được.

CREATE INDEX idx_cust_status_total ON orders(customer_id, status, total);

-- Index only scan (nhanh nhất) — không đọc bảng
SELECT customer_id, status, total FROM orders WHERE customer_id = 42;

-- Index scan + table fetch — đọc thêm bảng
SELECT * FROM orders WHERE customer_id = 42;
-- (vì cần order_id, order_date, ... không có trong index)

Trong code review và interview: cột cụ thể là norm, SELECT * chỉ trong scratch query.

4.3 OR vs UNION ALL với cột khác nhau

Khi predicate có OR trên hai cột khác nhau (mỗi cột có index riêng), optimizer thường không dùng được cả hai index — fallback seq scan.

-- Hai cột có index, nhưng OR
SELECT * FROM customers
WHERE email   = 'a@b.com'
   OR phone   = '0901234567';

-- Optimizer thường chọn seq scan

SELECT * FROM customers
WHERE email = 'a@b.com'
UNION ALL
SELECT * FROM customers
WHERE phone = '0901234567'
  AND email IS DISTINCT FROM 'a@b.com';
-- (loại trùng nếu cùng row có cả hai)

4.4 Anti-pattern khác — quick reference

Keyset pagination — pattern senior

-- Anti-pattern: OFFSET (chậm trên trang xa)
SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 100000;

-- Pattern: keyset (luôn nhanh)
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 50;
-- Cần track id cuối từ trang trước

Đề cập keyset pagination trong mock interview là tín hiệu candidate có production experience.

5.1 String và Date functions

5.2 Pagination, NULL handling

Pagination — LIMIT/TOP/FETCH

NULL handling — null-safe equality

NULL replacement

5.3 Set operations và feature support

LeetCode dùng MySQL 8+; mọi feature hiện đại đều dùng được. StrataScratch/DataLemur dùng PostgreSQL. Trong production, phải biết platform team đang dùng.

5.4 Strategy phương ngữ trong interview

1. Hỏi platform trước. "Code này tôi viết cho dialect nào?" — câu hỏi đầu tiên trong mock interview.
2. Default đa dialect. Khi không biết platform: viết theo chuẩn ANSI nhiều nhất có thể (COALESCE, EXTRACT, CASE WHEN thay vì IF).
3. Đề cập alternative. Khi viết MySQL-specific (DATE_FORMAT), có thể nói: "Nếu trên PostgreSQL, sẽ dùng TO_CHAR."
4. Biết "must-have" mỗi platform.
   • MySQL: LIMIT n OFFSET m, CONCAT, DATE_ADD ... INTERVAL, DATE_FORMAT.
   • PostgreSQL: || concat, EXTRACT, DATE_TRUNC, FILTER.
   • SQL Server: TOP, DATEADD/DATEDIFF, FORMAT, CHARINDEX.

6.1 Case study — query chậm và cách fix

Query gốc

SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE YEAR(o.order_date) = 2024
  AND c.country = 'Vietnam'
ORDER BY o.order_date DESC
LIMIT 100;

EXPLAIN report

Vấn đề và fix

1. YEAR(order_date) ngăn dùng index trên order_date → fix: nửa-mở interval.
2. Customers seq scan nếu country index thiếu → tạo index.
3. SELECT * kéo nhiều cột không cần — chọn cột cụ thể.

-- Fix:
CREATE INDEX idx_customer_country ON customers(country);
CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);

SELECT o.order_id, o.order_date, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_date <  '2025-01-01'
  AND c.country = 'Vietnam'
ORDER BY o.order_date DESC
LIMIT 100;

Sau fix: index scan trên orders, hash join nhỏ, sort tránh được nếu LIMIT đủ chặt → có thể nhanh 100×.

6.2 Index strategy cho 7 pattern (tuần 8)

SELECT customer_id, name, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) rn
FROM orders WHERE status = 'paid';

Knowledge Check — Tuần 9

8.1 Hands-on — local PostgreSQL hoặc DB Fiddle

Tuần này không phải LeetCode — luyện tools thật. Cài PostgreSQL local hoặc dùng DB Fiddle (db-fiddle.com).

Bài tập

1. Setup data sinh tổng hợp. Tạo bảng orders với 100K dòng (dùng generate_series trên PostgreSQL hoặc recursive CTE trên MySQL — đã học tuần 7).
2. Query không index. Chạy EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42. Note thời gian.
3. Tạo index single-col. CREATE INDEX idx_cust ON orders(customer_id). Chạy lại EXPLAIN ANALYZE. So sánh.
4. Composite index. Query WHERE customer_id = 42 AND status = 'paid' ORDER BY order_date. Thử 3 thứ tự cột khác nhau cho composite index. EXPLAIN cho từng cái. Quan sát.
5. Anti-pattern test. Chạy WHERE YEAR(order_date) = 2024 trước và sau khi viết lại bằng nửa-mở. Note Seq Scan vs Index Scan.
6. Pagination test. Chạy LIMIT 50 OFFSET 50000. Refactor sang keyset. So tốc độ.

Bonus — cross-dialect

Lấy 3 query bạn đã làm tuần 6-8. Viết lại trên cả MySQL syntax VÀ PostgreSQL syntax. Note tất cả các điểm khác (function, syntax window, COALESCE/IFNULL, ...).

8.2 Đọc thêm

Đọc thiết yếu

• SQL Performance Explained (Markus Winand) — toàn bộ. Cuốn sách quan trọng nhất về SQL performance; ngắn (200 trang) và practical.
• Markus Winand — use-the-index-luke.com phiên bản online miễn phí của cuốn trên.
• SQL Antipatterns (Bill Karwin) — Phần II "Database Programming Antipatterns".

Tham khảo dialect

• PostgreSQL: Chapter 14 Performance Tips, Chapter 11 Indexes.
• MySQL: Chapter 8 Optimization, đặc biệt phần "Optimizing Queries with EXPLAIN".
• Bert Wagner — Performance Tuning Mastery (free YouTube series).

Tự kiểm tra (không nhìn slide)

• Bạn vẽ được B-tree đơn giản và giải thích cách lookup không nhìn slide?
• Bạn nhớ quy tắc leftmost prefix cho composite index?
• Bạn liệt kê được 5 anti-pattern và cách fix mỗi cái?
• Bạn nhớ 3 algorithm join và khi nào optimizer chọn cái nào?
• Bạn dịch được 5 function MySQL ↔ PostgreSQL ↔ SQL Server không tra docs?