Tạo index trong sql server

     

Index (chỉ mục) trong Squốc lộ Server là các kết cấu tài liệu đặc biệt được link cùng với các bảng hoặc view giúp tăng tốc tróc nã vấn. Squốc lộ Server cung cấp nhị một số loại index: clustered index và non-clustered index.

Bạn đang xem: Tạo index trong sql server

Trong khuyên bảo này, các bạn sẽ mày mò hầu như thiết bị bạn cần biết về index trong SQL Server để sở hữu một kế hoạch chế tạo ra index xuất sắc nhằm mục tiêu tối ưu hóa những truy vấn của chúng ta.

Clustered Index trong Squốc lộ Server

Trong phần này, các bạn sẽ mày mò về clustered index trong Squốc lộ Server và phương pháp có mang clustered index đến bảng.

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

Câu lệnh tiếp sau đây sản xuất một bảng mới mang tên production.parts bao gồm nhị 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 cnhát một trong những 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,"Shoông xã Absorber"), (5,"Fork");Bảng production.parts không tồn tại khóa bao gồm, cho nên Squốc lộ Server lưu trữ các bản ghi của chính nó trong một kết cấu gồm sản phẩm công nghệ từ được Call là heap (đống).

lúc bạn tróc nã vấn tài liệu từ bỏ bảng production.parts, trình buổi tối ưu hóa tầm nã vấn đã quét toàn cục bảng nhằm xác xác định trí chính xác.

Ví dụ: câu lệnh này tra cứu bản ghi có id là 5.

SELECT part_id, part_nameFROM production.partsWHERE part_id = 5;Nếu bạn coi ước chừng chiến lược thực hiện trong Squốc lộ Server Management Studio, chúng ta cũng có thể thấy Squốc lộ Server đã giới thiệu chiến lược tầm nã vấn như sau:

*
Lưu ý: để thấy ước chừng planer triển khai vào Squốc lộ Server Management Studio, các bạn nhấn vào nút ít Display Estimated Execution Plan hoặc chọn truy nã vấn và dấn phím tắt Ctrl+L:
*

Vì bảng production.parts chỉ có năm bạn dạng ghi, nên truy hỏi vấn sẽ tiến hành siêu nkhô giòn. Tuy nhiên, ví như bảng cất một số trong những lượng bạn dạng ghi bự thì sẽ mất không ít thời gian và tài ngulặng để search tìm tài liệu.

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

Squốc lộ Server bao gồm nhì nhiều loại index là clustered index với non-clustered index.

Một clustered index lưu trữ các bản ghi tài liệu vào một cấu tạo được sắp xếp dựa trên những cực hiếm khóa của chính nó. Mỗi bảng chỉ bao gồm một clustered index vị những bạn dạng ghi dữ liệu chỉ có thể được thu xếp theo một sản phẩm từ bỏ. Bảng tất cả clustered index được hotline là clustered table.

Hình ảnh dưới đây minch họa cấu tạo 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 quan trọng được Điện thoại tư vấn là B-tree (balanced tree - cây cân bằng) cho phép tìm tìm, ckém, cập nhật và xóa bạn dạng ghi bất kỳ cùng với thời hạn giống hệt.

Trong cấu tạo này, nút ít trên thuộc của B-tree được gọi là nút ít gốc (root node). Các nút ít ngơi nghỉ cấp độ bên dưới thuộc được gọi là các nút ít lá (leaf nodes). Bất kỳ nút nào chính giữa các nút cội cùng nút ít lá được Call là nút trung gian.

Trong B-tree, nút ít gốc với nút trung gian chứa những trang chỉ mục nhằm lữu trữ các chỉ mục của những bản ghi. Các nút lá chứa những trang dữ liệu (data pages) của bảng. Các trang trong mỗi cung cấp của index được liên kết bằng kết cấu khác Hotline là danh sách link song.

Clustered Index với khóa chủ yếu trong SQL Server

lúc chúng ta chế tạo bảng bao gồm khóa chủ yếu, SQL Server đã tự động hóa tạo một clustered index tương xứng dựa trên các cột tất cả trong khóa chủ yếu.

Câu lệnh này chế tạo ra một bảng mới tên là production.part_prices gồm khóa thiết yếu bao gồ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ư chúng ta thấy vào hình trên, SQL Server đã auto tạo ra một clustered index mang tên là PK__part_pri_xxxx đến bảng production.part_prices.

Nếu chúng ta thêm khóa thiết yếu vào một trong những bảng đã có một clustered index, SQL Server đang yêu cầu khóa thiết yếu thực hiện một non-clustered index. Câu lệnh này có mang khóa thiết yếu cho bảng production.parts:

ALTER TABLE production.partsADD PRIMARY KEY(part_id);

Tạo Clustered Index trong Squốc lộ Server

Trong ngôi trường thích hợp một bảng không tồn tại khóa chính (vấn đề này khôn xiết hiếm) chúng ta có thể sử dụng câu lệnh CREATE CLUSTERED INDEX nhằm quan niệm một clustered index đến bảng.

Câu lệnh tiếp sau đây sản xuất một clustered index mang đến bảng production.parts:

CREATE CLUSTERED INDEX ix_parts_idON production.parts (part_id); Nếu chúng ta mnghỉ ngơi nút ít Indexes dưới tên bảng, bạn sẽ thấy tên chỉ mục new ix_parts_id cùng với kiểu dáng Clustered.

*

khi thực hiện câu lệnh tiếp sau đây, Squốc lộ Server duyệt y qua chỉ mục (Tìm tìm clustered index) nhằm xác xác định trí bản ghi, bí quyết này thì nhanh hao hơn quét toàn bộ bảng.

Xem thêm: Tuyển Dụng, Tìm Việc Làm Tuyển Nhân Viên Thu Ngân Siêu Thị Big C

SELECT part_id, part_nameFROM production.partsWHERE part_id = 5;

*

Cú pháp chế tạo clustered index trong Squốc lộ Server

Cú pháp tạo nên clustered index vào SQL Server nhỏng sau:

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

Thứ nhất, các bạn áp dụng mệnh đề CREATE CLUSTERED INDEX nhằm chế tạo ra clustered index.Thứ nhì, chỉ định thương hiệu của clustered index sau mệnh đề CREATE CLUSTERED INDEX.Thứ đọng ba, chỉ định và hướng dẫn lược thiết bị cùng thương hiệu bảng mà lại bạn có nhu cầu tạo nên index.Cuối cùng, liệt kê một hoặc những cột gồm vào index.

Non-clustered index trong SQL Server

Trong phần này, các bạn sẽ tò mò bí quyết sử dụng câu lệnh SQL Server CREATE INDEX nhằm chế tác các non-clustered index cho những bảng.

Giới thiệu về non-clustered index vào Squốc lộ Server

Non-clustered index là 1 trong cấu trúc dữ liệu góp nâng cấp vận tốc truy tìm xuất tài liệu tự các bảng. Không y hệt như clustered index, non-clustered index sắp xếp với lưu trữ dữ liệu đơn nhất cùng với những bạn dạng ghi vào bảng. Nó là 1 trong bản sao tài liệu của các cột được lựa chọn xuất phát điểm từ một bảng được liên kết.

Tương trường đoản cú nhỏng clustered index, non-clustered index thực hiện cấu trúc cây B-Tree để tổ chức tài liệu của chính nó.

Một bảng rất có thể có một hoặc các non-clustered index với mỗi non-clustered index hoàn toàn có thể bao gồm một hoặc nhiều cột của bảng.

Tấm hình tiếp sau đây minch họa kết cấu non-clustered index:

*

Bên cạnh bài toán tàng trữ những giá trị khóa index, các nút ít lá cũng tàng trữ những bé trỏ trỏ cho tới những phiên bản ghi tất cả chứa các cực hiếm khóa. Những con trỏ bạn dạng ghi này còn gọi là những xác định hàng (row locators).

Nếu bảng là một clustered table (bảng bao gồm clustered index), con trỏ bản ghi là khóa của clustered index. Trong trường vừa lòng bảng không tồn tại clustered index, nhỏ trỏ bản ghi trỏ cho bản ghi của bảng.

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

Để sinh sản một non-clustered index trong SQL Server, bạn sử dụng câu lệnh CREATE INDEX:

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

trước hết, hướng đẫn 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đọng nhì, chỉ định và hướng dẫn thương hiệu bảng mà lại bạn muốn sinh sản index cùng list các cột của bảng kia làm cột khóa index.

ví dụ như về non-clustered index trong Squốc lộ Server

Chúng tôi đang thực hiện bảng sales.customers từ cửa hàng dữ liệu mẫu để minc họa.

*

Bảng sales.customers là 1 clustered table bởi vì nó bao gồm một khóa bao gồm customer_id.

Tạo non-clustered index cho một cột trong Squốc lộ Server

Câu lệnh sau tìm kiếm tìm phần nhiều quý khách hàng tất cả can hệ sinh sống thành thị Atwater:

SELECT customer_id, cityFROM sales.customersWHERE đô thị = "Atwater";Nếu chúng ta coi khoảng chừng kế hoạch thực thi, bạn sẽ thấy trình buổi tối ưu hóa truy tìm vấn quét clustered index nhằm search các phiên bản ghi. Vấn đề này là vì bảng sales.customers không có index mang lại cột city.

*

Để nâng cấp vận tốc của truy hỏi vấn này, bạn cũng có thể sản xuất một non-clustered index cho cột city nhỏng sau:

CREATE INDEX ix_customers_cityON sales.customers(city);Bây tiếng, nếu như khách hàng xem xét lại khoảng chừng planer thực hiện của truy nã vấn bên trên, bạn sẽ thấy rằng trình tối ưu hóa truy vấn vấn thực hiện non-clustered index ix_customers_đô thị nhỏng sau:

*

Tạo non-clustered index đến các cột trong Squốc lộ Server

Câu lệnh sau đây kiếm tìm kiếm khách hàng gồm chúng ta 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 tìm vấn quét clustered index để kiếm tìm kiếm quý khách bao gồm họ là Berg và tên là Monika.

Để tăng vận tốc truy tìm xuất tài liệu, chúng ta có thể tạo thành một non-clustered index bao hàm cả nhị cột last_name cùng first_name như sau:

CREATE INDEX ix_customers_name ON sales.customers(last_name, first_name);Bây giờ, trình về tối ưu hóa truy vấn vấn vẫn thực hiện chỉ mục ix_customers_name nhằm tìm kiếm quý khách hàng.

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

*
lúc các bạn chế tạo một non-clustered index bao hàm nhiều cột, sản phẩm công nghệ từ bỏ của các cột vào chỉ mục là cực kỳ quan trọng đặc biệt. Quý khách hàng nên được đặt những cột cơ mà bạn hay thực hiện nhằm truy vấn tài liệu sinh hoạt đầu list cột.

Ví dụ: câu lệnh tiếp sau đây kiếm tìm kiếm người tiêu dùng bao gồm họ Albert. Vì cột last_name là cột trước tiên trong index, trình về tối ưu hóa truy vấn rất có thể tận dụng tối đa index với sử dụng cách 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 kiếm kiếm người sử dụng có tên là Adam. Nó cũng tận dụng index ix_customer_name tuy vậy nó bắt buộc quét toàn bộ index (index scan) để search tìm, lừ đừ hơn đối với áp dụng cách làm index seek để tra cứu kiếm.

SELECT customer_id, first_name, last_nameFROM sales.customersWHERE first_name = "Adam";

*
Do đó, phương pháp cực tốt là đặt những cột cơ mà các bạn thường xuyên áp dụng để tầm nã vấn tài liệu ở đầu danh sách cột của index.

Đổi thương hiệu index trong Squốc lộ Server

Trong phần này, các bạn sẽ tìm hiểu cách thay tên index bằng phương pháp sử dụng stored procedure khối hệ thống sp_rename và SQL Server Management Studio.

Xem thêm: Những Câu Chuyện Cảm Động Về Mẹ Hay Và Ý Nghĩa, Câu Chuyện Cảm Động Về Mẹ Nhân Ngày 8/3

Đổi thương hiệu index bằng cách áp dụng stored procedure sp_rename

sp_renamelà 1 stored procedure khối hệ thống có thể chấp nhận được các bạn thay tên ngẫu nhiên đối tượng như thế nào bởi người dùng tạo thành vào đại lý tài liệu ngày nay 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ó thể thực hiện những tđắm say số ví dụ nlỗi sau:


Chuyên mục: Tổng hợp