Trong bài trước, chúng ta đã tìm hiểu về Index và cấu trúc B-Tree, mục đích là giúp tăng tốc độ truy vấn, giảm số lần đọc vật lý trên ổ đĩa. Trong SQL Server có hai loại index chính:
- Cluster Index
- Non-Cluster Index
Ngoài ra, còn có một biến thể thường dùng trong thực tế là Composite Index (chỉ mục kết hợp nhiều cột).
Cluster Index
Ví dụ: tạo bảng Staff
CREATE TABLE Staff
( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, gender VARCHAR(50) NOT NULL, ranking INT NOT NULL, city_id INT NOT NULL, district_id INT NOT NULL
)
Trong lệnh trên, cột id được khai báo là PRIMARY KEY, do đó mặc định SQL Server sẽ tạo một Cluster Index trên cột này.
Kiểm tra index trong bảng:
EXECUTE sp_helpindex Staff
Một Cluster Index có tính chất sắp xếp dữ liệu theo một trật tự nhất định.
Giả dụ ta thực hiện insert 10 records và cố tình chèn thay đổi thứ tự insert dữ liệu như bên dưới
INSERT INTO Staff (id, name, gender, ranking, city_id, district_id) VALUES
(1, 'Nguyen Van A', 'Male', 1, 1, 101),
(10, 'Phan Thi J', 'Female', 1, 5, 502),
(3, 'Le Van C', 'Male', 3, 2, 201),
(4, 'Pham Thi D', 'Female', 1, 2, 202),
(6, 'Do Thi F', 'Female', 3, 3, 302),
(7, 'Vo Van G', 'Male', 1, 4, 401),
(8, 'Bui Thi H', 'Female', 2, 4, 402),
(9, 'Dang Van I', 'Male', 3, 5, 501),
(5, 'Hoang Van E', 'Male', 2, 3, 301),
(2, 'Tran Thi B', 'Female', 2, 1, 102);
Khi thực hiện:
SELECT * FROM Staff;
Kết quả trả về vẫn sắp xếp theo id tăng dần, mặc dù dữ liệu insert vào không theo thứ tự. Điều này chứng minh dữ liệu trong bảng được tổ chức vật lý dựa trên Cluster Index.
Hỏi là, một table có thể có bao nhiêu cluster index
⇒ Chỉ duy nhất 1 cluster index. Vì tính chất các dữ liệu table sẽ sắp xếp theo trật tự của clusster index nên có thể lý giải được việc này
Hỏi là, có thể tạo cluster index không phải là một primary key được không
⇒ Được, sử dụng lệnh Create CLUSTERED Index
Hỏi là, khi tạo private key mặc định cluster index được tạo theo, vậy private key có phải là cluster index không
⇒ Không. vì hoàn toàn có thể tạo một Cluster index không phải là private key
Non-Cluster Index
Ví dụ: tạo Non-Cluster Index trên cột name
CREATE INDEX idx_staff_name ON Staff (name ASC);
Kiểm tra lại:
EXECUTE sp_helpindex Staff;
Non-cluster index được lưu trữ ở một nơi khác và không làm thay đổi trật tự sắp xếp vật lý của table đó.
Ví dụ, khi tìm Staff theo name, SQL Server sẽ sử dụng Non-Cluster Index để nhanh chóng xác định vị trí, sau đó tham chiếu để lấy dữ liệu.
Hỏi là, một table có thể có bao nhiêu non-cluster index
⇒ Có nhiều non-cluster index. Tùy thuộc vào nhu cần tối ưu truy xuất dữ liệu ta sẽ đánh index theo các chiến lược khác nhau do vậy có thể tạo nhiều non-cluster index cho từng chiến lược cụ thể.
Hỏi là, khi operator xác định được key index trên non-cluster index thì nó có truy xuất ngay dữ liệu đó không
⇒ Không. Vì khi xác định được vị trí key index thì cần làm thêm một bước để tham chiếu đến địa chỉ và lấy dữ liệu. Khác với cluster index nó có thể lấy dữ liệu ra ngay.
Hôm nay chỉ nói lý thuyết phần thực hành sẽ để bài "Thực Hành Non-seekable" sẽ thấy rõ hơn việc có non-cluster index và không có non-cluster index sẽ có sự khác biệt khi lượng dữ liệu lớn
Composite Index
Ví dụ: tạo Composite Index trên nhiều cột
CREATE INDEX idx_staff_city_district
ON Staff(city_id, district_id);
Composite Index là một Non-Cluster Index trên nhiều cột. Nó đặc biệt hữu ích khi ta thường xuyên truy vấn kết hợp nhiều điều kiện.
Ví dụ truy vấn:
SELECT *
FROM Staff
WHERE city_id = 7 AND district_id = 8;
Trong trường hợp này, SQL Server có thể sử dụng Composite Index để có thể xác định vị trí không cần scan toàn bộ table giảm số lần I/O của hệ thống
Lời kết
-
Cluster Index:
- Dữ liệu được sắp xếp vật lý.
- Chỉ có 1 trên mỗi bảng.
- Thường gắn với Primary Key, nhưng không bắt buộc.
- Truy vấn nhanh hơn vì không cần Lookup.
-
Non-Cluster Index:
- Được lưu trữ tách biệt, không ảnh hưởng tới thứ tự vật lý.
- Có thể tạo nhiều trên cùng bảng.
- Tìm kiếm nhanh nhưng cần bước Lookup để lấy dữ liệu đầy đủ.
-
Composite Index:
- Là Non-Cluster Index trên nhiều cột.
- Giúp tối ưu truy vấn nhiều điều kiện.