Index trong sql: cách tạo index trong sql server, sql server: chỉ mục (index)

     

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

Bạn đang xem: Index trong sql: cách tạo index trong sql server, sql server: chỉ mục (index)

Trong gợi ý này, bạn sẽ tò mò rất nhiều sản phẩm công nghệ chúng ta cần phải biết về index trong SQL Server để có một chiến lược tạo ra index tốt nhằm về tối ưu hóa các truy nã vấn của doanh nghiệp.

Clustered Index trong Squốc lộ Server

Trong phần này, các bạn sẽ tìm hiểu về clustered index trong SQL Server và biện pháp quan niệm clustered index mang lại bảng.

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

Câu lệnh sau đây sản xuất một bảng bắt đầu có tên production.parts bao hàm hai cột part_id cùng part_name:

CREATE TABLE production.parts( part_id INT NOT NULL, part_name VARCHAR(100));Và câu lệnh này cyếu 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 tồn tại khóa chính, vì thế Squốc lộ Server lưu trữ các bạn dạng ghi của chính nó trong một cấu trúc có thứ tự được Call là heap (đống).

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

Ví dụ: câu lệnh này kiếm tìm bạn dạng ghi tất 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 triển khai trong Squốc lộ Server Management Studio, bạn có thể thấy SQL Server đang giới thiệu chiến lược truy nã vấn nlỗi sau:

*
Lưu ý: để thấy ước chừng planer triển khai trong Squốc lộ Server Management Studio, chúng ta nhấp chuột nút ít Display Estimated Execution Plan hoặc chọn truy tìm vấn với dấn phím tắt Ctrl+L:
*

Vì bảng production.parts chỉ tất cả năm phiên bản ghi, đề nghị truy tìm vấn sẽ tiến hành khôn xiết nkhô giòn. Tuy nhiên, nếu bảng cất một số trong những lượng bạn dạng ghi Khủng thì đang mất không hề ít thời gian với tài ngulặng để tra cứu kiếm tài liệu.

Để giải quyết và xử lý vụ việc này, Squốc lộ Server hỗ trợ một cấu trúc chuyên sử dụng để tăng tốc độ tróc nã xuất các bản ghi từ một bảng được hotline là index.

SQL Server gồm nhì một số loại index là clustered index cùng non-clustered index.

Một clustered index tàng trữ những phiên bản ghi dữ liệu trong một kết cấu được sắp xếp dựa vào những giá trị khóa của chính nó. Mỗi bảng chỉ có một clustered index bởi vì các bạn dạng ghi dữ liệu chỉ có thể được bố trí theo một máy từ. Bảng tất cả clustered index được gọi là clustered table.

Hình ảnh tiếp sau đây minch họa kết cấu của một clustered index:

*

Một clustered index tổ chức tài liệu bằng phương pháp sử dụng một kết cấu quan trọng được điện thoại tư vấn là B-tree (balanced tree - cây cân nặng bằng) cho phép tra cứu kiếm, ckém, cập nhật với xóa bạn dạng ghi bất kỳ cùng với thời gian tương đồng.

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

Trong B-tree, nút nơi bắt đầu với nút trung gian đựng những trang chỉ mục để lữu trữ các chỉ mục của các bạn dạng ghi. Các nút ít lá cất những trang tài liệu (data pages) của bảng. Các trang trong mỗi cung cấp của index được link bởi cấu trúc khác gọi là list link song.

Clustered Index với khóa bao gồm trong Squốc lộ Server

khi các bạn chế tạo ra bảng bao gồm khóa bao gồm, Squốc lộ Server sẽ auto tạo thành một clustered index tương ứng dựa vào các cột bao gồm vào khóa chủ yếu.

Câu lệnh này tạo một bảng bắt đầu thương hiệu là production.part_prices có khóa thiết yếu bao hàm nhì 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) );

*
Nlỗi chúng ta thấy trong hình bên trên, Squốc lộ Server sẽ tự động sản xuất một clustered index mang tên là PK__part_pri_xxxx cho bảng production.part_prices.

Nếu các bạn thêm khóa bao gồm vào một bảng vẫn tất cả một clustered index, SQL Server đã nên khóa bao gồm áp dụng một non-clustered index. Câu lệnh này khái niệm khóa bao gồm mang lại bảng production.parts:

ALTER TABLE production.partsĐịa Chỉ PRIMARY KEY(part_id);

Tạo Clustered Index vào SQL Server

Trong ngôi trường vừa lòng một bảng không có khóa bao gồm (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 để khái niệm một clustered index mang lại bảng.

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

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

*

Khi triển khai câu lệnh sau đây, Squốc lộ Server phê duyệt qua chỉ mục (Tìm kiếm clustered index) nhằm xác định vị trí bản ghi, cách này thì nhanh khô rộng quét toàn thể bảng.

Xem thêm: Bài 30: Phong Trào Yêu Nước Chống Pháp Từ Đầu Thế Kỷ 20 Đến Năm 1918

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 nlỗi sau:

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

trước hết, chúng ta sử dụng mệnh đề CREATE CLUSTERED INDEX để tạo thành clustered index.Thđọng nhị, hướng dẫn và chỉ định tên của clustered index sau mệnh đề CREATE CLUSTERED INDEX.Thứ ba, hướng đẫn lược đồ dùng với tên bảng mà bạn có nhu cầu tạo index.Cuối cùng, liệt kê một hoặc các cột tất cả vào index.

Non-clustered index vào SQL Server

Trong phần này, bạn sẽ tò mò phương pháp thực hiện câu lệnh SQL Server CREATE INDEX để chế tạo ra 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à 1 cấu trúc dữ liệu góp nâng cao tốc độ truy xuất dữ liệu trường đoản cú những bảng. Không giống như clustered index, non-clustered index thu xếp và tàng trữ dữ liệu hiếm hoi với những phiên bản ghi trong bảng. Nó là 1 trong những 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ừ 1 bảng được liên kết.

Tương từ bỏ như clustered index, non-clustered index áp dụng kết cấu cây B-Tree để tổ chức triển khai dữ liệu của nó.

Một bảng hoàn toàn có thể bao gồm một hoặc những non-clustered index cùng từng non-clustered index có thể gồm 1 hoặc các 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 vấn đề lưu trữ các giá trị khóa index, các nút lá cũng lưu trữ những con trỏ trỏ cho tới những bản ghi có chứa những giá trị khóa. Những nhỏ trỏ bản ghi này còn gọi là các xác định mặt hàng (row locators).

Nếu bảng là 1 clustered table (bảng bao gồm clustered index), nhỏ trỏ bản ghi là khóa của clustered index. Trong ngôi trường đúng theo bảng không có clustered index, con trỏ bản ghi trỏ mang lại bạn dạng ghi của bảng.

Tạo non-clustered index trong SQL Server

Để tạo ra một non-clustered index trong Squốc lộ Server, các bạn thực hiện câu lệnh CREATE INDEX:

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

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

lấy ví dụ như 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 chủng loại để minch họa.

*

Bảng sales.customers là 1 clustered table cũng chính vì nó gồm một khóa chủ yếu customer_id.

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

Câu lệnh sau tìm kiếm tìm hầu hết người tiêu dùng có tương tác làm việc thị trấn Atwater:

SELECT customer_id, cityFROM sales.customersWHERE đô thị = "Atwater";Nếu chúng ta xem ước chừng chiến lược thực hiện, bạn sẽ thấy trình tối ưu hóa truy nã vấn quét clustered index để kiếm tìm 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 đô thị.

*

Để nâng cấp vận tốc của truy tìm vấn này, bạn có thể tạo thành một non-clustered index đến 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 ước chừng kế hoạch xúc tiến của tầm nã vấn trên, các bạn sẽ thấy rằng trình buổi tối ưu hóa tầm nã vấn áp dụng non-clustered index ix_customers_thành phố nlỗi sau:

*

Tạo non-clustered index cho nhiều cột trong SQL Server

Câu lệnh dưới đây tìm kiếm quý khách hàng bao gồm chúng ta là Berg cùng thương hiệu là Monika:

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

*
Trình buổi tối ưu hóa truy nã vấn quét clustered index nhằm kiếm tìm kiếm người sử dụng tất cả chúng ta là Berg và thương hiệu là Monika.

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

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

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

*
khi bạn chế tác một non-clustered index bao gồm những cột, thứ tự của những cột vào chỉ mục là khôn xiết đặc trưng. Quý Khách nên được đặt các cột nhưng bạn thường xuyên sử dụng để truy tìm vấn tài liệu sinh hoạt đầu danh sách cột.

Ví dụ: câu lệnh sau đây kiếm tìm tìm người tiêu dùng 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 hỏi vấn rất có thể tận dụng tối đa index cùng áp dụng phương thức index seek để tra cứu kiếm:

SELECT customer_id, first_name, last_nameFROM sales.customersWHERE last_name = "Albert";

*
Câu lệnh tiếp 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 dẫu vậy nó buộc phải quét toàn cục index (index scan) để kiếm tìm kiếm, chậm chạp hơn đối với thực hiện cách làm index seek để tìm tìm.

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

*
Do đó, giải pháp cực tốt là đặt các cột mà lại các bạn hay sử dụng để truy vấn vấn tài liệu nghỉ ngơi đầu danh sách cột của index.

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

Trong phần này, các bạn sẽ tò mò bí quyết thay tên index bằng cách thực hiện stored procedure khối hệ thống sp_rename cùng SQL Server Management Studio.

Xem thêm: Giải Bài 7 Trang 10 Sgk Toán 9 Tập 1 0 Sgk Toán 9 Tập 1, Bài 7 Trang 10 Sgk Toán 9 Tập 1

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

sp_renamelà một trong những stored procedure hệ thống có thể chấp nhận được bạn đổi tên ngẫu nhiên đối tượng người tiêu dùng làm sao do người dùng sản xuất vào cửa hàng tài liệu bây giờ bao hàm bảng, index với cột.

Câu lệnh thay tên một index nlỗi sau:

EXEC sp_rename index_name, new_index_name, N"INDEX";Hoặc bạn cũng có thể áp dụng các ttê mê số rõ ràng như sau:


Chuyên mục: