Phân vùng MySQL – over partition by sql w3schools

Bạn đang xem : qua phân vùng bởi sql w3schools

Phân vùng MySQL

Cập nhật lần cuối vào ngày 04 tháng 4 năm 2022 13:10:55 (UTC / GMT +8 giờ)

Phân vùng là gì?

Phân vùng (một kỹ thuật thiết kế cơ sở dữ liệu) cải thiện hiệu suất, khả năng quản lý, đơn giản hóa việc bảo trì và giảm chi phí lưu trữ lượng lớn dữ liệu. Có thể đạt được phân vùng mà không cần chia bảng bằng cách đặt các bảng vào các ổ đĩa riêng lẻ. Phân vùng cho phép các bảng, chỉ mục và bảng được tổ chức theo chỉ mục được chia thành các phần nhỏ hơn, do đó các truy vấn chỉ truy cập một phần dữ liệu có thể chạy nhanh hơn vì có ít dữ liệu hơn để quét. Có hai hình thức phân vùng chính:

  • Phân vùng theo chiều ngang: Phân vùng theo chiều ngang chia các hàng trong bảng thành nhiều phân vùng (dựa trên logic). Tất cả các cột được xác định cho một bảng được tìm thấy trong mỗi phân vùng, vì vậy không có thuộc tính bảng thực sự nào bị thiếu. Tất cả các phân vùng có thể được giải quyết riêng lẻ hoặc chung. Ví dụ: một bảng chứa giao dịch giảm giá cả năm được phân chia theo chiều ngang thành mười hai phân vùng riêng biệt, trong đó mỗi phân vùng chứa dữ liệu của một tháng.
  • Phân vùng theo chiều dọc: Phân vùng theo chiều dọc chia một bảng thành nhiều bảng chứa ít cột hơn. Giống như phân vùng ngang, trong phân vùng dọc, một truy vấn quét ít dữ liệu hơn để tăng hiệu suất truy vấn. Ví dụ: một bảng chứa nhiều văn bản rất rộng hoặc cột BLOB không được giải quyết thường được chia thành hai bảng có nhiều cột được tham chiếu nhất trong một bảng và văn bản hoặc dữ liệu BLOB trong một bảng khác.

Phân vùng MySQL

Phiên bản: MySQL 5.6

MySQL hỗ trợ phân vùng bảng cơ bản nhưng không hỗ trợ phân vùng dọc (MySQL 5.6). Phần này mô tả chi tiết cách triển khai phân vùng như một phần của cơ sở dữ liệu của bạn.

Bằng cách kiểm tra đầu ra của câu lệnh SHOW PLUGINS, bạn sẽ chắc chắn liệu máy chủ MySQL của mình có hỗ trợ phân vùng hay không. Xem kết quả sau:

Đầu ra mẫu:

 mysql & gt; HIỂN THỊ PLUGINS;
+ ---------------------------- + ---------- + --------- ----------- + --------- + --------- +
| Tên | Tình trạng | Loại | Thư viện | Giấy phép |
+ ---------------------------- + ---------- + --------- ----------- + --------- + --------- +
| binlog | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| MySQL_native_password | CHỦ ĐỘNG | XÁC NHẬN | NULL | GPL |
| MySQL_old_password | CHỦ ĐỘNG | XÁC NHẬN | NULL | GPL |
| sha256_password | CHỦ ĐỘNG | XÁC NHẬN | NULL | GPL |
| CSV | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| BỘ NHỚ | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| MyISAM | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| MRG_MYISAM | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| KIẾN TRÚC | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| BLACKHOLE | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| LIÊN BANG | BẤT CHẤP | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| InnoDB | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| INNODB_TRX | CHỦ ĐỘNG | SCHEMA THÔNG TIN | NULL | GPL |
| INNODB_LOCKS | CHỦ ĐỘNG | SCHEMA THÔNG TIN | NULL | GPL |
| INNODB_CMPMEM | CHỦ ĐỘNG | SCHEMA THÔNG TIN | NULL | GPL |
| --------------------------- | ------ | ------------------ | ---- | --- |
| --------------------------- | ------ | ------------------ | ---- | --- |
| --------------------------- | ------ | ------------------ | ---- | --- |
| INNODB_SYS_TABLESPACES | CHỦ ĐỘNG | SCHEMA THÔNG TIN | NULL | GPL |
| INNODB_SYS_DATAFILES | CHỦ ĐỘNG | SCHEMA THÔNG TIN | NULL | GPL |
| PERFORMANCE_SCHEMA | CHỦ ĐỘNG | ĐỘNG CƠ LƯU TRỮ | NULL | GPL |
| 

phân vùng

|

HOẠT ĐỘNG

| ĐỘNG CƠ LƯU TRỮ | NULL | GPL | + ---------------------------- + ---------- + --------- ----------- + --------- + --------- + 42 hàng trong bộ (0,21 giây)

Các tệp nhị phân cộng đồng MySQL 5.6 bao gồm hỗ trợ phân vùng.

Bật và tắt hỗ trợ phân vùng:

  • Để bật tính năng phân vùng (nếu bạn đang biên dịch MySQL 5.6 từ nguồn), bản dựng phải được định cấu hình với tùy chọn -DWITH_PARTITION_STORAGE_ENGINE.
  • Để tắt hỗ trợ phân vùng, bạn có thể khởi động Máy chủ MySQL với tùy chọn –skip-partition, trong trường hợp này giá trị của has_partitioning bị TẮT.

Cách phân vùng bảng?

Trong MySQL, bạn có thể phân vùng bảng bằng lệnh CREATE TABLE hoặc ALTER TABLE. Xem cú pháp TẠO BẢNG sau:

 TẠO [TẠM THỜI] BẢNG [NẾU KHÔNG TỒN TẠI] tbl_name
(create_definition, ...)
[table_options]
[partition_options]

partition_options:

 PHẦN BỞI
{[LINEAR] HASH (expr)
| [LINEAR] KEY (column_list)
| RANGE (expr)
| LIST (expr)}
[PARTITIONS num]
[CHƯƠNG TRÌNH BỞI
{[LINEAR] HASH (expr)
| [LINEAR] KEY (column_list)}
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)

partition_definition:

 PARTITION partition_name
[GIÁ TRỊ
{ÍT HƠN {(expr) | GIÁ TRỊ TỐI ĐA}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text']
[HƯỚNG DỮ LIỆU [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablepace_name]
[NODEGROUP [=] node_group_id]
[(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:

 SUBPARTITION logic_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text']
[HƯỚNG DỮ LIỆU [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablepace_name]
[NODEGROUP [=] node_group_id]

ALTER TABLE: Hoạt động phân vùng

Câu lệnh ALTER TABLE có thể được sử dụng để thêm, bớt, hợp nhất và chia nhỏ các phân vùng cũng như để thực hiện bảo trì phân vùng. Ở đây chúng tôi đã xác định một bảng không phân vùng:

  TẠO BẢNG sale_mast (
bill_no INT,
bill_date DATETIME
);

Bảng này có thể được phân vùng bằng HASH (hoặc theo kiểu khác), sử dụng cột bill_no làm khóa phân vùng, thành 6 phân vùng (hoặc khác) bằng cách sử dụng câu lệnh ALTER TABLE:

  BẢNG THAY THẾ t1
    PHẦN BẰNG HASH (id)
    PHẦN 6;

Đặt tên phân vùng:

Tên của các phân vùng tuân theo các quy tắc của mã định danh MySQL khác, chẳng hạn như cơ sở dữ liệu, bảng, ràng buộc, thủ tục được lưu trữ, v.v. Tên phân vùng không phân biệt chữ hoa chữ thường.

Ưu điểm của phân vùng

  • Trong quá trình quét, trình tối ưu hóa MySQL truy cập những phân vùng sẽ đáp ứng một truy vấn cụ thể. Ví dụ: bảng hồ sơ giảm giá cả năm có thể được chia thành 4 phân vùng (tức là dữ liệu bán hàng từ tháng 4-tháng 6 (phân vùng p0), tháng 7-tháng 9 (phân vùng p1), tháng 10-tháng 12 (phân vùng p2), Jam-Mar ( phân vùng p0)). Nếu một truy vấn được đưa ra có chứa dữ liệu bán hàng từ quý 7 đến tháng 9, thì nó chỉ quét phân vùng p1 thay vì tổng số bản ghi bảng và truy vấn sẽ hoàn thành sớm hơn nhiều.
  • Phân vùng cho phép bạn kiểm soát nhiều hơn cách dữ liệu được quản lý bên trong cơ sở dữ liệu. Ví dụ: bạn có thể bỏ các phân vùng cụ thể trong một bảng được phân vùng nơi dữ liệu mất tính hữu dụng. Trong một số trường hợp, quá trình thêm dữ liệu mới sẽ được tạo điều kiện thuận lợi hơn rất nhiều bằng cách thêm một hoặc nhiều phân vùng mới để lưu trữ dữ liệu đó bằng lệnh ALTER TABLE.
  • Trong phân vùng, có thể lưu trữ nhiều dữ liệu trong một bảng hơn là có thể lưu trên một đĩa hoặc phân vùng hệ thống tệp.
  • MySQL 5.6 hỗ trợ lựa chọn phân vùng rõ ràng cho các truy vấn. Ví dụ: CHỌN * TỪ table1 PARTITION (p0, p1) WHERE col1 & lt; 10 chỉ chọn những hàng trong phân vùng p0 và p1 phù hợp với điều kiện WHERE, điều này có thể tăng tốc độ truy vấn rất nhiều
  • Lựa chọn phân vùng cũng hỗ trợ các câu lệnh sửa đổi dữ liệu DELETE, INSERT, REPLACE, UPDATE và LOAD DATA, LOAD XML.
Xem Thêm  Thẻ HTML - mã html cho thẻ meta

Các loại phân vùng MySQL

Các kiểu phân vùng sau có sẵn trong MySQL 5.6:

Phân vùng MySQL RANGE

Trong MySQL, chế độ phân vùng RANGE cho phép chúng tôi chỉ định các phạm vi khác nhau mà dữ liệu được chỉ định. Các phạm vi phải liền kề nhưng không chồng chéo và được xác định bằng cách sử dụng toán tử VALUES LESS HAN. Trong ví dụ sau, bảng sale_mast chứa bốn cột bill_no, bill_date, cust_code và số tiền. Bảng này có thể được phân vùng theo phạm vi theo nhiều cách khác nhau, tùy thuộc vào yêu cầu của bạn. Ở đây chúng tôi đã sử dụng cột bill_date và quyết định phân vùng bảng theo 4 cách bằng cách thêm mệnh đề PARTITION BY RANGE. Trong các phân vùng này, phạm vi ngày bán (sale_date) như sau:

  • phân vùng p0 (giảm giá từ 01-01-2013 đến 31-03-2013)
  • phân vùng p1 (giảm giá từ 01-04-2013 đến 30-06-2013)
  • phân vùng p2 (giảm giá từ 01-07-2013 đến 30-09-2013)
  • phân vùng p3 (giảm giá từ 01-10-2013 đến 30-12-2013)

Hãy tạo bảng:

 mysql & gt; TẠO BẢNG sale_mast (bill_no INT NOT NULL, bill_date TIMESTAMP KHÔNG ĐẦY,
cust_code VARCHAR (15) NOT NULL, số tiền DECIMAL (8,2) NOT NULL)
PARTITION BY RANGE (UNIX_TIMESTAMP (bill_date)) (
PHẦN THỨ p0 GIÁ TRỊ ÍT HƠN (UNIX_TIMESTAMP ('2013-04-01')),
GIÁ TRỊ CỦA PHẦN p1 THẤP HƠN (UNIX_TIMESTAMP ('2013-07-01')),
GIÁ TRỊ CỦA PHẦN p2 ÍT HƠN (UNIX_TIMESTAMP ('2013-10-01')),
PARTITION p3 GIÁ TRỊ ÍT HƠN (UNIX_TIMESTAMP ('2014-01-01')));
Truy vấn OK, 0 hàng bị ảnh hưởng (1,50 giây)

Bây giờ, hãy chèn một số bản ghi vào bảng sale_mast:

 mysql & gt; INSERT INTO sale_mast VALUES (1, '2013-01-02', 'C001', 125,56),
(2, '2013-01-25', 'C003', 456.50),
(3, '2013-02-15', 'C012', 365,00),
(4, '2013-03-26', 'C345', 785,00),
(5, '2013-04-19', 'C234', 656,00),
(6, '2013-05-31', 'C743', 854.00),
(7, '2013-06-11', 'C234', 542.00),
(8, '2013-07-24', 'C003', 300,00),
(8, '2013-08-02', 'C456', 475.20);
Truy vấn OK, 9 hàng bị ảnh hưởng (0,07 giây)
Hồ sơ: 9 Bản sao: 0 Cảnh báo: 0

mysql & gt; CHỌN * TỪ sale_mast;
+ --------- + --------------------- + ----------- + ----- --- +
| bill_no | bill_date | cust_code | số tiền |
+ --------- + --------------------- + ----------- + ----- --- +
| 1 | 2013-01-02 00:00:00 | C001 | 125,56 |
| 2 | 2013-01-25 00:00:00 | C003 | 456,50 |
| 3 | 2013-02-15 00:00:00 | C012 | 365,00 |
| 4 | 2013-03-26 00:00:00 | C345 | 785,00 |
| 5 | 2013-04-19 00:00:00 | C234 | 656,00 |
| 6 | 2013-05-31 00:00:00 | C743 | 854,00 |
| 7 | 2013-06-11 00:00:00 | C234 | 542,00 |
| 8 | 2013-07-24 00:00:00 | C003 | 300,00 |
| 9 | 2013-08-02 00:00:00 | C456 | 475,20 |
+ --------- + --------------------- + ----------- + ----- --- +
9 hàng trong bộ (0,00 giây) 

Đây là trạng thái phân vùng của bảng sale_mast:

 mysql & gt; CHỌN PARTITION_NAME, TABLE_ROWS TỪ INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sale_mast';
+ ---------------- + ------------ +
| PARTITION_NAME | TABLE_ROWS |
+ ---------------- + ------------ +
| p0 | 4 |
| p1 | 3 |
| p2 | 2 |
| p3 | 0 |
+ ---------------- + ------------ +
4 hàng trong bộ (0,02 giây)

Theo cách trên, bạn có thể phân vùng bảng dựa trên số tiền bán được (số tiền. Trong các phân vùng này, phạm vi số tiền bán được (số tiền) như sau:

  • phân vùng p0 (số lượng ưu đãi & lt; 100)
  • phân vùng p1 (số lượng ưu đãi & lt; 500)
  • phân vùng p2 (số lượng ưu đãi & lt; 1000)
  • phân vùng p3 (số lượng ưu đãi & lt; 1500)

Hãy tạo bảng:

 mysql & gt; TẠO BẢNG sale_mast1 (bill_no INT NOT NULL, bill_date TIMESTAMP KHÔNG ĐẦY,
cust_codE VARCHAR (15) KHÔNG ĐẦY ĐỦ, số tiền KHÔNG ĐẦY ĐỦ)
PHẦN THEO RANGE (số lượng) (
GIÁ TRỊ CỦA PHẦN p0 ÍT HƠN (100),
GIÁ TRỊ CỦA PHẦN p1 THẤP HƠN (500),
GIÁ TRỊ CỦA PHẦN p2 ÍT HƠN (1000),
PHẦN p3 GIÁ TRỊ ÍT HƠN (1500));
Truy vấn OK, 0 hàng bị ảnh hưởng (1,34 giây)

Thả phân vùng MySQL

Nếu bạn cảm thấy một số dữ liệu vô dụng trong bảng được phân vùng, bạn có thể bỏ một hoặc nhiều (các) phân vùng. Để xóa tất cả các hàng khỏi phân vùng p0 của sale_mast, bạn có thể sử dụng câu lệnh sau:

 MySQL & gt; ALTER TABLE sale_mast TRUNCATE PARTITION p0;
Truy vấn OK, 0 hàng bị ảnh hưởng (0,49 giây)

mysql & gt; CHỌN * TỪ sale_mast;
+ --------- + --------------------- + ----------- + ----- --- +
| bill_no | bill_date | cust_code | số tiền |
+ --------- + --------------------- + ----------- + ----- --- +
| 5 | 2013-04-19 00:00:00 | C234 | 656,00 |
| 6 | 2013-05-31 00:00:00 | C743 | 854,00 |
| 7 | 2013-06-11 00:00:00 | C234 | 542,00 |
| 8 | 2013-07-24 00:00:00 | C003 | 300,00 |
| 9 | 2013-08-02 00:00:00 | C456 | 475,20 |
+ --------- + --------------------- + ----------- + ----- --- +
5 hàng trong bộ (0,01 giây) 

Đây là trạng thái phân vùng của sale_mast sau khi bỏ phân vùng p0:

 MySQL & gt; CHỌN PARTITION_NAME, TABLE_ROWS TỪ INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sale_mast';
+ ---------------- + ------------ +
| PARTITION_NAME | TABLE_ROWS |
+ ---------------- + ------------ +
| 

p0

|

0

| | p1 | 3 | | p2 | 2 | | p3 | 0 | + ---------------- + ------------ + 4 hàng trong bộ (0,05 giây)

Phân vùng DANH SÁCH MySQL

Phân vùng danh sách cho phép chúng tôi phân đoạn dữ liệu dựa trên một bộ giá trị được xác định trước (ví dụ: 1, 2, 3). Điều này được thực hiện bằng cách sử dụng PARTITION BY LIST (expr) trong đó expr là giá trị cột và sau đó xác định từng phân vùng bằng giá trị IN (value_list), trong đó value_list là danh sách các số nguyên được phân tách bằng dấu phẩy. Trong MySQL 5.6, chỉ có thể khớp với danh sách các số nguyên (và có thể là NULL) khi phân vùng theo LIST. Trong ví dụ sau, bảng sale_mast2 chứa bốn cột bill_no, bill_date, agent_code và số tiền. Giả sử có 11 tác nhân đại diện cho ba thành phố A, B, C, chúng có thể được sắp xếp thành ba phân vùng với cách phân vùng LIST như sau:

Thành phố
ID đại lý

Một
1, 2, 3

B
4, 5, 6

C
7, 8, 9, 10, 11

Hãy tạo bảng:

 mysql & gt; TẠO BẢNG sale_mast2 (bill_no INT NOT NULL, bill_date TIMESTAMP KHÔNG ĐẦY,
agent_codE INT NOT NULL, số tiền INT NOT NULL)
PHẦN THEO DANH SÁCH (agent_code) (
CÁC GIÁ TRỊ pA PHẦN TRONG (1,2,3),
CÁC GIÁ TRỊ pB PHẦN TRONG (4,5,6),
CÁC GIÁ TRỊ pC PHẦN TRONG (7,8,9,10,11));
Truy vấn OK, 0 hàng bị ảnh hưởng (1,17 giây)

Phân vùng COLUMNS trong MySQL

Xem Thêm  Cách chuyển đổi chuỗi thành ngày tháng trong Javascript - chuyển đổi chuỗi ngày thành javascript ngày

Trong phân vùng COLUMNS, có thể sử dụng nhiều cột trong các khóa phân vùng. Có hai loại phân vùng COLUMNS:

Ngoài ra, cả phân vùng RANGE COLUMNS và phân vùng LIST COLUMNS đều hỗ trợ việc sử dụng các cột không phải số nguyên để xác định phạm vi giá trị hoặc thành viên danh sách. Các loại dữ liệu được phép được hiển thị trong danh sách sau:

Cả phân vùng RANGE COLUMNS và phân vùng LIST COLUMNS đều hỗ trợ các kiểu dữ liệu sau để xác định phạm vi giá trị hoặc danh sách thành viên.

  • Tất cả các kiểu số nguyên: TINYINT, SMALLINT, MEDIUMINT, INT (INT (INTEGER) và BIGINT.
  • DATE và DATETIME.

Phân vùng RANGE COLUMNS

Phân vùng RANGE COLUMNS tương tự như phân vùng theo dải với một số khác biệt đáng kể. RANGE COLUMNS chấp nhận danh sách một hoặc nhiều cột làm khóa phân vùng. Bạn có thể xác định phạm vi bằng cách sử dụng các loại cột khác nhau (đã đề cập ở trên) khác với loại số nguyên.

Đây là cú pháp cơ bản để tạo bảng được phân vùng bởi RANGE COLUMNS:

 TẠO BẢNG table_name
ĐƯỢC THAM GIA BỞI RANGE COLUMNS (column_list) (
    PARTITION partition_name CÓ GIÁ TRỊ ÍT HƠN (value_list) [,
    PARTITION partition_name VALUES ÍT HƠN (value_list)] [,
    ...]
)

column_list:
    column_name [, column_name] [, ...]

value_list:
    giá trị [, giá trị] [, ...]
  • column_list là danh sách gồm một hoặc nhiều cột.
  • value_list là danh sách các giá trị và phải được cung cấp cho mỗi định nghĩa phân vùng.
  • danh sách cột và trong danh sách giá trị xác định mỗi phân vùng phải diễn ra theo cùng một thứ tự
  • Thứ tự của tên cột trong danh sách cột phân vùng và danh sách giá trị không nhất thiết phải giống với thứ tự của định nghĩa cột bảng trong câu lệnh CREATE TABLE.

Đây là một ví dụ:

 mysql & gt; TẠO BẢNG table3 (col1 INT, col2 INT, col3 CHAR (5), col4 INT)
PHẦN BỞI RANGE COLUMNS (col1, col2, col3)
 (CÁC GIÁ TRỊ CỦA PHẦN p0 ÍT HƠN (50, 100, 'aaaaa'),
 GIÁ TRỊ CỦA PHẦN p1 THẤP HƠN (100,200, 'bbbbb'),
 GIÁ TRỊ CỦA PHẦN p2 THẤP HƠN (150,300, 'ccccc'),
 PHẦN p3 CÁC GIÁ TRỊ ÍT HƠN (MAXVALUE, MAXVALUE, MAXVALUE));
Truy vấn OK, 0 hàng bị ảnh hưởng (1,39 giây)

Trong ví dụ trên –

  • Bảng table3 chứa các cột col1, col2, col3, col4
  • Ba cột đầu tiên đã tham gia vào mệnh đề COLUMNS phân vùng, theo thứ tự col1, col2, col3.
  • Mỗi danh sách giá trị được sử dụng để xác định một phân vùng chứa 3 giá trị theo thứ tự giống nhau và dạng (INT, INT, CHAR (5)).

Phân vùng LIST COLUMNS

LIST COLUMNS chấp nhận danh sách một hoặc nhiều cột làm khóa phân vùng. Bạn có thể sử dụng các cột dữ liệu khác nhau thuộc kiểu khác với kiểu số nguyên làm cột phân vùng. Bạn có thể sử dụng các loại chuỗi, cột DATE và DATETIME

Trong một công ty có các đại lý ở 3 thành phố, nhằm mục đích bán hàng và tiếp thị. Chúng tôi đã tổ chức các đại lý ở 3 thành phố như trong bảng sau:

Thành phố
ID đại lý

Một
A1, A2, A3

B
B1, B2, B3

C
C1, C2, C3, C4, C5

Hãy tạo một bảng với phân vùng LIST COLUMNS dựa trên thông tin trên:

 mysql & gt; TẠO BẢNG salemast (agent_id VARCHAR (15), agent_name VARCHAR (50),
agent_address VARCHAR (100), city_code VARCHAR (10))
PHẦN BẰNG CỘT DANH SÁCH (agent_id) (
PARTITION pcity_a VALUES IN ('A1', 'A2', 'A3'),
PARTITION pcity_b VALUES IN ('B1', 'B2', 'B3'),
PARTITION pcity_c VALUES IN ('C1', 'C2', 'C3', 'C4', 'C5'));
Truy vấn OK, 0 hàng bị ảnh hưởng (1,06 giây)

Bạn có thể sử dụng cột DATE và DATETIME trong phân vùng LIST COLUMNS, xem ví dụ sau:

 TẠO BẢNG sale_master (bill_no INT NOT NULL, bill_date DATE,
cust_code VARCHAR (15) NOT NULL, số tiền DECIMAL (8,2) NOT NULL)
PHẦN BẰNG RANGE COLUMNS (bill_date) (
PARTITION p_qtr1 GIÁ TRỊ THẤP HƠN ('2013-04-01'),
PARTITION p_qtr2 GIÁ TRỊ THẤP HƠN ('2013-07-01'),
PARTITION p_qtr3 GIÁ TRỊ THẤP HƠN ('2013-10-01'),
PARTITION p_qtr4 CÁC GIÁ TRỊ ÍT HƠN ('2014-01-01'));

Phân vùng HASH trong MySQL

Phân vùng MySQL HASH được sử dụng để phân phối dữ liệu giữa một số lượng phân vùng được xác định trước trên giá trị cột hoặc biểu thức dựa trên giá trị cột. Điều này được thực hiện bằng cách sử dụng mệnh đề PARTITION BY HASH (expr), thêm vào BÁO CÁO TẠO BẢNG. Trong mệnh đề PARTITIONS num, num là một số nguyên dương biểu thị số lượng phân vùng của bảng. Câu lệnh sau tạo một bảng sử dụng băm trên cột studetn_id và được chia thành 4 phân vùng:

  MySQL & gt; TẠO BẢNG sinh viên (student_id INT NOT NULL,
lớp VARCHAR (8), tên VARCHAR (40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PHẦN BẰNG HASH (student_id)
PHẦN 4;
Truy vấn OK, 0 hàng bị ảnh hưởng (1,43 giây)

Cũng có thể tạo một phân vùng dựa trên năm học sinh được nhận vào. Xem tuyên bố sau:

 
MySQL & gt; TẠO BẢNG sinh viên (student_id INT NOT NULL,
lớp VARCHAR (8), lớp VARCHAR (8), tên VARCHAR (40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PARTITION BY HASH (YEAR (date_of_admission))
PHẦN 4;
Truy vấn OK, 0 hàng bị ảnh hưởng (1,27 giây)

Phân vùng KEY MySQL

Phân vùng MySQL KEY là một dạng phân vùng HASH đặc biệt, trong đó máy chủ MySQL cung cấp hàm băm để phân vùng khóa. Máy chủ sử dụng hàm băm nội bộ của riêng nó dựa trên thuật toán tương tự như PASSWORD (). Điều này được thực hiện bằng cách sử dụng PARTITION BY KEY, thêm vào TẠO BÁO CÁO. Trong phân vùng KEY KEY chỉ lấy một danh sách không hoặc nhiều tên cột. Bất kỳ cột nào được sử dụng làm khóa phân vùng phải bao gồm một phần hoặc tất cả khóa chính của bảng nếu bảng có một. Nếu có một khóa chính trong bảng, nó được sử dụng làm khóa phân vùng khi không có cột nào được chỉ định làm khóa phân vùng. Đây là một ví dụ:

 MySQL & gt; TẠO BẢNG table1 (id INT NOT NULL PRIMARY KEY,
fname VARCHAR (25), lname VARCHAR (25))
PHẦN THEO TỪ KHÓA ()
PHẦN 2;
Truy vấn OK, 0 hàng bị ảnh hưởng (0,84 giây)

Nếu không có khóa chính nhưng có một khóa duy nhất trong bảng thì khóa duy nhất được sử dụng cho khóa phân vùng:

 MySQL & gt; TẠO BẢNG table2 (id INT NOT NULL, fname VARCHAR (25),
tên VARCHAR (25),
TỪ KHÓA DUY NHẤT (id))
PHẦN THEO TỪ KHÓA ()
PHẦN 2;
Truy vấn OK, 0 hàng bị ảnh hưởng (0,77 giây)

Phân vùng con MySQL

Xem Thêm  BÀI GIẢNG LÝ THUYẾT TỐI ƯU - lý thuyết tối ưu hóa

Phân vùng con là một phương pháp để chia thêm từng phân vùng trong một bảng được phân vùng. Xem câu lệnh CREATE TABLE sau:

 TẠO BẢNG bảng 10 (BILL_NO INT, sale_date DATE, cust_code VARCHAR (15),
SỐ TIỀN KHOẢN TRÍCH (8,2))
PHẦN THEO RANGE (YEAR (sale_date))
CHƯƠNG TRÌNH THEO HASH (TO_DAYS (sale_date))
CHƯƠNG 4 (
CÁC GIÁ TRỊ CỦA PHẦN p0 ÍT HƠN (1990),
GIÁ TRỊ CỦA PHẦN p1 THẤP HƠN (2000),
GIÁ TRỊ CỦA PHẦN p2 ÍT HƠN (2010),
CÁC GIÁ TRỊ CỦA PHẦN p3 THẤP HƠN TỐI ĐA
);

Trong tuyên bố trên –

  • Bảng có 4 phân vùng RANGE.
  • Mỗi phân vùng này — p0, p1, p2 và p3 — lại được chia thành 4 phân mục con.
  • Do đó toàn bộ bảng được chia thành 4 * 4 = 16 phân vùng.

Đây là trạng thái phân vùng của bảng10:

 mysql & gt; CHỌN PARTITION_NAME, TABLE_ROWS TỪ
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'ổn định';
+ ---------------- + ------------ +
| PARTITION_NAME | TABLE_ROWS |
+ ---------------- + ------------ +
| p0 | 0 |
| p0 | 0 |
| p0 | 0 |
| p0 | 0 |
| p1 | 0 |
| p1 | 0 |
| p1 | 0 |
| p1 | 0 |
| p2 | 0 |
| p2 | 0 |
| p2 | 0 |
| p2 | 0 |
| p3 | 0 |
| p3 | 0 |
| p3 | 0 |
| p3 | 0 |
+ ---------------- + ------------ +
16 hàng trong bộ (0,16 giây)

Trước: BẢNG THAY THẾ MySQL
Tiếp theo: CHÈN MySQL


Xem thêm những thông tin liên quan đến chủ đề over partition by sql w3schools

Over clause in SQL Server

  • Tác giả: kudvenkat
  • Ngày đăng: 2015-09-29
  • Đánh giá: 4 ⭐ ( 5881 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: over partition by in sql server 2008
    sql server over clause partition
    partition by clause in sql server 2008
    over partition by clause in sql

    In this video we will discuss the power and use of Over clause in SQL Server.

    Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
    https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

    The OVER clause combined with PARTITION BY is used to break up data into partitions.
    Syntax : function (…) OVER (PARTITION BY col1, Col2, …)

    The specified function operates for each partition.

    For example :
    COUNT(Gender) OVER (PARTITION BY Gender) will partition the data by GENDER i.e there will 2 partitions (Male and Female) and then the COUNT() function is applied over each partition.

    Any of the following functions can be used. Please note this is not the complete list.
    COUNT(), AVG(), SUM(), MIN(), MAX(), ROW_NUMBER(), RANK(), DENSE_RANK() etc.

    Example :
    SQl Script to create Employees table
    Create Table Employees
    (
    Id int primary key,
    Name nvarchar(50),
    Gender nvarchar(10),
    Salary int
    )
    Go

    Insert Into Employees Values (1, ‘Mark’, ‘Male’, 5000)
    Insert Into Employees Values (2, ‘John’, ‘Male’, 4500)
    Insert Into Employees Values (3, ‘Pam’, ‘Female’, 5500)
    Insert Into Employees Values (4, ‘Sara’, ‘Female’, 4000)
    Insert Into Employees Values (5, ‘Todd’, ‘Male’, 3500)
    Insert Into Employees Values (6, ‘Mary’, ‘Female’, 5000)
    Insert Into Employees Values (7, ‘Ben’, ‘Male’, 6500)
    Insert Into Employees Values (8, ‘Jodi’, ‘Female’, 7000)
    Insert Into Employees Values (9, ‘Tom’, ‘Male’, 5500)
    Insert Into Employees Values (10, ‘Ron’, ‘Male’, 5000)
    Go

    Write a query to retrieve total count of employees by Gender. Also in the result we want Average, Minimum and Maximum salary by Gender.

    This can be very easily achieved using a simple GROUP BY query as show below.
    SELECT Gender, COUNT(*) AS GenderTotal, AVG(Salary) AS AvgSal,
    MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal
    FROM Employees
    GROUP BY Gender

    What if we want non-aggregated values (like employee Name and Salary) in result set along with aggregated values

    You cannot include non-aggregated columns in the GROUP BY query.
    SELECT Name, Salary, Gender, COUNT(*) AS GenderTotal, AVG(Salary) AS AvgSal,
    MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal
    FROM Employees
    GROUP BY Gender

    The above query will result in the following error : Column ‘Employees.Name’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

    One way to achieve this is by including the aggregations in a subquery and then JOINING it with the main query as shown in the example below. Look at the amount of T-SQL code we have to write.
    SELECT Name, Salary, Employees.Gender, Genders.GenderTotals,
    Genders.AvgSal, Genders.MinSal, Genders.MaxSal
    FROM Employees
    INNER JOIN
    (SELECT Gender, COUNT(*) AS GenderTotals,
    AVG(Salary) AS AvgSal,
    MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal
    FROM Employees
    GROUP BY Gender) AS Genders
    ON Genders.Gender = Employees.Gender

    Better way of doing this is by using the OVER clause combined with PARTITION BY
    SELECT Name, Salary, Gender,
    COUNT(Gender) OVER(PARTITION BY Gender) AS GenderTotals,
    AVG(Salary) OVER(PARTITION BY Gender) AS AvgSal,
    MIN(Salary) OVER(PARTITION BY Gender) AS MinSal,
    MAX(Salary) OVER(PARTITION BY Gender) AS MaxSal
    FROM Employees

    Text version of the video
    http://csharp-video-tutorials.blogspot.com/2015/09/over-clause-in-sql-server.html

    Slides
    http://csharp-video-tutorials.blogspot.com/2015/09/over-clause-in-sql-server_29.html

    All SQL Server Text Articles
    http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

    All SQL Server Slides
    http://csharp-video-tutorials.blogspot.com/p/sql-server.html

    All Dot Net and SQL Server Tutorials in English
    https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

    All Dot Net and SQL Server Tutorials in Arabic
    https://www.youtube.com/c/KudvenkatArabic/playlists

PARTITION BY in SQL

  • Tác giả: www.educba.com
  • Đánh giá: 5 ⭐ ( 9909 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Guide to PARTITION BY in SQL. Here we discuss the introduction to PARTITION BY in SQL along with examples to implement ROW_NUMBER with PARTITION BY Clause.

How to Use the SQL PARTITION BY With OVER

  • Tác giả: learnsql.com
  • Đánh giá: 4 ⭐ ( 5779 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: What is the SQL PARTITION BY clause used for? Learn how to get the most out of window functions. Grow your SQL skills!

SQL Tutorial

  • Tác giả: www.w3schools.com
  • Đánh giá: 3 ⭐ ( 5397 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

PARTITION BY Clause – GeeksforGeeks

  • Tác giả: www.geeksforgeeks.org
  • Đánh giá: 5 ⭐ ( 1208 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

SQL Over Clause

  • Tác giả: sqlskull.com
  • Đánh giá: 5 ⭐ ( 1908 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: SQL Server Tutorials By Pradeep Raturi SQL OVER clause allows the use of aggregation without using a group by clause.

SQL PARTITION BY Clause overview

  • Tác giả: www.sqlshack.com
  • Đánh giá: 3 ⭐ ( 4519 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: This article covers the SQL PARTITION BY clause and, in particular, the difference with GROUP BY in a select statement.

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình