SERIES INDEX NÂNG CAO - BÀI 3: HIỂU VỀ CLUSTERED INDEX VÀ SECONDARY INDEX TRONG MYSQL (INNODB ENGINE)

Đọc full series Index nâng cao: Kiến trúc và Tối ưu hóa Index trong MySQL dành cho Developer và DBA

Trong hệ quản trị cơ sở dữ liệu MySQL, đặc biệt với bộ lưu trữ InnoDB, việc hiểu cách hoạt động của Clustered Index và Secondary Index là rất quan trọng để tối ưu hiệu suất truy vấn.

Vậy Clustered Index là gì?

Clustered Index là một index đặc biệt mà InnoDB sử dụng để lưu trữ row data. Thông thường, Clustered Index chính là PRIMARY KEY của bảng.

• Khi bạn định nghĩa một PRIMARY KEY, InnoDB sẽ dùng nó làm Clustered Index.
• Nếu không có PRIMARY KEY, InnoDB sẽ chọn UNIQUE index đầu tiên có các cột được khai báo là NOT NULL để làm Clustered Index.
• Nếu không có PRIMARY KEY hoặc UNIQUE index phù hợp, InnoDB sẽ tạo ra một hidden clustered index có tên GEN_CLUST_INDEX, dựa trên một cột tổng hợp chứa row ID — một giá trị dài 6 bytes tăng dần mỗi khi thêm bản ghi mới. Hiểu đơn giản thì các rows sẽ được sắp xếp theo thứ tự row ID, mà thứ tự này sẽ tương ứng với thứ tự insert trước sau của từng row.

👉 LƯU Ý: Để hiệu suất tốt nhất, chúng ta nên luôn định nghĩa PRIMARY KEY cho mỗi bảng. Nếu không có cột nào phù hợp, hãy sử dụng một cột AUTO_INCREMENT.

Ví dụ:

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT
) ENGINE=InnoDB;

👉 Ở đây:

• id là PRIMARY KEY => trở thành Clustered Index.

Lợi ích của Clustered Index

Việc truy cập dữ liệu thông qua Clustered Index rất nhanh, vì quá trình tìm kiếm index sẽ dẫn trực tiếp đến page chứa row data.

Đối với các bảng lớn, điều này thường giúp tiết kiệm thao tác disk I/O so với cách lưu trữ mà row data nằm ở một page khác với index record.

Secondary Index hoạt động như thế nào? Nó liên quan gì đến Clustered Index?

Trong MySQL documentation có đoạn mô tả:

Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

Tức là, các index không phải Clustered Index thì được gọi là Secondary Index.

Mỗi bản ghi trong một Secondary Index của InnoDB sẽ chứa giá trị của PRIMARY KEY, cùng với các cột được chỉ định trong Secondary Index đó.

Vì lý do này, khi bạn tìm kiếm bằng Secondary Index, InnoDB sẽ:

1. Dùng Secondary Index để tìm giá trị PRIMARY KEY của bản ghi tương ứng.
2. Dùng PRIMARY KEY để truy cập dữ liệu thực tế thông qua Clustered Index.

⛔ Nếu PRIMARY KEY dài, mọi bản ghi trong Secondary Index cũng sẽ phình to hơn, vì phải chứa thêm dữ liệu của PRIMARY KEY. Do đó, nên chọn PRIMARY KEY ngắn gọn để tiết kiệm bộ nhớ và cải thiện hiệu suất.

Ví dụ:

CREATE TABLE users ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT
) ENGINE=InnoDB; CREATE INDEX idx_name ON users (last_name, first_name);

👉 Ở đây:

• id là PRIMARY KEY => Clustered Index.
• idx_name là Secondary Index.

Kiểm chứng bằng câu lệnh:

SHOW INDEX FROM users;

👉 Kết quả:

🙋🏻‍♂️ Một số kênh mạng xã hội khác mà tôi dùng để chia sẻ và trao đổi với anh em kiến thức về ngành CNTT và lập trình:

• Group "Khi nào giỏi lập trình thì đổi tên 🫢": https://www.facebook.com/groups/gioilaptrinhthidoiten

• Page "CLB Lập trình - THPT Ngọc Tảo": https://www.facebook.com/clb.it.ngoctao/

• TikTok "CLB Lập trình - THPT Ngọc Tảo": https://www.tiktok.com/@clb.it.ngoctao/

• Youtube "Tờ Mờ Sáng học Lập trình": https://www.youtube.com/@tmsanghoclaptrinh/