Bài giảng Kiến trúc cài đặt cơ sở dữ liệu

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

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ỡ.

GV Phi Loan - Khoa CNTT – HUI

Cấu trúc heap file

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)

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.

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

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á.

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́.

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.

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.

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.

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ằng 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.

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.

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 .

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

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

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

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.

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.

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

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

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. GV Phi Loan - Khoa CNTT – HUI

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.

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ề

GV Phi Loan - Khoa CNTT – HUI

Ví dụ USE AdventureWorks GO SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE EmailAddress = 'kristina1@adventure.com'

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 đó.

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

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

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.

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

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.

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.

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

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.

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

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.

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.

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̉.

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.

GV Phi Loan - Khoa CNTT – HUI

Lệnh tạo chỉ mục

• Ví dụ : Create index idxname ON Customer (FirstName, LastName)

GV Phi Loan - Khoa CNTT – HUI

Lệnh tạo chỉ mục

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

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

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.

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

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.

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 } )

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;

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̂̉

GV Phi Loan - Khoa CNTT – HUI

Reorganize or rebuild an index

• Dùng Management Studio • Dùng T-SQL

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

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 ;

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

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);

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)

GV Phi Loan - Khoa CNTT – HUI

Theo dõi các chỉ mục đang thiếu

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:

GV Phi Loan - Khoa CNTT – HUI

Ví dụ

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.

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̂̀ng thời chỉ ra việc sử dụng index so với chi phí của index đó.

GV Phi Loan - Khoa CNTT – HUI

Ví dụ

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.

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

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)

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);

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??

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);

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

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

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 %.

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

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

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).

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.

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.

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

GV Phi Loan - Khoa CNTT – HUI

Bài giảng Kiến trúc cài đặt cơ sở dữ liệu - Chương 7: Chỉ mục (Index)

Nội dung

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ự

Cấu trúc heap file

Tìm kiếm và chỉ mục

Chỉ mục - Indexes

Chỉ mục - Index

B-tree indexes

Index và truy vấn tối ưu

Index và truy vấn tối ưu

Index và truy vấn tối ưu

Phân loại cột trong mệnh đề WHERE

Thiết kế chỉ mục hiệu quả

Thiết kế chỉ mục hiệu quả

Thiết kế chỉ mục

Phân loại chỉ mục

Clustered indexes

Clustered indexes

unordered heap

Ví dụ minh họa clustered index

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.

Ví dụ minh họa nonclustered index

Covering Indexes

Covering Indexes

Ví dụ USE AdventureWorks GO SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE EmailAddress = 'kristina1@adventure.com'

Ví dụ

Ví dụ

Thao tác truy vấn

Bookmark lookup

Bookmark lookup

Bookmark lookup

Bookmark lookup

Bookmark lookup

Chỉ mục

Chỉ mục trong SQL Server

Index và truy vấn tối ưu

Chỉ mục phức (composite index)

Chỉ mục phức (composite index)

Chỉ mục và các ràng buộc

Lệnh tạo chỉ mục

Lệnh tạo chỉ mục

Fill factor

Fill factor

Page Splits

Ví dụ

Phân mảnh (Fragmentation)

Kiểm tra tình trạng phân mảnh

Ví dụ Kiểm tra tình trạng phân mảnh

Chỉ tiêu kỹ thuật về phân mảnh

Reorganize or rebuild an index

Reorganize an index

Reorganize indexes

Rebuild an index

Rebuild indexes

Theo dõi các chỉ mục đang thiếu

Theo dõi các chỉ mục đang thiếu

Ví dụ

Ví dụ

Duyệt lại các index không dùng

Duyệt lại các index không dùng

Ví dụ

Index with included column

Index with included column

Ví dụ về giới hạn kích cỡ của chỉ mục

Ví dụ về giới hạn kích cỡ của chỉ mục

Ví dụ về thiết kế chỉ mục

Ví dụ về thiết kế chỉ mục

Bất lợi của index

Tính toán không gian đĩa

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

Ví dụ tính toán không gian đĩa dùng cho index (Offline Index Operation)

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 /

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

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.

Ví dụ tính toán không gian đĩa dùng cho index (Offline Index Operation)

xếp – Khi SORT_IN_TEMPDB được đặt là OFF

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 /

Có thể bạn quan tâm