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.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *