Thiết kế cơ sở dữ liệu vật lý & Tuning

Thiết kế cơ sở dữ liệu vật lý & Tuning Nhóm thực hiện: Nguyễn Thị Ái Thảo 50502650 Lê Thị Bảo Thu 50502821 Thuyết trình cơ sở dữ liệu

Nội dung 1. Thiết kế cơ sở dữ liệu vật lý 2. Điều chỉnh (Tuning) 3.Tổng kết

Thiết kế cơ sở dữ liệu vật lý Giới thiệu. Thiết kế cơ sở dữ liệu vật lý trong cơ sở dữ liệu quan hệ.

Giới thiệu Quá trình thiết kế cơ sở dữ liệu: Thiết kế cơ sở dữ liệu vật lý & Tuning

Thiết kế cơ sở dữ liệu vật lý • Khái niệm: • Thiết kế cơ sở dữ liệu vật lý là quá trình chọn cấu trúc lưu trữ và truy suất dữ liệu cho cơ sở dữ liệu. • Mục tiêu: • Đạt được hiệu suất mong muốn. • Đảm bảo tính toàn vẹn, bảo mật và khả năng khôi phục dữ liệu. • Tiêu chuẩn: • Thời gian đáp ứng (response time). • Không gian sử dụng (space utilization). • Thông năng giao tác (transaction throughput). Thiết kế cơ sở dữ liệu vật lý & Tuning

Thiết kế cơ sở dữ liệu vật lý trong cơ sở dữ liệu quan hệ • Yếu tố ảnh hưởng đến thiết kế cơ sở dữ liệu vật lý. • Quyết định thiết kế cơ sở dữ liệu vật lý. Thiết kế cơ sở dữ liệu vật lý & Tuning

Yếu tố ảnh hưởng đến thiết kế cơ sở dữ liệu vật lý • Phân tích truy vấn cơ sở dữ liệu và giao tác: • Với mỗi tác vụ truy vấn ta cần xác định: • Files sẽ được truy cập tới. • Thuộc tính trong điều kiện chọn. • Điều kiện chọn (khoảng giá trị, đẳng thức, bất đẳng thức). • Thuộc tính trong điều kiện kết. • Thuộc tính mà giá trị sẽ được lấy bởi câu truy vấn. Thiết kế cơ sở dữ liệu vật lý & Tuning

Yếu tố ảnh hưởng đến thiết kế cơ sở dữ liệu vật lý • Phân tích truy vấn cơ sở dữ liệu và giao tác: • Với mỗi tác vụ update, cần xác định: • Files sẽ được update. • Loại của tác vụ: insert,update, hay delete. • Thuộc tính được xác định bởi điều kiện chọn cho việc delete hay update. • Thuộc tính mà giá trị bị thay đổi bởi tác vụ update. Thiết kế cơ sở dữ liệu vật lý & Tuning

Yếu tố ảnh hưởng đến thiết kế cơ sở dữ liệu vật lý • Phân tích tần số mong đợi của truy vấn và giao tác: • Xem xét tần số của truy vấn và giao tác. • Tần số trên cùng với thông tin về thuộc tính trong mỗi câu truy vấn được dùng để tạo ra danh sách tần số truy vấn và giao tác được sử dụng. • Luật 80-20: Khoảng 80% quá trình được tính toán chỉ bởi 20% truy vấn và giao tác. Thiết kế cơ sở dữ liệu vật lý & Tuning

Yếu tố ảnh hưởng đến thiết kế cơ sở dữ liệu vật lý • Phân tích ràng buộc thời gian của truy vấn và giao tác: • Một số truy vấn và giao tác có ràng buộc nghiêm ngặt về hiệu suất (performance constrains). • Ví dụ: Một giao tác cần 95% hoàn thành trong 5 giây và không kéo dài hơn 20 giây. • Độ ưu tiên cao hơn trong việc tuyển cho cấu trúc truy cập chính (primary access structure). Thiết kế cơ sở dữ liệu vật lý & Tuning

Yếu tố ảnh hưởng đến thiết kế cơ sở dữ liệu vật lý • Phân tích tần số mong đợi của toán tử update • Tối thiểu số con đường truy xuất tới một file được update thường xuyên. • Phân tích ràng buộc duy nhất trên thuộc tính • Con đường truy xuất cần được xác định cho tất cả thuộc tính là: • khoá tuyển. • khoá chính. • có ràng buộc duy nhất. Thiết kế cơ sở dữ liệu vật lý & Tuning

Quyết định thiết kế cơ sở dữ liệu vật lý • Quyết định thiết kế chỉ mục (index): • Đặt chỉ mục một thuộc tính. • Thuộc tính hoặc những thuộc tính nào được đặt chỉ mục. • Thiết lập chỉ mục nhóm (clustered index). • Sử dụng hash index hoặc tree index. • Sử dụng dynamic hashing cho một file. Thiết kế cơ sở dữ liệu vật lý & Tuning

Quyết định thiết kế cơ sở dữ liệu vật lý • Quyết định thiết kế chỉ mục: • Đặt chỉ mục một thuộc tính. • Thuộc tính là khoá chính. • Thuộc tính trong điều kiện chọn hoặc điều kiện kết. • Thuộc tính hoặc những thuộc tính nào được đặt chỉ mục. • Thiết lập nhóm thuộc tính (clustered index). • Sử dụng hash index hoặc a tree index. • Sử dụng dynamic hashing cho một file. Thiết kế cơ sở dữ liệu vật lý & Tuning

Quyết định thiết kế cơ sở dữ liệu vật lý • Quyết định thiết kế chỉ mục: • Đặt chỉ mục một thuộc tính. • Thuộc tính hoặc những thuộc tính nào được đặt chỉ mục. • Thiết lập chỉ mục nhóm (clustered index). • Sử dụng hash index hoặc tree index. • Sử dụng dynamic hashing cho một file. Thiết kế cơ sở dữ liệu vật lý & Tuning

Quyết định thiết kế cơ sở dữ liệu vật lý • Quyết định thiết kế chỉ mục: • Đặt chỉ mục một thuộc tính. • Thuộc tính hoặc những thuộc tính nào được đặt chỉ mục. • Một chỉ mục có thể được đặt trên một hoặc nhiều thuộc tính. • Nếu nhiều thuộc tính trong một quan hệ gắn liền với nhau trong một số câu truy vấn thì sử dụng chỉ mục đa thuộc tính (multiattribute index). • Thiết lập chỉ mục nhóm (clustered index). • Sử dụng hash index hoặc tree index. • Sử dụng dynamic hashing cho một file. Thiết kế cơ sở dữ liệu vật lý & Tuning

Quyết định thiết kế cơ sở dữ liệu vật lý • Quyết định thiết kế chỉ mục: • Đặt chỉ mục một thuộc tính. • Thuộc tính hoặc những thuộc tính nào được đặt chỉ mục. • Thiết lập chỉ mục nhóm (clustered index). • Sử dụng hash index hay tree index. • Sử dụng dynamic hashing cho một file. Thiết kế cơ sở dữ liệu vật lý & Tuning

Quyết định thiết kế cơ sở dữ liệu vật lý • Quyết định thiết kế chỉ mục: • Đặt chỉ mục một thuộc tính. • Thuộc tính hoặc những thuộc tính nào được đặt chỉ mục. • Thiết lập chỉ mục nhóm (clustered index). • Trong một bảng, nhiều nhất một chỉ mục là chỉ mục chính (primary index) hoặc chỉ mục nhóm (clustering index). • Việc chọn chỉ mục nhóm phụ thuộc vào yêu cầu bảng sắp thứ tự trên thuộc tính nào. • Một chỉ mục nhóm có thể thiết lập là chỉ mục đa thuộc tính. • Sử dụng hash index hay tree index. • Sử dụng dynamic hashing cho một file. Thiết kế cơ sở dữ liệu vật lý & Tuning

Quyết định thiết kế cơ sở dữ liệu vật lý • Quyết định thiết kế chỉ mục: • Đặt chỉ mục một thuộc tính. • Thuộc tính hoặc những thuộc tính nào được đặt chỉ mục. • Thiết lập chỉ mục nhóm (clustered index). • Sử dụng hash index hoặc tree index. • B+ tree index: truy vấn với điều kiện đẳng thức và khoảng giá trị trên thuộc tính được dùng làm khoá tìm kiếm. • Hash index: hoạt động tốt với điều kiện đẳng thức. • Sử dụng dynamic hashing cho một file. Thiết kế cơ sở dữ liệu vật lý & Tuning

Quyết định thiết kế cơ sở dữ liệu vật lý • Quyết định thiết kế chỉ mục: • Đặt chỉ mục một thuộc tính. • Thuộc tính hoặc những thuộc tính nào được đặt chỉ mục. • Thiết lập chỉ mục nhóm (clustered index). • Sử dụng hash index hay tree index. • Sử dụng dynamic hashing cho một file. • Files thường xuyên tăng và giảm kích thước (volatile). Thiết kế cơ sở dữ liệu vật lý & Tuning

Lựa chọn thiết kế cơ sở dữ liệu vật lý • Denormalization: • Phi chuẩn hoá (denormalization): Quá trình khi lưu trữ chuyển cơ sở dữ liệu luận lý ở dạng chuẩn cao (BCNF hoặc 4NF) sang dạng chuẩn thấp hơn, có thể là 1NF, 2NF, hoặc 3NF. • Sự dư thừa và không nhất quán dữ liệu. • Nhưng tăng tốc độ thực thi của những truy vấn và giao tác thường xuyên. Thiết kế cơ sở dữ liệu vật lý & Tuning

Tổng quan về điều chỉnh (tuning) cơ sở dữ liệu Khái niệm. Một số điều chỉnh.

Khái niệm tunning • Khi cơ sở dữ liệu được triển khai và vận hành, có thể xuất hiện một số vấn đề chưa được tính toán trong quá trình thiết kế cơ sở dữ liệu vật lý. • Cần xem xét lại thiết kế cơ sở dữ liệu vật lý. • Mục tiêu của tuning: • Tăng tốc độ thực thi của ứng dụng. • Giảm thời gian đáp ứng của truy vấn và giao tác. • Cải thiện thông năng của giao tác. Thiết kế cơ sở dữ liệu vật lý & Tuning

Một số điều chỉnh • Điều chỉnh chỉ mục (tuning indexes). • Điều chỉnh thiết kế cơ sở dữ liệu (tuning the database design). • Điều chỉnh truy vấn (tuning queries). Thiết kế cơ sở dữ liệu vật lý & Tuning

Điều chỉnh chỉ mục • Chỉ mục nào? • Truy vấn chạy quá lâu vì ít chỉ mục. • Những chỉ mục có thể không sử dụng. • Chỉ mục trải qua quá nhiều update vì chỉ mục được đặt trên thuộc tính thay đổi thường xuyên. • Những lựa chọn điều chỉnh chỉ mục: • Bỏ hoặc thiết lập chỉ mục mới. • Đổi non-clustered index thành clustered index hoặc ngược lại. • Tổ chức lại chỉ mục. Thiết kế cơ sở dữ liệu vật lý & Tuning

Điều chỉnh thiết kế cơ sở dữ liệu • Nếu thiết kế cơ sở dữ liệu không đạt được mục tiêu mong đợi • Xem lại thiết kế cơ sở dữ liệu luận lý, điều chỉnh lược đồ luận lý. • Ví dụ: • Denormalized. • Một quan hệ ở dạng BCNF có thể được lưu trữ ở nhiều bảng cũng ở dạng BCNF. (Vertical partitioning) • EMPLOYEE(Ssn,Name,Phone,Grade,Salary) • EMP1(Ssn,Name,Phone) EMP2(Ssn,Grade,Salary) Thiết kế cơ sở dữ liệu vật lý & Tuning

Điều chỉnh truy vấn • Hai dấu hiệu cần điều chỉnh truy vấn: • Truy vấn truy cập dữ liệu quá nhiều. • Chỉ mục thích hợp không được dùng trong câu truy vấn. • Các trường hợp điển hình: • Nhiều bộ tối ưu truy vấn (query optimizers) không sử dụng chỉ mục: • Có biểu thức toán học • Ví dụ: SALARY/365 > 10.50 • So sánh số học của các thuộc tính khác nhau về kích thước. • Ví dụ: AQTY = BQTY với AQTY kiểu INTEGER và BQTY kiểu SMALLINTEGER • So sánh NULL • Ví dụ: BDATE IS NULL • So sánh chuỗi con • Ví dụ: LNAME LIKE "%MANN" Thiết kế cơ sở dữ liệu vật lý & Tuning

Điều chỉnh truy vấn (2) • Chỉ mục thường không được sử dụng trong những truy vấn lồng nhau. • Ví dụ: SELECT SSN FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE MGRSSN = '333445555'); • Không sử dụng chỉ mục trên DNO trong EMPLOYEE. Thiết kế cơ sở dữ liệu vật lý & Tuning

Điều chỉnh truy vấn (3) • Tối thiểu toán tử DISTINCT. • Có thể bỏ những bảng kết quả tạm thời không cần thiết. • Trong một số trường hợp, bảng tạm thời có ích. SELECT SSN FROM EMPLOYEE AS E WHERE SALARY = SELECT MAX (SALARY) FROM EMPLOYEE AS M WHERE M.DNO = E.DNO • Để hiệu quả hơn: SELECT MAX (SALARY) AS HIGHSALARY, DNO INTO TEMP FROM EMPLOYEE GROUP BY DNO; SELECT SSN FROM EMPLOYEE, TEMP WHERE SALARY = HIGHSALARY AND EMPLOYEE.DNO = TEMP.DNO; Thiết kế cơ sở dữ liệu vật lý & Tuning

Điều chỉnh truy vấn (4) • Nếu có nhiều lựa chọn điều kiện kết chọn điều kiện sử dụng clustering index. • Một đặc tính của bộ tối ưu truy vấn là thứ tự của bảng trong mệnh đề FROM có thể ảnh hưởng đến quá trình kết. • Một số bộ tối ưu truy vấn trong trường hợp truy vấn lồng nhau không tốt bằng truy vấn tương đương không lồng nhau. Thiết kế cơ sở dữ liệu vật lý & Tuning

Hướng dẫn thêm về điều chỉnh truy vấn • Truy vấn với nhiều điều kiện chọn kết nối qua OR: SELECT FNAME, LNAME, SALARY, AGE FROM EMPLOYEE WHERE AGE > 45 OR SALARY < 50000; • Để hiệu quả hơn: SELECT FNAME, LNAME, SALARY, AGE FROM EMPLOYEE WHERE AGE > 45 UNION SELECT FNAME, LNAME, SALARY, AGE FROM EMPLOYEE WHERE SALARY < 50000; Thiết kế cơ sở dữ liệu vật lý & Tuning

Hướng dẫn thêm về điều chỉnh truy vấn • Điều kiện trong mệnh đề WHERE có thể được viết lại để sử dụng chỉ mục trên nhiều cột. SELECT REGION#, PRODUCT＿TYPE, MONTH, SALES FROM SALES_STATISTICS WHERE REGION# = 3 AND ((PRODUCT＿TYPE BETWEEN 1 AND 3) OR (PRODUCT＿TYPE BETWEEN 8 AND 10)); • Để hiệu quả hơn: SELECT REGION#, PRODUCT＿TYPE, MONTH, SALES FROM SALES_STATISTICS WHERE (REGION# = 3 AND (PRODUCT＿TYPE BETWEEN 1 AND 3)) OR (REGION# = 3 AND (PRODUCT＿TYPE BETWEEN 8 AND 10)); Thiết kế cơ sở dữ liệu vật lý & Tuning

Tổng kết • Các yếu tố ảnh hưởng đến quyết định thiết kế cơ sở dữ liệu vật lý. • Những chỉ dẫn để chọn thiết kế hợp lý. • Trong quá trình ứng dụng cơ sở dữ liệu, có thể nảy sinh vấn đề. • Điều chỉnh cơ sở dữ liệu (database tuning). Thiết kế cơ sở dữ liệu vật lý & Tuning

Thank you

Thiết kế cơ sở dữ liệu vật lý & Tuning