CREATE INDEX – Lệnh tạo chỉ mục
Nội dung
1. Mô tả
- Lệnh CREATE INDEX dùng để tạo chỉ mục (Index) trong bảng (Table).
- Ưu điểm Index: được sử dụng để truy vấn (SELECT) dữ liệu nhanh hơn.
- Nhược điểm Index: làm chậm quá trình cập nhật dữ liệu đầu vào từ các lệnh INSERT/ UPDATE/ DELETE. Vì vậy, chỉ nên tạo các Index trên các cột sẽ được tìm kiếm thường xuyên.
Phân loại Index:
1.1 Clustered Index
- Khi tạo khóa chính (primary key) trên bảng thì Clustered Index sẽ được tự động tạo ra, mỗi bảng chỉ có thể có tối đa một Clustered Index.
1.2 Non-Clustered Index
- Trong một bảng có thể chứa tối đa 249 Non-Clustered Index.
Các kiểu Index: B-Tree Index, Hash Index…
2. Cú pháp
2.1 Clustered index
--1
CREATE CLUSTERED INDEX Tên_chỉ_mục
ON Tên_bảng(Tên_cột_1, Tên_cột_2,...)
--2
CREATE UNIQUE CLUSTERED INDEX Tên_chỉ_mục
ON Tên_bảng(Tên_cột_1, Tên_cột_2,...)
2.2 Non-Clustered Index
--1
CREATE INDEX Tên_chỉ_mục
ON Tên_bảng(Tên_cột_1, Tên_cột_2,...)
--Hoặc
CREATE NONCLUSTERED INDEX Tên_chỉ_mục
ON Tên_bảng(Tên_cột_1, Tên_cột_2,...)
--2
CREATE UNIQUE INDEX Tên_chỉ_mục
ON Tên_bảng(Tên_cột_1, Tên_cột_2,...)
--Hoặc
CREATE UNIQUE NONCLUSTERED INDEX Tên_chỉ_mục
ON Tên_bảng(Tên_cột_1, Tên_cột_2,...)
3. Ví dụ
3.1 So sánh Clustered Index và Non-Clustered Index
USE AdventureWorks2017
GO
--1. Tạo bảng Sales.Customer_ClusteredIndex và sao chép dữ liệu từ bảng Sales.Customer
SELECT *
INTO Sales.Customer_ClusteredIndex
FROM Sales.Customer
GO
--Tạo 1 Clustered Index trên trường CustomerID
CREATE CLUSTERED INDEX Idx_Customer_ClusteredIndex_CustomerID
ON Sales.Customer_ClusteredIndex(CustomerID)
GO
--2. Tạo bảng Sales.Customer_NonClusteredIndex và sao chép dữ liệu từ bảng Sales.Customer
SELECT *
INTO Sales.Customer_NonClusteredIndex
FROM Sales.Customer
GO
--Tạo 1 Non-Clustered Index trên trường CustomerID
CREATE INDEX Idx_Customer_NonClusteredIndex_CustomerID
ON Sales.Customer_NonClusteredIndex(CustomerID)
GO
- Sử dụng tiện ích Include Actual Execution Plan (1) trên SSMS để so sánh tốc độ 2 câu truy vấn:
--Lệnh 1
SELECT *
FROM Sales.Customer_ClusteredIndex
WHERE CustomerID = 11111
--Lệnh 2
SELECT *
FROM Sales.Customer_NonClusteredIndex
WHERE CustomerID = 11111
- Theo hình trên thì lệnh 1 chiếm 33% và lệnh 2 chiếm 67% tổng chi phí truy vấn. Tức là trong trường hợp này Clustered Index đã giúp câu query thực hiện nhanh gấp hơn 2 lần so với Non-Clustered Index.
- Và nếu tìm kiếm trong khoảng như hình dưới thì Non-Clustered Index đã không còn tác dụng (quyét toàn bộ bảng) khi sử dụng toán tử BETWEEN AND trong mệnh đề WHERE.
--Lệnh 1
SELECT *
FROM Sales.Customer_ClusteredIndex
WHERE CustomerID BETWEEN 11111 AND 20000
--Lệnh 2
SELECT *
FROM Sales.Customer_NonClusteredIndex
WHERE CustomerID BETWEEN 11111 AND 20000
3.2 So sánh có Index (Non-Clustered Index) và không có Index
USE AdventureWorks2017
GO
--1. Tạo bảng Sales.Customer_Index và sao chép dữ liệu từ bảng Sales.Customer
SELECT *
INTO Sales.Customer_Index
FROM Sales.Customer
GO
--Tạo 1 Index (Non-Clustered Index) trên trường CustomerID
CREATE INDEX Idx_Customer_Index_CustomerID
ON Sales.Customer_Index(CustomerID)
--2. Tạo bảng Sales.Customer_NoIndex và sao chép dữ liệu từ bảng Sales.Customer
SELECT *
INTO Sales.Customer_NoIndex
FROM Sales.Customer
GO
- Sử dụng tiện ích Include Actual Execution Plan (1) để so sánh tốc độ 2 câu truy vấn:
--Lệnh 1
SELECT *
FROM Sales.Customer_Index
WHERE CustomerID = 11111
--Lệnh 2
SELECT *
FROM Sales.Customer_NoIndex
WHERE CustomerID = 11111
- Theo hình trên thì lệnh 1 chiếm 5% và lệnh 2 chiếm 95% tổng chi phí truy vấn. Tức là trong trường hợp này khi có Index đã giúp câu query thực hiện nhanh hơn gấp 19 lần.
Xem thêm: lệnh sửa chỉ mục ALTER INDEX, lệnh xoá chỉ mục DROP INDEX.
