Clustered Index Và Non-Clustered Index

     

Index (chỉ mục) trong SQL vps là các kết cấu dữ liệu đặc biệt được link với những bảng hoặc view giúp tăng tốc truy nã vấn. SQL Server cung cấp hai các loại index: clustered index với non-clustered index.

Bạn đang xem: Clustered index và non-clustered index

Trong giải đáp này, bạn sẽ tìm hiểu hồ hết thứ bạn cần phải biết về index trong SQL Server để có một kế hoạch tạo index giỏi nhằm về tối ưu hóa các truy vấn của bạn.

Clustered Index vào SQL Server

Trong phần này, bạn sẽ tìm đọc về clustered index vào SQL vps và giải pháp định nghĩa clustered index cho bảng.

Giới thiệu về Clustered Index trong SQL Server

Câu lệnh dưới đây tạo một bảng mới có tên production.parts bao hàm hai cột part_id với 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 dạng 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 tồn tại khóa chính, vì vậy SQL Server tàng trữ các bản ghi của nó trong một cấu trúc có thứ tự được hotline là heap (đống).

Khi các bạn truy vấn dữ liệu từ bảng production.parts, trình buổi tối ưu hóa truy nã vấn vẫn quét toàn thể bảng nhằm xác định vị trí chính xác.

Ví dụ: câu lệnh này tìm bản ghi bao gồm id là 5.

SELECT part_id, part_nameFROM production.partsWHERE part_id = 5;Nếu chúng ta xem cầu lượng kế hoạch tiến hành trong SQL hệ thống Management Studio, chúng ta có thể thấy SQL server đã giới thiệu kế hoạch truy nã vấn như sau:

*
Lưu ý: để xem mong lượng kế hoạch triển khai trong SQL hệ thống Management Studio, bạn nhấp chuột nút Display Estimated Execution Plan hoặc chọn truy vấn cùng nhấn phím tắt Ctrl+L:
*

Vì bảng production.parts chỉ tất cả năm bạn dạng ghi, đề nghị truy vấn sẽ thực hiện rất nhanh. Tuy nhiên, giả dụ bảng chứa một vài lượng bạn dạng ghi phệ thì vẫn mất rất nhiều thời gian cùng tài nguyên nhằm tìm kiếm dữ liệu.

Để giải quyết vấn đề này, SQL Server cung ứng một cấu trúc chuyên dụng nhằm tăng vận tốc truy xuất các phiên bản ghi xuất phát từ 1 bảng được điện thoại tư vấn là index.

SQL Server bao gồm hai loại index là clustered index cùng non-clustered index.

Một clustered index lưu trữ các 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ó. Từng bảng chỉ có một clustered index bởi vì các bản ghi tài liệu chỉ có thể được bố trí theo một sản phẩm công nghệ tự. Bảng bao gồm clustered index được hotline là clustered table.

Hình ảnh sau phía trên minh họa cấu trúc của một clustered index:

*

Một clustered index tổ chức triển khai dữ liệu bằng phương pháp sử dụng một kết cấu đặc biệt được điện thoại tư vấn là B-tree (balanced tree - cây cân bằng) có thể chấp nhận được tìm kiếm, chèn, cập nhật và xóa bạn dạng ghi ngẫu nhiên với thời gian như nhau.

Trong kết cấu này, nút trên cùng của B-tree được hotline là nút gốc (root node). Các nút ở cấp độ dưới thuộc được gọi là các nút lá (leaf nodes). Ngẫu nhiên nút như thế nào ở giữa những nút nơi bắt đầu và nút lá được call là nút trung gian.

Trong B-tree, nút cội và nút trung gian chứa những trang chỉ mục để lữu trữ các chỉ mục của các phiên bản ghi. Những nút lá chứa những trang dữ liệu (data pages) của bảng. Những trang trong mỗi cấp của index được links bằng kết cấu khác điện thoại tư vấn là danh sách link đôi.

Clustered Index cùng khóa chủ yếu trong SQL hệ thống

Khi chúng ta tạo bảng tất cả khóa chính, SQL hệ thống sẽ auto tạo một clustered index khớp ứng dựa trên các cột bao gồm trong khóa chính.

Câu lệnh này chế tác một bảng bắt đầu tên là production.part_prices có khóa chính bao hàm hai cột là: part_id với 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ư các bạn thấy trong hình trên, SQL vps đã auto tạo một clustered index mang tên là PK__part_pri_xxxx đến bảng production.part_prices.

Nếu các bạn thêm khóa chính vào trong 1 bảng đã tất cả một clustered index, SQL server sẽ cần khóa chính thực hiện một non-clustered index. Câu lệnh này quan niệm khóa bao gồm 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 tồn tại khóa chính (điều này cực kỳ hiếm) chúng ta cũng có thể sử dụng câu lệnh CREATE CLUSTERED INDEX để có mang một clustered index đến bảng.

Câu lệnh dưới đây tạo một clustered index mang lại bảng production.parts:

CREATE CLUSTERED INDEX ix_parts_idON production.parts (part_id); nếu như 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 mẫu mã Clustered.

*

Khi thực hiện câu lệnh dưới đây, SQL Server để ý qua chỉ mục (Tìm tìm clustered index) để xác xác định trí bạn dạng ghi, cách này thì cấp tốc hơn quét cục bộ bảng.

Xem thêm: Điện Thoại Nghe Nhạc Hay Nhất 2015, Smartphone Nghe Nhạc Hay Nhất 2015

SELECT part_id, part_nameFROM production.partsWHERE part_id = 5;

*

Cú pháp tạo clustered index vào SQL Server

Cú pháp tạo clustered index vào 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 áp dụng mệnh đề CREATE CLUSTERED INDEX để tạo thành clustered index.Thứ hai, chỉ định tên của clustered index sau mệnh đề CREATE CLUSTERED INDEX.Thứ ba, hướng dẫn và chỉ định lược đồ với tên bảng mà bạn muốn tạo index.Cuối cùng, liệt kê một hoặc các cột bao gồm trong index.

Non-clustered index trong SQL Server

Trong phần này, các bạn sẽ tìm phát âm cách áp dụng câu lệnh SQL vps CREATE INDEX để tạo các non-clustered index cho những bảng.

Giới thiệu về non-clustered index vào SQL Server

Non-clustered index là một cấu tạo dữ liệu giúp nâng cấp tốc độ tầm nã xuất tài liệu từ các bảng. Không y như clustered index, non-clustered index thu xếp và tàng trữ dữ liệu đơn nhất với các phiên bản ghi vào bảng. Nó là một phiên bản sao dữ liệu của những cột được chọn xuất phát từ 1 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 hoàn toàn có thể có một hoặc nhiều non-clustered index cùng mỗi non-clustered index bao gồm thể gồm 1 hoặc nhiều cột của bảng.

Hình ảnh sau trên đây minh họa cấu trúc non-clustered index:

*

Bên cạnh câu hỏi lưu trữ các giá trị khóa index, các nút lá cũng lưu lại trữ những con trỏ trỏ cho tới các bạn dạng ghi gồm chứa những giá trị khóa. Những bé trỏ bạn dạng ghi này có cách gọi khác là các định vị hàng (row locators).

Nếu bảng là một clustered table (bảng gồm clustered index), bé trỏ bạn dạng ghi là khóa của clustered index. Vào trường phù hợp bảng không tồn tại clustered index, nhỏ trỏ bản ghi trỏ đến bản ghi của bảng.

Tạo non-clustered index vào SQL Server

Để chế tạo ra một non-clustered index vào SQL Server, bạn áp dụng câu lệnh CREATE INDEX:

CREATE INDEX index_nameON table_name(column_list);Trong cú pháp này:

Đầu tiên, hướng đẫn tên của index sau mệnh đề CREATE NONCLUSTERED INDEX. Lưu ý rằng trường đoản cú khóa NONCLUSTERED là tùy chọn.Thứ hai, hướng dẫn và chỉ định tên bảng mà bạn muốn tạo index cùng danh sách những cột của bảng đó làm cho cột khóa index.

Ví dụ về non-clustered index vào SQL Server

Chúng tôi sẽ sử dụng bảng sales.customers từ bỏ cơ sở tài liệu mẫu nhằm minh họa.

*

Bảng sales.customers là một clustered table bởi vì nó có một khóa chủ yếu customer_id.

Tạo non-clustered index cho một cột vào SQL Server

Câu lệnh sau tìm kiếm những người tiêu dùng có add ở thành phố Atwater:

SELECT customer_id, cityFROM sales.customersWHERE đô thị = "Atwater";Nếu các bạn xem ước lượng chiến lược thực thi, các bạn sẽ thấy trình về tối ưu hóa truy vấn vấn quét clustered index để tìm các phiên bản ghi. Điều này là vì bảng sales.customers không tồn tại index mang lại cột city.

*

Để nâng cao tốc độ của tróc nã 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ầu lượng kế hoạch tiến hành của truy vấn trên, các bạn sẽ thấy rằng trình tối ưu hóa truy tìm vấn thực hiện non-clustered index ix_customers_city như sau:

*

Tạo non-clustered index cho các cột trong SQL Server

Câu lệnh tiếp sau đây tìm kiếm người sử dụ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 về tối ưu hóa truy tìm vấn quét clustered index để tìm kiếm quý khách hàng có bọn họ là Berg cùng tên là Monika.

Để tăng tốc độ truy xuất dữ liệu, chúng ta có thể tạo một non-clustered index bao gồm cả nhị cột last_name với first_name như sau:

CREATE INDEX ix_customers_name ON sales.customers(last_name, first_name);Bây giờ, trình buổi tối ưu hóa truy tìm vấn sẽ sử dụng chỉ mục ix_customers_name để tìm đao khách hàng.

SELECT customer_id, first_name, last_nameFROM sales.customersWHERE last_name = "Berg" và first_name = "Monika";

*
Khi các bạn tạo một non-clustered index bao gồm nhiều cột, sản phẩm công nghệ tự của những cột vào chỉ mục là khôn cùng quan trọng. Bạn nên đặt những cột mà chúng ta thường thực hiện để truy vấn tài liệu ở đầu list cột.

Ví dụ: câu lệnh dưới đây tìm kiếm người tiêu dùng có họ Albert. Vì chưng cột last_name là cột thứ nhất trong index, trình buổi tối ưu hóa truy vấn vấn hoàn toàn có thể tận dụng index và thực hiện 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 dưới đây tìm thích khách hàng có tên là Adam. Nó cũng tận dụng index ix_customer_name nhưng mà nó đề xuất quét tổng thể index (index scan) để tìm kiếm, chậm rãi 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 áp dụng để truy tìm vấn dữ liệu ở đầu list cột của index.

Đổi tên index trong SQL Server

Trong phần này, các bạn sẽ tìm phát âm cách đổi tên index bằng cách sử dụng stored procedure khối hệ thống sp_rename cùng SQL vps Management Studio.

Xem thêm: Khóa Học Làm Video Chuyên Nghiệp Chỉ 499K, Học Làm Video Chuyên Nghiệp

Đổi tên index bằng cách sử dụng stored procedure sp_rename

sp_renamelà một stored procedure hệ thống được cho phép bạn thay đổi tên bất kỳ đối tượng nào do người tiêu dùng tạo trong cơ sở tài liệu hiện tại bao hàm bảng, index với cột.

Câu lệnh thay tên một index như sau:

EXEC sp_rename index_name, new_index_name, N"INDEX";Hoặc bạn cũng có thể sử dụng những tham số ví dụ như sau: