| Postgresql 8.3.23 Tài liệu | ||||
|---|---|---|---|---|
| prev | Backward nhanh | Chương 5. Định nghĩa dữ liệu | Chuyển tiếp nhanh | Tiếp theo |
PostgreSQLHỗ trợ cơ bản cách đọc kèo bóng đá bảng. Phần này mô tả lý do tại sao và làm thế nào để Triển khai cách đọc kèo bóng đá như một phần của thiết kế cơ sở dữ liệu của bạn.
cách đọc kèo bóng đá đề cập đến việc phân tách những gì một cách logic một cách hợp lý Bảng thành các mảnh vật lý nhỏ hơn. cách đọc kèo bóng đá có thể cung cấp Một số lợi ích:
Hiệu suất truy vấn có thể được cải thiện đáng kể trong một số tình huống, đặc biệt là khi hầu hết các tình huống các hàng được truy cập của bảng nằm trong một cách đọc kèo bóng đá hoặc một số lượng nhỏ các cách đọc kèo bóng đá. Các cách đọc kèo bóng đá thay thế cho các cột hàng đầu của các chỉ mục, giảm kích thước chỉ số và làm cho nhiều khả năng các phần được sử dụng nhiều của chỉ mục phù hợp với bộ nhớ.
Khi các truy vấn hoặc cập nhật truy cập một tỷ lệ phần trăm lớn của A cách đọc kèo bóng đá duy nhất, hiệu suất có thể được cải thiện bằng cách sử dụng Lợi thế của việc quét tuần tự cách đọc kèo bóng đá đó thay vì sử dụng một chỉ mục và truy cập ngẫu nhiên đọc được phân tán trên toàn bộ bảng.
11158_11314Thay đổi BÀNnhanh hơn nhiều so với hoạt động hàng loạt. Nó cũng hoàn toàn tránhNút hútChi phí gây ra bởi một số lượng lớnXóa.
Dữ liệu hiếm khi được sử dụng có thể được di chuyển sang rẻ hơn và chậm hơn phương tiện lưu trữ.
Những lợi ích thường sẽ chỉ có giá trị khi một bảng nếu không thì sẽ rất lớn. Điểm chính xác mà một bảng sẽ được hưởng lợi từ việc cách đọc kèo bóng đá phụ thuộc vào ứng dụng, mặc dù một quy tắc ngón tay cái là kích thước của bảng nên vượt quá bộ nhớ vật lý của máy chủ cơ sở dữ liệu.
Hiện tại,PostgreSQLHỗ trợ cách đọc kèo bóng đá qua kế thừa bảng. Mỗi cách đọc kèo bóng đá Phải được tạo như một bảng con của một bảng cha mẹ đơn lẻ. Các Bản thân bảng cha mẹ thường trống; nó tồn tại chỉ để Đại diện cho toàn bộ tập dữ liệu. Bạn nên quen thuộc với kế thừa (xemPhần 5.8) Trước khi cố gắng thiết lập cách đọc kèo bóng đá.
Các hình thức cách đọc kèo bóng đá sau đây có thể được thực hiện trongPostgreSQL:
Bảng được cách đọc kèo bóng đá thành"Phạm vi"Được xác định bởi một cột hoặc bộ các cột, không có sự chồng chéo giữa các phạm vi của các giá trị được chỉ định cho các cách đọc kèo bóng đá khác nhau. Ví dụ một người có thể cách đọc kèo bóng đá theo phạm vi ngày hoặc các phạm vi định danh cho các đối tượng kinh doanh cụ thể.
Bảng được cách đọc kèo bóng đá bằng cách liệt kê rõ ràng Các giá trị khóa xuất hiện trong mỗi cách đọc kèo bóng đá.
Để thiết lập bảng được cách đọc kèo bóng đá, làm như sau:
Tạo"Master"Bảng, từ đó tất cả các cách đọc kèo bóng đá sẽ kế thừa.
Bảng này sẽ không chứa dữ liệu. Không xác định bất kỳ séc nào những ràng buộc trên bảng này, trừ khi bạn có ý định áp dụng như nhau cho tất cả các cách đọc kèo bóng đá. Không có điểm nào trong Xác định bất kỳ chỉ mục hoặc các ràng buộc duy nhất trên nó, hoặc.
Tạo một số"Child"Bảng mà mỗi kế thừa từ bảng chính. Thông thường, những điều này cách đọc kèo bóng đá bảng sẽ không thêm bất kỳ cột nào vào tập hợp được kế thừa từ Master.
Chúng tôi sẽ gọi các bảng con là cách đọc kèo bóng đá Họ theo mọi cách bình thườngPostgreSQLBảng.
Thêm các ràng buộc bảng vào các bảng cách đọc kèo bóng đá để xác định các giá trị khóa được phép trong mỗi cách đọc kèo bóng đá.
Ví dụ điển hình sẽ là:
14565_14707
Đảm bảo rằng các ràng buộc đảm bảo rằng không có sự chồng chéo giữa các giá trị khóa được phép trong các khác nhau cách đọc kèo bóng đá. Một sai lầm phổ biến là thiết lập các ràng buộc phạm vi Như thế này:
Kiểm tra (Outletid từ 100 đến 200) Kiểm tra (Outletid từ 200 đến 300)
Điều này là sai vì không rõ cách đọc kèo bóng đá nào Giá trị khóa 200 thuộc về.
Lưu ý rằng không có sự khác biệt về cú pháp giữa phạm vi và danh sách cách đọc kèo bóng đá; Những thuật ngữ đó được mô tả chỉ một.
Đối với mỗi cách đọc kèo bóng đá, hãy tạo một chỉ mục trên khóa (Các) cột, cũng như bất kỳ chỉ mục nào khác mà bạn có thể muốn. (Chỉ số chính không hoàn toàn cần thiết, nhưng trong hầu hết kịch bản nó là hữu ích. Nếu bạn dự định các giá trị chính sẽ độc đáo thì bạn nên luôn luôn tạo ra một ràng buộc khóa chính cho từng cách đọc kèo bóng đá.)
Tùy chọn, xác định trình kích hoạt hoặc quy tắc để chuyển hướng dữ liệu chèn vào bảng chính để thích hợp cách đọc kèo bóng đá.
Đảm bảo rằngurdaint_exclusionTham số cấu hình được bật trongPostgreSql.conf. Không có điều này, cách đọc kèo bóng đá truy vấn sẽ không được tối ưu hóa như mong muốn.
16207_16436
16471_16622
Chúng tôi biết rằng hầu hết cách đọc kèo bóng đá truy vấn sẽ truy cập chỉ vào tuần trước, Dữ liệu của tháng hoặc quý, vì việc sử dụng chính bảng này Sẽ được chuẩn bị báo cáo trực tuyến cho quản lý. Để giảm lượng dữ liệu cũ cần được lưu trữ, chúng tôi quyết định chỉ Giữ dữ liệu trị giá 3 năm gần đây nhất. Vào đầu Mỗi tháng chúng tôi sẽ xóa dữ liệu tháng cũ nhất.
Trong tình huống này, chúng ta có thể sử dụng cách đọc kèo bóng đá để giúp chúng ta gặp gỡ Tất cả các yêu cầu khác nhau của chúng tôi cho bảng đo. Thực hiện theo các bước được nêu ở trên, việc cách đọc kèo bóng đá có thể được thiết lập Như sau:
Bảng chính làĐo lườngbảng, được khai báo chính xác là bên trên.
Tiếp theo chúng tôi tạo một cách đọc kèo bóng đá cho mỗi tháng hoạt động:
Tạo bảng đo_Y2006M02 () kế thừa (đo lường); Tạo bảng đo_Y2006M03 () kế thừa (đo lường); ... Tạo bảng đo_Y2007M11 () kế thừa (đo lường); Tạo bảng đo_Y2007M12 () kế thừa (đo lường); Tạo bảng đo_Y2008M01 () kế thừa (đo lường);
Mỗi cách đọc kèo bóng đá là các bảng hoàn chỉnh đúng, nhưng họ thừa hưởng định nghĩa của họ từĐo lườngBảng.
Điều này giải quyết một trong những vấn đề của chúng tôi: xóa dữ liệu cũ. Mỗi tháng, tất cả những gì chúng ta sẽ cần làm là thực hiện mộtBẢNG DROPtrên bàn trẻ nhất và Tạo bảng con mới cho dữ liệu của tháng mới.
Chúng tôi phải cung cấp các ràng buộc bảng không chồng chéo. Thay vì chỉ tạo các bảng cách đọc kèo bóng đá như trên, tập lệnh tạo bảng phải thực sự là:
Tạo bảng đo_Y2006M02 (
Kiểm tra (Logdate = Date '2006-02-01' và Logdate <Date '2006-03-01'))
) Kế thừa (đo lường);
Tạo bảng đo_Y2006M03 (
Kiểm tra (Logdate = Date '2006-03-01' và Logdate <Date '2006-04-01'))
) Kế thừa (đo lường);
...
Tạo bảng đo_Y2007M11 (
Kiểm tra (logdate = ngày '2007-11-01' và logdate <ngày '2007-12-01'))
) Kế thừa (đo lường);
Tạo bảng đo_Y2007M12 (
Kiểm tra (logdate = ngày '2007-12-01' và logdate <ngày '2008-01-01'))
) Kế thừa (đo lường);
Tạo bảng đo_Y2008M01 (
Kiểm tra (Logdate = Date '2008-01-01' và Logdate <Date '2008-02-01'))
) Kế thừa (đo lường);
Chúng ta cũng cần cách đọc kèo bóng đá chỉ mục trên cách đọc kèo bóng đá cột chính:
19404_19796
Chúng tôi chọn không thêm cách đọc kèo bóng đá chỉ mục khác vào thời điểm này.
Chúng tôi muốn ứng dụng của chúng tôi có thể nóiChèn vào phép đo ...và có Dữ liệu được chuyển hướng vào bảng cách đọc kèo bóng đá thích hợp. Chúng tôi có thể sắp xếp điều đó bằng cách gắn chức năng kích hoạt phù hợp đến bảng chính. Nếu dữ liệu sẽ chỉ được thêm vào cách đọc kèo bóng đá mới nhất, chúng ta có thể sử dụng một trình kích hoạt rất đơn giản chức năng:
Tạo hoặc thay thế chức năng Đo_insert_trigger ()
Trả về kích hoạt là $$
BẮT ĐẦU
Chèn vào cách đọc kèo bóng đá giá trị đo_Y2008M01 (mới.*);
Trả lại null;
KẾT THÚC;
$$
Ngôn ngữ plpgsql;
Sau khi tạo chức năng, chúng tôi tạo một trình kích hoạt mà gọi hàm kích hoạt:
Tạo Trigger Insert_Meas mua sắm_Trigger
Trước khi chèn vào phép đo
Cho mỗi hàng thực thi quy trình đo_INSERT_TRIGGER ();
Chúng ta phải xác định lại chức năng kích hoạt mỗi tháng để Nó luôn luôn chỉ vào cách đọc kèo bóng đá hiện tại. Kích hoạt Định nghĩa không cần phải được cập nhật, tuy nhiên.
Chúng tôi có thể muốn chèn dữ liệu và có máy chủ tự động xác định vị trí cách đọc kèo bóng đá mà hàng nên được thêm vào. Chúng ta có thể làm điều này với một phức tạp hơn Chức năng kích hoạt, ví dụ:
21270_21973
Định nghĩa kích hoạt giống như trước đây. Lưu ý rằng mỗinếuKiểm tra phải khớp chính xác với nhau TheKiểm traràng buộc cho nó cách đọc kèo bóng đá.
Trong khi hàm này phức tạp hơn Trường hợp một tháng, nó không cần phải được cập nhật thường xuyên, Vì cách đọc kèo bóng đá nhánh có thể được thêm trước khi cần thiết.
Lưu ý:Trong thực tế, tốt nhất là kiểm tra cách đọc kèo bóng đá mới nhất đầu tiên, nếu hầu hết các phần chèn đi vào cách đọc kèo bóng đá đó. Để đơn giản, chúng tôi đã thể hiện Các bài kiểm tra của kích hoạt theo cùng một thứ tự như trong các phần khác của Ví dụ này.
Như chúng ta có thể thấy, một sơ đồ cách đọc kèo bóng đá phức tạp có thể yêu cầu một Số lượng đáng kể của DDL. Trong ví dụ trên, chúng tôi sẽ là Tạo một cách đọc kèo bóng đá mới mỗi tháng, vì vậy có thể là khôn ngoan khi Viết tập lệnh tạo DDL cần thiết tự động.
Thông thường tập hợp các cách đọc kèo bóng đá được thiết lập khi ban đầu Xác định bảng không nhằm mục đích tĩnh. Nó là phổ biến muốn xóa các cách đọc kèo bóng đá dữ liệu cũ và Định kỳ thêm các cách đọc kèo bóng đá mới cho dữ liệu mới. Một trong những lợi thế quan trọng của việc cách đọc kèo bóng đá chính xác là nó cho phép nhiệm vụ đau đớn này được thực hiện gần như ngay lập tức bằng cách thao túng cấu trúc cách đọc kèo bóng đá, thay vào đó hơn một lượng lớn dữ liệu di chuyển về mặt vật lý xung quanh.
Tùy chọn đơn giản nhất để xóa dữ liệu cũ chỉ đơn giản là bỏ rơi cách đọc kèo bóng đá không còn cần thiết nữa:
23920_23956
Điều này có thể nhanh chóng xóa hàng triệu bản ghi vì nó không cần phải xóa riêng mọi bản ghi.
một tùy chọn khác thường thích hơn là xóa cách đọc kèo bóng đá từ bảng được cách đọc kèo bóng đá nhưng giữ quyền truy cập vào nó như một bảng theo đúng nghĩa của nó:
thay đổi bảng đo_Y2006M02 Không đo lường kế thừa;
Điều này cho phép cách đọc kèo bóng đá hoạt động tiếp theo được thực hiện trên dữ liệu trước khi nó bị bỏ. Ví dụ: đây thường là một thời gian hữu ích Để sao lưu dữ liệu bằngCopy, pg_dumphoặc cách đọc kèo bóng đá công cụ tương tự. Nó cũng có thể là thời điểm hữu ích để tổng hợp dữ liệu thành nhỏ hơn định dạng, thực hiện cách đọc kèo bóng đá thao tác dữ liệu khác hoặc chạy báo cáo.
Tương tự chúng ta có thể thêm một cách đọc kèo bóng đá mới để xử lý dữ liệu mới. Chúng tôi có thể tạo một cách đọc kèo bóng đá trống trong bảng được cách đọc kèo bóng đá giống như Các cách đọc kèo bóng đá ban đầu đã được tạo ở trên:
Tạo bảng đo_Y2008M02 (
Kiểm tra (Logdate = Date '2008-02-01' và Logdate <Date '2008-03-01'))
) Kế thừa (đo lường);
25121_25392
Tạo bảng đo_Y2008M02 (Giống như đo lường bao gồm cách đọc kèo bóng đá mặc định bao gồm cách đọc kèo bóng đá ràng buộc); Thay đổi bảng đo_Y2008M02 Thêm ràng buộc Y2008M02 Kiểm tra (logdate = ngày '2008-02-01' và logdate <ngày '2008-03-01'); \ Sao chép Đo_Y2008M02 từ 'Đo lường_Y2008M02' - Có thể một số công việc chuẩn bị dữ liệu khác Thay đổi bảng đo_Y2008M02 Đo lường kế thừa;
Loại trừ ràng buộclà một truy vấn kỹ thuật tối ưu hóa giúp cải thiện hiệu suất cho Các bảng cách đọc kèo bóng đá được xác định trong thời trang được mô tả ở trên. BẰNG Một ví dụ:
Đặt ràng buộc_exclusion = ON; Chọn Đếm (*) từ Đo lường trong đó LogDate = Date '2008-01-01';
Không loại trừ ràng buộc, truy vấn trên sẽ quét Mỗi cách đọc kèo bóng đá củaĐo lườngBảng. Với sự loại trừ ràng buộc được bật, người lập kế hoạch sẽ kiểm tra các ràng buộc của mỗi cách đọc kèo bóng đá và cố gắng chứng minh rằng cách đọc kèo bóng đá không cần phải được quét vì nó không thể chứa bất kỳ hàng nào gặp Truy vấn củaWHEREmệnh đề. Khi người lập kế hoạch có thể chứng minh điều này, nó không bao gồm cách đọc kèo bóng đá khỏi truy vấn kế hoạch.
26942_26960Giải thíchLệnh đến Hiển thị sự khác biệt giữa một kế hoạch vớiurdaint_exclusiontrên và một kế hoạch với nó. Một gói mặc định điển hình cho loại thiết lập bảng này là:
Đặt ràng buộc_exclusion = TẮT;
Giải thích Chọn Đếm (*) từ Đo lường trong đó LogDate = Date '2008-01-01';
Kế hoạch truy vấn
-------------------------------------------------------------------------------------------------------
Tổng hợp (chi phí = 158,66..158,68 hàng = 1 chiều rộng = 0)
- Phụ lục (chi phí = 0,00..151,88 hàng = 2715 chiều rộng = 0)
- SEQ quét khi đo (chi phí = 0,00..30,38 hàng = 543 chiều rộng = 0)
Bộ lọc: (Logdate = '2008-01-01' :: Date)
- SEQ SCAN TRÊN Đo lường_Y2006M02 Đo (chi phí = 0,00..30,38 hàng = 543 chiều rộng = 0)
Bộ lọc: (Logdate = '2008-01-01' :: Date)
- SEQ SCAN TRÊN Đo lường_Y2006M03 Đo lường (chi phí = 0,00..30,38 hàng = 543 chiều rộng = 0)
Bộ lọc: (Logdate = '2008-01-01' :: Date)
...
- SEQ SCAN TRÊN Đo lường_Y2007M12 Đo lường (chi phí = 0,00..30,38 hàng = 543 chiều rộng = 0)
Bộ lọc: (Logdate = '2008-01-01' :: Date)
- SEQ SCAN TRÊN Đo lường_Y2008M01 Đo lường (chi phí = 0,00..30,38 hàng = 543 chiều rộng = 0)
Bộ lọc: (Logdate = '2008-01-01' :: Date)
Một số hoặc tất cả các cách đọc kèo bóng đá có thể sử dụng quét chỉ mục thay thế của các lần quét tuần tự bàn đầy đủ, nhưng vấn đề ở đây là Không cần phải quét các cách đọc kèo bóng đá cũ hơn để trả lời Truy vấn này. Khi chúng tôi kích hoạt loại trừ ràng buộc, chúng tôi sẽ nhận được Kế hoạch giảm đáng kể sẽ cung cấp tương tự trả lời:
28724_29380
Lưu ý rằng loại trừ ràng buộc chỉ được điều khiển bởiKiểm tra29477_29877
30075_30241
30276_30709
một quy tắc có chi phí cao hơn đáng kể so với kích hoạt, nhưng Chi phí được thanh toán một lần cho mỗi truy vấn thay vì một lần mỗi hàng, Vì vậy, phương pháp này có thể là lợi thế cho chậu bảo tình huống. Tuy nhiên, trong hầu hết cách đọc kèo bóng đá trường hợp, phương pháp kích hoạt sẽ Cung cấp hiệu suất tốt hơn.
lưu ý rằngCopyBỏ qua cách đọc kèo bóng đá quy tắc. Nếu bạn muốn sử dụngCopyđể chèn dữ liệu, bạn sẽ cần sao chép vào bảng cách đọc kèo bóng đá chính xác thay vì vào chủ.Copykhông kích hoạt lửa, vì vậy bạn có thể sử dụng nó bình thường nếu bạn sử dụng Phương pháp kích hoạt.
Một nhược điểm khác của phương pháp quy tắc là có Không có cách nào đơn giản để buộc lỗi nếu tập hợp cách đọc kèo bóng đá quy tắc không Bao gồm ngày chèn; Dữ liệu sẽ âm thầm đi vào thay vào đó, bảng chính.
cách đọc kèo bóng đá cũng có thể được sắp xếp bằng cách sử dụngLiên minh tất cả31665_31720
Tạo phép đo xem là
Chọn * Từ Đo lường_Y2006M02
Liên minh tất cả chọn * từ đo_Y2006M03
...
Liên minh tất cả chọn * từ số đo_Y2007M11
Liên minh tất cả chọn * từ số đo_Y2007M12
Liên minh tất cả Chọn * từ Đo lường_Y2008M01;
Tuy nhiên, sự cần thiết phải tạo lại chế độ xem thêm một bước bổ sung để Thêm và thả các cách đọc kèo bóng đá riêng lẻ của tập dữ liệu. TRONG Thực hành phương pháp này có rất ít để đề xuất nó so với sử dụng kế thừa.
Các cảnh báo sau đây áp dụng cho các bảng được cách đọc kèo bóng đá:
Không có cách tự động nào để xác minh rằng tất cả cách đọc kèo bóng đáKiểm traCác ràng buộc là lẫn nhau loại trừ. Sẽ an toàn hơn khi tạo mã tạo ra cách đọc kèo bóng đá và tạo và/hoặc sửa đổi các đối tượng liên quan hơn là viết mỗi bằng tay.
Các sơ đồ được hiển thị ở đây giả sử rằng khóa cách đọc kèo bóng đá Cột của một hàng không bao giờ thay đổi, hoặc ít nhất là không thay đổi Đủ để yêu cầu nó chuyển sang cách đọc kèo bóng đá khác. MỘTCập nhật33028_33089Kiểm traràng buộc. Nếu bạn cần xử lý những trường hợp như vậy, bạn có thể đặt Cập nhật phù hợp kích hoạt trên các bảng cách đọc kèo bóng đá, nhưng nó làm cho việc quản lý cấu trúc hơn nhiều phức tap.
33362_33388Nút húthoặccách đọc kèo bóng đá tích33461_33575
cách đọc kèo bóng đá tích đo lường;
Sẽ chỉ xử lý bảng chính.
cách đọc kèo bóng đá cảnh báo sau đây áp dụng cho loại trừ ràng buộc:
Loại trừ ràng buộc chỉ hoạt động khi truy vấnWHEREmệnh đề chứa hằng số. MỘT
Truy vấn được tham số hóa sẽ không được tối ưu hóa, vì
Người lập kế hoạch không thể biết cách đọc kèo bóng đá nào giá trị tham số
có thể chọn thời gian chạy. Vì lý do tương tự,"ổn định"cách đọc kèo bóng đá chức năng nhưcurrent_datephải tránh.
Giữ cho các ràng buộc cách đọc kèo bóng đá đơn giản, khác là Người lập kế hoạch có thể không thể chứng minh rằng các cách đọc kèo bóng đá không cần được ghé thăm. Sử dụng các điều kiện bình đẳng đơn giản cho danh sách cách đọc kèo bóng đá hoặc kiểm tra phạm vi đơn giản cho cách đọc kèo bóng đá phạm vi, như minh họa trong các ví dụ trước. Một quy tắc tốt của ngón tay cái là các ràng buộc cách đọc kèo bóng đá chỉ nên chứa So sánh (các) cột cách đọc kèo bóng đá với các hằng số Sử dụng các toán tử B-Tree-Index có thể chỉ định.
Tất cả các ràng buộc trên tất cả các cách đọc kèo bóng đá của bảng chính được kiểm tra trong quá trình loại trừ ràng buộc, vì vậy số lượng lớn cách đọc kèo bóng đá có khả năng tăng thời gian lập kế hoạch truy vấn đáng chú ý. cách đọc kèo bóng đá bằng cách sử dụng các kỹ thuật này sẽ hoạt động Vâng, có lẽ lên tới một trăm cách đọc kèo bóng đá; Đừng cố gắng sử dụng hàng ngàn cách đọc kèo bóng đá.