Thiết kế vật lý CSDL

Chương 5 Thiết kế vật lý CSDL 1 Nội dung  Mục tiêu thiết kế vật lý CSDL  Denormalization và partition  Chỉ mục (index)  2 Mục tiêu thiết kế vật lý CSDL  Đáp ứng tính hiệu quả trong xử lý dữ liệu  Giảm tối thiểu thời gian truy xuất dữ liệu  Do công nghệ phát triển, tiết kiệm không gian đĩa không còn là yếu tố quan trọng 3 Các yêu cầu thiết kế vật lý CSDL  Các bảng đã chuẩn hóa và ước tính được số hàng trong mỗi bảng.  Mỗi thuộc tính và kiểu dữ liệu  Nơi và khi nào dữ liệu được dùng (nhập, khôi phục, xóa và cập nhật dữ liệu)  Yêu cầu thời gian đáp ứng, bảo mật, sao lưu, khôi phục, bảo  Công nghệ được dùng để thực thi CSDL 4 Inputs, Outputs, and Environment of Physical Database Design 5 Dung lượng dữ liệu và phân tích tần suất sử dụng  Chọn định dạng lưu trữ (data type) cho mỗi thuộc tính từ mô hình dữ liệu sao cho  Maximize data integrity  Minimize storage space 6 Usage map 7 Bản ghi vật lý physical record  Là tập hợp các byte được chuyển đổi giữa bộ nhớ chính của máy và lưu trữ ổn định trên đĩa.  Số lượng bản ghi vật lý được truy xuất là chỉ số quan trọng đến việc thực thi CSDL  File là tập hợp các bản ghi vật lý được sắp xếp sao cho việc truy xuất hiệu quả. 8 Bản ghi luận lý (Logical record – LR) vs. Bản ghi vật lý (physical record – PR) 9 Chuyển đổi bản ghi vật lý  Khi CTUD yêu cầu 1 bản ghi luận lý, DBMS định vị bản ghi vật lý tương ứng.  Nếu là thao tác đọc: OS sẽ chuyển bản ghi vật lý chứa bản ghi đó vào vùng bộ nhớ của DBMS. DBMS chuyển bản ghi luận lý đến buffer của CTUD  Nếu là thao tác ghi: quy trình ngược lại. 10 Chuyển đổi bản ghi vật lý 11 Mục tiêu và ràng buộc thiết kế  Tối thiểu thời gian đáp ứng (response time) và thời gian thay đổi CSDL.  Khó khăn thiết kế là phải dự đoán được khi nào yêu cầu bản ghi luận lý sẽ dẫn đến việc chuyển đổi bản ghi vật lý. 12 Thiết kế field (cột)  Mục tiêu chọn kiểu dữ liệu: 1. Represent all possible values. 2. Improve data intergrity 3. Support all data manipulations. 4. Minimize storage space. 13 Thiết kế field (cột)  Trường có giá trị thưa hay quá lớn  lãng phí đĩa  nên mã hóa (coding) 14 Denormalizing  Denormalization is the process of transforming normalized relations into nonnormalized physical record specifications.  Mục đích của denormalization: cải thiện việc truy xuất và xử lý dữ liệu.  Cách thông dụng nhất: kết hợp nhiều bảng luận lý thành 1 bảng vật lý để tránh phải kết nối (join) khi truy xuất dữ liệu từ nhiều bảng. 15 Từ thực tế As in your dorm room, as long as you can find your favorite sweatshirt when you need it, it doesn't matter how tidy the room looks. 16 Các dạng denormalization 1. Two entities with a one-to-one relationship 2. A many-to-many relationship (associative entity) with nonkey attributes 3. Reference data 17 Dạng 1 18 Dạng 2 19 Dạng 3 20 Cân nhắc khi dùng denormalization  Có thể gây ra bất thường và không nhất quán (inconsistent) dữ liệu  Tăng dung lượng lưu trữ 21 Partitioning (phân hoạch)  Phân chia một quan hệ thành nhiều bảng vật lý  Các dạng phân hoạch  Ngang (horizontal partition)  Dọc (Vertical partition)  Vừa ngang vừa dọc (Combination) 22 Phân hoạch ngang Horizontal partition  Phân phối các hàng của 1 quan hệ thành nhiều bảng riêng biệt.  Hai cách phân hoạch:  Single column value (phân hoạch theo city của khách hàng)  Date 23 Ưu điểm của phân hoạch ngang 1. Efficiency: dữ liệu hay truy vấn cùng nhau sẽ được phân hoạch chung, việc bảo trì sẽ hiệu quả hơn. 2. Local optimization: mỗi partition sẽ làm tối ưu hơn khi sử dụng 3. Security: dữ liệu được cô lập theo nhóm người dùng. 4. Recovery and uptime: file nhỏ hơn sẽ tốn ít thời gian hơn cho việc sao lưu và khôi phục 5. Load balancing: các file có thể được lưu trữ ở các đĩa khác nhau  giảm việc xung đột truy xuất đên cùng 1 nơi lưu trữ 24 Nhược điểm của phân hoạch 1. Inconsistent access speed: partition khác nhau có thể có tốc độ truy xuất khác nhau  người dùng bất an. 2. Complexity: Partition thường trong suốt với programmers  gặp khó khăn khi phải kết hợp các partition. 3. Extra space and update time: dữ liệu có thể bị trùng lặp giữa các partition, tốn không gian lưu trữ. Cập nhật dữ liệu liên quan đến nhiều partition sẽ tốn thời gian hơn. 25 Phân hoạch dọc (Vertical partition)  Phân bố các cột của quan hệ luận lý thành nhiều bảng riêng biệt, có khóa chính lặp lại ở mỗi bảng. 26 Thiết kế các file vật lý  File vật lý (physical file) là 1 phần của bộ nhớ thứ cấp (đĩa cứng, hay băng từ) được đặt tên để lưu trữ các bản ghi vật lý.  Một số hệ điều hành (OS) cho phép chia file vật lý thành các extents. 27 Pages and extents  Đơn vị cơ bản để lưu trữ dữ liệu trong SQL Server là page ( kích cỡ page là 8Kb).  Data file (.mdf or .ndf) được chia luận lý thành các page được đánh số thứ tự liên tục từ 0 đến n.  Thao tác I/O được thực hiện ở mức page. 28 Pages and extents  Extents là 1 tập hợp 8 trang liên tiếp nhau được dùng để quản lý hiệu quả các page. 29 File organization  Là kỹ thuật để sắp xếp vật lý các bản ghi của 1 file vào thiết bị lưu trữ thứ cấp.  Các dạng tổ chức file thông dụng  Tuần tự (sequential)  Indexed (chỉ mục)  Hashed 30 File tuần tự Các bản ghi được lưu trữ tuần tự cùng với khóa chính. Để tìm 1 bản ghi: scan từng bản ghi từ đầu file cho đến khi tìm thấy hay đến cuối file. 31 File chỉ mục (indexed file)  Các bản ghi trong file dữ liệu được lưu trữ hoặc tuần tự hoặc không tuần tự.  File chỉ mục là bảng dùng để xác định vị trí các bản ghi theo giá trị khóa.  Tương tự như index trong mỗi quyển sách  Mục đích: Tăng tốc việc dò tìm dữ liệu theo điều kiện. 32 Các loại chỉ mục  Clustered index  Non-clustered index  33 Clustered index  Sắp xếp và lưu tữ các hàng dữ liệu trong bảng theo giá trị khóa.  Mỗi bảng chỉ có 1 clustered index  Các hàng dữ liệu được lưu trữ theo thứ tự của giá trị khóa. Nếu bảng không có clustered index, các hàng sẽ được lưu trữ theo thứ tự được nhập vào. 34 Clustered index  Đa số các DBMS đều tạo chỉ mục tự động cho các trường primary key  bảng chỉ mục được tích hợp vào bảng dữ liệu.  Các chỉ mục trên các trường khác đều là non- clustered. 35 Non-clustered index  Là 1 file tách riêng khỏi bảng dữ liệu.  Là bảng có 2 cột: key và địa chỉ của các bản ghi chứa khóa đó. 36 Tạo chỉ mục  Indexes sẽ được tạo tự động khi định nghĩa các ràng buộc PRIMARY KEY và UNIQUE  Để tạo chỉ mục clustered: 1. CREATE TABLE tablename với tùy chọn ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] 2. CREATE INDEX 37 Tạo chỉ mục CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON ( column [ ASC | DESC ] [ ,...n ] ) 38 Ví dụ  CREATE INDEX idx_Name ON Customer(FirstName+ LastName) 39 Sử dụng chỉ mục  Khi tạo chỉ mục nên cân nhắc giữa việc cải thiện tốc độ truy xuất dữ liệu và việc làm chậm tốc độ cập nhật dữ liệu (insert, delete, update) vì phải cập nhật đồng thời bảng chỉ mục. 40 Sử dụng chỉ mục 1. Chỉ dùng chỉ mục cho các bảng dữ liệu lớn 2. Tạo chỉ mục cho khóa chính 3. Tạo chỉ mục cho các cột thường xuyên xuất hiện trong mệnh đề WHERE của lệnh SQL hay các cột dùng kết nối các bảng 4. Tạo chỉ mục cho các cột trong mệnh đề ORDER BY và GROUP BY (tùy theo DBMS) 41 Sử dụng chỉ mục 5. Sử dụng chỉ mục cho các cột có nhiều giá trị khác nhau (nên có >100 giá trị phân biệt) 6. Thận trọng khi tạo chỉ mục cho các thuộc tính có giá trị null 42 Bài tập 1 Cho các bảng đã chuẩn hóa của CSDL sports league: TEAM(TeamID, TeamName, Teamlocation) PLAYER(PlayerID, PlayerFirstName, PlayerlastName, PlayerDateOfBirth, PlayerSpecialtyCode) SPECIALTY(SpecialtyCode, SpecialtyDescription) CONTRACT(TeamID, PlayerlD, StartTime. EndTime,Salary) LOCATION(LocationID, CityName, CityState,CityCountry, CityPopulation) MANAGER(ManagerID, ManagerName, ManagerTeam) Hãy đề xuất các denormalization và nêu lý do 43 Bài tập 2 Cho 2 lược đồ sau STUDENT(StudentiD, StudentName,CampusAddress,GPA) REGISTRATION(StudentiD, CourseiD, Grade) Và câu truy vấn  SELECT Student_T.StudentiD, StudentName, CourseiD, Grade FROM Student_T, Registration_T WHERE Student_T.StudentiD =Registration_ T.StudentiD AND GPA> 3.0 ORDER BY StudentName; Thuộc tính nào nên tạo chỉ mục để tăng tốc truy vấn trên. 44