CREATE INDEX – Lệnh tạo chỉ mục

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:

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.

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…

Cú pháp

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,...)

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,...)

Ví dụ

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

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.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *