SQL SERVER: Sử dụng INDEX để tăng tốc độ thực thi câu truy vấn

Index là gì

Index là một trong những thành phần quan trọng của SQL nó hổ trợ việc truy vấn dữ liệu nhanh hơn. Giống như phần mục lục trong cuốn sách, Index giúp SQL Server xác định được nơi dữ liệu bạn đang tìm một cách nhanh chóng.
Hãy tưởng tượng quá trình truy vấn dữ liệu với mệnh đề WHERE, hệ thống cần đọc qua hết các bản ghi trong Table, và kiểm tra chúng với điều kiện trong mệnh đề WHERE để xác định bảng ghi thoải mãn yêu cầu. Điều này tốn khá nhiều thời gian và tài nguyên hệ thống, đặc biệt là khi dữ liệu quá lớn. Index giống như một bảng dữ liệu có cấu trúc, các dữ liệu sẽ được sắp xếp theo một cấu trúc nhất định, thường là dạng BTREE và một giá trị để tham chiếu tới dữ liệu thật (lưu trong Table). Khi tìm kiếm, hệ thống sẽ sử dụng phương pháp tìm kiếm đặc biệt (tìm kiếm nhị phân) để xác định những node thoải mãn điều kiện, và từ giá trị tham chiếu, hệ thống sẽ trả về tất cả những dữ liệu chúng ta đang tìm

Ví dụ và phân tích hiệu suất của INDEX

Bây giờ chúng ta sẽ tạo hai bảng hoàn toàn giống nhau, bao giồng ID và Note
-TEST : sẽ là bảng chứ dữ liệu mà không có bất kỳ INDEX nào được sử dụng
-TEST_INDEX : chúng ta sẽ đánh INDEX cho cột ID của bảng này.
Sau đó chúng ta sẽ INSERT vào trong mỗi bảng 30000 row. Đối với bảng TEST_INDEX, khi dữ liệu được INSERT vào bảng, hệ thống cũng sẽ tự động cập nhật INDEX để đưa giá ID vào.

CREATE TABLE TEST
(
ID INT IDENTITY,
NOTE VARCHAR(255),
)

CREATE TABLE TEST_INDEX
(
ID INT IDENTITY,
NOTE VARCHAR(255),
)

CREATE INDEX TEST_INDEX_ID ON TEST_INDEX(ID)

DECLARE @count INT
SET @count = 1
WHILE (@count < 30000)
BEGIN
    INSERT INTO TEST(NOTE) VALUES('Test')
    INSERT INTO TEST_INDEX(NOTE) VALUES('Test')
    SET @count = @count + 1
END

Giờ chúng ta thử thực hiện câu lệnh truy vấn tim bảng ghi có ID = 20000

SELECT * FROM TEST WHERE ID = 20000
SELECT * FROM TEST_INDEX WHERE ID = 20000

Đối với bảng TEST, hệ thống cần quét hết tất cả bảng ghi trong Table và kiểm tra xem, nơi nào có ID = 20000. Hệ thống sẽ không dừng lại khi tim thấy bảng ghi đầu tiên. Nó cần quét qua tất cả bảng ghi để tìm bới ở đây có thể có nhiều hơn 1 bảng ghi có ID = 20000.
Đối với bảng TEST_INDEX, hệ thống sẽ tìm kiếm trong INDEX bằng thuật toán đặc biệt ( Thường là tìm kiếm nhị phân) để nhanh chóng xác định những node có ID = 20000. Và từ giá trị tham chiếu trong bảng ghi này, nó sẽ lấy tất cả những bảng ghi trong bảng TEST_INDEX

Chúng ta hãy thử xem hiệu suất của 2 câu truy vấn trên SQL SERVER. Đầu tiên, chúng ta cần bật chế độ "Include Actual Execution Plan" như hình bên dưới

Kết quả thực thi hai câu truy vấn

Ở đây chúng ta thấy, việc truy vấn trên  bảng TEST tốn tới 94% trong tổng quá trình thực hiện hai câu truy vấn.
- Đối với truy vấn trên bảng TEST: bảng này được quét 100%
- Đối với truy vấn trên bảng TEST_INDEX: Hệ thống đã tìm kiếm trên INDEX(chiếm 50%) và sau đó kết quả từ INDEX được JOIN đến TEST_INDEX (50%) để đưa ra dữ liệu

Các Loại INDEX

- Clustered Index: Index được lưu trữ vật lý cùng với TABLE. Loại này chỉ được phép có một trên mỗi bảng
- Non-Clustered Index: Được lưu trữ tách biệt với TABLE. Ở đây sẽ có giá trị tham chiếu trỏ tới giá trị thực trên table. Như ví dụ trên, INDEX chúng ta là Non-Clustered
Các INDEX có thể có 1 hoặc nhiều cột (Composite index). Index sẽ tự động tạo khi có ràng buộc PRIMARY KEY hoặc UNIQUE trên các cột của bảng.
Link tham khảo https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described

Sử dụng INDEX

- INDEX sẽ chiếm thêm nhiều không gian lưu trữ vì vậy chúng ta cần cân nhắc, không nên tạo quá nhiều INDEX nếu nó không thật sự cần thiết
- INDEX sẽ tự động cập nhật, khi dữ liệu được tạo INDEX có thay đổi. Vì vậy nó sẽ tốn chi phí hệ thống. Nên đối với những dữ liệu cần được thay đổi thường siêng thì cần xem xét trước khi thực hiện INDEX cho chúng
- Các câu truy vấn chỉ hiệu quả với INDEX nếu nó sargable (Search Argument-Able). Nghĩa là DBMS engine có thể sữ dụng INDEX để tăng tốc độ sử lý câu truy vấn. Hãy xem xét hay mệnh đề WHERE sau. WHERE ID = 20000 và WHERE ID + 1 = 20001. Hai câu sẽ cho ra cùng một kết quả những câu sau không thể sử dụng INDEX để tìm kiếm bởiID + 1 cần được tính toán trước khi so sánh. Nếu bạn xem "Include Actual Execution Plan" của câu truy vấn WHERE ID + 1 = 20001 cho bảng TEST và TEST_INDEX, bạn sẽ thấy hiệu suất là tương đương nhau, thậm chí bảng chứa INDEX còn chiếm nhiều hơn. Chính vì vậy, những trường đánh INDEX chỉ nên được sử dụng để so sánh, không nên được tính toán trong mệnh đề WHERE
Các toán tử nên được dùng:

=, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL