Tóm Tắt
Video hướng dẫn:
Tổng quan
Giả sử bạn có một cuốn sách SQL và bạn muốn tìm phần kiến thức nói về GROUP BY, bạn có thể sử dụng hai cách, thứ nhất bạn bạn có thể tìm từ đầu đến cuối (hoặc từ cuối lên đầu) cuốn sách, thứ hai bạn tìm đến phần mục lục của cuốn sách rồi tra xem mục bạn muốn xem nằm ở trang nào.
Rõ ràng cách thứ hai sẽ khoa học và tốn ít thời gian tìm kiếm hơn cách thứ nhất, INDEX (hay Chỉ mục) tương tự như cách thứ hai.
Mỗi bảng trong SQL có thể được ví như một cuốn sách, mỗi bản ghi (hàng dữ liệu) của bảng có thể coi như một trang sách. INDEX sẽ có nhiệm vụ lập chỉ mục cho bảng.
Bạn biết rằng các bản ghi trong một bảng được lưu trữ theo thứ tự mà chúng được chèn (INSERT) vào bảng, trong đó bản ghi được chèn vào trước sẽ có vị trí trước, bản ghi được chèn vào sau có vị trí sau; có nghĩa rằng các bản ghi trong bảng không được sắp xếp, nếu vậy khi truy xuất dữ liệu (tìm kiếm) từ bảng thì hệ thống phải quét (scan) trên toàn bộ bảng, và điều này sẽ làm giảm đi tốc độ truy xuất dữ liệu.
Nhưng khi bạn tạo chỉ mục (index) cho bảng thì lại khác, index sẽ tiến hành tạo một thứ tự cho các bản ghi của bảng, và điều này sẽ giúp làm tăng tốc độ truy xuất dữ liệu.
Lưu ý
+ Bạn không nên tạo index nếu nó không được sử dụng thường xuyên, bởi vì để duy trì nó sẽ phải mất thời gian cũng như tài nguyên lưu trữ.
+ Bạn không nên tạo index trên cột hay tập cột có chứa những giá trị / tập giá trị giống nhau.
Cấu trúc INDEX
Cấu trúc Cây nhị phân (Binary-tree = B-tree):
Cấu trúc INDEX dạng B-tree:
Về phân loại thì ta có hai loại INDEX cơ bản là Clustered Index và Nonclustered Index. Hai loại INDEX này được trình bày chi tiết dưới đây.
Clustered Index
Clustered Index sẽ tiến hành lưu trữ các bản ghi của bảng ở mức vật lý rồi tiến hành sắp xếp chúng, nó thường áp dụng cho bảng chứa một lượng bản ghi lớn. Dữ liệu được lưu trữ theo cách sắp xếp trên khóa clustered và mỗi nút lá (leaf) của clustered index chứa một bản ghi.
Các đặc điểm sau đây cần lưu ý khi sử dụng Clustered Index:
– Mỗi bảng chỉ được phép có một Clustered Index.
– Khóa chính (Primary Key – PK) chính là một Clustered Index.
– Clustered Index chỉ được tạo trên cột hoặc tập cột có chứa những giá trị hoặc tập giá trị duy nhất.
Giả sử ta có bảng Student gồm các cột sau: studentid (mã sinh viên), studentname (họ và tên), dateofbirth (ngày sinh), và email (email của sinh viên). Nếu bảng Student đã có PK thì bạn không thể tạo được Clustered Index trên nó vì PK chính là Clustered Index, muốn tạo bạn phải xóa PK. Nếu Student không có PK bạn có thể tạo được Clustered Index trên bảng này, nhưng nếu bạn định tạo trên cột studentname thì bạn cần xem lại vì cột này có thể có chứa nhiều sinh viên trùng tên; bạn có thể đặt Clustered Index trên cột email hoặc studentid là những cột chỉ chứa những giá trị duy nhất.
Cú pháp tạo Clustered Index như sau:
CREATE CLUSTERED INDEX Tên_index ON Tên_bảng(Các_cột);
Ví dụ, nếu bảng Student chưa có PK, ta thiết lập Clustered Index cho nó như sau:
CREATE CLUSTERED INDEX clus_student ON Student(studentid);
Nonclustered Index
Nonclustered Index được định nghĩa trên bảng trong đó dữ liệu có thể có cấu trúc phân cụm (clustered structure) hoặc ở dạng vun đống (heap).
Mỗi hàng index trong Nonclustered Index sẽ chứa một giá trị khóa nonclustered và một bộ định vị hàng.
Nonclustered Index thường áp dụng cho bảng chứa một lượng bản ghi nhỏ; một bảng có thể có nhiều Nonclustered Index.
Dữ liệu được lưu trữ theo cách sắp xếp trên khóa nonclustered và mỗi nút lá (leaf) của Nonclustered Index chứa một bản ghi.
Cú pháp tạo Nonclustered Index
CREATE NONCLUSTERED INDEX Tên_index ON Tên_bảng(Các_cột);
Hoặc:
CREATE INDEX Tên_index ON Tên_bảng(Các_cột);
Ví dụ, để tạo Nonclustered Index cho cột studentname của bảng Student, ta làm như sau:
CREATE NONCLUSTERED INDEX nonclus_student ON Student(studentname);
Hoặc:
CREATE INDEX nonclus_student ON Student(studentname);
Để tạo Nonclustered Index cho các cột studentname và dateofbirth của bảng Student, ta làm như sau:
CREATE NONCLUSTERED INDEX nonclus_student ON Student(studentname,dateofbirth);
Hoặc:
CREATE INDEX nonclus_student ON Student(studentname,dateofbirth);
Unique Index
Unique Index hay Unique Nonclustered Index dùng để tạo chỉ mục trên cột hay tập cột chứa những giá trị hoặc tập giá trị duy nhất.
Lưu ý: Khi đã tạo Unique Index trên cột hay tập cột của bảng thì sau đó bạn sẽ không thể chèn (INSERT) vào bảng này bản ghi có giá trị hay tập giá trị giống với bất kỳ giá trị hay tập giá trị đã được chèn trước đó.
Cú pháp:
CREATE UNIQUE NONCLUSTERED INDEX Tên_index ON Tên_bảng(Các_cột);
Hoặc:
CREATE UNIQUE INDEX Tên_index ON Tên_bảng(Các_cột);
Ví dụ, với bảng Student ở trên ta thấy rằng cột studentemail là cột chứa những email của sinh viên, những email này là duy nhất, tức là không có email giống nhau. Như vậy ta có thể tạo Unique Index trên cột này. Ta làm như sau:
CREATE UNIQUE NONCLUSTERED INDEX uninonclus_student ON Student(email);
Hoặc:
CREATE UNIQUE INDEX uninonclus_student ON Student(email);
Xoá Index
Để xoá Index ta sử dụng câu lệnh DROP INDEX. Đoạn mã dưới đây sẽ tiến hành xóa các index tương ứng của bảng Student:
DROP INDEX Student.clus_student;
DROP INDEX Student.nonclus_student;
DROP INDEX Student.uninonclus_student;
Xem thông tin Index
Nếu bạn muốn biết xem một bảng nào đó có những Index nào và thông tin cụ thể của nó ra sao bạn sử dụng sp_helpindex theo cú pháp sau:
EXEC sp_helpindex ‘Tên_bảng’;
Ví dụ:
EXEC sp_helpindex ‘Student’;
