5.10. soi kèo bóng đá truoctran

5.10.1. Tổng quan

soi kèo bóng đá truoctran đề cập đến việc chia những gì một cách logic một bảng lớn thành các mảnh vật lý nhỏ hơn. soi kèo bóng đá truoctran 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 các tình huống nhất định, đặc biệt khi hầu hết các hàng truy cập mạnh của bảng nằm trong một soi kèo bóng đá truoctran hoặc một số lượng nhỏ các soi kèo bóng đá truoctran. soi kèo bóng đá truoctran 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 các 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 một soi kèo bóng đá truoctran, hiệu suất có thể được cải thiện bằng cách tận dụng việc quét tuần tự soi kèo bóng đá truoctran đó thay vì sử dụng chỉ mục và truy cập ngẫu nhiên đọc phân tán trên toàn bộ bảng.

• Tải và xóa số lượng lớn có thể được thực hiện bằng cách thêm hoặc loại bỏ các soi kèo bóng đá truoctran, nếu yêu cầu đó được lên kế hoạch vào thiết kế soi kèo bóng đá truoctran.ALTER TABLE NO INHERITvàBẢNG DROPCả hai đều nhanh hơn nhiều so với hoạt động hàng loạt. soi kèo bóng đá truoctran lệnh này 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 phương tiện lưu trữ rẻ hơn và chậm hơn.

Các lợi ích thường sẽ chỉ có giá trị khi một bảng sẽ rất lớn. Điểm chính xác mà bảng sẽ được hưởng lợi từ việc soi kèo bóng đá truoctran phụ thuộc vào ứng dụng, mặc dù quy tắc ngón tay cái là kích thước của bảng phải vượt quá bộ nhớ vật lý của máy chủ cơ sở dữ liệu.

Hiện tại,PostgreSQLHỗ trợ soi kèo bóng đá truoctran qua kế thừa bảng. Mỗi soi kèo bóng đá truoctran phải được tạo như một bảng con của một bảng cha mẹ đơn lẻ.Phần 5.9) before attempting to set up partitioning.

Các hình thức soi kèo bóng đá truoctran sau đây có thể được thực hiện trongPostgreSQL:

5.10.2. Triển khai soi kèo bóng đá truoctran

Để thiết lập bảng được soi kèo bóng đá truoctran, làm như sau:

1. Tạo"Master"table, from which all of the partitions will inherit.

   Bảng này sẽ không chứa dữ liệu. Không xác định bất kỳ ràng buộc kiểm tra nào trên bảng này, trừ khi bạn có ý định áp dụng như nhau cho tất cả các soi kèo bóng đá truoctran.

2. Tạo một số"Child"Bảng mà mỗi người kế thừa từ bảng chính. Thông thường, soi kèo bóng đá truoctran bảng này sẽ không thêm bất kỳ cột nào vào tập hợp được kế thừa từ chủ.

   Chúng tôi sẽ gọi các bảng con là soi kèo bóng đá truoctran, mặc dù chúng theo mọi cách bình thườngPostgreSQLBảng (hoặc, có thể, soi kèo bóng đá truoctran bảng nước ngoài).

3. Thêm các ràng buộc bảng vào các bảng soi kèo bóng đá truoctran để xác định các giá trị khóa được phép trong mỗi soi kèo bóng đá truoctran.

   Ví dụ điển hình sẽ là:

   Kiểm tra (x = 1)

   Đả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 soi kèo bóng đá truoctran khác nhau. Một lỗi phổ biến là thiết lập các ràng buộc phạm vi như:

   Kiểm tra (Outletid từ 100 đến 200)

   Điều này là sai vì không rõ soi kèo bóng đá truoctran giá trị chính 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à soi kèo bóng đá truoctran danh sách; Các thuật ngữ đó chỉ mô tả.

4. Đối với mỗi soi kèo bóng đá truoctran, hãy tạo một chỉ mục trên (các) cột chính, cũng như bất kỳ chỉ mục nào khác mà bạn có thể muốn. .

5. 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 được chèn vào bảng chính đến soi kèo bóng đá truoctran thích hợp.

6. Đảm bảo rằngurdaint_exclusionTham số cấu hình không bị vô hiệu hóa trongPostgreSql.conf. Nếu có, soi kèo bóng đá truoctran truy vấn sẽ không được tối ưu hóa như mong muốn.

Ví dụ, giả sử chúng ta đang xây dựng một cơ sở dữ liệu cho một công ty kem lớn. Công ty đo nhiệt độ cực đại mỗi ngày cũng như doanh số bán kem ở mỗi khu vực.

Tạo phép đo bảng (

Chúng tôi biết rằng hầu hết soi kèo bóng đá truoctran truy vấn sẽ truy cập chỉ vào dữ liệu tuần trước, tháng hoặc quý, vì việc sử dụng chính bảng này sẽ là chuẩn bị soi kèo bóng đá truoctran báo cáo trực tuyến để 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.

Trong tình huống này, chúng ta có thể sử dụng soi kèo bóng đá truoctran để giúp chúng ta đáp ứng tất cả các yêu cầu khác nhau của chúng ta cho bảng đo. Thực hiện theo các bước được nêu ở trên, soi kèo bóng đá truoctran có thể được thiết lập như sau:

1. Bảng chính làĐo lườngBảng, khai báo chính xác như trên.

2. Next we create one partition for each active month:

   Tạo bảng đo_Y2006M02 () kế thừa (đo lường);

   Mỗi soi kèo bóng đá truoctran là các bảng hoàn chỉnh theo cách riêng của chúng, nhưng chúng kế thừa định nghĩa của chúng 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 thảTrên bảng con lâu đời nhất và tạo bảng con mới cho dữ liệu của tháng mới.

3. 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 soi kèo bóng đá truoctran như trên, tập lệnh tạo bảng thực sự là:

   Tạo bảng đo_Y2006M02 (

4. Có lẽ chúng ta cũng cần soi kèo bóng đá truoctran chỉ mục trên soi kèo bóng đá truoctran cột chính:

   Tạo chỉ số đo lường_y2006m02_logdate trên đo_Y2006M02 (logdate);

   Chúng tôi chọn không thêm soi kèo bóng đá truoctran chỉ mục vào thời điểm này.

5. 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 soi kèo bóng đá truoctran thích hợp. Chúng ta có thể sắp xếp điều đó bằng cách gắn hàm kích hoạt phù hợp vào bảng chính.

   Tạo hoặc thay thế chức năng Đo_insert_trigger ()

   Sau khi tạo chức năng, chúng tôi tạo một trình kích hoạt gọi chức năng kích hoạt:

   Tạo Trigger Insert_Meas mua sắm_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 chỉ vào soi kèo bóng đá truoctran hiện tại. Tuy nhiên, định nghĩa kích hoạt không cần phải được cập nhật.

   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í soi kèo bóng đá truoctran mà hàng nên được thêm vào. Chúng ta có thể làm điều này với chức năng kích hoạt phức tạp hơn, ví dụ:

   Tạo hoặc thay thế chức năng Đo_insert_trigger ()

   Định nghĩa kích hoạt giống như trước đây. Lưu ý rằng mỗiIFKiểm tra phải khớp chính xác vớiKiểm traràng buộc cho soi kèo bóng đá truoctran của nó.

   Trong khi chức năng 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ì soi kèo bóng đá truoctran nhánh có thể được thêm vào trước khi cần thiết.

   Lưu ý:Trong thực tế có thể tốt nhất là kiểm tra soi kèo bóng đá truoctran mới nhất trước tiên, nếu hầu hết các phần chèn đi vào soi kèo bóng đá truoctran đó. Để đơn giản, chúng tôi đã hiển thị các thử nghiệm của kích hoạt theo cùng 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ơ đồ soi kèo bóng đá truoctran phức tạp có thể yêu cầu một lượng DDL đáng kể. Trong ví dụ trên, chúng tôi sẽ tạo một soi kèo bóng đá truoctran mới mỗi tháng, vì vậy có thể là khôn ngoan khi viết một tập lệnh tạo DDL cần thiết tự động.

5.10.3. Quản lý soi kèo bóng đá truoctran

Thông thường tập hợp các soi kèo bóng đá truoctran được thiết lập khi xác định bảng ban đầu không có ý định vẫn tĩnh. Người ta thường muốn loại bỏ các soi kèo bóng đá truoctran dữ liệu cũ và định kỳ thêm các soi kèo bóng đá truoctran mới cho dữ liệu mới.

Tùy chọn đơn giản nhất để xóa dữ liệu cũ chỉ đơn giản là bỏ soi kèo bóng đá truoctran không còn cần thiết:

22214_22248

Điều này có thể nhanh chóng xóa hàng triệu bản ghi vì nó không phải xóa riêng mọi bản ghi.

22373_22521

thay đổi bảng đo_Y2006M02 Không có phép đo kế thừa;

Điều này cho phép soi kèo bóng đá truoctran hoạt động tiếp theo được thực hiện trên dữ liệu trước khi nó bị loại bỏ. Ví dụ: đây thường là thời gian hữu ích để sao lưu dữ liệu bằng cách sử dụngCopy, pg_dumphoặc soi kèo bóng đá truoctran công cụ tương tự. Nó cũng có thể là một thời gian hữu ích để tổng hợp dữ liệu thành soi kèo bóng đá truoctran định dạng nhỏ hơn, thực hiện soi kèo bóng đá truoctran 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 soi kèo bóng đá truoctran mới để xử lý dữ liệu mới. Chúng ta có thể tạo một soi kèo bóng đá truoctran trống trong bảng được soi kèo bóng đá truoctran giống như các soi kèo bóng đá truoctran ban đầu đã được tạo ở trên:

Tạo bảng đo_Y2008M02 (

Thay thế, đôi khi thuận tiện hơn để tạo bảng mới bên ngoài cấu trúc soi kèo bóng đá truoctran và biến nó thành một soi kèo bóng đá truoctran thích hợp sau. Điều này cho phép dữ liệu được tải, kiểm tra và chuyển đổi trước khi nó xuất hiện trong bảng được soi kèo bóng đá truoctran:

Tạo bảng đo_Y2008M02

5.10.4. soi kèo bóng đá truoctran và loại trừ ràng buộc

Loại trừ ràng buộclà một kỹ thuật tối ưu hóa truy vấn giúp cải thiện hiệu suất cho các bảng được soi kèo bóng đá truoctran được xác định theo kiểu được mô tả ở trên. Ví dụ:

Đặt ràng buộc_exclusion = ON;

Không loại trừ ràng buộc, truy vấn trên sẽ quét từng soi kèo bóng đá truoctran 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 từng soi kèo bóng đá truoctran và cố gắng chứng minh rằng soi kèo bóng đá truoctran 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 gỡ truy vấnWHEREmệnh đề. Khi người lập kế hoạch có thể chứng minh điều này, nó sẽ loại trừ soi kèo bóng đá truoctran khỏi kế hoạch truy vấn.

Bạn có thể sử dụngGiải thíchlệnh để hiển thị sự khác biệt giữa một kế hoạch vớiinstraint_exclusionon and a plan with it off. Một kế hoạch không được tối ưu hóa điển hình cho loại thiết lập bảng này là:

Đặt ràng buộc_exclusion = TẮT;

Một số hoặc tất cả các soi kèo bóng đá truoctran có thể sử dụng quét chỉ mục thay vì quét tuần tự bảng đầy đủ, nhưng điểm ở đây là không cần phải quét các soi kèo bóng đá truoctran 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 một kế hoạch rẻ hơn đáng kể sẽ cung cấp cùng một câu trả lời:

Đặt ràng buộc_exclusion = ON;

Lưu ý rằng loại trừ ràng buộc chỉ được điều khiển bởiKiểm traconstraints, not by the presence of indexes. Therefore it isn't necessary to define indexes on the key columns.

Cài đặt mặc định (và được đề xuất) củaurdaint_exclusionthực sự cũng khôngtrêncũng khôngTẮT, nhưng một cài đặt trung gian được gọi làsoi kèo bóng đá truoctran, gây ra kỹ thuật chỉ được áp dụng cho các truy vấn có khả năng làm việc trên các bảng được soi kèo bóng đá truoctran. ThetrênCài đặt khiến người lập kế hoạch kiểm traCHECKsoi kèo bóng đá truoctran ràng buộc trong tất cả soi kèo bóng đá truoctran truy vấn, ngay cả những câu hỏi đơn giản không có khả năng được hưởng lợi.

5.10.5. Phương pháp soi kèo bóng đá truoctran thay thế

Một cách tiếp cận khác để chuyển hướng chèn vào bảng soi kèo bóng đá truoctran thích hợp là thiết lập các quy tắc, thay vì kích hoạt trên bảng chính. Ví dụ:

Tạo quy tắc đo lường_insert_y2006m02 AS

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 trả 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ể thuận lợi cho soi kèo bóng đá truoctran tình huống chèn. Tuy nhiên, trong hầu hết soi kèo bóng đá truoctran trường hợp, phương pháp kích hoạt sẽ cung cấp hiệu suất tốt hơn.

Nhận thức rằngCopyBỏ qua soi kèo bóng đá truoctran 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 soi kèo bóng đá truoctran chính xác thay vì vào chủ.Copysoi kèo bóng đá truoctran 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à không có cách nào đơn giản để buộc lỗi nếu tập hợp soi kèo bóng đá truoctran quy tắc không bao gồm ngày chèn; Dữ liệu sẽ âm thầm đi vào bảng chính.

soi kèo bóng đá truoctran cũng có thể được sắp xếp bằng cách sử dụngLiên minh tất cảXem, thay vì kế thừa bảng. Ví dụ,

Tạo phép đo xem là

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à bỏ các soi kèo bóng đá truoctran riêng lẻ của tập dữ liệu. Trong thực tế, phương pháp này có rất ít đề xuất nó so với việc sử dụng kế thừa.

5.10.6. Hãy cẩn thận

Các cảnh báo sau đây áp dụng cho các bảng được soi kèo bóng đá truoctran:

• Không có cách tự động để xác minh rằng tất cả soi kèo bóng đá truoctranKiểm traCác ràng buộc là loại trừ lẫn nhau. Việc tạo mã tạo soi kèo bóng đá truoctran là an toàn hơn và sửa đổi các đối tượng liên quan hơn là viết mỗi cái bằng tay.

• Các sơ đồ được hiển thị ở đây giả sử rằng cột khóa soi kèo bóng đá truoctran 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 soi kèo bóng đá truoctran khác. MỘTCập nhậtĐiều đó cố gắng làm điều đó sẽ thất bại vìKiểm traràng buộc. Nếu bạn cần xử lý các trường hợp như vậy, bạn có thể đặt các kích hoạt cập nhật phù hợp trên các bảng soi kèo bóng đá truoctran, nhưng nó làm cho việc quản lý cấu trúc phức tạp hơn nhiều.

• Nếu bạn đang sử dụng thủ côngVACUUMhoặcsoi kèo bóng đá truoctran tíchCác lệnh, đừng quên rằng bạn cần phải chạy chúng trên từng soi kèo bóng đá truoctran. Một lệnh như:

  soi kèo bóng đá truoctran tích đo lường;

  Sẽ chỉ xử lý bảng chính.

• ChènCâu nói vớitrên xung độtĐiều khoản không có khả năng hoạt động như mong đợi, nhưtrên xung độtaction is only taken in case of unique violations on the specified target relation, not its child relations.

soi kèo bóng đá truoctran 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 soi kèo bóng đá truoctran hằng số (hoặc tham số được cung cấp bên ngoài). Ví dụ: so sánh với chức năng không thể truy xuất nhưcurrent_timestampKhông thể được tối ưu hóa, vì người lập kế hoạch không thể biết soi kèo bóng đá truoctran nào giá trị chức năng có thể rơi vào thời gian chạy.

• Giữ cho các ràng buộc soi kèo bóng đá truoctran đơn giản, nếu không người lập kế hoạch có thể không thể chứng minh rằng các soi kèo bóng đá truoctran không cần phải truy cập. Sử dụng các điều kiện bình đẳng đơn giản để soi kèo bóng đá truoctran danh sách hoặc kiểm tra phạm vi đơn giản để soi kèo bóng đá truoctran phạm vi, như được minh họa trong các ví dụ trước.

• Tất cả các ràng buộc đối với tất cả các soi kèo bóng đá truoctran của bảng chính được kiểm tra trong quá trình loại trừ ràng buộc, do đó số lượng lớn các soi kèo bóng đá truoctran có khả năng làm tăng đáng kể thời gian lập kế hoạch truy vấn. soi kèo bóng đá truoctran bằng cách sử dụng các kỹ thuật này sẽ hoạt động tốt với khoảng một trăm soi kèo bóng đá truoctran;