Bài giảng Các hệ cơ sở dữ liệu - 4. Cấu trúc lưu trữ và phương thức truy xuất
Con trỏ (tt)
-Con trỏ được tạo bằng lệnh DECLARE. ðầu tiên con
trỏ được khai báo và tạo ra trong bộ nhớ. Sau đó nó mới được mở.
- Lệnh OPEN mở con trỏ. Việc nhận về các bản ghi từ
một con trỏ được gọi là fetching. Một người dùng chỉ
có thể nhận về một bản ghi tại một thời điểm.
-Lệnh FETCH được sử dụng để đọc các bản ghi từ con trỏ.
- Ngầm định, một con trỏ là forward only. Nó có thể
truy xuất tuần tự các bản ghi từ bản ghi đầu tiên đến
bản ghi cuối cùng. Nó không thể truy xuất trực tiếp
hàng thứ 1 hoặc hàng cuối cùng trong một bảng.
19 trang |
Chia sẻ: vutrong32 | Lượt xem: 1157 | Lượt tải: 1
Bạn đang xem nội dung tài liệu Bài giảng Các hệ cơ sở dữ liệu - 4. Cấu trúc lưu trữ và phương thức truy xuất, để tải tài liệu về máy bạn click vào nút DOWNLOAD ở trên
Mục Tiêu
Các thành phần liên quan.
Tổ chức vật lý của SQL Server.
Cấu trúc lưu trữ và phương thức truy
xuất.
DBMS04 – Slides 2
Các Thành Phần Liên Quan
Bộ phận quản lý tập tin
.mdf : meta data file
.ldf : log data file
.bak : bakup data file
DBMS04 – Slides 3
Bộ phận quản lý ñĩa
Bộ phận quản lý dữ liệu vật lý
Tổ Chức Vật Lý trên SQL Server
Dữ liệu trong CSDL ñược tổ chức thành
các thành phần (Component) logic cho
user sử dụng như: Table, View.
Dữ liệu vật lý có thể lưu trên nhiều file
DBMS04 – Slides 4
hay thậm chí nhiều ổ ñĩa.
Người dùng (trừ các DBA) chỉ làm việc
trên các thành phần logic của SQL
Server.
Kiến trúc của SQL Server
DBMS04 – Slides 5
Kiến trúc của SQL Server
Các database hệ thống của SQL Server
master Database chứa thông tin về cấu
hình, phần quyền của hệ thống
tempdb Database chứa dữ liệu tạm cần lưu
trữ
DBMS04 – Slides 6
model Database chứa database mẫu ñể tạo
một database mới
msdb Database ñể hỗ trợ SQL Server Agent
Kiến trúc của SQL Server
DBMS04 – Slides 7
• Tập tin tuần tự
• Tập tin chỉ mục (Index)
• Tập tin tuần tự chỉ mục
Cấu Trúc Lưu Trữ và Phương
Thức Truy Xuất
DBMS04 – Slides 8
• Cây B+ và Kỹ thuật bảng băm
Giới thiệu Index
Tại sao tạo Index
Tăng tốc ñộ truy xuất dữ liệu
Không bắt buộc tính liên tục trên các dòng
Khi nào không nên tạo Index?
Tốn bộ nhớ trên ñĩa ñể lưu trữ Index. Khi
DBMS04 – Slides 9
user cập nhật dữ liệu trên cột Index, SQL
Server cũng cập nhật index
Việc quản lý Index sẽ tốn thời gian và tài
nguyên nên nếu Index không thường sử
dụng thì không cần tạo.
Các Loại Index
Clustered/Non clustered
Clustered = thứ tự các record lưu trữ vật lý sắp thứ
tự của index
Non clustered = thứ tự các record lưu trữ vật lý
không sắp thứ tự của index
Dense/sparse
DBMS04 – Slides 10
Dense = ðánh chỉ mục cho tất cả các records
Sparse = Chỉ ñánh chỉ một số records
Primary/secondary
Ví dụ: Sắp tăng theo tên, cùng tên thì sắp theo tuổi
thì tên là primary và tuổi là secondary.
B+ tree / Hash table /
Clustered Index
File ñược sắp theo thứ tự của thuộc
tính chỉ mục index
10
20
10
20 ục
DBMS04 – Slides 11
30
40
50
60
70
80
30
40
50
60
70
80
Non clustered Indexes
Dùng ñể sắp chỉ mục các thuộc tính không
phải là khóa chính
DBMS04 – Slides 12
10
10
20
20
20
30
30
30
20
30
30
20
10
20
10
30
Clustered vs. Non clustered Index
DBMS04 – Slides 13
Data entries
(Index File)
(Data file)
DataRecords
Data entries
Data Records
CLUSTERED UNCLUSTERED
d là bậc
Mỗi node có >= d và <= 2d keys trừ nút gốc
Cây B+
30 120 240
DBMS04 – Slides 14
Nút lá:
Keys k < 30
Keys 30<=k<120 Keys 120<=k<240 Keys 240<=k
40 50 60
40 50 60
Next leaf
B+ Tree Example
80
20 60 100 120 140
d = 2
DBMS04 – Slides 15
10 15 18 20 30 40 50 60 65 80 85 90
10 15 18 20 30 40 50 60 65 80 85 90
B+ Tree Design
How large d ?
Example:
Key size = 4 bytes
Pointer size = 8 bytes
DBMS04 – Slides 16
Block size = 4096 byes
2d x 4 + (2d+1) x 8 <= 4096
d = 170
Searching a B+ Tree
Exact key values:
Start at the root
Proceed down, to the leaf
Select name
From people
Where age = 25
DBMS04 – Slides 17
Range queries:
As above
Then sequential traversal
Select name
From people
Where 20 <= age
and age <= 30
Insertion in a B+ Tree
80
20 60 100 120 140
Insert K=19
DBMS04 – Slides 18
10 15 18 20 30 40 50 60 65 80 85 90
10 15 18 20 30 40 50 60 65 80 85 90
Insertion in a B+ Tree
80
20 60 100 120 140
After insertion
DBMS04 – Slides 19
10 15 18 19 20 30 40 50 60 65 80 85 90
10 15 18 20 30 40 50 60 65 80 85 9019
Insertion in a B+ Tree
80
20 60 100 120 140
Now insert 25
DBMS04 – Slides 20
10 15 18 19 20 30 40 50 60 65 80 85 90
10 15 18 20 30 40 50 60 65 80 85 9019
Insertion in a B+ Tree
80
20 60 100 120 140
After insertion
DBMS04 – Slides 21
10 15 18 19 20 25 30 40 50 60 65 80 85 90
10 15 18 20 25 30 40 60 65 80 85 9019 50
Insertion in a B+ Tree
80
20 60 100 120 140
But now have to split !
DBMS04 – Slides 22
10 15 18 19 20 25 30 40 50 60 65 80 85 90
10 15 18 20 25 30 40 60 65 80 85 9019 50
Insertion in a B+ Tree
80
20 30 60 100 120 140
After the split
DBMS04 – Slides 23
10 15 18 19 20 25 60 65 80 85 90
10 15 18 20 25 30 40 60 65 80 85 9019 50
30 40 50
Deletion from a B+ Tree
80
20 30 60 100 120 140
Delete 30
DBMS04 – Slides 24
10 15 18 19 20 25 60 65 80 85 90
10 15 18 20 25 30 40 60 65 80 85 9019 50
30 40 50
Deletion from a B+ Tree
80
20 30 60 100 120 140
After deleting 30
May change to
40, or not
DBMS04 – Slides 25
10 15 18 19 20 25 60 65 80 85 90
10 15 18 20 25 40 60 65 80 85 9019 50
40 50
Deletion from a B+ Tree
80
20 30 60 100 120 140
Now delete 25
DBMS04 – Slides 26
10 15 18 19 20 25 60 65 80 85 90
10 15 18 20 25 40 60 65 80 85 9019 50
40 50
Deletion from a B+ Tree
80
20 30 60 100 120 140
After deleting 25
Need to rebalance
Rotate
DBMS04 – Slides 27
10 15 18 19 20 60 65 80 85 90
10 15 18 20 40 60 65 80 85 9019 50
40 50
Deletion from a B+ Tree
80
19 30 60 100 120 140
Now delete 40
DBMS04 – Slides 28
10 15 18 19 20 60 65 80 85 90
10 15 18 20 40 60 65 80 85 9019 50
40 50
Deletion from a B+ Tree
80
19 30 60 100 120 140
After deleting 40
Rotation not possible
Need to merge nodes
DBMS04 – Slides 29
10 15 18 19 20 60 65 80 85 90
10 15 18 20 60 65 80 85 9019 50
50
Deletion from a B+ Tree
80
19 60 100 120 140
Final tree
DBMS04 – Slides 30
10 15 18 19 20 50 60 65 80 85 90
10 15 18 20 60 65 80 85 9019 50
Variation on B+tree: B-tree (no +)
Idea:
Avoid duplicate keys
Have record pointers in non-leaf nodes
DBMS04 – Slides 31
Note: Textbook’s B-Tree means B+-tree!
Hash Tables
Hash Tables
Bảng băm:
Có n khối dữ liệu cần lưu trữ
Cho một hàm hash f(k):khóa k {0, 1, ,
n-1}
f(k) sẽ trỏ ñến dữ liệu có khóa k
DBMS04 – Slides 33
Giả lưu giữ khối các dữ liệu với các khóa
như sau
h(e)=0
h(b)=h(f)=1
Ví dụ bảng băm
e
0
DBMS04 – Slides 34
h(g)=2
h(a)=h(c)=3
b
f
g
a
c
1
2
3
Tìm khối dữ liệu a:
Tính hàm băm (hash) h(a)=3
ðọc khối dữ liệu 3
Truy cập lần lượt các
Tìm một record trong bảng băm
e
0
DBMS04 – Slides 35
record trong khối 3 b
f
g
a
c
1
2
3
Thêm record vào một khối khi còn chỗ
trống
Ví dụ: thêm d với h(d)=2
Thêm vào bảng băm
e
0
DBMS04 – Slides 36
b
f
g
d
a
c
1
2
3
Tạo khối tràn (overflow) nếu hết chỗ
trống
Ví dụ:
Thêm vào bảng băm
e
0
DBMS04 – Slides 37
thêm k
với h(k)=1
b
f
g
d
a
c
1
2
3
k
Hiệu suất của bảng băm
Tốt nếu ít khối overflow.
DBMS04 – Slides 38
Tạo Index trên SQL Server
Trên SQL hỗ trợ 2 loại Index:
Cluster Index
Non Cluster Index
DBMS04 – Slides 39
Tạo Index trên SQL Server
Cluster Index: chỉ có thể tạo một cluster
index duy nhất cho một bảng dữ liệu.
Mặc ñịnh khóa chính sẽ thành cluster index
Dữ liệu của bảng sắp xếp theo thứ tự của
cluster index
DBMS04 – Slides 40
Tạo Index trên SQL Server
Non Cluster Index: có thể tạo 249 non-
cluster index cho một bảng dữ liệu.
Dữ liệu của bảng không sắp theo thứ tự
của non-cluster index.
Thường tạo index cho các cột dữ liệu dùng
DBMS04 – Slides 41
ñể join hay trong ñiều kiện where hoặc giá
trị cột này thương xuyên thay ñổi.
Tạo Index trên SQL Server
Cú pháp tạo Index:
CREATE [UNIQUE] [CLUSTERED |
NONCLUSTERED] INDEX index_name
ON table_name
(column_name[,column_name])
DBMS04 – Slides 42
Tạo Index trên SQL Server
Cú pháp tạo Index:
CREATE NONCLUSTERED INDEX
idxExternalCandidate
ON ExternalCandidate(cAgencyCode)
DBMS04 – Slides 43
CREATE CLUSTERED INDEX
idxRecruitment
ON
RecruitmentAgencies(cAgencyCode)
Bài tập tạo Index trên SQL Server
Giả sử CSDL của bạn có 1 bảng sau:
SinhVien(MaSV, TenSV, TuoiSV, DiaChi)
Trong ñó MaSV là khóa chính, thường dùng ñể
join các bảng khác; tên (TenSV) thường xuất
hiện trong ñiều kiện WHERE trong các câu
DBMS04 – Slides 44
truy vấn thông tin.
Yêu cầu: Xác ñịnh Cluster và non cluster index
cho bảng SinhVien. Viết câu lệnh SQL tạo
bảng và tạo các câu Index tương ứng.
Index trên SQL Server
Khóa chính Cluster Index
Các cột hay truy xuất nên tạo non
cluster index.
tăng tốc ñộ truy xuất CSDL.
DBMS04 – Slides 45
Ngoài ra, có thể sử dụng cộng cụ Index
Turning của SQL Server ñể tạo index
cho CSDL của mình tự ñộng theo
suggest của SQL Server
Tóm lại
Các thành phần liên quan ñến tổ chức vật
lý của một Hệ quản trị Cơ Sở Dữ Liệu
Kiến trúc Hệ quản trị Cơ Sở Dữ Liệu
Cấu trúc lưu trữ và phương thức truy xuất
DBMS04 – Slides 46
Stored Procedure và Trigger
Stored Procedure
Cho phép lập trình theo hướng Module
Thực thi nhanh hơn, giảm ñược việc
chiếm dụng ñường truyền mạng
Bảo mật
DBMS04 – Slides 48
Xử lý các chức năng và chia sẽ với các
ứng dụng khác
Cú pháp:
CREATE PROCEDURE proc_name
AS
BEGIN
sql_statement1
Stored Procedure
DBMS04 – Slides 49
sql_statement2
END
Stored Procedure Syntax
DBMS04 – Slides 50
Ví dụ 1 – SP không tham số
• Mở Query Analyzer, gõ:
CREATE PROCEDURE sp_XemDSSV
AS
BEGIN
PRINT N‘DANH SÁCH SINH VIÊN’
DBMS04 – Slides 51
SELECT MSSV, HoLot, Ten, NgaySinh,
NoiSinh, DiaChi
FROM SinhVien
END
• Bấm F5 ñể thực thi
Ví dụ 2 – SP có tham số
• Mở Query Analyzer, gõ:
CREATE PROCEDURE sp_XemSV
@MaSV nvarchar(11)
AS
BEGIN
DBMS04 – Slides 52
PRINT N‘SINH VIÊN’
SELECT HoLot, Ten, NgaySinh,
NoiSinh, DiaChi
FROM SinhVien
WHERE MSSV = @MaSV
END
• Bấm F5 ñể thực thi
Cú pháp:
sp_helptext proc_name
Ví dụ:
• Mở Query Analyzer, gõ:
Xem nội dung SP
DBMS04 – Slides 53
sp_helptext sp_XemDSSV
sp_helptext sp_XemSV
• Kiểm tra chính tả, nội dung procedure.
• Cú pháp:
EXECUTE proc_name danh_sách_tham_số
hoặc
EXEC proc_name danh_sách_tham_số
Gọi Stored Procedure
DBMS04 – Slides 54
hoặc
proc_name danh_sách_tham_số
//Mỗi tham số các nhau một dấu phẩy
• Mở Query Analyzer, gõ:
EXECUTE sp_XemDSSV
EXECUTE sp_XemSV ‘K29.103.010’
hoặc
Ví dụ
DBMS04 – Slides 55
EXEC sp_XemDSSV
EXEC sp_XemSV ‘K29.103.010’
hoặc
sp_XemDSSV
sp_XemSV ‘K29.103.010’
• Bấm F5 ñể thực thi
Trigger là gì?
Là một Stored Procedure gắn liền với Table cụ
thể, ñược gọi tự ñộng khi user thay ñổi dữ liệu
trên một table.
Khi có thao tác cập nhật dữ liệu (insert, update,
delete) thì trigger ứng với thao tác ñó ñược thực
DBMS04 – Slides 56
hiện tự ñộng
Trigger không ñược gọi trực tiếp, không có tham
số
Là thành phần của Transaction: Những lệnh
trong Trigger có thể ROLL BACK.
Sử dụng Trigger ñể làm gì?
ðể thực hiện cascade updates và cascade
deletes qua các table quan hệ trong database
Ép buộc tính toàn vẹn của dữ liệu phức tạp:
Thực hiện các ràng buộc có tham chiếu ñến các
column trong nhiều table.
DBMS04 – Slides 57
ðịnh nghĩa Custom Error Messages:
Dùng trigger ñể trả về các chuỗi thông báo trạng
thái của môt hành ñộng nào ñó.
Bảo trì các dữ liệu không ñược chuẩn hoá
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR [INSERT | DELETE | UPDATE]
Tạo Trigger: Cú pháp chung
DBMS04 – Slides 58
AS sql_statements
Tạo Trigger trong câu lệnh Update
CREATE TRIGGER tên_Trigger
ON tên_bảng
FOR UPDATE
AS
DBMS04 – Slides 59
IF UPDATE (tên_cột)
[AND UPDATE(tên_cột)| OR
UPDATE(tên_cột)]
các câu lệnh của trigger
Ví dụ
CauThu (MACT, HOTEN, VITRI, NGAYSINH,
DIACHI, MACLB, MAQG, SO)
Yêu cầu: Khi thêm 1 cầu thủ, kiểm tra số áo
không ñược trùng nhau!
DBMS04 – Slides 60
Trigger
Khi INSERT mới 1 record thì nó sẽ nằm
trong bảng INSERTED.
Khi UPDATE, DELETE 1 record thì nó sẽ
nằm trong bảng DELETED.
DBMS04 – Slides 61
Lời giải
CREATE TRIGGER trg_KiemTraSoAo
ON CauThu
FOR INSERT
AS
DBMS04 – Slides 62
BEGIN
DECLARE @So int
DECLARE @MaCLB varchar(6)
--Lấy Số áo và Mã CLB vừa Insert
SELECT @So = SO, @MaCLB = MACLB FROM
INSERTED
Lời giải
IF (SELECT COUNT(SO) FROM CAUTHU WHERE
MACLB = @MaCLB AND SO = @SO) > 1
BEGIN
PRINT N’Bị trùng số áo’
ROLLBACK
DBMS04 – Slides 63
END
ELSE
BEGIN
PRINT N’Thêm cầu thủ thành công’
COMMIT
END
END
Lập trình với con trỏ
Một con trỏ là một ñối tượng cơ sở dữ liệu ñược
sử dụng bởi ứng dụng ñể thao tác với các hàng dữ
liệu thay vì các tập hợp dữ liệu.
Con trỏ ñược dùng với Procedure và Trigger
Với con trỏ chúng ta có thể:
DBMS04 – Slides 64
Cho phép ñịnh vị các hàng chỉ ñịnh của tập kết quả.
Nhận về một hàng ñơn hoặc tập hợp các hàng từ vị trí
hiện tại của tập kết quả.
Hỗ trợ sửa ñổi dữ liệu của hàng ở vị trí hiện tại trong
tập kết quả.
Hỗ trợ nhiều cấp ñộ quan sát ñối với các thay ñổi ñược
tạo ra bởi các người dùng khác trên các dữ liêu của tập
kết quả.
Quy trình xử lý con trỏ
DBMS04 – Slides 65
Tạo con trỏ
Lệnh DECLARE dùng ñể tạo một con trỏ.
Nó chứa các lệnh SELECT ñể bao gồm các bản ghi từ
bảng.
Cú pháp là:
DECLARE CURSOR
DBMS04 – Slides 66
[LOCAL | GLOBAL]
[FORWARD ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC |FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR
[FOR UPDATE [OF Column_name[,.N]]]
Các bước sử dụng con trỏ
Mở con trỏ:
OPEN
Nhận về các bản ghi:
FETCH
DBMS04 – Slides 67
ðóng con trỏ:
CLOSE
Xoá các tham chiếu tới con trỏ:
DEALLOCATE
Truy xuất và duyệt con trỏ
FETCH FIRST: Truy xuất hàng ñầu tiên.
FETCH NEXT: Truy xuất hàng tiếp theo hàng truy
xuất trước ñó.
FETCH PRIOR: Truy xuất hàng trước hàng truy
xuất trước ñó.
DBMS04 – Slides 68
FETCH LAST: Truy xuất hàng cuối cùng.
FETCH ABSOLUTE n: Nếu n là một số nguyên
dương, nó sẽ truy xuất n hàng trong con trỏ. Nếu
n là một số nguyên âm, n hàng trước hàng cuối
cùng trong con trỏ ñược truy xuất. Nếu n bằng 0,
không hàng nào ñược truy xuất. Ví dụ, FETCH
Absolute 2 sẽ hiển thị bản ghi thứ hai của một
bảng.
Truy xuất và duyệt con trỏ
FETCH RELATIVE n: Truy xuất n hàng
từ hàng truy xuất trước ñó, nếu n là số
dương. Nếu n là số âm, n hàng trước
hàng truy xuất trước ñó ñược truy xuất.
Nếu n bằng 0, hàng hiện tại ñược nhận
DBMS04 – Slides 69
về.
Các biến toàn cục của lệnh FETCH
@@FETCH _STATUS: Biến này trả về
một số nguyên biễu diễn kết quả của
lệnh truy xuất cuối cùng của con trỏ.
@@CURSOR_ROWS: Biến này trả về
DBMS04 – Slides 70
tổng số hàng hiện tại trong con trỏ ñang
mở.
Ví dụ tạo con trỏ
DBMS04 – Slides 71
Con trỏ (tt)
Một con trỏ là một ñối tượng cơ sở dữ
liệu ñược sử dụng bởi ứng dụng ñể thao
tác với các hàng dữ liệu thay vì các tập
hợp dữ liệu. Sử dụng con trỏ, nhiều tác
vụ có thể ñược thực hiện theo từng
DBMS04 – Slides 72
hàng trên tập kết quả mà có thể cần
hoặc ko cần sự có mặt của bảng gốc
Con trỏ (tt)
Con trỏ ñược tạo bằng lệnh DECLARE. ðầu tiên con
trỏ ñược khai báo và tạo ra trong bộ nhớ. Sau ñó nó
mới ñược mở.
Lệnh OPEN mở con trỏ. Việc nhận về các bản ghi từ
một con trỏ ñược gọi là fetching. Một người dùng chỉ
có thể nhận về một bản ghi tại một thời ñiểm.
DBMS04 – Slides 73
Lệnh FETCH ñược sử dụng ñể ñọc các bản ghi từ con
trỏ.
Ngầm ñịnh, một con trỏ là forward only. Nó có thể
truy xuất tuần tự các bản ghi từ bản ghi ñầu tiên ñến
bản ghi cuối cùng. Nó không thể truy xuất trực tiếp
hàng thứ 1 hoặc hàng cuối cùng trong một bảng.
Con trỏ (tt)
Khi một con trỏ tạm thời không cần
thiết, nó có thể ñược ñóng bởi lệnh
CLOSE.
Mỗi khi con trỏ không ñược sử dụng,
DBMS04 – Slides 74
các tham chiếu ñến nó nên ñược loại bỏ
bằng lệnh DEALLOCATE
Câu hỏi
Các DBMS hỗ trợ stored procedure? trigger?
Công dụng của Stored Procedure? Trigger?
DBMS04 – Slides 75
So sánh Store Procedure và Trigger?
DBMS04 – Slides 76
Các file đính kèm theo tài liệu này:
- dbms04_9726.pdf