Đã đăng vào Thứ Tư, 12:08 SA 10 phút đọc

PostgreSQL Bài 27: Các lỗi phổ biến khiến Index bị vô hiệu hóa và cách khắc phục

Một trong những tình huống gây ức chế nhất với các lập trình viên backend là: rõ ràng đã tạo B-Tree Index trên bảng, nhưng khi kiểm tra EXPLAIN, PostgreSQL vẫn kiên quyết chạy Seq Scan (Sequential Scan toàn bảng).

Tại sao cơ sở dữ liệu lại "ngó lơ" chỉ mục bạn đã cất công tạo? Có phải do PostgreSQL bị lỗi?

Thực tế, Query Planner luôn đưa ra quyết định dựa trên tính toán chi phí (Cost-based). Phần lớn trường hợp index bị bỏ qua xuất phát từ các thói quen viết câu lệnh SQL tưởng chừng vô hại của lập trình viên, hoặc do cấu hình môi trường không tương thích. Bài học này sẽ điểm danh 6 lỗi kinh điển nhất khiến Index bị vô hiệu hóa và cách khắc phục triệt để.

1. Lỗi 1: Sử dụng Hàm hoặc Phép toán trực tiếp trên Cột Index

Đây là sai lầm phổ biến nhất trong các câu lệnh truy vấn hàng ngày.

1.1. Bản chất vấn đề

Cây B-Tree chỉ lưu trữ và sắp xếp các giá trị nguyên bản của cột. Khi bạn bọc một hàm số, hàm xử lý chuỗi hoặc phép toán số học lên cột đó ở vế trái của mệnh đề WHERE, engine không thể dùng cây B-Tree để định tuyến.

SQL

-- GIẢ SỬ ĐÃ CÓ INDEX:
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_products_price ON products(price);

SQL

-- SAI LẦM 1: Bọc hàm ép kiểu ngày tháng
SELECT * FROM users WHERE DATE(created_at) = '2026-10-09';

-- SAI LẦM 2: Thực hiện phép toán trên cột
SELECT * FROM products WHERE price * 0.9 < 100;

Hậu quả: Cả hai câu truy vấn trên đều kích hoạt Seq Scan. Với mỗi dòng, engine buộc phải tính toán DATE(created_at) hoặc price * 0.9 rồi mới so sánh.

1.2. Cách khắc phục

Cách 1: Cô lập cột độc lập (Isolate the Column) – Khuyến nghị số 1:

Hãy chuyển toàn bộ phép tính hoặc hàm sang vế giá trị so sánh (vế phải) để giữ nguyên vẹn cột ở vế trái:

SQL

-- Chuyển DATE(...) thành khoảng thời gian:
SELECT * FROM users 
WHERE created_at >= '2026-10-09 00:00:00+07' 
  AND created_at <  '2026-10-10 00:00:00+07';

-- Chuyển phép nhân sang vế phải thành phép chia:
SELECT * FROM products 
WHERE price < 100 / 0.9;

Cách 2: Sử dụng Expression-based Index (nếu bắt buộc phải dùng hàm):

Nếu nghiệp vụ bắt buộc phải lọc theo kết quả hàm (như LOWER(email)):

SQL
```
CREATE INDEX idx_users_lower_email ON users ((LOWER(email)));
```

2. Lỗi 2: Lệch kiểu dữ liệu và Ép kiểu ngầm định (Implicit Type Casting)

PostgreSQL là một hệ quản trị cơ sở dữ liệu có tính kiểm tra kiểu dữ liệu rất nghiêm ngặt. Khi hai vế của phép so sánh khác kiểu, engine sẽ tự động thực hiện ép kiểu ngầm định.

2.1. Cạm bẫy chuỗi số (`VARCHAR` vs `INTEGER`)

Giả sử bạn có cột lưu số điện thoại hoặc mã định danh dưới dạng chuỗi:

SQL

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    phone_number VARCHAR(20) NOT NULL
);
CREATE INDEX idx_accounts_phone ON accounts(phone_number);

Trong code backend (ví dụ Go, Laravel, Node.js), lập trình viên vô tình truyền vào một số nguyên thay vì chuỗi:

SQL

-- Vô tình truyền số nguyên (INTEGER) thay vì chuỗi '0912345678'
SELECT * FROM accounts WHERE phone_number = 0912345678;

2.2. Cơ chế ép kiểu nội bộ

PostgreSQL kiểm tra bảng quy tắc chuyển đổi kiểu dữ liệu (pg_cast). Theo quy chuẩn, khi so sánh giữa VARCHAR và INTEGER, PostgreSQL sẽ ưu tiên ép kiểu chuỗi sang số nguyên:

Plaintext

CÂU LỆNH BỊ BIẾN ĐỔI NGẦM THÀNH:
SELECT * FROM accounts WHERE phone_number::integer = 912345678;

Và như ở Lỗi 1: khi cột phone_number bị bọc trong hàm ép kiểu ngầm định ::integer, B-Tree Index bị vô hiệu hóa ngay lập tức!

2.3. Cách khắc phục

Luôn kiểm tra và truyền đúng kiểu dữ liệu từ tầng code backend (sử dụng chuỗi có dấu nháy đơn '...' cho các cột chuỗi):

SQL
```
SELECT * FROM accounts WHERE phone_number = '0912345678';
```

3. Lỗi 3: Ký tự đại diện đứng đầu trong `LIKE '%...'`

3.1. Bản chất vấn đề

Cây B-Tree được sắp xếp tuần tự theo thứ tự từ điển từ trái qua phải.

Tìm kiếm tiền tố LIKE 'abc%': Cây B-Tree nhảy tới nhánh bắt đầu bằng 'abc' và quét dải liên tục.
Tìm kiếm có ký tự đại diện ở đầu: LIKE '%abc' hoặc LIKE '%abc%'.

Từ điển B-Tree:
"apple"
"application"
"banana"
"database"
"postgresql"

Tìm LIKE '%base%':
Từ "database" có "base" ở giữa ──► Cây B-Tree không có cách nào biết từ nào chứa "base" mà không duyệt toàn bộ từ đầu đến cuối!

3.2. Cách khắc phục

Nếu chỉ tìm kiếm tiền tố: Tuyệt đối không thêm dấu % ở đầu (LIKE 'Hieu%').
Nếu nghiệp vụ bắt buộc phải tìm kiếm chuỗi con ở bất kỳ vị trí nào: Bỏ B-Tree, chuyển sang dùng GIN Trigram Index với extension pg_trgm (đã học ở Bài 19):

SQL
```
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
```

4. Lỗi 4: Xung đột Collation trên cột Text

Đây là lỗi rất khó phát hiện khi di chuyển cơ sở dữ liệu giữa các môi trường (ví dụ từ Local Mac/Windows lên Server Linux Docker).

4.1. Cơ chế Collation

Mỗi cơ sở dữ liệu hoặc cột chuỗi ký tự đều có một cấu hình sắp xếp (Collation), ví dụ: en_US.utf8, vi_VN.utf8, hoặc C.

Nếu bạn tạo một B-Tree Index thông thường trên cột TEXT:

SQL

CREATE TABLE documents (
    code TEXT
);
CREATE INDEX idx_documents_code ON documents(code);

Mặc định index này sử dụng collation của database (thường là en_US.utf8 hoặc tương đương).

Khi bạn chạy câu lệnh tìm kiếm tiền tố:

SQL

SELECT * FROM documents WHERE code LIKE 'DOC-2026%';

Trên một số hệ thống có Collation ngôn ngữ phức tạp, toán tử LIKE có thể không sử dụng idx_documents_code vì quy tắc sắp xếp ngôn ngữ tự nhiên không tương thích với quy tắc so sánh byte nhị phân của LIKE.

4.2. Cách khắc phục

Khi tạo index trên cột Text chuyên phục vụ cho toán tử LIKE 'abc%', hãy sử dụng lớp toán tử text_pattern_ops (hoặc varchar_pattern_ops):

SQL

CREATE INDEX idx_documents_code_pattern ON documents(code text_pattern_ops);

text_pattern_ops ép B-Tree so sánh từng byte nhị phân (tương đương Collation C), đảm bảo các truy vấn LIKE 'prefix%' luôn kích hoạt Index Scan 100%.

5. Lỗi 5: Lạm dụng Toán tử Phủ định (`!=`, `<>`, `NOT IN`)

B-Tree Index được thiết kế tối ưu nhất để trả lời câu hỏi: "Dữ liệu này nằm ở đâu?", chứ không hiệu quả để trả lời câu hỏi: "Dữ liệu này KHÔNG nằm ở đâu?".

5.1. Phân tích chi phí

Xét câu lệnh:

SQL

SELECT * FROM orders WHERE status != 'CANCELLED';

Nếu trong bảng, đơn hàng 'CANCELLED' chỉ chiếm 1% tổng số dòng.
Điều đó đồng nghĩa với việc câu truy vấn cần lấy ra 99% dữ liệu của bảng!

Lúc này, Query Planner tính toán:

Nếu dùng Index Scan: Phải nhảy ngẫu nhiên (Random I/O) qua lại giữa Index và Heap Table 990.000 lần.
Nếu dùng Sequential Scan: Đọc tuần tự một mạch toàn bộ bảng từ đĩa lên RAM.
Quyết định của Planner: Chọn Seq Scan vì đọc tuần tự 99% bảng nhanh hơn rất nhiều so với đọc ngẫu nhiên qua Index!

5.2. Cách khắc phục

Đảo ngược tư duy logic: Thay vì tìm những gì khác, hãy tìm chính xác những gì cần:

SQL

-- Nếu các trạng thái còn lại ít:
SELECT * FROM orders WHERE status IN ('PAID', 'PENDING', 'SHIPPED');

Sử dụng Partial Index: Nếu bạn chỉ quan tâm đến các đơn hàng chưa hủy:

SQL
```
CREATE INDEX idx_active_orders ON orders (id) WHERE status != 'CANCELLED';
```

6. Lỗi 6: Độ chọn lọc dữ liệu quá thấp (Low Selectivity)

Nhiều bạn thắc mắc: "Tôi query dấu bằng =, cột không có hàm, không lệch kiểu dữ liệu, nhưng Postgres vẫn chạy Seq Scan?"

6.1. Quy tắc Ngưỡng chọn lọc (Selectivity Threshold)

Index không phải lúc nào cũng nhanh hơn đọc toàn bảng.

┌────────────────────────────────────────────────────────┐
│ KHI NÀO PLANNER CHỌN GÌ?                               │
├────────────────────────────────┬───────────────────────┤
│ Lọc ra < 5% - 10% bảng         │ Ưu tiên Index Scan    │
│ Lọc ra 10% - 20% bảng          │ Ưu tiên Bitmap Scan   │
│ Lọc ra > 20% - 25% bảng        │ Bỏ Index, dùng Seq Scan│
└────────────────────────────────┴───────────────────────┘

Ví dụ thực tế:

Bảng users có 1 triệu dòng, có cột gender (giới tính: 'M' và 'F'). Mỗi giới tính chiếm xấp xỉ 500.000 dòng ( $50\%$ ).

SQL

CREATE INDEX idx_users_gender ON users(gender);

-- Planner CHẮC CHẮN SẼ CHỌN Seq Scan:
SELECT * FROM users WHERE gender = 'F';

Bởi vì việc đọc 500.000 dòng thông qua Index sẽ biến thành 500.000 phép Random I/O rời rạc, làm sập hiệu năng ổ đĩa. Quét tuần tự toàn bảng một lần là lựa chọn thông minh và nhanh hơn nhiều.

Bài học cốt lõi: Đừng bao giờ tạo B-Tree Index đơn lẻ trên các cột có độ phân tán cực thấp (Low Cardinality) như Giới tính, Cờ Boolean (is_active), hoặc Trạng thái ít giá trị, trừ khi bạn sử dụng kỹ thuật Partial Index đã học ở Bài 24.

7. Bảng tổng kết: Checklist gỡ lỗi khi Index không hoạt động

Khi gặp tình trạng index không chạy, hãy kiểm tra lần lượt theo bảng sau:

Kiểm tra	Nguyên nhân nghi vấn	Giải pháp xử lý
Cột có bị bọc bởi hàm?	`DATE(col)`, `LOWER(col)`, `col + 1`	Chuyển phép tính sang vế phải hoặc tạo Expression Index.
Kiểu dữ liệu hai vế có khớp?	Chuỗi so sánh với số, Timestamp so sánh với Timestamptz	Ép kiểu rõ ràng ở vế phải, truyền đúng kiểu từ application.
`LIKE` có chứa `%` ở đầu?	`LIKE '%abc%'`	Dùng GIN Trigram (`pg_trgm`) thay thế cho B-Tree.
Có dùng toán tử phủ định?	`!=`, `<>`, `NOT IN`	Đổi sang so sánh bằng tập hợp `IN (...)` hoặc dùng Partial Index.
Tỷ lệ dữ liệu trả về là bao nhiêu?	Query trả về > 20% tổng số dòng của bảng	Hành vi tối ưu bình thường của Planner (chấp nhận Seq Scan).
Dữ liệu thống kê có bị cũ?	Bảng vừa nạp hàng loạt dữ liệu mới	Chạy ngay lệnh `ANALYZE table_name;`.

8. Tóm tắt & Bài tiếp theo

Đừng bao giờ biến đổi dữ liệu trên cột có index ở mệnh đề WHERE; hãy cô lập cột về dạng nguyên bản.
Tránh ép kiểu ngầm định bằng cách truyền tham số chuẩn xác từ code backend.
Hiểu rõ ranh giới hiệu năng của B-Tree: không hỗ trợ tìm chuỗi con bất kỳ (%abc%) và không hiệu quả với các phép phủ định (!=).
Planner bỏ qua index để chạy Seq Scan trên tập dữ liệu lớn là một hành vi tối ưu có chủ đích dựa trên chi phí I/O.

Bài 28 xem tiếp: Kỹ thuật tạo Index an toàn trên Production với CREATE INDEX CONCURRENTLY — khép lại Phần 4, chúng ta sẽ tìm hiểu lý do tại sao lệnh CREATE INDEX thông thường có thể làm sập hệ thống do chiếm giữ ShareLock chặn toàn bộ lệnh ghi, cơ chế chạy 2-phase của CONCURRENTLY, và cách xử lý sự cố khi index bị gắn cờ INVALID.

PostgreSQL