I. About mysql partitioning
1. About
Partition là việc chia dữ liệu từ table đơn với một tệp dữ liệu vật lý thành nhiều phần khác và được lưu trữ
trong các tệp dữ liệu vật lý khác nhau.
Một số ưu điểm của partition
-
Cho phép lưu được nhiều dữ liệu hơn vào một table khi partition khi so sánh với việc lưu trữ dữ liệu
table vào một tệp tin hoặc một disk đơn. Nghĩa là dữ liệu partition có thể lưu trữ ở nhiều disk vật lý
khác nhau. -
Việc query có thể tối ưu hơn khi thực hiện query dữ liệu trên một hoặc một vài partition thay vì phải
query toàn bộ dữ liệu table.
Một số hạn chế của partition
Một số cấu trúc sau không được sử dụng trong partition:
-
Stored procedures, stored functions, UDFs, or plugins
-
Declared variables or user variables
Toán tử số học và logic:
-
Toán tử +, – , * được phép sử dụng trong partition tuy nhiên kết quả phải là integer hoặc NULL (ngoại trừ
sử dụng kiểu KEY partitioning). -
Toán tử
DIVđược hỗ trợ nhưng toán tử/không được phép. -
Các toán tử |, &, ^, <<, >>, and ~ không được phép sử dụng trong các biểu thức partition.
Query cache không được hỗ trợ trong partition
…
2. Partition types
-
Range partition: Loại partition này sẽ phân các row thành các partition với phạm vi (range) được định
nghĩa. -
List partition: Phân các row thành các partition dựa trên các column đúng với một trong một tập giá trị
rời rạc -
Hash partition: Mỗi partition trong kiểu hash dựa trên giá trị được trả về bởi một biểu thức do người
dùng định nghĩa. -
Key partition: Kiểu partition này giống với hash partition, ngoại trừ việc chỉ cung cấp một hoặc nhiều
column được đánh giá và MySQL server cung cấp hàm hash riêng của nó.
II. How to create partitions
1. How to add partitions to a table in mariadb / mysql
Thực hiện tạo cấu trúc bảng và đồng thời tạo partitions như sau:
CREATE TABLE `crm_realtime` (
`id` varchar(200) NOT NULL,
`name` varchar(200) DEFAULT NULL,
`status` int(11) DEFAULT '0',
`type` int(11) DEFAULT '0',
`user_id` int(11) DEFAULT NULL,
`registed_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `sales_team1` (`sales_team`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS (registed_at) (
PARTITION p0 VALUES LESS THAN ('2020-04-01'),
PARTITION p1 VALUES LESS THAN ('2020-05-01'),
PARTITION p2 VALUES LESS THAN ('2020-06-01'),
PARTITION p3 VALUES LESS THAN ('2020-07-01'),
PARTITION p4 VALUES LESS THAN ('2020-08-01'),
PARTITION p5 VALUES LESS THAN ('2020-09-01'),
PARTITION p6 VALUES LESS THAN ('2020-10-01'),
PARTITION p7 VALUES LESS THAN ('2020-11-01'),
PARTITION p8 VALUES LESS THAN ('2020-12-01'),
PARTITION p9 VALUES LESS THAN ('2021-01-01'),
PARTITION p10 VALUES LESS THAN ('2021-02-01'),
PARTITION p11 VALUES LESS THAN ('2021-03-01'),
PARTITION p12 VALUES LESS THAN (MAXVALUE)
);
2. How to add partitions to an existing table in mariadb / mysql
Chẳng hạn một bảng có cấu trúc dữ liệu như sau:
CREATE TABLE `crm_realtime` (
`id` varchar(200) NOT NULL,
`name` varchar(200) DEFAULT NULL,
`status` int(11) DEFAULT '0',
`type` int(11) DEFAULT '0',
`user_id` int(11) DEFAULT NULL,
`registed_at` datetime DEFAULT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `sales_team1` (`sales_team`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Khi table này crm_realtime phình to ra, chúng ta có thể thực hiện chia bảng thành nhiều partitions
để cho phép thực hiện query nhanh hơn.
Nếu tạo partition với range từng month theo trường registed_at kiểu datetime, yêu cầu các bước sau:
- Cập nhật các giá trị NULL của registed_at row đến các giá trị NOT NULL
- Thiết lập registed_at field là NOT NULL
- Thiết lập registed_at field là PRIMARY KEY
-
Thực hiện alter bảng và tạo các partitions với type RANGE và dùng function COLUMN cho kiểu dữ liệu
datetime (hoặc function UNIX_TIMESTAMP với kiểu dữ liệu timestamp)
Chúng ta thực hiện cụ thể như sau:
mysql>use crm;
mysql>update table crm_realtime set registed_at = updated_at where registed_at is NULL;
mysql>alter table crm_realtime modify column registed_at datetime NOT NULL;
mysql>alter table crm_realtime drop PRIMARY KEY, add primary key (`id`, `registed_at`);
msyql>alter table crm_realtime
PARTITION BY RANGE COLUMNS (registed_at) (
PARTITION p202003 VALUES LESS THAN ('2020-04-01'),
PARTITION p202004 VALUES LESS THAN ('2020-05-01'),
PARTITION p202005 VALUES LESS THAN ('2020-06-01'),
PARTITION p202006 VALUES LESS THAN ('2020-07-01'),
PARTITION p202007 VALUES LESS THAN ('2020-08-01'),
PARTITION p202008 VALUES LESS THAN ('2020-09-01'),
PARTITION p202009 VALUES LESS THAN ('2020-10-01'),
PARTITION p202010 VALUES LESS THAN ('2020-11-01'),
PARTITION p202011 VALUES LESS THAN ('2020-12-01'),
PARTITION p202012 VALUES LESS THAN ('2021-01-01'),
PARTITION p202101 VALUES LESS THAN ('2021-02-01'),
PARTITION p202102 VALUES LESS THAN ('2021-03-01')
);
III. Partition Management
- Show thông tin các partition của crm_realtime table
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='crm_realtime';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p202003 | 45495 |
| p202004 | 61398 |
| p202005 | 22573 |
| p202006 | 23146 |
| p202007 | 28804 |
| p202008 | 34576 |
| p202009 | 34566 |
| p202010 | 59116 |
| p202011 | 67070 |
| p202012 | 85030 |
| p202101 | 86926 |
| p202102 | 56797 |
or
SHOW CREATE TABLE crm_realtime\G
or
EXPLAIN SELECT * FROM crm_realtime\G
- Thêm mới partition từ table đã có sẵn các partitions
mysql> ALTER TABLE crm_realtime ADD PARTITION (
-> PARTITION p202103 VALUES LESS THAN ('2021-04-01')
-> );
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
- Thay đổi tên partition
Thông tin việc tạo các partition nên để theo quy tắc nhất định để cho phép select dữ liệu ở một hoặc một số
partition dễ dàng hơn. Chẳng hạn select dữ liệu theo mẫu pYYMM chẳng hạn.
mysql>
ALTER TABLE crm_realtime REORGANIZE PARTITION p1 INTO ( PARTITION p202003 VALUES LESS THAN ('2020-04-01') );
ALTER TABLE crm_realtime REORGANIZE PARTITION p2 INTO ( PARTITION p202004 VALUES LESS THAN ('2020-05-01') );
ALTER TABLE crm_realtime REORGANIZE PARTITION p3 INTO ( PARTITION p202005 VALUES LESS THAN ('2020-06-01') );
ALTER TABLE crm_realtime REORGANIZE PARTITION p4 INTO ( PARTITION p202006 VALUES LESS THAN ('2020-07-01') );
ALTER TABLE crm_realtime REORGANIZE PARTITION p5 INTO ( PARTITION p202007 VALUES LESS THAN ('2020-08-01') );
ALTER TABLE crm_realtime REORGANIZE PARTITION p6 INTO ( PARTITION p202008 VALUES LESS THAN ('2020-09-01') );
- Remove partition without delete data
ALTER TABLE table-name REMOVE PARTITIONING;
IV. How to use
- Show thông tin dữ liệu trong một partition được chỉ định
mysql> select id,name,registed_at from crm_realtime partition(p202103) limit 2;
+------------+------+---------------------+
| id | name | registed_at |
+------------+------+---------------------+
| 1000000009 | abc | 2021-03-10 02:00:41 |
| 1000101010 | def | 2021-03-12 02:00:58 |
+------------+------+---------------------+
2 rows in set (0.00 sec)
- Show thông tin dữ liệu trong một số partition
mysql> select id,name,registed_at from crm_realtime partition(p202102,p202103) limit 5;
+------------+--------------------+---------------------+
| id | name | registed_at |
+------------+--------------------+---------------------+
| 083878492 | nguyen van A | 2021-02-20 10:43:33 |
| 1000010000 | nguyen van B | 2021-02-19 02:00:32 |
| 1009944888 | nguyen van C | 2021-02-26 02:01:02 |
| 1011239922 | nguyen van D | 2021-02-17 16:58:04 |
| 1033355978 | nguyen van E | 2021-02-01 20:06:27 |
+------------+--------------------+---------------------+
5 rows in set (0.01 sec)
- Xóa dữ liệu trong partition
mysql> delete from crm_realtime partition(p202104) where name = "keepwalking";
Query OK, 1 row affected (0.00 sec)
NOTE
- Việc đánh partition table làm cho table phân thành nhiều mảnh vật lý. Việc query cũng sẽ lựa chọn một hoặc
một số partition tùy thuộc vào nhu cầu. Và query cả table (không sử dụng partition) dữ liệu vẫn toàn vẹn. - Viết thì dài chứ chốt lại chỉ cần chú ý đoạn dưới là được.
-- Tạo bảng mới giống bảng cũ mà không copy data
CREATE TABLE table_name_new LIKE `table_name`
-- Tạo bảng mới giống bảng cũ và coppy data sang
CREATE TABLE table_name_new AS SELECT * FROM `table_name` WHERE create_dt BETWEEN '2023-12-15 00:00:00' AND '2023-12-20 23:59:59' LIMIT 10000
-- Khi chưa có partition thì chạy cái này
ALTER TABLE table_name_new PARTITION BY RANGE (UNIX_TIMESTAMP(upload_time)) (
PARTITION p_20240101 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
PARTITION p_20240201 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
PARTITION nextmonth VALUES LESS THAN (MAXVALUE));
-- Nếu có partition rồi thì dùng REORGANIZE cái này -- nextmonth là PARTITION có LESS THAN (MAXVALUE) để update lại bắt đầu từ PARTITION này
ALTER TABLE table_name_new REORGANIZE PARTITION nextmonth INTO (
PARTITION p_20240101 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
PARTITION p_20240201 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
PARTITION nextmonth VALUES LESS THAN (MAXVALUE));
-- Cách drop PARTITION
ALTER TABLE table_name_new DROP PARTITION p_20240101;
ALTER TABLE table_name_new DROP PARTITION p_20240201;
ALTER TABLE table_name_new DROP PARTITION nextmonth;
-- API PHP dùng để chạy cronjob tạo parttion tự động.
public function action_updatePartition(){
$db = 'db_name';
$table = 'table_name';
$partitionToReorganise = 'nextmonth';
$currentYear = date("Y");
$currentMonth = date("m");
$currentDay = date("d");
$firstDayOfNewMonth = $currentYear.'-'.$currentMonth.'-01';
$newPartitionName = 'p_'.$currentYear.''.$currentMonth.'01';
// Tạo partiton hàng ngày
// $firstDayOfNewMonth = $currentYear.'-'.$currentMonth.'-'.$currentDay;
// $newPartitionName = 'p_'.$currentYear.''.$currentMonth.''.$currentDay;
$reorgQuery = "ALTER TABLE ".$table." REORGANIZE PARTITION ".$partitionToReorganise." INTO ( "
." PARTITION ".$newPartitionName." VALUES LESS THAN (UNIX_TIMESTAMP('".$firstDayOfNewMonth."')), "
." PARTITION ".$partitionToReorganise." VALUES LESS THAN (MAXVALUE) "
." )";
$updatePARTITION = DB::query($reorgQuery)->execute('db_connect_acc_root');
echo "glocalme_connection Updated PARTITION: $partitionToReorganise to $newPartitionName";
// Kiểm tra nếu partiton mà > 12 thì xóa bản partition name cũ nhất đi(để tối ưu DB)
$query_count = "SELECT count(PARTITION_NAME) as count_partition FROM information_schema.partitions WHERE TABLE_SCHEMA = '".$db."' AND TABLE_NAME = '".$table."' ";
$partition_Count = DB::query($query_count)->execute('information_schema')->current();
if(!empty($partition_Count)){
if($partition_Count['count_partition'] > 12){
$query_partition_old = "SELECT PARTITION_NAME, PARTITION_ORDINAL_POSITION FROM information_schema.partitions WHERE TABLE_SCHEMA = '".$db."' AND TABLE_NAME = '".$table."' AND PARTITION_ORDINAL_POSITION = 1";
$partition_old_names = DB::query($query_partition_old)->execute('information_schema')->current();
if(!empty($partition_old_names)){
$partition_old_name = $partition_old_names['PARTITION_NAME'];
$query = "ALTER TABLE $table DROP PARTITION $partition_old_name";
DB::query($query)->execute('db_connect_acc_root');
return "Droped PARTITION $partition_old_name in table $table Done";
}else{
return 'No PARTITION_ORDINAL_POSITION = 1';
}
}else{
return "No Drop PARTITION";
}
}else{
return "Empty partition_Count";
}
}
Cảm ơn bạn đã trả lời.