Bài giảng Thiết kế và quản trị cơ sở dữ liệu - Bài 4: Xử lý truy vấn và hiệu năng hệ cơ sở dữ liệu - Vũ Tuyết Trinh
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
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
cú pháp
(parser)
Biểu thức
ĐSQH
Biểu thức
ĐSQH
tối ưu
Bộ tối ưu
(optimizer)
Bộ sinh mã
(code generator)
Chương trình
tối ưu
1
Vũ Tuyết Trinh
Thiết kế và quản trị cơ sở dữ liệu
Cây toán tử
TYPE
WAGON (NW, TYPE, COND, STATION,
CAPACITY, WEIGHT)
TRAIN (NT, NW)
Cây toán tử logic
Thứ tự các phép toán
NW
Cây toán tử vật lý
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-
Aggregates
Select
ORDERBY
VIEW được coi là 1
block riêng rẽ
Project
Dạng cây thực thi
Join
(right-deep, bushy, …)
Nested Loop
Sort-Merge
Hash-Join
Thứ tự kết nối
2
Vũ Tuyết Trinh
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
Tuple R
relation)
Đặc điểm
one-and-haft pass, non-
Tuple R
Tuple S
blocking
Chi phí ?
SOURCE
R
SOURCE
S
Tuple-based NLJ, block-based NLJ, index-based NLJ
3
Vũ Tuyết Trinh
Thiết kế và quản trị cơ sở dữ liệu
Sort Merge Join
Nguyên tắc
Merge
Sắp xếp dữ liệu đầu vào
trộn dữ liệu
Đặc điểm
two-pass, blocking algorithm
Sort
Sort
Chi phí?
SOURCE
R
SOURCE
S
Hash Join (HJ)
Nguyên tắc
Matching
Tạo bảng băm trên R
Đọc S và đối sánh với dữ liệu
Hash Table R
trên bảng băm
1
n
Đặc điểm
…
two-pass, blocking algorithm
probe
Chi phí ?
build
Tuple R
Tuple S
hash(Tuple R)
hash(Tuple S)
SOURCE
R
SOURCE
S
4
Vũ Tuyết Trinh
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
WAGON
TRAIN
Cardinality
200000
60000
Record size
60
30
20
TRAFFIC
80000
Thông tin về các thuộc tính
Attribute
NW
TYPE
Cardinality
200000
200
Size
20
5
min -max
5-45
COND
CAPACITY
NT
5
400
15
15
10
6
2000
800
DATE
Thông tin về các chỉ số
Relation
WAGON
WAGON
WAGON
WAGON
TRAIN
TRAFFIC
TRAFFIC
Relation
Attributes
NW
TYPE
COND
CAPACITY
NT
NT
DATE
Cardinality
Unique
Yes
No
No
No
No
No
no
Type
Num of pages
Principal
Secondary
Secondary
Secondary
Principal
Principal
Principal
45
25
30
25
18
20
40
Record size
Num. of pages
(num of rec./page)
(NP’)
1500(375)
225(60)
200(60)
WAGON
TRAIN
TRAFFIC
200000
60000
80000
60(100)
30 (200)
20 (300)
5
Vũ Tuyết Trinh
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
Space
Size-Distribution
Estimator
6
Vũ Tuyết Trinh
Thiết kế và quản trị cơ sở dữ liệu
Query:
R1
R2
R3
R4
R4
R5
R5
Optimal Plan:
R1
R3
R2
Query:
R1
R2
R3
R4
R4
R5
R5
Optimal Plan:
R1
Optimal plan for
joining R3, R2, R4,
R1
R3
R2
Optimal plan for joining R3, R2, R4
7
Vũ Tuyết Trinh
Thiết kế và quản trị cơ sở dữ liệu
R1
R2
R3
R1
R2
R3
Sub-Optimal
for joining R1, R2, R3
Optimal
for joining R1, R2, R3
R2
R4
Cây toán tử tối ưu
R3
R1
Tiến trình tối ưu
{ R1, R2, R3, R4 }
{ 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 }
8
Vũ Tuyết Trinh
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
9
Vũ Tuyết Trinh
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
10
Vũ Tuyết Trinh
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?
11
Vũ Tuyết Trinh
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)
12
Vũ Tuyết Trinh
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
13
Vũ Tuyết Trinh
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;
14
Vũ Tuyết Trinh
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,
SELECT location
FROM employee, tech
WHERE
location
FROM employee, tech
WHERE
employee.dept =
tech.dept;
employee.dept =
tech.dept
AND ssnum =
43253265;
SELECT location
FROM techlocation
WHERE ssnum = 43253265;
15
Vũ Tuyết Trinh
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
16
Vũ Tuyết Trinh
16 trang
19020
Free
Bạn đang xem tài liệu "Bài giảng Thiết kế và quản trị cơ sở dữ liệu - Bài 4: Xử lý truy vấn và hiệu năng hệ cơ sở dữ liệu - Vũ Tuyết Trinh", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- bai_giang_thiet_ke_va_quan_tri_co_so_du_lieu_bai_4_xu_ly_tru.pdf
