Chương 11: Thiết kế và điều chỉnh dữ liệu mức vật lý

Chương 11Thiết kế và điều chỉnh dữ liệu mức vật lý1Giới thiệuĐây là một tệp thu gọn, chứa các thông tin (gọi là pointers) về vị trí thực của các bản ghi trong một tệp cơ sở dữ liệu. Khi truy tìm hoặc xếp loại cơ sở dữ liệu, chương trình sẽ xử dụng bảng index này thay cho toàn bộ cơ sở dữ liệu.Thao tác theo cách đó sẽ nhanh hơn rất nhiều so với việc truy tìm hay sắp xếp tiến hành trên cơ sở dữ liệu thực. Index là một bảng phụ lục liệt kê tất cả các từ, các tên, và các khái niệm theo thứ tự bảng chữ cái và theo số trang mà các thuật ngữ đó xuất hiện.2Thiết kế dữ liệu mức vật lýWorkloads: Việc cần thiết nhất là mô tả chính xác khối Workloads dự kiến gồm các yếu tố :Một danh sách các truy vấn và tần suất truy vấnMột danh sách các bản cập nhậtHiệu suất từng loại truy vấn3Thiết kế dữ liệu mức vật lýĐối với mỗi Query chúng ta phải xác định:Những mối quan hệ.Những thuộc tính được giữ lại (trong mệnh đề SELECT).Các thuộc tính có lựa chọn hoặc điều kiện tham gia thể hiện trên chúng (trong mệnh đề WHERE)4Thiết kế dữ liệu mức vật lýĐối với mỗi lần Update chúng ta phải xác định:Các thuộc tính có lựa chọn hoặc điều kiện tham gia thể hiện trên chúng (trong mệnh đề WHERE)Các loại cập nhật (INSERT, DELETE, UPDATE)Đối với các lệnh UPDATE các fields được sửa đổi.5Thiết kế dữ liệu mức vật lýCâu Query và Update thường có các thông số. Các giá trị của thông số này xác định sự lựa chọn và điều kiện tham gia.Câu Update có một thành phần Query được sử dụng để tìm ra các Tuples6Thiết kế dữ liệu mức vật lýThiết kế vật lý và điều chỉnh các quyết định: Quyết định quan trọng đưa ra dựa trên những điều sau đây:Tạo ra những (Index).Chúng ta nên thay đổi lược đồ khái niệm để nâng cao hiệu suất.Truy vấn phải thường xuyên được thi hành và phải được viết lại để chạy nhanh hơn.7Thiết kế dữ liệu mức vật lýCần điều chỉnh cơ sở dữ liệu:Điều chỉnh một cơ sở dữ liệu sau khi nó đã được thiết kế và triển khai là rất quan trọng.Phân biệt giữa thiết kế cơ sở dữ liệu và điều chỉnh cơ sở dữ liệu là một việc khó.Phải xem xét quá trình thiết kế nhiều hơn một lần từ một lược đồ khái niệm ban đầu được thiết kế và được thực hiện từ một tập hợp các quyết định (Index) và phân nhóm.8Lựa chọn IndexesPhương pháp: Xem xét lần lượt các truy vấn quan trọng nhất. Chọn phương án tốt nhất bằng cách sử dụng các (Indexes) hiện tại, và xem nếu có một kế hoạch tốt hơn để có thể bổ sung thêm (Indexes). Trước khi tạo ra một index, cũng phải xem xét các tác động đến phần Update trong Workloads!Trade-off: (Indexes) có thể thực hiện truy vấn đi nhanh hơn, cập nhật chậm hơn. Yêu cầu thêm không gian đĩa.9Lựa chọn IndexesHướng dẫn 1 (xem index): Không xây dựng (Indexes) truy vấn -bao gồm các thành phần truy vấn của việc cập nhật- không có lợi ích . Bất cứ khi nào có thể, hãy lựa chọn các Indexes làm tăng tốc hơn một truy vấn.Hướng dẫn 2 (lựa chọn khóa cần tìm kiếm): Các thuộc tính được đề cập trong một mệnh đề WHERE là ứng cử viên cho Indexing.10Lựa chọn IndexesHướng dẫn 3: Đa thuộc tính các phím tìm kiếm cần được xem xét khi một mệnh đề WHERE có chứa một số điều kiện.Hướng dẫn 4 (xem cluster): Các (Indexes) trên một mối quan hệ nhất định có thể được nhóm lại, và việc phân nhóm ảnh hưởng đến hiệu suất rất nhiều, do đó, sự lựa chọn (Indexes) của nhóm là rất quan trọng.11Lựa chọn IndexesHướng dẫn 5 (Hash so với cây Index): Khi xem xét điều kiện tham gia:Hash index trên là thích hợp cho Loops Nested Index.Nên được nhóm nếu tham gia cột không phải là khóa, và bộ dữ liệu bên trong cần phải được lấy.Clustered cây B + tham gia cột (s) tốt cho Sort-Merge.12Lựa chọn IndexesHướng dẫn 6 (cân bằng chi phí bảo trì Index): Sau khi xây dựng một danh sách Index như mong muốn, hãy xem xét tác động của mỗi Index về các Update trong Workloads13Ví dụ về cách chọn IndexesVí dụ1: SELECT E.ename, D.mgr FROM Employees E, Departments D WHERE D.dname=`Toy' AND E.dno=D.dnoHash Index trên D.dname chọn ‘Toy'. Vì điều này, index trên D.dno là không cần thiết.Hash index trên E.dno cho phép chúng ta có được sự kết hợp bên trong bộ Emp cho mỗi bộ Dept bên ngoài.Điều gì xảy ra nếu Where bao gồm:"... And E.age = 25"Có thể lấy bộ Emp sử dụng index trên E.age, sau đó tham gia với bộ Dept lựa chọn dname đáp ứng. So sánh với chiến lược sử dụng E.dno index.14Ví dụ về cách chọn IndexesNếu index E.age sẵn sàng được tạo ra,thì các Query này cung cấp ít sự thay đổi cho việc thêm vào index E.dno.15Ví dụ về cách chọn IndexesVí dụ 2:SELECT E.ename, D.mgrFROM Emp E, Dept DWHERE E.sal BETWEEN 10000 AND 20000AND E.hobby=‘Stamps’ AND E.dno=D.dno16Ví dụ về cách chọn IndexesRõ ràng, Emp nên là mối quan hệ bên ngoài. Cho thấy rằng chúng ta xây dựng hash Index trên D.dno. Index nào chúng ta nên xây dựng trên Emp?Cây B + trên E.sal có thể được sử dụng, HOẶC có thể được sử dụng Index trên E.hobby. Chỉ có một trong số này là cần thiết, và điều đó là tốt hơn là phụ thuộc vào việc chọn lọc các điều kiện.Như một quy tắc ngón tay cái, lựa chọn bình đẳng thêm nhiều lựa chọn hơn so với các lựa chọn phạm vi.17Ví dụ về cách chọn IndexesKhi cả hai ví dụ cho thấy, sự lựa chọn các Index được hướng dẫn bởi kế hoạch mà chúng ta mong đợi một cách tối ưu để xem xét cho một truy vấn. Làm tối ưu hóa theo một cách dễ hiểu!18Clustering and Indexing Phạm vi truy vấn là candidates tốt để cải thiện với một clustered index : SELECT E.dno FROM Employees E WHERE E.age > 40Nếu chúng ta có một cây Index B + về age, chúng ta có thể sử dụng nó để lấy bộ duy nhất đáp ứng Select E.age > 40.19Clustering and IndexingCác tinh chỉnh sau đây của các truy vấn trên: SELECT E.dno, COUNT (*) FROM Employees E WHERE E.age > 10 GROUP BY E.dnoNếu cây Index B + có sẵn trên age, chúng ta có thể lấy bộ dữ liệu bằng cách sử dụng nó, sắp xếp các bộ trên dno, và dùng để trả lời các truy vấn.20Clustering and IndexingHãy xem xét các truy vấn GROUP BY .Nếu có nhiều bộ có E.age>10, bằng cách sử dụng Index E.age và phân loại các bộ cần lấy có thể rất tốn kém.Phân nhóm Index E.dno có thể sẽ tốt hơn.Tuy nhiên, điều này có thể không là một kế hoạch tốt nếu hầu như tất cả các nhân viên đều hơn 10 tuổi. Kế hoạch này đặc biệt xấu nếu Index này không nhóm.21Clustering and IndexingClustering cũng rất quan trọng cho Index trên một Key tìm kiếm mà không bao candidate key, đó là Index trong đó một số mục dữ liệu có thể có cùng một giá trị Key. SELECT E.dno FROM Employees E WHERE E.hobby=‘Stamps’Sự bình đẳng giữa các truy vấn và bản saoGiúp đỡ cho việc phân nhóm trên E.hobby!22Clustering and IndexingClustered indexes đặc biệt quan trọng trong khi tham gia vào các mối quan hệ bên trong Index với các vòng lặp lồng nhau.SELECT E.ename, D.mgrFROM Employees E, Departments DWHERE D.dname=`Toy' AND E.dno=D.dno23Clustering and JoinsVí dụ:SELECT E.ename, D.mgrFROM Emp E, Dept DWHERE D.dname=‘Toy’ AND E.dno=D.dnoClustering đặc biệt quan trọng khi truy cập bộ dữ liệu. Nên làm cho Index được nhóm lại trên E.dno.Giả sử rằng mệnh đề WHERE là :Where E.hobby = ‘Stamps AND E.dno = D.dno24Clustering and JoinsNếu nhiều nhân viên thu thập tem, Sort-Merge tham gia có thể xem xét giá trị, sẽ giúp nhóm các Index trên D.dno.Tóm tắt: Clustering rất hữu ích cho bất cứ khi nào bộ được lấy ra.25Indexes on Multiple-attribute search keysĐôi khi nó còn dùng để xây dựng một Index trên một key tìm kiếm có chứa nhiều hơn một FieldVí dụ:SELECT E.eidFROM Employees EWHERE E.age BETWEEN 20 AND 30AND E.sal BETWEEN 3000 AND 5000Để lấy Emp với age= 30 và sal = 4000, một Index trên sẽ là tốt hơn so với một Index về tuổi tác hay một Index về sal.26Indexes on Multiple-attribute search keysIndex như vậy được gọi là Index tổng hợp hoặc nối.Sự lựa chọn khóa Index trực giao còn dùng để phân nhóm v.v.Nếu điều kiện là: 20 hoặc là tốt nhất.Nếu điều kiện là: age = 30 và 3000 tốt hơn nhiều hơn so với !Index tổng hợp càng lớn ,thì Updatecàng thường xuyên hơn27Index-Only PlansMột số truy vấn có thể được trả lời mà không cần lấy bất kỳ bộ dữ liệu từ một hoặc nhiều hơn các mối quan hệ có liên quan nếu đã có một Index thích hợp có sẵn.Ví dụ: Truy vấn này lấy các nhà quản lý của các phòng ban có ít nhất một nhân viên: SELECT D.mgr FROM Departments D, Employees E WHERE D.dno=E.dno28Index-Only PlansVí dụ: Truy vấn này lấy ý tưởng trên và bổ sung thêm SELECT D.mgr, E.eid FROM Departments D, Employees E WHERE D.dno=E.dnoVí dụ: Truy vấn tiếp theo cho thấy làm thế nào sự tổng hợp có thể ảnh hưởng đến sự lựa chọn của các Index : SELECT E.dno, COUNT(*) FROM Employees E GROUP BY E.dno29Tree index!Index-Only PlansVí dụ: Đây là một biến thể của ví dụ trước SELECT E.dno, COUNT(*) FROM Employees E WHERE E.sal=10,000 GROUP BY E.dno 30Index-Only PlansVí dụ: giả sử rằng chúng ta muốn sal tối thiểu cho mỗi dno SELECT E.dno, MIN(E.sal) FROM Employees E GROUP BY E.dnoVí dụ: SELECT AVG (E.sal) FROM Employees E WHERE E.age = 25 AND E.sal BETWEEN 3000 AND 500031Tree index! orTree!Điều chỉnh các lược đồ khái niệm32Điều chỉnh các lược đồ khái niệm33Điều chỉnh các lược đồ khái niệmLựa chọn lược đồ khái niệm cần phải được hướng dẫn bởi Workloads, ngoài các vấn đề dự phòng:Chúng ta có thể giải quyết một lược đồ 3NF hơn là BCNF.Workloads có thể ảnh hưởng đến sự lựa chọn, chúng ta thực hiện phân rã một mối quan hệ thành 3NF hoặc BCNF.Chúng ta có thể tiếp tục phân rã một lược đồ BCNF!Chúng ta có thể denormalize (tức là, lùi lại một bước phân rã), hoặc chúng ta có thể thêm field cho một quan hệ.Chúng ta còn có thể phân rã theo chiều ngang. 34Điều chỉnh các lược đồ khái niệmNếu thay đổi đó được thực hiện sau khi một cơ sở dữ liệu vẫn còn đang được sử dụng, thì đó được gọi là sơ đồ tiến hóa; có thể che giấu một số những thay đổi từ các ứng dụng tùy ý đồ của người định nghĩa.Ví dụ:Contracts (Cid, Sid, Jid, Did, Pid, Qty, Val)Depts (Did, Budget, Report) Suppliers (Sid, Address)Parts (Pid, Cost) Projects (Jid, Mgr) 35Điều chỉnh các lược đồ khái niệmChúng ta sẽ tập trung vào Contracts, ký hiệu là CSJDPQV.Các ràng buộc toàn vẹn sau đây được giữ:JP → C, SD → P, C là Primary Key.Các Candidate key cho CSJDPQV là gì?Hình thức bình thường của lược đồ quan hệ này là gì?36Giải quyết với các chuẩn 3NF và BCNFCSJDPQV có thể được phân tách ra thành SDP và CSJDQV, và cả hai mối quan hệ này đều ở dạng BCNF. (FD cho thấy điều đó?)Bảo toàn thông tin, nhưng không bảo toàn phụ thuộc hàm.Thêm CJP làm cho nó bảo toàn phụ thuộc hàm.37Giải quyết với các chuẩn 3NF và BCNFGiả sử rằng truy vấn này là rất quan trọng:Find the number of copies Q of part P ordered in contract C.Yêu cầu tham gia trên lược đồ phân rã, có thể được trả lời bởi sự kiểm tra của quan hệ gốc CSJDPQV .Có thể dẫn chúng ta để giải quyết cho CSJDPQV giản đồ 3NF.38DenormalizationGiả sử rằng các truy vấn sau đây là quan trọng:Giá trị của Contracts ít hơn ngân sách của các bộ phận?Để tăng tốc độ truy vấn này, chúng ta có thể thêm vào một Field ngân sách B cho Contracts.Này giới thiệu các FD D → B wrt Contracts.Như vậy, Contracts không còn trong 3NF.Chúng ta có thể chọn để sửa đổi Contracts nhưng nếu query này có ảnh hưởng lớn, ta sẽ không đạt được hiệu suất thích hợp.39Lựa chọn hình thức phân rãCó 2 cách để phân rã CSJDPQV sang dạng chuẩn BCNF:SDP và CSJDQV; bảo toàn thông tin nhưng không bảo toàn phụ thuộc hàm.SDP, CSJDQV và CJP; đều bảo toàn.Sự khác biệt giữa chúng chính là chi phí thực thi FD JP → C. 40Lựa chọn hình thức phân rã2nd phân rã : Index trên JP quan hệ trên CJP.1st: 41CREATE ASSERTION CheckDep CHECK ( NOT EXISTS  ( SELECT * FROM PartInfo P, ContractInfo C WHERE P.sid=C.sid AND P.did=C.did GROUP BY C.jid, P.pid HAVING COUNT (C.cid) > 1 ))Lựa chọn hình thức phân rãTheo đó FDs sau đây đã được đưa ra: JP → C, SD → P, C là các Primary Key.Giả sử rằng một nhà cung cấp nhất định luôn luôn tính cùng một giá cho một phần nhất định: SPQ → V.Nếu chúng ta quyết định rằng chúng ta muốn phân rã CSJDPQV thành BCNF, bây giờ chúng ta có một sự lựa chọn thứ ba:Bắt đầu bằng cách phân rã nó vào SPQV và CSJDPQ.Sau đó, phân rã CSJDPQ (không ở 3NF) thành SDP, CSJDQ.Điều này cho chúng ta sự bảo toàn thông tin: SPQV, SDP, CSJDQ.42Lựa chọn hình thức phân rãĐể bảo toàn JP → C, chúng ta có thể thêm CJP, như trước đây.Lựa chọn: {SPQV, SDP, CSJDQ} Hay {SDP, CSJDQV}?Giả sử chúng ta lựa chọn {SDP, CSJDQV}. Điều này là ở BCNF, và không có lý do gì để phân rã hơn nữa. 43Phân rã của một quan hệ BCNFTuy nhiên, giả sử rằng các truy vấn này là rất quan trọng:Tìm các Contracts được tổ chức bởi nhà cung cấp S.Tìm các Contracts bộ phận D có liên quan đến.Phân rã CSJDQV thêm vào CS, CD và CJQV có thể tăng tốc độ các truy vấn này. (Tại sao?)Mặt khác, các truy vấn sau đây chậm hơn:Tìm tổng giá trị của tất cả các Contracts được tổ chức bởi nhà cung cấp S.44Phân rã theo chiều ngangĐịnh nghĩa phân rã của chúng ta cho đến nay: Quan hệ được thay thế bởi một tập hợp các mối quan hệ như một phần thêm vào . Đây là phân rã theo chiều dọc . Trường hợp quan trọng nhất.Đôi khi, có thể thay thế mối quan hệ bởi một tập hợp các mối quan hệ , đó là sự lựa chọn. Đây là phân rã theo chiều ngang .Mỗi quan hệ mới có cùng một lược đồ như bản gốc, nhưng là tập con của các hàng.Một cách đúng đắn, các quan hệ mới phải chứa tất cả các hàng của bản gốc. Đặc trưng, các quan hệ mới không khớp với nhau.45Phân rã theo chiều ngangCiả sử rằng các Contracts có value> 10000 có quy định khác nhau. Điều này có nghĩa rằng các truy vấn về Contracts thường sẽ có các điều kiện val> 10000.Cách để đối phó với điều này là xây dựng một nhóm cây index B + trên các Field val của Contracts.Cách tiếp cận thứ hai là thay thế cho Contracts bởi hai mối quan hệ mới: LargeContracts và SmallContracts, với các thuộc tính như nhau (CSJDPQV).46Phân rã theo chiều ngangThực hiện như index trên các truy vấn như vậy, nhưng không có index trên không.Ngoài ra, có thể xây dựng các index nhóm trên các thuộc tính khác.47Masking Conceptual Schema ChangesVí dụ:CREATE VIEW Contracts(cid, sid, jid, did, pid, qty, val) AS SELECT * FROM LargeContracts UNION SELECT * FROM SmallContracts48Masking Conceptual Schema ChangesViệc thay thế Contracts bởi LargeContracts và SmallContracts có thể được che đậy bởi xem.Tuy nhiên, các truy vấn với điều kiện val> 10000 phải được yêu cầu LargeContracts wrt để thực hiện hiệu quả: người dùng nên quan tâm đến hiệu suất phải được nhận thức của sự thay đổi49Tuning Queries and ViewsNếu một truy vấn chạy chậm hơn so với dự kiến, kiểm tra nếu một index cần phải được xây dựng lại, hoặc nếu các số liệu thống kê quá già.Đôi khi, các DBMS có thể không được thực hiện các kế hoạch bạn đã có trong tâm trí. Các khu vực chung của sự yếu đuối:Lựa chọn liên quan đến các giá trị null.Lựa chọn liên quan đến biểu thức số học hoặc chuỗi.Lựa chọn liên quan đến HOẶC điều kiện. 50Tuning Queries and ViewsThiếu các tính năng đánh giá như chiến lược hoặc các phương pháp index tham gia nhất định hoặc ước lượng kích cỡ.Kiểm tra các kế hoạch đang được sử dụng! Sau đó điều chỉnh sự lựa chọn của các index hoặc viết lại các truy vấn / xem.519.11 Viết lại truy vấn SQL. Viết lại truy vấn SQLPhức tạp bởi sự tương tác của:NULLs, bản sao, tập hợp, truy vấn con.Phương châm:Sử dụng chỉ có một "khối truy vấn", nếu có thểNhưng không phải lúc nào cũng có thể ... 53The Notorious COUNT Bug54SELECT dname FROM Department D WHERE D.num_emps > (SELECT COUNT(*) FROM Employee E WHERE D.building = E.building)CREATE VIEW Temp (empcount, building) AS SELECT COUNT(*), E.building FROM Employee E GROUP BY E.buildingSELECT dname FROM Department D,Temp WHERE D.building = Temp.building AND D.num_emps > Temp.empcount;* Điều gì sẽ xảy ra khi Employee bị để trống?Tóm tắt thông tin (thiết kế)Thiết kế cơ sở dữ liệu bao gồm một số bước: phân tích yêu cầu, thiết kế ý tưởng, sàng lọc sơ đồ, thiết kế vật lý và điều chỉnh.Nói chung phải làm đi làm lại giữa các bước trên này để hoàn thiện thiết kế cơ sở dữ liệu, và quyết định một trong những bước có thể ảnh hưởng đến sự lựa chọn công việc khác.Hiểu biết về bản chất của Workloads cho các ứng dụng, và mục tiêu hoạt động, là điều cần thiết để phát triển một thiết kế tốt.Các truy vấn và cập nhật quan trọng là gì? Thuộc tính / quan hệ có liên quan thì sao?55Tóm tắt thông tin (thiết kế)Index phải được lựa chọn để tăng tốc độ các Index truy vấn quan trọng và chi phí bảo trì trên cập nhật cho các key Fields.Chọn Indexes có thể giúp đỡ nhiều truy vấn, nếu có thể.Xây dựng các Indexes để hỗ trợ chiến lược Index-Only.Clustering là một quyết định quan trọng, chỉ có các Index về một mối quan hệ nhất định có thể được nhóm lại!Order of fields in composite index key can be important.Indexes tĩnh có thể phải được xây dựng lại theo định kỳ.Thống kê phải được cập nhật định kỳ.56Tóm tắt thông tin (thiết kế)Lược đồ khái niệm cần được tinh chế bằng cách xem xét các tiêu chí thực hiện và khối Workloads:Có thể chọn 3NF hoặc thấp hơn hình thức bình thường hơn BCNF.Có thể chọn giữa các phân tách thay thế thành BCNF (hoặc 3NF) dựa trên khối Workloads.Có thể denormalize, hay khôi phục một số phân tách.Có thể phân hủy một mối quan hệ BCNF hơn nữa!Có thể chọn một phân hủy ngang của một quan hệ. 57Tóm tắt thông tin (thiết kế)Theo thời gian, index phải được điều chỉnh (giảm xuống, tạo ra, xây dựng lại, ...) để thực hiện.Nên xác định kế hoạch sử dụng bởi hệ thống, và điều chỉnh sự lựa chọn của các index thích hợp.Hệ thống có thể vẫn không tìm thấy một kế hoạch tốt:Chỉ có kế hoạch trái sâu xem xét!Giá trị Null, điều kiện số học, biểu thức chuỗi, sử dụng ORS, vv có thể nhầm lẫn một ưu hoa.Vì vậy, có thể phải viết lại các truy vấn / xem:Tránh các truy vấn lồng nhau, quan hệ tạm thời, điều kiện phức tạp, và các hoạt động như DISTINCT và GROUP BY 58