[Database][MS-SQL] Tăng tốc độ thực thi câu truy vấn(query) bằng hàm Index.

Index là câu lệnh rất mạnh để tăng hiệu năng thực hiện của câu truy vấn(query). Nói một cách trừu tượng thì một database không sử dụng index thì tốc độ xử lý của nó như là một chiết xe đạp còn nếu sử dụng index thì nó liền biến thành một  chiếc moto phân khối lớn.

Sơn sẽ dùng một ví dụ đơn giản để hướng dẫn sử dụng và mô tả hiêu năng thực thi của câu truy vấn khi sử dụng index.

Trước tiên ta cần tạo 1 database và có 2 bảng dữ liệu là data và data_index:

• data: là database không sử dụng index.
• data_index: là database có sử dụng index

Sau đây là srouce tạo database và 2 bảng(table) cùng với dữ liệu(data):

CREATE DATABASE Index_Test
DROP DATABASE Index_Test
DROP TABLE data
DROP TABLE data_index

CREATE TABLE data
(
P_Id int IDENTITY,
Value varchar(255) NOT NULL,
)

CREATE TABLE data_index
(
P_Id int IDENTITY,
Value varchar(255) NOT NULL,
)

CREATE INDEX Index_DataIndex ON dbo.data_index(P_Id)
CREATE NONCLUSTERED INDEX Index_DataIndex ON dbo.data_index(P_Id)
CREATE UNIQUE NONCLUSTERED INDEX  Index_DataIndex ON dbo.data_index(P_Id)
DROP INDEX Index_DataIndex ON dbo.data_index

DECLARE @intFlag INT
set @intFlag = 1
WHILE (@intFlag < 20000)
BEGIN
    INSERT INTO data (Value) VALUES('DEMO')
    INSERT INTO data_index(Value) VALUES('DEMO')
    PRINT @intFlag
    SET @intFlag = @intFlag + 1
END
-- Câu lệnh này chạy hơi lâu ^^!.

Sau khi có được 2 bảng dữ liệu trên bạn viết 2 câu truy vấn sau:

SELECT * FROM data WHERE P_Id = 100
SELECT * FROM data_index WHERE P_Id = 100

Hai câu lệnh trên sẽ cho bạn cùng một kết quả nhưng khi bạn xem xét trên phương diện thực thi(execution). Đầu tiên bạn hãy chạy câu lệnh thứ nhất và để theo dõi trạng thái thực thi bạn hãy click chọn icon execution trước khi thực thi câu lệnh(SQL Server 2008).

Như vậy ta thấy hệ thống sẽ thực thi câu lệnh bằng cách duyệt qua dữ liệu của bảng (table scan) và tìm ra các bản ghi thỏa mãn yêu cầu tìm kiếm.

Thao tác duyệt bảng có nghĩa là hệ thống cần phải đọc tuần tự từng bản ghi từ đầu đến cuối để tìm ra kết quả. Trong trường hợp này, nó phải đọc toàn bộ 20.000 bản ghi(record) và tìm ra bản ghi có P_Id=1000, đây là một thao tác rất chậm vì nó phải xử lý tất cả các bản ghi(record) trong bảng.

Nên nhớ hệ thống sẽ không dừng lại khi nó tìm được bản ghi đầu tiên có P_Id=1000, vì nó không biết liệu còn bản ghi nào khác có giá trị CustomerID tương tự hay không, cho nên để chắc chắn trả lại kết quả đầy đủ hệ thống vẫn phải tiếp tục đọc các bản ghi còn lại.

Ta có thể nhận xét thấy chi phí của thao tác duyệt bảng tăng tuyến tính cùng với số lượng bản ghi trong bảng (độ phức tạp là O(n)).

Tiếp theo ta hãy chạy thử câu lệnh thứ 2 của bạng table có tạo index.

Như trên hình ta không thấy thao tác table scan nữa, mà thay vào đó là Index Seek và RID lookup.

Index Seek là khi hệ thống có thể nhảy đến được node trên cây index chứa từ khóa thỏa mãn yêu cầu tìm kiếm.

Index là một cấu trúc dữ liệu có dạng B-tree, nên nó rất thích hợp với các thao tác tìm kiếm theo kiểu key=value, chỉ cần vài phép so sánh là hệ thống định vị được node chứa khóa cần tìm.

Node này chứa khóa (trường được index, ở đây là giá trị của P_Id) và RID là ID của bản ghi tương ứng trong bảng (đây là giá trị nội bộ chỉ dùng bên trong hệ thống, ta không truy cập được giá trị này).

Vì thế bước tiếp theo là dùng RID này để nhảy đến bản ghi tương ứng trong bảng (RID lookup) để lấy các trường dữ liệu cần thiết. Với Index Seek, độ phức tạp giảm xuống thành O(log n), một bước tiến vượt bậc so với table scan.

Bây giờ để nhìn một tổng quát hơn về hiệu năng xử lý của index trong 2 bảng, bạn hãy thực thi 2 câu lệnh cùng một lúc.

Ta thấy câu lệnh thứ nhất chiếm tới 96% tổng chi phí, trong khi câu lệnh thứ hai chỉ chiếm có 4%.

Index đã giúp cho lượng dữ liệu hệ thống cần xử lý để tìm ra kết quả giảm xuống đến mức tối thiểu, và điều đó đã tạo ra bước nhảy về tốc độ.

Từ đây ta rút ra một bài học quan trọng: Các trường(column) thường được dùng trong mệnh đề WHERE là các ứng cử viên đầu tiên cần được tạo index.

* Note:  
Một số lưu ý là việc sử dụng index vào database hoàn toàn không phải là một việc đơn giản, nếu database phức tạp và các câu lệnh query xử lý phức tạp thì việc sử dụng index cũng sẽ tăng theo, và áp dụng index là điều không dễ còn để nó nó thể tối ưu càng khó hơn, vì vậy khi áp dụng cần phải phân tích kỹ lương về cấu trúc database cũng như các câu query để xử lý dữ liệu.

Các từ chuyên môn bạn chịu khó lên chú Google để tìm hiểu sơ qua để hiểu nó là gì nha, vì giải thích sẽ rất dài dòng mà mình chưa thực sự hiểu lắm ^^!

Nếu có gì sai xót trong bài viết rất mong ACE hỗ trợ chỉnh lý.^^!