🪴ttuan's garden

9 min read

🗺️ MOC - Database Indexing & Những Điều Developer Cần Biết

50 dot notes tách từ ebook của Nguyễn Thế Huy. Sách khuyên đọc tuần tự lần đầu (chương sau xây trên chương trước), sau đó dùng từng note như tài liệu tra cứu độc lập.

Tư tưởng xuyên suốt của cả cuốn sách: Index = sorted list + bảng tóm tắt để nhảy nhanh. Random I/O đắt hơn Sequential I/O 10-100 lần. Mọi quyết định của database đều xoay quanh 2 sự thật này.

🧭 Lộ trình học đề xuất (học theo thứ tự 6 level)

Level 0 - Nền tảng bắt buộc (đọc trước tiên)

Hiểu index LÀ GÌ và chi phí vật lý bên dưới. Không nắm level này thì mọi thứ phía sau chỉ là học vẹt.

  1. B+ Tree - index = sorted list + bảng tóm tắt. Bắt đầu từ đây!
  2. Random IO vs Sequential IO - khái niệm vật lý giải thích MỌI quyết định của DB
  3. Heap Table vs Clustered Index - PostgreSQL vs MySQL lưu dữ liệu khác nhau thế nào
  4. Primary Key và thứ tự Insert - vì sao UUIDv4 làm PK là ý tưởng tồi
  5. Index Write Overhead - trade-off: nhiều index = ghi chậm
  6. Có Index chưa chắc Query nhanh - quy trình 4 bước, phá hiểu lầm lớn nhất

Level 1 - Bốn nguyên tắc vàng ⭐ (phần quan trọng nhất cuốn sách)

Tác giả: “Đây là tất cả những gì bạn cần để tạo index tốt cho bất kỳ query nào.” Học thuộc 4 nguyên tắc này.

  1. Fast Lookup - Nguyên tắc 1: nhảy thẳng đến vị trí cần tìm
  2. Quét một hướng - Nguyên tắc 2: scan ascending/descending từ điểm lookup
  3. Composite Index và Nguyên tắc Phễu - Nguyên tắc 3: từ trái sang phải, không bỏ qua cột ⭐⭐
  4. Range Condition phá vỡ Phễu - Nguyên tắc 4: equality trước, range sau ⭐⭐

Level 2 - Index với từng thao tác SQL

Áp dụng 4 nguyên tắc vào từng loại query thực tế.

  1. SQL Execution Order - đọc trước: thứ tự thực thi ≠ thứ tự viết
  2. Inequality và Index - != là kẻ giết hiệu suất thầm lặng
  3. NULL và Index - NULL ≠ NULL, bug logic lẫn performance
  4. LIKE và Wildcard - 'abc%' OK, '%abc%' thì không
  5. ORDER BY và Index - tránh bước sort bổ sung bằng mọi giá
  6. GROUP BY và DISTINCT - thách thức lớn nhất, hay bị quên tối ưu
  7. Nested Loop Join - join = 2 query độc lập, mỗi cái cần index riêng
  8. Subquery - không chậm như bạn nghĩ
  9. UPDATE và DELETE cũng cần Index - phần WHERE giống hệt SELECT

Level 3 - Tại sao database KHÔNG dùng index của tôi? 🔥

Phần debug - đọc khi (chắc chắn sẽ) gặp tình huống “index có mà DB lờ tịt”.

  1. Query Optimizer và Cost Model - bộ não của DB + checklist debug đầy đủ
  2. EXPLAIN - công cụ số 1, tập thói quen dùng trước khi deploy
  3. Statistics và ANALYZE - thống kê cũ = kẻ phá hoại ngầm
  4. Column Transformation - sai lầm #1: hàm trên cột làm index “mù”
  5. Type Mismatch và Implicit Cast - cái bẫy ngầm VARCHAR vs số trong MySQL
  6. Low-Cardinality Column và Index - vì sao index cột boolean thường vô nghĩa
  7. Index Selection - Xung đột Filter và Sort - khi DB phải chọn giữa nhiều index

Level 4 - Cạm bẫy & kỹ thuật nâng cao (“kho vũ khí”)

Đọc lướt một lần để biết chúng tồn tại, quay lại khi gặp tình huống thực tế.

Các loại index đặc biệt: 27. Functional Index - index trên biểu thức khi không thể viết lại query 28. Virtual Column - workaround cho MariaDB/SQL Server/MySQL JSON 29. Partial Index - chỉ index phần dữ liệu quan tâm (PostgreSQL) 30. Index-Only Query và Covering Index - không cần chạm vào bảng dữ liệu 31. Prefix Index và Hash Index - vượt giới hạn kích thước index 32. Trigram Index - giải cứu LIKE '%abc%' (PostgreSQL) 33. JSON Indexing và GIN Index - đánh index thế giới phi cấu trúc 34. Spatial Index - khi 2 range condition (lat/long) đụng nhau

Kỹ thuật & cạm bẫy: 35. Biến Range thành Equality - hóa giải Nguyên tắc 4 36. Ghost Condition - thêm điều kiện “thừa” giúp DB dùng index tốt hơn 37. Unique Constraint và NULL - lỗi kinh điển ai cũng mắc một lần 38. Unused Index - dọn dẹp index thừa, invisible index 39. Denormalization - bài toán JOIN mà index KHÔNG giải được

Level 5 - Viết query như chuyên gia & thao tác dữ liệu

  1. Keyset Pagination - phân trang đúng cách, thay LIMIT OFFSET
  2. CTE - chia query phức tạp thành bước nhỏ debug được
  3. Lateral Join - “top N per group” hiệu quả
  4. Kỹ thuật thao tác dữ liệu hiệu quả - lock contention, UPDATE JOIN, RETURNING, FOR UPDATE
  5. Query Tips hữu ích - NULLIF, gap-filling, FILTER, DISTINCT ON

Level 6 - Thiết kế Schema: nền móng vững chắc

  1. UUID vs Auto-increment - quyết định PK (kết nối ngược về Level 0)
  2. JSON Column - khi NoSQL gặp SQL
  3. Database Constraint - hàng rào bảo vệ cuối cùng (CHECK, exclusion)
  4. Materialized Path - lưu trữ cây đơn giản
  5. Partitioning - xóa data lớn trong tích tắc
  6. Pre-sort và Pre-aggregation - khi index cũng không đủ nhanh

🚀 Lối vào nhanh theo vấn đề (tra cứu khi gặp chuyện)

Bạn đang gặp gì?Đọc note
Query chậm dù đã có indexCó Index chưa chắc Query nhanh → EXPLAIN
DB không dùng index tôi vừa tạoQuery Optimizer và Cost Model (có checklist)
Không biết đặt cột nào trước trong indexComposite Index và Nguyên tắc Phễu → Range Condition phá vỡ Phễu
WHERE + ORDER BY chọn index thế nàoIndex Selection - Xung đột Filter và Sort
Search text %keyword%LIKE và Wildcard → Trigram Index
Query trên cột status/boolean chậmLow-Cardinality Column và Index → Partial Index
VARCHAR chứa số, query chậm bí ẩn (MySQL)Type Mismatch và Implicit Cast
JOIN chậm dù từng bảng đã có indexNested Loop Join → Denormalization
Phân trang page sâu chậmKeyset Pagination
Chọn primary key cho bảng mớiUUID vs Auto-increment → Primary Key và thứ tự Insert
Index cột JSONJSON Indexing và GIN Index
UNIQUE không chặn được dòng trùngUnique Constraint và NULL
Xóa log/data cũ quá chậmPartitioning
Dashboard aggregate chậmPre-sort và Pre-aggregation

🧠 5 câu thần chú rút gọn cả cuốn sách

  1. “Index = sorted list + bảng tóm tắt để nhảy nhanh” - B+ Tree
  2. “Từ trái sang phải, không bỏ qua cột” - Composite Index và Nguyên tắc Phễu
  3. “Equality trước, Range sau” - Range Condition phá vỡ Phễu
  4. “Biến đổi cột = index mù. Không ngoại lệ” - Column Transformation
  5. “Rows examined >> rows trả về = index chưa đủ tốt” - EXPLAIN

Tổng quan: thứ tự đọc 6 phần

flowchart TD
    Start(["📖 Bắt đầu"]) --> P1

    subgraph P1["1️⃣ NỀN TẢNG - Hiểu Index từ gốc rễ"]
        direction TB
        A1["B+ Tree<br/>(index = sorted list + bảng tóm tắt)"]
        A2["Primary Key & thứ tự insert<br/>(auto-increment vs UUID)"]
        A3["Heap Table vs Clustered Index<br/>(PostgreSQL vs MySQL)"]
        A4["Có index chưa chắc query nhanh<br/>(random I/O là thủ phạm)"]
        A1 --> A2 --> A3 --> A4
    end

    P1 --> P2

    subgraph P2["2️⃣ BỐN NGUYÊN TẮC VÀNG ⭐ (quan trọng nhất)"]
        direction TB
        B1["NT1: Fast Lookup<br/>(nhảy thẳng đến vị trí)"]
        B2["NT2: Quét một hướng<br/>(scan ASC/DESC từ điểm lookup)"]
        B3["NT3: Nguyên tắc Phễu<br/>(composite index: trái → phải)"]
        B4["NT4: Range phá vỡ Phễu<br/>(equality trước, range sau)"]
        B1 --> B2 --> B3 --> B4
    end

    P2 --> P3

    subgraph P3["3️⃣ INDEX VỚI TỪNG THAO TÁC SQL"]
        direction TB
        C0["Thứ tự thực thi SQL:<br/>FROM/JOIN → WHERE → GROUP BY<br/>→ SELECT → ORDER BY → LIMIT"]
        C1["!= • NULL • LIKE"]
        C2["ORDER BY • GROUP BY & DISTINCT"]
        C3["JOIN • Subquery • UPDATE/DELETE"]
        C0 --> C1 --> C2 --> C3
    end

    P3 --> P4

    subgraph P4["4️⃣ TẠI SAO DB KHÔNG DÙNG INDEX CỦA TÔI? 🔥"]
        direction TB
        D1["Quy trình 4 bước + Cost Model<br/>(EXPLAIN là công cụ số 1)"]
        D2["Index không khớp query<br/>(biến đổi cột, sai thứ tự)"]
        D3["Full scan nhanh hơn thật<br/>(DB đúng mà bạn sai!)"]
        D4["DB chọn index khác<br/>(xung đột filter vs sort)"]
        D1 --> D2 --> D3 --> D4
    end

    P4 --> P5

    subgraph P5["5️⃣ CẠM BẪY & MẸO NÂNG CAO (kho vũ khí)"]
        direction TB
        E1["Index đặc biệt: Functional • Partial<br/>Covering/INCLUDE • Prefix/Hash<br/>Trigram • GIN/JSON • Spatial"]
        E2["Kỹ thuật: Range → Equality<br/>Ghost Condition • Denormalization"]
        E3["Cạm bẫy: cột boolean • type mismatch<br/>UNIQUE + NULL • unused index"]
        E1 --> E2 --> E3
    end

    P5 --> P6

    subgraph P6["6️⃣ QUERY CHUYÊN GIA & THIẾT KẾ SCHEMA"]
        direction TB
        F1["Keyset Pagination • CTE<br/>Lateral Join • FOR UPDATE"]
        F2["Schema: UUID vs Auto-increment<br/>JSON Column • Constraint<br/>Partition • Pre-aggregation"]
        F1 --> F2
    end

    P6 --> Done(["✅ Dùng từng chương làm tài liệu tra cứu"])

    style P2 fill:#fff3cd,stroke:#e0a800,stroke-width:3px
    style P4 fill:#f8d7da,stroke:#c82333,stroke-width:2px
    style Start fill:#d4edda,stroke:#28a745
    style Done fill:#d4edda,stroke:#28a745

Hai sự thật vật lý chi phối tất cả

flowchart LR
    T1["🧱 Index = sorted list<br/>+ bảng tóm tắt phân cấp"] --> X["Mọi quyết định<br/>của Database"]
    T2["⚡ Random I/O đắt hơn<br/>Sequential I/O 10-100 lần"] --> X
    X --> R1["Vì sao 4 nguyên tắc vàng tồn tại"]
    X --> R2["Vì sao DB đôi khi bỏ qua index<br/>(và nó ĐÚNG)"]
    X --> R3["Vì sao cần covering index,<br/>partial index, pre-sort..."]

Flow debug nhanh: “Query chậm dù đã có index?”

flowchart TD
    Q(["🐌 Query chậm"]) --> E["Chạy EXPLAIN / EXPLAIN ANALYZE"]
    E --> C1{"DB có dùng<br/>index không?"}

    C1 -- "Không" --> W1{"Cột có bị biến đổi?<br/>(hàm, phép tính, CAST)"}
    W1 -- "Có" --> F1["Viết lại query trên cột gốc<br/>hoặc dùng Functional Index"]
    W1 -- "Không" --> W2{"Kiểu dữ liệu có khớp?<br/>(VARCHAR = số?)"}
    W2 -- "Không khớp" --> F2["Thêm dấu nháy -<br/>so sánh đúng kiểu chuỗi"]
    W2 -- "Khớp" --> W3{"Match bao nhiêu % rows?"}
    W3 -- "> 10-30%" --> F3["DB đúng! Full scan nhanh hơn.<br/>Xem lại thiết kế / partial index"]
    W3 -- "Ít mà vẫn full scan" --> F4["Chạy ANALYZE<br/>(thống kê cũ) • check invisible index"]

    C1 -- "Có" --> V1{"Rows examined >><br/>rows trả về?"}
    V1 -- "Có" --> F5["Index chưa đủ tốt:<br/>thêm cột vào composite index<br/>(equality trước, range sau)"]
    V1 -- "Không" --> V2{"Có bước Sort/Filesort<br/>hoặc temp table?"}
    V2 -- "Có" --> F6["Đưa cột ORDER BY / GROUP BY<br/>vào cuối index"]
    V2 -- "Không" --> F7["Xem lại JOIN:<br/>index cho MỌI thứ tự join,<br/>cân nhắc denormalization"]

    style Q fill:#f8d7da,stroke:#c82333
    style F1 fill:#d4edda,stroke:#28a745
    style F2 fill:#d4edda,stroke:#28a745
    style F3 fill:#d4edda,stroke:#28a745
    style F4 fill:#d4edda,stroke:#28a745
    style F5 fill:#d4edda,stroke:#28a745
    style F6 fill:#d4edda,stroke:#28a745
    style F7 fill:#d4edda,stroke:#28a745

Graph View