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.
Đang xem: Cơ Bản Về Index Là Gì Trong Sql Server
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_nameFROM production.partsWHERE 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.partsADD 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_idON 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.
Xem thêm: Hướng Dẫn Cách Sử Dụng Công Thức Mảng Trong Excel Là Gì, Ứng Dụng Công Thức Mảng Trong Excel
SELECT part_id, part_nameFROM production.partsWHERE 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_nameON 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
Đầ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, cityFROM sales.customersWHERE 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_cityON 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_nameFROM sales.customersWHERE 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_nameFROM sales.customersWHERE 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_nameFROM sales.customersWHERE 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_nameFROM sales.customersWHERE 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.
Xem thêm: #1 Hướng Dẫn Cách Sử Dụng Obs Studio Để Quay Màn Hình Máy Tính
Đổ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: