Sử dụng index trong sql

     

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

Bạn đang xem: Sử dụng index trong sql

Trong hướng dẫn này, các bạn sẽ tìm hiểu phần nhiều máy các bạn nên biết về index vào SQL Server để sở hữu một chiến lược tạo thành index giỏi nhằm mục tiêu buổi tối ưu hóa những truy nã vấn của khách hàng.

Clustered Index trong Squốc lộ Server

Trong phần này, bạn sẽ tò mò về clustered index trong SQL Server với phương pháp có mang clustered index mang đến bảng.

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

Câu lệnh dưới đây tạo ra một bảng mới mang tên production.parts bao gồm nhì 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 ckém 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,"Shoông xã Absorber"), (5,"Fork");Bảng production.parts không có khóa thiết yếu, cho nên vì thế Squốc lộ Server lưu trữ những bản ghi của chính nó vào một kết cấu bao gồm trang bị từ bỏ được hotline là heap (đống).

khi bạn truy hỏi vấn dữ liệu trường đoản cú bảng production.parts, trình về tối ưu hóa truy vấn đã quét toàn thể bảng để xác xác định trí đúng đắn.

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

SELECT part_id, part_nameFROM production.partsWHERE part_id = 5;Nếu chúng ta coi ước chừng kế hoạch tiến hành trong Squốc lộ Server Management Studio, bạn có thể thấy Squốc lộ Server sẽ giới thiệu kế hoạch truy nã vấn nlỗi sau:

*
Lưu ý: giúp xem ước tính chiến lược thực hiện vào SQL Server Management Studio, bạn bấm chuột nút Display Estimated Execution Plan hoặc lựa chọn tầm nã vấn và dấn phím tắt Ctrl+L:
*

Vì bảng production.parts chỉ gồm năm phiên bản ghi, bắt buộc truy nã vấn vẫn tiến hành siêu nkhô giòn. Tuy nhiên, ví như bảng đựng một số trong những lượng bản ghi to thì sẽ mất rất nhiều thời gian với tài nguyên ổn nhằm search kiếm dữ liệu.

Để xử lý vấn đề này, SQL Server cung ứng một cấu trúc chuyên dụng để tăng tốc độ truy vấn xuất các bản ghi xuất phát điểm từ một bảng được call là index.

Squốc lộ Server bao gồm hai một số loại index là clustered index cùng non-clustered index.

Một clustered index lưu trữ các bạn dạng ghi tài liệu vào một kết cấu được bố trí dựa vào những quý giá khóa của chính nó. Mỗi bảng chỉ tất cả một clustered index do những bản ghi dữ liệu chỉ rất có thể được sắp xếp theo một máy từ bỏ. Bảng bao gồm clustered index được Call là clustered table.

Hình ảnh dưới đây minh họa cấu tạo của một clustered index:

*

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

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

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

Clustered Index với khóa thiết yếu trong SQL Server

Lúc chúng ta sinh sản bảng có khóa chính, Squốc lộ Server vẫn tự động chế tác một clustered index tương xứng dựa vào những cột gồm vào khóa thiết yếu.

Câu lệnh này chế tạo ra một bảng new thương hiệu là production.part_prices bao gồm khóa bao gồm 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) );

*
Nlỗi chúng ta thấy vào hình bên trên, SQL Server sẽ tự động tạo thành một clustered index mang tên là PK__part_pri_xxxx mang đến bảng production.part_prices.

Nếu các bạn thêm khóa chủ yếu vào một bảng đang tất cả một clustered index, SQL Server đang đề nghị khóa thiết yếu sử dụng một non-clustered index. Câu lệnh này khái niệm khóa chính 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 có khóa chính (điều này khôn xiết hiếm) bạn cũng có thể áp dụng câu lệnh CREATE CLUSTERED INDEX nhằm định nghĩa một clustered index mang đến bảng.

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

CREATE CLUSTERED INDEX ix_parts_idON production.parts (part_id); Nếu bạn msinh hoạt nút í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 dáng Clustered.

*

lúc thực hiện câu lệnh tiếp sau đây, Squốc lộ Server coi xét qua chỉ mục (Tìm tìm clustered index) để xác định vị trí phiên bản ghi, giải pháp này thì nkhô hanh hơn quét toàn cục bảng.

Xem thêm: Ông Bầu Phước Sang Sau Vỡ Nợ 'Làm Gì Để Nhiều Tiền'?

SELECT part_id, part_nameFROM production.partsWHERE part_id = 5;

*

Cú pháp sản xuất clustered index trong SQL Server

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

Non-clustered index trong SQL Server

Trong phần này, các bạn sẽ tìm hiểu biện pháp sử dụng câu lệnh SQL Server CREATE INDEX nhằm sinh sản các non-clustered index cho các bảng.

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

Non-clustered index là một kết cấu tài liệu góp cải thiện tốc độ truy nã xuất tài liệu từ bỏ các bảng. Không y hệt như clustered index, non-clustered index thu xếp cùng lưu trữ dữ liệu hiếm hoi với các phiên bản ghi trong bảng. Nó là một trong phiên bản sao dữ 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 tự nhỏng clustered index, non-clustered index thực hiện cấu tạo cây B-Tree để tổ chức triển khai dữ liệu của nó.

Một bảng rất có thể gồm một hoặc các non-clustered index cùng mỗi non-clustered index rất có thể gồm 1 hoặc các cột của bảng.

Tấm hình dưới đây minh họa kết cấu non-clustered index:

*

Bên cạnh Việc lưu trữ những giá trị khóa index, những nút lá cũng lưu trữ những bé trỏ trỏ tới những bạn dạng ghi tất cả cất những quý hiếm khóa. Những con trỏ bạn dạng ghi này có cách gọi khác là những xác định hàng (row locators).

Nếu bảng là một trong những clustered table (bảng có clustered index), bé trỏ phiên bản ghi là khóa của clustered index. Trong ngôi trường đúng theo bảng không có clustered index, bé trỏ bạn dạng ghi trỏ mang đến bản ghi của bảng.

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

Để tạo một non-clustered index trong SQL 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 thương hiệu của index sau mệnh đề CREATE NONCLUSTERED INDEX. Lưu ý rằng từ khóa NONCLUSTERED là tùy lựa chọn.Thứ nhị, hướng đẫn tên bảng nhưng mà bạn muốn chế tạo ra index với danh sách 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 đã thực hiện bảng sales.customers trường đoản cú cửa hàng tài liệu chủng loại để minh họa.

*

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

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

Câu lệnh sau tra cứu tìm rất nhiều khách hàng gồm liên hệ ngơi nghỉ thị trấn Atwater:

SELECT customer_id, cityFROM sales.customersWHERE thành phố = "Atwater";Nếu chúng ta coi khoảng chừng chiến lược thực hiện, bạn sẽ thấy trình buổi tối ưu hóa truy hỏi vấn quét clustered index để tra cứu những phiên bản ghi. Như vậy là do bảng sales.customers không tồn tại index cho 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 khách hàng xem xét lại ước lượng kế hoạch xúc tiến của truy vấn vấn bê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 nlỗi sau:

*

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

Câu lệnh sau đây tra cứu tìm người tiêu dùng gồm bọn họ là Berg với thương hiệu 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 nhằm tìm tìm quý khách tất cả bọn họ là Berg và tên là Monika.

Để tăng tốc độ truy xuất dữ liệu, chúng ta có thể tạo thành một non-clustered index bao gồm cả hai 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ờ đồng hồ, trình về tối ưu hóa tầm nã vấn vẫn sử dụng chỉ mục ix_customers_name để tra cứu kiếm người tiêu dùng.

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

*
khi chúng ta chế tạo một non-clustered index bao hàm những cột, sản phẩm công nghệ từ bỏ của những cột trong chỉ mục là hết sức đặc trưng. Quý Khách nên được sắp xếp các cột mà bạn thường áp dụng để tróc nã vấn tài liệu sinh sống đầu danh sách cột.

Ví dụ: câu lệnh sau đây tìm kiếm kiếm người tiêu dùng tất cả chúng ta Albert. Vì cột last_name là cột trước tiên vào index, trình buổi tối ưu hóa truy vấn hoàn toàn có thể tận dụng tối đa index và áp dụng cách làm index seek nhằm search kiếm:

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

*
Câu lệnh tiếp sau đây kiếm tìm tìm khách hàng có tên là Adam. Nó cũng tận dụng index ix_customer_name tuy thế nó yêu cầu quét tổng thể index (index scan) nhằm tìm kiếm, chậm trễ rộng so với thực hiện cách tiến hành index seek để tìm kiếm.

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

*
Do đó, biện pháp rất tốt là đặt những cột mà chúng ta thường xuyên thực hiện để tróc nã vấn tài liệu sinh hoạt đầu danh sách cột của index.

Đổi tên index vào SQL Server

Trong phần này, bạn sẽ mày mò giải pháp đổi tên index bằng phương pháp áp dụng stored procedure hệ thống sp_rename với Squốc lộ Server Management Studio.

Xem thêm: Tiểu Sử Ca Sĩ Ngọc Sơn - Được Cập Nhật Mới Nhất

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

sp_renamelà 1 trong những stored procedure khối hệ thống cho phép bạn thay tên ngẫu nhiên đối tượng người tiêu dùng làm sao vì người dùng chế tạo ra vào cửa hàng dữ liệu hiện nay bao hàm bảng, index cùng cột.

Câu lệnh đổi tên một index nhỏng sau:

EXEC sp_rename index_name, new_index_name, N"INDEX";Hoặc bạn có thể thực hiện các tđam mê số ví dụ nhỏng sau: