Thiết kế và quản trị cơ sở dữ liệu Xử lý truy vấn và hiệu năng hệ CSDL Vũ Tuyết Trinh trinhvt-fit@mail.hut.edu.vn Bộ môn Hệ thống thông tin, Viện CNTT&TT Đại học Bách Khoa Hà Nội Xử lý câu hỏi truy vấn Câu lệnh SQL Phân tích Biểu thức cú pháp ĐSQH Biểu thức (parser) Bộ tối ưu ĐSQH (optimizer) tối ưu Bộ sinh mã (code generator) Chương trình tối ưu goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 1
Thiết kế và quản trị cơ sở dữ liệu Cây toán tử WAGON (NW, TYPE, COND, STATION, TYPE CAPACITY, WEIGHT) TRAIN (NT, NW) Cây toán tử logic Thứ tự các phép toán Cây toán tử vật lý NW Các thuật toán thực thi phép toán WAGON (NW, TYPE...) NT = 4002 TRAIN (NT, NW) Các phép toán vật lý (thuật toán) Query Blocks Thuật toán SELECT-FROM- Sort WHERE-GROUPBY- ORDERBY Aggregates VIEW được coi là 1 Select block riêng rẽ Project Dạng cây thực thi Join (right-deep, bushy, …) Nested Loop Thứ tự kết nối Sort-Merge Hash-Join goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 2
Thiết kế và quản trị cơ sở dữ liệu Truy nhập bảng Truy nhập tuần tự (Sequential scan): đọc theo khối Truy nhập theo địa chỉ (index scan): truy nhập vào bản ghi dựa trên chỉ mục Chi phí truy nhập ? S Phép toán nhiều pha: Nested-Loops Join Nguyên tắc Đọc từng bản ghi của quan Matching hệ R (external relation) & lặp trên quan hệ S (internal relation) Tuple R Đặc điểm one-and-haft pass, non- blocking Tuple R Tuple S Chi phí ? SOURCE SOURCE R S Tuple-based NLJ, block-based NLJ, index-based NLJ goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 3
Thiết kế và quản trị cơ sở dữ liệu Sort Merge Join Nguyên tắc Sắp xếp dữ liệu đầu vào Merge trộn dữ liệu Đặc điểm two-pass, blocking algorithm Sort Chi phí? Sort SOURCE SOURCE R S Hash Join (HJ) Nguyên tắc Tạo bảng băm trên R Matching Đọc S và đối sánh với dữ liệu trên bảng băm Hash Table R Đặc điểm 1 … n two-pass, blocking algorithm probe Chi phí ? build Tuple R Tuple S hash(Tuple R)hash(Tuple S) SOURCE SOURCE R S goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 4
Thiết kế và quản trị cơ sở dữ liệu Mô hình giá Chí phí thực hiện câu hỏi phụ thuộc: đọc/ghi bộ nhớ ngoài (số trang nhớ) Kích thước dữ liệu phải xử lý Chi phí truy nhập dữ liệu Đọc ghi dữ liệu xử lý Truyền thông giữa các trạm làm việc CTA = s * NBPAGES + t * NBNUPLETS (+ m * NBMESSAGES) Trọng số s = trọng số đọc/ghi dữ liệu (ví dụ = 1) t = trọng số xử lý của CPU (ví dụ = 1/3) m = trọng số truyền dữ liệu Thông tin về các quan hệ Kích thước của các quan hệ và bản ghi Relation Cardinality Record size WAGON 200000 60 TRAIN 60000 30 TRAFFIC 80000 20 Thông tin về các thuộc tính Attribute Cardinality Size min -max NW 200000 20 TYPE 200 5 COND 5 15 CAPACITY 400 15 5-45 NT 2000 10 DATE 800 6 Thông tin về các chỉ số Relation Attributes Unique Type Num of pages WAGON NW Yes Principal 45 WAGON TYPE No Secondary 25 WAGON COND No Secondary 30 WAGON CAPACITY No Secondary 25 TRAIN NT No Principal 18 TRAFFIC NT No Principal 20 TRAFFIC DATE no Principal 40 Relation Cardinality Record size Num. of pages (num of rec./page) (NP’) WAGON 200000 60(100) 1500(375) TRAIN 60000 30 (200) 225(60) TRAFFIC 80000 20 (300) 200(60) goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 5
Thiết kế và quản trị cơ sở dữ liệu Tối ưu Đặt vấn đề: Cho 1 câu truy vấn, các cây toán tử thực thi nào sẽ được xem xét ? Không gian tìm kiếm Chiến lược tìm kiếm Ước lượng giá cho các kế hoạch thực thi Lý tưởng: tìm ra kế hoạch thực thi tốt nhất Thực tế: Tránh kế hoạch thực thi tồi Bộ tối ưu Rewriter Algebraic Space Cost Model Planner Method-Structure Size-Distribution Space Estimator goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 6
Thiết kế và quản trị cơ sở dữ liệu Query: R1 R2 R3 R4 R5 Optimal Plan: R5 R1 R4 R3 R2 Query: R1 R2 R3 R4 R5 Optimal Plan: R5 R1 R4 Optimal plan for R3 R2 joining R3, R2, R4, R1 Optimal plan for joining R3, R2, R4 goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 7
Thiết kế và quản trị cơ sở dữ liệu R1 R2 R2 R3 R3 R1 Optimal Sub-Optimal for joining R1, R2, R3 for joining R1, R2, R3 R2 Cây toán tử tối ưu R4 R3 R1 { R1, R2, R3, R4 } Tiến trình tối ưu { R1, R2, R3 } { R1, R2, R4 } { R1, R3, R4 } { R2, R3, R4 } { R1, R2 } { R1, R3 } { R1, R4 } { R2, R3 } { R2, R4 } { R3, R4 } { R1 } { R2 } { R3 } { R4 } goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 8
Thiết kế và quản trị cơ sở dữ liệu Các lý do dẫn đến hiệu năng thực thi truy vấn chậm Đòi hỏi nhiều phép truy nhập đĩa Không sử dụng index Cấu trúc CSDL không hợp lý Các giao dịch dư thừa, lồng nhau … Ví dụ Employee(ssnum, name, manager, dept, salary, numfriends) Clustering index : ssnum Non clustering indexes (i) name (ii) dept Student(ssnum, name, degree_sought, year) Clustering index :ssnum Non clustering index :name Tech(dept, manager, location) Clustering index : dept goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 9
Thiết kế và quản trị cơ sở dữ liệu Các kỹ thuật viết lại truy vấn Sử dụng Index Loại bỏ DISTINCTs Xem xét câu truy vấn lồng nhau Điều kiện kết nối Mệnh đề having Sử dụng view Khung nhìn lưu trữ (Materialized views) Sử dụng Index Một số trường hợp index không được sử dụng Biểu thức toán học WHERE salary/12 >= 4000; Hàm tính toán trên xâu SELECT * FROM employee WHERE SUBSTR(name, 1, 1) = „G‟; So sánh các trường không cùng kiểu So sánh với giá trị rỗng goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 10
Thiết kế và quản trị cơ sở dữ liệu Hạn chế sử dụng DISTINCTs Ví dụ SELECT distinct ssnum FROM employee WHERE dept = „information systems‟ có thể loại bỏ distinct Hạn chế sử dụng DISTINCTs Ví dụ SELECT DISTINCT ssnum FROM employee, tech WHERE employee.dept = tech.dept Có cần sử dụng DISTINCT? goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 11
Thiết kế và quản trị cơ sở dữ liệu Employee(ssnum, name, manager, dept, salary, numfriends) Student(ssnum, name, degree_sought, year) Tech(dept, manager, location) SELECT ssnum FROM employee, tech WHERE employee.manager = tech.manager SELECT ssnum, tech.dept FROM employee, tech WHERE employee.manager = tech.manager SELECT student.ssnum FROM student, employee, tech WHERE student.name = employee.name AND employee.dept = tech.dept; 23 Truy vấn lồng nhau SELECT ssnum FROM employee WHERE salary > (select avg(salary) from employee) SELECT ssnum FROM employee WHERE dept in (select dept from tech) SELECT ssnum FROM employee e1 WHERE salary = (SELECT avg(e2.salary) FROM employee e2,tech WHERE e2.dept = e1.dept AND e2.dept = tech.dept) goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 12
Thiết kế và quản trị cơ sở dữ liệu Viết lại truy vấn lồng nhau SELECT ssnum FROM employee WHERE dept in (select dept from tech) SELECT ssnum FROM employee, tech WHERE employee.dept = tech.dept Truy vấn lồng nhau với các phép toán tập hợp Ví dụ SELECT avg(salary) FROM employee WHERE manager in (select manager from tech) SELECT avg(salary) FROM employee, tech WHERE employee.manager = tech.manager So sánh 2 câu truy vấn trên goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 13
Thiết kế và quản trị cơ sở dữ liệu Truy vấn lồng có kết nối Ví dụ SELECT ssnum FROM employee e1 WHERE salary = (SELECT avg(e2.salary FROM employee e2, tech WHERE e2.dept = e1.dept and e2.dept = tech.dept); INSERT INTO temp SELECT avg(salary) as avsalary, employee.dept FROM employee, tech WHERE employee.dept = tech.dept GROUP BY employee.dept; SELECT ssnum FROM employee, temp WHERE salary = avsalary AND employee.dept = temp.dept Employee(ssnum, name, manager, dept, salary, numfriends) Student(ssnum, name, degree_sought, year) Tech(dept, manager, location) SELECT ssnum FROM employee e1 WHERE numfriends = COUNT(SELECT e2.ssnum FROM employee e2, tech WHERE e2.dept = tech.dept AND e2.dept = e1.dept); INSERT INTO temp SELECT COUNT(ssnum) as numcolleagues, employee.dept FROM employee, tech WHERE employee.dept = tech.dept GROUP BY employee.dept; SELECT ssnum FROM employee, temp WHERE numfriends = numcolleagues AND employee.dept = temp.dept; goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 14
Thiết kế và quản trị cơ sở dữ liệu Điều kiện kết nối Kết nối trên trường dữ liệu có clustering indexes. Kết nối trên trường dữ liệu kiểu số “tốt hơn” là trên trường dữ liệu kiểu xâu ký tự Sử dụng khung nhìn CREATE VIEW techlocation AS SELECT ssnum, tech.dept, location SELECT location FROM employee, tech FROM employee, tech WHERE WHERE employee.dept = employee.dept = tech.dept; tech.dept SELECT location AND ssnum = FROM techlocation 43253265; WHERE ssnum = 43253265; goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 15
Thiết kế và quản trị cơ sở dữ liệu Lưu trữ khung nhìn Materialized views: CREATE MATERIALIZED VIEW VendorOutstanding BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT orders.vendor, sum(orders.quantity*item.price) FROM orders,item WHERE orders.itemnum = item.itemnum group by orders.vendor; Các tham số BUILD immediate/deferred REFRESH complete/fast ENABLE QUERY REWRITE Lợi ích: Tự động cập nhật các dữ liệu tính toán Sử dụng bởi bộ tối ưu truy vấn 32 goupee.com fb.com/groups/goupee Vũ Tuyết Trinh 16
Hiển thị 16 trang đầu. Tải tài liệu để xem đầy đủ.
Chưa có bình luận nào. Hãy là người đầu tiên!
Bình luận (0)
Chưa có bình luận nào. Hãy là người đầu tiên!