Index trong SQL Server: Hướng dẫn từ A-Z về Index trong SQL Server

Index (chỉ mục) trong SQL Server là các cấu trúc dữ liệu đặc biệt được liên kết với các bảng hoặc view giúp tăng tốc truy vấn. SQL Server cung cấp hai loại index: clustered index và non-clustered index.

Trong hướng dẫn này, bạn sẽ tìm hiểu mọi thứ bạn cần biết về index trong SQL Server để có một chiến lược tạo index tốt nhằm tối ưu hóa các truy vấn của bạn.

Clustered Index trong SQL Server

Trong phần này, bạn sẽ tìm hiểu về clustered index trong SQL Server và cách định nghĩa clustered index cho bảng.

Giới thiệu về Clustered Index trong SQL Server

Câu lệnh sau đây tạo một bảng mới có tên production.parts bao gồm hai cột part_id và part_name:

CREATE TABLE production.parts(
    part_id   INT NOT NULL, 
    part_name VARCHAR(100)
);

Và câu lệnh này chèn một số bản ghi vào bảng production.parts:

INSERT INTO 
    production.parts(part_id, part_name)
VALUES
    (1,'Frame'),
    (2,'Head Tube'),
    (3,'Handlebar Grip'),
    (4,'Shock Absorber'),
    (5,'Fork');

Bảng production.parts không có khóa chính, do đó SQL Server lưu trữ các bản ghi của nó trong một cấu trúc có thứ tự được gọi là heap (đống).

Khi bạn truy vấn dữ liệu từ bảng production.parts, trình tối ưu hóa truy vấn sẽ quét toàn bộ bảng để xác định vị trí chính xác.

Ví dụ: câu lệnh này tìm bản ghi có id là 5.

SELECT 
    part_id, 
    part_name
FROM 
    production.parts
WHERE 
    part_id = 5;

Nếu bạn xem ước lượng kế hoạch thực hiện trong SQL Server Management Studio, bạn có thể thấy SQL Server đã đưa ra kế hoạch truy vấn như sau:

Lưu ý: để xem ước lượng kế hoạch thực hiện trong SQL Server Management Studio, bạn bấm vào nút Display Estimated Execution Plan hoặc chọn truy vấn và nhấn phím tắt Ctrl+L:

Vì bảng production.parts chỉ có năm bản ghi, nên truy vấn sẽ thực thi rất nhanh. Tuy nhiên, nếu bảng chứa một số lượng bản ghi lớn thì sẽ mất rất nhiều thời gian và tài nguyên để tìm kiếm dữ liệu.

Để giải quyết vấn đề này, SQL Server cung cấp một cấu trúc chuyên dụng để tăng tốc độ truy xuất các bản ghi từ một bảng được gọi là index.

SQL Server có hai loại index là clustered index và non-clustered index.

Một clustered index lưu trữ các bản ghi dữ liệu trong một cấu trúc được sắp xếp dựa trên các giá trị khóa của nó. Mỗi bảng chỉ có một clustered index vì các bản ghi dữ liệu chỉ có thể được sắp xếp theo một thứ tự. Bảng có clustered index được gọi là clustered table.

Hình ảnh sau đây minh họa cấu trúc của một clustered index:

Một clustered index tổ chức dữ liệu bằng cách sử dụng một cấu trúc đặc biệt được gọi là B-tree (balanced tree – cây cân bằng) cho phép tìm kiếm, chèn, cập nhật và xóa bản ghi bất kỳ với thời gian như nhau.

Trong cấu trúc này, nút trên cùng của B-tree được gọi là nút gốc (root node). Các nút ở cấp độ dưới cùng được gọi là các nút lá (leaf nodes). Bất kỳ nút nào ở giữa các nút gốc và nút lá được gọi là nút trung gian.

Trong B-tree, nút gốc và nút trung gian chứa các trang chỉ mục để lữu trữ các chỉ mục của các bản ghi. Các nút lá chứa các trang dữ liệu (data pages) của bảng. Các trang trong mỗi cấp của index được liên kết bằng cấu trúc khác gọi là danh sách liên kết đôi.

Clustered Index và khóa chính trong SQL Server

Khi bạn tạo bảng có khóa chính, SQL Server sẽ tự động tạo một clustered index tương ứng dựa trên các cột có trong khóa chính.

Câu lệnh này tạo một bảng mới tên là production.part_prices có khóa chính bao gồm hai cột là: part_id và valid_from.

CREATE TABLE production.part_prices(
    part_id int,
    valid_from date,
    price decimal(18,4) not null,
    PRIMARY KEY(part_id, valid_from) 
);

Như bạn thấy trong hình trên, SQL Server đã tự động tạo một clustered index có tên là PK__part_pri_xxxx cho bảng production.part_prices.

Nếu bạn thêm khóa chính vào một bảng đã có một clustered index, SQL Server sẽ bắt buộc khóa chính sử dụng một non-clustered index. Câu lệnh này định nghĩa khóa chính cho bảng production.parts:

ALTER TABLE production.parts
ADD PRIMARY KEY(part_id);

Tạo Clustered Index trong SQL Server

Trong trường hợp một bảng không có khóa chính (điều này rất hiếm) bạn có thể sử dụng câu lệnh CREATE CLUSTERED INDEX để định nghĩa một clustered index cho bảng.

Câu lệnh sau đây tạo một clustered index cho bảng production.parts:

CREATE CLUSTERED INDEX ix_parts_id
ON production.parts (part_id);  

Nếu bạn mở nút Indexes dưới tên bảng, bạn sẽ thấy tên chỉ mục mới ix_parts_id với kiểu Clustered.

Khi thực hiện câu lệnh dưới đây, SQL Server duyệt qua chỉ mục (Tìm kiếm clustered index) để xác định vị trí bản ghi, cách này thì nhanh hơn quét toàn bộ bảng.

SELECT 
    part_id, 
    part_name
FROM 
    production.parts
WHERE 
    part_id = 5;

Cú pháp tạo clustered index trong SQL Server

Cú pháp tạo clustered index trong SQL Server như sau:

CREATE CLUSTERED INDEX index_name
ON schema_name.table_name (column_list);

Trong cú pháp này:

• Đầu tiên, bạn sử dụng mệnh đề CREATE CLUSTERED INDEX để tạo clustered index.
• Thứ hai, chỉ định tên của clustered index sau mệnh đề CREATE CLUSTERED INDEX.
• Thứ ba, chỉ định lược đồ và tên bảng mà bạn muốn tạo index.
• Cuối cùng, liệt kê một hoặc nhiều cột có trong index.

Non-clustered index trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng câu lệnh SQL Server CREATE INDEX để tạo các non-clustered index cho các bảng.

Giới thiệu về non-clustered index trong SQL Server

Non-clustered index là một cấu trúc dữ liệu giúp cải thiện tốc độ truy xuất dữ liệu từ các bảng. Không giống như clustered index, non-clustered index sắp xếp và lưu trữ dữ liệu riêng biệt với các bản ghi trong bảng. Nó là một bản sao dữ liệu của các cột được chọn từ một bảng được liên kết.

Tương tự như clustered index, non-clustered index sử dụng cấu trúc cây B-Tree để tổ chức dữ liệu của nó.

Một bảng có thể có một hoặc nhiều non-clustered index và mỗi non-clustered index có thể bao gồm một hoặc nhiều cột của bảng.

Hình ảnh sau đây minh họa cấu trúc non-clustered index:

Bên cạnh việc lưu trữ các giá trị khóa index, các nút lá cũng lưu trữ các con trỏ trỏ tới các bản ghi có chứa các giá trị khóa. Những con trỏ bản ghi này còn được gọi là các định vị hàng (row locators).

Nếu bảng là một clustered table (bảng có clustered index), con trỏ bản ghi là khóa của clustered index. Trong trường hợp bảng không có clustered index, con trỏ bản ghi trỏ đến bản ghi của bảng.

Tạo non-clustered index trong SQL Server

Để tạo một non-clustered index trong SQL Server, bạn sử dụng câu lệnh CREATE INDEX:

CREATE [NONCLUSTERED] INDEX index_name
ON table_name(column_list);

Trong cú pháp này:

• Đầu tiên, chỉ định tên của index sau mệnh đề CREATE NONCLUSTERED INDEX. Lưu ý rằng từ khóa NONCLUSTERED là tùy chọn.
• Thứ hai, chỉ định tên bảng mà bạn muốn tạo index và danh sách các cột của bảng đó làm cột khóa index.

Ví dụ về non-clustered index trong SQL Server

Chúng tôi sẽ sử dụng bảng sales.customers từ cơ sở dữ liệu mẫu để minh họa.

Bảng sales.customers là một clustered table bởi vì nó có một khóa chính customer_id.

Tạo non-clustered index cho một cột trong SQL Server

Câu lệnh sau tìm kiếm những khách hàng có địa chỉ ở thành phố Atwater:

SELECT 
    customer_id, 
    city
FROM 
    sales.customers
WHERE 
    city = 'Atwater';

Nếu bạn xem ước lượng kế hoạch thực thi, bạn sẽ thấy trình tối ưu hóa truy vấn quét clustered index để tìm các bản ghi. Điều này là do bảng sales.customers không có index cho cột city.

Để cải thiện tốc độ của truy vấn này, bạn có thể tạo một non-clustered index cho cột city như sau:

CREATE INDEX ix_customers_city
ON sales.customers(city);

Bây giờ, nếu bạn xem lại ước lượng kế hoạch thực thi của truy vấn trên, bạn sẽ thấy rằng trình tối ưu hóa truy vấn sử dụng non-clustered index ix_customers_city như sau:

Tạo non-clustered index cho nhiều cột trong SQL Server

Câu lệnh sau đây tìm kiếm khách hàng có họ là Berg và tên là Monika:

SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    sales.customers
WHERE 
    last_name = 'Berg' AND 
    first_name = 'Monika';

Trình tối ưu hóa truy vấn quét clustered index để tìm kiếm khách hàng có họ là Berg và tên là Monika.

Để tăng tốc độ truy xuất dữ liệu, bạn có thể tạo một non-clustered index bao gồm cả hai cột last_name và first_name như sau:

CREATE INDEX ix_customers_name 
ON sales.customers(last_name, first_name);

Bây giờ, trình tối ưu hóa truy vấn sẽ sử dụng chỉ mục ix_customers_name để tìm kiếm khách hàng.

SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    sales.customers
WHERE 
    last_name = 'Berg' AND 
    first_name = 'Monika';

Khi bạn tạo một non-clustered index bao gồm nhiều cột, thứ tự của các cột trong chỉ mục là rất quan trọng. Bạn nên đặt các cột mà bạn thường sử dụng để truy vấn dữ liệu ở đầu danh sách cột.

Ví dụ: câu lệnh sau đây tìm kiếm khách hàng có họ Albert. Vì cột last_name là cột đầu tiên trong index, trình tối ưu hóa truy vấn có thể tận dụng index và sử dụng phương thức index seek để tìm kiếm:

SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    sales.customers
WHERE 
    last_name = 'Albert';

Câu lệnh sau đây tìm kiếm khách hàng có tên là Adam. Nó cũng tận dụng index ix_customer_name nhưng nó cần quét toàn bộ index (index scan) để tìm kiếm, chậm hơn so với sử dụng phương thức index seek để tìm kiếm.

SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    sales.customers
WHERE 
    first_name = 'Adam';

Do đó, cách tốt nhất là đặt các cột mà bạn thường sử dụng để truy vấn dữ liệu ở đầu danh sách cột của index.

Đổi tên index trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách đổi tên index bằng cách sử dụng stored procedure hệ thống sp_rename và SQL Server Management Studio.

Đổi tên index bằng cách sử dụng stored procedure sp_rename

sp_renamelà một stored procedure hệ thống cho phép bạn đổi tên bất kỳ đối tượng nào do người dùng tạo trong cơ sở dữ liệu hiện tại bao gồm bảng, index và cột.

Câu lệnh đổi tên một index như sau:

EXEC sp_rename 
    index_name, 
    new_index_name, 
    N'INDEX';

Hoặc bạn có thể sử dụng các tham số rõ ràng như sau:

EXEC sp_rename 
    @objname = N'index_name', 
    @newname = N'new_index_name',   
    @objtype = N'INDEX';

Ví dụ: câu lệnh sau đổi tên index ix_customers_city của bảng sales.customers thành ix_cust_city:

EXEC sp_rename 
    @objname = N'sales.customers.ix_customers_city',
    @newname = N'ix_cust_city' ,
    @objtype = N'INDEX';

Hoặc sử dụng lệnh ngắn gọn sau:

EXEC sp_rename 
    N'sales.customers.ix_customers_city',
    N'ix_cust_city' ,
    N'INDEX';

Đổi tên index bằng SQL Server Management Studio (SSMS)

Để thay đổi tên của một index bằng SSMS, bạn làm theo các bước sau:

• Đầu tiên, di chuyển đến cơ sở dữ liệu, nhấp vào bảng và chọn nút Indexes.
• Tiếp theo, nhấp chuột phải vào index mà bạn muốn đổi tên và chọn menu Rename.
• Cuối cùng nhập tên mới của index và nhấn enter.

Trong hình dưới đây, chúng tôi sẽ đổi tên index ix_customers_name của bảng sales.customers:

Hình ảnh sau đây cho thấy index ix_customers_name đã đổi tên thành ix_cust_fullname:

Unique Index trong SQL Server

Trong phần này, bạn sẽ tìm hiểu về unique index trong SQL Server và cách sử dụng chúng để thực thi tính duy nhất của các giá trị trong một hoặc nhiều cột của bảng.

Tổng quan về unique index trong SQL Server

Unique index đảm bảo các cột khóa của chỉ mục không chứa bất kỳ giá trị trùng lặp nào.

Một unique index có thể bao gồm một hoặc nhiều cột. Nếu một unique index có một cột, các giá trị trong cột này sẽ là duy nhất. Trong trường hợp unique index có nhiều cột, sự kết hợp các giá trị trong các cột này là duy nhất.

Mọi nỗ lực INSERT hoặc UPDATE dữ liệu vào các cột khóa của unique index gây ra trùng lặp sẽ dẫn đến lỗi.

Một unique index có thể là clustered index hoặc non-clustered index.

Để tạo một unique index, bạn sử dụng câu lệnh CREATE UNIQUE INDEX như sau:

CREATE UNIQUE INDEX index_name
ON table_name(column_list);

Trong cú pháp này:

• Đầu tiên, chỉ định tên của unique index sau mệnh đề CREATE UNIQUE INDEX.
• Sau đó chỉ định tên của bảng mà index được liên kết và danh sách các cột sẽ được bao gồm trong index.

Ví dụ về unique index trong SQL Server

Hãy lấy một số ví dụ về việc sử dụng các unique index.

Tạo unique index cho một cột trong SQL Server

Truy vấn sau đây tìm kiếm khách hàng có email '[email protected]':

SELECT
    customer_id, 
    email 
FROM
    sales.customers
WHERE 
    email = '[email protected]';

Trình tối ưu hóa truy vấn phải quét toàn bộ clustered index để tìm các bản ghi phù hợp.

Để tăng tốc độ truy xuất truy vấn, bạn có thể thêm một non-clustered index vào cột email.

Tuy nhiên, với giả định rằng mỗi khách hàng sẽ có một email duy nhất, bạn có thể tạo một unique index cho cột email.

Vì bảng sales.customers đã có dữ liệu, trước tiên bạn cần kiểm tra các giá trị trùng lặp trong cột email:

SELECT 
    email, 
    COUNT(email)
FROM 
    sales.customers
GROUP BY 
    email
HAVING 
    COUNT(email) > 1;

Truy vấn trả về một tập kết quả trống. Nó có nghĩa là không có giá trị trùng lặp trong cột email.

Do đó, bạn có thể tạo một unique index cho cột email của bảng sales.customers:

CREATE UNIQUE INDEX ix_cust_email 
ON sales.customers(email);

Từ giờ trở đi, trình tối ưu hóa truy vấn sẽ tận dụng index ix_cust_email và sử dụng phương thức index seek để tìm kiếm khách hàng theo email.

Tạo unique index cho nhiều cột trong SQL Server

Đầu tiên, chúng ta sẽ tạo một bảng có tên t1 có hai cột để minh họa cho ví dụ này:

CREATE TABLE t1 (
    a INT, 
    b INT
);

Tiếp theo, tạo một unique index bao gồm cả hai cột a và b:

CREATE UNIQUE INDEX ix_uniq_ab 
ON t1(a, b);

Tiến hành INSERT một bản ghi mới vào bảng t1:

INSERT INTO t1(a,b) 
VALUES(1,1);

Sau đó, INSERT thêm một bản ghi khác vào bảng t1. Lưu ý rằng giá trị 1 được lặp lại trong cột a, nhưng sự kết hợp của các giá trị trong cột a và b không trùng lặp:

INSERT INTO t1(a,b) 
VALUES(1,2);

Cuối cùng, chèn một bản ghi đã tồn tại vào bảng t1:

INSERT INTO t1(a,b) 
VALUES(1,2);

Máy chủ SQL gặp lỗi :

Cannot insert duplicate key row in object 'dbo.t1' with unique index 'ix_ab'. The duplicate key value is (1, 2).

Unique index và NULL trong SQL Server

NULL thật đặc biệt. Nó đánh dấu cho biết thông tin còn thiếu hoặc không áp dụng.

NULL thậm chí không bằng chính nó. Tuy nhiên, khi nói đến unique index, SQL Server xử lý các giá trị NULL như nhau. Điều đó có nghĩa là nếu bạn tạo một unique index trên một cột NULL, bạn chỉ có thể có một giá trị NULL trong cột này.

Các câu lệnh sau đây tạo một bảng mới có tên t2 và định nghĩa một unique index trên cột a:

CREATE TABLE t2(
    a INT
);

CREATE UNIQUE INDEX a_uniq_t2
ON t2(a);

Truy vấn này chèn giá trị NULL vào cột a của bảng t2:

INSERT INTO t2(a) 
VALUES(NULL);

Tuy nhiên, khi thực hiện lại truy vấn trên, SQL Server báo lỗi do các giá trị NULL trùng lặp:

INSERT INTO t2(a) 
VALUES(NULL);

Unique index so với UNIQUE constraint

Cả unique index và UNIQUE constraint (ràng buộc duy nhất) đều thực thi tính duy nhất của các giá trị trong một hoặc nhiều cột. SQL Server xác thực sự trùng lặp theo cùng một cách cho cả unique index và unique constraint.

Khi bạn tạo một unique constraint, SQL Server sẽ tạo một unique index liên kết với unique constrain này.

Tuy nhiên, việc tạo một unique constraint trên các cột làm cho mục tiêu của unique index rõ ràng.

Vô hiệu hóa index trong SQL Server

Đôi khi, bạn cần phải vô hiệu hóa một index trước khi thực hiện UPDATE lớn trên bảng. Bằng cách vô hiệu hóa index, bạn có thể tăng tốc quá trình cập nhật bằng cách tránh chi phí ghi/cập nhật index.

Trong phần này, bạn sẽ học cách sử dụng câu lệnh ALTER TABLE để vô hiệu hóa các index của bảng.

Cú pháp vô hiệu hóa index trong SQL Server

Để vô hiệu hóa một index, bạn sử dụng câu lệnh ALTER INDEX như sau:

ALTER INDEX index_name
ON table_name
DISABLE;

Để vô hiệu hóa tất cả các index của bảng, bạn sử dụng mẫu câu lệnh ALTER INDEX ALL như sau:

ALTER INDEX ALL ON table_name
DISABLE;

Nếu bạn vô hiệu hóa một index, trình tối ưu hóa truy vấn sẽ không sử dụng index bị vô hiệu hóa đó để tạo các kế hoạch thực hiện truy vấn.

Khi bạn vô hiệu hóa một index trên một bảng, SQL Server sẽ giữ định nghĩa chỉ mục trong siêu dữ liệu và thống kê index trong các non-clustered index. Tuy nhiên, nếu bạn vô hiệu hóa một non-clustered index hoặc clustered index trên một view, SQL Server sẽ xóa tất cả dữ liệu index.

Nếu bạn vô hiệu hóa một clustered index của một bảng, bạn không thể truy cập vào dữ liệu bảng sử dụng ngôn ngữ thao tác dữ liệu như SELECT, INSERT, UPDATE và DELETE cho đến khi bạn xây dựng lại hoặc xóa clustered index.

Ví dụ vô hiệu hóa index trong SQL Server

Hãy lấy một số ví dụ về việc vô hiệu hóa các index để hiểu rõ hơn.

Vô hiệu hóa một index trong SQL Server

Ví dụ này sử dụng ALTER INDEX để vô hiệu hóa index ix_cust_city trên bảng sales.customers:

ALTER INDEX ix_cust_city 
ON sales.customers 
DISABLE;

Do đó, truy vấn sau đây tìm kiếm các khách hàng ở thành phố San Jose không thể sử dụng index đã bị vô hiệu hóa:

SELECT    
    first_name, 
    last_name, 
    city
FROM    
    sales.customers
WHERE 
    city = 'San Jose';

Dưới đây là kế hoạch thực hiện truy vấn ước tính:

Vô hiệu hóa tất cả index trong SQL Server

Câu lệnh này vô hiệu hóa tất cả các index của bảng sales.customers:

ALTER INDEX ALL ON sales.customers
DISABLE;

Do đã vô hiệu hóa clutered index của bảng nên bạn không thể truy cập dữ liệu trong bảng nữa.

SELECT * FROM sales.customers;

Đây là thông báo lỗi:

The query processor is unable to produce a plan because the index 'PK__customer__CD65CB855363011F' on table or view 'customers' is disabled.

Kích hoạt index trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng các câu lệnh khác nhau để kích hoạt một hoặc tất cả các index bị vô hiệu hóa trong bảng.

Đôi khi, bạn cần phải vô hiệu hóa một index trước khi thực hiện UPDATE lớn trên bảng. Bằng cách vô hiệu hóa index, bạn có thể tăng tốc quá trình cập nhật bằng cách tránh chi phí ghi/cập nhật index.

Sau khi hoàn thành cập nhật vào bảng, bạn cần kích hoạt lại các index. Vì index đã bị vô hiệu hóa, bạn có thể xây dựng lại index nhưng không thể chỉ kích hoạt nó. Bởi vì sau khi cập nhật, index cần được xây dựng lại để phản ánh dữ liệu mới trong bảng.

Trong SQL Server, bạn có thể xây dựng lại một chỉ mục bằng cách sử dụng lệnh ALTER INDEX hoặc lệnh DBCC DBREINDEX.

Kích hoạt index bằng câu lệnh ALTER INDEX và CREATE INDEX trong SQL Server

Câu lệnh này sử dụng lệnh ALTER INDEX để kích hoạt hoặc xây dựng lại một index trên bảng:

ALTER INDEX index_name 
ON table_name  
REBUILD;

Câu lệnh này sử dụng lệnh CREATE INDEX để xóa index bị vô hiệu hóa và tạo lại nó:

CREATE INDEX index_name 
ON table_name(column_list)
WITH(DROP_EXISTING=ON)

Câu lệnh sau sử dụng lệnh ALTER INDEX để kích hoạt tất cả các index bị vô hiệu hóa trên một bảng:

ALTER INDEX ALL ON table_name
REBUILD;

Kích hoạt index bằng câu lệnh DBCC DBREINDEX trong SQL Server

Câu lệnh sau đây sử dụng lệnh DBCC DBREINDEX để kích hoạt một index trên bảng:

DBCC DBREINDEX (table_name, index_name);

Câu lệnh sau đây sử dụng lệnh DBCC DBREINDEX để kích hoạt tất cả các index trên một bảng:

DBCC DBREINDEX (table_name, " ");

Ví dụ kích hoạt index trong SQL Server

Ví dụ sau sử dụng lệnh ALTER INDEX để kích hoạt tất cả các chỉ mục trên bảng sales.customers (đã bị vô hiệu hóa ở phần trước) từ cơ sở dữ liệu mẫu:

ALTER INDEX ALL ON sales.customers
REBUILD;

Xóa index trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng câu lệnh DROP INDEX trong SQL Server để xóa index.

Tổng quan về câu lệnh DROP INDEX trong SQL Server

Câu lệnh DROP INDEX xóa một hoặc nhiều index khỏi cơ sở dữ liệu hiện tại. Đây là cú pháp của câu lệnh DROP INDEX:

DROP INDEX [IF EXISTS] index_name
ON table_name;

Trong cú pháp này:

• Đầu tiên, chỉ định tên của index mà bạn muốn xóa sau mệnh đề DROP INDEX.
• Thứ hai, chỉ định tên của bảng chứa index.

Xóa một index không tồn tại sẽ dẫn đến một lỗi. Tuy nhiên, bạn có thể sử dụng tùy chọn IF EXISTS để xóa index một cách có điều kiện và tránh lỗi.

Lưu ý: tùy chọn IF EXISTS đã có sẵn kể từ SQL Server 2016 (13.x).

Câu lệnh DROP INDEX không thể xóa các index được tạo bởi PRIMARY KEY hoặc các ràng buộc UNIQUE. Để xóa các index liên quan đến các ràng buộc này, bạn sử dụng câu lệnh ALTER TABLE DROP CONSTRAINT.

Để xóa nhiều index khỏi một hoặc nhiều bảng cùng một lúc, bạn chỉ định danh sách tên index được phân tách bằng dấu phẩy với tên bảng tương ứng sau mệnh đề DROP INDEX như trong truy vấn sau:

DROP INDEX [IF EXISTS] 
    index_name1 ON table_name1,
    index_name2 ON table_name2,
    ...;

Ví dụ câu lệnh DROP INDEX trong SQL Server

Chúng tôi sẽ sử dụng bảng sales.customers từ cơ sở dữ liệu mẫu để minh họa cho câu lệnh DROP INDEX trong SQL Server.

Hình ảnh sau đây cho thấy các index của bảng sales.customers:

Sử dụng DROP INDEX để xóa một index trong SQL Server

Câu lệnh này sử dụng lệnh DROP INDEX để xóa index ix_cust_email khỏi bảng sales.customers:

DROP INDEX IF EXISTS ix_cust_email
ON sales.customers;

Nếu bạn kiểm tra các index của bảng sales.customers, bạn sẽ thấy rằng index ix_cust_email đã bị xóa.

Sử dụng DROP INDEX để xóa nhiều index trong SQL Server

Ví dụ sau sử dụng lệnh DROP INDEX để xóa các index ix_cust_city, ix_cust_fullname khỏi bảng sales.customers:

DROP INDEX 
    ix_cust_city ON sales.customers,
    ix_cust_fullname ON sales.customers;

Bảng sales.customers sau lệnh xóa index không còn non-clustered index:

Index với các cột được bao gồm trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng các index với các cột được bao gồm để cải thiện tốc độ truy vấn.

Giới thiệu về index với các cột được bao gồm trong SQL Server

Chúng tôi sẽ sử dụng bảng sales.customers từ cơ sở dữ liệu mẫu để minh họa cho index bao gồm nhiều cột trong SQL Server.

Câu lệnh sau đây tạo index cho cột email:

CREATE UNIQUE INDEX ix_cust_email 
ON sales.customers(email);

Câu lệnh này tìm kiếm khách hàng có email là '[email protected]':

SELECT    
    customer_id, 
    email
FROM    
    sales.customers
WHERE 
    email = '[email protected]';

Nếu bạn xem ước lượng kế hoạch thực hiện cho truy vấn trên, bạn sẽ thấy rằng trình tối ưu hóa truy vấn sử dụng thao tác index seek trên non-clustered index.

Tuy nhiên, hãy xem xét ví dụ sau:

SELECT    
    first_name,
    last_name, 
    email
FROM    
    sales.customers
WHERE email = '[email protected]';

Đây là kế hoạch thực hiện:

Trong kế hoạch thực hiện này:

Đầu tiên, trình tối ưu hóa truy vấn sử dụng index seek trên non-clustered index ix_cust_email để tìm email và customer_id.

Thứ hai, trình tối ưu hóa truy vấn sử dụng tra cứu khóa (key lookup) trên clustered index của bảng sales.customers để tìm tên và họ của khách hàng theo id khách hàng.

Thứ ba, mỗi bản ghi được tìm thấy trong non-clustered index sẽ khớp với các bản ghi được tìm thấy trong clustered index bằng các vòng lặp lồng nhau.

Như bạn có thể thấy chi phí cho việc tra cứu khóa là khoảng 50% truy vấn, khá tốn kém.

Để giúp giảm chi phí tra cứu khóa này, SQL Server cho phép bạn mở rộng chức năng của một non-clustered index bằng cách bao gồm các cột không phải khóa.

Bằng cách bao gồm các cột không phải khóa trong các non-clustered index, bạn có thể tạo các non-clustered index bao trùm cho nhiều truy vấn hơn.

Lưu ý: khi một index chứa tất cả các cột được tham chiếu bởi một truy vấn, chỉ mục thường được xem là bao trùm truy vấn.

Đầu tiên, chúng ta xóa index ix_cust_email trong bảng sales.customers:

DROP INDEX ix_cust_email 
ON sales.customers;

Sau đó, tạo một index mới là ix_cust_email_inc bao gồm hai cột tên và họ như sau:

CREATE UNIQUE INDEX ix_cust_email_inc
ON sales.customers(email)
INCLUDE(first_name,last_name);

Bây giờ, trình tối ưu hóa truy vấn sẽ chỉ sử dụng non-clustered index để trả về dữ liệu được yêu cầu của truy vấn:

Một index với các cột được bao gồm có thể cải thiện đáng kể hiệu năng truy vấn vì tất cả các cột trong truy vấn đều được bao gồm trong index; Trình tối ưu hóa truy vấn có thể định vị tất cả các giá trị cột trong index mà không cần truy cập vào bảng hoặc clustered index dẫn đến ít hoạt động I/O trên đĩa hơn.

Cú pháp tạo index với các cột được bao gồm trong SQL Server

Dưới đây minh họa cú pháp để tạo một non-clustered index với các cột được bao gồm:

CREATE [UNIQUE] INDEX index_name
ON table_name(key_column_list)
INCLUDE(included_column_list);

Trong cú pháp này:

• Đầu tiên, chỉ định tên của index sau mệnh đề CREATE INDEX. Nếu index là duy nhất, bạn cần thêm từ khóa UNIQUE.
• Thứ hai, chỉ định tên của bảng và danh sách danh sách cột chính cho index sau mệnh đề ON.
• Thứ ba, liệt kê một danh sách các cột được bao gồm bằng dấu phẩy trong mệnh đề INCLUDE.

Filtered index trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng các filtered index trong SQL Server để tạo các non-clustered index được tối ưu hóa cho các bảng.

Giới thiệu về filtered index trong SQL Server

Một non-clustered index, khi được sử dụng đúng cách, có thể cải thiện đáng kể hiệu năng của các truy vấn. Tuy nhiên, lợi ích của các non-clustered index có chi phí: lưu trữ và bảo trì.

• Đầu tiên, nó cần bộ nhớ bổ sung để lưu trữ bản sao dữ liệu của các cột khóa của index.
• Thứ hai, khi bạn INSERT, UPDATE hoặc DELETE các bản ghi khỏi bảng, SQL Server cần cập nhật non-clustered index được liên kết.

Nó sẽ không hiệu quả nếu các ứng dụng chỉ truy vấn một phần các bản ghi của bảng. Đây là đất dụng võ của các filtered index.

Một filtered index là một non-clustered index với một biểu thức cho phép bạn chỉ định những bản ghi nào sẽ được thêm vào index.

Cú pháp sau minh họa cách tạo filtered index:

CREATE INDEX index_name
ON table_name(column_list)
WHERE predicate;

Trong cú pháp này:

• Đầu tiên, chỉ định tên của filtered index sau lệnh CREATE INDEX.
• Thứ hai, liệt kê tên bảng với danh sách các cột sẽ được bao gồm trong index.
• Thứ ba, sử dụng mệnh đề WHERE với một biểu thức để chỉ định các bản ghi của bảng sẽ được đưa vào index.

Ví dụ về filtered index trong SQL Server

Chúng tôi sẽ sử dụng bảng sales.customers từ cơ sở dữ liệu mẫu để minh họa cho câu lệnh filtered index trong SQL Server.

Bảng sales.customers có cột phone, trong đó có nhiều giá trị NULL:

SELECT 
    SUM(CASE
        WHEN phone IS NULL
        THEN 1
        ELSE 0
    END) AS [Has Phone], 
    SUM(CASE
        WHEN phone IS NULL
        THEN 0
        ELSE 1
    END) AS [No Phone]
FROM 
    sales.customers;

Đây là kết quả:

Has Phone   No Phone
----------- -----------
1267        178

Cột phone là một ứng cử viên tốt cho filtered index.

Câu lệnh này tạo ra một filtered index cho cột phone của bảng sales.customers:

CREATE INDEX ix_cust_phone
ON sales.customers(phone)
WHERE phone IS NOT NULL;

Truy vấn sau đây tìm kiếm khách hàng có số điện thoại là (281) 363-3309:

SELECT    
    first_name,
    last_name, 
    phone
FROM    
    sales.customers
WHERE phone = '(281) 363-3309';

Dưới đây là kế hoạch thực hiện ước tính:

Trình tối ưu hóa truy vấn có thể tận dụng filtered index ix_cust_phone để tìm kiếm.

Lưu ý rằng để cải thiện tra cứu khóa, bạn có thể sử dụng một index với các cột được bao gồm (được trình bày ở phần trước trong bài viết này) để bao gồm cả hai cột first_name và last_name:

CREATE INDEX ix_cust_phone
ON sales.customers(phone)
INCLUDE (first_name, last_name)
WHERE phone IS NOT NULL;

Các lợi ích của filtered index trong SQL Server

Như đã đề cập trước đó, các filtered index có thể giúp bạn tiết kiệm không gian đặc biệt là khi giá trị của các cột khóa của index còn rải rác (có nhiều giá trị NULL).

Ngoài ra, các filtered index làm giảm chi phí bảo trì vì chỉ một phần của các bản ghi có dữ liệu, không phải tất cả, cần được cập nhật khi dữ liệu trong bảng liên kết thay đổi.

Tạo index trên các cột được tính toán trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách mô phỏng các index dựa trên chức năng trong SQL Server bằng cách sử dụng các index trên các cột được tính toán.

Giới thiệu về index trên các cột được tính toán

Xem bảng sales.customers từ cơ sở dữ liệu mẫu:

Truy vấn này tìm kiếm khách hàng có phần đầu (phần trước @) của địa chỉ email là 'garry.espinoza';

SELECT    
    first_name,
    last_name,
    email
FROM    
    sales.customers
WHERE 
    SUBSTRING(email, 0, 
        CHARINDEX('@', email, 0)
    ) = 'garry.espinoza';

Dưới đây là kế hoạch thực thi của truy vấn:

Như được thể hiện rõ ràng trong kế hoạch thực thi, trình tối ưu hóa truy vấn cần quét toàn bộ clustered index để định vị khách hàng, không hiệu quả.

Nếu bạn đã làm việc với Oracle hoặc PostgreSQL, bạn có thể biết rằng Oracle hỗ trợ các index dựa trên chức năng và PostgreSQL có các index dựa trên biểu thức. Các loại index này cho phép bạn lập index cho kết quả của hàm hoặc biểu thức sẽ cải thiện hiệu năng của các truy vấn có mệnh đề WHERE chứa hàm và biểu thức.

Trong SQL Server, bạn có thể sử dụng một index trên một cột được tính toán để đạt được hiệu quả tương tự của một index dựa trên chức năng:

• Đầu tiên, tạo một cột được tính toán dựa trên biểu thức trên mệnh đề WHERE.
• Thứ hai, tạo một non-clustered index cho cột được tính toán.

Ví dụ: để tìm kiếm khách hàng dựa trên các phần đầu của địa chỉ email của họ, bạn sử dụng các bước sau:

Đầu tiên, thêm một cột được tính toán vào bảng sales.customers:

ALTER TABLE sales.customers
ADD 
    email_local_part AS 
        SUBSTRING(email, 
            0, 
            CHARINDEX('@', email, 0)
        );

Sau đó, tạo một index trên cột email_local_part:

CREATE INDEX ix_cust_email_local_part
ON sales.customers(email_local_part);

Bây giờ, bạn có thể sử dụng cột email_local_part thay vì biểu thức trong mệnh đề WHERE để tìm khách hàng theo phần đầu của địa chỉ email:

SELECT    
    first_name,
    last_name,
    email
FROM    
    sales.customers
WHERE 
    email_local_part = 'garry.espinoza';

Trình tối ưu hóa truy vấn sử dụng thao tác tìm kiếm index seek trên index ix_cust_email_local_part như trong hình sau:

Yêu cầu đối với index trên các cột được tính toán

Để tạo một index trên một cột được tính toán, các yêu cầu sau phải được đáp ứng:

• Các hàm liên quan đến biểu thức cột được tính toán phải có cùng chủ sở hữu với bảng.
• Biểu thức cột được tính toán phải có tính xác định. Điều đó có nghĩa là biểu thức luôn trả về cùng một kết quả cho một tập hợp đầu vào đã cho.
• Cột được tính toán phải chính xác, có nghĩa là biểu thức của nó không được chứa bất kỳ kiểu dữ liệu FLOAT hoặc REAL nào.
• Kết quả của biểu thức cột được tính toán không thể là kiểu dữ liệu không thể đánh giá như TEXT, NTEXT hoặc IMAGE.
• Các tùy chọn ANSI_NULLS phải được thiết lập ON để khi cột được tính toán được định nghĩa bằng cách sử dụng lệnh CREATE TABLE hoặc ALTER TABLE. Bên cạnh đó, các tùy chọn ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, và CONCAT_NULL_YIELDS_NULL cũng phải được thiết lập là ON và NUMERIC_ROUNDABORT phải được thiết lập là OFF.

Trong hướng dẫn này, bạn đã tìm hiểu và học cách sử dụng index trong SQL Server để tối ưu tốc độ cho các truy vấn của bạn.