Cơ sở dữ liệu - Chương 7: Chỉ mục (index)

Cơ sở dữ liệu - Chương 7: Chỉ mục (index) Nếu không có chỉ mục, phải sử dụng phương pháp duyệt bảng SQL server cung cấp công cụ Database Engine Tuning Advior giúp phân tích môi trường và chọn index phù hợp

79 trang | Chia sẻ: huyhoang44 | Lượt xem: 701 | Lượt tải: 0

Bạn đang xem trước 20 trang tài liệu Cơ sở dữ liệu - Chương 7: Chỉ mục (index), để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên

GV Phi Loan - Khoa CNTT – HUI 1 GV Phi Loan - Khoa CNTT – HUI Nội dung • Cơ bản về chỉ mục • Chỉ mục: mục đích và các loại chỉ mục • Kế hoạch thực thi chỉ mục • Rebuild/ Reorganize chỉ mục 2 GV Phi Loan - Khoa CNTT – HUI Heap file • Môĩ ba ̉n ghi (record) đều có 1 mã nhâ ̣n da ̣ng duy nhất (unique identifier), go ̣i tắt là rid. • Tât́ cả các ba ̉n ghi được lưu trư ̃ theo thư ́ tự ngẫu nhiên (random order) vào file. • File không xếp thư ́ tự (unordered file) được go ̣i là heap file. Các ba ̉n ghi sẽ đuợc lưu trữ trong các trang (page) có cùng kích cỡ. 3 GV Phi Loan - Khoa CNTT – HUI Cá̂u trúc heap file 4 GV Phi Loan - Khoa CNTT – HUI Tìm kiêḿ và chỉ mục • Câú trúc của heap file không hỗ trợ nhiè̂u cho việc tìm kié̂m • Đẻ̂ tìm các bản ghi thỏa mãn 1 điè̂u kiện nào đó, ta cần phải tìm trên tất ca ̉ các trang dư ̃ liệu. • File chỉ mục là 1 cấu trúc file phụ đẻ̂ hỗ trợ việc tìm kié̂m rid của các bản ghi thỏa mãn điè̂u kiện dò tìm (search condition) 5 GV Phi Loan - Khoa CNTT – HUI Chỉ mục - Indexes • Mục đích: cải thiện việc truy tìm dữ liệu. • Ý tưởng: tương tự như index của sách. – Mỗi mục trong chỉ mục (index entry) sẽ chư ́a 1 thuật ngư ̃ (term) và 1 con tro ̉ (pointer) đẻ̂ chi ̉ đêń trang chư ́a thuật ngư ̃ đó trong quyẻ̂n sách. – Các entry được săṕ xé̂p theo thuật ngư ̃ đẻ̂ ta ̣o thành 1 bảng go ̣i là bảng chi ̉ mục. – Thay vì duyệt (scan) ca ̉ quyẻ̂n sách, chỉ câǹ truy xuất đêń bảng chi ̉ mục giúp đi ̣nh vị nhanh trang chư ́a thuật ngư ̃ cần tìm. 6 GV Phi Loan - Khoa CNTT – HUI Chỉ mục - Index • Index chứa: – 1 tập hợp các đầu vào chỉ mục (index entry) – Cơ chế dò tìm entry dựa vào giá trị search key • Các cơ chế dò tìm: – Các index entry được xếp theo search key như ISAM hay B+ tree – Hash index 7 GV Phi Loan - Khoa CNTT – HUI B-tree indexes • B-tree index nằm riêng trên như ̃ng trang index, có 1 mư ́c gốc (root level), mô ̣t hay nhiều mức trung gian (intermediate levels), và 1 mư ́c lá (leaf) hay mư ́c node. • Các cô ̣t được sắp xếp bởi b-tree index được go ̣i là cô ̣t khóa (key) của index. • Sự khác nhau giư ̃a chỉ mục clustered và non- clustered là số lượng và loa ̣i dư ̃ liệu được lưu trư ̃ ở mư ́c lá. 8 GV Phi Loan - Khoa CNTT – HUI Index và truy vá̂n tối ưu • Các index được thié̂t kế tốt sẽ giảm thao tác I/O đĩa và tiêu tôń tài nguyên ít hơn. • Các chỉ mục có thẻ̂ hô ̉ trợ cho nhiè̂u loa ̣i truy vâń chứa các lệnh SELECT, UPDATE, DELETE, và MERGE. • Khi thực thi truy vâń, query optimizer đánh giá và cho ̣n phương pháp nào hư ̃u hiệu nhât́ đê ̉ khôi phuc ̣ dư ̃ liệu – Duyệt qua toàn bô ̣ bảng (table scan) – Duyệt qua 1 hay nhiè̂u chỉ mục né̂u có. 9 GV Phi Loan - Khoa CNTT – HUI Index và truy vá̂n tối ưu • Khi duyệt toàn ba ̉ng, query optimizer đo ̣c tất ca ̉ các hàng trong ba ̉ng, và trích ra các hàng thỏa mãn điêù kiện truy vấn. • Việc duyệt ba ̉ng phát ra nhiè̂u thao tác I/O đĩa, tiêu tốn nhiều tài nguyên hơn. • Phương pháp duyệt ba ̉ng (table scan) có thẻ̂ là phương pháp hiê ̣u qua ̉ nhất nếu ba ̉ng kết qua ̉ của truy vấn chư ́a hầu hết các hàng có trong ba ̉ng. 10 GV Phi Loan - Khoa CNTT – HUI Index và truy vá̂n tối ưu • Khi query optimizer sử dụng index, nó dò tìm cột khóa của chỉ mục, tìm vị trí lưu trữ của hàng trong ba ̉ng và trích ra hàng dữ liê ̣u tho ̉a mãn điêù kiê ̣n truy vấn. • Viê ̣c dò tìm chi ̉ mục nhanh hơn nhiều so với duyệt ba ̉ng, vì chỉ mục chi ̉ chư ́a 1 số cột và các hàng trong chi ̉ mục đã được sắp xêṕ. 11 GV Phi Loan - Khoa CNTT – HUI Phân loại cô ̣t trong mê ̣nh đề WHERE • Cột bằng (Equality column): các cột xuá̂t hiện trong mệnh đề WHERE với dá̂u =. • Cột không bằng (Inequality column): các cô ̣t xuá̂t hiện trong mệnh đề WHERE với dá̂u (>) hay BETWEEN. 12 GV Phi Loan - Khoa CNTT – HUI Thiêt́ kế chỉ mục hiệu quả • Khi xây dựng chỉ mục trên nhiè̂u cột, cần phải liệt kê các cột băǹg trước khi liệt kê các cột không bằng. – Khi cho ̣n thư ́ tự các cột bằng, nên liệt kê cột hay cho ̣n nhiều nhất. – Sau khi liệt kê tất ca ̉ các cột bằng, tiếp tục liê ̣t kê các cột không bằng, nên liê ̣t kê cột không bằng hay chọn nhiè̂u nhất. 13 GV Phi Loan - Khoa CNTT – HUI Thiêt́ kế chỉ mục hiệu quả • Khi ké̂t nó̂i với 1 bảng khác, nên ta ̣o chỉ mục cho cô ̣t được dùng ké̂t nó̂i với ba ̉ng khác. 14 GV Phi Loan - Khoa CNTT – HUI Thiêt́ kế chỉ mục • Thié̂t kế chỉ mục không tốt, hoă ̣c không dùng chỉ mục đêù là nguyên nhân cơ ba ̉n cho viê ̣c “bottlenecks” cho các ứng dụng của database. • Chọn lựa đúng chi ̉ mục cần pha ̉i xét sự cân đôí giư ̃a tốc đô ̣ truy vấn và chi phí câ ̣p nhâ ̣t. • Các chỉ mục ít cô ̣t đòi ho ̉i không gian đĩa ít và chi phí ba ̉o trì thấp. • Các chỉ mục nhiều cô ̣t có thê ̉ hô ̉ trợ cho nhiều truy vấn hơn . 15 GV Phi Loan - Khoa CNTT – HUI Phân loại chỉ mục • Các loại chi ̉ mu ̣c trong SQL Server 2012: – Clustered – Non-clustered – Covering – Filtered – Primary XML – Secondary XML {Path, Property, Value} – Spatial – Full-text – Columnstore 16 GV Phi Loan - Khoa CNTT – HUI Clustered indexes – Clustered indexes sắp xé̂p và lưu trữ các hàng dư ̃ liê ̣u trong ba ̉ng hay view theo giá trị khóa cu ̉a index. – Môĩ ba ̉ng chỉ có duy nhất 1 clustered index – Hình a ̉nh tượng trưng của clustered index là telephone book 17 GV Phi Loan - Khoa CNTT – HUI Clustered indexes – Khi ba ̉ng có chứa clustered index thì các hàng cu ̉a ba ̉ng được xêṕ thư ́ tự. Ba ̉ng còn được go ̣i là clustered table. – Nêú ba ̉ng không chứa clustered index thì các hàng của ba ̉ng lưu trữ tự do và được gọi là heap 18 GV Phi Loan - Khoa CNTT – HUI unordered heap • Cũng có thẻ̂ tạo 1 ba ̉ng không câǹ có clustered index  dư ̃ liệu được lưu trư ̃ vào 1 heap không xé̂p thư ́ tự. Môĩ hàng sẽ được xác định bởi mã RowID của heap. • RowID là vị trí vâ ̣t l{ thực sự của hàng, gồm 3 giá trị: FieldID:PageNum:SlotNum, và không thê ̉ truy vâń trực tié̂p đến nó được. • Chỉ mục non-clustered indexes lưu trư ̃ RowID của heap thay vì lưu trữ khóa chỉ mục clustered. 19 GV Phi Loan - Khoa CNTT – HUI Ví dụ minh họa clustered index with an identity column as the clustered index key. The first name is the data column. 20 GV Phi Loan - Khoa CNTT – HUI 21 GV Phi Loan - Khoa CNTT – HUI Nonclustered indexes – Chi ̉ mục nonclustered tách riêng kho ̉i ba ̉ng dữ liê ̣u. – Chi ̉ mục nonclustered chứa các giá trị khóa và mỗi giá tri ̣ khóa có 1 con trỏ (pointer) trỏ đến hàng dư ̃ liê ̣u chư ́a giá trị khóa đó. Con tro ̉ này được gọi là row locator. – Trong SQL Server 2008, mô ̣t ba ̉ng có thê ̉ có tới 999 nonclustered index 22 GV Phi Loan - Khoa CNTT – HUI Nonclustered indexes –Cấu trúc của row locator phụ thuô ̣c vào các trang dư ̃ liê ̣u được lưu trư ̃ trong heap hay trong ba ̉ng clustered. • Né̂u trong heap, row locator là 1 con tro ̉ tro ̉ đêń RowID cu ̉a heap • Nếu trong ba ̉ng clustered, row locator là khóa chỉ mục clustered –Hình a ̉nh tượng trưng của nonclustered index là ba ̉ng chỉ mu ̣c nằm cuối sách 23 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ minh họa nonclustered index first name as the key column. The non-clustered index includes pointers to the clustered index key column.24 GV Phi Loan - Khoa CNTT – HUI 25 GV Phi Loan - Khoa CNTT – HUI Covering Indexes • Né̂u chỉ mục non-clustered chứa thêm các thông tin cần thié̂t cho 1 truy vâń được gọi là covering index. • Dùng covering indexes được xem như chiến lược chính để cải thiện việc thực thi truy vá̂n. 26 GV Phi Loan - Khoa CNTT – HUI Covering Indexes • Nhờ đưa thêm thông tin vào chi ̉ mục non-clustered mà tránh được thao tác look up. – Look up: thao tác mà hệ thống sau khi tìm kiếm trên cây index xong, pha ̉i nhảy tới bản ghi tương ứng trong bảng dư ̃ liê ̣u để lấy các trường dữ liệu cần trả về 27 GV Phi Loan - Khoa CNTT – HUI Ví dụ USE AdventureWorks GO SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE EmailAddress = 'kristina1@adventure.com' 28 GV Phi Loan - Khoa CNTT – HUI Ví dụ CREATE NONCLUSTERED INDEX Contact_EmailAddress ON Person.Contact(EmailAddress) INCLUDE (FirstName,LastName) • Né̂u ta ̣o chỉ mục icovering như trên sẽ tránh được chi phí look up • Lệnh trên vẫn tạo index trên trường EmailAddress, nhưng đồng thời “ký gửi” hai trường FirstName và LastName vào đó. 29 GV Phi Loan - Khoa CNTT – HUI Ví dụ • Key Lookup đã biến mất, bước truy nhập vào bảng dữ liệu đã bị loại bỏ, vì tất cả các trường dữ liệu mà câu lệnh yêu cầu đã được tìm thấy ngay tại index. Điều này cũng có nghĩa là chi phí câu lệnh được giảm đi một nửa 30 GV Phi Loan - Khoa CNTT – HUI Thao tác truy vá̂n • SQL Server sử dụng 3 thao tác chính đẻ̂ tìm dữ liệu: – Table scan: đo ̣c ca ̉ heap – Index scan: đo ̣c toàn bô ̣ mức lá của chỉ mục clustered hay non-clustered – Index seek: định vị hàng dư ̃ liê ̣u thông qua b-tree 31 GV Phi Loan - Khoa CNTT – HUI Bookmark lookup • Đôí vơ ́i các truy vâń không thươ ̀ng xuyên, tìm kié̂m theo bookmark rất phù hợp nhưng với các truy vấn tiêu tó̂n nhiè̂u CPU thì kiẻ̂u tìm kié̂m này không phù hợp. 32 GV Phi Loan - Khoa CNTT – HUI Bookmark lookup SELECT * FROM Production.WorkOrder WHERE ProductID = 757; Tìm các hàng có mã ProductID là 757 và tra ̉ vê ̀tât́ cả cột cho các hàng này. Ba ̉ng WorkOrder có chỉ mục clustered trên cột WorkOrderID 33 GV Phi Loan - Khoa CNTT – HUI Bookmark lookup • Query Optimizer có 2 tùy cho ̣n sau đẻ̂ thực thi: – Option 1: Duyệt toàn bô ̣ chỉ mục clustered đẻ̂ truy xuất đêń tất ca ̉ các cột, sau đó lọc kêt́ qua ̉ đề tìm ra các hàng tho ̉a mãn điều kiê ̣n dò tìm. 34 GV Phi Loan - Khoa CNTT – HUI Bookmark lookup – Option 2: Thực hiện dò tìm chỉ mục trên ba ̉ng chỉ mu ̣c IX_Workload_ProductID đê ̉ tìm ra 11 hàng tho ̉a mãn điều kiê ̣n. Trong quá trình tìm kiếm, optimizer biết được giá trị của WorkOrderID của 11 hàng này (vì cô ̣t khóa chỉ mục clustered đêù nằm ở mư ́c lá của chỉ mu ̣c non-clustered). Sau đó nó có thê ̉ dò tìm theo giá trị của 11 hàng này tư ̀ chỉ mục clustered đẻ̂ tìm các cột khác. 35 GV Phi Loan - Khoa CNTT – HUI Bookmark lookup • Option 2 nha ̉y tư ̀ chỉ mục non-clustered tìm hàng sang chỉ mục clustered đẻ̂ lấy toàn bô ̣ các cột cần thiết cho truy vấn được gọi là bookmark lookup 36 GV Phi Loan - Khoa CNTT – HUI Chỉ mục • Đa số các DBMS đều tạo chỉ mục tự động cho các trường primary key  bảng chỉ mục được tích hợp vào bảng dữ liệu. • Các chỉ mục trên các trường khác được lưu vào bảng chỉ mục. 37 GV Phi Loan - Khoa CNTT – HUI Chỉ mục trong SQL Server • Chỉ mục clustered còn được gọi là chỉ mục sơ cấp ( primary index) hay main index • Unclustered index thường được gọi là secondary index • Thường thì với mỗi bảng chỉ có 1 clustered index và có thể có nhiều unclustered index 38 GV Phi Loan - Khoa CNTT – HUI Index và truy vá̂n tối ưu • Né̂u không có chỉ mục, query optimizer pha ̉i sử dụng phương pháp duyệt ba ̉ng • SQL Server cung cấp công cụ Database Engine Tuning Advisor giúp phân tích môi trường database và cho ̣n index phù hợp. 39 GV Phi Loan - Khoa CNTT – HUI Chỉ mục phư ́c (composite index) • Chi ̉ mục phư ́c có thẻ̂ là clustered hay non- clustered mà cô ̣t khóa của nó gồm nhiêù cô ̣t. • Thực tế chỉ mu ̣c phức rá̂t thông dụng. • Thư ́ tự các cột trong chi ̉ mục phư ́c là quan tro ̣ng. Đẻ̂ sử dụng chỉ mục phư ́c, điêù kiê ̣n dò tìm pha ̉i bao gôm̀ các cột chỉ mục từ trái sang pha ̉i. 40 GV Phi Loan - Khoa CNTT – HUI Chỉ mục phư ́c (composite index) • Ví du ̣: né̂u có 1 chi ̉ mục phức mà khóa bao gôm̀ lastname, firstname, viê ̣c dò tìm theo firstname sẽ không thê ̉ nhanh được nếu dùng chỉ mục, nhưng nếu dò tìm theo lastname, hay lastname và firstname thì chỉ mu ̣c sẽ được sử dụng rất hiệu qua ̉. 41 GV Phi Loan - Khoa CNTT – HUI Chỉ mục và các ràng buộc • Chỉ mục unique clustered được ta ̣o tự đô ̣ng khi các ràng buô ̣c PRIMARY KEY và UNIQUE được ta ̣o. Tuy nhiên vâñ có thẻ̂ ta ̣o chỉ mục unique là non- clustered. • Thực tế ràng buô ̣c unique và chỉ mục unique chỉ là mô ̣t, chỉ câǹ ta ̣o 1 trong loa ̣i. • Sụ khác nhau cơ ba ̉n giữa unique constraint/index và primary key là primary key không cho phép giá trị null, còn unique constraint/index cho phép 1 giá trị null. 42 GV Phi Loan - Khoa CNTT – HUI Lê ̣nh tạo chỉ mục • Ví dụ : Create index idxname ON Customer (FirstName, LastName) 43 GV Phi Loan - Khoa CNTT – HUI Lê ̣nh tạo chỉ mục 44 GV Phi Loan - Khoa CNTT – HUI Fill factor • Khi chỉ mục được ta ̣o hay rebuild , giá trị fill-factor xác định phâǹ trăm không gian trên mõ̂i trang mư ́c lá sẽ chư ́a dữ liệu, đẻ̂ dành phâǹ còn lại trên mõ̂i trang đẻ̂ mở rộng trong tương lai. • Ví dụ: né̂u fill-factor là 80 có nghĩa là 20% của mõ̂i trang mư ́c lá sẽ được đẻ̂ tró̂ng 45 GV Phi Loan - Khoa CNTT – HUI Fill factor • Né̂u khóa chỉ mục clustered là IDENTITY, né̂u xác định fill factor <100 sẽ làm cho các trang mư ́c lá cu ̉a index luôn tró̂ng vì các hàng mơ ́i luôn được vié̂t vào trang cuó̂i. • Né̂u khóa chỉ mục clustered với giá trị bât́ kz, cho ̣n thừa số fill factor <100 sẽ hạn chê ́việc phân trang 46 GV Phi Loan - Khoa CNTT – HUI Page Splits • Chọn giá trị fill-factor phù hợp sẽ ha ̣n ché̂ được tình tra ̣ng phân trang (page split). • Khi 1 hàng mới được thêm vào 1 trang index đã đầy, Database Engine sẽ chuyê ̉n ½ số hàng sang trang mới  viê ̣c phân trang sẽ tạo chõ̂ tró̂ng đẻ̂ thêm hàng mới nhưng sẽ mât́ thời gian thực hiê ̣n và hao tốn tài nguyên. Bảng index sẽ pha ̉i rebuilt đê ̉ phân bố la ̣i dữ liê ̣u. 47 GV Phi Loan - Khoa CNTT – HUI Ví dụ USE AdventureWorks2012; GO CREATE INDEX IX_Emp_OrgaLevel_OrgNode ON HumanResources.Employee (OrganizationLevel, OrganizationNode) WITH (DROP_EXISTING = ON, FILLFACTOR = 80); GO 48 GV Phi Loan - Khoa CNTT – HUI Phân mảnh (Fragmentation) • Khi các trang index mà thứ tự logic cuả các giá trị khóa không còn trùng với thứ tự vật lý trong file dữ liệu được gọi là phân mảnh. • Khi mới đuợc tạo, index chưa bi ̣ phân mảnh, sau 1 thời gian, do dữ liệu bị sửa đô ̉i, index trở nên phân mảnh. 49 GV Phi Loan - Khoa CNTT – HUI Kiểm tra tình trạng phân mảnh • Cách 1: Mở property của index câǹ kiẻ̂m tra, chọn trang Fragmentation • Cách 2: – Dùng hàm hê ̣ thó̂ng sys.dm_db_index_physical_stats sys.dm_db_index_physical_stats ( { database_id | NULL | 0 | DEFAULT } , { object_id | NULL | 0 | DEFAULT } , { index_id | NULL | 0 | -1 | DEFAULT } , { partition_number | NULL | 0 | DEFAULT } , { mode | NULL | DEFAULT } ) 50 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ Kiê ̉m tra tình trạng phân mảnh USE AdventureWorks2008; GO SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GO 51 GV Phi Loan - Khoa CNTT – HUI Chỉ tiêu kỹ thuật về phân mảnh • Né̂u mức độ phân mảnh >30% nên rebuild la ̣i index • Nè̂u mức phân mảnh <10% :index chưa bị phân mảnh đáng kẻ̂ 52 GV Phi Loan - Khoa CNTT – HUI Reorganize or rebuild an index • Dùng Management Studio • Dùng T-SQL 53 GV Phi Loan - Khoa CNTT – HUI Reorganize an index • Dùng Management Studio – Đẻ̂ reorganize 1 index: nhấp pha ̉i ta ̣i inddex, chọn lệnh Reorganize – Đẻ̂ reorganize tất ca ̉ index của 1 ba ̉ng: nhấp pha ̉i ta ̣i folder indexes, chọn lê ̣nh Reorganize All 54 GV Phi Loan - Khoa CNTT – HUI Reorganize indexes • Dùng T-SQL USE AdventureWorks2008; GO -- Reorganize an index ALTER INDEX IX_Emp_OrgLevel_OrgNode ON HumanResources.Employee REORGANIZE ; GO -- Reorganize all indexes on a table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO 55 GV Phi Loan - Khoa CNTT – HUI Rebuild an index • Dùng Management Studio – Đẻ̂ rebuild 1 index: nhấp pha ̉i ta ̣i inddex, chọn lệnh Rebuild – Đẻ̂ rebuild tất ca ̉ index của 1 ba ̉ng: nhấp pha ̉i ta ̣i folder indexes, chọn lê ̣nh Rebuild All 56 GV Phi Loan - Khoa CNTT – HUI Rebuild indexes • Dùng T-SQL USE AdventureWorks2008; GO -- Rebuild an index ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO -- Reorganize all indexes on a table. ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO 57 GV Phi Loan - Khoa CNTT – HUI Theo dõi các chỉ mục đang thiêú • Mõ̂i lâǹ query optimizer biên dịch 1 lệnh T-SQL, SQL Server 2012 theo dõi khoa ̉ng 500 chỉ mục mơ ́i nhất hiện có mà optimizer có thẻ̂ đã dùng đẻ̂ thực thi truy vâń hiệu quả hơn. – Nhờ vào 3 DMV (dynamic management view) và 1 hàm DMF (dynamic management function) 58 GV Phi Loan - Khoa CNTT – HUI Theo dõi các chỉ mục đang thiêú 59 GV Phi Loan - Khoa CNTT – HUI Ví dụ • Lệnh sau liệt kê các index bị thié̂u có thẻ̂ có lợi cho các T-SQL batches đã được dùng ( đã gửi vê ̀cho query optimizer) kẻ̂ tư ̀ lần cuó̂i dịch vụ SQL Server được khởi động lại: 60 GV Phi Loan - Khoa CNTT – HUI Ví dụ 61 GV Phi Loan - Khoa CNTT – HUI Duyệt lại các index không dùng • Có như ̃ng chỉ mục được định nghĩa cho các ba ̉ng nhưng la ̣i không bao giờ được dùng đêń. • Môĩ khi 1 ba ̉ng được chèn, câ ̣p nhâ ̣t hay xóa, thì các chỉ mu ̣c liên quan đến ba ̉ng cũng sẽ được ba ̉o trì đê ̉ pha ̉n ánh các thay đỏ̂i này. – Né̂u quá nhiè̂u index mà query optimizer sẽ không dùng đến làm ảnh hưởng đến việc thực thi. • Duyệt có định kz đẻ̂ tìm các index không được dùng đêń. 62 GV Phi Loan - Khoa CNTT – HUI Duyệt lại các index không dùng • Không nên xóa các chỉ mục không dùng đêń mà chi ̉ nên disable. • Nêú sau này cần dùng đêń chỉ cần rebuild la ̣i index đó. • Ví dụ: lê ̣nh sau chỉ ra số thao tác được thực thi cùng với index, đôǹg thời chỉ ra việc sử dụng index so với chi phí cu ̉a index đó. 63 GV Phi Loan - Khoa CNTT – HUI Ví dụ 64 GV Phi Loan - Khoa CNTT – HUI Index with included column • Chi ̉ mục có cột không khoá included có thẻ̂ ca ̉i thiê ̣n đáng kê ̉ tốc đô ̣ thực thi truy vấn khi tất ca ̉ cột trong truy vấn đêù có mă ̣t trong chỉ mục. Query optimizer có thê ̉ định vị tất ca ̉ giá trị cô ̣t ngay bên trong truy vấn, không cần truy vâń đêń ba ̉ng dư ̃ liệu nư ̃a  số thao tác I/O gia ̉m. 65 GV Phi Loan - Khoa CNTT – HUI Index with included column • Chỉ mục nonclustered bị hạn ché̂ về kích cỡ: – Số cô ̣t khóa tối đa là 16 – Kích cỡ khóa chỉ mục tó̂i đa là 900 bytes 66 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ về giơ ́i hạn kích cơ ̃ cu ̉a chi ̉ mục • Gia ̉ sử muó̂n ta ̣o chỉ mục cho 3 cột sau trong ba ̉ng Document của DB AdventureWorks – Title nvarchar(50) – Revision nchar(5) – FileName nvarchar(400) 67 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ về giơ ́i hạn kích cơ ̃ cu ̉a chi ̉ mục • Chỉ mục chứa 3 cột này vượt quá 900 byte. Đẻ̂ khắc phục hạn chế này nên ta ̣o chỉ mục có trường không khoá được INCLUDE vào. CREATE INDEX IX_Document_Title ON Production.Document (Title, Revision) INCLUDE (FileName); 68 GV Phi Loan - Khoa CNTT – HUI Ví dụ về thiết kế chỉ mục • Giả sử có truy vâń sau: USE AdventureWorks; GO SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address WHERE PostalCode BETWEEN N'98000' and N'99999'; Hãy thié̂t kế chỉ mục hỗ trợ truy vâń này?? 69 GV Phi Loan - Khoa CNTT – HUI Ví dụ về thiết kế chỉ mục CREATE INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); 70 GV Phi Loan - Khoa CNTT – HUI Bất lợi của index • Chiếm không gian đĩa • Nếu index lớn thì các trang index cần được đọc vào bộ nhớ tó̂n chi phí cho thao tác vào ra (I/O) • Index cần được bảo trì (maintenance): các chỉ mục phải được sửa đổi cùng với sự thay đổi của dữ liệu 71 GV Phi Loan - Khoa CNTT – HUI Tính toán không gian đĩa • Khi index được ta ̣o, cần dùng không gian đĩa: – Lưu trư ̃ cấu trúc cũ và mới. Cấu trúc cũ vẫn chưa bị hu ̉y bo ̉ chừng nào transaction ta ̣o index chưa được commit. – Không gian ta ̣m dùng cho thao tác sắp xếp 72 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ tính toán không gian đĩa dùng cho index • Gia ̉ sử trước khi ta ̣o index, một CSDL thỏa mãn các điè̂u kiện sau: – Bảng chư ́a 1 triệu row, mỗi row chié̂m 200 byte. – Chỉ mục nonclustered A chư ́a 1 triệu row. Mỗi row dài 50 byte. – Chi ̉ mục nonclustered B chứa 1 triệu row. Mỗi row dài 80 byte. – Bô ̣ nhớ dành cho việc ta ̣o index là 2 MB. – Giá trị fill factor là 80 dùng cho tất cả index cũ và mơ ́i  Các trang đâỳ 80 %. 73 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ tính toán không gian đĩa dùng cho index (Offline Index Operation) • Xác định kích cơ ̃ của cấu trúc nguò̂n • Xác định kích cơ ̃ của cấu trúc index • Xác định không gian tạm dùng đẻ̂ sắp xé̂p 74 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ tính toán không gian đĩa dùng cho index (Offline Index Operation) • Xác đi ̣nh kích cỡ cu ̉a cá̂u trúc nguồn – Heap: 1 million * 200 bytes ~ 200 MB – Nonclustered index A: 1 million * 50 bytes / 80% ~ 63 MB – Nonclustered index B: 1 million * 80 bytes / 80% ~ 100 MB  Tỏ̂ng kích cỡ cho cấu trúc hiện có là 363 MB 75 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ tính toán không gian đĩa dùng cho index (Offline Index Operation) • Xác đi ̣nh kích cỡ cu ̉a cá̂u trúc index – Clustered index: 1 million * 200 bytes / 80% ~ 250 MB – Nonclustered index A: 1 million * (50 – 8 + 24) bytes / 80% ~ 83 MB – Nonclustered index B: 1 million * (80 – 8 + 24) bytes / 80% ~ 120 MB Tô ̉ng kích cỡ cu ̉a cấu trúc mới là 453 MB Tô ̉ng không gian đĩa cho cả cấu trúc nguồn và chi ̉ mục là 816 MB (363 + 453). 76 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ tính toán không gian đĩa dùng cho index (Offline Index Operation) • Xác đi ̣nh không gian tạm dùng để sắp xêṕ – Khi SORT_IN_TEMPDB được đă ̣t là ON, tempdb pha ̉i có đủ không gian đĩa dành cho chỉ mục lơ ́n nhất (1 million * 200 bytes ~ 200 MB). Fill factor không được xét đêń trong lúc sắp xếp. Không gian phu ̣ dành riêng cho tempdb là 2 MB. Tỏ̂ng không gian phụ là 202 MB. 77 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ tính toán không gian đĩa dùng cho index (Offline Index Operation) • Xác đi ̣nh không gian tạm dùng để sắp xêṕ – Khi SORT_IN_TEMPDB được đă ̣t là OFF (default), 250 MB không gian đĩa đã được dùng trong bước 2 sẽ được dùng sắp xêṕ. Không gian phu ̣ dành riêng cho tempdb là 2 MB.  Tô ̉ng không gian phu ̣ là 2 MB. 78 GV Phi Loan - Khoa CNTT – HUI Ví du ̣ tính toán không gian đĩa dùng cho index (Offline Index Operation) • Xác định kích cơ ̃ của cấu trúc nguò̂n – Heap: 1 million * 200 bytes ~ 200 MB – Nonclustered index A: 1 million * 50 bytes / 80% ~ 63 MB – Nonclustered index B: 1 million * 80 bytes / 80% ~ 100 MB  Tỏ̂ng kích cỡ cho cấu trúc hiện có là 363 MB 79

Các file đính kèm theo tài liệu này:

chuong_7_ktdbms_0488.pdf