Bài giảng Các hệ cơ sở dữ liệu: Cấu trúc lưu trữ và phương thức truy xuất - Lương Trần Hy Hiến

Chia sẻ: Gió Biển | Ngày: | Loại File: PDF | Số trang:19

Thêm vào BST

Báo xấu

105
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Bài giảng "Các hệ cơ sở dữ liệu: Cấu trúc lưu trữ và phương thức truy xuất" cung cấp cho người đọc các kiến thức: 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. Mời các bạn cùng tham khảo nội dung chi tiết.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

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