BI 6 CH MC 1 Ni dung 1

BÀI 6 CHỈ MỤC 1

Nội dung 1. Chỉ mục 1. Giới thiệu chỉ mục 2. Fill Factor 2. Các kiểu chỉ mục 1. Cluster Index 2. Non-Cluster Index 3. Đặc điểm của Index 4. Sử dụng và xóa Index 2

Chỉ mục INDEX Cơ bản về chỉ mục (Index) n n Chỉ mục thường gắn kết với 1 bảng hay view để tăng tốc việc tìm kiếm hay khôi phục các hàng trong bảng hay view đó. Chỉ mục chứa các khóa (key) được trích từ 1 hay nhiều cột trong bảng dữ liệu hay view. Các khóa này được xếp thứ tự trong cấu trúc B-tree nhờ đó giúp SQL Server tìm kiếm nhanh chóng và hiệu quả các hàng trong bảng dữ liệu thông qua các giá trị khóa của index. 3

Chỉ mục INDEX Cơ bản về chỉ mục n n n Chỉ mục được tạo ra dựa theo các giá trị được xếp thứ tự từ 1 hay nhiều cột được chọn. Chỉ mục được tạo tự động bất cứ lúc nào ta xác định khoá chính hay ràng buộc unique. Có hai loại chỉ mục: n Clustered n Non - Clustered Cả hai đều là B-tree index Mỗi bảng chỉ có thể có duy nhất 1 chỉ mục clustered nhưng không bắt buộc là phải có chỉ mục này. Một bảng có thể có tới 249 chỉ mục nonclustered 4

Chỉ mục INDEX Mục đích sử dụng chỉ mục n Cải thiện việc thực thi khi sắp xếp hay nhóm dữ liệu n Cải thiện việc thực thi các truy vấn có kết nối giữa các bảng n Cải thiện tính duy nhất của 1 cột hay nhiều cột 5

Chỉ mục INDEX n Nếu bảng không dùng chỉ mục: n n Các hàng không lưu trữ theo 1 thứ tự đặc biệt nào. Các trang dữ liệu cũng không sắp xếp tuần tự. 6

Chỉ mục INDEX 7

Chỉ mục INDEX Khóa Index được tạo từ cột emp_id của bảng employees. Khi tìm kiếm một giá trị trong cột dữ liệu, mà cột này tham gia tạo khóa Index, đầu tiên câu lệnh xác định vị trí của giá trị nằm trong khóa Index bằng phép duyệt cây, sau đó thực hiện tìm theo liên kết đến bản ghi chứa giá trị tương ứng với khóa trong bảng. 8

Chỉ mục INDEX n n n Việc thiết kế khóa Index dựa trên nhu cầu truy vấn, chèn dữ liệu Cột thường được sử dụng làm khóa truy vấn dữ liệu (xác định cột tham gia bảng, xác định dựa vào một số tham số sau: khóa Index). Tập lệnh thường sử dụng truy vấn cần tốc độ cao (xác định tập cột tham gia truy vấn). Dữ liệu nhập vào bảng có khóa Index cần nhanh hơn hay truy vấn cần nhanh hơn (xác định đặt clustered hoặc nonclustered). Lượng dữ liệu nhập đồng loạt nhiều hay ít (xác định tham số fillfactor). 9

B-tree INDEX n n n B-tree index nằm riêng trên những trang index, có 1 mức gốc (root level), một hay nhiều mức trung gian (intermediate levels), và 1 mức lá (leaf) hay mức node. Các cột được sắp xếp bởi b-tree index được gọi là cột (key) của index. Sự khác nhau giữa chỉ mục clustered và non-clustered là số lượng và loại dữ liệu được lưu trữ ở mức lá. 10

Chỉ mục INDEX Clustered indexes n Clustered indexes sắp xếp và lưu trữ các hàng dữ liệu trong bảng hay view theo giá trị khóa của index. n Mỗi bảng chỉ có duy nhất 1 clustered index n Hình ảnh tượng trưng của clustered index là telephone book n Khi bảng có chứa clustered index thì các hàng của bảng được xếp thứ tự. Bảng còn được gọi là clustered table n Nếu bảng không chứa clustered index thì các hàng của bảng lưu trữ tự do và được gọi là heap. 11

Chỉ mục INDEX Clustered indexes n Khi khóa đặt thuộc tính Clustered, dữ liệu của bảng sẽ được sắp xếp vật lý trên đĩa, như vậy khi thiết kế khóa dạng này dữ liệu được chèn và sẽ tìm đúng vị trí trên đĩa để lưu trữ (vùng đĩa dành cho bảng dữ liệu), chính vì vậy mà có thể xảy ra trường hợp phải dịch chuyển danh sách các giá trị đã có ở đĩa. n Những việc tạo khóa Index dạng này sẽ không cần sắp xếp giá trị ở dạng logic mà khi truy nhập đĩa đã bảo đảm dữ liệu được sắo xếp. 12

Chỉ mục INDEX Clustered indexes n Cũng có thể tạo 1 bảng không cần có Clustered indexes dữ liệu được lưu trữ vào 1 heap không xếp thứ tự. Mỗi hàng sẽ xác định bởi mã Row. ID của Heap. n Row. ID là vị trí vật lý thực sự của hàng, gồm 3 giá trị: Field. ID: Page. Num: Slot. Num, và không thể truy vấn trực tiếp đến nó được. n Chỉ mục non-clustered indexes lưu trữ Row. ID của Heap thay vì lưu trữ khóa chỉ mục clustered. 13

Chỉ mục INDEX Clustered indexes 14

Chỉ mục INDEX Non. Clustered indexes n Dữ liệu Index không sắp xếp ở dạng vật lý mà chỉ sắp xếp logic, dữ liệu của bảng lưu trữ giá trị khóa Index được sắp xếp, nhanh trong nhập dữ liệu. n Chỉ mục nonclustered tách riêng khỏi bảng dữ liệu. n Chỉ mục nonclustered chứa các giá trị khóa và mỗi giá trị khóa có 1 con trỏ (ponter) trỏ đến hàng dữ liệu chứa giá trị khóa đó. Con trỏ này được gọi là row locator. n Trong SQL Server 2008, một bảng có thể có tới 999 nonclutered index. 15

Chỉ mục INDEX Non. Clustered indexes n Cấu trúc của row locator phụ thuộc vào các trang dữ liệu được lưu trữ trong heap hay trong bảng clustered. n Nếu trong heap, row locator là 1 con trỏ đến row. ID của Heap n Nếu trong bảng clustered, row locator là khóa chỉ mục clustered. n Hình ảnh tượng trưng của nonclustered index là bảng chỉ mục nằm cuối sách. 16

Chỉ mục INDEX Non. Clustered indexes 17

Chỉ mục INDEX Cách tạo chỉ mục CREATE [UNIQUE] [CLUSTERED] [NONCLUSTERED] INDEX Index_name ON Table (Column_name) [WITH [PAD_INDEX] [FILLFACTOR = fillfactor] [[, ] DROP_EXISTING]] 18

Chỉ mục Clustered và Nonclustered n Chỉ mục clustered: n Dữ liệu được sắp xếp vật lý. n Chỉ có 1 chỉ mục clustered trong mỗi bảng. n Tương tự như danh bạ điện thoại (telephone directory) trong đó dữ liệu được sắp xếp bởi tên thuê bao Ví dụ CREATE CLUSTERED INDEX Customerid_ndx ON Orders (Customerid) 19

Chỉ mục Clustered và Nonclustered n Chỉ mục nonclustered: là chỉ mục có cấu trúc riêng biệt độc lập với thứ tự vật lý của bảng dữ liệu. n n Thứ tự vật lý của chỉ mục nonclustered không trùng với thứ tự các bản ghi trong bảng dữ liệu Tương tự như chỉ mục trong textbook Nên tạo chỉ mục clustered trước khi tạo chỉ mục nonclustered để chỉ mục nonclustered không cần phải tạo lại. Chỉ mục clustered thực thi nhanh hơn chỉ mục nonclustered. CREATE NONCLUSTERED INDEX Manv_ndx ON Nhanvien (Manv) 20

Xem - Xóa chỉ mục Xem chỉ mục Cú pháp Example sp_helpindex <table_name> sp_helpindex Customers Xóa chỉ mục Cú pháp sp_helpindex <table_name> Example sp_helpindex Customers 21

Chỉ mục INDEX Unique Index n Xác định dữ liệu của cột tham gia khóa Index không lặp lại. Fill Factor n Khi tạo khóa Index, dữ liệu tham giá tạo khóa Index sẽ được phân theo mức của B-Tree, các mức được phần theo page dữ liệu, giá trị Fill factor xác định phần khoảng trống tối đa của page theo tỷ lệ phần trăm. Nhờ khoảng trống này mà tốc độ bố trí cấu trúc Index, tốc độ truy lục thông tin trong cây được cải thiện. Chỉ mục Composite n Cho phép hai hay nhiều cột được sử dụng để tạo chỉ mục 22

Creating Unique Indexes USE library CREATE UNIQUE INDEX title_ident ON title (title_no) title_no title author synopsis 10 11 12 12 The Night-Born Lemon Walking Jack London ~~~ Motojirou ~~~ Henry David. Thoreau ~ ~~ ~~ Henry Duplicate key values are not allowed when a new row is added to the table 12 Le Petit Prince Antoine de Saint-Exupery ~~~

Creating Composite Indexes USE library CREATE UNIQUE INDEX loan_ident ON loan (isbn, copy_no) loan isbn 342 343 copy_no title_no 5 10 4 35 35 35 Column 1 Composite Key Column 2 member_no out_date 3744 5278 3445 1998 -01 -06 1998 -01 -04

Using the FILLFACTOR Option Specifies how much to fill the page n Impacts leaf-level pages n Data Pages Full Con Funk White Rudd White Barr . . . 470401. . . 470402. . . 470403. . . 470501. . . 470502. . . 470503 Akhtar Funk Smith Martin Smith Ota . . . 470601. . . 470602. . . 470603. . . 470604. . . 470701. . . 470702 Martin Phua Jones Smith Ganio Jones . . . 470801. . . 470802. . . 470803. . . 470804. . . 470901. . . 470902 Data Pages 50% Fillfactor Con. . . 470401 Rudd. . . 470501 Akhtar. . . 470601 Martin. . . 470604 Martin. . . 470801 Smith. . . 470804 Funk. . . 470402 White. . . 470502 Funk. . . 470402 Smith. . . 470701 Phua. . . 470802 Ganio. . . 470901 White. . . 470403 Barr. . . 470503 Smith. . . 470603 Ota. . . 470702 Jones. . . 470803 White. . . 470902

INDEX và truy vấn tối ưu n n n Các Index được thiết kế tốt sẽ giảm thao tác I/O đĩa và tiêu tốn tài nguyên ít hơn. Các chỉ mục có thể hỗ trợ cho nhiều loại truy vấn chứa các lệnh SELECT, UPDATE, DELETE và MERGE. Khi thực hiện truy vấn, query optimizer đánh giá và chọn phương pháp nào hữu hiệu nhất để khôi phục dữ liệu Duyệt qua toàn bộ bảng (table scan) Duyệt qua một hay nhiều chỉ mục nếu có 26