Index trong sql là gì

     

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

Bạn đang xem: Index trong sql là gì

Trong khuyên bảo này, bạn sẽ tìm hiểu hầu như thứ bạn cần phải biết về index trong SQL Server để có một kế hoạch 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 vào SQL hệ thống và biện pháp định nghĩa clustered index đến bảng.

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

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

Khi các bạn truy vấn tài liệu từ bảng production.parts, trình tối ưu hóa truy vấn đang quét toàn cục bảng để xác xác định trí chính xác.

Ví dụ: câu lệnh này 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 xem ước lượng kế hoạch triển khai trong SQL server Management Studio, chúng ta cũng có thể thấy SQL vps đã giới thiệu kế hoạch tróc nã vấn như sau:

*
Lưu ý: nhằm xem mong lượng kế hoạch tiến hành trong SQL vps Management Studio, bạn click chuột nút Display Estimated Execution Plan hoặc lựa chọn truy vấn với nhấn phím tắt Ctrl+L:
*

Vì bảng production.parts chỉ có năm bạn dạng ghi, buộc phải truy vấn sẽ tiến hành rất nhanh. Mặc dù nhiên, nếu bảng chứa một vài lượng phiên bản ghi béo thì đang mất không hề ít thời gian và tài nguyên nhằm tìm tìm dữ liệu.

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

SQL Server có hai một số loại index là clustered index cùng non-clustered index.

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

Hình hình ảnh sau phía trên minh họa kết cấu 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 điện thoại tư vấn là B-tree (balanced tree - cây cân nặng bằng) chất nhận được tìm kiếm, chèn, update và xóa bạn dạng ghi bất kỳ với thời hạn như nhau.

Trong cấu trúc 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 call là những nút lá (leaf nodes). Bất kỳ nút như thế nào ở giữa các nút cội và nút lá được hotline là nút trung gian.

Trong B-tree, nút cội và nút trung gian chứa các trang chỉ mục để lữu trữ những chỉ mục của các bạn dạng 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 link bằng cấu trúc khác hotline là danh sách link đôi.

Clustered Index cùng khóa chính trong SQL hệ thống

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

Câu lệnh này sinh sản một bảng new tên là production.part_prices gồm 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 đã tự động hóa tạo một clustered index có tên là PK__part_pri_xxxx mang đến bảng production.part_prices.

Nếu chúng ta thêm khóa chính vào một trong những bảng đã có một clustered index, SQL server sẽ phải khóa chính sử dụng một non-clustered index. Câu lệnh này tư tưởng 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 vừa lòng một bảng không có khóa chủ yếu (điều này vô cùng hiếm) chúng ta cũng có thể sử dụng câu lệnh CREATE CLUSTERED INDEX để định nghĩa một clustered index mang lại 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 thương hiệu chỉ mục new ix_parts_id với kiểu dáng Clustered.

*

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

Xem thêm: Cách Dỗ Trẻ Sơ Sinh Gắt Ngủ Mẹ Bớt Mệt Không Bị Trầm Cảm, Cách Dỗ Trẻ Sæ¡ Sinh GắT Ngủ Là Gì

SELECT part_id, part_nameFROM production.partsWHERE part_id = 5;

*

Cú pháp tạo nên clustered index trong SQL Server

Cú pháp sản xuất 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 ra clustered index.Thứ hai, chỉ định tên của clustered index sau mệnh đề CREATE CLUSTERED INDEX.Thứ ba, chỉ định và hướng dẫn 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 nhiều cột có 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 hệ thống CREATE INDEX để tạo các non-clustered index cho những 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 độ tầm nã xuất tài liệu từ những bảng. Không giống như clustered index, non-clustered index thu xếp và tàng trữ dữ liệu riêng lẻ với các phiên bản ghi trong bảng. Nó là một bản sao dữ liệu của những cột được chọn xuất phát từ một bảng được liên kết.

Tương trường đoản cú 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 rất có thể có một hoặc nhiều non-clustered index và mỗi non-clustered index có thể gồm một hoặc các cột của bảng.

Hình hình ảnh sau trên đâ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, các nút lá cũng giữ trữ những con trỏ trỏ cho tới các bản ghi bao gồm chứa các giá trị khóa. Những nhỏ trỏ phiên bản ghi này có cách gọi khác là các xác định hàng (row locators).

Nếu bảng là một trong những clustered table (bảng có clustered index), nhỏ trỏ bản ghi là khóa của clustered index. Trong trường đúng theo bảng không tồn tại clustered index, nhỏ trỏ bạn dạng ghi trỏ đế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, 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 và hướng dẫn tên của index sau mệnh đề CREATE NONCLUSTERED INDEX. Xem xét rằng trường đoản cú khóa NONCLUSTERED là tùy chọn.Thứ hai, chỉ định và hướng dẫn 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 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ừ bỏ cơ sở tài liệu mẫu nhằm minh họa.

*

Bảng sales.customers là 1 trong clustered table chính vì nó tất cả một khóa chính customer_id.

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

Câu lệnh sau kiếm tìm kiếm những người tiêu dùng có địa chỉ ở tp Atwater:

SELECT customer_id, cityFROM sales.customersWHERE city = "Atwater";Nếu các bạn xem mong lượng kế hoạch thực thi, bạn sẽ thấy trình về tối ưu hóa truy 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 đến cột city.

*

Để cải thiện tốc độ của truy vấn vấn này, chúng ta cũng có thể tạo một non-clustered index mang lại cột thành phố như sau:

CREATE INDEX ix_customers_cityON sales.customers(city);Bây giờ, nếu khách hàng 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 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ó bọn họ là Berg cùng tên là Monika:

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

*
Trình buổi tối ưu hóa truy hỏi vấn quét clustered index để tìm kiếm quý khách hàng có chúng ta là Berg cùng tên là Monika.

Để tăng tốc độ truy xuất dữ liệu, chúng ta cũng có thể tạo một non-clustered index bao gồ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 tối ưu hóa truy hỏi vấn sẽ sử dụng chỉ mục ix_customers_name nhằm 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, đồ vật tự của các cột vào chỉ mục là siêu quan trọng. Chúng ta nên đặt các cột mà chúng ta thường sử dụng để tróc nã vấn dữ liệu ở đầu list cột.

Ví dụ: câu lệnh dưới đây tìm kiếm khách hàng có bọn họ Albert. Vì cột last_name là cột thứ nhất trong index, trình về tối ưu hóa tróc 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 nhằm 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 khách hàng có tên là Adam. Nó cũng tận dụng tối đa index ix_customer_name tuy nhiên nó buộc phải quét tổng thể index (index scan) để tìm kiếm, đủng đỉnh hơn so với sử dụng phương thức index seek nhằm tìm kiếm.

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

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

Đổi thương hiệu index vào SQL Server

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

Xem thêm: Chụp Ảnh Mặt Bị Lệch Khi Chụp Cảm Thường, Nguyên Nhân Mặt Bi Lệch Là Gì

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

sp_renamelà một stored procedure hệ thống chất nhận được bạn đổi tên bất kỳ đối tượng như thế nào do người dùng tạo vào cơ sở tài liệu hiện tại bao hàm bảng, index cùng 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ể sử dụng những tham số rõ ràng như sau: