Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 4: Cấu trúc lưu trữ và phương thức truy xuất - Lương Trần Hy Hiế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