Clustered Index và Non-clustered Index

Khi làm việc với database, nhiều lập trình viên gặp một tình huống quen thuộc:

“Cùng một câu SQL, nhưng MySQL và PostgreSQL lại trả về kết quả khác nhau?”

Nguyên nhân không nằm ở SQL sai, mà nằm ở cách dữ liệu được lưu trữ bên dưới, cụ thể là:

• Clustered Index
• Physical order (thứ tự lưu vật lý)
• Và việc sử dụng LIMIT không có ORDER BY

Bài viết này sẽ giúp bạn:

• Hiểu rõ Clustered Index & Non-clustered Index
• Thấy được sự khác biệt giữa MySQL (InnoDB) và PostgreSQL
• Rút ra bài học quan trọng khi viết SQL trong thực tế

Clustered Index và Non-clustered Index là gì?

Hình trên mô tả rất trực quan sự khác nhau giữa hai loại index thông qua hai ví dụ quen thuộc:

• Clustered Index → Cuốn từ điển
• Non-clustered Index → Sách nấu ăn có mục lục

Chúng ta sẽ đi sâu từng phần.

1. Clustered Index – Dữ liệu được sắp xếp thật (Cuốn từ điển)

Bản chất

Clustered Index quyết định cách dữ liệu được lưu trữ vật lý trên đĩa.

Điều này có nghĩa:

• Dữ liệu nằm trực tiếp trong index
• Thứ tự của index chính là thứ tự của dữ liệu
• Một bảng chỉ có duy nhất 1 Clustered Index

Giống như cuốn từ điển:

• Các từ được sắp xếp A → Z
• Khi bạn tìm từ “Phở”, bạn thấy nội dung ngay lập tức
• Không cần tra thêm bước nào khác

Minh họa dữ liệu

Clustered Index (id) 1 → 2 → 3 → 4 → 5 → 6 → 7 → 8 → 9 → 10

Khi truy vấn:

SELECT * FROM keywords WHERE id = 4;

➡️ Database đi thẳng tới vị trí id = 4, đọc dữ liệu ngay.

Ưu điểm

• Rất nhanh khi truy vấn theo khóa chính
• Không cần key lookup
• Đọc dữ liệu tuần tự trên disk (sequential read)

Hạn chế

• Chỉ có 1 Clustered Index
• Insert chậm nếu key không tăng dần
• Update khóa chính có thể gây di chuyển dữ liệu vật lý

Trong MySQL (InnoDB)

• PRIMARY KEY luôn là Clustered Index
• Nếu không có PK → InnoDB tự tạo hidden clustered index
• Mọi Non-clustered Index đều trỏ về Primary Key

2. Non-clustered Index – Mục lục trỏ tới dữ liệu (Sách nấu ăn)

Bản chất

Non-clustered Index là một cấu trúc riêng biệt với dữ liệu bảng.

Nó:

• Không thay đổi thứ tự dữ liệu
• Chỉ lưu:
  - Giá trị cột được index
  - Con trỏ trỏ về dữ liệu thật

Giống như mục lục của sách nấu ăn:

• Mục lục: “Phở bò → trang 112”
• Muốn đọc nội dung:
  1. Tra mục lục
  2. Ghi nhớ số trang
  3. Lật đến trang đó

Minh họa kỹ thuật

Non-clustered Index (email)
email_a → id 3
email_b → id 1
email_c → id 5

Sau đó database: id = 3 → quay lại bảng → đọc dữ liệu

➡️ Đây gọi là Key Lookup

Ưu điểm

• Có thể tạo nhiều index
• Linh hoạt cho tìm kiếm theo nhiều cột
• Không ảnh hưởng thứ tự lưu dữ liệu

Nhược điểm

• Chậm hơn Clustered Index (do phải lookup)
• Tốn thêm bộ nhớ
• Nếu dùng nhiều có thể làm chậm ghi dữ liệu

3. Sự khác biệt cốt lõi giữa MySQL và PostgreSQL

👉 Đây chính là nguyên nhân khiến cùng một câu SQL cho kết quả khác nhau.

4. Ví dụ SQL thực tế

Tạo bảng

CREATE TABLE keywords (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

MySQL: id là Clustered Index

PostgreSQL: id không quyết định thứ tự lưu

Insert dữ liệu

INSERT INTO keywords VALUES (1, 'name1');
INSERT INTO keywords VALUES (2, 'name2');
INSERT INTO keywords VALUES (3, 'name3');
INSERT INTO keywords VALUES (4, 'name4');
INSERT INTO keywords VALUES (5, 'name5');
INSERT INTO keywords VALUES (6, 'name6');
INSERT INTO keywords VALUES (7, 'name7');
INSERT INTO keywords VALUES (8, 'name8');
INSERT INTO keywords VALUES (9, 'name9');
INSERT INTO keywords VALUES (10, 'name10');

Query với LIMIT (chưa có vấn đề)

SELECT id FROM keywords WHERE id > 2 LIMIT 5;

Kết quả (cả hai DB): 3 4 5 6 7

Xóa và insert lại dữ liệu

DELETE FROM keywords WHERE id = 4;
INSERT INTO keywords VALUES (4, 'name4');

Quan sát sự khác biệt
Query: SELECT * FROM keywords;

MySQL (InnoDB)

+----+--------+
| id | name   |
+----+--------+
|  1 | name1  |
|  2 | name2  |
|  3 | name3  |
|  4 | name4  |
|  5 | name5  |
|  6 | name6  |
|  7 | name7  |
|  8 | name8  |
|  9 | name9  |
| 10 | name10 |
+----+--------+

• Dữ liệu luôn được sắp xếp theo id
• Vì id là Clustered Index

PostgreSQL

+----+--------+
| id | name   |
+----+--------+
|  1 | name1  |
|  2 | name2  |
|  3 | name3  |
|  5 | name5  |
|  6 | name6  |
|  7 | name7  |
|  8 | name8  |
|  9 | name9  |
| 10 | name10 |
|  4 | name4  |
+----+--------+

• Record mới được append cuối table
• Không sắp xếp lại dữ liệu

Query lại với LIMIT

SELECT id FROM keywords WHERE id > 2 LIMIT 5;