Tạo chỉ mục theo cụm và không theo cụm trong SQL Server

Trong SQL Server, tồn tại hai loại chỉ mục; Chỉ mục được phân cụm và không được phân cụm. Cả chỉ mục được phân cụm và chỉ mục không được phân cụm đều có cấu trúc vật lý giống nhau. Hơn nữa, cả hai đều được lưu trữ trong SQL Server dưới dạng cấu trúc B-Tree.

Chỉ mục theo cụm:

Danh sách nhóm là một loại chỉ mục cụ thể sắp xếp lại bộ lưu trữ vật lý của các bản ghi trong bảng. Trong SQL Server, các chỉ mục được sử dụng để tăng tốc hoạt động của cơ sở dữ liệu, dẫn đến hiệu suất cao. Do đó, bảng có thể chỉ có một chỉ mục được nhóm lại, chỉ mục này thường được thực hiện trên khóa chính. Các nút lá của chỉ mục được phân nhóm chứa "Trang dữ liệu". Một bảng chỉ có thể sở hữu một chỉ mục nhóm.

Hãy để chúng tôi tạo một chỉ mục nhóm để hiểu rõ hơn. Trước hết, chúng ta cần tạo một cơ sở dữ liệu.

Tạo cơ sở dữ liệu

Để tạo một cơ sở dữ liệu. Nhấp chuột phải vào “Cơ sở dữ liệu” trong trình khám phá đối tượng và chọn “Cơ sở dữ liệu mới” Lựa chọn. Nhập tên của cơ sở dữ liệu và nhấp vào ok. Cơ sở dữ liệu đã được tạo như trong hình bên dưới.

Bây giờ chúng ta sẽ tạo một bảng có tên "Nhân viên" với khóa chính bằng cách sử dụng dạng xem thiết kế. Chúng ta có thể thấy trong hình bên dưới, chúng ta đã chỉ định chủ yếu cho tên "ID" đã nộp và chúng ta chưa tạo bất kỳ chỉ mục nào trên bảng.

Bạn cũng có thể tạo một bảng bằng cách thực thi đoạn mã sau.

SỬ DỤNG [test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo]. [Employee] ([ID] [int] IDENTITY (1,1) NOT NULL, [Dep_ID] [int] NULL, [Name] [ varchar] (200) NULL, [email] [varchar] (250) NULL, [city] [varchar] (250) NULL, [address] [varchar] (500) NULL, CONSTRAINT [Primary_Key_ID] CHÌA KHÓA CHÍNH XÁC ([ID ] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO

Kết quả sẽ như sau.

Đoạn mã trên đã tạo một bảng có tên "Nhân viên" với một trường ID, một số nhận dạng duy nhất làm khóa chính. Bây giờ trong bảng này, một chỉ mục theo nhóm sẽ được tự động tạo trên ID cột do các ràng buộc về khóa chính. Nếu bạn muốn xem tất cả các chỉ mục trên một bảng, hãy chạy thủ tục được lưu trữ “Sp_helpindex”. Thực thi đoạn mã sau để xem tất cả các chỉ mục trên bảng có tên "Nhân viên". Thủ tục lưu trữ này lấy tên bảng làm tham số đầu vào.

SỬ DỤNG kiểm tra THỰC HIỆN sp_helpindex Nhân viên

Kết quả sẽ như sau.

Một cách khác để xem chỉ mục bảng là đi tới "những cái bàn" trong trình khám phá đối tượng. Chọn bàn và sử dụng nó. Trong thư mục chỉ mục, bạn có thể thấy tất cả các chỉ mục liên quan đến bảng cụ thể đó như thể hiện trong hình bên dưới.

Vì đây là chỉ mục được phân cụm nên thứ tự logic và vật lý của chỉ mục sẽ giống nhau. Điều này có nghĩa là nếu một bản ghi có Id là 3, thì nó sẽ được lưu trữ ở hàng thứ ba của bảng. Tương tự, nếu bản ghi thứ năm có id là 6, nó sẽ được lưu trữ trong 5^{thứ tự} vị trí của bảng. Để hiểu thứ tự của các bản ghi, bạn cần thực hiện đoạn mã sau.

SỬ DỤNG [test] ĐI ĐẶT IDENTITY_INSERT [dbo]. [Nhân viên] BẬT CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ ( 8, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') CHÈN [dbo]. [Nhân viên] ([ID ], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (9, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ' , N'895 E 7th St Saint Paul Mn 551063852 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (10, 7, N'Pilar Ackaerman ', N'[email protected]', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID] , [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (11, 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro Hoặc 97124 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (12, 2, N'Aabdi Maghsoudi' , N'[email protected] ', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400') INSERT [dbo]. [Employee] ([ID], [Dep_ID], [Name], [email], [city], [address]) VALUES (13, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') CHÈN [dbo]. [Nhân viên] ([ ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (14, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (1 , 1, N'Aaaronboy Gutierrez ', N'[email protected]', N'HILLSBORO ', N'5840 Ne Cornell Rd Hillsboro Hoặc 97124') CHÈN [dbo]. [Nhân viên] ([ID], [ Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (2, 2, N'Aabdi Maghsoudi ', N'[email protected]', N'BRENTWOOD ', N'987400 Nebraska Medical Center Omaha Ne 681987400 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (3, 3, N'Aabharana, Sahni ' , N'[email protected] ', N'HYATTSVILLE', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [city], [address]) VALUES (4, 3, N'Aabharana, Sahni ', N'[email protected]', N'HYATTSVILLE ', N'2 Barlo Circle Suite A Dillsburg Pa 170191 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (5, 4, N'Aabish Mughal', N ' [email protected] ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố] , [address]) VALUES (6, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') CHÈN [dbo]. [Nhân viên ] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (7, 5, N'Aabram Howell ', N'[email protected]', N 'DILLSBURG', N'868 York Ave Atlanta Ga 303102750 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (15 , 4, N'Aabish Mughal ', N'abish_mughal @ gmail.com ', N'OMAHA', N'2975 Crouse Lane Burlington Nc 272150000 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [ address]) VALUES (16, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') CHÈN [dbo]. [Nhân viên] ( [ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (17, 5, N'Aabram Howell ', N'[email protected]', N'DILLSBURG ', N'868 York Ave Atlanta Ga 303102750') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (18, 6 , N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N'895 E 7th St Saint Paul Mn 551063852') CHÈN [dbo]. [Nhân viên] ([ID], [ Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (19, 6, N'Humbaerto Acevedo ', N'[email protected]', N'SAINT PAUL ', N' 895 E 7th St Saint Paul Mn 551063852 ') CHÈN [dbo]. [Nhân viên] ([ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ]) GIÁ TRỊ (20, 7, N 'Pilar Ackaerman', N'[email protected] ', N'ATLANTA ', N'5813 Eastern Ave Hyattsville Md 207822201') ĐẶT IDENTITY_INSERT [dbo]. [Nhân viên] TẮT

Mặc dù các bản ghi được lưu trữ trong cột "Id" theo thứ tự ngẫu nhiên của các giá trị. Nhưng do chỉ mục phân cụm trên cột id. Các bản ghi được lưu trữ vật lý theo thứ tự tăng dần của các giá trị trong cột id. Để xác minh điều này, chúng ta cần thực thi đoạn mã sau.

Chọn * từ test.dbo.

Kết quả sẽ như sau.

Chúng ta có thể thấy trong hình trên, các bản ghi đã được truy xuất theo thứ tự tăng dần của các giá trị trong cột id.

Chỉ mục nhóm tùy chỉnh

Bạn cũng có thể tạo chỉ mục nhóm tùy chỉnh. Vì chúng ta chỉ có thể tạo một chỉ mục được phân nhóm nên chúng ta cần xóa chỉ mục trước đó. Để xóa chỉ mục, hãy thực thi đoạn mã sau.

SỬ DỤNG [test] GO ALTER TABLE [dbo]. [Nhân viên] DROP CONSTRAINT [Primary_Key_ID] VỚI (ONLINE = OFF) ĐI

Kết quả sẽ như sau.

Bây giờ để tạo chỉ mục, hãy thực hiện đoạn mã sau trong cửa sổ truy vấn. Chỉ mục này đã được tạo trên nhiều cột nên nó được gọi là chỉ mục tổng hợp.

SỬ DỤNG [test] GO CREATE CLUSTERED INDEX [ClusteredIndex-20191128-173307] ON [dbo]. [Employee] ([ID] ASC, [Dep_ID] ASC) VỚI (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = TẮT, ONLINE = TẮT, ALLOW_ROW_LOCKS = BẬT, ALLOW_PAGE_LOCKS = BẬT) BẬT [CHÍNH] ĐI

Đầu ra sẽ như sau

Chúng tôi đã tạo chỉ mục nhóm tùy chỉnh trên ID và Dep_ID. Điều này sẽ sắp xếp các hàng theo Id và sau đó theo Dep_Id. Để xem điều này, hãy thực hiện đoạn mã sau. Kết quả sẽ là thứ tự tăng dần của ID và sau đó là Theo Dep_id.

CHỌN [ID], [Dep_ID], [Tên], [email], [thành phố], [địa chỉ] TỪ [kiểm tra]. [Dbo]. [Nhân viên]

Kết quả sẽ như sau.

Chỉ mục không phân cụm:

Chỉ mục không phân cụm là một loại chỉ mục cụ thể trong đó thứ tự logic của chỉ mục không khớp với thứ tự vật lý của các hàng được lưu trữ trên đĩa. Nút lá của chỉ mục không phân cụm không chứa các trang dữ liệu thay vì nó chứa thông tin về các hàng chỉ mục. Một bảng có thể có tối đa 249 chỉ mục. Theo mặc định, hạn chế Khóa duy nhất tạo ra Chỉ mục không bị trộn lẫn. Trong thao tác đọc, các chỉ mục không phân cụm chậm hơn các chỉ mục được phân cụm. Chỉ mục không phân cụm có một bản sao dữ liệu từ các cột được lập chỉ mục được giữ theo thứ tự cùng với các tham chiếu đến các hàng dữ liệu thực tế; con trỏ đến danh sách nhóm nếu có. Do đó, bạn nên chọn chỉ những cột đang được sử dụng trong chỉ mục thay vì sử dụng *. Bằng cách này, dữ liệu có thể được tìm nạp trực tiếp từ chỉ mục trùng lặp. Một chỉ mục được phân nhóm khác cũng được sử dụng để chọn các cột còn lại nếu nó được tạo.

Cú pháp được sử dụng để tạo chỉ mục không phân cụm tương tự như chỉ mục được phân cụm. Tuy nhiên, từ khóa “KHÔNG ĐƯỢC ĐIỀU CHỈNH” được sử dụng thay vì "ĐÃ ĐIỀU CHỈNH" trong trường hợp của chỉ mục không phân cụm. Thực thi tập lệnh sau để tạo chỉ mục không phân cụm.

SỬ DỤNG [test] GO SET ANSI_PADDING ON GO TẠO CHỈ SỐ KHÔNG ĐƯỢC ĐIỀU CHỈNH [NonClusteredIndex-20191129-104230] ON [dbo]. [Nhân viên] ([Tên] ASC) VỚI (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP , ONLINE = TẮT, ALLOW_ROW_LOCKS = BẬT, ALLOW_PAGE_LOCKS = BẬT) BẬT [CHÍNH] ĐI

Kết quả sẽ như sau.

Các bản ghi bảng được sắp xếp theo chỉ mục nhóm nếu nó đã được tạo. Chỉ mục không phân cụm mới này sẽ sắp xếp bảng theo định nghĩa của nó và sẽ được lưu trữ trong một địa chỉ vật lý riêng biệt. Tập lệnh trên sẽ tạo chỉ mục trên cột "TÊN" của bảng Nhân viên. Chỉ mục này sẽ sắp xếp bảng theo thứ tự tăng dần của cột “Tên”. Dữ liệu bảng và chỉ mục sẽ được lưu trữ ở các vị trí khác nhau, như chúng tôi đã nói trước đó. Bây giờ hãy thực thi tập lệnh sau để xem tác động của một chỉ mục không phân cụm mới.

chọn Tên từ Nhân viên

Kết quả sẽ như sau.

Chúng ta có thể thấy trong hình trên rằng cột Tên của bảng Nhân viên đã được hiển thị theo thứ tự tăng dần của cột tên, mặc dù chúng ta chưa đề cập đến mệnh đề “Đặt hàng theo ASC” với mệnh đề chọn. Điều này là do chỉ mục không phân cụm trên cột "Tên" được tạo trên bảng Nhân viên. Bây giờ nếu một truy vấn được viết để lấy Tên, email, thành phố và địa chỉ của một người cụ thể. Cơ sở dữ liệu trước tiên sẽ tìm kiếm tên cụ thể đó bên trong chỉ mục, sau đó truy xuất dữ liệu có liên quan, điều này sẽ làm giảm thời gian tìm nạp truy vấn, đặc biệt khi dữ liệu lớn.

chọn Tên, email, thành phố, địa chỉ từ Employee where name = 'Aaaronboy Gutierrez'

Phần kết luận

Từ cuộc thảo luận ở trên, chúng ta đã biết rằng chỉ mục được phân cụm chỉ có thể là một trong khi chỉ mục không được phân cụm có thể là nhiều. Chỉ mục phân cụm nhanh hơn so với chỉ mục không phân cụm. Chỉ mục được phân cụm không tiêu tốn thêm dung lượng lưu trữ trong khi chỉ mục không được phân cụm cần thêm bộ nhớ để lưu trữ chúng. Nếu chúng ta áp dụng một ràng buộc khóa chính trên bảng, chỉ mục nhóm được tạo tự động trên đó. Hơn nữa, nếu chúng ta áp dụng một ràng buộc khóa duy nhất trên bất kỳ cột nào, một chỉ mục không phân cụm sẽ tự động được tạo trên đó. Chỉ mục không phân cụm nhanh hơn so với chỉ mục được phân cụm cho hoạt động chèn và cập nhật. Một bảng có thể không có bất kỳ chỉ mục không phân cụm nào.