intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE

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

100
lượt xem
5
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ủ đề:
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

  1. 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
  2. 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
  3. 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
  4. Clustered vs. Non clustered Index Cây B+  d là bậc  Mỗi node có >= d và
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

 

Đồng bộ tài khoản
2=>2