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.

pdf19 trang | Chia sẻ: vutrong32 | Lượt xem: 1170 | Lượt tải: 1download
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:

  • pdfdbms04_9726.pdf