Index trong SQL Server | Comdy

• Trung Nguyen

• 16/05/2020

• 29 min read

Index ( chỉ mục ) trong SQL Server là những cấu trúc tài liệu đặc biệt quan trọng được link với những bảng hoặc view giúp tăng cường truy vấn. SQL Server phân phối 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à khám phá 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 đạt kế hoạch thực thi trong SQL Server Management Studio, bạn hoàn toà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.

Để xử lý yếu tố này, SQL Server cung ứng một cấu trúc chuyên sử dụng để tăng vận tốc truy xuất những 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 tàng trữ những bản ghi tài liệu trong một cấu trúc được sắp xếp dựa trên những giá trị khóa của nó. Mỗi bảng chỉ có một clustered index vì những bản ghi tài liệu chỉ hoàn toàn 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 triển khai tài liệu bằng cách sử dụng một cấu trúc đặc biệt quan trọng được gọi là B-tree ( balanced tree – cây cân đối ) được cho phép tìm kiếm, chèn, update và xóa bản ghi bất kể với thời hạn 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 những trang chỉ mục để lữu trữ những chỉ mục của những bản ghi. Các nút lá chứa những trang dữ liệu ( data pages ) của bảng. Các trang trong mỗi cấp của index được link bằng cấu trúc khác gọi là list link đô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 hóa tạo một clustered index tương ứng dựa trên những 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 triển khai câu lệnh dưới đây, SQL Server duyệt qua chỉ mục ( Tìm kiếm clustered index ) để xác lập vị trí bản ghi, cách này thì nhanh hơn quét hàng loạt 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 tài liệu giúp cải tổ vận tốc truy xuất tài liệu từ những bảng. Không giống như clustered index, non-clustered index sắp xếp và tàng trữ tài liệu riêng không liên quan gì đến nhau với những bản ghi trong bảng. Nó là một bản sao tài liệu của những cột được chọn từ một bảng được link .Tương tự như clustered index, non-clustered index sử dụng cấu trúc cây B-Tree để tổ chức triển khai tài liệu của nó .Một bảng hoàn toàn có thể có một hoặc nhiều non-clustered index và mỗi non-clustered index hoàn toàn có thể gồm có 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 tàng trữ những giá trị khóa index, những nút lá cũng tàng trữ những con trỏ trỏ tới những bản ghi có chứa những giá trị khóa. Những con trỏ bản ghi này còn được gọi là những xác định 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 gồm có nhiều cột, thứ tự của những cột trong chỉ mục là rất quan trọng. Bạn nên đặt những cột mà bạn thường sử dụng để truy vấn tài liệu ở đầu list 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 những cột mà bạn thường sử dụng để truy vấn tài liệu ở đầu list 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 hoàn toàn có thể sử dụng những 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)

Để đổi khác tên của một index bằng SSMS, bạn làm theo những 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ẽ khám phá về unique index trong SQL Server và cách sử dụng chúng để thực thi tính duy nhất của những 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 bảo vệ những 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 hoàn toàn có thể gồm có một hoặc nhiều cột. Nếu một unique index có một cột, những 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ự tích hợp những giá trị trong những cột này là duy nhất .Mọi nỗ lực INSERT hoặc UPDATE dữ liệu vào những 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 hoàn toàn 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ố ít ví dụ về việc sử dụng những 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 hàng loạt clustered index để tìm những bản ghi tương thích .

Để 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 quan trọng. Nó lưu lại cho biết thông tin còn thiếu hoặc không vận dụng .NULL thậm chí còn không bằng chính nó. Tuy nhiên, khi nói đến unique index, SQL Server giải quyết và xử lý những 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ỉ hoàn toàn 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 thi lại truy vấn trên, SQL Server báo lỗi do những 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 link 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 một index trước khi triển khai UPDATE lớn trên bảng. Bằng cách vô hiệu hóa index, bạn hoàn toàn có thể tăng cường quy trình update bằng cách tránh ngân sách ghi / update 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 một index, trình tối ưu hóa truy vấn sẽ không sử dụng index bị vô hiệu đó để tạo những kế hoạch triển khai truy vấn .Khi bạn vô hiệu 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 những non-clustered index. Tuy nhiên, nếu bạn vô hiệu một non-clustered index hoặc clustered index trên một view, SQL Server sẽ xóa tổng thể tài 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ố ít ví dụ về việc vô hiệu những 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 thi 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 clutered index của bảng nên bạn không hề truy vấn tài liệu trong bảng nữa .

SELECT * FROM sales.customers;

Đây là thông tin 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 và khám phá cách sử dụng những câu lệnh khác nhau để kích hoạt một hoặc tổng thể những index bị vô hiệu trong bảng .Đôi khi, bạn cần phải vô hiệu một index trước khi triển khai UPDATE lớn trên bảng. Bằng cách vô hiệu hóa index, bạn hoàn toàn có thể tăng cường quy trình update bằng cách tránh ngân sách ghi / update index .Sau khi hoàn thành xong update vào bảng, bạn cần kích hoạt lại những index. Vì index đã bị vô hiệu, bạn hoàn toàn có thể kiến thiết xây dựng lại index nhưng không hề chỉ kích hoạt nó. Bởi vì sau khi update, index cần được thiết kế xây dựng lại để phản ánh tài 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 và khám phá cách sử dụng những index với những cột được gồm có để cải tổ vậ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 đạt kế hoạch triển khai 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 triển khai :Trong kế hoạch thực thi 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 những bản ghi được tìm thấy trong clustered index bằng những vòng lặp lồng nhau .Như bạn hoàn toàn có thể thấy ngân sách cho việc tra cứu khóa là khoảng chừng 50 % truy vấn, khá tốn kém .Để giúp giảm ngân sách tra cứu khóa này, SQL Server được cho phép bạn lan rộng ra tính năng của một non-clustered index bằng cách gồm có những cột không phải khóa .Bằng cách gồm có những cột không phải khóa trong những non-clustered index, bạn hoàn toàn có thể tạo những 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ề tài liệu được nhu yếu của truy vấn :Một index với những cột được gồm có hoàn toàn có thể cải tổ đáng kể hiệu năng truy vấn vì toàn bộ những cột trong truy vấn đều được gồm có trong index ; Trình tối ưu hóa truy vấn hoàn toàn có thể định vị tất cả những giá trị cột trong index mà không cần truy vấn vào bảng hoặc clustered index dẫn đến ít hoạt động giải trí 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 những cột được gồm có :

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ẽ khám phá cách sử dụng những filtered index trong SQL Server để tạo những non-clustered index được tối ưu hóa cho những 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, hoàn toàn có thể cải tổ đáng kể hiệu năng của những truy vấn. Tuy nhiên, quyền lợi của những non-clustered index có ngân sách : tàng trữ và bảo dưỡng .

• Đầ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 suất cao nếu những ứng dụng chỉ truy vấn một phần những bản ghi của bảng. Đây là đất dụng võ của những filtered index .Một filtered index là một non-clustered index với một biểu thức đượ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à hiệu 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 triển khai ướ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 đó, những filtered index hoàn toàn có thể giúp bạn tiết kiệm chi phí khoảng trống đặc biệt quan trọng là khi giá trị của những cột khóa của index còn rải rác ( có nhiều giá trị NULL ) .Ngoài ra, những filtered index làm giảm ngân sách bảo dưỡng vì chỉ một phần của những bản ghi có tài liệu, không phải toàn bộ, cần được update khi tài liệu trong bảng link đổi khác .

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 và khám phá cách mô phỏng những index dựa trên công dụng trong SQL Server bằng cách sử dụng những index trên những cột được đo lường và thống kê .

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 bộc lộ rõ ràng trong kế hoạch thực thi, trình tối ưu hóa truy vấn cần quét hàng loạt clustered index để xác định người mua, không hiệu suất cao .

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 hoàn toàn có thể sử dụng một index trên một cột được đo lường và thống kê để đạt được hiệu suất cao tựa như của một index dựa trên tính 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 người mua dựa trên những phần đầu của địa chỉ email của họ, bạn sử dụng những 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à khám phá và học cách sử dụng index trong SQL Server để tối ưu vận tốc cho những truy vấn của bạn .

Nếu Comdy hữu ích và giúp bạn tiết kiệm thời gian làm việc

Bạn hoàn toàn có thể sung sướng đưa Comdy vào whitelist của trình chặn quảng cáo ❤ ️ để tương hỗ chúng tôi trong việc trả tiền cho dịch vụ tàng trữ web để duy trì hoạt động giải trí của website .