Index trong sql là gì

  -  

Có khi nào bạn từ bỏ hỏi câu truy tìm vấn sử dụng index như vậy nào? Index là gì? Có cấu tạo thế nào mà câu truy vấn lại cấp tốc hơn nhiều như thế? nội dung bài viết hôm nay đã giúp chúng ta hiểu rõ rộng về index để có thể tự vấn đáp những câu hỏi tương trường đoản cú như vậy.

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

Tìm dữ liệu trong một tập nhiều dòng

Tập dữ liệu không có thứ tự

Dữ liệu trong cơ sở dữ liệu quan hệ được lưu trữ dưới dạng bảng, hay nói cách khác cách khác là hàng với cột. Quan sát vào bảng dữ liệu trong hình sau đây và bạn hãy vấn đáp truy vấn “tìm nhân viên cấp dưới có ID bằng 5”. Bạn sẽ làm nắm nào?

*
Hình 1: Bảng tài liệu NhanVien

Để đảm không vứt sót có phải bạn sẽ duyệt từng chiếc một từ trên xuống dưới, thanh tra rà soát xem cột ID của cái nào có mức giá trị bởi 5 xuất xắc không?

Nếu dòng này nằm ngay trên đầu hoặc sống dòng thứ 2 hoặc thứ 3 thì bạn sẽ nhanh nệm tìm thấy nó. Dẫu vậy nếu nó nằm ở vị trí dưới cùng thì sao? bạn phải để mắt tới qua không còn bảng bắt đầu có kết quả cuối cùng.

Giả sử ngay dòng trước tiên cột ID đã bằng 5 rồi, liệu chúng ta có thường xuyên tìm xuống bên dưới không? cũng chính vì câu truy vấn của chúng ta không số lượng giới hạn chỉ search một tín đồ (TOP 1) nhưng cũng không có ràng buộc làm sao nói mỗi ID là duy nhất. Bởi vì đó, bạn bắt buộc phải duyệt mang đến cuối bảng vì hoàn toàn có thể có một nhân viên khác bao gồm ID cũng bởi 5 thì sao.

Tập tài liệu có sản phẩm tự

Nếu bảng này được bố trí theo vật dụng tự tăng dần của cột ID thì cầm nào?

*
Hình 2: Bảng dữ liệu NhanVien bố trí theo cột ID

Chúng ta có thể lựa lựa chọn duyệt bảng theo phía từ trên xuống hoặc từ dưới lên. Ta có thể dự đoán giá trị bắt buộc tìm là 5 thì xem xét từ bên trên xuống ngay gần hơn, nhưng mà nếu giá chỉ trị đề nghị tìm là 15 thì coi sóc từ dưới lên có lẽ rằng nhanh hơn?

Đó là do ví dụ của chúng ta chỉ tất cả vài dòng, nếu bảng NhanVien này còn có hàng trăm nghìn hoặc sản phẩm triệu chiếc thì vắt nào? chưa kể giá trị ID không chắc hẳn rằng liên tục. Họ sẽ ko đoán giá tốt trị cần tìm ở vị trí nào nên chỉ có thể có thể chọn một trong nhị hướng và duyệt mang lại đầu mặt kia.

Trong tình huống bảng đã bố trí theo đồ vật tự như này. Khi sẽ tìm thấy dòng gồm ID bằng 5 rồi, nếu tất cả thêm nhân viên khác thuộc ID thì hẳn bắt buộc ở ngay mẫu kế tiếp.

Bạn chỉ cần nhìn thêm dòng sau đó có phải bởi 5 tuyệt không. Nếu không phải thì ngừng truy vấn, còn ví như là 5 thì bình chọn thêm loại kế tiếp cho đến khi không tìm kiếm thêm được giá trị tương tự như nữa.

Dữ liệu mặc dù được sắp xếp theo đồ vật tự vẫn không giúp câu truy vấn vấn chạy cấp tốc được. Nếu rủi ro giá trị nên tìm nằm kề cuối thì ta vẫn buộc phải duyệt phần lớn là hết bảng. Đây vẫn không là index

Clustered index

Tìm kiếm với clustered index

Bây giờ, trường hợp ta tạo nên clustered index trên cột ID đến bảng này thì dữ liệu được tổ chức như vậy nào? bọn họ chạy câu lệnh dễ dàng sau để sản xuất index


Những dòng tài liệu trong bảng được gom đội lại cùng với nhau tạo ra thành page, một page có size 8KB với tùy thuộc vào size của mỗi dòng mà chứa được con số tương ứng. Mang dụ bảng NhanVien bên trên có kích cỡ 2000 bytes cho mỗi dòng, đề nghị mỗi page sẽ đựng được 4 cái như hình bên dưới

*
Hình 3: index B-Tree

Cột ID làm cho clustered index key nên dữ liệu của bảng sẽ tiến hành sắp xếp theo giá bán trị tăng cao trên cột này.

Chúng ta thấy những dòng dữ liệu với ID từ một đến 4 phía trong page vật dụng nhất. Trường đoản cú 5 mang đến 8 phía trong page sản phẩm công nghệ hai. Tự 9 mang lại 12 nằm trong page lắp thêm 3 với ID tự 13 mang đến 16 trực thuộc page thiết bị 4.

Các page này thể hiện không thiếu thốn dữ liệu của bảng và links hai chiều với nhau theo từng cặp nằm cạnh nhau hoặc có thể bất kì khoảng cách nào. Chỉ cần phải có liên kết là biểu đạt được trang bị tự của dữ liệu.

Phía bên trái bao gồm một page không giống chứa các giá trị 13, 9, 5 cùng NULL. Đây rất có thể xem là page mô tả rút gọn dữ liệu của cột ID.

Mỗi một mẫu trong page này thay mặt một tập tài liệu ở page mà nó links tới. Với page được liên kết tới này rất có thể lại chứa các dòng links tới phần đông page khác (nhiều cấp). Tất nhiên dữ liệu trong page phía trái là clustered key, vào hình 3 đó là các quý hiếm từ cột ID.

Cấu trúc tài liệu này gọi là B-Tree, nó để giúp đỡ SQL hệ thống xác định vị trí dòng dữ liệu cực cấp tốc vì không khí tìm kiếm sẽ sụt giảm đáng đề cập thông qua kết cấu này. Thử tìm lại nhân viên cấp dưới có ID bằng 5 xem.

Thay bởi vì duyệt từ trên đầu bảng như lúc nãy, giờ ta chỉ cần duyệt những dòng vào page bên trái. Xuất phát từ bất kì phía nào cho đến khi chạm mặt giá trị 5. Lần theo mặt đường dẫn link tới page nghỉ ngơi bên nên rồi để ý page này đã tìm thấy cực hiếm 5.

Vì họ khai báo clustered index key là unique nên việc tìm kiếm sẽ chấm dứt ngay khi tìm thấy chiếc dữ liệu có mức giá trị ID bằng 5. Nếu như không unique SQL Server vẫn tìm thêm làm việc page bên cạnh.

Có thể các bạn sẽ nói làm vì vậy cũng tiến hành nhiều phép so sánh, gần bằng duyệt từng loại trực tiếp trên bảng. Nhưng đấy là bảng có kích cỡ nhỏ, bạn hãy tưởng tượng nếu bảng tất cả một triệu pages thì cụ nào? thời hạn tìm tìm một dòng tài liệu khi kia không còn phụ thuộc vào vào số lượng dòng nhưng mà là độ cao của cây index.

Xem thêm: Sơ Đồ Đội Hình 4 231 Với - Sơ Đồ Phòng Thủ Chặc Chẽ Tấn Công Mạnh Mẽ 4

Đây chính là cách buổi giao lưu của index, nó sẽ giúp bọn họ định hướng tìm kiếm trong một tập dữ liệu nhỏ dại hơn và bởi đó sẽ sở hữu tốc độ cấp tốc hơn.

Cấu chế tạo ra của clustered index

Những page chứa dữ liệu của bảng như thế này gọi là leaf node, xét theo lever gọi là leaf cấp độ hay có cách gọi khác level 0. Cao hơn sẽ sở hữu level 1 với cứ tạo thêm 1 như vậy cho tới khi chứa đủ dữ liệu thì dừng.

Cấp cao nhất sẽ là root level. đầy đủ level ở giữa root và leaf thì gọi là intermediate level. Vào hình 3 không có intermediate level, chúng ta có thể xem minh họa nghỉ ngơi trang này.

Nếu leaf cấp độ (của clustered index) chứa tài liệu của bảng thì những level trên chứa chiếc gì? Những tài liệu này từ bỏ đâu nhưng mà có?

Mỗi page ở cấp độ trên cũng có thể có kích thước 8KB. Tài liệu được chứa trong này là clustered key của bảng, được call là index record. Kích thước của index record vẫn quyết định số lượng record gồm trong page.

Giả sử cột ID tất cả kiểu dữ liệu INT (4 bytes) cộng với các giá thành tổ chức tàng trữ nữa thành 11 bytes cho từng record. Với không gian 8096 bytes từng page rất có thể chứa tối đa 736 index records (tính toán tương đối). Khớp ứng với 736 pages ở cấp độ dưới.

Bây giờ đồng hồ ta thấy rõ rằng chu đáo 736 pages nhằm tìm một giá trị sẽ tốn thời gian hơn nhiều so với coi sóc 2 pages. Một page ở màn chơi 1 (root page) cùng 1 page ở cấp độ 0 (leaf page). Đây chủ yếu là bí quyết mà index hỗ trợ chúng ta tăng vận tốc tìm kiếm.

Chúng ta hãy cùng điều tra khảo sát clustered index ở bảng NhanVien nhằm kiểm chứng những điều trên. Sử dụng hai câu lệnh DBCC IND() cùng DBCC PAGE() như trả lời ở bài xích trước mình từng đề cập. (Mình sẽ cập nhật scripts tạo thành bảng NhanVien cùng insert dữ liệu sau)

*
Hình 4: danh sách data/index pages của clustered index bảng NhanVien

Các thông số từ trái qua phải tất cả tên database, tên bảng và index_id. Họ hãy để ý đến bảng kết quả. PagePID là ID của các page thuộc bảng NhanVien bên trên FileID 1 (vì các bảng có thể được lưu trên thuộc file và một bảng cũng hoàn toàn có thể được giữ trên nhiều file).

PageType 10 nằm trong system sử dụng để cai quản space của bảng ta tạm thời chưa quan tiền tâm. PageType 1 chính là data page chứa dữ liệu bảng NhanVien. PageType 2 là index page chứa index record, và cũng là root page trong ví dụ như của chúng ta.

Cột IndexLevel biểu hiện đúng như các gì bọn họ nói ở trên. Màn chơi 0 phải chăng nhất cũng là leaf level, vị trí chứa dữ liệu của bảng (data page). Màn chơi 1 cao nhất nên là root level. Hãy chú ý PagePID của root page vì họ sẽ điều tra khảo sát nội dung của nó.

Bốn page có level bằng 0 được link với nhị page lân cận thể hiện tại qua cột PrevPagePID và NextPagePID. Thuộc với thông tin FileID thông qua cột NextPageFID và PrevPageFID.

Tiếp theo, hãy cùng xem ngôn từ page root level gồm giống đông đảo gì bọn họ đã mô tả không nhé.

*
Hình 5: câu chữ page root của clustered index

Page root bao gồm ID 401 và câu chữ của nó tất cả 4 mẫu chứa key ID thứu tự NULL, 5, 9, 13. Mỗi mẫu này link tới page ở level 0 gồm pageID ở chột ChildPageId. Y như những gì chúng ta thấy trong hình 3.

Xem thêm: Intel Core 2 Duo Là Gì ? Khái Niệm Core I3, I5, I7 Là Gì? So Sánh Sự Khác Nhau

Vậy lúc tìm kiếm một giá trị, SQL server sẽ bắt đầu từ root page, lần theo quý giá trong page đó đi xuống những level thấp rộng và ở đầu cuối sẽ cho được page cất giá trị cần tìm. Thời hạn của việc tìm kiếm kiếm này phụ thuộc vào vào chiều cao của index (số lượng level). Và số lượng level dựa vào vào kích thước index key.

Tóm lại clustered index vào SQL Server tất cả các điểm lưu ý sau

Dữ liệu của bảng sẽ được sắp xếp theo thứ tự clustered keySử dụng cấu tạo B-Tree để tạo nên các cấp độ lưu trữ key hỗ trợ tìm kiếmIndex có level càng tốt thì việc đào bới tìm kiếm kiếm càng tốn thời hạn hơnLevel của index dựa vào vào độ lớn dữ liệu trong bảng và kích thước của index key